1 /*
2
3 virtualpg.c -- SQLite3 extension [VIRTUAL TABLE accessing PostgreSQL tables]
4
5 version 2.0.0-RC0, 2018 July 14
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) 2013-2018
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 #ifndef LOADABLE_EXTENSION
51 #include <sqlite3.h>
52 #else
53 #include <sqlite3ext.h>
54 #endif
55
56 #include "virtualpg.h"
57 #include "config.h"
58
59 #ifndef LOADABLE_EXTENSION
60 struct sqlite3_module my_pg_module;
61 #else
62 SQLITE_EXTENSION_INIT1 struct sqlite3_module my_pg_module;
63 #endif
64
65 #define SQLITEX_DATE 10001
66 #define SQLITEX_TIME 10002
67 #define SQLITEX_DATETIME 10003
68 #define SQLITEX_BOOL 10004
69
70 typedef struct VirtualPgStruct
71 {
72 /* extends the sqlite3_vtab struct */
73 const sqlite3_module *pModule; /* ptr to sqlite module: USED INTERNALLY BY SQLITE */
74 int nRef; /* # references: USED INTERNALLY BY SQLITE */
75 char *zErrMsg; /* error message: USE INTERNALLY BY SQLITE */
76 sqlite3 *db; /* the sqlite db holding the virtual table */
77 char *ConnInfo; /* PQ ConnInfo string */
78 PGconn *pg_conn; /* Postgres connection handle */
79 char *pg_schema; /* the Postgres schema name */
80 char *pg_table; /* the Postgres table name */
81 int isView; /* is a Postgres View or not */
82 int nColumns; /* number of columns into the table */
83 char **Column; /* the name for each column */
84 char **Type; /* the Postgres datatype for each column */
85 int *Mapping; /* the SQlite datatype for each column */
86 int *MaxSize; /* the max size (in bytes) for each column */
87 int *NotNull; /* NotNull clause for each column */
88 char *IsPK; /* Y/N for each column */
89 int newRowid;
90 char **PKstrings; /* array of PK string values */
91 int *PKidx; /* array of PK column indices */
92 int PKrows; /* number of rows in the PK array */
93 int PKcols; /* number of columns in the PK array */
94 int readOnly; /* PostgreSQL readOnly mode */
95 int julianNumbers; /* DATE/TIME as Julian numbers */
96 int pendingTransaction; /* True/False: if there is PostgreSQL pending transaction */
97 } VirtualPg;
98 typedef VirtualPg *VirtualPgPtr;
99
100 typedef struct vpgSqliteValueStruct
101 {
102 /* a multitype storing a column value */
103 int Type; /* current data-type */
104 sqlite3_int64 IntValue; /* INT value */
105 double DoubleValue; /* DOUBLE value */
106 char *Text; /* TEXT value */
107 unsigned char *Blob; /* BLOB value */
108 int Size; /* size (in bytes) for TEXT or BLOB */
109 } vpgSqliteValue;
110 typedef vpgSqliteValue *vpgSqliteValuePtr;
111
112 typedef struct VirtualPgCursorStruct
113 {
114 /* extends the sqlite3_vtab_cursor struct */
115 VirtualPgPtr pVtab; /* Virtual table of this cursor */
116 PGresult *resultSet; /* Postgres result set Object */
117 int nRows; /* num rows into the result set */
118 int nFields; /* num cols into the result set */
119 int currentRow; /* the current row index */
120 int nColumns; /* number of columns */
121 vpgSqliteValuePtr *Values; /* the current-row value for each column */
122 int eof; /* the EOF marker */
123 } VirtualPgCursor;
124 typedef VirtualPgCursor *VirtualPgCursorPtr;
125
126 typedef struct vpgMemBufferStruct
127 {
128 /* a struct handling a dynamically growing output buffer */
129 char *Buffer;
130 size_t WriteOffset;
131 size_t BufferSize;
132 int Error;
133 } vpgMemBuffer;
134 typedef vpgMemBuffer *vpgMemBufferPtr;
135
136
137
138
139 /*************************************************************
140 /
141 / virtualized libPQ methods
142 /
143 **************************************************************/
144 static virtualPQ pq;
145
146 extern void
vpgPQclear(PGresult * res)147 vpgPQclear (PGresult * res)
148 {
149 /* PQclear */
150 pq.PQclear (res);
151 }
152
153 extern PGconn *
vpgPQconnectdb(const char * conninfo)154 vpgPQconnectdb (const char *conninfo)
155 {
156 /* PQconnectdb */
157 return pq.PQconnectdb (conninfo);
158 }
159
160 extern char *
vpgPQerrorMessage(const PGconn * conn)161 vpgPQerrorMessage (const PGconn * conn)
162 {
163 /* PQerrorMessage */
164 return pq.PQerrorMessage (conn);
165 }
166
167 extern PGresult *
vpgPQexec(PGconn * conn,const char * command)168 vpgPQexec (PGconn * conn, const char *command)
169 {
170 /* PQexec */
171 return pq.PQexec (conn, command);
172 }
173
174 extern void
vpgPQfinish(PGconn * conn)175 vpgPQfinish (PGconn * conn)
176 {
177 /* PQfinish */
178 pq.PQfinish (conn);
179 }
180
181 extern int
vpgPQgetisnull(const PGresult * res,int row_number,int column_number)182 vpgPQgetisnull (const PGresult * res, int row_number, int column_number)
183 {
184 /* PQgetisnull */
185 return pq.PQgetisnull (res, row_number, column_number);
186 }
187
188 extern char *
vpgPQgetvalue(const PGresult * res,int row_number,int column_number)189 vpgPQgetvalue (const PGresult * res, int row_number, int column_number)
190 {
191 /* PQgetvalue */
192 return pq.PQgetvalue (res, row_number, column_number);
193 }
194
195 extern int
vpgPQlibVersion(void)196 vpgPQlibVersion (void)
197 {
198 /* PQlibVersion */
199 return pq.PQlibVersion ();
200 }
201
202 extern int
vpgPQnfields(const PGresult * res)203 vpgPQnfields (const PGresult * res)
204 {
205 /* PQnfields */
206 return pq.PQnfields (res);
207 }
208
209 extern int
vpgPQntuples(const PGresult * res)210 vpgPQntuples (const PGresult * res)
211 {
212 /* PQntuples */
213 return pq.PQntuples (res);
214 }
215
216 extern ExecStatusType
vpgPQresultStatus(const PGresult * res)217 vpgPQresultStatus (const PGresult * res)
218 {
219 /* PQresultStatus */
220 return pq.PQresultStatus (res);
221 }
222
223 extern ConnStatusType
vpgPQstatus(const PGconn * conn)224 vpgPQstatus (const PGconn * conn)
225 {
226 /* PQstatus */
227 return pq.PQstatus (conn);
228 }
229
230
231
232
233 /*************************************************************
234 /
235 / VirtualPG internal methods
236 /
237 **************************************************************/
238 static void
vpgResetError(sqlite3 * db)239 vpgResetError (sqlite3 * db)
240 {
241 /* attempting to reset the PostgreSQL error */
242 const char *sql = "SELECT PostgreSql_ResetLastError()";
243 sqlite3_exec (db, sql, NULL, 0, NULL);
244 }
245
246 static void
vpgReportError(sqlite3 * db,const char * emsg)247 vpgReportError (sqlite3 * db, const char *emsg)
248 {
249 /* attempting to report some PostgreSQL error */
250 char *sql =
251 sqlite3_mprintf
252 ("SELECT PostgreSql_SetLastError('VirtualPostgres: %q')", emsg);
253 sqlite3_exec (db, sql, NULL, 0, NULL);
254 sqlite3_free (sql);
255 }
256
257 static void
vpgMemBufferInitialize(vpgMemBufferPtr buf)258 vpgMemBufferInitialize (vpgMemBufferPtr buf)
259 {
260 /* initializing a dynamically growing output buffer */
261 buf->Buffer = NULL;
262 buf->WriteOffset = 0;
263 buf->BufferSize = 0;
264 buf->Error = 0;
265 }
266
267 static void
vpgMemBufferReset(vpgMemBufferPtr buf)268 vpgMemBufferReset (vpgMemBufferPtr buf)
269 {
270 /* cleaning a dynamically growing output buffer */
271 if (buf->Buffer)
272 free (buf->Buffer);
273 buf->Buffer = NULL;
274 buf->WriteOffset = 0;
275 buf->BufferSize = 0;
276 buf->Error = 0;
277 }
278
279 static void
vpgMemBufferAppend(vpgMemBufferPtr buf,const char * payload)280 vpgMemBufferAppend (vpgMemBufferPtr buf, const char *payload)
281 {
282 /* appending into the buffer */
283 size_t size = strlen (payload);
284 size_t free_size = buf->BufferSize - buf->WriteOffset;
285 if (size > free_size)
286 {
287 /* we must allocate a bigger buffer */
288 size_t new_size;
289 char *new_buf;
290 if (buf->BufferSize == 0)
291 new_size = size + 1024;
292 else if (buf->BufferSize <= 4196)
293 new_size = buf->BufferSize + size + 4196;
294 else if (buf->BufferSize <= 65536)
295 new_size = buf->BufferSize + size + 65536;
296 else
297 new_size = buf->BufferSize + size + (1024 * 1024);
298 new_buf = malloc (new_size);
299 if (!new_buf)
300 {
301 buf->Error = 1;
302 return;
303 }
304 if (buf->Buffer)
305 {
306 strcpy (new_buf, buf->Buffer);
307 free (buf->Buffer);
308 }
309 buf->Buffer = new_buf;
310 buf->BufferSize = new_size;
311 }
312 strcpy (buf->Buffer + buf->WriteOffset, payload);
313 buf->WriteOffset += size;
314 }
315
316 static vpgSqliteValuePtr
vpgAllocValue(void)317 vpgAllocValue (void)
318 {
319 /* allocates and initialites a Value multitype */
320 vpgSqliteValuePtr p = malloc (sizeof (vpgSqliteValue));
321 p->Type = SQLITE_NULL;
322 p->Text = NULL;
323 p->Blob = NULL;
324 return p;
325 }
326
327 static void
vpgFreeValue(vpgSqliteValuePtr p)328 vpgFreeValue (vpgSqliteValuePtr p)
329 {
330 /* freeing a Value multitype */
331 if (!p)
332 return;
333 if (p->Text)
334 free (p->Text);
335 if (p->Blob)
336 free (p->Blob);
337 free (p);
338 }
339
340 static void
vpgFreePKstrings(VirtualPgPtr p_vt)341 vpgFreePKstrings (VirtualPgPtr p_vt)
342 {
343 /* freeing the PK string values */
344 if (p_vt->PKidx != NULL)
345 free (p_vt->PKidx);
346 p_vt->PKidx = NULL;
347 if (p_vt->PKstrings != NULL)
348 {
349 int nPKs = p_vt->PKrows * p_vt->PKcols;
350 int c;
351 for (c = 0; c < nPKs; c++)
352 {
353 char *p = *(p_vt->PKstrings + c);
354 if (p != NULL)
355 free (p);
356 }
357 free (p_vt->PKstrings);
358 }
359 p_vt->PKstrings = NULL;
360 p_vt->PKrows = 0;
361 p_vt->PKcols = 0;
362 }
363
364 static int
vpgCountPKcols(VirtualPgPtr p_vt)365 vpgCountPKcols (VirtualPgPtr p_vt)
366 {
367 /* counting how many PK columns */
368 int c;
369 int n = 0;
370 for (c = 0; c < p_vt->nColumns; c++)
371 {
372 if (*(p_vt->IsPK + c) == 'Y')
373 n++;
374 }
375 return n;
376 }
377
378 static char *
vpgDoubleQuoted(const char * value)379 vpgDoubleQuoted (const char *value)
380 {
381 /*
382 / returns a well formatted TEXT value for SQL
383 / 1] strips trailing spaces
384 / 2] masks any QUOTE inside the string, appending another QUOTE
385 */
386 const char *p_in;
387 const char *p_end;
388 char qt = '"';
389 char *out;
390 char *p_out;
391 int len = 0;
392 int i;
393
394 if (!value)
395 return NULL;
396
397 p_end = value;
398 for (i = (strlen (value) - 1); i >= 0; i--)
399 {
400 /* stripping trailing spaces */
401 p_end = value + i;
402 if (value[i] != ' ')
403 break;
404 }
405
406 p_in = value;
407 while (p_in <= p_end)
408 {
409 /* computing the output length */
410 len++;
411 if (*p_in == qt)
412 len++;
413 p_in++;
414 }
415 if (len == 1 && *value == ' ')
416 {
417 /* empty string */
418 len = 0;
419 }
420
421 out = malloc (len + 3);
422 if (!out)
423 return NULL;
424
425 p_out = out;
426 p_in = value;
427 *p_out++ = '"';
428 if (len == 0)
429 {
430 /* empty string */
431 *p_out++ = '"';
432 *p_out = '\0';
433 return out;
434 }
435 while (p_in <= p_end)
436 {
437 /* creating the output string */
438 if (*p_in == qt)
439 *p_out++ = qt;
440 *p_out++ = *p_in++;
441 }
442 *p_out++ = '"';
443 *p_out = '\0';
444 return out;
445 }
446
447 static char *
vpgBuildPkWhere(VirtualPgPtr p_vt,int nRow)448 vpgBuildPkWhere (VirtualPgPtr p_vt, int nRow)
449 {
450 /* attempting to build a WHERE clause (PK columns) */
451 int idx;
452 int c;
453 char *where;
454 char *prev;
455 int col_idx;
456 const char *col_name;
457 char *xcol_name;
458 const char *value;
459 int type;
460 int quoted;
461 if (nRow < 0 || nRow >= p_vt->PKrows)
462 return NULL;
463
464 where = sqlite3_mprintf (" WHERE");
465 idx = nRow * p_vt->PKcols;
466 for (c = 0; c < p_vt->PKcols; c++)
467 {
468 prev = where;
469 col_idx = *(p_vt->PKidx + c);
470 col_name = *(p_vt->Column + col_idx);
471 xcol_name = vpgDoubleQuoted (col_name);
472 type = *(p_vt->Mapping + col_idx);
473 value = *(p_vt->PKstrings + idx + c);
474 switch (type)
475 {
476 case SQLITE_TEXT:
477 case SQLITEX_DATE:
478 case SQLITEX_TIME:
479 case SQLITEX_DATETIME:
480 case SQLITEX_BOOL:
481 quoted = 1;
482 break;
483 default:
484 quoted = 0;
485 break;
486 };
487 if (value == NULL)
488 {
489 if (c == 0)
490 where = sqlite3_mprintf ("%s %s IS NULL", prev, xcol_name);
491 else
492 where =
493 sqlite3_mprintf ("%s AND %s IS NULL", prev, xcol_name);
494 }
495 else if (quoted)
496 {
497 if (c == 0)
498 where =
499 sqlite3_mprintf ("%s %s = %Q", prev, xcol_name, value);
500 else
501 where =
502 sqlite3_mprintf ("%s AND %s = %Q", prev, xcol_name,
503 value);
504 }
505 else
506 {
507 if (c == 0)
508 where =
509 sqlite3_mprintf ("%s %s = %s", prev, xcol_name, value);
510 else
511 where =
512 sqlite3_mprintf ("%s AND %s = %s", prev, xcol_name,
513 value);
514 }
515 free (xcol_name);
516 sqlite3_free (prev);
517 }
518 return where;
519 }
520
521 static char *
vpgBuildPkOrderBy(VirtualPgPtr p_vt)522 vpgBuildPkOrderBy (VirtualPgPtr p_vt)
523 {
524 /* attempting to build an ORDER BY clause (PK columns) */
525 int c;
526 char *orderBy;
527 char *prev;
528 const char *col_name;
529 char *xcol_name;
530 int first = 1;
531
532 orderBy = sqlite3_mprintf (" ORDER BY");
533 for (c = 0; c < p_vt->nColumns; c++)
534 {
535 if (p_vt->IsPK[c] == 'Y')
536 {
537 prev = orderBy;
538 col_name = *(p_vt->Column + c);
539 xcol_name = vpgDoubleQuoted (col_name);
540 if (first)
541 {
542 first = 0;
543 orderBy = sqlite3_mprintf ("%s %s", prev, xcol_name);
544 }
545 else
546 orderBy = sqlite3_mprintf ("%s, %s", prev, xcol_name);
547 free (xcol_name);
548 sqlite3_free (prev);
549 }
550 }
551 return orderBy;
552 }
553
554 static void
vpgSetNullValue(vpgSqliteValuePtr p)555 vpgSetNullValue (vpgSqliteValuePtr p)
556 {
557 /* setting a NULL value to the multitype */
558 if (!p)
559 return;
560 p->Type = SQLITE_NULL;
561 if (p->Text)
562 free (p->Text);
563 if (p->Blob)
564 free (p->Blob);
565 p->Text = NULL;
566 p->Blob = NULL;
567 }
568
569 static void
vpgSetIntValue(vpgSqliteValuePtr p,const char * v)570 vpgSetIntValue (vpgSqliteValuePtr p, const char *v)
571 {
572 /* setting an INT value to the multitype */
573 sqlite3_int64 value = atoll (v);
574 if (!p)
575 return;
576 p->Type = SQLITE_INTEGER;
577 if (p->Text)
578 free (p->Text);
579 if (p->Blob)
580 free (p->Blob);
581 p->Text = NULL;
582 p->Blob = NULL;
583 p->IntValue = value;
584 }
585
586 static void
vpgSetDoubleValue(vpgSqliteValuePtr p,const char * v)587 vpgSetDoubleValue (vpgSqliteValuePtr p, const char *v)
588 {
589 /* setting a DOUBLE value to the multitype */
590 double value = atof (v);
591 if (!p)
592 return;
593 p->Type = SQLITE_FLOAT;
594 if (p->Text)
595 free (p->Text);
596 if (p->Blob)
597 free (p->Blob);
598 p->Text = NULL;
599 p->Blob = NULL;
600 p->DoubleValue = value;
601 }
602
603 static void
vpgSetDateValue(vpgSqliteValuePtr p,double v)604 vpgSetDateValue (vpgSqliteValuePtr p, double v)
605 {
606 /* setting a DOUBLE value to the multitype [DATE/TIME] */
607 if (!p)
608 return;
609 p->Type = SQLITE_FLOAT;
610 if (p->Text)
611 free (p->Text);
612 if (p->Blob)
613 free (p->Blob);
614 p->Text = NULL;
615 p->Blob = NULL;
616 p->DoubleValue = v;
617 }
618
619 static void
vpgSetBoolValue(vpgSqliteValuePtr p,const char * v)620 vpgSetBoolValue (vpgSqliteValuePtr p, const char *v)
621 {
622 /* setting a BOOL value to the multitype */
623 if (!p)
624 return;
625 p->Type = SQLITE_INTEGER;
626 if (p->Text)
627 free (p->Text);
628 if (p->Blob)
629 free (p->Blob);
630 p->Text = NULL;
631 p->Blob = NULL;
632 p->IntValue = 1;
633 if (strcmp (v, "f") == 0)
634 p->IntValue = 0;
635 }
636
637 static void
vpgSetTextValue(vpgSqliteValuePtr p,const char * value)638 vpgSetTextValue (vpgSqliteValuePtr p, const char *value)
639 {
640 /* setting a TEXT value to the multitype */
641 int size = strlen (value);
642 if (!p)
643 return;
644 p->Type = SQLITE_TEXT;
645 if (p->Text)
646 free (p->Text);
647 if (p->Blob)
648 free (p->Blob);
649 p->Blob = NULL;
650 p->Text = malloc (size);
651 memcpy (p->Text, value, size);
652 p->Size = size;
653 }
654
655 static double
vpgMakeJulianDay(sqlite3 * db,const char * value)656 vpgMakeJulianDay (sqlite3 * db, const char *value)
657 {
658 /* computing a Julian Day */
659 int ret;
660 sqlite3_stmt *stmt;
661 double julian;
662 const char *sql = "SELECT JulianDay(?)";
663 ret = sqlite3_prepare_v2 (db, sql, strlen (sql), &stmt, NULL);
664 if (ret != SQLITE_OK)
665 {
666 char *emsg = sqlite3_mprintf ("SQLite error (JulianDay): %s\n",
667 sqlite3_errmsg (db));
668 vpgReportError (db, emsg);
669 sqlite3_free (emsg);
670 return 0.0;
671 }
672 sqlite3_reset (stmt);
673 sqlite3_clear_bindings (stmt);
674 sqlite3_bind_text (stmt, 1, value, strlen (value), SQLITE_STATIC);
675 ret = sqlite3_step (stmt);
676 if (ret == SQLITE_ROW)
677 julian = sqlite3_column_double (stmt, 0);
678 else
679 {
680 char *emsg = sqlite3_mprintf ("SQLite error (JulianDay): %s\n",
681 sqlite3_errmsg (db));
682 sqlite3_finalize (stmt);
683 vpgReportError (db, emsg);
684 sqlite3_free (emsg);
685 return 0.0;
686 }
687 sqlite3_finalize (stmt);
688 return julian;
689 }
690
691 static char *
vpgMakeDate(sqlite3 * db,double value)692 vpgMakeDate (sqlite3 * db, double value)
693 {
694 /* tranforming a Julian Day into a DATE */
695 int ret;
696 sqlite3_stmt *stmt;
697 const char *str;
698 char *dt;
699 const char *sql = "SELECT Date(?)";
700 ret = sqlite3_prepare_v2 (db, sql, strlen (sql), &stmt, NULL);
701 if (ret != SQLITE_OK)
702 {
703 char *emsg = sqlite3_mprintf ("SQLite error (Date): %s\n",
704 sqlite3_errmsg (db));
705 vpgReportError (db, emsg);
706 sqlite3_free (emsg);
707 return sqlite3_mprintf ("%s", "1900-01-01");
708 }
709 sqlite3_reset (stmt);
710 sqlite3_clear_bindings (stmt);
711 sqlite3_bind_double (stmt, 1, value);
712 ret = sqlite3_step (stmt);
713 if (ret == SQLITE_ROW)
714 {
715 str = (const char *) sqlite3_column_text (stmt, 0);
716 dt = sqlite3_mprintf ("%s", str);
717 }
718 else
719 {
720 char *emsg = sqlite3_mprintf ("SQLite error (Date): %s\n",
721 sqlite3_errmsg (db));
722 vpgReportError (db, emsg);
723 sqlite3_free (emsg);
724 sqlite3_finalize (stmt);
725 return sqlite3_mprintf ("%s", "1900-01-01");
726 }
727 sqlite3_finalize (stmt);
728 return dt;
729 }
730
731 static char *
vpgMakeTime(sqlite3 * db,double value)732 vpgMakeTime (sqlite3 * db, double value)
733 {
734 /* tranforming a Julian Day into a TIME */
735 int ret;
736 sqlite3_stmt *stmt;
737 const char *str;
738 char *dt;
739 const char *sql = "SELECT Time(?)";
740 ret = sqlite3_prepare_v2 (db, sql, strlen (sql), &stmt, NULL);
741 if (ret != SQLITE_OK)
742 {
743 char *emsg = sqlite3_mprintf ("SQLite error (Time): %s\n",
744 sqlite3_errmsg (db));
745 vpgReportError (db, emsg);
746 sqlite3_free (emsg);
747 return sqlite3_mprintf ("%s", "12:00:00.000");
748 }
749 sqlite3_reset (stmt);
750 sqlite3_clear_bindings (stmt);
751 sqlite3_bind_double (stmt, 1, value);
752 ret = sqlite3_step (stmt);
753 if (ret == SQLITE_ROW)
754 {
755 str = (const char *) sqlite3_column_text (stmt, 0);
756 dt = sqlite3_mprintf ("%s", str);
757 }
758 else
759 {
760 char *emsg = sqlite3_mprintf ("SQLite error (Time): %s\n",
761 sqlite3_errmsg (db));
762 vpgReportError (db, emsg);
763 sqlite3_free (emsg);
764 sqlite3_finalize (stmt);
765 return sqlite3_mprintf ("%s", "12:00:00.000");
766 }
767 sqlite3_finalize (stmt);
768 return dt;
769 }
770
771 static char *
vpgMakeDatetime(sqlite3 * db,double value)772 vpgMakeDatetime (sqlite3 * db, double value)
773 {
774 /* tranforming a Julian Day into a TIMESTAMP */
775 int ret;
776 sqlite3_stmt *stmt;
777 const char *str;
778 char *dt;
779 const char *sql = "SELECT Datetime(?)";
780 ret = sqlite3_prepare_v2 (db, sql, strlen (sql), &stmt, NULL);
781 if (ret != SQLITE_OK)
782 {
783 char *emsg = sqlite3_mprintf ("SQLite error (Datetime): %s\n",
784 sqlite3_errmsg (db));
785 vpgReportError (db, emsg);
786 sqlite3_free (emsg);
787 return sqlite3_mprintf ("%s", "1900-01-01 12:00:00.000");
788 }
789 sqlite3_reset (stmt);
790 sqlite3_clear_bindings (stmt);
791 sqlite3_bind_double (stmt, 1, value);
792 ret = sqlite3_step (stmt);
793 if (ret == SQLITE_ROW)
794 {
795 str = (const char *) sqlite3_column_text (stmt, 0);
796 dt = sqlite3_mprintf ("%s", str);
797 }
798 else
799 {
800 char *emsg = sqlite3_mprintf ("SQLite error (Datetime): %s\n",
801 sqlite3_errmsg (db));
802 vpgReportError (db, emsg);
803 sqlite3_free (emsg);
804 sqlite3_finalize (stmt);
805 return sqlite3_mprintf ("%s", "1900-01-01 12:00:00.000");
806 }
807 sqlite3_finalize (stmt);
808 return dt;
809 }
810
811 static char *
vpgMakeBool(int value)812 vpgMakeBool (int value)
813 {
814 /* tranforming an INTEGER into a BOOL */
815 return sqlite3_mprintf ("%c", (value == 0) ? 'f' : 't');
816 }
817
818 static char *
vpgDequoted(const char * value)819 vpgDequoted (const char *value)
820 {
821 /*
822 / returns a well formatted TEXT value
823 / 1] if the input string begins and ends with ' sigle quote will be the target
824 / 2] if the input string begins and ends with " double quote will be the target
825 / 3] in any othet case the string will simply be copied
826 */
827 const char *pi = value;
828 const char *start;
829 const char *end;
830 char *clean;
831 char *po;
832 int len;
833 char target;
834 int mark = 0;
835 if (value == NULL)
836 return NULL;
837
838 len = strlen (value);
839 clean = malloc (len + 1);
840 if (*(value + 0) == '"' && *(value + len - 1) == '"')
841 target = '"';
842 else if (*(value + 0) == '\'' && *(value + len - 1) == '\'')
843 target = '\'';
844 else
845 {
846 /* no dequoting; simply copying */
847 strcpy (clean, value);
848 return clean;
849 }
850 start = value;
851 end = value + len - 1;
852 po = clean;
853 while (*pi != '\0')
854 {
855 if (mark)
856 {
857 if (*pi == target)
858 {
859 *po++ = *pi++;
860 mark = 0;
861 continue;
862 }
863 else
864 {
865 /* error: mismatching quote */
866 free (clean);
867 return NULL;
868 }
869 }
870 if (*pi == target)
871 {
872 if (pi == start || pi == end)
873 {
874 /* first or last char */
875 pi++;
876 continue;
877 }
878 /* found a quote marker */
879 mark = 1;
880 pi++;
881 continue;
882 }
883 *po++ = *pi++;
884 }
885 *po = '\0';
886 return clean;
887 }
888
889 static int
vpgMapType(const char * type)890 vpgMapType (const char *type)
891 {
892 /* mapping a PostgreSQL data-type to SQLite */
893 if (strcmp (type, "int2") == 0)
894 return SQLITE_INTEGER;
895 if (strcmp (type, "int4") == 0)
896 return SQLITE_INTEGER;
897 if (strcmp (type, "int8") == 0)
898 return SQLITE_INTEGER;
899 if (strcmp (type, "float4") == 0)
900 return SQLITE_FLOAT;
901 if (strcmp (type, "float8") == 0)
902 return SQLITE_FLOAT;
903 if (strcmp (type, "money") == 0)
904 return SQLITE_FLOAT;
905 if (strcmp (type, "numeric") == 0)
906 return SQLITE_FLOAT;
907 if (strcmp (type, "date") == 0)
908 return SQLITEX_DATE;
909 if (strcmp (type, "time") == 0)
910 return SQLITEX_TIME;
911 if (strcmp (type, "timestamp") == 0)
912 return SQLITEX_DATETIME;
913 if (strcmp (type, "bool") == 0)
914 return SQLITEX_BOOL;
915 return SQLITE_TEXT;
916 }
917
918 static void
vpgFreeTable(VirtualPgPtr p_vt)919 vpgFreeTable (VirtualPgPtr p_vt)
920 {
921 /* memory cleanup; freeing the virtual table struct */
922 int i;
923 if (!p_vt)
924 return;
925 if (p_vt->pg_conn)
926 vpgPQfinish (p_vt->pg_conn);
927 if (p_vt->pg_schema)
928 sqlite3_free (p_vt->pg_schema);
929 if (p_vt->pg_table)
930 sqlite3_free (p_vt->pg_table);
931 if (p_vt->Column)
932 {
933 for (i = 0; i < p_vt->nColumns; i++)
934 {
935 if (*(p_vt->Column + i))
936 sqlite3_free (*(p_vt->Column + i));
937 }
938 sqlite3_free (p_vt->Column);
939 }
940 if (p_vt->Type)
941 {
942 for (i = 0; i < p_vt->nColumns; i++)
943 {
944 if (*(p_vt->Type + i))
945 sqlite3_free (*(p_vt->Type + i));
946 }
947 sqlite3_free (p_vt->Type);
948 }
949 if (p_vt->Mapping)
950 sqlite3_free (p_vt->Mapping);
951 if (p_vt->MaxSize)
952 sqlite3_free (p_vt->MaxSize);
953 if (p_vt->NotNull)
954 sqlite3_free (p_vt->NotNull);
955 if (p_vt->IsPK)
956 sqlite3_free (p_vt->IsPK);
957 vpgFreePKstrings (p_vt);
958 sqlite3_free (p_vt);
959 }
960
961 static int
vpgInsertRow(VirtualPgPtr p_vt,int argc,sqlite3_value ** argv)962 vpgInsertRow (VirtualPgPtr p_vt, int argc, sqlite3_value ** argv)
963 {
964 /* trying to insert a row into the PostgreSQL real-table */
965 vpgMemBuffer sql_statement;
966 char *sql;
967 char *schema;
968 char *table;
969 char *xname;
970 int c;
971 int comma;
972 PGresult *res;
973 char dummy[1024];
974 char *emsg;
975
976 vpgResetError (p_vt->db);
977
978 /* preparing the PostgreSQL query */
979 schema = vpgDoubleQuoted (p_vt->pg_schema);
980 table = vpgDoubleQuoted (p_vt->pg_table);
981 sql = sqlite3_mprintf ("INSERT INTO %s.%s (", schema, table);
982 free (schema);
983 free (table);
984 vpgMemBufferInitialize (&sql_statement);
985 vpgMemBufferAppend (&sql_statement, sql);
986 sqlite3_free (sql);
987 comma = 0;
988 for (c = 0; c < p_vt->nColumns; c++)
989 {
990 /* column names */
991 xname = vpgDoubleQuoted (*(p_vt->Column + c));
992 if (comma)
993 vpgMemBufferAppend (&sql_statement, ", ");
994 vpgMemBufferAppend (&sql_statement, xname);
995 free (xname);
996 comma = 1;
997 }
998 vpgMemBufferAppend (&sql_statement, ") VALUES (");
999 comma = 0;
1000 for (c = 2; c < argc; c++)
1001 {
1002 /* column values */
1003 int pg_type = *(p_vt->Mapping + c - 2);
1004 if (comma)
1005 vpgMemBufferAppend (&sql_statement, ", ");
1006 switch (pg_type)
1007 {
1008 case SQLITE_INTEGER:
1009 #if defined(_WIN32) || defined(__MINGW32__)
1010 /* CAVEAT - M$ runtime doesn't supports %lld for 64 bits */
1011 sprintf (dummy, "%I64d", sqlite3_value_int64 (argv[c]));
1012 #else
1013 sprintf (dummy, "%lld", sqlite3_value_int64 (argv[c]));
1014 #endif
1015 sql = sqlite3_mprintf ("%s", dummy);
1016 vpgMemBufferAppend (&sql_statement, sql);
1017 sqlite3_free (sql);
1018 break;
1019 case SQLITE_FLOAT:
1020 sql =
1021 sqlite3_mprintf ("%1.16f", sqlite3_value_double (argv[c]));
1022 vpgMemBufferAppend (&sql_statement, sql);
1023 sqlite3_free (sql);
1024 break;
1025 case SQLITEX_DATE:
1026 if (p_vt->julianNumbers)
1027 {
1028 xname =
1029 vpgMakeDate (p_vt->db,
1030 sqlite3_value_double (argv[c]));
1031 sql = sqlite3_mprintf ("%Q", xname);
1032 sqlite3_free (xname);
1033 }
1034 else
1035 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c]));
1036 vpgMemBufferAppend (&sql_statement, sql);
1037 sqlite3_free (sql);
1038 break;
1039 case SQLITEX_TIME:
1040 if (p_vt->julianNumbers)
1041 {
1042 xname =
1043 vpgMakeTime (p_vt->db,
1044 sqlite3_value_double (argv[c]));
1045 sql = sqlite3_mprintf ("%Q", xname);
1046 sqlite3_free (xname);
1047 }
1048 else
1049 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c]));
1050 vpgMemBufferAppend (&sql_statement, sql);
1051 sqlite3_free (sql);
1052 break;
1053 case SQLITEX_DATETIME:
1054 if (p_vt->julianNumbers)
1055 {
1056 xname =
1057 vpgMakeDatetime (p_vt->db,
1058 sqlite3_value_double (argv[c]));
1059 sql = sqlite3_mprintf ("%Q", xname);
1060 sqlite3_free (xname);
1061 }
1062 else
1063 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c]));
1064 vpgMemBufferAppend (&sql_statement, sql);
1065 sqlite3_free (sql);
1066 break;
1067 case SQLITEX_BOOL:
1068 xname = vpgMakeBool (sqlite3_value_int (argv[c]));
1069 sql = sqlite3_mprintf ("%Q", xname);
1070 sqlite3_free (xname);
1071 vpgMemBufferAppend (&sql_statement, sql);
1072 sqlite3_free (sql);
1073 break;
1074 case SQLITE_TEXT:
1075 sql =
1076 sqlite3_mprintf ("%Q",
1077 (const char *)
1078 sqlite3_value_text (argv[c]));
1079 vpgMemBufferAppend (&sql_statement, sql);
1080 sqlite3_free (sql);
1081 break;
1082 default:
1083 vpgMemBufferAppend (&sql_statement, "NULL");
1084 break;
1085 };
1086 comma = 1;
1087 }
1088 vpgMemBufferAppend (&sql_statement, ")");
1089 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
1090 {
1091 res = vpgPQexec (p_vt->pg_conn, sql_statement.Buffer);
1092 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
1093 goto illegal;
1094 vpgMemBufferReset (&sql_statement);
1095 vpgPQclear (res);
1096 return SQLITE_OK;
1097 }
1098 illegal:
1099 emsg =
1100 sqlite3_mprintf ("Postgres INSERT failed:\n%s",
1101 vpgPQerrorMessage (p_vt->pg_conn));
1102 vpgReportError (p_vt->db, emsg);
1103 sqlite3_free (emsg);
1104 vpgMemBufferReset (&sql_statement);
1105 vpgPQclear (res);
1106 return SQLITE_ERROR;
1107 }
1108
1109 static int
vpgUpdateRow(VirtualPgPtr p_vt,int nRow,int argc,sqlite3_value ** argv)1110 vpgUpdateRow (VirtualPgPtr p_vt, int nRow, int argc, sqlite3_value ** argv)
1111 {
1112 /* trying to update a row into the PostgreSQL real-table */
1113 vpgMemBuffer sql_statement;
1114 char *sql;
1115 char *schema;
1116 char *table;
1117 char *xname;
1118 char *where;
1119 int c;
1120 int comma;
1121 PGresult *res;
1122 char dummy[1024];
1123 char *emsg;
1124
1125 vpgResetError (p_vt->db);
1126
1127 if (p_vt->nColumns + 2 != argc)
1128 {
1129 emsg = sqlite3_mprintf ("UPDATE failed: mismatching argc count\n");
1130 vpgReportError (p_vt->db, emsg);
1131 sqlite3_free (emsg);
1132 return SQLITE_ERROR;
1133 }
1134
1135 /* preparing the PostgreSQL query */
1136 schema = vpgDoubleQuoted (p_vt->pg_schema);
1137 table = vpgDoubleQuoted (p_vt->pg_table);
1138 sql = sqlite3_mprintf ("UPDATE %s.%s SET ", schema, table);
1139 free (schema);
1140 free (table);
1141 vpgMemBufferInitialize (&sql_statement);
1142 vpgMemBufferAppend (&sql_statement, sql);
1143 sqlite3_free (sql);
1144 comma = 0;
1145 for (c = 0; c < p_vt->nColumns; c++)
1146 {
1147 /* column names */
1148 int pg_type = *(p_vt->Mapping + c);
1149 int c2 = c + 2;
1150 xname = vpgDoubleQuoted (*(p_vt->Column + c));
1151 if (comma)
1152 vpgMemBufferAppend (&sql_statement, ", ");
1153 vpgMemBufferAppend (&sql_statement, xname);
1154 free (xname);
1155 vpgMemBufferAppend (&sql_statement, " = ");
1156 switch (pg_type)
1157 {
1158 case SQLITE_INTEGER:
1159 #if defined(_WIN32) || defined(__MINGW32__)
1160 // CAVEAT - M$ runtime doesn't supports %lld for 64 bits
1161 sprintf (dummy, "%I64d", sqlite3_value_int64 (argv[c2]));
1162 #else
1163 sprintf (dummy, "%lld", sqlite3_value_int64 (argv[c2]));
1164 #endif
1165 sql = sqlite3_mprintf ("%s", dummy);
1166 vpgMemBufferAppend (&sql_statement, sql);
1167 sqlite3_free (sql);
1168 break;
1169 case SQLITE_FLOAT:
1170 sql =
1171 sqlite3_mprintf ("%1.16f", sqlite3_value_double (argv[c2]));
1172 vpgMemBufferAppend (&sql_statement, sql);
1173 sqlite3_free (sql);
1174 break;
1175 case SQLITEX_DATE:
1176 if (p_vt->julianNumbers)
1177 {
1178 xname =
1179 vpgMakeDate (p_vt->db,
1180 sqlite3_value_double (argv[c2]));
1181 sql = sqlite3_mprintf ("%Q", xname);
1182 sqlite3_free (xname);
1183 }
1184 else
1185 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c2]));
1186 vpgMemBufferAppend (&sql_statement, sql);
1187 sqlite3_free (sql);
1188 break;
1189 case SQLITEX_TIME:
1190 if (p_vt->julianNumbers)
1191 {
1192 xname =
1193 vpgMakeTime (p_vt->db,
1194 sqlite3_value_double (argv[c2]));
1195 sql = sqlite3_mprintf ("%Q", xname);
1196 sqlite3_free (xname);
1197 }
1198 else
1199 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c2]));
1200 vpgMemBufferAppend (&sql_statement, sql);
1201 sqlite3_free (sql);
1202 break;
1203 case SQLITEX_DATETIME:
1204 if (p_vt->julianNumbers)
1205 {
1206 xname =
1207 vpgMakeDatetime (p_vt->db,
1208 sqlite3_value_double (argv[c2]));
1209 sql = sqlite3_mprintf ("%Q", xname);
1210 sqlite3_free (xname);
1211 }
1212 else
1213 sql = sqlite3_mprintf ("%Q", sqlite3_value_text (argv[c2]));
1214 vpgMemBufferAppend (&sql_statement, sql);
1215 sqlite3_free (sql);
1216 break;
1217 case SQLITEX_BOOL:
1218 xname = vpgMakeBool (sqlite3_value_int (argv[c2]));
1219 sql = sqlite3_mprintf ("%Q", xname);
1220 sqlite3_free (xname);
1221 vpgMemBufferAppend (&sql_statement, sql);
1222 sqlite3_free (sql);
1223 break;
1224 case SQLITE_TEXT:
1225 sql =
1226 sqlite3_mprintf ("%Q",
1227 (const char *)
1228 sqlite3_value_text (argv[c2]));
1229 vpgMemBufferAppend (&sql_statement, sql);
1230 sqlite3_free (sql);
1231 break;
1232 default:
1233 vpgMemBufferAppend (&sql_statement, "NULL");
1234 break;
1235 };
1236 comma = 1;
1237 }
1238
1239 /* appending the WHERE clause (PK values) */
1240 where = vpgBuildPkWhere (p_vt, nRow);
1241 if (where == NULL)
1242 goto illegal_pk;
1243 vpgMemBufferAppend (&sql_statement, where);
1244 sqlite3_free (where);
1245
1246 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
1247 {
1248 res = vpgPQexec (p_vt->pg_conn, sql_statement.Buffer);
1249 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
1250 goto illegal;
1251 vpgMemBufferReset (&sql_statement);
1252 vpgPQclear (res);
1253 return SQLITE_OK;
1254 }
1255 illegal_pk:
1256 emsg =
1257 sqlite3_mprintf
1258 ("Postgres UPDATE failed: unable to get PK values, sorry");
1259 vpgReportError (p_vt->db, emsg);
1260 sqlite3_free (emsg);
1261 vpgMemBufferReset (&sql_statement);
1262 return SQLITE_ERROR;
1263 illegal:
1264 emsg =
1265 sqlite3_mprintf ("Postgres UPDATE failed:\n%s",
1266 vpgPQerrorMessage (p_vt->pg_conn));
1267 vpgReportError (p_vt->db, emsg);
1268 sqlite3_free (emsg);
1269 vpgMemBufferReset (&sql_statement);
1270 vpgPQclear (res);
1271 return SQLITE_ERROR;
1272 }
1273
1274 static int
vpgDeleteRow(VirtualPgPtr p_vt,int nRow)1275 vpgDeleteRow (VirtualPgPtr p_vt, int nRow)
1276 {
1277 /* trying to delete a row from the PostgreSQL real-table */
1278 vpgMemBuffer sql_statement;
1279 char *schema;
1280 char *table;
1281 char *where;
1282 char *sql;
1283 PGresult *res;
1284 char *emsg;
1285
1286 vpgResetError (p_vt->db);
1287
1288 schema = vpgDoubleQuoted (p_vt->pg_schema);
1289 table = vpgDoubleQuoted (p_vt->pg_table);
1290 sql = sqlite3_mprintf ("DELETE FROM %s.%s", schema, table);
1291 free (schema);
1292 free (table);
1293 vpgMemBufferInitialize (&sql_statement);
1294 vpgMemBufferAppend (&sql_statement, sql);
1295 sqlite3_free (sql);
1296
1297 /* appending the WHERE clause (PK values) */
1298 where = vpgBuildPkWhere (p_vt, nRow);
1299 if (where == NULL)
1300 goto illegal_pk;
1301 vpgMemBufferAppend (&sql_statement, where);
1302 sqlite3_free (where);
1303
1304 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
1305 {
1306 res = vpgPQexec (p_vt->pg_conn, sql_statement.Buffer);
1307 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
1308 goto illegal;
1309 vpgMemBufferReset (&sql_statement);
1310 vpgPQclear (res);
1311 return SQLITE_OK;
1312 }
1313 illegal_pk:
1314 emsg =
1315 sqlite3_mprintf
1316 ("Postgres DELETE failed: unable to get PK values, sorry");
1317 vpgReportError (p_vt->db, emsg);
1318 sqlite3_free (emsg);
1319 vpgMemBufferReset (&sql_statement);
1320 return SQLITE_ERROR;
1321 illegal:
1322 emsg =
1323 sqlite3_mprintf ("Postgres DELETE failed:\n%s",
1324 vpgPQerrorMessage (p_vt->pg_conn));
1325 vpgReportError (p_vt->db, emsg);
1326 sqlite3_free (emsg);
1327 vpgPQclear (res);
1328 return SQLITE_ERROR;
1329 }
1330
1331 static void
vpgReadRow(VirtualPgCursorPtr cursor)1332 vpgReadRow (VirtualPgCursorPtr cursor)
1333 {
1334 /* trying to read a row from PostgreSQL real-table */
1335 int r;
1336 int c;
1337 PGresult *res = cursor->resultSet;
1338 vpgSqliteValuePtr value;
1339 if (cursor->currentRow >= cursor->nRows)
1340 {
1341 /* EOF condition */
1342 cursor->eof = 1;
1343 return;
1344 }
1345
1346 r = cursor->currentRow;
1347 for (c = 0; c < cursor->nFields; c++)
1348 {
1349 /* extracting/converting column values */
1350 value = *(cursor->Values + c);
1351 if (vpgPQgetisnull (res, r, c))
1352 vpgSetNullValue (value);
1353 else
1354 {
1355 const char *pg_value = vpgPQgetvalue (res, r, c);
1356 switch (*(cursor->pVtab->Mapping + c))
1357 {
1358 case SQLITE_INTEGER:
1359 vpgSetIntValue (value, pg_value);
1360 break;
1361 case SQLITE_FLOAT:
1362 vpgSetDoubleValue (value, pg_value);
1363 break;
1364 case SQLITEX_DATE:
1365 case SQLITEX_TIME:
1366 case SQLITEX_DATETIME:
1367 if (cursor->pVtab->julianNumbers)
1368 vpgSetDateValue (value,
1369 vpgMakeJulianDay (cursor->pVtab->db,
1370 pg_value));
1371 else
1372 vpgSetTextValue (value, pg_value);
1373 break;
1374 case SQLITEX_BOOL:
1375 vpgSetBoolValue (value, pg_value);
1376 break;
1377 case SQLITE_TEXT:
1378 vpgSetTextValue (value, pg_value);
1379 break;
1380 default:
1381 vpgSetNullValue (value);
1382 };
1383 }
1384 }
1385 }
1386
1387 static void
vpgCheckView(PGconn * pg_conn,const char * pg_schema,const char * pg_name,VirtualPgPtr p_vt)1388 vpgCheckView (PGconn * pg_conn, const char *pg_schema, const char *pg_name,
1389 VirtualPgPtr p_vt)
1390 {
1391 /* testing for a Postgres View */
1392 char *sql;
1393 PGresult *res;
1394 int nRows;
1395 int nFields;
1396 int count = 0;
1397
1398 sql =
1399 sqlite3_mprintf
1400 ("SELECT count(*) FROM pg_views where schemaname = %Q AND viewname = %Q",
1401 pg_schema, pg_name);
1402 res = vpgPQexec (pg_conn, sql);
1403 sqlite3_free (sql);
1404 if (vpgPQresultStatus (res) != PGRES_TUPLES_OK)
1405 goto illegal;
1406 nRows = vpgPQntuples (res);
1407 nFields = vpgPQnfields (res);
1408 if (nRows == 1 && nFields == 1)
1409 count = atoi (vpgPQgetvalue (res, 0, 0));
1410 if (count)
1411 p_vt->isView = 1;
1412 illegal:
1413 /* something has gone the wrong way */
1414 if (res != NULL)
1415 vpgPQclear (res);
1416 }
1417
1418 static int
vpg_setPKcolumn(VirtualPgPtr p_vt,const char * column)1419 vpg_setPKcolumn (VirtualPgPtr p_vt, const char *column)
1420 {
1421 /* marking a PK column */
1422 int c;
1423 for (c = 0; c < p_vt->nColumns; c++)
1424 {
1425 if (strcmp (column, *(p_vt->Column + c)) == 0)
1426 {
1427 *(p_vt->IsPK + c) = 'Y';
1428 return 1;
1429 }
1430 }
1431 return 0;
1432 }
1433
1434
1435
1436 /*************************************************************
1437 /
1438 / VirtualTable methods
1439 /
1440 **************************************************************/
1441 static int
vpg_create(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)1442 vpg_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
1443 sqlite3_vtab ** ppVTab, char **pzErr)
1444 {
1445 /* creates the virtual table connected to some PostgreSQL table */
1446 char *vtable;
1447 char *conninfo;
1448 char *pg_schema;
1449 char *pg_table;
1450 char prefix[64];
1451 char *xname;
1452 const char *col_name;
1453 const char *col_type;
1454 const char *is_not_null;
1455 int max_size;
1456 int not_null;
1457 char *sql;
1458 PGconn *pg_conn;
1459 PGresult *res;
1460 int nRows;
1461 int nFields;
1462 int r;
1463 int len;
1464 int readOnly = 1;
1465 int julianNumbers = 0;
1466 int nPKs = 0;
1467 VirtualPgPtr p_vt = NULL;
1468 vpgMemBuffer sql_statement;
1469 char *emsg;
1470
1471 vpgResetError (db);
1472
1473 if (pAux)
1474 pAux = pAux; /* unused arg warning suppression */
1475 /* checking arguments */
1476 if (argc >= 6 || argc <= 8)
1477 {
1478 vtable = vpgDequoted ((char *) argv[2]);
1479 conninfo = vpgDequoted ((char *) argv[3]);
1480 pg_schema = vpgDequoted ((char *) argv[4]);
1481 pg_table = vpgDequoted ((char *) argv[5]);
1482 if (argc >= 7)
1483 {
1484 /* testing for W */
1485 char *wr = vpgDequoted ((char *) argv[6]);
1486 if (strcmp (wr, "W") == 0)
1487 readOnly = 0;
1488 free (wr);
1489 }
1490 if (argc == 8)
1491 {
1492 /* testing for J */
1493 char *julian = vpgDequoted ((char *) argv[7]);
1494 if (strcmp (julian, "J") == 0)
1495 julianNumbers = 1;
1496 free (julian);
1497 }
1498 }
1499 else
1500 {
1501 *pzErr
1502 =
1503 sqlite3_mprintf
1504 ("[VirtualPostgres] CREATE VIRTUAL: illegal arg list {conn_info, schema, table [ , 'W' [ , 'J' ]] }\n");
1505 goto error;
1506 }
1507
1508 /* Make a connection to the PostgreSQL database */
1509 pg_conn = vpgPQconnectdb (conninfo);
1510 if (vpgPQstatus (pg_conn) != CONNECTION_OK)
1511 {
1512 emsg =
1513 sqlite3_mprintf ("Connection to Postgres failed:\n%s",
1514 vpgPQerrorMessage (pg_conn));
1515 vpgReportError (db, emsg);
1516 sqlite3_free (emsg);
1517 vpgPQfinish (pg_conn);
1518 goto create_emergency_default;
1519 }
1520
1521 /* retrieving the PosgreSQL table columns */
1522 sql =
1523 sqlite3_mprintf
1524 ("SELECT c.attname, d.typname, c.atttypmod, c.attnotnull "
1525 "FROM pg_attribute AS c " "JOIN pg_class AS t ON (c.attrelid = t.oid) "
1526 "JOIN pg_namespace AS s ON (t.relnamespace = s.oid) "
1527 "JOIN pg_type AS d ON (c.atttypid = d.oid) "
1528 "WHERE s.nspname = %Q AND t.relname = %Q AND c.attnum > 0 "
1529 "ORDER BY c.attnum", pg_schema, pg_table);
1530 res = vpgPQexec (pg_conn, sql);
1531 sqlite3_free (sql);
1532 if (vpgPQresultStatus (res) != PGRES_TUPLES_OK)
1533 goto illegal;
1534 nRows = vpgPQntuples (res);
1535 nFields = vpgPQnfields (res);
1536 if (nRows > 0 && nFields == 4)
1537 {
1538 p_vt = (VirtualPgPtr) sqlite3_malloc (sizeof (VirtualPg));
1539 if (!p_vt)
1540 return SQLITE_NOMEM;
1541 p_vt->db = db;
1542 p_vt->ConnInfo = conninfo;
1543 p_vt->isView = 0;
1544 p_vt->readOnly = readOnly;
1545 p_vt->pendingTransaction = 0;
1546 p_vt->julianNumbers = julianNumbers;
1547 p_vt->pg_conn = pg_conn;
1548 p_vt->nRef = 0;
1549 p_vt->zErrMsg = NULL;
1550 len = strlen (pg_schema);
1551 p_vt->pg_schema = sqlite3_malloc (len + 1);
1552 strcpy (p_vt->pg_schema, pg_schema);
1553 len = strlen (pg_table);
1554 p_vt->pg_table = sqlite3_malloc (len + 1);
1555 strcpy (p_vt->pg_table, pg_table);
1556 p_vt->nColumns = nRows;
1557 p_vt->Column = sqlite3_malloc (sizeof (char *) * nRows);
1558 p_vt->Type = sqlite3_malloc (sizeof (char *) * nRows);
1559 p_vt->Mapping = sqlite3_malloc (sizeof (int) * nRows);
1560 p_vt->MaxSize = sqlite3_malloc (sizeof (int) * nRows);
1561 p_vt->NotNull = sqlite3_malloc (sizeof (int) * nRows);
1562 p_vt->IsPK = sqlite3_malloc (sizeof (char) * nRows);
1563 p_vt->newRowid = 0;
1564 p_vt->PKstrings = NULL;
1565 p_vt->PKidx = NULL;
1566 p_vt->PKrows = 0;
1567 p_vt->PKcols = 0;
1568 for (r = 0; r < p_vt->nColumns; r++)
1569 {
1570 *(p_vt->Column + r) = NULL;
1571 *(p_vt->Type + r) = NULL;
1572 *(p_vt->Mapping + r) = SQLITE_NULL;
1573 *(p_vt->MaxSize + r) = -1;
1574 *(p_vt->NotNull + r) = -1;
1575 *(p_vt->IsPK + r) = 'N';
1576 }
1577 for (r = 0; r < nRows; r++)
1578 {
1579 col_name = vpgPQgetvalue (res, r, 0);
1580 col_type = vpgPQgetvalue (res, r, 1);
1581 max_size = atoi (vpgPQgetvalue (res, r, 2));
1582 if (max_size > 0)
1583 max_size -= 4;
1584 is_not_null = vpgPQgetvalue (res, r, 3);
1585 if (*is_not_null == 't')
1586 not_null = 1;
1587 else
1588 not_null = 0;
1589 len = strlen (col_name);
1590 *(p_vt->Column + r) = sqlite3_malloc (len + 1);
1591 strcpy (*(p_vt->Column + r), col_name);
1592 len = strlen (col_type);
1593 *(p_vt->Type + r) = sqlite3_malloc (len + 1);
1594 strcpy (*(p_vt->Type + r), col_type);
1595 *(p_vt->Mapping + r) = vpgMapType (col_type);
1596 *(p_vt->MaxSize + r) = max_size;
1597 *(p_vt->NotNull + r) = not_null;
1598 }
1599 }
1600 vpgPQclear (res);
1601 res = NULL;
1602
1603 if (p_vt == NULL)
1604 goto illegal;
1605
1606 /* retrieving all PosgreSQL PK columns */
1607 sql =
1608 sqlite3_mprintf
1609 ("SELECT a.attname FROM pg_index AS i "
1610 "JOIN pg_attribute AS a ON (a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)) "
1611 "WHERE i.indrelid = '%s.%s'::regclass AND i.indisprimary", pg_schema,
1612 pg_table);
1613 res = vpgPQexec (pg_conn, sql);
1614 sqlite3_free (sql);
1615 if (vpgPQresultStatus (res) != PGRES_TUPLES_OK)
1616 goto illegal;
1617 nRows = vpgPQntuples (res);
1618 nFields = vpgPQnfields (res);
1619 if (nRows > 0 && nFields == 1)
1620 {
1621 for (r = 0; r < nRows; r++)
1622 {
1623 col_name = vpgPQgetvalue (res, r, 0);
1624 if (!vpg_setPKcolumn (p_vt, col_name))
1625 goto illegal;
1626 nPKs++;
1627 }
1628 }
1629 vpgPQclear (res);
1630 res = NULL;
1631 if (nPKs == 0)
1632 {
1633 /* missing PK; defaulting to ReadOnly */
1634 p_vt->readOnly = 1;
1635 }
1636
1637 /* preparing the COLUMNs for this VIRTUAL TABLE */
1638 vpgMemBufferInitialize (&sql_statement);
1639 xname = vpgDoubleQuoted (vtable);
1640 sql = sqlite3_mprintf ("CREATE TABLE %s ", xname);
1641 free (xname);
1642 vpgMemBufferAppend (&sql_statement, sql);
1643 sqlite3_free (sql);
1644 for (r = 0; r < p_vt->nColumns; r++)
1645 {
1646 if (r == 0)
1647 strcpy (prefix, "(");
1648 else
1649 strcpy (prefix, ", ");
1650 xname = vpgDoubleQuoted (*(p_vt->Column + r));
1651 if (*(p_vt->MaxSize + r) > 0)
1652 {
1653 if (*(p_vt->NotNull + r))
1654 sql =
1655 sqlite3_mprintf ("%s%s %s(%d) NOT NULL", prefix, xname,
1656 *(p_vt->Type + r),
1657 *(p_vt->MaxSize + r));
1658 else
1659 sql =
1660 sqlite3_mprintf ("%s%s %s(%d)", prefix, xname,
1661 *(p_vt->Type + r),
1662 *(p_vt->MaxSize + r));
1663 }
1664 else
1665 {
1666 if (*(p_vt->NotNull + r))
1667 sql =
1668 sqlite3_mprintf ("%s%s %s NOT NULL", prefix, xname,
1669 *(p_vt->Type + r));
1670 else
1671 sql =
1672 sqlite3_mprintf ("%s%s %s", prefix, xname,
1673 *(p_vt->Type + r));
1674 }
1675 free (xname);
1676 vpgMemBufferAppend (&sql_statement, sql);
1677 sqlite3_free (sql);
1678 }
1679 vpgMemBufferAppend (&sql_statement, ")");
1680 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
1681 {
1682 if (sqlite3_declare_vtab (db, sql_statement.Buffer) != SQLITE_OK)
1683 {
1684 *pzErr
1685 =
1686 sqlite3_mprintf
1687 ("[VirtualPostgres] CREATE VIRTUAL: invalid SQL statement \"%s\"",
1688 sql);
1689 goto error;
1690 }
1691 vpgMemBufferReset (&sql_statement);
1692 }
1693 else
1694 goto error;
1695 *ppVTab = (sqlite3_vtab *) p_vt;
1696 free (vtable);
1697 vpgCheckView (pg_conn, pg_schema, pg_table, p_vt);
1698 free (pg_schema);
1699 free (pg_table);
1700 vpgPQfinish (pg_conn);
1701 p_vt->pg_conn = NULL;
1702 return SQLITE_OK;
1703
1704 create_emergency_default:
1705 /* preparing a fake VIRTUAL TABLE */
1706 xname = vpgDoubleQuoted (vtable);
1707 sql = sqlite3_mprintf ("CREATE TABLE %s (dummy integer)", xname);
1708 free (xname);
1709 if (sqlite3_declare_vtab (db, sql) != SQLITE_OK)
1710 {
1711 *pzErr
1712 =
1713 sqlite3_mprintf
1714 ("[VirtualPostgres] CREATE VIRTUAL: invalid SQL statement \"%s\"",
1715 sql);
1716 goto error;
1717 }
1718 sqlite3_free (sql);
1719 nRows = 1;
1720 p_vt = (VirtualPgPtr) sqlite3_malloc (sizeof (VirtualPg));
1721 if (!p_vt)
1722 return SQLITE_NOMEM;
1723 p_vt->db = db;
1724 p_vt->ConnInfo = conninfo;
1725 p_vt->isView = 0;
1726 p_vt->readOnly = 1;
1727 p_vt->pendingTransaction = 0;
1728 p_vt->julianNumbers = 0;
1729 p_vt->pg_conn = NULL;
1730 p_vt->nRef = 0;
1731 p_vt->zErrMsg = NULL;
1732 len = strlen (pg_schema);
1733 p_vt->pg_schema = sqlite3_malloc (len + 1);
1734 strcpy (p_vt->pg_schema, pg_schema);
1735 len = strlen (pg_table);
1736 p_vt->pg_table = sqlite3_malloc (len + 1);
1737 strcpy (p_vt->pg_table, pg_table);
1738 p_vt->nColumns = nRows;
1739 p_vt->Column = sqlite3_malloc (sizeof (char *) * nRows);
1740 p_vt->Type = sqlite3_malloc (sizeof (char *) * nRows);
1741 p_vt->Mapping = sqlite3_malloc (sizeof (int) * nRows);
1742 p_vt->MaxSize = sqlite3_malloc (sizeof (int) * nRows);
1743 p_vt->NotNull = sqlite3_malloc (sizeof (int) * nRows);
1744 p_vt->IsPK = sqlite3_malloc (sizeof (char) * nRows);
1745 p_vt->newRowid = 0;
1746 p_vt->PKstrings = NULL;
1747 p_vt->PKidx = NULL;
1748 p_vt->PKrows = 0;
1749 p_vt->PKcols = 0;
1750 for (r = 0; r < p_vt->nColumns; r++)
1751 {
1752 *(p_vt->Column + r) = NULL;
1753 *(p_vt->Type + r) = NULL;
1754 *(p_vt->Mapping + r) = SQLITE_NULL;
1755 *(p_vt->MaxSize + r) = -1;
1756 *(p_vt->NotNull + r) = -1;
1757 *(p_vt->IsPK + r) = 'N';
1758 }
1759 for (r = 0; r < nRows; r++)
1760 {
1761 col_name = "dummy";
1762 col_type = "integer";
1763 not_null = 0;
1764 max_size = 10;
1765 len = strlen (col_name);
1766 *(p_vt->Column + r) = sqlite3_malloc (len + 1);
1767 strcpy (*(p_vt->Column + r), col_name);
1768 len = strlen (col_type);
1769 *(p_vt->Type + r) = sqlite3_malloc (len + 1);
1770 strcpy (*(p_vt->Type + r), col_type);
1771 *(p_vt->Mapping + r) = vpgMapType (col_type);
1772 *(p_vt->MaxSize + r) = max_size;
1773 *(p_vt->NotNull + r) = not_null;
1774 }
1775 *ppVTab = (sqlite3_vtab *) p_vt;
1776 free (vtable);
1777 free (pg_schema);
1778 free (pg_table);
1779 return SQLITE_OK;
1780
1781 illegal:
1782 /* something has gone the wrong way */
1783 if (res != NULL)
1784 vpgPQclear (res);
1785 *pzErr
1786 =
1787 sqlite3_mprintf
1788 ("[VirtualPostgres] '%s'.'%s' isn't a valid Postgres table\n",
1789 pg_schema, pg_table);
1790 vpgPQfinish (pg_conn);
1791 p_vt->pg_conn = NULL;
1792 p_vt->ConnInfo = conninfo;
1793 conninfo = NULL;
1794
1795 error:
1796 if (vtable != NULL)
1797 free (vtable);
1798 if (conninfo != NULL)
1799 free (conninfo);
1800 if (pg_schema)
1801 free (pg_schema);
1802 if (pg_table)
1803 free (pg_table);
1804 if (p_vt)
1805 vpgFreeTable (p_vt);
1806 return SQLITE_ERROR;
1807 }
1808
1809 static int
vpg_connect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)1810 vpg_connect (sqlite3 * db, void *pAux, int argc, const char *const *argv,
1811 sqlite3_vtab ** ppVTab, char **pzErr)
1812 {
1813 /* connects the virtual table to some shapefile - simply aliases vpg_create() */
1814 return vpg_create (db, pAux, argc, argv, ppVTab, pzErr);
1815 }
1816
1817 static int
vpg_best_index(sqlite3_vtab * pVTab,sqlite3_index_info * pIndex)1818 vpg_best_index (sqlite3_vtab * pVTab, sqlite3_index_info * pIndex)
1819 {
1820 /* best index selection */
1821 if (pVTab || pIndex)
1822 pVTab = pVTab; /* unused arg warning suppression */
1823 return SQLITE_OK;
1824 }
1825
1826 static int
vpg_disconnect(sqlite3_vtab * pVTab)1827 vpg_disconnect (sqlite3_vtab * pVTab)
1828 {
1829 /* disconnects the virtual table */
1830 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
1831 if (p_vt->ConnInfo != NULL)
1832 free (p_vt->ConnInfo);
1833 vpgFreeTable (p_vt);
1834 return SQLITE_OK;
1835 }
1836
1837 static int
vpg_destroy(sqlite3_vtab * pVTab)1838 vpg_destroy (sqlite3_vtab * pVTab)
1839 {
1840 /* destroys the virtual table - simply aliases vpg_disconnect() */
1841 return vpg_disconnect (pVTab);
1842 }
1843
1844 static int
vpg_open(sqlite3_vtab * pVTab,sqlite3_vtab_cursor ** ppCursor)1845 vpg_open (sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** ppCursor)
1846 {
1847 /* opening a new cursor */
1848 int c;
1849 char *xname;
1850 PGresult *res;
1851 int nRows;
1852 int nFields;
1853 int first = 1;
1854 char *emsg;
1855 vpgMemBuffer sql_statement;
1856 VirtualPgCursorPtr cursor = NULL;
1857 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
1858 p_vt->pg_conn = vpgPQconnectdb (p_vt->ConnInfo);
1859 if (vpgPQstatus (p_vt->pg_conn) != CONNECTION_OK)
1860 {
1861 emsg =
1862 sqlite3_mprintf ("Connection to Postgres failed:\n%s",
1863 vpgPQerrorMessage (p_vt->pg_conn));
1864 vpgReportError (p_vt->db, emsg);
1865 sqlite3_free (emsg);
1866 vpgPQfinish (p_vt->pg_conn);
1867 p_vt->pg_conn = NULL;
1868 }
1869
1870 /* preparing the PostgreSQL query */
1871 vpgMemBufferInitialize (&sql_statement);
1872 vpgMemBufferAppend (&sql_statement, "SELECT");
1873 for (c = 0; c < p_vt->nColumns; c++)
1874 {
1875 xname = vpgDoubleQuoted (*(p_vt->Column + c));
1876 if (first)
1877 {
1878 first = 0;
1879 vpgMemBufferAppend (&sql_statement, " ");
1880 }
1881 else
1882 vpgMemBufferAppend (&sql_statement, ", ");
1883 vpgMemBufferAppend (&sql_statement, xname);
1884 free (xname);
1885 }
1886 vpgMemBufferAppend (&sql_statement, " FROM ");
1887 xname = vpgDoubleQuoted (p_vt->pg_schema);
1888 vpgMemBufferAppend (&sql_statement, xname);
1889 free (xname);
1890 vpgMemBufferAppend (&sql_statement, ".");
1891 xname = vpgDoubleQuoted (p_vt->pg_table);
1892 vpgMemBufferAppend (&sql_statement, xname);
1893 free (xname);
1894 if (p_vt->readOnly == 0)
1895 {
1896 /* appending the ORDER BY <PK> clause */
1897 char *orderBy = vpgBuildPkOrderBy (p_vt);
1898 if (orderBy != NULL)
1899 {
1900 vpgMemBufferAppend (&sql_statement, orderBy);
1901 sqlite3_free (orderBy);
1902 }
1903 }
1904 if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
1905 {
1906 res = vpgPQexec (p_vt->pg_conn, sql_statement.Buffer);
1907 if (vpgPQresultStatus (res) != PGRES_TUPLES_OK)
1908 goto illegal;
1909 nRows = vpgPQntuples (res);
1910 nFields = vpgPQnfields (res);
1911 vpgMemBufferReset (&sql_statement);
1912 }
1913 else
1914 return SQLITE_ERROR;
1915
1916 cursor = (VirtualPgCursorPtr) sqlite3_malloc (sizeof (VirtualPgCursor));
1917 if (cursor == NULL)
1918 return SQLITE_ERROR;
1919 cursor->resultSet = res;
1920 cursor->nRows = nRows;
1921 cursor->nFields = nFields;
1922 cursor->nColumns = p_vt->nColumns;
1923 p_vt->newRowid = nRows;
1924 cursor->Values = sqlite3_malloc (sizeof (vpgSqliteValue) * cursor->nFields);
1925 for (c = 0; c < cursor->nColumns; c++)
1926 {
1927 vpgSqliteValue *val = vpgAllocValue ();
1928 *(cursor->Values + c) = val;
1929 }
1930 cursor->pVtab = p_vt;
1931 cursor->eof = 0;
1932 cursor->currentRow = 0;
1933 if (p_vt->readOnly == 0)
1934 {
1935 /* saving all PK values */
1936 vpgFreePKstrings (p_vt);
1937 p_vt->PKcols = vpgCountPKcols (p_vt);
1938 if (nRows > 0 && p_vt->PKcols > 0)
1939 {
1940 /* allocating and initializing the PK struct */
1941 int k;
1942 int r;
1943 int nPKs = nRows * p_vt->PKcols;
1944 p_vt->PKrows = nRows;
1945 p_vt->PKstrings = malloc (sizeof (char *) * nPKs);
1946 p_vt->PKidx = malloc (sizeof (int) * p_vt->PKcols);
1947 r = 0;
1948 for (c = 0; c < p_vt->nColumns; c++)
1949 {
1950 /* initializing the PK column indices array */
1951 if (*(p_vt->IsPK + c) == 'Y')
1952 {
1953 *(p_vt->PKidx + r) = c;
1954 r++;
1955 }
1956 }
1957 k = 0;
1958 for (r = 0; r < nRows; r++)
1959 {
1960 for (c = 0; c < p_vt->PKcols; c++)
1961 {
1962 if (vpgPQgetisnull (res, r, *(p_vt->PKidx + c)))
1963 *(p_vt->PKstrings + k) = NULL;
1964 else
1965 {
1966 int len;
1967 const char *value = vpgPQgetvalue (res, r,
1968 *
1969 (p_vt->
1970 PKidx +
1971 c));
1972 len = strlen (value);
1973 *(p_vt->PKstrings + k) = malloc (len + 1);
1974 strcpy (*(p_vt->PKstrings + k), value);
1975 }
1976 k++;
1977 }
1978 }
1979 }
1980 }
1981
1982 *ppCursor = (sqlite3_vtab_cursor *) cursor;
1983 vpgReadRow (cursor);
1984 return SQLITE_OK;
1985 illegal:
1986 vpgPQclear (res);
1987 vpgMemBufferReset (&sql_statement);
1988 return SQLITE_ERROR;
1989 }
1990
1991 static int
vpg_close(sqlite3_vtab_cursor * pCursor)1992 vpg_close (sqlite3_vtab_cursor * pCursor)
1993 {
1994 /* closing the cursor */
1995 int c;
1996 VirtualPgCursorPtr cursor = (VirtualPgCursorPtr) pCursor;
1997 vpgPQfinish (cursor->pVtab->pg_conn);
1998 cursor->pVtab->pg_conn = NULL;
1999 for (c = 0; c < cursor->nColumns; c++)
2000 vpgFreeValue (*(cursor->Values + c));
2001 sqlite3_free (cursor->Values);
2002 vpgPQclear (cursor->resultSet);
2003 sqlite3_free (pCursor);
2004 return SQLITE_OK;
2005 }
2006
2007 static int
vpg_filter(sqlite3_vtab_cursor * pCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)2008 vpg_filter (sqlite3_vtab_cursor * pCursor, int idxNum, const char *idxStr,
2009 int argc, sqlite3_value ** argv)
2010 {
2011 /* setting up a cursor filter */
2012 if (pCursor || idxNum || idxStr || argc || argv)
2013 pCursor = pCursor; /* unused arg warning suppression */
2014 return SQLITE_OK;
2015 }
2016
2017 static int
vpg_next(sqlite3_vtab_cursor * pCursor)2018 vpg_next (sqlite3_vtab_cursor * pCursor)
2019 {
2020 /* fetching next row from cursor */
2021 VirtualPgCursorPtr cursor = (VirtualPgCursorPtr) pCursor;
2022 (cursor->currentRow)++;
2023 vpgReadRow (cursor);
2024 return SQLITE_OK;
2025 }
2026
2027 static int
vpg_eof(sqlite3_vtab_cursor * pCursor)2028 vpg_eof (sqlite3_vtab_cursor * pCursor)
2029 {
2030 /* cursor EOF */
2031 VirtualPgCursorPtr cursor = (VirtualPgCursorPtr) pCursor;
2032 return cursor->eof;
2033 }
2034
2035 static int
vpg_column(sqlite3_vtab_cursor * pCursor,sqlite3_context * pContext,int column)2036 vpg_column (sqlite3_vtab_cursor * pCursor, sqlite3_context * pContext,
2037 int column)
2038 {
2039 /* fetching value for the Nth column */
2040 VirtualPgCursorPtr cursor = (VirtualPgCursorPtr) pCursor;
2041 vpgSqliteValuePtr value;
2042 if (column >= 0 && column < cursor->nColumns)
2043 {
2044 value = *(cursor->Values + column);
2045 switch (value->Type)
2046 {
2047 case SQLITE_INTEGER:
2048 sqlite3_result_int64 (pContext, value->IntValue);
2049 break;
2050 case SQLITE_FLOAT:
2051 sqlite3_result_double (pContext, value->DoubleValue);
2052 break;
2053 case SQLITE_TEXT:
2054 sqlite3_result_text (pContext, value->Text, value->Size,
2055 SQLITE_STATIC);
2056 break;
2057 case SQLITE_BLOB:
2058 sqlite3_result_blob (pContext, value->Blob, value->Size,
2059 SQLITE_STATIC);
2060 break;
2061 default:
2062 sqlite3_result_null (pContext);
2063 break;
2064 };
2065 }
2066 else
2067 sqlite3_result_null (pContext);
2068 return SQLITE_OK;
2069 }
2070
2071 static int
vpg_rowid(sqlite3_vtab_cursor * pCursor,sqlite_int64 * pRowid)2072 vpg_rowid (sqlite3_vtab_cursor * pCursor, sqlite_int64 * pRowid)
2073 {
2074 /* fetching the ROWID */
2075 VirtualPgCursorPtr cursor = (VirtualPgCursorPtr) pCursor;
2076 *pRowid = cursor->currentRow;
2077 return SQLITE_OK;
2078 }
2079
2080 static int
vpg_update(sqlite3_vtab * pVTab,int argc,sqlite3_value ** argv,sqlite_int64 * pRowid)2081 vpg_update (sqlite3_vtab * pVTab, int argc, sqlite3_value ** argv,
2082 sqlite_int64 * pRowid)
2083 {
2084 /* generic update [INSERT / UPDATE / DELETE] */
2085 int ret;
2086 int nRow;
2087 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
2088 if (p_vt->readOnly)
2089 return SQLITE_READONLY;
2090 if (p_vt->pg_conn == NULL)
2091 {
2092 /* last attempt to establish a DB connection */
2093 p_vt->pg_conn = vpgPQconnectdb (p_vt->ConnInfo);
2094 }
2095 if (argc == 1)
2096 {
2097 /* performing a DELETE */
2098 nRow = sqlite3_value_int (argv[0]);
2099 ret = vpgDeleteRow (p_vt, nRow);
2100 }
2101 else
2102 {
2103 if (sqlite3_value_type (argv[0]) == SQLITE_NULL)
2104 {
2105 /* performing an INSERT */
2106 ret = vpgInsertRow (p_vt, argc, argv);
2107 if (ret == SQLITE_OK)
2108 *pRowid = p_vt->newRowid;
2109 }
2110 else
2111 {
2112 /* performing an UPDATE */
2113 nRow = sqlite3_value_int (argv[0]);
2114 ret = vpgUpdateRow (p_vt, nRow, argc, argv);
2115 }
2116 }
2117 return ret;
2118 }
2119
2120 static int
vpg_begin(sqlite3_vtab * pVTab)2121 vpg_begin (sqlite3_vtab * pVTab)
2122 {
2123 /* BEGIN TRANSACTION */
2124 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
2125 if (pVTab)
2126 pVTab = pVTab; /* unused arg warning suppression */
2127 if (p_vt->readOnly == 0)
2128 {
2129 /* Beginning a PostgreSQL Transaction */
2130 PGresult *res = vpgPQexec (p_vt->pg_conn, "BEGIN");
2131 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
2132 goto end;
2133 vpgPQclear (res);
2134 p_vt->pendingTransaction = 1;
2135 }
2136 end:
2137 return SQLITE_OK;
2138 }
2139
2140 static int
vpg_sync(sqlite3_vtab * pVTab)2141 vpg_sync (sqlite3_vtab * pVTab)
2142 {
2143 /* SYNC */
2144 if (pVTab)
2145 pVTab = pVTab; /* unused arg warning suppression */
2146 return SQLITE_OK;
2147 }
2148
2149 static int
vpg_commit(sqlite3_vtab * pVTab)2150 vpg_commit (sqlite3_vtab * pVTab)
2151 {
2152 /* COMMIT TRANSACTION */
2153 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
2154 if (pVTab)
2155 pVTab = pVTab; /* unused arg warning suppression */
2156 if (p_vt->pendingTransaction)
2157 {
2158 /* Committing a PostgreSQL Transaction */
2159 PGresult *res = vpgPQexec (p_vt->pg_conn, "COMMIT");
2160 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
2161 goto end;
2162 vpgPQclear (res);
2163 p_vt->pendingTransaction = 0;
2164 }
2165 end:
2166 return SQLITE_OK;
2167 }
2168
2169 static int
vpg_rollback(sqlite3_vtab * pVTab)2170 vpg_rollback (sqlite3_vtab * pVTab)
2171 {
2172 /* ROLLBACK TRANSACTION */
2173 VirtualPgPtr p_vt = (VirtualPgPtr) pVTab;
2174 if (pVTab)
2175 pVTab = pVTab; /* unused arg warning suppression */
2176 if (p_vt->pendingTransaction)
2177 {
2178 /* Rolling back a PostgreSQL Transaction */
2179 PGresult *res = vpgPQexec (p_vt->pg_conn, "ROLLBACK");
2180 if (vpgPQresultStatus (res) != PGRES_COMMAND_OK)
2181 goto end;
2182 vpgPQclear (res);
2183 p_vt->pendingTransaction = 0;
2184 }
2185 end:
2186 return SQLITE_OK;
2187 }
2188
2189 static int
vpg_rename(sqlite3_vtab * pVTab,const char * zNew)2190 vpg_rename (sqlite3_vtab * pVTab, const char *zNew)
2191 {
2192 /* RENAME TABLE */
2193 if (pVTab)
2194 pVTab = pVTab; /* unused arg warning suppression */
2195 if (zNew)
2196 zNew = zNew; /* unused arg warning suppression */
2197 return SQLITE_ERROR;
2198 }
2199
2200 static int
VirtualpgInit(sqlite3 * db)2201 VirtualpgInit (sqlite3 * db)
2202 {
2203 /* initializing the module methods */
2204 int rc = SQLITE_OK;
2205 my_pg_module.iVersion = 1;
2206 my_pg_module.xCreate = &vpg_create;
2207 my_pg_module.xConnect = &vpg_connect;
2208 my_pg_module.xBestIndex = &vpg_best_index;
2209 my_pg_module.xDisconnect = &vpg_disconnect;
2210 my_pg_module.xDestroy = &vpg_destroy;
2211 my_pg_module.xOpen = &vpg_open;
2212 my_pg_module.xClose = &vpg_close;
2213 my_pg_module.xFilter = &vpg_filter;
2214 my_pg_module.xNext = &vpg_next;
2215 my_pg_module.xEof = &vpg_eof;
2216 my_pg_module.xColumn = &vpg_column;
2217 my_pg_module.xRowid = &vpg_rowid;
2218 my_pg_module.xUpdate = &vpg_update;
2219 my_pg_module.xBegin = &vpg_begin;
2220 my_pg_module.xSync = &vpg_sync;
2221 my_pg_module.xCommit = &vpg_commit;
2222 my_pg_module.xRollback = &vpg_rollback;
2223 my_pg_module.xFindFunction = NULL;
2224 my_pg_module.xRename = &vpg_rename;
2225 sqlite3_create_module_v2 (db, "VirtualPostgres", &my_pg_module, NULL, 0);
2226 return rc;
2227 }
2228
2229 #ifndef LOADABLE_EXTENSION
2230
2231 VIRTUALPG_DECLARE int
virtualpg_extension_init(sqlite3 * db,virtualPQptr virtual_api)2232 virtualpg_extension_init (sqlite3 * db, virtualPQptr virtual_api)
2233 {
2234 /* registrering the virtual table - classic library */
2235 if (virtual_api == NULL)
2236 return SQLITE_ERROR;
2237
2238 /*
2239 / initializing the virtual LibPQ methods
2240 /
2241 / the library is always based on a virtualized LibPQ
2242 / expected to be correctly initialized by the caller
2243 */
2244 pq.PQclear = NULL;
2245 pq.PQconnectdb = NULL;
2246 pq.PQerrorMessage = NULL;
2247 pq.PQexec = NULL;
2248 pq.PQfinish = NULL;
2249 pq.PQgetisnull = NULL;
2250 pq.PQgetvalue = NULL;
2251 pq.PQlibVersion = NULL;
2252 pq.PQnfields = NULL;
2253 pq.PQntuples = NULL;
2254 pq.PQresultStatus = NULL;
2255 pq.PQstatus = NULL;
2256
2257 if (virtual_api->PQclear == NULL)
2258 return SQLITE_ERROR;
2259 pq.PQclear = virtual_api->PQclear;
2260
2261 if (virtual_api->PQconnectdb == NULL)
2262 return SQLITE_ERROR;
2263 pq.PQconnectdb = virtual_api->PQconnectdb;
2264
2265 if (virtual_api->PQerrorMessage == NULL)
2266 return SQLITE_ERROR;
2267 pq.PQerrorMessage = virtual_api->PQerrorMessage;
2268
2269 if (virtual_api->PQexec == NULL)
2270 return SQLITE_ERROR;
2271 pq.PQexec = virtual_api->PQexec;
2272
2273 if (virtual_api->PQfinish == NULL)
2274 return SQLITE_ERROR;
2275 pq.PQfinish = virtual_api->PQfinish;
2276
2277 if (virtual_api->PQgetisnull == NULL)
2278 return SQLITE_ERROR;
2279 pq.PQgetisnull = virtual_api->PQgetisnull;
2280
2281 if (virtual_api->PQgetvalue == NULL)
2282 return SQLITE_ERROR;
2283 pq.PQgetvalue = virtual_api->PQgetvalue;
2284
2285 if (virtual_api->PQlibVersion == NULL)
2286 return SQLITE_ERROR;
2287 pq.PQlibVersion = virtual_api->PQlibVersion;
2288
2289 if (virtual_api->PQnfields == NULL)
2290 return SQLITE_ERROR;
2291 pq.PQnfields = virtual_api->PQnfields;
2292
2293 if (virtual_api->PQntuples == NULL)
2294 return SQLITE_ERROR;
2295 pq.PQntuples = virtual_api->PQntuples;
2296
2297 if (virtual_api->PQresultStatus == NULL)
2298 return SQLITE_ERROR;
2299 pq.PQresultStatus = virtual_api->PQresultStatus;
2300
2301 if (virtual_api->PQstatus == NULL)
2302 return SQLITE_ERROR;
2303 pq.PQstatus = virtual_api->PQstatus;
2304
2305 /* registering the VirtualTable */
2306 return VirtualpgInit (db);
2307 }
2308
2309 VIRTUALPG_DECLARE const char *
virtualpg_version()2310 virtualpg_version ()
2311 {
2312 /* returning the current Version string */
2313 return VERSION;
2314 }
2315
2316 #else /* built as LOADABLE EXTENSION only */
2317
2318 VIRTUALPG_DECLARE int
sqlite3_modvirtualpg_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)2319 sqlite3_modvirtualpg_init (sqlite3 * db, char **pzErrMsg,
2320 const sqlite3_api_routines * pApi)
2321 {
2322 /* Register the virtual table */
2323 SQLITE_EXTENSION_INIT2 (pApi);
2324 (void) pzErrMsg;
2325
2326 /*
2327 / initializing the virtual LibPQ methods
2328 /
2329 / the loadable module is always based on hard-linked LibPQ
2330 */
2331 pq.PQclear = PQclear;
2332 pq.PQconnectdb = PQconnectdb;
2333 pq.PQerrorMessage = PQerrorMessage;
2334 pq.PQexec = PQexec;
2335 pq.PQfinish = PQfinish;
2336 pq.PQgetisnull = PQgetisnull;
2337 pq.PQgetvalue = PQgetvalue;
2338 pq.PQlibVersion = PQlibVersion;
2339 pq.PQnfields = PQnfields;
2340 pq.PQntuples = PQntuples;
2341 pq.PQresultStatus = PQresultStatus;
2342 pq.PQstatus = PQstatus;
2343
2344 return VirtualpgInit (db);
2345 }
2346
2347 #endif
2348