Upper-lower case issues with sqlite driver and affect on v.db.reconnect.all

[ewcgrass]

I am rebuilding an number of existing mapsets from GRASS 6.4.4 (svn 02 Nov 2013) to GRASS 7.0.0 (svn 17 Jan 2015), per the directions given in the wiki. The 6.4.4 mapset database files are in .dbf format, with conversions being done to sqlite format. v.build.all works fine, as does db.connect -d, but v.db.reconnect.all terminates early when it encounters a .dbf table that has the name "CAT" (upper case) in the key column, as opposed to "cat" (lower case).

What happens is the sqlite table is created with all the attributes being properly copied over, but the sqlite table refuses to connect to the vector file. Once the .dbf table has been converted to sqlite format, then I can change the case of the key column using sqliteman, after which the table and vector file will then connect properly using either v.db.connect or v.db.reconnect.all. However, v.db.reconnect.all will terminate early again upon encountering the next .dbf table in which the key column is "CAT" in upper case.

I can show the .dbf table attibutes by right-clicking on the vector map being converted (after having used v.build.all, of course) on the layer manager gui, and can access the "manage table" tab in the gui that appears. However, it shows "cat" in lower case when it is in fact in upper case "CAT", and will not allow the name to be changed from "CAT" to "cat" because the name cat already exists, nor will it allow CAT to be changed temporarily to something like "cat1" as a temporary place holder so it can be changed again to "cat", since "cat" or "CAT" is required in the key column.

The .dbf tables that cause this problem are tables that have been modified and saved using OpenOffice or LibreOffice calc, which defaults to saving all column names to upper case.

I could get around this issue by repeatedly using v.db.reconnect.all and sqliteman until all tables have been properly copied over and connected, but that defeats the whole purpose of having v.db.reconnect.all in the first place, and also highlight the problem that exists with the sqlite driver regarding column name capitalization, which is something that is likely to affect many other GRASS users.

My suggest(s) for correcting this would be to either have a means of ignoring errors in v.db.reconnect.all (but this could cause other yet undefined problems), or to have the sqlite driver (or whatever other GRASS module is used by v.db.reconnect.all that is causing early termination) to accept "CAT" as well as "cat".

This issue does not appear to be a random one, since I have been able to encounter it and correct it (by recursively using sqliteman with v.db.reconnect.all and/or v.db.connect with the newly changed table with the sqlite file) repeatedly with all vector files for which the category column in the dbf table is spelled "CAT".

Good work on GRASS 7 folks... its impressive!

[mmetz]

Replying to ewcgrass:

I am rebuilding an number of existing mapsets from GRASS 6.4.4 (svn 02 Nov 2013) to GRASS 7.0.0 (svn 17 Jan 2015), per the directions given in the wiki. The 6.4.4 mapset database files are in .dbf format, with conversions being done to sqlite format. v.build.all works fine, as does db.connect -d, but v.db.reconnect.all terminates early when it encounters a .dbf table that has the name "CAT" (upper case) in the key column, as opposed to "cat" (lower case).

What happens is the sqlite table is created with all the attributes being properly copied over, but the sqlite table refuses to connect to the vector file. Once the .dbf table has been converted to sqlite format, then I can change the case of the key column using sqliteman, after which the table and vector file will then connect properly using either v.db.connect or v.db.reconnect.all. However, v.db.reconnect.all will terminate early again upon encountering the next .dbf table in which the key column is "CAT" in upper case.

I can show the .dbf table attibutes by right-clicking on the vector map being converted (after having used v.build.all, of course) on the layer manager gui, and can access the "manage table" tab in the gui that appears. However, it shows "cat" in lower case when it is in fact in upper case "CAT", and will not allow the name to be changed from "CAT" to "cat" because the name cat already exists, nor will it allow CAT to be changed temporarily to something like "cat1" as a temporary place holder so it can be changed again to "cat", since "cat" or "CAT" is required in the key column.

The .dbf tables that cause this problem are tables that have been modified and saved using OpenOffice or LibreOffice calc, which defaults to saving all column names to upper case.

Have you updated the vector db connections (in GRASS 6.4) after editing the tables? If you change the key column name from lower case to upper case, you need to run v.db.connect map= key= first before doing anything else.

I could get around this issue by repeatedly using v.db.reconnect.all and sqliteman until all tables have been properly copied over and connected, but that defeats the whole purpose of having v.db.reconnect.all in the first place, and also highlight the problem that exists with the sqlite driver regarding column name capitalization, which is something that is likely to affect many other GRASS users.

My suggest(s) for correcting this would be to either have a means of ignoring errors in v.db.reconnect.all (but this could cause other yet undefined problems), or to have the sqlite driver (or whatever other GRASS module is used by v.db.reconnect.all that is causing early termination) to accept "CAT" as well as "cat".

This issue does not appear to be a random one, since I have been able to encounter it and correct it (by recursively using sqliteman with v.db.reconnect.all and/or v.db.connect with the newly changed table with the sqlite file) repeatedly with all vector files for which the category column in the dbf table is spelled "CAT".

Good work on GRASS 7 folks... its impressive!

[ewcgrass]

Thanks for solving this so quickly. I missed that entirely because having different key column names never negatively affected doing work with the vector file attribute data in GRASS 6.4. Running v.db.connect map= key=cat in GRASS 6.4 before migrating fixes the problem, as it does running v.db.connect map= key=CAT in GRASS 7.0 after migrating from .dbf to sqlite!

v.db.reconnect.all still hangs at every other database error it encounters during migration (eg. syntax issues re. column names vs sqlite keywords, quotes or squiggles in table data, dropping columns due to name length >10 in the .dbf table which for odd reasons were never caught earlier, etc.). But I guess this is not all bad, as it is an opportunity to clean up database files? About 300 dbf tables migrations completed, about 1200 more to go.

Perhaps this Ticket should be changed from a "bug" to a "wish", where v.db.reconnect.all could perhaps be modified to catch these migration errors more graciously?