1 /*
2
3 statistics.c -- helper functions updating internal statistics
4
5 version 5.0, 2020 August 1
6
7 Author: Sandro Furieri a.furieri@lqt.it
8
9 ------------------------------------------------------------------------------
10
11 Version: MPL 1.1/GPL 2.0/LGPL 2.1
12
13 The contents of this file are subject to the Mozilla Public License Version
14 1.1 (the "License"); you may not use this file except in compliance with
15 the License. You may obtain a copy of the License at
16 http://www.mozilla.org/MPL/
17
18 Software distributed under the License is distributed on an "AS IS" basis,
19 WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
20 for the specific language governing rights and limitations under the
21 License.
22
23 The Original Code is the SpatiaLite library
24
25 The Initial Developer of the Original Code is Alessandro Furieri
26
27 Portions created by the Initial Developer are Copyright (C) 2008-2021
28 the Initial Developer. All Rights Reserved.
29
30 Contributor(s):
31 Pepijn Van Eeckhoudt <pepijnvaneeckhoudt@luciad.com>
32 (implementing Android support)
33 Mark Johnson <mj10777@googlemail.com>
34 (extending Drop/Rename Table and RenameColumn)
35
36 Alternatively, the contents of this file may be used under the terms of
37 either the GNU General Public License Version 2 or later (the "GPL"), or
38 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
39 in which case the provisions of the GPL or the LGPL are applicable instead
40 of those above. If you wish to allow use of your version of this file only
41 under the terms of either the GPL or the LGPL, and not to allow others to
42 use your version of this file under the terms of the MPL, indicate your
43 decision by deleting the provisions above and replace them with the notice
44 and other provisions required by the GPL or the LGPL. If you do not delete
45 the provisions above, a recipient may use your version of this file under
46 the terms of any one of the MPL, the GPL or the LGPL.
47
48 */
49
50 #include <sys/types.h>
51 #include <stdlib.h>
52 #include <stdio.h>
53 #include <string.h>
54 #include <math.h>
55 #include <float.h>
56 #include <locale.h>
57 #include <ctype.h>
58
59 #if defined(_WIN32) && !defined(__MINGW32__)
60 #include "config-msvc.h"
61 #else
62 #include "config.h"
63 #endif
64
65 #if defined(_WIN32) || defined(WIN32)
66 #include <io.h>
67 #define isatty _isatty
68 #else
69 #include <unistd.h>
70 #endif
71
72 #include <spatialite/sqlite.h>
73 #include <spatialite/debug.h>
74
75 #include <spatialite/gaiageo.h>
76 #include <spatialite.h>
77 #include <spatialite_private.h>
78 #include <spatialite/gaiaaux.h>
79
80 #ifdef _WIN32
81 #define strcasecmp _stricmp
82 #endif /* not WIN32 */
83
84 struct field_item_infos
85 {
86 int ordinal;
87 char *col_name;
88 int null_values;
89 int integer_values;
90 int double_values;
91 int text_values;
92 int blob_values;
93 int max_size;
94 int int_minmax_set;
95 int int_min;
96 int int_max;
97 int dbl_minmax_set;
98 double dbl_min;
99 double dbl_max;
100 struct field_item_infos *next;
101 };
102
103 struct field_container_infos
104 {
105 struct field_item_infos *first;
106 struct field_item_infos *last;
107 };
108
109 static int
do_update_layer_statistics_v4(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)110 do_update_layer_statistics_v4 (sqlite3 * sqlite, const char *table,
111 const char *column, int count, int has_coords,
112 double min_x, double min_y, double max_x,
113 double max_y)
114 {
115 /* update GEOMETRY_COLUMNS_STATISTICS Version >= 4.0.0 */
116 char sql[8192];
117 int ret;
118 int error = 0;
119 sqlite3_stmt *stmt;
120
121 strcpy (sql, "INSERT OR REPLACE INTO geometry_columns_statistics ");
122 strcat (sql, "(f_table_name, f_geometry_column, last_verified, ");
123 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
124 strcat (sql, "extent_max_x, extent_max_y) VALUES (?, ?, ");
125 strcat (sql, "strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), ?, ?, ?, ?, ?)");
126
127 /* compiling SQL prepared statement */
128 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
129 if (ret != SQLITE_OK)
130 return 0;
131
132 /* binding INSERT params */
133 sqlite3_reset (stmt);
134 sqlite3_clear_bindings (stmt);
135 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
136 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
137 sqlite3_bind_int (stmt, 3, count);
138 if (has_coords)
139 {
140 sqlite3_bind_double (stmt, 4, min_x);
141 sqlite3_bind_double (stmt, 5, min_y);
142 sqlite3_bind_double (stmt, 6, max_x);
143 sqlite3_bind_double (stmt, 7, max_y);
144 }
145 else
146 {
147 sqlite3_bind_null (stmt, 4);
148 sqlite3_bind_null (stmt, 5);
149 sqlite3_bind_null (stmt, 6);
150 sqlite3_bind_null (stmt, 7);
151 }
152 ret = sqlite3_step (stmt);
153 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
154 ;
155 else
156 error = 1;
157 ret = sqlite3_finalize (stmt);
158 if (ret != SQLITE_OK)
159 return 0;
160 if (error)
161 return 0;
162 return 1;
163 }
164
165 static int
do_update_layer_statistics(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)166 do_update_layer_statistics (sqlite3 * sqlite, const char *table,
167 const char *column, int count, int has_coords,
168 double min_x, double min_y, double max_x,
169 double max_y)
170 {
171 /* update LAYER_STATISTICS [single table/geometry] */
172 char sql[8192];
173 int ret;
174 int error = 0;
175 sqlite3_stmt *stmt;
176 int metadata_version = checkSpatialMetaData (sqlite);
177
178 if (metadata_version == 3)
179 {
180 /* current metadata style >= v.4.0.0 */
181 return do_update_layer_statistics_v4 (sqlite, table, column, count,
182 has_coords, min_x, min_y, max_x,
183 max_y);
184 }
185
186 if (!check_layer_statistics (sqlite))
187 return 0;
188 strcpy (sql, "INSERT OR REPLACE INTO layer_statistics ");
189 strcat (sql, "(raster_layer, table_name, geometry_column, ");
190 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
191 strcat (sql, "extent_max_x, extent_max_y) ");
192 strcat (sql, "VALUES (0, ?, ?, ?, ?, ?, ?, ?)");
193
194 /* compiling SQL prepared statement */
195 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
196 if (ret != SQLITE_OK)
197 return 0;
198 /* binding INSERT params */
199 sqlite3_reset (stmt);
200 sqlite3_clear_bindings (stmt);
201 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
202 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
203 sqlite3_bind_int (stmt, 3, count);
204 if (has_coords)
205 {
206 sqlite3_bind_double (stmt, 4, min_x);
207 sqlite3_bind_double (stmt, 5, min_y);
208 sqlite3_bind_double (stmt, 6, max_x);
209 sqlite3_bind_double (stmt, 7, max_y);
210 }
211 else
212 {
213 sqlite3_bind_null (stmt, 4);
214 sqlite3_bind_null (stmt, 5);
215 sqlite3_bind_null (stmt, 6);
216 sqlite3_bind_null (stmt, 7);
217 }
218 ret = sqlite3_step (stmt);
219 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
220 ;
221 else
222 error = 1;
223 ret = sqlite3_finalize (stmt);
224 if (ret != SQLITE_OK)
225 return 0;
226 if (error)
227 return 0;
228 return 1;
229 }
230
231 static int
do_update_views_layer_statistics_v4(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)232 do_update_views_layer_statistics_v4 (sqlite3 * sqlite, const char *table,
233 const char *column, int count,
234 int has_coords, double min_x,
235 double min_y, double max_x, double max_y)
236 {
237 /* update VIEWS_GEOMETRY_COLUMNS_STATISTICS Version >= 4.0.0 */
238 char sql[8192];
239 int ret;
240 int error = 0;
241 sqlite3_stmt *stmt;
242
243 strcpy (sql, "INSERT OR REPLACE INTO views_geometry_columns_statistics ");
244 strcat (sql, "(view_name, view_geometry, last_verified, ");
245 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
246 strcat (sql, "extent_max_x, extent_max_y) VALUES (?, ?, ");
247 strcat (sql, "strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), ?, ?, ?, ?, ?)");
248
249 /* compiling SQL prepared statement */
250 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
251 if (ret != SQLITE_OK)
252 return 0;
253
254 /* binding INSERT params */
255 sqlite3_reset (stmt);
256 sqlite3_clear_bindings (stmt);
257 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
258 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
259 sqlite3_bind_int (stmt, 3, count);
260 if (has_coords)
261 {
262 sqlite3_bind_double (stmt, 4, min_x);
263 sqlite3_bind_double (stmt, 5, min_y);
264 sqlite3_bind_double (stmt, 6, max_x);
265 sqlite3_bind_double (stmt, 7, max_y);
266 }
267 else
268 {
269 sqlite3_bind_null (stmt, 4);
270 sqlite3_bind_null (stmt, 5);
271 sqlite3_bind_null (stmt, 6);
272 sqlite3_bind_null (stmt, 7);
273 }
274 ret = sqlite3_step (stmt);
275 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
276 ;
277 else
278 error = 1;
279 ret = sqlite3_finalize (stmt);
280 if (ret != SQLITE_OK)
281 return 0;
282 if (error)
283 return 0;
284 return 1;
285 }
286
287 static int
do_update_views_layer_statistics(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)288 do_update_views_layer_statistics (sqlite3 * sqlite, const char *table,
289 const char *column, int count,
290 int has_coords, double min_x, double min_y,
291 double max_x, double max_y)
292 {
293 /* update VIEWS_LAYER_STATISTICS [single table/geometry] */
294 char sql[8192];
295 int ret;
296 int error = 0;
297 sqlite3_stmt *stmt;
298 int metadata_version = checkSpatialMetaData (sqlite);
299
300 if (metadata_version == 3)
301 {
302 /* current metadata style >= v.4.0.0 */
303 return do_update_views_layer_statistics_v4 (sqlite, table, column,
304 count, has_coords, min_x,
305 min_y, max_x, max_y);
306 }
307
308 if (!check_views_layer_statistics (sqlite))
309 return 0;
310 strcpy (sql, "INSERT OR REPLACE INTO views_layer_statistics ");
311 strcat (sql, "(view_name, view_geometry, ");
312 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
313 strcat (sql, "extent_max_x, extent_max_y) ");
314 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?)");
315
316 /* compiling SQL prepared statement */
317 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
318 if (ret != SQLITE_OK)
319 return 0;
320
321 /* binding INSERT params */
322 sqlite3_reset (stmt);
323 sqlite3_clear_bindings (stmt);
324 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
325 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
326 sqlite3_bind_int (stmt, 3, count);
327 if (has_coords)
328 {
329 sqlite3_bind_double (stmt, 4, min_x);
330 sqlite3_bind_double (stmt, 5, min_y);
331 sqlite3_bind_double (stmt, 6, max_x);
332 sqlite3_bind_double (stmt, 7, max_y);
333 }
334 else
335 {
336 sqlite3_bind_null (stmt, 4);
337 sqlite3_bind_null (stmt, 5);
338 sqlite3_bind_null (stmt, 6);
339 sqlite3_bind_null (stmt, 7);
340 }
341 ret = sqlite3_step (stmt);
342 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
343 ;
344 else
345 error = 1;
346 ret = sqlite3_finalize (stmt);
347 if (ret != SQLITE_OK)
348 return 0;
349 if (error)
350 return 0;
351 return 1;
352 }
353
354 static int
do_update_virts_layer_statistics_v4(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)355 do_update_virts_layer_statistics_v4 (sqlite3 * sqlite, const char *table,
356 const char *column, int count,
357 int has_coords, double min_x,
358 double min_y, double max_x, double max_y)
359 {
360 /* update VIRTS_GEOMETRY_COLUMNS_STATISTICS Version >= 4.0.0 */
361 char sql[8192];
362 int ret;
363 int error = 0;
364 sqlite3_stmt *stmt;
365
366 strcpy (sql, "INSERT OR REPLACE INTO virts_geometry_columns_statistics ");
367 strcat (sql, "(virt_name, virt_geometry, last_verified, ");
368 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
369 strcat (sql, "extent_max_x, extent_max_y) VALUES (?, ?, ");
370 strcat (sql, "strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), ?, ?, ?, ?, ?)");
371
372 /* compiling SQL prepared statement */
373 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
374 if (ret != SQLITE_OK)
375 return 0;
376
377 /* binding INSERT params */
378 sqlite3_reset (stmt);
379 sqlite3_clear_bindings (stmt);
380 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
381 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
382 sqlite3_bind_int (stmt, 3, count);
383 if (has_coords)
384 {
385 sqlite3_bind_double (stmt, 4, min_x);
386 sqlite3_bind_double (stmt, 5, min_y);
387 sqlite3_bind_double (stmt, 6, max_x);
388 sqlite3_bind_double (stmt, 7, max_y);
389 }
390 else
391 {
392 sqlite3_bind_null (stmt, 4);
393 sqlite3_bind_null (stmt, 5);
394 sqlite3_bind_null (stmt, 6);
395 sqlite3_bind_null (stmt, 7);
396 }
397 ret = sqlite3_step (stmt);
398 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
399 ;
400 else
401 error = 1;
402 ret = sqlite3_finalize (stmt);
403 if (ret != SQLITE_OK)
404 return 0;
405 if (error)
406 return 0;
407 return 1;
408 }
409
410 static int
do_update_virts_layer_statistics(sqlite3 * sqlite,const char * table,const char * column,int count,int has_coords,double min_x,double min_y,double max_x,double max_y)411 do_update_virts_layer_statistics (sqlite3 * sqlite, const char *table,
412 const char *column, int count,
413 int has_coords, double min_x, double min_y,
414 double max_x, double max_y)
415 {
416 /* update VIRTS_LAYER_STATISTICS [single table/geometry] */
417 char sql[8192];
418 int ret;
419 int error = 0;
420 sqlite3_stmt *stmt;
421 int metadata_version = checkSpatialMetaData (sqlite);
422
423 if (metadata_version == 3)
424 {
425 /* current metadata style >= v.4.0.0 */
426 return do_update_virts_layer_statistics_v4 (sqlite, table, column,
427 count, has_coords, min_x,
428 min_y, max_x, max_y);
429 }
430
431 if (!check_virts_layer_statistics (sqlite))
432 return 0;
433 strcpy (sql, "INSERT OR REPLACE INTO virts_layer_statistics ");
434 strcat (sql, "(virt_name, virt_geometry, ");
435 strcat (sql, "row_count, extent_min_x, extent_min_y, ");
436 strcat (sql, "extent_max_x, extent_max_y) ");
437 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?)");
438
439 /* compiling SQL prepared statement */
440 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
441 if (ret != SQLITE_OK)
442 return 0;
443
444 /* binding INSERT params */
445 sqlite3_reset (stmt);
446 sqlite3_clear_bindings (stmt);
447 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
448 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
449 sqlite3_bind_int (stmt, 3, count);
450 if (has_coords)
451 {
452 sqlite3_bind_double (stmt, 4, min_x);
453 sqlite3_bind_double (stmt, 5, min_y);
454 sqlite3_bind_double (stmt, 6, max_x);
455 sqlite3_bind_double (stmt, 7, max_y);
456 }
457 else
458 {
459 sqlite3_bind_null (stmt, 4);
460 sqlite3_bind_null (stmt, 5);
461 sqlite3_bind_null (stmt, 6);
462 sqlite3_bind_null (stmt, 7);
463 }
464 ret = sqlite3_step (stmt);
465 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
466 ;
467 else
468 error = 1;
469 ret = sqlite3_finalize (stmt);
470 if (ret != SQLITE_OK)
471 return 0;
472 if (error)
473 return 0;
474 return 1;
475 }
476
477 static void
update_field_infos(struct field_container_infos * infos,int ordinal,const char * col_name,const char * type,int size,int count)478 update_field_infos (struct field_container_infos *infos, int ordinal,
479 const char *col_name, const char *type, int size, int count)
480 {
481 /* updating the field container infos */
482 int len;
483 struct field_item_infos *p = infos->first;
484 while (p)
485 {
486 if (strcasecmp (col_name, p->col_name) == 0)
487 {
488 /* updating an already defined field */
489 if (strcasecmp (type, "null") == 0)
490 p->null_values += count;
491 if (strcasecmp (type, "integer") == 0)
492 p->integer_values += count;
493 if (strcasecmp (type, "real") == 0)
494 p->double_values += count;
495 if (strcasecmp (type, "text") == 0)
496 {
497 p->text_values += count;
498 if (size > p->max_size)
499 p->max_size = size;
500 }
501 if (strcasecmp (type, "blob") == 0)
502 {
503 p->blob_values += count;
504 if (size > p->max_size)
505 p->max_size = size;
506 }
507 return;
508 }
509 p = p->next;
510 }
511 /* inserting a new field */
512 p = malloc (sizeof (struct field_item_infos));
513 p->ordinal = ordinal;
514 len = strlen (col_name);
515 p->col_name = malloc (len + 1);
516 strcpy (p->col_name, col_name);
517 p->null_values = 0;
518 p->integer_values = 0;
519 p->double_values = 0;
520 p->text_values = 0;
521 p->blob_values = 0;
522 p->max_size = -1;
523 p->int_minmax_set = 0;
524 p->int_min = 0;
525 p->int_max = 0;
526 p->dbl_minmax_set = 0;
527 p->dbl_min = 0.0;
528 p->dbl_max = 0.0;
529 p->next = NULL;
530 if (strcasecmp (type, "null") == 0)
531 p->null_values += count;
532 if (strcasecmp (type, "integer") == 0)
533 p->integer_values += count;
534 if (strcasecmp (type, "real") == 0)
535 p->double_values += count;
536 if (strcasecmp (type, "text") == 0)
537 {
538 p->text_values += count;
539 if (size > p->max_size)
540 p->max_size = size;
541 }
542 if (strcasecmp (type, "blob") == 0)
543 {
544 p->blob_values += count;
545 if (size > p->max_size)
546 p->max_size = size;
547 }
548 if (infos->first == NULL)
549 infos->first = p;
550 if (infos->last != NULL)
551 infos->last->next = p;
552 infos->last = p;
553 }
554
555 static void
update_field_infos_int_minmax(struct field_container_infos * infos,const char * col_name,int int_min,int int_max)556 update_field_infos_int_minmax (struct field_container_infos *infos,
557 const char *col_name, int int_min, int int_max)
558 {
559 /* updating the field container infos - Int MinMax */
560 struct field_item_infos *p = infos->first;
561 while (p)
562 {
563 if (strcasecmp (col_name, p->col_name) == 0)
564 {
565 p->int_minmax_set = 1;
566 p->int_min = int_min;
567 p->int_max = int_max;
568 return;
569 }
570 p = p->next;
571 }
572 }
573
574 static void
update_field_infos_double_minmax(struct field_container_infos * infos,const char * col_name,double dbl_min,double dbl_max)575 update_field_infos_double_minmax (struct field_container_infos *infos,
576 const char *col_name, double dbl_min,
577 double dbl_max)
578 {
579 /* updating the field container infos - Double MinMax */
580 struct field_item_infos *p = infos->first;
581 while (p)
582 {
583 if (strcasecmp (col_name, p->col_name) == 0)
584 {
585 p->dbl_minmax_set = 1;
586 p->dbl_min = dbl_min;
587 p->dbl_max = dbl_max;
588 return;
589 }
590 p = p->next;
591 }
592 }
593
594 static void
free_field_infos(struct field_container_infos * infos)595 free_field_infos (struct field_container_infos *infos)
596 {
597 /* memory cleanup - freeing a field infos container */
598 struct field_item_infos *p = infos->first;
599 struct field_item_infos *pn;
600 while (p)
601 {
602 /* destroying field items */
603 pn = p->next;
604 if (p->col_name)
605 free (p->col_name);
606 free (p);
607 p = pn;
608 }
609 }
610
611 static int
do_update_field_infos(sqlite3 * sqlite,const char * table,const char * column,struct field_container_infos * infos)612 do_update_field_infos (sqlite3 * sqlite, const char *table,
613 const char *column, struct field_container_infos *infos)
614 {
615 /* update GEOMETRY_COLUMNS_FIELD_INFOS Version >= 4.0.0 */
616 char sql[8192];
617 char *sql_statement;
618 int ret;
619 int error = 0;
620 sqlite3_stmt *stmt;
621 struct field_item_infos *p = infos->first;
622
623 /* deleting any previous row */
624 sql_statement = sqlite3_mprintf ("DELETE FROM geometry_columns_field_infos "
625 "WHERE Lower(f_table_name) = Lower(%Q) AND "
626 "Lower(f_geometry_column) = Lower(%Q)",
627 table, column);
628 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, NULL);
629 sqlite3_free (sql_statement);
630 if (ret != SQLITE_OK)
631 return 0;
632
633 /* reinserting yet again */
634 strcpy (sql, "INSERT INTO geometry_columns_field_infos ");
635 strcat (sql, "(f_table_name, f_geometry_column, ordinal, ");
636 strcat (sql, "column_name, null_values, integer_values, ");
637 strcat (sql, "double_values, text_values, blob_values, max_size, ");
638 strcat (sql, "integer_min, integer_max, double_min, double_max) ");
639 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
640
641 /* compiling SQL prepared statement */
642 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
643 if (ret != SQLITE_OK)
644 return 0;
645
646 while (p)
647 {
648 /* binding INSERT params */
649 sqlite3_reset (stmt);
650 sqlite3_clear_bindings (stmt);
651 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
652 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
653 sqlite3_bind_int (stmt, 3, p->ordinal);
654 sqlite3_bind_text (stmt, 4, p->col_name, strlen (p->col_name),
655 SQLITE_STATIC);
656 sqlite3_bind_int (stmt, 5, p->null_values);
657 sqlite3_bind_int (stmt, 6, p->integer_values);
658 sqlite3_bind_int (stmt, 7, p->double_values);
659 sqlite3_bind_int (stmt, 8, p->text_values);
660 sqlite3_bind_int (stmt, 9, p->blob_values);
661 if (p->max_size < 0)
662 sqlite3_bind_null (stmt, 10);
663 else
664 sqlite3_bind_int (stmt, 10, p->max_size);
665 if (p->int_minmax_set)
666 {
667 sqlite3_bind_int (stmt, 11, p->int_min);
668 sqlite3_bind_int (stmt, 12, p->int_max);
669 }
670 else
671 {
672 sqlite3_bind_null (stmt, 11);
673 sqlite3_bind_null (stmt, 12);
674 }
675 if (p->dbl_minmax_set)
676 {
677 sqlite3_bind_double (stmt, 13, p->dbl_min);
678 sqlite3_bind_double (stmt, 14, p->dbl_max);
679 }
680 else
681 {
682 sqlite3_bind_null (stmt, 13);
683 sqlite3_bind_null (stmt, 14);
684 }
685 ret = sqlite3_step (stmt);
686 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
687 ;
688 else
689 error = 1;
690 p = p->next;
691 }
692 ret = sqlite3_finalize (stmt);
693 if (ret != SQLITE_OK)
694 return 0;
695 if (error)
696 return 0;
697 return 1;
698 }
699
700 static int
do_update_views_field_infos(sqlite3 * sqlite,const char * table,const char * column,struct field_container_infos * infos)701 do_update_views_field_infos (sqlite3 * sqlite, const char *table,
702 const char *column,
703 struct field_container_infos *infos)
704 {
705 /* update VIEW_GEOMETRY_COLUMNS_FIELD_INFOS Version >= 4.0.0 */
706 char sql[8192];
707 char *sql_statement;
708 int ret;
709 int error = 0;
710 sqlite3_stmt *stmt;
711 struct field_item_infos *p = infos->first;
712
713 /* deleting any previous row */
714 sql_statement =
715 sqlite3_mprintf ("DELETE FROM views_geometry_columns_field_infos "
716 "WHERE Lower(view_name) = Lower(%Q) AND "
717 "Lower(view_geometry) = Lower(%Q)", table, column);
718 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, NULL);
719 sqlite3_free (sql_statement);
720 if (ret != SQLITE_OK)
721 return 0;
722
723 /* reinserting yet again */
724 strcpy (sql, "INSERT INTO views_geometry_columns_field_infos ");
725 strcat (sql, "(view_name, view_geometry, ordinal, ");
726 strcat (sql, "column_name, null_values, integer_values, ");
727 strcat (sql, "double_values, text_values, blob_values, max_size, ");
728 strcat (sql, "integer_min, integer_max, double_min, double_max) ");
729 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
730
731 /* compiling SQL prepared statement */
732 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
733 if (ret != SQLITE_OK)
734 return 0;
735
736 while (p)
737 {
738 /* binding INSERT params */
739 sqlite3_reset (stmt);
740 sqlite3_clear_bindings (stmt);
741 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
742 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
743 sqlite3_bind_int (stmt, 3, p->ordinal);
744 sqlite3_bind_text (stmt, 4, p->col_name, strlen (p->col_name),
745 SQLITE_STATIC);
746 sqlite3_bind_int (stmt, 5, p->null_values);
747 sqlite3_bind_int (stmt, 6, p->integer_values);
748 sqlite3_bind_int (stmt, 7, p->double_values);
749 sqlite3_bind_int (stmt, 8, p->text_values);
750 sqlite3_bind_int (stmt, 9, p->blob_values);
751 if (p->max_size < 0)
752 sqlite3_bind_null (stmt, 10);
753 else
754 sqlite3_bind_int (stmt, 10, p->max_size);
755 if (p->int_minmax_set)
756 {
757 sqlite3_bind_int (stmt, 11, p->int_min);
758 sqlite3_bind_int (stmt, 12, p->int_max);
759 }
760 else
761 {
762 sqlite3_bind_null (stmt, 11);
763 sqlite3_bind_null (stmt, 12);
764 }
765 if (p->dbl_minmax_set)
766 {
767 sqlite3_bind_double (stmt, 13, p->dbl_min);
768 sqlite3_bind_double (stmt, 14, p->dbl_max);
769 }
770 else
771 {
772 sqlite3_bind_null (stmt, 13);
773 sqlite3_bind_null (stmt, 14);
774 }
775 ret = sqlite3_step (stmt);
776 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
777 ;
778 else
779 error = 1;
780 p = p->next;
781 }
782 ret = sqlite3_finalize (stmt);
783 if (ret != SQLITE_OK)
784 return 0;
785 if (error)
786 return 0;
787 return 1;
788 }
789
790 static int
do_update_virts_field_infos(sqlite3 * sqlite,const char * table,const char * column,struct field_container_infos * infos)791 do_update_virts_field_infos (sqlite3 * sqlite, const char *table,
792 const char *column,
793 struct field_container_infos *infos)
794 {
795 /* update VIRTS_GEOMETRY_COLUMNS_FIELD_INFOS Version >= 4.0.0 */
796 char sql[8192];
797 char *sql_statement;
798 int ret;
799 int error = 0;
800 sqlite3_stmt *stmt;
801 struct field_item_infos *p = infos->first;
802
803 /* deleting any previous row */
804 sql_statement =
805 sqlite3_mprintf ("DELETE FROM virts_geometry_columns_field_infos "
806 "WHERE Lower(virt_name) = Lower(%Q) AND "
807 "Lower(virt_geometry) = Lower(%Q)", table, column);
808 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, NULL);
809 sqlite3_free (sql_statement);
810 if (ret != SQLITE_OK)
811 return 0;
812
813 /* reinserting yet again */
814 strcpy (sql, "INSERT INTO virts_geometry_columns_field_infos ");
815 strcat (sql, "(virt_name, virt_geometry, ordinal, ");
816 strcat (sql, "column_name, null_values, integer_values, ");
817 strcat (sql, "double_values, text_values, blob_values, max_size, ");
818 strcat (sql, "integer_min, integer_max, double_min, double_max) ");
819 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
820
821 /* compiling SQL prepared statement */
822 ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
823 if (ret != SQLITE_OK)
824 return 0;
825
826 while (p)
827 {
828 /* binding INSERT params */
829 sqlite3_reset (stmt);
830 sqlite3_clear_bindings (stmt);
831 sqlite3_bind_text (stmt, 1, table, strlen (table), SQLITE_STATIC);
832 sqlite3_bind_text (stmt, 2, column, strlen (column), SQLITE_STATIC);
833 sqlite3_bind_int (stmt, 3, p->ordinal);
834 sqlite3_bind_text (stmt, 4, p->col_name, strlen (p->col_name),
835 SQLITE_STATIC);
836 sqlite3_bind_int (stmt, 5, p->null_values);
837 sqlite3_bind_int (stmt, 6, p->integer_values);
838 sqlite3_bind_int (stmt, 7, p->double_values);
839 sqlite3_bind_int (stmt, 8, p->text_values);
840 sqlite3_bind_int (stmt, 9, p->blob_values);
841 if (p->max_size < 0)
842 sqlite3_bind_null (stmt, 10);
843 else
844 sqlite3_bind_int (stmt, 10, p->max_size);
845 if (p->int_minmax_set)
846 {
847 sqlite3_bind_int (stmt, 11, p->int_min);
848 sqlite3_bind_int (stmt, 12, p->int_max);
849 }
850 else
851 {
852 sqlite3_bind_null (stmt, 11);
853 sqlite3_bind_null (stmt, 12);
854 }
855 if (p->dbl_minmax_set)
856 {
857 sqlite3_bind_double (stmt, 13, p->dbl_min);
858 sqlite3_bind_double (stmt, 14, p->dbl_max);
859 }
860 else
861 {
862 sqlite3_bind_null (stmt, 13);
863 sqlite3_bind_null (stmt, 14);
864 }
865 ret = sqlite3_step (stmt);
866 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
867 ;
868 else
869 error = 1;
870 p = p->next;
871 }
872 ret = sqlite3_finalize (stmt);
873 if (ret != SQLITE_OK)
874 return 0;
875 if (error)
876 return 0;
877 return 1;
878 }
879
880 static int
do_compute_minmax(sqlite3 * sqlite,const char * table,struct field_container_infos * infos)881 do_compute_minmax (sqlite3 * sqlite, const char *table,
882 struct field_container_infos *infos)
883 {
884 /* Pass2 - computing Integer / Double min/max ranges */
885 char *quoted;
886 char *sql_statement;
887 int int_min;
888 int int_max;
889 double dbl_min;
890 double dbl_max;
891 int ret;
892 int i;
893 int c;
894 char **results;
895 int rows;
896 int columns;
897 const char *col_name;
898 int is_double;
899 int comma = 0;
900 int empty = 1;
901 gaiaOutBuffer out_buf;
902 struct field_item_infos *ptr;
903
904 gaiaOutBufferInitialize (&out_buf);
905 gaiaAppendToOutBuffer (&out_buf, "SELECT DISTINCT ");
906 ptr = infos->first;
907 while (ptr)
908 {
909 quoted = gaiaDoubleQuotedSql (ptr->col_name);
910 if (ptr->integer_values >= 0 && ptr->double_values == 0
911 && ptr->blob_values == 0 && ptr->text_values == 0)
912 {
913 if (comma)
914 sql_statement =
915 sqlite3_mprintf (", 0, %Q, min(\"%s\"), max(\"%s\")",
916 ptr->col_name, quoted, quoted);
917 else
918 {
919 comma = 1;
920 sql_statement =
921 sqlite3_mprintf (" 0, %Q, min(\"%s\"), max(\"%s\")",
922 ptr->col_name, quoted, quoted);
923 }
924 gaiaAppendToOutBuffer (&out_buf, sql_statement);
925 sqlite3_free (sql_statement);
926 empty = 0;
927 }
928 if (ptr->double_values >= 0 && ptr->integer_values == 0
929 && ptr->blob_values == 0 && ptr->text_values == 0)
930 {
931 if (comma)
932 sql_statement =
933 sqlite3_mprintf (", 1, %Q, min(\"%s\"), max(\"%s\")",
934 ptr->col_name, quoted, quoted);
935 else
936 {
937 comma = 1;
938 sql_statement =
939 sqlite3_mprintf (" 1, %Q, min(\"%s\"), max(\"%s\")",
940 ptr->col_name, quoted, quoted);
941 }
942 gaiaAppendToOutBuffer (&out_buf, sql_statement);
943 sqlite3_free (sql_statement);
944 empty = 0;
945 }
946 free (quoted);
947 ptr = ptr->next;
948 }
949 if (out_buf.Buffer == NULL)
950 return 0;
951 if (empty)
952 {
953 /* no columns to check */
954 gaiaOutBufferReset (&out_buf);
955 return 1;
956
957 }
958 quoted = gaiaDoubleQuotedSql (table);
959 sql_statement = sqlite3_mprintf (" FROM \"%s\"", quoted);
960 free (quoted);
961 gaiaAppendToOutBuffer (&out_buf, sql_statement);
962 sqlite3_free (sql_statement);
963 /* executing the SQL query */
964 ret = sqlite3_get_table (sqlite, out_buf.Buffer, &results, &rows, &columns,
965 NULL);
966 gaiaOutBufferReset (&out_buf);
967 if (ret != SQLITE_OK)
968 return 0;
969 if (rows < 1)
970 ;
971 else
972 {
973 for (i = 1; i <= rows; i++)
974 {
975 for (c = 0; c < columns; c += 4)
976 {
977 /* retrieving field infos */
978 is_double = atoi (results[(i * columns) + c + 0]);
979 col_name = results[(i * columns) + c + 1];
980 if (results[(i * columns) + c + 2] != NULL
981 && results[(i * columns) + c + 3] != NULL)
982 {
983 if (!is_double)
984 {
985 int_min =
986 atoi (results[(i * columns) + c + 2]);
987 int_max =
988 atoi (results[(i * columns) + c + 3]);
989 update_field_infos_int_minmax (infos,
990 col_name,
991 int_min,
992 int_max);
993 }
994 else
995 {
996 dbl_min =
997 atof (results[(i * columns) + c + 2]);
998 dbl_max =
999 atof (results[(i * columns) + c + 3]);
1000 update_field_infos_double_minmax (infos,
1001 col_name,
1002 dbl_min,
1003 dbl_max);
1004 }
1005 }
1006 }
1007 }
1008 }
1009 sqlite3_free_table (results);
1010
1011 return 1;
1012 }
1013
1014 static void
copy_attributes_into_layer(struct field_container_infos * infos,gaiaVectorLayerPtr lyr)1015 copy_attributes_into_layer (struct field_container_infos *infos,
1016 gaiaVectorLayerPtr lyr)
1017 {
1018 /* copying the AttributeField definitions into the VectorLayer */
1019 gaiaLayerAttributeFieldPtr fld;
1020 int len;
1021 struct field_item_infos *p = infos->first;
1022 while (p)
1023 {
1024 /* adding an AttributeField definition */
1025 fld = malloc (sizeof (gaiaLayerAttributeField));
1026 fld->Ordinal = p->ordinal;
1027 len = strlen (p->col_name);
1028 fld->AttributeFieldName = malloc (len + 1);
1029 strcpy (fld->AttributeFieldName, p->col_name);
1030 fld->NullValuesCount = p->null_values;
1031 fld->IntegerValuesCount = p->integer_values;
1032 fld->DoubleValuesCount = p->double_values;
1033 fld->TextValuesCount = p->text_values;
1034 fld->BlobValuesCount = p->blob_values;
1035 fld->MaxSize = NULL;
1036 fld->IntRange = NULL;
1037 fld->DoubleRange = NULL;
1038 if (p->max_size)
1039 {
1040 fld->MaxSize = malloc (sizeof (gaiaAttributeFieldMaxSize));
1041 fld->MaxSize->MaxSize = p->max_size;
1042 }
1043 if (p->int_minmax_set)
1044 {
1045 fld->IntRange = malloc (sizeof (gaiaAttributeFieldIntRange));
1046 fld->IntRange->MinValue = p->int_min;
1047 fld->IntRange->MaxValue = p->int_max;
1048 }
1049 if (p->dbl_minmax_set)
1050 {
1051 fld->DoubleRange =
1052 malloc (sizeof (gaiaAttributeFieldDoubleRange));
1053 fld->DoubleRange->MinValue = p->dbl_min;
1054 fld->DoubleRange->MaxValue = p->dbl_max;
1055 }
1056 fld->Next = NULL;
1057 if (lyr->First == NULL)
1058 lyr->First = fld;
1059 if (lyr->Last != NULL)
1060 lyr->Last->Next = fld;
1061 lyr->Last = fld;
1062 p = p->next;
1063 }
1064 }
1065
1066 SPATIALITE_PRIVATE int
doComputeFieldInfos(void * p_sqlite,const char * table,const char * column,int stat_type,void * p_lyr)1067 doComputeFieldInfos (void *p_sqlite, const char *table,
1068 const char *column, int stat_type, void *p_lyr)
1069 {
1070 /* computes FIELD_INFOS [single table/geometry] */
1071 sqlite3 *sqlite = (sqlite3 *) p_sqlite;
1072 gaiaVectorLayerPtr lyr = (gaiaVectorLayerPtr) p_lyr;
1073 char *sql_statement;
1074 char *quoted;
1075 int ret;
1076 int i;
1077 int c;
1078 char **results;
1079 int rows;
1080 int columns;
1081 int ordinal;
1082 const char *col_name;
1083 const char *type;
1084 const char *sz;
1085 int size;
1086 int count;
1087 int error = 0;
1088 int comma = 0;
1089 gaiaOutBuffer out_buf;
1090 gaiaOutBuffer group_by;
1091 struct field_container_infos infos;
1092
1093 gaiaOutBufferInitialize (&out_buf);
1094 gaiaOutBufferInitialize (&group_by);
1095 infos.first = NULL;
1096 infos.last = NULL;
1097
1098 /* retrieving the column names for the current table */
1099 /* then building the SQL query statement */
1100 quoted = gaiaDoubleQuotedSql (table);
1101 sql_statement = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", quoted);
1102 free (quoted);
1103 ret =
1104 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
1105 NULL);
1106 sqlite3_free (sql_statement);
1107 if (ret != SQLITE_OK)
1108 return 0;
1109
1110 if (rows < 1)
1111 ;
1112 else
1113 {
1114 gaiaAppendToOutBuffer (&out_buf, "SELECT DISTINCT Count(*)");
1115 gaiaAppendToOutBuffer (&group_by, "GROUP BY");
1116 for (i = 1; i <= rows; i++)
1117 {
1118 ordinal = atoi (results[(i * columns) + 0]);
1119 col_name = results[(i * columns) + 1];
1120 quoted = gaiaDoubleQuotedSql (col_name);
1121 sql_statement =
1122 sqlite3_mprintf
1123 (", %d, %Q AS col_%d, typeof(\"%s\") AS typ_%d, max(length(\"%s\"))",
1124 ordinal, col_name, ordinal, quoted, ordinal, quoted);
1125 free (quoted);
1126 gaiaAppendToOutBuffer (&out_buf, sql_statement);
1127 sqlite3_free (sql_statement);
1128 if (!comma)
1129 {
1130 comma = 1;
1131 sql_statement =
1132 sqlite3_mprintf (" col_%d, typ_%d", ordinal, ordinal);
1133 }
1134 else
1135 sql_statement =
1136 sqlite3_mprintf (", col_%d, typ_%d", ordinal, ordinal);
1137 gaiaAppendToOutBuffer (&group_by, sql_statement);
1138 sqlite3_free (sql_statement);
1139 }
1140 }
1141 sqlite3_free_table (results);
1142
1143 if (out_buf.Buffer == NULL)
1144 return 0;
1145 quoted = gaiaDoubleQuotedSql (table);
1146 sql_statement = sqlite3_mprintf (" FROM \"%s\" ", quoted);
1147 free (quoted);
1148 gaiaAppendToOutBuffer (&out_buf, sql_statement);
1149 sqlite3_free (sql_statement);
1150 gaiaAppendToOutBuffer (&out_buf, group_by.Buffer);
1151 gaiaOutBufferReset (&group_by);
1152
1153 /* executing the SQL query */
1154 ret = sqlite3_get_table (sqlite, out_buf.Buffer, &results, &rows, &columns,
1155 NULL);
1156 gaiaOutBufferReset (&out_buf);
1157 if (ret != SQLITE_OK)
1158 return 0;
1159 if (rows < 1)
1160 ;
1161 else
1162 {
1163 for (i = 1; i <= rows; i++)
1164 {
1165 count = atoi (results[(i * columns) + 0]);
1166 for (c = 1; c < columns; c += 4)
1167 {
1168 /* retrieving field infos */
1169 ordinal = atoi (results[(i * columns) + c + 0]);
1170 col_name = results[(i * columns) + c + 1];
1171 type = results[(i * columns) + c + 2];
1172 sz = results[(i * columns) + c + 3];
1173 if (sz == NULL)
1174 size = -1;
1175 else
1176 size = atoi (sz);
1177 update_field_infos (&infos, ordinal, col_name, type, size,
1178 count);
1179 }
1180 }
1181 }
1182 sqlite3_free_table (results);
1183
1184 /* Pass-2: computing INTEGER and DOUBLE min/max ranges */
1185 if (!error)
1186 {
1187 if (!do_compute_minmax (sqlite, table, &infos))
1188 error = 1;
1189 }
1190
1191 switch (stat_type)
1192 {
1193 case SPATIALITE_STATISTICS_LEGACY:
1194 if (!error)
1195 copy_attributes_into_layer (&infos, lyr);
1196 free_field_infos (&infos);
1197 if (error)
1198 return 0;
1199 return 1;
1200 break;
1201 case SPATIALITE_STATISTICS_GENUINE:
1202 if (!do_update_field_infos (sqlite, table, column, &infos))
1203 error = 1;
1204 break;
1205 case SPATIALITE_STATISTICS_VIEWS:
1206 if (!do_update_views_field_infos (sqlite, table, column, &infos))
1207 error = 1;
1208 break;
1209 case SPATIALITE_STATISTICS_VIRTS:
1210 if (!do_update_virts_field_infos (sqlite, table, column, &infos))
1211 error = 1;
1212 break;
1213 };
1214 free_field_infos (&infos);
1215 if (error)
1216 return 0;
1217 return 1;
1218 }
1219
1220 static int
do_compute_layer_statistics(sqlite3 * sqlite,const char * table,const char * column,int stat_type)1221 do_compute_layer_statistics (sqlite3 * sqlite, const char *table,
1222 const char *column, int stat_type)
1223 {
1224 /* computes LAYER_STATISTICS [single table/geometry] */
1225 int ret;
1226 int error = 0;
1227 int count;
1228 double min_x = DBL_MAX;
1229 double min_y = DBL_MAX;
1230 double max_x = 0.0 - DBL_MAX;
1231 double max_y = 0.0 - DBL_MAX;
1232 int has_coords = 1;
1233 char *quoted;
1234 char *col_quoted;
1235 char *sql_statement;
1236 sqlite3_stmt *stmt;
1237 int metadata_version = checkSpatialMetaData (sqlite);
1238
1239 quoted = gaiaDoubleQuotedSql ((const char *) table);
1240 col_quoted = gaiaDoubleQuotedSql ((const char *) column);
1241
1242 if (metadata_version == 4)
1243 {
1244 /* GeoPackage Vector only */
1245 sql_statement = sqlite3_mprintf ("UPDATE gpkg_contents SET "
1246 "min_x = (SELECT Min(MbrMinX(%s)) FROM \"%s\"),"
1247 "min_y = (SELECT Min(MbrMinY(%s)) FROM \"%s\"),"
1248 "max_x = (SELECT Max(MbrMinX(%s)) FROM \"%s\"),"
1249 "max_y = (SELECT Max(MbrMinY(%s)) FROM \"%s\"),"
1250 "last_change = strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ', 'now') "
1251 "WHERE ((lower(table_name) = lower('%s')) AND (Lower(data_type) = 'features'))",
1252 col_quoted, quoted, col_quoted,
1253 quoted, col_quoted, quoted,
1254 col_quoted, quoted, quoted);
1255 free (quoted);
1256 free (col_quoted);
1257 if (sqlite3_exec (sqlite, sql_statement, NULL, NULL, NULL) !=
1258 SQLITE_OK)
1259 {
1260 sqlite3_free (sql_statement);
1261 return 0;
1262 }
1263 sqlite3_free (sql_statement);
1264 return 1;
1265 }
1266 else
1267 {
1268 sql_statement = sqlite3_mprintf ("SELECT Count(*), "
1269 "Min(MbrMinX(\"%s\")), Min(MbrMinY(\"%s\")), Max(MbrMaxX(\"%s\")), Max(MbrMaxY(\"%s\")) "
1270 "FROM \"%s\"", col_quoted,
1271 col_quoted, col_quoted, col_quoted,
1272 quoted);
1273 }
1274 free (quoted);
1275 free (col_quoted);
1276
1277 /* compiling SQL prepared statement */
1278 ret =
1279 sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
1280 &stmt, NULL);
1281 sqlite3_free (sql_statement);
1282 if (ret != SQLITE_OK)
1283 return 0;
1284 while (1)
1285 {
1286 /* scrolling the result set rows */
1287 ret = sqlite3_step (stmt);
1288 if (ret == SQLITE_DONE)
1289 break; /* end of result set */
1290 if (ret == SQLITE_ROW)
1291 {
1292 count = sqlite3_column_int (stmt, 0);
1293 if (sqlite3_column_type (stmt, 1) == SQLITE_NULL)
1294 has_coords = 0;
1295 else
1296 min_x = sqlite3_column_double (stmt, 1);
1297 if (sqlite3_column_type (stmt, 2) == SQLITE_NULL)
1298 has_coords = 0;
1299 else
1300 min_y = sqlite3_column_double (stmt, 2);
1301 if (sqlite3_column_type (stmt, 3) == SQLITE_NULL)
1302 has_coords = 0;
1303 else
1304 max_x = sqlite3_column_double (stmt, 3);
1305 if (sqlite3_column_type (stmt, 4) == SQLITE_NULL)
1306 has_coords = 0;
1307 else
1308 max_y = sqlite3_column_double (stmt, 4);
1309 switch (stat_type)
1310 {
1311 case SPATIALITE_STATISTICS_GENUINE:
1312 if (!do_update_layer_statistics
1313 (sqlite, table, column, count, has_coords, min_x,
1314 min_y, max_x, max_y))
1315 error = 1;
1316 break;
1317 case SPATIALITE_STATISTICS_VIEWS:
1318 if (!do_update_views_layer_statistics
1319 (sqlite, table, column, count, has_coords, min_x,
1320 min_y, max_x, max_y))
1321 error = 1;
1322 break;
1323 case SPATIALITE_STATISTICS_VIRTS:
1324 if (!do_update_virts_layer_statistics
1325 (sqlite, table, column, count, has_coords, min_x,
1326 min_y, max_x, max_y))
1327 error = 1;
1328 break;
1329 };
1330 }
1331 else
1332 error = 1;
1333 }
1334 ret = sqlite3_finalize (stmt);
1335 if (ret != SQLITE_OK)
1336 return 0;
1337 if (error)
1338 return 0;
1339 if (metadata_version == 3)
1340 {
1341 /* current metadata style >= v.4.0.0 */
1342 if (!doComputeFieldInfos (sqlite, table, column, stat_type, NULL))
1343 return 0;
1344 }
1345 return 1;
1346 }
1347
1348 static int
genuine_layer_statistics_v4(sqlite3 * sqlite,const char * table,const char * column)1349 genuine_layer_statistics_v4 (sqlite3 * sqlite, const char *table,
1350 const char *column)
1351 {
1352 /* updating GEOMETRY_COLUMNS_STATISTICS Version >= 4.0.0 */
1353 char *sql_statement;
1354 int ret;
1355 const char *f_table_name;
1356 const char *f_geometry_column;
1357 int i;
1358 char **results;
1359 int rows;
1360 int columns;
1361 int error = 0;
1362
1363 if (table == NULL && column == NULL)
1364 {
1365 /* processing any table/geometry found in GEOMETRY_COLUMNS */
1366 sql_statement =
1367 sqlite3_mprintf ("SELECT t.f_table_name, t.f_geometry_column "
1368 "FROM geometry_columns_time AS t, "
1369 "geometry_columns_statistics AS s "
1370 "WHERE Lower(s.f_table_name) = Lower(t.f_table_name) AND "
1371 "Lower(s.f_geometry_column) = Lower(t.f_geometry_column) AND "
1372 "(s.last_verified < t.last_insert OR "
1373 "s.last_verified < t.last_update OR "
1374 "s.last_verified < t.last_delete OR "
1375 "s.last_verified IS NULL)");
1376 }
1377 else if (column == NULL)
1378 {
1379 /* processing any geometry belonging to this table */
1380 sql_statement =
1381 sqlite3_mprintf ("SELECT t.f_table_name, t.f_geometry_column "
1382 "FROM geometry_columns_time AS t, "
1383 "geometry_columns_statistics AS s "
1384 "WHERE Lower(t.f_table_name) = Lower(%Q) AND "
1385 "Lower(s.f_table_name) = Lower(t.f_table_name) AND "
1386 "Lower(s.f_geometry_column) = Lower(t.f_geometry_column) AND "
1387 "(s.last_verified < t.last_insert OR "
1388 "s.last_verified < t.last_update OR "
1389 "s.last_verified < t.last_delete OR "
1390 "s.last_verified IS NULL)", table);
1391 }
1392 else
1393 {
1394 /* processing a single table/geometry entry */
1395 sql_statement =
1396 sqlite3_mprintf ("SELECT t.f_table_name, t.f_geometry_column "
1397 "FROM geometry_columns_time AS t, "
1398 "geometry_columns_statistics AS s "
1399 "WHERE Lower(t.f_table_name) = Lower(%Q) AND "
1400 "Lower(t.f_geometry_column) = Lower(%Q) AND "
1401 "Lower(s.f_table_name) = Lower(t.f_table_name) AND "
1402 "Lower(s.f_geometry_column) = Lower(t.f_geometry_column) AND "
1403 "(s.last_verified < t.last_insert OR "
1404 "s.last_verified < t.last_update OR "
1405 "s.last_verified < t.last_delete OR "
1406 "s.last_verified IS NULL)", table, column);
1407 }
1408 ret =
1409 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
1410 NULL);
1411 sqlite3_free (sql_statement);
1412 if (ret != SQLITE_OK)
1413 return 0;
1414
1415 if (rows < 1)
1416 ;
1417 else
1418 {
1419 for (i = 1; i <= rows; i++)
1420 {
1421 f_table_name = results[(i * columns) + 0];
1422 f_geometry_column = results[(i * columns) + 1];
1423 if (!do_compute_layer_statistics
1424 (sqlite, f_table_name, f_geometry_column,
1425 SPATIALITE_STATISTICS_GENUINE))
1426 {
1427 error = 1;
1428 break;
1429 }
1430 }
1431 }
1432 sqlite3_free_table (results);
1433 if (error)
1434 return 0;
1435 return 1;
1436 }
1437
1438 static int
genuine_layer_statistics(sqlite3 * sqlite,const char * table,const char * column)1439 genuine_layer_statistics (sqlite3 * sqlite, const char *table,
1440 const char *column)
1441 {
1442 /* updating genuine LAYER_STATISTICS metadata */
1443 char *sql_statement;
1444 int ret;
1445 const char *f_table_name;
1446 const char *f_geometry_column;
1447 int i;
1448 char **results;
1449 int rows;
1450 int columns;
1451 int error = 0;
1452 int metadata_version = checkSpatialMetaData (sqlite);
1453
1454 if (metadata_version == 3)
1455 {
1456 /* current metadata style >= v.4.0.0 */
1457 return genuine_layer_statistics_v4 (sqlite, table, column);
1458 }
1459
1460 if (table == NULL && column == NULL)
1461 {
1462 /* processing any table/geometry found in GEOMETRY_COLUMNS */
1463 if (metadata_version == 4)
1464 {
1465 /* GeoPackage Vector only */
1466 sql_statement =
1467 sqlite3_mprintf
1468 ("SELECT table_name, column_name FROM gpkg_geometry_columns");
1469 }
1470 else
1471 {
1472 sql_statement =
1473 sqlite3_mprintf
1474 ("SELECT f_table_name, f_geometry_column FROM geometry_columns");
1475 }
1476 }
1477 else if (column == NULL)
1478 {
1479 /* processing any geometry belonging to this table */
1480 if (metadata_version == 4)
1481 {
1482 /* GeoPackage Vector only */
1483 sql_statement =
1484 sqlite3_mprintf
1485 ("SELECT table_name, column_name FROM gpkg_geometry_columns WHERE (lower(table_name) = lower('%s'))",
1486 table);
1487 }
1488 else
1489 {
1490 sql_statement =
1491 sqlite3_mprintf ("SELECT f_table_name, f_geometry_column "
1492 "FROM geometry_columns "
1493 "WHERE Lower(f_table_name) = Lower(%Q)",
1494 table);
1495 }
1496 }
1497 else
1498 {
1499 /* processing a single table/geometry entry */
1500 if (metadata_version == 4)
1501 {
1502 /* GeoPackage Vector only */
1503 sql_statement =
1504 sqlite3_mprintf
1505 ("SELECT table_name, column_name FROM gpkg_geometry_columns WHERE ((lower(table_name) = lower('%s')) AND (Lower(column_name) = lower('%s')))",
1506 table, column);
1507 }
1508 else
1509 {
1510 sql_statement =
1511 sqlite3_mprintf ("SELECT f_table_name, f_geometry_column "
1512 "FROM geometry_columns "
1513 "WHERE Lower(f_table_name) = Lower(%Q) "
1514 "AND Lower(f_geometry_column) = Lower(%Q)",
1515 table, column);
1516 }
1517 }
1518 ret =
1519 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
1520 NULL);
1521 sqlite3_free (sql_statement);
1522 if (ret != SQLITE_OK)
1523 return 0;
1524 if (rows < 1)
1525 ;
1526 else
1527 {
1528 for (i = 1; i <= rows; i++)
1529 {
1530 f_table_name = results[(i * columns) + 0];
1531 f_geometry_column = results[(i * columns) + 1];
1532 if (!do_compute_layer_statistics
1533 (sqlite, f_table_name, f_geometry_column,
1534 SPATIALITE_STATISTICS_GENUINE))
1535 {
1536 error = 1;
1537 break;
1538 }
1539 }
1540 }
1541 sqlite3_free_table (results);
1542 if (error)
1543 return 0;
1544 return 1;
1545 }
1546
1547 static int
views_layer_statistics(sqlite3 * sqlite,const char * table,const char * column)1548 views_layer_statistics (sqlite3 * sqlite, const char *table, const char *column)
1549 {
1550 /* updating VIEWS_LAYER_STATISTICS metadata */
1551 char *sql_statement;
1552 int ret;
1553 const char *view_name;
1554 const char *view_geometry;
1555 int i;
1556 char **results;
1557 int rows;
1558 int columns;
1559 int error = 0;
1560
1561 if (table == NULL && column == NULL)
1562 {
1563 /* processing any table/geometry found in VIEWS_GEOMETRY_COLUMNS */
1564 sql_statement = sqlite3_mprintf ("SELECT view_name, view_geometry "
1565 "FROM views_geometry_columns");
1566 }
1567 else if (column == NULL)
1568 {
1569 /* processing any geometry belonging to this table */
1570 sql_statement = sqlite3_mprintf ("SELECT view_name, view_geometry "
1571 "FROM views_geometry_columns "
1572 "WHERE Lower(view_name) = Lower(%Q)",
1573 table);
1574 }
1575 else
1576 {
1577 /* processing a single table/geometry entry */
1578 sql_statement = sqlite3_mprintf ("SELECT view_name, view_geometry "
1579 "FROM views_geometry_columns "
1580 "WHERE Lower(view_name) = Lower(%Q) "
1581 "AND Lower(view_geometry) = Lower(%Q)",
1582 table, column);
1583 }
1584 ret =
1585 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
1586 NULL);
1587 sqlite3_free (sql_statement);
1588 if (ret != SQLITE_OK)
1589 return 0;
1590 if (rows < 1)
1591 ;
1592 else
1593 {
1594 for (i = 1; i <= rows; i++)
1595 {
1596 view_name = results[(i * columns) + 0];
1597 view_geometry = results[(i * columns) + 1];
1598 if (!do_compute_layer_statistics
1599 (sqlite, view_name, view_geometry,
1600 SPATIALITE_STATISTICS_VIEWS))
1601 {
1602 error = 1;
1603 break;
1604 }
1605 }
1606 }
1607 sqlite3_free_table (results);
1608 if (error)
1609 return 0;
1610 return 1;
1611 }
1612
1613 static int
virts_layer_statistics(sqlite3 * sqlite,const char * table,const char * column)1614 virts_layer_statistics (sqlite3 * sqlite, const char *table, const char *column)
1615 {
1616 /* updating VIRTS_LAYER_STATISTICS metadata */
1617 char *sql_statement;
1618 int ret;
1619 const char *f_table_name;
1620 const char *f_geometry_column;
1621 int i;
1622 char **results;
1623 int rows;
1624 int columns;
1625 int error = 0;
1626
1627 if (table == NULL && column == NULL)
1628 {
1629 /* processing any table/geometry found in VIRTS_GEOMETRY_COLUMNS */
1630 sql_statement = sqlite3_mprintf ("SELECT virt_name, virt_geometry "
1631 "FROM virts_geometry_columns");
1632 }
1633 else if (column == NULL)
1634 {
1635 /* processing any geometry belonging to this table */
1636 sql_statement = sqlite3_mprintf ("SELECT virt_name, virt_geometry "
1637 "FROM virts_geometry_columns "
1638 "WHERE Lower(virt_name) = Lower(%Q)",
1639 table);
1640 }
1641 else
1642 {
1643 /* processing a single table/geometry entry */
1644 sql_statement = sqlite3_mprintf ("SELECT virt_name, virt_geometry "
1645 "FROM virts_geometry_columns "
1646 "WHERE Lower(virt_name) = Lower(%Q) "
1647 "AND Lower(virt_geometry) = Lower(%Q)",
1648 table, column);
1649 }
1650 ret =
1651 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
1652 NULL);
1653 sqlite3_free (sql_statement);
1654 if (ret != SQLITE_OK)
1655 return 0;
1656 if (rows < 1)
1657 ;
1658 else
1659 {
1660 for (i = 1; i <= rows; i++)
1661 {
1662 f_table_name = results[(i * columns) + 0];
1663 f_geometry_column = results[(i * columns) + 1];
1664 if (!do_compute_layer_statistics
1665 (sqlite, f_table_name, f_geometry_column,
1666 SPATIALITE_STATISTICS_VIRTS))
1667 {
1668 error = 1;
1669 break;
1670 }
1671 }
1672 }
1673 sqlite3_free_table (results);
1674 if (error)
1675 return 0;
1676 return 1;
1677 }
1678
1679 static int
has_views_metadata(sqlite3 * sqlite)1680 has_views_metadata (sqlite3 * sqlite)
1681 {
1682 /* testing if the VIEWS_GEOMETRY_COLUMNS table exists */
1683 char **results;
1684 int rows;
1685 int columns;
1686 int ret;
1687 int i;
1688 int defined = 0;
1689 ret =
1690 sqlite3_get_table (sqlite, "PRAGMA table_info(views_geometry_columns)",
1691 &results, &rows, &columns, NULL);
1692 if (ret != SQLITE_OK)
1693 return 0;
1694 if (rows < 1)
1695 ;
1696 else
1697 {
1698 for (i = 1; i <= rows; i++)
1699 defined = 1;
1700 }
1701 sqlite3_free_table (results);
1702 return defined;
1703 }
1704
1705 static int
has_virts_metadata(sqlite3 * sqlite)1706 has_virts_metadata (sqlite3 * sqlite)
1707 {
1708 /* testing if the VIRTS_GEOMETRY_COLUMNS table exists */
1709 char **results;
1710 int rows;
1711 int columns;
1712 int ret;
1713 int i;
1714 int defined = 0;
1715 ret =
1716 sqlite3_get_table (sqlite, "PRAGMA table_info(virts_geometry_columns)",
1717 &results, &rows, &columns, NULL);
1718 if (ret != SQLITE_OK)
1719 return 0;
1720 if (rows < 1)
1721 ;
1722 else
1723 {
1724 for (i = 1; i <= rows; i++)
1725 defined = 1;
1726 }
1727 sqlite3_free_table (results);
1728 return defined;
1729 }
1730
1731 SPATIALITE_DECLARE int
update_layer_statistics(sqlite3 * sqlite,const char * table,const char * column)1732 update_layer_statistics (sqlite3 * sqlite, const char *table,
1733 const char *column)
1734 {
1735 /* updating LAYER_STATISTICS metadata [main] */
1736 if (!genuine_layer_statistics (sqlite, table, column))
1737 return 0;
1738 if (has_views_metadata (sqlite))
1739 {
1740 if (!views_layer_statistics (sqlite, table, column))
1741 return 0;
1742 }
1743 if (has_virts_metadata (sqlite))
1744 {
1745 if (!virts_layer_statistics (sqlite, table, column))
1746 return 0;
1747 }
1748 return 1;
1749 }
1750
1751 struct table_params
1752 {
1753 /* a struct supporting Drop Table / Rename Table / Rename Column */
1754 char **rtrees;
1755 int n_rtrees;
1756 int is_view;
1757 int ok_geometry_columns;
1758 int ok_geometry_columns_time;
1759 int ok_views_geometry_columns;
1760 int ok_virts_geometry_columns;
1761 int ok_geometry_columns_auth;
1762 int ok_geometry_columns_field_infos;
1763 int ok_geometry_columns_statistics;
1764 int ok_views_geometry_columns_auth;
1765 int ok_views_geometry_columns_field_infos;
1766 int ok_views_geometry_columns_statistics;
1767 int ok_virts_geometry_columns_auth;
1768 int ok_virts_geometry_columns_field_infos;
1769 int ok_virts_geometry_columns_statistics;
1770 int ok_layer_statistics;
1771 int ok_views_layer_statistics;
1772 int ok_virts_layer_statistics;
1773 int ok_layer_params;
1774 int ok_layer_sub_classes;
1775 int ok_layer_table_layout;
1776 int ok_vector_coverages;
1777 int ok_vector_coverages_keyword;
1778 int ok_vector_coverages_srid;
1779 int ok_se_vector_styled_layers;
1780 int ok_se_raster_styled_layers;
1781 int metadata_version;
1782 int ok_gpkg_geometry_columns;
1783 int ok_gpkg_contents;
1784 int ok_gpkg_extensions;
1785 int ok_gpkg_tile_matrix;
1786 int ok_gpkg_tile_matrix_set;
1787 int ok_gpkg_ogr_contents;
1788 int ok_gpkg_metadata_reference;
1789 int gpkg_table_type;
1790 int ok_table_exists;
1791 int is_geometry_column;
1792 int count_geometry_columns;
1793 int has_topologies;
1794 int has_raster_coverages;
1795 int is_raster_coverage_entry;
1796 int table_type;
1797 int command_type;
1798 char *error_message;
1799 };
1800
1801 /*/ -- -- ---------------------------------- --
1802 // do_check_existing
1803 // -- -- ---------------------------------- --
1804 // table_type:
1805 // - 0 [default]: table and views
1806 // - 1: table only
1807 // - 2: view only
1808 // - 3: table,view,index and trigger [and anything else that may exist]
1809 // -- -- ---------------------------------- --
1810 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
1811 // -- -- ---------------------------------- --
1812 */
1813 static int
do_check_existing(sqlite3 * sqlite,const char * prefix,const char * table,int table_type)1814 do_check_existing (sqlite3 * sqlite, const char *prefix, const char *table,
1815 int table_type)
1816 {
1817 /* checking for an existing Table or View */
1818 char *sql;
1819 char *q_prefix;
1820 int count = 0;
1821 int ret;
1822 int i;
1823 char **results;
1824 int rows;
1825 int columns;
1826
1827 q_prefix = gaiaDoubleQuotedSql (prefix);
1828 switch (table_type)
1829 {
1830 case 1:
1831 sql = sqlite3_mprintf ("SELECT Count(*) FROM \"%s\".sqlite_master "
1832 "WHERE Upper(name) = Upper(%Q) AND type = 'table'",
1833 q_prefix, table);
1834 break;
1835 case 2:
1836 sql = sqlite3_mprintf ("SELECT Count(*) FROM \"%s\".sqlite_master "
1837 "WHERE Upper(name) = Upper(%Q) AND type = 'view'",
1838 q_prefix, table);
1839 break;
1840 case 3:
1841 sql = sqlite3_mprintf ("SELECT Count(*) FROM \"%s\".sqlite_master "
1842 "WHERE Upper(name) = Upper(%Q)", q_prefix,
1843 table);
1844 break;
1845 case 0:
1846 default:
1847 sql = sqlite3_mprintf ("SELECT Count(*) FROM \"%s\".sqlite_master "
1848 "WHERE Upper(name) = Upper(%Q) AND type IN ('table', 'view')",
1849 q_prefix, table);
1850 break;
1851 }
1852 free (q_prefix);
1853 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
1854 sqlite3_free (sql);
1855 if (ret != SQLITE_OK)
1856 goto unknown;
1857 if (rows < 1)
1858 ;
1859 else
1860 {
1861 for (i = 1; i <= rows; i++)
1862 count = atoi (results[(i * columns) + 0]);
1863 }
1864 sqlite3_free_table (results);
1865 unknown:
1866 return count;
1867 }
1868
1869 static int
do_check_existing_column(sqlite3 * sqlite,const char * prefix,const char * table,const char * column)1870 do_check_existing_column (sqlite3 * sqlite, const char *prefix,
1871 const char *table, const char *column)
1872 {
1873 /* checking for an existing Table Column */
1874 char *sql;
1875 char *q_prefix;
1876 int ret;
1877 int i;
1878 char **results;
1879 int rows;
1880 int columns;
1881 int exists = 0;
1882
1883 q_prefix = gaiaDoubleQuotedSql (prefix);
1884 sql = sqlite3_mprintf ("PRAGMA \"%s\".table_info(%Q)", q_prefix, table);
1885 free (q_prefix);
1886 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
1887 sqlite3_free (sql);
1888 if (ret != SQLITE_OK)
1889 goto unknown;
1890 if (rows < 1)
1891 ;
1892 else
1893 {
1894 for (i = 1; i <= rows; i++)
1895 {
1896 const char *col = results[(i * columns) + 1];
1897 if (strcasecmp (column, col) == 0)
1898 exists = 1;
1899 }
1900 }
1901 sqlite3_free_table (results);
1902 unknown:
1903 return exists;
1904 }
1905
1906 static char *
do_retrieve_coverage_name(sqlite3 * sqlite,const char * prefix,const char * table,int table_only)1907 do_retrieve_coverage_name (sqlite3 * sqlite, const char *prefix,
1908 const char *table, int table_only)
1909 {
1910 /* checking for a registered Vector Coverage */
1911 char *sql;
1912 char *q_prefix;
1913 char *coverage_name = NULL;
1914 int ret;
1915 int i;
1916 char **results;
1917 int rows;
1918 int columns;
1919
1920 q_prefix = gaiaDoubleQuotedSql (prefix);
1921 if (table_only)
1922 {
1923 sql =
1924 sqlite3_mprintf
1925 ("SELECT coverage_name FROM \"%s\".vector_coverages "
1926 "WHERE f_table_name = %Q", q_prefix, table);
1927 }
1928 else
1929 {
1930 sql =
1931 sqlite3_mprintf
1932 ("SELECT coverage_name FROM \"%s\".vector_coverages "
1933 "WHERE f_table_name = %Q OR view_name = %Q OR virt_name = %Q",
1934 q_prefix, table, table, table);
1935 }
1936 free (q_prefix);
1937 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
1938 sqlite3_free (sql);
1939 if (ret != SQLITE_OK)
1940 goto unknown;
1941 if (rows < 1)
1942 ;
1943 else
1944 {
1945 for (i = 1; i <= rows; i++)
1946 {
1947 const char *cvg = results[(i * columns) + 0];
1948 if (cvg != NULL)
1949 {
1950 int len = strlen (cvg);
1951 if (coverage_name != NULL)
1952 free (coverage_name);
1953 coverage_name = malloc (len + 1);
1954 strcpy (coverage_name, cvg);
1955 }
1956 }
1957 }
1958 sqlite3_free_table (results);
1959 unknown:
1960 return coverage_name;
1961 }
1962
1963 /*/ -- -- ---------------------------------- --
1964 // do_drop_raster_triggers_index
1965 // -- -- ---------------------------------- --
1966 // Drop any TRIGGERs, INDEXes belonging to a raster_coverage Admin TABLE
1967 // * geometry entry and its SpatialIndex
1968 // * DROP the raster_coverage Admin TABLE when compleated
1969 // -- -- ---------------------------------- --
1970 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
1971 // -- -- ---------------------------------- --
1972 */
1973 static int
do_drop_raster_triggers_index(sqlite3 * sqlite,const char * prefix,const char * tbl_name,int contains_geometry,struct table_params * aux)1974 do_drop_raster_triggers_index (sqlite3 * sqlite, const char *prefix,
1975 const char *tbl_name, int contains_geometry,
1976 struct table_params *aux)
1977 {
1978 if ((aux) && (aux->is_raster_coverage_entry == 1))
1979 {
1980 /* the table name is a verified raster_coverage entry */
1981 int ret;
1982 char *errMsg = NULL;
1983 if (prefix == NULL)
1984 prefix = "main";
1985 char *xprefix = gaiaDoubleQuotedSql (prefix);
1986 char **results = NULL;
1987 int rows = 0;
1988 int columns = 0;
1989 int i = 0;
1990 char *sql_statement =
1991 sqlite3_mprintf
1992 ("SELECT type,name FROM \"%s\".sqlite_master WHERE ((type IN ('trigger','index')) AND (lower(tbl_name) = lower(%Q)))",
1993 xprefix, tbl_name);
1994 ret =
1995 sqlite3_get_table (sqlite, sql_statement, &results, &rows,
1996 &columns, NULL);
1997 sqlite3_free (sql_statement);
1998 if ((ret == SQLITE_OK) && (rows > 0) && (results))
1999 {
2000 /* Use sqlite3_get_table so that 'sqlite_master' will not be locked during the 'DROP TRIGGER/INDEX' commands */
2001 for (i = 1; i <= rows; i++)
2002 {
2003 const char *type = results[(i * columns) + 0];
2004 const char *type_name = results[(i * columns) + 1];
2005 char *xtype_name = gaiaDoubleQuotedSql (type_name);
2006 if (strcmp (type, "trigger") == 0)
2007 {
2008 /* DROP all TRIGGERs belonging to this TABLE */
2009 sql_statement =
2010 sqlite3_mprintf ("DROP TRIGGER \"%s\".\"%s\"",
2011 xprefix, type_name);
2012 }
2013 else
2014 {
2015 /* DROP all INDEXes belonging to this TABLE */
2016 sql_statement =
2017 sqlite3_mprintf ("DROP INDEX \"%s\".\"%s\"",
2018 xprefix, type_name);
2019 }
2020 free (xtype_name);
2021 xtype_name = NULL;
2022 ret =
2023 sqlite3_exec (sqlite, sql_statement, NULL, NULL,
2024 &errMsg);
2025 sqlite3_free (sql_statement);
2026 sql_statement = NULL;
2027 if (ret != SQLITE_OK)
2028 {
2029 if (strcmp (type, "trigger") == 0)
2030 {
2031 aux->error_message =
2032 sqlite3_mprintf
2033 ("DROP of TRIGGER [%s] failed with rc=%d reason: %s",
2034 type_name, ret, errMsg);
2035 }
2036 else
2037 {
2038 aux->error_message =
2039 sqlite3_mprintf
2040 ("DROP of INDEX [%s] failed with rc=%d reason: %s",
2041 type_name, ret, errMsg);
2042 }
2043 sqlite3_free (errMsg);
2044 errMsg = NULL;
2045 free (xprefix);
2046 xprefix = NULL;
2047 sqlite3_free_table (results);
2048 results = NULL;
2049 return 0;
2050 }
2051 }
2052 /* free the used results in case of further errors */
2053 sqlite3_free_table (results);
2054 results = NULL;
2055 if (contains_geometry)
2056 {
2057 char jolly = '%';
2058 sql_statement =
2059 sqlite3_mprintf
2060 ("SELECT name FROM \"%s\".sqlite_master WHERE type = 'table' AND "
2061 "Lower(name) IN (SELECT "
2062 "Lower('idx_' || f_table_name || '_' || f_geometry_column) "
2063 "FROM \"%s\".geometry_columns WHERE Lower(f_table_name) = Lower(%Q)) "
2064 "AND sql LIKE('%cvirtual%c') AND sql LIKE('%crtree%c')",
2065 xprefix, xprefix, tbl_name, jolly, jolly, jolly,
2066 jolly);
2067 ret =
2068 sqlite3_get_table (sqlite, sql_statement, &results,
2069 &rows, &columns, NULL);
2070 sqlite3_free (sql_statement);
2071 sql_statement = NULL;
2072 if ((ret == SQLITE_OK) && (rows > 0) && (results))
2073 {
2074 /* Use sqlite3_get_table so that 'sqlite_master' will not be locked during the 'DROP TRIGGER/INDEX' commands */
2075 for (i = 1; i <= rows; i++)
2076 {
2077 const char *rtree_name =
2078 results[(i * columns) + 0];
2079 char *q_rtree =
2080 gaiaDoubleQuotedSql (rtree_name);
2081 sql_statement =
2082 sqlite3_mprintf
2083 ("DROP TABLE \"%s\".\"%s\"", xprefix,
2084 q_rtree);
2085 free (q_rtree);
2086 q_rtree = NULL;
2087 ret =
2088 sqlite3_exec (sqlite, sql_statement, NULL,
2089 NULL, &errMsg);
2090 sqlite3_free (sql_statement);
2091 sql_statement = NULL;
2092 if (ret != SQLITE_OK)
2093 {
2094 aux->error_message =
2095 sqlite3_mprintf
2096 ("DROP of SpatialIndex TABLE [%s] failed with rc=%d reason: %s",
2097 rtree_name, ret, errMsg);
2098 sqlite3_free_table (results);
2099 results = NULL;
2100 free (xprefix);
2101 xprefix = NULL;
2102 return 0;
2103 }
2104 }
2105 }
2106 sqlite3_free_table (results);
2107 results = NULL;
2108 /*/
2109 // ON DELETE CASCADE will remove the cousins:
2110 // - geometry_columns_statistics, geometry_columns_field_infos, geometry_columns_time and geometry_columns_auth
2111 */
2112 sql_statement =
2113 sqlite3_mprintf
2114 ("DELETE FROM \"%s\".geometry_columns WHERE lower(f_table_name) = lower(%Q)",
2115 xprefix, tbl_name);
2116 ret =
2117 sqlite3_exec (sqlite, sql_statement, NULL, NULL,
2118 &errMsg);
2119 sqlite3_free (sql_statement);
2120 sql_statement = NULL;
2121 if (ret != SQLITE_OK)
2122 {
2123 aux->error_message =
2124 sqlite3_mprintf
2125 ("DELETE of geometry_columns entry for [%s] failed with rc=%d reason: %s",
2126 tbl_name, ret, errMsg);
2127 sqlite3_free (errMsg);
2128 errMsg = NULL;
2129 free (xprefix);
2130 xprefix = NULL;
2131 return 0;
2132 }
2133 }
2134 }
2135 if (results)
2136 {
2137 /* makes valgrind happy when placed here */
2138 sqlite3_free_table (results);
2139 results = NULL;
2140 }
2141 /* DROP the TABLE after all of the TRIGGERs and INDEXes have been removed */
2142 sql_statement =
2143 sqlite3_mprintf ("DROP TABLE \"%s\".\"%s\"", xprefix, tbl_name);
2144 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2145 sqlite3_free (sql_statement);
2146 sql_statement = NULL;
2147 if (ret != SQLITE_OK)
2148 {
2149 aux->error_message =
2150 sqlite3_mprintf
2151 ("DROP of TABLE [%s] failed with rc=%d reason: %s",
2152 tbl_name, ret, errMsg);
2153 sqlite3_free (errMsg);
2154 errMsg = NULL;
2155 free (xprefix);
2156 xprefix = NULL;
2157 return 0;
2158 }
2159 free (xprefix);
2160 xprefix = NULL;
2161 if (errMsg)
2162 {
2163 sqlite3_free (errMsg);
2164 errMsg = NULL;
2165 }
2166 return 1;
2167 }
2168 return 0;
2169 }
2170
2171 /*/ -- -- ---------------------------------- --
2172 // do_drop_raster_coverage
2173 // * check_for_system_tables will allow only when within the 'main' Database
2174 // -- -- ---------------------------------- --
2175 // Admin Tables for each raster_coverage entry:
2176 // * 'raster_coverage'_levels
2177 // --> FK reference: none
2178 // --> TRIGGERS and index:
2179 // * 'raster_coverage'_sections
2180 // --> FK reference: none
2181 // --> TRIGGERs and index: tbl_name='raster_coverage'_sections
2182 // * 'raster_coverage'_tiles
2183 // --> FK reference to levels and sections
2184 // --> TRIGGERs and index: tbl_name='raster_coverage'_tiles
2185 // * 'raster_coverage'_tile_data
2186 // --> FK reference to tiles
2187 // --> TRIGGERs : tbl_name='raster_coverage'_tile_data
2188 // -- -- ---------------------------------- --
2189 // Delete any registred raster-styles
2190 // -- -- ---------------------------------- --
2191 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
2192 // -- -- ---------------------------------- --
2193 */
2194 static int
do_drop_raster_coverage(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux)2195 do_drop_raster_coverage (sqlite3 * sqlite, const char *prefix,
2196 const char *table, struct table_params *aux)
2197 {
2198 if ((aux) && (aux->is_raster_coverage_entry == 1))
2199 {
2200 /*
2201 // the table name is a verified raster_coverage entry
2202 // the proper order is important
2203 */
2204 char *tbl_name = sqlite3_mprintf ("%s_tile_data", table); /* no geometry column */
2205 if (!do_drop_raster_triggers_index (sqlite, prefix, tbl_name, 0, aux))
2206 {
2207 /* aux->error_message will contain any error message */
2208 sqlite3_free (tbl_name);
2209 tbl_name = NULL;
2210 return 0;
2211 }
2212 sqlite3_free (tbl_name);
2213 tbl_name = NULL;
2214 tbl_name = sqlite3_mprintf ("%s_tiles", table); /* with geometry column */
2215 if (!do_drop_raster_triggers_index (sqlite, prefix, tbl_name, 1, aux))
2216 {
2217 /* aux->error_message will contain any error message */
2218 sqlite3_free (tbl_name);
2219 tbl_name = NULL;
2220 return 0;
2221 }
2222 sqlite3_free (tbl_name);
2223 tbl_name = NULL;
2224 tbl_name = sqlite3_mprintf ("%s_sections", table); /* with geometry column */
2225 if (!do_drop_raster_triggers_index (sqlite, prefix, tbl_name, 1, aux))
2226 {
2227 /* aux->error_message will coniain any error message */
2228 sqlite3_free (tbl_name);
2229 tbl_name = NULL;
2230 return 0;
2231 }
2232 sqlite3_free (tbl_name);
2233 tbl_name = NULL;
2234 tbl_name = sqlite3_mprintf ("%s_levels", table); /* no geometry column */
2235 if (!do_drop_raster_triggers_index (sqlite, prefix, tbl_name, 0, aux))
2236 {
2237 /* aux->error_message will contain any error message */
2238 sqlite3_free (tbl_name);
2239 tbl_name = NULL;
2240 return 0;
2241 }
2242 sqlite3_free (tbl_name);
2243 tbl_name = NULL;
2244 if (prefix == NULL)
2245 prefix = "main";
2246 char *errMsg = NULL;
2247 char *xprefix = gaiaDoubleQuotedSql (prefix);
2248 /*
2249 // ON DELETE CASCADE will remove the cousins:
2250 // - raster_coverages_keyword and raster_coverages_srid
2251 */
2252 char *sql_statement =
2253 sqlite3_mprintf
2254 ("DELETE FROM \"%s\".raster_coverages WHERE lower(coverage_name) = lower(%Q)",
2255 xprefix, table);
2256 int ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2257 sqlite3_free (sql_statement);
2258 sql_statement = NULL;
2259 if (ret != SQLITE_OK)
2260 {
2261 free (xprefix);
2262 xprefix = NULL;
2263 aux->error_message =
2264 sqlite3_mprintf
2265 ("DELETE of raster_coverages entry for [%s] failed with rc=%d reason: %s",
2266 table, ret, errMsg);
2267 sqlite3_free (errMsg);
2268 errMsg = NULL;
2269 return 0;
2270 }
2271 if (aux->ok_se_raster_styled_layers)
2272 {
2273 /* - delete any registred raster-styles entries [no error if none exist] */
2274 sql_statement =
2275 sqlite3_mprintf
2276 ("DELETE FROM \"%s\".SE_raster_styled_layers WHERE lower(coverage_name) = lower(%Q)",
2277 xprefix, table);
2278 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2279 sqlite3_free (sql_statement);
2280 sql_statement = NULL;
2281 if (ret != SQLITE_OK)
2282 {
2283 free (xprefix);
2284 xprefix = NULL;
2285 aux->error_message =
2286 sqlite3_mprintf
2287 ("DELETE of SE_raster_styled_layers entry for [%s] failed with rc=%d reason: %s",
2288 table, ret, errMsg);
2289 sqlite3_free (errMsg);
2290 errMsg = NULL;
2291 return 0;
2292 }
2293 }
2294 if (xprefix)
2295 {
2296 free (xprefix);
2297 xprefix = NULL;
2298 }
2299 return 1;
2300 }
2301 return 0;
2302 }
2303
2304 /*/ -- -- ---------------------------------- --
2305 // do_rename_raster_triggers_index
2306 // -- -- ---------------------------------- --
2307 // Drop any TRIGGERs, INDEXes belonging to a raster_coverage Admin TABLE
2308 // * geometry entry and its SpatialIndex
2309 // * DROP the raster_coverage Admin TABLE when compleated
2310 // -- -- ---------------------------------- --
2311 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
2312 // -- -- ---------------------------------- --
2313 */
2314 static int
do_rename_raster_triggers_index(sqlite3 * sqlite,const char * prefix,const char * tbl_name,const char * tbl_new,const char * name_old,const char * name_new,int contains_geometry,struct table_params * aux)2315 do_rename_raster_triggers_index (sqlite3 * sqlite, const char *prefix,
2316 const char *tbl_name, const char *tbl_new,
2317 const char *name_old, const char *name_new,
2318 int contains_geometry,
2319 struct table_params *aux)
2320 {
2321 if ((aux) && (aux->is_raster_coverage_entry == 1))
2322 {
2323 /* the table name is a verified raster_coverage entry */
2324 int ret;
2325 char *errMsg = NULL;
2326 if (prefix == NULL)
2327 prefix = "main";
2328 char *xprefix = gaiaDoubleQuotedSql (prefix);
2329 char **results = NULL;
2330 int rows = 0;
2331 int columns = 0;
2332 int i = 0;
2333 char *q_old;
2334 char *q_new;
2335 q_old = gaiaDoubleQuotedSql (tbl_name);
2336 q_new = gaiaDoubleQuotedSql (tbl_new);
2337 /* ALTER TABLE "main"."middle_earth_1418sr_sections" RENAME TO "replace('middle_earth_1418sr_sections','middle_earth_1418sr','center_earth_1418sr')"" */
2338 char *sql_statement =
2339 sqlite3_mprintf ("ALTER TABLE \"%s\".\"%s\" RENAME TO \"%s\"",
2340 xprefix, q_old, q_new);
2341 free (q_old);
2342 q_old = NULL;
2343 free (q_new);
2344 q_new = NULL;
2345 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2346 sqlite3_free (sql_statement);
2347 sql_statement = NULL;
2348 if (ret != SQLITE_OK)
2349 {
2350 aux->error_message =
2351 sqlite3_mprintf
2352 ("RENAME TABLE from [%s] to [%s] failed with rc=%d reason: %s",
2353 tbl_name, tbl_new, ret, errMsg);
2354 sqlite3_free (errMsg);
2355 errMsg = NULL;
2356 free (xprefix);
2357 xprefix = NULL;
2358 return 0;
2359 }
2360 /* Note: having been renamed, the 'tbl_name' in sqlite_master now contains the new name */
2361 if (contains_geometry)
2362 {
2363 /* Must be compleated first to avoid TRIGGER errors [geometry_columns still contains the old name] */
2364 char jolly = '%';
2365 sql_statement =
2366 sqlite3_mprintf
2367 ("SELECT name, replace(name,%Q,%Q) AS name_new FROM \"%s\".sqlite_master WHERE type = 'table' AND "
2368 "Lower(name) IN (SELECT "
2369 "Lower('idx_' || f_table_name || '_' || f_geometry_column) "
2370 "FROM \"%s\".geometry_columns WHERE Lower(f_table_name) = Lower(%Q)) "
2371 "AND sql LIKE('%cvirtual%c') AND sql LIKE('%crtree%c')",
2372 name_old, name_new, xprefix, xprefix, tbl_name, jolly,
2373 jolly, jolly, jolly);
2374 ret =
2375 sqlite3_get_table (sqlite, sql_statement, &results, &rows,
2376 &columns, NULL);
2377 sqlite3_free (sql_statement);
2378 sql_statement = NULL;
2379 if ((ret == SQLITE_OK) && (rows > 0) && (results))
2380 {
2381 /* Use sqlite3_get_table so that 'sqlite_master' will not be locked during the 'DROP TRIGGER/INDEX' commands */
2382 for (i = 1; i <= rows; i++)
2383 {
2384 const char *rtree_name = results[(i * columns) + 0];
2385 const char *rtree_name_new =
2386 results[(i * columns) + 1];
2387 char *q_rtree = gaiaDoubleQuotedSql (rtree_name);
2388 char *q_rtree_new =
2389 gaiaDoubleQuotedSql (rtree_name_new);
2390 sql_statement =
2391 sqlite3_mprintf
2392 ("ALTER TABLE \"%s\".\"%s\" RENAME TO \"%s\"",
2393 xprefix, q_rtree, q_rtree_new);
2394 free (q_rtree);
2395 q_rtree = NULL;
2396 free (q_rtree_new);
2397 q_rtree_new = NULL;
2398 ret =
2399 sqlite3_exec (sqlite, sql_statement, NULL, NULL,
2400 &errMsg);
2401 sqlite3_free (sql_statement);
2402 sql_statement = NULL;
2403 if (ret != SQLITE_OK)
2404 {
2405 aux->error_message =
2406 sqlite3_mprintf
2407 ("ALTER of SpatialIndex TABLE from [%s] to [%s] failed with rc=%d reason: %s",
2408 rtree_name, rtree_name_new, ret, errMsg);
2409 sqlite3_free_table (results);
2410 results = NULL;
2411 sqlite3_free (errMsg);
2412 errMsg = NULL;
2413 free (xprefix);
2414 xprefix = NULL;
2415 return 0;
2416 }
2417 }
2418 }
2419 sqlite3_free_table (results);
2420 results = NULL;
2421 /*
2422 // Rename the entries in the cousins:
2423 // - geometry_columns_statistics, geometry_columns_field_infos, geometry_columns_time and geometry_columns_auth
2424 */
2425 sql_statement =
2426 sqlite3_mprintf
2427 ("UPDATE \"%s\".geometry_columns SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q);"
2428 "UPDATE \"%s\".geometry_columns_auth SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q);"
2429 "UPDATE \"%s\".geometry_columns_time SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q);"
2430 "UPDATE \"%s\".geometry_columns_field_infos SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q);"
2431 "UPDATE \"%s\".geometry_columns_statistics SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q);",
2432 xprefix, tbl_new, tbl_name, xprefix, tbl_new, tbl_name,
2433 xprefix, tbl_new, tbl_name, xprefix, tbl_new, tbl_name,
2434 xprefix, tbl_new, tbl_name);
2435 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2436 sqlite3_free (sql_statement);
2437 sql_statement = NULL;
2438 if (ret != SQLITE_OK)
2439 {
2440 aux->error_message =
2441 sqlite3_mprintf
2442 ("UPDATE of geometry_columns entry from [%s] to [%s] failed with rc=%d reason: %s",
2443 tbl_name, tbl_new, ret, errMsg);
2444 sqlite3_free (errMsg);
2445 errMsg = NULL;
2446 free (xprefix);
2447 xprefix = NULL;
2448 return 0;
2449 }
2450 }
2451 sql_statement =
2452 sqlite3_mprintf
2453 ("SELECT type,name,replace(name,%Q,%Q) AS name_new, replace(sql,%Q,%Q) AS sql_new FROM \"%s\".sqlite_master WHERE ((type IN ('trigger','index')) AND (lower(tbl_name) = lower(%Q)))",
2454 name_old, name_new, name_old, name_new, xprefix, tbl_new);
2455 ret =
2456 sqlite3_get_table (sqlite, sql_statement, &results, &rows,
2457 &columns, NULL);
2458 sqlite3_free (sql_statement);
2459 if ((ret == SQLITE_OK) && (rows > 0) && (results))
2460 {
2461 /* Use sqlite3_get_table so that 'sqlite_master' will not be locked during the 'DROP TRIGGER/INDEX' commands */
2462 for (i = 1; i <= rows; i++)
2463 {
2464 const char *type = results[(i * columns) + 0];
2465 const char *type_name = results[(i * columns) + 1];
2466 const char *type_name_new = results[(i * columns) + 2];
2467 const char *type_sql = results[(i * columns) + 3];
2468 char *xtype_name = gaiaDoubleQuotedSql (type_name);
2469 if (strcmp (type, "trigger") == 0)
2470 {
2471 /* DROP all TRIGGERs belonging to this TABLE */
2472 sql_statement =
2473 sqlite3_mprintf ("DROP TRIGGER \"%s\".\"%s\"",
2474 xprefix, xtype_name);
2475 }
2476 else
2477 {
2478 /*
2479 // DROP all INDEXes belonging to this TABLE
2480 // DROP INDEX idx_middle_earth_1418sr_sect_name ; CREATE INDEX "idx_center_earth_1418sr_sect_name" ON "center_earth_1418sr_sections" (section_name)
2481 */
2482 sql_statement =
2483 sqlite3_mprintf ("DROP INDEX \"%s\".\"%s\"",
2484 xprefix, xtype_name);
2485 }
2486 free (xtype_name);
2487 xtype_name = NULL;
2488 ret =
2489 sqlite3_exec (sqlite, sql_statement, NULL, NULL,
2490 &errMsg);
2491 sqlite3_free (sql_statement);
2492 sql_statement = NULL;
2493 if (ret != SQLITE_OK)
2494 {
2495 if (strcmp (type, "trigger") == 0)
2496 {
2497 aux->error_message =
2498 sqlite3_mprintf
2499 ("DROP of TRIGGER [%s] failed with rc=%d reason: %s",
2500 type_name, ret, errMsg);
2501 }
2502 else
2503 {
2504 aux->error_message =
2505 sqlite3_mprintf
2506 ("DROP of INDEX [%s] failed with rc=%d reason: %s",
2507 type_name, ret, errMsg);
2508 }
2509 sqlite3_free (errMsg);
2510 errMsg = NULL;
2511 free (xprefix);
2512 xprefix = NULL;
2513 sqlite3_free_table (results);
2514 results = NULL;
2515 return 0;
2516 }
2517 sql_statement = sqlite3_mprintf ("%s", type_sql);
2518 ret =
2519 sqlite3_exec (sqlite, sql_statement, NULL, NULL,
2520 &errMsg);
2521 sqlite3_free (sql_statement);
2522 sql_statement = NULL;
2523 if (ret != SQLITE_OK)
2524 {
2525 if (strcmp (type, "trigger") == 0)
2526 {
2527 aux->error_message =
2528 sqlite3_mprintf
2529 ("CREATE of TRIGGER [%s] failed with rc=%d reason: %s",
2530 type_name_new, ret, errMsg);
2531 }
2532 else
2533 {
2534 aux->error_message =
2535 sqlite3_mprintf
2536 ("CREATE of INDEX [%s] failed with rc=%d reason: %s",
2537 type_name_new, ret, errMsg);
2538 }
2539 sqlite3_free (errMsg);
2540 errMsg = NULL;
2541 free (xprefix);
2542 xprefix = NULL;
2543 sqlite3_free_table (results);
2544 results = NULL;
2545 return 0;
2546 }
2547 }
2548 }
2549 if (results)
2550 { /* makes valgrind happy when placed here */
2551 sqlite3_free_table (results);
2552 results = NULL;
2553 }
2554 if (xprefix)
2555 {
2556 free (xprefix);
2557 xprefix = NULL;
2558 }
2559 }
2560 return 1;
2561 }
2562
2563 /*/ -- -- ---------------------------------- --
2564 // do_rename_raster_coverage
2565 // -- -- ---------------------------------- --
2566 // Admin Tables for each raster_coverage entry:
2567 // * 'raster_coverage'_levels
2568 // --> FK reference: none
2569 // --> TRIGGERS and index:
2570 // * 'raster_coverage'_sections
2571 // --> FK reference: none
2572 // --> TRIGGERs and index: tbl_name='raster_coverage'_sections
2573 // * 'raster_coverage'_tiles
2574 // --> FK reference to levels and sections
2575 // --> TRIGGERs and index: tbl_name='raster_coverage'_tiles
2576 // * 'raster_coverage'_tile_data
2577 // --> FK reference to tiles
2578 // --> TRIGGERs : tbl_name='raster_coverage'_tile_data
2579 // -- -- ---------------------------------- --
2580 // Update any registred raster-styles
2581 // -- -- ---------------------------------- --
2582 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
2583 // -- -- ---------------------------------- --
2584 */
2585 static int
do_rename_raster_coverage(sqlite3 * sqlite,const char * prefix,const char * name_old,const char * name_new,struct table_params * aux)2586 do_rename_raster_coverage (sqlite3 * sqlite, const char *prefix,
2587 const char *name_old, const char *name_new,
2588 struct table_params *aux)
2589 {
2590 if ((aux) && (aux->is_raster_coverage_entry == 1))
2591 {
2592 /*
2593 // the old_name is a verified raster_coverage entry, the new_name does not exist in raster_coverage
2594 // the proper order is important
2595 */
2596 char *tbl_name = sqlite3_mprintf ("%s_tiles", name_old); /* with geometry column */
2597 char *tbl_new = sqlite3_mprintf ("%s_tiles", name_new); /* with geometry column */
2598 if (!do_rename_raster_triggers_index
2599 (sqlite, prefix, tbl_name, tbl_new, name_old, name_new, 1, aux))
2600 {
2601 /* aux->error_message will contain any error message */
2602 sqlite3_free (tbl_name);
2603 tbl_name = NULL;
2604 sqlite3_free (tbl_new);
2605 tbl_new = NULL;
2606 return 0;
2607 }
2608 sqlite3_free (tbl_name);
2609 tbl_name = NULL;
2610 sqlite3_free (tbl_new);
2611 tbl_new = NULL;
2612 tbl_name = sqlite3_mprintf ("%s_tile_data", name_old); /* no geometry column */
2613 tbl_new = sqlite3_mprintf ("%s_tile_data", name_new); /* no geometry column */
2614 if (!do_rename_raster_triggers_index
2615 (sqlite, prefix, tbl_name, tbl_new, name_old, name_new, 0, aux))
2616 { /* aux->error_message will contain any error message */
2617 sqlite3_free (tbl_name);
2618 tbl_name = NULL;
2619 sqlite3_free (tbl_new);
2620 tbl_new = NULL;
2621 return 0;
2622 }
2623 sqlite3_free (tbl_name);
2624 tbl_name = NULL;
2625 sqlite3_free (tbl_new);
2626 tbl_new = NULL;
2627 tbl_name = sqlite3_mprintf ("%s_sections", name_old); /* with geometry column */
2628 tbl_new = sqlite3_mprintf ("%s_sections", name_new); /* with geometry column */
2629 if (!do_rename_raster_triggers_index
2630 (sqlite, prefix, tbl_name, tbl_new, name_old, name_new, 1, aux))
2631 {
2632 /* aux->error_message will contain any error message */
2633 sqlite3_free (tbl_name);
2634 tbl_name = NULL;
2635 sqlite3_free (tbl_new);
2636 tbl_new = NULL;
2637 return 0;
2638 }
2639 sqlite3_free (tbl_name);
2640 tbl_name = NULL;
2641 sqlite3_free (tbl_new);
2642 tbl_new = NULL;
2643 tbl_name = sqlite3_mprintf ("%s_levels", name_old); /* no geometry column */
2644 tbl_new = sqlite3_mprintf ("%s_levels", name_new); /* no geometry column */
2645 if (!do_rename_raster_triggers_index
2646 (sqlite, prefix, tbl_name, tbl_new, name_old, name_new, 0, aux))
2647 {
2648 /* aux->error_message will contain any error message */
2649 sqlite3_free (tbl_name);
2650 tbl_name = NULL;
2651 sqlite3_free (tbl_new);
2652 tbl_new = NULL;
2653 return 0;
2654 }
2655 sqlite3_free (tbl_name);
2656 tbl_name = NULL;
2657 sqlite3_free (tbl_new);
2658 tbl_new = NULL;
2659 if (prefix == NULL)
2660 prefix = "main";
2661 char *errMsg = NULL;
2662 char *xprefix = gaiaDoubleQuotedSql (prefix);
2663 /*
2664 // ON DELETE CASCADE will remove the cousins:
2665 // - raster_coverages_keyword and raster_coverages_srid
2666 */
2667 char *sql_statement =
2668 sqlite3_mprintf
2669 ("UPDATE \"%s\".raster_coverages SET coverage_name = lower(%Q) WHERE lower(coverage_name) = lower(%Q)",
2670 xprefix, name_new, name_old);
2671 int ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2672 sqlite3_free (sql_statement);
2673 sql_statement = NULL;
2674 if (ret != SQLITE_OK)
2675 {
2676 free (xprefix);
2677 xprefix = NULL;
2678 aux->error_message =
2679 sqlite3_mprintf
2680 ("UPDATE of raster_coverages entry from [%s] to [%s] failed with rc=%d reason: %s",
2681 name_old, name_new, ret, errMsg);
2682 sqlite3_free (errMsg);
2683 errMsg = NULL;
2684 return 0;
2685 }
2686 if (aux->ok_se_raster_styled_layers)
2687 {
2688 /* - update any registred raster-styles [no error if none exist] */
2689 sql_statement =
2690 sqlite3_mprintf
2691 ("UPDATE \"%s\".SE_raster_styled_layers SET coverage_name = lower(%Q) WHERE lower(coverage_name) = lower(%Q)",
2692 xprefix, name_new, name_old);
2693 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &errMsg);
2694 sqlite3_free (sql_statement);
2695 sql_statement = NULL;
2696 if (ret != SQLITE_OK)
2697 {
2698 free (xprefix);
2699 xprefix = NULL;
2700 aux->error_message =
2701 sqlite3_mprintf
2702 ("UPDATE of SE_raster_styled_layers from [%s] to [%s] failed with rc=%d reason: %s",
2703 name_old, name_new, ret, errMsg);
2704 sqlite3_free (errMsg);
2705 errMsg = NULL;
2706 return 0;
2707 }
2708 }
2709 if (xprefix)
2710 {
2711 free (xprefix);
2712 xprefix = NULL;
2713 }
2714 return 1;
2715 }
2716 return 0;
2717 }
2718
2719 static int
do_drop_table(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux)2720 do_drop_table (sqlite3 * sqlite, const char *prefix, const char *table,
2721 struct table_params *aux)
2722 {
2723 /* performing the actual work */
2724 char *sql;
2725 char *q_prefix;
2726 char *q_name;
2727 int i;
2728 int ret;
2729 char *errMsg = NULL;
2730
2731 if (!do_check_existing (sqlite, prefix, table, 0))
2732 return 0;
2733
2734 if (aux->ok_vector_coverages)
2735 {
2736 /* deleting from VECTOR_COVERAGES */
2737 char *coverage = do_retrieve_coverage_name (sqlite, prefix, table, 0);
2738 if (coverage != NULL)
2739 {
2740 q_prefix = gaiaDoubleQuotedSql (prefix);
2741 if (aux->ok_vector_coverages_srid)
2742 {
2743 /* deleting from VECTOR_COVERAGES_SRID */
2744 sql =
2745 sqlite3_mprintf
2746 ("DELETE FROM \"%s\".vector_coverages_srid "
2747 "WHERE lower(coverage_name) = lower(%Q)", q_prefix,
2748 coverage);
2749 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2750 sqlite3_free (sql);
2751 if (ret != SQLITE_OK)
2752 {
2753 aux->error_message = errMsg;
2754 return 0;
2755 }
2756 }
2757 if (aux->ok_vector_coverages_keyword)
2758 {
2759 /* deleting from VECTOR_COVERAGES_KEYWORD */
2760 sql =
2761 sqlite3_mprintf
2762 ("DELETE FROM \"%s\".vector_coverages_keyword "
2763 "WHERE lower(coverage_name) = lower(%Q)", q_prefix,
2764 coverage);
2765 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2766 sqlite3_free (sql);
2767 if (ret != SQLITE_OK)
2768 {
2769 aux->error_message = errMsg;
2770 return 0;
2771 }
2772 }
2773 if (aux->ok_se_vector_styled_layers)
2774 {
2775 /* deleting from SE_VECTOR_STYLED_LAYERS */
2776 sql =
2777 sqlite3_mprintf
2778 ("DELETE FROM \"%s\".SE_vector_styled_layers "
2779 "WHERE lower(coverage_name) = lower(%Q)", q_prefix,
2780 coverage);
2781 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2782 sqlite3_free (sql);
2783 if (ret != SQLITE_OK)
2784 {
2785 aux->error_message = errMsg;
2786 return 0;
2787 }
2788 }
2789 /* and finally deleting from VECTOR COVERAGES */
2790 sql = sqlite3_mprintf ("DELETE FROM \"%s\".vector_coverages "
2791 "WHERE lower(coverage_name) = lower(%Q)",
2792 q_prefix, coverage);
2793 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2794 sqlite3_free (sql);
2795 if (ret != SQLITE_OK)
2796 {
2797 aux->error_message = errMsg;
2798 return 0;
2799 }
2800 free (q_prefix);
2801 free (coverage);
2802 }
2803 }
2804
2805 if (aux->is_view)
2806 {
2807 /* dropping a View */
2808 q_name = gaiaDoubleQuotedSql (table);
2809 q_prefix = gaiaDoubleQuotedSql (prefix);
2810 sql =
2811 sqlite3_mprintf ("DROP VIEW IF EXISTS \"%s\".\"%s\"", q_prefix,
2812 q_name);
2813 free (q_prefix);
2814 free (q_name);
2815 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2816 sqlite3_free (sql);
2817 if (ret != SQLITE_OK)
2818 {
2819 aux->error_message = errMsg;
2820 return 0;
2821 }
2822 }
2823 else
2824 {
2825 /* dropping a Table */
2826 q_name = gaiaDoubleQuotedSql (table);
2827 q_prefix = gaiaDoubleQuotedSql (prefix);
2828 sql =
2829 sqlite3_mprintf ("DROP TABLE IF EXISTS \"%s\".\"%s\"", q_prefix,
2830 q_name);
2831 free (q_prefix);
2832 free (q_name);
2833 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2834 sqlite3_free (sql);
2835 if (ret != SQLITE_OK)
2836 {
2837 aux->error_message = errMsg;
2838 return 0;
2839 }
2840 }
2841
2842 for (i = 0; i < aux->n_rtrees; i++)
2843 {
2844 /* dropping any R*Tree */
2845 q_name = gaiaDoubleQuotedSql (*(aux->rtrees + i));
2846 q_prefix = gaiaDoubleQuotedSql (prefix);
2847 sql =
2848 sqlite3_mprintf ("DROP TABLE IF EXISTS \"%s\".\"%s\"", q_prefix,
2849 q_name);
2850 free (q_prefix);
2851 free (q_name);
2852 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2853 sqlite3_free (sql);
2854 if (ret != SQLITE_OK)
2855 {
2856 aux->error_message = errMsg;
2857 return 0;
2858 }
2859 }
2860
2861 if (aux->ok_layer_params)
2862 {
2863 /* deleting from LAYER_PARAMS */
2864 q_prefix = gaiaDoubleQuotedSql (prefix);
2865 sql = sqlite3_mprintf ("DELETE FROM \"%s\".layer_params "
2866 "WHERE lower(table_name) = lower(%Q)",
2867 q_prefix, table);
2868 free (q_prefix);
2869 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2870 sqlite3_free (sql);
2871 if (ret != SQLITE_OK)
2872 {
2873 aux->error_message = errMsg;
2874 return 0;
2875 }
2876 }
2877 if (aux->ok_layer_sub_classes)
2878 {
2879 /* deleting from LAYER_SUB_CLASSES */
2880 q_prefix = gaiaDoubleQuotedSql (prefix);
2881 sql = sqlite3_mprintf ("DELETE FROM \"%s\".layer_sub_classes "
2882 "WHERE lower(table_name) = lower(%Q)",
2883 q_prefix, table);
2884 free (q_prefix);
2885 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2886 sqlite3_free (sql);
2887 if (ret != SQLITE_OK)
2888 {
2889 aux->error_message = errMsg;
2890 return 0;
2891 }
2892 }
2893 if (aux->ok_layer_table_layout)
2894 {
2895 /* deleting from LAYER_TABLE_LAYOUT */
2896 q_prefix = gaiaDoubleQuotedSql (prefix);
2897 sql = sqlite3_mprintf ("DELETE FROM \"%s\".layer_table_layout "
2898 "WHERE lower(table_name) = lower(%Q)",
2899 q_prefix, table);
2900 free (q_prefix);
2901 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2902 sqlite3_free (sql);
2903 if (ret != SQLITE_OK)
2904 {
2905 aux->error_message = errMsg;
2906 return 0;
2907 }
2908 }
2909 if (aux->ok_geometry_columns_auth)
2910 {
2911 /* deleting from GEOMETRY_COLUMNS_AUTH */
2912 q_prefix = gaiaDoubleQuotedSql (prefix);
2913 sql = sqlite3_mprintf ("DELETE FROM \"%s\".geometry_columns_auth "
2914 "WHERE lower(f_table_name) = lower(%Q)",
2915 q_prefix, table);
2916 free (q_prefix);
2917 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2918 sqlite3_free (sql);
2919 if (ret != SQLITE_OK)
2920 {
2921 aux->error_message = errMsg;
2922 return 0;
2923 }
2924 }
2925 if (aux->ok_geometry_columns_time)
2926 {
2927 /* deleting from GEOMETRY_COLUMNS_TIME */
2928 q_prefix = gaiaDoubleQuotedSql (prefix);
2929 sql = sqlite3_mprintf ("DELETE FROM \"%s\".geometry_columns_time "
2930 "WHERE lower(f_table_name) = lower(%Q)",
2931 q_prefix, table);
2932 free (q_prefix);
2933 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2934 sqlite3_free (sql);
2935 if (ret != SQLITE_OK)
2936 {
2937 aux->error_message = errMsg;
2938 return 0;
2939 }
2940 }
2941 if (aux->ok_geometry_columns_field_infos)
2942 {
2943 /* deleting from GEOMETRY_COLUMNS_FIELD_INFOS */
2944 q_prefix = gaiaDoubleQuotedSql (prefix);
2945 sql =
2946 sqlite3_mprintf
2947 ("DELETE FROM \"%s\".geometry_columns_field_infos "
2948 "WHERE lower(f_table_name) = lower(%Q)", q_prefix, table);
2949 free (q_prefix);
2950 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2951 sqlite3_free (sql);
2952 if (ret != SQLITE_OK)
2953 {
2954 aux->error_message = errMsg;
2955 return 0;
2956 }
2957 }
2958 if (aux->ok_geometry_columns_statistics)
2959 {
2960 /* deleting from GEOMETRY_COLUMNS_STATISTICS */
2961 q_prefix = gaiaDoubleQuotedSql (prefix);
2962 sql =
2963 sqlite3_mprintf ("DELETE FROM \"%s\".geometry_columns_statistics "
2964 "WHERE lower(f_table_name) = lower(%Q)",
2965 q_prefix, table);
2966 free (q_prefix);
2967 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2968 sqlite3_free (sql);
2969 if (ret != SQLITE_OK)
2970 {
2971 aux->error_message = errMsg;
2972 return 0;
2973 }
2974 }
2975 if (aux->ok_views_geometry_columns_auth)
2976 {
2977 /* deleting from VIEWS_GEOMETRY_COLUMNS_AUTH */
2978 q_prefix = gaiaDoubleQuotedSql (prefix);
2979 sql =
2980 sqlite3_mprintf ("DELETE FROM \"%s\".views_geometry_columns_auth "
2981 "WHERE lower(view_name) = lower(%Q)", q_prefix,
2982 table);
2983 free (q_prefix);
2984 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
2985 sqlite3_free (sql);
2986 if (ret != SQLITE_OK)
2987 {
2988 aux->error_message = errMsg;
2989 return 0;
2990 }
2991 }
2992 if (aux->ok_views_geometry_columns_field_infos)
2993 {
2994 /* deleting from VIEWS_GEOMETRY_COLUMNS_FIELD_INFOS */
2995 q_prefix = gaiaDoubleQuotedSql (prefix);
2996 sql =
2997 sqlite3_mprintf
2998 ("DELETE FROM \"%s\".views_geometry_columns_field_infos "
2999 "WHERE view_name = %Q", q_prefix, table);
3000 free (q_prefix);
3001 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3002 sqlite3_free (sql);
3003 if (ret != SQLITE_OK)
3004 {
3005 aux->error_message = errMsg;
3006 return 0;
3007 }
3008 }
3009 if (aux->ok_views_geometry_columns_statistics)
3010 {
3011 /* deleting from VIEWS_GEOMETRY_COLUMNS_STATISTICS */
3012 q_prefix = gaiaDoubleQuotedSql (prefix);
3013 sql =
3014 sqlite3_mprintf
3015 ("DELETE FROM \"%s\".views_geometry_columns_statistics "
3016 "WHERE lower(view_name) = lower(%Q)", q_prefix, table);
3017 free (q_prefix);
3018 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3019 sqlite3_free (sql);
3020 if (ret != SQLITE_OK)
3021 {
3022 aux->error_message = errMsg;
3023 return 0;
3024 }
3025 }
3026 if (aux->ok_virts_geometry_columns_auth)
3027 {
3028 /* deleting from VIRTS_GEOMETRY_COLUMNS_AUTH */
3029 q_prefix = gaiaDoubleQuotedSql (prefix);
3030 sql =
3031 sqlite3_mprintf ("DELETE FROM \"%s\".virts_geometry_columns_auth "
3032 "WHERE lower(virt_name) = lower(%Q)", q_prefix,
3033 table);
3034 free (q_prefix);
3035 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3036 sqlite3_free (sql);
3037 if (ret != SQLITE_OK)
3038 {
3039 aux->error_message = errMsg;
3040 return 0;
3041 }
3042 }
3043 if (aux->ok_virts_geometry_columns_field_infos)
3044 {
3045 /* deleting from VIRTS_GEOMETRY_COLUMNS_FIELD_INFOS */
3046 q_prefix = gaiaDoubleQuotedSql (prefix);
3047 sql =
3048 sqlite3_mprintf
3049 ("DELETE FROM \"%s\".virts_geometry_columns_field_infos "
3050 "WHERE lower(virt_name) = lower(%Q)", q_prefix, table);
3051 free (q_prefix);
3052 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3053 sqlite3_free (sql);
3054 if (ret != SQLITE_OK)
3055 {
3056 aux->error_message = errMsg;
3057 return 0;
3058 }
3059 }
3060 if (aux->ok_virts_geometry_columns_statistics)
3061 {
3062 /* deleting from VIRTS_GEOMETRY_COLUMNS_STATISTICS */
3063 q_prefix = gaiaDoubleQuotedSql (prefix);
3064 sql =
3065 sqlite3_mprintf
3066 ("DELETE FROM \"%s\".virts_geometry_columns_statistics "
3067 "WHERE lower(virt_name) = lower(%Q)", q_prefix, table);
3068 free (q_prefix);
3069 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3070 sqlite3_free (sql);
3071 if (ret != SQLITE_OK)
3072 {
3073 aux->error_message = errMsg;
3074 return 0;
3075 }
3076 }
3077 if (aux->ok_layer_statistics)
3078 {
3079 /* deleting from LAYER_STATISTICS */
3080 q_prefix = gaiaDoubleQuotedSql (prefix);
3081 sql = sqlite3_mprintf ("DELETE FROM \"%s\".layer_statistics "
3082 "WHERE lower(table_name) = lower(%Q)",
3083 q_prefix, table);
3084 free (q_prefix);
3085 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3086 sqlite3_free (sql);
3087 if (ret != SQLITE_OK)
3088 {
3089 aux->error_message = errMsg;
3090 return 0;
3091 }
3092 }
3093 if (aux->ok_views_layer_statistics)
3094 {
3095 /* deleting from VIEWS_LAYER_STATISTICS */
3096 q_prefix = gaiaDoubleQuotedSql (prefix);
3097 sql = sqlite3_mprintf ("DELETE FROM \"%s\".views_layer_statistics "
3098 "WHERE lower(view_name) = lower(%Q)", q_prefix,
3099 table);
3100 free (q_prefix);
3101 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3102 sqlite3_free (sql);
3103 if (ret != SQLITE_OK)
3104 {
3105 aux->error_message = errMsg;
3106 return 0;
3107 }
3108 }
3109 if (aux->ok_virts_layer_statistics)
3110 {
3111 /* deleting from VIRTS_LAYER_STATISTICS */
3112 q_prefix = gaiaDoubleQuotedSql (prefix);
3113 sql = sqlite3_mprintf ("DELETE FROM \"%s\".virts_layer_statistics "
3114 "WHERE lower(virt_name) = lower(%Q)", q_prefix,
3115 table);
3116 free (q_prefix);
3117 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3118 sqlite3_free (sql);
3119 if (ret != SQLITE_OK)
3120 {
3121 aux->error_message = errMsg;
3122 return 0;
3123 }
3124 }
3125 if (aux->ok_geometry_columns)
3126 {
3127 /* deleting from GEOMETRY_COLUMNS */
3128 q_prefix = gaiaDoubleQuotedSql (prefix);
3129 sql = sqlite3_mprintf ("DELETE FROM \"%s\".geometry_columns "
3130 "WHERE lower(f_table_name) = lower(%Q)",
3131 q_prefix, table);
3132 free (q_prefix);
3133 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3134 sqlite3_free (sql);
3135 if (ret != SQLITE_OK)
3136 {
3137 aux->error_message = errMsg;
3138 return 0;
3139 }
3140 }
3141 if (aux->ok_views_geometry_columns)
3142 {
3143 /* deleting from VIEWS_GEOMETRY_COLUMNS */
3144 q_prefix = gaiaDoubleQuotedSql (prefix);
3145 sql = sqlite3_mprintf ("DELETE FROM \"%s\".views_geometry_columns "
3146 "WHERE lower(view_name) = lower(%Q)", q_prefix,
3147 table);
3148 free (q_prefix);
3149 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3150 sqlite3_free (sql);
3151 if (ret != SQLITE_OK)
3152 {
3153 aux->error_message = errMsg;
3154 return 0;
3155 }
3156 }
3157 if (aux->ok_virts_geometry_columns)
3158 {
3159 /* deleting from VIEWS_GEOMETRY_COLUMNS */
3160 q_prefix = gaiaDoubleQuotedSql (prefix);
3161 sql = sqlite3_mprintf ("DELETE FROM \"%s\".virts_geometry_columns "
3162 "WHERE lower(virt_name) = lower(%Q)", q_prefix,
3163 table);
3164 free (q_prefix);
3165 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3166 sqlite3_free (sql);
3167 if (ret != SQLITE_OK)
3168 {
3169 aux->error_message = errMsg;
3170 return 0;
3171 }
3172 }
3173
3174 return 1;
3175 }
3176
3177 static int
doDropGeometryTriggers(sqlite3 * sqlite,const char * table,const char * geom,struct table_params * aux,char ** error_message)3178 doDropGeometryTriggers (sqlite3 * sqlite, const char *table, const char *geom,
3179 struct table_params *aux, char **error_message)
3180 {
3181 /* dropping Geometry Triggers */
3182 int ret;
3183 int i;
3184 char **results;
3185 int rows;
3186 int columns;
3187 char *sql;
3188 char *string;
3189 char *errMsg = NULL;
3190 if ((aux) && (aux->metadata_version == 2))
3191 {
3192 return 1; /* Fdo has no TRIGGERs */
3193 }
3194
3195 /* searching all Geometry Triggers to be dropped [for GeoPackage: wildcard before and after needed] */
3196 string = sqlite3_mprintf ("%%_%s_%s%%", table, geom);
3197 sql =
3198 sqlite3_mprintf
3199 ("SELECT name FROM MAIN.sqlite_master WHERE name LIKE %Q "
3200 "AND type = 'trigger'", string);
3201 sqlite3_free (string);
3202 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
3203 sqlite3_free (sql);
3204 if (ret != SQLITE_OK)
3205 return 0;
3206 if (rows < 1)
3207 ;
3208 else
3209 {
3210 for (i = 1; i <= rows; i++)
3211 {
3212 /* found a Geometry Trigger */
3213 const char *trigger = results[(i * columns) + 0];
3214 char *q_trigger = gaiaDoubleQuotedSql (trigger);
3215 sql = sqlite3_mprintf ("DROP TRIGGER main.\"%s\"", q_trigger);
3216 free (q_trigger);
3217 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3218 sqlite3_free (sql);
3219 if (ret != SQLITE_OK)
3220 {
3221 if (error_message != NULL)
3222 *error_message = errMsg;
3223 return 0;
3224 }
3225 }
3226 }
3227 sqlite3_free_table (results);
3228 return 1;
3229 }
3230
3231 /*/ -- -- ---------------------------------- --
3232 // doUpdateGeometryTriggers
3233 // -- -- ---------------------------------- --
3234 // Rebuild of TRIGGERs and SpatialIndex
3235 // - based on Database Type
3236 // - Spatialite: calls default updateGeometryTriggers
3237 // - GeoPackage: calls 'gpkgAddGeometryTriggers' and 'gpkgAddSpatialIndex'
3238 // -> building Index
3239 // - Fdo: return 1 with no further activity
3240 // -- -- ---------------------------------- --
3241 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
3242 // -- -- ---------------------------------- --
3243 */
3244 static int
doUpdateGeometryTriggers(sqlite3 * sqlite,const char * table,const char * column,struct table_params * aux,char ** error_message)3245 doUpdateGeometryTriggers (sqlite3 * sqlite, const char *table,
3246 const char *column, struct table_params *aux,
3247 char **error_message)
3248 {
3249 char *sql = NULL;
3250 switch (aux->metadata_version)
3251 {
3252 case 1:
3253 case 3:
3254 updateGeometryTriggers (sqlite, table, column); /* returns void */
3255 return 1;
3256 break;
3257 case 2:
3258 return 1; /* Fdo has no TRIGGERs */
3259 break;
3260 case 4:
3261 if (aux->ok_gpkg_extensions)
3262 {
3263 /* Note: fnct_gpkgAddGeometryTriggers does not check tha validaty of the arguments! */
3264 sql =
3265 sqlite3_mprintf ("SELECT gpkgAddGeometryTriggers(%Q,%Q);",
3266 table, column);
3267 }
3268 break;
3269 }
3270 if ((sql) && (aux->metadata_version == 4) && (aux->ok_gpkg_extensions))
3271 {
3272 /* Only for valid GeoPackage Databases */
3273 char *errMsg = NULL;
3274 int ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3275 sqlite3_free (sql);
3276 if (ret != SQLITE_OK)
3277 {
3278 if (error_message != NULL)
3279 {
3280 *error_message =
3281 sqlite3_mprintf
3282 ("gpkgAddGeometryTriggers for [%s(%s)] failed with rc=%d reason: %s",
3283 table, column, ret, errMsg);
3284 }
3285 sqlite3_free (errMsg);
3286 errMsg = NULL;
3287 return 0;
3288 }
3289 /* Previous SpatialIndex entry in gpkg_extensions was removed [new entry will be added during gpkgAddSpatialIndex] */
3290 sql =
3291 sqlite3_mprintf ("SELECT gpkgAddSpatialIndex(%Q,%Q);", table,
3292 column);
3293 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg); /* Usage of '&errMsg 'here causes a leak */
3294 sqlite3_free (sql);
3295 if (ret != SQLITE_OK)
3296 {
3297 if (error_message != NULL)
3298 {
3299 *error_message =
3300 sqlite3_mprintf
3301 ("gpkgAddSpatialIndex for [%s(%s)] failed with rc=%d reason: %s",
3302 table, column, ret, errMsg);
3303 }
3304 sqlite3_free (errMsg);
3305 errMsg = NULL;
3306 return 0;
3307 }
3308 /* Rebuild SpatialIndex [old SpatialIndex was removed] */
3309 sql =
3310 sqlite3_mprintf
3311 ("INSERT INTO \"rtree_%s_%s\" (id,minx,maxx,miny,maxy) SELECT ROWID, ST_MinX(\"%s\"),ST_MaxX(\"%s\"), ST_MinY(\"%s\"),ST_MaxY(\"%s\") FROM %Q;",
3312 table, column, column, column, column, column, table);
3313 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3314 sqlite3_free (sql);
3315 if (ret != SQLITE_OK)
3316 {
3317 if (error_message != NULL)
3318 {
3319 *error_message =
3320 sqlite3_mprintf
3321 ("filling rtree for [%s(%s)] failed with rc=%d reason: %s",
3322 table, column, ret, errMsg);
3323 }
3324 sqlite3_free (errMsg);
3325 errMsg = NULL;
3326 return 0;
3327 }
3328 if (errMsg)
3329 {
3330 sqlite3_free (errMsg);
3331 errMsg = NULL;
3332 }
3333 }
3334 return 1;
3335 }
3336
3337 static int
do_drop_geotriggers(sqlite3 * sqlite,const char * table,const char * column,struct table_params * aux,char ** error_message)3338 do_drop_geotriggers (sqlite3 * sqlite, const char *table, const char *column,
3339 struct table_params *aux, char **error_message)
3340 {
3341 /* dropping any previous Geometry Trigger */
3342 int ret;
3343 int i;
3344 char **results;
3345 int rows;
3346 int columns;
3347 char *sql;
3348 if ((aux) && (aux->metadata_version > 0)
3349 && ((aux->count_geometry_columns > 0)
3350 || (aux->is_geometry_column == 1)))
3351 {
3352 /*
3353 // This is a spatialite, GeoPackage/FDO TABLE that contains a geometry-column [not a sqlite3 TABLE]
3354 // searching all Geometry Triggers defined by the old Table
3355 */
3356 switch (aux->metadata_version)
3357 {
3358 case 1:
3359 case 3:
3360 if (column)
3361 sql =
3362 sqlite3_mprintf
3363 ("SELECT f_geometry_column FROM MAIN.geometry_columns WHERE Lower(f_table_name) = Lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3364 table, column);
3365 else
3366 sql =
3367 sqlite3_mprintf
3368 ("SELECT f_geometry_column FROM MAIN.geometry_columns WHERE Lower(f_table_name) = Lower(%Q)",
3369 table);
3370 break;
3371 case 2:
3372 return 1; /* Fdo has no TRIGGERs */
3373 break;
3374 case 4:
3375 sql =
3376 sqlite3_mprintf
3377 ("SELECT column_name FROM MAIN.gpkg_geometry_columns WHERE Lower(table_name) = Lower(%Q)",
3378 table);
3379 break;
3380 }
3381 ret =
3382 sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
3383 sqlite3_free (sql);
3384 if (ret != SQLITE_OK)
3385 return 0;
3386 if (rows < 1)
3387 ;
3388 else
3389 {
3390 for (i = 1; i <= rows; i++)
3391 {
3392 /* dropping Triggers for some Geometry Column */
3393 const char *geom = results[(i * columns) + 0];
3394 if (!doDropGeometryTriggers
3395 (sqlite, table, geom, aux, error_message))
3396 {
3397 sqlite3_free_table (results);
3398 return 0;
3399 }
3400 }
3401 }
3402 sqlite3_free_table (results);
3403 }
3404 return 1;
3405 }
3406
3407 static int
do_rebuild_geotriggers(sqlite3 * sqlite,const char * table,const char * column,struct table_params * aux)3408 do_rebuild_geotriggers (sqlite3 * sqlite, const char *table, const char *column,
3409 struct table_params *aux)
3410 {
3411 /* re-installing all Geometry Triggers */
3412 int ret;
3413 int i;
3414 char **results;
3415 int rows;
3416 int columns;
3417 char *sql;
3418 if ((aux) && (aux->metadata_version > 0)
3419 && ((aux->count_geometry_columns > 0)
3420 || (aux->is_geometry_column == 1)))
3421 {
3422 /*
3423 // This is a spatialite, GeoPackage/FDO TABLE that contains a geometry-column [not a sqlite3 TABLE]
3424 // searching all Geometry Triggers defined by the Table
3425 */
3426 switch (aux->metadata_version)
3427 {
3428 case 1:
3429 case 3:
3430 if (column)
3431 sql =
3432 sqlite3_mprintf
3433 ("SELECT f_geometry_column FROM MAIN.geometry_columns WHERE Lower(f_table_name) = Lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3434 table, column);
3435 else
3436 sql =
3437 sqlite3_mprintf
3438 ("SELECT f_geometry_column FROM MAIN.geometry_columns WHERE Lower(f_table_name) = Lower(%Q)",
3439 table);
3440 break;
3441 case 2:
3442 return 1; /* Fdo has no TRIGGERs */
3443 break;
3444 case 4:
3445 sql =
3446 sqlite3_mprintf
3447 ("SELECT column_name FROM MAIN.gpkg_geometry_columns WHERE Lower(table_name) = Lower(%Q)",
3448 table);
3449 break;
3450 }
3451 ret =
3452 sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
3453 sqlite3_free (sql);
3454 if (ret != SQLITE_OK)
3455 return 0;
3456 if (rows < 1)
3457 ;
3458 else
3459 {
3460 for (i = 1; i <= rows; i++)
3461 {
3462 /* found a Geometry Column, Rebuild of TRIGGERs and SpatialIndex based on Database Type */
3463 const char *geom = results[(i * columns) + 0];
3464 if (!doUpdateGeometryTriggers
3465 (sqlite, table, geom, aux, &aux->error_message))
3466 {
3467 sqlite3_free_table (results);
3468 return 0;
3469 }
3470 }
3471 }
3472 sqlite3_free_table (results);
3473 }
3474 return 1;
3475 }
3476
3477 static int
do_rename_column_pre(sqlite3 * sqlite,const char * prefix,const char * table,const char * old_name,const char * new_name,struct table_params * aux,char ** error_message)3478 do_rename_column_pre (sqlite3 * sqlite, const char *prefix, const char *table,
3479 const char *old_name, const char *new_name,
3480 struct table_params *aux, char **error_message)
3481 {
3482 /* renaming a Column - preparatory steps */
3483 char *sql;
3484 char *q_prefix;
3485 int ret;
3486 char *errMsg = NULL;
3487 if ((aux) && (aux->metadata_version > 0) && (aux->is_geometry_column == 1))
3488 {
3489 /*
3490 // Drop TRIGGERs only if a geometry-column is being renamed and it a spatialite geometry-column [not for GeoPackage or Fdo]
3491 // dropping any previous Geometry Trigger [spatialite only with at least 1 geometry-column ; returns true for others]
3492 */
3493 if (!do_drop_geotriggers
3494 (sqlite, table, old_name, aux, error_message))
3495 return 0;
3496 /* updating fist all metadata tables */
3497 /* updating GEOMETRY_COLUMNS */
3498 q_prefix = gaiaDoubleQuotedSql (prefix);
3499 switch (aux->metadata_version)
3500 {
3501 case 1:
3502 case 2: /* Fdo uses same column/table-name */
3503 case 3:
3504 sql =
3505 sqlite3_mprintf
3506 ("UPDATE \"%s\".geometry_columns SET f_geometry_column = lower(%Q) WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3507 q_prefix, new_name, table, old_name);
3508 break;
3509 case 4:
3510 sql =
3511 sqlite3_mprintf
3512 ("UPDATE \"%s\".gpkg_geometry_columns SET column_name = lower(%Q) WHERE Lower(table_name) = Lower(%Q)",
3513 q_prefix, new_name, table);
3514 break;
3515 }
3516 free (q_prefix);
3517 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3518 sqlite3_free (sql);
3519 if (ret != SQLITE_OK)
3520 {
3521 if (error_message != NULL)
3522 {
3523 if (aux->metadata_version == 4)
3524 *error_message =
3525 sqlite3_mprintf
3526 ("UPDATE of gpkg_geometry_columns for [%s(%s) from ] failed with rc=%d reason: %s",
3527 table, new_name, old_name, ret, errMsg);
3528 else
3529 *error_message =
3530 sqlite3_mprintf
3531 ("UPDATE of geometry_columns for [%s(%s) from ] failed with rc=%d reason: %s",
3532 table, new_name, old_name, ret, errMsg);
3533 }
3534 sqlite3_free (errMsg);
3535 errMsg = NULL;
3536 return 0;
3537 }
3538 if (aux->ok_layer_params)
3539 {
3540 /* updating LAYER_PARAMS */
3541 q_prefix = gaiaDoubleQuotedSql (prefix);
3542 sql =
3543 sqlite3_mprintf
3544 ("UPDATE \"%s\".layer_params SET geometry_column = lower(%Q) "
3545 "WHERE lower(table_name) = lower(%Q) AND lower(geometry_column) = lower(%Q)",
3546 q_prefix, new_name, table, old_name);
3547 free (q_prefix);
3548 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg); /* mj10777: possibly empty */
3549 sqlite3_free (sql);
3550 if (ret != SQLITE_OK)
3551 {
3552 if (error_message != NULL)
3553 *error_message = errMsg;
3554 return 0;
3555 }
3556 }
3557 if (aux->ok_layer_sub_classes)
3558 {
3559 /* updating LAYER_SUB_CLASSES */
3560 q_prefix = gaiaDoubleQuotedSql (prefix);
3561 sql =
3562 sqlite3_mprintf
3563 ("UPDATE \"%s\".layer_sub_classes SET geometry_column = lower(%Q) "
3564 "WHERE lower(table_name) = lower(%Q) AND lower(geometry_column) = lower(%Q)",
3565 q_prefix, new_name, table, old_name);
3566 free (q_prefix);
3567 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg); /* mj10777: possibly empty */
3568 sqlite3_free (sql);
3569 if (ret != SQLITE_OK)
3570 {
3571 if (error_message != NULL)
3572 *error_message = errMsg;
3573 return 0;
3574 }
3575 }
3576 if (aux->ok_layer_table_layout)
3577 {
3578 /* updating LAYER_TABLE_LAYOUT */
3579 q_prefix = gaiaDoubleQuotedSql (prefix);
3580 sql =
3581 sqlite3_mprintf
3582 ("UPDATE \"%s\".layer_table_layout SET geometry_column = lower(%Q) "
3583 "WHERE lower(table_name) = lower(%Q) AND lower(geometry_column) = lower(%Q)",
3584 q_prefix, new_name, table, old_name);
3585 free (q_prefix);
3586 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3587 sqlite3_free (sql);
3588 if (ret != SQLITE_OK)
3589 {
3590 if (error_message != NULL)
3591 *error_message = errMsg;
3592 return 0;
3593 }
3594 }
3595 if (aux->ok_geometry_columns_auth)
3596 {
3597 /* updating GEOMETRY_COLUMNS_AUTH */
3598 q_prefix = gaiaDoubleQuotedSql (prefix);
3599 sql =
3600 sqlite3_mprintf
3601 ("UPDATE \"%s\".geometry_columns_auth SET f_geometry_column = lower(%Q) "
3602 "WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3603 q_prefix, new_name, table, old_name);
3604 free (q_prefix);
3605 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3606 sqlite3_free (sql);
3607 if (ret != SQLITE_OK)
3608 {
3609 if (error_message != NULL)
3610 *error_message = errMsg;
3611 return 0;
3612 }
3613 }
3614 if (aux->ok_geometry_columns_time)
3615 {
3616 /* updating GEOMETRY_COLUMNS_TIME */
3617 q_prefix = gaiaDoubleQuotedSql (prefix);
3618 sql =
3619 sqlite3_mprintf
3620 ("UPDATE \"%s\".geometry_columns_time SET f_geometry_column = lower(%Q) "
3621 "WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3622 q_prefix, new_name, table, old_name);
3623 free (q_prefix);
3624 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3625 sqlite3_free (sql);
3626 if (ret != SQLITE_OK)
3627 {
3628 if (error_message != NULL)
3629 *error_message = errMsg;
3630 return 0;
3631 }
3632 }
3633 if (aux->ok_geometry_columns_field_infos)
3634 {
3635 /* updating GEOMETRY_COLUMNS_FIELD_INFOS */
3636 q_prefix = gaiaDoubleQuotedSql (prefix);
3637 sql =
3638 sqlite3_mprintf
3639 ("UPDATE \"%s\".geometry_columns_field_infos SET f_geometry_column = lower(%Q) "
3640 "WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3641 q_prefix, new_name, table, old_name);
3642 free (q_prefix);
3643 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3644 sqlite3_free (sql);
3645 if (ret != SQLITE_OK)
3646 {
3647 if (error_message != NULL)
3648 *error_message = errMsg;
3649 return 0;
3650 }
3651 }
3652 if (aux->ok_geometry_columns_statistics)
3653 {
3654 /* updating GEOMETRY_COLUMNS_STATISTICS */
3655 q_prefix = gaiaDoubleQuotedSql (prefix);
3656 sql =
3657 sqlite3_mprintf
3658 ("UPDATE \"%s\".geometry_columns_statistics SET f_geometry_column = lower(%Q) "
3659 "WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3660 q_prefix, new_name, table, old_name);
3661 free (q_prefix);
3662 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3663 sqlite3_free (sql);
3664 if (ret != SQLITE_OK)
3665 {
3666 if (error_message != NULL)
3667 *error_message = errMsg;
3668 return 0;
3669 }
3670 }
3671 if (aux->ok_layer_statistics)
3672 {
3673 /* updating LAYER_STATISTICS */
3674 q_prefix = gaiaDoubleQuotedSql (prefix);
3675 sql =
3676 sqlite3_mprintf
3677 ("UPDATE \"%s\".layer_statistics SET geometry_column = lower(%Q) "
3678 "WHERE lower(table_name) = lower(%Q) AND lower(geometry_column) = lower(%Q)",
3679 q_prefix, new_name, old_name);
3680 free (q_prefix);
3681 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3682 sqlite3_free (sql);
3683 if (ret != SQLITE_OK)
3684 {
3685 if (error_message != NULL)
3686 *error_message = errMsg;
3687 return 0;
3688 }
3689 }
3690 if (aux->ok_vector_coverages)
3691 {
3692 /* updating VECTOR_COVERAGES */
3693 q_prefix = gaiaDoubleQuotedSql (prefix);
3694 sql =
3695 sqlite3_mprintf
3696 ("UPDATE \"%s\".vector_coverages SET f_geometry_column = %Q "
3697 "WHERE lower(f_table_name) = lower(%Q) AND lower(f_geometry_column) = lower(%Q)",
3698 q_prefix, new_name, table, old_name);
3699 free (q_prefix);
3700 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3701 sqlite3_free (sql);
3702 if (ret != SQLITE_OK)
3703 {
3704 if (error_message != NULL)
3705 *error_message = errMsg;
3706 return 0;
3707 }
3708 }
3709 if (aux->ok_gpkg_extensions)
3710 {
3711 /* remove entry from gpkg_extensions */
3712 q_prefix = gaiaDoubleQuotedSql (prefix);
3713 sql = sqlite3_mprintf ("DELETE FROM \"%s\".gpkg_extensions "
3714 "WHERE lower(table_name) = lower(%Q) AND lower(column_name) = lower(%Q)",
3715 q_prefix, table, old_name);
3716 free (q_prefix);
3717 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3718 sqlite3_free (sql);
3719 if (ret != SQLITE_OK)
3720 {
3721 if (error_message != NULL)
3722 *error_message = errMsg;
3723 return 0;
3724 }
3725 }
3726 }
3727 if (errMsg)
3728 {
3729 sqlite3_free (errMsg);
3730 errMsg = NULL;
3731 }
3732 return 1;
3733 }
3734
3735 static int
do_rename_column_post(sqlite3 * sqlite,const char * prefix,const char * table,const char * old_name,const char * new_name,struct table_params * aux,char ** error_message)3736 do_rename_column_post (sqlite3 * sqlite, const char *prefix, const char *table,
3737 const char *old_name, const char *new_name,
3738 struct table_params *aux, char **error_message)
3739 {
3740 /* renaming a Column - final steps */
3741 char *sql;
3742 char *q_prefix;
3743 char *q_table;
3744 char *q_old;
3745 char *q_new;
3746 int ret;
3747 char *errMsg = NULL;
3748
3749 /* renaming the Column itself */
3750 q_prefix = gaiaDoubleQuotedSql (prefix);
3751 q_table = gaiaDoubleQuotedSql (table);
3752 q_old = gaiaDoubleQuotedSql (old_name);
3753 q_new = gaiaDoubleQuotedSql (new_name);
3754 sql =
3755 sqlite3_mprintf
3756 ("ALTER TABLE \"%s\".\"%s\" RENAME COLUMN \"%s\" TO \"%s\"", q_prefix,
3757 q_table, q_old, q_new);
3758 free (q_prefix);
3759 free (q_table);
3760 free (q_old);
3761 free (q_new);
3762 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3763 sqlite3_free (sql);
3764 if (ret != SQLITE_OK)
3765 {
3766 if (error_message != NULL)
3767 *error_message = errMsg;
3768 return 0;
3769 }
3770 /* re-installing all Geometry Triggers [spatialite only with at least 1 geometry-column ; returns true for others] */
3771 if (!do_rebuild_geotriggers (sqlite, table, new_name, aux))
3772 {
3773 if (aux->error_message)
3774 {
3775 /* Use the prepaired message, if we have one */
3776 if (error_message)
3777 {
3778 *error_message =
3779 sqlite3_mprintf ("%s", aux->error_message);
3780 }
3781 sqlite3_free (aux->error_message);
3782 aux->error_message = NULL;
3783 }
3784 else if (error_message != NULL)
3785 {
3786 *error_message =
3787 sqlite3_mprintf ("unable to rebuild Geometry Triggers");
3788 }
3789 return 0;
3790 }
3791 /* update of registred vector-styles is not needed since 'vector_coverages' now contains the renamed TABLE or COLUMN. The 'coverage_name' remains unchanged. */
3792 return 1;
3793 }
3794
3795 static int
do_drop_table5(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux,char ** error_message)3796 do_drop_table5 (sqlite3 * sqlite, const char *prefix, const char *table,
3797 struct table_params *aux, char **error_message)
3798 {
3799 /* dropping a Table */
3800 char *sql;
3801 char *q_prefix;
3802 char *q_table;
3803 int ret;
3804 char *errMsg = NULL;
3805 if ((aux) && (aux->metadata_version > 0))
3806 {
3807 /* updating first all metadata tables */
3808 if (aux->ok_geometry_columns)
3809 {
3810 /* deleting from GEOMETRY_COLUMNS */
3811 q_prefix = gaiaDoubleQuotedSql (prefix);
3812 sql =
3813 sqlite3_mprintf
3814 ("DELETE FROM \"%s\".geometry_columns WHERE lower(f_table_name) = lower(%Q)",
3815 q_prefix, table);
3816 free (q_prefix);
3817 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3818 sqlite3_free (sql);
3819 if (ret != SQLITE_OK)
3820 {
3821 if (error_message != NULL)
3822 *error_message = errMsg;
3823 return 0;
3824 }
3825 }
3826 if (aux->ok_views_geometry_columns)
3827 {
3828 /* deleting from VIEWS_GEOMETRY_COLUMNS */
3829 q_prefix = gaiaDoubleQuotedSql (prefix);
3830 sql =
3831 sqlite3_mprintf
3832 ("DELETE FROM \"%s\".views_geometry_columns WHERE lower(view_name) = lower(%Q)",
3833 q_prefix, table);
3834 free (q_prefix);
3835 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3836 sqlite3_free (sql);
3837 if (ret != SQLITE_OK)
3838 {
3839 if (error_message != NULL)
3840 *error_message = errMsg;
3841 return 0;
3842 }
3843 }
3844
3845 if (aux->ok_layer_params)
3846 {
3847 /* deleting from LAYER_PARAMS */
3848 q_prefix = gaiaDoubleQuotedSql (prefix);
3849 sql =
3850 sqlite3_mprintf
3851 ("DELETE FROM \"%s\".layer_params WHERE lower(table_name) = lower(%Q)",
3852 q_prefix, table);
3853 free (q_prefix);
3854 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3855 sqlite3_free (sql);
3856 if (ret != SQLITE_OK)
3857 {
3858 if (error_message != NULL)
3859 *error_message = errMsg;
3860 return 0;
3861 }
3862 }
3863 if (aux->ok_layer_sub_classes)
3864 {
3865 /* deleting from LAYER_SUB_CLASSES */
3866 q_prefix = gaiaDoubleQuotedSql (prefix);
3867 sql =
3868 sqlite3_mprintf
3869 ("DELETE FROM \"%s\".layer_sub_classes WHERE lower(table_name) = lower(%Q)",
3870 q_prefix, table);
3871 free (q_prefix);
3872 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3873 sqlite3_free (sql);
3874 if (ret != SQLITE_OK)
3875 {
3876 if (error_message != NULL)
3877 *error_message = errMsg;
3878 return 0;
3879 }
3880 }
3881 if (aux->ok_layer_table_layout)
3882 {
3883 /* deleting from LAYER_TABLE_LAYOUT */
3884 q_prefix = gaiaDoubleQuotedSql (prefix);
3885 sql =
3886 sqlite3_mprintf
3887 ("DELETE FROM \"%s\".layer_table_layout WHERE lower(table_name) = lower(%Q)",
3888 q_prefix, table);
3889 free (q_prefix);
3890 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3891 sqlite3_free (sql);
3892 if (ret != SQLITE_OK)
3893 {
3894 if (error_message != NULL)
3895 *error_message = errMsg;
3896 return 0;
3897 }
3898 }
3899 if (aux->ok_geometry_columns_auth)
3900 {
3901 /* deleting from GEOMETRY_COLUMNS_AUTH */
3902 q_prefix = gaiaDoubleQuotedSql (prefix);
3903 sql =
3904 sqlite3_mprintf
3905 ("DELETE FROM \"%s\".geometry_columns_auth WHERE lower(f_table_name) = lower(%Q)",
3906 q_prefix, table);
3907 free (q_prefix);
3908 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3909 sqlite3_free (sql);
3910 if (ret != SQLITE_OK)
3911 {
3912 if (error_message != NULL)
3913 *error_message = errMsg;
3914 return 0;
3915 }
3916 }
3917 if (aux->ok_geometry_columns_time)
3918 {
3919 /* deleting from GEOMETRY_COLUMNS_TIME */
3920 q_prefix = gaiaDoubleQuotedSql (prefix);
3921 sql =
3922 sqlite3_mprintf
3923 ("DELETE FROM \"%s\".geometry_columns_time WHERE lower(f_table_name) = lower(%Q)",
3924 q_prefix, table);
3925 free (q_prefix);
3926 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3927 sqlite3_free (sql);
3928 if (ret != SQLITE_OK)
3929 {
3930 if (error_message != NULL)
3931 *error_message = errMsg;
3932 return 0;
3933 }
3934 }
3935 if (aux->ok_geometry_columns_field_infos)
3936 {
3937 /* deleting from GEOMETRY_COLUMNS_FIELD_INFOS */
3938 q_prefix = gaiaDoubleQuotedSql (prefix);
3939 sql =
3940 sqlite3_mprintf
3941 ("DELETE FROM \"%s\".geometry_columns_field_infos WHERE lower(f_table_name) = lower(%Q)",
3942 q_prefix, table);
3943 free (q_prefix);
3944 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3945 sqlite3_free (sql);
3946 if (ret != SQLITE_OK)
3947 {
3948 if (error_message != NULL)
3949 *error_message = errMsg;
3950 return 0;
3951 }
3952 }
3953 if (aux->ok_geometry_columns_statistics)
3954 {
3955 /* deleting from GEOMETRY_COLUMNS_STATISTICS */
3956 q_prefix = gaiaDoubleQuotedSql (prefix);
3957 sql =
3958 sqlite3_mprintf
3959 ("DELETE FROM \"%s\".geometry_columns_statistics WHERE lower(f_table_name) = lower(%Q)",
3960 q_prefix, table);
3961 free (q_prefix);
3962 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3963 sqlite3_free (sql);
3964 if (ret != SQLITE_OK)
3965 {
3966 if (error_message != NULL)
3967 *error_message = errMsg;
3968 return 0;
3969 }
3970 }
3971 if (aux->ok_layer_statistics)
3972 {
3973 /* deleting from LAYER_STATISTICS */
3974 q_prefix = gaiaDoubleQuotedSql (prefix);
3975 sql =
3976 sqlite3_mprintf
3977 ("DELETE FROM \"%s\".layer_statistics WHERE lower(table_name) = lower(%Q)",
3978 q_prefix, table);
3979 free (q_prefix);
3980 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3981 sqlite3_free (sql);
3982 if (ret != SQLITE_OK)
3983 {
3984 if (error_message != NULL)
3985 *error_message = errMsg;
3986 return 0;
3987 }
3988 }
3989 if (aux->ok_vector_coverages)
3990 {
3991 /* deleting from VECTOR_COVERAGES */
3992 q_prefix = gaiaDoubleQuotedSql (prefix);
3993 sql =
3994 sqlite3_mprintf
3995 ("DELETE FROM \"%s\".vector_coverages WHERE lower(f_table_name) = lower(%Q)",
3996 q_prefix, table);
3997 free (q_prefix);
3998 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
3999 sqlite3_free (sql);
4000 if (ret != SQLITE_OK)
4001 {
4002 if (error_message != NULL)
4003 *error_message = errMsg;
4004 return 0;
4005 }
4006 }
4007 if (aux->ok_views_geometry_columns_auth)
4008 {
4009 /* deleting from VIEWS_GEOMETRY_COLUMNS_AUTH */
4010 q_prefix = gaiaDoubleQuotedSql (prefix);
4011 sql =
4012 sqlite3_mprintf
4013 ("DELETE FROM \"%s\".views_geometry_columns_auth "
4014 "WHERE lower(view_name) = lower(%Q)", q_prefix, table);
4015 free (q_prefix);
4016 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4017 sqlite3_free (sql);
4018 if (ret != SQLITE_OK)
4019 {
4020 aux->error_message = errMsg;
4021 return 0;
4022 }
4023 }
4024 if (aux->ok_views_geometry_columns_field_infos)
4025 {
4026 /* deleting from VIEWS_GEOMETRY_COLUMNS_FIELD_INFOS */
4027 q_prefix = gaiaDoubleQuotedSql (prefix);
4028 sql =
4029 sqlite3_mprintf
4030 ("DELETE FROM \"%s\".views_geometry_columns_field_infos "
4031 "WHERE view_name = %Q", q_prefix, table);
4032 free (q_prefix);
4033 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4034 sqlite3_free (sql);
4035 if (ret != SQLITE_OK)
4036 {
4037 aux->error_message = errMsg;
4038 return 0;
4039 }
4040 }
4041 if (aux->ok_views_geometry_columns_statistics)
4042 {
4043 /* deleting from VIEWS_GEOMETRY_COLUMNS_STATISTICS */
4044 q_prefix = gaiaDoubleQuotedSql (prefix);
4045 sql =
4046 sqlite3_mprintf
4047 ("DELETE FROM \"%s\".views_geometry_columns_statistics "
4048 "WHERE lower(view_name) = lower(%Q)", q_prefix, table);
4049 free (q_prefix);
4050 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4051 sqlite3_free (sql);
4052 if (ret != SQLITE_OK)
4053 {
4054 aux->error_message = errMsg;
4055 return 0;
4056 }
4057 }
4058 if ((aux->ok_gpkg_geometry_columns) && (aux->gpkg_table_type == 1))
4059 {
4060 /* GeoPackage-Vector: must be removed before 'gpkg_contents' entry */
4061 q_prefix = gaiaDoubleQuotedSql (prefix);
4062 sql =
4063 sqlite3_mprintf
4064 ("DELETE FROM \"%s\".gpkg_geometry_columns WHERE lower(table_name) = lower(%Q) ",
4065 q_prefix, table);
4066 free (q_prefix);
4067 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4068 sqlite3_free (sql);
4069 if (ret != SQLITE_OK)
4070 {
4071 if (error_message != NULL)
4072 *error_message = errMsg;
4073 return 0;
4074 }
4075 }
4076 }
4077 if (aux->ok_gpkg_contents)
4078 {
4079 /* GeoPackage only: update entry in gpkg_content [may be a raster] */
4080 if (aux->gpkg_table_type == 2)
4081 {
4082 /* GeoPackage-Raster: must be removed before 'gpkg_contents' entry */
4083 if (aux->ok_gpkg_tile_matrix)
4084 {
4085 /* update entry in gpkg_tile_matrix [only raster] */
4086 q_prefix = gaiaDoubleQuotedSql (prefix);
4087 sql =
4088 sqlite3_mprintf
4089 ("DELETE FROM \"%s\".gpkg_tile_matrix WHERE lower(table_name) = lower(%Q) ",
4090 q_prefix, table);
4091 free (q_prefix);
4092 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4093 sqlite3_free (sql);
4094 if (ret != SQLITE_OK)
4095 {
4096 if (error_message != NULL)
4097 *error_message = errMsg;
4098 return 0;
4099 }
4100 }
4101 if (aux->ok_gpkg_tile_matrix_set)
4102 {
4103 /* update entry in gpkg_tile_matrix_set [only raster] */
4104 q_prefix = gaiaDoubleQuotedSql (prefix);
4105 sql =
4106 sqlite3_mprintf
4107 ("DELETE FROM \"%s\".gpkg_tile_matrix_set WHERE lower(table_name) = lower(%Q) ",
4108 q_prefix, table);
4109 free (q_prefix);
4110 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4111 sqlite3_free (sql);
4112 if (ret != SQLITE_OK)
4113 {
4114 if (error_message != NULL)
4115 *error_message = errMsg;
4116 return 0;
4117 }
4118 }
4119 }
4120 q_prefix = gaiaDoubleQuotedSql (prefix);
4121 sql =
4122 sqlite3_mprintf
4123 ("DELETE FROM \"%s\".gpkg_contents WHERE lower(table_name) = lower(%Q) ",
4124 q_prefix, table);
4125 free (q_prefix);
4126 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4127 sqlite3_free (sql);
4128 if (ret != SQLITE_OK)
4129 {
4130 if (error_message != NULL)
4131 *error_message = errMsg;
4132 return 0;
4133 }
4134 if (aux->ok_gpkg_extensions)
4135 {
4136 /* update entry in gpkg_extensions [may be a raster] */
4137 q_prefix = gaiaDoubleQuotedSql (prefix);
4138 sql =
4139 sqlite3_mprintf
4140 ("DELETE FROM \"%s\".gpkg_extensions WHERE lower(table_name) = lower(%Q) ",
4141 q_prefix, table);
4142 free (q_prefix);
4143 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4144 sqlite3_free (sql);
4145 if (ret != SQLITE_OK)
4146 {
4147 if (error_message != NULL)
4148 *error_message = errMsg;
4149 return 0;
4150 }
4151 }
4152 if (aux->ok_gpkg_ogr_contents)
4153 {
4154 /* update entry in gpkg_ogr_contents [may be a raster] */
4155 q_prefix = gaiaDoubleQuotedSql (prefix);
4156 sql =
4157 sqlite3_mprintf
4158 ("DELETE FROM \"%s\".gpkg_ogr_contents WHERE lower(table_name) = lower(%Q) ",
4159 q_prefix, table);
4160 free (q_prefix);
4161 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4162 sqlite3_free (sql);
4163 if (ret != SQLITE_OK)
4164 {
4165 if (error_message != NULL)
4166 *error_message = errMsg;
4167 return 0;
4168 }
4169 }
4170 if (aux->ok_gpkg_metadata_reference)
4171 {
4172 /* update entry in gpkg_metadata_reference [mostly raster] */
4173 q_prefix = gaiaDoubleQuotedSql (prefix);
4174 sql =
4175 sqlite3_mprintf
4176 ("DELETE FROM \"%s\".gpkg_metadata_reference WHERE lower(table_name) = lower(%Q) ",
4177 q_prefix, table);
4178 free (q_prefix);
4179 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4180 sqlite3_free (sql);
4181 if (ret != SQLITE_OK)
4182 {
4183 /* Note: there may be no entries */
4184 if (error_message != NULL)
4185 *error_message = errMsg;
4186 return 0;
4187 }
4188 }
4189 } /* GeoPackage only */
4190
4191 /* dropping the Table itself */
4192 q_prefix = gaiaDoubleQuotedSql (prefix);
4193 q_table = gaiaDoubleQuotedSql (table);
4194 if (aux->is_view)
4195 sql = sqlite3_mprintf ("DROP VIEW \"%s\".\"%s\"", q_prefix, table);
4196 else
4197 sql = sqlite3_mprintf ("DROP TABLE \"%s\".\"%s\"", q_prefix, table);
4198 free (q_prefix);
4199 free (q_table);
4200 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4201 sqlite3_free (sql);
4202 if (ret != SQLITE_OK)
4203 {
4204 if (error_message != NULL)
4205 *error_message = errMsg;
4206 return 0;
4207 }
4208
4209 if (aux->ok_virts_geometry_columns)
4210 {
4211 /* deleting from VIRTS_GEOMETRY_COLUMNS */
4212 q_prefix = gaiaDoubleQuotedSql (prefix);
4213 sql =
4214 sqlite3_mprintf
4215 ("DELETE FROM \"%s\".virts_geometry_columns WHERE lower(virt_name) = lower(%Q)",
4216 q_prefix, table);
4217 free (q_prefix);
4218 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4219 sqlite3_free (sql);
4220 if (ret != SQLITE_OK)
4221 {
4222 if (error_message != NULL)
4223 *error_message = errMsg;
4224 return 0;
4225 }
4226 }
4227 if (aux->ok_virts_geometry_columns_auth)
4228 {
4229 /* deleting from VIRTS_GEOMETRY_COLUMNS_AUTH */
4230 q_prefix = gaiaDoubleQuotedSql (prefix);
4231 sql =
4232 sqlite3_mprintf ("DELETE FROM \"%s\".virts_geometry_columns_auth "
4233 "WHERE lower(virt_name) = lower(%Q)", q_prefix,
4234 table);
4235 free (q_prefix);
4236 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4237 sqlite3_free (sql);
4238 if (ret != SQLITE_OK)
4239 {
4240 aux->error_message = errMsg;
4241 return 0;
4242 }
4243 }
4244 if (aux->ok_virts_geometry_columns_field_infos)
4245 {
4246 /* deleting from VIRTS_GEOMETRY_COLUMNS_FIELD_INFOS */
4247 q_prefix = gaiaDoubleQuotedSql (prefix);
4248 sql =
4249 sqlite3_mprintf
4250 ("DELETE FROM \"%s\".virts_geometry_columns_field_infos "
4251 "WHERE lower(virt_name) = lower(%Q)", q_prefix, table);
4252 free (q_prefix);
4253 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4254 sqlite3_free (sql);
4255 if (ret != SQLITE_OK)
4256 {
4257 aux->error_message = errMsg;
4258 return 0;
4259 }
4260 }
4261 if (aux->ok_virts_geometry_columns_statistics)
4262 {
4263 /* deleting from VIRTS_GEOMETRY_COLUMNS_STATISTICS */
4264 q_prefix = gaiaDoubleQuotedSql (prefix);
4265 sql =
4266 sqlite3_mprintf
4267 ("DELETE FROM \"%s\".virts_geometry_columns_statistics "
4268 "WHERE lower(virt_name) = lower(%Q)", q_prefix, table);
4269 free (q_prefix);
4270 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4271 sqlite3_free (sql);
4272 if (ret != SQLITE_OK)
4273 {
4274 aux->error_message = errMsg;
4275 return 0;
4276 }
4277 }
4278
4279 return 1;
4280 }
4281
4282 static int
do_rename_table_pre(sqlite3 * sqlite,const char * prefix,const char * old_name,const char * new_name,struct table_params * aux,char ** error_message)4283 do_rename_table_pre (sqlite3 * sqlite, const char *prefix, const char *old_name,
4284 const char *new_name, struct table_params *aux,
4285 char **error_message)
4286 {
4287 /* renaming a Table - preparatory steps */
4288 char *sql;
4289 char *q_prefix;
4290 int ret;
4291 char *errMsg = NULL;
4292 if ((aux) && (aux->metadata_version > 0)
4293 && (aux->count_geometry_columns > 0))
4294 {
4295 /*
4296 // This is a spatialite, GeoPackage/FDO TABLE that contains a geometry-column [not a sqlite3 TABLE]
4297 // dropping any previous Geometry Trigger [spatialite and GeoPackage only with at least 1 geometry-column ; returns true for others]
4298 */
4299 if (!do_drop_geotriggers (sqlite, old_name, NULL, aux, error_message))
4300 return 0;
4301
4302 /* updating fist all metadata tables */
4303 if ((aux->ok_geometry_columns) || (aux->ok_gpkg_geometry_columns))
4304 {
4305 /* updating GEOMETRY_COLUMNS */
4306 q_prefix = gaiaDoubleQuotedSql (prefix);
4307 switch (aux->metadata_version)
4308 {
4309 case 1:
4310 case 2:
4311 case 3:
4312 sql =
4313 sqlite3_mprintf
4314 ("UPDATE \"%s\".geometry_columns SET f_table_name = lower(%Q) WHERE lower(f_table_name) = lower(%Q)",
4315 q_prefix, new_name, old_name);
4316 break;
4317 case 4:
4318 sql =
4319 sqlite3_mprintf
4320 ("UPDATE \"%s\".gpkg_geometry_columns SET table_name = lower(%Q) WHERE Lower(table_name) = Lower(%Q)",
4321 q_prefix, new_name, old_name);
4322 break;
4323 }
4324 free (q_prefix);
4325 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4326 sqlite3_free (sql);
4327 if (ret != SQLITE_OK)
4328 {
4329 if (error_message != NULL)
4330 *error_message = errMsg;
4331 return 0;
4332 }
4333 }
4334
4335 if (aux->ok_layer_params)
4336 {
4337 /* updating LAYER_PARAMS */
4338 q_prefix = gaiaDoubleQuotedSql (prefix);
4339 sql =
4340 sqlite3_mprintf
4341 ("UPDATE \"%s\".layer_params SET table_name = lower(%Q) "
4342 "WHERE lower(table_name) = lower(%Q)", q_prefix, new_name,
4343 old_name);
4344 free (q_prefix);
4345 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4346 sqlite3_free (sql);
4347 if (ret != SQLITE_OK)
4348 {
4349 if (error_message != NULL)
4350 *error_message = errMsg;
4351 return 0;
4352 }
4353 }
4354 if (aux->ok_layer_sub_classes)
4355 {
4356 /* updating LAYER_SUB_CLASSES */
4357 q_prefix = gaiaDoubleQuotedSql (prefix);
4358 sql =
4359 sqlite3_mprintf
4360 ("UPDATE \"%s\".layer_sub_classes SET table_name = lower(%Q) "
4361 "WHERE lower(table_name) = lower(%Q)", q_prefix, new_name,
4362 old_name);
4363 free (q_prefix);
4364 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4365 sqlite3_free (sql);
4366 if (ret != SQLITE_OK)
4367 {
4368 if (error_message != NULL)
4369 *error_message = errMsg;
4370 return 0;
4371 }
4372 }
4373 if (aux->ok_layer_table_layout)
4374 {
4375 /* updating LAYER_TABLE_LAYOUT */
4376 q_prefix = gaiaDoubleQuotedSql (prefix);
4377 sql =
4378 sqlite3_mprintf
4379 ("UPDATE \"%s\".layer_table_layout SET table_name = lower(%Q) "
4380 "WHERE lower(table_name) = lower(%Q)", q_prefix, new_name,
4381 old_name);
4382 free (q_prefix);
4383 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4384 sqlite3_free (sql);
4385 if (ret != SQLITE_OK)
4386 {
4387 if (error_message != NULL)
4388 *error_message = errMsg;
4389 return 0;
4390 }
4391 }
4392 if (aux->ok_geometry_columns_auth)
4393 {
4394 /* updating GEOMETRY_COLUMNS_AUTH */
4395 q_prefix = gaiaDoubleQuotedSql (prefix);
4396 sql =
4397 sqlite3_mprintf
4398 ("UPDATE \"%s\".geometry_columns_auth SET f_table_name = lower(%Q) "
4399 "WHERE lower(f_table_name) = lower(%Q)", q_prefix,
4400 new_name, old_name);
4401 free (q_prefix);
4402 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4403 sqlite3_free (sql);
4404 if (ret != SQLITE_OK)
4405 {
4406 if (error_message != NULL)
4407 *error_message = errMsg;
4408 return 0;
4409 }
4410 }
4411 if (aux->ok_geometry_columns_time)
4412 {
4413 /* updating GEOMETRY_COLUMNS_TIME */
4414 q_prefix = gaiaDoubleQuotedSql (prefix);
4415 sql =
4416 sqlite3_mprintf
4417 ("UPDATE \"%s\".geometry_columns_time SET f_table_name = lower(%Q) "
4418 "WHERE lower(f_table_name) = lower(%Q)", q_prefix,
4419 new_name, old_name);
4420 free (q_prefix);
4421 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4422 sqlite3_free (sql);
4423 if (ret != SQLITE_OK)
4424 {
4425 if (error_message != NULL)
4426 *error_message = errMsg;
4427 return 0;
4428 }
4429 }
4430 if (aux->ok_geometry_columns_field_infos)
4431 {
4432 /* updating GEOMETRY_COLUMNS_FIELD_INFOS */
4433 q_prefix = gaiaDoubleQuotedSql (prefix);
4434 sql =
4435 sqlite3_mprintf
4436 ("UPDATE \"%s\".geometry_columns_field_infos SET f_table_name = lower(%Q) "
4437 "WHERE lower(f_table_name) = lower(%Q)", q_prefix,
4438 new_name, old_name);
4439 free (q_prefix);
4440 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4441 sqlite3_free (sql);
4442 if (ret != SQLITE_OK)
4443 {
4444 if (error_message != NULL)
4445 *error_message = errMsg;
4446 return 0;
4447 }
4448 }
4449 if (aux->ok_geometry_columns_statistics)
4450 {
4451 /* updating GEOMETRY_COLUMNS_STATISTICS */
4452 q_prefix = gaiaDoubleQuotedSql (prefix);
4453 sql =
4454 sqlite3_mprintf
4455 ("UPDATE \"%s\".geometry_columns_statistics SET f_table_name = lower(%Q) "
4456 "WHERE lower(f_table_name) = lower(%Q)", q_prefix,
4457 new_name, old_name);
4458 free (q_prefix);
4459 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4460 sqlite3_free (sql);
4461 if (ret != SQLITE_OK)
4462 {
4463 if (error_message != NULL)
4464 *error_message = errMsg;
4465 return 0;
4466 }
4467 }
4468 if (aux->ok_layer_statistics)
4469 {
4470 /* updating LAYER_STATISTICS */
4471 q_prefix = gaiaDoubleQuotedSql (prefix);
4472 sql =
4473 sqlite3_mprintf
4474 ("UPDATE \"%s\".layer_statistics SET table_name = lower(%Q) "
4475 "WHERE lower(table_name) = lower(%Q)", q_prefix, new_name,
4476 old_name);
4477 free (q_prefix);
4478 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4479 sqlite3_free (sql);
4480 if (ret != SQLITE_OK)
4481 {
4482 if (error_message != NULL)
4483 *error_message = errMsg;
4484 return 0;
4485 }
4486 }
4487 if (aux->ok_vector_coverages)
4488 {
4489 /* updating VECTOR_COVERAGES */
4490 q_prefix = gaiaDoubleQuotedSql (prefix);
4491 sql =
4492 sqlite3_mprintf
4493 ("UPDATE \"%s\".vector_coverages SET f_table_name = %Q "
4494 "WHERE lower(f_table_name) = lower(%Q)", q_prefix,
4495 new_name, old_name);
4496 free (q_prefix);
4497 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4498 sqlite3_free (sql);
4499 if (ret != SQLITE_OK)
4500 {
4501 if (error_message != NULL)
4502 *error_message = errMsg;
4503 return 0;
4504 }
4505 }
4506 }
4507 if (aux->ok_gpkg_contents)
4508 {
4509 /* GeoPackage only: update entry in gpkg_content [may be a raster] */
4510 q_prefix = gaiaDoubleQuotedSql (prefix);
4511 sql =
4512 sqlite3_mprintf
4513 ("UPDATE \"%s\".gpkg_contents SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4514 q_prefix, new_name, old_name);
4515 free (q_prefix);
4516 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4517 sqlite3_free (sql);
4518 if (ret != SQLITE_OK)
4519 {
4520 if (error_message != NULL)
4521 *error_message = errMsg;
4522 return 0;
4523 }
4524 if (aux->ok_gpkg_extensions)
4525 {
4526 /* update entry in gpkg_extensions [may be a raster or SpatialIndex] */
4527 q_prefix = gaiaDoubleQuotedSql (prefix);
4528 /* Remove SpatialIndex entry [will be added later during gpkgAddSpatialIndex] */
4529 sql =
4530 sqlite3_mprintf
4531 ("DELETE FROM \"%s\".gpkg_extensions WHERE ((lower(table_name) = lower(%Q)) AND (extension_name = 'gpkg_rtree_index'))",
4532 q_prefix, old_name);
4533 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4534 sqlite3_free (sql);
4535 if (ret != SQLITE_OK)
4536 {
4537 free (q_prefix);
4538 if (error_message != NULL)
4539 *error_message = errMsg;
4540 return 0;
4541 }
4542 sql =
4543 sqlite3_mprintf
4544 ("UPDATE \"%s\".gpkg_extensions SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4545 q_prefix, new_name, old_name);
4546 free (q_prefix);
4547 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4548 sqlite3_free (sql);
4549 if (ret != SQLITE_OK)
4550 {
4551 if (error_message != NULL)
4552 *error_message = errMsg;
4553 return 0;
4554 }
4555 }
4556 if (aux->ok_gpkg_ogr_contents)
4557 {
4558 /* update entry in gpkg_ogr_contents [may be a raster] */
4559 q_prefix = gaiaDoubleQuotedSql (prefix);
4560 sql =
4561 sqlite3_mprintf
4562 ("UPDATE \"%s\".gpkg_ogr_contents SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4563 q_prefix, new_name, old_name);
4564 free (q_prefix);
4565 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4566 sqlite3_free (sql);
4567 if (ret != SQLITE_OK)
4568 {
4569 if (error_message != NULL)
4570 *error_message = errMsg;
4571 return 0;
4572 }
4573 }
4574 if (aux->ok_gpkg_metadata_reference)
4575 {
4576 /* update entry in gpkg_metadata_reference [mostly raster] */
4577 q_prefix = gaiaDoubleQuotedSql (prefix);
4578 sql =
4579 sqlite3_mprintf
4580 ("UPDATE \"%s\".gpkg_metadata_reference SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4581 q_prefix, new_name, old_name);
4582 free (q_prefix);
4583 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4584 sqlite3_free (sql);
4585 if (ret != SQLITE_OK)
4586 {
4587 /* Note: there may be no entries */
4588 if (error_message != NULL)
4589 *error_message = errMsg;
4590 return 0;
4591 }
4592 }
4593 if (aux->gpkg_table_type == 2)
4594 {
4595 /* GeoPackage-Rasters only */
4596 if (aux->ok_gpkg_tile_matrix)
4597 {
4598 /* update entry in gpkg_tile_matrix [only raster] */
4599 q_prefix = gaiaDoubleQuotedSql (prefix);
4600 sql =
4601 sqlite3_mprintf
4602 ("UPDATE \"%s\".gpkg_tile_matrix SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4603 q_prefix, new_name, old_name);
4604 free (q_prefix);
4605 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4606 sqlite3_free (sql);
4607 if (ret != SQLITE_OK)
4608 {
4609 if (error_message != NULL)
4610 *error_message = errMsg;
4611 return 0;
4612 }
4613 }
4614 if (aux->ok_gpkg_tile_matrix_set)
4615 {
4616 /* update entry in gpkg_tile_matrix_set [only raster] */
4617 q_prefix = gaiaDoubleQuotedSql (prefix);
4618 sql =
4619 sqlite3_mprintf
4620 ("UPDATE \"%s\".gpkg_tile_matrix_set SET table_name = lower(%Q) WHERE lower(table_name) = lower(%Q) ",
4621 q_prefix, new_name, old_name);
4622 free (q_prefix);
4623 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4624 sqlite3_free (sql);
4625 if (ret != SQLITE_OK)
4626 {
4627 if (error_message != NULL)
4628 *error_message = errMsg;
4629 return 0;
4630 }
4631 }
4632 }
4633 } /* GeoPackage only */
4634 return 1;
4635 }
4636
4637 static int
do_rename_table_post(sqlite3 * sqlite,const char * prefix,const char * old_name,const char * new_name,struct table_params * aux,char ** error_message)4638 do_rename_table_post (sqlite3 * sqlite, const char *prefix,
4639 const char *old_name, const char *new_name,
4640 struct table_params *aux, char **error_message)
4641 {
4642 /* renaming a Table - final steps */
4643 char *sql;
4644 char *q_prefix;
4645 char *q_old;
4646 char *q_new;
4647 int ret;
4648 char *errMsg = NULL;
4649
4650 /* renaming the Table itself */
4651 q_prefix = gaiaDoubleQuotedSql (prefix);
4652 q_old = gaiaDoubleQuotedSql (old_name);
4653 q_new = gaiaDoubleQuotedSql (new_name);
4654 sql =
4655 sqlite3_mprintf ("ALTER TABLE \"%s\".\"%s\" RENAME TO \"%s\"", q_prefix,
4656 q_old, q_new);
4657 free (q_prefix);
4658 free (q_old);
4659 free (q_new);
4660 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4661 sqlite3_free (sql);
4662 if (ret != SQLITE_OK)
4663 {
4664 if (error_message != NULL)
4665 *error_message = errMsg;
4666 return 0;
4667 }
4668
4669 /* re-installing all Geometry Triggers [spatialite only with at least 1 geometry-column ; returns true for others] */
4670 if (!do_rebuild_geotriggers (sqlite, new_name, NULL, aux))
4671 {
4672 if (aux->error_message)
4673 {
4674 /* Use the prepaired message, if we have one */
4675 if (error_message)
4676 {
4677 *error_message =
4678 sqlite3_mprintf ("%s", aux->error_message);
4679 }
4680 sqlite3_free (aux->error_message);
4681 aux->error_message = NULL;
4682 }
4683 else if (error_message != NULL)
4684 {
4685 *error_message =
4686 sqlite3_mprintf ("unable to rebuild Geometry Triggers");
4687 }
4688 return 0;
4689 }
4690 /* update of registred vector-styles is not needed since 'vector_coverages' now contains the renamed TABLE or COLUMN. The 'coverage_name' remains unchanged. */
4691 return 1;
4692 }
4693
4694 static int
do_drop_rtree(sqlite3 * sqlite,const char * prefix,const char * rtree,char ** error_message)4695 do_drop_rtree (sqlite3 * sqlite, const char *prefix, const char *rtree,
4696 char **error_message)
4697 {
4698 /* dropping some R*Tree */
4699 char *sql;
4700 char *q_prefix;
4701 char *q_rtree;
4702 int ret;
4703 char *errMsg = NULL;
4704
4705 q_prefix = gaiaDoubleQuotedSql (prefix);
4706 q_rtree = gaiaDoubleQuotedSql (rtree);
4707 sql = sqlite3_mprintf ("DROP TABLE \"%s\".\"%s\"", q_prefix, q_rtree);
4708 free (q_prefix);
4709 free (q_rtree);
4710 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &errMsg);
4711 sqlite3_free (sql);
4712 if (ret != SQLITE_OK)
4713 {
4714 if (error_message != NULL)
4715 *error_message = errMsg;
4716 return 0;
4717 }
4718 return 1;
4719 }
4720
4721 static int
do_drop_sub_view(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux)4722 do_drop_sub_view (sqlite3 * sqlite, const char *prefix, const char *table,
4723 struct table_params *aux)
4724 {
4725 /* dropping any depending View */
4726 int ret;
4727 int i;
4728 char **results;
4729 int rows;
4730 int columns;
4731 char *sql;
4732 char *q_prefix;
4733 struct table_params aux2;
4734
4735 if (aux->ok_views_geometry_columns == 0)
4736 return 1;
4737
4738 /* initializing the aux params */
4739 aux2.rtrees = NULL;
4740 aux2.n_rtrees = 0;
4741 aux2.is_view = 1;
4742 aux2.ok_geometry_columns = 0;
4743 aux2.ok_geometry_columns_time = aux->ok_geometry_columns_time;
4744 aux2.ok_views_geometry_columns = aux->ok_views_geometry_columns;
4745 aux2.ok_virts_geometry_columns = aux->ok_virts_geometry_columns;
4746 aux2.ok_geometry_columns_auth = aux->ok_geometry_columns_auth;
4747 aux2.ok_geometry_columns_field_infos = aux->ok_geometry_columns_field_infos;
4748 aux2.ok_geometry_columns_statistics = aux->ok_geometry_columns_statistics;
4749 aux2.ok_views_geometry_columns_auth = aux->ok_views_geometry_columns_auth;
4750 aux2.ok_views_geometry_columns_field_infos =
4751 aux->ok_views_geometry_columns_field_infos;
4752 aux2.ok_views_geometry_columns_statistics =
4753 aux->ok_views_geometry_columns_statistics;
4754 aux2.ok_virts_geometry_columns_auth = aux->ok_virts_geometry_columns_auth;
4755 aux2.ok_virts_geometry_columns_field_infos =
4756 aux->ok_virts_geometry_columns_field_infos;
4757 aux2.ok_virts_geometry_columns_statistics =
4758 aux->ok_virts_geometry_columns_statistics;
4759 aux2.ok_layer_statistics = aux->ok_layer_statistics;
4760 aux2.ok_views_layer_statistics = aux->ok_views_layer_statistics;
4761 aux2.ok_virts_layer_statistics = aux->ok_virts_layer_statistics;
4762 aux2.ok_layer_params = aux->ok_layer_params;
4763 aux2.ok_layer_sub_classes = aux->ok_layer_sub_classes;
4764 aux2.ok_layer_table_layout = aux->ok_layer_table_layout;
4765 aux2.ok_vector_coverages = aux->ok_vector_coverages;
4766 aux2.ok_vector_coverages_keyword = aux->ok_vector_coverages_keyword;
4767 aux2.ok_vector_coverages_srid = aux->ok_vector_coverages_srid;
4768 aux2.ok_se_vector_styled_layers = aux->ok_se_vector_styled_layers;
4769 aux2.ok_se_raster_styled_layers = aux->ok_se_raster_styled_layers;
4770 aux2.metadata_version = aux->metadata_version;
4771 aux2.ok_gpkg_geometry_columns = aux->ok_gpkg_geometry_columns;
4772 aux2.ok_gpkg_contents = aux->ok_gpkg_contents;
4773 aux2.ok_gpkg_extensions = aux->ok_gpkg_extensions;
4774 aux2.ok_gpkg_tile_matrix = aux->ok_gpkg_tile_matrix;
4775 aux2.ok_gpkg_tile_matrix_set = aux->ok_gpkg_tile_matrix_set;
4776 aux2.ok_gpkg_ogr_contents = aux->ok_gpkg_ogr_contents;
4777 aux2.ok_gpkg_metadata_reference = aux->ok_gpkg_metadata_reference;
4778 aux2.gpkg_table_type = aux->gpkg_table_type;
4779 /* 'command_type MUST not be reset! */
4780 aux2.ok_table_exists = 0;
4781 aux2.error_message = NULL;
4782 aux2.ok_table_exists = 0;
4783 aux2.is_geometry_column = 0;
4784 aux2.count_geometry_columns = 0;
4785 aux2.is_view = 0; /* default: not a view */
4786 aux2.table_type = -1; /* default: table not found */
4787 aux2.has_topologies = 0;
4788 aux2.is_raster_coverage_entry = 0;
4789 aux2.has_raster_coverages = 0;
4790
4791 /* identifying any View depending on the target */
4792 q_prefix = gaiaDoubleQuotedSql (prefix);
4793 sql =
4794 sqlite3_mprintf ("SELECT view_name FROM \"%s\".views_geometry_columns "
4795 "WHERE Lower(f_table_name) = Lower(%Q)", q_prefix,
4796 table);
4797 free (q_prefix);
4798 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
4799 sqlite3_free (sql);
4800 if (ret != SQLITE_OK)
4801 return 0;
4802 if (rows < 1)
4803 ;
4804 else
4805 {
4806 for (i = 1; i <= rows; i++)
4807 {
4808 const char *name = results[(i * columns) + 0];
4809 /* dropping the view itself */
4810 if (!do_drop_table (sqlite, prefix, name, &aux2))
4811 return 0;
4812 }
4813 }
4814 sqlite3_free_table (results);
4815 return 1;
4816 }
4817
4818 static int
check_table_layout(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux)4819 check_table_layout (sqlite3 * sqlite, const char *prefix, const char *table,
4820 struct table_params *aux)
4821 {
4822 /* checking the actual DB configuration */
4823 int i;
4824 char **results;
4825 int rows;
4826 int columns;
4827 char jolly = '%';
4828 int ret;
4829 char *sql;
4830 char *q_prefix = gaiaDoubleQuotedSql (prefix);
4831
4832 if (!aux)
4833 {
4834 return 0;
4835 }
4836 /* initializing the aux params */
4837 aux->rtrees = NULL;
4838 aux->n_rtrees = 0;
4839 aux->is_view = 0;
4840 aux->ok_geometry_columns = 0;
4841 aux->ok_geometry_columns_time = 0;
4842 aux->ok_views_geometry_columns = 0;
4843 aux->ok_virts_geometry_columns = 0;
4844 aux->ok_geometry_columns_auth = 0;
4845 aux->ok_geometry_columns_field_infos = 0;
4846 aux->ok_geometry_columns_statistics = 0;
4847 aux->ok_views_geometry_columns_auth = 0;
4848 aux->ok_views_geometry_columns_field_infos = 0;
4849 aux->ok_views_geometry_columns_statistics = 0;
4850 aux->ok_virts_geometry_columns_auth = 0;
4851 aux->ok_virts_geometry_columns_field_infos = 0;
4852 aux->ok_virts_geometry_columns_statistics = 0;
4853 aux->ok_layer_statistics = 0;
4854 aux->ok_views_layer_statistics = 0;
4855 aux->ok_virts_layer_statistics = 0;
4856 aux->ok_layer_params = 0;
4857 aux->ok_layer_sub_classes = 0;
4858 aux->ok_layer_table_layout = 0;
4859 aux->ok_vector_coverages = 0;
4860 aux->ok_vector_coverages_keyword = 0;
4861 aux->ok_vector_coverages_srid = 0;
4862 aux->ok_se_vector_styled_layers = 0;
4863 aux->ok_se_raster_styled_layers = 0;
4864 aux->metadata_version = 0;
4865 aux->ok_gpkg_geometry_columns = 0;
4866 aux->ok_gpkg_contents = 0;
4867 aux->ok_gpkg_extensions = 0;
4868 aux->ok_gpkg_tile_matrix = 0;
4869 aux->ok_gpkg_tile_matrix_set = 0;
4870 aux->ok_gpkg_ogr_contents = 0;
4871 aux->ok_gpkg_metadata_reference = 0;
4872 aux->gpkg_table_type = 0;
4873 /* 'command_type MUST not be reset! */
4874 aux->ok_table_exists = 0;
4875 aux->error_message = NULL;
4876
4877 aux->is_geometry_column = 0;
4878 aux->count_geometry_columns = 0;
4879 aux->is_view = 0; /* default: not a view */
4880 aux->table_type = -1; /* default: table not found */
4881 aux->has_topologies = 0;
4882 aux->is_raster_coverage_entry = 0;
4883 aux->has_raster_coverages = 0;
4884 if (strcasecmp (prefix, "TEMP") == 0)
4885 {
4886 /* TEMPORARY object; unconditioanally returning TRUE */
4887 free (q_prefix);
4888 aux->ok_table_exists = 1;
4889 aux->table_type = 1;
4890 return 1;
4891 }
4892 sql =
4893 sqlite3_mprintf
4894 ("SELECT type, name FROM \"%s\".sqlite_master WHERE type = 'table' or type = 'view'",
4895 q_prefix);
4896 free (q_prefix);
4897 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
4898 sqlite3_free (sql);
4899 if (ret != SQLITE_OK)
4900 return 0;
4901 if (rows < 1)
4902 ret = 0;
4903 else
4904 {
4905 ret = 1;
4906 for (i = 1; i <= rows; i++)
4907 {
4908 const char *type = results[(i * columns) + 0];
4909 const char *name = results[(i * columns) + 1];
4910 if (name)
4911 {
4912 /* checking which tables are actually defined */
4913 if (strcasecmp (name, "geometry_columns") == 0)
4914 aux->ok_geometry_columns = 1;
4915 else if (strcasecmp (name, "geometry_columns_time") == 0)
4916 aux->ok_geometry_columns_time = 1;
4917 else if (strcasecmp (name, "views_geometry_columns") == 0)
4918 aux->ok_views_geometry_columns = 1;
4919 else if (strcasecmp (name, "virts_geometry_columns") == 0)
4920 aux->ok_virts_geometry_columns = 1;
4921 else if (strcasecmp (name, "geometry_columns_auth") == 0)
4922 aux->ok_geometry_columns_auth = 1;
4923 else if (strcasecmp (name, "views_geometry_columns_auth")
4924 == 0)
4925 aux->ok_views_geometry_columns_auth = 1;
4926 else if (strcasecmp (name, "virts_geometry_columns_auth")
4927 == 0)
4928 aux->ok_virts_geometry_columns_auth = 1;
4929 if (strcasecmp (name, "geometry_columns_statistics") == 0)
4930 aux->ok_geometry_columns_statistics = 1;
4931 else if (strcasecmp
4932 (name, "views_geometry_columns_statistics") == 0)
4933 aux->ok_views_geometry_columns_statistics = 1;
4934 else if (strcasecmp
4935 (name, "virts_geometry_columns_statistics") == 0)
4936 aux->ok_virts_geometry_columns_statistics = 1;
4937 else if (strcasecmp (name, "geometry_columns_field_infos")
4938 == 0)
4939 aux->ok_geometry_columns_field_infos = 1;
4940 if (strcasecmp
4941 (name, "views_geometry_columns_field_infos") == 0)
4942 aux->ok_views_geometry_columns_field_infos = 1;
4943 else if (strcasecmp
4944 (name,
4945 "virts_geometry_columns_field_infos") == 0)
4946 aux->ok_virts_geometry_columns_field_infos = 1;
4947 else if (strcasecmp (name, "layer_params") == 0)
4948 aux->ok_layer_params = 1;
4949 else if (strcasecmp (name, "layer_statistics") == 0)
4950 aux->ok_layer_statistics = 1;
4951 else if (strcasecmp (name, "layer_sub_classes") == 0)
4952 aux->ok_layer_sub_classes = 1;
4953 else if (strcasecmp (name, "layer_table_layout") == 0)
4954 aux->ok_layer_table_layout = 1;
4955 else if (strcasecmp (name, "views_geometry_columns") == 0)
4956 aux->ok_views_geometry_columns = 1;
4957 else if (strcasecmp (name, "virts_geometry_columns") == 0)
4958 aux->ok_virts_geometry_columns = 1;
4959 else if (strcasecmp (name, "vector_coverages") == 0)
4960 aux->ok_vector_coverages = 1;
4961 else if (strcasecmp (name, "vector_coverages_keyword") ==
4962 0)
4963 aux->ok_vector_coverages_keyword = 1;
4964 else if (strcasecmp (name, "vector_coverages_srid") == 0)
4965 aux->ok_vector_coverages_srid = 1;
4966 else if (strcasecmp (name, "se_vector_styled_layers") ==
4967 0)
4968 aux->ok_se_vector_styled_layers = 1;
4969 else if (strcasecmp (name, "SE_raster_styled_layers") ==
4970 0)
4971 aux->ok_se_raster_styled_layers = 1;
4972 else if (strcasecmp (name, "topologies") == 0)
4973 aux->has_topologies = 1;
4974 else if (strcasecmp (name, "raster_coverages") == 0)
4975 aux->has_raster_coverages = 1;
4976 else if (strcasecmp (name, "gpkg_contents") == 0)
4977 aux->ok_gpkg_contents = 1;
4978 else if (strcasecmp (name, "gpkg_geometry_columns") == 0)
4979 aux->ok_gpkg_geometry_columns = 1;
4980 else if (strcasecmp (name, "gpkg_extensions") == 0)
4981 aux->ok_gpkg_extensions = 1;
4982 else if (strcasecmp (name, "gpkg_tile_matrix") == 0)
4983 aux->ok_gpkg_tile_matrix = 1;
4984 else if (strcasecmp (name, "gpkg_tile_matrix_set") == 0)
4985 aux->ok_gpkg_tile_matrix_set = 1;
4986 else if (strcasecmp (name, "gpkg_ogr_contents") == 0)
4987 aux->ok_gpkg_ogr_contents = 1;
4988 else if (strcasecmp (name, "gpkg_metadata_reference") ==
4989 0)
4990 aux->ok_gpkg_metadata_reference = 1;
4991
4992 if (strcasecmp (name, table) == 0)
4993 {
4994 aux->ok_table_exists = 1;
4995 /* checking if the target is a view */
4996 if (strcasecmp (type, "table") == 0)
4997 aux->table_type = 0;
4998 else if (strcasecmp (type, "view") == 0)
4999 {
5000 aux->is_view = 1;
5001 aux->table_type = 1;
5002 }
5003 else if (strcasecmp (type, "trigger") == 0)
5004 aux->table_type = 2;
5005 else if (strcasecmp (type, "index") == 0)
5006 aux->table_type = 3;
5007 }
5008 }
5009 }
5010 }
5011 sqlite3_free_table (results);
5012 if (!ret)
5013 return 0;
5014
5015 /* Used to avoid damage of Fdo-Tables */
5016 aux->metadata_version = checkSpatialMetaData (sqlite);
5017
5018 /* identifying any possible R*Tree supporting the main target */
5019 q_prefix = gaiaDoubleQuotedSql (prefix);
5020 if (aux->ok_gpkg_geometry_columns == 1)
5021 {
5022 sql =
5023 sqlite3_mprintf
5024 ("SELECT name FROM \"%s\".sqlite_master WHERE type = 'table' AND "
5025 "Lower(name) IN (SELECT "
5026 "Lower('rtree_' || table_name || '_' || column_name) "
5027 "FROM \"%s\".gpkg_geometry_columns WHERE Lower(table_name) = Lower(%Q)) "
5028 "AND sql LIKE('%cvirtual%c') AND sql LIKE('%crtree%c')",
5029 q_prefix, q_prefix, table, jolly, jolly, jolly, jolly);
5030 }
5031 else
5032 {
5033 sql =
5034 sqlite3_mprintf
5035 ("SELECT name FROM \"%s\".sqlite_master WHERE type = 'table' AND "
5036 "Lower(name) IN (SELECT "
5037 "Lower('idx_' || f_table_name || '_' || f_geometry_column) "
5038 "FROM \"%s\".geometry_columns WHERE Lower(f_table_name) = Lower(%Q)) "
5039 "AND sql LIKE('%cvirtual%c') AND sql LIKE('%crtree%c')",
5040 q_prefix, q_prefix, table, jolly, jolly, jolly, jolly);
5041 }
5042 free (q_prefix);
5043 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5044 sqlite3_free (sql);
5045 if (ret != SQLITE_OK)
5046 return 0;
5047 if (rows < 1)
5048 ;
5049 else
5050 {
5051 /* allocating the rtrees array */
5052 aux->n_rtrees = rows;
5053 aux->rtrees = malloc (sizeof (char **) * aux->n_rtrees);
5054 for (i = 1; i <= rows; i++)
5055 {
5056 const char *name = results[(i * columns) + 0];
5057 int len = strlen (name);
5058 *(aux->rtrees + (i - 1)) = malloc (len + 1);
5059 strcpy (*(aux->rtrees + (i - 1)), name);
5060 }
5061 }
5062 sqlite3_free_table (results);
5063 return 1;
5064 }
5065
5066 static int
check_topology_table(sqlite3 * sqlite,const char * prefix,const char * table)5067 check_topology_table (sqlite3 * sqlite, const char *prefix, const char *table)
5068 {
5069 /* checking for GeoTables belonging to some TopoGeo or TopoNet */
5070 char *xprefix;
5071 char *sql;
5072 char *table_name;
5073 int ret;
5074 int i;
5075 char **results;
5076 int rows;
5077 int columns;
5078 int found = 0;
5079
5080 if (prefix == NULL)
5081 prefix = "main";
5082
5083 /* testing within Topologies */
5084 xprefix = gaiaDoubleQuotedSql (prefix);
5085 sql =
5086 sqlite3_mprintf ("SELECT topology_name FROM \"%s\".topologies",
5087 xprefix);
5088 free (xprefix);
5089 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5090 sqlite3_free (sql);
5091 if (ret != SQLITE_OK)
5092 goto networks;
5093 if (rows < 1)
5094 ;
5095 else
5096 {
5097 for (i = 1; i <= rows; i++)
5098 {
5099 const char *name = results[(i * columns) + 0];
5100 table_name = sqlite3_mprintf ("%s_node", name);
5101 if (strcasecmp (table, table_name) == 0)
5102 found = 1;
5103 sqlite3_free (table_name);
5104 table_name = sqlite3_mprintf ("%s_edge", name);
5105 if (strcasecmp (table, table_name) == 0)
5106 found = 1;
5107 sqlite3_free (table_name);
5108 table_name = sqlite3_mprintf ("%s_face", name);
5109 if (strcasecmp (table, table_name) == 0)
5110 found = 1;
5111 sqlite3_free (table_name);
5112 table_name = sqlite3_mprintf ("%s_seeds", name);
5113 if (strcasecmp (table, table_name) == 0)
5114 found = 1;
5115 sqlite3_free (table_name);
5116 table_name = sqlite3_mprintf ("%s_topofeatures", name);
5117 if (strcasecmp (table, table_name) == 0)
5118 found = 1;
5119 sqlite3_free (table_name);
5120 table_name = sqlite3_mprintf ("%s_topolayers", name);
5121 if (strcasecmp (table, table_name) == 0)
5122 found = 1;
5123 sqlite3_free (table_name);
5124 }
5125 }
5126 sqlite3_free_table (results);
5127 if (found)
5128 {
5129 spatialite_e ("DropTable: can't drop TopoGeo table \"%s\".\"%s\"",
5130 prefix, table);
5131 return 1;
5132 }
5133
5134 networks:
5135 /* testing within Networks */
5136 xprefix = gaiaDoubleQuotedSql (prefix);
5137 sql = sqlite3_mprintf ("SELECT network_name FROM \"%s\".netowrks", xprefix);
5138 free (xprefix);
5139 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5140 sqlite3_free (sql);
5141 if (ret != SQLITE_OK)
5142 goto end;
5143 if (rows < 1)
5144 ;
5145 else
5146 {
5147 for (i = 1; i <= rows; i++)
5148 {
5149 const char *name = results[(i * columns) + 0];
5150 table_name = sqlite3_mprintf ("%s_node", name);
5151 if (strcasecmp (table, table_name) == 0)
5152 found = 1;
5153 sqlite3_free (table_name);
5154 table_name = sqlite3_mprintf ("%s_link", name);
5155 if (strcasecmp (table, table_name) == 0)
5156 found = 1;
5157 sqlite3_free (table_name);
5158 table_name = sqlite3_mprintf ("%s_seeds", name);
5159 if (strcasecmp (table, table_name) == 0)
5160 found = 1;
5161 sqlite3_free (table_name);
5162 }
5163 }
5164 sqlite3_free_table (results);
5165 if (found)
5166 return 1;
5167
5168 end:
5169 return 0;
5170 }
5171
5172 static int
check_raster_table(sqlite3 * sqlite,const char * prefix,const char * table,struct table_params * aux)5173 check_raster_table (sqlite3 * sqlite, const char *prefix, const char *table,
5174 struct table_params *aux)
5175 {
5176 /* checking for Raster Coverage tables */
5177 char *xprefix;
5178 char *sql;
5179 char *table_name;
5180 int ret;
5181 int i;
5182 char **results;
5183 int rows;
5184 int columns;
5185 int found = 0;
5186
5187 if (prefix == NULL)
5188 prefix = "main";
5189
5190 /* testing within Raster Coverages */
5191 xprefix = gaiaDoubleQuotedSql (prefix);
5192 sql =
5193 sqlite3_mprintf ("SELECT coverage_name FROM \"%s\".raster_coverages",
5194 xprefix);
5195 free (xprefix);
5196 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5197 sqlite3_free (sql);
5198 if (ret != SQLITE_OK)
5199 goto end;
5200 if (rows < 1)
5201 ;
5202 else
5203 {
5204 for (i = 1; i <= rows; i++)
5205 {
5206 const char *name = results[(i * columns) + 0];
5207 if (strcasecmp (table, name) == 0)
5208 {
5209 aux->is_raster_coverage_entry = 1;
5210 found = 1;
5211 }
5212 table_name = sqlite3_mprintf ("%s_node", name);
5213 if (strcasecmp (table, table_name) == 0)
5214 found = 1;
5215 sqlite3_free (table_name);
5216 table_name = sqlite3_mprintf ("%s_levels", name);
5217 if (strcasecmp (table, table_name) == 0)
5218 found = 1;
5219 sqlite3_free (table_name);
5220 table_name = sqlite3_mprintf ("%s_sections", name);
5221 if (strcasecmp (table, table_name) == 0)
5222 found = 1;
5223 sqlite3_free (table_name);
5224 table_name = sqlite3_mprintf ("%s_tiles", name);
5225 if (strcasecmp (table, table_name) == 0)
5226 found = 1;
5227 sqlite3_free (table_name);
5228 table_name = sqlite3_mprintf ("%s_tile_data", name);
5229 if (strcasecmp (table, table_name) == 0)
5230 found = 1;
5231 sqlite3_free (table_name);
5232 }
5233 }
5234 sqlite3_free_table (results);
5235 if (found)
5236 return 1;
5237 end:
5238 return 0;
5239 }
5240
5241 static int
check_rtree_internal_table(sqlite3 * sqlite,const char * prefix,const char * table,int is_gpkg)5242 check_rtree_internal_table (sqlite3 * sqlite, const char *prefix,
5243 const char *table, int is_gpkg)
5244 {
5245 /* checking for R*Tree internal tables */
5246 char *xprefix;
5247 char *rtree_type = NULL;
5248 char *sql;
5249 char *table_name;
5250 int ret;
5251 int i;
5252 char **results;
5253 int rows;
5254 int columns;
5255 int found = 0;
5256
5257 if (prefix == NULL)
5258 prefix = "main";
5259
5260 /* testing within Raster Coverages */
5261 xprefix = gaiaDoubleQuotedSql (prefix);
5262 if (!is_gpkg)
5263 {
5264 sql =
5265 sqlite3_mprintf
5266 ("SELECT f_table_name, f_geometry_column FROM \"%s\".geometry_columns WHERE spatial_index_enabled = 1",
5267 xprefix);
5268 rtree_type = sqlite3_mprintf ("idx");
5269 }
5270 else
5271 {
5272 sql =
5273 sqlite3_mprintf
5274 ("SELECT table_name, column_name FROM \"%s\".gpkg_geometry_columns ",
5275 xprefix);
5276 rtree_type = sqlite3_mprintf ("rtree");
5277 }
5278 free (xprefix);
5279 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5280 sqlite3_free (sql);
5281 if (ret != SQLITE_OK)
5282 goto end;
5283 if (rows < 1)
5284 ;
5285 else
5286 {
5287 for (i = 1; i <= rows; i++)
5288 {
5289 const char *tbl = results[(i * columns) + 0];
5290 const char *geom = results[(i * columns) + 1];
5291 table_name =
5292 sqlite3_mprintf ("%s_%s_%s_node", rtree_type, tbl, geom);
5293 if (strcasecmp (table, table_name) == 0)
5294 found = 1;
5295 sqlite3_free (table_name);
5296 table_name =
5297 sqlite3_mprintf ("%s_%s_%s_parent", rtree_type, tbl, geom);
5298 if (strcasecmp (table, table_name) == 0)
5299 found = 1;
5300 sqlite3_free (table_name);
5301 table_name =
5302 sqlite3_mprintf ("%s_%s_%s_rowid", rtree_type, tbl, geom);
5303 if (strcasecmp (table, table_name) == 0)
5304 found = 1;
5305 sqlite3_free (table_name);
5306 }
5307 }
5308 sqlite3_free_table (results);
5309 sqlite3_free (rtree_type);
5310 rtree_type = NULL;
5311 if (found)
5312 return 1;
5313 end:
5314 if (rtree_type)
5315 sqlite3_free (rtree_type);
5316 return 0;
5317 }
5318
5319 static int
check_spatialite_table(const char * table)5320 check_spatialite_table (const char *table)
5321 {
5322 /*
5323 // Note: sqlite3 prevents 'sqlite_master' from being droped [will not list itself, returns 'not existing table']
5324 // checking for SpatiaLite / RasterLite2 internal tables
5325 */
5326 if (strcasecmp (table, "data_licenses") == 0)
5327 return 1;
5328 if (strcasecmp (table, "geometry_columns") == 0)
5329 return 1;
5330 if (strcasecmp (table, "geometry_columns_time") == 0)
5331 return 1;
5332 if (strcasecmp (table, "networks") == 0)
5333 return 1;
5334 if (strcasecmp (table, "postgres_geometry_columns") == 0)
5335 return 1;
5336 if (strcasecmp (table, "raster_coverages") == 0)
5337 return 1;
5338 if (strcasecmp (table, "raster_coverages_keyword") == 0)
5339 return 1;
5340 if (strcasecmp (table, "raster_coverages_ref_sys") == 0)
5341 return 1;
5342 if (strcasecmp (table, "raster_coverages_srid") == 0)
5343 return 1;
5344 if (strcasecmp (table, "spatial_ref_sys") == 0)
5345 return 1;
5346 if (strcasecmp (table, "spatial_ref_sys_all") == 0)
5347 return 1;
5348 if (strcasecmp (table, "spatial_ref_sys_aux") == 0)
5349 return 1;
5350 if (strcasecmp (table, "spatialite_history") == 0)
5351 return 1;
5352 if (strcasecmp (table, "stored_procedures") == 0)
5353 return 1;
5354 if (strcasecmp (table, "stored_variables") == 0)
5355 return 1;
5356 if (strcasecmp (table, "tmp_vector_coverages") == 0)
5357 return 1;
5358 if (strcasecmp (table, "topologies") == 0)
5359 return 1;
5360 if (strcasecmp (table, "vector_coverages") == 0)
5361 return 1;
5362 if (strcasecmp (table, "vector_coverages_keyword") == 0)
5363 return 1;
5364 if (strcasecmp (table, "vector_coverages_ref_sys") == 0)
5365 return 1;
5366 if (strcasecmp (table, "vector_coverages_srid") == 0)
5367 return 1;
5368 if (strcasecmp (table, "vector_layers") == 0)
5369 return 1;
5370 if (strcasecmp (table, "views_geometry_columns") == 0)
5371 return 1;
5372 if (strcasecmp (table, "virts_geometry_columns") == 0)
5373 return 1;
5374 if (strcasecmp (table, "geometry_columns_auth") == 0)
5375 return 1;
5376 if (strcasecmp (table, "geometry_columns_field_infos") == 0)
5377 return 1;
5378 if (strcasecmp (table, "geometry_columns_statistics") == 0)
5379 return 1;
5380 if (strcasecmp (table, "geom_cols_ref_sys") == 0)
5381 return 1;
5382 if (strcasecmp (table, "sql_statements_log") == 0)
5383 return 1;
5384 if (strcasecmp (table, "vector_layers_auth") == 0)
5385 return 1;
5386 if (strcasecmp (table, "vector_layers_field_infos") == 0)
5387 return 1;
5388 if (strcasecmp (table, "vector_layers_statistics") == 0)
5389 return 1;
5390 if (strcasecmp (table, "views_geometry_columns_auth") == 0)
5391 return 1;
5392 if (strcasecmp (table, "views_geometry_columns_field_infos") == 0)
5393 return 1;
5394 if (strcasecmp (table, "views_geometry_columns_statistics") == 0)
5395 return 1;
5396 if (strcasecmp (table, "virts_geometry_columns_auth") == 0)
5397 return 1;
5398 if (strcasecmp (table, "virts_geometry_columns_field_infos") == 0)
5399 return 1;
5400 if (strcasecmp (table, "virts_geometry_columns_statistics") == 0)
5401 return 1;
5402 if (strcasecmp (table, "SE_external_graphics") == 0)
5403 return 1;
5404 if (strcasecmp (table, "SE_fonts") == 0)
5405 return 1;
5406 if (strcasecmp (table, "rl2map_configurations") == 0)
5407 return 1;
5408 if (strcasecmp (table, "SE_raster_styled_layers") == 0)
5409 return 1;
5410 if (strcasecmp (table, "SE_vector_styled_layers") == 0)
5411 return 1;
5412 if (strcasecmp (table, "SE_vector_styles") == 0)
5413 return 1;
5414 if (strcasecmp (table, "iso_metadata") == 0)
5415 return 1;
5416 if (strcasecmp (table, "iso_metadata_reference") == 0)
5417 return 1;
5418 if (strcasecmp (table, "KNN") == 0)
5419 return 1;
5420 if (strcasecmp (table, "SpatialIndex") == 0)
5421 return 1;
5422 return 0;
5423 }
5424
5425 static int
check_gpkg_table(const char * table)5426 check_gpkg_table (const char *table)
5427 {
5428 /*
5429 // Note: sqlite3 prevents 'sqlite_master' from being droped [will not list itsself, returns 'not existing table']
5430 // checking for GeoPackage / RasterLite2 internal tables
5431 */
5432 if (strcasecmp (table, "gpkg_contents") == 0)
5433 return 1;
5434 if (strcasecmp (table, "gpkg_extensions") == 0)
5435 return 1;
5436 if (strcasecmp (table, "gpkg_geometry_columns") == 0)
5437 return 1;
5438 if (strcasecmp (table, "gpkg_metadata") == 0)
5439 return 1;
5440 if (strcasecmp (table, "gpkg_metadata_reference") == 0)
5441 return 1;
5442 if (strcasecmp (table, "gpkg_spatial_ref_sys") == 0)
5443 return 1;
5444 if (strcasecmp (table, "gpkg_tile_matrix") == 0)
5445 return 1;
5446 if (strcasecmp (table, "gpkg_tile_matrix_set") == 0)
5447 return 1;
5448 if (strcasecmp (table, "gpkg_ogr_contents") == 0)
5449 return 1;
5450 return 0;
5451 }
5452
5453 /*/ -- -- ---------------------------------- --
5454 // do_check_gpkg_table_type
5455 // -- -- ---------------------------------- --
5456 // Called from
5457 // * check_for_system_tables
5458 // -- -- ---------------------------------- --
5459 // gpkg_type:
5460 // - 0 : table not found in 'gpkg_contents'
5461 // - 1: table is a 'features' type [Vector]
5462 // - 2: table is a 'tiles' type [Raster]
5463 // -- -- ---------------------------------- --
5464 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
5465 // -- -- ---------------------------------- --
5466 */
5467 static int
do_check_gpkg_table_type(sqlite3 * sqlite,const char * prefix,const char * table)5468 do_check_gpkg_table_type (sqlite3 * sqlite, const char *prefix,
5469 const char *table)
5470 {
5471 /* checking for a registered GeoPackage Vector or RasterTable */
5472 char *xprefix;
5473 char *sql_statement;
5474 int ret;
5475 sqlite3_stmt *stmt_sql = NULL;
5476 int gpkg_type = 0; /* Table not found */
5477 if (prefix == NULL)
5478 prefix = "main";
5479 xprefix = gaiaDoubleQuotedSql (prefix);
5480 sql_statement =
5481 sqlite3_mprintf
5482 ("SELECT CASE WHEN (data_type = 'features') THEN 1 ELSE 2 END FROM \"%s\".gpkg_contents WHERE Upper(table_name) = Upper(%Q)",
5483 xprefix, table);
5484 free (xprefix);
5485 ret = sqlite3_prepare_v2 (sqlite, sql_statement, -1, &stmt_sql, NULL);
5486 if (ret == SQLITE_OK)
5487 {
5488 sqlite3_free (sql_statement);
5489 while (sqlite3_step (stmt_sql) == SQLITE_ROW)
5490 {
5491 if (sqlite3_column_type (stmt_sql, 0) != SQLITE_NULL)
5492 {
5493 gpkg_type = sqlite3_column_int (stmt_sql, 0); /* 1=vector ; 2=raster */
5494 }
5495 }
5496 sqlite3_finalize (stmt_sql);
5497 }
5498 return gpkg_type;
5499 }
5500
5501 static int
do_check_geometry_column(sqlite3 * sqlite,const char * prefix,const char * table,const char * column,struct table_params * aux)5502 do_check_geometry_column (sqlite3 * sqlite, const char *prefix,
5503 const char *table, const char *column,
5504 struct table_params *aux)
5505 {
5506 /* checking for a registered GeoPackage Vector or RasterTable */
5507 char *xprefix;
5508 char *sql;
5509 int ret;
5510 int i;
5511 char **results;
5512 int rows;
5513 int columns;
5514 int is_geotable = 0;
5515 if ((aux) && (aux->metadata_version > 0)
5516 && ((aux->ok_geometry_columns == 1)
5517 || (aux->ok_gpkg_geometry_columns == 1)))
5518 {
5519 /* This is a spatialite, GeoPackage/FDO TABLE that contains a geometry-column [not a sqlite3 TABLE] */
5520 if (prefix == NULL)
5521 prefix = "main";
5522 /* parsing the SQL CREATE statement */
5523 xprefix = gaiaDoubleQuotedSql (prefix);
5524 if (aux->ok_geometry_columns == 1)
5525 {
5526 if (column)
5527 {
5528 sql =
5529 sqlite3_mprintf
5530 ("SELECT Count(*) FROM \"%s\".geometry_columns WHERE ((Upper(f_table_name) = Upper(%Q)) AND (Upper(f_geometry_column) = Upper(%Q)))",
5531 xprefix, table, column);
5532 }
5533 else
5534 {
5535 sql =
5536 sqlite3_mprintf
5537 ("SELECT Count(*) FROM \"%s\".geometry_columns WHERE (Upper(f_table_name) = Upper(%Q))",
5538 xprefix, table);
5539 }
5540 }
5541 else
5542 {
5543 if (column)
5544 {
5545 sql =
5546 sqlite3_mprintf
5547 ("SELECT Count(*) FROM \"%s\".gpkg_geometry_columns WHERE ((Upper(table_name) = Upper(%Q)) AND (Upper(column_name) = Upper(%Q)))",
5548 xprefix, table, column);
5549 }
5550 else
5551 {
5552 sql =
5553 sqlite3_mprintf
5554 ("SELECT Count(*) FROM \"%s\".gpkg_geometry_columns WHERE (Upper(table_name) = Upper(%Q))",
5555 xprefix, table);
5556 }
5557 }
5558 free (xprefix);
5559 ret =
5560 sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5561 sqlite3_free (sql);
5562 if (ret != SQLITE_OK)
5563 goto end;
5564 if (rows < 1)
5565 ;
5566 else
5567 {
5568 for (i = 1; i <= rows; i++)
5569 {
5570 const char *count = results[(i * columns) + 0];
5571 if (atoi (count) > 0)
5572 {
5573 is_geotable = 1;
5574 if (column)
5575 {
5576 aux->is_geometry_column = 1;
5577 }
5578 else
5579 {
5580 aux->count_geometry_columns = atoi (count);
5581 }
5582 }
5583 }
5584 }
5585 sqlite3_free_table (results);
5586 }
5587 if (is_geotable)
5588 return 1;
5589 end:
5590 return 0;
5591 }
5592
5593 static int
do_check_geotable(sqlite3 * sqlite,const char * prefix,const char * table)5594 do_check_geotable (sqlite3 * sqlite, const char *prefix, const char *table)
5595 {
5596 /* checking for a registered GeoTable */
5597 char *xprefix;
5598 char *sql;
5599 int ret;
5600 int i;
5601 char **results;
5602 int rows;
5603 int columns;
5604 int is_geotable = 0;
5605
5606 if (prefix == NULL)
5607 prefix = "main";
5608
5609 /* parsing the SQL CREATE statement */
5610 xprefix = gaiaDoubleQuotedSql (prefix);
5611 sql =
5612 sqlite3_mprintf
5613 ("SELECT Count(*) FROM \"%s\".geometry_columns WHERE Upper(f_table_name) = Upper(%Q)",
5614 xprefix, table);
5615 free (xprefix);
5616 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
5617 sqlite3_free (sql);
5618 if (ret != SQLITE_OK)
5619 goto end;
5620 if (rows < 1)
5621 ;
5622 else
5623 {
5624 for (i = 1; i <= rows; i++)
5625 {
5626 const char *count = results[(i * columns) + 0];
5627 if (atoi (count) > 0)
5628 is_geotable = 1;
5629 }
5630 }
5631 sqlite3_free_table (results);
5632 if (is_geotable)
5633 return 1;
5634 end:
5635 return 0;
5636 }
5637
5638 /*/ -- -- ---------------------------------- --
5639 // check_for_system_tables
5640 // -- -- ---------------------------------- --
5641 // Called from
5642 // * gaiaDropTable5 [command_type=0]
5643 // * gaiaDropTableEx3 [command_type=0]
5644 // * gaiaRenameTable [command_type=1 and 10 (for new table-name)]
5645 // * gaiaRenameColumn [command_type=2 and 20]
5646 // -- -- ---------------------------------- --
5647 // table_type: [as found in 'sqlite_master']
5648 // - 0: table
5649 // - 1: view
5650 // - 2: trigger
5651 // - 3: index
5652 // -- -- ---------------------------------- --
5653 // RasterLite2, GeoPackage and Fdo supported: for Raster and Vector
5654 // * gaiaRenameColumn: not supported for RasterLite2 [returns error]
5655 // -- -- ---------------------------------- --
5656 // Styles:
5657 // - Vector:
5658 // -> Update of registred vector-styles is not needed since 'vector_coverages' will contain the renamed TABLE or COLUMN. 7
5659 // -> The 'coverage_name' remains unchanged.
5660 // - Raster:
5661 // Update of any registred raster-style is delt with the renamed raster_coverage name
5662 // -- -- ---------------------------------- --
5663 // Author: Mark Johnson <mj10777@googlemail.com> 2019-01-12
5664 // -- -- ---------------------------------- --
5665 */
5666 static int
check_for_system_tables(sqlite3 * sqlite,const char * prefix,const char * table,const char * column,struct table_params * aux)5667 check_for_system_tables (sqlite3 * sqlite, const char *prefix,
5668 const char *table, const char *column,
5669 struct table_params *aux)
5670 {
5671 if (aux)
5672 {
5673 if (aux->command_type >= 10)
5674 {
5675 /* Avoid 'Uninitialised value was created by a stack allocation' report */
5676 if (((aux->ok_table_exists == 1) && (aux->table_type == 0))
5677 || (aux->is_raster_coverage_entry == 1))
5678 {
5679 /* 10 and 20 assume that 1 or 2 has been called and the TABLE is valid for changing [not: view, trigger or index] */
5680 if (aux->command_type == 10)
5681 {
5682 /* gaiaRenameTable checking if new TABLE name exists as [table, view, trigger or index] */
5683 if (aux->is_raster_coverage_entry == 1)
5684 {
5685 if (check_raster_table
5686 (sqlite, prefix, table, aux))
5687 {
5688 /* if the old-table name exists and is a Raster-Coverage-table, then this is an error */
5689 aux->error_message =
5690 sqlite3_mprintf
5691 ("forbidden: Raster Coverage name exists [%s.%s]",
5692 prefix, table);
5693 goto error_free_aux_memory;
5694 }
5695 }
5696 else
5697 {
5698 if (do_check_existing
5699 (sqlite, prefix, table, 3))
5700 {
5701 /* if the new table-name exists, then this is an error */
5702 aux->error_message =
5703 sqlite3_mprintf
5704 ("already existing table [%s.%s]",
5705 prefix, table);
5706 goto error_free_aux_memory;
5707 }
5708 }
5709 return 1;
5710 }
5711 if (aux->command_type == 20)
5712 {
5713 /* gaiaRenameColumn checking if new COLUMN name exists */
5714 if (do_check_existing_column
5715 (sqlite, prefix, table, column))
5716 {
5717 /* if the new column-name exists, then this is an error */
5718 aux->error_message =
5719 sqlite3_mprintf
5720 ("column already defined [%s.%s] %s",
5721 prefix, table, column);
5722 goto error_free_aux_memory;
5723 }
5724 return 1;
5725 }
5726 }
5727 }
5728 /* checking the actual DB configuration, storing found values */
5729 if (!check_table_layout (sqlite, prefix, table, aux))
5730 {
5731 /* fill 'table_params' with found system-tables [also checking if table exists and table_type] */
5732 aux->error_message =
5733 sqlite3_mprintf ("unable to get the DB layout");
5734 goto error_free_aux_memory;
5735 }
5736 if (aux->has_raster_coverages == 1)
5737 {
5738 /*
5739 // Only if TABLE 'raster_coverages' exist
5740 // avoiding to drop/rename Raster Coverage tables
5741 // Will set is_raster_coverage_entry = 1 if 'table' is a raster converage entry
5742 */
5743 if (check_raster_table (sqlite, prefix, table, aux))
5744 {
5745 if (aux->is_raster_coverage_entry == 0)
5746 {
5747 /* if the old-table name exists and is a Raster-Coverage Admin-table, then this is an error */
5748 aux->error_message =
5749 sqlite3_mprintf
5750 ("forbidden: Raster Coverage internal Table [%s.%s]",
5751 prefix, table);
5752 goto error_free_aux_memory;
5753 }
5754 }
5755 if (aux->is_raster_coverage_entry == 1)
5756 {
5757 /*
5758 // A raster converage entry was found
5759 // Update/Delete any registred raster-styles delt with
5760 */
5761 if (aux->command_type == 2)
5762 {
5763 /* gaiaRenameColumn */
5764 aux->error_message =
5765 sqlite3_mprintf
5766 ("forbidden: no column to rename in a Raster Coverage [%s.%s]",
5767 prefix, table);
5768 goto error_free_aux_memory;
5769 }
5770 }
5771 }
5772 if ((aux->ok_table_exists == 0)
5773 && (aux->is_raster_coverage_entry == 0))
5774 {
5775 /* if the old-table name does NOT exist, then this is an error */
5776 aux->error_message =
5777 sqlite3_mprintf ("not existing table [%s.%s]", prefix,
5778 table);
5779 goto error_free_aux_memory;
5780 }
5781 if ((aux->command_type == 0) && (aux->is_raster_coverage_entry == 0))
5782 {
5783 /*
5784 // gaiaDropTable checking source name [when not a raster_coverage name]
5785 // checking for an existing trigger or index
5786 */
5787 if ((aux->table_type < 0) || (aux->table_type > 1))
5788 {
5789 /* if the old-table name (as a trigger or index) exists, then this is an error */
5790 aux->error_message =
5791 sqlite3_mprintf
5792 ("forbidden: can't DROP a Trigger or Index, only Table and Views are supported [%s.%s]",
5793 prefix, table);
5794 goto error_free_aux_memory;
5795 }
5796 }
5797 if (((aux->command_type == 0) || (aux->command_type == 1))
5798 && (aux->is_raster_coverage_entry == 0))
5799 {
5800 /* Set aux->count_geometry_columns with the amount of geometry-columns this TABLE contains. */
5801 do_check_geometry_column (sqlite, prefix, table, NULL, aux);
5802 }
5803 if ((aux->command_type == 1) || (aux->command_type == 2))
5804 {
5805 /*
5806 // gaiaRenameTable checking source name [any trigger or index has already returned with error]
5807 // checking for an existing view
5808 */
5809 if (aux->table_type == 1)
5810 {
5811 /* if the old-table name (as a view) exists, then this is an error */
5812 aux->error_message =
5813 sqlite3_mprintf
5814 ("forbidden: can't rename a View, only Tables are supported [%s.%s]",
5815 prefix, table);
5816 goto error_free_aux_memory;
5817 }
5818 if ((strcasecmp (prefix, "main") != 0)
5819 && (aux->ok_geometry_columns == 1))
5820 {
5821 /* Only if TABLE 'geometry_columns' exist [inclusave RasterLite2] */
5822 if (aux->is_raster_coverage_entry == 1)
5823 {
5824 /* is a raster converage entry */
5825 aux->error_message =
5826 sqlite3_mprintf
5827 ("forbidden: Raster-Coverage not in the MAIN DB [%s.%s]",
5828 prefix, table);
5829 goto error_free_aux_memory;
5830 }
5831 else
5832 {
5833 /*/ checking if a GeoTable not in the MAIN DB */
5834 if (do_check_geotable (sqlite, prefix, table))
5835 {
5836 /* if the old-table name (in a ATTACHed database) exists, then this is an error */
5837 aux->error_message =
5838 sqlite3_mprintf
5839 ("forbidden: Spatial Table not in the MAIN DB [%s.%s]",
5840 prefix, table);
5841 goto error_free_aux_memory;
5842 }
5843 }
5844 }
5845 }
5846 if (check_spatialite_table (table))
5847 {
5848 /* if the old-table name is a system-table, then this is an error [does NOT check if TABLE exists] */
5849 aux->error_message =
5850 sqlite3_mprintf
5851 ("forbidden: SpatiaLite internal Table [%s.%s]", prefix,
5852 table);
5853 goto error_free_aux_memory;
5854 }
5855 if (aux->metadata_version == 2)
5856 {
5857 /* DROPing or Renaming Fdo Vector TABLE allowed */
5858 }
5859 if (aux->ok_gpkg_contents == 1)
5860 {
5861 /* DROPing or Renaming GeoPackage Vector/Raster internal not supported */
5862 if (check_gpkg_table (table))
5863 {
5864 /* if the old-table name is a system-table, then this is an error [does NOT check if TABLE exists] */
5865 aux->error_message =
5866 sqlite3_mprintf
5867 ("forbidden: GeoPackage internal Table [%s.%s]",
5868 prefix, table);
5869 goto error_free_aux_memory;
5870 }
5871 if ((aux->command_type == 0) || (aux->command_type == 1))
5872 {
5873 /* checking if a GeoPackage-Vector/Raster System-Table is being called */
5874 aux->gpkg_table_type =
5875 do_check_gpkg_table_type (sqlite, prefix, table);
5876 /* if the result is 0, this is not GeoPackage-Table */
5877 if ((aux->gpkg_table_type == 1)
5878 || (aux->gpkg_table_type == 2))
5879 {
5880 /* Renaming a non Geometry column in a GeoPackage vector/raster TABLE [1/2] is allowed */
5881 if (aux->gpkg_table_type == 1)
5882 {
5883 /* DROP or Renaming for GeoPackage vector/raster TABLE [1] is supported */
5884 }
5885 if (aux->gpkg_table_type == 2)
5886 {
5887 /* DROP or Renaming for GeoPackage raster TABLE [2] is supported */
5888 }
5889 }
5890 }
5891 }
5892 if (aux->has_topologies == 1)
5893 {
5894 /*
5895 // Only if TABLE 'topologies' exist
5896 // avoiding to drop/rename TopoGeo or TopoNet tables
5897 */
5898 if (check_topology_table (sqlite, prefix, table))
5899 {
5900 /* if the old-table name exists and is a topology-table, then this is an error */
5901 aux->error_message =
5902 sqlite3_mprintf
5903 ("forbidden: Topology internal Table [%s.%s]", prefix,
5904 table);
5905 goto error_free_aux_memory;
5906 }
5907 }
5908 if (aux->table_type == 0)
5909 {
5910 /*
5911 // R*Tree internal tables are of type 'table' [0]
5912 // avoiding to drop/rename R*Tree internal tables [Spatialite: 'idx_']
5913 */
5914 if (check_rtree_internal_table (sqlite, prefix, table, 0))
5915 {
5916 /* if the old-table name exists and is a Spatialite-R*Tree-table, then this is an error */
5917 aux->error_message =
5918 sqlite3_mprintf
5919 ("forbidden: R*Tree (Spatial Index) internal Table [%s.%s]",
5920 prefix, table);
5921 goto error_free_aux_memory;
5922 }
5923 /* avoiding to drop/rename R*Tree internal tables [GeoPackage: 'rtree_'] */
5924 if (check_rtree_internal_table (sqlite, prefix, table, 1))
5925 {
5926 /* if the old-table name exists and is a GeoPackage-R*Tree-table, then this is an error */
5927 aux->error_message =
5928 sqlite3_mprintf
5929 ("forbidden: R*Tree (GeoPackage-Spatial Index) internal Table [%s.%s]",
5930 prefix, table);
5931 goto error_free_aux_memory;
5932 }
5933 }
5934 if ((aux->command_type == 2)
5935 && ((aux->table_type == 0) || (aux->table_type == 1)))
5936 {
5937 /* Only tables/views supported will be queried */
5938 int gpkg_table_type =
5939 do_check_geometry_column (sqlite, prefix, table, column,
5940 aux);
5941 if (aux->ok_gpkg_geometry_columns == 1)
5942 {
5943 /* The gpkg_geometry_columns TABLE exists, check if Column is registered in gpkg_geometry_columns, renaming GeoPackage Geometry column is supported */
5944 gpkg_table_type =
5945 do_check_gpkg_table_type (sqlite, prefix, table);
5946 /* if the result is 0, this is not GeoPackage-Table and the column may not exist */
5947 if ((gpkg_table_type == 1) || (gpkg_table_type == 2))
5948 {
5949 /* Renaming a non Geometry column in a GeoPackage vector TABLE [1] is allowed */
5950 if (gpkg_table_type == 2)
5951 {
5952 /* prevent any column renaming for GeoPackage raster TABLE [2] */
5953 aux->error_message =
5954 sqlite3_mprintf
5955 ("forbidden: GeoPackage Raster column [%s.%s] %s",
5956 prefix, table, column);
5957 goto error_free_aux_memory;
5958 }
5959 }
5960 }
5961 if (aux->metadata_version == 2)
5962 {
5963 /* Renaming a Fdo Geometry column is supported */
5964 }
5965 if (aux->is_geometry_column == 0)
5966 {
5967 /* not a geometry column, checking if the column exists [spatialite, GeoPackage, Fdo] */
5968 if (!do_check_existing_column
5969 (sqlite, prefix, table, column))
5970 {
5971 /* if the old-column name does NOT exist, then this is an error */
5972 aux->error_message =
5973 sqlite3_mprintf
5974 ("not existing column [%s.%s] %s", prefix,
5975 table, column);
5976 goto error_free_aux_memory;
5977 }
5978 }
5979 }
5980 return 1;
5981 }
5982 error_free_aux_memory:
5983 if ((aux->n_rtrees > 0) && (aux->rtrees))
5984 {
5985 /* cleanup any allocated memory on an error condition */
5986 int i;
5987 for (i = 0; i < aux->n_rtrees; i++)
5988 {
5989 if (*(aux->rtrees + i) != NULL)
5990 {
5991 free (*(aux->rtrees + i));
5992 }
5993 }
5994 free (aux->rtrees);
5995 aux->rtrees = NULL;
5996 aux->n_rtrees = 0;
5997 }
5998 return 0; /* misuse of function [no pointer to struct table_params] */
5999 }
6000
6001 SPATIALITE_DECLARE int
gaiaDropTable(sqlite3 * sqlite,const char * table)6002 gaiaDropTable (sqlite3 * sqlite, const char *table)
6003 {
6004 return gaiaDropTableEx (sqlite, "main", table);
6005 }
6006
6007 SPATIALITE_DECLARE int
gaiaDropTableEx(sqlite3 * sqlite,const char * prefix,const char * table)6008 gaiaDropTableEx (sqlite3 * sqlite, const char *prefix, const char *table)
6009 {
6010 return gaiaDropTableEx2 (sqlite, prefix, table, 1);
6011 }
6012
6013 SPATIALITE_DECLARE int
gaiaDropTableEx2(sqlite3 * sqlite,const char * prefix,const char * table,int transaction)6014 gaiaDropTableEx2 (sqlite3 * sqlite, const char *prefix, const char *table,
6015 int transaction)
6016 {
6017 return gaiaDropTableEx3 (sqlite, prefix, table, transaction, NULL);
6018 }
6019
6020 SPATIALITE_DECLARE int
gaiaDropTableEx3(sqlite3 * sqlite,const char * prefix,const char * table,int transaction,char ** error_message)6021 gaiaDropTableEx3 (sqlite3 * sqlite, const char *prefix, const char *table,
6022 int transaction, char **error_message)
6023 {
6024 /*
6025 / DEPRECATED !!!!
6026 / use gaiaDropTable5() as a full replacement
6027 /
6028 / dropping a Spatial Table and any other related stuff
6029 */
6030 int ret;
6031 /* initializing done during check_for_system_tables call to check_table_layout, retaining command_type value */
6032 struct table_params aux;
6033 aux.command_type = 0;
6034
6035 if (error_message != NULL)
6036 *error_message = NULL;
6037 if (prefix == NULL)
6038 return 0;
6039 if (table == NULL)
6040 return 0;
6041
6042 if (transaction)
6043 {
6044 /* the whole operation is a single transaction */
6045 ret = sqlite3_exec (sqlite, "BEGIN", NULL, NULL, NULL);
6046 if (ret != SQLITE_OK)
6047 return 0;
6048 }
6049
6050 if (!check_for_system_tables (sqlite, prefix, table, NULL, &aux))
6051 {
6052 goto rollback;
6053 }
6054 if (aux.is_raster_coverage_entry == 1)
6055 {
6056 /* the table name is a verified raster_coverage entry */
6057 if (!do_drop_raster_coverage (sqlite, prefix, table, &aux))
6058 {
6059 if (aux.error_message)
6060 {
6061 if (error_message)
6062 {
6063 *error_message =
6064 sqlite3_mprintf ("%s", aux.error_message);
6065 }
6066 sqlite3_free (aux.error_message);
6067 aux.error_message = NULL;
6068 }
6069 goto rollback;
6070 }
6071 return 1;
6072 }
6073 /* recursively dropping any depending View */
6074 if (!do_drop_sub_view (sqlite, prefix, table, &aux))
6075 goto rollback;
6076 if (!do_drop_table (sqlite, prefix, table, &aux))
6077 goto rollback;
6078
6079 if (transaction)
6080 {
6081 /* committing the still pending transaction */
6082 ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, NULL);
6083 if (ret != SQLITE_OK)
6084 goto rollback;
6085 }
6086
6087 if (aux.rtrees)
6088 {
6089 /* memory cleanup - rtrees */
6090 int i;
6091 for (i = 0; i < aux.n_rtrees; i++)
6092 {
6093 if (*(aux.rtrees + i) != NULL)
6094 free (*(aux.rtrees + i));
6095 }
6096 free (aux.rtrees);
6097 }
6098 return 1;
6099
6100 rollback:
6101 if (transaction)
6102 {
6103 /* invalidating the still pending transaction */
6104 sqlite3_exec (sqlite, "ROLLBACK", NULL, NULL, NULL);
6105 }
6106
6107 if (aux.rtrees)
6108 {
6109 /* memory cleanup - rtrees */
6110 int i;
6111 for (i = 0; i < aux.n_rtrees; i++)
6112 {
6113 if (*(aux.rtrees + i) != NULL)
6114 free (*(aux.rtrees + i));
6115 }
6116 free (aux.rtrees);
6117 }
6118 if (aux.error_message != NULL)
6119 {
6120 if (error_message != NULL)
6121 *error_message = aux.error_message;
6122 else
6123 {
6124 spatialite_e ("DropGeoTable error: %s\r", aux.error_message);
6125 sqlite3_free (aux.error_message);
6126 }
6127 }
6128 return 0;
6129 }
6130
6131 SPATIALITE_DECLARE int
gaiaDropTable5(sqlite3 * sqlite,const char * prefix,const char * table,char ** error_message)6132 gaiaDropTable5 (sqlite3 * sqlite,
6133 const char *prefix, const char *table, char **error_message)
6134 {
6135 /* dropping a Spatial Table and any other related stuff */
6136 int ret;
6137 /* initializing done during check_for_system_tables call to check_table_layout, retaining command_type value */
6138 struct table_params aux;
6139 aux.command_type = 0;
6140
6141 if (error_message != NULL)
6142 *error_message = NULL;
6143
6144 if (prefix == NULL)
6145 prefix = "main";
6146 if (table == NULL)
6147 goto invalid_arg;
6148
6149 if (!check_for_system_tables (sqlite, prefix, table, NULL, &aux))
6150 {
6151 if (aux.error_message)
6152 {
6153 if (error_message)
6154 {
6155 *error_message =
6156 sqlite3_mprintf ("%s", aux.error_message);
6157 }
6158 sqlite3_free (aux.error_message);
6159 aux.error_message = NULL;
6160 }
6161 return 0;
6162 }
6163
6164 /* the whole operation is a single transaction */
6165 ret = sqlite3_exec (sqlite, "SAVEPOINT drop_table", NULL, NULL, NULL);
6166 if (ret != SQLITE_OK)
6167 {
6168 if (error_message)
6169 *error_message = sqlite3_mprintf ("unable to set a SAVEPOINT");
6170 return 0;
6171 }
6172 if (aux.is_raster_coverage_entry == 1)
6173 {
6174 /* the table name is a verified raster_coverage entry */
6175 if (!do_drop_raster_coverage (sqlite, prefix, table, &aux))
6176 {
6177 if (aux.error_message)
6178 {
6179 if (error_message)
6180 {
6181 *error_message =
6182 sqlite3_mprintf ("%s", aux.error_message);
6183 }
6184 sqlite3_free (aux.error_message);
6185 aux.error_message = NULL;
6186 }
6187 goto rollback;
6188 }
6189 goto savepoint;
6190 }
6191 if (!do_drop_table5 (sqlite, prefix, table, &aux, error_message))
6192 goto rollback;
6193
6194 if (aux.rtrees)
6195 {
6196 /* dropping old R*Trees */
6197 int i;
6198 for (i = 0; i < aux.n_rtrees; i++)
6199 {
6200 if (*(aux.rtrees + i) != NULL)
6201 {
6202 if (!do_drop_rtree
6203 (sqlite, prefix, *(aux.rtrees + i), error_message))
6204 goto rollback;
6205 }
6206 }
6207 }
6208 if (aux.rtrees)
6209 {
6210 /* memory cleanup - rtrees */
6211 int i;
6212 for (i = 0; i < aux.n_rtrees; i++)
6213 {
6214 if (*(aux.rtrees + i) != NULL)
6215 free (*(aux.rtrees + i));
6216 }
6217 free (aux.rtrees);
6218 }
6219
6220 savepoint:
6221 /* confirming the transaction */
6222 ret =
6223 sqlite3_exec (sqlite, "RELEASE SAVEPOINT drop_table", NULL, NULL, NULL);
6224 if (ret != SQLITE_OK)
6225 {
6226 if (error_message)
6227 *error_message =
6228 sqlite3_mprintf ("unable to RELEASE the SAVEPOINT");
6229 return 0;
6230 }
6231 return 1;
6232
6233 invalid_arg:
6234 if (error_message)
6235 *error_message = sqlite3_mprintf ("invalid argument.");
6236 return 0;
6237
6238 rollback:
6239 if (aux.rtrees)
6240 {
6241 /* memory cleanup - rtrees */
6242 int i;
6243 for (i = 0; i < aux.n_rtrees; i++)
6244 {
6245 if (*(aux.rtrees + i) != NULL)
6246 free (*(aux.rtrees + i));
6247 }
6248 free (aux.rtrees);
6249 }
6250 sqlite3_exec (sqlite, "ROLLBACK TO SAVEPOINT drop_table", NULL, NULL, NULL);
6251 sqlite3_exec (sqlite, "RELEASE SAVEPOINT drop_table", NULL, NULL, NULL);
6252 return 0;
6253 }
6254
6255 SPATIALITE_DECLARE int
gaiaRenameTable(sqlite3 * sqlite,const char * prefix,const char * old_name,const char * new_name,char ** error_message)6256 gaiaRenameTable (sqlite3 * sqlite,
6257 const char *prefix,
6258 const char *old_name,
6259 const char *new_name, char **error_message)
6260 {
6261 /* renaming a Spatial Table and any other related stuff */
6262 int ret;
6263 /* initializing done during check_for_system_tables call to check_table_layout, retaining command_type value */
6264 struct table_params aux;
6265 aux.command_type = 1;
6266 int fk_on = 1;
6267 char **results;
6268 int rows;
6269 int columns;
6270 int i;
6271
6272 if (error_message != NULL)
6273 *error_message = NULL;
6274
6275 /* checking the version of SQLite */
6276 if (sqlite3_libversion_number () < 3025000)
6277 {
6278 if (error_message)
6279 *error_message =
6280 sqlite3_mprintf
6281 ("libsqlite 3.25 or later is strictly required");
6282 return 0;
6283 }
6284 if (prefix == NULL)
6285 prefix = "main";
6286 if (old_name == NULL)
6287 goto invalid_arg;
6288 if (new_name == NULL)
6289 goto invalid_arg;
6290
6291 if (!check_for_system_tables (sqlite, prefix, old_name, NULL, &aux))
6292 {
6293 if (aux.error_message)
6294 {
6295 if (error_message)
6296 {
6297 *error_message =
6298 sqlite3_mprintf ("%s", aux.error_message);
6299 }
6300 sqlite3_free (aux.error_message);
6301 aux.error_message = NULL;
6302 }
6303 return 0;
6304 }
6305 else
6306 {
6307 /* The source TABLE exists and fulfills all other conditions. check if new_name exists */
6308 aux.command_type = 10; /* check_table_layout will not be called again */
6309 if (!check_for_system_tables (sqlite, prefix, new_name, NULL, &aux))
6310 {
6311 if (aux.error_message)
6312 {
6313 if (error_message)
6314 {
6315 *error_message =
6316 sqlite3_mprintf ("%s", aux.error_message);
6317 }
6318 sqlite3_free (aux.error_message);
6319 aux.error_message = NULL;
6320 }
6321 return 0;
6322 }
6323 }
6324 /* saving the current FKs mode */
6325 ret =
6326 sqlite3_get_table (sqlite, "PRAGMA foreign_keys", &results, &rows,
6327 &columns, NULL);
6328 if (ret == SQLITE_OK)
6329 {
6330 if (rows < 1)
6331 ;
6332 else
6333 {
6334 for (i = 1; i <= rows; i++)
6335 fk_on = atoi (results[(i * columns) + 0]);
6336 }
6337 sqlite3_free_table (results);
6338 }
6339 if (fk_on)
6340 {
6341 /* disabling FKs constraints */
6342 ret =
6343 sqlite3_exec (sqlite, "PRAGMA foreign_keys = 0", NULL, NULL,
6344 NULL);
6345 if (ret != SQLITE_OK)
6346 {
6347 if (error_message)
6348 *error_message =
6349 sqlite3_mprintf ("unable to disable FKs constraints");
6350 return 0;
6351 }
6352 }
6353
6354 /* starting a transaction */
6355 ret = sqlite3_exec (sqlite, "SAVEPOINT rename_table_pre", NULL, NULL, NULL);
6356 if (ret != SQLITE_OK)
6357 {
6358 if (error_message)
6359 *error_message = sqlite3_mprintf ("unable to set a SAVEPOINT");
6360 return 0;
6361 }
6362 if (aux.is_raster_coverage_entry == 1)
6363 {
6364 /* the old_name is a verified raster_coverage entry, the new_name does not exist in raster_coverage */
6365 if (!do_rename_raster_coverage
6366 (sqlite, prefix, old_name, new_name, &aux))
6367 {
6368 if (aux.error_message)
6369 {
6370 if (error_message)
6371 {
6372 *error_message =
6373 sqlite3_mprintf ("%s", aux.error_message);
6374 }
6375 sqlite3_free (aux.error_message);
6376 aux.error_message = NULL;
6377 }
6378 goto rollback;
6379 }
6380 goto savepoint;
6381 }
6382 if (!do_rename_table_pre
6383 (sqlite, prefix, old_name, new_name, &aux, error_message))
6384 goto rollback;
6385
6386 if (aux.rtrees)
6387 {
6388 /* dropping old R*Trees */
6389 int i;
6390 for (i = 0; i < aux.n_rtrees; i++)
6391 {
6392 if (*(aux.rtrees + i) != NULL)
6393 {
6394 if (!do_drop_rtree
6395 (sqlite, prefix, *(aux.rtrees + i), error_message))
6396 goto rollback;
6397 }
6398 }
6399 }
6400 if (aux.rtrees)
6401 {
6402 /* memory cleanup - rtrees */
6403 int i;
6404 for (i = 0; i < aux.n_rtrees; i++)
6405 {
6406 if (*(aux.rtrees + i) != NULL)
6407 free (*(aux.rtrees + i));
6408 }
6409 free (aux.rtrees);
6410 }
6411
6412 savepoint:
6413 /* confirming the transaction */
6414 ret =
6415 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_table_pre", NULL, NULL,
6416 NULL);
6417 if (ret != SQLITE_OK)
6418 {
6419 if (error_message)
6420 *error_message =
6421 sqlite3_mprintf ("unable to RELEASE the SAVEPOINT");
6422 return 0;
6423 }
6424
6425 if (fk_on)
6426 {
6427 /* re-enabling FKs constraints */
6428 ret =
6429 sqlite3_exec (sqlite, "PRAGMA foreign_keys = 1", NULL, NULL,
6430 NULL);
6431 if (ret != SQLITE_OK)
6432 {
6433 if (error_message)
6434 *error_message =
6435 sqlite3_mprintf ("unable to re-enable FKs constraints");
6436 return 0;
6437 }
6438 }
6439 if (aux.is_raster_coverage_entry == 1)
6440 {
6441 /* the table name is a verified raster_coverage entry */
6442 return 1;
6443 }
6444 /* and finally renaming the table itself */
6445 ret =
6446 sqlite3_exec (sqlite, "SAVEPOINT rename_table_post", NULL, NULL, NULL);
6447 if (ret != SQLITE_OK)
6448 {
6449 if (error_message)
6450 *error_message = sqlite3_mprintf ("unable to set a SAVEPOINT");
6451 return 0;
6452 }
6453 if (!do_rename_table_post
6454 (sqlite, prefix, old_name, new_name, &aux, error_message))
6455 goto rollback_post;
6456 ret =
6457 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_table_post", NULL, NULL,
6458 NULL);
6459 if (ret != SQLITE_OK)
6460 {
6461 if (error_message)
6462 *error_message =
6463 sqlite3_mprintf ("unable to RELEASE the SAVEPOINT");
6464 return 0;
6465 }
6466 return 1;
6467
6468 invalid_arg:
6469 if (error_message)
6470 *error_message = sqlite3_mprintf ("invalid argument.");
6471 return 0;
6472
6473 rollback:
6474 if (aux.rtrees)
6475 {
6476 /* memory cleanup - rtrees */
6477 int i;
6478 for (i = 0; i < aux.n_rtrees; i++)
6479 {
6480 if (*(aux.rtrees + i) != NULL)
6481 free (*(aux.rtrees + i));
6482 }
6483 free (aux.rtrees);
6484 }
6485 sqlite3_exec (sqlite, "ROLLBACK TO SAVEPOINT rename_table_pre", NULL,
6486 NULL, NULL);
6487 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_table_pre", NULL, NULL,
6488 NULL);
6489 return 0;
6490
6491 rollback_post:
6492 sqlite3_exec (sqlite, "ROLLBACK TO SAVEPOINT rename_table_post", NULL,
6493 NULL, NULL);
6494 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_table_post", NULL, NULL,
6495 NULL);
6496 return 0;
6497 }
6498
6499 SPATIALITE_DECLARE int
gaiaRenameColumn(sqlite3 * sqlite,const char * prefix,const char * table,const char * old_name,const char * new_name,char ** error_message)6500 gaiaRenameColumn (sqlite3 * sqlite,
6501 const char *prefix,
6502 const char *table,
6503 const char *old_name,
6504 const char *new_name, char **error_message)
6505 {
6506 /* renaming a Spatial Table's Column and any other related stuff */
6507 int ret;
6508 /* initializing done during check_for_system_tables call to check_table_layout, retaining command_type value */
6509 struct table_params aux;
6510 aux.command_type = 2;
6511 int fk_on = 1;
6512 char **results;
6513 int rows;
6514 int columns;
6515 int i;
6516
6517 if (error_message != NULL)
6518 *error_message = NULL;
6519
6520 /* checking the version of SQLite */
6521 if (sqlite3_libversion_number () < 3025000)
6522 {
6523 if (error_message)
6524 *error_message =
6525 sqlite3_mprintf
6526 ("libsqlite 3.25 or later is strictly required");
6527 return 0;
6528 }
6529 if (prefix == NULL)
6530 prefix = "main";
6531 if (old_name == NULL)
6532 goto invalid_arg;
6533 if (new_name == NULL)
6534 goto invalid_arg;
6535
6536 if (!check_for_system_tables (sqlite, prefix, table, old_name, &aux))
6537 {
6538 if (aux.error_message)
6539 {
6540 if (error_message)
6541 {
6542 *error_message =
6543 sqlite3_mprintf ("%s", aux.error_message);
6544 }
6545 sqlite3_free (aux.error_message);
6546 aux.error_message = NULL;
6547 }
6548 return 0;
6549 }
6550 else
6551 {
6552 /* The source TABLE and COLUMN exists and fulfills all other conditions. check if new_name exists */
6553 aux.command_type = 20; /* check_table_layout will not be called again */
6554 if (!check_for_system_tables (sqlite, prefix, table, new_name, &aux))
6555 {
6556 if (aux.error_message)
6557 {
6558 if (error_message)
6559 {
6560 *error_message =
6561 sqlite3_mprintf ("%s", aux.error_message);
6562 }
6563 sqlite3_free (aux.error_message);
6564 aux.error_message = NULL;
6565 }
6566 return 0;
6567 }
6568 }
6569 if (aux.is_raster_coverage_entry == 1)
6570 {
6571 /* the table name is a verified raster_coverage entry */
6572 return 0; /* should never happen [check_for_system_tables returns as error] */
6573 }
6574
6575 /* saving the current FKs mode */
6576 ret =
6577 sqlite3_get_table (sqlite, "PRAGMA foreign_keys", &results, &rows,
6578 &columns, NULL);
6579 if (ret == SQLITE_OK)
6580 {
6581 if (rows < 1)
6582 ;
6583 else
6584 {
6585 for (i = 1; i <= rows; i++)
6586 fk_on = atoi (results[(i * columns) + 0]);
6587 }
6588 sqlite3_free_table (results);
6589 }
6590 if (fk_on)
6591 {
6592 /* disabling FKs constraints */
6593 ret =
6594 sqlite3_exec (sqlite, "PRAGMA foreign_keys = 0", NULL, NULL,
6595 NULL);
6596 if (ret != SQLITE_OK)
6597 {
6598 if (error_message)
6599 *error_message =
6600 sqlite3_mprintf ("unable to disable FKs constraints");
6601 return 0;
6602 }
6603 }
6604
6605 /* the whole operation is a single transaction */
6606 ret =
6607 sqlite3_exec (sqlite, "SAVEPOINT rename_column_pre", NULL, NULL, NULL);
6608 if (ret != SQLITE_OK)
6609 {
6610 if (error_message)
6611 *error_message = sqlite3_mprintf ("unable to set a SAVEPOINT");
6612 return 0;
6613 }
6614 if (!do_rename_column_pre
6615 (sqlite, prefix, table, old_name, new_name, &aux, error_message))
6616 goto rollback;
6617
6618 if ((aux.rtrees) && (aux.is_geometry_column == 1))
6619 {
6620 /* dropping old R*Trees, only when renaming a geometry-column */
6621 int i;
6622 for (i = 0; i < aux.n_rtrees; i++)
6623 {
6624 if (*(aux.rtrees + i) != NULL)
6625 {
6626 if (!do_drop_rtree
6627 (sqlite, prefix, *(aux.rtrees + i), error_message))
6628 goto rollback;
6629 }
6630 }
6631 }
6632 if (aux.rtrees)
6633 {
6634 /* memory cleanup - rtrees */
6635 int i;
6636 for (i = 0; i < aux.n_rtrees; i++)
6637 {
6638 if (*(aux.rtrees + i) != NULL)
6639 free (*(aux.rtrees + i));
6640 }
6641 free (aux.rtrees);
6642 }
6643
6644 /* confirming the transaction */
6645 ret =
6646 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_column_pre", NULL, NULL,
6647 NULL);
6648 if (ret != SQLITE_OK)
6649 {
6650 if (error_message)
6651 *error_message =
6652 sqlite3_mprintf ("unable to RELEASE the SAVEPOINT");
6653 return 0;
6654 }
6655
6656 if (fk_on)
6657 {
6658 /* re-enabling FKs constraints */
6659 ret =
6660 sqlite3_exec (sqlite, "PRAGMA foreign_keys = 1", NULL, NULL,
6661 NULL);
6662 if (ret != SQLITE_OK)
6663 {
6664 if (error_message)
6665 *error_message =
6666 sqlite3_mprintf ("unable to re-enable FKs constraints");
6667 return 0;
6668 }
6669 }
6670
6671 /* renaming the column itself */
6672 ret =
6673 sqlite3_exec (sqlite, "SAVEPOINT rename_column_post", NULL, NULL, NULL);
6674 if (ret != SQLITE_OK)
6675 {
6676 if (error_message)
6677 *error_message = sqlite3_mprintf ("unable to set a SAVEPOINT");
6678 return 0;
6679 }
6680 if (!do_rename_column_post
6681 (sqlite, prefix, table, old_name, new_name, &aux, error_message))
6682 goto rollback_post;
6683 ret =
6684 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_column_post", NULL,
6685 NULL, NULL);
6686 if (ret != SQLITE_OK)
6687 {
6688 if (error_message)
6689 *error_message =
6690 sqlite3_mprintf ("unable to RELEASE the SAVEPOINT");
6691 return 0;
6692 }
6693 return 1;
6694
6695 invalid_arg:
6696 if (error_message)
6697 *error_message = sqlite3_mprintf ("invalid argument.");
6698 return 0;
6699
6700 rollback:
6701 if (aux.rtrees)
6702 {
6703 /* memory cleanup - rtrees */
6704 int i;
6705 for (i = 0; i < aux.n_rtrees; i++)
6706 {
6707 if (*(aux.rtrees + i) != NULL)
6708 free (*(aux.rtrees + i));
6709 }
6710 free (aux.rtrees);
6711 }
6712 sqlite3_exec (sqlite, "ROLLBACK TO SAVEPOINT rename_column_pre", NULL,
6713 NULL, NULL);
6714 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_column_pre", NULL, NULL,
6715 NULL);
6716 return 0;
6717
6718 rollback_post:
6719 sqlite3_exec (sqlite, "ROLLBACK TO SAVEPOINT rename_column_post", NULL,
6720 NULL, NULL);
6721 sqlite3_exec (sqlite, "RELEASE SAVEPOINT rename_column_post", NULL, NULL,
6722 NULL);
6723 return 0;
6724 }
6725