Missing case sensitivity for PostGIS database fields

Logged In: YES user_id=1799 Se corresponde con el ticket del iti 2292 Reported by bducke in date 2009-06-19

Logged In: YES user_id=1799 2009-06-19 changed by bducke Directly related to this the Table Manager has the same issue. If I edit the fields of an attribute table for a PostGIS layer and e.g. rename field1 to Field1 it complains that the field exists which is not strictly true as in an SQL DBMS field1 and Field2 or different tables. -------------------------------------------- 2009-06-19 changed by bducke Another related problem exporting a Shapefile layer with a DBF attribute table and uppercase fields to a PostGIS layer converts all attribute fields names to lower case. -------------------------------------------- 2009-06-19 changed by bducke More on this the Import fields extension will fail with an error message if the join fields in the two tables only differ by capitalization. If I choose not to import the join field then all other fields will be imported but converted to lower case in the process. Also the import process is successful but still ends in a nasty error message. To know that it actually succeeded I needed to lock into the PostgreSQL table directly. I then had to restart gvSIG remove the PostGIS layer from the project re-establish the PostGIS connection and add the layer again to make the changes visible. -------------------------------------------- 2009-06-22 changed by vagazzi Este ticket tiene relacion con 956 que esta validado pero que por lo visto sigue ocurriendo. -------------------------------------------- 2009-06-23 changed by vagazzi Lo que supongo que puede haber pasado con el ticket 956 es que se haya validado probando la funcionalidades a partir de una capa exportada a PostGIS desde gvSIG con lo cual dicha capa cuando es guardada en la BBDD ya lo hace con su nombre y campos en minusculas. -------------------------------------------- 2009-06-23 changed by vagazzi priority changed from major to critical -------------------------------------------- 2009-06-29 changed by pmercade owner changed from pmercade to jmvivo -------------------------------------------- 2009-07-10 changed by jmvivo En este ticket surgen varios casos 1. Que el "Nombre de capa" no distingue en entre mayusculas y minusculas. 2. Que en GDBMS distingue entre mayusculas y minusculas. 3. Que para exportar una tabla a postgreSQL se convierten todos los campos a minusculas. Hay que decir varias cosas - En PostgreSQL como en otros gestores por defecto los identificadores se crean siempre en minusculas a no ser que se le fuerce especificamente (usando comillas dobles en la construccion de sentencias). Mas informacion aqui http www.postgresql.org docs 8.3 static sql-syntax-lexical.html SQL-SYNTAX-IDENTIFIERS - Ahora mismo no se como se comporta GDBMS con el "Case Sensitive". - El driver de DBF creo que por defecto devuelve todos los nombres de campo en mayusculas (creo que mas por razones historicas). - Depende de la parte de la aplicacion puede que encontremos con diferentes "sensibilidades". - El ticket bug 956 habla de "campos" y no de "tablas". En el titulo de ticket habla de campos pero en la redaccion habla de nombre de tablas y si se puede abrir tablas con campos con mayusculas. Creo en este ticket hay demasiadas cosas y creo que no son buenas abordarlas en este punto. Como muestra un boton... por el arreglo del ticket bug 956 aparecieron 3 bug (no recuerdo los numeros). Ademas... habria que homogeneizar este comportamiento al resto de proveedores -------------------------------------------- 2009-07-14 changed by vagazzi Trying to clean up this mess all comments refer to postgis layer 1. gvSIG is only case sensitive with field"s names when adding a postgis layer (not editing) 2. gvSIG is NOT case sensitive when a) adding a layer which name has capital letters b) editing layer"s fields which names have capital letters c) create new or export a layer not only for the layer name but also for field names. This behaviour will not be changed in 1.9 version but we will inform user about that restrictions on the releases notes. -------------------------------------------- 2009-07-14 changed by bducke OK. I think I mixed up some things myself here (ahem). Sorry for causing so much fuss. I just reviewed the case-sensitivity issue on different DBMS. Turns out the SQL standard does not cover this and different SQL DBMS use different strategies. Since gvSIG supports many DBMS (Postgres MySQL Oracle ODBC...) the whole situation seems very tricky. Mostly the behaviour seems to be this 1) At the core level all SQL DBMS are case-sensitive. However most of them will hide this from the user by default. E.g. PostgreSQL requires marks for identifiers to be case-sensitive. 2) Thus there is case sensitivity when CREATING tables or fields. So My table will actually be stored with capital M never automatically converted to lower case. 3) RENAMING is handled at application not standard SQL level. Some DBMS provide their own SQL extensions for this such as ALTER TABLE in PostgreSQL. Other applications do this by dropping the old table then re-creating it with the new name. Same is true for fields. Since creation is case-sensitive renaming seems case-sensitive to the user as well. Obviously gvSIG can have its own strategy here but users may expect to be able to rename my table to My table . DBF is different from all this because it is just a file format not a proper SQL DBMS. So it"s entirely up to the individual application to decide a strategy. Since in the DBF case table name file name case sensitivity actually depends on the file system When creating fields some applications seem to automatically convert all names to lower case others to upper case. In any case all operations are completely case-insensitive. So there may also be applications that convert all to upper case when importing a DBF file (or to lower case). Nice mess So in summary I agree that the above comments and gvSIG"s current behaviour make sense 1. Be case sensitive when adding a table or field in any of the supported SQL DBMS but not when editing a DBF file. 2. Be case-insensitive for all operations on existing tables and fields. This means that checks for field and table names also need to be case-insensitive. When I first filed this bug report they were not So connecting to My table would fail because the check would look for my table which does not exist and then fail This behaviour needs to be corrected. I still consider it a bug because it prevents me from connecting to any tables and fields with mixed case names. 3. Some decision has to be made regarding DBF. Maybe the best way would be to automatically convert names of new fields to lower case and to also convert to lower case on import of DBF tables. As you mentioned users need to be advised about these strategies. -------------------------------------------- 2009-08-07 changed by pmercade owner changed from jmvivo to vcaballero -------------------------------------------- 2009-08-07 changed by vagazzi Cierro este ticket como quedamos ayer esta pendiente documentarlo he abierto ticket 2461. Las limitaciones que se comentan aqui no se adordaran para la 1.9. -------------------------------------------- 2009-08-07 changed by vagazzi resolution set to wontfix -------------------------------------------- 2009-08-07 changed by vagazzi status changed from new to closed --------------------------------------------