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