3
check_gpkg_base_core_contents_data_table_def - Test case for GeoPackage Extensions
5
Author: Brad Hards <bradh@frogmouth.net>
7
------------------------------------------------------------------------------
9
Version: MPL 1.1/GPL 2.0/LGPL 2.1
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/
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
21
The Original Code is GeoPackage extensions
23
The Initial Developer of the Original Code is Brad Hards
25
Portions created by the Initial Developer are Copyright (C) 2013
26
the Initial Developer. All Rights Reserved.
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.
54
#include "spatialite.h"
56
#include "test_helpers.h"
58
struct tableDefinitionElement
60
const char *column_name;
61
const char *column_type;
63
const char *defaultValue;
64
const char *keySignature;
68
struct tableDefinitionElement tableValues[] = {
69
{"table_name", "TEXT", false, NULL, "PRIMARY KEY", false},
70
{"data_type", "TEXT", false, NULL, NULL, false},
71
{"identifier", "TEXT", true, NULL, NULL, false},
72
{"description", "TEXT", true, "''", NULL, false},
73
{"min_x", "DOUBLE", true, NULL, NULL, false},
74
{"min_y", "DOUBLE", true, NULL, NULL, false},
75
{"max_x", "DOUBLE", true, NULL, NULL, false},
76
{"max_y", "DOUBLE", true, NULL, NULL, false},
77
{"srs_id", "INTEGER", true, NULL, NULL, false},
78
{NULL, NULL, false, NULL, NULL, false}
82
main (int argc UNUSED, char *argv[]UNUSED)
84
sqlite3 *db_handle = NULL;
89
void *cache = spatialite_alloc_connection ();
95
bool valid_last_change = false;
96
bool valid_fk_constraint = false;
99
sqlite3_open_v2 (":memory:", &db_handle,
100
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
101
/* For debugging / testing if required */
103
ret = sqlite3_open_v2 ("check_gpkg_base_core_spatial_ref_sys_data_table_def.sqlite", &db_handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
105
spatialite_init_ex (db_handle, cache, 0);
106
if (ret != SQLITE_OK)
108
fprintf (stderr, "cannot open in-memory db: %s\n",
109
sqlite3_errmsg (db_handle));
110
sqlite3_close (db_handle);
116
sqlite3_exec (db_handle, "SELECT gpkgCreateBaseTables()", NULL, NULL,
118
if (ret != SQLITE_OK)
121
"Unexpected gpkgCreateBaseTables() result: %i, (%s)\n", ret,
123
sqlite3_free (err_msg);
128
/ Test Case ID /base/core/contents/data/table_def
129
/ Test Purpose Verify that the gpkg_contents table exists and has the correct definition.
131
/ SELECT sql FROM sqlite_master WHERE type = table AND tbl_name = gpkg_contents
132
/ Fail if returns an empty result set.
133
/ Pass if the column names and column definitions in the returned CREATE TABLE statement,
134
/ including data type, nullability, default values and primary, foreign and unique key
135
/ constraints match all of those in the contents of C.2 Table 18. Column order, check
136
/ constraint and trigger definitions, and other column definitions in the returned sql
139
/ Reference Clause 1.1.3.1.1 Req 13:
143
"SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_contents'";
145
sqlite3_prepare_v2 (db_handle, sql_statement, strlen (sql_statement),
147
if (ret != SQLITE_OK)
149
fprintf (stderr, "failed to prepare SQL SELECT statement: %i (%s)\n",
150
ret, sqlite3_errmsg (db_handle));
153
ret = sqlite3_step (stmt);
154
if (ret != SQLITE_ROW)
156
fprintf (stderr, "unexpected return value for first step: %i (%s)\n",
157
ret, sqlite3_errmsg (db_handle));
160
if (sqlite3_column_type (stmt, 0) != SQLITE_TEXT)
162
fprintf (stderr, "bad type for column 0: %i\n",
163
sqlite3_column_type (stmt, 0));
167
strdup (strstr ((const char *) sqlite3_column_text (stmt, 0), "(") +
171
fprintf (stderr, "unrecognised format for SQL\n");
175
for (j = 1, str = sql;; j++, str = NULL)
177
token = strtok_r (str, ",", &saveptr);
182
for (i = 0; tableValues[i].column_name != NULL; ++i)
184
while (isspace (*token))
188
if (strncasecmp (token, "CONSTRAINT", strlen ("CONSTRAINT")) ==
193
"FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id)"))
195
valid_fk_constraint = true;
199
if (strcasestr (token, tableValues[i].column_name))
201
if (!strcasestr (token, tableValues[i].column_type))
204
"missing COLUMN TYPE %s for %s: %s\n",
205
tableValues[i].column_type,
206
tableValues[i].column_name, token);
209
if (tableValues[i].mayBeNull
210
&& strcasestr (token, "NOT NULL"))
213
"Unexpected NOT NULL constraint for %s: %s\n",
214
tableValues[i].column_name, token);
217
if ((!tableValues[i].mayBeNull)
218
&& (!strcasestr (token, "NOT NULL")))
221
"Missing NOT NULL constraint for %s: %s\n",
222
tableValues[i].column_name, token);
225
if ((tableValues[i].keySignature != NULL)
226
&& (!strcasestr (token, tableValues[i].keySignature)))
229
"Missing %s constraint for %s: %s\n",
230
tableValues[i].keySignature,
231
tableValues[i].column_name, token);
234
if ((tableValues[i].keySignature == NULL)
235
&& (strcasestr (token, "KEY")))
238
"Unexpected key constraint for %s: %s\n",
239
tableValues[i].column_name, token);
242
if ((tableValues[i].defaultValue == NULL)
243
&& (strcasestr (token, "DEFAULT")))
246
"Unexpected default value for %s: %s\n",
247
tableValues[i].column_name, token);
250
if ((tableValues[i].defaultValue != NULL)
251
&& (!strcasestr (token, tableValues[i].defaultValue)))
254
"Missing default value for %s: %s\n",
255
tableValues[i].column_name, token);
258
tableValues[i].wasFound = true;
261
if (strcasestr (token, "last_change "))
263
/* last_change default has an embedded ',' so we need to grab the next token too. */
264
char *nexttoken = strtok_r (str, ",", &saveptr);
266
malloc (strlen (token) + strlen (",") + strlen (nexttoken) +
268
strcpy (fulltoken, token);
269
strcat (fulltoken, ",");
270
strcat (fulltoken, nexttoken);
271
if (!strcasestr (fulltoken, "DATETIME"))
274
"missing COLUMN TYPE DATETIME for last_change: %s\n",
279
if (!strcasestr (fulltoken, "NOT NULL"))
282
"Missing NOT NULL constraint for last_change: %s\n",
287
if (strcasestr (fulltoken, "KEY"))
290
"Unexpected KEY constraint for last_change: %s\n",
297
"DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ',CURRENT_TIMESTAMP))"))
300
"Missing default value for last_change: %s\n",
306
valid_last_change = true;
311
for (i = 0; tableValues[i].column_name != NULL; ++i)
313
if (tableValues[i].wasFound == false)
315
fprintf (stderr, "did not find expected %s column\n",
316
tableValues[i].column_name);
320
if (!valid_last_change)
323
"did not find expected last_change column, or required properties were not found\n");
326
if (!valid_fk_constraint)
328
fprintf (stderr, "did not find expected FK constraint\n");
333
ret = sqlite3_step (stmt);
334
if (ret != SQLITE_DONE)
336
fprintf (stderr, "unexpected return value for second step: %i\n",
340
ret = sqlite3_finalize (stmt);
342
sqlite3_free (err_msg);
344
ret = sqlite3_close (db_handle);
345
if (ret != SQLITE_OK)
347
fprintf (stderr, "sqlite3_close() error: %s\n",
348
sqlite3_errmsg (db_handle));
352
spatialite_cleanup_ex (cache);
353
spatialite_shutdown ();