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