[GRASS-dev] [GRASS GIS] #3071: v.db.join: fails with SQLite because of lower/UPPER case issues

GRASS GIS trac at osgeo.org
Mon Jun 27 05:17:06 PDT 2016

#3071: v.db.join: fails with SQLite because of lower/UPPER case issues
  Reporter:  sbl          |      Owner:  grass-dev@…
      Type:  defect       |     Status:  new
  Priority:  normal       |  Milestone:  7.0.5
 Component:  Default      |    Version:  unspecified
Resolution:               |   Keywords:
       CPU:  Unspecified  |   Platform:  Unspecified

Comment (by sbl):

 Some update:

 If SQLite would adhere to SQL 92 standard[1], quoting of identifiers in
 SQL would be able to fix the issue more properly (meanig that the original
 case sensitive comparison was correct).

 However, it seems that SQLite is not case sensitive even if identifiers
 are quoted:
 v.db.addcolumn map=mygeology column='"c1" text'
 v.db.addcolumn map=mygeology column='"C1" text' # Fails due to duplicate

 So, a case-insensitive workaround for SQLite seems necessary anyway...

 And, having tested a simple column quoting approach (see new diffs), it
 seems that introducing column quoting seems to be a quite invasive change,
 as it allows for column names which can break several other tools (e.g.
 the attribute table viewer)...

 It seems also relevant what the expected user-input is (esp. quoted or
 unquoted column names)! Cause after
 db.execute sql='ALTER TABLE "mygeology" ADD COLUMN ""C1"" text'

 I get Warning messages for every SQL query on the map...
 WARNING: SQLite driver: unable to parse decltype: C1"" text

 [1] http://stackoverflow.com/a/19933159

Ticket URL: <https://trac.osgeo.org/grass/ticket/3071#comment:1>
GRASS GIS <https://grass.osgeo.org>

More information about the grass-dev mailing list