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