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