1 /*
2
3 virtualdbf.c -- SQLite3 extension [VIRTUAL TABLE accessing DBF]
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) 2008-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 <sys/types.h>
47 #include <stdlib.h>
48 #include <stdio.h>
49 #include <string.h>
50
51 #if defined(_WIN32) && !defined(__MINGW32__)
52 #include "config-msvc.h"
53 #else
54 #include "config.h"
55 #endif
56
57 #include <spatialite/sqlite.h>
58 #include <spatialite/debug.h>
59
60 #include <spatialite/spatialite_ext.h>
61 #include <spatialite/gaiaaux.h>
62 #include <spatialite/gaiageo.h>
63
64 #ifdef _WIN32
65 #define strcasecmp _stricmp
66 #endif /* not WIN32 */
67
68 #ifndef OMIT_ICONV /* if ICONV is disabled no DBF support is available */
69
70 static struct sqlite3_module my_dbf_module;
71
72 typedef struct VirtualDbfStruct
73 {
74 /* extends the sqlite3_vtab struct */
75 const sqlite3_module *pModule; /* ptr to sqlite module: USED INTERNALLY BY SQLITE */
76 int nRef; /* # references: USED INTERNALLY BY SQLITE */
77 char *zErrMsg; /* error message: USE INTERNALLY BY SQLITE */
78 sqlite3 *db; /* the sqlite db holding the virtual table */
79 gaiaDbfPtr dbf; /* the DBF struct */
80 int text_dates;
81 } VirtualDbf;
82 typedef VirtualDbf *VirtualDbfPtr;
83
84 typedef struct VirtualDbfConstraintStruct
85 {
86 /* a constraint to be verified for xFilter */
87 int iColumn; /* Column on left-hand side of constraint */
88 int op; /* Constraint operator */
89 char valueType; /* value Type ('I'=int,'D'=double,'T'=text) */
90 sqlite3_int64 intValue; /* Int64 comparison value */
91 double dblValue; /* Double comparison value */
92 char *txtValue; /* Text comparison value */
93 struct VirtualDbfConstraintStruct *next;
94 } VirtualDbfConstraint;
95 typedef VirtualDbfConstraint *VirtualDbfConstraintPtr;
96
97 typedef struct VirtualDbfCursorStruct
98 {
99 /* extends the sqlite3_vtab_cursor struct */
100 VirtualDbfPtr pVtab; /* Virtual table of this cursor */
101 long current_row; /* the current row ID */
102 int eof; /* the EOF marker */
103 VirtualDbfConstraintPtr firstConstraint;
104 VirtualDbfConstraintPtr lastConstraint;
105 } VirtualDbfCursor;
106
107 typedef VirtualDbfCursor *VirtualDbfCursorPtr;
108
109 static char *
convert_dbf_colname_case(const char * buf,int colname_case)110 convert_dbf_colname_case (const char *buf, int colname_case)
111 {
112 /* converts a DBF column-name to Lower- or Upper-case */
113 int len = strlen (buf);
114 char *clean = malloc (len + 1);
115 char *p = clean;
116 strcpy (clean, buf);
117 while (*p != '\0')
118 {
119 if (colname_case == GAIA_DBF_COLNAME_LOWERCASE)
120 {
121 if (*p >= 'A' && *p <= 'Z')
122 *p = *p - 'A' + 'a';
123 }
124 if (colname_case == GAIA_DBF_COLNAME_UPPERCASE)
125 {
126 if (*p >= 'a' && *p <= 'z')
127 *p = *p - 'a' + 'A';
128 }
129 p++;
130 }
131 return clean;
132 }
133
134 static int
vdbf_create(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)135 vdbf_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
136 sqlite3_vtab ** ppVTab, char **pzErr)
137 {
138 /* creates the virtual table connected to some DBF */
139 char *sql;
140 VirtualDbfPtr p_vt;
141 char path[2048];
142 char encoding[128];
143 const char *pEncoding = NULL;
144 char ColnameCase[128];
145 const char *pColnameCase;
146 int len;
147 const char *pPath = NULL;
148 gaiaDbfFieldPtr pFld;
149 int cnt;
150 int col_cnt;
151 int seed;
152 int dup;
153 int idup;
154 int text_dates = 0;
155 int colname_case = GAIA_DBF_COLNAME_LOWERCASE;
156 char *xname;
157 char **col_name = NULL;
158 gaiaOutBuffer sql_statement;
159 if (pAux)
160 pAux = pAux; /* unused arg warning suppression */
161 /* checking for DBF PATH */
162 if (argc == 5 || argc == 6 || argc == 7)
163 {
164 pPath = argv[3];
165 len = strlen (pPath);
166 if ((*(pPath + 0) == '\'' || *(pPath + 0) == '"')
167 && (*(pPath + len - 1) == '\'' || *(pPath + len - 1) == '"'))
168 {
169 /* the path is enclosed between quotes - we need to dequote it */
170 strcpy (path, pPath + 1);
171 len = strlen (path);
172 *(path + len - 1) = '\0';
173 }
174 else
175 strcpy (path, pPath);
176 pEncoding = argv[4];
177 len = strlen (pEncoding);
178 if ((*(pEncoding + 0) == '\'' || *(pEncoding + 0) == '"')
179 && (*(pEncoding + len - 1) == '\''
180 || *(pEncoding + len - 1) == '"'))
181 {
182 /* the charset-name is enclosed between quotes - we need to dequote it */
183 strcpy (encoding, pEncoding + 1);
184 len = strlen (encoding);
185 *(encoding + len - 1) = '\0';
186 }
187 else
188 strcpy (encoding, pEncoding);
189 if (argc >= 6)
190 text_dates = atoi (argv[5]);
191 if (argc >= 7)
192 {
193 pColnameCase = argv[6];
194 len = strlen (pColnameCase);
195 if ((*(pColnameCase + 0) == '\'' || *(pColnameCase + 0) == '"')
196 && (*(pColnameCase + len - 1) == '\''
197 || *(pColnameCase + len - 1) == '"'))
198 {
199 /* the charset-name is enclosed between quotes - we need to dequote it */
200 strcpy (ColnameCase, pColnameCase + 1);
201 len = strlen (ColnameCase);
202 *(ColnameCase + len - 1) = '\0';
203 }
204 else
205 strcpy (ColnameCase, pColnameCase);
206 if (strcasecmp (ColnameCase, "uppercase") == 0
207 || strcasecmp (ColnameCase, "upper") == 0)
208 colname_case = GAIA_DBF_COLNAME_UPPERCASE;
209 else if (strcasecmp (ColnameCase, "samecase") == 0
210 || strcasecmp (ColnameCase, "same") == 0)
211 colname_case = GAIA_DBF_COLNAME_CASE_IGNORE;
212 else
213 colname_case = GAIA_DBF_COLNAME_LOWERCASE;
214 }
215 }
216 else
217 {
218 *pzErr =
219 sqlite3_mprintf
220 ("[VirtualDbf module] CREATE VIRTUAL: illegal arg list {dbf_path, encoding [ , text_dates [ , colname_case ]] }");
221 return SQLITE_ERROR;
222 }
223 p_vt = (VirtualDbfPtr) sqlite3_malloc (sizeof (VirtualDbf));
224 if (!p_vt)
225 return SQLITE_NOMEM;
226 p_vt->pModule = &my_dbf_module;
227 p_vt->nRef = 0;
228 p_vt->zErrMsg = NULL;
229 p_vt->db = db;
230 p_vt->dbf = gaiaAllocDbf ();
231 p_vt->text_dates = text_dates;
232 /* trying to open file */
233 gaiaOpenDbfRead (p_vt->dbf, path, encoding, "UTF-8");
234 if (!(p_vt->dbf->Valid))
235 {
236 /* something is going the wrong way; creating a stupid default table */
237 xname = gaiaDoubleQuotedSql ((const char *) argv[2]);
238 sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (PKUID INTEGER)", xname);
239 free (xname);
240 if (sqlite3_declare_vtab (db, sql) != SQLITE_OK)
241 {
242 sqlite3_free (sql);
243 *pzErr =
244 sqlite3_mprintf
245 ("[VirtualDbf module] cannot build a table from DBF\n");
246 return SQLITE_ERROR;
247 }
248 sqlite3_free (sql);
249 *ppVTab = (sqlite3_vtab *) p_vt;
250 return SQLITE_OK;
251 }
252 /* preparing the COLUMNs for this VIRTUAL TABLE */
253 gaiaOutBufferInitialize (&sql_statement);
254 xname = gaiaDoubleQuotedSql (argv[2]);
255 if (colname_case == GAIA_DBF_COLNAME_LOWERCASE)
256 sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (pkuid INTEGER", xname);
257 else
258 sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (PKUID INTEGER", xname);
259 free (xname);
260 gaiaAppendToOutBuffer (&sql_statement, sql);
261 sqlite3_free (sql);
262 /* checking for duplicate / illegal column names and antialising them */
263 col_cnt = 0;
264 pFld = p_vt->dbf->Dbf->First;
265 while (pFld)
266 {
267 /* counting DBF fields */
268 col_cnt++;
269 pFld = pFld->Next;
270 }
271 col_name = malloc (sizeof (char *) * col_cnt);
272 cnt = 0;
273 seed = 0;
274 pFld = p_vt->dbf->Dbf->First;
275 while (pFld)
276 {
277 char *casename = convert_dbf_colname_case (pFld->Name, colname_case);
278 xname = gaiaDoubleQuotedSql (casename);
279 free (casename);
280 dup = 0;
281 for (idup = 0; idup < cnt; idup++)
282 {
283 if (strcasecmp (xname, *(col_name + idup)) == 0)
284 dup = 1;
285 }
286 if (strcasecmp (xname, "\"PKUID\"") == 0)
287 dup = 1;
288 if (dup)
289 {
290 free (xname);
291 sql = sqlite3_mprintf ("COL_%d", seed++);
292 casename = convert_dbf_colname_case (sql, colname_case);
293 xname = gaiaDoubleQuotedSql (sql);
294 free (casename);
295 sqlite3_free (sql);
296 }
297 if (pFld->Type == 'N')
298 {
299 if (pFld->Decimals > 0 || pFld->Length > 18)
300 sql = sqlite3_mprintf (", \"%s\" DOUBLE", xname);
301 else
302 sql = sqlite3_mprintf (", \"%s\" INTEGER", xname);
303 }
304 else if (pFld->Type == 'F')
305 sql = sqlite3_mprintf (", \"%s\" DOUBLE", xname);
306 else if (pFld->Type == 'D')
307 {
308 if (text_dates)
309 sql =
310 sqlite3_mprintf (", \"%s\" VARCHAR(%d)", xname,
311 pFld->Length);
312 else
313 sql = sqlite3_mprintf (", \"%s\" DOUBLE", xname);
314 }
315 else
316 sql =
317 sqlite3_mprintf (", \"%s\" VARCHAR(%d)", xname, pFld->Length);
318 gaiaAppendToOutBuffer (&sql_statement, sql);
319 sqlite3_free (sql);
320 *(col_name + cnt) = xname;
321 cnt++;
322 pFld = pFld->Next;
323 }
324 gaiaAppendToOutBuffer (&sql_statement, ")");
325 if (col_name)
326 {
327 /* releasing memory allocation for column names */
328 for (cnt = 0; cnt < col_cnt; cnt++)
329 free (*(col_name + cnt));
330 free (col_name);
331 }
332 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
333 {
334 if (sqlite3_declare_vtab (db, sql_statement.Buffer) != SQLITE_OK)
335 {
336 *pzErr =
337 sqlite3_mprintf
338 ("[VirtualDbf module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
339 sql_statement.Buffer);
340 gaiaOutBufferReset (&sql_statement);
341 return SQLITE_ERROR;
342 }
343 }
344 gaiaOutBufferReset (&sql_statement);
345 *ppVTab = (sqlite3_vtab *) p_vt;
346 return SQLITE_OK;
347 }
348
349 static int
vdbf_connect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)350 vdbf_connect (sqlite3 * db, void *pAux, int argc, const char *const *argv,
351 sqlite3_vtab ** ppVTab, char **pzErr)
352 {
353 /* connects the virtual table to some DBF - simply aliases vdbf_create() */
354 return vdbf_create (db, pAux, argc, argv, ppVTab, pzErr);
355 }
356
357 static int
vdbf_best_index(sqlite3_vtab * pVTab,sqlite3_index_info * pIndex)358 vdbf_best_index (sqlite3_vtab * pVTab, sqlite3_index_info * pIndex)
359 {
360 /* best index selection */
361 int i;
362 int iArg = 0;
363 char str[2048];
364 char buf[64];
365
366 if (pVTab)
367 pVTab = pVTab; /* unused arg warning suppression */
368
369 *str = '\0';
370 for (i = 0; i < pIndex->nConstraint; i++)
371 {
372 if (pIndex->aConstraint[i].usable)
373 {
374 iArg++;
375 pIndex->aConstraintUsage[i].argvIndex = iArg;
376 pIndex->aConstraintUsage[i].omit = 1;
377 sprintf (buf, "%d:%d,", pIndex->aConstraint[i].iColumn,
378 pIndex->aConstraint[i].op);
379 strcat (str, buf);
380 }
381 }
382 if (*str != '\0')
383 {
384 pIndex->idxStr = sqlite3_mprintf ("%s", str);
385 pIndex->needToFreeIdxStr = 1;
386 }
387
388 return SQLITE_OK;
389 }
390
391 static int
vdbf_disconnect(sqlite3_vtab * pVTab)392 vdbf_disconnect (sqlite3_vtab * pVTab)
393 {
394 /* disconnects the virtual table */
395 VirtualDbfPtr p_vt = (VirtualDbfPtr) pVTab;
396 if (p_vt->dbf)
397 gaiaFreeDbf (p_vt->dbf);
398 sqlite3_free (p_vt);
399 return SQLITE_OK;
400 }
401
402 static int
vdbf_destroy(sqlite3_vtab * pVTab)403 vdbf_destroy (sqlite3_vtab * pVTab)
404 {
405 /* destroys the virtual table - simply aliases vdbf_disconnect() */
406 return vdbf_disconnect (pVTab);
407 }
408
409 static void
vdbf_read_row(VirtualDbfCursorPtr cursor,int * deleted_row)410 vdbf_read_row (VirtualDbfCursorPtr cursor, int *deleted_row)
411 {
412 /* trying to read a "row" from DBF */
413 int ret;
414 int deleted;
415 if (!(cursor->pVtab->dbf->Valid))
416 {
417 cursor->eof = 1;
418 return;
419 }
420 ret =
421 gaiaReadDbfEntity_ex (cursor->pVtab->dbf, cursor->current_row, &deleted,
422 cursor->pVtab->text_dates);
423 if (!ret)
424 {
425 if (!(cursor->pVtab->dbf->LastError)) /* normal DBF EOF */
426 {
427 cursor->eof = 1;
428 return;
429 }
430 /* an error occurred */
431 spatialite_e ("%s\n", cursor->pVtab->dbf->LastError);
432 cursor->eof = 1;
433 return;
434 }
435 cursor->current_row++;
436 *deleted_row = deleted;
437 }
438
439 static int
vdbf_open(sqlite3_vtab * pVTab,sqlite3_vtab_cursor ** ppCursor)440 vdbf_open (sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** ppCursor)
441 {
442 /* opening a new cursor */
443 int deleted;
444 VirtualDbfCursorPtr cursor =
445 (VirtualDbfCursorPtr) sqlite3_malloc (sizeof (VirtualDbfCursor));
446 if (cursor == NULL)
447 return SQLITE_ERROR;
448 cursor->firstConstraint = NULL;
449 cursor->lastConstraint = NULL;
450 cursor->pVtab = (VirtualDbfPtr) pVTab;
451 cursor->current_row = 0;
452 cursor->eof = 0;
453 *ppCursor = (sqlite3_vtab_cursor *) cursor;
454 while (1)
455 {
456 vdbf_read_row (cursor, &deleted);
457 if (cursor->eof)
458 break;
459 if (!deleted)
460 break;
461 }
462 return SQLITE_OK;
463 }
464
465 static void
vdbf_free_constraints(VirtualDbfCursorPtr cursor)466 vdbf_free_constraints (VirtualDbfCursorPtr cursor)
467 {
468 /* memory cleanup - cursor constraints */
469 VirtualDbfConstraintPtr pC;
470 VirtualDbfConstraintPtr pCn;
471 pC = cursor->firstConstraint;
472 while (pC)
473 {
474 pCn = pC->next;
475 if (pC->txtValue)
476 sqlite3_free (pC->txtValue);
477 sqlite3_free (pC);
478 pC = pCn;
479 }
480 cursor->firstConstraint = NULL;
481 cursor->lastConstraint = NULL;
482 }
483
484 static int
vdbf_close(sqlite3_vtab_cursor * pCursor)485 vdbf_close (sqlite3_vtab_cursor * pCursor)
486 {
487 /* closing the cursor */
488 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
489 vdbf_free_constraints (cursor);
490 sqlite3_free (pCursor);
491 return SQLITE_OK;
492 }
493
494 static int
vdbf_parse_constraint(const char * str,int index,int * iColumn,int * op)495 vdbf_parse_constraint (const char *str, int index, int *iColumn, int *op)
496 {
497 /* parsing a constraint string */
498 char buf[64];
499 const char *in = str;
500 char *out = buf;
501 int i = 0;
502 int found = 0;
503
504 *out = '\0';
505 while (*in != '\0')
506 {
507 if (*in == ',')
508 {
509 if (index == i)
510 {
511 *out = '\0';
512 found = 1;
513 break;
514 }
515 i++;
516 in++;
517 continue;
518 }
519 if (index == i)
520 *out++ = *in;
521 in++;
522 }
523 if (!found)
524 return 0;
525 in = buf;
526 for (i = 0; i < (int) strlen (buf); i++)
527 {
528 if (buf[i] == ':')
529 {
530 buf[i] = '\0';
531 *iColumn = atoi (buf);
532 *op = atoi (buf + i + 1);
533 return 1;
534 }
535 in++;
536 }
537 return 0;
538 }
539
540 static int
vdbf_eval_constraints(VirtualDbfCursorPtr cursor)541 vdbf_eval_constraints (VirtualDbfCursorPtr cursor)
542 {
543 /* evaluating Filter constraints */
544 int nCol;
545 gaiaDbfFieldPtr pFld;
546 VirtualDbfConstraintPtr pC = cursor->firstConstraint;
547 if (pC == NULL)
548 return 1;
549 while (pC)
550 {
551 int ok = 0;
552 if (pC->iColumn == 0)
553 {
554 /* the PRIMARY KEY column */
555 if (pC->op == SQLITE_INDEX_CONSTRAINT_ISNULL)
556 {
557 ok = 0;
558 goto done;
559 }
560 if (pC->op == SQLITE_INDEX_CONSTRAINT_ISNOTNULL)
561 {
562 ok = 1;
563 goto done;
564 }
565 if (pC->valueType == 'I')
566 {
567 switch (pC->op)
568 {
569 case SQLITE_INDEX_CONSTRAINT_EQ:
570 if (cursor->current_row == pC->intValue)
571 ok = 1;
572 break;
573 case SQLITE_INDEX_CONSTRAINT_GT:
574 if (cursor->current_row > pC->intValue)
575 ok = 1;
576 break;
577 case SQLITE_INDEX_CONSTRAINT_LE:
578 if (cursor->current_row <= pC->intValue)
579 ok = 1;
580 break;
581 case SQLITE_INDEX_CONSTRAINT_LT:
582 if (cursor->current_row < pC->intValue)
583 ok = 1;
584 break;
585 case SQLITE_INDEX_CONSTRAINT_GE:
586 if (cursor->current_row >= pC->intValue)
587 ok = 1;
588 break;
589 case SQLITE_INDEX_CONSTRAINT_NE:
590 if (cursor->current_row != pC->intValue)
591 ok = 1;
592 break;
593 };
594 }
595 goto done;
596 }
597 nCol = 1;
598 pFld = cursor->pVtab->dbf->Dbf->First;
599 while (pFld)
600 {
601 if (nCol == pC->iColumn)
602 {
603 if ((pFld->Value))
604 {
605 switch (pC->op)
606 {
607 case SQLITE_INDEX_CONSTRAINT_ISNULL:
608 if (pFld->Value->Type == GAIA_NULL_VALUE)
609 ok = 1;
610 break;
611 case SQLITE_INDEX_CONSTRAINT_ISNOTNULL:
612 if (pFld->Value->Type != GAIA_NULL_VALUE)
613 ok = 1;
614 break;
615 };
616 if (ok)
617 break;
618 switch (pFld->Value->Type)
619 {
620 case GAIA_INT_VALUE:
621 if (pC->valueType == 'I')
622 {
623
624 switch (pC->op)
625 {
626 case SQLITE_INDEX_CONSTRAINT_EQ:
627 if (pFld->Value->IntValue ==
628 pC->intValue)
629 ok = 1;
630 break;
631 case SQLITE_INDEX_CONSTRAINT_GT:
632 if (pFld->Value->IntValue >
633 pC->intValue)
634 ok = 1;
635 break;
636 case SQLITE_INDEX_CONSTRAINT_LE:
637 if (pFld->Value->IntValue <=
638 pC->intValue)
639 ok = 1;
640 break;
641 case SQLITE_INDEX_CONSTRAINT_LT:
642 if (pFld->Value->IntValue <
643 pC->intValue)
644 ok = 1;
645 break;
646 case SQLITE_INDEX_CONSTRAINT_GE:
647 if (pFld->Value->IntValue >=
648 pC->intValue)
649 ok = 1;
650 break;
651 case SQLITE_INDEX_CONSTRAINT_NE:
652 if (pFld->Value->IntValue !=
653 pC->intValue)
654 ok = 1;
655 break;
656 };
657 }
658 break;
659 case GAIA_DOUBLE_VALUE:
660 if (pC->valueType == 'I')
661 {
662
663 switch (pC->op)
664 {
665 case SQLITE_INDEX_CONSTRAINT_EQ:
666 if (pFld->Value->DblValue ==
667 pC->intValue)
668 ok = 1;
669 break;
670 case SQLITE_INDEX_CONSTRAINT_GT:
671 if (pFld->Value->DblValue >
672 pC->intValue)
673 ok = 1;
674 break;
675 case SQLITE_INDEX_CONSTRAINT_LE:
676 if (pFld->Value->DblValue <=
677 pC->intValue)
678 ok = 1;
679 break;
680 case SQLITE_INDEX_CONSTRAINT_LT:
681 if (pFld->Value->DblValue <
682 pC->intValue)
683 ok = 1;
684 break;
685 case SQLITE_INDEX_CONSTRAINT_GE:
686 if (pFld->Value->DblValue >=
687 pC->intValue)
688 ok = 1;
689 break;
690 case SQLITE_INDEX_CONSTRAINT_NE:
691 if (pFld->Value->DblValue !=
692 pC->intValue)
693 ok = 1;
694 break;
695 };
696 }
697 if (pC->valueType == 'D')
698 {
699
700 switch (pC->op)
701 {
702 case SQLITE_INDEX_CONSTRAINT_EQ:
703 if (pFld->Value->DblValue ==
704 pC->dblValue)
705 ok = 1;
706 break;
707 case SQLITE_INDEX_CONSTRAINT_GT:
708 if (pFld->Value->DblValue >
709 pC->dblValue)
710 ok = 1;
711 break;
712 case SQLITE_INDEX_CONSTRAINT_LE:
713 if (pFld->Value->DblValue <=
714 pC->dblValue)
715 ok = 1;
716 break;
717 case SQLITE_INDEX_CONSTRAINT_LT:
718 if (pFld->Value->DblValue <
719 pC->dblValue)
720 ok = 1;
721 break;
722 case SQLITE_INDEX_CONSTRAINT_GE:
723 if (pFld->Value->DblValue >=
724 pC->dblValue)
725 ok = 1;
726 break;
727 case SQLITE_INDEX_CONSTRAINT_NE:
728 if (pFld->Value->DblValue !=
729 pC->dblValue)
730 ok = 1;
731 break;
732 }
733 }
734 break;
735 case GAIA_TEXT_VALUE:
736 if (pC->valueType == 'T' && pC->txtValue)
737 {
738
739 int ret;
740 ret =
741 strcmp (pFld->Value->TxtValue,
742 pC->txtValue);
743 switch (pC->op)
744 {
745 case SQLITE_INDEX_CONSTRAINT_EQ:
746 if (ret == 0)
747 ok = 1;
748 break;
749 case SQLITE_INDEX_CONSTRAINT_GT:
750 if (ret > 0)
751 ok = 1;
752 break;
753 case SQLITE_INDEX_CONSTRAINT_LE:
754 if (ret <= 0)
755 ok = 1;
756 break;
757 case SQLITE_INDEX_CONSTRAINT_LT:
758 if (ret < 0)
759 ok = 1;
760 break;
761 case SQLITE_INDEX_CONSTRAINT_GE:
762 if (ret >= 0)
763 ok = 1;
764 break;
765 case SQLITE_INDEX_CONSTRAINT_NE:
766 if (ret != 0)
767 ok = 1;
768 break;
769 #ifdef HAVE_DECL_SQLITE_INDEX_CONSTRAINT_LIKE
770 case SQLITE_INDEX_CONSTRAINT_LIKE:
771 ret =
772 sqlite3_strlike (pC->txtValue,
773 pFld->
774 Value->TxtValue,
775 0);
776 if (ret == 0)
777 ok = 1;
778 break;
779 #endif
780 };
781 }
782 break;
783 };
784 }
785 goto done;
786 }
787 nCol++;
788 pFld = pFld->Next;
789 }
790 done:
791 if (!ok)
792 return 0;
793 pC = pC->next;
794 }
795 return 1;
796 }
797
798 static int
vdbf_filter(sqlite3_vtab_cursor * pCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)799 vdbf_filter (sqlite3_vtab_cursor * pCursor, int idxNum, const char *idxStr,
800 int argc, sqlite3_value ** argv)
801 {
802 /* setting up a cursor filter */
803 int i;
804 int iColumn;
805 int op;
806 int len;
807 int deleted;
808 VirtualDbfConstraintPtr pC;
809 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
810 if (idxNum)
811 idxNum = idxNum; /* unused arg warning suppression */
812
813 /* resetting any previously set filter constraint */
814 vdbf_free_constraints (cursor);
815
816 for (i = 0; i < argc; i++)
817 {
818 if (!vdbf_parse_constraint (idxStr, i, &iColumn, &op))
819 continue;
820 pC = sqlite3_malloc (sizeof (VirtualDbfConstraint));
821 if (!pC)
822 continue;
823 pC->iColumn = iColumn;
824 pC->op = op;
825 pC->valueType = '\0';
826 pC->txtValue = NULL;
827 pC->next = NULL;
828
829 if (sqlite3_value_type (argv[i]) == SQLITE_INTEGER)
830 {
831 pC->valueType = 'I';
832 pC->intValue = sqlite3_value_int64 (argv[i]);
833 }
834 if (sqlite3_value_type (argv[i]) == SQLITE_FLOAT)
835 {
836 pC->valueType = 'D';
837 pC->dblValue = sqlite3_value_double (argv[i]);
838 }
839 if (sqlite3_value_type (argv[i]) == SQLITE_TEXT)
840 {
841 pC->valueType = 'T';
842 len = sqlite3_value_bytes (argv[i]) + 1;
843 pC->txtValue = (char *) sqlite3_malloc (len);
844 if (pC->txtValue)
845 strcpy (pC->txtValue,
846 (char *) sqlite3_value_text (argv[i]));
847 }
848 if (cursor->firstConstraint == NULL)
849 cursor->firstConstraint = pC;
850 if (cursor->lastConstraint != NULL)
851 cursor->lastConstraint->next = pC;
852 cursor->lastConstraint = pC;
853 }
854
855 cursor->current_row = 0;
856 cursor->eof = 0;
857 while (1)
858 {
859 vdbf_read_row (cursor, &deleted);
860 if (cursor->eof)
861 break;
862 if (deleted)
863 continue;
864 if (vdbf_eval_constraints (cursor))
865 break;
866 }
867 return SQLITE_OK;
868 }
869
870 static int
vdbf_next(sqlite3_vtab_cursor * pCursor)871 vdbf_next (sqlite3_vtab_cursor * pCursor)
872 {
873 /* fetching a next row from cursor */
874 int deleted;
875 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
876 while (1)
877 {
878 vdbf_read_row (cursor, &deleted);
879 if (cursor->eof)
880 break;
881 if (deleted)
882 continue;
883 if (vdbf_eval_constraints (cursor))
884 break;
885 }
886 return SQLITE_OK;
887 }
888
889 static int
vdbf_eof(sqlite3_vtab_cursor * pCursor)890 vdbf_eof (sqlite3_vtab_cursor * pCursor)
891 {
892 /* cursor EOF */
893 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
894 return cursor->eof;
895 }
896
897 static int
vdbf_column(sqlite3_vtab_cursor * pCursor,sqlite3_context * pContext,int column)898 vdbf_column (sqlite3_vtab_cursor * pCursor, sqlite3_context * pContext,
899 int column)
900 {
901 /* fetching value for the Nth column */
902 int nCol = 1;
903 gaiaDbfFieldPtr pFld;
904 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
905 if (column == 0)
906 {
907 /* the PRIMARY KEY column */
908 sqlite3_result_int (pContext, cursor->current_row);
909 return SQLITE_OK;
910 }
911 pFld = cursor->pVtab->dbf->Dbf->First;
912 while (pFld)
913 {
914 /* column values */
915 if (nCol == column)
916 {
917 if (!(pFld->Value))
918 sqlite3_result_null (pContext);
919 else
920 {
921 switch (pFld->Value->Type)
922 {
923 case GAIA_INT_VALUE:
924 sqlite3_result_int64 (pContext,
925 pFld->Value->IntValue);
926 break;
927 case GAIA_DOUBLE_VALUE:
928 sqlite3_result_double (pContext,
929 pFld->Value->DblValue);
930 break;
931 case GAIA_TEXT_VALUE:
932 sqlite3_result_text (pContext,
933 pFld->Value->TxtValue,
934 strlen (pFld->Value->TxtValue),
935 SQLITE_STATIC);
936 break;
937 default:
938 sqlite3_result_null (pContext);
939 break;
940 }
941 }
942 break;
943 }
944 nCol++;
945 pFld = pFld->Next;
946 }
947 return SQLITE_OK;
948 }
949
950 static int
vdbf_rowid(sqlite3_vtab_cursor * pCursor,sqlite_int64 * pRowid)951 vdbf_rowid (sqlite3_vtab_cursor * pCursor, sqlite_int64 * pRowid)
952 {
953 /* fetching the ROWID */
954 VirtualDbfCursorPtr cursor = (VirtualDbfCursorPtr) pCursor;
955 *pRowid = cursor->current_row;
956 return SQLITE_OK;
957 }
958
959 static int
vdbf_update(sqlite3_vtab * pVTab,int argc,sqlite3_value ** argv,sqlite_int64 * pRowid)960 vdbf_update (sqlite3_vtab * pVTab, int argc, sqlite3_value ** argv,
961 sqlite_int64 * pRowid)
962 {
963 /* generic update [INSERT / UPDATE / DELETE */
964 if (pVTab || argc || argv || pRowid)
965 pVTab = pVTab; /* unused arg warning suppression */
966 return SQLITE_READONLY;
967 }
968
969 static int
vdbf_begin(sqlite3_vtab * pVTab)970 vdbf_begin (sqlite3_vtab * pVTab)
971 {
972 /* BEGIN TRANSACTION */
973 if (pVTab)
974 pVTab = pVTab; /* unused arg warning suppression */
975 return SQLITE_OK;
976 }
977
978 static int
vdbf_sync(sqlite3_vtab * pVTab)979 vdbf_sync (sqlite3_vtab * pVTab)
980 {
981 /* BEGIN TRANSACTION */
982 if (pVTab)
983 pVTab = pVTab; /* unused arg warning suppression */
984 return SQLITE_OK;
985 }
986
987 static int
vdbf_commit(sqlite3_vtab * pVTab)988 vdbf_commit (sqlite3_vtab * pVTab)
989 {
990 /* BEGIN TRANSACTION */
991 if (pVTab)
992 pVTab = pVTab; /* unused arg warning suppression */
993 return SQLITE_OK;
994 }
995
996 static int
vdbf_rollback(sqlite3_vtab * pVTab)997 vdbf_rollback (sqlite3_vtab * pVTab)
998 {
999 /* BEGIN TRANSACTION */
1000 if (pVTab)
1001 pVTab = pVTab; /* unused arg warning suppression */
1002 return SQLITE_OK;
1003 }
1004
1005 static int
vdbf_rename(sqlite3_vtab * pVTab,const char * zNew)1006 vdbf_rename (sqlite3_vtab * pVTab, const char *zNew)
1007 {
1008 /* BEGIN TRANSACTION */
1009 if (pVTab)
1010 pVTab = pVTab; /* unused arg warning suppression */
1011 if (zNew)
1012 zNew = zNew; /* unused arg warning suppression */
1013 return SQLITE_ERROR;
1014 }
1015
1016 static int
spliteVirtualDbfInit(sqlite3 * db)1017 spliteVirtualDbfInit (sqlite3 * db)
1018 {
1019 int rc = SQLITE_OK;
1020 my_dbf_module.iVersion = 1;
1021 my_dbf_module.xCreate = &vdbf_create;
1022 my_dbf_module.xConnect = &vdbf_connect;
1023 my_dbf_module.xBestIndex = &vdbf_best_index;
1024 my_dbf_module.xDisconnect = &vdbf_disconnect;
1025 my_dbf_module.xDestroy = &vdbf_destroy;
1026 my_dbf_module.xOpen = &vdbf_open;
1027 my_dbf_module.xClose = &vdbf_close;
1028 my_dbf_module.xFilter = &vdbf_filter;
1029 my_dbf_module.xNext = &vdbf_next;
1030 my_dbf_module.xEof = &vdbf_eof;
1031 my_dbf_module.xColumn = &vdbf_column;
1032 my_dbf_module.xRowid = &vdbf_rowid;
1033 my_dbf_module.xUpdate = &vdbf_update;
1034 my_dbf_module.xBegin = &vdbf_begin;
1035 my_dbf_module.xSync = &vdbf_sync;
1036 my_dbf_module.xCommit = &vdbf_commit;
1037 my_dbf_module.xRollback = &vdbf_rollback;
1038 my_dbf_module.xFindFunction = NULL;
1039 my_dbf_module.xRename = &vdbf_rename;
1040 sqlite3_create_module_v2 (db, "VirtualDbf", &my_dbf_module, NULL, 0);
1041 return rc;
1042 }
1043
1044 SPATIALITE_PRIVATE int
virtualdbf_extension_init(void * xdb)1045 virtualdbf_extension_init (void *xdb)
1046 {
1047 sqlite3 *db = (sqlite3 *) xdb;
1048 return spliteVirtualDbfInit (db);
1049 }
1050
1051 #endif /* ICONV enabled/disabled */
1052