Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Difference From 25bca24c55175a98 To 4afe976081fb4cff
|
2012-09-29
| ||
| 02:48 |
Add convenience API for creating tiles tables and adding zoom levels.
Also update geopackage_contents to define an informative extent. check-in: f30b241aee user: bradh tags: trunk | |
|
2012-09-22
| ||
| 03:33 |
Update to reflect 2012-09-21 version of spec.
Adds geopackage_contents and manifest tables, updates tile_table_metadata table. check-in: 25bca24c55 user: bradh tags: trunk | |
|
2012-09-16
| ||
| 10:10 | Update trigger routines to match current spec. Update tests and add new tests to match. check-in: 4afe976081 user: bradh tags: trunk | |
| 10:09 | Remove duplicate newlines. check-in: 610eecc80b user: bradh tags: trunk | |
Changes to src/gpkgCreateBaseTables.c.
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 ... 117 118 119 120 121 122 123 124 125 126 127 128 129 130 ... 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 |
char *sql_stmt = NULL;
sqlite3 *sqlite = NULL;
char *errMsg = NULL;
int ret = 0;
int i = 0;
const char* tableSchemas[] = {
"CREATE TABLE geopackage_contents (\n"
"table_name TEXT NOT NULL PRIMARY KEY,\n"
"data_type TEXT NOT NULL,\n"
"identifier TEXT NOT NULL DEFAULT '',\n"
"description TEXT NOT NULL DEFAULT '',\n"
"last_change TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ',CURRENT_TIMESTAMP)));",
/* GeoPackage specification Table 19/20 */
"CREATE TABLE raster_columns (\n"
"r_table_name TEXT NOT NULL,\n"
"r_raster_column TEXT NOT NULL,\n"
"srid INTEGER NOT NULL DEFAULT 0,\n"
"CONSTRAINT pk_rc PRIMARY KEY (r_table_name, r_raster_column) ON CONFLICT ROLLBACK,\n"
"CONSTRAINT fk_rc_r_srid FOREIGN KEY (srid) REFERENCES spatial_ref_sys(srid));",
................................................................................
"WHERE NEW.r_raster_column <> lower(NEW.r_raster_column);\n"
"END;",
/* GeoPackage specification Table 23/24 */
/* TODO: see if there is a nicer way to manage this using a VIEW */
"CREATE TABLE tile_table_metadata (\n"
"t_table_name TEXT NOT NULL PRIMARY KEY,\n"
"is_times_two_zoom INTEGER NOT NULL DEFAULT 1\n"
");",
/* The next four triggers are from GeoPackage specification Table 25 */
"CREATE TRIGGER 'tile_table_metadata_t_table_name_insert'\n"
"BEFORE INSERT ON 'tile_table_metadata'\n"
"FOR EACH ROW BEGIN\n"
................................................................................
"CREATE TRIGGER 'metadata_reference_timestamp_update'\n"
"BEFORE UPDATE OF 'timestamp' ON 'metadata_reference'\n"
"FOR EACH ROW BEGIN\n"
"SELECT RAISE(ROLLBACK, 'update on table metadata_reference violates constraint: timestamp must be a valid time in ISO 8601 \"yyyy-mm-ddThh-mm-ss.cccZ\" form')\n"
"WHERE NOT (NEW.timestamp GLOB '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[1-3][1-9]T[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]Z'\n"
"AND strftime('%s',NEW.timestamp) NOT NULL);\n"
"END;",
"CREATE TABLE manifest (\n"
"id TEXT NOT NULL PRIMARY KEY,\n"
"manifest TEXT NOT NULL\n"
");",
NULL
};
for (i = 0; tableSchemas[i] != NULL; ++i)
{
sql_stmt = sqlite3_mprintf("%s", tableSchemas[i]);
|
< < < < < < < > > > > < < < < < |
53 54 55 56 57 58 59 60 61 62 63 64 65 66 ... 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 ... 371 372 373 374 375 376 377 378 379 380 381 382 383 384 |
char *sql_stmt = NULL;
sqlite3 *sqlite = NULL;
char *errMsg = NULL;
int ret = 0;
int i = 0;
const char* tableSchemas[] = {
/* GeoPackage specification Table 19/20 */
"CREATE TABLE raster_columns (\n"
"r_table_name TEXT NOT NULL,\n"
"r_raster_column TEXT NOT NULL,\n"
"srid INTEGER NOT NULL DEFAULT 0,\n"
"CONSTRAINT pk_rc PRIMARY KEY (r_table_name, r_raster_column) ON CONFLICT ROLLBACK,\n"
"CONSTRAINT fk_rc_r_srid FOREIGN KEY (srid) REFERENCES spatial_ref_sys(srid));",
................................................................................
"WHERE NEW.r_raster_column <> lower(NEW.r_raster_column);\n"
"END;",
/* GeoPackage specification Table 23/24 */
/* TODO: see if there is a nicer way to manage this using a VIEW */
"CREATE TABLE tile_table_metadata (\n"
"t_table_name TEXT NOT NULL PRIMARY KEY,\n"
"min_x DOUBLE NOT NULL DEFAULT -180.0,\n"
"min_y DOUBLE NOT NULL DEFAULT -90.0,\n"
"max_x DOUBLE NOT NULL DEFAULT 180.0,\n"
"max_y DOUBLE NOT NULL DEFAULT 90.0 ,\n"
"is_times_two_zoom INTEGER NOT NULL DEFAULT 1\n"
");",
/* The next four triggers are from GeoPackage specification Table 25 */
"CREATE TRIGGER 'tile_table_metadata_t_table_name_insert'\n"
"BEFORE INSERT ON 'tile_table_metadata'\n"
"FOR EACH ROW BEGIN\n"
................................................................................
"CREATE TRIGGER 'metadata_reference_timestamp_update'\n"
"BEFORE UPDATE OF 'timestamp' ON 'metadata_reference'\n"
"FOR EACH ROW BEGIN\n"
"SELECT RAISE(ROLLBACK, 'update on table metadata_reference violates constraint: timestamp must be a valid time in ISO 8601 \"yyyy-mm-ddThh-mm-ss.cccZ\" form')\n"
"WHERE NOT (NEW.timestamp GLOB '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[1-3][1-9]T[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]Z'\n"
"AND strftime('%s',NEW.timestamp) NOT NULL);\n"
"END;",
NULL
};
for (i = 0; tableSchemas[i] != NULL; ++i)
{
sql_stmt = sqlite3_mprintf("%s", tableSchemas[i]);
|
Changes to test/check_createBaseTables.c.
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
if (ret != SQLITE_OK) {
fprintf(stderr, "Unexpected INSERT INTO raster_columns result: %i, (%s)\n", ret, err_msg);
sqlite3_free (err_msg);
return -101;
}
/* check tile_table_metadata table is OK */
ret = sqlite3_exec (db_handle, "INSERT INTO tile_table_metadata VALUES (\"sample_matrix_tiles\", 1)", NULL, NULL, &err_msg);
if (ret != SQLITE_OK) {
fprintf(stderr, "Unexpected INSERT INTO tile_table_metadata result: %i, (%s)\n", ret, err_msg);
sqlite3_free (err_msg);
return -102;
}
/* check tile_matrix_metadata table is OK */
................................................................................
/* check creation when the tables already exist */
ret = sqlite3_exec (db_handle, "SELECT gpkgCreateBaseTables()", NULL, NULL, &err_msg);
if (ret != SQLITE_ERROR) {
fprintf(stderr, "Unexpected duplicate gpkgCreateBaseTables() result: %i, (%s)\n", ret, err_msg);
return -110;
}
if (strcmp("table geopackage_contents already exists", err_msg) != 0)
{
fprintf(stderr, "Unexpected duplicate gpkgCreateBaseTables() error message: %s\n", err_msg);
return -111;
}
sqlite3_free (err_msg);
ret = sqlite3_close (db_handle);
|
|
|
|
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
if (ret != SQLITE_OK) {
fprintf(stderr, "Unexpected INSERT INTO raster_columns result: %i, (%s)\n", ret, err_msg);
sqlite3_free (err_msg);
return -101;
}
/* check tile_table_metadata table is OK */
ret = sqlite3_exec (db_handle, "INSERT INTO tile_table_metadata VALUES (\"sample_matrix_tiles\", -179.0, -89.0, 179.0, 89.0, 1)", NULL, NULL, &err_msg);
if (ret != SQLITE_OK) {
fprintf(stderr, "Unexpected INSERT INTO tile_table_metadata result: %i, (%s)\n", ret, err_msg);
sqlite3_free (err_msg);
return -102;
}
/* check tile_matrix_metadata table is OK */
................................................................................
/* check creation when the tables already exist */
ret = sqlite3_exec (db_handle, "SELECT gpkgCreateBaseTables()", NULL, NULL, &err_msg);
if (ret != SQLITE_ERROR) {
fprintf(stderr, "Unexpected duplicate gpkgCreateBaseTables() result: %i, (%s)\n", ret, err_msg);
return -110;
}
if (strcmp("table raster_columns already exists", err_msg) != 0)
{
fprintf(stderr, "Unexpected duplicate gpkgCreateBaseTables() error message: %s\n", err_msg);
return -111;
}
sqlite3_free (err_msg);
ret = sqlite3_close (db_handle);
|