Changeset 63238

Timestamp:

Nov 28, 2014, 2:15:16 AM (10 years ago)

Author:

neteler

Message:

v.db.update: add support for extended SQLite functions

Location:

grass/trunk

Files:

• db/drivers/sqlite/db.c (modified) (1 diff)
• scripts/v.db.update/v.db.update.html (modified) (4 diffs)
• scripts/v.db.update/v.db.update.py (modified) (4 diffs)

grass/trunk/db/drivers/sqlite/db.c

@@ -111 +111 @@
     }
 
+    /* enable loading of extensions */
+    sqlite3_enable_load_extension(sqlite, 1);
+
     /* set the sqlite busy handler */
     sqlite3_busy_handler(sqlite, sqlite_busy_callback, NULL);

grass/trunk/scripts/v.db.update/v.db.update.html

@@ -14 +14 @@
 
 <h2>EXAMPLES</h2>
+
+<h3>Replacing of NULL values</h3>
 
 In this example, selectively display lakes without (blue) and with
@@ -38 +40 @@
 </pre></div>
 
-Spearfish example: adding new column, copying values from other table
+<h3>Updating of columns with on the fly calculation</h3>
+
+Spearfish example: adding new column, copying values from another table
 column with on the fly calculation:
 <div class="code"><pre>
@@ -47 +51 @@
 </pre></div>
 
+<h3>Type casting</h3>
+
 Type cast (type conversion) of strings to double precision
 (unsupported by DBF driver):
@@ -53 +59 @@
 v.db.update mygeodetic_pts col=zval qcol="CAST(z_value AS double precision)" \
             where="z_value &lt;&gt; 'N/A'"
+</pre></div>
+
+<h3>Updating of columns with on the fly calculation (SQLite extended functions)</h3>
+
+North Carolina data set example: adding new column, copying values from
+another table column with on the fly calculation:
+
+<div class="code"><pre>
+g.copy vect=precip_30ynormals,myprecip_30ynormals
+v.db.addcolumn myprecip_30ynormals column="logjuly double precision"
+v.db.update myprecip_30ynormals column="logjuly" qcolumn="log(jul)" \
+  sqliteextra=$HOME/sqlite_extensions/libsqlitefunctions.so
+
+v.db.select myprecip_30ynormals columns=jul,logjuly
+jul|logjuly
+132.842|4.88916045210132
+127|4.84418708645859
+124.206|4.82194147751127
+104.648|4.65060233738593
+98.298|4.58800368106618
+...
 </pre></div>
 

grass/trunk/scripts/v.db.update/v.db.update.py

@@ -44 +44 @@
 #%option G_OPT_DB_WHERE
 #%end
+#%option G_OPT_F_INPUT
+#% key: sqliteextra
+#% description: Name of SQLite extension file for extra functions (SQLite backend only)
+#% gisprompt: old,bin,file
+#% required: no
+#%end
 
 import sys
@@ -56 +62 @@
     qcolumn = options['query_column']
     where = options['where']
+    sqlitefile  = options['sqliteextra']
 
     mapset = grass.gisenv()['MAPSET']
@@ -71 +78 @@
     database = f['database']
     driver = f['driver']
+
+    # check for SQLite backend for extra functions
+    if sqlitefile and driver != "sqlite":
+        grass.fatal(_("Use of libsqlitefunctions only with SQLite backend"))
+    if driver == "sqlite" and sqlitefile:
+        if not os.access(sqlitefile, os.R_OK):
+            grass.fatal(_("File <%s> not found") % sqlitefile)
 
     # checking column types
@@ -94 +108 @@
         cmd += " WHERE " + where
 
+    # SQLite: preload extra functions from extension lib if provided by user
+    if sqlitefile:
+        sqliteload = "SELECT load_extension('%s');\n" % sqlitefile
+        cmd = sqliteload + cmd
+
     grass.verbose("SQL: \"%s\"" % cmd)
-
     grass.write_command('db.execute', input = '-', database = database, driver = driver, stdin = cmd)