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