Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: OpenGIS Simple Features Reference Implementation
4 : * Purpose: Implements OGRSQLiteDriver class.
5 : * Author: Frank Warmerdam, warmerdam@pobox.com
6 : *
7 : ******************************************************************************
8 : *
9 : * Contributor: Alessandro Furieri, a.furieri@lqt.it
10 : * Portions of this module properly supporting SpatiaLite DB creation
11 : * Developed for Faunalia ( http://www.faunalia.it) with funding from
12 : * Regione Toscana - Settore SISTEMA INFORMATIVO TERRITORIALE ED AMBIENTALE
13 : *
14 : ******************************************************************************
15 : * Copyright (c) 2004, Frank Warmerdam <warmerdam@pobox.com>
16 : * Copyright (c) 2007-2013, Even Rouault <even dot rouault at spatialys.com>
17 : *
18 : * SPDX-License-Identifier: MIT
19 : ****************************************************************************/
20 :
21 : #include "cpl_port.h"
22 : #include "ogr_sqlite.h"
23 :
24 : #include <cstring>
25 : #include <string>
26 :
27 : #include "cpl_conv.h"
28 : #include "cpl_error.h"
29 : #include "cpl_string.h"
30 : #include "cpl_vsi.h"
31 : #include "gdal.h"
32 : #include "gdal_priv.h"
33 : #include "ogr_core.h"
34 : #include "sqlite3.h"
35 :
36 : /************************************************************************/
37 : /* OGRSQLiteDriverIdentify() */
38 : /************************************************************************/
39 :
40 54197 : static int OGRSQLiteDriverIdentify(GDALOpenInfo *poOpenInfo)
41 :
42 : {
43 54197 : if (STARTS_WITH_CI(poOpenInfo->pszFilename, "SQLITE:"))
44 : {
45 156 : return TRUE;
46 : }
47 :
48 108082 : CPLString osExt(CPLGetExtensionSafe(poOpenInfo->pszFilename));
49 54041 : if (EQUAL(osExt, "gpkg") && GDALGetDriverByName("GPKG") != nullptr)
50 : {
51 2122 : return FALSE;
52 : }
53 51919 : if (EQUAL(osExt, "mbtiles") && GDALGetDriverByName("MBTILES") != nullptr)
54 : {
55 265 : if (CSLCount(poOpenInfo->papszAllowedDrivers) == 1 &&
56 74 : EQUAL(poOpenInfo->papszAllowedDrivers[0], "SQLite"))
57 : {
58 74 : return TRUE;
59 : }
60 117 : return FALSE;
61 : }
62 :
63 51730 : if (STARTS_WITH_CI(poOpenInfo->pszFilename, "VirtualShape:") &&
64 2 : EQUAL(osExt, "shp"))
65 : {
66 2 : return TRUE;
67 : }
68 :
69 : #ifdef HAVE_RASTERLITE2
70 : if (STARTS_WITH_CI(poOpenInfo->pszFilename, "RASTERLITE2:"))
71 : return poOpenInfo->nOpenFlags & GDAL_OF_RASTER;
72 : #endif
73 :
74 51726 : if (EQUAL(poOpenInfo->pszFilename, ":memory:"))
75 96 : return TRUE;
76 :
77 : #ifdef SQLITE_OPEN_URI
78 : // This code enables support for named memory databases in SQLite.
79 : // Named memory databases use file name format
80 : // file:name?mode=memory&cache=shared
81 : // SQLITE_USE_URI is checked only to enable backward compatibility, in case
82 : // we accidentally hijacked some other format.
83 51632 : if (STARTS_WITH(poOpenInfo->pszFilename, "file:") &&
84 2 : CPLTestBool(CPLGetConfigOption("SQLITE_USE_URI", "YES")))
85 : {
86 2 : char *queryparams = strchr(poOpenInfo->pszFilename, '?');
87 2 : if (queryparams)
88 : {
89 2 : if (strstr(queryparams, "mode=memory") != nullptr)
90 2 : return TRUE;
91 : }
92 : }
93 : #endif
94 :
95 : /* -------------------------------------------------------------------- */
96 : /* Verify that the target is a real file, and has an */
97 : /* appropriate magic string at the beginning. */
98 : /* -------------------------------------------------------------------- */
99 51628 : if (poOpenInfo->nHeaderBytes < 100)
100 48157 : return FALSE;
101 :
102 : #ifdef ENABLE_SQL_SQLITE_FORMAT
103 3471 : if (STARTS_WITH(reinterpret_cast<const char *>(poOpenInfo->pabyHeader),
104 : "-- SQL SQLITE"))
105 : {
106 2 : return TRUE;
107 : }
108 3469 : if (STARTS_WITH(reinterpret_cast<const char *>(poOpenInfo->pabyHeader),
109 : "-- SQL RASTERLITE"))
110 : {
111 0 : return -1;
112 : }
113 3469 : if (STARTS_WITH(reinterpret_cast<const char *>(poOpenInfo->pabyHeader),
114 : "-- SQL MBTILES"))
115 : {
116 0 : if (GDALGetDriverByName("MBTILES") != nullptr)
117 0 : return FALSE;
118 0 : if (poOpenInfo->eAccess == GA_Update)
119 0 : return FALSE;
120 0 : return -1;
121 : }
122 : #endif
123 :
124 3469 : if (!STARTS_WITH(reinterpret_cast<const char *>(poOpenInfo->pabyHeader),
125 : "SQLite format 3"))
126 2992 : return FALSE;
127 :
128 : // In case we are opening /vsizip/foo.zip with a .gpkg inside
129 1431 : if ((memcmp(poOpenInfo->pabyHeader + 68, "GP10", 4) == 0 ||
130 477 : memcmp(poOpenInfo->pabyHeader + 68, "GP11", 4) == 0 ||
131 955 : memcmp(poOpenInfo->pabyHeader + 68, "GPKG", 4) == 0) &&
132 1 : GDALGetDriverByName("GPKG") != nullptr)
133 : {
134 1 : return FALSE;
135 : }
136 :
137 : // Could be a Rasterlite or MBTiles file as well
138 476 : return -1;
139 : }
140 :
141 : /************************************************************************/
142 : /* Open() */
143 : /************************************************************************/
144 :
145 393 : static GDALDataset *OGRSQLiteDriverOpen(GDALOpenInfo *poOpenInfo)
146 :
147 : {
148 393 : if (OGRSQLiteDriverIdentify(poOpenInfo) == FALSE)
149 0 : return nullptr;
150 :
151 : /* -------------------------------------------------------------------- */
152 : /* Check VirtualShape:xxx.shp syntax */
153 : /* -------------------------------------------------------------------- */
154 393 : const auto nLen = strlen(poOpenInfo->pszFilename);
155 393 : if (STARTS_WITH_CI(poOpenInfo->pszFilename, "VirtualShape:") && nLen > 4 &&
156 1 : EQUAL(poOpenInfo->pszFilename + nLen - 4, ".SHP"))
157 : {
158 2 : auto poDS = std::make_unique<OGRSQLiteDataSource>();
159 :
160 1 : char **papszOptions = CSLAddString(nullptr, "SPATIALITE=YES");
161 1 : int nRet = poDS->Create(":memory:", papszOptions);
162 1 : poDS->SetDescription(poOpenInfo->pszFilename);
163 1 : CSLDestroy(papszOptions);
164 1 : if (!nRet)
165 : {
166 0 : return nullptr;
167 : }
168 :
169 : char *pszSQLiteFilename =
170 1 : CPLStrdup(poOpenInfo->pszFilename + strlen("VirtualShape:"));
171 2 : if (!std::unique_ptr<GDALDataset>(GDALDataset::Open(
172 1 : pszSQLiteFilename, GDAL_OF_VECTOR, nullptr, nullptr, nullptr)))
173 : {
174 0 : CPLFree(pszSQLiteFilename);
175 0 : return nullptr;
176 : }
177 :
178 1 : char *pszLastDot = strrchr(pszSQLiteFilename, '.');
179 1 : if (pszLastDot)
180 1 : *pszLastDot = '\0';
181 :
182 2 : const std::string osTableName = CPLGetBasenameSafe(pszSQLiteFilename);
183 :
184 1 : char *pszSQL = CPLStrdup(CPLSPrintf(
185 : "CREATE VIRTUAL TABLE %s USING VirtualShape(%s, CP1252, -1)",
186 : osTableName.c_str(), pszSQLiteFilename));
187 1 : poDS->ExecuteSQL(pszSQL, nullptr, nullptr);
188 1 : CPLFree(pszSQL);
189 1 : CPLFree(pszSQLiteFilename);
190 1 : poDS->DisableUpdate();
191 1 : return poDS.release();
192 : }
193 :
194 : /* -------------------------------------------------------------------- */
195 : /* We think this is really an SQLite database, go ahead and try */
196 : /* and open it. */
197 : /* -------------------------------------------------------------------- */
198 392 : OGRSQLiteDataSource *poDS = new OGRSQLiteDataSource();
199 :
200 392 : if (!poDS->Open(poOpenInfo))
201 : {
202 4 : delete poDS;
203 4 : return nullptr;
204 : }
205 : else
206 388 : return poDS;
207 : }
208 :
209 : /************************************************************************/
210 : /* Create() */
211 : /************************************************************************/
212 :
213 247 : static GDALDataset *OGRSQLiteDriverCreate(const char *pszName, int nBands,
214 : CPL_UNUSED int nXSize,
215 : CPL_UNUSED int nYSize,
216 : CPL_UNUSED GDALDataType eDT,
217 : char **papszOptions)
218 : {
219 247 : if (nBands != 0)
220 : {
221 0 : CPLError(CE_Failure, CPLE_NotSupported,
222 : "Raster creation through Create() interface is not supported. "
223 : "Only CreateCopy() is supported");
224 0 : return nullptr;
225 : }
226 :
227 : /* -------------------------------------------------------------------- */
228 : /* First, ensure there isn't any such file yet. */
229 : /* -------------------------------------------------------------------- */
230 : VSIStatBufL sStatBuf;
231 :
232 247 : if (VSIStatL(pszName, &sStatBuf) == 0)
233 : {
234 0 : CPLError(CE_Failure, CPLE_AppDefined,
235 : "It seems a file system object called '%s' already exists.",
236 : pszName);
237 :
238 0 : return nullptr;
239 : }
240 :
241 : /* -------------------------------------------------------------------- */
242 : /* Try to create datasource. */
243 : /* -------------------------------------------------------------------- */
244 247 : OGRSQLiteDataSource *poDS = new OGRSQLiteDataSource();
245 :
246 247 : if (!poDS->Create(pszName, papszOptions))
247 : {
248 2 : delete poDS;
249 2 : return nullptr;
250 : }
251 :
252 245 : return poDS;
253 : }
254 :
255 : /************************************************************************/
256 : /* Delete() */
257 : /************************************************************************/
258 :
259 88 : static CPLErr OGRSQLiteDriverDelete(const char *pszName)
260 : {
261 88 : if (VSIUnlink(pszName) == 0)
262 66 : return CE_None;
263 : else
264 22 : return CE_Failure;
265 : }
266 :
267 : /************************************************************************/
268 : /* RegisterOGRSQLite() */
269 : /************************************************************************/
270 :
271 2024 : void RegisterOGRSQLite()
272 :
273 : {
274 2024 : if (!GDAL_CHECK_VERSION("SQLite driver"))
275 283 : return;
276 :
277 2024 : if (GDALGetDriverByName("SQLite") != nullptr)
278 283 : return;
279 :
280 1741 : GDALDriver *poDriver = new GDALDriver();
281 :
282 1741 : poDriver->SetDescription("SQLite");
283 1741 : poDriver->SetMetadataItem(GDAL_DCAP_VECTOR, "YES");
284 1741 : poDriver->SetMetadataItem(GDAL_DCAP_CREATE_LAYER, "YES");
285 1741 : poDriver->SetMetadataItem(GDAL_DCAP_DELETE_LAYER, "YES");
286 1741 : poDriver->SetMetadataItem(GDAL_DCAP_CREATE_FIELD, "YES");
287 1741 : poDriver->SetMetadataItem(GDAL_DCAP_DELETE_FIELD, "YES");
288 1741 : poDriver->SetMetadataItem(GDAL_DCAP_REORDER_FIELDS, "YES");
289 1741 : poDriver->SetMetadataItem(GDAL_DCAP_CURVE_GEOMETRIES, "YES");
290 1741 : poDriver->SetMetadataItem(GDAL_DCAP_MEASURED_GEOMETRIES, "YES");
291 1741 : poDriver->SetMetadataItem(GDAL_DCAP_Z_GEOMETRIES, "YES");
292 1741 : poDriver->SetMetadataItem(GDAL_DMD_SUPPORTED_SQL_DIALECTS, "SQLITE OGRSQL");
293 1741 : poDriver->SetMetadataItem(GDAL_DCAP_CAN_READ_AFTER_DELETE, "YES");
294 :
295 : #ifdef HAVE_RASTERLITE2
296 : poDriver->SetMetadataItem(GDAL_DCAP_RASTER, "YES");
297 : poDriver->SetMetadataItem(GDAL_DMD_LONGNAME,
298 : "SQLite / Spatialite / RasterLite2");
299 : poDriver->SetMetadataItem(GDAL_DMD_SUBDATASETS, "YES");
300 : poDriver->SetMetadataItem(GDAL_DCAP_CREATE_SUBDATASETS, "YES");
301 : #else
302 1741 : poDriver->SetMetadataItem(GDAL_DMD_LONGNAME, "SQLite / Spatialite");
303 : #endif
304 1741 : poDriver->SetMetadataItem(GDAL_DMD_HELPTOPIC, "drivers/vector/sqlite.html");
305 1741 : poDriver->SetMetadataItem(GDAL_DMD_EXTENSIONS, "sqlite db");
306 :
307 1741 : poDriver->SetMetadataItem(
308 : GDAL_DMD_OPENOPTIONLIST,
309 : "<OpenOptionList>"
310 : " <Option name='LIST_ALL_TABLES' type='boolean' description='Whether "
311 : "all tables, including non-spatial ones, should be listed' "
312 : "default='NO'/>"
313 : " <Option name='LIST_VIRTUAL_OGR' type='boolean' description='Whether "
314 : "VirtualOGR virtual tables should be listed. Should only be enabled on "
315 : "trusted datasources to avoid potential safety issues' default='NO'/>"
316 : " <Option name='PRELUDE_STATEMENTS' type='string' description='SQL "
317 : "statement(s) to send on the SQLite connection before any other ones'/>"
318 : #ifdef HAVE_RASTERLITE2
319 : " <Option name='1BIT_AS_8BIT' type='boolean' scope='raster' "
320 : "description='Whether to promote 1-bit monochrome raster as 8-bit, so "
321 : "as to have higher quality overviews' default='YES'/>"
322 : #endif
323 : " <Option name='OGR_SCHEMA' type='string' description='"
324 : "Partially or totally overrides the auto-detected schema to use for "
325 : "creating the layer. "
326 : "The overrides are defined as a JSON list of field definitions. "
327 : "This can be a filename or a JSON string or a URL.'/>"
328 1741 : "</OpenOptionList>");
329 :
330 : CPLString osCreationOptions(
331 : "<CreationOptionList>"
332 : #ifdef HAVE_SPATIALITE
333 : " <Option name='SPATIALITE' type='boolean' description='Whether to "
334 : "create a Spatialite database' default='NO'/>"
335 : #endif
336 : " <Option name='METADATA' type='boolean' description='Whether to "
337 : "create the geometry_columns and spatial_ref_sys tables' "
338 : "default='YES'/>"
339 : " <Option name='INIT_WITH_EPSG' type='boolean' description='Whether "
340 : "to insert the content of the EPSG CSV files into the spatial_ref_sys "
341 : "table ' default='NO'/>"
342 : #ifdef HAVE_RASTERLITE2
343 : " <Option name='APPEND_SUBDATASET' scope='raster' type='boolean' "
344 : "description='Whether to add the raster to the existing file' "
345 : "default='NO'/>"
346 : " <Option name='COVERAGE' scope='raster' type='string' "
347 : "description='Coverage name'/>"
348 : " <Option name='SECTION' scope='raster' type='string' "
349 : "description='Section name'/>"
350 : " <Option name='COMPRESS' scope='raster' type='string-select' "
351 : "description='Raster compression' default='NONE'>"
352 : " <Value>NONE</Value>"
353 : #endif
354 3482 : );
355 : #ifdef HAVE_RASTERLITE2
356 : if (rl2_is_supported_codec(RL2_COMPRESSION_DEFLATE))
357 : osCreationOptions += " <Value>DEFLATE</Value>";
358 : if (rl2_is_supported_codec(RL2_COMPRESSION_LZMA))
359 : osCreationOptions += " <Value>LZMA</Value>";
360 : if (rl2_is_supported_codec(RL2_COMPRESSION_PNG))
361 : osCreationOptions += " <Value>PNG</Value>";
362 : if (rl2_is_supported_codec(RL2_COMPRESSION_CCITTFAX4))
363 : osCreationOptions += " <Value>CCITTFAX4</Value>";
364 : if (rl2_is_supported_codec(RL2_COMPRESSION_JPEG))
365 : osCreationOptions += " <Value>JPEG</Value>";
366 : if (rl2_is_supported_codec(RL2_COMPRESSION_LOSSY_WEBP))
367 : osCreationOptions += " <Value>WEBP</Value>";
368 : if (rl2_is_supported_codec(RL2_COMPRESSION_LOSSY_JP2))
369 : osCreationOptions += " <Value>JPEG2000</Value>";
370 : #endif
371 : osCreationOptions +=
372 : #ifdef HAVE_RASTERLITE2
373 : " </Option>"
374 : " <Option name='QUALITY' scope='raster' type='int' description='Image "
375 : "quality for JPEG, WEBP and JPEG2000 compressions'/>"
376 : " <Option name='PIXEL_TYPE' scope='raster' type='string-select' "
377 : "description='Raster pixel type. Determines photometric "
378 : "interpretation'>"
379 : " <Value>MONOCHROME</Value>"
380 : " <Value>PALETTE</Value>"
381 : " <Value>GRAYSCALE</Value>"
382 : " <Value>RGB</Value>"
383 : " <Value>MULTIBAND</Value>"
384 : " <Value>DATAGRID</Value>"
385 : " </Option>"
386 : " <Option name='BLOCKXSIZE' scope='raster' type='int' "
387 : "description='Block width' default='512'/>"
388 : " <Option name='BLOCKYSIZE' scope='raster' type='int' "
389 : "description='Block height' default='512'/>"
390 : " <Option name='NBITS' scope='raster' type='int' description='Force "
391 : "bit width. 1, 2 or 4 are supported'/>"
392 : " <Option name='PYRAMIDIZE' scope='raster' type='boolean' "
393 : "description='Whether to automatically build relevant "
394 : "pyramids/overviews' default='NO'/>"
395 : #endif
396 1741 : "</CreationOptionList>";
397 :
398 1741 : poDriver->SetMetadataItem(GDAL_DMD_CREATIONOPTIONLIST, osCreationOptions);
399 :
400 1741 : poDriver->SetMetadataItem(
401 : GDAL_DS_LAYER_CREATIONOPTIONLIST,
402 : "<LayerCreationOptionList>"
403 : " <Option name='FORMAT' type='string-select' description='Format of "
404 : "geometry columns'>"
405 : " <Value>WKB</Value>"
406 : " <Value>WKT</Value>"
407 : #ifdef HAVE_SPATIALITE
408 : " <Value>SPATIALITE</Value>"
409 : #endif
410 : " </Option>"
411 : " <Option name='GEOMETRY_NAME' type='string' description='Name of "
412 : "geometry column. Defaults to WKT_GEOMETRY for FORMAT=WKT or GEOMETRY "
413 : "otherwise'/>"
414 : " <Option name='LAUNDER' type='boolean' description='Whether layer "
415 : "and field names will be laundered' default='YES'/>"
416 : #ifdef HAVE_SPATIALITE
417 : " <Option name='SPATIAL_INDEX' type='boolean' description='Whether to "
418 : "create a spatial index for Spatialite databases' default='YES'/>"
419 : " <Option name='COMPRESS_GEOM' type='boolean' description='Whether to "
420 : "use compressed format of Spatialite geometries' default='NO'/>"
421 : #endif
422 : " <Option name='SRID' type='int' description='Forced SRID of the "
423 : "layer'/>"
424 : " <Option name='COMPRESS_COLUMNS' type='string' "
425 : "description='=column_name1[,column_name2, ...]. list of (String) "
426 : "columns that must be compressed with ZLib DEFLATE algorithm'/>"
427 : " <Option name='OVERWRITE' type='boolean' description='Whether to "
428 : "overwrite an existing table with the layer name to be created' "
429 : "default='NO'/>"
430 : " <Option name='FID' type='string' description='Name of the FID "
431 : "column to create' default='OGC_FID'/>"
432 : #if SQLITE_VERSION_NUMBER >= 3037000
433 : " <Option name='STRICT' type='boolean' description='Whether to create "
434 : "the table in STRICT mode (only compatible of readers with sqlite >= "
435 : "3.37)' default='NO'/>"
436 : #endif
437 1741 : "</LayerCreationOptionList>");
438 :
439 1741 : poDriver->SetMetadataItem(GDAL_DMD_CREATIONFIELDDATATYPES,
440 : "Integer Integer64 Real String Date DateTime "
441 : "Time Binary IntegerList Integer64List "
442 1741 : "RealList StringList");
443 1741 : poDriver->SetMetadataItem(GDAL_DMD_CREATIONFIELDDATASUBTYPES,
444 1741 : "Boolean Int16 Float32");
445 1741 : poDriver->SetMetadataItem(GDAL_DMD_CREATION_FIELD_DEFN_FLAGS,
446 1741 : "WidthPrecision Nullable Default Unique");
447 1741 : poDriver->SetMetadataItem(
448 : GDAL_DMD_ALTER_FIELD_DEFN_FLAGS,
449 1741 : "Name Type WidthPrecision Nullable Default Unique");
450 :
451 : #ifdef HAVE_RASTERLITE2
452 : poDriver->SetMetadataItem(GDAL_DMD_CREATIONDATATYPES,
453 : "Byte Int8 UInt16 Int16 UInt32 Int32 Float32 "
454 : "Float64");
455 : #endif
456 1741 : poDriver->SetMetadataItem(GDAL_DCAP_NOTNULL_FIELDS, "YES");
457 1741 : poDriver->SetMetadataItem(GDAL_DCAP_DEFAULT_FIELDS, "YES");
458 1741 : poDriver->SetMetadataItem(GDAL_DCAP_UNIQUE_FIELDS, "YES");
459 1741 : poDriver->SetMetadataItem(GDAL_DCAP_NOTNULL_GEOMFIELDS, "YES");
460 1741 : poDriver->SetMetadataItem(GDAL_DCAP_VIRTUALIO, "YES");
461 1741 : poDriver->SetMetadataItem(GDAL_DCAP_MULTIPLE_VECTOR_LAYERS, "YES");
462 1741 : poDriver->SetMetadataItem(GDAL_DCAP_RELATIONSHIPS, "YES");
463 1741 : poDriver->SetMetadataItem(GDAL_DCAP_CREATE_RELATIONSHIP, "YES");
464 1741 : poDriver->SetMetadataItem(GDAL_DMD_RELATIONSHIP_FLAGS,
465 1741 : "OneToMany Association Composite");
466 1741 : poDriver->SetMetadataItem(GDAL_DMD_RELATIONSHIP_RELATED_TABLE_TYPES,
467 1741 : "features");
468 :
469 : #ifdef ENABLE_SQL_SQLITE_FORMAT
470 1741 : poDriver->SetMetadataItem("ENABLE_SQL_SQLITE_FORMAT", "YES");
471 : #endif
472 : #ifdef SQLITE_HAS_COLUMN_METADATA
473 1741 : poDriver->SetMetadataItem("SQLITE_HAS_COLUMN_METADATA", "YES");
474 : #endif
475 :
476 1741 : poDriver->SetMetadataItem(GDAL_DCAP_UPDATE, "YES");
477 1741 : poDriver->SetMetadataItem(GDAL_DMD_UPDATE_ITEMS, "Features");
478 :
479 1741 : poDriver->pfnOpen = OGRSQLiteDriverOpen;
480 1741 : poDriver->pfnIdentify = OGRSQLiteDriverIdentify;
481 1741 : poDriver->pfnCreate = OGRSQLiteDriverCreate;
482 : #ifdef HAVE_RASTERLITE2
483 : poDriver->pfnCreateCopy = OGRSQLiteDriverCreateCopy;
484 : #endif
485 1741 : poDriver->pfnDelete = OGRSQLiteDriverDelete;
486 1741 : poDriver->pfnUnloadDriver = OGRSQLiteDriverUnload;
487 :
488 1741 : GetGDALDriverManager()->RegisterDriver(poDriver);
489 : }
|
