1 /*
2
3 virtualgpkg.c -- SQLite3 extension [VIRTUAL TABLE accessing GPKG tables]
4
5 version 5.0, 2020 August 1
6
7 Author: Sandro Furieri a.furieri@lqt.it
8
9 -----------------------------------------------------------------------------
10
11 Version: MPL 1.1/GPL 2.0/LGPL 2.1
12
13 The contents of this file are subject to the Mozilla Public License Version
14 1.1 (the "License"); you may not use this file except in compliance with
15 the License. You may obtain a copy of the License at
16 http://www.mozilla.org/MPL/
17
18 Software distributed under the License is distributed on an "AS IS" basis,
19 WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
20 for the specific language governing rights and limitations under the
21 License.
22
23 The Original Code is the SpatiaLite library
24
25 The Initial Developer of the Original Code is Alessandro Furieri
26
27 Portions created by the Initial Developer are Copyright (C) 2015-2021
28 the Initial Developer. All Rights Reserved.
29
30 Contributor(s):
31
32 Alternatively, the contents of this file may be used under the terms of
33 either the GNU General Public License Version 2 or later (the "GPL"), or
34 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
35 in which case the provisions of the GPL or the LGPL are applicable instead
36 of those above. If you wish to allow use of your version of this file only
37 under the terms of either the GPL or the LGPL, and not to allow others to
38 use your version of this file under the terms of the MPL, indicate your
39 decision by deleting the provisions above and replace them with the notice
40 and other provisions required by the GPL or the LGPL. If you do not delete
41 the provisions above, a recipient may use your version of this file under
42 the terms of any one of the MPL, the GPL or the LGPL.
43
44 */
45
46 #include <stdlib.h>
47 #include <stdio.h>
48 #include <string.h>
49
50 #if defined(_WIN32) && !defined(__MINGW32__)
51 #include "config-msvc.h"
52 #else
53 #include "config.h"
54 #endif
55
56 #include <spatialite/sqlite.h>
57
58 #include <spatialite/spatialite_ext.h>
59 #include <spatialite/gaiaaux.h>
60 #include <spatialite/gaiageo.h>
61 #include <spatialite/geopackage.h>
62
63 /* 64 bit integer: portable format for printf() */
64 #if defined(_WIN32) && !defined(__MINGW32__)
65 #define FRMT64 "%I64d"
66 #else
67 #define FRMT64 "%lld"
68 #endif
69
70 #if defined(_WIN32) && !defined(__MINGW32__)
71 #define LONG64_MAX _I64_MAX
72 #define LONG64_MIN _I64_MIN
73 #else
74 #define LONG64_MAX 9223372036854775807LL
75 #define LONG64_MIN (-LONG64_MAX + 1)
76 #endif
77
78 #ifdef _WIN32
79 #define strcasecmp _stricmp
80 #endif /* not WIN32 */
81
82 #ifdef ENABLE_GEOPACKAGE /* only if GeoPackage support is enabled */
83
84 struct sqlite3_module my_gpkg_module;
85
86 typedef struct SqliteValue
87 {
88 /* a multitype storing a column value */
89 int Type;
90 sqlite3_int64 IntValue;
91 double DoubleValue;
92 char *Text;
93 unsigned char *Blob;
94 int Size;
95 } SqliteValue;
96 typedef SqliteValue *SqliteValuePtr;
97
98 typedef struct VirtualGPKGStruct
99 {
100 /* extends the sqlite3_vtab struct */
101 const sqlite3_module *pModule; /* ptr to sqlite module: USED INTERNALLY BY SQLITE */
102 int nRef; /* # references: USED INTERNALLY BY SQLITE */
103 char *zErrMsg; /* error message: USE INTERNALLY BY SQLITE */
104 sqlite3 *db; /* the sqlite db holding the virtual table */
105 char *db_prefix; /* the prefix identifying the ATTACHED-DB where the table is */
106 char *table; /* the real-table name */
107 int nColumns; /* the # columns into the table */
108 char **Column; /* the name for each column */
109 char **Type; /* the type for each column */
110 int *NotNull; /* NotNull clause for each column */
111 SqliteValuePtr *Value; /* the current-row value for each column */
112 char *GeoColumn; /* name of the Geometry column */
113 int Srid; /* SRID of the Geometry column */
114 int GeoType; /* Type of the Geometry column */
115 } VirtualGPKG;
116 typedef VirtualGPKG *VirtualGPKGPtr;
117
118 typedef struct VirtualGPKGCursorStruct
119 {
120 /* extends the sqlite3_vtab_cursor struct */
121 VirtualGPKGPtr pVtab; /* Virtual table of this cursor */
122 sqlite3_stmt *stmt;
123 sqlite3_int64 current_row; /* the current row ID */
124 int eof; /* the EOF marker */
125 } VirtualGPKGCursor;
126 typedef VirtualGPKGCursor *VirtualGPKGCursorPtr;
127
128 static SqliteValuePtr
value_alloc(void)129 value_alloc (void)
130 {
131 /* allocates and initialites a Value multitype */
132 SqliteValuePtr p = malloc (sizeof (SqliteValue));
133 p->Type = SQLITE_NULL;
134 p->Text = NULL;
135 p->Blob = NULL;
136 return p;
137 }
138
139 static void
value_free(SqliteValuePtr p)140 value_free (SqliteValuePtr p)
141 {
142 /* freeing a Value multitype */
143 if (!p)
144 return;
145 if (p->Text)
146 free (p->Text);
147 if (p->Blob)
148 free (p->Blob);
149 free (p);
150 }
151
152 static void
value_set_null(SqliteValuePtr p)153 value_set_null (SqliteValuePtr p)
154 {
155 /* setting a NULL value to the multitype */
156 if (!p)
157 return;
158 p->Type = SQLITE_NULL;
159 if (p->Text)
160 free (p->Text);
161 if (p->Blob)
162 free (p->Blob);
163 p->Text = NULL;
164 p->Blob = NULL;
165 }
166
167 static void
value_set_int(SqliteValuePtr p,sqlite3_int64 value)168 value_set_int (SqliteValuePtr p, sqlite3_int64 value)
169 {
170 /* setting an INT value to the multitype */
171 if (!p)
172 return;
173 p->Type = SQLITE_INTEGER;
174 if (p->Text)
175 free (p->Text);
176 if (p->Blob)
177 free (p->Blob);
178 p->Text = NULL;
179 p->Blob = NULL;
180 p->IntValue = value;
181 }
182
183 static void
value_set_double(SqliteValuePtr p,double value)184 value_set_double (SqliteValuePtr p, double value)
185 {
186 /* setting a DOUBLE value to the multitype */
187 if (!p)
188 return;
189 p->Type = SQLITE_FLOAT;
190 if (p->Text)
191 free (p->Text);
192 if (p->Blob)
193 free (p->Blob);
194 p->Text = NULL;
195 p->Blob = NULL;
196 p->DoubleValue = value;
197 }
198
199 static void
value_set_text(SqliteValuePtr p,const char * value,int size)200 value_set_text (SqliteValuePtr p, const char *value, int size)
201 {
202 /* setting a TEXT value to the multitype */
203 if (!p)
204 return;
205 p->Type = SQLITE_TEXT;
206 if (p->Text)
207 free (p->Text);
208 if (p->Blob)
209 free (p->Blob);
210 p->Blob = NULL;
211 p->Text = malloc (size);
212 memcpy (p->Text, value, size);
213 p->Size = size;
214 }
215
216 static void
value_set_blob(SqliteValuePtr p,const unsigned char * value,int size)217 value_set_blob (SqliteValuePtr p, const unsigned char *value, int size)
218 {
219 /* setting a BLOB value to the multitype */
220 if (!p)
221 return;
222 p->Type = SQLITE_BLOB;
223 if (p->Text)
224 free (p->Text);
225 if (p->Blob)
226 free (p->Blob);
227 p->Text = NULL;
228 p->Blob = malloc (size);
229 memcpy (p->Blob, value, size);
230 p->Size = size;
231 }
232
233 static void
vgpkg_read_row(VirtualGPKGCursorPtr cursor)234 vgpkg_read_row (VirtualGPKGCursorPtr cursor)
235 {
236 /* trying to read a row from GPKG real-table */
237 sqlite3_stmt *stmt;
238 int ret;
239 int ic;
240 const char *text;
241 const unsigned char *blob;
242 int size;
243 sqlite3_int64 pk;
244 stmt = cursor->stmt;
245 sqlite3_bind_int64 (stmt, 1, cursor->current_row);
246 ret = sqlite3_step (stmt);
247 if (ret == SQLITE_ROW)
248 {
249 pk = sqlite3_column_int64 (stmt, 0);
250 for (ic = 0; ic < cursor->pVtab->nColumns; ic++)
251 {
252 /* fetching column values */
253 switch (sqlite3_column_type (stmt, ic + 1))
254 {
255 case SQLITE_INTEGER:
256 value_set_int (*(cursor->pVtab->Value + ic),
257 sqlite3_column_int64 (stmt, ic + 1));
258 break;
259 case SQLITE_FLOAT:
260 value_set_double (*(cursor->pVtab->Value + ic),
261 sqlite3_column_double (stmt, ic + 1));
262 break;
263 case SQLITE_TEXT:
264 text = (char *) sqlite3_column_text (stmt, ic + 1);
265 size = sqlite3_column_bytes (stmt, ic + 1);
266 value_set_text (*(cursor->pVtab->Value + ic), text, size);
267 break;
268 case SQLITE_BLOB:
269 blob = sqlite3_column_blob (stmt, ic + 1);
270 size = sqlite3_column_bytes (stmt, ic + 1);
271 value_set_blob (*(cursor->pVtab->Value + ic), blob, size);
272 break;
273 case SQLITE_NULL:
274 default:
275 value_set_null (*(cursor->pVtab->Value + ic));
276 break;
277 };
278 }
279 }
280 else
281 {
282 /* an error occurred */
283 cursor->eof = 1;
284 return;
285 }
286 cursor->eof = 0;
287 cursor->current_row = pk;
288 }
289
290 static int
vgpkg_insert_row(VirtualGPKGPtr p_vt,sqlite3_int64 * rowid,int argc,sqlite3_value ** argv)291 vgpkg_insert_row (VirtualGPKGPtr p_vt, sqlite3_int64 * rowid, int argc,
292 sqlite3_value ** argv)
293 {
294 /* trying to insert a row into GPKG real-table */
295 sqlite3_stmt *stmt;
296 int ret;
297 int i;
298 int ic;
299 char prefix[16];
300 const char *text;
301 const unsigned char *blob;
302 gaiaOutBuffer sql_statement;
303 int size;
304 char *sql;
305 char buf[256];
306 char *xprefix;
307 char *xname;
308 gaiaOutBufferInitialize (&sql_statement);
309 xprefix = gaiaDoubleQuotedSql (p_vt->db_prefix);
310 xname = gaiaDoubleQuotedSql (p_vt->table);
311 sql = sqlite3_mprintf ("INSERT INTO \"%s\".\"%s\" ", xprefix, xname);
312 free (xname);
313 free (xprefix);
314 gaiaAppendToOutBuffer (&sql_statement, sql);
315 sqlite3_free (sql);
316 for (ic = 0; ic < p_vt->nColumns; ic++)
317 {
318 if (ic == 0)
319 strcpy (prefix, "(");
320 else
321 strcpy (prefix, ", ");
322 xname = gaiaDoubleQuotedSql (*(p_vt->Column + ic));
323 sql = sqlite3_mprintf ("%s%s", prefix, xname);
324 free (xname);
325 gaiaAppendToOutBuffer (&sql_statement, sql);
326 sqlite3_free (sql);
327 }
328 gaiaAppendToOutBuffer (&sql_statement, ") VALUES ");
329 for (ic = 0; ic < p_vt->nColumns; ic++)
330 {
331 if (ic == 0)
332 strcpy (prefix, "(");
333 else
334 strcpy (prefix, ", ");
335 if (strcasecmp (*(p_vt->Column + ic), p_vt->GeoColumn) == 0)
336 {
337 /* this is the geometry column */
338 sql = sqlite3_mprintf ("%sAsGPB(?)", prefix);
339 gaiaAppendToOutBuffer (&sql_statement, sql);
340 sqlite3_free (sql);
341 }
342 else
343 {
344 sprintf (buf, "%s?", prefix);
345 gaiaAppendToOutBuffer (&sql_statement, buf);
346 }
347 }
348 gaiaAppendToOutBuffer (&sql_statement, ")");
349 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
350 ret =
351 sqlite3_prepare_v2 (p_vt->db, sql_statement.Buffer,
352 strlen (sql_statement.Buffer), &stmt, NULL);
353 else
354 ret = SQLITE_ERROR;
355 gaiaOutBufferReset (&sql_statement);
356 if (ret != SQLITE_OK)
357 return SQLITE_ERROR;
358 for (i = 2; i < argc; i++)
359 {
360 switch (sqlite3_value_type (argv[i]))
361 {
362 case SQLITE_INTEGER:
363 sqlite3_bind_int64 (stmt, i - 1, sqlite3_value_int64 (argv[i]));
364 break;
365 case SQLITE_FLOAT:
366 sqlite3_bind_double (stmt, i - 1,
367 sqlite3_value_double (argv[i]));
368 break;
369 case SQLITE_TEXT:
370 text = (char *) sqlite3_value_text (argv[i]);
371 size = sqlite3_value_bytes (argv[i]);
372 sqlite3_bind_text (stmt, i - 1, text, size, SQLITE_STATIC);
373 break;
374 case SQLITE_BLOB:
375 blob = sqlite3_value_blob (argv[i]);
376 size = sqlite3_value_bytes (argv[i]);
377 sqlite3_bind_blob (stmt, i - 1, blob, size, SQLITE_STATIC);
378 break;
379 case SQLITE_NULL:
380 default:
381 sqlite3_bind_null (stmt, i - 1);
382 break;
383 };
384 }
385 sqlite3_finalize (stmt);
386 *rowid = sqlite3_last_insert_rowid (p_vt->db);
387 return SQLITE_OK;
388 }
389
390 static int
vgpkg_update_row(VirtualGPKGPtr p_vt,sqlite3_int64 rowid,int argc,sqlite3_value ** argv)391 vgpkg_update_row (VirtualGPKGPtr p_vt, sqlite3_int64 rowid, int argc,
392 sqlite3_value ** argv)
393 {
394 /* trying to update a row in GPKG real-table */
395 sqlite3_stmt *stmt;
396 int ret;
397 int i;
398 int ic;
399 char prefix[16];
400 const char *text;
401 const unsigned char *blob;
402 gaiaOutBuffer sql_statement;
403 int size;
404 char *sql;
405 char buf[256];
406 char *xprefix;
407 char *xname;
408 gaiaOutBufferInitialize (&sql_statement);
409 xprefix = gaiaDoubleQuotedSql (p_vt->db_prefix);
410 xname = gaiaDoubleQuotedSql (p_vt->table);
411 sql = sqlite3_mprintf ("UPDATE \"%s\".\"%s\" SET", xprefix, xname);
412 free (xname);
413 free (xprefix);
414 gaiaAppendToOutBuffer (&sql_statement, sql);
415 sqlite3_free (sql);
416 for (ic = 0; ic < p_vt->nColumns; ic++)
417 {
418 if (ic == 0)
419 strcpy (prefix, " ");
420 else
421 strcpy (prefix, ", ");
422 xname = gaiaDoubleQuotedSql (*(p_vt->Column + ic));
423 if (strcasecmp (*(p_vt->Column + ic), p_vt->GeoColumn) == 0)
424 {
425 /* this is the geometry column */
426 sql = sqlite3_mprintf ("%s%s = AsGPB(?)", prefix, xname);
427 }
428 else
429 sql = sqlite3_mprintf ("%s%s = ?", prefix, xname);
430 free (xname);
431 gaiaAppendToOutBuffer (&sql_statement, sql);
432 sqlite3_free (sql);
433 }
434 sprintf (buf, " WHERE ROWID = " FRMT64, rowid);
435 gaiaAppendToOutBuffer (&sql_statement, buf);
436 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
437 ret =
438 sqlite3_prepare_v2 (p_vt->db, sql_statement.Buffer,
439 strlen (sql_statement.Buffer), &stmt, NULL);
440 else
441 ret = SQLITE_ERROR;
442 gaiaOutBufferReset (&sql_statement);
443 if (ret != SQLITE_OK)
444 return SQLITE_ERROR;
445 for (i = 2; i < argc; i++)
446 {
447 switch (sqlite3_value_type (argv[i]))
448 {
449 case SQLITE_INTEGER:
450 sqlite3_bind_int64 (stmt, i - 1, sqlite3_value_int64 (argv[i]));
451 break;
452 case SQLITE_FLOAT:
453 sqlite3_bind_double (stmt, i - 1,
454 sqlite3_value_double (argv[i]));
455 break;
456 case SQLITE_TEXT:
457 text = (char *) sqlite3_value_text (argv[i]);
458 size = sqlite3_value_bytes (argv[i]);
459 sqlite3_bind_text (stmt, i - 1, text, size, SQLITE_STATIC);
460 break;
461 case SQLITE_BLOB:
462 blob = sqlite3_value_blob (argv[i]);
463 size = sqlite3_value_bytes (argv[i]);
464 sqlite3_bind_blob (stmt, i - 1, blob, size, SQLITE_STATIC);
465 break;
466 case SQLITE_NULL:
467 default:
468 sqlite3_bind_null (stmt, i - 1);
469 break;
470 };
471 }
472 ret = sqlite3_step (stmt);
473 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
474 ;
475 else
476 {
477 sqlite3_finalize (stmt);
478 return ret;
479 }
480 sqlite3_finalize (stmt);
481 return SQLITE_OK;
482 }
483
484 static int
vgpkg_delete_row(VirtualGPKGPtr p_vt,sqlite3_int64 rowid)485 vgpkg_delete_row (VirtualGPKGPtr p_vt, sqlite3_int64 rowid)
486 {
487 /* trying to delete a row from GPKG real-table */
488 char *sql_statement;
489 char dummy[256];
490 int ret;
491 char *xprefix;
492 char *xname;
493 xprefix = gaiaDoubleQuotedSql (p_vt->db_prefix);
494 xname = gaiaDoubleQuotedSql (p_vt->table);
495 sprintf (dummy, FRMT64, rowid);
496 sql_statement =
497 sqlite3_mprintf ("DELETE FROM \"%s\" WHERE ROWID = %s", xname, dummy);
498 free (xname);
499 free (xprefix);
500 ret = sqlite3_exec (p_vt->db, sql_statement, NULL, NULL, NULL);
501 sqlite3_free (sql_statement);
502 return ret;
503 }
504
505 static void
free_table(VirtualGPKGPtr p_vt)506 free_table (VirtualGPKGPtr p_vt)
507 {
508 /* memory cleanup; freeing the virtual table struct */
509 int i;
510 if (!p_vt)
511 return;
512 if (p_vt->db_prefix)
513 sqlite3_free (p_vt->db_prefix);
514 if (p_vt->table)
515 sqlite3_free (p_vt->table);
516 if (p_vt->Column)
517 {
518 for (i = 0; i < p_vt->nColumns; i++)
519 {
520 if (*(p_vt->Column + i))
521 sqlite3_free (*(p_vt->Column + i));
522 }
523 sqlite3_free (p_vt->Column);
524 }
525 if (p_vt->Type)
526 {
527 for (i = 0; i < p_vt->nColumns; i++)
528 {
529 if (*(p_vt->Type + i))
530 sqlite3_free (*(p_vt->Type + i));
531 }
532 sqlite3_free (p_vt->Type);
533 }
534 if (p_vt->NotNull)
535 sqlite3_free (p_vt->NotNull);
536 if (p_vt->Value)
537 {
538 for (i = 0; i < p_vt->nColumns; i++)
539 {
540 if (*(p_vt->Value + i))
541 value_free (*(p_vt->Value + i));
542 }
543 sqlite3_free (p_vt->Value);
544 }
545 if (p_vt->GeoColumn)
546 sqlite3_free (p_vt->GeoColumn);
547 sqlite3_free (p_vt);
548 }
549
550 static int
vgpkg_create(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)551 vgpkg_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
552 sqlite3_vtab ** ppVTab, char **pzErr)
553 {
554 /* creates the virtual table connected to some GPKG table */
555 char *vtable = NULL;
556 char *db_prefix = NULL;
557 char *table = NULL;
558 int ret;
559 int i;
560 int len;
561 int n_rows;
562 int n_columns;
563 const char *col_name;
564 const char *col_type;
565 int not_null;
566 const char *type;
567 int has_z;
568 int has_m;
569 char **results;
570 char *sql;
571 char prefix[16];
572 char *xdb_prefix;
573 char *xname;
574 gaiaOutBuffer sql_statement;
575 VirtualGPKGPtr p_vt = NULL;
576 if (pAux)
577 pAux = pAux; /* unused arg warning suppression */
578 gaiaOutBufferInitialize (&sql_statement);
579 /* checking for table_name */
580 if (argc == 4)
581 {
582 vtable = gaiaDequotedSql ((char *) argv[2]);
583 db_prefix = gaiaDequotedSql ("main");
584 table = gaiaDequotedSql ((char *) argv[3]);
585 }
586 else if (argc == 5)
587 {
588 vtable = gaiaDequotedSql ((char *) argv[2]);
589 db_prefix = gaiaDequotedSql ((char *) argv[3]);
590 table = gaiaDequotedSql ((char *) argv[4]);
591 }
592 else
593 {
594 *pzErr =
595 sqlite3_mprintf
596 ("[VirtualGPKG module] CREATE VIRTUAL: illegal arg list {table_name}\n");
597 goto error;
598 }
599 /* retrieving the base table columns */
600 xdb_prefix = gaiaDoubleQuotedSql (db_prefix);
601 xname = gaiaDoubleQuotedSql (table);
602 sql =
603 sqlite3_mprintf ("PRAGMA \"%s\".table_info(\"%s\")", xdb_prefix, xname);
604 free (xname);
605 free (xdb_prefix);
606 ret = sqlite3_get_table (db, sql, &results, &n_rows, &n_columns, NULL);
607 sqlite3_free (sql);
608 if (ret != SQLITE_OK)
609 goto illegal;
610 if (n_rows >= 1)
611 {
612 p_vt = (VirtualGPKGPtr) sqlite3_malloc (sizeof (VirtualGPKG));
613 if (!p_vt)
614 return SQLITE_NOMEM;
615 p_vt->db = db;
616 p_vt->nRef = 0;
617 p_vt->zErrMsg = NULL;
618 len = strlen (db_prefix);
619 p_vt->db_prefix = sqlite3_malloc (len + 1);
620 strcpy (p_vt->db_prefix, db_prefix);
621 len = strlen (table);
622 p_vt->table = sqlite3_malloc (len + 1);
623 strcpy (p_vt->table, table);
624 p_vt->nColumns = n_rows;
625 p_vt->Column = sqlite3_malloc (sizeof (char *) * n_rows);
626 p_vt->Type = sqlite3_malloc (sizeof (char *) * n_rows);
627 p_vt->NotNull = sqlite3_malloc (sizeof (int) * n_rows);
628 p_vt->Value = sqlite3_malloc (sizeof (SqliteValuePtr) * n_rows);
629 for (i = 0; i < n_rows; i++)
630 {
631 *(p_vt->Column + i) = NULL;
632 *(p_vt->Type + i) = NULL;
633 *(p_vt->NotNull + i) = -1;
634 *(p_vt->Value + i) = value_alloc ();
635 }
636 p_vt->GeoColumn = NULL;
637 p_vt->Srid = -1;
638 p_vt->GeoType = GAIA_UNKNOWN;
639 for (i = 1; i <= n_rows; i++)
640 {
641 col_name = results[(i * n_columns) + 1];
642 col_type = results[(i * n_columns) + 2];
643 if (atoi (results[(i * n_columns) + 3]) == 0)
644 not_null = 0;
645 else
646 not_null = 1;
647 len = strlen (col_name);
648 *(p_vt->Column + (i - 1)) = sqlite3_malloc (len + 1);
649 strcpy (*(p_vt->Column + (i - 1)), col_name);
650 len = strlen (col_type);
651 *(p_vt->Type + (i - 1)) = sqlite3_malloc (len + 1);
652 strcpy (*(p_vt->Type + (i - 1)), col_type);
653 *(p_vt->NotNull + (i - 1)) = not_null;
654 }
655 sqlite3_free_table (results);
656 }
657 else
658 goto illegal;
659 /* retrieving the base table columns */
660 xdb_prefix = gaiaDoubleQuotedSql (db_prefix);
661 sql =
662 sqlite3_mprintf
663 ("SELECT column_name, geometry_type_name, srs_id, z, m\n"
664 "FROM \"%s\".gpkg_geometry_columns WHERE Upper(table_name) = Upper(%Q)",
665 xdb_prefix, table);
666 free (xdb_prefix);
667 ret = sqlite3_get_table (db, sql, &results, &n_rows, &n_columns, NULL);
668 sqlite3_free (sql);
669 if (ret != SQLITE_OK)
670 goto illegal;
671 if (n_rows >= 1)
672 {
673 col_name = results[n_columns + 0];
674 type = results[n_columns + 1];
675 p_vt->Srid = atoi (results[n_columns + 2]);
676 has_z = atoi (results[n_columns + 3]);
677 has_m = atoi (results[n_columns + 4]);
678 len = strlen (col_name);
679 p_vt->GeoColumn = sqlite3_malloc (len + 1);
680 strcpy (p_vt->GeoColumn, col_name);
681 if (strcasecmp (type, "POINT") == 0)
682 {
683 if (has_z && has_m)
684 p_vt->GeoType = GAIA_POINTZM;
685 else if (has_z)
686 p_vt->GeoType = GAIA_POINTZ;
687 else if (has_m)
688 p_vt->GeoType = GAIA_POINTM;
689 else
690 p_vt->GeoType = GAIA_POINT;
691 }
692 if (strcasecmp (type, "LINESTRING") == 0)
693 {
694 if (has_z && has_m)
695 p_vt->GeoType = GAIA_LINESTRINGZM;
696 else if (has_z)
697 p_vt->GeoType = GAIA_LINESTRINGZ;
698 else if (has_m)
699 p_vt->GeoType = GAIA_LINESTRINGM;
700 else
701 p_vt->GeoType = GAIA_LINESTRING;
702 }
703 if (strcasecmp (type, "POLYGON") == 0)
704 {
705 if (has_z && has_m)
706 p_vt->GeoType = GAIA_POLYGONZM;
707 else if (has_z)
708 p_vt->GeoType = GAIA_POLYGONZ;
709 else if (has_m)
710 p_vt->GeoType = GAIA_POLYGONM;
711 else
712 p_vt->GeoType = GAIA_POLYGON;
713 }
714 if (strcasecmp (type, "MULTIPOINT") == 0)
715 {
716 if (has_z && has_m)
717 p_vt->GeoType = GAIA_MULTIPOINTZM;
718 else if (has_z)
719 p_vt->GeoType = GAIA_MULTIPOINTZ;
720 else if (has_m)
721 p_vt->GeoType = GAIA_MULTIPOINTM;
722 else
723 p_vt->GeoType = GAIA_MULTIPOINT;
724 }
725 if (strcasecmp (type, "MULTILINESTRING") == 0)
726 {
727 if (has_z && has_m)
728 p_vt->GeoType = GAIA_MULTILINESTRINGZM;
729 else if (has_z)
730 p_vt->GeoType = GAIA_MULTILINESTRINGZ;
731 else if (has_m)
732 p_vt->GeoType = GAIA_MULTILINESTRINGM;
733 else
734 p_vt->GeoType = GAIA_MULTILINESTRING;
735 }
736 if (strcasecmp (type, "MULTIPOLYGON") == 0)
737 {
738 if (has_z && has_m)
739 p_vt->GeoType = GAIA_MULTIPOLYGONZM;
740 else if (has_z)
741 p_vt->GeoType = GAIA_MULTIPOLYGONZ;
742 else if (has_m)
743 p_vt->GeoType = GAIA_MULTIPOLYGONM;
744 else
745 p_vt->GeoType = GAIA_MULTIPOLYGON;
746 }
747 if (strcasecmp (type, "GEOMCOLLECTION") == 0)
748 {
749 if (has_z && has_m)
750 p_vt->GeoType = GAIA_GEOMETRYCOLLECTIONZM;
751 else if (has_z)
752 p_vt->GeoType = GAIA_GEOMETRYCOLLECTIONZ;
753 else if (has_m)
754 p_vt->GeoType = GAIA_GEOMETRYCOLLECTIONM;
755 else
756 p_vt->GeoType = GAIA_GEOMETRYCOLLECTION;
757 }
758 sqlite3_free_table (results);
759 }
760 else
761 goto illegal;
762 /* preparing the COLUMNs for this VIRTUAL TABLE */
763 xdb_prefix = gaiaDoubleQuotedSql (db_prefix);
764 xname = gaiaDoubleQuotedSql (vtable);
765 sql = sqlite3_mprintf ("CREATE TABLE \"%s\".\"%s\" ", xdb_prefix, xname);
766 free (xname);
767 free (xdb_prefix);
768 gaiaAppendToOutBuffer (&sql_statement, sql);
769 sqlite3_free (sql);
770 for (i = 0; i < p_vt->nColumns; i++)
771 {
772 if (i == 0)
773 strcpy (prefix, "(");
774 else
775 strcpy (prefix, ", ");
776 xname = gaiaDoubleQuotedSql (*(p_vt->Column + i));
777 if (*(p_vt->NotNull + i))
778 sql =
779 sqlite3_mprintf ("%s%s %s NOT NULL", prefix, xname,
780 *(p_vt->Type + i));
781 else
782 sql =
783 sqlite3_mprintf ("%s%s %s", prefix, xname, *(p_vt->Type + i));
784 free (xname);
785 gaiaAppendToOutBuffer (&sql_statement, sql);
786 sqlite3_free (sql);
787 }
788 gaiaAppendToOutBuffer (&sql_statement, ")");
789 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
790 {
791 if (sqlite3_declare_vtab (db, sql_statement.Buffer) != SQLITE_OK)
792 {
793 *pzErr =
794 sqlite3_mprintf
795 ("[VirtualGPKG module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
796 sql);
797 goto error;
798 }
799 gaiaOutBufferReset (&sql_statement);
800 }
801 else
802 goto error;
803 *ppVTab = (sqlite3_vtab *) p_vt;
804 free (vtable);
805 free (db_prefix);
806 free (table);
807 return SQLITE_OK;
808 illegal:
809 /* something is going the wrong way */
810 gaiaOutBufferReset (&sql_statement);
811 if (p_vt)
812 free_table (p_vt);
813 *pzErr =
814 sqlite3_mprintf
815 ("[VirtualGPKG module] '%s' isn't a valid GPKG Geometry table\n",
816 table);
817 error:
818 if (vtable)
819 free (vtable);
820 if (db_prefix)
821 free (db_prefix);
822 if (table)
823 free (table);
824 gaiaOutBufferReset (&sql_statement);
825 return SQLITE_ERROR;
826 }
827
828 static int
vgpkg_connect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)829 vgpkg_connect (sqlite3 * db, void *pAux, int argc, const char *const *argv,
830 sqlite3_vtab ** ppVTab, char **pzErr)
831 {
832 /* connects the virtual table to some shapefile - simply aliases vgpkg_create() */
833 return vgpkg_create (db, pAux, argc, argv, ppVTab, pzErr);
834 }
835
836 static int
vgpkg_best_index(sqlite3_vtab * pVTab,sqlite3_index_info * pIndex)837 vgpkg_best_index (sqlite3_vtab * pVTab, sqlite3_index_info * pIndex)
838 {
839 /* best index selection */
840 if (pVTab || pIndex)
841 pVTab = pVTab; /* unused arg warning suppression */
842 return SQLITE_OK;
843 }
844
845 static int
vgpkg_disconnect(sqlite3_vtab * pVTab)846 vgpkg_disconnect (sqlite3_vtab * pVTab)
847 {
848 /* disconnects the virtual table */
849 VirtualGPKGPtr p_vt = (VirtualGPKGPtr) pVTab;
850 free_table (p_vt);
851 return SQLITE_OK;
852 }
853
854 static int
vgpkg_destroy(sqlite3_vtab * pVTab)855 vgpkg_destroy (sqlite3_vtab * pVTab)
856 {
857 /* destroys the virtual table - simply aliases vgpkg_disconnect() */
858 return vgpkg_disconnect (pVTab);
859 }
860
861 static int
vgpkg_open(sqlite3_vtab * pVTab,sqlite3_vtab_cursor ** ppCursor)862 vgpkg_open (sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** ppCursor)
863 {
864 /* opening a new cursor */
865 sqlite3_stmt *stmt;
866 gaiaOutBuffer sql_statement;
867 int ret;
868 char *sql;
869 int ic;
870 char *xprefix;
871 char *xname;
872 VirtualGPKGCursorPtr cursor =
873 (VirtualGPKGCursorPtr) sqlite3_malloc (sizeof (VirtualGPKGCursor));
874 if (cursor == NULL)
875 return SQLITE_ERROR;
876 cursor->pVtab = (VirtualGPKGPtr) pVTab;
877 gaiaOutBufferInitialize (&sql_statement);
878 gaiaAppendToOutBuffer (&sql_statement, "SELECT ROWID");
879 for (ic = 0; ic < cursor->pVtab->nColumns; ic++)
880 {
881 value_set_null (*(cursor->pVtab->Value + ic));
882 xname = gaiaDoubleQuotedSql (*(cursor->pVtab->Column + ic));
883 if (strcasecmp
884 (*(cursor->pVtab->Column + ic), cursor->pVtab->GeoColumn) == 0)
885 {
886 /* this is the geometry column */
887 sql = sqlite3_mprintf (",GeomFromGPB(\"%s\")", xname);
888 }
889 else
890 sql = sqlite3_mprintf (",\"%s\"", xname);
891 free (xname);
892 gaiaAppendToOutBuffer (&sql_statement, sql);
893 sqlite3_free (sql);
894 }
895 xprefix = gaiaDoubleQuotedSql (cursor->pVtab->db_prefix);
896 xname = gaiaDoubleQuotedSql (cursor->pVtab->table);
897 sql = sqlite3_mprintf (" FROM \"%s\" WHERE ROWID >= ?", xname);
898 free (xname);
899 free (xprefix);
900 gaiaAppendToOutBuffer (&sql_statement, sql);
901 sqlite3_free (sql);
902 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
903 ret =
904 sqlite3_prepare_v2 (cursor->pVtab->db, sql_statement.Buffer,
905 strlen (sql_statement.Buffer), &stmt, NULL);
906 else
907 ret = SQLITE_ERROR;
908 gaiaOutBufferReset (&sql_statement);
909 if (ret != SQLITE_OK)
910 {
911 /* an error occurred */
912 cursor->eof = 1;
913 return SQLITE_ERROR;
914 }
915 cursor->stmt = stmt;
916 cursor->current_row = LONG64_MIN;
917 cursor->eof = 0;
918 *ppCursor = (sqlite3_vtab_cursor *) cursor;
919 vgpkg_read_row (cursor);
920 return SQLITE_OK;
921 }
922
923 static int
vgpkg_close(sqlite3_vtab_cursor * pCursor)924 vgpkg_close (sqlite3_vtab_cursor * pCursor)
925 {
926 /* closing the cursor */
927 int ic;
928 VirtualGPKGCursorPtr cursor = (VirtualGPKGCursorPtr) pCursor;
929 for (ic = 0; ic < cursor->pVtab->nColumns; ic++)
930 value_set_null (*(cursor->pVtab->Value + ic));
931 if (cursor->stmt)
932 sqlite3_finalize (cursor->stmt);
933 sqlite3_free (pCursor);
934 return SQLITE_OK;
935 }
936
937 static int
vgpkg_filter(sqlite3_vtab_cursor * pCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)938 vgpkg_filter (sqlite3_vtab_cursor * pCursor, int idxNum, const char *idxStr,
939 int argc, sqlite3_value ** argv)
940 {
941 /* setting up a cursor filter */
942 if (pCursor || idxNum || idxStr || argc || argv)
943 pCursor = pCursor; /* unused arg warning suppression */
944 return SQLITE_OK;
945 }
946
947 static int
vgpkg_next(sqlite3_vtab_cursor * pCursor)948 vgpkg_next (sqlite3_vtab_cursor * pCursor)
949 {
950 /* fetching next row from cursor */
951 VirtualGPKGCursorPtr cursor = (VirtualGPKGCursorPtr) pCursor;
952 (cursor->current_row)++;
953 vgpkg_read_row (cursor);
954 return SQLITE_OK;
955 }
956
957 static int
vgpkg_eof(sqlite3_vtab_cursor * pCursor)958 vgpkg_eof (sqlite3_vtab_cursor * pCursor)
959 {
960 /* cursor EOF */
961 VirtualGPKGCursorPtr cursor = (VirtualGPKGCursorPtr) pCursor;
962 return cursor->eof;
963 }
964
965 static int
vgpkg_column(sqlite3_vtab_cursor * pCursor,sqlite3_context * pContext,int column)966 vgpkg_column (sqlite3_vtab_cursor * pCursor, sqlite3_context * pContext,
967 int column)
968 {
969 /* fetching value for the Nth column */
970 VirtualGPKGCursorPtr cursor = (VirtualGPKGCursorPtr) pCursor;
971 SqliteValuePtr value;
972 if (column >= 0 && column < cursor->pVtab->nColumns)
973 {
974 value = *(cursor->pVtab->Value + column);
975 switch (value->Type)
976 {
977 case SQLITE_INTEGER:
978 sqlite3_result_int64 (pContext, value->IntValue);
979 break;
980 case SQLITE_FLOAT:
981 sqlite3_result_double (pContext, value->DoubleValue);
982 break;
983 case SQLITE_TEXT:
984 sqlite3_result_text (pContext, value->Text, value->Size,
985 SQLITE_STATIC);
986 break;
987 case SQLITE_BLOB:
988 sqlite3_result_blob (pContext, value->Blob, value->Size,
989 SQLITE_STATIC);
990 break;
991 default:
992 sqlite3_result_null (pContext);
993 break;
994 };
995 }
996 else
997 sqlite3_result_null (pContext);
998 return SQLITE_OK;
999 }
1000
1001 static int
vgpkg_rowid(sqlite3_vtab_cursor * pCursor,sqlite_int64 * pRowid)1002 vgpkg_rowid (sqlite3_vtab_cursor * pCursor, sqlite_int64 * pRowid)
1003 {
1004 /* fetching the ROWID */
1005 VirtualGPKGCursorPtr cursor = (VirtualGPKGCursorPtr) pCursor;
1006 *pRowid = cursor->current_row;
1007 return SQLITE_OK;
1008 }
1009
1010 static int
vgpkg_update(sqlite3_vtab * pVTab,int argc,sqlite3_value ** argv,sqlite_int64 * pRowid)1011 vgpkg_update (sqlite3_vtab * pVTab, int argc, sqlite3_value ** argv,
1012 sqlite_int64 * pRowid)
1013 {
1014 /* generic update [INSERT / UPDATE / DELETE */
1015 sqlite3_int64 rowid = 0;
1016 int ret;
1017 VirtualGPKGPtr p_vt = (VirtualGPKGPtr) pVTab;
1018 if (argc == 1)
1019 {
1020 /* performing a DELETE */
1021 if (sqlite3_value_type (argv[0]) == SQLITE_INTEGER)
1022 {
1023 rowid = sqlite3_value_int64 (argv[0]);
1024 ret = vgpkg_delete_row (p_vt, rowid);
1025 }
1026 else
1027 ret = SQLITE_MISMATCH;
1028 }
1029 else
1030 {
1031 if (sqlite3_value_type (argv[0]) == SQLITE_NULL)
1032 {
1033 /* performing an INSERT */
1034 ret = vgpkg_insert_row (p_vt, &rowid, argc, argv);
1035 if (ret == SQLITE_OK)
1036 *pRowid = rowid;
1037 }
1038 else
1039 {
1040 /* performing an UPDATE */
1041 rowid = sqlite3_value_int64 (argv[0]);
1042 ret = vgpkg_update_row (p_vt, rowid, argc, argv);
1043 }
1044 }
1045 return ret;
1046 }
1047
1048 static int
vgpkg_begin(sqlite3_vtab * pVTab)1049 vgpkg_begin (sqlite3_vtab * pVTab)
1050 {
1051 /* BEGIN TRANSACTION */
1052 if (pVTab)
1053 pVTab = pVTab; /* unused arg warning suppression */
1054 return SQLITE_OK;
1055 }
1056
1057 static int
vgpkg_sync(sqlite3_vtab * pVTab)1058 vgpkg_sync (sqlite3_vtab * pVTab)
1059 {
1060 /* BEGIN TRANSACTION */
1061 if (pVTab)
1062 pVTab = pVTab; /* unused arg warning suppression */
1063 return SQLITE_OK;
1064 }
1065
1066 static int
vgpkg_commit(sqlite3_vtab * pVTab)1067 vgpkg_commit (sqlite3_vtab * pVTab)
1068 {
1069 /* BEGIN TRANSACTION */
1070 if (pVTab)
1071 pVTab = pVTab; /* unused arg warning suppression */
1072 return SQLITE_OK;
1073 }
1074
1075 static int
vgpkg_rollback(sqlite3_vtab * pVTab)1076 vgpkg_rollback (sqlite3_vtab * pVTab)
1077 {
1078 /* BEGIN TRANSACTION */
1079 if (pVTab)
1080 pVTab = pVTab; /* unused arg warning suppression */
1081 return SQLITE_OK;
1082 }
1083
1084 static int
vgpkg_rename(sqlite3_vtab * pVTab,const char * zNew)1085 vgpkg_rename (sqlite3_vtab * pVTab, const char *zNew)
1086 {
1087 /* BEGIN TRANSACTION */
1088 if (pVTab)
1089 pVTab = pVTab; /* unused arg warning suppression */
1090 if (zNew)
1091 zNew = zNew; /* unused arg warning suppression */
1092 return SQLITE_ERROR;
1093 }
1094
1095 static int
spliteVirtualGPKGInit(sqlite3 * db)1096 spliteVirtualGPKGInit (sqlite3 * db)
1097 {
1098 int rc = SQLITE_OK;
1099 my_gpkg_module.iVersion = 1;
1100 my_gpkg_module.xCreate = &vgpkg_create;
1101 my_gpkg_module.xConnect = &vgpkg_connect;
1102 my_gpkg_module.xBestIndex = &vgpkg_best_index;
1103 my_gpkg_module.xDisconnect = &vgpkg_disconnect;
1104 my_gpkg_module.xDestroy = &vgpkg_destroy;
1105 my_gpkg_module.xOpen = &vgpkg_open;
1106 my_gpkg_module.xClose = &vgpkg_close;
1107 my_gpkg_module.xFilter = &vgpkg_filter;
1108 my_gpkg_module.xNext = &vgpkg_next;
1109 my_gpkg_module.xEof = &vgpkg_eof;
1110 my_gpkg_module.xColumn = &vgpkg_column;
1111 my_gpkg_module.xRowid = &vgpkg_rowid;
1112 my_gpkg_module.xUpdate = &vgpkg_update;
1113 my_gpkg_module.xBegin = &vgpkg_begin;
1114 my_gpkg_module.xSync = &vgpkg_sync;
1115 my_gpkg_module.xCommit = &vgpkg_commit;
1116 my_gpkg_module.xRollback = &vgpkg_rollback;
1117 my_gpkg_module.xFindFunction = NULL;
1118 my_gpkg_module.xRename = &vgpkg_rename;
1119 sqlite3_create_module_v2 (db, "VirtualGPKG", &my_gpkg_module, NULL, 0);
1120 return rc;
1121 }
1122
1123 SPATIALITE_PRIVATE int
virtualgpkg_extension_init(void * xdb)1124 virtualgpkg_extension_init (void *xdb)
1125 {
1126 sqlite3 *db = (sqlite3 *) xdb;
1127 return spliteVirtualGPKGInit (db);
1128 }
1129
1130 #endif /* end GEOPACKAGE conditional */
1131