Mixed of uppercase/lowercase in the DATA statement is not supported by PostGIS

[nsavard]

Uppercase characters in the DATA parameter value of a map file are not handled correctly with a PostGIS connection.

Example: CONNECTIONTYPE postgis CONNECTION "dbname=cite-wfs110 user=testsuite host=localhost port=5432" DATA "surfaceProperty from PrimitiveGeoFeature USING UNIQUE id USING SRID=4326"

I'll attach a patch.

[nsavard]

patch to handle uppercase table and geometry column name

[nsavard]

I realized that I could have been more accurate in my description of the problem. I meant a table name and geometry column name are not handled correctly.

[pramsey]

Yes, I see. However, I'm not fond of your solution, as it changes the behavior of the data string from case insensitive to case super-sensitive. And PgSQL is, for the most part, case insensitive (or, rather, in the absence of stated preference, everything is coerced into lower case). So the current behavior is more "PgSQL attuned" than the patched behavior. Unfortunately, the "correct" patch would be to quote the "mixedCase" components in the data string, but the data string is already a quoted value.

[aboudreault]

pramsey, I understand your point and why the patch is not appropriate. Will check what could be done to be able to use " or \" inside a flex quoted value.

[pramsey]

That's great, thanks aboudreault. In some ways adding case sensitivity is better, certainly a lot less ugly than escaped quotation marks, but: it will certainly break peoples existing map files, while adding """" somehow won't do so.

[aboudreault]

pramsey, according to your comment, I would say that the lines 935-939 of mappostgis.c (the argument passed to the force_2d function, which add quotes) make the query super-sensitive. Do you agree that we shouldn't quote that column name?

[pramsey]

No. My concern isn't that we force things into case insensitivity, it's that we don't break backwards compatibility with existing map files. The quoting of that field indicates that in fact we already have a strict case requirement, with is forcing everyone to always use lower case, in both their map files and their databases. Which means allowing mixed case will not break any existing functionality, merely increase the possibilities for people with mixed databases.

So, I change my recommendation 180 degrees -- your patch to complete the support of fully quoted columns looks good to me.

[aboudreault]

Unfortunately, the patch wont work for when the data string is something like:

DATA "Way from (select Way,osm_id ,landuse, name from osm_polygon where landuse is not null)"

I guess we'll need to parse the "table_name" and quote all column names too.

[pramsey]

That starts to get deeply fiddly. Just supporting "coLumn from taBle" forms would at least be an improvement, no? Or does your mandate stretch further than that...

[aboudreault]

I agree that we could only fix that for the moment, and make a complete patch if needed in the future.

[aboudreault]

pramsey, I've tested the patch that nsavard provided. In his case, there are column names AND table names that use uppercase. I could easily fix the problem for the column names but can't do it cleanly for the from source. I cannot simply quote the from source (which is normally a table name) because it will break the query if the from source is a subquery.

I think it is not consistent if the column names are case sensitive but the table names isn't. nsavard can change his column names, it is only a test environnement. I propose to resolve this ticket as won't fix and make further work if it's a problem for other users. What's your thoughts?

[pramsey]

Fine w/ me, it's been this way for a loooong time :)