Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: OpenGIS Simple Features Reference Implementation
4 : * Purpose: Implements OGRSQLiteTableLayer class, access to an existing table.
5 : * Author: Frank Warmerdam, warmerdam@pobox.com
6 : *
7 : ******************************************************************************
8 : * Copyright (c) 2004, Frank Warmerdam
9 : * Copyright (c) 2009-2013, Even Rouault <even dot rouault at spatialys.com>
10 : *
11 : * Permission is hereby granted, free of charge, to any person obtaining a
12 : * copy of this software and associated documentation files (the "Software"),
13 : * to deal in the Software without restriction, including without limitation
14 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 : * and/or sell copies of the Software, and to permit persons to whom the
16 : * Software is furnished to do so, subject to the following conditions:
17 : *
18 : * The above copyright notice and this permission notice shall be included
19 : * in all copies or substantial portions of the Software.
20 : *
21 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 : * DEALINGS IN THE SOFTWARE.
28 : ****************************************************************************/
29 :
30 : #include "cpl_port.h"
31 : #include "ogr_sqlite.h"
32 : #include "ogrsqliteutility.h"
33 :
34 : #include <climits>
35 : #include <cstddef>
36 : #include <cstdio>
37 : #include <cstdlib>
38 : #include <cstring>
39 : #include <ctime>
40 : #include <limits>
41 : #include <memory>
42 : #include <set>
43 : #include <string>
44 : #include <utility>
45 : #include <vector>
46 :
47 : #include "cpl_conv.h"
48 : #include "cpl_error.h"
49 : #include "cpl_string.h"
50 : #include "cpl_time.h"
51 : #include "ogr_core.h"
52 : #include "ogr_feature.h"
53 : #include "ogr_geometry.h"
54 : #include "ogr_p.h"
55 : #include "ogr_spatialref.h"
56 : #include "ogrsf_frmts.h"
57 : #include "sqlite3.h"
58 :
59 : static const char UNSUPPORTED_OP_READ_ONLY[] =
60 : "%s : unsupported operation on a read-only datasource.";
61 :
62 : /************************************************************************/
63 : /* OGRSQLiteTableLayer() */
64 : /************************************************************************/
65 :
66 2303 : OGRSQLiteTableLayer::OGRSQLiteTableLayer(OGRSQLiteDataSource *poDSIn)
67 : : OGRSQLiteLayer(poDSIn),
68 : m_bSpatialite2D(
69 6909 : poDSIn->GetSpatialiteVersionNumber() <
70 2303 : OGRSQLiteDataSource::MakeSpatialiteVersionNumber(2, 4, 0)),
71 2303 : m_bHasCheckedTriggers(!CPLTestBool(
72 4606 : CPLGetConfigOption("OGR_SQLITE_DISABLE_INSERT_TRIGGERS", "YES")))
73 : {
74 2303 : }
75 :
76 : /************************************************************************/
77 : /* ~OGRSQLiteTableLayer() */
78 : /************************************************************************/
79 :
80 4606 : OGRSQLiteTableLayer::~OGRSQLiteTableLayer()
81 :
82 : {
83 2303 : ClearStatement();
84 2303 : ClearInsertStmt();
85 :
86 : const int nGeomFieldCount =
87 2303 : m_poFeatureDefn ? m_poFeatureDefn->GetGeomFieldCount() : 0;
88 2821 : for (int i = 0; i < nGeomFieldCount; i++)
89 : {
90 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
91 518 : m_poFeatureDefn->myGetGeomFieldDefn(i);
92 : // Restore temporarily disabled triggers.
93 1314 : for (int j = 0; j < static_cast<int>(
94 796 : poGeomFieldDefn->m_aosDisabledTriggers.size());
95 : j++)
96 : {
97 278 : CPLDebug("SQLite", "Restoring trigger %s",
98 278 : poGeomFieldDefn->m_aosDisabledTriggers[j].first.c_str());
99 : // This may fail since CreateSpatialIndex() reinstalls triggers, so
100 : // don't check result.
101 556 : CPL_IGNORE_RET_VAL(sqlite3_exec(
102 278 : m_poDS->GetDB(),
103 278 : poGeomFieldDefn->m_aosDisabledTriggers[j].second.c_str(),
104 : nullptr, nullptr, nullptr));
105 : }
106 : }
107 :
108 2303 : CPLFree(m_pszTableName);
109 2303 : CPLFree(m_pszEscapedTableName);
110 2303 : CPLFree(m_pszCreationGeomFormat);
111 4606 : }
112 :
113 : /************************************************************************/
114 : /* CreateSpatialIndexIfNecessary() */
115 : /************************************************************************/
116 :
117 9407 : void OGRSQLiteTableLayer::CreateSpatialIndexIfNecessary()
118 : {
119 9407 : if (m_bDeferredSpatialIndexCreation)
120 : {
121 262 : for (int iGeomCol = 0; iGeomCol < m_poFeatureDefn->GetGeomFieldCount();
122 : iGeomCol++)
123 131 : CreateSpatialIndex(iGeomCol);
124 131 : m_bDeferredSpatialIndexCreation = false;
125 : }
126 9407 : }
127 :
128 : /************************************************************************/
129 : /* ClearInsertStmt() */
130 : /************************************************************************/
131 :
132 3818 : void OGRSQLiteTableLayer::ClearInsertStmt()
133 : {
134 3818 : if (m_hInsertStmt != nullptr)
135 : {
136 443 : sqlite3_finalize(m_hInsertStmt);
137 443 : m_hInsertStmt = nullptr;
138 : }
139 3818 : m_osLastInsertStmt = "";
140 3818 : }
141 :
142 : /************************************************************************/
143 : /* Initialize() */
144 : /************************************************************************/
145 :
146 2303 : CPLErr OGRSQLiteTableLayer::Initialize(const char *m_pszTableNameIn,
147 : bool bIsTable, bool bIsVirtualShapeIn,
148 : bool bDeferredCreationIn,
149 : bool bMayEmitError)
150 : {
151 2303 : SetDescription(m_pszTableNameIn);
152 :
153 2303 : m_bIsTable = bIsTable;
154 2303 : m_bIsVirtualShape = bIsVirtualShapeIn;
155 2303 : m_pszTableName = CPLStrdup(m_pszTableNameIn);
156 2303 : m_bDeferredCreation = bDeferredCreationIn;
157 2303 : m_pszEscapedTableName = CPLStrdup(SQLEscapeLiteral(m_pszTableName));
158 :
159 2303 : if (!bDeferredCreationIn && strchr(m_pszTableName, '(') != nullptr &&
160 300 : m_pszTableName[strlen(m_pszTableName) - 1] == ')')
161 : {
162 300 : char *pszErrMsg = nullptr;
163 300 : int nRowCount = 0, nColCount = 0;
164 300 : char **papszResult = nullptr;
165 : const char *pszSQL =
166 300 : CPLSPrintf("SELECT * FROM sqlite_master WHERE name = '%s'",
167 : m_pszEscapedTableName);
168 300 : int rc = sqlite3_get_table(m_poDS->GetDB(), pszSQL, &papszResult,
169 : &nRowCount, &nColCount, &pszErrMsg);
170 300 : int bFound = (rc == SQLITE_OK && nRowCount == 1);
171 300 : sqlite3_free_table(papszResult);
172 300 : sqlite3_free(pszErrMsg);
173 :
174 300 : if (!bFound)
175 : {
176 299 : char *pszGeomCol = CPLStrdup(strchr(m_pszTableName, '(') + 1);
177 299 : pszGeomCol[strlen(pszGeomCol) - 1] = 0;
178 299 : *strchr(m_pszTableName, '(') = 0;
179 299 : CPLFree(m_pszEscapedTableName);
180 299 : m_pszEscapedTableName = CPLStrdup(SQLEscapeLiteral(m_pszTableName));
181 299 : EstablishFeatureDefn(pszGeomCol, bMayEmitError);
182 299 : CPLFree(pszGeomCol);
183 595 : if (m_poFeatureDefn == nullptr ||
184 296 : m_poFeatureDefn->GetGeomFieldCount() == 0)
185 293 : return CE_Failure;
186 : }
187 : }
188 :
189 2010 : return CE_None;
190 : }
191 :
192 : /************************************************************************/
193 : /* GetGeomFormat() */
194 : /************************************************************************/
195 :
196 327 : static OGRSQLiteGeomFormat GetGeomFormat(const char *pszGeomFormat)
197 : {
198 327 : OGRSQLiteGeomFormat eGeomFormat = OSGF_None;
199 327 : if (pszGeomFormat)
200 : {
201 327 : if (EQUAL(pszGeomFormat, "WKT"))
202 2 : eGeomFormat = OSGF_WKT;
203 325 : else if (EQUAL(pszGeomFormat, "WKB"))
204 180 : eGeomFormat = OSGF_WKB;
205 145 : else if (EQUAL(pszGeomFormat, "FGF"))
206 1 : eGeomFormat = OSGF_FGF;
207 144 : else if (EQUAL(pszGeomFormat, "SpatiaLite"))
208 144 : eGeomFormat = OSGF_SpatiaLite;
209 : }
210 327 : return eGeomFormat;
211 : }
212 :
213 : /************************************************************************/
214 : /* SetCreationParameters() */
215 : /************************************************************************/
216 :
217 382 : void OGRSQLiteTableLayer::SetCreationParameters(const char *pszFIDColumnName,
218 : OGRwkbGeometryType eGeomType,
219 : const char *pszGeomFormat,
220 : const char *pszGeometryName,
221 : OGRSpatialReference *poSRS,
222 : int nSRSId)
223 :
224 : {
225 382 : m_pszFIDColumn = CPLStrdup(pszFIDColumnName);
226 382 : m_poFeatureDefn = new OGRSQLiteFeatureDefn(m_pszTableName);
227 382 : m_poFeatureDefn->SetGeomType(wkbNone);
228 382 : m_poFeatureDefn->Reference();
229 382 : m_pszCreationGeomFormat =
230 382 : (pszGeomFormat) ? CPLStrdup(pszGeomFormat) : nullptr;
231 382 : if (eGeomType != wkbNone)
232 : {
233 259 : if (nSRSId == UNINITIALIZED_SRID)
234 0 : nSRSId = m_poDS->GetUndefinedSRID();
235 259 : OGRSQLiteGeomFormat eGeomFormat = GetGeomFormat(pszGeomFormat);
236 : auto poGeomFieldDefn =
237 259 : std::make_unique<OGRSQLiteGeomFieldDefn>(pszGeometryName, -1);
238 259 : poGeomFieldDefn->SetType(eGeomType);
239 259 : poGeomFieldDefn->m_nSRSId = nSRSId;
240 259 : poGeomFieldDefn->m_eGeomFormat = eGeomFormat;
241 259 : poGeomFieldDefn->SetSpatialRef(poSRS);
242 259 : m_poFeatureDefn->AddGeomFieldDefn(std::move(poGeomFieldDefn));
243 : }
244 382 : }
245 :
246 : /************************************************************************/
247 : /* GetName() */
248 : /************************************************************************/
249 :
250 16640 : const char *OGRSQLiteTableLayer::GetName()
251 : {
252 16640 : return GetDescription();
253 : }
254 :
255 : /************************************************************************/
256 : /* GetMetadata() */
257 : /************************************************************************/
258 :
259 14 : char **OGRSQLiteTableLayer::GetMetadata(const char *pszDomain)
260 :
261 : {
262 : // Update GetMetadataItem() optimization that skips calling GetMetadata()
263 : // when key != OLMD_FID64 if we add more metadata items.
264 :
265 14 : GetLayerDefn();
266 14 : if (!m_bHasTriedDetectingFID64 && m_pszFIDColumn != nullptr)
267 : {
268 9 : m_bHasTriedDetectingFID64 = true;
269 :
270 : /* --------------------------------------------------------------------
271 : */
272 : /* Find if the FID holds 64bit values */
273 : /* --------------------------------------------------------------------
274 : */
275 :
276 : // Normally the fid should be AUTOINCREMENT, so check sqlite_sequence
277 9 : OGRErr err = OGRERR_NONE;
278 : char *pszSQL =
279 9 : sqlite3_mprintf("SELECT seq FROM sqlite_sequence WHERE name = '%q'",
280 : m_pszTableName);
281 9 : CPLPushErrorHandler(CPLQuietErrorHandler);
282 9 : GIntBig nMaxId = SQLGetInteger64(m_poDS->GetDB(), pszSQL, &err);
283 9 : CPLPopErrorHandler();
284 9 : sqlite3_free(pszSQL);
285 9 : if (err != OGRERR_NONE)
286 : {
287 2 : CPLErrorReset();
288 :
289 : // In case of error, fallback to taking the MAX of the FID
290 2 : pszSQL = sqlite3_mprintf("SELECT MAX(\"%w\") FROM \"%w\"",
291 : m_pszFIDColumn, m_pszTableName);
292 :
293 2 : nMaxId = SQLGetInteger64(m_poDS->GetDB(), pszSQL, nullptr);
294 2 : sqlite3_free(pszSQL);
295 : }
296 9 : if (nMaxId > INT_MAX)
297 2 : OGRLayer::SetMetadataItem(OLMD_FID64, "YES");
298 : }
299 :
300 14 : return OGRSQLiteLayer::GetMetadata(pszDomain);
301 : }
302 :
303 : /************************************************************************/
304 : /* GetMetadataItem() */
305 : /************************************************************************/
306 :
307 13 : const char *OGRSQLiteTableLayer::GetMetadataItem(const char *pszName,
308 : const char *pszDomain)
309 : {
310 13 : if (!((pszDomain == nullptr || EQUAL(pszDomain, "")) &&
311 11 : EQUAL(pszName, OLMD_FID64)))
312 6 : return nullptr;
313 7 : return CSLFetchNameValue(GetMetadata(pszDomain), pszName);
314 : }
315 :
316 : /************************************************************************/
317 : /* EstablishFeatureDefn() */
318 : /************************************************************************/
319 :
320 1107 : CPLErr OGRSQLiteTableLayer::EstablishFeatureDefn(const char *pszGeomCol,
321 : bool bMayEmitError)
322 : {
323 1107 : sqlite3 *hDB = m_poDS->GetDB();
324 :
325 : /* -------------------------------------------------------------------- */
326 : /* Get the column definitions for this table. */
327 : /* -------------------------------------------------------------------- */
328 1107 : bool bHasRowId = m_bIsTable;
329 :
330 : // SELECT .. FROM ... LIMIT ... is broken on VirtualShape tables with
331 : // spatialite 5.0.1 and sqlite 3.38.0
332 : const char *pszSQLConst =
333 1107 : CPLSPrintf(m_bIsVirtualShape ? "SELECT %s* FROM '%s'"
334 : : "SELECT %s* FROM '%s' LIMIT 1",
335 1107 : m_bIsTable ? "_rowid_, " : "", m_pszEscapedTableName);
336 :
337 1107 : sqlite3_stmt *hColStmt = nullptr;
338 1107 : int rc = sqlite3_prepare_v2(hDB, pszSQLConst, -1, &hColStmt, nullptr);
339 1107 : if (rc != SQLITE_OK)
340 : {
341 293 : const char *pszErrMsg = sqlite3_errmsg(hDB);
342 293 : if (m_bIsTable && pszErrMsg && strstr(pszErrMsg, "_rowid_") != nullptr)
343 : {
344 : // This is likely a table WITHOUT ROWID
345 1 : bHasRowId = false;
346 1 : sqlite3_finalize(hColStmt);
347 1 : hColStmt = nullptr;
348 : pszSQLConst =
349 1 : CPLSPrintf("SELECT * FROM '%s' LIMIT 1", m_pszEscapedTableName);
350 1 : rc = sqlite3_prepare_v2(hDB, pszSQLConst, -1, &hColStmt, nullptr);
351 : }
352 293 : if (rc != SQLITE_OK)
353 : {
354 292 : if (bMayEmitError)
355 : {
356 289 : CPLError(
357 : CE_Failure, CPLE_AppDefined,
358 : "Unable to query table %s for column definitions : %s.",
359 : m_pszTableName, sqlite3_errmsg(hDB));
360 : }
361 292 : return CE_Failure;
362 : }
363 : }
364 :
365 815 : rc = sqlite3_step(hColStmt);
366 815 : if (rc != SQLITE_DONE && rc != SQLITE_ROW)
367 : {
368 64 : CPLError(CE_Failure, CPLE_AppDefined,
369 : "In Initialize(): sqlite3_step(%s):\n %s", pszSQLConst,
370 : sqlite3_errmsg(hDB));
371 64 : sqlite3_finalize(hColStmt);
372 64 : return CE_Failure;
373 : }
374 :
375 : /* -------------------------------------------------------------------- */
376 : /* What should we use as FID? If there is a primary key */
377 : /* integer field, then this will be used as the _rowid_, and we */
378 : /* will pick up the real column name here. */
379 : /* */
380 : /* Note that the select _rowid_ will return the real column */
381 : /* name if the rowid corresponds to another primary key */
382 : /* column. */
383 : /* -------------------------------------------------------------------- */
384 751 : if (bHasRowId)
385 : {
386 749 : CPLFree(m_pszFIDColumn);
387 749 : m_pszFIDColumn =
388 749 : CPLStrdup(SQLUnescape(sqlite3_column_name(hColStmt, 0)));
389 : }
390 :
391 : /* -------------------------------------------------------------------- */
392 : /* Collect the rest of the fields. */
393 : /* -------------------------------------------------------------------- */
394 751 : if (pszGeomCol)
395 : {
396 592 : std::set<CPLString> aosGeomCols;
397 296 : aosGeomCols.insert(pszGeomCol);
398 : std::set<CPLString> aosIgnoredCols(
399 592 : m_poDS->GetGeomColsForTable(m_pszTableName));
400 296 : aosIgnoredCols.erase(pszGeomCol);
401 296 : BuildFeatureDefn(GetDescription(), false, hColStmt, &aosGeomCols,
402 : aosIgnoredCols);
403 : }
404 : else
405 : {
406 910 : std::set<CPLString> aosIgnoredCols;
407 : const std::set<CPLString> &aosGeomCols(
408 455 : m_poDS->GetGeomColsForTable(m_pszTableName));
409 455 : BuildFeatureDefn(GetDescription(), false, hColStmt,
410 455 : (m_bIsVirtualShape) ? nullptr : &aosGeomCols,
411 : aosIgnoredCols);
412 : }
413 751 : sqlite3_finalize(hColStmt);
414 :
415 : /* -------------------------------------------------------------------- */
416 : /* Set the properties of the geometry column. */
417 : /* -------------------------------------------------------------------- */
418 751 : bool bHasSpatialiteCol = false;
419 1000 : for (int i = 0; i < m_poFeatureDefn->GetGeomFieldCount(); i++)
420 : {
421 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
422 249 : m_poFeatureDefn->myGetGeomFieldDefn(i);
423 249 : poGeomFieldDefn->m_nSRSId = m_poDS->GetUndefinedSRID();
424 :
425 249 : if (m_poDS->IsSpatialiteDB())
426 : {
427 187 : if (m_poDS->HasSpatialite4Layout())
428 : {
429 177 : pszSQLConst = CPLSPrintf(
430 : "SELECT srid, geometry_type, coord_dimension, "
431 : "spatial_index_enabled FROM geometry_columns WHERE "
432 : "lower(f_table_name) = lower('%s') AND "
433 : "lower(f_geometry_column) = lower('%s')",
434 : m_pszEscapedTableName,
435 354 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
436 : }
437 : else
438 : {
439 10 : pszSQLConst = CPLSPrintf(
440 : "SELECT srid, type, coord_dimension, spatial_index_enabled "
441 : "FROM geometry_columns WHERE lower(f_table_name) = "
442 : "lower('%s') AND lower(f_geometry_column) = lower('%s')",
443 : m_pszEscapedTableName,
444 20 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
445 : }
446 : }
447 : else
448 : {
449 62 : pszSQLConst = CPLSPrintf(
450 : "SELECT srid, geometry_type, coord_dimension, geometry_format "
451 : "FROM geometry_columns WHERE lower(f_table_name) = lower('%s') "
452 : "AND lower(f_geometry_column) = lower('%s')",
453 : m_pszEscapedTableName,
454 124 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
455 : }
456 249 : char *pszErrMsg = nullptr;
457 249 : int nRowCount = 0, nColCount = 0;
458 249 : char **papszResult = nullptr;
459 249 : rc = sqlite3_get_table(hDB, pszSQLConst, &papszResult, &nRowCount,
460 : &nColCount, &pszErrMsg);
461 249 : OGRwkbGeometryType eGeomType = wkbUnknown;
462 249 : OGRSQLiteGeomFormat eGeomFormat = OSGF_None;
463 249 : if (rc == SQLITE_OK && nRowCount == 1)
464 : {
465 246 : char **papszRow = papszResult + nColCount;
466 246 : if (papszRow[1] == nullptr || papszRow[2] == nullptr)
467 : {
468 0 : CPLDebug("SQLite", "Did not get expected col value");
469 0 : sqlite3_free_table(papszResult);
470 0 : continue;
471 : }
472 246 : if (papszRow[0] != nullptr)
473 194 : poGeomFieldDefn->m_nSRSId = atoi(papszRow[0]);
474 246 : if (m_poDS->IsSpatialiteDB())
475 : {
476 184 : if (papszRow[3] != nullptr)
477 184 : poGeomFieldDefn->m_bHasSpatialIndex =
478 184 : atoi(papszRow[3]) != 0;
479 184 : if (m_poDS->HasSpatialite4Layout())
480 : {
481 174 : int nGeomType = atoi(papszRow[1]);
482 :
483 174 : if (nGeomType >= 0 && nGeomType <= 7) /* XY */
484 113 : eGeomType = (OGRwkbGeometryType)nGeomType;
485 61 : else if (nGeomType >= 1000 && nGeomType <= 1007) /* XYZ */
486 29 : eGeomType = wkbSetZ(wkbFlatten(nGeomType));
487 32 : else if (nGeomType >= 2000 && nGeomType <= 2007) /* XYM */
488 16 : eGeomType = wkbSetM(wkbFlatten(nGeomType));
489 16 : else if (nGeomType >= 3000 && nGeomType <= 3007) /* XYZM */
490 16 : eGeomType = wkbSetM(wkbSetZ(wkbFlatten(nGeomType)));
491 : }
492 : else
493 : {
494 10 : eGeomType = OGRFromOGCGeomType(papszRow[1]);
495 :
496 10 : if (strcmp(papszRow[2], "XYZ") == 0 ||
497 10 : strcmp(papszRow[2], "3") ==
498 : 0) // SpatiaLite's own 3D geometries
499 0 : eGeomType = wkbSetZ(eGeomType);
500 10 : else if (strcmp(papszRow[2], "XYM") == 0)
501 0 : eGeomType = wkbSetM(eGeomType);
502 10 : else if (strcmp(papszRow[2], "XYZM") ==
503 : 0) // M coordinate declared
504 0 : eGeomType = wkbSetM(wkbSetZ(eGeomType));
505 : }
506 184 : eGeomFormat = OSGF_SpatiaLite;
507 : }
508 : else
509 : {
510 62 : eGeomType = (OGRwkbGeometryType)atoi(papszRow[1]);
511 62 : if (atoi(papszRow[2]) > 2)
512 7 : eGeomType = wkbSetZ(eGeomType);
513 62 : eGeomFormat = GetGeomFormat(papszRow[3]);
514 : }
515 : }
516 249 : sqlite3_free_table(papszResult);
517 249 : sqlite3_free(pszErrMsg);
518 :
519 249 : poGeomFieldDefn->m_eGeomFormat = eGeomFormat;
520 249 : poGeomFieldDefn->SetType(eGeomType);
521 498 : poGeomFieldDefn->SetSpatialRef(
522 249 : m_poDS->FetchSRS(poGeomFieldDefn->m_nSRSId));
523 :
524 : // cppcheck-suppress knownConditionTrueFalse
525 249 : if (eGeomFormat == OSGF_SpatiaLite)
526 185 : bHasSpatialiteCol = true;
527 : }
528 :
529 366 : if (bHasSpatialiteCol && m_poDS->IsSpatialiteLoaded() &&
530 183 : m_poDS->GetSpatialiteVersionNumber() <
531 1117 : OGRSQLiteDataSource::MakeSpatialiteVersionNumber(2, 4, 0) &&
532 0 : m_poDS->GetUpdate())
533 : {
534 : // we need to test version required by Spatialite TRIGGERs
535 : // hColStmt = NULL;
536 : pszSQLConst =
537 0 : CPLSPrintf("SELECT sql FROM sqlite_master WHERE type = 'trigger' "
538 : "AND tbl_name = '%s' AND sql LIKE '%%RTreeAlign%%'",
539 : m_pszEscapedTableName);
540 :
541 : int nRowTriggerCount, nColTriggerCount;
542 : char **papszTriggerResult, *pszErrMsg;
543 :
544 0 : /* rc = */ sqlite3_get_table(hDB, pszSQLConst, &papszTriggerResult,
545 : &nRowTriggerCount, &nColTriggerCount,
546 : &pszErrMsg);
547 0 : if (nRowTriggerCount >= 1)
548 : {
549 : // obsolete library version not supporting new triggers
550 : // enforcing ReadOnly mode
551 0 : CPLDebug("SQLITE", "Enforcing ReadOnly mode : obsolete library "
552 : "version not supporting new triggers");
553 0 : m_poDS->DisableUpdate();
554 : }
555 :
556 0 : sqlite3_free_table(papszTriggerResult);
557 : }
558 : /* -------------------------------------------------------------------- */
559 : /* Check if there are default values and nullable status */
560 : /* -------------------------------------------------------------------- */
561 :
562 751 : char **papszResult = nullptr;
563 751 : int nRowCount = 0;
564 751 : int nColCount = 0;
565 751 : char *pszErrMsg = nullptr;
566 : /* #|name|type|notnull|default|pk */
567 751 : char *pszSQL = sqlite3_mprintf("PRAGMA table_info('%q')", m_pszTableName);
568 751 : rc = sqlite3_get_table(hDB, pszSQL, &papszResult, &nRowCount, &nColCount,
569 : &pszErrMsg);
570 751 : sqlite3_free(pszSQL);
571 751 : if (rc != SQLITE_OK)
572 : {
573 0 : sqlite3_free(pszErrMsg);
574 : }
575 : else
576 : {
577 751 : if (nColCount == 6)
578 : {
579 : const std::set<std::string> uniqueFieldsUC(
580 1502 : SQLGetUniqueFieldUCConstraints(hDB, m_pszTableName));
581 3453 : for (int i = 0; i < nRowCount; i++)
582 : {
583 2702 : const char *pszName = papszResult[(i + 1) * 6 + 1];
584 2702 : const char *pszNotNull = papszResult[(i + 1) * 6 + 3];
585 2702 : const char *pszDefault = papszResult[(i + 1) * 6 + 4];
586 : const int idx = pszName != nullptr
587 2702 : ? m_poFeatureDefn->GetFieldIndex(pszName)
588 2702 : : -1;
589 2702 : if (pszDefault != nullptr)
590 : {
591 18 : if (idx >= 0)
592 : {
593 : OGRFieldDefn *poFieldDefn =
594 17 : m_poFeatureDefn->GetFieldDefn(idx);
595 17 : if (poFieldDefn->GetType() == OFTString &&
596 3 : !EQUAL(pszDefault, "NULL") &&
597 3 : !STARTS_WITH_CI(pszDefault, "CURRENT_") &&
598 20 : pszDefault[0] != '(' && pszDefault[0] != '\'' &&
599 0 : CPLGetValueType(pszDefault) == CPL_VALUE_STRING)
600 : {
601 0 : CPLString osDefault("'");
602 : char *pszTmp =
603 0 : CPLEscapeString(pszDefault, -1, CPLES_SQL);
604 0 : osDefault += pszTmp;
605 0 : CPLFree(pszTmp);
606 0 : osDefault += "'";
607 0 : poFieldDefn->SetDefault(osDefault);
608 : }
609 32 : else if ((poFieldDefn->GetType() == OFTDate ||
610 15 : poFieldDefn->GetType() == OFTDateTime) &&
611 9 : !EQUAL(pszDefault, "NULL") &&
612 9 : !STARTS_WITH_CI(pszDefault, "CURRENT_") &&
613 5 : pszDefault[0] != '(' &&
614 5 : pszDefault[0] != '\'' &&
615 1 : !(pszDefault[0] >= '0' &&
616 35 : pszDefault[0] <= '9') &&
617 1 : CPLGetValueType(pszDefault) ==
618 : CPL_VALUE_STRING)
619 : {
620 2 : CPLString osDefault("(");
621 1 : osDefault += pszDefault;
622 1 : osDefault += ")";
623 1 : poFieldDefn->SetDefault(osDefault);
624 : }
625 : else
626 16 : poFieldDefn->SetDefault(pszDefault);
627 : }
628 : }
629 2702 : if (pszName != nullptr && pszNotNull != nullptr &&
630 2702 : EQUAL(pszNotNull, "1"))
631 : {
632 649 : if (idx >= 0)
633 514 : m_poFeatureDefn->GetFieldDefn(idx)->SetNullable(0);
634 : else
635 : {
636 : const int geomFieldIdx =
637 135 : m_poFeatureDefn->GetGeomFieldIndex(pszName);
638 135 : if (geomFieldIdx >= 0)
639 3 : m_poFeatureDefn->GetGeomFieldDefn(geomFieldIdx)
640 3 : ->SetNullable(0);
641 : }
642 : }
643 4408 : if (idx >= 0 &&
644 4408 : uniqueFieldsUC.find(CPLString(pszName).toupper()) !=
645 4408 : uniqueFieldsUC.end())
646 : {
647 8 : m_poFeatureDefn->GetFieldDefn(idx)->SetUnique(TRUE);
648 : }
649 : }
650 : }
651 751 : sqlite3_free_table(papszResult);
652 : }
653 :
654 751 : nRowCount = 0;
655 751 : nColCount = 0;
656 751 : papszResult = nullptr;
657 :
658 751 : pszSQL = sqlite3_mprintf(
659 : "SELECT sql FROM sqlite_master WHERE type='table' AND name='%q'",
660 : m_pszTableName);
661 751 : rc = sqlite3_get_table(hDB, pszSQL, &papszResult, &nRowCount, &nColCount,
662 : nullptr);
663 751 : sqlite3_free(pszSQL);
664 751 : if (rc == SQLITE_OK && nRowCount == 1 && nColCount == 1)
665 : {
666 750 : const char *pszCreateTableSQL = papszResult[1];
667 750 : const char *pszLastParenthesis = strrchr(pszCreateTableSQL, ')');
668 750 : if (pszLastParenthesis)
669 : {
670 750 : m_bStrict = CPLString(pszLastParenthesis + 1).ifind("STRICT") !=
671 : std::string::npos;
672 : #ifdef DEBUG_VERBOSE
673 : if (m_bStrict)
674 : CPLDebug("SQLite", "Table %s has STRICT mode", m_pszTableName);
675 : #endif
676 : }
677 :
678 750 : const int nFieldCount = m_poFeatureDefn->GetFieldCount();
679 2455 : for (int i = 0; i < nFieldCount; ++i)
680 : {
681 1705 : auto poFieldDefn = m_poFeatureDefn->GetFieldDefn(i);
682 1705 : if (poFieldDefn->GetType() == OFTInteger)
683 : {
684 : // In strict mode, the number of allowed types is severely
685 : // restricted, so interpret INTEGER as Int64 by default, unless
686 : // a check constraint makes it clear it is Int32
687 341 : if (m_bStrict)
688 4 : poFieldDefn->SetType(OFTInteger64);
689 341 : if (strstr(pszCreateTableSQL,
690 0 : ("CHECK (\"" +
691 682 : CPLString(poFieldDefn->GetNameRef())
692 682 : .replaceAll('"', "\"\"") +
693 : "\" BETWEEN -2147483648 AND 2147483647)")
694 341 : .c_str()))
695 : {
696 2 : poFieldDefn->SetType(OFTInteger);
697 : }
698 339 : else if (strstr(pszCreateTableSQL,
699 0 : ("CHECK (\"" +
700 678 : CPLString(poFieldDefn->GetNameRef())
701 678 : .replaceAll('"', "\"\"") +
702 : "\" BETWEEN -9223372036854775808 AND "
703 : "9223372036854775807)")
704 339 : .c_str()))
705 : {
706 2 : poFieldDefn->SetType(OFTInteger64);
707 : }
708 : }
709 1364 : else if (poFieldDefn->GetType() == OFTString)
710 : {
711 688 : if (strstr(pszCreateTableSQL,
712 0 : ("CHECK (\"" +
713 1376 : CPLString(poFieldDefn->GetNameRef())
714 1376 : .replaceAll('"', "\"\"") +
715 : "\" LIKE "
716 : "'[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2]["
717 : "0-9]:[0-5][0-9]:[0-6][0-9]*')")
718 688 : .c_str()))
719 : {
720 1 : poFieldDefn->SetType(OFTDateTime);
721 : }
722 687 : else if (strstr(
723 : pszCreateTableSQL,
724 0 : ("CHECK (\"" +
725 1374 : CPLString(poFieldDefn->GetNameRef())
726 1374 : .replaceAll('"', "\"\"") +
727 : "\" LIKE "
728 : "'[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T')")
729 687 : .c_str()))
730 : {
731 1 : poFieldDefn->SetType(OFTDate);
732 : }
733 686 : else if (strstr(pszCreateTableSQL,
734 0 : ("CHECK (\"" +
735 1372 : CPLString(poFieldDefn->GetNameRef())
736 1372 : .replaceAll('"', "\"\"") +
737 : "\" LIKE '[0-2][0-9]:[0-5][0-9]:[0-6][0-9]*')")
738 686 : .c_str()))
739 : {
740 1 : poFieldDefn->SetType(OFTTime);
741 : }
742 : }
743 : }
744 : }
745 751 : sqlite3_free_table(papszResult);
746 :
747 751 : return CE_None;
748 : }
749 :
750 : /************************************************************************/
751 : /* RecomputeOrdinals() */
752 : /************************************************************************/
753 :
754 418 : OGRErr OGRSQLiteTableLayer::RecomputeOrdinals()
755 : {
756 418 : sqlite3 *hDB = m_poDS->GetDB();
757 418 : sqlite3_stmt *hColStmt = nullptr;
758 : /* -------------------------------------------------------------------- */
759 : /* Get the column definitions for this table. */
760 : /* -------------------------------------------------------------------- */
761 :
762 418 : const char *pszSQL = CPLSPrintf(
763 : "SELECT %s* FROM '%s' LIMIT 1",
764 418 : m_pszFIDColumn != nullptr ? "_rowid_, " : "", m_pszEscapedTableName);
765 :
766 418 : int rc = sqlite3_prepare_v2(hDB, pszSQL, -1, &hColStmt, nullptr);
767 418 : if (rc != SQLITE_OK)
768 : {
769 0 : CPLError(CE_Failure, CPLE_AppDefined,
770 : "Unable to query table %s for column definitions : %s.",
771 : m_pszTableName, sqlite3_errmsg(hDB));
772 :
773 0 : return OGRERR_FAILURE;
774 : }
775 :
776 418 : rc = sqlite3_step(hColStmt);
777 418 : if (rc != SQLITE_DONE && rc != SQLITE_ROW)
778 : {
779 0 : CPLError(CE_Failure, CPLE_AppDefined,
780 : "In Initialize(): sqlite3_step(%s):\n %s", pszSQL,
781 : sqlite3_errmsg(hDB));
782 0 : sqlite3_finalize(hColStmt);
783 0 : return OGRERR_FAILURE;
784 : }
785 :
786 418 : int nRawColumns = sqlite3_column_count(hColStmt);
787 :
788 418 : CPLFree(m_panFieldOrdinals);
789 418 : m_panFieldOrdinals =
790 418 : (int *)CPLMalloc(sizeof(int) * m_poFeatureDefn->GetFieldCount());
791 418 : int nCountFieldOrdinals = 0;
792 418 : int nCountGeomFieldOrdinals = 0;
793 418 : m_iFIDCol = -1;
794 :
795 2643 : for (int iCol = 0; iCol < nRawColumns; iCol++)
796 : {
797 2225 : CPLString osName = SQLUnescape(sqlite3_column_name(hColStmt, iCol));
798 2225 : int nIdx = m_poFeatureDefn->GetFieldIndex(osName);
799 2225 : if (m_pszFIDColumn != nullptr && strcmp(osName, m_pszFIDColumn) == 0)
800 : {
801 836 : if (m_iFIDCol < 0)
802 : {
803 418 : m_iFIDCol = iCol;
804 418 : if (nIdx >=
805 : 0) /* in case it has also been created as a regular field */
806 1 : nCountFieldOrdinals++;
807 : }
808 836 : continue;
809 : }
810 1389 : if (nIdx >= 0)
811 : {
812 1084 : m_panFieldOrdinals[nIdx] = iCol;
813 1084 : nCountFieldOrdinals++;
814 : }
815 : else
816 : {
817 305 : nIdx = m_poFeatureDefn->GetGeomFieldIndex(osName);
818 305 : if (nIdx >= 0)
819 : {
820 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
821 305 : m_poFeatureDefn->myGetGeomFieldDefn(nIdx);
822 305 : poGeomFieldDefn->m_iCol = iCol;
823 305 : nCountGeomFieldOrdinals++;
824 : }
825 : }
826 : }
827 : (void)nCountFieldOrdinals;
828 418 : CPLAssert(nCountFieldOrdinals == m_poFeatureDefn->GetFieldCount());
829 : (void)nCountGeomFieldOrdinals;
830 418 : CPLAssert(nCountGeomFieldOrdinals == m_poFeatureDefn->GetGeomFieldCount());
831 418 : CPLAssert(m_pszFIDColumn == nullptr || m_iFIDCol >= 0);
832 :
833 418 : sqlite3_finalize(hColStmt);
834 :
835 418 : return OGRERR_NONE;
836 : }
837 :
838 : /************************************************************************/
839 : /* GetLayerDefn() */
840 : /************************************************************************/
841 :
842 15901 : OGRFeatureDefn *OGRSQLiteTableLayer::GetLayerDefn()
843 : {
844 15901 : if (m_poFeatureDefn)
845 15093 : return m_poFeatureDefn;
846 :
847 808 : EstablishFeatureDefn(nullptr, /* bMayEmitError = */ true);
848 :
849 808 : if (m_poFeatureDefn == nullptr)
850 : {
851 353 : m_bLayerDefnError = true;
852 :
853 353 : m_poFeatureDefn = new OGRSQLiteFeatureDefn(GetDescription());
854 353 : m_poFeatureDefn->Reference();
855 : }
856 : else
857 455 : LoadStatistics();
858 :
859 808 : return m_poFeatureDefn;
860 : }
861 :
862 : /************************************************************************/
863 : /* ResetStatement() */
864 : /************************************************************************/
865 :
866 755 : OGRErr OGRSQLiteTableLayer::ResetStatement()
867 :
868 : {
869 1510 : CPLString osSQL;
870 :
871 755 : if (m_bDeferredCreation)
872 0 : RunDeferredCreationIfNecessary();
873 :
874 755 : ClearStatement();
875 :
876 755 : m_iNextShapeId = 0;
877 :
878 : osSQL.Printf("SELECT %s* FROM '%s' %s",
879 755 : m_pszFIDColumn != nullptr ? "_rowid_, " : "",
880 755 : m_pszEscapedTableName, m_osWHERE.c_str());
881 : #ifdef DEBUG_VERBOSE
882 : CPLDebug("SQLite", "%s", osSQL.c_str());
883 : #endif
884 :
885 : const int rc =
886 755 : sqlite3_prepare_v2(m_poDS->GetDB(), osSQL, -1, &m_hStmt, nullptr);
887 755 : if (rc == SQLITE_OK)
888 : {
889 754 : return OGRERR_NONE;
890 : }
891 :
892 1 : CPLError(CE_Failure, CPLE_AppDefined,
893 : "In ResetStatement(): sqlite3_prepare_v2(%s):\n %s",
894 1 : osSQL.c_str(), sqlite3_errmsg(m_poDS->GetDB()));
895 1 : m_hStmt = nullptr;
896 1 : return OGRERR_FAILURE;
897 : }
898 :
899 : /************************************************************************/
900 : /* GetNextFeature() */
901 : /************************************************************************/
902 :
903 3229 : OGRFeature *OGRSQLiteTableLayer::GetNextFeature()
904 :
905 : {
906 3229 : if (m_bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE)
907 0 : return nullptr;
908 :
909 3229 : if (HasLayerDefnError())
910 1 : return nullptr;
911 :
912 3228 : OGRFeature *poFeature = OGRSQLiteLayer::GetNextFeature();
913 3228 : if (poFeature && m_iFIDAsRegularColumnIndex >= 0)
914 : {
915 1 : poFeature->SetField(m_iFIDAsRegularColumnIndex, poFeature->GetFID());
916 : }
917 3228 : return poFeature;
918 : }
919 :
920 : /************************************************************************/
921 : /* GetFeature() */
922 : /************************************************************************/
923 :
924 35 : OGRFeature *OGRSQLiteTableLayer::GetFeature(GIntBig nFeatureId)
925 :
926 : {
927 35 : if (m_bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE)
928 0 : return nullptr;
929 :
930 35 : if (HasLayerDefnError())
931 0 : return nullptr;
932 :
933 : /* -------------------------------------------------------------------- */
934 : /* If we don't have an explicit FID column, just read through */
935 : /* the result set iteratively to find our target. */
936 : /* -------------------------------------------------------------------- */
937 35 : if (m_pszFIDColumn == nullptr)
938 1 : return OGRSQLiteLayer::GetFeature(nFeatureId);
939 :
940 : /* -------------------------------------------------------------------- */
941 : /* Setup explicit query statement to fetch the record we want. */
942 : /* -------------------------------------------------------------------- */
943 68 : CPLString osSQL;
944 :
945 34 : ClearStatement();
946 :
947 34 : m_iNextShapeId = nFeatureId;
948 :
949 : osSQL.Printf("SELECT _rowid_, * FROM '%s' WHERE \"%s\" = " CPL_FRMT_GIB,
950 : m_pszEscapedTableName,
951 34 : SQLEscapeLiteral(m_pszFIDColumn).c_str(), nFeatureId);
952 :
953 34 : CPLDebug("OGR_SQLITE", "exec(%s)", osSQL.c_str());
954 :
955 : const int rc =
956 34 : sqlite3_prepare_v2(m_poDS->GetDB(), osSQL,
957 34 : static_cast<int>(osSQL.size()), &m_hStmt, nullptr);
958 34 : if (rc != SQLITE_OK)
959 : {
960 0 : CPLError(CE_Failure, CPLE_AppDefined,
961 : "In GetFeature(): sqlite3_prepare_v2(%s):\n %s",
962 0 : osSQL.c_str(), sqlite3_errmsg(m_poDS->GetDB()));
963 :
964 0 : return nullptr;
965 : }
966 : /* -------------------------------------------------------------------- */
967 : /* Get the feature if possible. */
968 : /* -------------------------------------------------------------------- */
969 34 : OGRFeature *poFeature = GetNextRawFeature();
970 :
971 34 : ResetReading();
972 :
973 34 : return poFeature;
974 : }
975 :
976 : /************************************************************************/
977 : /* SetAttributeFilter() */
978 : /************************************************************************/
979 :
980 359 : OGRErr OGRSQLiteTableLayer::SetAttributeFilter(const char *pszQuery)
981 :
982 : {
983 359 : CPLFree(m_pszAttrQueryString);
984 359 : m_pszAttrQueryString = (pszQuery) ? CPLStrdup(pszQuery) : nullptr;
985 :
986 359 : if (pszQuery == nullptr)
987 60 : m_osQuery = "";
988 : else
989 299 : m_osQuery = pszQuery;
990 :
991 359 : BuildWhere();
992 :
993 359 : ResetReading();
994 :
995 359 : return OGRERR_NONE;
996 : }
997 :
998 : /************************************************************************/
999 : /* SetSpatialFilter() */
1000 : /************************************************************************/
1001 :
1002 42 : void OGRSQLiteTableLayer::SetSpatialFilter(OGRGeometry *poGeomIn)
1003 : {
1004 42 : SetSpatialFilter(0, poGeomIn);
1005 42 : }
1006 :
1007 68 : void OGRSQLiteTableLayer::SetSpatialFilter(int iGeomField,
1008 : OGRGeometry *poGeomIn)
1009 :
1010 : {
1011 68 : if (iGeomField == 0)
1012 : {
1013 61 : m_iGeomFieldFilter = 0;
1014 : }
1015 : else
1016 : {
1017 7 : if (iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount())
1018 : {
1019 6 : CPLError(CE_Failure, CPLE_AppDefined,
1020 : "Invalid geometry field index : %d", iGeomField);
1021 6 : return;
1022 : }
1023 :
1024 1 : m_iGeomFieldFilter = iGeomField;
1025 : }
1026 :
1027 62 : if (InstallFilter(poGeomIn))
1028 : {
1029 31 : BuildWhere();
1030 :
1031 31 : ResetReading();
1032 : }
1033 : }
1034 :
1035 : /************************************************************************/
1036 : /* CheckSpatialIndexTable() */
1037 : /************************************************************************/
1038 :
1039 133 : bool OGRSQLiteTableLayer::CheckSpatialIndexTable(int iGeomCol)
1040 : {
1041 133 : GetLayerDefn();
1042 133 : if (iGeomCol < 0 || iGeomCol >= m_poFeatureDefn->GetGeomFieldCount())
1043 0 : return false;
1044 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
1045 133 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomCol);
1046 261 : if (HasSpatialIndex(iGeomCol) &&
1047 128 : !poGeomFieldDefn->m_bHasCheckedSpatialIndexTable)
1048 : {
1049 41 : poGeomFieldDefn->m_bHasCheckedSpatialIndexTable = true;
1050 41 : char **papszResult = nullptr;
1051 41 : int nRowCount = 0;
1052 41 : int nColCount = 0;
1053 41 : char *pszErrMsg = nullptr;
1054 :
1055 82 : CPLString osSQL;
1056 :
1057 : /* This will ensure that RTree support is available */
1058 : osSQL.Printf("SELECT pkid FROM 'idx_%s_%s' WHERE xmax > 0 AND xmin < 0 "
1059 : "AND ymax > 0 AND ymin < 0",
1060 : m_pszEscapedTableName,
1061 41 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
1062 :
1063 41 : int rc = sqlite3_get_table(m_poDS->GetDB(), osSQL.c_str(), &papszResult,
1064 : &nRowCount, &nColCount, &pszErrMsg);
1065 :
1066 41 : if (rc != SQLITE_OK)
1067 : {
1068 0 : CPLDebug("SQLITE",
1069 : "Count not find or use idx_%s_%s layer (%s). Disabling "
1070 : "spatial index",
1071 : m_pszEscapedTableName, poGeomFieldDefn->GetNameRef(),
1072 : pszErrMsg);
1073 0 : sqlite3_free(pszErrMsg);
1074 0 : poGeomFieldDefn->m_bHasSpatialIndex = false;
1075 : }
1076 : else
1077 : {
1078 41 : sqlite3_free_table(papszResult);
1079 : }
1080 : }
1081 :
1082 133 : return poGeomFieldDefn->m_bHasSpatialIndex;
1083 : }
1084 :
1085 : /************************************************************************/
1086 : /* HasFastSpatialFilter() */
1087 : /************************************************************************/
1088 :
1089 8 : bool OGRSQLiteTableLayer::HasFastSpatialFilter(int iGeomCol)
1090 : {
1091 16 : OGRPolygon oFakePoly;
1092 8 : const char *pszWKT = "POLYGON((0 0,0 1,1 1,1 0,0 0))";
1093 8 : oFakePoly.importFromWkt(&pszWKT);
1094 8 : CPLString osSpatialWhere = GetSpatialWhere(iGeomCol, &oFakePoly);
1095 16 : return osSpatialWhere.find("ROWID") == 0;
1096 : }
1097 :
1098 : /************************************************************************/
1099 : /* GetSpatialWhere() */
1100 : /************************************************************************/
1101 :
1102 505 : CPLString OGRSQLiteTableLayer::GetSpatialWhere(int iGeomCol,
1103 : OGRGeometry *poFilterGeom)
1104 : {
1105 626 : if (!m_poDS->IsSpatialiteDB() || iGeomCol < 0 ||
1106 121 : iGeomCol >= GetLayerDefn()->GetGeomFieldCount())
1107 384 : return "";
1108 :
1109 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
1110 121 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomCol);
1111 121 : if (poFilterGeom != nullptr && CheckSpatialIndexTable(iGeomCol))
1112 : {
1113 : return FormatSpatialFilterFromRTree(
1114 85 : poFilterGeom, "ROWID", m_pszEscapedTableName,
1115 170 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
1116 : }
1117 :
1118 40 : if (poFilterGeom != nullptr && m_poDS->IsSpatialiteLoaded() &&
1119 4 : !poGeomFieldDefn->m_bHasSpatialIndex)
1120 : {
1121 : return FormatSpatialFilterFromMBR(
1122 8 : poFilterGeom, SQLEscapeName(poGeomFieldDefn->GetNameRef()).c_str());
1123 : }
1124 :
1125 32 : return "";
1126 : }
1127 :
1128 : /************************************************************************/
1129 : /* BuildWhere() */
1130 : /* */
1131 : /* Build the WHERE statement appropriate to the current set of */
1132 : /* criteria (spatial and attribute queries). */
1133 : /************************************************************************/
1134 :
1135 390 : void OGRSQLiteTableLayer::BuildWhere()
1136 :
1137 : {
1138 390 : m_osWHERE = "";
1139 :
1140 : CPLString osSpatialWHERE =
1141 780 : GetSpatialWhere(m_iGeomFieldFilter, m_poFilterGeom);
1142 390 : if (!osSpatialWHERE.empty())
1143 : {
1144 22 : m_osWHERE = "WHERE ";
1145 22 : m_osWHERE += osSpatialWHERE;
1146 : }
1147 :
1148 390 : if (!m_osQuery.empty())
1149 : {
1150 297 : if (m_osWHERE.empty())
1151 : {
1152 293 : m_osWHERE = "WHERE ";
1153 293 : m_osWHERE += m_osQuery;
1154 : }
1155 : else
1156 : {
1157 4 : m_osWHERE += " AND (";
1158 4 : m_osWHERE += m_osQuery;
1159 4 : m_osWHERE += ")";
1160 : }
1161 : }
1162 390 : }
1163 :
1164 : /************************************************************************/
1165 : /* TestCapability() */
1166 : /************************************************************************/
1167 :
1168 1161 : int OGRSQLiteTableLayer::TestCapability(const char *pszCap)
1169 :
1170 : {
1171 1161 : if (EQUAL(pszCap, OLCFastFeatureCount))
1172 90 : return m_poFilterGeom == nullptr || HasSpatialIndex(0);
1173 :
1174 1071 : else if (EQUAL(pszCap, OLCFastSpatialFilter))
1175 2 : return HasSpatialIndex(0);
1176 :
1177 1069 : else if (EQUAL(pszCap, OLCFastGetExtent))
1178 : {
1179 12 : return GetLayerDefn()->GetGeomFieldCount() >= 1 &&
1180 12 : myGetLayerDefn()->myGetGeomFieldDefn(0)->m_bCachedExtentIsValid;
1181 : }
1182 :
1183 1063 : else if (EQUAL(pszCap, OLCRandomRead))
1184 6 : return m_pszFIDColumn != nullptr;
1185 :
1186 1057 : else if (EQUAL(pszCap, OLCSequentialWrite) || EQUAL(pszCap, OLCRandomWrite))
1187 : {
1188 38 : return m_poDS->GetUpdate();
1189 : }
1190 :
1191 1019 : else if (EQUAL(pszCap, OLCDeleteFeature))
1192 : {
1193 6 : return m_poDS->GetUpdate() && m_pszFIDColumn != nullptr;
1194 : }
1195 :
1196 1013 : else if (EQUAL(pszCap, OLCCreateField) || EQUAL(pszCap, OLCCreateGeomField))
1197 38 : return m_poDS->GetUpdate();
1198 :
1199 975 : else if (EQUAL(pszCap, OLCDeleteField))
1200 7 : return m_poDS->GetUpdate();
1201 :
1202 968 : else if (EQUAL(pszCap, OLCAlterFieldDefn))
1203 7 : return m_poDS->GetUpdate();
1204 :
1205 961 : else if (EQUAL(pszCap, OLCReorderFields))
1206 7 : return m_poDS->GetUpdate();
1207 :
1208 954 : else if (EQUAL(pszCap, OLCCurveGeometries))
1209 512 : return m_poDS->TestCapability(ODsCCurveGeometries);
1210 :
1211 442 : else if (EQUAL(pszCap, OLCMeasuredGeometries))
1212 413 : return m_poDS->TestCapability(ODsCMeasuredGeometries);
1213 :
1214 29 : else if (EQUAL(pszCap, OLCZGeometries))
1215 9 : return true;
1216 :
1217 : else
1218 20 : return OGRSQLiteLayer::TestCapability(pszCap);
1219 : }
1220 :
1221 : /************************************************************************/
1222 : /* GetFeatureCount() */
1223 : /************************************************************************/
1224 :
1225 85 : GIntBig OGRSQLiteTableLayer::GetFeatureCount(int bForce)
1226 :
1227 : {
1228 85 : if (HasLayerDefnError())
1229 0 : return 0;
1230 :
1231 85 : if (!TestCapability(OLCFastFeatureCount))
1232 7 : return OGRSQLiteLayer::GetFeatureCount(bForce);
1233 :
1234 78 : if (m_nFeatureCount >= 0 && m_poFilterGeom == nullptr && m_osQuery.empty())
1235 : {
1236 44 : return m_nFeatureCount;
1237 : }
1238 :
1239 : /* -------------------------------------------------------------------- */
1240 : /* Form count SQL. */
1241 : /* -------------------------------------------------------------------- */
1242 34 : const char *pszSQL = nullptr;
1243 :
1244 77 : if (m_poFilterGeom != nullptr &&
1245 34 : CheckSpatialIndexTable(m_iGeomFieldFilter) && m_osQuery.empty())
1246 : {
1247 5 : OGREnvelope sEnvelope;
1248 :
1249 5 : m_poFilterGeom->getEnvelope(&sEnvelope);
1250 : const char *pszGeomCol =
1251 5 : m_poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter)->GetNameRef();
1252 5 : pszSQL = CPLSPrintf("SELECT count(*) FROM 'idx_%s_%s' WHERE "
1253 : "xmax >= %.12f AND xmin <= %.12f AND ymax >= %.12f "
1254 : "AND ymin <= %.12f",
1255 : m_pszEscapedTableName,
1256 5 : SQLEscapeLiteral(pszGeomCol).c_str(),
1257 5 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
1258 5 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
1259 : }
1260 : else
1261 : {
1262 29 : pszSQL = CPLSPrintf("SELECT count(*) FROM '%s' %s",
1263 : m_pszEscapedTableName, m_osWHERE.c_str());
1264 : }
1265 :
1266 34 : CPLDebug("SQLITE", "Running %s", pszSQL);
1267 :
1268 : /* -------------------------------------------------------------------- */
1269 : /* Execute. */
1270 : /* -------------------------------------------------------------------- */
1271 34 : OGRErr eErr = OGRERR_NONE;
1272 34 : GIntBig nResult = SQLGetInteger64(m_poDS->GetDB(), pszSQL, &eErr);
1273 34 : if (eErr == OGRERR_FAILURE)
1274 : {
1275 0 : nResult = -1;
1276 : }
1277 : else
1278 : {
1279 34 : if (m_poFilterGeom == nullptr && m_osQuery.empty())
1280 : {
1281 18 : m_nFeatureCount = nResult;
1282 18 : if (m_poDS->GetUpdate())
1283 12 : ForceStatisticsToBeFlushed();
1284 : }
1285 : }
1286 :
1287 34 : return nResult;
1288 : }
1289 :
1290 : /************************************************************************/
1291 : /* GetExtent() */
1292 : /************************************************************************/
1293 :
1294 48 : OGRErr OGRSQLiteTableLayer::GetExtent(OGREnvelope *psExtent, int bForce)
1295 : {
1296 48 : return GetExtent(0, psExtent, bForce);
1297 : }
1298 :
1299 60 : OGRErr OGRSQLiteTableLayer::GetExtent(int iGeomField, OGREnvelope *psExtent,
1300 : int bForce)
1301 : {
1302 60 : if (HasLayerDefnError())
1303 0 : return OGRERR_FAILURE;
1304 :
1305 : /* -------------------------------------------------------------------- */
1306 : /* If this layer has a none geometry type, then we can */
1307 : /* reasonably assume there are not extents available. */
1308 : /* -------------------------------------------------------------------- */
1309 105 : if (iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() ||
1310 45 : GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone)
1311 : {
1312 15 : if (iGeomField != 0)
1313 : {
1314 6 : CPLError(CE_Failure, CPLE_AppDefined,
1315 : "Invalid geometry field index : %d", iGeomField);
1316 : }
1317 15 : return OGRERR_FAILURE;
1318 : }
1319 :
1320 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
1321 45 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomField);
1322 45 : if (poGeomFieldDefn->m_bCachedExtentIsValid)
1323 : {
1324 10 : *psExtent = poGeomFieldDefn->m_oCachedExtent;
1325 10 : return OGRERR_NONE;
1326 : }
1327 :
1328 69 : if (CheckSpatialIndexTable(iGeomField) &&
1329 34 : !CPLTestBool(CPLGetConfigOption("OGR_SQLITE_EXACT_EXTENT", "NO")))
1330 : {
1331 : const char *pszSQL =
1332 2 : CPLSPrintf("SELECT MIN(xmin), MIN(ymin), "
1333 : "MAX(xmax), MAX(ymax) FROM 'idx_%s_%s'",
1334 : m_pszEscapedTableName,
1335 4 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
1336 :
1337 2 : CPLDebug("SQLITE", "Running %s", pszSQL);
1338 :
1339 : /* --------------------------------------------------------------------
1340 : */
1341 : /* Execute. */
1342 : /* --------------------------------------------------------------------
1343 : */
1344 2 : char **papszResult = nullptr;
1345 : char *pszErrMsg;
1346 2 : int nRowCount = 0;
1347 2 : int nColCount = 0;
1348 :
1349 2 : if (sqlite3_get_table(m_poDS->GetDB(), pszSQL, &papszResult, &nRowCount,
1350 2 : &nColCount, &pszErrMsg) != SQLITE_OK)
1351 2 : return OGRSQLiteLayer::GetExtent(psExtent, bForce);
1352 :
1353 2 : OGRErr eErr = OGRERR_FAILURE;
1354 :
1355 2 : if (nRowCount == 1 && nColCount == 4 && papszResult[4 + 0] != nullptr &&
1356 2 : papszResult[4 + 1] != nullptr && papszResult[4 + 2] != nullptr &&
1357 2 : papszResult[4 + 3] != nullptr)
1358 : {
1359 2 : psExtent->MinX = CPLAtof(papszResult[4 + 0]);
1360 2 : psExtent->MinY = CPLAtof(papszResult[4 + 1]);
1361 2 : psExtent->MaxX = CPLAtof(papszResult[4 + 2]);
1362 2 : psExtent->MaxY = CPLAtof(papszResult[4 + 3]);
1363 2 : eErr = OGRERR_NONE;
1364 :
1365 2 : if (m_poFilterGeom == nullptr && m_osQuery.empty())
1366 : {
1367 2 : poGeomFieldDefn->m_bCachedExtentIsValid = true;
1368 2 : if (m_poDS->GetUpdate())
1369 1 : ForceStatisticsToBeFlushed();
1370 2 : poGeomFieldDefn->m_oCachedExtent = *psExtent;
1371 : }
1372 : }
1373 :
1374 2 : sqlite3_free_table(papszResult);
1375 :
1376 2 : if (eErr == OGRERR_NONE)
1377 2 : return eErr;
1378 : }
1379 :
1380 : OGRErr eErr;
1381 33 : if (iGeomField == 0)
1382 33 : eErr = OGRSQLiteLayer::GetExtent(psExtent, bForce);
1383 : else
1384 0 : eErr = OGRSQLiteLayer::GetExtent(iGeomField, psExtent, bForce);
1385 33 : if (eErr == OGRERR_NONE && m_poFilterGeom == nullptr && m_osQuery.empty())
1386 : {
1387 33 : poGeomFieldDefn->m_bCachedExtentIsValid = true;
1388 33 : ForceStatisticsToBeFlushed();
1389 33 : poGeomFieldDefn->m_oCachedExtent = *psExtent;
1390 : }
1391 33 : return eErr;
1392 : }
1393 :
1394 : /************************************************************************/
1395 : /* OGRSQLiteFieldDefnToSQliteFieldDefn() */
1396 : /************************************************************************/
1397 :
1398 4797 : CPLString OGRSQLiteFieldDefnToSQliteFieldDefn(OGRFieldDefn *poFieldDefn,
1399 : bool bSQLiteDialectInternalUse,
1400 : bool bStrict)
1401 : {
1402 4797 : if (bStrict)
1403 : {
1404 8 : switch (poFieldDefn->GetType())
1405 : {
1406 1 : case OFTInteger:
1407 0 : return "INTEGER CHECK (\"" +
1408 2 : CPLString(poFieldDefn->GetNameRef())
1409 2 : .replaceAll('"', "\"\"") +
1410 1 : "\" BETWEEN -2147483648 AND 2147483647)";
1411 1 : case OFTInteger64:
1412 0 : return "INTEGER CHECK (\"" +
1413 2 : CPLString(poFieldDefn->GetNameRef())
1414 2 : .replaceAll('"', "\"\"") +
1415 : "\" BETWEEN -9223372036854775808 AND "
1416 1 : "9223372036854775807)";
1417 1 : case OFTReal:
1418 1 : return "REAL";
1419 1 : case OFTBinary:
1420 1 : return "BLOB";
1421 1 : case OFTDateTime:
1422 0 : return "TEXT CHECK (\"" +
1423 2 : CPLString(poFieldDefn->GetNameRef())
1424 2 : .replaceAll('"', "\"\"") +
1425 : "\" LIKE "
1426 : "'[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2][0-9]:"
1427 1 : "[0-5][0-9]:[0-6][0-9]*')";
1428 1 : case OFTDate:
1429 0 : return "TEXT CHECK (\"" +
1430 2 : CPLString(poFieldDefn->GetNameRef())
1431 2 : .replaceAll('"', "\"\"") +
1432 1 : "\" LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T')";
1433 1 : case OFTTime:
1434 0 : return "TEXT CHECK (\"" +
1435 2 : CPLString(poFieldDefn->GetNameRef())
1436 2 : .replaceAll('"', "\"\"") +
1437 1 : "\" LIKE '[0-2][0-9]:[0-5][0-9]:[0-6][0-9]*')";
1438 1 : default:
1439 1 : return "TEXT";
1440 : }
1441 : }
1442 :
1443 4789 : switch (poFieldDefn->GetType())
1444 : {
1445 1049 : case OFTInteger:
1446 1049 : if (poFieldDefn->GetSubType() == OFSTBoolean)
1447 245 : return "INTEGER_BOOLEAN";
1448 804 : else if (poFieldDefn->GetSubType() == OFSTInt16)
1449 213 : return "INTEGER_INT16";
1450 : else
1451 591 : return "INTEGER";
1452 : break;
1453 262 : case OFTInteger64:
1454 262 : return "BIGINT";
1455 519 : case OFTReal:
1456 952 : if (bSQLiteDialectInternalUse &&
1457 433 : poFieldDefn->GetSubType() == OFSTFloat32)
1458 210 : return "FLOAT_FLOAT32";
1459 : else
1460 309 : return "FLOAT";
1461 : break;
1462 235 : case OFTBinary:
1463 235 : return "BLOB";
1464 : break;
1465 1086 : case OFTString:
1466 : {
1467 1086 : if (poFieldDefn->GetWidth() > 0)
1468 61 : return CPLSPrintf("VARCHAR(%d)", poFieldDefn->GetWidth());
1469 : else
1470 1025 : return "VARCHAR";
1471 : break;
1472 : }
1473 277 : case OFTDateTime:
1474 277 : return "TIMESTAMP";
1475 : break;
1476 249 : case OFTDate:
1477 249 : return "DATE";
1478 : break;
1479 217 : case OFTTime:
1480 217 : return "TIME";
1481 : break;
1482 229 : case OFTIntegerList:
1483 229 : return "JSONINTEGERLIST";
1484 : break;
1485 217 : case OFTInteger64List:
1486 217 : return "JSONINTEGER64LIST";
1487 : break;
1488 220 : case OFTRealList:
1489 220 : return "JSONREALLIST";
1490 : break;
1491 229 : case OFTStringList:
1492 229 : return "JSONSTRINGLIST";
1493 : break;
1494 0 : default:
1495 0 : return "VARCHAR";
1496 : break;
1497 : }
1498 : }
1499 :
1500 : /************************************************************************/
1501 : /* FieldDefnToSQliteFieldDefn() */
1502 : /************************************************************************/
1503 :
1504 : CPLString
1505 1071 : OGRSQLiteTableLayer::FieldDefnToSQliteFieldDefn(OGRFieldDefn *poFieldDefn)
1506 : {
1507 : CPLString osRet =
1508 1071 : OGRSQLiteFieldDefnToSQliteFieldDefn(poFieldDefn, false, m_bStrict);
1509 1663 : if (!m_bStrict && poFieldDefn->GetType() == OFTString &&
1510 592 : CSLFindString(m_papszCompressedColumns, poFieldDefn->GetNameRef()) >= 0)
1511 8 : osRet += "_deflate";
1512 :
1513 1071 : return osRet;
1514 : }
1515 :
1516 : /************************************************************************/
1517 : /* CreateField() */
1518 : /************************************************************************/
1519 :
1520 980 : OGRErr OGRSQLiteTableLayer::CreateField(const OGRFieldDefn *poFieldIn,
1521 : CPL_UNUSED int bApproxOK)
1522 : {
1523 1960 : OGRFieldDefn oField(poFieldIn);
1524 :
1525 980 : if (HasLayerDefnError())
1526 0 : return OGRERR_FAILURE;
1527 :
1528 980 : if (!m_poDS->GetUpdate())
1529 : {
1530 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
1531 : "CreateField");
1532 0 : return OGRERR_FAILURE;
1533 : }
1534 :
1535 2940 : if (m_pszFIDColumn != nullptr &&
1536 982 : EQUAL(oField.GetNameRef(), m_pszFIDColumn) &&
1537 1962 : oField.GetType() != OFTInteger && oField.GetType() != OFTInteger64)
1538 : {
1539 1 : CPLError(CE_Failure, CPLE_AppDefined, "Wrong field type for %s",
1540 : oField.GetNameRef());
1541 1 : return OGRERR_FAILURE;
1542 : }
1543 :
1544 979 : ClearInsertStmt();
1545 :
1546 979 : if (m_poDS->IsSpatialiteDB() && EQUAL(oField.GetNameRef(), "ROWID") &&
1547 0 : !(m_pszFIDColumn != nullptr &&
1548 0 : EQUAL(oField.GetNameRef(), m_pszFIDColumn)))
1549 : {
1550 0 : CPLError(CE_Warning, CPLE_AppDefined,
1551 : "In a Spatialite DB, a 'ROWID' column that is not the integer "
1552 : "primary key can corrupt spatial index. "
1553 : "See "
1554 : "https://www.gaia-gis.it/fossil/libspatialite/"
1555 : "wiki?name=Shadowed+ROWID+issues");
1556 : }
1557 :
1558 : /* -------------------------------------------------------------------- */
1559 : /* Do we want to "launder" the column names into SQLite */
1560 : /* friendly format? */
1561 : /* -------------------------------------------------------------------- */
1562 979 : if (m_bLaunderColumnNames)
1563 : {
1564 977 : char *pszSafeName = m_poDS->LaunderName(oField.GetNameRef());
1565 :
1566 977 : oField.SetName(pszSafeName);
1567 977 : CPLFree(pszSafeName);
1568 : }
1569 :
1570 2887 : if ((oField.GetType() == OFTTime || oField.GetType() == OFTDate ||
1571 2893 : oField.GetType() == OFTDateTime) &&
1572 103 : !(CPLTestBool(CPLGetConfigOption("OGR_SQLITE_ENABLE_DATETIME", "YES"))))
1573 : {
1574 0 : oField.SetType(OFTString);
1575 : }
1576 :
1577 979 : if (!m_bDeferredCreation)
1578 : {
1579 21 : CPLString osCommand;
1580 :
1581 21 : CPLString osFieldType(FieldDefnToSQliteFieldDefn(&oField));
1582 : osCommand.Printf(
1583 : "ALTER TABLE '%s' ADD COLUMN '%s' %s", m_pszEscapedTableName,
1584 21 : SQLEscapeLiteral(oField.GetNameRef()).c_str(), osFieldType.c_str());
1585 21 : if (!oField.IsNullable())
1586 : {
1587 0 : osCommand += " NOT NULL";
1588 : }
1589 21 : if (oField.IsUnique())
1590 : {
1591 0 : osCommand += " UNIQUE";
1592 : }
1593 21 : if (oField.GetDefault() != nullptr && !oField.IsDefaultDriverSpecific())
1594 : {
1595 0 : osCommand += " DEFAULT ";
1596 0 : osCommand += oField.GetDefault();
1597 : }
1598 21 : else if (!oField.IsNullable())
1599 : {
1600 : // This is kind of dumb, but SQLite mandates a DEFAULT value
1601 : // when adding a NOT NULL column in an ALTER TABLE ADD COLUMN
1602 : // statement, which defeats the purpose of NOT NULL,
1603 : // whereas it doesn't in CREATE TABLE
1604 0 : osCommand += " DEFAULT ''";
1605 : }
1606 :
1607 : #ifdef DEBUG
1608 21 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
1609 : #endif
1610 :
1611 21 : if (SQLCommand(m_poDS->GetDB(), osCommand) != OGRERR_NONE)
1612 0 : return OGRERR_FAILURE;
1613 : }
1614 :
1615 : /* -------------------------------------------------------------------- */
1616 : /* Add the field to the OGRFeatureDefn. */
1617 : /* -------------------------------------------------------------------- */
1618 979 : m_poFeatureDefn->AddFieldDefn(&oField);
1619 :
1620 979 : if (m_pszFIDColumn != nullptr && EQUAL(oField.GetNameRef(), m_pszFIDColumn))
1621 : {
1622 1 : m_iFIDAsRegularColumnIndex = m_poFeatureDefn->GetFieldCount() - 1;
1623 : }
1624 :
1625 979 : if (!m_bDeferredCreation)
1626 21 : RecomputeOrdinals();
1627 :
1628 979 : return OGRERR_NONE;
1629 : }
1630 :
1631 : /************************************************************************/
1632 : /* CreateGeomField() */
1633 : /************************************************************************/
1634 :
1635 : OGRErr
1636 11 : OGRSQLiteTableLayer::CreateGeomField(const OGRGeomFieldDefn *poGeomFieldIn,
1637 : CPL_UNUSED int bApproxOK)
1638 : {
1639 11 : OGRwkbGeometryType eType = poGeomFieldIn->GetType();
1640 11 : if (eType == wkbNone)
1641 : {
1642 0 : CPLError(CE_Failure, CPLE_AppDefined,
1643 : "Cannot create geometry field of type wkbNone");
1644 0 : return OGRERR_FAILURE;
1645 : }
1646 11 : if (m_poDS->IsSpatialiteDB())
1647 : {
1648 : // We need to catch this right now as AddGeometryColumn does not
1649 : // return an error
1650 5 : OGRwkbGeometryType eFType = wkbFlatten(eType);
1651 5 : if (eFType > wkbGeometryCollection)
1652 : {
1653 1 : CPLError(CE_Failure, CPLE_NotSupported,
1654 : "Cannot create geometry field of type %s",
1655 : OGRToOGCGeomType(eType));
1656 1 : return OGRERR_FAILURE;
1657 : }
1658 : }
1659 :
1660 : auto poGeomField = std::make_unique<OGRSQLiteGeomFieldDefn>(
1661 20 : poGeomFieldIn->GetNameRef(), -1);
1662 10 : if (EQUAL(poGeomField->GetNameRef(), ""))
1663 : {
1664 0 : if (m_poFeatureDefn->GetGeomFieldCount() == 0)
1665 0 : poGeomField->SetName("GEOMETRY");
1666 : else
1667 0 : poGeomField->SetName(CPLSPrintf(
1668 0 : "GEOMETRY%d", m_poFeatureDefn->GetGeomFieldCount() + 1));
1669 : }
1670 10 : auto poSRSIn = poGeomFieldIn->GetSpatialRef();
1671 10 : if (poSRSIn)
1672 : {
1673 5 : auto l_poSRS = poSRSIn->Clone();
1674 5 : l_poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
1675 5 : poGeomField->SetSpatialRef(l_poSRS);
1676 5 : l_poSRS->Release();
1677 : }
1678 :
1679 : /* -------------------------------------------------------------------- */
1680 : /* Do we want to "launder" the column names into Postgres */
1681 : /* friendly format? */
1682 : /* -------------------------------------------------------------------- */
1683 10 : if (m_bLaunderColumnNames)
1684 : {
1685 10 : char *pszSafeName = m_poDS->LaunderName(poGeomField->GetNameRef());
1686 :
1687 10 : poGeomField->SetName(pszSafeName);
1688 10 : CPLFree(pszSafeName);
1689 : }
1690 :
1691 10 : const OGRSpatialReference *poSRS = poGeomField->GetSpatialRef();
1692 10 : int nSRSId = -1;
1693 10 : if (poSRS != nullptr)
1694 5 : nSRSId = m_poDS->FetchSRSId(poSRS);
1695 :
1696 10 : poGeomField->SetType(eType);
1697 10 : poGeomField->SetNullable(poGeomFieldIn->IsNullable());
1698 10 : poGeomField->m_nSRSId = nSRSId;
1699 10 : if (m_poDS->IsSpatialiteDB())
1700 4 : poGeomField->m_eGeomFormat = OSGF_SpatiaLite;
1701 6 : else if (m_pszCreationGeomFormat)
1702 6 : poGeomField->m_eGeomFormat = GetGeomFormat(m_pszCreationGeomFormat);
1703 : else
1704 0 : poGeomField->m_eGeomFormat = OSGF_WKB;
1705 :
1706 : /* -------------------------------------------------------------------- */
1707 : /* Create the new field. */
1708 : /* -------------------------------------------------------------------- */
1709 10 : if (!m_bDeferredCreation)
1710 : {
1711 0 : if (RunAddGeometryColumn(poGeomField.get(), true) != OGRERR_NONE)
1712 : {
1713 0 : return OGRERR_FAILURE;
1714 : }
1715 : }
1716 :
1717 10 : m_poFeatureDefn->AddGeomFieldDefn(std::move(poGeomField));
1718 :
1719 10 : if (!m_bDeferredCreation)
1720 0 : RecomputeOrdinals();
1721 :
1722 10 : return OGRERR_NONE;
1723 : }
1724 :
1725 : /************************************************************************/
1726 : /* RunAddGeometryColumn() */
1727 : /************************************************************************/
1728 :
1729 269 : OGRErr OGRSQLiteTableLayer::RunAddGeometryColumn(
1730 : const OGRSQLiteGeomFieldDefn *poGeomFieldDefn,
1731 : bool bAddColumnsForNonSpatialite)
1732 : {
1733 269 : OGRwkbGeometryType eType = poGeomFieldDefn->GetType();
1734 269 : const char *pszGeomCol = poGeomFieldDefn->GetNameRef();
1735 269 : int nSRSId = poGeomFieldDefn->m_nSRSId;
1736 :
1737 269 : const int nCoordDim = eType == wkbFlatten(eType) ? 2 : 3;
1738 :
1739 269 : if (bAddColumnsForNonSpatialite && !m_poDS->IsSpatialiteDB())
1740 : {
1741 : CPLString osCommand =
1742 0 : CPLSPrintf("ALTER TABLE '%s' ADD COLUMN ", m_pszEscapedTableName);
1743 0 : if (poGeomFieldDefn->m_eGeomFormat == OSGF_WKT)
1744 : {
1745 : osCommand += CPLSPrintf(
1746 : " '%s' VARCHAR",
1747 0 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
1748 : }
1749 : else
1750 : {
1751 : osCommand += CPLSPrintf(
1752 : " '%s' BLOB",
1753 0 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
1754 : }
1755 0 : if (!poGeomFieldDefn->IsNullable())
1756 0 : osCommand += " NOT NULL DEFAULT ''";
1757 :
1758 : #ifdef DEBUG
1759 0 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
1760 : #endif
1761 :
1762 0 : if (SQLCommand(m_poDS->GetDB(), osCommand) != OGRERR_NONE)
1763 0 : return OGRERR_FAILURE;
1764 : }
1765 :
1766 538 : CPLString osCommand;
1767 :
1768 269 : if (m_poDS->IsSpatialiteDB())
1769 : {
1770 : /*
1771 : / SpatiaLite full support: calling AddGeometryColumn()
1772 : /
1773 : / IMPORTANT NOTICE: on SpatiaLite any attempt aimed
1774 : / to directly INSERT a row into GEOMETRY_COLUMNS
1775 : / [by-passing AddGeometryColumn() as absolutely required]
1776 : / will severely [and irremediably] corrupt the DB !!!
1777 : */
1778 146 : const char *pszType = OGRToOGCGeomType(eType);
1779 146 : if (pszType[0] == '\0')
1780 0 : pszType = "GEOMETRY";
1781 :
1782 : /*
1783 : / SpatiaLite v.2.4.0 (or any subsequent) is required
1784 : / to support 2.5D: if an obsolete version of the library
1785 : / is found we'll unconditionally activate 2D casting mode
1786 : */
1787 146 : int iSpatialiteVersion = m_poDS->GetSpatialiteVersionNumber();
1788 146 : const char *pszCoordDim = "2";
1789 146 : if (iSpatialiteVersion <
1790 146 : OGRSQLiteDataSource::MakeSpatialiteVersionNumber(2, 4, 0) &&
1791 : nCoordDim == 3)
1792 : {
1793 0 : CPLDebug("SQLITE", "Spatialite < 2.4.0 --> 2.5D geometry not "
1794 : "supported. Casting to 2D");
1795 : }
1796 146 : else if (OGR_GT_HasM(eType))
1797 : {
1798 32 : pszCoordDim = (OGR_GT_HasZ(eType)) ? "'XYZM'" : "'XYM'";
1799 : }
1800 114 : else if (OGR_GT_HasZ(eType))
1801 : {
1802 36 : pszCoordDim = "3";
1803 : }
1804 : osCommand.Printf("SELECT AddGeometryColumn("
1805 : "'%s', '%s', %d, '%s', %s",
1806 : m_pszEscapedTableName,
1807 292 : SQLEscapeLiteral(pszGeomCol).c_str(), nSRSId, pszType,
1808 146 : pszCoordDim);
1809 146 : if (iSpatialiteVersion >=
1810 292 : OGRSQLiteDataSource::MakeSpatialiteVersionNumber(3, 0, 0) &&
1811 146 : !poGeomFieldDefn->IsNullable())
1812 1 : osCommand += ", 1";
1813 146 : osCommand += ")";
1814 : }
1815 : else
1816 : {
1817 123 : const char *pszGeomFormat =
1818 245 : (poGeomFieldDefn->m_eGeomFormat == OSGF_WKT) ? "WKT"
1819 123 : : (poGeomFieldDefn->m_eGeomFormat == OSGF_WKB) ? "WKB"
1820 1 : : (poGeomFieldDefn->m_eGeomFormat == OSGF_FGF) ? "FGF"
1821 : : "Spatialite";
1822 123 : if (nSRSId > 0)
1823 : {
1824 : osCommand.Printf(
1825 : "INSERT INTO geometry_columns "
1826 : "(f_table_name, f_geometry_column, geometry_format, "
1827 : "geometry_type, coord_dimension, srid) VALUES "
1828 : "('%s','%s','%s', %d, %d, %d)",
1829 14 : m_pszEscapedTableName, SQLEscapeLiteral(pszGeomCol).c_str(),
1830 14 : pszGeomFormat, (int)wkbFlatten(eType), nCoordDim, nSRSId);
1831 : }
1832 : else
1833 : {
1834 : osCommand.Printf(
1835 : "INSERT INTO geometry_columns "
1836 : "(f_table_name, f_geometry_column, geometry_format, "
1837 : "geometry_type, coord_dimension) VALUES "
1838 : "('%s','%s','%s', %d, %d)",
1839 109 : m_pszEscapedTableName, SQLEscapeLiteral(pszGeomCol).c_str(),
1840 109 : pszGeomFormat, (int)wkbFlatten(eType), nCoordDim);
1841 : }
1842 : }
1843 :
1844 : #ifdef DEBUG
1845 269 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
1846 : #endif
1847 :
1848 269 : return SQLCommand(m_poDS->GetDB(), osCommand);
1849 : }
1850 :
1851 : /************************************************************************/
1852 : /* InitFieldListForRecrerate() */
1853 : /************************************************************************/
1854 :
1855 21 : void OGRSQLiteTableLayer::InitFieldListForRecrerate(
1856 : char *&pszNewFieldList, char *&pszFieldListForSelect, size_t &nBufLenOut,
1857 : int nExtraSpace)
1858 : {
1859 21 : size_t nFieldListLen = 100 + 2 * nExtraSpace;
1860 :
1861 114 : for (int iField = 0; iField < m_poFeatureDefn->GetFieldCount(); iField++)
1862 : {
1863 93 : OGRFieldDefn *poFieldDefn = m_poFeatureDefn->GetFieldDefn(iField);
1864 93 : nFieldListLen += 2 * strlen(poFieldDefn->GetNameRef()) + 70;
1865 93 : nFieldListLen += strlen(" UNIQUE");
1866 93 : if (poFieldDefn->GetDefault() != nullptr)
1867 18 : nFieldListLen += 10 + strlen(poFieldDefn->GetDefault());
1868 : }
1869 :
1870 21 : nFieldListLen +=
1871 21 : 50 + (m_pszFIDColumn ? 2 * strlen(m_pszFIDColumn) : strlen("OGC_FID"));
1872 40 : for (int iField = 0; iField < m_poFeatureDefn->GetGeomFieldCount();
1873 : iField++)
1874 : {
1875 19 : nFieldListLen +=
1876 19 : 70 +
1877 19 : 2 * strlen(m_poFeatureDefn->GetGeomFieldDefn(iField)->GetNameRef());
1878 : }
1879 :
1880 21 : nBufLenOut = nFieldListLen;
1881 21 : pszFieldListForSelect = (char *)CPLCalloc(1, nFieldListLen);
1882 21 : pszNewFieldList = (char *)CPLCalloc(1, nFieldListLen);
1883 :
1884 : /* -------------------------------------------------------------------- */
1885 : /* Build list of old fields, and the list of new fields. */
1886 : /* -------------------------------------------------------------------- */
1887 0 : snprintf(pszFieldListForSelect, nFieldListLen, "\"%s\"",
1888 21 : m_pszFIDColumn ? SQLEscapeName(m_pszFIDColumn).c_str()
1889 : : "OGC_FID");
1890 0 : snprintf(pszNewFieldList, nFieldListLen, "\"%s\" INTEGER PRIMARY KEY",
1891 21 : m_pszFIDColumn ? SQLEscapeName(m_pszFIDColumn).c_str()
1892 : : "OGC_FID");
1893 :
1894 40 : for (int iField = 0; iField < m_poFeatureDefn->GetGeomFieldCount();
1895 : iField++)
1896 : {
1897 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
1898 19 : m_poFeatureDefn->myGetGeomFieldDefn(iField);
1899 19 : strcat(pszFieldListForSelect, ",");
1900 19 : strcat(pszNewFieldList, ",");
1901 :
1902 19 : strcat(pszFieldListForSelect, "\"");
1903 19 : strcat(pszFieldListForSelect,
1904 38 : SQLEscapeName(poGeomFieldDefn->GetNameRef()));
1905 19 : strcat(pszFieldListForSelect, "\"");
1906 :
1907 19 : strcat(pszNewFieldList, "\"");
1908 19 : strcat(pszNewFieldList, SQLEscapeName(poGeomFieldDefn->GetNameRef()));
1909 19 : strcat(pszNewFieldList, "\"");
1910 :
1911 19 : if (poGeomFieldDefn->m_eGeomFormat == OSGF_WKT)
1912 0 : strcat(pszNewFieldList, " VARCHAR");
1913 : else
1914 19 : strcat(pszNewFieldList, " BLOB");
1915 19 : if (!poGeomFieldDefn->IsNullable())
1916 2 : strcat(pszNewFieldList, " NOT NULL");
1917 : }
1918 21 : }
1919 :
1920 : /************************************************************************/
1921 : /* AddColumnDef() */
1922 : /************************************************************************/
1923 :
1924 84 : void OGRSQLiteTableLayer::AddColumnDef(char *pszNewFieldList, size_t nBufLen,
1925 : OGRFieldDefn *poFldDefn)
1926 : {
1927 168 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
1928 84 : nBufLen - strlen(pszNewFieldList), ", '%s' %s",
1929 168 : SQLEscapeLiteral(poFldDefn->GetNameRef()).c_str(),
1930 168 : FieldDefnToSQliteFieldDefn(poFldDefn).c_str());
1931 84 : if (!poFldDefn->IsNullable())
1932 0 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
1933 0 : nBufLen - strlen(pszNewFieldList), " NOT NULL");
1934 84 : if (poFldDefn->IsUnique())
1935 0 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
1936 0 : nBufLen - strlen(pszNewFieldList), " UNIQUE");
1937 100 : if (poFldDefn->GetDefault() != nullptr &&
1938 16 : !poFldDefn->IsDefaultDriverSpecific())
1939 : {
1940 14 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
1941 14 : nBufLen - strlen(pszNewFieldList), " DEFAULT %s",
1942 : poFldDefn->GetDefault());
1943 : }
1944 84 : }
1945 :
1946 : /************************************************************************/
1947 : /* RecreateTable() */
1948 : /************************************************************************/
1949 :
1950 21 : OGRErr OGRSQLiteTableLayer::RecreateTable(const char *pszFieldListForSelect,
1951 : const char *pszNewFieldList,
1952 : const char *pszGenericErrorMessage,
1953 : const char *pszAdditionalDef)
1954 : {
1955 : /* -------------------------------------------------------------------- */
1956 : /* Do this all in a transaction. */
1957 : /* -------------------------------------------------------------------- */
1958 21 : m_poDS->SoftStartTransaction();
1959 :
1960 : /* -------------------------------------------------------------------- */
1961 : /* Save existing related triggers and index */
1962 : /* -------------------------------------------------------------------- */
1963 21 : char *pszErrMsg = nullptr;
1964 21 : sqlite3 *hDB = m_poDS->GetDB();
1965 42 : CPLString osSQL;
1966 :
1967 : osSQL.Printf("SELECT sql FROM sqlite_master WHERE type IN "
1968 : "('trigger','index') AND tbl_name='%s'",
1969 21 : m_pszEscapedTableName);
1970 :
1971 : int nRowTriggerIndexCount, nColTriggerIndexCount;
1972 21 : char **papszTriggerIndexResult = nullptr;
1973 21 : int rc = sqlite3_get_table(hDB, osSQL.c_str(), &papszTriggerIndexResult,
1974 : &nRowTriggerIndexCount, &nColTriggerIndexCount,
1975 : &pszErrMsg);
1976 :
1977 : /* -------------------------------------------------------------------- */
1978 : /* Make a backup of the table. */
1979 : /* -------------------------------------------------------------------- */
1980 :
1981 21 : if (rc == SQLITE_OK)
1982 23 : rc = sqlite3_exec(
1983 : hDB,
1984 : CPLSPrintf("CREATE TABLE t1_back(%s %s)%s", pszNewFieldList,
1985 : pszAdditionalDef
1986 23 : ? (std::string(", ") + pszAdditionalDef).c_str()
1987 : : "",
1988 21 : m_bStrict ? " STRICT" : ""),
1989 : nullptr, nullptr, &pszErrMsg);
1990 :
1991 21 : if (rc == SQLITE_OK)
1992 21 : rc = sqlite3_exec(hDB,
1993 : CPLSPrintf("INSERT INTO t1_back SELECT %s FROM '%s'",
1994 : pszFieldListForSelect,
1995 : m_pszEscapedTableName),
1996 : nullptr, nullptr, &pszErrMsg);
1997 :
1998 : /* -------------------------------------------------------------------- */
1999 : /* Drop the original table */
2000 : /* -------------------------------------------------------------------- */
2001 21 : if (rc == SQLITE_OK)
2002 21 : rc = sqlite3_exec(hDB,
2003 : CPLSPrintf("DROP TABLE '%s'", m_pszEscapedTableName),
2004 : nullptr, nullptr, &pszErrMsg);
2005 :
2006 : /* -------------------------------------------------------------------- */
2007 : /* Rename backup table as new table */
2008 : /* -------------------------------------------------------------------- */
2009 21 : if (rc == SQLITE_OK)
2010 : {
2011 21 : const char *pszCmd = CPLSPrintf("ALTER TABLE t1_back RENAME TO '%s'",
2012 : m_pszEscapedTableName);
2013 21 : rc = sqlite3_exec(hDB, pszCmd, nullptr, nullptr, &pszErrMsg);
2014 : }
2015 :
2016 : /* -------------------------------------------------------------------- */
2017 : /* Recreate existing related tables, triggers and index */
2018 : /* -------------------------------------------------------------------- */
2019 :
2020 21 : if (rc == SQLITE_OK)
2021 : {
2022 21 : for (int i = 1; i <= nRowTriggerIndexCount &&
2023 21 : nColTriggerIndexCount == 1 && rc == SQLITE_OK;
2024 : i++)
2025 : {
2026 0 : if (papszTriggerIndexResult[i] != nullptr &&
2027 0 : papszTriggerIndexResult[i][0] != '\0')
2028 0 : rc = sqlite3_exec(hDB, papszTriggerIndexResult[i], nullptr,
2029 : nullptr, &pszErrMsg);
2030 : }
2031 : }
2032 :
2033 : /* -------------------------------------------------------------------- */
2034 : /* COMMIT on success or ROLLBACK on failure. */
2035 : /* -------------------------------------------------------------------- */
2036 :
2037 21 : sqlite3_free_table(papszTriggerIndexResult);
2038 :
2039 21 : if (rc == SQLITE_OK)
2040 : {
2041 21 : m_poDS->SoftCommitTransaction();
2042 :
2043 21 : return OGRERR_NONE;
2044 : }
2045 : else
2046 : {
2047 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s:\n %s",
2048 : pszGenericErrorMessage, pszErrMsg);
2049 0 : sqlite3_free(pszErrMsg);
2050 :
2051 0 : m_poDS->SoftRollbackTransaction();
2052 :
2053 0 : return OGRERR_FAILURE;
2054 : }
2055 : }
2056 :
2057 : /************************************************************************/
2058 : /* DeleteField() */
2059 : /************************************************************************/
2060 :
2061 7 : OGRErr OGRSQLiteTableLayer::DeleteField(int iFieldToDelete)
2062 : {
2063 7 : if (HasLayerDefnError())
2064 0 : return OGRERR_FAILURE;
2065 :
2066 7 : if (!m_poDS->GetUpdate())
2067 : {
2068 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
2069 : "DeleteField");
2070 0 : return OGRERR_FAILURE;
2071 : }
2072 :
2073 13 : if (iFieldToDelete < 0 ||
2074 6 : iFieldToDelete >= m_poFeatureDefn->GetFieldCount())
2075 : {
2076 2 : CPLError(CE_Failure, CPLE_NotSupported, "Invalid field index");
2077 2 : return OGRERR_FAILURE;
2078 : }
2079 :
2080 5 : ResetReading();
2081 :
2082 5 : if (m_poDS->SoftStartTransaction() != OGRERR_NONE)
2083 0 : return OGRERR_FAILURE;
2084 :
2085 : // ALTER TABLE ... DROP COLUMN ... was first implemented in 3.35.0 but
2086 : // there was bug fixes related to it until 3.35.5
2087 : #if SQLITE_VERSION_NUMBER >= 3035005L
2088 : const char *pszFieldName =
2089 5 : m_poFeatureDefn->GetFieldDefn(iFieldToDelete)->GetNameRef();
2090 5 : OGRErr eErr = SQLCommand(
2091 10 : m_poDS->GetDB(), CPLString()
2092 : .Printf("ALTER TABLE \"%s\" DROP COLUMN \"%s\"",
2093 10 : SQLEscapeName(m_pszTableName).c_str(),
2094 15 : SQLEscapeName(pszFieldName).c_str())
2095 : .c_str());
2096 : #else
2097 : /* -------------------------------------------------------------------- */
2098 : /* Build list of old fields, and the list of new fields. */
2099 : /* -------------------------------------------------------------------- */
2100 : char *pszNewFieldList = nullptr;
2101 : char *pszFieldListForSelect = nullptr;
2102 : size_t nBufLen = 0;
2103 :
2104 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect, nBufLen);
2105 :
2106 : for (int iField = 0; iField < m_poFeatureDefn->GetFieldCount(); iField++)
2107 : {
2108 : OGRFieldDefn *poFldDefn = m_poFeatureDefn->GetFieldDefn(iField);
2109 :
2110 : if (iField == iFieldToDelete)
2111 : continue;
2112 :
2113 : snprintf(pszFieldListForSelect + strlen(pszFieldListForSelect),
2114 : nBufLen - strlen(pszFieldListForSelect), ", \"%s\"",
2115 : SQLEscapeName(poFldDefn->GetNameRef()).c_str());
2116 :
2117 : AddColumnDef(pszNewFieldList, nBufLen, poFldDefn);
2118 : }
2119 :
2120 : /* -------------------------------------------------------------------- */
2121 : /* Recreate table. */
2122 : /* -------------------------------------------------------------------- */
2123 : CPLString osErrorMsg;
2124 : osErrorMsg.Printf(
2125 : "Failed to remove field %s from table %s",
2126 : m_poFeatureDefn->GetFieldDefn(iFieldToDelete)->GetNameRef(),
2127 : m_poFeatureDefn->GetName());
2128 :
2129 : OGRErr eErr = RecreateTable(pszFieldListForSelect, pszNewFieldList,
2130 : osErrorMsg.c_str());
2131 :
2132 : CPLFree(pszFieldListForSelect);
2133 : CPLFree(pszNewFieldList);
2134 : #endif
2135 :
2136 : /* -------------------------------------------------------------------- */
2137 : /* Check foreign key integrity if enforcement of foreign keys */
2138 : /* constraint is enabled. */
2139 : /* -------------------------------------------------------------------- */
2140 10 : if (eErr == OGRERR_NONE &&
2141 5 : SQLGetInteger(m_poDS->GetDB(), "PRAGMA foreign_keys", nullptr))
2142 : {
2143 0 : CPLDebug("SQLite", "Running PRAGMA foreign_key_check");
2144 0 : eErr = m_poDS->PragmaCheck("foreign_key_check", "", 0);
2145 : }
2146 :
2147 : /* -------------------------------------------------------------------- */
2148 : /* Finish */
2149 : /* -------------------------------------------------------------------- */
2150 :
2151 5 : if (eErr == OGRERR_NONE)
2152 : {
2153 5 : eErr = m_poDS->SoftCommitTransaction();
2154 5 : if (eErr == OGRERR_NONE)
2155 : {
2156 5 : eErr = m_poFeatureDefn->DeleteFieldDefn(iFieldToDelete);
2157 :
2158 5 : RecomputeOrdinals();
2159 :
2160 5 : ResetReading();
2161 : }
2162 : }
2163 : else
2164 : {
2165 0 : m_poDS->SoftRollbackTransaction();
2166 : }
2167 :
2168 5 : return eErr;
2169 : }
2170 :
2171 : /************************************************************************/
2172 : /* AlterFieldDefn() */
2173 : /************************************************************************/
2174 :
2175 12 : OGRErr OGRSQLiteTableLayer::AlterFieldDefn(int iFieldToAlter,
2176 : OGRFieldDefn *poNewFieldDefn,
2177 : int nFlagsIn)
2178 : {
2179 12 : if (HasLayerDefnError())
2180 0 : return OGRERR_FAILURE;
2181 :
2182 12 : if (!m_poDS->GetUpdate())
2183 : {
2184 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
2185 : "AlterFieldDefn");
2186 0 : return OGRERR_FAILURE;
2187 : }
2188 :
2189 12 : if (iFieldToAlter < 0 || iFieldToAlter >= m_poFeatureDefn->GetFieldCount())
2190 : {
2191 2 : CPLError(CE_Failure, CPLE_NotSupported, "Invalid field index");
2192 2 : return OGRERR_FAILURE;
2193 : }
2194 :
2195 10 : ClearInsertStmt();
2196 10 : ResetReading();
2197 :
2198 : /* -------------------------------------------------------------------- */
2199 : /* Check that the new column name is not a duplicate. */
2200 : /* -------------------------------------------------------------------- */
2201 :
2202 : OGRFieldDefn *poFieldDefnToAlter =
2203 10 : m_poFeatureDefn->GetFieldDefn(iFieldToAlter);
2204 20 : const CPLString osOldColName(poFieldDefnToAlter->GetNameRef());
2205 10 : const CPLString osNewColName((nFlagsIn & ALTER_NAME_FLAG)
2206 : ? CPLString(poNewFieldDefn->GetNameRef())
2207 20 : : osOldColName);
2208 :
2209 10 : const bool bRenameCol = osOldColName != osNewColName;
2210 10 : if (bRenameCol)
2211 : {
2212 21 : if ((m_pszFIDColumn &&
2213 7 : strcmp(poNewFieldDefn->GetNameRef(), m_pszFIDColumn) == 0) ||
2214 7 : (GetGeomType() != wkbNone &&
2215 7 : strcmp(poNewFieldDefn->GetNameRef(),
2216 21 : m_poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef()) == 0) ||
2217 7 : m_poFeatureDefn->GetFieldIndex(poNewFieldDefn->GetNameRef()) >= 0)
2218 : {
2219 0 : CPLError(CE_Failure, CPLE_AppDefined,
2220 : "Field name %s is already used for another field",
2221 : poNewFieldDefn->GetNameRef());
2222 0 : return OGRERR_FAILURE;
2223 : }
2224 : }
2225 :
2226 : /* -------------------------------------------------------------------- */
2227 : /* Build the modified field definition from the flags. */
2228 : /* -------------------------------------------------------------------- */
2229 20 : OGRFieldDefn oTmpFieldDefn(poFieldDefnToAlter);
2230 10 : int nActualFlags = 0;
2231 10 : if (bRenameCol)
2232 : {
2233 7 : nActualFlags |= ALTER_NAME_FLAG;
2234 7 : oTmpFieldDefn.SetName(poNewFieldDefn->GetNameRef());
2235 : }
2236 18 : if ((nFlagsIn & ALTER_TYPE_FLAG) != 0 &&
2237 8 : (poFieldDefnToAlter->GetType() != poNewFieldDefn->GetType() ||
2238 6 : poFieldDefnToAlter->GetSubType() != poNewFieldDefn->GetSubType()))
2239 : {
2240 2 : nActualFlags |= ALTER_TYPE_FLAG;
2241 2 : oTmpFieldDefn.SetSubType(OFSTNone);
2242 2 : oTmpFieldDefn.SetType(poNewFieldDefn->GetType());
2243 2 : oTmpFieldDefn.SetSubType(poNewFieldDefn->GetSubType());
2244 : }
2245 21 : if ((nFlagsIn & ALTER_WIDTH_PRECISION_FLAG) != 0 &&
2246 11 : (poFieldDefnToAlter->GetWidth() != poNewFieldDefn->GetWidth() ||
2247 3 : poFieldDefnToAlter->GetPrecision() != poNewFieldDefn->GetPrecision()))
2248 : {
2249 5 : nActualFlags |= ALTER_WIDTH_PRECISION_FLAG;
2250 5 : oTmpFieldDefn.SetWidth(poNewFieldDefn->GetWidth());
2251 5 : oTmpFieldDefn.SetPrecision(poNewFieldDefn->GetPrecision());
2252 : }
2253 18 : if ((nFlagsIn & ALTER_NULLABLE_FLAG) != 0 &&
2254 8 : poFieldDefnToAlter->IsNullable() != poNewFieldDefn->IsNullable())
2255 : {
2256 2 : nActualFlags |= ALTER_NULLABLE_FLAG;
2257 2 : oTmpFieldDefn.SetNullable(poNewFieldDefn->IsNullable());
2258 : }
2259 20 : if ((nFlagsIn & ALTER_DEFAULT_FLAG) != 0 &&
2260 10 : !((poFieldDefnToAlter->GetDefault() == nullptr &&
2261 8 : poNewFieldDefn->GetDefault() == nullptr) ||
2262 2 : (poFieldDefnToAlter->GetDefault() != nullptr &&
2263 2 : poNewFieldDefn->GetDefault() != nullptr &&
2264 1 : strcmp(poFieldDefnToAlter->GetDefault(),
2265 : poNewFieldDefn->GetDefault()) == 0)))
2266 : {
2267 2 : nActualFlags |= ALTER_DEFAULT_FLAG;
2268 2 : oTmpFieldDefn.SetDefault(poNewFieldDefn->GetDefault());
2269 : }
2270 18 : if ((nFlagsIn & ALTER_UNIQUE_FLAG) != 0 &&
2271 8 : poFieldDefnToAlter->IsUnique() != poNewFieldDefn->IsUnique())
2272 : {
2273 0 : nActualFlags |= ALTER_UNIQUE_FLAG;
2274 0 : oTmpFieldDefn.SetUnique(poNewFieldDefn->IsUnique());
2275 : }
2276 :
2277 10 : if (nActualFlags == ALTER_NAME_FLAG)
2278 : {
2279 1 : CPLDebug("SQLite", "Running ALTER TABLE RENAME COLUMN");
2280 1 : OGRErr eErr = SQLCommand(
2281 1 : m_poDS->GetDB(),
2282 1 : CPLString()
2283 : .Printf("ALTER TABLE \"%s\" RENAME COLUMN \"%s\" TO \"%s\"",
2284 2 : SQLEscapeName(m_pszTableName).c_str(),
2285 2 : SQLEscapeName(osOldColName).c_str(),
2286 4 : SQLEscapeName(osNewColName).c_str())
2287 : .c_str());
2288 :
2289 1 : if (eErr != OGRERR_NONE)
2290 0 : return eErr;
2291 : }
2292 : else
2293 : {
2294 : /* --------------------------------------------------------------------
2295 : */
2296 : /* Build list of old fields, and the list of new fields. */
2297 : /* --------------------------------------------------------------------
2298 : */
2299 9 : char *pszNewFieldList = nullptr;
2300 9 : char *pszFieldListForSelect = nullptr;
2301 9 : size_t nBufLen = 0;
2302 :
2303 9 : InitFieldListForRecrerate(
2304 : pszNewFieldList, pszFieldListForSelect, nBufLen,
2305 9 : static_cast<int>(strlen(poNewFieldDefn->GetNameRef())) + 50 +
2306 9 : (poNewFieldDefn->GetDefault()
2307 9 : ? static_cast<int>(strlen(poNewFieldDefn->GetDefault()))
2308 : : 0));
2309 :
2310 56 : for (int iField = 0; iField < m_poFeatureDefn->GetFieldCount();
2311 : iField++)
2312 : {
2313 47 : OGRFieldDefn *poFldDefn = m_poFeatureDefn->GetFieldDefn(iField);
2314 :
2315 47 : snprintf(pszFieldListForSelect + strlen(pszFieldListForSelect),
2316 47 : nBufLen - strlen(pszFieldListForSelect), ", \"%s\"",
2317 94 : SQLEscapeName(poFldDefn->GetNameRef()).c_str());
2318 :
2319 47 : if (iField == iFieldToAlter)
2320 : {
2321 18 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
2322 9 : nBufLen - strlen(pszNewFieldList), ", '%s' %s",
2323 18 : SQLEscapeLiteral(oTmpFieldDefn.GetNameRef()).c_str(),
2324 18 : FieldDefnToSQliteFieldDefn(&oTmpFieldDefn).c_str());
2325 7 : if ((nFlagsIn & ALTER_NAME_FLAG) &&
2326 16 : oTmpFieldDefn.GetType() == OFTString &&
2327 6 : CSLFindString(m_papszCompressedColumns,
2328 : poFldDefn->GetNameRef()) >= 0)
2329 : {
2330 0 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
2331 0 : nBufLen - strlen(pszNewFieldList), "_deflate");
2332 : }
2333 9 : if (!oTmpFieldDefn.IsNullable())
2334 1 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
2335 1 : nBufLen - strlen(pszNewFieldList), " NOT NULL");
2336 9 : if (oTmpFieldDefn.IsUnique())
2337 0 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
2338 0 : nBufLen - strlen(pszNewFieldList), " UNIQUE");
2339 9 : if (oTmpFieldDefn.GetDefault())
2340 : {
2341 1 : snprintf(pszNewFieldList + strlen(pszNewFieldList),
2342 1 : nBufLen - strlen(pszNewFieldList), " DEFAULT %s",
2343 : oTmpFieldDefn.GetDefault());
2344 : }
2345 : }
2346 : else
2347 : {
2348 38 : AddColumnDef(pszNewFieldList, nBufLen, poFldDefn);
2349 : }
2350 : }
2351 :
2352 : /* --------------------------------------------------------------------
2353 : */
2354 : /* Recreate table. */
2355 : /* --------------------------------------------------------------------
2356 : */
2357 9 : CPLString osErrorMsg;
2358 : osErrorMsg.Printf(
2359 : "Failed to alter field %s from table %s",
2360 9 : m_poFeatureDefn->GetFieldDefn(iFieldToAlter)->GetNameRef(),
2361 9 : m_poFeatureDefn->GetName());
2362 :
2363 9 : OGRErr eErr = RecreateTable(pszFieldListForSelect, pszNewFieldList,
2364 : osErrorMsg.c_str());
2365 :
2366 9 : CPLFree(pszFieldListForSelect);
2367 9 : CPLFree(pszNewFieldList);
2368 :
2369 9 : if (eErr != OGRERR_NONE)
2370 0 : return eErr;
2371 : }
2372 :
2373 : /* -------------------------------------------------------------------- */
2374 : /* Finish */
2375 : /* -------------------------------------------------------------------- */
2376 :
2377 10 : OGRFieldDefn *poFieldDefn = m_poFeatureDefn->GetFieldDefn(iFieldToAlter);
2378 :
2379 10 : if (nActualFlags & ALTER_TYPE_FLAG)
2380 : {
2381 2 : int iIdx = 0;
2382 2 : if (poNewFieldDefn->GetType() != OFTString &&
2383 0 : (iIdx = CSLFindString(m_papszCompressedColumns,
2384 : poFieldDefn->GetNameRef())) >= 0)
2385 : {
2386 0 : m_papszCompressedColumns =
2387 0 : CSLRemoveStrings(m_papszCompressedColumns, iIdx, 1, nullptr);
2388 : }
2389 2 : poFieldDefn->SetSubType(OFSTNone);
2390 2 : poFieldDefn->SetType(poNewFieldDefn->GetType());
2391 2 : poFieldDefn->SetSubType(poNewFieldDefn->GetSubType());
2392 : }
2393 10 : if (nActualFlags & ALTER_NAME_FLAG)
2394 : {
2395 : const int iIdx =
2396 7 : CSLFindString(m_papszCompressedColumns, poFieldDefn->GetNameRef());
2397 7 : if (iIdx >= 0)
2398 : {
2399 0 : CPLFree(m_papszCompressedColumns[iIdx]);
2400 0 : m_papszCompressedColumns[iIdx] =
2401 0 : CPLStrdup(poNewFieldDefn->GetNameRef());
2402 : }
2403 7 : poFieldDefn->SetName(poNewFieldDefn->GetNameRef());
2404 : }
2405 10 : if (nActualFlags & ALTER_WIDTH_PRECISION_FLAG)
2406 : {
2407 5 : poFieldDefn->SetWidth(poNewFieldDefn->GetWidth());
2408 5 : poFieldDefn->SetPrecision(poNewFieldDefn->GetPrecision());
2409 : }
2410 10 : if (nActualFlags & ALTER_NULLABLE_FLAG)
2411 2 : poFieldDefn->SetNullable(poNewFieldDefn->IsNullable());
2412 10 : if (nActualFlags & ALTER_DEFAULT_FLAG)
2413 2 : poFieldDefn->SetDefault(poNewFieldDefn->GetDefault());
2414 :
2415 10 : return OGRERR_NONE;
2416 : }
2417 :
2418 : /************************************************************************/
2419 : /* AddForeignKeysToTable() */
2420 : /************************************************************************/
2421 :
2422 2 : OGRErr OGRSQLiteTableLayer::AddForeignKeysToTable(const char *pszKeys)
2423 : {
2424 2 : if (HasLayerDefnError())
2425 0 : return OGRERR_FAILURE;
2426 :
2427 2 : if (!m_poDS->GetUpdate())
2428 : {
2429 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
2430 : "AddForeignKeysToTable");
2431 0 : return OGRERR_FAILURE;
2432 : }
2433 :
2434 2 : ClearInsertStmt();
2435 2 : ResetReading();
2436 :
2437 : /* -------------------------------------------------------------------- */
2438 : /* Build list of old fields, and the list of new fields. */
2439 : /* -------------------------------------------------------------------- */
2440 2 : char *pszNewFieldList = nullptr;
2441 2 : char *pszFieldListForSelect = nullptr;
2442 2 : size_t nBufLen = 0;
2443 :
2444 2 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect, nBufLen,
2445 : 0);
2446 :
2447 6 : for (int iField = 0; iField < m_poFeatureDefn->GetFieldCount(); iField++)
2448 : {
2449 4 : OGRFieldDefn *poFldDefn = m_poFeatureDefn->GetFieldDefn(iField);
2450 :
2451 4 : snprintf(pszFieldListForSelect + strlen(pszFieldListForSelect),
2452 4 : nBufLen - strlen(pszFieldListForSelect), ", \"%s\"",
2453 8 : SQLEscapeName(poFldDefn->GetNameRef()).c_str());
2454 :
2455 4 : AddColumnDef(pszNewFieldList, nBufLen, poFldDefn);
2456 : }
2457 :
2458 : /* -------------------------------------------------------------------- */
2459 : /* Recreate table. */
2460 : /* -------------------------------------------------------------------- */
2461 4 : CPLString osErrorMsg;
2462 : osErrorMsg.Printf("Failed to add foreign keys to table %s",
2463 2 : m_poFeatureDefn->GetName());
2464 :
2465 2 : OGRErr eErr = RecreateTable(pszFieldListForSelect, pszNewFieldList,
2466 : osErrorMsg.c_str(), pszKeys);
2467 :
2468 2 : CPLFree(pszFieldListForSelect);
2469 2 : CPLFree(pszNewFieldList);
2470 :
2471 2 : if (eErr != OGRERR_NONE)
2472 0 : return eErr;
2473 :
2474 : /* -------------------------------------------------------------------- */
2475 : /* Finish */
2476 : /* -------------------------------------------------------------------- */
2477 :
2478 2 : return OGRERR_NONE;
2479 : }
2480 :
2481 : /************************************************************************/
2482 : /* ReorderFields() */
2483 : /************************************************************************/
2484 :
2485 15 : OGRErr OGRSQLiteTableLayer::ReorderFields(int *panMap)
2486 : {
2487 15 : if (HasLayerDefnError())
2488 0 : return OGRERR_FAILURE;
2489 :
2490 15 : if (!m_poDS->GetUpdate())
2491 : {
2492 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
2493 : "ReorderFields");
2494 0 : return OGRERR_FAILURE;
2495 : }
2496 :
2497 15 : if (m_poFeatureDefn->GetFieldCount() == 0)
2498 4 : return OGRERR_NONE;
2499 :
2500 11 : OGRErr eErr = OGRCheckPermutation(panMap, m_poFeatureDefn->GetFieldCount());
2501 11 : if (eErr != OGRERR_NONE)
2502 1 : return eErr;
2503 :
2504 10 : ClearInsertStmt();
2505 10 : ResetReading();
2506 :
2507 : /* -------------------------------------------------------------------- */
2508 : /* Build list of old fields, and the list of new fields. */
2509 : /* -------------------------------------------------------------------- */
2510 10 : char *pszNewFieldList = nullptr;
2511 10 : char *pszFieldListForSelect = nullptr;
2512 10 : size_t nBufLen = 0;
2513 :
2514 10 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect, nBufLen);
2515 :
2516 52 : for (int iField = 0; iField < m_poFeatureDefn->GetFieldCount(); iField++)
2517 : {
2518 42 : OGRFieldDefn *poFldDefn = m_poFeatureDefn->GetFieldDefn(panMap[iField]);
2519 :
2520 42 : snprintf(pszFieldListForSelect + strlen(pszFieldListForSelect),
2521 42 : nBufLen - strlen(pszFieldListForSelect), ", \"%s\"",
2522 84 : SQLEscapeName(poFldDefn->GetNameRef()).c_str());
2523 :
2524 42 : AddColumnDef(pszNewFieldList, nBufLen, poFldDefn);
2525 : }
2526 :
2527 : /* -------------------------------------------------------------------- */
2528 : /* Recreate table. */
2529 : /* -------------------------------------------------------------------- */
2530 20 : CPLString osErrorMsg;
2531 : osErrorMsg.Printf("Failed to reorder fields from table %s",
2532 10 : m_poFeatureDefn->GetName());
2533 :
2534 10 : eErr = RecreateTable(pszFieldListForSelect, pszNewFieldList,
2535 : osErrorMsg.c_str());
2536 :
2537 10 : CPLFree(pszFieldListForSelect);
2538 10 : CPLFree(pszNewFieldList);
2539 :
2540 10 : if (eErr != OGRERR_NONE)
2541 0 : return eErr;
2542 :
2543 : /* -------------------------------------------------------------------- */
2544 : /* Finish */
2545 : /* -------------------------------------------------------------------- */
2546 :
2547 10 : eErr = m_poFeatureDefn->ReorderFieldDefns(panMap);
2548 :
2549 10 : RecomputeOrdinals();
2550 :
2551 10 : return eErr;
2552 : }
2553 :
2554 : /************************************************************************/
2555 : /* BindValues() */
2556 : /************************************************************************/
2557 :
2558 : /* the bBindNullValues is set to TRUE by SetFeature() for UPDATE statements, */
2559 : /* and to FALSE by CreateFeature() for INSERT statements; */
2560 :
2561 1544 : OGRErr OGRSQLiteTableLayer::BindValues(OGRFeature *poFeature,
2562 : sqlite3_stmt *m_hStmtIn,
2563 : bool bBindUnsetAsNull)
2564 : {
2565 1544 : sqlite3 *hDB = m_poDS->GetDB();
2566 :
2567 : /* -------------------------------------------------------------------- */
2568 : /* Bind the geometry */
2569 : /* -------------------------------------------------------------------- */
2570 1544 : int nBindField = 1;
2571 1544 : int nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
2572 2229 : for (int iField = 0; iField < nFieldCount; iField++)
2573 : {
2574 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
2575 685 : m_poFeatureDefn->myGetGeomFieldDefn(iField);
2576 685 : OGRSQLiteGeomFormat eGeomFormat = poGeomFieldDefn->m_eGeomFormat;
2577 685 : if (eGeomFormat == OSGF_FGF)
2578 0 : continue;
2579 685 : OGRGeometry *poGeom = poFeature->GetGeomFieldRef(iField);
2580 685 : int rc = SQLITE_OK;
2581 685 : if (poGeom != nullptr)
2582 : {
2583 575 : if (eGeomFormat == OSGF_WKT)
2584 : {
2585 1 : char *pszWKT = nullptr;
2586 1 : poGeom->exportToWkt(&pszWKT);
2587 1 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++, pszWKT, -1,
2588 : CPLFree);
2589 : }
2590 574 : else if (eGeomFormat == OSGF_WKB)
2591 : {
2592 279 : const size_t nWKBLen = poGeom->WkbSize();
2593 279 : if (nWKBLen >
2594 279 : static_cast<size_t>(std::numeric_limits<int>::max()))
2595 : {
2596 0 : CPLError(CE_Failure, CPLE_NotSupported,
2597 : "Too large geometry");
2598 0 : return OGRERR_FAILURE;
2599 : }
2600 279 : GByte *pabyWKB = (GByte *)VSI_MALLOC_VERBOSE(nWKBLen);
2601 279 : if (pabyWKB)
2602 : {
2603 279 : poGeom->exportToWkb(wkbNDR, pabyWKB);
2604 279 : rc = sqlite3_bind_blob(m_hStmtIn, nBindField++, pabyWKB,
2605 : static_cast<int>(nWKBLen), CPLFree);
2606 : }
2607 : else
2608 : {
2609 0 : return OGRERR_FAILURE;
2610 : }
2611 : }
2612 295 : else if (eGeomFormat == OSGF_SpatiaLite)
2613 : {
2614 295 : int nBLOBLen = 0;
2615 295 : GByte *pabySLBLOB = nullptr;
2616 :
2617 295 : const int nSRSId = poGeomFieldDefn->m_nSRSId;
2618 295 : CPL_IGNORE_RET_VAL(ExportSpatiaLiteGeometry(
2619 295 : poGeom, nSRSId, wkbNDR, m_bSpatialite2D, m_bUseComprGeom,
2620 : &pabySLBLOB, &nBLOBLen));
2621 295 : rc = sqlite3_bind_blob(m_hStmtIn, nBindField++, pabySLBLOB,
2622 : nBLOBLen, CPLFree);
2623 : }
2624 : else
2625 : {
2626 0 : rc = SQLITE_OK;
2627 0 : CPL_IGNORE_RET_VAL(rc);
2628 0 : CPLAssert(false);
2629 : }
2630 : }
2631 : else
2632 : {
2633 110 : rc = sqlite3_bind_null(m_hStmtIn, nBindField++);
2634 : }
2635 :
2636 685 : if (rc != SQLITE_OK)
2637 : {
2638 0 : CPLError(CE_Failure, CPLE_AppDefined,
2639 : "sqlite3_bind_blob/text() failed:\n %s",
2640 : sqlite3_errmsg(hDB));
2641 0 : return OGRERR_FAILURE;
2642 : }
2643 : }
2644 :
2645 : /* -------------------------------------------------------------------- */
2646 : /* Bind field values. */
2647 : /* -------------------------------------------------------------------- */
2648 1544 : nFieldCount = m_poFeatureDefn->GetFieldCount();
2649 12006 : for (int iField = 0; iField < nFieldCount; iField++)
2650 : {
2651 10462 : if (iField == m_iFIDAsRegularColumnIndex)
2652 3 : continue;
2653 10459 : if (!bBindUnsetAsNull && !poFeature->IsFieldSet(iField))
2654 10 : continue;
2655 :
2656 10449 : int rc = SQLITE_OK;
2657 :
2658 17277 : if ((bBindUnsetAsNull && !poFeature->IsFieldSet(iField)) ||
2659 6828 : poFeature->IsFieldNull(iField))
2660 : {
2661 3675 : rc = sqlite3_bind_null(m_hStmtIn, nBindField++);
2662 : }
2663 : else
2664 : {
2665 6774 : OGRFieldDefn *poFieldDefn = m_poFeatureDefn->GetFieldDefn(iField);
2666 6774 : switch (poFieldDefn->GetType())
2667 : {
2668 2059 : case OFTInteger:
2669 : {
2670 2059 : int nFieldVal = poFeature->GetFieldAsInteger(iField);
2671 2059 : rc = sqlite3_bind_int(m_hStmtIn, nBindField++, nFieldVal);
2672 2059 : break;
2673 : }
2674 :
2675 211 : case OFTInteger64:
2676 : {
2677 211 : GIntBig nFieldVal = poFeature->GetFieldAsInteger64(iField);
2678 211 : rc = sqlite3_bind_int64(m_hStmtIn, nBindField++, nFieldVal);
2679 211 : break;
2680 : }
2681 :
2682 409 : case OFTReal:
2683 : {
2684 409 : double dfFieldVal = poFeature->GetFieldAsDouble(iField);
2685 409 : rc = sqlite3_bind_double(m_hStmtIn, nBindField++,
2686 : dfFieldVal);
2687 409 : break;
2688 : }
2689 :
2690 39 : case OFTBinary:
2691 : {
2692 39 : int nDataLength = 0;
2693 : GByte *pabyData =
2694 39 : poFeature->GetFieldAsBinary(iField, &nDataLength);
2695 39 : rc = sqlite3_bind_blob(m_hStmtIn, nBindField++, pabyData,
2696 : nDataLength, SQLITE_TRANSIENT);
2697 39 : break;
2698 : }
2699 :
2700 134 : case OFTDateTime:
2701 : {
2702 : char *pszStr =
2703 134 : OGRGetXMLDateTime(poFeature->GetRawFieldRef(iField));
2704 134 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++, pszStr, -1,
2705 : SQLITE_TRANSIENT);
2706 134 : CPLFree(pszStr);
2707 134 : break;
2708 : }
2709 :
2710 86 : case OFTDate:
2711 : {
2712 86 : int nYear = 0;
2713 86 : int nMonth = 0;
2714 86 : int nDay = 0;
2715 86 : int nHour = 0;
2716 86 : int nMinute = 0;
2717 86 : int nSecond = 0;
2718 86 : int nTZ = 0;
2719 86 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth,
2720 : &nDay, &nHour, &nMinute,
2721 : &nSecond, &nTZ);
2722 : char szBuffer[64];
2723 86 : snprintf(szBuffer, sizeof(szBuffer), "%04d-%02d-%02d",
2724 : nYear, nMonth, nDay);
2725 86 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++, szBuffer,
2726 : -1, SQLITE_TRANSIENT);
2727 86 : break;
2728 : }
2729 :
2730 4 : case OFTTime:
2731 : {
2732 4 : int nYear = 0;
2733 4 : int nMonth = 0;
2734 4 : int nDay = 0;
2735 4 : int nHour = 0;
2736 4 : int nMinute = 0;
2737 4 : int nTZ = 0;
2738 4 : float fSecond = 0.0f;
2739 4 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth,
2740 : &nDay, &nHour, &nMinute,
2741 : &fSecond, &nTZ);
2742 : char szBuffer[64];
2743 4 : if (OGR_GET_MS(fSecond) != 0)
2744 0 : snprintf(szBuffer, sizeof(szBuffer), "%02d:%02d:%06.3f",
2745 : nHour, nMinute, fSecond);
2746 : else
2747 4 : snprintf(szBuffer, sizeof(szBuffer), "%02d:%02d:%02d",
2748 : nHour, nMinute, (int)fSecond);
2749 4 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++, szBuffer,
2750 : -1, SQLITE_TRANSIENT);
2751 4 : break;
2752 : }
2753 :
2754 58 : case OFTStringList:
2755 : case OFTIntegerList:
2756 : case OFTInteger64List:
2757 : case OFTRealList:
2758 : {
2759 58 : char *pszJSon = poFeature->GetFieldAsSerializedJSon(iField);
2760 58 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++, pszJSon, -1,
2761 : SQLITE_TRANSIENT);
2762 58 : CPLFree(pszJSon);
2763 58 : break;
2764 : }
2765 :
2766 3774 : default:
2767 : {
2768 : const char *pszRawValue =
2769 3774 : poFeature->GetFieldAsString(iField);
2770 3774 : if (CSLFindString(m_papszCompressedColumns,
2771 3774 : m_poFeatureDefn->GetFieldDefn(iField)
2772 3774 : ->GetNameRef()) >= 0)
2773 : {
2774 17 : size_t nBytesOut = 0;
2775 : void *pOut =
2776 17 : CPLZLibDeflate(pszRawValue, strlen(pszRawValue), -1,
2777 : nullptr, 0, &nBytesOut);
2778 17 : if (pOut != nullptr)
2779 : {
2780 17 : rc = sqlite3_bind_blob(
2781 : m_hStmtIn, nBindField++, pOut,
2782 : static_cast<int>(nBytesOut), CPLFree);
2783 : }
2784 : else
2785 0 : rc = SQLITE_ERROR;
2786 : }
2787 : else
2788 : {
2789 3757 : rc = sqlite3_bind_text(m_hStmtIn, nBindField++,
2790 : pszRawValue, -1,
2791 : SQLITE_TRANSIENT);
2792 : }
2793 3774 : break;
2794 : }
2795 : }
2796 : }
2797 :
2798 10449 : if (rc != SQLITE_OK)
2799 : {
2800 0 : CPLError(CE_Failure, CPLE_AppDefined,
2801 : "sqlite3_bind_() for column %s failed:\n %s",
2802 0 : m_poFeatureDefn->GetFieldDefn(iField)->GetNameRef(),
2803 : sqlite3_errmsg(hDB));
2804 0 : return OGRERR_FAILURE;
2805 : }
2806 : }
2807 :
2808 1544 : return OGRERR_NONE;
2809 : }
2810 :
2811 : /************************************************************************/
2812 : /* ISetFeature() */
2813 : /************************************************************************/
2814 :
2815 26 : OGRErr OGRSQLiteTableLayer::ISetFeature(OGRFeature *poFeature)
2816 :
2817 : {
2818 26 : if (HasLayerDefnError())
2819 0 : return OGRERR_FAILURE;
2820 :
2821 26 : if (m_pszFIDColumn == nullptr)
2822 : {
2823 0 : CPLError(CE_Failure, CPLE_AppDefined,
2824 : "SetFeature() without any FID column.");
2825 0 : return OGRERR_FAILURE;
2826 : }
2827 :
2828 26 : if (poFeature->GetFID() == OGRNullFID)
2829 : {
2830 0 : CPLError(CE_Failure, CPLE_AppDefined,
2831 : "SetFeature() with unset FID fails.");
2832 0 : return OGRERR_FAILURE;
2833 : }
2834 :
2835 26 : if (!m_poDS->GetUpdate())
2836 : {
2837 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
2838 : "SetFeature");
2839 0 : return OGRERR_FAILURE;
2840 : }
2841 :
2842 : /* In case the FID column has also been created as a regular field */
2843 26 : if (m_iFIDAsRegularColumnIndex >= 0)
2844 : {
2845 5 : if (!poFeature->IsFieldSetAndNotNull(m_iFIDAsRegularColumnIndex) ||
2846 2 : poFeature->GetFieldAsInteger64(m_iFIDAsRegularColumnIndex) !=
2847 2 : poFeature->GetFID())
2848 : {
2849 2 : CPLError(CE_Failure, CPLE_AppDefined,
2850 : "Inconsistent values of FID and field of same name");
2851 2 : return OGRERR_FAILURE;
2852 : }
2853 : }
2854 :
2855 24 : if (m_bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE)
2856 0 : return OGRERR_FAILURE;
2857 :
2858 24 : sqlite3 *hDB = m_poDS->GetDB();
2859 24 : int bNeedComma = false;
2860 :
2861 : /* -------------------------------------------------------------------- */
2862 : /* Form the UPDATE command. */
2863 : /* -------------------------------------------------------------------- */
2864 48 : CPLString osCommand = CPLSPrintf("UPDATE '%s' SET ", m_pszEscapedTableName);
2865 :
2866 : /* -------------------------------------------------------------------- */
2867 : /* Add geometry field name. */
2868 : /* -------------------------------------------------------------------- */
2869 24 : int nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
2870 48 : for (int iField = 0; iField < nFieldCount; iField++)
2871 : {
2872 : OGRSQLiteGeomFormat eGeomFormat =
2873 24 : m_poFeatureDefn->myGetGeomFieldDefn(iField)->m_eGeomFormat;
2874 24 : if (eGeomFormat == OSGF_FGF)
2875 0 : continue;
2876 24 : if (bNeedComma)
2877 1 : osCommand += ",";
2878 :
2879 24 : osCommand += "\"";
2880 48 : osCommand += SQLEscapeName(
2881 48 : m_poFeatureDefn->GetGeomFieldDefn(iField)->GetNameRef());
2882 24 : osCommand += "\" = ?";
2883 :
2884 24 : bNeedComma = true;
2885 : }
2886 :
2887 : /* -------------------------------------------------------------------- */
2888 : /* Add field names. */
2889 : /* -------------------------------------------------------------------- */
2890 24 : nFieldCount = m_poFeatureDefn->GetFieldCount();
2891 99 : for (int iField = 0; iField < nFieldCount; iField++)
2892 : {
2893 75 : if (iField == m_iFIDAsRegularColumnIndex)
2894 1 : continue;
2895 74 : if (!poFeature->IsFieldSet(iField))
2896 0 : continue;
2897 74 : if (bNeedComma)
2898 73 : osCommand += ",";
2899 :
2900 74 : osCommand += "\"";
2901 : osCommand +=
2902 74 : SQLEscapeName(m_poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
2903 74 : osCommand += "\" = ?";
2904 :
2905 74 : bNeedComma = true;
2906 : }
2907 :
2908 24 : if (!bNeedComma)
2909 0 : return OGRERR_NONE;
2910 :
2911 : /* -------------------------------------------------------------------- */
2912 : /* Merge final command. */
2913 : /* -------------------------------------------------------------------- */
2914 24 : osCommand += " WHERE \"";
2915 24 : osCommand += SQLEscapeName(m_pszFIDColumn);
2916 24 : osCommand += CPLSPrintf("\" = " CPL_FRMT_GIB, poFeature->GetFID());
2917 :
2918 : /* -------------------------------------------------------------------- */
2919 : /* Prepare the statement. */
2920 : /* -------------------------------------------------------------------- */
2921 : #ifdef DEBUG_VERBOSE
2922 : CPLDebug("OGR_SQLITE", "prepare_v2(%s)", osCommand.c_str());
2923 : #endif
2924 :
2925 24 : sqlite3_stmt *hUpdateStmt = nullptr;
2926 24 : int rc = sqlite3_prepare_v2(hDB, osCommand, -1, &hUpdateStmt, nullptr);
2927 :
2928 24 : if (rc != SQLITE_OK)
2929 : {
2930 0 : CPLError(CE_Failure, CPLE_AppDefined,
2931 : "In SetFeature(): sqlite3_prepare_v2(%s):\n %s",
2932 : osCommand.c_str(), sqlite3_errmsg(hDB));
2933 :
2934 0 : return OGRERR_FAILURE;
2935 : }
2936 :
2937 : /* -------------------------------------------------------------------- */
2938 : /* Bind values. */
2939 : /* -------------------------------------------------------------------- */
2940 24 : OGRErr eErr = BindValues(poFeature, hUpdateStmt, false);
2941 24 : if (eErr != OGRERR_NONE)
2942 : {
2943 0 : sqlite3_finalize(hUpdateStmt);
2944 0 : return eErr;
2945 : }
2946 :
2947 : /* -------------------------------------------------------------------- */
2948 : /* Execute the update. */
2949 : /* -------------------------------------------------------------------- */
2950 24 : rc = sqlite3_step(hUpdateStmt);
2951 :
2952 24 : if (rc != SQLITE_OK && rc != SQLITE_DONE)
2953 : {
2954 0 : CPLError(CE_Failure, CPLE_AppDefined, "sqlite3_step() failed:\n %s",
2955 : sqlite3_errmsg(hDB));
2956 :
2957 0 : sqlite3_finalize(hUpdateStmt);
2958 0 : return OGRERR_FAILURE;
2959 : }
2960 :
2961 24 : sqlite3_finalize(hUpdateStmt);
2962 :
2963 24 : eErr =
2964 24 : (sqlite3_changes(hDB) > 0) ? OGRERR_NONE : OGRERR_NON_EXISTING_FEATURE;
2965 24 : if (eErr == OGRERR_NONE)
2966 : {
2967 20 : nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
2968 40 : for (int iField = 0; iField < nFieldCount; iField++)
2969 : {
2970 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
2971 20 : m_poFeatureDefn->myGetGeomFieldDefn(iField);
2972 20 : OGRGeometry *poGeom = poFeature->GetGeomFieldRef(iField);
2973 24 : if (poGeomFieldDefn->m_bCachedExtentIsValid && poGeom != nullptr &&
2974 4 : !poGeom->IsEmpty())
2975 : {
2976 4 : OGREnvelope sGeomEnvelope;
2977 4 : poGeom->getEnvelope(&sGeomEnvelope);
2978 4 : poGeomFieldDefn->m_oCachedExtent.Merge(sGeomEnvelope);
2979 : }
2980 : }
2981 20 : ForceStatisticsToBeFlushed();
2982 : }
2983 :
2984 24 : return eErr;
2985 : }
2986 :
2987 : /************************************************************************/
2988 : /* AreTriggersSimilar */
2989 : /************************************************************************/
2990 :
2991 278 : static int AreTriggersSimilar(const char *pszExpectedTrigger,
2992 : const char *pszTriggerSQL)
2993 : {
2994 278 : int i = 0; // Used after for.
2995 93433 : for (; pszTriggerSQL[i] != '\0' && pszExpectedTrigger[i] != '\0'; i++)
2996 : {
2997 93155 : if (pszTriggerSQL[i] == pszExpectedTrigger[i])
2998 91765 : continue;
2999 1390 : if (pszTriggerSQL[i] == '\n' && pszExpectedTrigger[i] == ' ')
3000 1390 : continue;
3001 0 : if (pszTriggerSQL[i] == ' ' && pszExpectedTrigger[i] == '\n')
3002 0 : continue;
3003 0 : return FALSE;
3004 : }
3005 278 : return pszTriggerSQL[i] == '\0' && pszExpectedTrigger[i] == '\0';
3006 : }
3007 :
3008 : /************************************************************************/
3009 : /* ICreateFeature() */
3010 : /************************************************************************/
3011 :
3012 1535 : OGRErr OGRSQLiteTableLayer::ICreateFeature(OGRFeature *poFeature)
3013 :
3014 : {
3015 1535 : sqlite3 *hDB = m_poDS->GetDB();
3016 3070 : CPLString osCommand;
3017 1535 : bool bNeedComma = false;
3018 :
3019 1535 : if (HasLayerDefnError())
3020 0 : return OGRERR_FAILURE;
3021 :
3022 1535 : if (!m_poDS->GetUpdate())
3023 : {
3024 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
3025 : "CreateFeature");
3026 0 : return OGRERR_FAILURE;
3027 : }
3028 :
3029 1535 : if (m_bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE)
3030 0 : return OGRERR_FAILURE;
3031 :
3032 : // For speed-up, disable Spatialite triggers that :
3033 : // * check the geometry type
3034 : // * update the last_insert columns in geometry_columns_time and the spatial
3035 : // index We do that only if there's no spatial index currently active We'll
3036 : // check ourselves the first constraint and update last_insert at layer
3037 : // closing
3038 1730 : if (!m_bHasCheckedTriggers && m_poDS->HasSpatialite4Layout() &&
3039 195 : m_poFeatureDefn->GetGeomFieldCount() > 0)
3040 : {
3041 162 : m_bHasCheckedTriggers = true;
3042 :
3043 162 : char *pszErrMsg = nullptr;
3044 :
3045 : // Backup INSERT ON triggers
3046 162 : int nRowCount = 0, nColCount = 0;
3047 162 : char **papszResult = nullptr;
3048 : char *pszSQL3 =
3049 162 : sqlite3_mprintf("SELECT name, sql FROM sqlite_master WHERE "
3050 : "tbl_name = '%q' AND type = 'trigger' AND (name "
3051 : "LIKE 'ggi_%%' OR name LIKE 'tmi_%%')",
3052 : m_pszTableName);
3053 162 : sqlite3_get_table(m_poDS->GetDB(), pszSQL3, &papszResult, &nRowCount,
3054 : &nColCount, &pszErrMsg);
3055 162 : sqlite3_free(pszSQL3);
3056 :
3057 162 : if (pszErrMsg)
3058 0 : sqlite3_free(pszErrMsg);
3059 162 : pszErrMsg = nullptr;
3060 :
3061 326 : for (int j = 0; j < m_poFeatureDefn->GetGeomFieldCount(); j++)
3062 : {
3063 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3064 164 : m_poFeatureDefn->myGetGeomFieldDefn(j);
3065 164 : if (!((m_bDeferredSpatialIndexCreation ||
3066 25 : !poGeomFieldDefn->m_bHasSpatialIndex)))
3067 25 : continue;
3068 139 : const char *pszGeomCol = poGeomFieldDefn->GetNameRef();
3069 :
3070 425 : for (int i = 0; i < nRowCount; i++)
3071 : {
3072 286 : const char *pszTriggerName = papszResult[2 * (i + 1) + 0];
3073 286 : const char *pszTriggerSQL = papszResult[2 * (i + 1) + 1];
3074 572 : if (pszTriggerName != nullptr && pszTriggerSQL != nullptr &&
3075 572 : CPLString(pszTriggerName)
3076 286 : .tolower()
3077 572 : .find(CPLString(pszGeomCol).tolower()) !=
3078 : std::string::npos)
3079 : {
3080 278 : const char *pszExpectedTrigger = nullptr;
3081 278 : if (STARTS_WITH(pszTriggerName, "ggi_"))
3082 : {
3083 139 : pszExpectedTrigger = CPLSPrintf(
3084 : "CREATE TRIGGER \"ggi_%s_%s\" BEFORE INSERT ON "
3085 : "\"%s\" "
3086 : "FOR EACH ROW BEGIN "
3087 : "SELECT RAISE(ROLLBACK, '%s.%s violates Geometry "
3088 : "constraint [geom-type or SRID not allowed]') "
3089 : "WHERE (SELECT geometry_type FROM geometry_columns "
3090 : "WHERE Lower(f_table_name) = Lower('%s') AND "
3091 : "Lower(f_geometry_column) = Lower('%s') "
3092 : "AND GeometryConstraints(NEW.\"%s\", "
3093 : "geometry_type, srid) = 1) IS NULL; "
3094 : "END",
3095 : m_pszTableName, pszGeomCol, m_pszTableName,
3096 : m_pszTableName, pszGeomCol, m_pszTableName,
3097 : pszGeomCol, pszGeomCol);
3098 : }
3099 139 : else if (STARTS_WITH(pszTriggerName, "tmi_"))
3100 : {
3101 139 : pszExpectedTrigger = CPLSPrintf(
3102 : "CREATE TRIGGER \"tmi_%s_%s\" AFTER INSERT ON "
3103 : "\"%s\" "
3104 : "FOR EACH ROW BEGIN "
3105 : "UPDATE geometry_columns_time SET last_insert = "
3106 : "strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ', 'now') "
3107 : "WHERE Lower(f_table_name) = Lower('%s') AND "
3108 : "Lower(f_geometry_column) = Lower('%s'); "
3109 : "END",
3110 : m_pszTableName, pszGeomCol, m_pszTableName,
3111 : m_pszTableName, pszGeomCol);
3112 : }
3113 : /* Cannot happen due to the tests that lead to that code
3114 : * path */
3115 : /* that check there's no spatial index active */
3116 : /* A further potential optimization would be to rebuild the
3117 : * spatial index */
3118 : /* afterwards... */
3119 : /*else if( STARTS_WITH(pszTriggerName, "gii_") )
3120 : {
3121 : pszExpectedTrigger = CPLSPrintf(
3122 : "CREATE TRIGGER \"gii_%s_%s\" AFTER INSERT ON \"%s\" "
3123 : "FOR EACH ROW BEGIN "
3124 : "UPDATE geometry_columns_time SET last_insert =
3125 : strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ', 'now') " "WHERE
3126 : Lower(f_table_name) = Lower('%s') AND
3127 : Lower(f_geometry_column) = Lower('%s'); " "DELETE FROM
3128 : \"idx_%s_%s\" WHERE pkid=NEW.ROWID; " "SELECT
3129 : RTreeAlign('idx_%s_%s', NEW.ROWID, NEW.\"%s\"); " "END",
3130 : m_pszTableName, pszGeomCol, m_pszTableName,
3131 : m_pszTableName, pszGeomCol,
3132 : m_pszTableName, pszGeomCol,
3133 : m_pszTableName, pszGeomCol, pszGeomCol);
3134 : }*/
3135 :
3136 556 : if (pszExpectedTrigger != nullptr &&
3137 278 : AreTriggersSimilar(pszExpectedTrigger, pszTriggerSQL))
3138 : {
3139 : // And drop them
3140 : pszSQL3 =
3141 278 : sqlite3_mprintf("DROP TRIGGER %s", pszTriggerName);
3142 278 : int rc = sqlite3_exec(m_poDS->GetDB(), pszSQL3, nullptr,
3143 : nullptr, &pszErrMsg);
3144 278 : if (rc != SQLITE_OK)
3145 0 : CPLDebug("SQLITE", "Error %s",
3146 0 : pszErrMsg ? pszErrMsg : "");
3147 : else
3148 : {
3149 278 : CPLDebug("SQLite", "Dropping trigger %s",
3150 : pszTriggerName);
3151 278 : poGeomFieldDefn->m_aosDisabledTriggers.push_back(
3152 556 : std::pair<CPLString, CPLString>(pszTriggerName,
3153 : pszTriggerSQL));
3154 : }
3155 278 : sqlite3_free(pszSQL3);
3156 278 : if (pszErrMsg)
3157 0 : sqlite3_free(pszErrMsg);
3158 278 : pszErrMsg = nullptr;
3159 : }
3160 : else
3161 : {
3162 0 : CPLDebug("SQLite",
3163 : "Cannot drop %s trigger. Doesn't match "
3164 : "expected definition",
3165 : pszTriggerName);
3166 : }
3167 : }
3168 : }
3169 : }
3170 :
3171 162 : sqlite3_free_table(papszResult);
3172 : }
3173 :
3174 1535 : ResetReading();
3175 :
3176 2196 : for (int j = 0; j < m_poFeatureDefn->GetGeomFieldCount(); j++)
3177 : {
3178 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3179 675 : m_poFeatureDefn->myGetGeomFieldDefn(j);
3180 675 : OGRGeometry *poGeom = poFeature->GetGeomFieldRef(j);
3181 675 : if (!poGeomFieldDefn->m_aosDisabledTriggers.empty() &&
3182 : poGeom != nullptr)
3183 : {
3184 256 : OGRwkbGeometryType eGeomType = poGeomFieldDefn->GetType();
3185 404 : if (eGeomType != wkbUnknown &&
3186 148 : poGeom->getGeometryType() != eGeomType)
3187 : {
3188 42 : CPLError(CE_Failure, CPLE_AppDefined,
3189 : "Cannot insert feature with geometry of type %s%s in "
3190 : "column %s. Type %s%s expected",
3191 14 : OGRToOGCGeomType(poGeom->getGeometryType()),
3192 14 : (wkbFlatten(poGeom->getGeometryType()) !=
3193 14 : poGeom->getGeometryType())
3194 : ? "Z"
3195 : : "",
3196 : poGeomFieldDefn->GetNameRef(),
3197 : OGRToOGCGeomType(eGeomType),
3198 14 : (wkbFlatten(eGeomType) != eGeomType) ? "Z" : "");
3199 14 : return OGRERR_FAILURE;
3200 : }
3201 : }
3202 : }
3203 :
3204 1521 : int bReuseStmt = false;
3205 :
3206 : /* If there's a unset field with a default value, then we must create */
3207 : /* a specific INSERT statement to avoid unset fields to be bound to NULL */
3208 1521 : bool bHasDefaultValue = false;
3209 1521 : int nFieldCount = m_poFeatureDefn->GetFieldCount();
3210 11901 : for (int iField = 0; iField < nFieldCount; iField++)
3211 : {
3212 14006 : if (!poFeature->IsFieldSet(iField) &&
3213 3625 : poFeature->GetFieldDefnRef(iField)->GetDefault() != nullptr)
3214 : {
3215 1 : bHasDefaultValue = true;
3216 1 : break;
3217 : }
3218 : }
3219 :
3220 : /* In case the FID column has also been created as a regular field */
3221 1521 : if (m_iFIDAsRegularColumnIndex >= 0)
3222 : {
3223 3 : if (poFeature->GetFID() == OGRNullFID)
3224 : {
3225 2 : if (poFeature->IsFieldSetAndNotNull(m_iFIDAsRegularColumnIndex))
3226 : {
3227 1 : poFeature->SetFID(
3228 1 : poFeature->GetFieldAsInteger64(m_iFIDAsRegularColumnIndex));
3229 : }
3230 : }
3231 : else
3232 : {
3233 2 : if (!poFeature->IsFieldSetAndNotNull(m_iFIDAsRegularColumnIndex) ||
3234 1 : poFeature->GetFieldAsInteger64(m_iFIDAsRegularColumnIndex) !=
3235 1 : poFeature->GetFID())
3236 : {
3237 1 : CPLError(CE_Failure, CPLE_AppDefined,
3238 : "Inconsistent values of FID and field of same name");
3239 1 : return OGRERR_FAILURE;
3240 : }
3241 : }
3242 : }
3243 :
3244 : int bTemporaryStatement =
3245 1520 : (poFeature->GetFID() != OGRNullFID || bHasDefaultValue);
3246 1520 : if (m_hInsertStmt == nullptr || bTemporaryStatement)
3247 : {
3248 886 : CPLString osValues;
3249 :
3250 : /* --------------------------------------------------------------------
3251 : */
3252 : /* Form the INSERT command. */
3253 : /* --------------------------------------------------------------------
3254 : */
3255 443 : osCommand += CPLSPrintf("INSERT INTO '%s' (", m_pszEscapedTableName);
3256 :
3257 : /* --------------------------------------------------------------------
3258 : */
3259 : /* Add FID if we have a cleartext FID column. */
3260 : /* --------------------------------------------------------------------
3261 : */
3262 443 : if (m_pszFIDColumn != nullptr && poFeature->GetFID() != OGRNullFID)
3263 : {
3264 68 : osCommand += "\"";
3265 68 : osCommand += SQLEscapeName(m_pszFIDColumn);
3266 68 : osCommand += "\"";
3267 :
3268 68 : osValues += CPLSPrintf(CPL_FRMT_GIB, poFeature->GetFID());
3269 68 : bNeedComma = true;
3270 : }
3271 :
3272 : /* --------------------------------------------------------------------
3273 : */
3274 : /* Add geometry. */
3275 : /* --------------------------------------------------------------------
3276 : */
3277 443 : nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
3278 732 : for (int iField = 0; iField < nFieldCount; iField++)
3279 : {
3280 : OGRSQLiteGeomFormat eGeomFormat =
3281 289 : m_poFeatureDefn->myGetGeomFieldDefn(iField)->m_eGeomFormat;
3282 289 : if (eGeomFormat == OSGF_FGF)
3283 0 : continue;
3284 289 : if (bHasDefaultValue &&
3285 0 : poFeature->GetGeomFieldRef(iField) == nullptr)
3286 0 : continue;
3287 289 : if (bNeedComma)
3288 : {
3289 63 : osCommand += ",";
3290 63 : osValues += ",";
3291 : }
3292 :
3293 289 : osCommand += "\"";
3294 578 : osCommand += SQLEscapeName(
3295 578 : m_poFeatureDefn->GetGeomFieldDefn(iField)->GetNameRef());
3296 289 : osCommand += "\"";
3297 :
3298 289 : osValues += "?";
3299 :
3300 289 : bNeedComma = true;
3301 : }
3302 :
3303 : /* --------------------------------------------------------------------
3304 : */
3305 : /* Add field values. */
3306 : /* --------------------------------------------------------------------
3307 : */
3308 443 : nFieldCount = m_poFeatureDefn->GetFieldCount();
3309 1818 : for (int iField = 0; iField < nFieldCount; iField++)
3310 : {
3311 1375 : if (iField == m_iFIDAsRegularColumnIndex)
3312 2 : continue;
3313 1373 : if (bHasDefaultValue && !poFeature->IsFieldSet(iField))
3314 10 : continue;
3315 :
3316 1363 : if (bNeedComma)
3317 : {
3318 1215 : osCommand += ",";
3319 1215 : osValues += ",";
3320 : }
3321 :
3322 1363 : osCommand += "\"";
3323 2726 : osCommand += SQLEscapeName(
3324 2726 : m_poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
3325 1363 : osCommand += "\"";
3326 :
3327 1363 : osValues += "?";
3328 :
3329 1363 : bNeedComma = true;
3330 : }
3331 :
3332 : /* --------------------------------------------------------------------
3333 : */
3334 : /* Merge final command. */
3335 : /* --------------------------------------------------------------------
3336 : */
3337 443 : osCommand += ") VALUES (";
3338 443 : osCommand += osValues;
3339 443 : osCommand += ")";
3340 :
3341 443 : if (bNeedComma == false)
3342 : osCommand = CPLSPrintf("INSERT INTO '%s' DEFAULT VALUES",
3343 444 : m_pszEscapedTableName);
3344 : }
3345 : else
3346 : {
3347 1077 : bReuseStmt = true;
3348 : }
3349 :
3350 : /* -------------------------------------------------------------------- */
3351 : /* Prepare the statement. */
3352 : /* -------------------------------------------------------------------- */
3353 1963 : if (!bReuseStmt &&
3354 443 : (m_hInsertStmt == nullptr || osCommand != m_osLastInsertStmt))
3355 : {
3356 : #ifdef DEBUG
3357 443 : CPLDebug("OGR_SQLITE", "prepare_v2(%s)", osCommand.c_str());
3358 : #endif
3359 :
3360 443 : ClearInsertStmt();
3361 443 : if (poFeature->GetFID() == OGRNullFID)
3362 375 : m_osLastInsertStmt = osCommand;
3363 :
3364 : const int rc =
3365 443 : sqlite3_prepare_v2(hDB, osCommand, -1, &m_hInsertStmt, nullptr);
3366 443 : if (rc != SQLITE_OK)
3367 : {
3368 0 : CPLError(CE_Failure, CPLE_AppDefined,
3369 : "In CreateFeature(): sqlite3_prepare_v2(%s):\n %s",
3370 : osCommand.c_str(), sqlite3_errmsg(hDB));
3371 :
3372 0 : ClearInsertStmt();
3373 0 : return OGRERR_FAILURE;
3374 : }
3375 : }
3376 :
3377 : /* -------------------------------------------------------------------- */
3378 : /* Bind values. */
3379 : /* -------------------------------------------------------------------- */
3380 1520 : OGRErr eErr = BindValues(poFeature, m_hInsertStmt, !bHasDefaultValue);
3381 1520 : if (eErr != OGRERR_NONE)
3382 : {
3383 0 : sqlite3_reset(m_hInsertStmt);
3384 0 : return eErr;
3385 : }
3386 :
3387 : /* -------------------------------------------------------------------- */
3388 : /* Execute the insert. */
3389 : /* -------------------------------------------------------------------- */
3390 1520 : const int rc = sqlite3_step(m_hInsertStmt);
3391 :
3392 1520 : if (rc != SQLITE_OK && rc != SQLITE_DONE)
3393 : {
3394 3 : CPLError(CE_Failure, CPLE_AppDefined,
3395 : "sqlite3_step() failed:\n %s (%d)", sqlite3_errmsg(hDB), rc);
3396 3 : sqlite3_reset(m_hInsertStmt);
3397 3 : ClearInsertStmt();
3398 3 : return OGRERR_FAILURE;
3399 : }
3400 :
3401 : /* -------------------------------------------------------------------- */
3402 : /* Capture the FID/rowid. */
3403 : /* -------------------------------------------------------------------- */
3404 1517 : const sqlite_int64 nFID = sqlite3_last_insert_rowid(hDB);
3405 1517 : if (nFID > 0)
3406 : {
3407 1515 : poFeature->SetFID(nFID);
3408 1515 : if (m_iFIDAsRegularColumnIndex >= 0)
3409 2 : poFeature->SetField(m_iFIDAsRegularColumnIndex, nFID);
3410 : }
3411 :
3412 1517 : sqlite3_reset(m_hInsertStmt);
3413 :
3414 1517 : if (bTemporaryStatement)
3415 68 : ClearInsertStmt();
3416 :
3417 1517 : nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
3418 2173 : for (int iField = 0; iField < nFieldCount; iField++)
3419 : {
3420 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3421 656 : m_poFeatureDefn->myGetGeomFieldDefn(iField);
3422 656 : OGRGeometry *poGeom = poFeature->GetGeomFieldRef(iField);
3423 :
3424 656 : if ((poGeomFieldDefn->m_bCachedExtentIsValid || m_nFeatureCount == 0) &&
3425 1312 : poGeom != nullptr && !poGeom->IsEmpty())
3426 : {
3427 465 : OGREnvelope sGeomEnvelope;
3428 465 : poGeom->getEnvelope(&sGeomEnvelope);
3429 465 : poGeomFieldDefn->m_oCachedExtent.Merge(sGeomEnvelope);
3430 465 : poGeomFieldDefn->m_bCachedExtentIsValid = true;
3431 465 : ForceStatisticsToBeFlushed();
3432 : }
3433 : }
3434 :
3435 1517 : if (m_nFeatureCount >= 0)
3436 : {
3437 1421 : ForceStatisticsToBeFlushed();
3438 1421 : m_nFeatureCount++;
3439 : }
3440 :
3441 1517 : return OGRERR_NONE;
3442 : }
3443 :
3444 : /************************************************************************/
3445 : /* DeleteFeature() */
3446 : /************************************************************************/
3447 :
3448 13 : OGRErr OGRSQLiteTableLayer::DeleteFeature(GIntBig nFID)
3449 :
3450 : {
3451 26 : CPLString osSQL;
3452 :
3453 13 : if (HasLayerDefnError())
3454 0 : return OGRERR_FAILURE;
3455 :
3456 13 : if (m_pszFIDColumn == nullptr)
3457 : {
3458 0 : CPLError(CE_Failure, CPLE_NotSupported,
3459 : "Can't delete feature on a layer without FID column.");
3460 0 : return OGRERR_FAILURE;
3461 : }
3462 :
3463 13 : if (!m_poDS->GetUpdate())
3464 : {
3465 0 : CPLError(CE_Failure, CPLE_NotSupported, UNSUPPORTED_OP_READ_ONLY,
3466 : "DeleteFeature");
3467 0 : return OGRERR_FAILURE;
3468 : }
3469 :
3470 13 : if (m_bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE)
3471 0 : return OGRERR_FAILURE;
3472 :
3473 13 : ResetReading();
3474 :
3475 : osSQL.Printf("DELETE FROM '%s' WHERE \"%s\" = " CPL_FRMT_GIB,
3476 26 : m_pszEscapedTableName, SQLEscapeName(m_pszFIDColumn).c_str(),
3477 13 : nFID);
3478 :
3479 13 : CPLDebug("OGR_SQLITE", "exec(%s)", osSQL.c_str());
3480 :
3481 13 : if (SQLCommand(m_poDS->GetDB(), osSQL) != OGRERR_NONE)
3482 0 : return OGRERR_FAILURE;
3483 :
3484 13 : OGRErr eErr = (sqlite3_changes(m_poDS->GetDB()) > 0)
3485 13 : ? OGRERR_NONE
3486 13 : : OGRERR_NON_EXISTING_FEATURE;
3487 13 : if (eErr == OGRERR_NONE)
3488 : {
3489 6 : int nFieldCount = m_poFeatureDefn->GetGeomFieldCount();
3490 12 : for (int iField = 0; iField < nFieldCount; iField++)
3491 : {
3492 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3493 6 : m_poFeatureDefn->myGetGeomFieldDefn(iField);
3494 6 : poGeomFieldDefn->m_bCachedExtentIsValid = false;
3495 : }
3496 6 : m_nFeatureCount--;
3497 6 : ForceStatisticsToBeFlushed();
3498 : }
3499 :
3500 13 : return eErr;
3501 : }
3502 :
3503 : /************************************************************************/
3504 : /* CreateSpatialIndex() */
3505 : /************************************************************************/
3506 :
3507 131 : int OGRSQLiteTableLayer::CreateSpatialIndex(int iGeomCol)
3508 : {
3509 262 : CPLString osCommand;
3510 :
3511 131 : if (m_bDeferredCreation)
3512 0 : RunDeferredCreationIfNecessary();
3513 :
3514 131 : if (iGeomCol < 0 || iGeomCol >= m_poFeatureDefn->GetGeomFieldCount())
3515 0 : return FALSE;
3516 :
3517 : osCommand.Printf(
3518 : "SELECT CreateSpatialIndex('%s', '%s')", m_pszEscapedTableName,
3519 262 : SQLEscapeLiteral(
3520 131 : m_poFeatureDefn->GetGeomFieldDefn(iGeomCol)->GetNameRef())
3521 131 : .c_str());
3522 :
3523 131 : char *pszErrMsg = nullptr;
3524 131 : sqlite3 *hDB = m_poDS->GetDB();
3525 : #ifdef DEBUG
3526 131 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
3527 : #endif
3528 131 : int rc = sqlite3_exec(hDB, osCommand, nullptr, nullptr, &pszErrMsg);
3529 131 : if (rc != SQLITE_OK)
3530 : {
3531 0 : CPLError(CE_Failure, CPLE_AppDefined,
3532 : "Unable to create spatial index:\n%s", pszErrMsg);
3533 0 : sqlite3_free(pszErrMsg);
3534 0 : return FALSE;
3535 : }
3536 :
3537 131 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomCol)->m_bHasSpatialIndex = true;
3538 131 : return TRUE;
3539 : }
3540 :
3541 : /************************************************************************/
3542 : /* RunDeferredCreationIfNecessary() */
3543 : /************************************************************************/
3544 :
3545 13257 : OGRErr OGRSQLiteTableLayer::RunDeferredCreationIfNecessary()
3546 : {
3547 13257 : if (!m_bDeferredCreation)
3548 12875 : return OGRERR_NONE;
3549 382 : m_bDeferredCreation = false;
3550 :
3551 764 : CPLString osCommand;
3552 :
3553 : osCommand.Printf(
3554 : "CREATE TABLE '%s' ( \"%s\" INTEGER PRIMARY KEY AUTOINCREMENT",
3555 382 : m_pszEscapedTableName, SQLEscapeName(m_pszFIDColumn).c_str());
3556 :
3557 382 : if (!m_poDS->IsSpatialiteDB())
3558 : {
3559 358 : for (int i = 0; i < m_poFeatureDefn->GetGeomFieldCount(); i++)
3560 : {
3561 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3562 123 : m_poFeatureDefn->myGetGeomFieldDefn(i);
3563 :
3564 123 : if (poGeomFieldDefn->m_eGeomFormat == OSGF_WKT)
3565 : {
3566 : osCommand += CPLSPrintf(
3567 : ", '%s' VARCHAR",
3568 1 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
3569 : }
3570 : else
3571 : {
3572 : osCommand += CPLSPrintf(
3573 : ", '%s' BLOB",
3574 122 : SQLEscapeLiteral(poGeomFieldDefn->GetNameRef()).c_str());
3575 : }
3576 123 : if (!poGeomFieldDefn->IsNullable())
3577 : {
3578 1 : osCommand += " NOT NULL";
3579 : }
3580 : }
3581 : }
3582 :
3583 1340 : for (int i = 0; i < m_poFeatureDefn->GetFieldCount(); i++)
3584 : {
3585 958 : OGRFieldDefn *poFieldDefn = m_poFeatureDefn->GetFieldDefn(i);
3586 958 : if (i == m_iFIDAsRegularColumnIndex)
3587 1 : continue;
3588 1914 : CPLString osFieldType(FieldDefnToSQliteFieldDefn(poFieldDefn));
3589 : osCommand += CPLSPrintf(
3590 1914 : ", '%s' %s", SQLEscapeLiteral(poFieldDefn->GetNameRef()).c_str(),
3591 1914 : osFieldType.c_str());
3592 957 : if (!poFieldDefn->IsNullable())
3593 : {
3594 302 : osCommand += " NOT NULL";
3595 : }
3596 957 : if (poFieldDefn->IsUnique())
3597 : {
3598 1 : osCommand += " UNIQUE";
3599 : }
3600 957 : const char *pszDefault = poFieldDefn->GetDefault();
3601 978 : if (pszDefault != nullptr &&
3602 21 : (!poFieldDefn->IsDefaultDriverSpecific() ||
3603 1 : (pszDefault[0] == '(' &&
3604 1 : pszDefault[strlen(pszDefault) - 1] == ')' &&
3605 1 : (STARTS_WITH_CI(pszDefault + 1, "strftime") ||
3606 0 : STARTS_WITH_CI(pszDefault + 1, " strftime")))))
3607 : {
3608 21 : osCommand += " DEFAULT ";
3609 21 : osCommand += poFieldDefn->GetDefault();
3610 : }
3611 : }
3612 382 : osCommand += ")";
3613 382 : if (m_bStrict)
3614 1 : osCommand += " STRICT";
3615 :
3616 : #ifdef DEBUG
3617 382 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
3618 : #endif
3619 :
3620 382 : if (SQLCommand(m_poDS->GetDB(), osCommand) != OGRERR_NONE)
3621 0 : return OGRERR_FAILURE;
3622 :
3623 : /* -------------------------------------------------------------------- */
3624 : /* Eventually we should be adding this table to a table of */
3625 : /* "geometric layers", capturing the WKT projection, and */
3626 : /* perhaps some other housekeeping. */
3627 : /* -------------------------------------------------------------------- */
3628 382 : if (m_poDS->HasGeometryColumns())
3629 : {
3630 : /* Sometimes there is an old cruft entry in the geometry_columns
3631 : * table if things were not properly cleaned up before. We make
3632 : * an effort to clean out such cruft.
3633 : */
3634 : osCommand.Printf(
3635 : "DELETE FROM geometry_columns WHERE f_table_name = '%s'",
3636 374 : m_pszEscapedTableName);
3637 :
3638 : #ifdef DEBUG
3639 374 : CPLDebug("OGR_SQLITE", "exec(%s)", osCommand.c_str());
3640 : #endif
3641 374 : if (SQLCommand(m_poDS->GetDB(), osCommand) != OGRERR_NONE)
3642 0 : return OGRERR_FAILURE;
3643 :
3644 643 : for (int i = 0; i < m_poFeatureDefn->GetGeomFieldCount(); i++)
3645 : {
3646 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3647 269 : m_poFeatureDefn->myGetGeomFieldDefn(i);
3648 269 : if (RunAddGeometryColumn(poGeomFieldDefn, false) != OGRERR_NONE)
3649 0 : return OGRERR_FAILURE;
3650 : }
3651 : }
3652 :
3653 382 : if (RecomputeOrdinals() != OGRERR_NONE)
3654 0 : return OGRERR_FAILURE;
3655 :
3656 382 : if (m_poDS->IsSpatialiteDB() && m_poDS->GetLayerCount() == 1)
3657 : {
3658 : /* To create the layer_statistics and spatialite_history tables */
3659 35 : if (SQLCommand(m_poDS->GetDB(), "SELECT UpdateLayerStatistics()") !=
3660 : OGRERR_NONE)
3661 0 : return OGRERR_FAILURE;
3662 : }
3663 :
3664 382 : return OGRERR_NONE;
3665 : }
3666 :
3667 : /************************************************************************/
3668 : /* HasSpatialIndex() */
3669 : /************************************************************************/
3670 :
3671 157 : bool OGRSQLiteTableLayer::HasSpatialIndex(int iGeomCol)
3672 : {
3673 157 : GetLayerDefn();
3674 157 : if (iGeomCol < 0 || iGeomCol >= m_poFeatureDefn->GetGeomFieldCount())
3675 0 : return false;
3676 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3677 157 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomCol);
3678 :
3679 157 : CreateSpatialIndexIfNecessary();
3680 :
3681 157 : return poGeomFieldDefn->m_bHasSpatialIndex;
3682 : }
3683 :
3684 : /************************************************************************/
3685 : /* InitFeatureCount() */
3686 : /************************************************************************/
3687 :
3688 382 : void OGRSQLiteTableLayer::InitFeatureCount()
3689 : {
3690 382 : m_nFeatureCount = 0;
3691 382 : ForceStatisticsToBeFlushed();
3692 382 : }
3693 :
3694 : /************************************************************************/
3695 : /* InvalidateCachedFeatureCountAndExtent() */
3696 : /************************************************************************/
3697 :
3698 59 : void OGRSQLiteTableLayer::InvalidateCachedFeatureCountAndExtent()
3699 : {
3700 59 : m_nFeatureCount = -1;
3701 92 : for (int iGeomCol = 0; iGeomCol < GetLayerDefn()->GetGeomFieldCount();
3702 : iGeomCol++)
3703 33 : m_poFeatureDefn->myGetGeomFieldDefn(iGeomCol)->m_bCachedExtentIsValid =
3704 : false;
3705 59 : ForceStatisticsToBeFlushed();
3706 59 : }
3707 :
3708 : /************************************************************************/
3709 : /* DoStatisticsNeedToBeFlushed() */
3710 : /************************************************************************/
3711 :
3712 0 : bool OGRSQLiteTableLayer::DoStatisticsNeedToBeFlushed()
3713 : {
3714 0 : return m_bStatisticsNeedsToBeFlushed && m_poDS->IsSpatialiteDB() &&
3715 0 : m_poDS->IsSpatialiteLoaded();
3716 : }
3717 :
3718 : /************************************************************************/
3719 : /* ForceStatisticsToBeFlushed() */
3720 : /************************************************************************/
3721 :
3722 2399 : void OGRSQLiteTableLayer::ForceStatisticsToBeFlushed()
3723 : {
3724 2399 : m_bStatisticsNeedsToBeFlushed = true;
3725 2399 : }
3726 :
3727 : /************************************************************************/
3728 : /* AreStatisticsValid() */
3729 : /************************************************************************/
3730 :
3731 1 : bool OGRSQLiteTableLayer::AreStatisticsValid()
3732 : {
3733 1 : return m_nFeatureCount >= 0;
3734 : }
3735 :
3736 : /************************************************************************/
3737 : /* LoadStatisticsSpatialite4DB() */
3738 : /************************************************************************/
3739 :
3740 223 : void OGRSQLiteTableLayer::LoadStatisticsSpatialite4DB()
3741 : {
3742 389 : for (int iCol = 0; iCol < GetLayerDefn()->GetGeomFieldCount(); iCol++)
3743 : {
3744 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3745 173 : m_poFeatureDefn->myGetGeomFieldDefn(iCol);
3746 173 : const char *pszGeomCol = poGeomFieldDefn->GetNameRef();
3747 :
3748 173 : CPLString osSQL;
3749 173 : CPLString osLastEvtDate;
3750 : osSQL.Printf(
3751 : "SELECT MAX(last_insert, last_update, last_delete) FROM "
3752 : "geometry_columns_time WHERE "
3753 : "(f_table_name = lower('%s') AND f_geometry_column = lower('%s'))"
3754 : #ifdef WORKAROUND_SQLITE3_BUGS
3755 : " OR 0"
3756 : #endif
3757 : ,
3758 173 : m_pszEscapedTableName, SQLEscapeLiteral(pszGeomCol).c_str());
3759 :
3760 173 : sqlite3 *hDB = m_poDS->GetDB();
3761 173 : int nRowCount = 0;
3762 173 : int nColCount = 0;
3763 173 : char **papszResult = nullptr;
3764 :
3765 173 : sqlite3_get_table(hDB, osSQL.c_str(), &papszResult, &nRowCount,
3766 : &nColCount, nullptr);
3767 :
3768 : /* Make it a Unix timestamp */
3769 173 : int nYear = 0;
3770 173 : int nMonth = 0;
3771 173 : int nDay = 0;
3772 173 : char chSep = 0;
3773 173 : int nHour = 0;
3774 173 : int nMinute = 0;
3775 173 : float fSecond = 0.0f;
3776 339 : if (nRowCount == 1 && nColCount == 1 && papszResult[1] != nullptr &&
3777 166 : sscanf(papszResult[1], "%04d-%02d-%02d%c%02d:%02d:%f", &nYear,
3778 : &nMonth, &nDay, &chSep, &nHour, &nMinute, &fSecond) == 7)
3779 : {
3780 166 : osLastEvtDate = papszResult[1];
3781 : }
3782 :
3783 173 : sqlite3_free_table(papszResult);
3784 173 : papszResult = nullptr;
3785 :
3786 173 : if (osLastEvtDate.empty())
3787 7 : return;
3788 :
3789 : osSQL.Printf(
3790 : "SELECT last_verified, row_count, extent_min_x, extent_min_y, "
3791 : "extent_max_x, extent_max_y FROM geometry_columns_statistics WHERE "
3792 : "(f_table_name = lower('%s') AND f_geometry_column = lower('%s'))"
3793 : #ifdef WORKAROUND_SQLITE3_BUGS
3794 : " OR 0"
3795 : #endif
3796 : ,
3797 166 : m_pszEscapedTableName, SQLEscapeLiteral(pszGeomCol).c_str());
3798 :
3799 166 : nRowCount = 0;
3800 166 : nColCount = 0;
3801 166 : sqlite3_get_table(hDB, osSQL.c_str(), &papszResult, &nRowCount,
3802 : &nColCount, nullptr);
3803 :
3804 232 : if (nRowCount == 1 && nColCount == 6 && papszResult[6] != nullptr &&
3805 66 : sscanf(papszResult[6], "%04d-%02d-%02d%c%02d:%02d:%f", &nYear,
3806 : &nMonth, &nDay, &chSep, &nHour, &nMinute, &fSecond) == 7)
3807 : {
3808 132 : CPLString osLastVerified(papszResult[6]);
3809 :
3810 : /* Check that the information in geometry_columns_statistics is more
3811 : */
3812 : /* recent than geometry_columns_time */
3813 66 : if (osLastVerified.compare(osLastEvtDate) > 0)
3814 : {
3815 30 : char **papszRow = papszResult + 6;
3816 30 : const char *pszRowCount = papszRow[1];
3817 30 : const char *pszMinX = papszRow[2];
3818 30 : const char *pszMinY = papszRow[3];
3819 30 : const char *pszMaxX = papszRow[4];
3820 30 : const char *pszMaxY = papszRow[5];
3821 :
3822 30 : CPLDebug("SQLITE", "Loading statistics for %s,%s",
3823 : m_pszTableName, pszGeomCol);
3824 :
3825 30 : if (pszRowCount != nullptr)
3826 : {
3827 30 : m_nFeatureCount = CPLAtoGIntBig(pszRowCount);
3828 30 : if (m_nFeatureCount == 0)
3829 : {
3830 29 : m_nFeatureCount = -1;
3831 29 : pszMinX = nullptr;
3832 : }
3833 : else
3834 : {
3835 1 : CPLDebug("SQLITE",
3836 : "Layer %s feature count : " CPL_FRMT_GIB,
3837 : m_pszTableName, m_nFeatureCount);
3838 : }
3839 : }
3840 :
3841 30 : if (pszMinX != nullptr && pszMinY != nullptr &&
3842 1 : pszMaxX != nullptr && pszMaxY != nullptr)
3843 : {
3844 1 : poGeomFieldDefn->m_bCachedExtentIsValid = true;
3845 1 : poGeomFieldDefn->m_oCachedExtent.MinX = CPLAtof(pszMinX);
3846 1 : poGeomFieldDefn->m_oCachedExtent.MinY = CPLAtof(pszMinY);
3847 1 : poGeomFieldDefn->m_oCachedExtent.MaxX = CPLAtof(pszMaxX);
3848 1 : poGeomFieldDefn->m_oCachedExtent.MaxY = CPLAtof(pszMaxY);
3849 1 : CPLDebug("SQLITE", "Layer %s extent : %s,%s,%s,%s",
3850 : m_pszTableName, pszMinX, pszMinY, pszMaxX,
3851 : pszMaxY);
3852 : }
3853 : }
3854 : else
3855 : {
3856 36 : CPLDebug("SQLite", "Statistics in %s is not up-to-date",
3857 : m_pszTableName);
3858 : }
3859 : }
3860 :
3861 166 : sqlite3_free_table(papszResult);
3862 166 : papszResult = nullptr;
3863 : }
3864 : }
3865 :
3866 : /************************************************************************/
3867 : /* LoadStatistics() */
3868 : /************************************************************************/
3869 :
3870 455 : void OGRSQLiteTableLayer::LoadStatistics()
3871 : {
3872 455 : if (!m_poDS->IsSpatialiteDB() || !m_poDS->IsSpatialiteLoaded())
3873 445 : return;
3874 :
3875 243 : if (m_poDS->HasSpatialite4Layout())
3876 : {
3877 223 : LoadStatisticsSpatialite4DB();
3878 223 : return;
3879 : }
3880 :
3881 20 : if (GetLayerDefn()->GetGeomFieldCount() != 1)
3882 10 : return;
3883 10 : const char *pszGeomCol = m_poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef();
3884 :
3885 10 : GIntBig nFileTimestamp = m_poDS->GetFileTimestamp();
3886 10 : if (nFileTimestamp == 0)
3887 0 : return;
3888 :
3889 : /* Find the most recent event in the 'spatialite_history' that is */
3890 : /* a UpdateLayerStatistics event on all tables and geometry columns */
3891 20 : CPLString osSQL;
3892 : osSQL.Printf("SELECT MAX(timestamp) FROM spatialite_history WHERE "
3893 : "((table_name = '%s' AND geometry_column = '%s') OR "
3894 : "(table_name = 'ALL-TABLES' AND geometry_column = "
3895 : "'ALL-GEOMETRY-COLUMNS')) AND "
3896 : "event = 'UpdateLayerStatistics'",
3897 10 : m_pszEscapedTableName, SQLEscapeLiteral(pszGeomCol).c_str());
3898 :
3899 10 : sqlite3 *hDB = m_poDS->GetDB();
3900 10 : int nRowCount = 0, nColCount = 0;
3901 10 : char **papszResult = nullptr, *pszErrMsg = nullptr;
3902 :
3903 10 : sqlite3_get_table(hDB, osSQL.c_str(), &papszResult, &nRowCount, &nColCount,
3904 : &pszErrMsg);
3905 :
3906 : /* Make it a Unix timestamp */
3907 : int nYear, nMonth, nDay, nHour, nMinute, nSecond;
3908 : struct tm brokendown;
3909 10 : GIntBig nTS = -1;
3910 10 : if (nRowCount >= 1 && nColCount == 1 && papszResult[1] != nullptr &&
3911 0 : sscanf(papszResult[1], "%04d-%02d-%02d %02d:%02d:%02d", &nYear, &nMonth,
3912 : &nDay, &nHour, &nMinute, &nSecond) == 6)
3913 : {
3914 0 : brokendown.tm_year = nYear - 1900;
3915 0 : brokendown.tm_mon = nMonth - 1;
3916 0 : brokendown.tm_mday = nDay;
3917 0 : brokendown.tm_hour = nHour;
3918 0 : brokendown.tm_min = nMinute;
3919 0 : brokendown.tm_sec = nSecond;
3920 0 : nTS = CPLYMDHMSToUnixTime(&brokendown);
3921 : }
3922 :
3923 : /* If it is equal to the modified timestamp of the DB (as a file) */
3924 : /* then we can safely use the data from the layer_statistics, since */
3925 : /* it will be up-to-date */
3926 : // cppcheck-suppress knownConditionTrueFalse
3927 10 : if (nFileTimestamp == nTS || nFileTimestamp == nTS + 1)
3928 : {
3929 : osSQL.Printf("SELECT row_count, extent_min_x, extent_min_y, "
3930 : "extent_max_x, extent_max_y "
3931 : "FROM layer_statistics WHERE table_name = '%s' AND "
3932 : "geometry_column = '%s'",
3933 : m_pszEscapedTableName,
3934 0 : SQLEscapeLiteral(pszGeomCol).c_str());
3935 :
3936 0 : sqlite3_free_table(papszResult);
3937 0 : papszResult = nullptr;
3938 :
3939 0 : sqlite3_get_table(hDB, osSQL.c_str(), &papszResult, &nRowCount,
3940 : &nColCount, &pszErrMsg);
3941 :
3942 0 : if (nRowCount == 1)
3943 : {
3944 0 : char **papszRow = papszResult + 5;
3945 0 : const char *pszRowCount = papszRow[0];
3946 0 : const char *pszMinX = papszRow[1];
3947 0 : const char *pszMinY = papszRow[2];
3948 0 : const char *pszMaxX = papszRow[3];
3949 0 : const char *pszMaxY = papszRow[4];
3950 :
3951 0 : CPLDebug("SQLITE",
3952 : "File timestamp matches layer statistics timestamp. "
3953 : "Loading statistics for %s",
3954 : m_pszTableName);
3955 :
3956 0 : if (pszRowCount != nullptr)
3957 : {
3958 0 : m_nFeatureCount = CPLAtoGIntBig(pszRowCount);
3959 0 : CPLDebug("SQLITE", "Layer %s feature count : " CPL_FRMT_GIB,
3960 : m_pszTableName, m_nFeatureCount);
3961 : }
3962 :
3963 0 : if (pszMinX != nullptr && pszMinY != nullptr &&
3964 0 : pszMaxX != nullptr && pszMaxY != nullptr)
3965 : {
3966 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3967 0 : m_poFeatureDefn->myGetGeomFieldDefn(0);
3968 0 : poGeomFieldDefn->m_bCachedExtentIsValid = true;
3969 0 : poGeomFieldDefn->m_oCachedExtent.MinX = CPLAtof(pszMinX);
3970 0 : poGeomFieldDefn->m_oCachedExtent.MinY = CPLAtof(pszMinY);
3971 0 : poGeomFieldDefn->m_oCachedExtent.MaxX = CPLAtof(pszMaxX);
3972 0 : poGeomFieldDefn->m_oCachedExtent.MaxY = CPLAtof(pszMaxY);
3973 0 : CPLDebug("SQLITE", "Layer %s extent : %s,%s,%s,%s",
3974 : m_pszTableName, pszMinX, pszMinY, pszMaxX, pszMaxY);
3975 : }
3976 : }
3977 : }
3978 :
3979 10 : if (pszErrMsg)
3980 7 : sqlite3_free(pszErrMsg);
3981 :
3982 10 : sqlite3_free_table(papszResult);
3983 : }
3984 :
3985 : /************************************************************************/
3986 : /* SaveStatistics() */
3987 : /************************************************************************/
3988 :
3989 338 : int OGRSQLiteTableLayer::SaveStatistics()
3990 : {
3991 158 : if (!m_bStatisticsNeedsToBeFlushed || !m_poDS->IsSpatialiteDB() ||
3992 496 : !m_poDS->IsSpatialiteLoaded() || !m_poDS->GetUpdate())
3993 180 : return -1;
3994 158 : if (GetLayerDefn()->GetGeomFieldCount() != 1)
3995 6 : return -1;
3996 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
3997 152 : m_poFeatureDefn->myGetGeomFieldDefn(0);
3998 152 : const char *pszGeomCol = poGeomFieldDefn->GetNameRef();
3999 :
4000 304 : CPLString osSQL;
4001 152 : sqlite3 *hDB = m_poDS->GetDB();
4002 152 : char *pszErrMsg = nullptr;
4003 :
4004 : // Update geometry_columns_time.
4005 152 : if (!poGeomFieldDefn->m_aosDisabledTriggers.empty())
4006 : {
4007 120 : char *pszSQL3 = sqlite3_mprintf(
4008 : "UPDATE geometry_columns_time "
4009 : "SET last_insert = strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ', 'now') "
4010 : "WHERE Lower(f_table_name) = Lower('%q') AND "
4011 : "Lower(f_geometry_column) = Lower('%q')",
4012 : m_pszTableName, poGeomFieldDefn->GetNameRef());
4013 120 : if (sqlite3_exec(m_poDS->GetDB(), pszSQL3, nullptr, nullptr,
4014 120 : &pszErrMsg) != SQLITE_OK)
4015 : {
4016 0 : CPLDebug("SQLITE", "%s: error %s", pszSQL3,
4017 0 : pszErrMsg ? pszErrMsg : "unknown");
4018 0 : sqlite3_free(pszErrMsg);
4019 0 : pszErrMsg = nullptr;
4020 : }
4021 120 : sqlite3_free(pszSQL3);
4022 : }
4023 :
4024 152 : const char *pszStatTableName = m_poDS->HasSpatialite4Layout()
4025 152 : ? "geometry_columns_statistics"
4026 152 : : "layer_statistics";
4027 152 : if (SQLGetInteger(m_poDS->GetDB(),
4028 : CPLSPrintf("SELECT 1 FROM sqlite_master WHERE type IN "
4029 : "('view', 'table') AND name = '%s'",
4030 : pszStatTableName),
4031 152 : nullptr) == 0)
4032 : {
4033 1 : return TRUE;
4034 : }
4035 : const char *pszFTableName =
4036 151 : m_poDS->HasSpatialite4Layout() ? "f_table_name" : "table_name";
4037 151 : const char *pszFGeometryColumn = m_poDS->HasSpatialite4Layout()
4038 151 : ? "f_geometry_column"
4039 151 : : "geometry_column";
4040 302 : CPLString osTableName(m_pszTableName);
4041 302 : CPLString osGeomCol(pszGeomCol);
4042 151 : const char *pszNowValue = "";
4043 151 : if (m_poDS->HasSpatialite4Layout())
4044 : {
4045 151 : osTableName = osTableName.tolower();
4046 151 : osGeomCol = osGeomCol.tolower();
4047 151 : pszNowValue = ", strftime('%Y-%m-%dT%H:%M:%fZ','now')";
4048 : }
4049 :
4050 151 : if (m_nFeatureCount >= 0)
4051 : {
4052 : /* Update or add entry in the layer_statistics table */
4053 126 : if (poGeomFieldDefn->m_bCachedExtentIsValid)
4054 : {
4055 : osSQL.Printf("INSERT OR REPLACE INTO %s (%s"
4056 : "%s, %s, row_count, extent_min_x, "
4057 : "extent_min_y, extent_max_x, extent_max_y%s) VALUES ("
4058 : "%s'%s', '%s', " CPL_FRMT_GIB ", ?, ?, ?, ?%s)",
4059 : pszStatTableName,
4060 106 : m_poDS->HasSpatialite4Layout() ? "" : "raster_layer, ",
4061 : pszFTableName, pszFGeometryColumn,
4062 106 : m_poDS->HasSpatialite4Layout() ? ", last_verified"
4063 : : "",
4064 106 : m_poDS->HasSpatialite4Layout() ? "" : "0 ,",
4065 212 : SQLEscapeLiteral(osTableName).c_str(),
4066 212 : SQLEscapeLiteral(osGeomCol).c_str(), m_nFeatureCount,
4067 636 : pszNowValue);
4068 :
4069 106 : sqlite3_stmt *m_hStmtInsert = nullptr;
4070 : int rc =
4071 106 : sqlite3_prepare_v2(hDB, osSQL, -1, &m_hStmtInsert, nullptr);
4072 106 : if (rc == SQLITE_OK)
4073 106 : rc = sqlite3_bind_double(m_hStmtInsert, 1,
4074 : poGeomFieldDefn->m_oCachedExtent.MinX);
4075 106 : if (rc == SQLITE_OK)
4076 106 : rc = sqlite3_bind_double(m_hStmtInsert, 2,
4077 : poGeomFieldDefn->m_oCachedExtent.MinY);
4078 106 : if (rc == SQLITE_OK)
4079 106 : rc = sqlite3_bind_double(m_hStmtInsert, 3,
4080 : poGeomFieldDefn->m_oCachedExtent.MaxX);
4081 106 : if (rc == SQLITE_OK)
4082 106 : rc = sqlite3_bind_double(m_hStmtInsert, 4,
4083 : poGeomFieldDefn->m_oCachedExtent.MaxY);
4084 106 : if (rc == SQLITE_OK)
4085 106 : rc = sqlite3_step(m_hStmtInsert);
4086 106 : if (rc != SQLITE_DONE)
4087 : {
4088 0 : CPLError(CE_Failure, CPLE_AppDefined,
4089 : "In Initialize(): sqlite3_step(%s):\n %s",
4090 : osSQL.c_str(), sqlite3_errmsg(hDB));
4091 : }
4092 106 : sqlite3_finalize(m_hStmtInsert);
4093 106 : return rc == SQLITE_DONE;
4094 : }
4095 : else
4096 : {
4097 : osSQL.Printf(
4098 : "INSERT OR REPLACE INTO %s (%s"
4099 : "%s, %s, row_count, extent_min_x, "
4100 : "extent_min_y, extent_max_x, extent_max_y%s) VALUES ("
4101 : "%s'%s', '%s', " CPL_FRMT_GIB ", NULL, NULL, NULL, NULL%s)",
4102 : pszStatTableName,
4103 20 : m_poDS->HasSpatialite4Layout() ? "" : "raster_layer, ",
4104 : pszFTableName, pszFGeometryColumn,
4105 20 : m_poDS->HasSpatialite4Layout() ? ", last_verified" : "",
4106 20 : m_poDS->HasSpatialite4Layout() ? "" : "0 ,",
4107 40 : SQLEscapeLiteral(osTableName).c_str(),
4108 40 : SQLEscapeLiteral(osGeomCol).c_str(), m_nFeatureCount,
4109 120 : pszNowValue);
4110 20 : return SQLCommand(hDB, osSQL) == OGRERR_NONE;
4111 : }
4112 : }
4113 : else
4114 : {
4115 : /* Remove any existing entry in layer_statistics if for some reason */
4116 : /* we know that it will out-of-sync */
4117 : osSQL.Printf("DELETE FROM %s WHERE "
4118 : "%s = '%s' AND %s = '%s'",
4119 : pszStatTableName, pszFTableName,
4120 50 : SQLEscapeLiteral(osTableName).c_str(), pszFGeometryColumn,
4121 75 : SQLEscapeLiteral(osGeomCol).c_str());
4122 25 : return SQLCommand(hDB, osSQL) == OGRERR_NONE;
4123 : }
4124 : }
4125 :
4126 : /************************************************************************/
4127 : /* SetCompressedColumns() */
4128 : /************************************************************************/
4129 :
4130 382 : void OGRSQLiteTableLayer::SetCompressedColumns(const char *pszCompressedColumns)
4131 : {
4132 382 : m_papszCompressedColumns =
4133 382 : CSLTokenizeString2(pszCompressedColumns, ",", CSLT_HONOURSTRINGS);
4134 382 : }
|
