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