1 /*
2
3 extra_tables.c -- Creating all SLD/SE and ISO Metadata extra tables
4
5 version 5.0, 2020 August 1
6
7 Author: Sandro Furieri a.furieri@lqt.it
8
9 ------------------------------------------------------------------------------
10
11 Version: MPL 1.1/GPL 2.0/LGPL 2.1
12
13 The contents of this file are subject to the Mozilla Public License Version
14 1.1 (the "License"); you may not use this file except in compliance with
15 the License. You may obtain a copy of the License at
16 http://www.mozilla.org/MPL/
17
18 Software distributed under the License is distributed on an "AS IS" basis,
19 WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
20 for the specific language governing rights and limitations under the
21 License.
22
23 The Original Code is the SpatiaLite library
24
25 The Initial Developer of the Original Code is Alessandro Furieri
26
27 Portions created by the Initial Developer are Copyright (C) 2008-2021
28 the Initial Developer. All Rights Reserved.
29
30 Contributor(s):
31
32 Alternatively, the contents of this file may be used under the terms of
33 either the GNU General Public License Version 2 or later (the "GPL"), or
34 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
35 in which case the provisions of the GPL or the LGPL are applicable instead
36 of those above. If you wish to allow use of your version of this file only
37 under the terms of either the GPL or the LGPL, and not to allow others to
38 use your version of this file under the terms of the MPL, indicate your
39 decision by deleting the provisions above and replace them with the notice
40 and other provisions required by the GPL or the LGPL. If you do not delete
41 the provisions above, a recipient may use your version of this file under
42 the terms of any one of the MPL, the GPL or the LGPL.
43
44 */
45
46 /*
47
48 CREDITS:
49
50 this module has been partly funded by:
51 Regione Toscana - Settore Sistema Informativo Territoriale ed Ambientale
52 (implementing XML support - ISO Metadata and SLD/SE Styles)
53
54 */
55
56 #include <stdlib.h>
57 #include <stdio.h>
58 #include <string.h>
59
60 #if defined(_WIN32) && !defined(__MINGW32__)
61 #include "config-msvc.h"
62 #else
63 #include "config.h"
64 #endif
65
66 #include <spatialite/sqlite.h>
67 #include <spatialite/debug.h>
68
69 #include <spatialite.h>
70 #include <spatialite_private.h>
71 #include <spatialite/gaiaaux.h>
72 #include <spatialite/stored_procedures.h>
73
74 #ifdef _WIN32
75 #define strcasecmp _stricmp
76 #endif /* not WIN32 */
77
78 static int
check_splite_metacatalog(sqlite3 * sqlite)79 check_splite_metacatalog (sqlite3 * sqlite)
80 {
81 /* checks if "splite_metacatalog" really exists */
82 int table_name = 0;
83 int column_name = 0;
84 int table_name2 = 0;
85 int column_name2 = 0;
86 int value = 0;
87 int count = 0;
88 char sql[1024];
89 int ret;
90 const char *name;
91 int i;
92 char **results;
93 int rows;
94 int columns;
95 /* checking the "splite_metacatalog" table */
96 strcpy (sql, "PRAGMA table_info(splite_metacatalog)");
97 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
98 if (ret != SQLITE_OK)
99 return 0;
100 if (rows < 1)
101 ;
102 else
103 {
104 for (i = 1; i <= rows; i++)
105 {
106 name = results[(i * columns) + 1];
107 if (strcasecmp (name, "table_name") == 0)
108 table_name = 1;
109 if (strcasecmp (name, "column_name") == 0)
110 column_name = 1;
111 }
112 }
113 sqlite3_free_table (results);
114 /* checking the "splite_metacatalog_statistics" table */
115 strcpy (sql, "PRAGMA table_info(splite_metacatalog_statistics)");
116 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
117 if (ret != SQLITE_OK)
118 return 0;
119 if (rows < 1)
120 ;
121 else
122 {
123 for (i = 1; i <= rows; i++)
124 {
125 name = results[(i * columns) + 1];
126 if (strcasecmp (name, "table_name") == 0)
127 table_name2 = 1;
128 if (strcasecmp (name, "column_name") == 0)
129 column_name2 = 1;
130 if (strcasecmp (name, "value") == 0)
131 value = 1;
132 if (strcasecmp (name, "count") == 0)
133 count = 1;
134 }
135 }
136 sqlite3_free_table (results);
137 if (table_name && column_name && table_name2 && column_name2 && value
138 && count)
139 return 1;
140 return 0;
141 }
142
143 static int
metacatalog_statistics(sqlite3 * sqlite,sqlite3_stmt * stmt_out,sqlite3_stmt * stmt_del,const char * table,const char * column)144 metacatalog_statistics (sqlite3 * sqlite, sqlite3_stmt * stmt_out,
145 sqlite3_stmt * stmt_del, const char *table,
146 const char *column)
147 {
148 /* auxiliary - updating "splite_metacatalog_statistics" */
149 char *xtable;
150 char *xcolumn;
151 char *sql_statement;
152 int ret;
153 sqlite3_stmt *stmt_in;
154
155 xtable = gaiaDoubleQuotedSql (table);
156 xcolumn = gaiaDoubleQuotedSql (column);
157 sql_statement = sqlite3_mprintf ("SELECT \"%s\", Count(*) FROM \"%s\" "
158 "GROUP BY \"%s\"", xcolumn, xtable,
159 xcolumn);
160 free (xcolumn);
161 free (xtable);
162 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
163 &stmt_in, NULL);
164 sqlite3_free (sql_statement);
165 if (ret != SQLITE_OK)
166 {
167 spatialite_e ("Update MetaCatalog Statistics(4) error: \"%s\"\n",
168 sqlite3_errmsg (sqlite));
169 return 0;
170 }
171
172 /* deleting all existing rows */
173 sqlite3_reset (stmt_del);
174 sqlite3_clear_bindings (stmt_del);
175 sqlite3_bind_text (stmt_del, 1, table, strlen (table), SQLITE_STATIC);
176 sqlite3_bind_text (stmt_del, 2, column, strlen (column), SQLITE_STATIC);
177 ret = sqlite3_step (stmt_del);
178 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
179 ;
180 else
181 {
182 spatialite_e ("populate MetaCatalog Statistics(5) error: \"%s\"\n",
183 sqlite3_errmsg (sqlite));
184 sqlite3_finalize (stmt_in);
185 return 0;
186 }
187
188 while (1)
189 {
190 /* scrolling the result set rows */
191 ret = sqlite3_step (stmt_in);
192 if (ret == SQLITE_DONE)
193 break; /* end of result set */
194 if (ret == SQLITE_ROW)
195 {
196 sqlite3_reset (stmt_out);
197 sqlite3_clear_bindings (stmt_out);
198 sqlite3_bind_text (stmt_out, 1, table, strlen (table),
199 SQLITE_STATIC);
200 sqlite3_bind_text (stmt_out, 2, column, strlen (column),
201 SQLITE_STATIC);
202 switch (sqlite3_column_type (stmt_in, 0))
203 {
204 case SQLITE_INTEGER:
205 sqlite3_bind_int64 (stmt_out, 3,
206 sqlite3_column_int (stmt_in, 0));
207 break;
208 case SQLITE_FLOAT:
209 sqlite3_bind_double (stmt_out, 3,
210 sqlite3_column_double (stmt_in, 0));
211 break;
212 case SQLITE_TEXT:
213 sqlite3_bind_text (stmt_out, 3,
214 (const char *)
215 sqlite3_column_text (stmt_in, 0),
216 sqlite3_column_bytes (stmt_in, 0),
217 SQLITE_STATIC);
218 break;
219 case SQLITE_BLOB:
220 sqlite3_bind_blob (stmt_out, 3,
221 sqlite3_column_blob (stmt_in, 0),
222 sqlite3_column_bytes (stmt_in, 0),
223 SQLITE_STATIC);
224 break;
225 default:
226 sqlite3_bind_null (stmt_out, 3);
227 break;
228 };
229 sqlite3_bind_int (stmt_out, 4, sqlite3_column_int (stmt_in, 1));
230 ret = sqlite3_step (stmt_out);
231 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
232 ;
233 else
234 {
235 spatialite_e
236 ("populate MetaCatalog Statistics(6) error: \"%s\"\n",
237 sqlite3_errmsg (sqlite));
238 sqlite3_finalize (stmt_in);
239 return 0;
240 }
241 }
242 }
243 sqlite3_finalize (stmt_in);
244
245 return 1;
246 }
247
248 SPATIALITE_DECLARE int
gaiaUpdateMetaCatalogStatistics(sqlite3 * sqlite,const char * table,const char * column)249 gaiaUpdateMetaCatalogStatistics (sqlite3 * sqlite, const char *table,
250 const char *column)
251 {
252 /* Updates the "splite_metacalog_statistics" table */
253 char *sql_statement;
254 int ret;
255 sqlite3_stmt *stmt_in;
256 sqlite3_stmt *stmt_out;
257 sqlite3_stmt *stmt_del;
258
259 if (!check_splite_metacatalog (sqlite))
260 {
261 spatialite_e
262 ("invalid or not existing \"splite_metacatalog_statistics\" table\n");
263 return 0;
264 }
265
266 /* updating the MetaCatalog statistics */
267 sql_statement = sqlite3_mprintf ("SELECT table_name, column_name "
268 "FROM splite_metacatalog WHERE "
269 "Lower(table_name) = Lower(%Q) "
270 "AND Lower(column_name) = Lower(%Q)",
271 table, column);
272 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
273 &stmt_in, NULL);
274 sqlite3_free (sql_statement);
275 if (ret != SQLITE_OK)
276 {
277 spatialite_e ("Update MetaCatalog Statistics(1) error: \"%s\"\n",
278 sqlite3_errmsg (sqlite));
279 return 0;
280 }
281
282 sql_statement = "INSERT INTO splite_metacatalog_statistics "
283 "(table_name, column_name, value, count) " "VALUES (?, ?, ?, ?)";
284 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
285 &stmt_out, NULL);
286 if (ret != SQLITE_OK)
287 {
288 sqlite3_finalize (stmt_in);
289 spatialite_e ("Update MetaCatalog Statistics(2) error: \"%s\"\n",
290 sqlite3_errmsg (sqlite));
291 return 0;
292 }
293
294 sql_statement = "DELETE FROM splite_metacatalog_statistics "
295 "WHERE Lower(table_name) = Lower(?) AND Lower(column_name) = Lower(?)";
296 ret =
297 sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
298 &stmt_del, NULL);
299 if (ret != SQLITE_OK)
300 {
301 sqlite3_finalize (stmt_in);
302 sqlite3_finalize (stmt_out);
303 spatialite_e ("Update MetaCatalog Statistics(3) error: \"%s\"\n",
304 sqlite3_errmsg (sqlite));
305 return 0;
306 }
307
308 while (1)
309 {
310 /* scrolling the result set rows */
311 ret = sqlite3_step (stmt_in);
312 if (ret == SQLITE_DONE)
313 break; /* end of result set */
314 if (ret == SQLITE_ROW)
315 {
316 const char *table =
317 (const char *) sqlite3_column_text (stmt_in, 0);
318 const char *column =
319 (const char *) sqlite3_column_text (stmt_in, 1);
320 if (!metacatalog_statistics
321 (sqlite, stmt_out, stmt_del, table, column))
322 {
323 sqlite3_finalize (stmt_in);
324 sqlite3_finalize (stmt_out);
325 sqlite3_finalize (stmt_del);
326 return 0;
327 }
328 }
329 }
330 sqlite3_finalize (stmt_in);
331 sqlite3_finalize (stmt_out);
332 sqlite3_finalize (stmt_del);
333 return 1;
334 }
335
336 static int
check_master_table(sqlite3 * sqlite,const char * master_table,const char * table,const char * column)337 check_master_table (sqlite3 * sqlite, const char *master_table,
338 const char *table, const char *column)
339 {
340 /* checks if the Master Table could be accessed */
341 int table_name = 0;
342 int column_name = 0;
343 char *sql;
344 int ret;
345 char *xmaster;
346 const char *name;
347 int i;
348 char **results;
349 int rows;
350 int columns;
351 /* checking the Master table */
352 xmaster = gaiaDoubleQuotedSql (master_table);
353 sql = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", xmaster);
354 free (xmaster);
355 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
356 sqlite3_free (sql);
357 if (ret != SQLITE_OK)
358 return 0;
359 if (rows < 1)
360 ;
361 else
362 {
363 for (i = 1; i <= rows; i++)
364 {
365 name = results[(i * columns) + 1];
366 if (strcasecmp (name, table) == 0)
367 table_name = 1;
368 if (strcasecmp (name, column) == 0)
369 column_name = 1;
370 }
371 }
372 sqlite3_free_table (results);
373 if (table_name && column_name)
374 return 1;
375 return 0;
376 }
377
378 SPATIALITE_DECLARE int
gaiaUpdateMetaCatalogStatisticsFromMaster(sqlite3 * sqlite,const char * master_table,const char * table_name,const char * column_name)379 gaiaUpdateMetaCatalogStatisticsFromMaster (sqlite3 * sqlite,
380 const char *master_table,
381 const char *table_name,
382 const char *column_name)
383 {
384 /* Updates the "splite_metacalog_statistics" table (using a Master Table) */
385 int ret;
386 char *sql_statement;
387 sqlite3_stmt *stmt;
388 char *xmaster;
389 char *xtable;
390 char *xcolumn;
391 if (!check_master_table (sqlite, master_table, table_name, column_name))
392 {
393 spatialite_e
394 ("UpdateMetaCatalogStatisticsFromMaster: mismatching or not existing Master Table\n");
395 return 0;
396 }
397
398 /* scanning the Master Table */
399 xmaster = gaiaDoubleQuotedSql (master_table);
400 xtable = gaiaDoubleQuotedSql (table_name);
401 xcolumn = gaiaDoubleQuotedSql (column_name);
402 sql_statement =
403 sqlite3_mprintf ("SELECT \"%s\", \"%s\" FROM \"%s\"", xtable, xcolumn,
404 xmaster);
405 free (xmaster);
406 free (xtable);
407 free (xcolumn);
408 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
409 &stmt, NULL);
410 sqlite3_free (sql_statement);
411 if (ret != SQLITE_OK)
412 {
413 spatialite_e
414 ("UpdateMetaCatalogStatisticsFromMaster(1) error: \"%s\"\n",
415 sqlite3_errmsg (sqlite));
416 return 0;
417 }
418
419 while (1)
420 {
421 /* scrolling the result set rows */
422 ret = sqlite3_step (stmt);
423 if (ret == SQLITE_DONE)
424 break; /* end of result set */
425 if (ret == SQLITE_ROW)
426 {
427 const char *table =
428 (const char *) sqlite3_column_text (stmt, 0);
429 const char *column =
430 (const char *) sqlite3_column_text (stmt, 1);
431 if (!gaiaUpdateMetaCatalogStatistics (sqlite, table, column))
432 {
433 sqlite3_finalize (stmt);
434 return 0;
435 }
436 }
437 }
438 sqlite3_finalize (stmt);
439 return 1;
440 }
441
442 static int
check_unique_index(sqlite3 * sqlite,const char * index,const char * column)443 check_unique_index (sqlite3 * sqlite, const char *index, const char *column)
444 {
445 /* checks if a column has any Unique constraint - pass two */
446 char *xindex;
447 char *sql_statement;
448 int ret;
449 sqlite3_stmt *stmt_in;
450 int is_unique = 0;
451 int index_parts = 0;
452
453 xindex = gaiaDoubleQuotedSql (index);
454 sql_statement = sqlite3_mprintf ("PRAGMA index_info(\"%s\")", xindex);
455 free (xindex);
456 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
457 &stmt_in, NULL);
458 sqlite3_free (sql_statement);
459 if (ret != SQLITE_OK)
460 {
461 spatialite_e ("populate MetaCatalog(8) error: \"%s\"\n",
462 sqlite3_errmsg (sqlite));
463 return 0;
464 }
465
466 while (1)
467 {
468 /* scrolling the result set rows */
469 ret = sqlite3_step (stmt_in);
470 if (ret == SQLITE_DONE)
471 break; /* end of result set */
472 if (ret == SQLITE_ROW)
473 {
474 const char *colname =
475 (const char *) sqlite3_column_text (stmt_in, 2);
476 if (strcasecmp (colname, column) == 0)
477 is_unique = 1;
478 index_parts++;
479 }
480 }
481 sqlite3_finalize (stmt_in);
482
483 if (index_parts > 1)
484 {
485 /* ignoring any multi-column index */
486 is_unique = 0;
487 }
488 return is_unique;
489 }
490
491 static int
check_unique(sqlite3 * sqlite,const char * table,const char * column)492 check_unique (sqlite3 * sqlite, const char *table, const char *column)
493 {
494 /* checks if a column has any Unique constraint */
495 char *xtable;
496 char *sql_statement;
497 int ret;
498 sqlite3_stmt *stmt_in;
499 int is_unique = 0;
500
501 xtable = gaiaDoubleQuotedSql (table);
502 sql_statement = sqlite3_mprintf ("PRAGMA index_list(\"%s\")", xtable);
503 free (xtable);
504 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
505 &stmt_in, NULL);
506 sqlite3_free (sql_statement);
507 if (ret != SQLITE_OK)
508 {
509 spatialite_e ("populate MetaCatalog(7) error: \"%s\"\n",
510 sqlite3_errmsg (sqlite));
511 return 0;
512 }
513
514 while (1)
515 {
516 /* scrolling the result set rows */
517 ret = sqlite3_step (stmt_in);
518 if (ret == SQLITE_DONE)
519 break; /* end of result set */
520 if (ret == SQLITE_ROW)
521 {
522 const char *idxname =
523 (const char *) sqlite3_column_text (stmt_in, 1);
524 if (sqlite3_column_int (stmt_in, 2) == 1)
525 {
526 /* Unique Index */
527 if (check_unique_index (sqlite, idxname, column))
528 is_unique = 1;
529 }
530 }
531 }
532 sqlite3_finalize (stmt_in);
533
534 return is_unique;
535 }
536
537 static int
check_foreign_key(sqlite3 * sqlite,const char * table,const char * column)538 check_foreign_key (sqlite3 * sqlite, const char *table, const char *column)
539 {
540 /* checks if a column is part of any Foreign Key */
541 char *xtable;
542 char *sql_statement;
543 int ret;
544 sqlite3_stmt *stmt_in;
545 int is_foreign_key = 0;
546
547 xtable = gaiaDoubleQuotedSql (table);
548 sql_statement = sqlite3_mprintf ("PRAGMA foreign_key_list(\"%s\")", xtable);
549 free (xtable);
550 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
551 &stmt_in, NULL);
552 sqlite3_free (sql_statement);
553 if (ret != SQLITE_OK)
554 {
555 spatialite_e ("populate MetaCatalog(6) error: \"%s\"\n",
556 sqlite3_errmsg (sqlite));
557 return 0;
558 }
559
560 while (1)
561 {
562 /* scrolling the result set rows */
563 ret = sqlite3_step (stmt_in);
564 if (ret == SQLITE_DONE)
565 break; /* end of result set */
566 if (ret == SQLITE_ROW)
567 {
568 const char *colname =
569 (const char *) sqlite3_column_text (stmt_in, 3);
570 if (strcasecmp (colname, column) == 0)
571 is_foreign_key = 1;
572 }
573 }
574 sqlite3_finalize (stmt_in);
575
576 return is_foreign_key;
577 }
578
579 static int
table_info(sqlite3 * sqlite,sqlite3_stmt * stmt_out,const char * table)580 table_info (sqlite3 * sqlite, sqlite3_stmt * stmt_out, const char *table)
581 {
582 /* auxiliary - populating "splite_metacatalog" */
583 char *xtable;
584 char *sql_statement;
585 int ret;
586 sqlite3_stmt *stmt_in;
587
588 xtable = gaiaDoubleQuotedSql (table);
589 sql_statement = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", xtable);
590 free (xtable);
591 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
592 &stmt_in, NULL);
593 sqlite3_free (sql_statement);
594 if (ret != SQLITE_OK)
595 {
596 spatialite_e ("populate MetaCatalog(3) error: \"%s\"\n",
597 sqlite3_errmsg (sqlite));
598 return 0;
599 }
600
601 while (1)
602 {
603 /* scrolling the result set rows */
604 ret = sqlite3_step (stmt_in);
605 if (ret == SQLITE_DONE)
606 break; /* end of result set */
607 if (ret == SQLITE_ROW)
608 {
609 int is_foreign_key;
610 int is_unique;
611 sqlite3_reset (stmt_out);
612 sqlite3_clear_bindings (stmt_out);
613 sqlite3_bind_text (stmt_out, 1, table, strlen (table),
614 SQLITE_STATIC);
615 sqlite3_bind_text (stmt_out, 2,
616 (const char *) sqlite3_column_text (stmt_in,
617 1),
618 sqlite3_column_bytes (stmt_in, 1),
619 SQLITE_STATIC);
620 sqlite3_bind_text (stmt_out, 3,
621 (const char *) sqlite3_column_text (stmt_in,
622 2),
623 sqlite3_column_bytes (stmt_in, 2),
624 SQLITE_STATIC);
625 sqlite3_bind_int (stmt_out, 4, sqlite3_column_int (stmt_in, 3));
626 sqlite3_bind_int (stmt_out, 5, sqlite3_column_int (stmt_in, 5));
627 is_foreign_key =
628 check_foreign_key (sqlite, table,
629 (const char *)
630 sqlite3_column_text (stmt_in, 1));
631 sqlite3_bind_int (stmt_out, 6, is_foreign_key);
632 is_unique =
633 check_unique (sqlite, table,
634 (const char *) sqlite3_column_text (stmt_in,
635 1));
636 sqlite3_bind_int (stmt_out, 7, is_unique);
637 ret = sqlite3_step (stmt_out);
638 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
639 ;
640 else
641 {
642 spatialite_e ("populate MetaCatalog(4) error: \"%s\"\n",
643 sqlite3_errmsg (sqlite));
644 sqlite3_finalize (stmt_in);
645 return 0;
646 }
647 }
648 }
649 sqlite3_finalize (stmt_in);
650
651 return 1;
652 }
653
654 SPATIALITE_DECLARE int
gaiaCreateMetaCatalogTables(sqlite3 * sqlite)655 gaiaCreateMetaCatalogTables (sqlite3 * sqlite)
656 {
657 /* Creates both "splite_metacatalog" and "splite_metacalog_statistics" tables */
658 char *sql_statement;
659 char *err_msg = NULL;
660 int ret;
661 sqlite3_stmt *stmt_in;
662 sqlite3_stmt *stmt_out;
663
664 /* creating "splite_metacatalog" */
665 sql_statement = "CREATE TABLE splite_metacatalog (\n"
666 "table_name TEXT NOT NULL,\n"
667 "column_name TEXT NOT NULL,\n"
668 "type TEXT NOT NULL,\n"
669 "not_null INTEGER NOT NULL,\n"
670 "primary_key INTEGER NOT NULL,\n"
671 "foreign_key INTEGER NOT NULL,\n"
672 "unique_value INTEGER NOT NULL,\n"
673 "CONSTRAINT pk_splite_metacatalog PRIMARY KEY (table_name, column_name))";
674 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
675 if (ret != SQLITE_OK)
676 {
677 spatialite_e
678 ("CREATE TABLE splite_metacatalog - error: %s\n", err_msg);
679 sqlite3_free (err_msg);
680 return 0;
681 }
682
683 /* creating "splite_metacatalog_statistics" */
684 sql_statement = "CREATE TABLE splite_metacatalog_statistics (\n"
685 "table_name TEXT NOT NULL,\n"
686 "column_name TEXT NOT NULL,\n"
687 "value TEXT,\n"
688 "count INTEGER NOT NULL,\n"
689 "CONSTRAINT pk_splite_metacatalog_statistics PRIMARY KEY (table_name, column_name, value),\n"
690 "CONSTRAINT fk_splite_metacatalog_statistics FOREIGN KEY (table_name, column_name) "
691 "REFERENCES splite_metacatalog (table_name, column_name))";
692 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
693 if (ret != SQLITE_OK)
694 {
695 spatialite_e
696 ("CREATE TABLE splite_metacatalog_statistics - error: %s\n",
697 err_msg);
698 sqlite3_free (err_msg);
699 return 0;
700 }
701
702 /* populating the MetaCatalog table */
703 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
704 "AND sql NOT LIKE 'CREATE VIRTUAL TABLE%'";
705 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
706 &stmt_in, NULL);
707 if (ret != SQLITE_OK)
708 {
709 spatialite_e ("populate MetaCatalog(1) error: \"%s\"\n",
710 sqlite3_errmsg (sqlite));
711 return 0;
712 }
713
714 sql_statement = "INSERT INTO splite_metacatalog "
715 "(table_name, column_name, type, not_null, primary_key, foreign_key, unique_value) "
716 "VALUES (?, ?, ?, ?, ?, ?, ?)";
717 ret = sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
718 &stmt_out, NULL);
719 if (ret != SQLITE_OK)
720 {
721 sqlite3_finalize (stmt_in);
722 spatialite_e ("populate MetaCatalog(2) error: \"%s\"\n",
723 sqlite3_errmsg (sqlite));
724 return 0;
725 }
726
727 while (1)
728 {
729 /* scrolling the result set rows */
730 ret = sqlite3_step (stmt_in);
731 if (ret == SQLITE_DONE)
732 break; /* end of result set */
733 if (ret == SQLITE_ROW)
734 {
735 const char *table =
736 (const char *) sqlite3_column_text (stmt_in, 0);
737 if (!table_info (sqlite, stmt_out, table))
738 {
739 sqlite3_finalize (stmt_in);
740 sqlite3_finalize (stmt_out);
741 return 0;
742 }
743 }
744 }
745 sqlite3_finalize (stmt_in);
746 sqlite3_finalize (stmt_out);
747 return 1;
748 }
749
750 static int
check_raster_coverages(sqlite3 * sqlite)751 check_raster_coverages (sqlite3 * sqlite)
752 {
753 /* checking if the "raster_coverages" table already exists */
754 int exists = 0;
755 char *sql_statement;
756 char *errMsg = NULL;
757 int ret;
758 char **results;
759 int rows;
760 int columns;
761 int i;
762 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
763 "AND Upper(name) = Upper('raster_coverages')";
764 ret =
765 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
766 &errMsg);
767 if (ret != SQLITE_OK)
768 {
769 sqlite3_free (errMsg);
770 return 0;
771 }
772 for (i = 1; i <= rows; i++)
773 exists = 1;
774 sqlite3_free_table (results);
775 return exists;
776 }
777
778 static int
check_raster_coverages_srid(sqlite3 * sqlite)779 check_raster_coverages_srid (sqlite3 * sqlite)
780 {
781 /* checking if the "raster_coverages_srid" table already exists */
782 int exists = 0;
783 char *sql_statement;
784 char *errMsg = NULL;
785 int ret;
786 char **results;
787 int rows;
788 int columns;
789 int i;
790 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
791 "AND Upper(name) = Upper('raster_coverages_srid')";
792 ret =
793 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
794 &errMsg);
795 if (ret != SQLITE_OK)
796 {
797 sqlite3_free (errMsg);
798 return 0;
799 }
800 for (i = 1; i <= rows; i++)
801 exists = 1;
802 sqlite3_free_table (results);
803 return exists;
804 }
805
806 static int
check_raster_coverages_ref_sys(sqlite3 * sqlite)807 check_raster_coverages_ref_sys (sqlite3 * sqlite)
808 {
809 /* checking if the "raster_coverages_ref_sys" view already exists */
810 int exists = 0;
811 char *sql_statement;
812 char *errMsg = NULL;
813 int ret;
814 char **results;
815 int rows;
816 int columns;
817 int i;
818 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'view' "
819 "AND Upper(name) = Upper('raster_coverages_ref_sys')";
820 ret =
821 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
822 &errMsg);
823 if (ret != SQLITE_OK)
824 {
825 sqlite3_free (errMsg);
826 return 0;
827 }
828 for (i = 1; i <= rows; i++)
829 exists = 1;
830 sqlite3_free_table (results);
831 return exists;
832 }
833
834 static int
check_raster_coverages_keyword(sqlite3 * sqlite)835 check_raster_coverages_keyword (sqlite3 * sqlite)
836 {
837 /* checking if the "raster_coverages_keyword" table already exists */
838 int exists = 0;
839 char *sql_statement;
840 char *errMsg = NULL;
841 int ret;
842 char **results;
843 int rows;
844 int columns;
845 int i;
846 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
847 "AND Upper(name) = Upper('raster_coverages_keyword')";
848 ret =
849 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
850 &errMsg);
851 if (ret != SQLITE_OK)
852 {
853 sqlite3_free (errMsg);
854 return 0;
855 }
856 for (i = 1; i <= rows; i++)
857 exists = 1;
858 sqlite3_free_table (results);
859 return exists;
860 }
861
862 static void
drop_raster_coverages_triggers(sqlite3 * sqlite)863 drop_raster_coverages_triggers (sqlite3 * sqlite)
864 {
865 /* dropping all "raster_coverages" triggers */
866 char *sql;
867 int ret;
868 char *err_msg = NULL;
869 char **results;
870 int rows;
871 int columns;
872 int i;
873
874 /* checking for existing tables */
875 sql =
876 "SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name "
877 "IN ('raster_coverages', 'raster_coverages_srid', 'raster_coverages_keyword')";
878 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
879 if (ret != SQLITE_OK)
880 {
881 spatialite_e ("SQL error: %s\n", err_msg);
882 sqlite3_free (err_msg);
883 return;
884 }
885 for (i = 1; i <= rows; i++)
886 {
887 const char *name = results[(i * columns) + 0];
888 sql = sqlite3_mprintf ("DROP TRIGGER %s", name);
889 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
890 if (ret != SQLITE_OK)
891 {
892 spatialite_e ("SQL error: %s\n", err_msg);
893 sqlite3_free (err_msg);
894 return;
895 }
896 sqlite3_free (sql);
897 }
898 sqlite3_free_table (results);
899 }
900
901 static int
create_raster_coverages_triggers(sqlite3 * sqlite)902 create_raster_coverages_triggers (sqlite3 * sqlite)
903 {
904 /* creating the "raster_coverages" triggers */
905 char *sql;
906 int ret;
907 char *err_msg = NULL;
908 char **results;
909 int rows;
910 int columns;
911 int i;
912 int ok_raster_coverages = 0;
913 int ok_raster_coverages_srid = 0;
914 int ok_raster_coverages_keyword = 0;
915
916 /* checking for existing tables */
917 sql =
918 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name "
919 "IN ('raster_coverages', 'raster_coverages_srid', 'raster_coverages_keyword')";
920 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
921 if (ret != SQLITE_OK)
922 {
923 spatialite_e ("SQL error: %s\n", err_msg);
924 sqlite3_free (err_msg);
925 return 0;
926 }
927 for (i = 1; i <= rows; i++)
928 {
929 const char *name = results[(i * columns) + 0];
930 if (strcasecmp (name, "raster_coverages") == 0)
931 ok_raster_coverages = 1;
932 if (strcasecmp (name, "raster_coverages_srid") == 0)
933 ok_raster_coverages_srid = 1;
934 if (strcasecmp (name, "raster_coverages_keyword") == 0)
935 ok_raster_coverages_keyword = 1;
936 }
937 sqlite3_free_table (results);
938
939 if (ok_raster_coverages)
940 {
941 /* creating the raster_coverages triggers */
942 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_name_insert\n"
943 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
944 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
945 "coverage_name value must not contain a single quote')\n"
946 "WHERE NEW.coverage_name LIKE ('%''%');\n"
947 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
948 "coverage_name value must not contain a double quote')\n"
949 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
950 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
951 "coverage_name value must be lower case')\n"
952 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
953 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
954 if (ret != SQLITE_OK)
955 {
956 spatialite_e ("SQL error: %s\n", err_msg);
957 sqlite3_free (err_msg);
958 return 0;
959 }
960 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_name_update\n"
961 "BEFORE UPDATE OF 'coverage_name' ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
962 "SELECT RAISE(ABORT,'update on raster_coverages violates constraint: "
963 "coverage_name value must not contain a single quote')\n"
964 "WHERE NEW.coverage_name LIKE ('%''%');\n"
965 "SELECT RAISE(ABORT,'update on raster_coverages violates constraint: "
966 "coverage_name value must not contain a double quote')\n"
967 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
968 "SELECT RAISE(ABORT,'update on raster_coverages violates constraint: "
969 "coverage_name value must be lower case')\n"
970 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
971 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
972 if (ret != SQLITE_OK)
973 {
974 spatialite_e ("SQL error: %s\n", err_msg);
975 sqlite3_free (err_msg);
976 return 0;
977 }
978 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_sample_insert\n"
979 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
980 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
981 "sample_type must be one of ''1-BIT'' | ''2-BIT'' | ''4-BIT'' | "
982 "''INT8'' | ''UINT8'' | ''INT16'' | ''UINT16'' | ''INT32'' | "
983 "''UINT32'' | ''FLOAT'' | ''DOUBLE''')\n"
984 "WHERE NEW.sample_type NOT IN ('1-BIT', '2-BIT', '4-BIT', "
985 "'INT8', 'UINT8', 'INT16', 'UINT16', 'INT32', "
986 "'UINT32', 'FLOAT', 'DOUBLE');\nEND";
987 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
988 if (ret != SQLITE_OK)
989 {
990 spatialite_e ("SQL error: %s\n", err_msg);
991 sqlite3_free (err_msg);
992 return 0;
993 }
994 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_sample_update\n"
995 "BEFORE UPDATE OF 'sample_type' ON 'raster_coverages'"
996 "\nFOR EACH ROW BEGIN\n"
997 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
998 "sample_type must be one of ''1-BIT'' | ''2-BIT'' | ''4-BIT'' | "
999 "''INT8'' | ''UINT8'' | ''INT16'' | ''UINT16'' | ''INT32'' | "
1000 "''UINT32'' | ''FLOAT'' | ''DOUBLE''')\n"
1001 "WHERE NEW.sample_type NOT IN ('1-BIT', '2-BIT', '4-BIT', "
1002 "'INT8', 'UINT8', 'INT16', 'UINT16', 'INT32', "
1003 "'UINT32', 'FLOAT', 'DOUBLE');\nEND";
1004 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1005 if (ret != SQLITE_OK)
1006 {
1007 spatialite_e ("SQL error: %s\n", err_msg);
1008 sqlite3_free (err_msg);
1009 return 0;
1010 }
1011 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_pixel_insert\n"
1012 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1013 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1014 "pixel_type must be one of ''MONOCHROME'' | ''PALETTE'' | "
1015 "''GRAYSCALE'' | ''RGB'' | ''MULTIBAND'' | ''DATAGRID''')\n"
1016 "WHERE NEW.pixel_type NOT IN ('MONOCHROME', 'PALETTE', "
1017 "'GRAYSCALE', 'RGB', 'MULTIBAND', 'DATAGRID');\nEND";
1018 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1019 if (ret != SQLITE_OK)
1020 {
1021 spatialite_e ("SQL error: %s\n", err_msg);
1022 sqlite3_free (err_msg);
1023 return 0;
1024 }
1025 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_pixel_update\n"
1026 "BEFORE UPDATE OF 'pixel_type' ON 'raster_coverages'"
1027 "\nFOR EACH ROW BEGIN\n"
1028 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1029 "pixel_type must be one of ''MONOCHROME'' | ''PALETTE'' | "
1030 "''GRAYSCALE'' | ''RGB'' | ''MULTIBAND'' | ''DATAGRID''')\n"
1031 "WHERE NEW.pixel_type NOT IN ('MONOCHROME', 'PALETTE', "
1032 "'GRAYSCALE', 'RGB', 'MULTIBAND', 'DATAGRID');\nEND";
1033 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1034 if (ret != SQLITE_OK)
1035 {
1036 spatialite_e ("SQL error: %s\n", err_msg);
1037 sqlite3_free (err_msg);
1038 return 0;
1039 }
1040 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_bands_insert\n"
1041 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1042 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1043 "num_bands must be >= 1')\nWHERE NEW.num_bands < 1;\nEND";
1044 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1045 if (ret != SQLITE_OK)
1046 {
1047 spatialite_e ("SQL error: %s\n", err_msg);
1048 sqlite3_free (err_msg);
1049 return 0;
1050 }
1051 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_bands_update\n"
1052 "BEFORE UPDATE OF 'num_bands' ON 'raster_coverages'"
1053 "\nFOR EACH ROW BEGIN\n"
1054 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1055 "num_bands must be >= 1')\nWHERE NEW.num_bands < 1;\nEND";
1056 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1057 if (ret != SQLITE_OK)
1058 {
1059 spatialite_e ("SQL error: %s\n", err_msg);
1060 sqlite3_free (err_msg);
1061 return 0;
1062 }
1063 sql =
1064 "CREATE TRIGGER IF NOT EXISTS raster_coverages_compression_insert\n"
1065 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1066 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1067 "compression must be one of ''NONE'' | ''DEFLATE'' | ''DEFLATE_NO'' | "
1068 "''LZMA'' | ''LZMA_NO'' | ''LZ4'' | ''LZ4_NO'' | ''ZSTD'' | ''ZSTD_NO'' | "
1069 "''PNG'' | ''JPEG'' | ''LOSSY_WEBP'' | ''LOSSLESS_WEBP'' | ''CCITTFAX4'' | "
1070 "''LOSSY_JP2'' | ''LOSSLESS_JP2''')\n"
1071 "WHERE NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1072 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1073 "'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', 'CCITTFAX4', "
1074 "'LOSSY_JP2', 'LOSSLESS_JP2');\nEND";
1075 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1076 if (ret != SQLITE_OK)
1077 {
1078 spatialite_e ("SQL error: %s\n", err_msg);
1079 sqlite3_free (err_msg);
1080 return 0;
1081 }
1082 sql =
1083 "CREATE TRIGGER IF NOT EXISTS raster_coverages_compression_update\n"
1084 "BEFORE UPDATE OF 'compression' ON 'raster_coverages'"
1085 "\nFOR EACH ROW BEGIN\n"
1086 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1087 "compression must be one of ''NONE'' | ''DEFLATE'' | ''DEFLATE_NO'' | "
1088 "''LZMA'' | ''LZMA_NO'' | ''LZ4'' | ''LZ4_NO'' | ''ZSTD'' | ''ZSTD_NO'' | "
1089 "''PNG'' | ''JPEG'' | ''LOSSY_WEBP'' | ''LOSSLESS_WEBP'' | ''CCITTFAX4'' | "
1090 "''LOSSY_JP2'' | ''LOSSLESS_JP2''')\n"
1091 "WHERE NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1092 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1093 "'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', 'CCITTFAX4', "
1094 "'LOSSY_JP2', 'LOSSLESS_JP2');\nEND";
1095 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1096 if (ret != SQLITE_OK)
1097 {
1098 spatialite_e ("SQL error: %s\n", err_msg);
1099 sqlite3_free (err_msg);
1100 return 0;
1101 }
1102 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_quality_insert\n"
1103 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1104 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1105 "quality must be between 0 and 100')\n"
1106 "WHERE NEW.quality NOT BETWEEN 0 AND 100;\nEND";
1107 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1108 if (ret != SQLITE_OK)
1109 {
1110 spatialite_e ("SQL error: %s\n", err_msg);
1111 sqlite3_free (err_msg);
1112 return 0;
1113 }
1114 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_quality_update\n"
1115 "BEFORE UPDATE OF 'quality' ON 'raster_coverages'"
1116 "\nFOR EACH ROW BEGIN\n"
1117 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1118 "quality must be between 0 and 100')\n"
1119 "WHERE NEW.quality NOT BETWEEN 0 AND 100;\nEND";
1120 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1121 if (ret != SQLITE_OK)
1122 {
1123 spatialite_e ("SQL error: %s\n", err_msg);
1124 sqlite3_free (err_msg);
1125 return 0;
1126 }
1127 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_tilew_insert\n"
1128 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1129 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1130 "tile_width must be an exact multiple of 8 between 256 and 1024')\n"
1131 "WHERE CastToInteger(NEW.tile_width) IS NULL OR "
1132 "NEW.tile_width NOT BETWEEN 256 AND 1024 OR (NEW.tile_width % 8) <> 0;\nEND";
1133 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1134 if (ret != SQLITE_OK)
1135 {
1136 spatialite_e ("SQL error: %s\n", err_msg);
1137 sqlite3_free (err_msg);
1138 return 0;
1139 }
1140 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_tilew_update\n"
1141 "BEFORE UPDATE OF 'tile_width' ON 'raster_coverages'"
1142 "\nFOR EACH ROW BEGIN\n"
1143 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1144 "tile_width must be an exact multiple of 8 between 256 and 1024')\n"
1145 "WHERE CastToInteger(NEW.tile_width) IS NULL OR "
1146 "NEW.tile_width NOT BETWEEN 256 AND 1024 OR (NEW.tile_width % 8) <> 0;\nEND";
1147 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1148 if (ret != SQLITE_OK)
1149 {
1150 spatialite_e ("SQL error: %s\n", err_msg);
1151 sqlite3_free (err_msg);
1152 return 0;
1153 }
1154 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_tileh_insert\n"
1155 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1156 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1157 "tile_height must be an exact multiple of 8 between 256 and 1024')\n"
1158 "WHERE CastToInteger(NEW.tile_height) IS NULL OR "
1159 "NEW.tile_height NOT BETWEEN 256 AND 1024 OR (NEW.tile_height % 8) <> 0;\nEND";
1160 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1161 if (ret != SQLITE_OK)
1162 {
1163 spatialite_e ("SQL error: %s\n", err_msg);
1164 sqlite3_free (err_msg);
1165 return 0;
1166 }
1167 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_tileh_update\n"
1168 "BEFORE UPDATE OF 'tile_height' ON 'raster_coverages'"
1169 "\nFOR EACH ROW BEGIN\n"
1170 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1171 "tile_height must be an exact multiple of 8 between 256 and 1024')\n"
1172 "WHERE CastToInteger(NEW.tile_height) IS NULL OR "
1173 "NEW.tile_height NOT BETWEEN 256 AND 1024 OR (NEW.tile_height % 8) <> 0;\nEND";
1174 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1175 if (ret != SQLITE_OK)
1176 {
1177 spatialite_e ("SQL error: %s\n", err_msg);
1178 sqlite3_free (err_msg);
1179 return 0;
1180 }
1181 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_horzres_insert\n"
1182 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1183 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1184 "horz_resolution must be positive')\n"
1185 "WHERE NEW.horz_resolution IS NOT NULL AND "
1186 "(NEW.horz_resolution <= 0.0 OR CastToDouble(NEW.horz_resolution) IS NULL);\nEND";
1187 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1188 if (ret != SQLITE_OK)
1189 {
1190 spatialite_e ("SQL error: %s\n", err_msg);
1191 sqlite3_free (err_msg);
1192 return 0;
1193 }
1194 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_horzres_update\n"
1195 "BEFORE UPDATE OF 'horz_resolution' ON 'raster_coverages'"
1196 "\nFOR EACH ROW BEGIN\n"
1197 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1198 "horz_resolution must be positive')\n"
1199 "WHERE NEW.horz_resolution IS NOT NULL AND "
1200 "(NEW.horz_resolution <= 0.0 OR CastToDouble(NEW.horz_resolution) IS NULL);\nEND";
1201 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1202 if (ret != SQLITE_OK)
1203 {
1204 spatialite_e ("SQL error: %s\n", err_msg);
1205 sqlite3_free (err_msg);
1206 return 0;
1207 }
1208 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_vertres_insert\n"
1209 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1210 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1211 "vert_resolution must be positive')\n"
1212 "WHERE NEW.vert_resolution IS NOT NULL AND "
1213 "(NEW.vert_resolution <= 0.0 OR CastToDouble(NEW.vert_resolution) IS NULL);\nEND";
1214 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1215 if (ret != SQLITE_OK)
1216 {
1217 spatialite_e ("SQL error: %s\n", err_msg);
1218 sqlite3_free (err_msg);
1219 return 0;
1220 }
1221 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_vertres_update\n"
1222 "BEFORE UPDATE OF 'vert_resolution' ON 'raster_coverages'"
1223 "\nFOR EACH ROW BEGIN\n"
1224 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1225 "vert_resolution must be positive')\n"
1226 "WHERE NEW.vert_resolution IS NOT NULL AND "
1227 "(NEW.vert_resolution <= 0.0 OR CastToDouble(NEW.vert_resolution) IS NULL);\nEND";
1228 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1229 if (ret != SQLITE_OK)
1230 {
1231 spatialite_e ("SQL error: %s\n", err_msg);
1232 sqlite3_free (err_msg);
1233 return 0;
1234 }
1235 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_nodata_insert\n"
1236 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1237 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1238 "invalid nodata_pixel')\nWHERE NEW.nodata_pixel IS NOT NULL AND "
1239 "IsValidPixel(NEW.nodata_pixel, NEW.sample_type, NEW.num_bands) <> 1;\nEND";
1240 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1241 if (ret != SQLITE_OK)
1242 {
1243 spatialite_e ("SQL error: %s\n", err_msg);
1244 sqlite3_free (err_msg);
1245 return 0;
1246 }
1247 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_nodata_update\n"
1248 "BEFORE UPDATE OF 'nodata_pixel' ON 'raster_coverages'"
1249 "\nFOR EACH ROW BEGIN\n"
1250 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1251 "invalid nodata_pixel')\nWHERE NEW.nodata_pixel IS NOT NULL AND "
1252 "IsValidPixel(NEW.nodata_pixel, NEW.sample_type, NEW.num_bands) <> 1;\nEND";
1253 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1254 if (ret != SQLITE_OK)
1255 {
1256 spatialite_e ("SQL error: %s\n", err_msg);
1257 sqlite3_free (err_msg);
1258 return 0;
1259 }
1260 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_palette_insert\n"
1261 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1262 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1263 "invalid palette')\nWHERE NEW.palette IS NOT NULL AND "
1264 "(NEW.pixel_type <> 'PALETTE' OR NEW.num_bands <> 1 OR "
1265 "IsValidRasterPalette(NEW.palette, NEW.sample_type) <> 1);\nEND";
1266 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1267 if (ret != SQLITE_OK)
1268 {
1269 spatialite_e ("SQL error: %s\n", err_msg);
1270 sqlite3_free (err_msg);
1271 return 0;
1272 }
1273 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_palette_update\n"
1274 "BEFORE UPDATE OF 'palette' ON 'raster_coverages'"
1275 "\nFOR EACH ROW BEGIN\n"
1276 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1277 "invalid palette')\nWHERE NEW.palette IS NOT NULL AND "
1278 "(NEW.pixel_type <> 'PALETTE' OR NEW.num_bands <> 1 OR "
1279 "IsValidRasterPalette(NEW.palette, NEW.sample_type) <> 1);\nEND";
1280 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1281 if (ret != SQLITE_OK)
1282 {
1283 spatialite_e ("SQL error: %s\n", err_msg);
1284 sqlite3_free (err_msg);
1285 return 0;
1286 }
1287 sql =
1288 "CREATE TRIGGER IF NOT EXISTS raster_coverages_statistics_insert\n"
1289 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1290 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1291 "invalid statistics')\nWHERE NEW.statistics IS NOT NULL AND "
1292 "IsValidRasterStatistics(NEW.statistics, NEW.sample_type, NEW.num_bands) <> 1;\nEND";
1293 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1294 if (ret != SQLITE_OK)
1295 {
1296 spatialite_e ("SQL error: %s\n", err_msg);
1297 sqlite3_free (err_msg);
1298 return 0;
1299 }
1300 sql =
1301 "CREATE TRIGGER IF NOT EXISTS raster_coverages_statistics_update\n"
1302 "BEFORE UPDATE OF 'statistics' ON 'raster_coverages'"
1303 "\nFOR EACH ROW BEGIN\n"
1304 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1305 "invalid statistics')\nWHERE NEW.statistics IS NOT NULL AND "
1306 "IsValidRasterStatistics(NEW.statistics, NEW.sample_type, NEW.num_bands) <> 1;\nEND";
1307 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1308 if (ret != SQLITE_OK)
1309 {
1310 spatialite_e ("SQL error: %s\n", err_msg);
1311 sqlite3_free (err_msg);
1312 return 0;
1313 }
1314 sql =
1315 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monosample_insert\n"
1316 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1317 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1318 "inconsistent MONOCHROME sample_type')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1319 "AND NEW.sample_type <> '1-BIT';\nEND";
1320 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1321 if (ret != SQLITE_OK)
1322 {
1323 spatialite_e ("SQL error: %s\n", err_msg);
1324 sqlite3_free (err_msg);
1325 return 0;
1326 }
1327 sql =
1328 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monosample_update\n"
1329 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1330 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1331 "inconsistent MONOCHROME sample_type')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1332 "AND NEW.sample_type <>'1-BIT';\nEND";
1333 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1334 if (ret != SQLITE_OK)
1335 {
1336 spatialite_e ("SQL error: %s\n", err_msg);
1337 sqlite3_free (err_msg);
1338 return 0;
1339 }
1340 sql =
1341 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monocompr_insert\n"
1342 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1343 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1344 "inconsistent MONOCHROME compression')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1345 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1346 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1347 "'CCITTFAX4');\nEND";
1348 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1349 if (ret != SQLITE_OK)
1350 {
1351 spatialite_e ("SQL error: %s\n", err_msg);
1352 sqlite3_free (err_msg);
1353 return 0;
1354 }
1355 sql =
1356 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monocompr_update\n"
1357 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1358 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1359 "inconsistent MONOCHROME compression')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1360 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1361 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', 'CCITTFAX4');\nEND";
1362 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1363 if (ret != SQLITE_OK)
1364 {
1365 spatialite_e ("SQL error: %s\n", err_msg);
1366 sqlite3_free (err_msg);
1367 return 0;
1368 }
1369 sql =
1370 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monobands_insert\n"
1371 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1372 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1373 "inconsistent MONOCHROME num_bands')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1374 "AND NEW.num_bands <> 1;\nEND";
1375 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1376 if (ret != SQLITE_OK)
1377 {
1378 spatialite_e ("SQL error: %s\n", err_msg);
1379 sqlite3_free (err_msg);
1380 return 0;
1381 }
1382 sql =
1383 "CREATE TRIGGER IF NOT EXISTS raster_coverages_monobands_update\n"
1384 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1385 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1386 "inconsistent MONOCHROME num_bands')\nWHERE NEW.pixel_type = 'MONOCHROME' "
1387 "AND NEW.num_bands <> 1;\nEND";
1388 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1389 if (ret != SQLITE_OK)
1390 {
1391 spatialite_e ("SQL error: %s\n", err_msg);
1392 sqlite3_free (err_msg);
1393 return 0;
1394 }
1395 sql =
1396 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltsample_insert\n"
1397 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1398 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1399 "inconsistent PALETTE sample_type')\nWHERE NEW.pixel_type = 'PALETTE' "
1400 "AND NEW.sample_type NOT IN ('1-BIT', '2-BIT', '4-BIT', 'UINT8');\nEND";
1401 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1402 if (ret != SQLITE_OK)
1403 {
1404 spatialite_e ("SQL error: %s\n", err_msg);
1405 sqlite3_free (err_msg);
1406 return 0;
1407 }
1408 sql =
1409 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltsample_update\n"
1410 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1411 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1412 "inconsistent PALETTE sample_type')\nWHERE NEW.pixel_type = 'PALETTE' "
1413 "AND NEW.sample_type NOT IN ('1-BIT', '2-BIT', '4-BIT', 'UINT8');\nEND";
1414 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1415 if (ret != SQLITE_OK)
1416 {
1417 spatialite_e ("SQL error: %s\n", err_msg);
1418 sqlite3_free (err_msg);
1419 return 0;
1420 }
1421 sql =
1422 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltcompr_insert\n"
1423 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1424 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1425 "inconsistent PALETTE compression')\nWHERE NEW.pixel_type = 'PALETTE' "
1426 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1427 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG');\nEND";
1428 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1429 if (ret != SQLITE_OK)
1430 {
1431 spatialite_e ("SQL error: %s\n", err_msg);
1432 sqlite3_free (err_msg);
1433 return 0;
1434 }
1435 sql =
1436 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltcompr_update\n"
1437 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1438 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1439 "inconsistent PALETTE compression')\nWHERE NEW.pixel_type = 'PALETTE' "
1440 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1441 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG');\nEND";
1442 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1443 if (ret != SQLITE_OK)
1444 {
1445 spatialite_e ("SQL error: %s\n", err_msg);
1446 sqlite3_free (err_msg);
1447 return 0;
1448 }
1449 sql =
1450 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltbands_insert\n"
1451 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1452 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1453 "inconsistent PALETTE num_bands')\nWHERE NEW.pixel_type = 'PALETTE' "
1454 "AND NEW.num_bands <> 1;\nEND";
1455 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1456 if (ret != SQLITE_OK)
1457 {
1458 spatialite_e ("SQL error: %s\n", err_msg);
1459 sqlite3_free (err_msg);
1460 return 0;
1461 }
1462 sql =
1463 "CREATE TRIGGER IF NOT EXISTS raster_coverages_pltbands_update\n"
1464 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1465 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1466 "inconsistent PALETTE num_bands')\nWHERE NEW.pixel_type = 'PALETTE' "
1467 "AND NEW.num_bands <> 1;\nEND";
1468 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1469 if (ret != SQLITE_OK)
1470 {
1471 spatialite_e ("SQL error: %s\n", err_msg);
1472 sqlite3_free (err_msg);
1473 return 0;
1474 }
1475 sql =
1476 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graysample_insert\n"
1477 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1478 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1479 "inconsistent GRAYSCALE sample_type')\nWHERE NEW.pixel_type = 'GRAYSCALE' "
1480 "AND NEW.sample_type NOT IN ('2-BIT', '4-BIT', 'UINT8');\nEND";
1481 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1482 if (ret != SQLITE_OK)
1483 {
1484 spatialite_e ("SQL error: %s\n", err_msg);
1485 sqlite3_free (err_msg);
1486 return 0;
1487 }
1488 sql =
1489 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graysample_update\n"
1490 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1491 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1492 "inconsistent GRAYSCALE sample_type')\nWHERE NEW.pixel_type = 'GRAYSCALE' "
1493 "AND NEW.sample_type NOT IN ('2-BIT', '4-BIT', 'UINT8');\nEND";
1494 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1495 if (ret != SQLITE_OK)
1496 {
1497 spatialite_e ("SQL error: %s\n", err_msg);
1498 sqlite3_free (err_msg);
1499 return 0;
1500 }
1501 sql =
1502 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graybands_insert\n"
1503 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1504 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1505 "inconsistent GRAYSCALE num_bands')\nWHERE NEW.pixel_type = 'GRAYSCALE' "
1506 "AND NEW.num_bands <> 1;\nEND";
1507 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1508 if (ret != SQLITE_OK)
1509 {
1510 spatialite_e ("SQL error: %s\n", err_msg);
1511 sqlite3_free (err_msg);
1512 return 0;
1513 }
1514 sql =
1515 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graybands_update\n"
1516 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1517 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1518 "inconsistent GRAYSCALE num_bands')\nWHERE NEW.pixel_type = 'GRAYSCALE' "
1519 "AND NEW.num_bands <> 1;\nEND";
1520 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1521 if (ret != SQLITE_OK)
1522 {
1523 spatialite_e ("SQL error: %s\n", err_msg);
1524 sqlite3_free (err_msg);
1525 return 0;
1526 }
1527 sql =
1528 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graycompr_insert\n"
1529 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1530 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1531 "inconsistent GRAYSCALE compression')\nWHERE NEW.pixel_type = "
1532 "'GRAYSCALE' AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1533 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1534 "'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', 'LOSSY_JP2', "
1535 "'LOSSLESS_JP2');\nEND";
1536 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1537 if (ret != SQLITE_OK)
1538 {
1539 spatialite_e ("SQL error: %s\n", err_msg);
1540 sqlite3_free (err_msg);
1541 return 0;
1542 }
1543 sql =
1544 "CREATE TRIGGER IF NOT EXISTS raster_coverages_graycompr_update\n"
1545 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1546 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1547 "inconsistent GRAYSCALE compression')\nWHERE NEW.pixel_type = "
1548 "'GRAYSCALE' AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1549 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1550 "'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', 'LOSSY_JP2', "
1551 "'LOSSLESS_JP2');\nEND";
1552 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1553 if (ret != SQLITE_OK)
1554 {
1555 spatialite_e ("SQL error: %s\n", err_msg);
1556 sqlite3_free (err_msg);
1557 return 0;
1558 }
1559 sql =
1560 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbsample_insert\n"
1561 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1562 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1563 "inconsistent RGB sample_type')\nWHERE NEW.pixel_type = 'RGB' "
1564 "AND NEW.sample_type NOT IN ('UINT8', 'UINT16');\nEND";
1565 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1566 if (ret != SQLITE_OK)
1567 {
1568 spatialite_e ("SQL error: %s\n", err_msg);
1569 sqlite3_free (err_msg);
1570 return 0;
1571 }
1572 sql =
1573 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbsample_update\n"
1574 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1575 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1576 "inconsistent RGB sample_type')\nWHERE NEW.pixel_type = 'RGB' "
1577 "AND NEW.sample_type NOT IN ('UINT8', 'UINT16');\nEND";
1578 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1579 if (ret != SQLITE_OK)
1580 {
1581 spatialite_e ("SQL error: %s\n", err_msg);
1582 sqlite3_free (err_msg);
1583 return 0;
1584 }
1585 sql =
1586 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbcompr_insert\n"
1587 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1588 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1589 "inconsistent RGB compression')\nWHERE NEW.pixel_type = 'RGB' "
1590 "AND ((NEW.sample_type = 'UINT8' AND NEW.compression NOT IN ("
1591 "'NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', "
1592 "'ZSTD', 'ZSTD_NO', 'PNG', 'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', "
1593 "'LOSSY_JP2', 'LOSSLESS_JP2') OR (NEW.sample_type = "
1594 "'UINT16' AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1595 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1596 "'LOSSY_JP2', 'LOSSLESS_JP2'))));\nEND";
1597 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1598 if (ret != SQLITE_OK)
1599 {
1600 spatialite_e ("SQL error: %s\n", err_msg);
1601 sqlite3_free (err_msg);
1602 return 0;
1603 }
1604 sql =
1605 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbcompr_update\n"
1606 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1607 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1608 "inconsistent RGB compression')\nWHERE NEW.pixel_type = 'RGB' "
1609 "AND ((NEW.sample_type = 'UINT8' AND NEW.compression NOT IN ("
1610 "'NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', "
1611 "'ZSTD', 'ZSTD_NO', 'PNG', 'JPEG', 'LOSSY_WEBP', 'LOSSLESS_WEBP', "
1612 "'LOSSY_JP2', 'LOSSLESS_JP2') OR (NEW.sample_type = "
1613 "'UINT16' AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', "
1614 "'LZMA', 'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1615 "'LOSSY_JP2', 'LOSSLESS_JP2'))));\nEND";
1616 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1617 if (ret != SQLITE_OK)
1618 {
1619 spatialite_e ("SQL error: %s\n", err_msg);
1620 sqlite3_free (err_msg);
1621 return 0;
1622 }
1623 sql =
1624 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbbands_insert\n"
1625 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1626 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1627 "inconsistent RGB num_bands')\nWHERE NEW.pixel_type = 'RGB' "
1628 "AND NEW.num_bands <> 3;\nEND";
1629 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1630 if (ret != SQLITE_OK)
1631 {
1632 spatialite_e ("SQL error: %s\n", err_msg);
1633 sqlite3_free (err_msg);
1634 return 0;
1635 }
1636 sql =
1637 "CREATE TRIGGER IF NOT EXISTS raster_coverages_rgbbands_update\n"
1638 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1639 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1640 "inconsistent RGB num_bands')\nWHERE NEW.pixel_type = 'RGB' "
1641 "AND NEW.num_bands <> 3;\nEND";
1642 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1643 if (ret != SQLITE_OK)
1644 {
1645 spatialite_e ("SQL error: %s\n", err_msg);
1646 sqlite3_free (err_msg);
1647 return 0;
1648 }
1649 sql =
1650 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multisample_insert\n"
1651 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1652 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1653 "inconsistent MULTIBAND sample_type')\nWHERE NEW.pixel_type = 'MULTIBAND' "
1654 "AND NEW.sample_type NOT IN ('UINT8', 'UINT16');\nEND";
1655 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1656 if (ret != SQLITE_OK)
1657 {
1658 spatialite_e ("SQL error: %s\n", err_msg);
1659 sqlite3_free (err_msg);
1660 return 0;
1661 }
1662 sql =
1663 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multisample_update\n"
1664 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1665 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1666 "inconsistent MULTIBAND sample_type')\nWHERE NEW.pixel_type = 'MULTIBAND' "
1667 "AND NEW.sample_type NOT IN ('UINT8', 'UINT16');\nEND";
1668 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1669 if (ret != SQLITE_OK)
1670 {
1671 spatialite_e ("SQL error: %s\n", err_msg);
1672 sqlite3_free (err_msg);
1673 return 0;
1674 }
1675 sql =
1676 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multicompr_insert\n"
1677 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1678 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1679 "inconsistent MULTIBAND compression')\nWHERE NEW.pixel_type = "
1680 "'MULTIBAND' AND ((NEW.num_bands NOT IN (3, 4) AND "
1681 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1682 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO')) OR "
1683 "(NEW.sample_type <> 'UINT16' AND NEW.num_bands IN (3, 4) AND "
1684 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1685 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1686 "'LOSSY_WEBP', 'LOSSLESS_WEBP', 'LOSSY_JP2', 'LOSSLESS_JP2')) OR "
1687 "(NEW.sample_type = 'UINT16' AND NEW.num_bands IN (3, 4) AND "
1688 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1689 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1690 "'LOSSY_JP2', 'LOSSLESS_JP2')));\nEND";
1691 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1692 if (ret != SQLITE_OK)
1693 {
1694 spatialite_e ("SQL error: %s\n", err_msg);
1695 sqlite3_free (err_msg);
1696 return 0;
1697 }
1698 sql =
1699 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multicompr_update\n"
1700 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1701 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1702 "inconsistent MULTIBAND compression')\nWHERE NEW.pixel_type = "
1703 "'MULTIBAND' AND ((NEW.num_bands NOT IN (3, 4) AND "
1704 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1705 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO')) OR "
1706 "(NEW.sample_type <> 'UINT16' AND NEW.num_bands IN (3, 4) AND "
1707 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1708 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1709 "'LOSSY_WEBP', 'LOSSLESS_WEBP', 'LOSSY_JP2', 'LOSSLESS_JP2')) OR "
1710 "(NEW.sample_type = 'UINT16' AND NEW.num_bands IN (3, 4) AND "
1711 "NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1712 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1713 "'LOSSY_JP2', 'LOSSLESS_JP2')));\nEND";
1714 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1715 if (ret != SQLITE_OK)
1716 {
1717 spatialite_e ("SQL error: %s\n", err_msg);
1718 sqlite3_free (err_msg);
1719 return 0;
1720 }
1721 sql =
1722 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multibands_insert\n"
1723 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1724 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1725 "inconsistent MULTIBAND num_bands')\nWHERE NEW.pixel_type = 'MULTIBAND' "
1726 "AND NEW.num_bands < 2;\nEND";
1727 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1728 if (ret != SQLITE_OK)
1729 {
1730 spatialite_e ("SQL error: %s\n", err_msg);
1731 sqlite3_free (err_msg);
1732 return 0;
1733 }
1734 sql =
1735 "CREATE TRIGGER IF NOT EXISTS raster_coverages_multibands_update\n"
1736 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1737 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1738 "inconsistent MULTIBAND num_bands')\nWHERE NEW.pixel_type = 'MULTIBAND' "
1739 "AND NEW.num_bands < 2;\nEND";
1740 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1741 if (ret != SQLITE_OK)
1742 {
1743 spatialite_e ("SQL error: %s\n", err_msg);
1744 sqlite3_free (err_msg);
1745 return 0;
1746 }
1747 sql =
1748 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridsample_insert\n"
1749 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1750 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1751 "inconsistent DATAGRID sample_type')\nWHERE NEW.pixel_type = 'DATAGRID' "
1752 "AND NEW.sample_type NOT IN ('INT8', 'UINT8', 'INT16', 'UINT16', "
1753 "'INT32', 'UINT32', 'FLOAT', 'DOUBLE');\nEND";
1754 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1755 if (ret != SQLITE_OK)
1756 {
1757 spatialite_e ("SQL error: %s\n", err_msg);
1758 sqlite3_free (err_msg);
1759 return 0;
1760 }
1761 sql =
1762 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridsample_update\n"
1763 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1764 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1765 "inconsistent DATAGRID sample_type')\nWHERE NEW.pixel_type = 'DATAGRID' "
1766 "AND NEW.sample_type NOT IN ('INT8', 'UINT8', 'INT16', 'UINT16', "
1767 "'INT32', 'UINT32', 'FLOAT', 'DOUBLE');\nEND";
1768 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1769 if (ret != SQLITE_OK)
1770 {
1771 spatialite_e ("SQL error: %s\n", err_msg);
1772 sqlite3_free (err_msg);
1773 return 0;
1774 }
1775 sql =
1776 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridcompr_insert\n"
1777 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1778 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1779 "inconsistent DATAGRID compression')\nWHERE NEW.pixel_type = 'DATAGRID' "
1780 "AND (((NEW.sample_type NOT IN ('UINT8', 'UINT16')) AND NEW.compression "
1781 "NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', 'LZMA_NO', 'LZ4', "
1782 "'LZ4_NO', 'ZSTD', 'ZSTD_NO')) OR ((NEW.sample_type IN ('UINT8', 'UINT16')) "
1783 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1784 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1785 "'LOSSY_JP2', 'LOSSLESS_JP2')));\nEND";
1786 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1787 if (ret != SQLITE_OK)
1788 {
1789 spatialite_e ("SQL error: %s\n", err_msg);
1790 sqlite3_free (err_msg);
1791 return 0;
1792 }
1793 sql =
1794 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridcompr_update\n"
1795 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1796 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1797 "inconsistent DATAGRID compression')\nWHERE NEW.pixel_type = 'DATAGRID' "
1798 "AND (((NEW.sample_type NOT IN ('UINT8', 'UINT16')) AND NEW.compression "
1799 "NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', 'LZMA_NO', 'LZ4', "
1800 "'LZ4_NO', 'ZSTD', 'ZSTD_NO')) OR ((NEW.sample_type IN ('UINT8', 'UINT16')) "
1801 "AND NEW.compression NOT IN ('NONE', 'DEFLATE', 'DEFLATE_NO', 'LZMA', "
1802 "'LZMA_NO', 'LZ4', 'LZ4_NO', 'ZSTD', 'ZSTD_NO', 'PNG', "
1803 "'LOSSY_JP2', 'LOSSLESS_JP2')));\nEND";
1804 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1805 if (ret != SQLITE_OK)
1806 {
1807 spatialite_e ("SQL error: %s\n", err_msg);
1808 sqlite3_free (err_msg);
1809 return 0;
1810 }
1811 sql =
1812 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridbands_insert\n"
1813 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1814 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1815 "inconsistent DATAGRID num_bands')\nWHERE NEW.pixel_type = 'DATAGRID' "
1816 "AND NEW.num_bands <> 1;\nEND";
1817 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1818 if (ret != SQLITE_OK)
1819 {
1820 spatialite_e ("SQL error: %s\n", err_msg);
1821 sqlite3_free (err_msg);
1822 return 0;
1823 }
1824 sql =
1825 "CREATE TRIGGER IF NOT EXISTS raster_coverages_gridbands_update\n"
1826 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1827 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1828 "inconsistent DATAGRID num_bands')\nWHERE NEW.pixel_type = 'DATAGRID' "
1829 "AND NEW.num_bands <> 1;\nEND";
1830 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1831 if (ret != SQLITE_OK)
1832 {
1833 spatialite_e ("SQL error: %s\n", err_msg);
1834 sqlite3_free (err_msg);
1835 return 0;
1836 }
1837 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_georef_insert\n"
1838 "BEFORE INSERT ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1839 "SELECT RAISE(ABORT,'insert on raster_coverages violates constraint: "
1840 "inconsistent georeferencing infos')\n"
1841 "WHERE NOT ((NEW.horz_resolution IS NULL AND NEW.vert_resolution IS NULL "
1842 "AND NEW.srid IS NULL) OR (NEW.horz_resolution IS NOT NULL "
1843 "AND NEW.vert_resolution IS NOT NULL AND NEW.srid IS NOT NULL));\nEND";
1844 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1845 if (ret != SQLITE_OK)
1846 {
1847 spatialite_e ("SQL error: %s\n", err_msg);
1848 sqlite3_free (err_msg);
1849 return 0;
1850 }
1851 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_georef_update\n"
1852 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1853 "SELECT RAISE(ABORT,'update on raster_coverages violates constraint: "
1854 "inconsistent georeferencing infos')\n"
1855 "WHERE NOT ((NEW.horz_resolution IS NULL AND NEW.vert_resolution IS NULL "
1856 "AND NEW.srid IS NULL) OR (NEW.horz_resolution IS NOT NULL "
1857 "AND NEW.vert_resolution IS NOT NULL AND NEW.srid IS NOT NULL));\nEND";
1858 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1859 if (ret != SQLITE_OK)
1860 {
1861 spatialite_e ("SQL error: %s\n", err_msg);
1862 sqlite3_free (err_msg);
1863 return 0;
1864 }
1865 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_update\n"
1866 "BEFORE UPDATE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1867 "SELECT RAISE(ABORT, 'update on raster_coverages violates constraint: "
1868 "attempting to change the definition of an already populated Coverage')\n"
1869 "WHERE IsPopulatedCoverage(NULL, OLD.coverage_name) = 1 AND "
1870 "((OLD.sample_type <> NEW.sample_type) AND (OLD.pixel_type <> NEW.sample_type) "
1871 "OR (OLD.num_bands <> NEW.num_bands) OR (OLD.compression <> NEW.compression) "
1872 "OR (OLD.quality <> NEW.quality) OR (OLD.tile_width <> NEW.tile_width) "
1873 "OR (OLD.tile_height <> NEW.tile_height) OR (OLD.horz_resolution <> NEW.horz_resolution) "
1874 "OR (OLD.vert_resolution <> NEW.vert_resolution) OR "
1875 "(OLD.srid <> NEW.srid) OR (OLD.nodata_pixel <> NEW.nodata_pixel));\nEND";
1876 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1877 if (ret != SQLITE_OK)
1878 {
1879 spatialite_e ("SQL error: %s\n", err_msg);
1880 sqlite3_free (err_msg);
1881 return 0;
1882 }
1883 sql = "CREATE TRIGGER IF NOT EXISTS raster_coverages_delete\n"
1884 "BEFORE DELETE ON 'raster_coverages'\nFOR EACH ROW BEGIN\n"
1885 "SELECT RAISE(ABORT, 'delete on raster_coverages violates constraint: "
1886 "attempting to delete the definition of an already populated Coverage')\n"
1887 "WHERE IsPopulatedCoverage(NULL, OLD.coverage_name) = 1;\nEND";
1888 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1889 if (ret != SQLITE_OK)
1890 {
1891 spatialite_e ("SQL error: %s\n", err_msg);
1892 sqlite3_free (err_msg);
1893 return 0;
1894 }
1895 }
1896
1897 if (ok_raster_coverages_srid)
1898 {
1899 /* creating the raster_coverages_srid triggers */
1900 sql =
1901 "CREATE TRIGGER IF NOT EXISTS raster_coverages_srid_name_insert\n"
1902 "BEFORE INSERT ON 'raster_coverages_srid'\nFOR EACH ROW BEGIN\n"
1903 "SELECT RAISE(ABORT,'insert on raster_coverages_srid violates constraint: "
1904 "coverage_name value must not contain a single quote')\n"
1905 "WHERE NEW.coverage_name LIKE ('%''%');\n"
1906 "SELECT RAISE(ABORT,'insert on raster_coverages_srid violates constraint: "
1907 "coverage_name value must not contain a double quote')\n"
1908 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
1909 "SELECT RAISE(ABORT,'insert on raster_coverages_srid violates constraint: "
1910 "coverage_name value must be lower case')\n"
1911 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
1912 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1913 if (ret != SQLITE_OK)
1914 {
1915 spatialite_e ("SQL error: %s\n", err_msg);
1916 sqlite3_free (err_msg);
1917 return 0;
1918 }
1919 sql =
1920 "CREATE TRIGGER IF NOT EXISTS raster_coverages_srid_name_update\n"
1921 "BEFORE UPDATE OF 'coverage_name' ON 'raster_coverages_srid'\nFOR EACH ROW BEGIN\n"
1922 "SELECT RAISE(ABORT,'update on raster_coverages_srid violates constraint: "
1923 "coverage_name value must not contain a single quote')\n"
1924 "WHERE NEW.coverage_name LIKE ('%''%');\n"
1925 "SELECT RAISE(ABORT,'update on raster_coverages_srid violates constraint: "
1926 "coverage_name value must not contain a double quote')\n"
1927 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
1928 "SELECT RAISE(ABORT,'update on raster_coverages_srid violates constraint: "
1929 "coverage_name value must be lower case')\n"
1930 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
1931 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1932 if (ret != SQLITE_OK)
1933 {
1934 spatialite_e ("SQL error: %s\n", err_msg);
1935 sqlite3_free (err_msg);
1936 return 0;
1937 }
1938 }
1939
1940 if (ok_raster_coverages_keyword)
1941 {
1942 /* creating the raster_coverages_keyword triggers */
1943 sql =
1944 "CREATE TRIGGER IF NOT EXISTS raster_coverages_keyword_name_insert\n"
1945 "BEFORE INSERT ON 'raster_coverages_keyword'\nFOR EACH ROW BEGIN\n"
1946 "SELECT RAISE(ABORT,'insert on raster_coverages_keyword violates constraint: "
1947 "coverage_name value must not contain a single quote')\n"
1948 "WHERE NEW.coverage_name LIKE ('%''%');\n"
1949 "SELECT RAISE(ABORT,'insert on raster_coverages_keyword violates constraint: "
1950 "coverage_name value must not contain a double quote')\n"
1951 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
1952 "SELECT RAISE(ABORT,'insert on raster_coverages_keyword violates constraint: "
1953 "coverage_name value must be lower case')\n"
1954 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
1955 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1956 if (ret != SQLITE_OK)
1957 {
1958 spatialite_e ("SQL error: %s\n", err_msg);
1959 sqlite3_free (err_msg);
1960 return 0;
1961 }
1962 sql =
1963 "CREATE TRIGGER IF NOT EXISTS raster_coverages_keyword_name_update\n"
1964 "BEFORE UPDATE OF 'coverage_name' ON 'raster_coverages_keyword'\nFOR EACH ROW BEGIN\n"
1965 "SELECT RAISE(ABORT,'update on raster_coverages_keyword violates constraint: "
1966 "coverage_name value must not contain a single quote')\n"
1967 "WHERE NEW.coverage_name LIKE ('%''%');\n"
1968 "SELECT RAISE(ABORT,'update on raster_coverages_keyword violates constraint: "
1969 "coverage_name value must not contain a double quote')\n"
1970 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
1971 "SELECT RAISE(ABORT,'update on raster_coverages_keyword violates constraint: "
1972 "coverage_name value must be lower case')\n"
1973 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
1974 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
1975 if (ret != SQLITE_OK)
1976 {
1977 spatialite_e ("SQL error: %s\n", err_msg);
1978 sqlite3_free (err_msg);
1979 return 0;
1980 }
1981 }
1982 return 1;
1983 }
1984
1985 SPATIALITE_PRIVATE int
create_raster_coverages(sqlite3 * sqlite)1986 create_raster_coverages (sqlite3 * sqlite)
1987 {
1988 /* creating the "raster_coverages" table */
1989 char *sql;
1990 int ret;
1991 char *err_msg = NULL;
1992 sql = "CREATE TABLE IF NOT EXISTS raster_coverages (\n"
1993 "coverage_name TEXT NOT NULL PRIMARY KEY,\n"
1994 "title TEXT NOT NULL DEFAULT '*** missing Title ***',\n"
1995 "abstract TEXT NOT NULL DEFAULT '*** missing Abstract ***',\n"
1996 "sample_type TEXT NOT NULL DEFAULT '*** undefined ***',\n"
1997 "pixel_type TEXT NOT NULL DEFAULT '*** undefined ***',\n"
1998 "num_bands INTEGER NOT NULL DEFAULT 1,\n"
1999 "compression TEXT NOT NULL DEFAULT 'NONE',\n"
2000 "quality INTEGER NOT NULL DEFAULT 100,\n"
2001 "tile_width INTEGER NOT NULL DEFAULT 512,\n"
2002 "tile_height INTEGER NOT NULL DEFAULT 512,\n"
2003 "horz_resolution DOUBLE NOT NULL,\n"
2004 "vert_resolution DOUBLE NOT NULL,\n"
2005 "srid INTEGER NOT NULL,\n"
2006 "nodata_pixel BLOB NOT NULL,\n"
2007 "palette BLOB,\n"
2008 "statistics BLOB,\n"
2009 "geo_minx DOUBLE,\n"
2010 "geo_miny DOUBLE,\n"
2011 "geo_maxx DOUBLE,\n"
2012 "geo_maxy DOUBLE,\n"
2013 "extent_minx DOUBLE,\n"
2014 "extent_miny DOUBLE,\n"
2015 "extent_maxx DOUBLE,\n"
2016 "extent_maxy DOUBLE,\n"
2017 "strict_resolution INTEGER NOT NULL,\n"
2018 "mixed_resolutions INTEGER NOT NULL,\n"
2019 "section_paths INTEGER NOT NULL,\n"
2020 "section_md5 INTEGER NOT NULL,\n"
2021 "section_summary INTEGER NOT NULL,\n"
2022 "is_queryable INTEGER NOT NULL,\n"
2023 "red_band_index INTEGER,\n"
2024 "green_band_index INTEGER,\n"
2025 "blue_band_index INTEGER,\n"
2026 "nir_band_index INTEGER,\n"
2027 "enable_auto_ndvi INTEGER,\n"
2028 "copyright TEXT NOT NULL DEFAULT '*** unknown ***',\n"
2029 "license INTEGER NOT NULL DEFAULT 0,\n"
2030 "CONSTRAINT fk_rc_srs FOREIGN KEY (srid) "
2031 "REFERENCES spatial_ref_sys (srid),\n"
2032 "CONSTRAINT fk_rc_lic FOREIGN KEY (license) "
2033 "REFERENCES data_licenses (id))";
2034 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2035 if (ret != SQLITE_OK)
2036 {
2037 spatialite_e ("CREATE TABLE 'raster_coverages' error: %s\n", err_msg);
2038 sqlite3_free (err_msg);
2039 return 0;
2040 }
2041
2042 /* creating the raster_coverages_srid table */
2043 sql = "CREATE TABLE IF NOT EXISTS raster_coverages_srid (\n"
2044 "coverage_name TEXT NOT NULL,\n"
2045 "srid INTEGER NOT NULL,\n"
2046 "extent_minx DOUBLE,\n"
2047 "extent_miny DOUBLE,\n"
2048 "extent_maxx DOUBLE,\n"
2049 "extent_maxy DOUBLE,\n"
2050 "CONSTRAINT pk_raster_coverages_srid PRIMARY KEY (coverage_name, srid),\n"
2051 "CONSTRAINT fk_raster_coverages_srid FOREIGN KEY (coverage_name) "
2052 "REFERENCES raster_coverages (coverage_name) ON DELETE CASCADE,\n"
2053 "CONSTRAINT fk_raster_srid FOREIGN KEY (srid) "
2054 "REFERENCES spatial_ref_sys (srid))";
2055 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2056 if (ret != SQLITE_OK)
2057 {
2058 spatialite_e ("CREATE TABLE 'raster_coverages_srid' error: %s\n",
2059 err_msg);
2060 sqlite3_free (err_msg);
2061 return 0;
2062 }
2063
2064 /* creating the raster_coverages_ref_sys view */
2065 sql = "CREATE VIEW IF NOT EXISTS raster_coverages_ref_sys AS\n"
2066 "SELECT c.coverage_name AS coverage_name, c.title AS title, "
2067 "c.abstract AS abstract, c.sample_type AS sample_type, "
2068 "c.pixel_type AS pixel_type, c.num_bands AS num_bands, "
2069 "c.compression AS compression, c.quality AS quality, "
2070 "c.tile_width AS tile_width, c.tile_height AS tile_height, "
2071 "c.horz_resolution AS horz_resolution, c.vert_resolution AS vert_resolution, "
2072 "c.nodata_pixel AS nodata_pixel, c.palette AS palette, "
2073 "c.statistics AS statistics, c.geo_minx AS geo_minx, "
2074 "c.geo_miny AS geo_miny, c.geo_maxx AS geo_maxx, "
2075 "c.geo_maxy AS geo_maxy, c.extent_minx AS extent_minx, "
2076 "c.extent_miny AS extent_miny, c.extent_maxx AS extent_maxx, "
2077 "c.extent_maxy AS extent_maxy, c.srid AS srid, 1 AS native_srid, "
2078 "s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2079 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text, "
2080 "c.strict_resolution AS strict_resolution, "
2081 "c.mixed_resolutions AS mixed_resolutions, "
2082 "c.section_paths AS section_paths, c.section_md5 AS section_md5, "
2083 "c.section_summary AS section_summary, c.is_queryable AS is_queryable, "
2084 "c.red_band_index, c.green_band_index, c.blue_band_index, "
2085 "c.nir_band_index, c.enable_auto_ndvi\n"
2086 "FROM raster_coverages AS c\n"
2087 "LEFT JOIN spatial_ref_sys AS s ON (c.srid = s.srid)\n"
2088 "UNION\nSELECT c.coverage_name AS coverage_name, c.title AS title, "
2089 "c.abstract AS abstract, c.sample_type AS sample_type, "
2090 "c.pixel_type AS pixel_type, c.num_bands AS num_bands, "
2091 "c.compression AS compression, c.quality AS quality, "
2092 "c.tile_width AS tile_width, c.tile_height AS tile_height, "
2093 "c.horz_resolution AS horz_resolution, c.vert_resolution AS vert_resolution, "
2094 "c.nodata_pixel AS nodata_pixel, c.palette AS palette, "
2095 "c.statistics AS statistics, c.geo_minx AS geo_minx, "
2096 "c.geo_miny AS geo_miny, c.geo_maxx AS geo_maxx, "
2097 "c.geo_maxy AS geo_maxy, x.extent_minx AS extent_minx, "
2098 "x.extent_miny AS extent_miny, x.extent_maxx AS extent_maxx, "
2099 "x.extent_maxy AS extent_maxy, s.srid AS srid, 0 AS native_srid, "
2100 "s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2101 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text, "
2102 "c.strict_resolution AS strict_resolution, "
2103 "c.mixed_resolutions AS mixed_resolutions, "
2104 "c.section_paths AS section_paths, c.section_md5 AS section_md5, "
2105 "c.section_summary AS section_summary, c.is_queryable AS is_queryable, "
2106 "c.red_band_index, c.green_band_index, c.blue_band_index, "
2107 "c.nir_band_index, c.enable_auto_ndvi\n"
2108 "FROM raster_coverages AS c\n"
2109 "JOIN raster_coverages_srid AS x ON (c.coverage_name = x.coverage_name)\n"
2110 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)";
2111 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2112 if (ret != SQLITE_OK)
2113 {
2114 spatialite_e ("CREATE VIEW 'raster_coverages_ref_sys' error: %s\n",
2115 err_msg);
2116 sqlite3_free (err_msg);
2117 return 0;
2118 }
2119
2120 /* creating the raster_coverages_keyword table */
2121 sql = "CREATE TABLE IF NOT EXISTS raster_coverages_keyword (\n"
2122 "coverage_name TEXT NOT NULL,\n"
2123 "keyword TEXT NOT NULL,\n"
2124 "CONSTRAINT pk_raster_coverages_keyword PRIMARY KEY (coverage_name, keyword),\n"
2125 "CONSTRAINT fk_raster_coverages_keyword FOREIGN KEY (coverage_name) "
2126 "REFERENCES raster_coverages (coverage_name) ON DELETE CASCADE)";
2127 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2128 if (ret != SQLITE_OK)
2129 {
2130 spatialite_e ("CREATE TABLE 'raster_coverages_keyword' error: %s\n",
2131 err_msg);
2132 sqlite3_free (err_msg);
2133 return 0;
2134 }
2135
2136 /* creating the Triggers */
2137 if (!create_raster_coverages_triggers (sqlite))
2138 return 0;
2139 return 1;
2140 }
2141
2142 SPATIALITE_PRIVATE int
createRasterCoveragesTable(void * p_sqlite)2143 createRasterCoveragesTable (void *p_sqlite)
2144 {
2145 /* Creating the main RasterCoverages table */
2146 int ok_table;
2147 sqlite3 *sqlite = p_sqlite;
2148
2149 /* checking if already defined */
2150 ok_table = check_raster_coverages (sqlite);
2151 if (ok_table)
2152 {
2153 spatialite_e
2154 ("CreateRasterCoveragesTable() error: table 'raster_coverages' already exists\n");
2155 goto error;
2156 }
2157 ok_table = check_raster_coverages_srid (sqlite);
2158 if (ok_table)
2159 {
2160 spatialite_e
2161 ("CreateRasterCoveragesTable() error: table 'raster_coverages_srid' already exists\n");
2162 goto error;
2163 }
2164 ok_table = check_raster_coverages_ref_sys (sqlite);
2165 if (ok_table)
2166 {
2167 spatialite_e
2168 ("CreateRasterCoveragesTable() error: view 'raster_coverages_ref_sys' already exists\n");
2169 goto error;
2170 }
2171 ok_table = check_raster_coverages_keyword (sqlite);
2172 if (ok_table)
2173 {
2174 spatialite_e
2175 ("CreateRasterCoveragesTable() error: table 'raster_coverages_keyword' already exists\n");
2176 goto error;
2177 }
2178
2179 /* creating the main RasterCoverages table */
2180 if (!create_raster_coverages (sqlite))
2181 goto error;
2182 return 1;
2183
2184 error:
2185 return 0;
2186 }
2187
2188 SPATIALITE_PRIVATE int
reCreateRasterCoveragesTriggers(void * p_sqlite)2189 reCreateRasterCoveragesTriggers (void *p_sqlite)
2190 {
2191 /* (re)Creating RasterCoverages triggers */
2192 sqlite3 *sqlite = p_sqlite;
2193
2194 drop_raster_coverages_triggers (sqlite);
2195 if (!create_raster_coverages_triggers (sqlite))
2196 return 0;
2197 return 1;
2198 }
2199
2200 static int
create_rl2map_configurations_triggers(sqlite3 * sqlite,int relaxed)2201 create_rl2map_configurations_triggers (sqlite3 * sqlite, int relaxed)
2202 {
2203 /* creating the rl2map_configurations triggers */
2204 char *sql;
2205 int ret;
2206 char **results;
2207 int rows;
2208 int columns;
2209 int i;
2210 char *err_msg = NULL;
2211 int ok_rl2map_config = 0;
2212
2213 /* checking for existing tables */
2214 sql =
2215 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'rl2map_configurations'";
2216 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
2217 if (ret != SQLITE_OK)
2218 {
2219 spatialite_e ("SQL error: %s\n", err_msg);
2220 sqlite3_free (err_msg);
2221 return 0;
2222 }
2223 for (i = 1; i <= rows; i++)
2224 {
2225 const char *name = results[(i * columns) + 0];
2226 if (strcasecmp (name, "rl2map_configurations") == 0)
2227 ok_rl2map_config = 1;
2228 }
2229 sqlite3_free_table (results);
2230
2231 if (ok_rl2map_config)
2232 {
2233 /* creating the rl2map_configurations triggers */
2234 if (relaxed == 0)
2235 {
2236 /* strong trigger - imposing XML schema validation */
2237 sql = "CREATE TRIGGER rl2map_config_insert\n"
2238 "BEFORE INSERT ON 'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2239 "SELECT RAISE(ABORT,'insert on rl2map_configurations violates constraint: "
2240 "not a valid RL2MapConfig')\n"
2241 "WHERE XB_IsMapConfig(NEW.config) <> 1;\n"
2242 "SELECT RAISE(ABORT,'insert on rl2map_configurations violates constraint: "
2243 "not an XML Schema Validated RL2MapConfig')\n"
2244 "WHERE XB_IsSchemaValidated(NEW.config) <> 1;\nEND";
2245 }
2246 else
2247 {
2248 /* relaxed trigger - not imposing XML schema validation */
2249 sql = "CREATE TRIGGER rl2map_config_insert\n"
2250 "BEFORE INSERT ON 'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2251 "SELECT RAISE(ABORT,'insert on rl2map_configurations violates constraint: "
2252 "not a valid RL2MapConfig')\n"
2253 "WHERE XB_IsMapConfig(NEW.config) <> 1;\nEND";
2254 }
2255 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2256 if (ret != SQLITE_OK)
2257 {
2258 spatialite_e ("SQL error: %s\n", err_msg);
2259 sqlite3_free (err_msg);
2260 return 0;
2261 }
2262 if (relaxed == 0)
2263 {
2264 /* strong trigger - imposing XML schema validation */
2265 sql = "CREATE TRIGGER rl2map_config_update\n"
2266 "BEFORE UPDATE ON 'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2267 "SELECT RAISE(ABORT,'update on rl2map_configurations violates constraint: "
2268 "not a valid RL2MapConfig')\n"
2269 "WHERE XB_IsMapConfig(NEW.config) <> 1;\n"
2270 "SELECT RAISE(ABORT,'update on rl2map_configurations violates constraint: "
2271 "not an XML Schema Validated RL2MapConfig')\n"
2272 "WHERE XB_IsSchemaValidated(NEW.config) <> 1;\nEND";
2273 }
2274 else
2275 {
2276 /* relaxed trigger - not imposing XML schema validation */
2277 sql = "CREATE TRIGGER rl2map_config_update\n"
2278 "BEFORE UPDATE ON 'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2279 "SELECT RAISE(ABORT,'update on rl2map_configurations violates constraint: "
2280 "not a valid RL2MapConfig')\n"
2281 "WHERE XB_IsMapConfig(NEW.config) <> 1;\nEND";
2282 }
2283 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2284 if (ret != SQLITE_OK)
2285 {
2286 spatialite_e ("SQL error: %s\n", err_msg);
2287 sqlite3_free (err_msg);
2288 return 0;
2289 }
2290 /* automatically setting the style_name after inserting */
2291 sql = "CREATE TRIGGER rl2map_config_name_ins\n"
2292 "AFTER INSERT ON 'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2293 "UPDATE rl2map_configurations "
2294 "SET name = XB_GetName(NEW.config) " "WHERE id = NEW.id;\nEND";
2295 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2296 if (ret != SQLITE_OK)
2297 {
2298 spatialite_e ("SQL error: %s\n", err_msg);
2299 sqlite3_free (err_msg);
2300 return 0;
2301 }
2302 /* automatically setting the style_name after updating */
2303 sql = "CREATE TRIGGER rl2map_config_name_upd\n"
2304 "AFTER UPDATE OF config ON "
2305 "'rl2map_configurations'\nFOR EACH ROW BEGIN\n"
2306 "UPDATE rl2map_configurations "
2307 "SET name = XB_GetName(NEW.config) " "WHERE id = NEW.id;\nEND";
2308 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2309 if (ret != SQLITE_OK)
2310 {
2311 spatialite_e ("SQL error: %s\n", err_msg);
2312 sqlite3_free (err_msg);
2313 return 0;
2314 }
2315 }
2316 return 1;
2317 }
2318
2319 SPATIALITE_PRIVATE int
create_rl2map_configurations(sqlite3 * sqlite,int relaxed)2320 create_rl2map_configurations (sqlite3 * sqlite, int relaxed)
2321 {
2322 /* creating the "rl2map_configurations" table */
2323 char *sql;
2324 int ret;
2325 char *err_msg = NULL;
2326
2327 sql = "CREATE TABLE rl2map_configurations (\n"
2328 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
2329 "name TEXT NOT NULL DEFAULT 'missing_name' UNIQUE,\n"
2330 "config BLOB NOT NULL)";
2331 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2332 if (ret != SQLITE_OK)
2333 {
2334 spatialite_e ("CREATE TABLE 'rl2map_configurations' error: %s\n",
2335 err_msg);
2336 sqlite3_free (err_msg);
2337 return 0;
2338 }
2339
2340 /* creating the Triggers */
2341 if (!create_rl2map_configurations_triggers (sqlite, relaxed))
2342 return 0;
2343 return 1;
2344 }
2345
2346 static int
check_if_coverage_exists(sqlite3 * sqlite,const char * db_prefix,const char * coverage)2347 check_if_coverage_exists (sqlite3 * sqlite, const char *db_prefix,
2348 const char *coverage)
2349 {
2350 /* checking if a Coverage table already exists */
2351 int exists = 0;
2352 char *sql_statement;
2353 char *errMsg = NULL;
2354 int ret;
2355 char **results;
2356 int rows;
2357 int columns;
2358 int i;
2359 char *xdb_prefix;
2360
2361 if (db_prefix == NULL)
2362 db_prefix = "MAIN";
2363 xdb_prefix = gaiaDoubleQuotedSql (db_prefix);
2364 sql_statement =
2365 sqlite3_mprintf
2366 ("SELECT name FROM \"%s\".sqlite_master WHERE type = 'table' "
2367 "AND Upper(name) = Upper(%Q)", xdb_prefix, coverage);
2368 free (xdb_prefix);
2369 ret =
2370 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2371 &errMsg);
2372 sqlite3_free (sql_statement);
2373 if (ret != SQLITE_OK)
2374 {
2375 sqlite3_free (errMsg);
2376 return 0;
2377 }
2378 for (i = 1; i <= rows; i++)
2379 exists = 1;
2380 sqlite3_free_table (results);
2381 return exists;
2382 }
2383
2384 SPATIALITE_PRIVATE int
checkPopulatedCoverage(void * p_sqlite,const char * db_prefix,const char * coverage_name)2385 checkPopulatedCoverage (void *p_sqlite, const char *db_prefix,
2386 const char *coverage_name)
2387 {
2388 /* checking if a Coverage table is already populated */
2389 int is_populated = 0;
2390 char *xdb_prefix;
2391 char *xname;
2392 char *xxname;
2393 char *sql_statement;
2394 char *errMsg = NULL;
2395 int ret;
2396 char **results;
2397 int rows;
2398 int columns;
2399 int i;
2400 sqlite3 *sqlite = p_sqlite;
2401
2402 xname = sqlite3_mprintf ("%s_tile_data", coverage_name);
2403 if (!check_if_coverage_exists (sqlite, db_prefix, xname))
2404 {
2405 sqlite3_free (xname);
2406 return 0;
2407 }
2408 if (db_prefix == NULL)
2409 db_prefix = "MAIN";
2410 xdb_prefix = gaiaDoubleQuotedSql (db_prefix);
2411 xxname = gaiaDoubleQuotedSql (xname);
2412 sqlite3_free (xname);
2413 sql_statement =
2414 sqlite3_mprintf ("SELECT ROWID FROM \"%s\".\"%s\" LIMIT 10", xdb_prefix,
2415 xxname);
2416 free (xdb_prefix);
2417 free (xxname);
2418 ret =
2419 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2420 &errMsg);
2421 sqlite3_free (sql_statement);
2422 if (ret != SQLITE_OK)
2423 {
2424 sqlite3_free (errMsg);
2425 return 0;
2426 }
2427 for (i = 1; i <= rows; i++)
2428 is_populated = 1;
2429 sqlite3_free_table (results);
2430 return is_populated;
2431 }
2432
2433 static int
check_vector_coverages(sqlite3 * sqlite)2434 check_vector_coverages (sqlite3 * sqlite)
2435 {
2436 /* checking if the "vector_coverages" table already exists */
2437 int exists = 0;
2438 char *sql_statement;
2439 char *errMsg = NULL;
2440 int ret;
2441 char **results;
2442 int rows;
2443 int columns;
2444 int i;
2445 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
2446 "AND Upper(name) = Upper('vector_coverages')";
2447 ret =
2448 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2449 &errMsg);
2450 if (ret != SQLITE_OK)
2451 {
2452 sqlite3_free (errMsg);
2453 return 0;
2454 }
2455 for (i = 1; i <= rows; i++)
2456 exists = 1;
2457 sqlite3_free_table (results);
2458 return exists;
2459 }
2460
2461 static int
check_vector_coverages_srid(sqlite3 * sqlite)2462 check_vector_coverages_srid (sqlite3 * sqlite)
2463 {
2464 /* checking if the "vector_coverages_srid" table already exists */
2465 int exists = 0;
2466 char *sql_statement;
2467 char *errMsg = NULL;
2468 int ret;
2469 char **results;
2470 int rows;
2471 int columns;
2472 int i;
2473 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
2474 "AND Upper(name) = Upper('vector_coverages_srid')";
2475 ret =
2476 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2477 &errMsg);
2478 if (ret != SQLITE_OK)
2479 {
2480 sqlite3_free (errMsg);
2481 return 0;
2482 }
2483 for (i = 1; i <= rows; i++)
2484 exists = 1;
2485 sqlite3_free_table (results);
2486 return exists;
2487 }
2488
2489 static int
check_vector_coverages_ref_sys(sqlite3 * sqlite)2490 check_vector_coverages_ref_sys (sqlite3 * sqlite)
2491 {
2492 /* checking if the "vector_coverages_ref_sys" view already exists */
2493 int exists = 0;
2494 char *sql_statement;
2495 char *errMsg = NULL;
2496 int ret;
2497 char **results;
2498 int rows;
2499 int columns;
2500 int i;
2501 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'view' "
2502 "AND Upper(name) = Upper('vector_coverages_ref_sys')";
2503 ret =
2504 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2505 &errMsg);
2506 if (ret != SQLITE_OK)
2507 {
2508 sqlite3_free (errMsg);
2509 return 0;
2510 }
2511 for (i = 1; i <= rows; i++)
2512 exists = 1;
2513 sqlite3_free_table (results);
2514 return exists;
2515 }
2516
2517 static int
check_vector_coverages_keyword(sqlite3 * sqlite)2518 check_vector_coverages_keyword (sqlite3 * sqlite)
2519 {
2520 /* checking if the "vector_coverages_keyword" table already exists */
2521 int exists = 0;
2522 char *sql_statement;
2523 char *errMsg = NULL;
2524 int ret;
2525 char **results;
2526 int rows;
2527 int columns;
2528 int i;
2529 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table' "
2530 "AND Upper(name) = Upper('vector_coverages_keyword')";
2531 ret =
2532 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
2533 &errMsg);
2534 if (ret != SQLITE_OK)
2535 {
2536 sqlite3_free (errMsg);
2537 return 0;
2538 }
2539 for (i = 1; i <= rows; i++)
2540 exists = 1;
2541 sqlite3_free_table (results);
2542 return exists;
2543 }
2544
2545 static void
drop_vector_coverages_triggers(sqlite3 * sqlite)2546 drop_vector_coverages_triggers (sqlite3 * sqlite)
2547 {
2548 /* dropping all "vector_coverages" triggers */
2549 char *sql;
2550 int ret;
2551 char *err_msg = NULL;
2552 char **results;
2553 int rows;
2554 int columns;
2555 int i;
2556
2557 /* checking for existing tables */
2558 sql =
2559 "SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name "
2560 "IN ('vector_coverages', 'vector_coverages_srid', 'vector_coverages_keyword')";
2561 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
2562 if (ret != SQLITE_OK)
2563 {
2564 spatialite_e ("SQL error: %s\n", err_msg);
2565 sqlite3_free (err_msg);
2566 return;
2567 }
2568 for (i = 1; i <= rows; i++)
2569 {
2570 const char *name = results[(i * columns) + 0];
2571 sql = sqlite3_mprintf ("DROP TRIGGER %s", name);
2572 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2573 if (ret != SQLITE_OK)
2574 {
2575 spatialite_e ("SQL error: %s\n", err_msg);
2576 sqlite3_free (err_msg);
2577 return;
2578 }
2579 sqlite3_free (sql);
2580 }
2581 sqlite3_free_table (results);
2582 }
2583
2584 static int
create_vector_coverages_triggers(sqlite3 * sqlite)2585 create_vector_coverages_triggers (sqlite3 * sqlite)
2586 {
2587 /* creating the "vector_coverages" triggers */
2588 char *sql;
2589 int ret;
2590 char *err_msg = NULL;
2591 char **results;
2592 int rows;
2593 int columns;
2594 int i;
2595 int ok_vector_coverages = 0;
2596 int ok_vector_coverages_srid = 0;
2597 int ok_vector_coverages_keyword = 0;
2598
2599 /* checking for existing tables */
2600 sql =
2601 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name "
2602 "IN ('vector_coverages', 'vector_coverages_srid', 'vector_coverages_keyword')";
2603 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
2604 if (ret != SQLITE_OK)
2605 {
2606 spatialite_e ("SQL error: %s\n", err_msg);
2607 sqlite3_free (err_msg);
2608 return 0;
2609 }
2610 for (i = 1; i <= rows; i++)
2611 {
2612 const char *name = results[(i * columns) + 0];
2613 if (strcasecmp (name, "vector_coverages") == 0)
2614 ok_vector_coverages = 1;
2615 if (strcasecmp (name, "vector_coverages_srid") == 0)
2616 ok_vector_coverages_srid = 1;
2617 if (strcasecmp (name, "vector_coverages_keyword") == 0)
2618 ok_vector_coverages_keyword = 1;
2619 }
2620 sqlite3_free_table (results);
2621
2622 if (ok_vector_coverages)
2623 {
2624 /* creating the vector_coverages triggers */
2625 sql = "CREATE TRIGGER IF NOT EXISTS vector_coverages_name_insert\n"
2626 "BEFORE INSERT ON 'vector_coverages'\nFOR EACH ROW BEGIN\n"
2627 "SELECT RAISE(ABORT,'insert on vector_coverages violates constraint: "
2628 "coverage_name value must not contain a single quote')\n"
2629 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2630 "SELECT RAISE(ABORT,'insert on vector_coverages violates constraint: "
2631 "coverage_name value must not contain a double quote')\n"
2632 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2633 "SELECT RAISE(ABORT,'insert on layer_vectors violates constraint: "
2634 "coverage_name value must be lower case')\n"
2635 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2636 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2637 if (ret != SQLITE_OK)
2638 {
2639 spatialite_e ("SQL error: %s\n", err_msg);
2640 sqlite3_free (err_msg);
2641 return 0;
2642 }
2643 sql = "CREATE TRIGGER IF NOT EXISTS vector_coverages_name_update\n"
2644 "BEFORE UPDATE OF 'coverage_name' ON 'vector_coverages'\nFOR EACH ROW BEGIN\n"
2645 "SELECT RAISE(ABORT,'update on vector_coverages violates constraint: "
2646 "coverage_name value must not contain a single quote')\n"
2647 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2648 "SELECT RAISE(ABORT,'update on vector_coverages violates constraint: "
2649 "coverage_name value must not contain a double quote')\n"
2650 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2651 "SELECT RAISE(ABORT,'update on vector_coverages violates constraint: "
2652 "coverage_name value must be lower case')\n"
2653 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2654 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2655 if (ret != SQLITE_OK)
2656 {
2657 spatialite_e ("SQL error: %s\n", err_msg);
2658 sqlite3_free (err_msg);
2659 return 0;
2660 }
2661 }
2662
2663 if (ok_vector_coverages_srid)
2664 {
2665 /* creating the vector_coverages_srid triggers */
2666 sql =
2667 "CREATE TRIGGER IF NOT EXISTS vector_coverages_srid_name_insert\n"
2668 "BEFORE INSERT ON 'vector_coverages_srid'\nFOR EACH ROW BEGIN\n"
2669 "SELECT RAISE(ABORT,'insert on vector_coverages_srid violates constraint: "
2670 "coverage_name value must not contain a single quote')\n"
2671 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2672 "SELECT RAISE(ABORT,'insert on vector_coverages_srid violates constraint: "
2673 "coverage_name value must not contain a double quote')\n"
2674 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2675 "SELECT RAISE(ABORT,'insert on vector_coverages_srid violates constraint: "
2676 "coverage_name value must be lower case')\n"
2677 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2678 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2679 if (ret != SQLITE_OK)
2680 {
2681 spatialite_e ("SQL error: %s\n", err_msg);
2682 sqlite3_free (err_msg);
2683 return 0;
2684 }
2685 sql =
2686 "CREATE TRIGGER IF NOT EXISTS vector_coverages_srid_name_update\n"
2687 "BEFORE UPDATE OF 'coverage_name' ON 'vector_coverages_srid'\nFOR EACH ROW BEGIN\n"
2688 "SELECT RAISE(ABORT,'update on vector_coverages_srid violates constraint: "
2689 "coverage_name value must not contain a single quote')\n"
2690 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2691 "SELECT RAISE(ABORT,'update on vector_coverages_srid violates constraint: "
2692 "coverage_name value must not contain a double quote')\n"
2693 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2694 "SELECT RAISE(ABORT,'update on vector_coverages_srid violates constraint: "
2695 "coverage_name value must be lower case')\n"
2696 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2697 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2698 if (ret != SQLITE_OK)
2699 {
2700 spatialite_e ("SQL error: %s\n", err_msg);
2701 sqlite3_free (err_msg);
2702 return 0;
2703 }
2704 }
2705
2706 if (ok_vector_coverages_keyword)
2707 {
2708 /* creating the vector_coverages_keyword triggers */
2709 sql =
2710 "CREATE TRIGGER IF NOT EXISTS vector_coverages_keyword_name_insert\n"
2711 "BEFORE INSERT ON 'vector_coverages_keyword'\nFOR EACH ROW BEGIN\n"
2712 "SELECT RAISE(ABORT,'insert on vector_coverages_keyword violates constraint: "
2713 "coverage_name value must not contain a single quote')\n"
2714 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2715 "SELECT RAISE(ABORT,'insert on vector_coverages_keyword violates constraint: "
2716 "coverage_name value must not contain a double quote')\n"
2717 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2718 "SELECT RAISE(ABORT,'insert on vector_coverages_keyword violates constraint: "
2719 "coverage_name value must be lower case')\n"
2720 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2721 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2722 if (ret != SQLITE_OK)
2723 {
2724 spatialite_e ("SQL error: %s\n", err_msg);
2725 sqlite3_free (err_msg);
2726 return 0;
2727 }
2728 sql =
2729 "CREATE TRIGGER IF NOT EXISTS vector_coverages_keyword_name_update\n"
2730 "BEFORE UPDATE OF 'coverage_name' ON 'vector_coverages_keyword'\nFOR EACH ROW BEGIN\n"
2731 "SELECT RAISE(ABORT,'update on vector_coverages_keyword violates constraint: "
2732 "coverage_name value must not contain a single quote')\n"
2733 "WHERE NEW.coverage_name LIKE ('%''%');\n"
2734 "SELECT RAISE(ABORT,'update on vector_coverages_keyword violates constraint: "
2735 "coverage_name value must not contain a double quote')\n"
2736 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
2737 "SELECT RAISE(ABORT,'update on vector_coverages_keyword violates constraint: "
2738 "coverage_name value must be lower case')\n"
2739 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
2740 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2741 if (ret != SQLITE_OK)
2742 {
2743 spatialite_e ("SQL error: %s\n", err_msg);
2744 sqlite3_free (err_msg);
2745 return 0;
2746 }
2747 }
2748 return 1;
2749 }
2750
2751 SPATIALITE_PRIVATE int
create_vector_coverages(sqlite3 * sqlite)2752 create_vector_coverages (sqlite3 * sqlite)
2753 {
2754 /* creating the "vector_coverages" table */
2755 char *sql;
2756 int ret;
2757 char *err_msg = NULL;
2758 sql = "CREATE TABLE IF NOT EXISTS vector_coverages (\n"
2759 "coverage_name TEXT NOT NULL PRIMARY KEY,\n"
2760 "f_table_name TEXT,\n"
2761 "f_geometry_column TEXT,\n"
2762 "view_name TEXT,\n"
2763 "view_geometry TEXT,\n"
2764 "virt_name TEXT,\n"
2765 "virt_geometry TEXT,\n"
2766 "topology_name TEXT,\n"
2767 "network_name TEXT,\n"
2768 "geo_minx DOUBLE,\n"
2769 "geo_miny DOUBLE,\n"
2770 "geo_maxx DOUBLE,\n"
2771 "geo_maxy DOUBLE,\n"
2772 "extent_minx DOUBLE,\n"
2773 "extent_miny DOUBLE,\n"
2774 "extent_maxx DOUBLE,\n"
2775 "extent_maxy DOUBLE,\n"
2776 "title TEXT NOT NULL DEFAULT '*** missing Title ***',\n"
2777 "abstract TEXT NOT NULL DEFAULT '*** missing Abstract ***',\n"
2778 "is_queryable INTEGER NOT NULL,\n"
2779 "is_editable INTEGER NOT NULL,\n"
2780 "copyright TEXT NOT NULL DEFAULT '*** unknown ***',\n"
2781 "license INTEGER NOT NULL DEFAULT 0,\n"
2782 "CONSTRAINT fk_vc_gc FOREIGN KEY (f_table_name, f_geometry_column) "
2783 "REFERENCES geometry_columns (f_table_name, f_geometry_column) "
2784 "ON DELETE CASCADE,\n"
2785 "CONSTRAINT fk_vc_sv FOREIGN KEY (view_name, view_geometry) "
2786 "REFERENCES views_geometry_columns (view_name, view_geometry) "
2787 "ON DELETE CASCADE,\n"
2788 "CONSTRAINT fk_vc_vt FOREIGN KEY (virt_name, virt_geometry) "
2789 "REFERENCES virts_geometry_columns (virt_name, virt_geometry) "
2790 "ON DELETE CASCADE,\n"
2791 "CONSTRAINT fk_vc_lic FOREIGN KEY (license) "
2792 "REFERENCES data_licenses (id))";
2793 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2794 if (ret != SQLITE_OK)
2795 {
2796 spatialite_e ("CREATE TABLE 'vector_coverages' error: %s\n", err_msg);
2797 sqlite3_free (err_msg);
2798 return 0;
2799 }
2800 /* creating the VectorLayers index */
2801 sql =
2802 "CREATE UNIQUE INDEX IF NOT EXISTS idx_vector_coverages ON vector_coverages "
2803 "(f_table_name, f_geometry_column)";
2804 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2805 if (ret != SQLITE_OK)
2806 {
2807 spatialite_e ("CREATE INDEX 'idx_vector_coverages' error: %s\n",
2808 err_msg);
2809 sqlite3_free (err_msg);
2810 return 0;
2811 }
2812
2813 /* creating the vector_coverages_srid table */
2814 sql = "CREATE TABLE IF NOT EXISTS vector_coverages_srid (\n"
2815 "coverage_name TEXT NOT NULL,\n"
2816 "srid INTEGER NOT NULL,\n"
2817 "extent_minx DOUBLE,\n"
2818 "extent_miny DOUBLE,\n"
2819 "extent_maxx DOUBLE,\n"
2820 "extent_maxy DOUBLE,\n"
2821 "CONSTRAINT pk_vector_coverages_srid PRIMARY KEY (coverage_name, srid),\n"
2822 "CONSTRAINT fk_vector_coverages_srid FOREIGN KEY (coverage_name) "
2823 "REFERENCES vector_coverages (coverage_name) ON DELETE CASCADE,\n"
2824 "CONSTRAINT fk_vector_srid FOREIGN KEY (srid) "
2825 "REFERENCES spatial_ref_sys (srid))";
2826 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2827 if (ret != SQLITE_OK)
2828 {
2829 spatialite_e ("CREATE TABLE 'vector_coverages_srid' error: %s\n",
2830 err_msg);
2831 sqlite3_free (err_msg);
2832 return 0;
2833 }
2834
2835 /* creating the vector_coverages_ref_sys view */
2836 sql = "CREATE VIEW IF NOT EXISTS vector_coverages_ref_sys AS\n"
2837 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2838 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2839 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, v.extent_minx AS extent_minx, "
2840 "v.extent_miny AS extent_miny, v.extent_maxx AS extent_maxx, v.extent_maxy AS extent_maxy, "
2841 "s.srid AS srid, 1 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2842 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2843 "FROM vector_coverages AS v\n"
2844 "JOIN geometry_columns AS x ON (v.topology_name IS NULL AND v.network_name IS NULL AND "
2845 "v.f_table_name IS NOT NULL AND v.f_geometry_column IS NOT NULL AND "
2846 "v.f_table_name = x.f_table_name AND v.f_geometry_column = x.f_geometry_column)\n"
2847 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)\n"
2848 "UNION\n"
2849 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2850 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2851 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, v.extent_minx AS extent_minx, "
2852 "v.extent_miny AS extent_miny, v.extent_maxx AS extent_maxx, v.extent_maxy AS extent_maxy, "
2853 "s.srid AS srid, 1 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2854 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2855 "FROM vector_coverages AS v\n"
2856 "JOIN views_geometry_columns AS y ON (v.view_name IS NOT NULL AND "
2857 "v.view_geometry IS NOT NULL AND v.view_name = y.view_name AND "
2858 "v.view_geometry = y.view_geometry)\n"
2859 "JOIN geometry_columns AS x ON (y.f_table_name = x.f_table_name AND "
2860 "y.f_geometry_column = x.f_geometry_column)\n"
2861 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)\n"
2862 "UNION\n"
2863 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2864 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2865 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, v.extent_minx AS extent_minx, "
2866 "v.extent_miny AS extent_miny, v.extent_maxx AS extent_maxx, v.extent_maxy AS extent_maxy, "
2867 "s.srid AS srid, 1 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2868 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2869 "FROM vector_coverages AS v\n"
2870 "JOIN virts_geometry_columns AS x ON (v.virt_name IS NOT NULL "
2871 "AND v.virt_geometry IS NOT NULL AND v.virt_name = x.virt_name "
2872 "AND v.virt_geometry = x.virt_geometry)\n"
2873 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)\n"
2874 "UNION\n"
2875 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2876 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2877 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, v.extent_minx AS extent_minx, "
2878 "v.extent_miny AS extent_miny, v.extent_maxx AS extent_maxx, v.extent_maxy AS extent_maxy, "
2879 "s.srid AS srid, 1 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2880 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2881 "FROM vector_coverages AS v\n"
2882 "JOIN topologies AS x ON (v.topology_name IS NOT NULL AND v.topology_name = x.topology_name)\n"
2883 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)\n"
2884 "UNION\n"
2885 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2886 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2887 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, v.extent_minx AS extent_minx, "
2888 "v.extent_miny AS extent_miny, v.extent_maxx AS extent_maxx, v.extent_maxy AS extent_maxy, "
2889 "s.srid AS srid, 1 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2890 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2891 "FROM vector_coverages AS v\n"
2892 "JOIN networks AS x ON (v.network_name IS NOT NULL AND v.network_name = x.network_name)\n"
2893 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)\n"
2894 "UNION\n"
2895 "SELECT v.coverage_name AS coverage_name, v.title AS title, v.abstract AS abstract, "
2896 "v.is_queryable AS is_queryable, v.geo_minx AS geo_minx, v.geo_miny AS geo_miny, "
2897 "v.geo_maxx AS geo_maxx, v.geo_maxy AS geo_maxy, x.extent_minx AS extent_minx, "
2898 "x.extent_miny AS extent_miny, x.extent_maxx AS extent_maxx, x.extent_maxy AS extent_maxy, "
2899 "s.srid AS srid, 0 AS native_srid, s.auth_name AS auth_name, s.auth_srid AS auth_srid, "
2900 "s.ref_sys_name AS ref_sys_name, s.proj4text AS proj4text\n"
2901 "FROM vector_coverages AS v\n"
2902 "JOIN vector_coverages_srid AS x ON (v.coverage_name = x.coverage_name)\n"
2903 "LEFT JOIN spatial_ref_sys AS s ON (x.srid = s.srid)";
2904 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2905 if (ret != SQLITE_OK)
2906 {
2907 spatialite_e ("CREATE VIEW 'vector_coverages_ref_sys' error: %s\n",
2908 err_msg);
2909 sqlite3_free (err_msg);
2910 return 0;
2911 }
2912
2913 /* creating the vector_coverages_keyword table */
2914 sql = "CREATE TABLE IF NOT EXISTS vector_coverages_keyword (\n"
2915 "coverage_name TEXT NOT NULL,\n"
2916 "keyword TEXT NOT NULL,\n"
2917 "CONSTRAINT pk_vector_coverages_keyword PRIMARY KEY (coverage_name, keyword),\n"
2918 "CONSTRAINT fk_vector_coverages_keyword FOREIGN KEY (coverage_name) "
2919 "REFERENCES vector_coverages (coverage_name) ON DELETE CASCADE)";
2920 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
2921 if (ret != SQLITE_OK)
2922 {
2923 spatialite_e ("CREATE TABLE 'vector_coverages_keyword' error: %s\n",
2924 err_msg);
2925 sqlite3_free (err_msg);
2926 return 0;
2927 }
2928 if (!create_vector_coverages_triggers (sqlite))
2929 return 0;
2930 return 1;
2931 }
2932
2933 SPATIALITE_PRIVATE int
createVectorCoveragesTable(void * p_sqlite)2934 createVectorCoveragesTable (void *p_sqlite)
2935 {
2936 /* Creating the main VectorCoverages table */
2937 int ok_table;
2938 sqlite3 *sqlite = p_sqlite;
2939
2940 #ifdef ENABLE_RTTOPO /* only if RTTOPO is enabled */
2941
2942 /* attempting to create Topologies and Networks tables */
2943 do_create_topologies (sqlite);
2944 do_create_networks (sqlite);
2945
2946 /* checking if already defined */
2947 ok_table = check_vector_coverages (sqlite);
2948 if (ok_table)
2949 {
2950 spatialite_e
2951 ("CreateVectorCoveragesTable() error: table 'vector_coverages' already exists\n");
2952 goto error;
2953 }
2954 ok_table = check_vector_coverages_srid (sqlite);
2955 if (ok_table)
2956 {
2957 spatialite_e
2958 ("CreateVectorCoveragesTable() error: table 'vector_coverages_srid' already exists\n");
2959 goto error;
2960 }
2961 ok_table = check_vector_coverages_ref_sys (sqlite);
2962 if (ok_table)
2963 {
2964 spatialite_e
2965 ("CreateVectorCoveragesTable() error: view 'vector_coverages_ref_sys' already exists\n");
2966 goto error;
2967 }
2968 ok_table = check_vector_coverages_keyword (sqlite);
2969 if (ok_table)
2970 {
2971 spatialite_e
2972 ("CreateVectorCoveragesTable() error: table 'vector_coverages_keyword' already exists\n");
2973 goto error;
2974 }
2975
2976 /* creating the main VectorCoverages table and triggers */
2977 if (!create_vector_coverages (sqlite))
2978 goto error;
2979 return 1;
2980
2981 #else
2982
2983 spatialite_e
2984 ("CreateVectorCoveragesTable() error: libspatialite was built by disabling Topology\n");
2985
2986 #endif /* end ENABLE_RTTOPO conditionals */
2987
2988 error:
2989 return 0;
2990 }
2991
2992 SPATIALITE_PRIVATE int
reCreateVectorCoveragesTriggers(void * p_sqlite)2993 reCreateVectorCoveragesTriggers (void *p_sqlite)
2994 {
2995 /* (re)Creating VectorCoverages triggers */
2996 sqlite3 *sqlite = p_sqlite;
2997
2998 drop_vector_coverages_triggers (sqlite);
2999 if (!create_vector_coverages_triggers (sqlite))
3000 return 0;
3001 return 1;
3002 }
3003
3004 static int
check_wms_getcapabilities(sqlite3 * sqlite)3005 check_wms_getcapabilities (sqlite3 * sqlite)
3006 {
3007 /* checking if the "wms_getcapabilities" table already exists */
3008 int exists = 0;
3009 const char *sql_statement;
3010 char *errMsg = NULL;
3011 int ret;
3012 char **results;
3013 int rows;
3014 int columns;
3015 int i;
3016 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table'"
3017 "AND Upper(name) = Upper('wms_getcapabilities')";
3018 ret =
3019 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
3020 &errMsg);
3021 if (ret != SQLITE_OK)
3022 {
3023 sqlite3_free (errMsg);
3024 return 0;
3025 }
3026 for (i = 1; i <= rows; i++)
3027 exists = 1;
3028 sqlite3_free_table (results);
3029 return exists;
3030 }
3031
3032 static int
check_wms_getmap(sqlite3 * sqlite)3033 check_wms_getmap (sqlite3 * sqlite)
3034 {
3035 /* checking if the "wms_getmap" table already exists */
3036 int exists = 0;
3037 const char *sql_statement;
3038 char *errMsg = NULL;
3039 int ret;
3040 char **results;
3041 int rows;
3042 int columns;
3043 int i;
3044 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table'"
3045 "AND Upper(name) = Upper('wms_getmap')";
3046 ret =
3047 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
3048 &errMsg);
3049 if (ret != SQLITE_OK)
3050 {
3051 sqlite3_free (errMsg);
3052 return 0;
3053 }
3054 for (i = 1; i <= rows; i++)
3055 exists = 1;
3056 sqlite3_free_table (results);
3057 return exists;
3058 }
3059
3060 static int
check_wms_settings(sqlite3 * sqlite)3061 check_wms_settings (sqlite3 * sqlite)
3062 {
3063 /* checking if the "wms_settings" table already exists */
3064 int exists = 0;
3065 const char *sql_statement;
3066 char *errMsg = NULL;
3067 int ret;
3068 char **results;
3069 int rows;
3070 int columns;
3071 int i;
3072 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table'"
3073 "AND Upper(name) = Upper('wms_settings')";
3074 ret =
3075 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
3076 &errMsg);
3077 if (ret != SQLITE_OK)
3078 {
3079 sqlite3_free (errMsg);
3080 return 0;
3081 }
3082 for (i = 1; i <= rows; i++)
3083 exists = 1;
3084 sqlite3_free_table (results);
3085 return exists;
3086 }
3087
3088 static int
check_wms_ref_sys(sqlite3 * sqlite)3089 check_wms_ref_sys (sqlite3 * sqlite)
3090 {
3091 /* checking if the "wms_ref_sys" table already exists */
3092 int exists = 0;
3093 const char *sql_statement;
3094 char *errMsg = NULL;
3095 int ret;
3096 char **results;
3097 int rows;
3098 int columns;
3099 int i;
3100 sql_statement = "SELECT name FROM sqlite_master WHERE type = 'table'"
3101 "AND Upper(name) = Upper('wms_ref_sys')";
3102 ret =
3103 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
3104 &errMsg);
3105 if (ret != SQLITE_OK)
3106 {
3107 sqlite3_free (errMsg);
3108 return 0;
3109 }
3110 for (i = 1; i <= rows; i++)
3111 exists = 1;
3112 sqlite3_free_table (results);
3113 return exists;
3114 }
3115
3116 SPATIALITE_PRIVATE int
create_wms_tables(sqlite3 * sqlite)3117 create_wms_tables (sqlite3 * sqlite)
3118 {
3119 /* creating the WMS support tables */
3120 char *sql;
3121 int ret;
3122 char *err_msg = NULL;
3123 sql = "CREATE TABLE IF NOT EXISTS wms_getcapabilities (\n"
3124 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3125 "url TEXT NOT NULL,\n"
3126 "title TEXT NOT NULL DEFAULT '*** undefined ***',\n"
3127 "abstract TEXT NOT NULL DEFAULT '*** undefined ***')";
3128 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3129 if (ret != SQLITE_OK)
3130 {
3131 spatialite_e ("CREATE TABLE 'wms_getcapabilities' error: %s\n",
3132 err_msg);
3133 sqlite3_free (err_msg);
3134 return 0;
3135 }
3136 sql =
3137 "CREATE UNIQUE INDEX IF NOT EXISTS idx_wms_getcapabilities ON wms_getcapabilities (url)";
3138 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3139 if (ret != SQLITE_OK)
3140 {
3141 spatialite_e ("CREATE INDEX 'idx_wms_getcapabilities' error: %s\n",
3142 err_msg);
3143 sqlite3_free (err_msg);
3144 return 0;
3145 }
3146
3147 sql = "CREATE TABLE IF NOT EXISTS wms_getmap (\n"
3148 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3149 "parent_id INTEGER NOT NULL,\n"
3150 "url TEXT NOT NULL,\n"
3151 "layer_name TEXT NOT NULL,\n"
3152 "title TEXT NOT NULL DEFAULT '*** undefined ***',\n"
3153 "abstract TEXT NOT NULL DEFAULT '*** undefined ***',\n"
3154 "version TEXT NOT NULL,\n"
3155 "srs TEXT NOT NULL,\n"
3156 "format TEXT NOT NULL,\n"
3157 "style TEXT NOT NULL,\n"
3158 "transparent INTEGER NOT NULL CHECK (transparent IN (0, 1)),\n"
3159 "flip_axes INTEGER NOT NULL CHECK (flip_axes IN (0, 1)),\n"
3160 "is_queryable INTEGER NOT NULL CHECK (is_queryable IN (0, 1)),\n"
3161 "getfeatureinfo_url TEXT,\n"
3162 "bgcolor TEXT,\n"
3163 "tiled INTEGER NOT NULL CHECK (tiled IN (0, 1)),\n"
3164 "tile_width INTEGER NOT NULL CHECK (tile_width BETWEEN 256 AND 5000),\n"
3165 "tile_height INTEGER NOT NULL CHECK (tile_width BETWEEN 256 AND 5000),\n"
3166 "is_cached INTEGER NOT NULL CHECK (is_cached IN (0, 1)),\n"
3167 "copyright TEXT NOT NULL DEFAULT '*** unknown ***',\n"
3168 "license INTEGER NOT NULL DEFAULT 0,\n"
3169 "CONSTRAINT fk_wms_getmap FOREIGN KEY (parent_id) "
3170 "REFERENCES wms_getcapabilities (id) ON DELETE CASCADE,\n"
3171 "CONSTRAINT fk_wms_lic FOREIGN KEY (license) "
3172 "REFERENCES data_licenses (id))";
3173 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3174 if (ret != SQLITE_OK)
3175 {
3176 spatialite_e ("CREATE TABLE 'wms_getmap' error: %s\n", err_msg);
3177 sqlite3_free (err_msg);
3178 return 0;
3179 }
3180 sql = "CREATE UNIQUE INDEX idx_wms_getmap ON wms_getmap (url, layer_name)";
3181 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3182 if (ret != SQLITE_OK)
3183 {
3184 spatialite_e ("CREATE INDEX 'idx_wms_getmap' error: %s\n", err_msg);
3185 sqlite3_free (err_msg);
3186 return 0;
3187 }
3188
3189 sql = "CREATE TABLE IF NOT EXISTS wms_settings (\n"
3190 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3191 "parent_id INTEGER NOT NULL,\n"
3192 "key TEXT NOT NULL CHECK (Lower(key) IN ('version', 'format', 'style')),\n"
3193 "value TEXT NOT NULL,\n"
3194 "is_default INTEGER NOT NULL CHECK (is_default IN (0, 1)),\n"
3195 "CONSTRAINT fk_wms_settings FOREIGN KEY (parent_id) "
3196 "REFERENCES wms_getmap (id) ON DELETE CASCADE)";
3197 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3198 if (ret != SQLITE_OK)
3199 {
3200 spatialite_e ("CREATE TABLE 'wms_settings' error: %s\n", err_msg);
3201 sqlite3_free (err_msg);
3202 return 0;
3203 }
3204 sql =
3205 "CREATE UNIQUE INDEX idx_wms_settings ON wms_settings (parent_id, key, value)";
3206 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3207 if (ret != SQLITE_OK)
3208 {
3209 spatialite_e ("CREATE INDEX 'idx_wms_settings' error: %s\n", err_msg);
3210 sqlite3_free (err_msg);
3211 return 0;
3212 }
3213
3214 sql = "CREATE TABLE IF NOT EXISTS wms_ref_sys (\n"
3215 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3216 "parent_id INTEGER NOT NULL,\n"
3217 "srs TEXT NOT NULL,\n"
3218 "minx DOUBLE NOT NULL,\n"
3219 "miny DOUBLE NOT NULL,\n"
3220 "maxx DOUBLE NOT NULL,\n"
3221 "maxy DOUBLE NOT NULL,\n"
3222 "is_default INTEGER NOT NULL CHECK (is_default IN (0, 1)),\n"
3223 "CONSTRAINT fk_wms_ref_sys FOREIGN KEY (parent_id) "
3224 "REFERENCES wms_getmap (id) ON DELETE CASCADE)";
3225 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3226 if (ret != SQLITE_OK)
3227 {
3228 spatialite_e ("CREATE TABLE 'wms_ref_sys' error: %s\n", err_msg);
3229 sqlite3_free (err_msg);
3230 return 0;
3231 }
3232 sql =
3233 "CREATE UNIQUE INDEX IF NOT EXISTS idx_wms_ref_sys ON wms_ref_sys (parent_id, srs)";
3234 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3235 if (ret != SQLITE_OK)
3236 {
3237 spatialite_e ("CREATE INDEX 'idx_wms_ref_sys' error: %s\n", err_msg);
3238 sqlite3_free (err_msg);
3239 return 0;
3240 }
3241
3242 return 1;
3243 }
3244
3245 SPATIALITE_PRIVATE int
createWMSTables(void * p_sqlite)3246 createWMSTables (void *p_sqlite)
3247 {
3248 /* Creating all WMS support tables */
3249 int ok_table;
3250 sqlite3 *sqlite = p_sqlite;
3251
3252 /* checking if already defined */
3253 ok_table = check_wms_getcapabilities (sqlite);
3254 if (ok_table)
3255 {
3256 spatialite_e
3257 ("WMS_CreateTables() error: table 'wms_getcapabilities' already exists\n");
3258 goto error;
3259 }
3260 ok_table = check_wms_getmap (sqlite);
3261 if (ok_table)
3262 {
3263 spatialite_e
3264 ("WMS_CreateTables() error: table 'wms_getmap' already exists\n");
3265 goto error;
3266 }
3267 ok_table = check_wms_settings (sqlite);
3268 if (ok_table)
3269 {
3270 spatialite_e
3271 ("WMS_CreateTables() error: table 'wms_settings' already exists\n");
3272 goto error;
3273 }
3274 ok_table = check_wms_ref_sys (sqlite);
3275 if (ok_table)
3276 {
3277 spatialite_e
3278 ("WMS_CreateTables() error: table 'wms_ref_sys' already exists\n");
3279 goto error;
3280 }
3281
3282 /* creating the WMS support tables */
3283 if (!create_wms_tables (sqlite))
3284 goto error;
3285 return 1;
3286
3287 error:
3288 return 0;
3289 }
3290
3291 #ifdef ENABLE_LIBXML2 /* including LIBXML2 */
3292
3293 static void
drop_styling_triggers(sqlite3 * sqlite)3294 drop_styling_triggers (sqlite3 * sqlite)
3295 {
3296 /* dropping all "styling" triggers */
3297 char *sql;
3298 int ret;
3299 char *err_msg = NULL;
3300 char **results;
3301 int rows;
3302 int columns;
3303 int i;
3304
3305 /* checking for existing tables */
3306 sql =
3307 "SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name "
3308 "IN ('SE_external_graphics', 'SE_fonts', 'SE_vector_styles', 'SE_raster_styles', "
3309 "'SE_vector_styled_layers', 'SE_raster_styled_layers', 'rl2map_configurations')";
3310 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3311 if (ret != SQLITE_OK)
3312 {
3313 spatialite_e ("SQL error: %s\n", err_msg);
3314 sqlite3_free (err_msg);
3315 return;
3316 }
3317 for (i = 1; i <= rows; i++)
3318 {
3319 const char *name = results[(i * columns) + 0];
3320 sql = sqlite3_mprintf ("DROP TRIGGER %s", name);
3321 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3322 if (ret != SQLITE_OK)
3323 {
3324 spatialite_e ("SQL error: %s\n", err_msg);
3325 sqlite3_free (err_msg);
3326 return;
3327 }
3328 sqlite3_free (sql);
3329 }
3330 sqlite3_free_table (results);
3331 }
3332
3333 static int
check_styling_table(sqlite3 * sqlite,const char * table,int is_view)3334 check_styling_table (sqlite3 * sqlite, const char *table, int is_view)
3335 {
3336 /* checking if some SLD/SE Styling-related table/view already exists */
3337 int exists = 0;
3338 char *sql_statement;
3339 char *errMsg = NULL;
3340 int ret;
3341 char **results;
3342 int rows;
3343 int columns;
3344 int i;
3345 sql_statement =
3346 sqlite3_mprintf ("SELECT name FROM sqlite_master WHERE type = '%s'"
3347 "AND Upper(name) = Upper(%Q)",
3348 (!is_view) ? "table" : "view", table);
3349 ret =
3350 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
3351 &errMsg);
3352 sqlite3_free (sql_statement);
3353 if (ret != SQLITE_OK)
3354 {
3355 sqlite3_free (errMsg);
3356 return 0;
3357 }
3358 for (i = 1; i <= rows; i++)
3359 exists = 1;
3360 sqlite3_free_table (results);
3361 return exists;
3362 }
3363
3364 static int
create_external_graphics_triggers(sqlite3 * sqlite)3365 create_external_graphics_triggers (sqlite3 * sqlite)
3366 {
3367 /* creating the SE_external_graphics triggers */
3368 char *sql;
3369 int ret;
3370 char **results;
3371 int rows;
3372 int columns;
3373 int i;
3374 char *err_msg = NULL;
3375 int ok_external_graphics = 0;
3376
3377 /* checking for existing tables */
3378 sql =
3379 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_external_graphics'";
3380 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3381 if (ret != SQLITE_OK)
3382 {
3383 spatialite_e ("SQL error: %s\n", err_msg);
3384 sqlite3_free (err_msg);
3385 return 0;
3386 }
3387 for (i = 1; i <= rows; i++)
3388 {
3389 const char *name = results[(i * columns) + 0];
3390 if (strcasecmp (name, "SE_external_graphics") == 0)
3391 ok_external_graphics = 1;
3392 }
3393 sqlite3_free_table (results);
3394
3395 if (ok_external_graphics)
3396 {
3397 /* creating the SE_external_graphics triggers */
3398 sql = "CREATE TRIGGER sextgr_mime_type_insert\n"
3399 "BEFORE INSERT ON 'SE_external_graphics'\nFOR EACH ROW BEGIN\n"
3400 "SELECT RAISE(ABORT,'insert on SE_external_graphics violates constraint: "
3401 "GetMimeType(resource) must be one of ''image/gif'' | ''image/png'' | "
3402 "''image/jpeg'' | ''image/svg+xml''')\n"
3403 "WHERE GetMimeType(NEW.resource) NOT IN ('image/gif', 'image/png', "
3404 "'image/jpeg', 'image/svg+xml');\nEND";
3405 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3406 if (ret != SQLITE_OK)
3407 {
3408 spatialite_e ("SQL error: %s\n", err_msg);
3409 sqlite3_free (err_msg);
3410 return 0;
3411 }
3412 sql = "CREATE TRIGGER sextgr_mime_type_update\n"
3413 "BEFORE UPDATE OF 'mime_type' ON 'SE_external_graphics'"
3414 "\nFOR EACH ROW BEGIN\n"
3415 "SELECT RAISE(ABORT, 'update on SE_external_graphics violates constraint: "
3416 "GetMimeType(resource) must be one of ''image/gif'' | ''image/png'' | "
3417 "''image/jpeg'' | ''image/svg+xml''')\n"
3418 "WHERE GetMimeType(NEW.resource) NOT IN ('image/gif', 'image/png', "
3419 "'image/jpeg', 'image/svg+xml');\nEND";
3420 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3421 if (ret != SQLITE_OK)
3422 {
3423 spatialite_e ("SQL error: %s\n", err_msg);
3424 sqlite3_free (err_msg);
3425 return 0;
3426 }
3427 }
3428 return 1;
3429 }
3430
3431 SPATIALITE_PRIVATE int
create_external_graphics(sqlite3 * sqlite)3432 create_external_graphics (sqlite3 * sqlite)
3433 {
3434 /* creating the SE_external_graphics table */
3435 char *sql;
3436 int ret;
3437 char *err_msg = NULL;
3438 sql = "CREATE TABLE SE_external_graphics (\n"
3439 "xlink_href TEXT NOT NULL PRIMARY KEY,\n"
3440 "title TEXT NOT NULL DEFAULT '*** undefined ***',\n"
3441 "abstract TEXT NOT NULL DEFAULT '*** undefined ***',\n"
3442 "resource BLOB NOT NULL,\n"
3443 "file_name TEXT NOT NULL DEFAULT '*** undefined ***')";
3444 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3445 if (ret != SQLITE_OK)
3446 {
3447 spatialite_e ("CREATE TABLE 'SE_external_graphics' error: %s\n",
3448 err_msg);
3449 sqlite3_free (err_msg);
3450 return 0;
3451 }
3452 if (!create_external_graphics_triggers (sqlite))
3453 return 0;
3454 return 1;
3455 }
3456
3457 static int
create_fonts_triggers(sqlite3 * sqlite)3458 create_fonts_triggers (sqlite3 * sqlite)
3459 {
3460 /* creating the SE_fonts triggers */
3461 char *sql;
3462 int ret;
3463 char **results;
3464 int rows;
3465 int columns;
3466 int i;
3467 char *err_msg = NULL;
3468 int ok_fonts = 0;
3469
3470 /* checking for existing tables */
3471 sql =
3472 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_fonts'";
3473 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3474 if (ret != SQLITE_OK)
3475 {
3476 spatialite_e ("SQL error: %s\n", err_msg);
3477 sqlite3_free (err_msg);
3478 return 0;
3479 }
3480 for (i = 1; i <= rows; i++)
3481 {
3482 const char *name = results[(i * columns) + 0];
3483 if (strcasecmp (name, "topologies") == 0)
3484 ok_fonts = 1;
3485 }
3486 sqlite3_free_table (results);
3487
3488 if (ok_fonts)
3489 {
3490 /* creating the SE_fonts triggers */
3491 sql = "CREATE TRIGGER se_font_insert1\n"
3492 "BEFORE INSERT ON 'SE_fonts'\nFOR EACH ROW BEGIN\n"
3493 "SELECT RAISE(ABORT,'insert on SE_Fonts violates constraint: "
3494 "invalid Font')\nWHERE IsValidFont(NEW.font) <> 1;\nEND";
3495 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3496 if (ret != SQLITE_OK)
3497 {
3498 spatialite_e ("SQL error: %s\n", err_msg);
3499 sqlite3_free (err_msg);
3500 return 0;
3501 }
3502 sql = "CREATE TRIGGER se_font_insert2\n"
3503 "BEFORE INSERT ON 'SE_fonts'\nFOR EACH ROW BEGIN\n"
3504 "SELECT RAISE(ABORT,'insert on SE_Fonts violates constraint: "
3505 "mismatching FontFacename')\nWHERE "
3506 "CheckFontFacename(NEW.font_facename, NEW.font) <> 1;\nEND";
3507 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3508 if (ret != SQLITE_OK)
3509 {
3510 spatialite_e ("SQL error: %s\n", err_msg);
3511 sqlite3_free (err_msg);
3512 return 0;
3513 }
3514 /* rejecting any possible UPDATE */
3515 sql = "CREATE TRIGGER se_font_update\n"
3516 "BEFORE UPDATE ON 'SE_fonts'\nFOR EACH ROW BEGIN\n"
3517 "SELECT RAISE(ABORT,'UPDATE on SE_Fonts is always forbidden')\n;\nEND";
3518 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3519 if (ret != SQLITE_OK)
3520 {
3521 spatialite_e ("SQL error: %s\n", err_msg);
3522 sqlite3_free (err_msg);
3523 return 0;
3524 }
3525 }
3526 return 1;
3527 }
3528
3529 SPATIALITE_PRIVATE int
create_fonts(sqlite3 * sqlite)3530 create_fonts (sqlite3 * sqlite)
3531 {
3532 /* creating the SE_fonts table */
3533 char *sql;
3534 int ret;
3535 char *err_msg = NULL;
3536 sql = "CREATE TABLE SE_fonts (\n"
3537 "font_facename TEXT NOT NULL PRIMARY KEY,\n" "font BLOB NOT NULL)";
3538 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3539 if (ret != SQLITE_OK)
3540 {
3541 spatialite_e ("CREATE TABLE 'SE_fonts' error: %s\n", err_msg);
3542 sqlite3_free (err_msg);
3543 return 0;
3544 }
3545 if (!create_fonts_triggers (sqlite))
3546 return 0;
3547 return 1;
3548 }
3549
3550 static int
create_vector_styles_triggers(sqlite3 * sqlite,int relaxed)3551 create_vector_styles_triggers (sqlite3 * sqlite, int relaxed)
3552 {
3553 /* creating the SE_vector_styles triggers */
3554 char *sql;
3555 int ret;
3556 char **results;
3557 int rows;
3558 int columns;
3559 int i;
3560 char *err_msg = NULL;
3561 int ok_vector_styles = 0;
3562
3563 /* checking for existing tables */
3564 sql =
3565 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_vector_styles'";
3566 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3567 if (ret != SQLITE_OK)
3568 {
3569 spatialite_e ("SQL error: %s\n", err_msg);
3570 sqlite3_free (err_msg);
3571 return 0;
3572 }
3573 for (i = 1; i <= rows; i++)
3574 {
3575 const char *name = results[(i * columns) + 0];
3576 if (strcasecmp (name, "SE_vector_styles") == 0)
3577 ok_vector_styles = 1;
3578 }
3579 sqlite3_free_table (results);
3580
3581 if (ok_vector_styles)
3582 {
3583 /* creating the SE_vector_styles triggers */
3584 if (relaxed == 0)
3585 {
3586 /* strong trigger - imposing XML schema validation */
3587 sql = "CREATE TRIGGER sevector_style_insert\n"
3588 "BEFORE INSERT ON 'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3589 "SELECT RAISE(ABORT,'insert on SE_vector_styles violates constraint: "
3590 "not a valid SLD/SE Vector Style')\n"
3591 "WHERE XB_IsSldSeVectorStyle(NEW.style) <> 1;\n"
3592 "SELECT RAISE(ABORT,'insert on SE_vector_styles violates constraint: "
3593 "not an XML Schema Validated SLD/SE Vector Style')\n"
3594 "WHERE XB_IsSchemaValidated(NEW.style) <> 1;\nEND";
3595 }
3596 else
3597 {
3598 /* relaxed trigger - not imposing XML schema validation */
3599 sql = "CREATE TRIGGER sevector_style_insert\n"
3600 "BEFORE INSERT ON 'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3601 "SELECT RAISE(ABORT,'insert on SE_vector_styles violates constraint: "
3602 "not a valid SLD/SE Vector Style')\n"
3603 "WHERE XB_IsSldSeVectorStyle(NEW.style) <> 1;\nEND";
3604 }
3605 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3606 if (ret != SQLITE_OK)
3607 {
3608 spatialite_e ("SQL error: %s\n", err_msg);
3609 sqlite3_free (err_msg);
3610 return 0;
3611 }
3612 if (relaxed == 0)
3613 {
3614 /* strong trigger - imposing XML schema validation */
3615 sql = "CREATE TRIGGER sevector_style_update\n"
3616 "BEFORE UPDATE ON 'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3617 "SELECT RAISE(ABORT,'update on SE_vector_styles violates constraint: "
3618 "not a valid SLD/SE Vector Style')\n"
3619 "WHERE XB_IsSldSeVectorStyle(NEW.style) <> 1;\n"
3620 "SELECT RAISE(ABORT,'update on SE_vector_styles violates constraint: "
3621 "not an XML Schema Validated SLD/SE Vector Style')\n"
3622 "WHERE XB_IsSchemaValidated(NEW.style) <> 1;\nEND";
3623 }
3624 else
3625 {
3626 /* relaxed trigger - not imposing XML schema validation */
3627 sql = "CREATE TRIGGER sevector_style_update\n"
3628 "BEFORE UPDATE ON 'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3629 "SELECT RAISE(ABORT,'update on SE_vector_styles violates constraint: "
3630 "not a valid SLD/SE Vector Style')\n"
3631 "WHERE XB_IsSldSeVectorStyle(NEW.style) <> 1;\nEND";
3632 }
3633 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3634 if (ret != SQLITE_OK)
3635 {
3636 spatialite_e ("SQL error: %s\n", err_msg);
3637 sqlite3_free (err_msg);
3638 return 0;
3639 }
3640 /* automatically setting the style_name after inserting */
3641 sql = "CREATE TRIGGER sevector_style_name_ins\n"
3642 "AFTER INSERT ON 'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3643 "UPDATE SE_vector_styles "
3644 "SET style_name = XB_GetName(NEW.style) "
3645 "WHERE style_id = NEW.style_id;\nEND";
3646 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3647 if (ret != SQLITE_OK)
3648 {
3649 spatialite_e ("SQL error: %s\n", err_msg);
3650 sqlite3_free (err_msg);
3651 return 0;
3652 }
3653 /* automatically setting the style_name after updating */
3654 sql = "CREATE TRIGGER sevector_style_name_upd\n"
3655 "AFTER UPDATE OF style ON "
3656 "'SE_vector_styles'\nFOR EACH ROW BEGIN\n"
3657 "UPDATE SE_vector_styles "
3658 "SET style_name = XB_GetName(NEW.style) "
3659 "WHERE style_id = NEW.style_id;\nEND";
3660 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3661 if (ret != SQLITE_OK)
3662 {
3663 spatialite_e ("SQL error: %s\n", err_msg);
3664 sqlite3_free (err_msg);
3665 return 0;
3666 }
3667 }
3668 return 1;
3669 }
3670
3671 SPATIALITE_PRIVATE int
create_vector_styles(sqlite3 * sqlite,int relaxed)3672 create_vector_styles (sqlite3 * sqlite, int relaxed)
3673 {
3674 /* creating the SE_vector_styles table */
3675 char *sql;
3676 int ret;
3677 char *err_msg = NULL;
3678 sql = "CREATE TABLE SE_vector_styles (\n"
3679 "style_id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3680 "style_name TEXT NOT NULL DEFAULT 'missing_name' UNIQUE,\n"
3681 "style BLOB NOT NULL)";
3682 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3683 if (ret != SQLITE_OK)
3684 {
3685 spatialite_e ("CREATE TABLE 'SE_vector_styles' error: %s\n", err_msg);
3686 sqlite3_free (err_msg);
3687 return 0;
3688 }
3689 /* creating the style Name index */
3690 sql = "CREATE INDEX idx_vector_styles ON SE_vector_styles (style_name)";
3691 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3692 if (ret != SQLITE_OK)
3693 {
3694 spatialite_e ("CREATE INDEX 'idx_vector_styles' error: %s\n",
3695 err_msg);
3696 sqlite3_free (err_msg);
3697 return 0;
3698 }
3699 if (!create_vector_styles_triggers (sqlite, relaxed))
3700 return 0;
3701 return 1;
3702 }
3703
3704 static int
create_vector_styled_layers_triggers(sqlite3 * sqlite)3705 create_vector_styled_layers_triggers (sqlite3 * sqlite)
3706 {
3707 /* creating the SE_vector_styled_layers triggers */
3708 char *sql;
3709 int ret;
3710 char **results;
3711 int rows;
3712 int columns;
3713 int i;
3714 char *err_msg = NULL;
3715 int ok_vector_styled = 0;
3716
3717 /* checking for existing tables */
3718 sql =
3719 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_vector_styled_layers'";
3720 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3721 if (ret != SQLITE_OK)
3722 {
3723 spatialite_e ("SQL error: %s\n", err_msg);
3724 sqlite3_free (err_msg);
3725 return 0;
3726 }
3727 for (i = 1; i <= rows; i++)
3728 {
3729 const char *name = results[(i * columns) + 0];
3730 if (strcasecmp (name, "SE_vector_styled_layers") == 0)
3731 ok_vector_styled = 1;
3732 }
3733 sqlite3_free_table (results);
3734
3735 if (ok_vector_styled)
3736 {
3737 /* creating the SE_vector_styled_layers triggers */
3738 sql = "CREATE TRIGGER sevstl_coverage_name_insert\n"
3739 "BEFORE INSERT ON 'SE_vector_styled_layers'\nFOR EACH ROW BEGIN\n"
3740 "SELECT RAISE(ABORT,'insert on SE_vector_styled_layers violates constraint: "
3741 "coverage_name value must not contain a single quote')\n"
3742 "WHERE NEW.coverage_name LIKE ('%''%');\n"
3743 "SELECT RAISE(ABORT,'insert on SE_vector_styled_layers violates constraint: "
3744 "coverage_name value must not contain a double quote')\n"
3745 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
3746 "SELECT RAISE(ABORT,'insert on SE_vector_styled_layers violates constraint: "
3747 "coverage_name value must be lower case')\n"
3748 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
3749 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3750 if (ret != SQLITE_OK)
3751 {
3752 spatialite_e ("SQL error: %s\n", err_msg);
3753 sqlite3_free (err_msg);
3754 return 0;
3755 }
3756 sql = "CREATE TRIGGER sevstl_coverage_name_update\n"
3757 "BEFORE UPDATE OF 'coverage_name' ON 'SE_vector_styled_layers'\nFOR EACH ROW BEGIN\n"
3758 "SELECT RAISE(ABORT,'update on SE_vector_styled_layers violates constraint: "
3759 "coverage_name value must not contain a single quote')\n"
3760 "WHERE NEW.coverage_name LIKE ('%''%');\n"
3761 "SELECT RAISE(ABORT,'update on SE_vector_styled_layers violates constraint: "
3762 "coverage_name value must not contain a double quote')\n"
3763 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
3764 "SELECT RAISE(ABORT,'update on SE_vector_styled_layers violates constraint: "
3765 "coverage_name value must be lower case')\n"
3766 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
3767 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3768 if (ret != SQLITE_OK)
3769 {
3770 spatialite_e ("SQL error: %s\n", err_msg);
3771 sqlite3_free (err_msg);
3772 return 0;
3773 }
3774 }
3775 return 1;
3776 }
3777
3778 SPATIALITE_PRIVATE int
create_vector_styled_layers(sqlite3 * sqlite)3779 create_vector_styled_layers (sqlite3 * sqlite)
3780 {
3781 /* creating the SE_vector_styled_layers table */
3782 char *sql;
3783 int ret;
3784 char *err_msg = NULL;
3785 sql = "CREATE TABLE SE_vector_styled_layers (\n"
3786 "coverage_name TEXT NOT NULL,\n"
3787 "style_id INTEGER NOT NULL,\n"
3788 "CONSTRAINT pk_sevstl PRIMARY KEY "
3789 "(coverage_name, style_id),\n"
3790 "CONSTRAINT fk_sevstl_cvg FOREIGN KEY (coverage_name) "
3791 "REFERENCES vector_coverages (coverage_name) ON DELETE CASCADE,\n"
3792 "CONSTRAINT fk_sevstl_stl FOREIGN KEY (style_id) "
3793 "REFERENCES SE_vector_styles (style_id) ON DELETE CASCADE)";
3794 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3795 if (ret != SQLITE_OK)
3796 {
3797 spatialite_e ("CREATE TABLE 'SE_vector_styled_layers' error: %s\n",
3798 err_msg);
3799 sqlite3_free (err_msg);
3800 return 0;
3801 }
3802 /* creating the style_id index */
3803 sql = "CREATE INDEX idx_sevstl_style ON SE_vector_styled_layers (style_id)";
3804 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3805 if (ret != SQLITE_OK)
3806 {
3807 spatialite_e ("CREATE INDEX 'idx_svstl_style' error: %s\n", err_msg);
3808 sqlite3_free (err_msg);
3809 return 0;
3810 }
3811 if (!create_vector_styled_layers_triggers (sqlite))
3812 return 0;
3813 return 1;
3814 }
3815
3816 static int
create_raster_styles_triggers(sqlite3 * sqlite,int relaxed)3817 create_raster_styles_triggers (sqlite3 * sqlite, int relaxed)
3818 {
3819 /* creating the SE_raster_styles triggers */
3820 char *sql;
3821 int ret;
3822 char **results;
3823 int rows;
3824 int columns;
3825 int i;
3826 char *err_msg = NULL;
3827 int ok_raster_styles = 0;
3828
3829 /* checking for existing tables */
3830 sql =
3831 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_raster_styles'";
3832 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3833 if (ret != SQLITE_OK)
3834 {
3835 spatialite_e ("SQL error: %s\n", err_msg);
3836 sqlite3_free (err_msg);
3837 return 0;
3838 }
3839 for (i = 1; i <= rows; i++)
3840 {
3841 const char *name = results[(i * columns) + 0];
3842 if (strcasecmp (name, "SE_raster_styles") == 0)
3843 ok_raster_styles = 1;
3844 }
3845 sqlite3_free_table (results);
3846
3847 if (ok_raster_styles)
3848 {
3849 /* creating the SE_raster_styles triggers */
3850 if (relaxed == 0)
3851 {
3852 /* strong trigger - imposing XML schema validation */
3853 sql = "CREATE TRIGGER seraster_style_insert\n"
3854 "BEFORE INSERT ON 'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3855 "SELECT RAISE(ABORT,'insert on SE_raster_styles violates constraint: "
3856 "not a valid SLD/SE Raster Style')\n"
3857 "WHERE XB_IsSldSeRasterStyle(NEW.style) <> 1;\n"
3858 "SELECT RAISE(ABORT,'insert on SE_raster_styles violates constraint: "
3859 "not an XML Schema Validated SLD/SE Raster Style')\n"
3860 "WHERE XB_IsSchemaValidated(NEW.style) <> 1;\nEND";
3861 }
3862 else
3863 {
3864 /* relaxed trigger - not imposing XML schema validation */
3865 sql = "CREATE TRIGGER seraster_style_insert\n"
3866 "BEFORE INSERT ON 'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3867 "SELECT RAISE(ABORT,'insert on SE_raster_styles violates constraint: "
3868 "not a valid SLD/SE Raster Style')\n"
3869 "WHERE XB_IsSldSeRasterStyle(NEW.style) <> 1;\nEND";
3870 }
3871 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3872 if (ret != SQLITE_OK)
3873 {
3874 spatialite_e ("SQL error: %s\n", err_msg);
3875 sqlite3_free (err_msg);
3876 return 0;
3877 }
3878 if (relaxed == 0)
3879 {
3880 /* strong trigger - imposing XML schema validation */
3881 sql = "CREATE TRIGGER seraster_style_update\n"
3882 "BEFORE UPDATE ON 'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3883 "SELECT RAISE(ABORT,'update on SE_raster_styles violates constraint: "
3884 "not a valid SLD/SE Raster Style')\n"
3885 "WHERE XB_IsSldSeRasterStyle(NEW.style) <> 1;\n"
3886 "SELECT RAISE(ABORT,'update on SE_raster_styles violates constraint: "
3887 "not an XML Schema Validated SLD/SE Raster Style')\n"
3888 "WHERE XB_IsSchemaValidated(NEW.style) <> 1;\nEND";
3889 }
3890 else
3891 {
3892 /* relaxed trigger - not imposing XML schema validation */
3893 sql = "CREATE TRIGGER seraster_style_update\n"
3894 "BEFORE UPDATE ON 'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3895 "SELECT RAISE(ABORT,'update on SE_raster_styles violates constraint: "
3896 "not a valid SLD/SE Raster Style')\n"
3897 "WHERE XB_IsSldSeRasterStyle(NEW.style) <> 1;\nEND";
3898 }
3899 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3900 if (ret != SQLITE_OK)
3901 {
3902 spatialite_e ("SQL error: %s\n", err_msg);
3903 sqlite3_free (err_msg);
3904 return 0;
3905 }
3906 /* automatically setting the style_name after inserting */
3907 sql = "CREATE TRIGGER seraster_style_name_ins\n"
3908 "AFTER INSERT ON 'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3909 "UPDATE SE_raster_styles "
3910 "SET style_name = XB_GetName(NEW.style) "
3911 "WHERE style_id = NEW.style_id;\nEND";
3912 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3913 if (ret != SQLITE_OK)
3914 {
3915 spatialite_e ("SQL error: %s\n", err_msg);
3916 sqlite3_free (err_msg);
3917 return 0;
3918 }
3919 /* automatically setting the style_name after updating */
3920 sql = "CREATE TRIGGER seraster_style_name_upd\n"
3921 "AFTER UPDATE OF style ON "
3922 "'SE_raster_styles'\nFOR EACH ROW BEGIN\n"
3923 "UPDATE SE_raster_styles "
3924 "SET style_name = XB_GetName(NEW.style) "
3925 "WHERE style_id = NEW.style_id;\nEND";
3926 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3927 if (ret != SQLITE_OK)
3928 {
3929 spatialite_e ("SQL error: %s\n", err_msg);
3930 sqlite3_free (err_msg);
3931 return 0;
3932 }
3933 }
3934 return 1;
3935 }
3936
3937 SPATIALITE_PRIVATE int
create_raster_styles(sqlite3 * sqlite,int relaxed)3938 create_raster_styles (sqlite3 * sqlite, int relaxed)
3939 {
3940 /* creating the SE_raster_styles table */
3941 char *sql;
3942 int ret;
3943 char *err_msg = NULL;
3944 sql = "CREATE TABLE SE_raster_styles (\n"
3945 "style_id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
3946 "style_name TEXT NOT NULL DEFAULT 'missing_name' UNIQUE,\n"
3947 "style BLOB NOT NULL)";
3948 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3949 if (ret != SQLITE_OK)
3950 {
3951 spatialite_e ("CREATE TABLE 'SE_raster_styles' error: %s\n", err_msg);
3952 sqlite3_free (err_msg);
3953 return 0;
3954 }
3955 /* creating the style Name index */
3956 sql = "CREATE INDEX idx_raster_styles ON SE_raster_styles (style_name)";
3957 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
3958 if (ret != SQLITE_OK)
3959 {
3960 spatialite_e ("CREATE INDEX 'idx_raster_styles' error: %s\n",
3961 err_msg);
3962 sqlite3_free (err_msg);
3963 return 0;
3964 }
3965 if (!create_raster_styles_triggers (sqlite, relaxed))
3966 return 0;
3967 return 1;
3968 }
3969
3970 static int
create_raster_styled_layers_triggers(sqlite3 * sqlite)3971 create_raster_styled_layers_triggers (sqlite3 * sqlite)
3972 {
3973 /* creating the SE_raster_styled_layers triggers */
3974 char *sql;
3975 int ret;
3976 char **results;
3977 int rows;
3978 int columns;
3979 int i;
3980 char *err_msg = NULL;
3981 int ok_raster_styled = 0;
3982
3983 /* checking for existing tables */
3984 sql =
3985 "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'SE_raster_styled_layers'";
3986 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, &err_msg);
3987 if (ret != SQLITE_OK)
3988 {
3989 spatialite_e ("SQL error: %s\n", err_msg);
3990 sqlite3_free (err_msg);
3991 return 0;
3992 }
3993 for (i = 1; i <= rows; i++)
3994 {
3995 const char *name = results[(i * columns) + 0];
3996 if (strcasecmp (name, "SE_raster_styled_layers") == 0)
3997 ok_raster_styled = 1;
3998 }
3999 sqlite3_free_table (results);
4000
4001 if (ok_raster_styled)
4002 {
4003 /* creating the SE_raster_styled_layers triggers */
4004 sql = "CREATE TRIGGER serstl_coverage_name_insert\n"
4005 "BEFORE INSERT ON 'SE_raster_styled_layers'\nFOR EACH ROW BEGIN\n"
4006 "SELECT RAISE(ABORT,'insert on SE_raster_styled_layers violates constraint: "
4007 "coverage_name value must not contain a single quote')\n"
4008 "WHERE NEW.coverage_name LIKE ('%''%');\n"
4009 "SELECT RAISE(ABORT,'insert on SE_raster_styled_layers violates constraint: "
4010 "coverage_name value must not contain a double quote')\n"
4011 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
4012 "SELECT RAISE(ABORT,'insert on SE_raster_styled_layers violates constraint: "
4013 "coverage_name value must be lower case')\n"
4014 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
4015 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4016 if (ret != SQLITE_OK)
4017 {
4018 spatialite_e ("SQL error: %s\n", err_msg);
4019 sqlite3_free (err_msg);
4020 return 0;
4021 }
4022 sql = "CREATE TRIGGER serstl_coverage_name_update\n"
4023 "BEFORE UPDATE OF 'coverage_name' ON 'SE_raster_styled_layers'\nFOR EACH ROW BEGIN\n"
4024 "SELECT RAISE(ABORT,'update on SE_raster_styled_layers violates constraint: "
4025 "coverage_name value must not contain a single quote')\n"
4026 "WHERE NEW.coverage_name LIKE ('%''%');\n"
4027 "SELECT RAISE(ABORT,'update on SE_raster_styled_layers violates constraint: "
4028 "coverage_name value must not contain a double quote')\n"
4029 "WHERE NEW.coverage_name LIKE ('%\"%');\n"
4030 "SELECT RAISE(ABORT,'update on SE_raster_styled_layers violates constraint: "
4031 "coverage_name value must be lower case')\n"
4032 "WHERE NEW.coverage_name <> lower(NEW.coverage_name);\nEND";
4033 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4034 if (ret != SQLITE_OK)
4035 {
4036 spatialite_e ("SQL error: %s\n", err_msg);
4037 sqlite3_free (err_msg);
4038 return 0;
4039 }
4040 }
4041 return 1;
4042 }
4043
4044 SPATIALITE_PRIVATE int
create_raster_styled_layers(sqlite3 * sqlite)4045 create_raster_styled_layers (sqlite3 * sqlite)
4046 {
4047 /* creating the SE_raster_styled_layers table */
4048 char *sql;
4049 int ret;
4050 char *err_msg = NULL;
4051 sql = "CREATE TABLE SE_raster_styled_layers (\n"
4052 "coverage_name TEXT NOT NULL,\n"
4053 "style_id INTEGER NOT NULL,\n"
4054 "CONSTRAINT pk_serstl PRIMARY KEY (coverage_name, style_id),\n"
4055 "CONSTRAINT fk_serstl_cov FOREIGN KEY (coverage_name) "
4056 "REFERENCES raster_coverages (coverage_name) ON DELETE CASCADE,\n"
4057 "CONSTRAINT fk_serstl_stl FOREIGN KEY (style_id) "
4058 "REFERENCES SE_raster_styles (style_id) ON DELETE CASCADE)";
4059 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4060 if (ret != SQLITE_OK)
4061 {
4062 spatialite_e ("CREATE TABLE 'SE_raster_styled_layers' error: %s\n",
4063 err_msg);
4064 sqlite3_free (err_msg);
4065 return 0;
4066 }
4067 /* creating the style_id index */
4068 sql = "CREATE INDEX idx_serstl_style ON SE_raster_styled_layers (style_id)";
4069 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4070 if (ret != SQLITE_OK)
4071 {
4072 spatialite_e ("CREATE INDEX 'idx_serstl_style' error: %s\n", err_msg);
4073 sqlite3_free (err_msg);
4074 return 0;
4075 }
4076 if (!create_raster_styled_layers_triggers (sqlite))
4077 return 0;
4078 return 1;
4079 }
4080
4081 SPATIALITE_PRIVATE int
create_external_graphics_view(sqlite3 * sqlite)4082 create_external_graphics_view (sqlite3 * sqlite)
4083 {
4084 /* creating the SE_external_graphics_view view */
4085 char *sql_statement;
4086 int ret;
4087 char *err_msg = NULL;
4088 sql_statement =
4089 sqlite3_mprintf
4090 ("CREATE VIEW SE_external_graphics_view AS\n"
4091 "SELECT xlink_href AS xlink_href, title AS title, "
4092 "abstract AS abstract, resource AS resource, "
4093 "file_name AS file_name, GetMimeType(resource) AS mime_type\n"
4094 "FROM SE_external_graphics");
4095 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4096 sqlite3_free (sql_statement);
4097 if (ret != SQLITE_OK)
4098 {
4099 spatialite_e
4100 ("CREATE VIEW 'SE_external_graphics_view' error: %s\n", err_msg);
4101 sqlite3_free (err_msg);
4102 return 0;
4103 }
4104 return 1;
4105 }
4106
4107 SPATIALITE_PRIVATE int
create_fonts_view(sqlite3 * sqlite)4108 create_fonts_view (sqlite3 * sqlite)
4109 {
4110 /* creating the SE_fonts_view view */
4111 char *sql_statement;
4112 int ret;
4113 char *err_msg = NULL;
4114 sql_statement =
4115 sqlite3_mprintf
4116 ("CREATE VIEW SE_fonts_view AS\n"
4117 "SELECT font_facename AS font_facename, "
4118 "GetFontFamily(font) AS family_name, "
4119 "IsFontBold(font) AS bold, IsFontItalic(font) AS italic, "
4120 "font AS font\nFROM SE_fonts");
4121 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4122 sqlite3_free (sql_statement);
4123 if (ret != SQLITE_OK)
4124 {
4125 spatialite_e ("CREATE VIEW 'SE_fonts_view' error: %s\n", err_msg);
4126 sqlite3_free (err_msg);
4127 return 0;
4128 }
4129 return 1;
4130 }
4131
4132 SPATIALITE_PRIVATE int
create_vector_styles_view(sqlite3 * sqlite)4133 create_vector_styles_view (sqlite3 * sqlite)
4134 {
4135 /* creating the SE_vector_styles_view view */
4136 char *sql_statement;
4137 int ret;
4138 char *err_msg = NULL;
4139 sql_statement =
4140 sqlite3_mprintf ("CREATE VIEW SE_vector_styles_view AS \n"
4141 "SELECT style_name AS name, XB_GetTitle(style) AS title, "
4142 "XB_GetAbstract(style) AS abstract, style AS style, "
4143 "XB_IsSchemaValidated(style) AS schema_validated, "
4144 "XB_GetSchemaURI(style) AS schema_uri\n"
4145 "FROM SE_vector_styles");
4146 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4147 sqlite3_free (sql_statement);
4148 if (ret != SQLITE_OK)
4149 {
4150 spatialite_e
4151 ("CREATE VIEW 'SE_vector_styles_view' error: %s\n", err_msg);
4152 sqlite3_free (err_msg);
4153 return 0;
4154 }
4155 return 1;
4156 }
4157
4158 SPATIALITE_PRIVATE int
create_raster_styles_view(sqlite3 * sqlite)4159 create_raster_styles_view (sqlite3 * sqlite)
4160 {
4161 /* creating the SE_raster_styles_view view */
4162 char *sql_statement;
4163 int ret;
4164 char *err_msg = NULL;
4165 sql_statement =
4166 sqlite3_mprintf ("CREATE VIEW SE_raster_styles_view AS \n"
4167 "SELECT style_name AS name, XB_GetTitle(style) AS title, "
4168 "XB_GetAbstract(style) AS abstract, style AS style, "
4169 "XB_IsSchemaValidated(style) AS schema_validated, "
4170 "XB_GetSchemaURI(style) AS schema_uri\n"
4171 "FROM SE_raster_styles");
4172 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4173 sqlite3_free (sql_statement);
4174 if (ret != SQLITE_OK)
4175 {
4176 spatialite_e
4177 ("CREATE VIEW 'SE_raster_styles_view' error: %s\n", err_msg);
4178 sqlite3_free (err_msg);
4179 return 0;
4180 }
4181 return 1;
4182 }
4183
4184 SPATIALITE_PRIVATE int
create_rl2map_configurations_view(sqlite3 * sqlite)4185 create_rl2map_configurations_view (sqlite3 * sqlite)
4186 {
4187 /* creating the rl2map_configurations_view view */
4188 char *sql_statement;
4189 int ret;
4190 char *err_msg = NULL;
4191 sql_statement =
4192 sqlite3_mprintf ("CREATE VIEW rl2map_configurations_view AS \n"
4193 "SELECT name AS name, XB_GetTitle(config) AS title, "
4194 "XB_GetAbstract(config) AS abstract, config AS config, "
4195 "XB_IsSchemaValidated(config) AS schema_validated, "
4196 "XB_GetSchemaURI(config) AS schema_uri\n"
4197 "FROM rl2map_configurations");
4198 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4199 sqlite3_free (sql_statement);
4200 if (ret != SQLITE_OK)
4201 {
4202 spatialite_e
4203 ("CREATE VIEW 'rl2map_configurations_view' error: %s\n", err_msg);
4204 sqlite3_free (err_msg);
4205 return 0;
4206 }
4207 return 1;
4208 }
4209
4210 SPATIALITE_PRIVATE int
create_vector_styled_layers_view(sqlite3 * sqlite)4211 create_vector_styled_layers_view (sqlite3 * sqlite)
4212 {
4213 /* creating the SE_vector_styled_layers_view view */
4214 char *sql_statement;
4215 int ret;
4216 char *err_msg = NULL;
4217 sql_statement =
4218 sqlite3_mprintf ("CREATE VIEW SE_vector_styled_layers_view AS \n"
4219 "SELECT l.coverage_name AS coverage_name, v.f_table_name AS f_table_name, "
4220 "v.f_geometry_column AS f_geometry_column, l.style_id AS style_id, "
4221 "s.style_name AS name, XB_GetTitle(s.style) AS title, "
4222 "XB_GetAbstract(s.style) AS abstract, s.style AS style, "
4223 "XB_IsSchemaValidated(s.style) AS schema_validated, "
4224 "XB_GetSchemaURI(s.style) AS schema_uri\n"
4225 "FROM SE_vector_styled_layers AS l\n"
4226 "JOIN vector_coverages AS v ON (l.coverage_name = v.coverage_name) "
4227 "JOIN SE_vector_styles AS s ON (l.style_id = s.style_id)");
4228 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4229 sqlite3_free (sql_statement);
4230 if (ret != SQLITE_OK)
4231 {
4232 spatialite_e
4233 ("CREATE VIEW 'SE_vector_styled_layers_view' error: %s\n",
4234 err_msg);
4235 sqlite3_free (err_msg);
4236 return 0;
4237 }
4238 return 1;
4239 }
4240
4241 static int
auto_register_standard_brushes(sqlite3 * sqlite)4242 auto_register_standard_brushes (sqlite3 * sqlite)
4243 {
4244 /* AutoRegistering all Graphic Standard Brushes reguired by RasterLite2 */
4245 char *sql_statement;
4246 int ret;
4247 char *err_msg = NULL;
4248 sql_statement = sqlite3_mprintf ("SELECT SE_AutoRegisterStandardBrushes()");
4249 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4250 sqlite3_free (sql_statement);
4251 if (ret != SQLITE_OK)
4252 {
4253 spatialite_e
4254 ("SELECT SE_AutoRegisterStandardBrushes() error: %s\n", err_msg);
4255 sqlite3_free (err_msg);
4256 return 0;
4257 }
4258 return 1;
4259 }
4260
4261 SPATIALITE_PRIVATE int
create_raster_styled_layers_view(sqlite3 * sqlite)4262 create_raster_styled_layers_view (sqlite3 * sqlite)
4263 {
4264 /* creating the SE_raster_styled_layers_view view */
4265 char *sql_statement;
4266 int ret;
4267 char *err_msg = NULL;
4268 sql_statement =
4269 sqlite3_mprintf ("CREATE VIEW SE_raster_styled_layers_view AS \n"
4270 "SELECT l.coverage_name AS coverage_name, l.style_id AS style_id, "
4271 "s.style_name AS name, XB_GetTitle(s.style) AS title, "
4272 "XB_GetAbstract(s.style) AS abstract, s.style AS style, "
4273 "XB_IsSchemaValidated(s.style) AS schema_validated, "
4274 "XB_GetSchemaURI(s.style) AS schema_uri\n"
4275 "FROM SE_raster_styled_layers AS l\n"
4276 "JOIN SE_raster_styles AS s ON (l.style_id = s.style_id)");
4277 ret = sqlite3_exec (sqlite, sql_statement, NULL, NULL, &err_msg);
4278 sqlite3_free (sql_statement);
4279 if (ret != SQLITE_OK)
4280 {
4281 spatialite_e
4282 ("CREATE VIEW 'SE_raster_styled_layers_view' error: %s\n",
4283 err_msg);
4284 sqlite3_free (err_msg);
4285 return 0;
4286 }
4287 return 1;
4288 }
4289
4290 SPATIALITE_PRIVATE int
createStylingTables_ex(void * p_sqlite,int relaxed,int transaction)4291 createStylingTables_ex (void *p_sqlite, int relaxed, int transaction)
4292 {
4293 /* Creating the SE Styling tables */
4294 const char *tables[15];
4295 int views[15];
4296 const char **p_tbl;
4297 int *p_view;
4298 int ok_table;
4299 sqlite3 *sqlite = p_sqlite;
4300 int ret;
4301
4302 if (transaction)
4303 {
4304 /* starting a Transaction */
4305 ret = sqlite3_exec (p_sqlite, "BEGIN", NULL, NULL, NULL);
4306 if (ret != SQLITE_OK)
4307 goto error;
4308 }
4309
4310 /* checking SLD/SE Styling tables */
4311 tables[0] = "SE_external_graphics";
4312 tables[1] = "SE_fonts";
4313 tables[2] = "SE_vector_styles";
4314 tables[3] = "SE_raster_styles";
4315 tables[4] = "RL2map_configurations";
4316 tables[5] = "SE_vector_styled_layers";
4317 tables[6] = "SE_raster_styled_layers";
4318 tables[7] = "SE_external_graphics_view";
4319 tables[8] = "SE_fonts_view";
4320 tables[9] = "SE_vector_styles_view";
4321 tables[10] = "SE_raster_styles_view";
4322 tables[11] = "RL2map_configurations_view";
4323 tables[12] = "SE_vector_styled_layers_view";
4324 tables[13] = "SE_raster_styled_layers_view";
4325 tables[14] = NULL;
4326 views[0] = 0;
4327 views[1] = 0;
4328 views[2] = 0;
4329 views[3] = 0;
4330 views[4] = 0;
4331 views[5] = 0;
4332 views[6] = 0;
4333 views[7] = 1;
4334 views[8] = 1;
4335 views[9] = 1;
4336 views[10] = 1;
4337 views[11] = 1;
4338 views[12] = 1;
4339 views[13] = 1;
4340 p_tbl = tables;
4341 p_view = views;
4342 while (*p_tbl != NULL)
4343 {
4344 ok_table = check_styling_table (sqlite, *p_tbl, *p_view);
4345 if (ok_table)
4346 goto error;
4347 p_tbl++;
4348 p_view++;
4349 }
4350
4351 /* creating the SLD/SE Styling tables */
4352 if (!check_raster_coverages (sqlite))
4353 {
4354 /* creating the main RasterCoverages table as well */
4355 if (!create_raster_coverages (sqlite))
4356 goto error;
4357 }
4358 #ifdef ENABLE_RTTOPO /* only if RTTOPO is enabled */
4359 if (!check_vector_coverages (sqlite))
4360 {
4361 /* creating both TOPOLOGIES and NETWORKS tables */
4362 do_create_topologies (sqlite);
4363 do_create_networks (sqlite);
4364 /* creating the main VectorCoverages table as well */
4365 if (!create_vector_coverages (sqlite))
4366 goto error;
4367 }
4368 #endif /* end TOPOLOGY conditionals */
4369 if (!create_external_graphics (sqlite))
4370 goto error;
4371 if (!create_fonts (sqlite))
4372 goto error;
4373 if (!create_vector_styles (sqlite, relaxed))
4374 goto error;
4375 if (!create_raster_styles (sqlite, relaxed))
4376 goto error;
4377 if (!create_rl2map_configurations (sqlite, relaxed))
4378 goto error;
4379 if (!create_vector_styled_layers (sqlite))
4380 goto error;
4381 if (!create_raster_styled_layers (sqlite))
4382 goto error;
4383 if (!create_external_graphics_view (sqlite))
4384 goto error;
4385 if (!auto_register_standard_brushes (sqlite))
4386 goto error;
4387 if (!create_fonts_view (sqlite))
4388 goto error;
4389 if (!create_vector_styles_view (sqlite))
4390 goto error;
4391 if (!create_raster_styles_view (sqlite))
4392 goto error;
4393 if (!create_rl2map_configurations_view (sqlite))
4394 goto error;
4395 if (!create_vector_styled_layers_view (sqlite))
4396 goto error;
4397 if (!create_raster_styled_layers_view (sqlite))
4398 goto error;
4399
4400 if (transaction)
4401 {
4402 /* confirming the still pending Transaction */
4403 ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, NULL);
4404 if (ret != SQLITE_OK)
4405 goto error;
4406 }
4407 return 1;
4408
4409 error:
4410 return 0;
4411 }
4412
4413 SPATIALITE_PRIVATE int
reCreateStylingTriggers(void * p_sqlite,int relaxed,int transaction)4414 reCreateStylingTriggers (void *p_sqlite, int relaxed, int transaction)
4415 {
4416 /* (re)Creating the SE Styling triggers */
4417 sqlite3 *sqlite = p_sqlite;
4418 int ret;
4419
4420 if (transaction)
4421 {
4422 /* starting a Transaction */
4423 ret = sqlite3_exec (p_sqlite, "BEGIN", NULL, NULL, NULL);
4424 if (ret != SQLITE_OK)
4425 goto error;
4426 }
4427
4428 /* (re)creating the main RasterCoverages trigger as well */
4429 drop_raster_coverages_triggers (sqlite);
4430 if (!create_raster_coverages_triggers (sqlite))
4431 goto error;
4432
4433 #ifdef ENABLE_RTTOPO /* only if RTTOPO enabled */
4434 /* (re)creating both TOPOLOGIES and NETWORKS triggers */
4435 drop_topologies_triggers (sqlite);
4436 if (!do_create_topologies_triggers (sqlite))
4437 goto error;
4438 drop_networks_triggers (sqlite);
4439 if (!do_create_networks_triggers (sqlite))
4440 goto error;
4441
4442 /* (re)creating the main VectorCoverages table as well */
4443 drop_vector_coverages_triggers (sqlite);
4444 if (!create_vector_coverages_triggers (sqlite))
4445 goto error;
4446 #endif /* end RTTOPO conditional */
4447
4448 drop_styling_triggers (sqlite);
4449 if (!create_external_graphics_triggers (sqlite))
4450 goto error;
4451 if (!create_fonts_triggers (sqlite))
4452 goto error;
4453 if (!create_vector_styles_triggers (sqlite, relaxed))
4454 goto error;
4455 if (!create_raster_styles_triggers (sqlite, relaxed))
4456 goto error;
4457 if (!create_vector_styled_layers_triggers (sqlite))
4458 goto error;
4459 if (!create_raster_styled_layers_triggers (sqlite))
4460 goto error;
4461
4462 if (transaction)
4463 {
4464 /* confirming the still pending Transaction */
4465 ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, NULL);
4466 if (ret != SQLITE_OK)
4467 goto error;
4468 }
4469 return 1;
4470
4471 error:
4472 return 0;
4473 }
4474
4475 SPATIALITE_PRIVATE int
createStylingTables(void * p_sqlite,int relaxed)4476 createStylingTables (void *p_sqlite, int relaxed)
4477 {
4478 /* Creating the SE Styling tables */
4479 return createStylingTables_ex (p_sqlite, relaxed, 0);
4480 }
4481
4482 static int
check_iso_metadata_table(sqlite3 * sqlite,const char * table,int is_view)4483 check_iso_metadata_table (sqlite3 * sqlite, const char *table, int is_view)
4484 {
4485 /* checking if some ISO Metadata-related table/view already exists */
4486 int exists = 0;
4487 char *sql_statement;
4488 char *errMsg = NULL;
4489 int ret;
4490 char **results;
4491 int rows;
4492 int columns;
4493 int i;
4494 sql_statement =
4495 sqlite3_mprintf ("SELECT name FROM sqlite_master WHERE type = '%s'"
4496 "AND Upper(name) = Upper(%Q)",
4497 (!is_view) ? "table" : "view", table);
4498 ret =
4499 sqlite3_get_table (sqlite, sql_statement, &results, &rows, &columns,
4500 &errMsg);
4501 sqlite3_free (sql_statement);
4502 if (ret != SQLITE_OK)
4503 {
4504 sqlite3_free (errMsg);
4505 return 0;
4506 }
4507 for (i = 1; i <= rows; i++)
4508 exists = 1;
4509 sqlite3_free_table (results);
4510 return exists;
4511 }
4512
4513 SPATIALITE_PRIVATE int
create_iso_metadata(sqlite3 * sqlite,int relaxed)4514 create_iso_metadata (sqlite3 * sqlite, int relaxed)
4515 {
4516 /* creating the ISO_metadata table */
4517 char *sql;
4518 int ret;
4519 char *err_msg = NULL;
4520 sql = "CREATE TABLE ISO_metadata (\n"
4521 "id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
4522 "md_scope TEXT NOT NULL DEFAULT 'dataset',\n"
4523 "metadata BLOB NOT NULL DEFAULT (zeroblob(4)),\n"
4524 "fileId TEXT,\nparentId TEXT)";
4525 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4526 if (ret != SQLITE_OK)
4527 {
4528 spatialite_e ("CREATE TABLE 'ISO_metadata' error: %s\n", err_msg);
4529 sqlite3_free (err_msg);
4530 return 0;
4531 }
4532 /* adding the Geometry column */
4533 sql =
4534 "SELECT AddGeometryColumn('ISO_metadata', 'geometry', 4326, 'MULTIPOLYGON', 'XY')";
4535 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4536 if (ret != SQLITE_OK)
4537 {
4538 spatialite_e
4539 (" AddGeometryColumn 'ISO_metadata'.'geometry' error:%s\n",
4540 err_msg);
4541 sqlite3_free (err_msg);
4542 return 0;
4543 }
4544 /* adding a Spatial Index */
4545 sql = "SELECT CreateSpatialIndex ('ISO_metadata', 'geometry')";
4546 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4547 if (ret != SQLITE_OK)
4548 {
4549 spatialite_e
4550 ("CreateSpatialIndex 'ISO_metadata'.'geometry' error: %s\n",
4551 err_msg);
4552 sqlite3_free (err_msg);
4553 return 0;
4554 }
4555 /* creating the ISO_metadata triggers */
4556 sql = "CREATE TRIGGER 'ISO_metadata_md_scope_insert'\n"
4557 "BEFORE INSERT ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4558 "SELECT RAISE(ROLLBACK, 'insert on table ISO_metadata violates constraint: "
4559 "md_scope must be one of ''undefined'' | ''fieldSession'' | ''collectionSession'' "
4560 "| ''series'' | ''dataset'' | ''featureType'' | ''feature'' | ''attributeType'' "
4561 "| ''attribute'' | ''tile'' | ''model'' | ''catalogue'' | ''schema'' "
4562 "| ''taxonomy'' | ''software'' | ''service'' | ''collectionHardware'' "
4563 "| ''nonGeographicDataset'' | ''dimensionGroup''')\n"
4564 "WHERE NOT(NEW.md_scope IN ('undefined','fieldSession','collectionSession',"
4565 "'series','dataset','featureType','feature','attributeType','attribute',"
4566 "'tile','model','catalogue','schema','taxonomy','software','service',"
4567 "'collectionHardware','nonGeographicDataset','dimensionGroup'));\nEND";
4568 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4569 if (ret != SQLITE_OK)
4570 {
4571 spatialite_e ("SQL error: %s\n", err_msg);
4572 sqlite3_free (err_msg);
4573 return 0;
4574 }
4575 sql = "CREATE TRIGGER 'ISO_metadata_md_scope_update'\n"
4576 "BEFORE UPDATE OF 'md_scope' ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4577 "SELECT RAISE(ROLLBACK, 'update on table ISO_metadata violates constraint: "
4578 "md_scope must be one of ''undefined'' | ''fieldSession'' | ''collectionSession'' "
4579 "| ''series'' | ''dataset'' | ''featureType'' | ''feature'' | ''attributeType'' |"
4580 " ''attribute'' | ''tile'' | ''model'' | ''catalogue'' | ''schema'' "
4581 "| ''taxonomy'' | ''software'' | ''service'' | ''collectionHardware'' "
4582 "| ''nonGeographicDataset'' | ''dimensionGroup''')\n"
4583 "WHERE NOT(NEW.md_scope IN ('undefined','fieldSession','collectionSession',"
4584 "'series','dataset','featureType','feature','attributeType','attribute',"
4585 "'tile','model','catalogue','schema','taxonomy','software','service',"
4586 "'collectionHardware','nonGeographicDataset','dimensionGroup'));\nEND";
4587 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4588 if (ret != SQLITE_OK)
4589 {
4590 spatialite_e ("SQL error: %s\n", err_msg);
4591 sqlite3_free (err_msg);
4592 return 0;
4593 }
4594 sql = "CREATE TRIGGER 'ISO_metadata_fileIdentifier_insert'\n"
4595 "AFTER INSERT ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4596 "UPDATE ISO_metadata SET fileId = XB_GetFileId(NEW.metadata), "
4597 "parentId = XB_GetParentId(NEW.metadata), "
4598 "geometry = XB_GetGeometry(NEW.metadata) WHERE id = NEW.id;\n"
4599 "UPDATE ISO_metadata_reference "
4600 "SET md_parent_id = GetIsoMetadataId(NEW.parentId) "
4601 "WHERE md_file_id = NEW.id;\nEND";
4602 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4603 if (ret != SQLITE_OK)
4604 {
4605 spatialite_e ("SQL error: %s\n", err_msg);
4606 sqlite3_free (err_msg);
4607 return 0;
4608 }
4609 sql = "CREATE TRIGGER 'ISO_metadata_fileIdentifier_update'\n"
4610 "AFTER UPDATE ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4611 "UPDATE ISO_metadata SET fileId = XB_GetFileId(NEW.metadata), "
4612 "parentId = XB_GetParentId(NEW.metadata), "
4613 "geometry = XB_GetGeometry(NEW.metadata) WHERE id = NEW.id;\n"
4614 "UPDATE ISO_metadata_reference "
4615 "SET md_parent_id = GetIsoMetadataId(NEW.parentId) "
4616 "WHERE md_file_id = NEW.id;\nEND";
4617 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4618 if (ret != SQLITE_OK)
4619 {
4620 spatialite_e ("SQL error: %s\n", err_msg);
4621 sqlite3_free (err_msg);
4622 return 0;
4623 }
4624 if (relaxed == 0)
4625 {
4626 /* strong trigger - imposing XML schema validation */
4627 sql = "CREATE TRIGGER ISO_metadata_insert\n"
4628 "BEFORE INSERT ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4629 "SELECT RAISE(ABORT,'insert on ISO_metadata violates constraint: "
4630 "not a valid ISO Metadata XML')\n"
4631 "WHERE XB_IsIsoMetadata(NEW.metadata) <> 1 AND NEW.id <> 0;\n"
4632 "SELECT RAISE(ABORT,'insert on ISO_metadata violates constraint: "
4633 "not an XML Schema Validated ISO Metadata')\n"
4634 "WHERE XB_IsSchemaValidated(NEW.metadata) <> 1 AND NEW.id <> 0;\nEND";
4635 }
4636 else
4637 {
4638 /* relaxed trigger - not imposing XML schema validation */
4639 sql = "CREATE TRIGGER ISO_metadata_insert\n"
4640 "BEFORE INSERT ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4641 "SELECT RAISE(ABORT,'insert on ISO_metadata violates constraint: "
4642 "not a valid ISO Metadata XML')\n"
4643 "WHERE XB_IsIsoMetadata(NEW.metadata) <> 1 AND NEW.id <> 0;\nEND";
4644 }
4645 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4646 if (ret != SQLITE_OK)
4647 {
4648 spatialite_e ("SQL error: %s\n", err_msg);
4649 sqlite3_free (err_msg);
4650 return 0;
4651 }
4652 if (relaxed == 0)
4653 {
4654 /* strong trigger - imposing XML schema validation */
4655 sql = "CREATE TRIGGER ISO_metadata_update\n"
4656 "BEFORE UPDATE ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4657 "SELECT RAISE(ABORT,'update on ISO_metadata violates constraint: "
4658 "not a valid ISO Metadata XML')\n"
4659 "WHERE XB_IsIsoMetadata(NEW.metadata) <> 1 AND NEW.id <> 0;\n"
4660 "SELECT RAISE(ABORT,'update on ISO_metadata violates constraint: "
4661 "not an XML Schema Validated ISO Metadata')\n"
4662 "WHERE XB_IsSchemaValidated(NEW.metadata) <> 1 AND NEW.id <> 0;\nEND";
4663 }
4664 else
4665 {
4666 /* relaxed trigger - not imposing XML schema validation */
4667 sql = "CREATE TRIGGER ISO_metadata_update\n"
4668 "BEFORE UPDATE ON 'ISO_metadata'\nFOR EACH ROW BEGIN\n"
4669 "SELECT RAISE(ABORT,'update on ISO_metadata violates constraint: "
4670 "not a valid ISO Metadata XML')\n"
4671 "WHERE XB_IsIsoMetadata(NEW.metadata) <> 1 AND NEW.id <> 0;\nEND";
4672 }
4673 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4674 if (ret != SQLITE_OK)
4675 {
4676 spatialite_e ("SQL error: %s\n", err_msg);
4677 sqlite3_free (err_msg);
4678 return 0;
4679 }
4680 /* creating any Index on ISO_metadata */
4681 sql = "CREATE UNIQUE INDEX idx_ISO_metadata_ids ON "
4682 "ISO_metadata (fileId)";
4683 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4684 if (ret != SQLITE_OK)
4685 {
4686 spatialite_e ("Create Index 'idx_ISO_metadata_ids' error: %s\n",
4687 err_msg);
4688 sqlite3_free (err_msg);
4689 return 0;
4690 }
4691 sql = "CREATE INDEX idx_ISO_metadata_parents ON " "ISO_metadata (parentId)";
4692 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4693 if (ret != SQLITE_OK)
4694 {
4695 spatialite_e ("Create Index 'idx_ISO_metadata_parents' error: %s\n",
4696 err_msg);
4697 sqlite3_free (err_msg);
4698 return 0;
4699 }
4700 return 1;
4701 }
4702
4703 SPATIALITE_PRIVATE int
create_iso_metadata_reference(sqlite3 * sqlite)4704 create_iso_metadata_reference (sqlite3 * sqlite)
4705 {
4706 /* creating the ISO_metadata_reference table */
4707 char *sql;
4708 int ret;
4709 char *err_msg = NULL;
4710 sql = "CREATE TABLE ISO_metadata_reference (\n"
4711 "reference_scope TEXT NOT NULL DEFAULT 'table',\n"
4712 "table_name TEXT NOT NULL DEFAULT 'undefined',\n"
4713 "column_name TEXT NOT NULL DEFAULT 'undefined',\n"
4714 "row_id_value INTEGER NOT NULL DEFAULT 0,\n"
4715 "timestamp TEXT NOT NULL DEFAULT ("
4716 "strftime('%Y-%m-%dT%H:%M:%fZ',CURRENT_TIMESTAMP)),\n"
4717 "md_file_id INTEGER NOT NULL DEFAULT 0,\n"
4718 "md_parent_id INTEGER NOT NULL DEFAULT 0,\n"
4719 "CONSTRAINT fk_isometa_mfi FOREIGN KEY (md_file_id) "
4720 "REFERENCES ISO_metadata(id),\n"
4721 "CONSTRAINT fk_isometa_mpi FOREIGN KEY (md_parent_id) "
4722 "REFERENCES ISO_metadata(id))";
4723 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4724 if (ret != SQLITE_OK)
4725 {
4726 spatialite_e ("CREATE TABLE 'ISO_metadata_reference' error: %s\n",
4727 err_msg);
4728 sqlite3_free (err_msg);
4729 return 0;
4730 }
4731 /* creating the ISO_metadata_reference triggers */
4732 sql = "CREATE TRIGGER 'ISO_metadata_reference_scope_insert'\n"
4733 "BEFORE INSERT ON 'ISO_metadata_reference'\nFOR EACH ROW BEGIN\n"
4734 "SELECT RAISE(ROLLBACK, 'insert on table ISO_metadata_reference violates constraint: "
4735 "reference_scope must be one of ''table'' | ''column'' | ''row'' | ''row/col''')\n"
4736 "WHERE NOT NEW.reference_scope IN ('table','column','row','row/col');\nEND";
4737 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4738 if (ret != SQLITE_OK)
4739 {
4740 spatialite_e ("SQL error: %s\n", err_msg);
4741 sqlite3_free (err_msg);
4742 return 0;
4743 }
4744 sql = "CREATE TRIGGER 'ISO_metadata_reference_scope_update'\n"
4745 "BEFORE UPDATE OF 'reference_scope' ON 'ISO_metadata_reference'\n"
4746 "FOR EACH ROW BEGIN\n"
4747 "SELECT RAISE(ROLLBACK, 'update on table ISO_metadata_reference violates constraint: "
4748 "referrence_scope must be one of ''table'' | ''column'' | ''row'' | ''row/col''')\n"
4749 "WHERE NOT NEW.reference_scope IN ('table','column','row','row/col');\nEND";
4750 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4751 if (ret != SQLITE_OK)
4752 {
4753 spatialite_e ("SQL error: %s\n", err_msg);
4754 sqlite3_free (err_msg);
4755 return 0;
4756 }
4757 sql = "CREATE TRIGGER 'ISO_metadata_reference_table_name_insert'\n"
4758 "BEFORE INSERT ON 'ISO_metadata_reference'\nFOR EACH ROW BEGIN\n"
4759 "SELECT RAISE(ROLLBACK, 'insert on table ISO_metadata_reference violates constraint: "
4760 "table_name must be the name of a table in geometry_columns')\n"
4761 "WHERE NOT NEW.table_name IN (\n"
4762 "SELECT f_table_name AS table_name FROM geometry_columns);\nEND";
4763 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4764 if (ret != SQLITE_OK)
4765 {
4766 spatialite_e ("SQL error: %s\n", err_msg);
4767 sqlite3_free (err_msg);
4768 return 0;
4769 }
4770 sql = "CREATE TRIGGER 'ISO_metadata_reference_table_name_update'\n"
4771 "BEFORE UPDATE OF 'table_name' ON 'ISO_metadata_reference'\n"
4772 "FOR EACH ROW BEGIN\n"
4773 "SELECT RAISE(ROLLBACK, 'update on table ISO_metadata_reference violates constraint: "
4774 "table_name must be the name of a table in geometry_columns')\n"
4775 "WHERE NOT NEW.table_name IN (\n"
4776 "SELECT f_table_name AS table_name FROM geometry_columns);\nEND";
4777 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4778 if (ret != SQLITE_OK)
4779 {
4780 spatialite_e ("SQL error: %s\n", err_msg);
4781 sqlite3_free (err_msg);
4782 return 0;
4783 }
4784 sql = "CREATE TRIGGER 'ISO_metadata_reference_row_id_value_insert'\n"
4785 "BEFORE INSERT ON 'ISO_metadata_reference'\nFOR EACH ROW BEGIN\n"
4786 "SELECT RAISE(ROLLBACK, 'insert on ISO_table ISO_metadata_reference violates constraint: "
4787 "row_id_value must be 0 when reference_scope is ''table'' or ''column''')\n"
4788 "WHERE NEW.reference_scope IN ('table','column') AND NEW.row_id_value <> 0;\n"
4789 "SELECT RAISE(ROLLBACK, 'insert on table ISO_metadata_reference violates constraint: "
4790 "row_id_value must exist in specified table when reference_scope is ''row'' or ''row/col''')\n"
4791 "WHERE NEW.reference_scope IN ('row','row/col') AND NOT EXISTS\n"
4792 "(SELECT rowid FROM (SELECT NEW.table_name AS table_name) "
4793 "WHERE rowid = NEW.row_id_value);\nEND";
4794 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4795 if (ret != SQLITE_OK)
4796 {
4797 spatialite_e ("SQL error: %s\n", err_msg);
4798 sqlite3_free (err_msg);
4799 return 0;
4800 }
4801 sql = "CREATE TRIGGER 'ISO_metadata_reference_row_id_value_update'\n"
4802 "BEFORE UPDATE OF 'row_id_value' ON 'ISO_metadata_reference'\n"
4803 "FOR EACH ROW BEGIN\n"
4804 "SELECT RAISE(ROLLBACK, 'update on table ISO_metadata_reference violates constraint: "
4805 "row_id_value must be 0 when reference_scope is ''table'' or ''column''')\n"
4806 "WHERE NEW.reference_scope IN ('table','column') AND NEW.row_id_value <> 0;\n"
4807 "SELECT RAISE(ROLLBACK, 'update on ISO_table metadata_reference violates constraint: "
4808 "row_id_value must exist in specified table when reference_scope is ''row'' or ''row/col''')\n"
4809 "WHERE NEW.reference_scope IN ('row','row/col') AND NOT EXISTS\n"
4810 "(SELECT rowid FROM (SELECT NEW.table_name AS table_name) "
4811 "WHERE rowid = NEW.row_id_value);\nEND";
4812 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4813 if (ret != SQLITE_OK)
4814 {
4815 spatialite_e ("SQL error: %s\n", err_msg);
4816 sqlite3_free (err_msg);
4817 return 0;
4818 }
4819 sql = "CREATE TRIGGER 'ISO_metadata_reference_timestamp_insert'\n"
4820 "BEFORE INSERT ON 'ISO_metadata_reference'\nFOR EACH ROW BEGIN\n"
4821 "SELECT RAISE(ROLLBACK, 'insert on table ISO_metadata_reference violates constraint: "
4822 "timestamp must be a valid time in ISO 8601 ''yyyy-mm-ddThh:mm:ss.cccZ'' form')\n"
4823 "WHERE NOT (NEW.timestamp GLOB'[1-2][0-9][0-9][0-9]-[0-1][0-9]-[1-3][0-9]T"
4824 "[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]Z' AND strftime('%s',"
4825 "NEW.timestamp) NOT NULL);\nEND";
4826 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4827 if (ret != SQLITE_OK)
4828 {
4829 spatialite_e ("SQL error: %s\n", err_msg);
4830 sqlite3_free (err_msg);
4831 return 0;
4832 }
4833 sql = "CREATE TRIGGER 'ISO_metadata_reference_timestamp_update'\n"
4834 "BEFORE UPDATE OF 'timestamp' ON 'ISO_metadata_reference'\n"
4835 "FOR EACH ROW BEGIN\n"
4836 "SELECT RAISE(ROLLBACK, 'update on table ISO_metadata_reference violates constraint: "
4837 "timestamp must be a valid time in ISO 8601 ''yyyy-mm-ddThh:mm:ss.cccZ'' form')\n"
4838 "WHERE NOT (NEW.timestamp GLOB'[1-2][0-9][0-9][0-9]-[0-1][0-9]-[1-3][0-9]T"
4839 "[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]Z' AND strftime('%s',"
4840 "NEW.timestamp) NOT NULL);\nEND";
4841 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4842 if (ret != SQLITE_OK)
4843 {
4844 spatialite_e ("SQL error: %s\n", err_msg);
4845 sqlite3_free (err_msg);
4846 return 0;
4847 }
4848 /* creating any Index on ISO_metadata_reference */
4849 sql = "CREATE INDEX idx_ISO_metadata_reference_ids ON "
4850 "ISO_metadata_reference (md_file_id)";
4851 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4852 if (ret != SQLITE_OK)
4853 {
4854 spatialite_e
4855 ("Create Index 'idx_ISO_metadata_reference_ids' error: %s\n",
4856 err_msg);
4857 sqlite3_free (err_msg);
4858 return 0;
4859 }
4860 sql = "CREATE INDEX idx_ISO_metadata_reference_parents ON "
4861 "ISO_metadata_reference (md_parent_id)";
4862 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4863 if (ret != SQLITE_OK)
4864 {
4865 spatialite_e
4866 ("Create Index 'idx_ISO_metadata_reference_parents' error: %s\n",
4867 err_msg);
4868 sqlite3_free (err_msg);
4869 return 0;
4870 }
4871 return 1;
4872 }
4873
4874 SPATIALITE_PRIVATE int
create_iso_metadata_view(sqlite3 * sqlite)4875 create_iso_metadata_view (sqlite3 * sqlite)
4876 {
4877 /* creating the ISO_metadata_view view */
4878 char *sql;
4879 int ret;
4880 char *err_msg = NULL;
4881 sql = "CREATE VIEW ISO_metadata_view AS\n"
4882 "SELECT id AS id, md_scope AS md_scope, XB_GetTitle(metadata) AS title, "
4883 "XB_GetAbstract(metadata) AS abstract, geometry AS geometry, "
4884 "fileId AS fileIdentifier, parentId AS parentIdentifier, metadata AS metadata, "
4885 "XB_IsSchemaValidated(metadata) AS schema_validated, "
4886 "XB_GetSchemaURI(metadata) AS metadata_schema_URI\n"
4887 "FROM ISO_metadata";
4888 ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
4889 if (ret != SQLITE_OK)
4890 {
4891 spatialite_e ("CREATE VIEW 'ISO_metadata_view' error: %s\n", err_msg);
4892 sqlite3_free (err_msg);
4893 return 0;
4894 }
4895 return 1;
4896 }
4897
4898 SPATIALITE_PRIVATE int
createIsoMetadataTables(void * p_sqlite,int relaxed)4899 createIsoMetadataTables (void *p_sqlite, int relaxed)
4900 {
4901 /* Creating the ISO Metadata tables */
4902 const char *tables[4];
4903 int views[3];
4904 const char **p_tbl;
4905 int *p_view;
4906 int ok_table;
4907 int ret;
4908 char *err_msg = NULL;
4909 sqlite3 *sqlite = p_sqlite;
4910
4911 /* checking ISO Metadata tables */
4912 tables[0] = "ISO_metadata";
4913 tables[1] = "ISO_metadata_reference";
4914 tables[2] = "ISO_metadata_view";
4915 tables[3] = NULL;
4916 views[0] = 0;
4917 views[1] = 0;
4918 views[2] = 1;
4919 p_tbl = tables;
4920 p_view = views;
4921 while (*p_tbl != NULL)
4922 {
4923 ok_table = check_iso_metadata_table (sqlite, *p_tbl, *p_view);
4924 if (ok_table)
4925 {
4926 spatialite_e
4927 ("CreateIsoMetadataTables() error: table '%s' already exists\n",
4928 *p_tbl);
4929 goto error;
4930 }
4931 p_tbl++;
4932 p_view++;
4933 }
4934
4935 /* creating the ISO Metadata tables */
4936 if (!create_iso_metadata (sqlite, relaxed))
4937 goto error;
4938 if (!create_iso_metadata_reference (sqlite))
4939 goto error;
4940 if (!create_iso_metadata_view (sqlite))
4941 goto error;
4942 /* inserting the default "undef" row into ISO_metadata */
4943 ret =
4944 sqlite3_exec (sqlite,
4945 "INSERT INTO ISO_metadata (id, md_scope) VALUES (0, 'undefined')",
4946 NULL, NULL, &err_msg);
4947 if (ret != SQLITE_OK)
4948 {
4949 spatialite_e
4950 ("Insert default 'undefined' ISO_metadata row - error: %s\n",
4951 err_msg);
4952 sqlite3_free (err_msg);
4953 return 0;
4954 }
4955 return 1;
4956
4957 error:
4958 return 0;
4959 }
4960
4961 #endif /* end including LIBXML2 */
4962
4963 static int
do_check_if_table_exists(sqlite3 * sqlite,const char * table)4964 do_check_if_table_exists (sqlite3 * sqlite, const char *table)
4965 {
4966 /* checking if a Table is already defined */
4967 int ret;
4968 int i;
4969 char **results;
4970 int rows;
4971 int columns;
4972 int count = 0;
4973 char *qtable = gaiaDoubleQuotedSql (table);
4974 char *sql = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", qtable);
4975 free (qtable);
4976 ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
4977 sqlite3_free (sql);
4978 if (ret != SQLITE_OK)
4979 return 0;
4980 for (i = 1; i <= rows; i++)
4981 count++;
4982 sqlite3_free_table (results);
4983 if (count == 0)
4984 return 0;
4985 return 1;
4986 }
4987
4988 SPATIALITE_PRIVATE int
createMissingSystemTables(sqlite3 * sqlite,const void * cache,int relaxed,int transaction,char ** err_msg)4989 createMissingSystemTables (sqlite3 * sqlite, const void *cache, int relaxed,
4990 int transaction, char **err_msg)
4991 {
4992 /* attempting to create all missing System Tables required by version 5 */
4993 #ifndef ENABLE_LIBXML2 /* unsupported LIBXML2 */
4994 *err_msg =
4995 sqlite3_mprintf
4996 ("this build does not support LIBXML2 ... cowardly quitting");
4997 return 0;
4998 #endif
4999 #ifndef ENABLE_RTTOPO /* unsipported RTTOPO */
5000 *err_msg =
5001 sqlite3_mprintf
5002 ("this build does not support RTTOPO ... cowardly quitting");
5003 return 0;
5004 #endif
5005
5006 #ifdef ENABLE_LIBXML2 /* only if LibXML2 support is available */
5007 #ifdef ENABLE_RTTOPO /* RTTOPO is supported */
5008 int ret;
5009 struct str_tables
5010 {
5011 const char *table_name;
5012 int (*creator) (sqlite3 * sqlite);
5013 int (*creator_void) (void *sqlite);
5014 int (*creator_relaxed) (sqlite3 * sqlite, int relaxed);
5015 int (*creator_cache) (sqlite3 * sqlite, const void *cache);
5016 };
5017 static struct str_tables tables[] = {
5018 {"data_licenses", create_data_licenses, NULL, NULL, NULL},
5019 {"raster_coverages", create_raster_coverages, NULL, NULL, NULL},
5020 {"raster_coverages_keyword", create_raster_coverages, NULL, NULL, NULL},
5021 {"raster_coverages_srid", create_raster_coverages, NULL, NULL, NULL},
5022 {"raster_coverages_ref_sys", create_raster_coverages, NULL, NULL, NULL},
5023 {"vector_coverages", create_vector_coverages, NULL, NULL, NULL},
5024 {"vector_coverages_keyword", create_vector_coverages, NULL, NULL, NULL},
5025 {"vector_coverages_srid", create_vector_coverages, NULL, NULL, NULL},
5026 {"vector_coverages_ref_sys", create_vector_coverages, NULL, NULL, NULL},
5027 {"wms_getcapabilities", create_wms_tables, NULL, NULL, NULL},
5028 {"wms_getmap", create_wms_tables, NULL, NULL, NULL},
5029 {"wms_ref_sys", create_wms_tables, NULL, NULL, NULL},
5030 {"wms_settings", create_wms_tables, NULL, NULL, NULL},
5031 {"topologies", NULL, do_create_topologies, NULL, NULL},
5032 {"networks", NULL, do_create_networks, NULL, NULL},
5033 {"SE_external_graphics", create_external_graphics, NULL, NULL, NULL},
5034 {"SE_external_graphics_view", create_external_graphics_view, NULL, NULL,
5035 NULL},
5036 {"SE_fonts", create_fonts, NULL, NULL, NULL},
5037 {"SE_fonts_view", create_fonts_view, NULL, NULL, NULL},
5038 {"SE_raster_styles", NULL, NULL, create_raster_styles, NULL},
5039 {"SE_raster_styles_view", create_raster_styles_view, NULL, NULL, NULL},
5040 {"SE_raster_styled_layers", create_raster_styled_layers, NULL, NULL,
5041 NULL},
5042 {"SE_raster_styled_layers_view", create_raster_styled_layers_view, NULL,
5043 NULL, NULL},
5044 {"SE_vector_styles", NULL, NULL, create_vector_styles, NULL},
5045 {"SE_vector_styles_view", create_vector_styles_view, NULL, NULL, NULL},
5046 {"SE_vector_styled_layers", create_vector_styled_layers, NULL, NULL,
5047 NULL},
5048 {"SE_vector_styled_layers_view", create_vector_styled_layers_view, NULL,
5049 NULL, NULL},
5050 {"ISO_metadata", NULL, NULL, create_iso_metadata, NULL},
5051 {"ISO_metadata_reference", create_iso_metadata_reference, NULL, NULL,
5052 NULL},
5053 {"ISO_metadata_view", create_iso_metadata_view, NULL, NULL, NULL},
5054 {"stored_procedures", NULL, NULL, NULL, gaia_stored_proc_create_tables},
5055 {"stored_variables", NULL, NULL, NULL, gaia_stored_proc_create_tables},
5056 {"rl2map_configurations", NULL, NULL, create_rl2map_configurations,
5057 NULL},
5058 {"rl2map_configurations_view", create_rl2map_configurations_view, NULL,
5059 NULL, NULL},
5060 {NULL, NULL, NULL, NULL, NULL}
5061 };
5062 struct str_tables *p_table = tables;
5063
5064 if (transaction)
5065 {
5066 /* starting a Transaction */
5067 ret = sqlite3_exec (sqlite, "BEGIN", NULL, NULL, NULL);
5068 if (ret != SQLITE_OK)
5069 {
5070 *err_msg =
5071 sqlite3_mprintf ("Unable to start a Transaction (BEGIN)");
5072 return 0;
5073 }
5074 }
5075
5076 while (p_table->table_name != NULL)
5077 {
5078 int exists = do_check_if_table_exists (sqlite, p_table->table_name);
5079 if (!exists)
5080 {
5081 if (p_table->creator != NULL)
5082 ret = p_table->creator (sqlite);
5083 if (p_table->creator_void != NULL)
5084 ret = p_table->creator_void ((void *) sqlite);
5085 if (p_table->creator_relaxed != NULL)
5086 ret = p_table->creator_relaxed (sqlite, relaxed);
5087 if (p_table->creator_cache != NULL)
5088 ret = p_table->creator_cache (sqlite, cache);
5089 if (!ret)
5090 {
5091 *err_msg =
5092 sqlite3_mprintf ("Unable to create \"%s\"",
5093 p_table->table_name);
5094 return 0;
5095 }
5096 }
5097 if (strcmp (p_table->table_name, "SE_external_graphics") == 0)
5098 {
5099 /* autoregistering all Standard Brushes */
5100 ret =
5101 sqlite3_exec (sqlite,
5102 "SELECT SE_AutoRegisterStandardBrushes()",
5103 NULL, NULL, NULL);
5104 if (ret != SQLITE_OK)
5105 {
5106 *err_msg =
5107 sqlite3_mprintf
5108 ("Unexpected failure when registering Standard Brushes");
5109 return 0;
5110 }
5111 }
5112 p_table++;
5113 }
5114
5115 if (transaction)
5116 {
5117 /* confirming the still pending Transaction */
5118 ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, NULL);
5119 if (ret != SQLITE_OK)
5120 {
5121 *err_msg =
5122 sqlite3_mprintf
5123 ("Unable to confirm a Transaction (COMMIT)");
5124 return 0;
5125 }
5126 }
5127
5128 /* full success */
5129 *err_msg = NULL;
5130 return 1;
5131 #endif /* end RTTOPO */
5132 #endif /* end LIBXML2 */
5133 }
5134