[postgis-users] OGR2OGR Uppercase/Lowercase

[san at cobalt.rmnet.it]

dblasby wrote:
> San,
> 
> The reason I haven't added the always-quoting-column-names is because it can
> cause confusion.
> 
> For example,
> 
> CREATE TABLE MyTable (PlaceCode int, PlaceName text);
> SELECT AddGeometryColumn(...'MyGeometry'...);
> 
> You've now got a table that has changed the 'PlaceCode' column to 'placecode'
> and 'PlaceName' to 'placename' and 'MyTable' to 'mytable'.  On the other hand,
> you've
> added a geometry column with the name 'MyGeometry'.  If you were to access the
> table with something like:
> 
> SELECT placename,mygeometry FROM mytable;
> 
> You'll get an error because there is no column named mygeometry (its
> 'MyGeometry').  Even a query like:
> 
> SELECT PlaceName,MyGeometry FROM MyTable;
> 
> Will not work!
> 
> On the other hand, I do see your point - its probably a good idea to allow the
> postgis.sql functions "AddGeometryColumn",  "DropGeometryColumn", and a few
> others to always assume that the column and table names are assumed to be
> littorals.  Hopefully I'll be able to mitigate user confusion by sending out
> NOTICES that the column name isn't what they're expecting.

Why should users expect something different from what they typed in ?
IMHO, if a user types MixedCase she probably mean MixedCase ...
It's usually easier to type in lowercase, so if someone takes
care of hitting the SHIFT key he probably wants it.

If I use:

  CREATE TABLE "MyTable" ("PlaceCode" int, "PlaceName" text);

Should I use postgis this way ?: 

  SELECT AddGeometryColumn(...'"MyGeometry"'...);

I think I tried this before going further but I did not like to have
quotes in the text field of geometry_columns, I thouhgt it was unclean.

Anyway, I understand that the optional use of esplicit double-quotes
gives the users a choice between lowercase forcing or litterals.

If everything else will work it might be ok that way...

The shp2pgsql command should nonetheless give the user a choice
of quoting or not all the names used in the output query (attributes,
database and table). I remember I could not use MixedCase tables
or databases with shp2pgsql.


> 
> The mapserver connector is another problem - I think its a bad idea to always
> quote column (and table) names!
> 
> For example, the above example "SELECT PlaceName,MyGeometry FROM MyTable;" will
> now fail because the column "PlaceName" is really 'placename' and the table
> name "MyTable" is really 'mytable'.  There will be a lot of people
> (justifiably) upset that their SQL isn't working in mapserver when its working
> perfectly fine in psql.
> 
> Besides, you can always do queries like:
> DATA 'the_geom from (SELECT "MyGeometry" as the_geom, placename,oid FROM
> mytable) as foo using unique oid using SRID=1234'
> 
> I dont know much about the shp2pgsql utility, so I will not comment on that
> (perhaps jeff will).
> 
> What do you think?

I think there should be in all cases the possibility of using or not
the force-to-lowercase effect(?). I started working on patches because
I had NO POSSIBILITY of using databases/tables/columns with mixed case
names togheter with postgis/mapserv.

It might be enaught to only patch shp2pgsql, but I am not sure at the
moment, did anyone else try to use names-quoting with postgis/mapserv ?

> dave
> ps. I really do think you're asking for trouble if your database has to quote
> column or table names.
> 

san;