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

[GRASS GIS]

#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
 columns
 }}}

 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>