[GRASS-dev] [GRASS GIS] #2568: Upper-lower case issues with sqlite driver and affect on v.db.reconnect.all

[GRASS GIS]

#2568: Upper-lower case issues with sqlite driver and affect on v.db.reconnect.all
----------------------+-----------------------------------------------------
 Reporter:  ewcgrass  |       Owner:  grass-dev@…              
     Type:  defect    |      Status:  new                      
 Priority:  major     |   Milestone:  7.0.0                    
Component:  Database  |     Version:  svn-releasebranch70      
 Keywords:            |    Platform:  Linux                    
      Cpu:  x86-64    |  
----------------------+-----------------------------------------------------

Comment(by mmetz):

 Replying to [ticket:2568 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!

-- 
Ticket URL: <http://trac.osgeo.org/grass/ticket/2568#comment:1>
GRASS GIS <http://grass.osgeo.org>