1 /*
2
3 stored_procedures.c -- SpatiaLite Stored Procedures support
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) 2017-2021
28 the Initial Developer. All Rights Reserved.
29
30 Contributor(s):
31
32 Alternatively, the contents of this file may be used under the terms of
33 either the GNU General Public License Version 2 or later (the "GPL"), or
34 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
35 in which case the provisions of the GPL or the LGPL are applicable instead
36 of those above. If you wish to allow use of your version of this file only
37 under the terms of either the GPL or the LGPL, and not to allow others to
38 use your version of this file under the terms of the MPL, indicate your
39 decision by deleting the provisions above and replace them with the notice
40 and other provisions required by the GPL or the LGPL. If you do not delete
41 the provisions above, a recipient may use your version of this file under
42 the terms of any one of the MPL, the GPL or the LGPL.
43
44 */
45
46 #include <stdlib.h>
47 #include <stdio.h>
48 #include <string.h>
49
50 #if defined(_WIN32) && !defined(__MINGW32__)
51 #include "config-msvc.h"
52 #else
53 #include "config.h"
54 #endif
55
56 #include <spatialite/sqlite.h>
57 #include <spatialite/gaiageo.h>
58 #include <spatialite/gaiaaux.h>
59 #include <spatialite/gg_const.h>
60 #include <spatialite/gg_formats.h>
61 #include <spatialite/stored_procedures.h>
62 #include <spatialite_private.h>
63 #include <spatialite.h>
64 #include <spatialite/debug.h>
65
66 #ifdef _WIN32
67 #define strcasecmp _stricmp
68 #endif /* not WIN32 */
69
70 /* 64 bit integer: portable format for printf() */
71 #if defined(_WIN32) && !defined(__MINGW32__)
72 #define FRMT64 "%I64d"
73 #else
74 #define FRMT64 "%lld"
75 #endif
76
77 struct sp_var_item
78 {
79 /* a variable Argument item */
80 char *varname;
81 short count;
82 struct sp_var_item *next;
83 };
84
85 struct sp_var_list
86 {
87 /* a list of Variable Arguments */
88 struct sp_var_item *first;
89 struct sp_var_item *last;
90 };
91
92 static void
stored_proc_reset_error(const void * ctx)93 stored_proc_reset_error (const void *ctx)
94 {
95 /* resetting the Stored Procedures Last Error Message */
96 struct splite_internal_cache *cache = (struct splite_internal_cache *) ctx;
97 if (cache != NULL)
98 {
99 if (cache->storedProcError != NULL)
100 {
101 free (cache->storedProcError);
102 cache->storedProcError = NULL;
103 }
104 }
105 }
106
107 SPATIALITE_PRIVATE void
gaia_sql_proc_set_error(const void * ctx,const char * errmsg)108 gaia_sql_proc_set_error (const void *ctx, const char *errmsg)
109 {
110 /* setting the Stored Procedures Last Error Message */
111 struct splite_internal_cache *cache = (struct splite_internal_cache *) ctx;
112 if (cache != NULL)
113 {
114 int len;
115 if (cache->storedProcError != NULL)
116 {
117 free (cache->storedProcError);
118 cache->storedProcError = NULL;
119 }
120 if (errmsg == NULL)
121 return;
122
123 len = strlen (errmsg);
124 cache->storedProcError = malloc (len + 1);
125 strcpy (cache->storedProcError, errmsg);
126 }
127 }
128
129 SQLPROC_DECLARE char *
gaia_sql_proc_get_last_error(const void * p_cache)130 gaia_sql_proc_get_last_error (const void *p_cache)
131 {
132 /* return the last Stored Procedures Error Message (if any) */
133 struct splite_internal_cache *cache =
134 (struct splite_internal_cache *) p_cache;
135
136 if (cache == NULL)
137 return NULL;
138 return cache->storedProcError;
139 }
140
141 SQLPROC_DECLARE SqlProc_VarListPtr
gaia_sql_proc_create_variables()142 gaia_sql_proc_create_variables ()
143 {
144 /* allocating an empty list of Variables with Values */
145 SqlProc_VarListPtr list = malloc (sizeof (SqlProc_VarList));
146 if (list == NULL)
147 return NULL;
148
149 list->Error = 0;
150 list->ErrMessage = NULL;
151 list->First = NULL;
152 list->Last = NULL;
153 return list;
154 }
155
156 SQLPROC_DECLARE void
gaia_sql_proc_destroy_variables(SqlProc_VarListPtr list)157 gaia_sql_proc_destroy_variables (SqlProc_VarListPtr list)
158 {
159 /* destroying a List of Variables with Values */
160 SqlProc_VariablePtr var;
161 SqlProc_VariablePtr n_var;
162 if (list == NULL)
163 return;
164
165 var = list->First;
166 while (var != NULL)
167 {
168 n_var = var->Next;
169 if (var->Name != NULL)
170 free (var->Name);
171 if (var->Value != NULL)
172 free (var->Value);
173 free (var);
174 var = n_var;
175 }
176 if (list->ErrMessage != NULL)
177 sqlite3_free (list->ErrMessage);
178 free (list);
179 }
180
181 static int
parse_variable_name_value(const char * str,char ** name,char ** value)182 parse_variable_name_value (const char *str, char **name, char **value)
183 {
184 /* attempting to parse a Variable with Value definition */
185 char marker = '\0';
186 int end = 0;
187 int start;
188 int name_len;
189 int value_len;
190 char *nm;
191 char *val;
192 int i;
193
194 *name = NULL;
195 *value = NULL;
196 if (*str == '@')
197 marker = '@';
198 if (*str == '$')
199 marker = '$';
200 if (marker == '\0')
201 return 0;
202
203 /* searching the closing marker - variable name */
204 for (i = 1; i < (int) strlen (str); i++)
205 {
206 if (str[i] == marker)
207 {
208 end = i;
209 break;
210 }
211 }
212 if (end == 0)
213 return 0;
214
215 /* searching the start position of Value */
216 if (end + 1 >= (int) strlen (str))
217 return 0;
218 if (str[end + 1] != '=')
219 return 0;
220 start = end + 2;
221
222 name_len = end - 1;
223 value_len = strlen (str + start);
224 if (name_len == 0 || value_len == 0)
225 return 0;
226 nm = malloc (name_len + 1);
227 memcpy (nm, str + 1, end - 1);
228 *(nm + name_len) = '\0';
229 val = malloc (value_len + 1);
230 strcpy (val, str + start);
231
232 *name = nm;
233 *value = val;
234 return 1;
235 }
236
237 SQLPROC_DECLARE int
gaia_sql_proc_add_variable(SqlProc_VarListPtr list,const char * str)238 gaia_sql_proc_add_variable (SqlProc_VarListPtr list, const char *str)
239 {
240 /* adding a Variable with Values to the List */
241 char *name;
242 char *value;
243 SqlProc_VariablePtr var;
244 if (list == NULL)
245 return 0;
246
247 if (!parse_variable_name_value (str, &name, &value))
248 {
249 list->ErrMessage =
250 sqlite3_mprintf ("Illegal Variable with Value definition: %s",
251 str);
252 return 0;
253 }
254
255 var = list->First;
256 while (var != NULL)
257 {
258 /* checking for redefined variables */
259 if (strcasecmp (name, var->Name) == 0)
260 {
261 list->ErrMessage =
262 sqlite3_mprintf
263 ("Duplicated Variable: @%s@ is already defined.", name);
264 return 0;
265 }
266 var = var->Next;
267 }
268
269 /* adding the Variable with Value */
270 var = malloc (sizeof (SqlProc_Variable));
271 var->Name = name;
272 var->Value = value;
273 var->Next = NULL;
274 if (list->First == NULL)
275 list->First = var;
276 if (list->Last != NULL)
277 list->Last->Next = var;
278 list->Last = var;
279 return 1;
280 }
281
282 SQLPROC_DECLARE int
gaia_sql_proc_is_valid_var_value(const char * str)283 gaia_sql_proc_is_valid_var_value (const char *str)
284 {
285 /* checking a Variable with Values for validity */
286 char *name;
287 char *value;
288
289 if (!parse_variable_name_value (str, &name, &value))
290 return 0;
291 free (name);
292 free (value);
293 return 1;
294 }
295
296
297 static struct sp_var_list *
alloc_var_list()298 alloc_var_list ()
299 {
300 /* allocating an empty list of Variables */
301 struct sp_var_list *list = malloc (sizeof (struct sp_var_list));
302 list->first = NULL;
303 list->last = NULL;
304 return list;
305 }
306
307 static void
free_var_list(struct sp_var_list * list)308 free_var_list (struct sp_var_list *list)
309 {
310 /* destroying a list of Variables */
311 struct sp_var_item *item;
312 struct sp_var_item *nitem;
313
314 if (list == NULL)
315 return;
316 item = list->first;
317 while (item != NULL)
318 {
319 nitem = item->next;
320 if (item->varname != NULL)
321 free (item->varname);
322 free (item);
323 item = nitem;
324 }
325 free (list);
326 }
327
328 static void
add_variable_ex(struct sp_var_list * list,char * varname,short ref_count)329 add_variable_ex (struct sp_var_list *list, char *varname, short ref_count)
330 {
331 /* adding a Variable to the List */
332 struct sp_var_item *item;
333
334 if (list == NULL)
335 return;
336 if (varname == NULL)
337 return;
338
339 /* inserting a new variable */
340 item = malloc (sizeof (struct sp_var_item));
341 item->varname = varname;
342 item->count = ref_count;
343 item->next = NULL;
344 if (list->first == NULL)
345 list->first = item;
346 if (list->last != NULL)
347 list->last->next = item;
348 list->last = item;
349 }
350
351 #ifndef OMIT_ICONV /* ICONV is supported */
352
353 static void
add_variable(struct sp_var_list * list,char * varname)354 add_variable (struct sp_var_list *list, char *varname)
355 {
356 /* adding a Variable to the List */
357 struct sp_var_item *item;
358
359 if (list == NULL)
360 return;
361 if (varname == NULL)
362 return;
363
364 /* checking for already defined variables */
365 item = list->first;
366 while (item != NULL)
367 {
368 if (strcasecmp (item->varname, varname) == 0)
369 {
370 /* already defined */
371 item->count += 1; /* increasing the reference count */
372 free (varname);
373 return;
374 }
375 item = item->next;
376 }
377
378 /* inserting a new variable */
379 item = malloc (sizeof (struct sp_var_item));
380 item->varname = varname;
381 item->count = 1;
382 item->next = NULL;
383 if (list->first == NULL)
384 list->first = item;
385 if (list->last != NULL)
386 list->last->next = item;
387 list->last = item;
388 }
389
390 static int
var_list_required_size(struct sp_var_list * list)391 var_list_required_size (struct sp_var_list *list)
392 {
393 /* computing the size required to store the List into the BLOB */
394 struct sp_var_item *item;
395 int size = 0;
396 /* checking for already defined variables */
397 item = list->first;
398 while (item != NULL)
399 {
400 size += strlen (item->varname) + 7;
401 item = item->next;
402 }
403 return size;
404 }
405
406 static short
var_list_count_items(struct sp_var_list * list)407 var_list_count_items (struct sp_var_list *list)
408 {
409 /* counting how many variables are there */
410 struct sp_var_item *item;
411 short count = 0;
412 /* checking for already defined variables */
413 item = list->first;
414 while (item != NULL)
415 {
416 count++;
417 item = item->next;
418 }
419 return count;
420 }
421
422 #endif
423
424 SQLPROC_DECLARE int
gaia_sql_proc_parse(const void * cache,const char * xsql,const char * charset,unsigned char ** blob,int * blob_sz)425 gaia_sql_proc_parse (const void *cache, const char *xsql,
426 const char *charset, unsigned char **blob, int *blob_sz)
427 {
428 /* attempting to parse a Stored Procedure from Text */
429 #ifndef OMIT_ICONV /* ICONV is supported */
430 int len;
431 int i;
432 char *sql = NULL;
433 int start_line;
434 int macro;
435 int comment;
436 int variable;
437 char varMark;
438 int varStart;
439 struct sp_var_list *list = NULL;
440 struct sp_var_item *item;
441 unsigned char *stored_proc = NULL;
442 unsigned char *p_out;
443 int stored_proc_sz;
444 short num_vars;
445 int sql_len;
446 int endian_arch = gaiaEndianArch ();
447 stored_proc_reset_error (cache);
448
449 if (xsql == NULL)
450 {
451 const char *errmsg = "NULL SQL body\n";
452 gaia_sql_proc_set_error (cache, errmsg);
453 goto err;
454 }
455 len = strlen (xsql);
456 if (len == 0)
457 {
458 const char *errmsg = "Empty SQL body\n";
459 gaia_sql_proc_set_error (cache, errmsg);
460 goto err;
461 }
462 sql = sqlite3_malloc (len + 1);
463 strcpy (sql, xsql);
464
465 /* converting the SQL body to UTF-8 */
466 if (!gaiaConvertCharset (&sql, charset, "UTF-8"))
467 {
468 char *errmsg =
469 sqlite3_mprintf
470 ("Unable to convert the SQL body from %s to UTF-8\n", charset);
471 gaia_sql_proc_set_error (cache, errmsg);
472 sqlite3_free (errmsg);
473 goto err;
474 }
475
476 /* parsing the SQL body */
477 len = strlen (sql);
478 start_line = 1;
479 macro = 0;
480 comment = 0;
481 variable = 0;
482 list = alloc_var_list ();
483 for (i = 0; i < len; i++)
484 {
485 if (sql[i] == '\n')
486 {
487 /* EndOfLine found */
488 macro = 0;
489 comment = 0;
490 variable = 0;
491 start_line = 1;
492 continue;
493 }
494 if (start_line && (sql[i] == ' ' || sql[i] == '\t'))
495 {
496 /* skipping leading blanks */
497 continue;
498 }
499 if (start_line && sql[i] == '.')
500 macro = 1;
501 if (start_line && sql[i] == '-')
502 {
503 if (i < len - 1)
504 {
505 if (sql[i + 1] == '-')
506 comment = 1;
507 }
508 }
509 start_line = 0;
510 if (macro || comment)
511 continue;
512 if (sql[i] == '@' || sql[i] == '$')
513 {
514 if (variable && sql[i] == varMark)
515 {
516 /* a variable name ends here */
517 int sz = i - varStart;
518 int j;
519 int k;
520 char *varname = malloc (sz);
521 for (k = 0, j = varStart + 1; j < i; j++, k++)
522 *(varname + k) = sql[j];
523 *(varname + k) = '\0';
524 add_variable (list, varname);
525 variable = 0;
526 }
527 else
528 {
529 /* a variable name may start here */
530 variable = 1;
531 varMark = sql[i];
532 varStart = i;
533 }
534 }
535 }
536
537 /* computing the BLOB size */
538 stored_proc_sz = 13;
539 sql_len = strlen (sql);
540 stored_proc_sz += sql_len;
541 stored_proc_sz += var_list_required_size (list);
542
543 /* allocating the Stored Procedure BLOB object */
544 stored_proc = malloc (stored_proc_sz);
545 p_out = stored_proc;
546
547 /* preparing the Stored Procedure BLOB object */
548 *p_out++ = '\0';
549 *p_out++ = SQLPROC_START; /* START signature */
550 *p_out++ = GAIA_LITTLE_ENDIAN; /* byte ordering */
551 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
552 num_vars = var_list_count_items (list);
553 gaiaExport16 (p_out, num_vars, 1, endian_arch); /* Number of Variables */
554 p_out += 2;
555 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
556 item = list->first;
557 while (item != NULL)
558 {
559 len = strlen (item->varname);
560 gaiaExport16 (p_out, len, 1, endian_arch); /* Variable Name length */
561 p_out += 2;
562 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
563 memcpy (p_out, item->varname, len);
564 p_out += len;
565 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
566 gaiaExport16 (p_out, item->count, 1, endian_arch); /* Variable reference count */
567 p_out += 2;
568 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
569 item = item->next;
570 }
571 gaiaExport32 (p_out, sql_len, 1, endian_arch); /* SQL Body Length */
572 p_out += 4;
573 *p_out++ = SQLPROC_DELIM; /* DELIMITER signature */
574 memcpy (p_out, sql, sql_len);
575 p_out += sql_len;
576 *p_out = SQLPROC_STOP; /* STOP signature */
577
578 sqlite3_free (sql);
579 free_var_list (list);
580 *blob = stored_proc;
581 *blob_sz = stored_proc_sz;
582 return 1;
583
584 err:
585 if (sql != NULL)
586 sqlite3_free (sql);
587 if (list != NULL)
588 free_var_list (list);
589 *blob = NULL;
590 *blob_sz = 0;
591 return 0;
592
593 #endif /* ICONV conditional */
594
595 if (cache == NULL && xsql == NULL && charset == NULL)
596 cache = NULL; /* silencing stupid compiler warnings */
597
598 spatialite_e
599 ("gaia_sql_proc_parse: ICONV support was disabled in this build !!!\n");
600
601 *blob = NULL;
602 *blob_sz = 0;
603 return 0;
604 }
605
606 SQLPROC_DECLARE int
gaia_sql_proc_import(const void * cache,const char * filepath,const char * charset,unsigned char ** blob,int * blob_sz)607 gaia_sql_proc_import (const void *cache, const char *filepath,
608 const char *charset, unsigned char **blob, int *blob_sz)
609 {
610 /* attempting to import a Stored Procedure from an external File */
611 FILE *in = NULL;
612 size_t size;
613 char *sql = NULL;
614 stored_proc_reset_error (cache);
615
616 /* opening the input file */
617 #ifdef _WIN32
618 in = gaia_win_fopen (filepath, "rb");
619 #else
620 in = fopen (filepath, "rb");
621 #endif
622 if (in == NULL)
623 {
624 char *errmsg = sqlite3_mprintf ("Unable to open: %s\n", filepath);
625 gaia_sql_proc_set_error (cache, errmsg);
626 sqlite3_free (errmsg);
627 goto err;
628 }
629
630 /* determining the file size */
631 if (fseek (in, 0, SEEK_END) != 0)
632 {
633 char *errmsg =
634 sqlite3_mprintf ("Unable to read from: %s\n", filepath);
635 gaia_sql_proc_set_error (cache, errmsg);
636 sqlite3_free (errmsg);
637 goto err;
638 }
639 size = ftell (in);
640 rewind (in);
641
642 /* allocating and feeding the SQL body */
643 sql = malloc (size + 1);
644 if (fread (sql, 1, size, in) != size)
645 {
646 char *errmsg =
647 sqlite3_mprintf ("Unable to read from: %s\n", filepath);
648 gaia_sql_proc_set_error (cache, errmsg);
649 sqlite3_free (errmsg);
650 goto err;
651 }
652 *(sql + size) = '\0';
653
654 /* attempting to parse the SQL body */
655 if (!gaia_sql_proc_parse (cache, sql, charset, blob, blob_sz))
656 goto err;
657
658 free (sql);
659 fclose (in);
660 return 1;
661
662 err:
663 if (in != NULL)
664 fclose (in);
665 if (sql != NULL)
666 free (sql);
667 return 0;
668 }
669
670 SQLPROC_DECLARE int
gaia_sql_proc_is_valid(const unsigned char * blob,int blob_sz)671 gaia_sql_proc_is_valid (const unsigned char *blob, int blob_sz)
672 {
673 /* checking for a valid Stored Procedure BLOB Object */
674 const unsigned char *p_out = blob;
675 int endian;
676 int endian_arch = gaiaEndianArch ();
677 short size;
678 short num_vars;
679 short i_vars;
680 int len;
681 if (blob == NULL)
682 return 0;
683 if (blob_sz < 9)
684 return 0;
685
686 if (*p_out++ != '\0') /* first byte should alway be null */
687 return 0;
688 if (*p_out++ != SQLPROC_START) /* start marker */
689 return 0;
690 endian = *p_out++;
691 if (endian != GAIA_LITTLE_ENDIAN && endian != GAIA_BIG_ENDIAN) /* endianness */
692 return 0;
693 if (*p_out++ != SQLPROC_DELIM) /* delimiter marker */
694 return 0;
695 if ((p_out - blob) >= blob_sz)
696 return 0;
697 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
698 p_out += 2;
699 if ((p_out - blob) >= blob_sz)
700 return 0;
701 if (*p_out++ != SQLPROC_DELIM)
702 return 0;
703 for (i_vars = 0; i_vars < num_vars; i_vars++)
704 {
705 if ((p_out - blob) >= blob_sz)
706 return 0;
707 size = gaiaImport16 (p_out, endian, endian_arch); /* Variable Name Length */
708 p_out += 2;
709 if ((p_out - blob) >= blob_sz)
710 return 0;
711 if (*p_out++ != SQLPROC_DELIM)
712 return 0;
713 p_out += size; /* skipping the variable name */
714 if ((p_out - blob) >= blob_sz)
715 return 0;
716 if (*p_out++ != SQLPROC_DELIM)
717 return 0;
718 if ((p_out - blob) >= blob_sz)
719 return 0;
720 p_out += 2; /* Variable Name Length */
721 if ((p_out - blob) >= blob_sz)
722 return 0;
723 if (*p_out++ != SQLPROC_DELIM)
724 return 0;
725 }
726 if ((p_out - blob) >= blob_sz)
727 return 0;
728 len = gaiaImport32 (p_out, endian, endian_arch); /* SQL Body Length */
729 p_out += 4;
730 if ((p_out - blob) >= blob_sz)
731 return 0;
732 if (*p_out++ != SQLPROC_DELIM)
733 return 0;
734 p_out += len; /* skipping the SQL body */
735 if ((p_out - blob) >= blob_sz)
736 return 0;
737 if (*p_out != SQLPROC_STOP)
738 return 0;
739
740 return 1;
741 }
742
743 SQLPROC_DECLARE int
gaia_sql_proc_var_count(const unsigned char * blob,int blob_sz)744 gaia_sql_proc_var_count (const unsigned char *blob, int blob_sz)
745 {
746 /* return the total count of Variabiles from a Stored Procedure BLOB Object */
747 const unsigned char *p_out = blob;
748 int endian;
749 int endian_arch = gaiaEndianArch ();
750 short num_vars;
751 if (!gaia_sql_proc_is_valid (blob, blob_sz))
752 return 0;
753
754 p_out += 2;
755 endian = *p_out++;
756 p_out++;
757 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
758 return num_vars;
759 }
760
761 SQLPROC_DECLARE char *
gaia_sql_proc_variable(const unsigned char * blob,int blob_sz,int index)762 gaia_sql_proc_variable (const unsigned char *blob, int blob_sz, int index)
763 {
764 /* return the Name of the Nth Variabile from a Stored Procedure BLOB Object */
765 const unsigned char *p_out = blob;
766 int endian;
767 int endian_arch = gaiaEndianArch ();
768 short size;
769 short num_vars;
770 short i_vars;
771 if (!gaia_sql_proc_is_valid (blob, blob_sz))
772 return NULL;
773 if (index < 0)
774 return NULL;
775
776 p_out += 2;
777 endian = *p_out++;
778 p_out++;
779 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
780 p_out += 3;
781 for (i_vars = 0; i_vars < num_vars; i_vars++)
782 {
783 size = gaiaImport16 (p_out, endian, endian_arch); /* Variable Name Length */
784 p_out += 3;
785 if (i_vars == index)
786 {
787 char *varname = malloc (size + 3);
788 *varname = '@';
789 memcpy (varname + 1, p_out, size);
790 *(varname + size + 1) = '@';
791 *(varname + size + 2) = '\0';
792 return varname;
793 }
794 p_out += size; /* skipping the variable name */
795 p_out++;
796 p_out += 3; /* skipping the reference count */
797 }
798 return NULL;
799 }
800
801 SQLPROC_DECLARE char *
gaia_sql_proc_all_variables(const unsigned char * blob,int blob_sz)802 gaia_sql_proc_all_variables (const unsigned char *blob, int blob_sz)
803 {
804 /* return a comma separated list of Variable Names from a Stored Procedure BLOB Object */
805 const unsigned char *p_out = blob;
806 int endian;
807 int endian_arch = gaiaEndianArch ();
808 short size;
809 short num_vars;
810 short i_vars;
811 char *varname;
812 char *varlist = NULL;
813 if (!gaia_sql_proc_is_valid (blob, blob_sz))
814 return NULL;
815
816 p_out += 2;
817 endian = *p_out++;
818 p_out++;
819 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
820 p_out += 3;
821 for (i_vars = 0; i_vars < num_vars; i_vars++)
822 {
823 size = gaiaImport16 (p_out, endian, endian_arch); /* Variable Name Length */
824 p_out += 3;
825 varname = malloc (size + 3);
826 *varname = '@';
827 memcpy (varname + 1, p_out, size);
828 *(varname + size + 1) = '@';
829 *(varname + size + 2) = '\0';
830 if (varlist == NULL)
831 varlist = sqlite3_mprintf ("%s", varname);
832 else
833 {
834 char *prev = varlist;
835 varlist = sqlite3_mprintf ("%s %s", prev, varname);
836 sqlite3_free (prev);
837 }
838 free (varname);
839 p_out += size; /* skipping the variable name */
840 p_out++;
841 p_out += 3; /* skipping the reference count */
842 }
843 return varlist;
844 }
845
846 SQLPROC_DECLARE char *
gaia_sql_proc_raw_sql(const unsigned char * blob,int blob_sz)847 gaia_sql_proc_raw_sql (const unsigned char *blob, int blob_sz)
848 {
849 /* return the raw SQL body from a Stored Procedure BLOB Object */
850 const unsigned char *p_out = blob;
851 int endian;
852 int endian_arch = gaiaEndianArch ();
853 short size;
854 int len;
855 short num_vars;
856 short i_vars;
857 char *sql = NULL;
858 if (!gaia_sql_proc_is_valid (blob, blob_sz))
859 return NULL;
860
861 p_out += 2;
862 endian = *p_out++;
863 p_out++;
864 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
865 p_out += 3;
866 for (i_vars = 0; i_vars < num_vars; i_vars++)
867 {
868 size = gaiaImport16 (p_out, endian, endian_arch); /* Variable Name Length */
869 p_out += 3;
870 p_out += size; /* skipping the variable name */
871 p_out++;
872 p_out += 3; /* skipping the reference count */
873 }
874 len = gaiaImport32 (p_out, endian, endian_arch); /* SQL Body Length */
875 p_out += 5;
876 sql = malloc (len + 1);
877 memcpy (sql, p_out, len);
878 *(sql + len) = '\0';
879 return sql;
880 }
881
882 static struct sp_var_list *
build_var_list(const unsigned char * blob,int blob_sz)883 build_var_list (const unsigned char *blob, int blob_sz)
884 {
885 /* building a list of Variables with Values and reference count */
886 const unsigned char *p_out = blob;
887 int endian;
888 int endian_arch = gaiaEndianArch ();
889 short size;
890 short ref_count;
891 short num_vars;
892 short i_vars;
893 char *varname;
894 struct sp_var_list *list = NULL;
895 if (!gaia_sql_proc_is_valid (blob, blob_sz))
896 return NULL;
897
898 list = alloc_var_list ();
899 p_out += 2;
900 endian = *p_out++;
901 p_out++;
902 num_vars = gaiaImport16 (p_out, endian, endian_arch); /* Variables Count */
903 p_out += 3;
904 for (i_vars = 0; i_vars < num_vars; i_vars++)
905 {
906 size = gaiaImport16 (p_out, endian, endian_arch); /* Variable Name Length */
907 p_out += 3;
908 varname = malloc (size + 1);
909 memcpy (varname, p_out, size);
910 *(varname + size) = '\0';
911 p_out += size; /* skipping the variable name */
912 p_out++;
913 ref_count = gaiaImport16 (p_out, endian, endian_arch); /* Variable reference count */
914 p_out += 3; /* skipping the reference count */
915 add_variable_ex (list, varname, ref_count);
916 }
917 return list;
918 }
919
920 static const char *
search_replacement_value(SqlProc_VarListPtr variables,const char * varname)921 search_replacement_value (SqlProc_VarListPtr variables, const char *varname)
922 {
923 /* searching a Variable replacement value (if any) */
924 SqlProc_VariablePtr var = variables->First;
925 while (var != NULL)
926 {
927 if (strcasecmp (var->Name, varname) == 0)
928 {
929 /* found a replacement value */
930 return var->Value;
931 }
932 var = var->Next;
933 }
934 return NULL;
935 }
936
937 static char *
search_stored_var(sqlite3 * handle,const char * varname)938 search_stored_var (sqlite3 * handle, const char *varname)
939 {
940 /* searching a Stored Variable */
941 const char *sql;
942 sqlite3_stmt *stmt = NULL;
943 int ret;
944 char *var_with_value = NULL;
945
946 sql = "SELECT value FROM stored_variables WHERE name = ?";
947 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
948 if (ret != SQLITE_OK)
949 return NULL;
950
951 sqlite3_reset (stmt);
952 sqlite3_clear_bindings (stmt);
953 sqlite3_bind_text (stmt, 1, varname, strlen (varname), SQLITE_STATIC);
954 while (1)
955 {
956 /* scrolling the result set rows */
957 ret = sqlite3_step (stmt);
958 if (ret == SQLITE_DONE)
959 break; /* end of result set */
960 if (ret == SQLITE_ROW)
961 {
962 if (sqlite3_column_type (stmt, 0) == SQLITE_TEXT)
963 {
964 const char *data =
965 (const char *) sqlite3_column_text (stmt, 0);
966 var_with_value = sqlite3_mprintf ("%s", data);
967 }
968 }
969 }
970 sqlite3_finalize (stmt);
971 return var_with_value;
972 }
973
974 static int
get_value_length(sqlite3 * handle,SqlProc_VarListPtr variables,const char * varname)975 get_value_length (sqlite3 * handle, SqlProc_VarListPtr variables,
976 const char *varname)
977 {
978 /* retieving a Variable replacement Value length */
979 char *stored_var;
980 SqlProc_VariablePtr var = variables->First;
981 while (var != NULL)
982 {
983 if (strcasecmp (var->Name, varname) == 0)
984 {
985 /* found a replacement value */
986 return strlen (var->Value);
987 }
988 var = var->Next;
989 }
990
991 /* attempting to get a Stored Variable */
992 stored_var = search_stored_var (handle, varname);
993 if (stored_var != NULL)
994 {
995 int len = strlen (stored_var);
996 sqlite3_free (stored_var);
997 return len;
998 }
999 return 4; /* undefined; defaults to NULL */
1000 }
1001
1002 SQLPROC_DECLARE int
gaia_sql_proc_cooked_sql(sqlite3 * handle,const void * cache,const unsigned char * blob,int blob_sz,SqlProc_VarListPtr variables,char ** sql)1003 gaia_sql_proc_cooked_sql (sqlite3 * handle, const void *cache,
1004 const unsigned char *blob, int blob_sz,
1005 SqlProc_VarListPtr variables, char **sql)
1006 {
1007 /* return the cooked SQL body from a raw SQL body by replacing Variable Values */
1008 int len;
1009 int i;
1010 int start_line;
1011 int macro;
1012 int comment;
1013 int variable;
1014 char varMark;
1015 int varStart;
1016 char *raw = NULL;
1017 char *cooked = NULL;
1018 char *p_out;
1019 int buf_size;
1020 struct sp_var_list *list = NULL;
1021 struct sp_var_item *item;
1022 stored_proc_reset_error (cache);
1023
1024 *sql = NULL;
1025 if (variables == NULL)
1026 {
1027 const char *errmsg = "NULL Variables List (Arguments)\n";
1028 gaia_sql_proc_set_error (cache, errmsg);
1029 goto err;
1030 }
1031
1032 /* retrieving the Raw SQL Body */
1033 raw = gaia_sql_proc_raw_sql (blob, blob_sz);
1034 if (raw == NULL)
1035 {
1036 const char *errmsg = "NULL Raw SQL body\n";
1037 gaia_sql_proc_set_error (cache, errmsg);
1038 goto err;
1039 }
1040 len = strlen (raw);
1041 if (len == 0)
1042 {
1043 const char *errmsg = "Empty Raw SQL body\n";
1044 gaia_sql_proc_set_error (cache, errmsg);
1045 goto err;
1046 }
1047
1048 /* building the Variables List from the BLOB */
1049 list = build_var_list (blob, blob_sz);
1050 if (list == NULL)
1051 {
1052 const char *errmsg = "NULL Variables List (Raw SQL)\n";
1053 gaia_sql_proc_set_error (cache, errmsg);
1054 goto err;
1055 }
1056
1057 /* allocating the Cooked buffer */
1058 buf_size = strlen (raw);
1059 item = list->first;
1060 while (item != NULL)
1061 {
1062 int value_len = get_value_length (handle, variables, item->varname);
1063 buf_size -= (strlen (item->varname) + 2) * item->count;
1064 buf_size += value_len * item->count;
1065 item = item->next;
1066 }
1067 cooked = malloc (buf_size + 1);
1068 p_out = cooked;
1069
1070 /* parsing the Raw SQL body */
1071 start_line = 1;
1072 macro = 0;
1073 comment = 0;
1074 variable = 0;
1075 for (i = 0; i < len; i++)
1076 {
1077 if (raw[i] == '\n')
1078 {
1079 /* EndOfLine found */
1080 macro = 0;
1081 comment = 0;
1082 variable = 0;
1083 start_line = 1;
1084 *p_out++ = raw[i];
1085 continue;
1086 }
1087 if (start_line && (raw[i] == ' ' || raw[i] == '\t'))
1088 {
1089 /* skipping leading blanks */
1090 *p_out++ = raw[i];
1091 continue;
1092 }
1093 if (start_line && raw[i] == '.')
1094 macro = 1;
1095 if (start_line && raw[i] == '-')
1096 {
1097 if (i < len - 1)
1098 {
1099 if (raw[i + 1] == '-')
1100 comment = 1;
1101 }
1102 }
1103 start_line = 0;
1104 if (macro || comment)
1105 {
1106 *p_out++ = raw[i];
1107 continue;
1108 }
1109 if (raw[i] == '@' || raw[i] == '$')
1110 {
1111 if (variable && raw[i] == varMark)
1112 {
1113 /* a variable name ends here */
1114 int sz = i - varStart;
1115 int j;
1116 int k;
1117 char *stored_var = NULL;
1118 const char *replacement_value;
1119 char *varname = malloc (sz);
1120 for (k = 0, j = varStart + 1; j < i; j++, k++)
1121 *(varname + k) = raw[j];
1122 *(varname + k) = '\0';
1123 replacement_value =
1124 search_replacement_value (variables, varname);
1125 if (replacement_value == NULL)
1126 {
1127 /* attempting to get a Stored Variable */
1128 stored_var = search_stored_var (handle, varname);
1129 replacement_value = stored_var;
1130 }
1131 free (varname);
1132 if (replacement_value == NULL)
1133 replacement_value = "NULL";
1134 for (k = 0; k < (int) strlen (replacement_value); k++)
1135 *p_out++ = replacement_value[k];
1136 if (stored_var != NULL)
1137 sqlite3_free (stored_var);
1138 variable = 0;
1139 }
1140 else
1141 {
1142 /* a variable name may start here */
1143 variable = 1;
1144 varMark = raw[i];
1145 varStart = i;
1146 }
1147 continue;
1148 }
1149 if (!variable)
1150 *p_out++ = raw[i];
1151 }
1152 *p_out = '\0';
1153
1154 free (raw);
1155 free_var_list (list);
1156 *sql = cooked;
1157 return 1;
1158
1159 err:
1160 if (raw != NULL)
1161 free (raw);
1162 if (cooked != NULL)
1163 free (cooked);
1164 if (list != NULL)
1165 free_var_list (list);
1166 return 0;
1167 }
1168
1169 static int
test_stored_proc_tables(sqlite3 * handle)1170 test_stored_proc_tables (sqlite3 * handle)
1171 {
1172 /* testing if the Stored Procedures Tables are already defined */
1173 int ok_name = 0;
1174 int ok_title = 0;
1175 int ok_sql_proc = 0;
1176 int ok_var_value = 0;
1177 char sql[1024];
1178 int ret;
1179 const char *name;
1180 int i;
1181 char **results;
1182 int rows;
1183 int columns;
1184
1185 /* checking the STORED_PROCEDURES table */
1186 strcpy (sql, "PRAGMA table_info(stored_procedures)");
1187 ret = sqlite3_get_table (handle, sql, &results, &rows, &columns, NULL);
1188 if (ret != SQLITE_OK)
1189 return 0;
1190 if (rows < 1)
1191 ;
1192 else
1193 {
1194 for (i = 1; i <= rows; i++)
1195 {
1196 name = results[(i * columns) + 1];
1197 if (strcasecmp (name, "name") == 0)
1198 ok_name = 1;
1199 if (strcasecmp (name, "title") == 0)
1200 ok_title = 1;
1201 if (strcasecmp (name, "sql_proc") == 0)
1202 ok_sql_proc = 1;
1203 }
1204 }
1205 sqlite3_free_table (results);
1206 if (ok_name && ok_title && ok_sql_proc)
1207 ;
1208 else
1209 return 0;
1210
1211 /* checking the STORED_PROCEDURES table */
1212 ok_name = 0;
1213 ok_title = 0;
1214 strcpy (sql, "PRAGMA table_info(stored_variables)");
1215 ret = sqlite3_get_table (handle, sql, &results, &rows, &columns, NULL);
1216 if (ret != SQLITE_OK)
1217 return 0;
1218 if (rows < 1)
1219 ;
1220 else
1221 {
1222 for (i = 1; i <= rows; i++)
1223 {
1224 name = results[(i * columns) + 1];
1225 if (strcasecmp (name, "name") == 0)
1226 ok_name = 1;
1227 if (strcasecmp (name, "title") == 0)
1228 ok_title = 1;
1229 if (strcasecmp (name, "value") == 0)
1230 ok_var_value = 1;
1231 }
1232 }
1233 sqlite3_free_table (results);
1234 if (ok_name && ok_title && ok_var_value)
1235 return 1;
1236 return 0;
1237 }
1238
1239 SQLPROC_DECLARE int
gaia_stored_proc_create_tables(sqlite3 * handle,const void * cache)1240 gaia_stored_proc_create_tables (sqlite3 * handle, const void *cache)
1241 {
1242 /* attempts to create the Stored Procedures Tables */
1243 char sql[4192];
1244 char *errMsg = NULL;
1245 int ret;
1246
1247 if (test_stored_proc_tables (handle))
1248 return 1;
1249 stored_proc_reset_error (cache);
1250
1251 /* creating the STORED_PROCEDURES table */
1252 strcpy (sql, "CREATE TABLE IF NOT EXISTS ");
1253 strcat (sql, "stored_procedures (\n");
1254 strcat (sql, "name TEXT NOT NULL PRIMARY KEY,\n");
1255 strcat (sql, "title TEXT NOT NULL,\n");
1256 strcat (sql, "sql_proc BLOB NOT NULL)");
1257 ret = sqlite3_exec (handle, sql, NULL, NULL, &errMsg);
1258 if (ret != SQLITE_OK)
1259 {
1260 char *errmsg =
1261 sqlite3_mprintf ("gaia_stored_create \"stored_procedures\": %s",
1262 sqlite3_errmsg (handle));
1263 gaia_sql_proc_set_error (cache, errmsg);
1264 sqlite3_free (errmsg);
1265 return 0;
1266 }
1267
1268 /* creating Triggers supporting STORED_PROCEDURES */
1269 sprintf (sql,
1270 "CREATE TRIGGER IF NOT EXISTS storproc_ins BEFORE INSERT ON stored_procedures\n"
1271 "FOR EACH ROW BEGIN\n"
1272 "SELECT RAISE(ROLLBACK, 'Invalid \"sql_proc\": not a BLOB of the SQL Procedure type')\n"
1273 "WHERE SqlProc_IsValid(NEW.sql_proc) <> 1;\nEND");
1274 ret = sqlite3_exec (handle, sql, NULL, NULL, &errMsg);
1275 if (ret != SQLITE_OK)
1276 {
1277 char *errmsg =
1278 sqlite3_mprintf ("gaia_stored_create \"storproc_ins\": %s",
1279 sqlite3_errmsg (handle));
1280 gaia_sql_proc_set_error (cache, errmsg);
1281 sqlite3_free (errmsg);
1282 return 0;
1283 }
1284 sprintf (sql,
1285 "CREATE TRIGGER IF NOT EXISTS storproc_upd BEFORE UPDATE OF sql_proc ON stored_procedures\n"
1286 "FOR EACH ROW BEGIN\n"
1287 "SELECT RAISE(ROLLBACK, 'Invalid \"sql_proc\": not a BLOB of the SQL Procedure type')\n"
1288 "WHERE SqlProc_IsValid(NEW.sql_proc) <> 1;\nEND");
1289 ret = sqlite3_exec (handle, sql, NULL, NULL, &errMsg);
1290 if (ret != SQLITE_OK)
1291 {
1292 char *errmsg =
1293 sqlite3_mprintf ("gaia_stored_create \"storproc_upd\": %s",
1294 sqlite3_errmsg (handle));
1295 gaia_sql_proc_set_error (cache, errmsg);
1296 sqlite3_free (errmsg);
1297 return 0;
1298 }
1299
1300 /* creating the STORED_VALUES table */
1301 strcpy (sql, "CREATE TABLE IF NOT EXISTS ");
1302 strcat (sql, "stored_variables (\n");
1303 strcat (sql, "name TEXT NOT NULL PRIMARY KEY,\n");
1304 strcat (sql, "title TEXT NOT NULL,\n");
1305 strcat (sql, "value TEXT NOT NULL)");
1306 ret = sqlite3_exec (handle, sql, NULL, NULL, &errMsg);
1307 if (ret != SQLITE_OK)
1308 {
1309 char *errmsg =
1310 sqlite3_mprintf ("gaia_stored_create \"stored_variables\": %s",
1311 sqlite3_errmsg (handle));
1312 gaia_sql_proc_set_error (cache, errmsg);
1313 sqlite3_free (errmsg);
1314 return 0;
1315 }
1316
1317 if (test_stored_proc_tables (handle))
1318 return 1;
1319 return 0;
1320 }
1321
1322 SQLPROC_DECLARE int
gaia_stored_proc_store(sqlite3 * handle,const void * cache,const char * name,const char * title,const unsigned char * blob,int blob_sz)1323 gaia_stored_proc_store (sqlite3 * handle, const void *cache, const char *name,
1324 const char *title, const unsigned char *blob,
1325 int blob_sz)
1326 {
1327 /* permanently registering a Stored Procedure */
1328 const char *sql;
1329 sqlite3_stmt *stmt;
1330 int ret;
1331 stored_proc_reset_error (cache);
1332
1333 sql =
1334 "INSERT INTO stored_procedures(name, title, sql_proc) VALUES (?, ?, ?)";
1335 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1336 if (ret != SQLITE_OK)
1337 {
1338 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_store: %s",
1339 sqlite3_errmsg (handle));
1340 gaia_sql_proc_set_error (cache, errmsg);
1341 sqlite3_free (errmsg);
1342 return 0;
1343 }
1344
1345 sqlite3_reset (stmt);
1346 sqlite3_clear_bindings (stmt);
1347 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1348 sqlite3_bind_text (stmt, 2, title, strlen (title), SQLITE_STATIC);
1349 sqlite3_bind_blob (stmt, 3, blob, blob_sz, SQLITE_STATIC);
1350 ret = sqlite3_step (stmt);
1351 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1352 ;
1353 else
1354 {
1355 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_store: %s",
1356 sqlite3_errmsg (handle));
1357 gaia_sql_proc_set_error (cache, errmsg);
1358 sqlite3_free (errmsg);
1359 sqlite3_finalize (stmt);
1360 return 0;
1361 }
1362 sqlite3_finalize (stmt);
1363 return 1;
1364 }
1365
1366 SQLPROC_DECLARE int
gaia_stored_proc_fetch(sqlite3 * handle,const void * cache,const char * name,unsigned char ** blob,int * blob_sz)1367 gaia_stored_proc_fetch (sqlite3 * handle, const void *cache, const char *name,
1368 unsigned char **blob, int *blob_sz)
1369 {
1370 /* will return the SQL Procedure BLOB from a given Stored Procedure */
1371 const char *sql;
1372 sqlite3_stmt *stmt;
1373 int ret;
1374 unsigned char *p_blob = NULL;
1375 int p_blob_sz = 0;
1376 stored_proc_reset_error (cache);
1377
1378 sql = "SELECT sql_proc FROM stored_procedures WHERE name = ?";
1379 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1380 if (ret != SQLITE_OK)
1381 {
1382 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_fetch: %s",
1383 sqlite3_errmsg (handle));
1384 gaia_sql_proc_set_error (cache, errmsg);
1385 sqlite3_free (errmsg);
1386 return 0;
1387 }
1388
1389 sqlite3_reset (stmt);
1390 sqlite3_clear_bindings (stmt);
1391 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1392 while (1)
1393 {
1394 /* scrolling the result set rows */
1395 ret = sqlite3_step (stmt);
1396 if (ret == SQLITE_DONE)
1397 break; /* end of result set */
1398 if (ret == SQLITE_ROW)
1399 {
1400 if (sqlite3_column_type (stmt, 0) == SQLITE_BLOB)
1401 {
1402 const unsigned char *data = sqlite3_column_blob (stmt, 0);
1403 p_blob_sz = sqlite3_column_bytes (stmt, 0);
1404 p_blob = malloc (p_blob_sz);
1405 memcpy (p_blob, data, p_blob_sz);
1406 }
1407 }
1408 }
1409 sqlite3_finalize (stmt);
1410
1411 *blob = p_blob;
1412 *blob_sz = p_blob_sz;
1413 if (p_blob == NULL)
1414 return 0;
1415 return 1;
1416 }
1417
1418 SQLPROC_DECLARE int
gaia_stored_proc_delete(sqlite3 * handle,const void * cache,const char * name)1419 gaia_stored_proc_delete (sqlite3 * handle, const void *cache, const char *name)
1420 {
1421 /* removing a permanently registered Stored Procedure */
1422 const char *sql;
1423 sqlite3_stmt *stmt;
1424 int ret;
1425 stored_proc_reset_error (cache);
1426
1427 sql = "DELETE FROM stored_procedures WHERE name = ?";
1428 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1429 if (ret != SQLITE_OK)
1430 {
1431 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_delete: %s",
1432 sqlite3_errmsg (handle));
1433 gaia_sql_proc_set_error (cache, errmsg);
1434 sqlite3_free (errmsg);
1435 return 0;
1436 }
1437
1438 sqlite3_reset (stmt);
1439 sqlite3_clear_bindings (stmt);
1440 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1441 ret = sqlite3_step (stmt);
1442 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1443 ;
1444 else
1445 {
1446 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_delete: %s",
1447 sqlite3_errmsg (handle));
1448 gaia_sql_proc_set_error (cache, errmsg);
1449 sqlite3_free (errmsg);
1450 sqlite3_finalize (stmt);
1451 return 0;
1452 }
1453 sqlite3_finalize (stmt);
1454 if (sqlite3_changes (handle) == 0)
1455 return 0;
1456 return 1;
1457 }
1458
1459 SQLPROC_DECLARE int
gaia_stored_proc_update_title(sqlite3 * handle,const void * cache,const char * name,const char * title)1460 gaia_stored_proc_update_title (sqlite3 * handle, const void *cache,
1461 const char *name, const char *title)
1462 {
1463 /* updating a permanently registered Stored Procedure - Title */
1464 const char *sql;
1465 sqlite3_stmt *stmt;
1466 int ret;
1467 stored_proc_reset_error (cache);
1468
1469 sql = "UPDATE stored_procedures SET title = ? WHERE name = ?";
1470 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1471 if (ret != SQLITE_OK)
1472 {
1473 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_update_title: %s",
1474 sqlite3_errmsg (handle));
1475 gaia_sql_proc_set_error (cache, errmsg);
1476 sqlite3_free (errmsg);
1477 return 0;
1478 }
1479
1480 sqlite3_reset (stmt);
1481 sqlite3_clear_bindings (stmt);
1482 sqlite3_bind_text (stmt, 1, title, strlen (title), SQLITE_STATIC);
1483 sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC);
1484 ret = sqlite3_step (stmt);
1485 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1486 ;
1487 else
1488 {
1489 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_update_title: %s",
1490 sqlite3_errmsg (handle));
1491 gaia_sql_proc_set_error (cache, errmsg);
1492 sqlite3_free (errmsg);
1493 sqlite3_finalize (stmt);
1494 return 0;
1495 }
1496 sqlite3_finalize (stmt);
1497 if (sqlite3_changes (handle) == 0)
1498 return 0;
1499 return 1;
1500 }
1501
1502 SQLPROC_DECLARE int
gaia_stored_proc_update_sql(sqlite3 * handle,const void * cache,const char * name,const unsigned char * blob,int blob_sz)1503 gaia_stored_proc_update_sql (sqlite3 * handle, const void *cache,
1504 const char *name, const unsigned char *blob,
1505 int blob_sz)
1506 {
1507 /* updating a permanently registered Stored Procedure - SQL body */
1508 const char *sql;
1509 sqlite3_stmt *stmt;
1510 int ret;
1511 stored_proc_reset_error (cache);
1512
1513 sql = "UPDATE stored_procedures SET sql_proc = ? WHERE name = ?";
1514 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1515 if (ret != SQLITE_OK)
1516 {
1517 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_update_sql: %s",
1518 sqlite3_errmsg (handle));
1519 gaia_sql_proc_set_error (cache, errmsg);
1520 sqlite3_free (errmsg);
1521 return 0;
1522 }
1523
1524 sqlite3_reset (stmt);
1525 sqlite3_clear_bindings (stmt);
1526 sqlite3_bind_blob (stmt, 1, blob, blob_sz, SQLITE_STATIC);
1527 sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC);
1528 ret = sqlite3_step (stmt);
1529 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1530 ;
1531 else
1532 {
1533 char *errmsg = sqlite3_mprintf ("gaia_stored_proc_update_sql: %s",
1534 sqlite3_errmsg (handle));
1535 gaia_sql_proc_set_error (cache, errmsg);
1536 sqlite3_free (errmsg);
1537 sqlite3_finalize (stmt);
1538 return 0;
1539 }
1540 sqlite3_finalize (stmt);
1541 if (sqlite3_changes (handle) == 0)
1542 return 0;
1543 return 1;
1544 }
1545
1546 SQLPROC_DECLARE int
gaia_stored_var_store(sqlite3 * handle,const void * cache,const char * name,const char * title,const char * value)1547 gaia_stored_var_store (sqlite3 * handle, const void *cache, const char *name,
1548 const char *title, const char *value)
1549 {
1550 /* permanently registering a Stored Variable */
1551 const char *sql;
1552 sqlite3_stmt *stmt;
1553 int ret;
1554 stored_proc_reset_error (cache);
1555
1556 sql = "INSERT INTO stored_variables(name, title, value) VALUES (?, ?, ?)";
1557 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1558 if (ret != SQLITE_OK)
1559 {
1560 char *errmsg = sqlite3_mprintf ("gaia_stored_var_store: %s",
1561 sqlite3_errmsg (handle));
1562 gaia_sql_proc_set_error (cache, errmsg);
1563 sqlite3_free (errmsg);
1564 return 0;
1565 }
1566
1567 sqlite3_reset (stmt);
1568 sqlite3_clear_bindings (stmt);
1569 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1570 sqlite3_bind_text (stmt, 2, title, strlen (title), SQLITE_STATIC);
1571 sqlite3_bind_text (stmt, 3, value, strlen (value), SQLITE_STATIC);
1572 ret = sqlite3_step (stmt);
1573 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1574 ;
1575 else
1576 {
1577 char *errmsg = sqlite3_mprintf ("gaia_stored_var_store: %s",
1578 sqlite3_errmsg (handle));
1579 gaia_sql_proc_set_error (cache, errmsg);
1580 sqlite3_free (errmsg);
1581 sqlite3_finalize (stmt);
1582 return 0;
1583 }
1584 sqlite3_finalize (stmt);
1585 return 1;
1586 }
1587
1588 SQLPROC_DECLARE int
gaia_stored_var_fetch(sqlite3 * handle,const void * cache,const char * name,int variable_with_value,char ** value)1589 gaia_stored_var_fetch (sqlite3 * handle, const void *cache, const char *name,
1590 int variable_with_value, char **value)
1591 {
1592 /* will return a Variable with Value string from a given Stored Variable */
1593 const char *sql;
1594 sqlite3_stmt *stmt;
1595 int ret;
1596 char *p_value = NULL;
1597 stored_proc_reset_error (cache);
1598
1599 sql = "SELECT value FROM stored_variables WHERE name = ?";
1600 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1601 if (ret != SQLITE_OK)
1602 {
1603 char *errmsg = sqlite3_mprintf ("gaia_stored_var_fetch: %s",
1604 sqlite3_errmsg (handle));
1605 gaia_sql_proc_set_error (cache, errmsg);
1606 sqlite3_free (errmsg);
1607 return 0;
1608 }
1609
1610 sqlite3_reset (stmt);
1611 sqlite3_clear_bindings (stmt);
1612 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1613 while (1)
1614 {
1615 /* scrolling the result set rows */
1616 ret = sqlite3_step (stmt);
1617 if (ret == SQLITE_DONE)
1618 break; /* end of result set */
1619 if (ret == SQLITE_ROW)
1620 {
1621 if (sqlite3_column_type (stmt, 0) == SQLITE_TEXT)
1622 {
1623 const char *data =
1624 (const char *) sqlite3_column_text (stmt, 0);
1625 char *var_with_val;
1626 if (variable_with_value)
1627 {
1628 /* returning a Variable with Value string */
1629 var_with_val =
1630 sqlite3_mprintf ("@%s@=%s", name, data);
1631 }
1632 else
1633 {
1634 /* just returning the bare Value alone */
1635 var_with_val = sqlite3_mprintf ("%s", data);
1636 }
1637 p_value = malloc (strlen (var_with_val) + 1);
1638 strcpy (p_value, var_with_val);
1639 sqlite3_free (var_with_val);
1640 }
1641 }
1642 }
1643 sqlite3_finalize (stmt);
1644
1645 *value = p_value;;
1646 if (p_value == NULL)
1647 return 0;
1648 return 1;
1649 }
1650
1651 SQLPROC_DECLARE int
gaia_stored_var_delete(sqlite3 * handle,const void * cache,const char * name)1652 gaia_stored_var_delete (sqlite3 * handle, const void *cache, const char *name)
1653 {
1654 /* removing a permanently registered Stored Variable */
1655 const char *sql;
1656 sqlite3_stmt *stmt;
1657 int ret;
1658 stored_proc_reset_error (cache);
1659
1660 sql = "DELETE FROM stored_variables WHERE name = ?";
1661 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1662 if (ret != SQLITE_OK)
1663 {
1664 char *errmsg = sqlite3_mprintf ("gaia_stored_var_delete: %s",
1665 sqlite3_errmsg (handle));
1666 gaia_sql_proc_set_error (cache, errmsg);
1667 sqlite3_free (errmsg);
1668 return 0;
1669 }
1670
1671 sqlite3_reset (stmt);
1672 sqlite3_clear_bindings (stmt);
1673 sqlite3_bind_text (stmt, 1, name, strlen (name), SQLITE_STATIC);
1674 ret = sqlite3_step (stmt);
1675 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1676 ;
1677 else
1678 {
1679 char *errmsg = sqlite3_mprintf ("gaia_stored_var_delete: %s",
1680 sqlite3_errmsg (handle));
1681 gaia_sql_proc_set_error (cache, errmsg);
1682 sqlite3_free (errmsg);
1683 sqlite3_finalize (stmt);
1684 return 0;
1685 }
1686 sqlite3_finalize (stmt);
1687 if (sqlite3_changes (handle) == 0)
1688 return 0;
1689 return 1;
1690 }
1691
1692 SQLPROC_DECLARE int
gaia_stored_var_update_title(sqlite3 * handle,const void * cache,const char * name,const char * title)1693 gaia_stored_var_update_title (sqlite3 * handle, const void *cache,
1694 const char *name, const char *title)
1695 {
1696 /* updating a permanently registered Stored Variable - Title */
1697 const char *sql;
1698 sqlite3_stmt *stmt;
1699 int ret;
1700 stored_proc_reset_error (cache);
1701
1702 sql = "UPDATE stored_variables SET title = ? WHERE name = ?";
1703 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1704 if (ret != SQLITE_OK)
1705 {
1706 char *errmsg = sqlite3_mprintf ("gaia_stored_var_update_title: %s",
1707 sqlite3_errmsg (handle));
1708 gaia_sql_proc_set_error (cache, errmsg);
1709 sqlite3_free (errmsg);
1710 return 0;
1711 }
1712
1713 sqlite3_reset (stmt);
1714 sqlite3_clear_bindings (stmt);
1715 sqlite3_bind_text (stmt, 1, title, strlen (title), SQLITE_STATIC);
1716 sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC);
1717 ret = sqlite3_step (stmt);
1718 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1719 ;
1720 else
1721 {
1722 char *errmsg = sqlite3_mprintf ("gaia_stored_var_update_title: %s",
1723 sqlite3_errmsg (handle));
1724 gaia_sql_proc_set_error (cache, errmsg);
1725 sqlite3_free (errmsg);
1726 sqlite3_finalize (stmt);
1727 return 0;
1728 }
1729 sqlite3_finalize (stmt);
1730 if (sqlite3_changes (handle) == 0)
1731 return 0;
1732 return 1;
1733 }
1734
1735 SQLPROC_DECLARE int
gaia_stored_var_update_value(sqlite3 * handle,const void * cache,const char * name,const char * value)1736 gaia_stored_var_update_value (sqlite3 * handle, const void *cache,
1737 const char *name, const char *value)
1738 {
1739 /* updating a permanently registered Stored Variable - Variable with Value */
1740 const char *sql;
1741 sqlite3_stmt *stmt;
1742 int ret;
1743 stored_proc_reset_error (cache);
1744
1745 sql = "UPDATE stored_variables SET value = ? WHERE name = ?";
1746 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
1747 if (ret != SQLITE_OK)
1748 {
1749 char *errmsg = sqlite3_mprintf ("gaia_stored_var_update_value: %s",
1750 sqlite3_errmsg (handle));
1751 gaia_sql_proc_set_error (cache, errmsg);
1752 sqlite3_free (errmsg);
1753 return 0;
1754 }
1755
1756 sqlite3_reset (stmt);
1757 sqlite3_clear_bindings (stmt);
1758 sqlite3_bind_text (stmt, 1, value, strlen (value), SQLITE_STATIC);
1759 sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC);
1760 ret = sqlite3_step (stmt);
1761 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1762 ;
1763 else
1764 {
1765 char *errmsg = sqlite3_mprintf ("gaia_stored_var_update_value: %s",
1766 sqlite3_errmsg (handle));
1767 gaia_sql_proc_set_error (cache, errmsg);
1768 sqlite3_free (errmsg);
1769 sqlite3_finalize (stmt);
1770 return 0;
1771 }
1772 sqlite3_finalize (stmt);
1773 if (sqlite3_changes (handle) == 0)
1774 return 0;
1775 return 1;
1776 }
1777
1778 static const char *
consume_empty_sql(const char * ptr)1779 consume_empty_sql (const char *ptr)
1780 {
1781 /* testing for an empty SQL string */
1782 int comment_marker = 0;
1783 const char *p = ptr;
1784 while (1)
1785 {
1786 char c = *p;
1787 if (c == '\0')
1788 break;
1789 if (c == ' ' || c == '\0' || c == '\t' || c == '\r' || c == '\n')
1790 {
1791 p++;
1792 continue; /* ignoring leading blanks */
1793 }
1794 if (c == '.')
1795 {
1796 while (1)
1797 {
1798 /* consuming a dot-macro until the end of the line */
1799 c = *p;
1800 if (c == '\n' || c == '\0')
1801 break;
1802 p++;
1803 }
1804 if (c != '\0')
1805 p++;
1806 continue;
1807 }
1808 if (c == '-')
1809 {
1810 if (comment_marker)
1811 {
1812 while (1)
1813 {
1814 /* consuming a comment until the end of the line */
1815 c = *p;
1816 if (c == '\n' || c == '\0')
1817 break;
1818 p++;
1819 }
1820 comment_marker = 0;
1821 if (c != '\0')
1822 p++;
1823 continue;
1824 }
1825 comment_marker = 1;
1826 p++;
1827 continue;
1828 }
1829 break;
1830 }
1831 return p;
1832 }
1833
1834 static void
do_clean_double(char * buffer)1835 do_clean_double (char *buffer)
1836 {
1837 /* cleans unneeded trailing zeros */
1838 int i;
1839 for (i = strlen (buffer) - 1; i > 0; i--)
1840 {
1841 if (buffer[i] == '0')
1842 buffer[i] = '\0';
1843 else
1844 break;
1845 }
1846 if (buffer[i] == '.')
1847 buffer[i] = '\0';
1848 if (strcmp (buffer, "-0") == 0)
1849 {
1850 /* avoiding to return embarassing NEGATIVE ZEROes */
1851 strcpy (buffer, "0");
1852 }
1853
1854 if (strcmp (buffer, "-1.#QNAN") == 0 || strcmp (buffer, "NaN") == 0
1855 || strcmp (buffer, "1.#QNAN") == 0
1856 || strcmp (buffer, "-1.#IND") == 0 || strcmp (buffer, "1.#IND") == 0)
1857 {
1858 /* on Windows a NaN could be represented in "odd" ways */
1859 /* this is intended to restore a consistent behaviour */
1860 strcpy (buffer, "nan");
1861 }
1862 }
1863
1864 static void
do_log_double(FILE * log,double value,int precision)1865 do_log_double (FILE * log, double value, int precision)
1866 {
1867 /* printing a well-formatted DOUBLE into the Logfile */
1868 char *buf;
1869 if (precision < 0)
1870 buf = sqlite3_mprintf ("%1.6f", value);
1871 else
1872 buf = sqlite3_mprintf ("%.*f", precision, value);
1873 do_clean_double (buf);
1874 fprintf (log, "%s", buf);
1875 sqlite3_free (buf);
1876 }
1877
1878 static char *
do_title_bar(int len)1879 do_title_bar (int len)
1880 {
1881 /* building a bar line */
1882 int i;
1883 char *line = sqlite3_malloc (len + 1);
1884 for (i = 0; i < len; i++)
1885 *(line + i) = '-';
1886 *(line + len) = '\0';
1887 return line;
1888 }
1889
1890 static void
print_elapsed_time(FILE * log,double seconds)1891 print_elapsed_time (FILE * log, double seconds)
1892 {
1893 /* well formatting elapsed time */
1894 int int_time = (int) seconds;
1895 int millis = (int) ((seconds - (double) int_time) * 1000.0);
1896 int secs = int_time % 60;
1897 int_time /= 60;
1898 int mins = int_time % 60;
1899 int_time /= 60;
1900 int hh = int_time;
1901 if (hh == 0 && mins == 0)
1902 fprintf (log, "-- Execution time: %d.%03d\n", secs, millis);
1903 else if (hh == 0)
1904 fprintf (log, "-- Execution time: %d:%02d.%03d\n", mins, secs, millis);
1905 else
1906 fprintf (log, "-- Execution time: %d:%02d:%02d.%03d\n", hh, mins, secs,
1907 millis);
1908 }
1909
1910 static char *
get_timestamp(sqlite3 * sqlite)1911 get_timestamp (sqlite3 * sqlite)
1912 {
1913 /* retrieving the current timestamp */
1914 char *now;
1915 const char *sql;
1916 int ret;
1917 int i;
1918 char **results;
1919 int rows;
1920 int columns;
1921
1922 sql = "SELECT DateTime('now')";
1923 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
1924 if (ret != SQLITE_OK)
1925 goto error;
1926 for (i = 1; i <= rows; i++)
1927 {
1928 const char *timestamp = results[(i * columns) + 0];
1929 now = sqlite3_mprintf ("%s", timestamp);
1930 }
1931 sqlite3_free_table (results);
1932 return now;
1933
1934 error:
1935 now = sqlite3_mprintf ("unknown");
1936 return now;
1937 }
1938
1939 static char *
do_clean_failing_sql(const char * pSql)1940 do_clean_failing_sql (const char *pSql)
1941 {
1942 /* attempting to insulate the failing SQL statement */
1943 int max = 0;
1944 const char *in = pSql;
1945 char *fail = NULL;
1946 char *out;
1947
1948 while (1)
1949 {
1950 if (*in == '\0')
1951 break;
1952 if (*in == ';')
1953 {
1954 max++;
1955 break;
1956 }
1957 max++;
1958 in++;
1959 }
1960 fail = malloc (max + 1);
1961 out = fail;
1962 in = pSql;
1963 while (max > 0)
1964 {
1965 *out++ = *in++;
1966 max--;
1967 }
1968 *out = '\0';
1969 return fail;
1970 }
1971
1972 static sqlite3 *
do_clone_mem_db(sqlite3 * origin,void * cache,int mode)1973 do_clone_mem_db (sqlite3 * origin, void *cache, int mode)
1974 {
1975 /* opening a new connection by clonig a MEMORY MAIN DB */
1976 int ret;
1977 sqlite3_backup *backup;
1978 sqlite3 *handle;
1979
1980 ret = sqlite3_open_v2 (":memory:", &handle, mode, NULL);
1981 if (ret != SQLITE_OK)
1982 {
1983 spatialite_e ("SqlProcExec: sqlite3_open_v2 error: %s\n",
1984 sqlite3_errmsg (handle));
1985 sqlite3_close (handle);
1986 return NULL;
1987 }
1988 backup = sqlite3_backup_init (handle, "main", origin, "main");
1989 if (!backup)
1990 goto stop;
1991 while (1)
1992 {
1993 ret = sqlite3_backup_step (backup, 1024);
1994 if (ret == SQLITE_DONE)
1995 break;
1996 }
1997 ret = sqlite3_backup_finish (backup);
1998 if (ret != SQLITE_OK)
1999 goto stop;
2000 spatialite_internal_init (handle, cache);
2001 return handle;
2002
2003 stop:
2004 sqlite3_close (handle);
2005 return NULL;
2006 }
2007
2008 static int
do_clone_memory_db(sqlite3 * main_handle,sqlite3 * handle,const char * db_name)2009 do_clone_memory_db (sqlite3 * main_handle, sqlite3 * handle,
2010 const char *db_name)
2011 {
2012 /* cloning back an eventual MEMORY DB */
2013 int ret;
2014 sqlite3_backup *backup;
2015 const char *db_path = sqlite3_db_filename (main_handle, db_name);
2016 if (db_path != NULL)
2017 {
2018 if (strlen (db_path) > 0)
2019 return 1; /* not a MEMORY DB - quitting */
2020 }
2021 backup = sqlite3_backup_init (main_handle, db_name, handle, db_name);
2022 if (!backup)
2023 return 0;
2024 while (1)
2025 {
2026 ret = sqlite3_backup_step (backup, 1024);
2027 if (ret == SQLITE_DONE)
2028 break;
2029 }
2030 ret = sqlite3_backup_finish (backup);
2031 if (ret != SQLITE_OK)
2032 return 0;
2033 return 1;
2034 }
2035
2036 static sqlite3 *
do_open_new_connection(sqlite3 * origin,void * cache)2037 do_open_new_connection (sqlite3 * origin, void *cache)
2038 {
2039 /* opening a new connection to the MAIN DB */
2040 int ret;
2041 sqlite3 *handle;
2042 int mem_db = 0;
2043 const char *db_path = sqlite3_db_filename (origin, "main");
2044 int rd_only = sqlite3_db_readonly (origin, "main");
2045 int mode = SQLITE_OPEN_READWRITE;
2046 if (rd_only)
2047 mode = SQLITE_OPEN_READONLY;
2048 if (db_path == NULL)
2049 mem_db = 1;
2050 else if (*db_path == '\0')
2051 mem_db = 1;
2052 if (mem_db)
2053 return do_clone_mem_db (origin, cache, mode);
2054
2055 /* creating a new connection */
2056 ret = sqlite3_open_v2 (db_path, &handle, mode, NULL);
2057 if (ret != SQLITE_OK)
2058 {
2059 spatialite_e ("SqlProcExec: sqlite3_open_v2 error: %s\n",
2060 sqlite3_errmsg (handle));
2061 sqlite3_close (handle);
2062 return NULL;
2063 }
2064 sqlite3_enable_load_extension (handle, 1);
2065 spatialite_internal_init (handle, cache);
2066 return handle;
2067 }
2068
2069 static int
do_attach_all(sqlite3 * origin,sqlite3 * handle)2070 do_attach_all (sqlite3 * origin, sqlite3 * handle)
2071 {
2072 /* attaching all required database-files to the new connection */
2073 int ret;
2074 int i;
2075 char **results;
2076 int rows;
2077 int columns;
2078
2079 /* listing all database-files attached to the initial connection */
2080 ret =
2081 sqlite3_get_table (origin, "PRAGMA database_list", &results, &rows,
2082 &columns, NULL);
2083 if (ret != SQLITE_OK)
2084 return 0;
2085 if (rows < 1)
2086 ;
2087 else
2088 {
2089 for (i = 1; i <= rows; i++)
2090 {
2091 int mem_db = 0;
2092 char *sql;
2093 char *xdb_name;
2094 const char *db_name = results[(i * columns) + 1];
2095 const char *db_path = results[(i * columns) + 2];
2096 if (strcasecmp (db_name, "main") == 0)
2097 continue; /* ignoring MAIN */
2098 if (db_path == NULL)
2099 mem_db = 1;
2100 else if (*db_path == '\0')
2101 mem_db = 1;
2102 if (mem_db && strcasecmp (db_name, "temp") == 0)
2103 goto skip_temp; /* "temp" is already attached by default */
2104 xdb_name = gaiaDoubleQuotedSql (db_name);
2105 if (mem_db)
2106 sql =
2107 sqlite3_mprintf ("ATTACH DATABASE %Q AS \"%s\"",
2108 ":memory:", xdb_name);
2109 else
2110 sql =
2111 sqlite3_mprintf ("ATTACH DATABASE %Q AS \"%s\"",
2112 db_path, xdb_name);
2113 free (xdb_name);
2114 ret = sqlite3_exec (handle, sql, NULL, NULL, NULL);
2115 sqlite3_free (sql);
2116 if (ret != SQLITE_OK)
2117 {
2118 spatialite_e ("SqlProcExec: ATTACH DATABASE error: %s\n",
2119 sqlite3_errmsg (handle));
2120 sqlite3_free_table (results);
2121 return 0;
2122 }
2123 skip_temp:
2124 if (mem_db)
2125 {
2126 if (!do_clone_memory_db (handle, origin, db_name))
2127 {
2128 spatialite_e
2129 ("SqlProcExec: ATTACH DATABASE error: %s\n",
2130 sqlite3_errmsg (handle));
2131 return 0;
2132 }
2133 }
2134 }
2135 }
2136 sqlite3_free_table (results);
2137 return 1;
2138 }
2139
2140 SQLPROC_DECLARE int
gaia_sql_proc_execute(sqlite3 * main_handle,const void * ctx,const char * sql)2141 gaia_sql_proc_execute (sqlite3 * main_handle, const void *ctx, const char *sql)
2142 {
2143 /* executing an already cooked SQL Body */
2144 const char *pSql = sql;
2145 sqlite3_stmt *stmt;
2146 int retval = 0;
2147 int n_stmts = 0;
2148 FILE *log = NULL;
2149 int ret;
2150 struct gaia_variant_value *ret_value;
2151 struct splite_internal_cache *main_cache =
2152 (struct splite_internal_cache *) ctx;
2153
2154 /* opening a new connection */
2155 struct splite_internal_cache *cache = spatialite_alloc_connection ();
2156 sqlite3 *handle = do_open_new_connection (main_handle, cache);
2157 if (handle == NULL)
2158 return 0;
2159 /* attaching all the required database-files */
2160 if (!do_attach_all (main_handle, handle))
2161 return 0;
2162
2163 if (cache != NULL)
2164 {
2165 gaia_sql_proc_logfile (cache, main_cache->SqlProcLogfile,
2166 main_cache->SqlProcLogfileAppend);
2167 cache->gpkg_mode = main_cache->gpkg_mode;
2168 cache->gpkg_amphibious_mode = main_cache->gpkg_amphibious_mode;
2169 cache->decimal_precision = main_cache->decimal_precision;
2170 cache->is_pause_enabled = main_cache->is_pause_enabled;
2171 cache->SqlProcContinue = 1;
2172 gaia_set_variant_null (cache->SqlProcRetValue);
2173 log = cache->SqlProcLog;
2174 }
2175
2176 if (log != NULL)
2177 {
2178 /* printing a session header */
2179 char *now = get_timestamp (handle);
2180 fprintf (log,
2181 "--=========================================================================================\n");
2182 fprintf (log, "--== SQL session start = %s\n", now);
2183 sqlite3_free (now);
2184 fprintf (log,
2185 "--=========================================================================================\n");
2186 }
2187
2188 while (1)
2189 {
2190 const char *sql_tail;
2191 int ret;
2192 int title;
2193 clock_t clock_start;
2194 clock_t clock_end;
2195 double seconds;
2196 int n_rows;
2197 int rs;
2198
2199 if (cache != NULL)
2200 {
2201 if (cache->SqlProcContinue == 0)
2202 {
2203 /* found a pending EXIT request */
2204 if (log != NULL)
2205 fprintf (log,
2206 "\n-- ***** quitting ... found a pending EXIT request *************\n\n");
2207 break;
2208 }
2209 }
2210
2211 pSql = consume_empty_sql (pSql);
2212 if (strlen (pSql) == 0)
2213 break;
2214 clock_start = clock ();
2215 ret = sqlite3_prepare_v2 (handle, pSql, strlen (pSql), &stmt,
2216 &sql_tail);
2217 if (ret != SQLITE_OK)
2218 {
2219 if (log != NULL)
2220 {
2221 char *failSql = do_clean_failing_sql (pSql);
2222 fprintf (log, "--=== SQL error: %s\n",
2223 sqlite3_errmsg (handle));
2224 fprintf (log, "-- failing SQL statement was:\n%s\n\n",
2225 failSql);
2226 free (failSql);
2227 }
2228 goto stop;
2229 }
2230 pSql = sql_tail;
2231 if (log != NULL)
2232 fprintf (log, "%s\n", sqlite3_sql (stmt));
2233 title = 1;
2234 n_rows = 0;
2235 rs = 0;
2236 n_stmts++;
2237 while (1)
2238 {
2239 /* executing an SQL statement */
2240 int i;
2241 int n_cols;
2242 ret = sqlite3_step (stmt);
2243 if (title && log != NULL
2244 && (ret == SQLITE_DONE || ret == SQLITE_ROW))
2245 {
2246 char *bar;
2247 char *line;
2248 char *names;
2249 char *prev;
2250 n_cols = sqlite3_column_count (stmt);
2251 if (n_cols > 0)
2252 {
2253 /* printing column names */
2254 rs = 1;
2255 for (i = 0; i < n_cols; i++)
2256 {
2257 const char *nm =
2258 sqlite3_column_name (stmt, i);
2259 if (i == 0)
2260 {
2261 line = do_title_bar (strlen (nm));
2262 bar = sqlite3_mprintf ("%s", line);
2263 sqlite3_free (line);
2264 names = sqlite3_mprintf ("%s", nm);
2265 }
2266 else
2267 {
2268 prev = bar;
2269 line = do_title_bar (strlen (nm));
2270 bar =
2271 sqlite3_mprintf ("%s+%s", prev,
2272 line);
2273 sqlite3_free (line);
2274 sqlite3_free (prev);
2275 prev = names;
2276 names =
2277 sqlite3_mprintf ("%s|%s", prev, nm);
2278 sqlite3_free (prev);
2279 }
2280 }
2281 fprintf (log, "-- %s\n", bar);
2282 fprintf (log, "-- %s\n", names);
2283 fprintf (log, "-- %s\n", bar);
2284 sqlite3_free (names);
2285 sqlite3_free (bar);
2286 }
2287 title = 0;
2288 }
2289 if (ret == SQLITE_DONE)
2290 break;
2291 else if (ret == SQLITE_ROW)
2292 {
2293 sqlite3_int64 int64;
2294 double dbl;
2295 int sz;
2296 if (log == NULL)
2297 continue;
2298 n_rows++;
2299 /* updating the Logfile */
2300 n_cols = sqlite3_column_count (stmt);
2301 for (i = 0; i < n_cols; i++)
2302 {
2303 /* printing column values */
2304 if (i > 0)
2305 fprintf (log, "|");
2306 else
2307 fprintf (log, "-- ");
2308 switch (sqlite3_column_type (stmt, i))
2309 {
2310 case SQLITE_INTEGER:
2311 int64 = sqlite3_column_int64 (stmt, i);
2312 fprintf (log, FRMT64, int64);
2313 break;
2314 case SQLITE_FLOAT:
2315 dbl = sqlite3_column_double (stmt, i);
2316 do_log_double (log, dbl,
2317 cache->decimal_precision);
2318 break;
2319 case SQLITE_TEXT:
2320 sz = sqlite3_column_bytes (stmt, i);
2321 if (sz <= 128)
2322 fprintf (log, "%s",
2323 (const char *)
2324 sqlite3_column_text (stmt, i));
2325 else
2326 fprintf (log, "TEXT[%d bytes]", sz);
2327 break;
2328 case SQLITE_BLOB:
2329 sz = sqlite3_column_bytes (stmt, i);
2330 fprintf (log, "BLOB[%d bytes]", sz);
2331 break;
2332 case SQLITE_NULL:
2333 default:
2334 fprintf (log, "NULL");
2335 break;
2336 };
2337 }
2338 fprintf (log, "\n");
2339 }
2340 else
2341 {
2342 char *errmsg =
2343 sqlite3_mprintf ("gaia_sql_proc_execute: %s",
2344 sqlite3_errmsg (handle));
2345 gaia_sql_proc_set_error (cache, errmsg);
2346 if (log != NULL)
2347 {
2348 fprintf (log, "--=== SQL error: %s\n",
2349 sqlite3_errmsg (handle));
2350 }
2351 sqlite3_free (errmsg);
2352 sqlite3_finalize (stmt);
2353 goto stop;
2354 }
2355 }
2356 sqlite3_finalize (stmt);
2357 clock_end = clock ();
2358 seconds =
2359 (double) (clock_end - clock_start) / (double) CLOCKS_PER_SEC;
2360 if (log != NULL)
2361 {
2362 if (rs)
2363 fprintf (log, "--=== %d %s === ", n_rows,
2364 (n_rows == 1) ? "row" : "rows");
2365 else
2366 fprintf (log, "--=== ");
2367 print_elapsed_time (log, seconds);
2368 fprintf (log, "\n");
2369 fflush (log);
2370 }
2371 }
2372 retval = 1;
2373
2374 stop:
2375 if (log != NULL)
2376 {
2377 /* printing a session footer */
2378 char *now = get_timestamp (handle);
2379 fprintf (log,
2380 "--=========================================================================================\n");
2381 fprintf (log,
2382 "--== SQL session end = %s = %d statement%s executed\n",
2383 now, n_stmts, (n_stmts == 1) ? " was" : "s were");
2384 sqlite3_free (now);
2385 fprintf (log,
2386 "--=========================================================================================\n\n\n");
2387 fflush (log);
2388 }
2389 /* updating the actual MEMORY DB (if any) */
2390 do_clone_memory_db (main_handle, handle, "main");
2391
2392 /* terminating the new connection */
2393 finalize_topologies (cache);
2394 ret = sqlite3_close (handle);
2395 if (ret != SQLITE_OK)
2396 spatialite_e ("SqlProcExec: sqlite3_close() error: %s\n",
2397 sqlite3_errmsg (handle));
2398
2399 /* copying an eventual RETVALUE */
2400 ret_value = cache->SqlProcRetValue;
2401 switch (ret_value->dataType)
2402 {
2403 case SQLITE_INTEGER:
2404 gaia_set_variant_int64 (main_cache->SqlProcRetValue,
2405 ret_value->intValue);
2406 break;
2407 case SQLITE_FLOAT:
2408 gaia_set_variant_double (main_cache->SqlProcRetValue,
2409 ret_value->dblValue);
2410 break;
2411 case SQLITE_TEXT:
2412 gaia_set_variant_text (main_cache->SqlProcRetValue,
2413 ret_value->textValue, ret_value->size);
2414 break;
2415 case SQLITE_BLOB:
2416 gaia_set_variant_blob (main_cache->SqlProcRetValue,
2417 ret_value->blobValue, ret_value->size);
2418 break;
2419 case SQLITE_NULL:
2420 default:
2421 gaia_set_variant_null (main_cache->SqlProcRetValue);
2422 break;
2423
2424 };
2425 spatialite_internal_cleanup (cache);
2426 return retval;
2427 }
2428
2429 SQLPROC_DECLARE int
gaia_sql_proc_logfile(const void * ctx,const char * filepath,int append)2430 gaia_sql_proc_logfile (const void *ctx, const char *filepath, int append)
2431 {
2432 /* enabling/disabling the Logfile */
2433 FILE *log;
2434 int len;
2435 struct splite_internal_cache *cache = (struct splite_internal_cache *) ctx;
2436
2437 if (cache == NULL)
2438 return 0;
2439
2440 if (filepath == NULL)
2441 {
2442 /* disabling the Logfile */
2443 if (cache->SqlProcLogfile != NULL)
2444 {
2445 free (cache->SqlProcLogfile);
2446 cache->SqlProcLogfile = NULL;
2447 }
2448 if (cache->SqlProcLog != NULL)
2449 fclose (cache->SqlProcLog);
2450 cache->SqlProcLog = NULL;
2451 return 1;
2452 }
2453
2454 /* attempting to enable the Logfile */
2455 if (append)
2456 #ifdef _WIN32
2457 log = gaia_win_fopen (filepath, "ab");
2458 #else
2459 log = fopen (filepath, "ab");
2460 #endif
2461 else
2462 #ifdef _WIN32
2463 log = gaia_win_fopen (filepath, "wb");
2464 #else
2465 log = fopen (filepath, "wb");
2466 #endif
2467 if (log == NULL)
2468 return 0;
2469
2470 /* closing the current Logfile (if any) */
2471 if (cache->SqlProcLogfile != NULL)
2472 free (cache->SqlProcLogfile);
2473 if (cache->SqlProcLog != NULL)
2474 fclose (cache->SqlProcLog);
2475
2476 /* resetting the current Logfile */
2477 len = strlen (filepath);
2478 cache->SqlProcLogfile = malloc (len + 1);
2479 strcpy (cache->SqlProcLogfile, filepath);
2480 cache->SqlProcLog = log;
2481 cache->SqlProcLogfileAppend = append;
2482 return 1;
2483 }
2484