1 /*
2
3 check_gpkgVirtual.c - Test case for GeoPackage Extensions
4
5 Author: Sandro Furieri <a.furieri@lqt.it>
6
7 ------------------------------------------------------------------------------
8
9 Version: MPL 1.1/GPL 2.0/LGPL 2.1
10
11 The contents of this file are subject to the Mozilla Public License Version
12 1.1 (the "License"); you may not use this file except in compliance with
13 the License. You may obtain a copy of the License at
14 http://www.mozilla.org/MPL/
15
16 Software distributed under the License is distributed on an "AS IS" basis,
17 WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
18 for the specific language governing rights and limitations under the
19 License.
20
21 The Original Code is GeoPackage extensions
22
23 The Initial Developer of the Original Code is Sandro Furieri
24
25 Portions created by the Initial Developer are Copyright (C) 2014
26 the Initial Developer. All Rights Reserved.
27
28 Contributor(s):
29
30
31 Alternatively, the contents of this file may be used under the terms of
32 either the GNU General Public License Version 2 or later (the "GPL"), or
33 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
34 in which case the provisions of the GPL or the LGPL are applicable instead
35 of those above. If you wish to allow use of your version of this file only
36 under the terms of either the GPL or the LGPL, and not to allow others to
37 use your version of this file under the terms of the MPL, indicate your
38 decision by deleting the provisions above and replace them with the notice
39 and other provisions required by the GPL or the LGPL. If you do not delete
40 the provisions above, a recipient may use your version of this file under
41 the terms of any one of the MPL, the GPL or the LGPL.
42
43 */
44
45 #include <stdlib.h>
46 #include <unistd.h>
47 #include <stdio.h>
48 #include <string.h>
49
50 #include <spatialite/gaiaconfig.h>
51
52 #ifdef ENABLE_GEOPACKAGE /* only if GEOPACKAGE is enabled */
53
54 #include "sqlite3.h"
55 #include "spatialite.h"
56
57 static void
do_unlink_all()58 do_unlink_all ()
59 {
60 /* deleting all temporary files */
61 unlink ("./copy-gpkg_test.gpkg");
62 }
63
64 static int
test_table(sqlite3 * handle,const char * table)65 test_table (sqlite3 * handle, const char *table)
66 {
67 /* testing a 'raw' GPKG table */
68 char *sql;
69 int ret;
70 sqlite3_stmt *stmt = NULL;
71
72 sql = sqlite3_mprintf ("SELECT geom, IsValidGPB(geom), ST_Srid(geom), "
73 "ST_MinX(geom), ST_MaxX(geom), ST_MinY(geom), ST_MaxY(geom), "
74 "ST_Is3D(geom), ST_IsMeasured(geom), ST_MinZ(geom), ST_MaxZ(geom), "
75 "ST_MinM(geom), ST_MaxM(geom), ST_GeometryType(geom) FROM %s",
76 table);
77
78 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
79 sqlite3_free (sql);
80 if (ret != SQLITE_OK)
81 {
82 fprintf (stderr, "SELECT FROM \"%s\" error: %s\n", table,
83 sqlite3_errmsg (handle));
84 goto stop;
85 }
86 while (1)
87 {
88 /* scrolling the result set rows */
89 ret = sqlite3_step (stmt);
90 if (ret == SQLITE_DONE)
91 break; /* end of result set */
92 if (ret == SQLITE_ROW)
93 {
94 int is_null = 0;
95 int has_z;
96 int has_m;
97 if (sqlite3_column_type (stmt, 0) == SQLITE_NULL)
98 is_null = 1;
99 if (sqlite3_column_type (stmt, 1) != SQLITE_INTEGER)
100 {
101 fprintf (stderr,
102 "Unexpected ST_IsValidGPB: not an Integer\n");
103 goto stop;
104 }
105 if (is_null)
106 {
107 if (sqlite3_column_int (stmt, 1) != 0)
108 {
109 fprintf (stderr,
110 "Unexpected result (NULL) ST_IsValidGPB=%d\n",
111 sqlite3_column_int (stmt, 1));
112 goto stop;
113 }
114 continue;
115 }
116 else
117 {
118 if (sqlite3_column_int (stmt, 1) != 1)
119 {
120 fprintf (stderr,
121 "Unexpected result ST_IsValidGPB=%d\n",
122 sqlite3_column_int (stmt, 1));
123 goto stop;
124 }
125 }
126 if (sqlite3_column_type (stmt, 2) != SQLITE_INTEGER)
127 {
128 fprintf (stderr, "Unexpected ST_SRID: not an Integer\n");
129 goto stop;
130 }
131 if (sqlite3_column_int (stmt, 2) != 4326)
132 {
133 fprintf (stderr, "Unexpected result ST_SRID=%d\n",
134 sqlite3_column_int (stmt, 2));
135 goto stop;
136 }
137 if (sqlite3_column_type (stmt, 3) != SQLITE_FLOAT)
138 {
139 fprintf (stderr, "Unexpected ST_MinX: not a Double\n");
140 goto stop;
141 }
142 if (sqlite3_column_type (stmt, 4) != SQLITE_FLOAT)
143 {
144 fprintf (stderr, "Unexpected ST_MaxX: not a Double\n");
145 goto stop;
146 }
147 if (sqlite3_column_type (stmt, 5) != SQLITE_FLOAT)
148 {
149 fprintf (stderr, "Unexpected ST_MinY: not a Double\n");
150 goto stop;
151 }
152 if (sqlite3_column_type (stmt, 6) != SQLITE_FLOAT)
153 {
154 fprintf (stderr, "Unexpected ST_MaxY: not a Double\n");
155 goto stop;
156 }
157 if (sqlite3_column_type (stmt, 7) != SQLITE_INTEGER)
158 {
159 fprintf (stderr, "Unexpected ST_Is3D: not an Integer\n");
160 goto stop;
161 }
162 has_z = sqlite3_column_int (stmt, 7);
163 if (sqlite3_column_type (stmt, 8) != SQLITE_INTEGER)
164 {
165 fprintf (stderr,
166 "Unexpected ST_IsMeasured: not an Integer\n");
167 goto stop;
168 }
169 has_m = sqlite3_column_int (stmt, 8);
170 if (has_z)
171 {
172 if (sqlite3_column_type (stmt, 9) != SQLITE_FLOAT)
173 {
174 fprintf (stderr,
175 "Unexpected ST_MinZ: not a Double\n");
176 goto stop;
177 }
178 if (sqlite3_column_type (stmt, 10) != SQLITE_FLOAT)
179 {
180 fprintf (stderr,
181 "Unexpected ST_MaxZ: not a Double\n");
182 goto stop;
183 }
184 }
185 else
186 {
187 if (sqlite3_column_type (stmt, 9) != SQLITE_NULL)
188 {
189 fprintf (stderr, "Unexpected ST_MinZ: not NULL\n");
190 goto stop;
191 }
192 if (sqlite3_column_type (stmt, 10) != SQLITE_NULL)
193 {
194 fprintf (stderr, "Unexpected ST_MaxZ: not NULL\n");
195 goto stop;
196 }
197 }
198 if (has_m)
199 {
200 if (sqlite3_column_type (stmt, 11) != SQLITE_FLOAT)
201 {
202 fprintf (stderr,
203 "Unexpected ST_MinM: not a Double\n");
204 goto stop;
205 }
206 if (sqlite3_column_type (stmt, 12) != SQLITE_FLOAT)
207 {
208 fprintf (stderr,
209 "Unexpected ST_MaxM: not a Double\n");
210 goto stop;
211 }
212 }
213 else
214 {
215 if (sqlite3_column_type (stmt, 11) != SQLITE_NULL)
216 {
217 fprintf (stderr, "Unexpected ST_MinM: not NULL\n");
218 goto stop;
219 }
220 if (sqlite3_column_type (stmt, 12) != SQLITE_NULL)
221 {
222 fprintf (stderr, "Unexpected ST_MaxM: not NULL\n");
223 goto stop;
224 }
225 }
226 if (sqlite3_column_type (stmt, 13) != SQLITE_TEXT)
227 {
228 fprintf (stderr,
229 "Unexpected ST_GeometryType: not TEXT\n");
230 goto stop;
231 }
232 }
233 else
234 {
235 /* an unexpected error occurred */
236 fprintf (stderr, "Error while querying from \"%s\": %s\n",
237 table, sqlite3_errmsg (handle));
238 goto stop;
239 }
240 }
241 sqlite3_finalize (stmt);
242 return 1;
243
244 stop:
245 if (stmt != NULL)
246 sqlite3_finalize (stmt);
247 return 0;
248 }
249
250 static int
test_vtable(sqlite3 * handle,const char * table,int mode)251 test_vtable (sqlite3 * handle, const char *table, int mode)
252 {
253 /* testing a VirtualGPKG table */
254 char *sql;
255 int ret;
256 sqlite3_stmt *stmt = NULL;
257
258 if (mode)
259 {
260 sql = sqlite3_mprintf ("SELECT ROWID, first_name, "
261 "last_name, value1, value2 FROM vgpkg_%s",
262 table);
263 }
264 else
265 {
266 sql = sqlite3_mprintf ("SELECT geom, ST_AsText(geom), id, name "
267 "FROM vgpkg_%s", table);
268 }
269
270 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
271 sqlite3_free (sql);
272 if (ret != SQLITE_OK)
273 {
274 fprintf (stderr, "SELECT FROM \"vgpkg_%s\" error: %s\n", table,
275 sqlite3_errmsg (handle));
276 goto stop;
277 }
278 while (1)
279 {
280 /* scrolling the result set rows */
281 ret = sqlite3_step (stmt);
282 if (ret == SQLITE_DONE)
283 break; /* end of result set */
284 if (ret == SQLITE_ROW)
285 {
286 if (mode)
287 {
288 if (sqlite3_column_type (stmt, 0) != SQLITE_INTEGER)
289 {
290 fprintf (stderr,
291 "Unexpected ROWID: not an INTEGER\n");
292 goto stop;
293 }
294 if (sqlite3_column_type (stmt, 1) != SQLITE_TEXT)
295 {
296 fprintf (stderr,
297 "Unexpected \"first_name\": not a TEXT string\n");
298 goto stop;
299 }
300 if (sqlite3_column_type (stmt, 2) != SQLITE_TEXT)
301 {
302 fprintf (stderr,
303 "Unexpected \"first_name\": not a TEXT string\n");
304 goto stop;
305 }
306 if (sqlite3_column_type (stmt, 3) != SQLITE_FLOAT)
307 {
308 fprintf (stderr,
309 "Unexpected \"first_name\": not a DOUBLE\n");
310 goto stop;
311 }
312 if (sqlite3_column_type (stmt, 4) != SQLITE_FLOAT)
313 {
314 fprintf (stderr,
315 "Unexpected \"first_name\": not a DOUBLE\n");
316 goto stop;
317 }
318 }
319 else
320 {
321 if (sqlite3_column_type (stmt, 0) == SQLITE_NULL)
322 continue;
323 if (sqlite3_column_type (stmt, 1) != SQLITE_TEXT)
324 {
325 fprintf (stderr,
326 "Unexpected ST_AsText: not a TEXT string\n");
327 goto stop;
328 }
329 if (sqlite3_column_type (stmt, 2) != SQLITE_INTEGER)
330 {
331 fprintf (stderr,
332 "Unexpected \"id\": not an INTEGER\n");
333 goto stop;
334 }
335 if (sqlite3_column_type (stmt, 3) != SQLITE_TEXT)
336 {
337 fprintf (stderr,
338 "Unexpected \"name\": not a TEXT string\n");
339 goto stop;
340 }
341 }
342 }
343 else
344 {
345 /* an unexpected error occurred */
346 fprintf (stderr, "Error while querying from \"vgpkg_%s\": %s\n",
347 table, sqlite3_errmsg (handle));
348 goto stop;
349 }
350 }
351 sqlite3_finalize (stmt);
352 return 1;
353
354 stop:
355 if (stmt != NULL)
356 sqlite3_finalize (stmt);
357 return 0;
358 }
359
360 static int
test_vtable_out(sqlite3 * handle)361 test_vtable_out (sqlite3 * handle)
362 {
363 /* testing VirtualGPKG insert/update/delete */
364 const char *sql;
365 int ret;
366 char *sql_err = NULL;
367
368 sql = "BEGIN";
369 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
370 if (ret != SQLITE_OK)
371 {
372 fprintf (stderr, "BEGIN error: %s\n", sql_err);
373 sqlite3_free (sql_err);
374 return 0;
375 }
376
377 sql =
378 "INSERT INTO vgpkg_test_pk (first_name, last_name, value1, value2, geom) "
379 "VALUES ('z1', 'charlie', 1, 2, MakePoint(3, 3, 4326))";
380 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
381 if (ret != SQLITE_OK)
382 {
383 fprintf (stderr, "INSERT #1 error: %s\n", sql_err);
384 sqlite3_free (sql_err);
385 return 0;
386 }
387
388 sql =
389 "INSERT INTO vgpkg_test_pk (first_name, last_name, value1, value2, geom) "
390 "VALUES ('z2', 'annie', 1.1, 2.2, MakePoint(4, 4, 4326))";
391 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
392 if (ret != SQLITE_OK)
393 {
394 fprintf (stderr, "INSERT #2 error: %s\n", sql_err);
395 sqlite3_free (sql_err);
396 return 0;
397 }
398
399 sql = "INSERT INTO vgpkg_test_pk (first_name, last_name, value1, value2) "
400 "VALUES ('z3', 'peter', 2.2, 3.3)";
401 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
402 if (ret != SQLITE_OK)
403 {
404 fprintf (stderr, "INSERT #3 error: %s\n", sql_err);
405 sqlite3_free (sql_err);
406 return 0;
407 }
408
409 sql = "UPDATE vgpkg_test_pk SET geom = MakePoint(14, 14, 4326) "
410 "WHERE first_name = 'z1'";
411 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
412 if (ret != SQLITE_OK)
413 {
414 fprintf (stderr, "UPDATE #1 error: %s\n", sql_err);
415 sqlite3_free (sql_err);
416 return 0;
417 }
418
419 sql = "UPDATE vgpkg_test_pk SET value1 = 13.4 " "WHERE ROWID = 3";
420 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
421 if (ret != SQLITE_OK)
422 {
423 fprintf (stderr, "UPDATE #2 error: %s\n", sql_err);
424 sqlite3_free (sql_err);
425 return 0;
426 }
427
428 sql = "UPDATE vgpkg_test_pk SET geom = NULL, value2 = 51 "
429 "WHERE ROWID = 2";
430 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
431 if (ret != SQLITE_OK)
432 {
433 fprintf (stderr, "UPDATE #3 error: %s\n", sql_err);
434 sqlite3_free (sql_err);
435 return 0;
436 }
437
438 sql = "DELETE FROM vgpkg_test_pk WHERE ROWID = 1";
439 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
440 if (ret != SQLITE_OK)
441 {
442 fprintf (stderr, "DELETE #1 error: %s\n", sql_err);
443 sqlite3_free (sql_err);
444 return 0;
445 }
446
447 sql = "DELETE FROM vgpkg_test_pk WHERE first_name > 'z'";
448 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
449 if (ret != SQLITE_OK)
450 {
451 fprintf (stderr, "DELETE #2 error: %s\n", sql_err);
452 sqlite3_free (sql_err);
453 return 0;
454 }
455
456 sql = "ROLLBACK";
457 ret = sqlite3_exec (handle, sql, NULL, NULL, &sql_err);
458 if (ret != SQLITE_OK)
459 {
460 fprintf (stderr, "ROLLBACK error: %s\n", sql_err);
461 sqlite3_free (sql_err);
462 return 0;
463 }
464
465 return 1;
466 }
467
468 int
main(int argc,char * argv[])469 main (int argc, char *argv[])
470 {
471 sqlite3 *db_handle;
472 int ret;
473 const char *sql;
474 void *cache = NULL;
475 char *sql_err = NULL;
476 if (argc > 1 || argv[0] == NULL)
477 argc = 1; /* silencing stupid compiler warnings */
478
479 do_unlink_all ();
480
481 /* directly testing GPKG */
482 ret = system ("cp ./gpkg_test.gpkg copy-gpkg_test.gpkg");
483 if (ret != 0)
484 {
485 fprintf (stderr, "cannot copy gpkg_test.gpkg database\n");
486 return -1;
487 }
488
489 cache = spatialite_alloc_connection ();
490 ret =
491 sqlite3_open_v2 ("./copy-gpkg_test.gpkg", &db_handle,
492 SQLITE_OPEN_READWRITE, NULL);
493 if (ret != SQLITE_OK)
494 {
495 fprintf (stderr, "cannot open '%s': %s\n", "copy-gpkg_test.gpkg",
496 sqlite3_errmsg (db_handle));
497 do_unlink_all ();
498 sqlite3_close (db_handle);
499 spatialite_cleanup_ex (cache);
500 spatialite_shutdown ();
501 return -1;
502 }
503 spatialite_init_ex (db_handle, cache, 0);
504
505 if (!test_table (db_handle, "pt2d"))
506 {
507 do_unlink_all ();
508 sqlite3_close (db_handle);
509 spatialite_cleanup_ex (cache);
510 spatialite_shutdown ();
511 return -1;
512 }
513
514 if (!test_table (db_handle, "ln3dz"))
515 {
516 do_unlink_all ();
517 sqlite3_close (db_handle);
518 spatialite_cleanup_ex (cache);
519 spatialite_shutdown ();
520 return -1;
521 }
522
523 if (!test_table (db_handle, "pg2dm"))
524 {
525 do_unlink_all ();
526 sqlite3_close (db_handle);
527 spatialite_cleanup_ex (cache);
528 spatialite_shutdown ();
529 return -1;
530 }
531
532 if (!test_table (db_handle, "mpt3dzm"))
533 {
534 do_unlink_all ();
535 sqlite3_close (db_handle);
536 spatialite_cleanup_ex (cache);
537 spatialite_shutdown ();
538 return -1;
539 }
540
541 if (!test_table (db_handle, "mln2dm"))
542 {
543 do_unlink_all ();
544 sqlite3_close (db_handle);
545 spatialite_cleanup_ex (cache);
546 spatialite_shutdown ();
547 return -1;
548 }
549
550 if (!test_table (db_handle, "mpg3dz"))
551 {
552 do_unlink_all ();
553 sqlite3_close (db_handle);
554 spatialite_cleanup_ex (cache);
555 spatialite_shutdown ();
556 return -1;
557 }
558
559 if (!test_table (db_handle, "gc3dz"))
560 {
561 do_unlink_all ();
562 sqlite3_close (db_handle);
563 spatialite_cleanup_ex (cache);
564 spatialite_shutdown ();
565 return -1;
566 }
567
568 /* activating Auto GPKG Wrapping */
569 sql = "SELECT AutoGPKGStart()";
570 ret = sqlite3_exec (db_handle, sql, NULL, NULL, &sql_err);
571 if (ret != SQLITE_OK)
572 {
573 fprintf (stderr, "AutoGPKGStart error: %s\n", sql_err);
574 sqlite3_free (sql_err);
575 do_unlink_all ();
576 sqlite3_close (db_handle);
577 spatialite_cleanup_ex (cache);
578 spatialite_shutdown ();
579 return -1;
580 }
581
582 /* testing the Virtual Tables */
583 if (!test_vtable (db_handle, "pt2d", 0))
584 {
585 do_unlink_all ();
586 sqlite3_close (db_handle);
587 spatialite_cleanup_ex (cache);
588 spatialite_shutdown ();
589 return -1;
590 }
591
592 if (!test_vtable (db_handle, "ln3dz", 0))
593 {
594 do_unlink_all ();
595 sqlite3_close (db_handle);
596 spatialite_cleanup_ex (cache);
597 spatialite_shutdown ();
598 return -1;
599 }
600
601 if (!test_vtable (db_handle, "pg2dm", 0))
602 {
603 do_unlink_all ();
604 sqlite3_close (db_handle);
605 spatialite_cleanup_ex (cache);
606 spatialite_shutdown ();
607 return -1;
608 }
609
610 if (!test_vtable (db_handle, "mpt3dzm", 0))
611 {
612 do_unlink_all ();
613 sqlite3_close (db_handle);
614 spatialite_cleanup_ex (cache);
615 spatialite_shutdown ();
616 return -1;
617 }
618
619 if (!test_vtable (db_handle, "mln2dm", 0))
620 {
621 do_unlink_all ();
622 sqlite3_close (db_handle);
623 spatialite_cleanup_ex (cache);
624 spatialite_shutdown ();
625 return -1;
626 }
627
628 if (!test_vtable (db_handle, "mpg3dz", 0))
629 {
630 do_unlink_all ();
631 sqlite3_close (db_handle);
632 spatialite_cleanup_ex (cache);
633 spatialite_shutdown ();
634 return -1;
635 }
636
637 if (!test_vtable (db_handle, "gc3dz", 0))
638 {
639 do_unlink_all ();
640 sqlite3_close (db_handle);
641 spatialite_cleanup_ex (cache);
642 spatialite_shutdown ();
643 return -1;
644 }
645
646 if (!test_vtable (db_handle, "test_pk", 1))
647 {
648 do_unlink_all ();
649 sqlite3_close (db_handle);
650 spatialite_cleanup_ex (cache);
651 spatialite_shutdown ();
652 return -1;
653 }
654
655 if (!test_vtable_out (db_handle))
656 {
657 do_unlink_all ();
658 sqlite3_close (db_handle);
659 spatialite_cleanup_ex (cache);
660 spatialite_shutdown ();
661 return -1;
662 }
663
664 /* quitting Auto GPKG Wrapping */
665 sql = "SELECT AutoGPKGStop()";
666 ret = sqlite3_exec (db_handle, sql, NULL, NULL, &sql_err);
667 if (ret != SQLITE_OK)
668 {
669 fprintf (stderr, "AutoGPKGStop error: %s\n", sql_err);
670 sqlite3_free (sql_err);
671 do_unlink_all ();
672 sqlite3_close (db_handle);
673 spatialite_cleanup_ex (cache);
674 spatialite_shutdown ();
675 return -1;
676 }
677
678 sqlite3_close (db_handle);
679 spatialite_cleanup_ex (cache);
680 spatialite_shutdown ();
681
682 do_unlink_all ();
683 return 0;
684 }
685
686 #endif /* endif GEOPACKAGE enabled */
687