1 : /******************************************************************************
2 : * $Id: ogrsqlitetablelayer.cpp 25394 2012-12-29 22:01:36Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRSQLiteTableLayer class, access to an existing table.
6 : * Author: Frank Warmerdam, warmerdam@pobox.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2004, Frank Warmerdam
10 : *
11 : * Permission is hereby granted, free of charge, to any person obtaining a
12 : * copy of this software and associated documentation files (the "Software"),
13 : * to deal in the Software without restriction, including without limitation
14 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 : * and/or sell copies of the Software, and to permit persons to whom the
16 : * Software is furnished to do so, subject to the following conditions:
17 : *
18 : * The above copyright notice and this permission notice shall be included
19 : * in all copies or substantial portions of the Software.
20 : *
21 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 : * DEALINGS IN THE SOFTWARE.
28 : ****************************************************************************/
29 :
30 : #include "cpl_conv.h"
31 : #include "cpl_string.h"
32 : #include "ogr_sqlite.h"
33 : #include "ogr_p.h"
34 : #include "cpl_time.h"
35 : #include <string>
36 :
37 : #define UNSUPPORTED_OP_READ_ONLY "%s : unsupported operation on a read-only datasource."
38 :
39 : CPL_CVSID("$Id: ogrsqlitetablelayer.cpp 25394 2012-12-29 22:01:36Z rouault $");
40 :
41 : /************************************************************************/
42 : /* OGRSQLiteTableLayer() */
43 : /************************************************************************/
44 :
45 737 : OGRSQLiteTableLayer::OGRSQLiteTableLayer( OGRSQLiteDataSource *poDSIn )
46 :
47 : {
48 737 : poDS = poDSIn;
49 :
50 737 : bLaunderColumnNames = TRUE;
51 :
52 : /* SpatiaLite v.2.4.0 (or any subsequent) is required
53 : to support 2.5D: if an obsolete version of the library
54 : is found we'll unconditionally activate 2D casting mode.
55 : */
56 737 : bSpatialite2D = OGRSQLiteGetSpatialiteVersionNumber() < 24;
57 :
58 737 : iNextShapeId = 0;
59 :
60 737 : poFeatureDefn = NULL;
61 737 : pszTableName = NULL;
62 737 : pszEscapedTableName = NULL;
63 :
64 737 : bHasCheckedSpatialIndexTable = FALSE;
65 737 : bDeferedSpatialIndexCreation = FALSE;
66 :
67 737 : hInsertStmt = NULL;
68 :
69 737 : eGeomType = wkbUnknown;
70 737 : bLayerDefnError = FALSE;
71 :
72 737 : bStatisticsNeedsToBeFlushed = FALSE;
73 737 : bCachedExtentIsValid = FALSE;
74 737 : nFeatureCount = -1;
75 737 : }
76 :
77 : /************************************************************************/
78 : /* ~OGRSQLiteTableLayer() */
79 : /************************************************************************/
80 :
81 737 : OGRSQLiteTableLayer::~OGRSQLiteTableLayer()
82 :
83 : {
84 737 : ClearStatement();
85 737 : ClearInsertStmt();
86 :
87 737 : CPLFree(pszTableName);
88 737 : CPLFree(pszEscapedTableName);
89 737 : }
90 :
91 : /************************************************************************/
92 : /* CreateSpatialIndexIfNecessary() */
93 : /************************************************************************/
94 :
95 631 : void OGRSQLiteTableLayer::CreateSpatialIndexIfNecessary()
96 : {
97 631 : if( bDeferedSpatialIndexCreation )
98 : {
99 70 : CreateSpatialIndex();
100 : }
101 631 : }
102 :
103 : /************************************************************************/
104 : /* ClearInsertStmt() */
105 : /************************************************************************/
106 :
107 1078 : void OGRSQLiteTableLayer::ClearInsertStmt()
108 : {
109 1078 : if( hInsertStmt != NULL )
110 : {
111 241 : sqlite3_finalize( hInsertStmt );
112 241 : hInsertStmt = NULL;
113 : }
114 1078 : osLastInsertStmt = "";
115 1078 : }
116 :
117 : /************************************************************************/
118 : /* Initialize() */
119 : /************************************************************************/
120 :
121 737 : CPLErr OGRSQLiteTableLayer::Initialize( const char *pszTableName,
122 : const char *pszGeomCol,
123 : int bMustIncludeGeomColName,
124 : OGRwkbGeometryType eGeomType,
125 : const char *pszGeomFormat,
126 : OGRSpatialReference *poSRS,
127 : int nSRSId,
128 : int bHasSpatialIndex,
129 : int bHasM,
130 : int bIsVirtualShapeIn )
131 :
132 : {
133 : int rc;
134 737 : sqlite3 *hDB = poDS->GetDB();
135 :
136 737 : if( pszGeomCol == NULL )
137 236 : osGeomColumn = "";
138 : else
139 501 : osGeomColumn = pszGeomCol;
140 :
141 737 : if( pszGeomFormat )
142 : {
143 509 : if ( EQUAL(pszGeomFormat, "WKT") )
144 18 : eGeomFormat = OSGF_WKT;
145 491 : else if ( EQUAL(pszGeomFormat,"WKB") )
146 140 : eGeomFormat = OSGF_WKB;
147 351 : else if ( EQUAL(pszGeomFormat,"FGF") )
148 2 : eGeomFormat = OSGF_FGF;
149 349 : else if( EQUAL(pszGeomFormat,"SpatiaLite") )
150 349 : eGeomFormat = OSGF_SpatiaLite;
151 : }
152 :
153 737 : CPLFree( pszFIDColumn );
154 737 : pszFIDColumn = NULL;
155 :
156 737 : if( nSRSId == UNINITIALIZED_SRID )
157 384 : nSRSId = poDS->GetUndefinedSRID();
158 :
159 737 : this->poSRS = poSRS;
160 737 : this->nSRSId = nSRSId;
161 737 : this->bHasSpatialIndex = bHasSpatialIndex;
162 737 : this->bHasM = bHasM;
163 737 : this->bIsVirtualShape = bIsVirtualShapeIn;
164 737 : this->pszTableName = CPLStrdup(pszTableName);
165 737 : this->eGeomType = eGeomType;
166 :
167 737 : if( bMustIncludeGeomColName )
168 6 : osLayerName.Printf("%s(%s)", pszTableName, osGeomColumn.c_str());
169 : else
170 731 : osLayerName = pszTableName;
171 :
172 737 : pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszTableName));
173 :
174 737 : sqlite3_stmt *hColStmt = NULL;
175 : const char *pszSQL;
176 :
177 1086 : if ( eGeomFormat == OSGF_SpatiaLite &&
178 : OGRSQLiteIsSpatialiteLoaded() &&
179 : OGRSQLiteGetSpatialiteVersionNumber() < 24 && poDS->GetUpdate() )
180 : {
181 : // we need to test version required by Spatialite TRIGGERs
182 0 : hColStmt = NULL;
183 : pszSQL = CPLSPrintf( "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND tbl_name = '%s' AND sql LIKE '%%RTreeAlign%%'",
184 0 : pszEscapedTableName );
185 :
186 : int nRowTriggerCount, nColTriggerCount;
187 : char **papszTriggerResult, *pszErrMsg;
188 :
189 : rc = sqlite3_get_table( hDB, pszSQL, &papszTriggerResult,
190 0 : &nRowTriggerCount, &nColTriggerCount, &pszErrMsg );
191 0 : if( nRowTriggerCount >= 1 )
192 : {
193 : // obsolete library version not supporting new triggers
194 : // enforcing ReadOnly mode
195 0 : CPLDebug("SQLITE", "Enforcing ReadOnly mode : obsolete library version not supporting new triggers");
196 0 : poDS->SetUpdate(FALSE);
197 : }
198 :
199 0 : sqlite3_free_table( papszTriggerResult );
200 : }
201 :
202 737 : if( poSRS )
203 283 : poSRS->Reference();
204 :
205 737 : LoadStatistics();
206 :
207 737 : return CE_None;
208 : }
209 :
210 : /************************************************************************/
211 : /* GetName() */
212 : /************************************************************************/
213 :
214 823 : const char* OGRSQLiteTableLayer::GetName()
215 : {
216 823 : return osLayerName.c_str();
217 : }
218 :
219 : /************************************************************************/
220 : /* EstablishFeatureDefn() */
221 : /************************************************************************/
222 :
223 516 : CPLErr OGRSQLiteTableLayer::EstablishFeatureDefn()
224 : {
225 516 : sqlite3 *hDB = poDS->GetDB();
226 : int rc;
227 : const char *pszSQL;
228 516 : sqlite3_stmt *hColStmt = NULL;
229 :
230 : /* -------------------------------------------------------------------- */
231 : /* Get the column definitions for this table. */
232 : /* -------------------------------------------------------------------- */
233 :
234 : pszSQL = CPLSPrintf( "SELECT _rowid_, * FROM '%s' LIMIT 1",
235 516 : pszEscapedTableName );
236 :
237 516 : rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
238 516 : if( rc != SQLITE_OK )
239 : {
240 : CPLError( CE_Failure, CPLE_AppDefined,
241 : "Unable to query table %s for column definitions : %s.",
242 85 : pszTableName, sqlite3_errmsg(hDB) );
243 :
244 85 : return CE_Failure;
245 : }
246 :
247 431 : rc = sqlite3_step( hColStmt );
248 431 : if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
249 : {
250 : CPLError( CE_Failure, CPLE_AppDefined,
251 : "In Initialize(): sqlite3_step(%s):\n %s",
252 0 : pszSQL, sqlite3_errmsg(hDB) );
253 0 : sqlite3_finalize( hColStmt );
254 0 : return CE_Failure;
255 : }
256 :
257 : /* -------------------------------------------------------------------- */
258 : /* What should we use as FID? If there is a primary key */
259 : /* integer field, then this will be used as the _rowid_, and we */
260 : /* will pick up the real column name here. Otherwise, we will */
261 : /* just use fid. */
262 : /* */
263 : /* Note that the select _rowid_ will return the real column */
264 : /* name if the rowid corresponds to another primary key */
265 : /* column. */
266 : /* -------------------------------------------------------------------- */
267 431 : pszFIDColumn = CPLStrdup(OGRSQLiteParamsUnquote(sqlite3_column_name( hColStmt, 0 )));
268 :
269 : /* -------------------------------------------------------------------- */
270 : /* Collect the rest of the fields. */
271 : /* -------------------------------------------------------------------- */
272 431 : BuildFeatureDefn( osLayerName, hColStmt, poDS->GetGeomColsForTable(pszTableName) );
273 431 : sqlite3_finalize( hColStmt );
274 :
275 : /* -------------------------------------------------------------------- */
276 : /* Set the geometry type if we know it. */
277 : /* -------------------------------------------------------------------- */
278 431 : if( eGeomType != wkbUnknown )
279 206 : poFeatureDefn->SetGeomType( eGeomType );
280 :
281 431 : return CE_None;
282 : }
283 :
284 : /************************************************************************/
285 : /* GetLayerDefn() */
286 : /************************************************************************/
287 :
288 4039 : OGRFeatureDefn* OGRSQLiteTableLayer::GetLayerDefn()
289 : {
290 4039 : if (poFeatureDefn)
291 3523 : return poFeatureDefn;
292 :
293 516 : EstablishFeatureDefn();
294 :
295 516 : if (poFeatureDefn == NULL)
296 : {
297 85 : bLayerDefnError = TRUE;
298 :
299 85 : poFeatureDefn = new OGRFeatureDefn( osLayerName );
300 85 : poFeatureDefn->Reference();
301 : }
302 :
303 516 : return poFeatureDefn;
304 : }
305 :
306 : /************************************************************************/
307 : /* ResetStatement() */
308 : /************************************************************************/
309 :
310 355 : OGRErr OGRSQLiteTableLayer::ResetStatement()
311 :
312 : {
313 : int rc;
314 355 : CPLString osSQL;
315 :
316 355 : ClearStatement();
317 :
318 355 : iNextShapeId = 0;
319 :
320 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' %s",
321 : pszEscapedTableName,
322 355 : osWHERE.c_str() );
323 :
324 :
325 : //#ifdef HAVE_SQLITE3_PREPARE_V2
326 : // rc = sqlite3_prepare_v2( poDS->GetDB(), osSQL, osSQL.size(),
327 : // &hStmt, NULL );
328 : //#else
329 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
330 355 : &hStmt, NULL );
331 : //#endif
332 :
333 355 : if( rc == SQLITE_OK )
334 : {
335 353 : return OGRERR_NONE;
336 : }
337 : else
338 : {
339 : CPLError( CE_Failure, CPLE_AppDefined,
340 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
341 2 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
342 2 : hStmt = NULL;
343 2 : return OGRERR_FAILURE;
344 0 : }
345 : }
346 :
347 : /************************************************************************/
348 : /* GetNextFeature() */
349 : /************************************************************************/
350 :
351 1177 : OGRFeature *OGRSQLiteTableLayer::GetNextFeature()
352 :
353 : {
354 1177 : if (HasLayerDefnError())
355 1 : return NULL;
356 :
357 1176 : return OGRSQLiteLayer::GetNextFeature();
358 : }
359 :
360 : /************************************************************************/
361 : /* GetFeature() */
362 : /************************************************************************/
363 :
364 38 : OGRFeature *OGRSQLiteTableLayer::GetFeature( long nFeatureId )
365 :
366 : {
367 38 : if (HasLayerDefnError())
368 0 : return NULL;
369 :
370 : /* -------------------------------------------------------------------- */
371 : /* If we don't have an explicit FID column, just read through */
372 : /* the result set iteratively to find our target. */
373 : /* -------------------------------------------------------------------- */
374 38 : if( pszFIDColumn == NULL )
375 0 : return OGRSQLiteLayer::GetFeature( nFeatureId );
376 :
377 : /* -------------------------------------------------------------------- */
378 : /* Setup explicit query statement to fetch the record we want. */
379 : /* -------------------------------------------------------------------- */
380 38 : CPLString osSQL;
381 : int rc;
382 :
383 38 : ClearStatement();
384 :
385 38 : iNextShapeId = nFeatureId;
386 :
387 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' WHERE \"%s\" = %ld",
388 : pszEscapedTableName,
389 38 : OGRSQLiteEscape(pszFIDColumn).c_str(), nFeatureId );
390 :
391 38 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
392 :
393 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
394 38 : &hStmt, NULL );
395 38 : if( rc != SQLITE_OK )
396 : {
397 : CPLError( CE_Failure, CPLE_AppDefined,
398 : "In GetFeature(): sqlite3_prepare(%s):\n %s",
399 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
400 :
401 0 : return NULL;
402 : }
403 : /* -------------------------------------------------------------------- */
404 : /* Get the feature if possible. */
405 : /* -------------------------------------------------------------------- */
406 38 : OGRFeature *poFeature = NULL;
407 :
408 38 : poFeature = GetNextRawFeature();
409 :
410 38 : ResetReading();
411 :
412 38 : return poFeature;
413 : }
414 :
415 : /************************************************************************/
416 : /* SetAttributeFilter() */
417 : /************************************************************************/
418 :
419 137 : OGRErr OGRSQLiteTableLayer::SetAttributeFilter( const char *pszQuery )
420 :
421 : {
422 137 : if( pszQuery == NULL )
423 43 : osQuery = "";
424 : else
425 94 : osQuery = pszQuery;
426 :
427 137 : BuildWhere();
428 :
429 137 : ResetReading();
430 :
431 137 : return OGRERR_NONE;
432 : }
433 :
434 :
435 : /************************************************************************/
436 : /* SetSpatialFilter() */
437 : /************************************************************************/
438 :
439 68 : void OGRSQLiteTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
440 :
441 : {
442 68 : if( InstallFilter( poGeomIn ) )
443 : {
444 29 : BuildWhere();
445 :
446 29 : ResetReading();
447 : }
448 68 : }
449 :
450 : /************************************************************************/
451 : /* CheckSpatialIndexTable() */
452 : /************************************************************************/
453 :
454 42 : int OGRSQLiteTableLayer::CheckSpatialIndexTable()
455 : {
456 42 : if (HasSpatialIndex() && !bHasCheckedSpatialIndexTable)
457 : {
458 13 : bHasCheckedSpatialIndexTable = TRUE;
459 : char **papszResult;
460 : int nRowCount, nColCount;
461 13 : char *pszErrMsg = NULL;
462 :
463 13 : CPLString osSQL;
464 :
465 : /* This will ensure that RTree support is available */
466 : osSQL.Printf("SELECT pkid FROM 'idx_%s_%s' WHERE xmax > 0 AND xmin < 0 AND ymax > 0 AND ymin < 0",
467 13 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
468 :
469 : int rc = sqlite3_get_table( poDS->GetDB(), osSQL.c_str(),
470 : &papszResult, &nRowCount,
471 13 : &nColCount, &pszErrMsg );
472 :
473 13 : if( rc != SQLITE_OK )
474 : {
475 : CPLDebug("SQLITE", "Count not find or use idx_%s_%s layer (%s). Disabling spatial index",
476 0 : pszEscapedTableName, osGeomColumn.c_str(), pszErrMsg);
477 0 : sqlite3_free( pszErrMsg );
478 0 : bHasSpatialIndex = FALSE;
479 : }
480 : else
481 : {
482 13 : sqlite3_free_table(papszResult);
483 13 : }
484 : }
485 :
486 42 : return bHasSpatialIndex;
487 : }
488 :
489 : /************************************************************************/
490 : /* GetSpatialWhere() */
491 : /************************************************************************/
492 :
493 183 : CPLString OGRSQLiteTableLayer::GetSpatialWhere(OGRGeometry* poFilterGeom)
494 : {
495 183 : CPLString osSpatialWHERE;
496 :
497 183 : if( !poDS->IsSpatialiteDB() )
498 135 : return osSpatialWHERE;
499 :
500 48 : if( poFilterGeom != NULL && CheckSpatialIndexTable() )
501 : {
502 22 : OGREnvelope sEnvelope;
503 :
504 22 : CPLLocaleC oLocaleEnforcer;
505 :
506 22 : poFilterGeom->getEnvelope( &sEnvelope );
507 :
508 : osSpatialWHERE.Printf("ROWID IN ( SELECT pkid FROM 'idx_%s_%s' WHERE "
509 : "xmax >= %.12f AND xmin <= %.12f AND ymax >= %.12f AND ymin <= %.12f)",
510 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
511 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
512 22 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
513 : }
514 :
515 48 : if( poFilterGeom != NULL &&
516 : OGRSQLiteIsSpatialiteLoaded() && !bHasSpatialIndex )
517 : {
518 2 : OGREnvelope sEnvelope;
519 :
520 2 : CPLLocaleC oLocaleEnforcer;
521 :
522 2 : poFilterGeom->getEnvelope( &sEnvelope );
523 :
524 : /* A bit inefficient but still faster than OGR filtering */
525 : osSpatialWHERE.Printf("MBRIntersects(\"%s\", BuildMBR(%.12f, %.12f, %.12f, %.12f, %d))",
526 : OGRSQLiteEscapeName(osGeomColumn).c_str(),
527 : sEnvelope.MinX - 1e-11, sEnvelope.MinY - 1e-11,
528 : sEnvelope.MaxX + 1e-11, sEnvelope.MaxY + 1e-11,
529 2 : nSRSId);
530 : }
531 :
532 48 : return osSpatialWHERE;
533 : }
534 :
535 : /************************************************************************/
536 : /* BuildWhere() */
537 : /* */
538 : /* Build the WHERE statement appropriate to the current set of */
539 : /* criteria (spatial and attribute queries). */
540 : /************************************************************************/
541 :
542 166 : void OGRSQLiteTableLayer::BuildWhere()
543 :
544 : {
545 166 : osWHERE = "";
546 :
547 166 : CPLString osSpatialWHERE = GetSpatialWhere(m_poFilterGeom);
548 332 : if (osSpatialWHERE.size() != 0)
549 : {
550 11 : osWHERE = "WHERE ";
551 11 : osWHERE += osSpatialWHERE;
552 : }
553 :
554 166 : if( osQuery.size() > 0 )
555 : {
556 90 : if( osWHERE.size() == 0 )
557 : {
558 90 : osWHERE = "WHERE ";
559 90 : osWHERE += osQuery;
560 : }
561 : else
562 : {
563 0 : osWHERE += " AND (";
564 0 : osWHERE += osQuery;
565 0 : osWHERE += ")";
566 : }
567 166 : }
568 166 : }
569 :
570 : /************************************************************************/
571 : /* TestCapability() */
572 : /************************************************************************/
573 :
574 176 : int OGRSQLiteTableLayer::TestCapability( const char * pszCap )
575 :
576 : {
577 176 : if (EQUAL(pszCap,OLCFastFeatureCount))
578 : return m_poFilterGeom == NULL || osGeomColumn.size() == 0 ||
579 101 : HasSpatialIndex();
580 :
581 75 : else if (EQUAL(pszCap,OLCFastSpatialFilter))
582 2 : return HasSpatialIndex();
583 :
584 73 : else if( EQUAL(pszCap,OLCFastGetExtent) )
585 0 : return bCachedExtentIsValid;
586 :
587 73 : else if( EQUAL(pszCap,OLCRandomRead) )
588 14 : return pszFIDColumn != NULL;
589 :
590 59 : else if( EQUAL(pszCap,OLCSequentialWrite)
591 : || EQUAL(pszCap,OLCRandomWrite) )
592 : {
593 12 : return poDS->GetUpdate();
594 : }
595 :
596 47 : else if( EQUAL(pszCap,OLCDeleteFeature) )
597 : {
598 10 : return poDS->GetUpdate() && pszFIDColumn != NULL;
599 : }
600 :
601 37 : else if( EQUAL(pszCap,OLCCreateField) )
602 0 : return poDS->GetUpdate();
603 :
604 37 : else if( EQUAL(pszCap,OLCDeleteField) )
605 1 : return poDS->GetUpdate();
606 :
607 36 : else if( EQUAL(pszCap,OLCAlterFieldDefn) )
608 1 : return poDS->GetUpdate();
609 :
610 35 : else if( EQUAL(pszCap,OLCReorderFields) )
611 1 : return poDS->GetUpdate();
612 :
613 : else
614 34 : return OGRSQLiteLayer::TestCapability( pszCap );
615 : }
616 :
617 : /************************************************************************/
618 : /* GetFeatureCount() */
619 : /************************************************************************/
620 :
621 99 : int OGRSQLiteTableLayer::GetFeatureCount( int bForce )
622 :
623 : {
624 99 : if (HasLayerDefnError())
625 0 : return 0;
626 :
627 99 : if( !TestCapability(OLCFastFeatureCount) )
628 9 : return OGRSQLiteLayer::GetFeatureCount( bForce );
629 :
630 90 : if (nFeatureCount >= 0 && m_poFilterGeom == NULL &&
631 : osQuery.size() == 0 )
632 : {
633 49 : return nFeatureCount;
634 : }
635 :
636 : /* -------------------------------------------------------------------- */
637 : /* Form count SQL. */
638 : /* -------------------------------------------------------------------- */
639 : const char *pszSQL;
640 :
641 41 : if (m_poFilterGeom != NULL && CheckSpatialIndexTable() &&
642 : strlen(osQuery) == 0)
643 : {
644 9 : OGREnvelope sEnvelope;
645 :
646 9 : m_poFilterGeom->getEnvelope( &sEnvelope );
647 : pszSQL = CPLSPrintf("SELECT count(*) FROM 'idx_%s_%s' WHERE "
648 : "xmax >= %.12f AND xmin <= %.12f AND ymax >= %.12f AND ymin <= %.12f",
649 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
650 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
651 9 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
652 : }
653 : else
654 : {
655 : pszSQL = CPLSPrintf( "SELECT count(*) FROM '%s' %s",
656 32 : pszEscapedTableName, osWHERE.c_str() );
657 : }
658 :
659 41 : CPLDebug("SQLITE", "Running %s", pszSQL);
660 :
661 : /* -------------------------------------------------------------------- */
662 : /* Execute. */
663 : /* -------------------------------------------------------------------- */
664 : char **papszResult, *pszErrMsg;
665 : int nRowCount, nColCount;
666 41 : int nResult = -1;
667 :
668 41 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
669 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
670 0 : return -1;
671 :
672 41 : if( nRowCount == 1 && nColCount == 1 )
673 : {
674 41 : nResult = atoi(papszResult[1]);
675 :
676 41 : if( m_poFilterGeom == NULL && osQuery.size() == 0 )
677 : {
678 18 : nFeatureCount = nResult;
679 18 : bStatisticsNeedsToBeFlushed = TRUE;
680 : }
681 : }
682 :
683 41 : sqlite3_free_table( papszResult );
684 :
685 41 : return nResult;
686 : }
687 :
688 : /************************************************************************/
689 : /* GetExtent() */
690 : /************************************************************************/
691 :
692 36 : OGRErr OGRSQLiteTableLayer::GetExtent(OGREnvelope *psExtent, int bForce)
693 : {
694 36 : if (HasLayerDefnError())
695 0 : return OGRERR_FAILURE;
696 :
697 36 : if (GetGeomType() == wkbNone)
698 0 : return OGRERR_FAILURE;
699 :
700 36 : if (bCachedExtentIsValid)
701 : {
702 27 : memcpy(psExtent, &oCachedExtent, sizeof(oCachedExtent));
703 27 : return OGRERR_NONE;
704 : }
705 :
706 9 : if (CheckSpatialIndexTable() &&
707 : !CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_EXACT_EXTENT", "NO")))
708 : {
709 : const char* pszSQL;
710 :
711 : pszSQL = CPLSPrintf("SELECT MIN(xmin), MIN(ymin), MAX(xmax), MAX(ymax) FROM 'idx_%s_%s'",
712 2 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
713 :
714 2 : CPLDebug("SQLITE", "Running %s", pszSQL);
715 :
716 : /* -------------------------------------------------------------------- */
717 : /* Execute. */
718 : /* -------------------------------------------------------------------- */
719 : char **papszResult, *pszErrMsg;
720 : int nRowCount, nColCount;
721 :
722 2 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
723 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
724 0 : return OGRSQLiteLayer::GetExtent(psExtent, bForce);
725 :
726 2 : OGRErr eErr = OGRERR_FAILURE;
727 :
728 10 : if( nRowCount == 1 && nColCount == 4 &&
729 2 : papszResult[4+0] != NULL &&
730 2 : papszResult[4+1] != NULL &&
731 2 : papszResult[4+2] != NULL &&
732 2 : papszResult[4+3] != NULL)
733 : {
734 2 : psExtent->MinX = atof(papszResult[4+0]);
735 2 : psExtent->MinY = atof(papszResult[4+1]);
736 2 : psExtent->MaxX = atof(papszResult[4+2]);
737 2 : psExtent->MaxY = atof(papszResult[4+3]);
738 2 : eErr = OGRERR_NONE;
739 :
740 2 : if( m_poFilterGeom == NULL && osQuery.size() == 0 )
741 : {
742 2 : bCachedExtentIsValid = TRUE;
743 2 : bStatisticsNeedsToBeFlushed = TRUE;
744 2 : memcpy(&oCachedExtent, psExtent, sizeof(oCachedExtent));
745 : }
746 : }
747 :
748 2 : sqlite3_free_table( papszResult );
749 :
750 2 : if (eErr == OGRERR_NONE)
751 2 : return eErr;
752 : }
753 :
754 7 : OGRErr eErr = OGRSQLiteLayer::GetExtent(psExtent, bForce);
755 7 : if( eErr == OGRERR_NONE && m_poFilterGeom == NULL && osQuery.size() == 0 )
756 : {
757 7 : bCachedExtentIsValid = TRUE;
758 7 : bStatisticsNeedsToBeFlushed = TRUE;
759 7 : memcpy(&oCachedExtent, psExtent, sizeof(oCachedExtent));
760 : }
761 7 : return eErr;
762 : }
763 :
764 : /************************************************************************/
765 : /* OGRSQLiteFieldDefnToSQliteFieldDefn() */
766 : /************************************************************************/
767 :
768 1967 : CPLString OGRSQLiteFieldDefnToSQliteFieldDefn( OGRFieldDefn* poFieldDefn )
769 : {
770 1967 : switch( poFieldDefn->GetType() )
771 : {
772 409 : case OFTInteger: return "INTEGER"; break;
773 257 : case OFTReal : return "FLOAT"; break;
774 196 : case OFTBinary : return "BLOB"; break;
775 : case OFTString :
776 : {
777 541 : if( poFieldDefn->GetWidth() > 0 )
778 118 : return CPLSPrintf("VARCHAR(%d)", poFieldDefn->GetWidth());
779 : else
780 423 : return "VARCHAR";
781 : break;
782 : }
783 188 : case OFTDateTime: return "TIMESTAMP"; break;
784 188 : case OFTDate : return "DATE"; break;
785 188 : case OFTTime : return "TIME"; break;
786 0 : default : return "VARCHAR"; break;
787 : }
788 : }
789 :
790 : /************************************************************************/
791 : /* FieldDefnToSQliteFieldDefn() */
792 : /************************************************************************/
793 :
794 172 : CPLString OGRSQLiteTableLayer::FieldDefnToSQliteFieldDefn( OGRFieldDefn* poFieldDefn )
795 : {
796 172 : CPLString osRet = OGRSQLiteFieldDefnToSQliteFieldDefn(poFieldDefn);
797 172 : if( poFieldDefn->GetType() == OFTString &&
798 : CSLFindString(papszCompressedColumns, poFieldDefn->GetNameRef()) >= 0 )
799 8 : osRet += "_deflate";
800 :
801 0 : return osRet;
802 : }
803 :
804 : /************************************************************************/
805 : /* CreateField() */
806 : /************************************************************************/
807 :
808 83 : OGRErr OGRSQLiteTableLayer::CreateField( OGRFieldDefn *poFieldIn,
809 : int bApproxOK )
810 :
811 : {
812 83 : OGRFieldDefn oField( poFieldIn );
813 :
814 83 : if (HasLayerDefnError())
815 0 : return OGRERR_FAILURE;
816 :
817 83 : ResetReading();
818 :
819 83 : if (!poDS->GetUpdate())
820 : {
821 : CPLError( CE_Failure, CPLE_NotSupported,
822 : UNSUPPORTED_OP_READ_ONLY,
823 0 : "CreateField");
824 0 : return OGRERR_FAILURE;
825 : }
826 :
827 83 : ClearInsertStmt();
828 :
829 : /* -------------------------------------------------------------------- */
830 : /* Do we want to "launder" the column names into SQLite */
831 : /* friendly format? */
832 : /* -------------------------------------------------------------------- */
833 83 : if( bLaunderColumnNames )
834 : {
835 81 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
836 :
837 81 : oField.SetName( pszSafeName );
838 81 : CPLFree( pszSafeName );
839 : }
840 :
841 :
842 83 : if( (oField.GetType() == OFTTime || oField.GetType() == OFTDate ||
843 : oField.GetType() == OFTDateTime) &&
844 : !(CSLTestBoolean(
845 : CPLGetConfigOption("OGR_SQLITE_ENABLE_DATETIME", "YES"))) )
846 : {
847 0 : oField.SetType(OFTString);
848 : }
849 :
850 : /* ADD COLUMN only avaliable since sqlite 3.1.3 */
851 83 : if (CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_USE_ADD_COLUMN", "YES")) &&
852 : sqlite3_libversion_number() > 3 * 1000000 + 1 * 1000 + 3)
853 : {
854 : int rc;
855 83 : char *pszErrMsg = NULL;
856 83 : sqlite3 *hDB = poDS->GetDB();
857 83 : CPLString osCommand;
858 :
859 83 : CPLString osFieldType(FieldDefnToSQliteFieldDefn(&oField));
860 : osCommand.Printf("ALTER TABLE '%s' ADD COLUMN '%s' %s",
861 : pszEscapedTableName,
862 : OGRSQLiteEscape(oField.GetNameRef()).c_str(),
863 83 : osFieldType.c_str());
864 :
865 : #ifdef DEBUG
866 83 : CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
867 : #endif
868 :
869 83 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
870 83 : if( rc != SQLITE_OK )
871 : {
872 : CPLError( CE_Failure, CPLE_AppDefined,
873 : "Failed to add field %s to table %s:\n %s",
874 : oField.GetNameRef(), poFeatureDefn->GetName(),
875 0 : pszErrMsg );
876 0 : sqlite3_free( pszErrMsg );
877 0 : return OGRERR_FAILURE;
878 0 : }
879 : }
880 : else
881 : {
882 0 : OGRErr eErr = AddColumnAncientMethod(oField);
883 0 : if (eErr != OGRERR_NONE)
884 0 : return eErr;
885 : }
886 :
887 : /* -------------------------------------------------------------------- */
888 : /* Add the field to the OGRFeatureDefn. */
889 : /* -------------------------------------------------------------------- */
890 : int iNewField;
891 83 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
892 :
893 83 : if( poFeatureDefn->GetGeomType() != wkbNone )
894 : {
895 67 : iNextOrdinal++;
896 : }
897 :
898 180 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
899 : {
900 97 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
901 :
902 : // we already added OGC_FID so don't do it again
903 97 : if( EQUAL(poFldDefn->GetNameRef(),"OGC_FID") )
904 0 : continue;
905 :
906 97 : iNextOrdinal++;
907 : }
908 :
909 83 : poFeatureDefn->AddFieldDefn( &oField );
910 :
911 83 : iNewField = poFeatureDefn->GetFieldCount() - 1;
912 : panFieldOrdinals = (int *)
913 83 : CPLRealloc(panFieldOrdinals, (iNewField+1) * sizeof(int) );
914 83 : panFieldOrdinals[iNewField] = iNextOrdinal;
915 :
916 83 : return OGRERR_NONE;
917 : }
918 :
919 : /************************************************************************/
920 : /* InitFieldListForRecrerate() */
921 : /************************************************************************/
922 :
923 23 : void OGRSQLiteTableLayer::InitFieldListForRecrerate(char* & pszNewFieldList,
924 : char* & pszFieldListForSelect,
925 : int nExtraSpace)
926 : {
927 23 : int iField, nFieldListLen = 100 + 2 * nExtraSpace;
928 :
929 118 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
930 : {
931 : nFieldListLen +=
932 95 : 2 * strlen(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()) + 50;
933 : }
934 :
935 23 : nFieldListLen += 50 + (pszFIDColumn ? 2 * strlen(pszFIDColumn) : strlen("OGC_FID"));
936 23 : if( poFeatureDefn->GetGeomType() != wkbNone )
937 : {
938 23 : nFieldListLen += 50 + 2 * strlen(osGeomColumn);
939 : }
940 :
941 23 : pszFieldListForSelect = (char *) CPLCalloc(1,nFieldListLen);
942 23 : pszNewFieldList = (char *) CPLCalloc(1,nFieldListLen);
943 :
944 : /* -------------------------------------------------------------------- */
945 : /* Build list of old fields, and the list of new fields. */
946 : /* -------------------------------------------------------------------- */
947 23 : sprintf( pszFieldListForSelect, "\"%s\"", pszFIDColumn ? OGRSQLiteEscapeName(pszFIDColumn).c_str() : "OGC_FID" );
948 23 : sprintf( pszNewFieldList, "\"%s\" INTEGER PRIMARY KEY",pszFIDColumn ? OGRSQLiteEscapeName(pszFIDColumn).c_str() : "OGC_FID" );
949 :
950 23 : if( poFeatureDefn->GetGeomType() != wkbNone )
951 : {
952 23 : strcat( pszFieldListForSelect, "," );
953 23 : strcat( pszNewFieldList, "," );
954 :
955 23 : strcat( pszFieldListForSelect, "\"");
956 23 : strcat( pszFieldListForSelect, OGRSQLiteEscapeName(osGeomColumn) );
957 23 : strcat( pszFieldListForSelect, "\"");
958 :
959 23 : strcat( pszNewFieldList, "\"");
960 46 : strcat( pszNewFieldList, OGRSQLiteEscapeName(osGeomColumn) );
961 23 : strcat( pszNewFieldList, "\"");
962 :
963 23 : if ( eGeomFormat == OSGF_WKT )
964 0 : strcat( pszNewFieldList, " VARCHAR" );
965 : else
966 23 : strcat( pszNewFieldList, " BLOB" );
967 : }
968 23 : }
969 :
970 : /************************************************************************/
971 : /* AddColumnAncientMethod() */
972 : /************************************************************************/
973 :
974 0 : OGRErr OGRSQLiteTableLayer::AddColumnAncientMethod( OGRFieldDefn& oField)
975 : {
976 :
977 : /* -------------------------------------------------------------------- */
978 : /* How much space do we need for the list of fields. */
979 : /* -------------------------------------------------------------------- */
980 : int iField;
981 : char *pszOldFieldList, *pszNewFieldList;
982 :
983 : InitFieldListForRecrerate(pszNewFieldList, pszOldFieldList,
984 0 : strlen( oField.GetNameRef() ));
985 :
986 : /* -------------------------------------------------------------------- */
987 : /* Build list of old fields, and the list of new fields. */
988 : /* -------------------------------------------------------------------- */
989 :
990 0 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
991 :
992 0 : if( poFeatureDefn->GetGeomType() != wkbNone )
993 : {
994 0 : iNextOrdinal++;
995 : }
996 :
997 0 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
998 : {
999 0 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1000 :
1001 : // we already added OGC_FID so don't do it again
1002 0 : if( EQUAL(poFldDefn->GetNameRef(),pszFIDColumn ? pszFIDColumn : "OGC_FID") )
1003 0 : continue;
1004 :
1005 : sprintf( pszOldFieldList+strlen(pszOldFieldList),
1006 0 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1007 :
1008 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1009 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1010 0 : FieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1011 :
1012 0 : iNextOrdinal++;
1013 : }
1014 :
1015 : /* -------------------------------------------------------------------- */
1016 : /* Add the new field. */
1017 : /* -------------------------------------------------------------------- */
1018 :
1019 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1020 : ", '%s' %s", OGRSQLiteEscape(oField.GetNameRef()).c_str(),
1021 0 : FieldDefnToSQliteFieldDefn(&oField).c_str() );
1022 :
1023 : /* ==================================================================== */
1024 : /* Backup, destroy, recreate and repopulate the table. SQLite */
1025 : /* has no ALTER TABLE so we have to do all this to add a */
1026 : /* column. */
1027 : /* ==================================================================== */
1028 :
1029 : /* -------------------------------------------------------------------- */
1030 : /* Do this all in a transaction. */
1031 : /* -------------------------------------------------------------------- */
1032 0 : poDS->SoftStartTransaction();
1033 :
1034 : /* -------------------------------------------------------------------- */
1035 : /* Save existing related triggers and index */
1036 : /* -------------------------------------------------------------------- */
1037 : int rc;
1038 0 : char *pszErrMsg = NULL;
1039 0 : sqlite3 *hDB = poDS->GetDB();
1040 0 : CPLString osSQL;
1041 :
1042 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
1043 0 : pszEscapedTableName );
1044 :
1045 : int nRowTriggerIndexCount, nColTriggerIndexCount;
1046 0 : char **papszTriggerIndexResult = NULL;
1047 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
1048 0 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
1049 :
1050 : /* -------------------------------------------------------------------- */
1051 : /* Make a backup of the table. */
1052 : /* -------------------------------------------------------------------- */
1053 :
1054 0 : if( rc == SQLITE_OK )
1055 : rc = sqlite3_exec( hDB,
1056 : CPLSPrintf( "CREATE TEMPORARY TABLE t1_back(%s)",
1057 : pszOldFieldList ),
1058 0 : NULL, NULL, &pszErrMsg );
1059 :
1060 0 : if( rc == SQLITE_OK )
1061 : rc = sqlite3_exec( hDB,
1062 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
1063 : pszOldFieldList,
1064 : pszEscapedTableName ),
1065 0 : NULL, NULL, &pszErrMsg );
1066 :
1067 :
1068 : /* -------------------------------------------------------------------- */
1069 : /* Drop the original table, and recreate with new field. */
1070 : /* -------------------------------------------------------------------- */
1071 0 : if( rc == SQLITE_OK )
1072 : rc = sqlite3_exec( hDB,
1073 : CPLSPrintf( "DROP TABLE '%s'",
1074 : pszEscapedTableName ),
1075 0 : NULL, NULL, &pszErrMsg );
1076 :
1077 0 : if( rc == SQLITE_OK )
1078 : {
1079 : const char *pszCmd =
1080 : CPLSPrintf( "CREATE TABLE '%s' (%s)",
1081 : pszEscapedTableName,
1082 0 : pszNewFieldList );
1083 : rc = sqlite3_exec( hDB, pszCmd,
1084 0 : NULL, NULL, &pszErrMsg );
1085 :
1086 0 : CPLDebug( "OGR_SQLITE", "exec(%s)", pszCmd );
1087 : }
1088 :
1089 : /* -------------------------------------------------------------------- */
1090 : /* Copy backup field values into new table. */
1091 : /* -------------------------------------------------------------------- */
1092 :
1093 0 : if( rc == SQLITE_OK )
1094 : rc = sqlite3_exec( hDB,
1095 : CPLSPrintf( "INSERT INTO '%s' SELECT %s, NULL FROM t1_back",
1096 : pszEscapedTableName,
1097 : pszOldFieldList ),
1098 0 : NULL, NULL, &pszErrMsg );
1099 :
1100 0 : CPLFree( pszOldFieldList );
1101 0 : CPLFree( pszNewFieldList );
1102 :
1103 : /* -------------------------------------------------------------------- */
1104 : /* Cleanup backup table. */
1105 : /* -------------------------------------------------------------------- */
1106 :
1107 0 : if( rc == SQLITE_OK )
1108 : rc = sqlite3_exec( hDB,
1109 : CPLSPrintf( "DROP TABLE t1_back" ),
1110 0 : NULL, NULL, &pszErrMsg );
1111 :
1112 : /* -------------------------------------------------------------------- */
1113 : /* Recreate existing related tables, triggers and index */
1114 : /* -------------------------------------------------------------------- */
1115 :
1116 0 : if( rc == SQLITE_OK )
1117 : {
1118 : int i;
1119 :
1120 0 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
1121 : {
1122 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
1123 : rc = sqlite3_exec( hDB,
1124 0 : papszTriggerIndexResult[i],
1125 0 : NULL, NULL, &pszErrMsg );
1126 : }
1127 : }
1128 :
1129 : /* -------------------------------------------------------------------- */
1130 : /* COMMIT on success or ROLLBACK on failuire. */
1131 : /* -------------------------------------------------------------------- */
1132 :
1133 0 : sqlite3_free_table( papszTriggerIndexResult );
1134 :
1135 0 : if( rc == SQLITE_OK )
1136 : {
1137 0 : poDS->SoftCommit();
1138 : }
1139 : else
1140 : {
1141 : CPLError( CE_Failure, CPLE_AppDefined,
1142 : "Failed to add field %s to table %s:\n %s",
1143 : oField.GetNameRef(), poFeatureDefn->GetName(),
1144 0 : pszErrMsg );
1145 0 : sqlite3_free( pszErrMsg );
1146 :
1147 0 : poDS->SoftRollback();
1148 :
1149 0 : return OGRERR_FAILURE;
1150 : }
1151 :
1152 0 : return OGRERR_NONE;
1153 : }
1154 :
1155 : /************************************************************************/
1156 : /* RecreateTable() */
1157 : /************************************************************************/
1158 :
1159 23 : OGRErr OGRSQLiteTableLayer::RecreateTable(const char* pszFieldListForSelect,
1160 : const char* pszNewFieldList,
1161 : const char* pszGenericErrorMessage)
1162 : {
1163 : /* -------------------------------------------------------------------- */
1164 : /* Do this all in a transaction. */
1165 : /* -------------------------------------------------------------------- */
1166 23 : poDS->SoftStartTransaction();
1167 :
1168 : /* -------------------------------------------------------------------- */
1169 : /* Save existing related triggers and index */
1170 : /* -------------------------------------------------------------------- */
1171 : int rc;
1172 23 : char *pszErrMsg = NULL;
1173 23 : sqlite3 *hDB = poDS->GetDB();
1174 23 : CPLString osSQL;
1175 :
1176 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
1177 23 : pszEscapedTableName );
1178 :
1179 : int nRowTriggerIndexCount, nColTriggerIndexCount;
1180 23 : char **papszTriggerIndexResult = NULL;
1181 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
1182 23 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
1183 :
1184 : /* -------------------------------------------------------------------- */
1185 : /* Make a backup of the table. */
1186 : /* -------------------------------------------------------------------- */
1187 :
1188 23 : if( rc == SQLITE_OK )
1189 : rc = sqlite3_exec( hDB,
1190 : CPLSPrintf( "CREATE TABLE t1_back(%s)",
1191 : pszNewFieldList ),
1192 23 : NULL, NULL, &pszErrMsg );
1193 :
1194 23 : if( rc == SQLITE_OK )
1195 : rc = sqlite3_exec( hDB,
1196 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
1197 : pszFieldListForSelect,
1198 : pszEscapedTableName ),
1199 23 : NULL, NULL, &pszErrMsg );
1200 :
1201 :
1202 : /* -------------------------------------------------------------------- */
1203 : /* Drop the original table */
1204 : /* -------------------------------------------------------------------- */
1205 23 : if( rc == SQLITE_OK )
1206 : rc = sqlite3_exec( hDB,
1207 : CPLSPrintf( "DROP TABLE '%s'",
1208 : pszEscapedTableName ),
1209 23 : NULL, NULL, &pszErrMsg );
1210 :
1211 : /* -------------------------------------------------------------------- */
1212 : /* Rename backup table as new table */
1213 : /* -------------------------------------------------------------------- */
1214 23 : if( rc == SQLITE_OK )
1215 : {
1216 : const char *pszCmd =
1217 : CPLSPrintf( "ALTER TABLE t1_back RENAME TO '%s'",
1218 23 : pszEscapedTableName);
1219 : rc = sqlite3_exec( hDB, pszCmd,
1220 23 : NULL, NULL, &pszErrMsg );
1221 : }
1222 :
1223 : /* -------------------------------------------------------------------- */
1224 : /* Recreate existing related tables, triggers and index */
1225 : /* -------------------------------------------------------------------- */
1226 :
1227 23 : if( rc == SQLITE_OK )
1228 : {
1229 : int i;
1230 :
1231 23 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
1232 : {
1233 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
1234 : rc = sqlite3_exec( hDB,
1235 0 : papszTriggerIndexResult[i],
1236 0 : NULL, NULL, &pszErrMsg );
1237 : }
1238 : }
1239 :
1240 : /* -------------------------------------------------------------------- */
1241 : /* COMMIT on success or ROLLBACK on failuire. */
1242 : /* -------------------------------------------------------------------- */
1243 :
1244 23 : sqlite3_free_table( papszTriggerIndexResult );
1245 :
1246 23 : if( rc == SQLITE_OK )
1247 : {
1248 23 : poDS->SoftCommit();
1249 :
1250 23 : return OGRERR_NONE;
1251 : }
1252 : else
1253 : {
1254 : CPLError( CE_Failure, CPLE_AppDefined,
1255 : "%s:\n %s",
1256 : pszGenericErrorMessage,
1257 0 : pszErrMsg );
1258 0 : sqlite3_free( pszErrMsg );
1259 :
1260 0 : poDS->SoftRollback();
1261 :
1262 0 : return OGRERR_FAILURE;
1263 0 : }
1264 : }
1265 :
1266 : /************************************************************************/
1267 : /* DeleteField() */
1268 : /************************************************************************/
1269 :
1270 8 : OGRErr OGRSQLiteTableLayer::DeleteField( int iFieldToDelete )
1271 : {
1272 8 : if (HasLayerDefnError())
1273 0 : return OGRERR_FAILURE;
1274 :
1275 8 : if (!poDS->GetUpdate())
1276 : {
1277 : CPLError( CE_Failure, CPLE_NotSupported,
1278 : UNSUPPORTED_OP_READ_ONLY,
1279 0 : "DeleteField");
1280 0 : return OGRERR_FAILURE;
1281 : }
1282 :
1283 8 : if (iFieldToDelete < 0 || iFieldToDelete >= poFeatureDefn->GetFieldCount())
1284 : {
1285 : CPLError( CE_Failure, CPLE_NotSupported,
1286 2 : "Invalid field index");
1287 2 : return OGRERR_FAILURE;
1288 : }
1289 :
1290 6 : ResetReading();
1291 :
1292 : /* -------------------------------------------------------------------- */
1293 : /* Build list of old fields, and the list of new fields. */
1294 : /* -------------------------------------------------------------------- */
1295 : int iField;
1296 : char *pszNewFieldList, *pszFieldListForSelect;
1297 6 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1298 :
1299 26 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1300 : {
1301 20 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1302 :
1303 20 : if (iField == iFieldToDelete)
1304 6 : continue;
1305 :
1306 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1307 14 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1308 :
1309 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1310 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1311 28 : FieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1312 : }
1313 :
1314 : /* -------------------------------------------------------------------- */
1315 : /* Recreate table. */
1316 : /* -------------------------------------------------------------------- */
1317 6 : CPLString osErrorMsg;
1318 : osErrorMsg.Printf("Failed to remove field %s from table %s",
1319 : poFeatureDefn->GetFieldDefn(iFieldToDelete)->GetNameRef(),
1320 6 : poFeatureDefn->GetName());
1321 :
1322 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1323 : pszNewFieldList,
1324 6 : osErrorMsg.c_str());
1325 :
1326 6 : CPLFree( pszFieldListForSelect );
1327 6 : CPLFree( pszNewFieldList );
1328 :
1329 6 : if (eErr != OGRERR_NONE)
1330 0 : return eErr;
1331 :
1332 : /* -------------------------------------------------------------------- */
1333 : /* Finish */
1334 : /* -------------------------------------------------------------------- */
1335 6 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
1336 :
1337 6 : if( poFeatureDefn->GetGeomType() != wkbNone )
1338 : {
1339 6 : iNextOrdinal++;
1340 : }
1341 :
1342 6 : int iNewField = 0;
1343 26 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1344 : {
1345 20 : if (iField == iFieldToDelete)
1346 6 : continue;
1347 :
1348 14 : panFieldOrdinals[iNewField ++] = iNextOrdinal++;
1349 : }
1350 :
1351 6 : return poFeatureDefn->DeleteFieldDefn( iFieldToDelete );
1352 : }
1353 :
1354 : /************************************************************************/
1355 : /* AlterFieldDefn() */
1356 : /************************************************************************/
1357 :
1358 9 : OGRErr OGRSQLiteTableLayer::AlterFieldDefn( int iFieldToAlter, OGRFieldDefn* poNewFieldDefn, int nFlags )
1359 : {
1360 9 : if (HasLayerDefnError())
1361 0 : return OGRERR_FAILURE;
1362 :
1363 9 : if (!poDS->GetUpdate())
1364 : {
1365 : CPLError( CE_Failure, CPLE_NotSupported,
1366 : UNSUPPORTED_OP_READ_ONLY,
1367 0 : "AlterFieldDefn");
1368 0 : return OGRERR_FAILURE;
1369 : }
1370 :
1371 9 : if (iFieldToAlter < 0 || iFieldToAlter >= poFeatureDefn->GetFieldCount())
1372 : {
1373 : CPLError( CE_Failure, CPLE_NotSupported,
1374 2 : "Invalid field index");
1375 2 : return OGRERR_FAILURE;
1376 : }
1377 :
1378 7 : ClearInsertStmt();
1379 7 : ResetReading();
1380 :
1381 : /* -------------------------------------------------------------------- */
1382 : /* Build list of old fields, and the list of new fields. */
1383 : /* -------------------------------------------------------------------- */
1384 : int iField;
1385 : char *pszNewFieldList, *pszFieldListForSelect;
1386 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect,
1387 7 : strlen(poNewFieldDefn->GetNameRef()));
1388 :
1389 40 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1390 : {
1391 33 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1392 :
1393 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1394 33 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1395 :
1396 33 : if (iField == iFieldToAlter)
1397 : {
1398 7 : OGRFieldDefn oTmpFieldDefn(poFldDefn);
1399 7 : if( (nFlags & ALTER_NAME_FLAG) )
1400 7 : oTmpFieldDefn.SetName(poNewFieldDefn->GetNameRef());
1401 7 : if( (nFlags & ALTER_TYPE_FLAG) )
1402 7 : oTmpFieldDefn.SetType(poNewFieldDefn->GetType());
1403 7 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
1404 : {
1405 7 : oTmpFieldDefn.SetWidth(poNewFieldDefn->GetWidth());
1406 7 : oTmpFieldDefn.SetPrecision(poNewFieldDefn->GetPrecision());
1407 : }
1408 :
1409 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1410 : ", '%s' %s",
1411 : OGRSQLiteEscape(oTmpFieldDefn.GetNameRef()).c_str(),
1412 7 : FieldDefnToSQliteFieldDefn(&oTmpFieldDefn).c_str() );
1413 7 : if ( (nFlags & ALTER_NAME_FLAG) &&
1414 : oTmpFieldDefn.GetType() == OFTString &&
1415 : CSLFindString(papszCompressedColumns, poFldDefn->GetNameRef()) >= 0 )
1416 : {
1417 0 : sprintf( pszNewFieldList+strlen(pszNewFieldList), "_deflate");
1418 7 : }
1419 : }
1420 : else
1421 : {
1422 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1423 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1424 26 : FieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1425 : }
1426 : }
1427 :
1428 : /* -------------------------------------------------------------------- */
1429 : /* Recreate table. */
1430 : /* -------------------------------------------------------------------- */
1431 7 : CPLString osErrorMsg;
1432 : osErrorMsg.Printf("Failed to alter field %s from table %s",
1433 : poFeatureDefn->GetFieldDefn(iFieldToAlter)->GetNameRef(),
1434 7 : poFeatureDefn->GetName());
1435 :
1436 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1437 : pszNewFieldList,
1438 7 : osErrorMsg.c_str());
1439 :
1440 7 : CPLFree( pszFieldListForSelect );
1441 7 : CPLFree( pszNewFieldList );
1442 :
1443 7 : if (eErr != OGRERR_NONE)
1444 0 : return eErr;
1445 :
1446 : /* -------------------------------------------------------------------- */
1447 : /* Finish */
1448 : /* -------------------------------------------------------------------- */
1449 :
1450 7 : OGRFieldDefn* poFieldDefn = poFeatureDefn->GetFieldDefn(iFieldToAlter);
1451 :
1452 7 : if (nFlags & ALTER_TYPE_FLAG)
1453 : {
1454 : int iIdx;
1455 7 : if( poNewFieldDefn->GetType() != OFTString &&
1456 : (iIdx = CSLFindString(papszCompressedColumns,
1457 : poFieldDefn->GetNameRef())) >= 0 )
1458 : {
1459 : papszCompressedColumns = CSLRemoveStrings(papszCompressedColumns,
1460 0 : iIdx, 1, NULL);
1461 : }
1462 7 : poFieldDefn->SetType(poNewFieldDefn->GetType());
1463 : }
1464 7 : if (nFlags & ALTER_NAME_FLAG)
1465 : {
1466 : int iIdx;
1467 7 : if( (iIdx = CSLFindString(papszCompressedColumns,
1468 : poFieldDefn->GetNameRef())) >= 0 )
1469 : {
1470 0 : CPLFree(papszCompressedColumns[iIdx]);
1471 0 : papszCompressedColumns[iIdx] =
1472 0 : CPLStrdup(poNewFieldDefn->GetNameRef());
1473 : }
1474 7 : poFieldDefn->SetName(poNewFieldDefn->GetNameRef());
1475 : }
1476 7 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
1477 : {
1478 7 : poFieldDefn->SetWidth(poNewFieldDefn->GetWidth());
1479 7 : poFieldDefn->SetPrecision(poNewFieldDefn->GetPrecision());
1480 : }
1481 7 : return OGRERR_NONE;
1482 : }
1483 :
1484 : /************************************************************************/
1485 : /* ReorderFields() */
1486 : /************************************************************************/
1487 :
1488 12 : OGRErr OGRSQLiteTableLayer::ReorderFields( int* panMap )
1489 : {
1490 12 : if (HasLayerDefnError())
1491 0 : return OGRERR_FAILURE;
1492 :
1493 12 : if (!poDS->GetUpdate())
1494 : {
1495 : CPLError( CE_Failure, CPLE_NotSupported,
1496 : UNSUPPORTED_OP_READ_ONLY,
1497 0 : "ReorderFields");
1498 0 : return OGRERR_FAILURE;
1499 : }
1500 :
1501 12 : if (poFeatureDefn->GetFieldCount() == 0)
1502 1 : return OGRERR_NONE;
1503 :
1504 11 : OGRErr eErr = OGRCheckPermutation(panMap, poFeatureDefn->GetFieldCount());
1505 11 : if (eErr != OGRERR_NONE)
1506 1 : return eErr;
1507 :
1508 10 : ClearInsertStmt();
1509 10 : ResetReading();
1510 :
1511 : /* -------------------------------------------------------------------- */
1512 : /* Build list of old fields, and the list of new fields. */
1513 : /* -------------------------------------------------------------------- */
1514 : int iField;
1515 : char *pszNewFieldList, *pszFieldListForSelect;
1516 10 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1517 :
1518 104 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1519 : {
1520 42 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(panMap[iField]);
1521 :
1522 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1523 42 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1524 :
1525 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1526 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1527 84 : FieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1528 : }
1529 :
1530 : /* -------------------------------------------------------------------- */
1531 : /* Recreate table. */
1532 : /* -------------------------------------------------------------------- */
1533 10 : CPLString osErrorMsg;
1534 : osErrorMsg.Printf("Failed to reorder fields from table %s",
1535 10 : poFeatureDefn->GetName());
1536 :
1537 : eErr = RecreateTable(pszFieldListForSelect,
1538 : pszNewFieldList,
1539 10 : osErrorMsg.c_str());
1540 :
1541 10 : CPLFree( pszFieldListForSelect );
1542 10 : CPLFree( pszNewFieldList );
1543 :
1544 10 : if (eErr != OGRERR_NONE)
1545 0 : return eErr;
1546 :
1547 : /* -------------------------------------------------------------------- */
1548 : /* Finish */
1549 : /* -------------------------------------------------------------------- */
1550 :
1551 10 : return poFeatureDefn->ReorderFieldDefns( panMap );
1552 : }
1553 :
1554 : /************************************************************************/
1555 : /* BindValues() */
1556 : /************************************************************************/
1557 :
1558 : /* the bBindNullValues is set to TRUE by SetFeature() for UPDATE statements, */
1559 : /* and to FALSE by CreateFeature() for INSERT statements; */
1560 :
1561 473 : OGRErr OGRSQLiteTableLayer::BindValues( OGRFeature *poFeature,
1562 : sqlite3_stmt* hStmt,
1563 : int bBindNullValues )
1564 : {
1565 : int rc;
1566 473 : sqlite3 *hDB = poDS->GetDB();
1567 :
1568 : /* -------------------------------------------------------------------- */
1569 : /* Bind the geometry */
1570 : /* -------------------------------------------------------------------- */
1571 473 : int nBindField = 1;
1572 :
1573 473 : if( osGeomColumn.size() != 0 &&
1574 : eGeomFormat != OSGF_FGF )
1575 : {
1576 429 : OGRGeometry* poGeom = poFeature->GetGeometryRef();
1577 429 : if ( poGeom != NULL )
1578 : {
1579 405 : if ( eGeomFormat == OSGF_WKT )
1580 : {
1581 2 : char *pszWKT = NULL;
1582 2 : poGeom->exportToWkt( &pszWKT );
1583 2 : rc = sqlite3_bind_text( hStmt, nBindField++, pszWKT, -1, CPLFree );
1584 : }
1585 403 : else if( eGeomFormat == OSGF_WKB )
1586 : {
1587 170 : int nWKBLen = poGeom->WkbSize();
1588 170 : GByte *pabyWKB = (GByte *) CPLMalloc(nWKBLen + 1);
1589 :
1590 170 : poGeom->exportToWkb( wkbNDR, pabyWKB );
1591 170 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabyWKB, nWKBLen, CPLFree );
1592 : }
1593 233 : else if ( eGeomFormat == OSGF_SpatiaLite )
1594 : {
1595 : int nBLOBLen;
1596 : GByte *pabySLBLOB;
1597 :
1598 233 : if( nSRSId == UNINITIALIZED_SRID )
1599 0 : nSRSId = poDS->GetUndefinedSRID();
1600 :
1601 : ExportSpatiaLiteGeometry( poGeom, nSRSId, wkbNDR, bHasM,
1602 233 : bSpatialite2D, bUseComprGeom, &pabySLBLOB, &nBLOBLen );
1603 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabySLBLOB,
1604 233 : nBLOBLen, CPLFree );
1605 : }
1606 : else
1607 : {
1608 0 : rc = SQLITE_OK;
1609 0 : CPLAssert(0);
1610 : }
1611 : }
1612 : else
1613 : {
1614 24 : if (bBindNullValues)
1615 2 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1616 : else
1617 22 : rc = SQLITE_OK;
1618 : }
1619 :
1620 429 : if( rc != SQLITE_OK )
1621 : {
1622 : CPLError( CE_Failure, CPLE_AppDefined,
1623 : "sqlite3_bind_blob/text() failed:\n %s",
1624 0 : sqlite3_errmsg(hDB) );
1625 0 : return OGRERR_FAILURE;
1626 : }
1627 : }
1628 :
1629 : /* -------------------------------------------------------------------- */
1630 : /* Bind field values. */
1631 : /* -------------------------------------------------------------------- */
1632 : int iField;
1633 473 : int nFieldCount = poFeatureDefn->GetFieldCount();
1634 1491 : for( iField = 0; iField < nFieldCount; iField++ )
1635 : {
1636 : const char *pszRawValue;
1637 :
1638 1018 : if( !poFeature->IsFieldSet( iField ) )
1639 : {
1640 342 : if (bBindNullValues)
1641 8 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1642 : else
1643 334 : rc = SQLITE_OK;
1644 : }
1645 : else
1646 : {
1647 676 : switch( poFeatureDefn->GetFieldDefn(iField)->GetType() )
1648 : {
1649 : case OFTInteger:
1650 : {
1651 143 : int nFieldVal = poFeature->GetFieldAsInteger( iField );
1652 143 : rc = sqlite3_bind_int(hStmt, nBindField++, nFieldVal);
1653 143 : break;
1654 : }
1655 :
1656 : case OFTReal:
1657 : {
1658 210 : double dfFieldVal = poFeature->GetFieldAsDouble( iField );
1659 210 : rc = sqlite3_bind_double(hStmt, nBindField++, dfFieldVal);
1660 210 : break;
1661 : }
1662 :
1663 : case OFTBinary:
1664 : {
1665 24 : int nDataLength = 0;
1666 : GByte* pabyData =
1667 24 : poFeature->GetFieldAsBinary( iField, &nDataLength );
1668 : rc = sqlite3_bind_blob(hStmt, nBindField++,
1669 24 : pabyData, nDataLength, SQLITE_TRANSIENT);
1670 24 : break;
1671 : }
1672 :
1673 : case OFTDateTime:
1674 : {
1675 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1676 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1677 2 : &nHour, &nMinute, &nSecond, &nTZ);
1678 : char szBuffer[64];
1679 : sprintf(szBuffer, "%04d-%02d-%02dT%02d:%02d:%02d",
1680 2 : nYear, nMonth, nDay, nHour, nMinute, nSecond);
1681 : rc = sqlite3_bind_text(hStmt, nBindField++,
1682 2 : szBuffer, -1, SQLITE_TRANSIENT);
1683 2 : break;
1684 : }
1685 :
1686 : case OFTDate:
1687 : {
1688 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1689 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1690 2 : &nHour, &nMinute, &nSecond, &nTZ);
1691 : char szBuffer[64];
1692 2 : sprintf(szBuffer, "%04d-%02d-%02dT", nYear, nMonth, nDay);
1693 : rc = sqlite3_bind_text(hStmt, nBindField++,
1694 2 : szBuffer, -1, SQLITE_TRANSIENT);
1695 2 : break;
1696 : }
1697 :
1698 : case OFTTime:
1699 : {
1700 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1701 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1702 2 : &nHour, &nMinute, &nSecond, &nTZ);
1703 : char szBuffer[64];
1704 2 : sprintf(szBuffer, "%02d:%02d:%02d", nHour, nMinute, nSecond);
1705 : rc = sqlite3_bind_text(hStmt, nBindField++,
1706 2 : szBuffer, -1, SQLITE_TRANSIENT);
1707 2 : break;
1708 : }
1709 :
1710 : default:
1711 : {
1712 293 : pszRawValue = poFeature->GetFieldAsString( iField );
1713 293 : if( CSLFindString(papszCompressedColumns,
1714 : poFeatureDefn->GetFieldDefn(iField)->GetNameRef()) >= 0 )
1715 : {
1716 17 : size_t nBytesOut = 0;
1717 : void* pOut = CPLZLibDeflate( pszRawValue,
1718 : strlen(pszRawValue), -1,
1719 : NULL, 0,
1720 17 : &nBytesOut );
1721 17 : if( pOut != NULL )
1722 : {
1723 : rc = sqlite3_bind_blob(hStmt, nBindField++,
1724 : pOut,
1725 : nBytesOut,
1726 17 : CPLFree);
1727 : }
1728 : else
1729 0 : rc = SQLITE_ERROR;
1730 : }
1731 : else
1732 : {
1733 : rc = sqlite3_bind_text(hStmt, nBindField++,
1734 276 : pszRawValue, -1, SQLITE_TRANSIENT);
1735 : }
1736 : break;
1737 : }
1738 : }
1739 : }
1740 :
1741 1018 : if( rc != SQLITE_OK )
1742 : {
1743 : CPLError( CE_Failure, CPLE_AppDefined,
1744 : "sqlite3_bind_() for column %s failed:\n %s",
1745 : poFeatureDefn->GetFieldDefn(iField)->GetNameRef(),
1746 0 : sqlite3_errmsg(hDB) );
1747 0 : return OGRERR_FAILURE;
1748 : }
1749 : }
1750 :
1751 473 : return OGRERR_NONE;
1752 : }
1753 :
1754 : /************************************************************************/
1755 : /* SetFeature() */
1756 : /************************************************************************/
1757 :
1758 21 : OGRErr OGRSQLiteTableLayer::SetFeature( OGRFeature *poFeature )
1759 :
1760 : {
1761 21 : if (HasLayerDefnError())
1762 0 : return OGRERR_FAILURE;
1763 :
1764 21 : if( pszFIDColumn == NULL )
1765 : {
1766 : CPLError( CE_Failure, CPLE_AppDefined,
1767 0 : "SetFeature() without any FID column." );
1768 0 : return OGRERR_FAILURE;
1769 : }
1770 :
1771 21 : if( poFeature->GetFID() == OGRNullFID )
1772 : {
1773 : CPLError( CE_Failure, CPLE_AppDefined,
1774 0 : "SetFeature() with unset FID fails." );
1775 0 : return OGRERR_FAILURE;
1776 : }
1777 :
1778 21 : if (!poDS->GetUpdate())
1779 : {
1780 : CPLError( CE_Failure, CPLE_NotSupported,
1781 : UNSUPPORTED_OP_READ_ONLY,
1782 0 : "SetFeature");
1783 0 : return OGRERR_FAILURE;
1784 : }
1785 :
1786 21 : sqlite3 *hDB = poDS->GetDB();
1787 21 : CPLString osCommand;
1788 21 : int bNeedComma = FALSE;
1789 :
1790 21 : ResetReading();
1791 :
1792 : /* -------------------------------------------------------------------- */
1793 : /* Form the UPDATE command. */
1794 : /* -------------------------------------------------------------------- */
1795 21 : osCommand += CPLSPrintf( "UPDATE '%s' SET ", pszEscapedTableName );
1796 :
1797 : /* -------------------------------------------------------------------- */
1798 : /* Add geometry field name. */
1799 : /* -------------------------------------------------------------------- */
1800 21 : if( osGeomColumn.size() != 0 &&
1801 : eGeomFormat != OSGF_FGF )
1802 : {
1803 21 : osCommand += "\"";
1804 21 : osCommand += OGRSQLiteEscapeName(osGeomColumn);
1805 21 : osCommand += "\" = ?";
1806 :
1807 21 : bNeedComma = TRUE;
1808 : }
1809 :
1810 : /* -------------------------------------------------------------------- */
1811 : /* Add field names. */
1812 : /* -------------------------------------------------------------------- */
1813 : int iField;
1814 21 : int nFieldCount = poFeatureDefn->GetFieldCount();
1815 :
1816 89 : for( iField = 0; iField < nFieldCount; iField++ )
1817 : {
1818 68 : if( bNeedComma )
1819 68 : osCommand += ",";
1820 :
1821 68 : osCommand += "\"";
1822 68 : osCommand += OGRSQLiteEscapeName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
1823 68 : osCommand += "\" = ?";
1824 :
1825 68 : bNeedComma = TRUE;
1826 : }
1827 :
1828 21 : if (!bNeedComma)
1829 0 : return OGRERR_NONE;
1830 :
1831 : /* -------------------------------------------------------------------- */
1832 : /* Merge final command. */
1833 : /* -------------------------------------------------------------------- */
1834 21 : osCommand += " WHERE \"";
1835 21 : osCommand += OGRSQLiteEscapeName(pszFIDColumn);
1836 21 : osCommand += CPLSPrintf("\" = %ld", poFeature->GetFID());
1837 :
1838 : /* -------------------------------------------------------------------- */
1839 : /* Prepare the statement. */
1840 : /* -------------------------------------------------------------------- */
1841 : int rc;
1842 : sqlite3_stmt *hUpdateStmt;
1843 :
1844 : #ifdef DEBUG
1845 21 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
1846 : #endif
1847 :
1848 21 : rc = sqlite3_prepare( hDB, osCommand, -1, &hUpdateStmt, NULL );
1849 21 : if( rc != SQLITE_OK )
1850 : {
1851 : CPLError( CE_Failure, CPLE_AppDefined,
1852 : "In SetFeature(): sqlite3_prepare(%s):\n %s",
1853 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
1854 :
1855 0 : return OGRERR_FAILURE;
1856 : }
1857 :
1858 : /* -------------------------------------------------------------------- */
1859 : /* Bind values. */
1860 : /* -------------------------------------------------------------------- */
1861 21 : OGRErr eErr = BindValues( poFeature, hUpdateStmt, TRUE );
1862 21 : if (eErr != OGRERR_NONE)
1863 : {
1864 0 : sqlite3_finalize( hUpdateStmt );
1865 0 : return eErr;
1866 : }
1867 :
1868 : /* -------------------------------------------------------------------- */
1869 : /* Execute the update. */
1870 : /* -------------------------------------------------------------------- */
1871 21 : rc = sqlite3_step( hUpdateStmt );
1872 :
1873 21 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
1874 : {
1875 : CPLError( CE_Failure, CPLE_AppDefined,
1876 : "sqlite3_step() failed:\n %s",
1877 0 : sqlite3_errmsg(hDB) );
1878 :
1879 0 : sqlite3_finalize( hUpdateStmt );
1880 0 : return OGRERR_FAILURE;
1881 : }
1882 :
1883 21 : sqlite3_finalize( hUpdateStmt );
1884 :
1885 21 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1886 22 : if( bCachedExtentIsValid &&
1887 1 : poGeom != NULL && !poGeom->IsEmpty() )
1888 : {
1889 1 : OGREnvelope sGeomEnvelope;
1890 1 : poGeom->getEnvelope(&sGeomEnvelope);
1891 1 : oCachedExtent.Merge(sGeomEnvelope);
1892 : }
1893 21 : bStatisticsNeedsToBeFlushed = TRUE;
1894 :
1895 21 : return OGRERR_NONE;
1896 : }
1897 :
1898 : /************************************************************************/
1899 : /* CreateFeature() */
1900 : /************************************************************************/
1901 :
1902 452 : OGRErr OGRSQLiteTableLayer::CreateFeature( OGRFeature *poFeature )
1903 :
1904 : {
1905 452 : sqlite3 *hDB = poDS->GetDB();
1906 452 : CPLString osCommand;
1907 452 : CPLString osValues;
1908 452 : int bNeedComma = FALSE;
1909 :
1910 452 : if (HasLayerDefnError())
1911 0 : return OGRERR_FAILURE;
1912 :
1913 452 : if (!poDS->GetUpdate())
1914 : {
1915 : CPLError( CE_Failure, CPLE_NotSupported,
1916 : UNSUPPORTED_OP_READ_ONLY,
1917 0 : "CreateFeature");
1918 0 : return OGRERR_FAILURE;
1919 : }
1920 :
1921 452 : ResetReading();
1922 :
1923 : /* -------------------------------------------------------------------- */
1924 : /* Form the INSERT command. */
1925 : /* -------------------------------------------------------------------- */
1926 452 : osCommand += CPLSPrintf( "INSERT INTO '%s' (", pszEscapedTableName );
1927 :
1928 : /* -------------------------------------------------------------------- */
1929 : /* Add FID if we have a cleartext FID column. */
1930 : /* -------------------------------------------------------------------- */
1931 452 : if( pszFIDColumn != NULL // && !EQUAL(pszFIDColumn,"OGC_FID")
1932 : && poFeature->GetFID() != OGRNullFID )
1933 : {
1934 100 : osCommand += "\"";
1935 100 : osCommand += OGRSQLiteEscapeName(pszFIDColumn);
1936 100 : osCommand += "\"";
1937 :
1938 100 : osValues += CPLSPrintf( "%ld", poFeature->GetFID() );
1939 100 : bNeedComma = TRUE;
1940 : }
1941 :
1942 : /* -------------------------------------------------------------------- */
1943 : /* Add geometry. */
1944 : /* -------------------------------------------------------------------- */
1945 452 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1946 :
1947 452 : if( osGeomColumn.size() != 0 &&
1948 : poGeom != NULL &&
1949 : eGeomFormat != OSGF_FGF )
1950 : {
1951 :
1952 386 : if( bNeedComma )
1953 : {
1954 100 : osCommand += ",";
1955 100 : osValues += ",";
1956 : }
1957 :
1958 386 : osCommand += "\"";
1959 386 : osCommand += OGRSQLiteEscapeName(osGeomColumn);
1960 386 : osCommand += "\"";
1961 :
1962 386 : osValues += "?";
1963 :
1964 386 : bNeedComma = TRUE;
1965 : }
1966 :
1967 : /* -------------------------------------------------------------------- */
1968 : /* Add field values. */
1969 : /* -------------------------------------------------------------------- */
1970 : int iField;
1971 452 : int nFieldCount = poFeatureDefn->GetFieldCount();
1972 :
1973 1402 : for( iField = 0; iField < nFieldCount; iField++ )
1974 : {
1975 950 : if( !poFeature->IsFieldSet( iField ) )
1976 334 : continue;
1977 :
1978 616 : if( bNeedComma )
1979 : {
1980 552 : osCommand += ",";
1981 552 : osValues += ",";
1982 : }
1983 :
1984 616 : osCommand += "\"";
1985 616 : osCommand += OGRSQLiteEscapeName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
1986 616 : osCommand += "\"";
1987 :
1988 616 : osValues += "?";
1989 :
1990 616 : bNeedComma = TRUE;
1991 : }
1992 :
1993 : /* -------------------------------------------------------------------- */
1994 : /* Merge final command. */
1995 : /* -------------------------------------------------------------------- */
1996 452 : osCommand += ") VALUES (";
1997 452 : osCommand += osValues;
1998 452 : osCommand += ")";
1999 :
2000 452 : if (bNeedComma == FALSE)
2001 2 : osCommand = CPLSPrintf( "INSERT INTO '%s' DEFAULT VALUES", pszEscapedTableName );
2002 :
2003 : /* -------------------------------------------------------------------- */
2004 : /* Prepare the statement. */
2005 : /* -------------------------------------------------------------------- */
2006 : int rc;
2007 :
2008 452 : if (hInsertStmt == NULL ||
2009 : osCommand != osLastInsertStmt)
2010 : {
2011 : #ifdef DEBUG
2012 241 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
2013 : #endif
2014 :
2015 241 : ClearInsertStmt();
2016 241 : osLastInsertStmt = osCommand;
2017 :
2018 : #ifdef HAVE_SQLITE3_PREPARE_V2
2019 241 : rc = sqlite3_prepare_v2( hDB, osCommand, -1, &hInsertStmt, NULL );
2020 : #else
2021 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
2022 : #endif
2023 241 : if( rc != SQLITE_OK )
2024 : {
2025 : CPLError( CE_Failure, CPLE_AppDefined,
2026 : "In CreateFeature(): sqlite3_prepare(%s):\n %s",
2027 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
2028 :
2029 0 : ClearInsertStmt();
2030 0 : return OGRERR_FAILURE;
2031 : }
2032 : }
2033 :
2034 : /* -------------------------------------------------------------------- */
2035 : /* Bind values. */
2036 : /* -------------------------------------------------------------------- */
2037 452 : OGRErr eErr = BindValues( poFeature, hInsertStmt, FALSE );
2038 452 : if (eErr != OGRERR_NONE)
2039 : {
2040 0 : sqlite3_reset( hInsertStmt );
2041 0 : return eErr;
2042 : }
2043 :
2044 : /* -------------------------------------------------------------------- */
2045 : /* Execute the insert. */
2046 : /* -------------------------------------------------------------------- */
2047 452 : rc = sqlite3_step( hInsertStmt );
2048 :
2049 452 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
2050 : {
2051 : CPLError( CE_Failure, CPLE_AppDefined,
2052 : "sqlite3_step() failed:\n %s (%d)",
2053 0 : sqlite3_errmsg(hDB), rc );
2054 0 : sqlite3_reset( hInsertStmt );
2055 0 : return OGRERR_FAILURE;
2056 : }
2057 :
2058 : /* -------------------------------------------------------------------- */
2059 : /* Capture the FID/rowid. */
2060 : /* -------------------------------------------------------------------- */
2061 452 : const sqlite_int64 nFID = sqlite3_last_insert_rowid( hDB );
2062 452 : if(nFID > 0)
2063 : {
2064 450 : poFeature->SetFID( (long)nFID ); /* Possible truncation if nFID is 64bit */
2065 : }
2066 :
2067 452 : sqlite3_reset( hInsertStmt );
2068 :
2069 793 : if( (bCachedExtentIsValid || nFeatureCount == 0) &&
2070 341 : poGeom != NULL && !poGeom->IsEmpty() )
2071 : {
2072 317 : OGREnvelope sGeomEnvelope;
2073 317 : poGeom->getEnvelope(&sGeomEnvelope);
2074 317 : oCachedExtent.Merge(sGeomEnvelope);
2075 317 : bCachedExtentIsValid = TRUE;
2076 317 : bStatisticsNeedsToBeFlushed = TRUE;
2077 : }
2078 452 : if( nFeatureCount >= 0 )
2079 : {
2080 376 : bStatisticsNeedsToBeFlushed = TRUE;
2081 376 : nFeatureCount ++;
2082 : }
2083 :
2084 452 : return OGRERR_NONE;
2085 : }
2086 :
2087 : /************************************************************************/
2088 : /* DeleteFeature() */
2089 : /************************************************************************/
2090 :
2091 8 : OGRErr OGRSQLiteTableLayer::DeleteFeature( long nFID )
2092 :
2093 : {
2094 8 : CPLString osSQL;
2095 : int rc;
2096 8 : char *pszErrMsg = NULL;
2097 :
2098 8 : if (HasLayerDefnError())
2099 0 : return OGRERR_FAILURE;
2100 :
2101 8 : if( pszFIDColumn == NULL )
2102 : {
2103 : CPLError( CE_Failure, CPLE_NotSupported,
2104 0 : "Can't delete feature on a layer without FID column.");
2105 0 : return OGRERR_FAILURE;
2106 : }
2107 :
2108 8 : if (!poDS->GetUpdate())
2109 : {
2110 : CPLError( CE_Failure, CPLE_NotSupported,
2111 : UNSUPPORTED_OP_READ_ONLY,
2112 0 : "DeleteFeature");
2113 0 : return OGRERR_FAILURE;
2114 : }
2115 :
2116 8 : ResetReading();
2117 :
2118 : osSQL.Printf( "DELETE FROM '%s' WHERE \"%s\" = %ld",
2119 : pszEscapedTableName,
2120 8 : OGRSQLiteEscapeName(pszFIDColumn).c_str(), nFID );
2121 :
2122 8 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
2123 :
2124 8 : rc = sqlite3_exec( poDS->GetDB(), osSQL, NULL, NULL, &pszErrMsg );
2125 8 : if( rc != SQLITE_OK )
2126 : {
2127 : CPLError( CE_Failure, CPLE_AppDefined,
2128 : "In DeleteFeature(): sqlite3_exec(%s):\n %s",
2129 0 : osSQL.c_str(), pszErrMsg );
2130 0 : sqlite3_free( pszErrMsg );
2131 0 : return OGRERR_FAILURE;
2132 : }
2133 :
2134 8 : int nChanged = sqlite3_changes( poDS->GetDB() );
2135 :
2136 8 : if( nChanged == 1 )
2137 : {
2138 8 : bCachedExtentIsValid = FALSE;
2139 8 : nFeatureCount --;
2140 8 : bStatisticsNeedsToBeFlushed = TRUE;
2141 : }
2142 :
2143 8 : return OGRERR_NONE;
2144 : }
2145 :
2146 : /************************************************************************/
2147 : /* CreateSpatialIndex() */
2148 : /************************************************************************/
2149 :
2150 70 : int OGRSQLiteTableLayer::CreateSpatialIndex()
2151 : {
2152 70 : CPLString osCommand;
2153 :
2154 : osCommand.Printf("SELECT CreateSpatialIndex('%s', '%s')",
2155 70 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2156 :
2157 70 : char* pszErrMsg = NULL;
2158 70 : sqlite3 *hDB = poDS->GetDB();
2159 : #ifdef DEBUG
2160 70 : CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
2161 : #endif
2162 70 : int rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
2163 70 : if( rc != SQLITE_OK )
2164 : {
2165 : CPLError( CE_Failure, CPLE_AppDefined,
2166 0 : "Unable to create spatial index:\n%s", pszErrMsg );
2167 0 : sqlite3_free( pszErrMsg );
2168 0 : return FALSE;
2169 : }
2170 :
2171 70 : bHasSpatialIndex = TRUE;
2172 70 : return TRUE;
2173 : }
2174 :
2175 : /************************************************************************/
2176 : /* HasSpatialIndex() */
2177 : /************************************************************************/
2178 :
2179 68 : int OGRSQLiteTableLayer::HasSpatialIndex()
2180 : {
2181 68 : if( bDeferedSpatialIndexCreation )
2182 : {
2183 0 : bDeferedSpatialIndexCreation = FALSE;
2184 0 : bHasSpatialIndex = CreateSpatialIndex();
2185 : }
2186 :
2187 68 : return bHasSpatialIndex;
2188 : }
2189 :
2190 : /************************************************************************/
2191 : /* InitFeatureCount() */
2192 : /************************************************************************/
2193 :
2194 120 : void OGRSQLiteTableLayer::InitFeatureCount()
2195 : {
2196 120 : nFeatureCount = 0;
2197 120 : bStatisticsNeedsToBeFlushed = TRUE;
2198 120 : }
2199 :
2200 : /************************************************************************/
2201 : /* InvalidateCachedFeatureCountAndExtent() */
2202 : /************************************************************************/
2203 :
2204 261 : void OGRSQLiteTableLayer::InvalidateCachedFeatureCountAndExtent()
2205 : {
2206 261 : nFeatureCount = -1;
2207 261 : bCachedExtentIsValid = FALSE;
2208 261 : bStatisticsNeedsToBeFlushed = TRUE;
2209 261 : }
2210 :
2211 : /************************************************************************/
2212 : /* DoStatisticsNeedToBeFlushed() */
2213 : /************************************************************************/
2214 :
2215 3 : int OGRSQLiteTableLayer::DoStatisticsNeedToBeFlushed()
2216 : {
2217 : return bStatisticsNeedsToBeFlushed &&
2218 : poDS->IsSpatialiteDB() &&
2219 3 : OGRSQLiteIsSpatialiteLoaded();
2220 : }
2221 :
2222 : /************************************************************************/
2223 : /* ForceStatisticsToBeFlushed() */
2224 : /************************************************************************/
2225 :
2226 3 : void OGRSQLiteTableLayer::ForceStatisticsToBeFlushed()
2227 : {
2228 3 : bStatisticsNeedsToBeFlushed = TRUE;
2229 3 : }
2230 :
2231 : /************************************************************************/
2232 : /* AreStatisticsValid() */
2233 : /************************************************************************/
2234 :
2235 3 : int OGRSQLiteTableLayer::AreStatisticsValid()
2236 : {
2237 3 : return nFeatureCount >= 0;
2238 : }
2239 :
2240 : /************************************************************************/
2241 : /* LoadStatisticsSpatialite4DB() */
2242 : /************************************************************************/
2243 :
2244 4 : void OGRSQLiteTableLayer::LoadStatisticsSpatialite4DB()
2245 : {
2246 4 : CPLString osSQL;
2247 4 : CPLString osLastEvtDate;
2248 : osSQL.Printf("SELECT MAX(last_insert, last_update, last_delete) FROM geometry_columns_time WHERE "
2249 : "f_table_name = '%s' AND f_geometry_column = '%s'",
2250 4 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2251 :
2252 4 : sqlite3 *hDB = poDS->GetDB();
2253 4 : int nRowCount = 0, nColCount = 0;
2254 4 : char **papszResult = NULL;
2255 :
2256 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2257 4 : &nRowCount, &nColCount, NULL );
2258 :
2259 : /* Make it a Unix timestamp */
2260 : int nYear, nMonth, nDay, nHour, nMinute;
2261 : float fSecond;
2262 6 : if( nRowCount == 1 && nColCount == 1 && papszResult[1] != NULL &&
2263 2 : sscanf( papszResult[1], "%04d-%02d-%02dT%02d:%02d:%f",
2264 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &fSecond ) == 6 )
2265 : {
2266 2 : osLastEvtDate = papszResult[1];
2267 : }
2268 :
2269 4 : sqlite3_free_table( papszResult );
2270 4 : papszResult = NULL;
2271 :
2272 4 : if( osLastEvtDate.size() == 0 )
2273 : return;
2274 :
2275 : osSQL.Printf("SELECT last_verified, row_count, extent_min_x, extent_min_y, "
2276 : "extent_max_x, extent_max_y FROM geometry_columns_statistics WHERE "
2277 : "f_table_name = '%s' AND f_geometry_column = '%s'",
2278 2 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2279 :
2280 2 : nRowCount = 0;
2281 2 : nColCount = 0;
2282 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2283 2 : &nRowCount, &nColCount, NULL );
2284 :
2285 4 : if( nRowCount == 1 && nColCount == 6 && papszResult[6] != NULL &&
2286 2 : sscanf( papszResult[6], "%04d-%02d-%02dT%02d:%02d:%f",
2287 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &fSecond ) == 6 )
2288 : {
2289 2 : CPLString osLastVerified(papszResult[6]);
2290 :
2291 : /* Check that the information in geometry_columns_statistics is more */
2292 : /* recent than geometry_columns_time */
2293 2 : if( osLastVerified.compare(osLastEvtDate) > 0 )
2294 : {
2295 2 : char **papszRow = papszResult + 6;
2296 2 : const char* pszRowCount = papszRow[1];
2297 2 : const char* pszMinX = papszRow[2];
2298 2 : const char* pszMinY = papszRow[3];
2299 2 : const char* pszMaxX = papszRow[4];
2300 2 : const char* pszMaxY = papszRow[5];
2301 :
2302 2 : CPLDebug("SQLITE", "Loading statistics for %s", pszTableName);
2303 :
2304 2 : if( pszRowCount != NULL )
2305 : {
2306 2 : nFeatureCount = (GIntBig) CPLScanUIntBig( pszRowCount, 32 );
2307 2 : if( nFeatureCount == 0)
2308 : {
2309 0 : nFeatureCount = -1;
2310 0 : pszMinX = NULL;
2311 : }
2312 : else
2313 : {
2314 : CPLDebug("SQLite", "Layer %s feature count : " CPL_FRMT_GIB,
2315 2 : pszTableName, nFeatureCount);
2316 : }
2317 : }
2318 :
2319 2 : if( pszMinX != NULL && pszMinY != NULL &&
2320 : pszMaxX != NULL && pszMaxY != NULL )
2321 : {
2322 2 : bCachedExtentIsValid = TRUE;
2323 2 : oCachedExtent.MinX = CPLAtof(pszMinX);
2324 2 : oCachedExtent.MinY = CPLAtof(pszMinY);
2325 2 : oCachedExtent.MaxX = CPLAtof(pszMaxX);
2326 2 : oCachedExtent.MaxY = CPLAtof(pszMaxY);
2327 : CPLDebug("SQLite", "Layer %s extent : %s,%s,%s,%s",
2328 2 : pszTableName, pszMinX,pszMinY,pszMaxX,pszMaxY);
2329 : }
2330 2 : }
2331 : }
2332 :
2333 2 : sqlite3_free_table( papszResult );
2334 2 : papszResult = NULL;
2335 : }
2336 :
2337 : /************************************************************************/
2338 : /* LoadStatistics() */
2339 : /************************************************************************/
2340 :
2341 737 : void OGRSQLiteTableLayer::LoadStatistics()
2342 : {
2343 737 : if( !poDS->IsSpatialiteDB() || !OGRSQLiteIsSpatialiteLoaded() )
2344 327 : return;
2345 :
2346 410 : if( poDS->HasSpatialite4Layout() )
2347 : {
2348 4 : LoadStatisticsSpatialite4DB();
2349 4 : return;
2350 : }
2351 :
2352 406 : GIntBig nFileTimestamp = poDS->GetFileTimestamp();
2353 406 : if( nFileTimestamp == 0 )
2354 71 : return;
2355 :
2356 : /* Find the most recent event in the 'spatialite_history' that is */
2357 : /* a UpdateLayerStatistics event on all tables and geometry columns */
2358 335 : CPLString osSQL;
2359 : osSQL.Printf("SELECT MAX(timestamp) FROM spatialite_history WHERE "
2360 : "((table_name = '%s' AND geometry_column = '%s') OR "
2361 : "(table_name = 'ALL-TABLES' AND geometry_column = 'ALL-GEOMETRY-COLUMNS')) AND "
2362 : "event = 'UpdateLayerStatistics'",
2363 335 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2364 :
2365 335 : sqlite3 *hDB = poDS->GetDB();
2366 335 : int nRowCount = 0, nColCount = 0;
2367 335 : char **papszResult = NULL, *pszErrMsg = NULL;
2368 :
2369 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2370 335 : &nRowCount, &nColCount, &pszErrMsg );
2371 :
2372 : /* Make it a Unix timestamp */
2373 : int nYear, nMonth, nDay, nHour, nMinute, nSecond;
2374 : struct tm brokendown;
2375 335 : GIntBig nTS = -1;
2376 621 : if( nRowCount >= 1 && nColCount == 1 && papszResult[1] != NULL &&
2377 286 : sscanf( papszResult[1], "%04d-%02d-%02d %02d:%02d:%02d",
2378 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &nSecond ) == 6 )
2379 : {
2380 286 : brokendown.tm_year = nYear - 1900;
2381 286 : brokendown.tm_mon = nMonth - 1;
2382 286 : brokendown.tm_mday = nDay;
2383 286 : brokendown.tm_hour = nHour;
2384 286 : brokendown.tm_min = nMinute;
2385 286 : brokendown.tm_sec = nSecond;
2386 286 : nTS = CPLYMDHMSToUnixTime(&brokendown);
2387 : }
2388 :
2389 : /* If it is equal to the modified timestamp of the DB (as a file) */
2390 : /* then we can safely use the data from the layer_statistics, since */
2391 : /* it will be up-to-date */
2392 335 : if( nFileTimestamp == nTS || nFileTimestamp == nTS + 1 )
2393 : {
2394 : osSQL.Printf("SELECT row_count, extent_min_x, extent_min_y, extent_max_x, extent_max_y "
2395 : "FROM layer_statistics WHERE table_name = '%s' AND geometry_column = '%s'",
2396 286 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2397 :
2398 286 : sqlite3_free_table( papszResult );
2399 286 : papszResult = NULL;
2400 :
2401 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2402 286 : &nRowCount, &nColCount, &pszErrMsg );
2403 :
2404 286 : if( nRowCount == 1 )
2405 : {
2406 126 : char **papszRow = papszResult + 5;
2407 126 : const char* pszRowCount = papszRow[0];
2408 126 : const char* pszMinX = papszRow[1];
2409 126 : const char* pszMinY = papszRow[2];
2410 126 : const char* pszMaxX = papszRow[3];
2411 126 : const char* pszMaxY = papszRow[4];
2412 :
2413 : CPLDebug("SQLITE", "File timestamp matches layer statistics timestamp. "
2414 126 : "Loading statistics for %s", pszTableName);
2415 :
2416 126 : if( pszRowCount != NULL )
2417 : {
2418 126 : nFeatureCount = (GIntBig) CPLScanUIntBig( pszRowCount, 32 );
2419 : CPLDebug("SQLite", "Layer %s feature count : " CPL_FRMT_GIB,
2420 126 : pszTableName, nFeatureCount);
2421 : }
2422 :
2423 126 : if( pszMinX != NULL && pszMinY != NULL &&
2424 : pszMaxX != NULL && pszMaxY != NULL )
2425 : {
2426 88 : bCachedExtentIsValid = TRUE;
2427 88 : oCachedExtent.MinX = CPLAtof(pszMinX);
2428 88 : oCachedExtent.MinY = CPLAtof(pszMinY);
2429 88 : oCachedExtent.MaxX = CPLAtof(pszMaxX);
2430 88 : oCachedExtent.MaxY = CPLAtof(pszMaxY);
2431 : CPLDebug("SQLite", "Layer %s extent : %s,%s,%s,%s",
2432 88 : pszTableName, pszMinX,pszMinY,pszMaxX,pszMaxY);
2433 : }
2434 : }
2435 : }
2436 :
2437 335 : if( pszErrMsg )
2438 12 : sqlite3_free( pszErrMsg );
2439 :
2440 335 : sqlite3_free_table( papszResult );
2441 : }
2442 :
2443 : /************************************************************************/
2444 : /* SaveStatistics() */
2445 : /************************************************************************/
2446 :
2447 347 : int OGRSQLiteTableLayer::SaveStatistics()
2448 : {
2449 347 : if( !bStatisticsNeedsToBeFlushed || !poDS->IsSpatialiteDB() || !OGRSQLiteIsSpatialiteLoaded() )
2450 231 : return -1;
2451 :
2452 116 : CPLString osSQL;
2453 116 : sqlite3 *hDB = poDS->GetDB();
2454 116 : char* pszErrMsg = NULL;
2455 :
2456 116 : if( nFeatureCount >= 0 )
2457 : {
2458 : /* Update or add entry in the layer_statistics table */
2459 95 : if( bCachedExtentIsValid )
2460 : {
2461 : osSQL.Printf("INSERT OR REPLACE INTO layer_statistics (raster_layer, "
2462 : "table_name, geometry_column, row_count, extent_min_x, "
2463 : "extent_min_y, extent_max_x, extent_max_y) VALUES ("
2464 : "0, '%s', '%s', " CPL_FRMT_GIB ", %.18g, %.18g, %.18g, %.18g)",
2465 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
2466 : nFeatureCount,
2467 : oCachedExtent.MinX, oCachedExtent.MinY,
2468 76 : oCachedExtent.MaxX, oCachedExtent.MaxY);
2469 : }
2470 : else
2471 : {
2472 : osSQL.Printf("INSERT OR REPLACE INTO layer_statistics (raster_layer, "
2473 : "table_name, geometry_column, row_count, extent_min_x, "
2474 : "extent_min_y, extent_max_x, extent_max_y) VALUES ("
2475 : "0, '%s', '%s', " CPL_FRMT_GIB ", NULL, NULL, NULL, NULL)",
2476 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
2477 19 : nFeatureCount);
2478 : }
2479 : }
2480 : else
2481 : {
2482 : /* Remove any existing entry in layer_statistics if for some reason */
2483 : /* we know that it will out-of-sync */
2484 : osSQL.Printf("DELETE FROM layer_statistics WHERE "
2485 : "table_name = '%s' AND geometry_column = '%s'",
2486 21 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2487 : }
2488 :
2489 116 : int rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, &pszErrMsg );
2490 116 : if( rc != SQLITE_OK )
2491 : {
2492 13 : CPLDebug("SQLITE", "Error %s", pszErrMsg ? pszErrMsg : "unknown");
2493 13 : sqlite3_free( pszErrMsg );
2494 13 : return FALSE;
2495 : }
2496 : else
2497 103 : return TRUE;
2498 : }
2499 :
2500 : /************************************************************************/
2501 : /* SetCompressedColumns() */
2502 : /************************************************************************/
2503 :
2504 120 : void OGRSQLiteTableLayer::SetCompressedColumns( const char* pszCompressedColumns )
2505 : {
2506 : papszCompressedColumns = CSLTokenizeString2( pszCompressedColumns, ",",
2507 120 : CSLT_HONOURSTRINGS );
2508 120 : }
|
