[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):

 Replying to [comment:2 neteler]:
 > I checked the patches:
 >
 >  * v.db.addcolumn_quote.diff​: contains backticks. This is unlikely to
 work in Python/SQL. Is this an encoding issue of the patch?
 backticks are used in MySQL to quote identifiers:
 http://dev.mysql.com/doc/refman/5.7/en/identifiers.html
 >  * v.db.join.diff​: Not sure how SQLite behaves. I found an older
 discussion here:  http://stackoverflow.com/a/19933159/452464 - is there
 any official reference on the SQLite site?
 See: https://www.sqlite.org/lang_keywords.html
 >  * v.db.join_quote.diff​: If quotes are needed then also the other SQL
 queries needs to undergo an audit. Then we need to write down our coding
 style somewhere near
 https://trac.osgeo.org/grass/wiki/Grass7/VectorLib/OGRInterface or better
 a dedicated SQL/DBMI page
 It is indeed something that probably requires a deliberate decision either
 to allow for table and column names that require quoted identifiers in
 GRASS modules or not (of course users can do anything the DB backends
 allow for outside GRASS or using db.execute).[[BR]]
 A drawback of quoted identifiers is that also nonsense - including SQL
 keywords - is possible e.g. in column names.[[BR]]
 However, v.in.ogr writes data source names (which can contain upper case
 characters) as layer names, which again are (at least in some cases) used
 as table names. And also column names can be laundered to lower case
 during import (or not, users choice), making it hard to foresee if quoting
 is required, esp. in multi user environments.[[BR]]
 So, at the moment at least, users can run into UPPER/lower case issues for
 different reasons.

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