1 : /******************************************************************************
2 : * $Id: ogrsqlitetablelayer.cpp 25129 2012-10-14 22:05:58Z 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 25129 2012-10-14 22:05:58Z rouault $");
40 :
41 : /************************************************************************/
42 : /* OGRSQLiteTableLayer() */
43 : /************************************************************************/
44 :
45 1396 : OGRSQLiteTableLayer::OGRSQLiteTableLayer( OGRSQLiteDataSource *poDSIn )
46 :
47 : {
48 1396 : poDS = poDSIn;
49 :
50 1396 : 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 1396 : bSpatialite2D = OGRSQLiteGetSpatialiteVersionNumber() < 24;
57 :
58 1396 : iNextShapeId = 0;
59 :
60 1396 : poFeatureDefn = NULL;
61 1396 : pszTableName = NULL;
62 1396 : pszEscapedTableName = NULL;
63 :
64 1396 : bHasCheckedSpatialIndexTable = FALSE;
65 1396 : bDeferedSpatialIndexCreation = FALSE;
66 :
67 1396 : hInsertStmt = NULL;
68 :
69 1396 : eGeomType = wkbUnknown;
70 1396 : bLayerDefnError = FALSE;
71 :
72 1396 : bStatisticsNeedsToBeFlushed = FALSE;
73 1396 : bCachedExtentIsValid = FALSE;
74 1396 : nFeatureCount = -1;
75 1396 : }
76 :
77 : /************************************************************************/
78 : /* ~OGRSQLiteTableLayer() */
79 : /************************************************************************/
80 :
81 1396 : OGRSQLiteTableLayer::~OGRSQLiteTableLayer()
82 :
83 : {
84 1396 : ClearStatement();
85 1396 : ClearInsertStmt();
86 :
87 1396 : CPLFree(pszTableName);
88 1396 : CPLFree(pszEscapedTableName);
89 1396 : }
90 :
91 : /************************************************************************/
92 : /* CreateSpatialIndexIfNecessary() */
93 : /************************************************************************/
94 :
95 1374 : void OGRSQLiteTableLayer::CreateSpatialIndexIfNecessary()
96 : {
97 1374 : if( bDeferedSpatialIndexCreation )
98 : {
99 70 : CreateSpatialIndex();
100 : }
101 1374 : }
102 :
103 : /************************************************************************/
104 : /* ClearInsertStmt() */
105 : /************************************************************************/
106 :
107 1704 : void OGRSQLiteTableLayer::ClearInsertStmt()
108 : {
109 1704 : if( hInsertStmt != NULL )
110 : {
111 224 : sqlite3_finalize( hInsertStmt );
112 224 : hInsertStmt = NULL;
113 : }
114 1704 : osLastInsertStmt = "";
115 1704 : }
116 :
117 : /************************************************************************/
118 : /* Initialize() */
119 : /************************************************************************/
120 :
121 1396 : 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 1396 : sqlite3 *hDB = poDS->GetDB();
135 :
136 1396 : if( pszGeomCol == NULL )
137 896 : osGeomColumn = "";
138 : else
139 500 : osGeomColumn = pszGeomCol;
140 :
141 1396 : if( pszGeomFormat )
142 : {
143 500 : if ( EQUAL(pszGeomFormat, "WKT") )
144 18 : eGeomFormat = OSGF_WKT;
145 482 : else if ( EQUAL(pszGeomFormat,"WKB") )
146 132 : eGeomFormat = OSGF_WKB;
147 350 : else if ( EQUAL(pszGeomFormat,"FGF") )
148 2 : eGeomFormat = OSGF_FGF;
149 348 : else if( EQUAL(pszGeomFormat,"SpatiaLite") )
150 348 : eGeomFormat = OSGF_SpatiaLite;
151 : }
152 :
153 1396 : CPLFree( pszFIDColumn );
154 1396 : pszFIDColumn = NULL;
155 :
156 1396 : if( nSRSId == UNINITIALIZED_SRID )
157 1054 : nSRSId = poDS->GetUndefinedSRID();
158 :
159 1396 : this->poSRS = poSRS;
160 1396 : this->nSRSId = nSRSId;
161 1396 : this->bHasSpatialIndex = bHasSpatialIndex;
162 1396 : this->bHasM = bHasM;
163 1396 : this->bIsVirtualShape = bIsVirtualShapeIn;
164 1396 : this->pszTableName = CPLStrdup(pszTableName);
165 1396 : this->eGeomType = eGeomType;
166 :
167 1396 : if( bMustIncludeGeomColName )
168 6 : osLayerName.Printf("%s(%s)", pszTableName, osGeomColumn.c_str());
169 : else
170 1390 : osLayerName = pszTableName;
171 :
172 1396 : pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszTableName));
173 :
174 1396 : sqlite3_stmt *hColStmt = NULL;
175 : const char *pszSQL;
176 :
177 1744 : 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 1396 : if( poSRS )
203 282 : poSRS->Reference();
204 :
205 1396 : LoadStatistics();
206 :
207 1396 : return CE_None;
208 : }
209 :
210 : /************************************************************************/
211 : /* GetName() */
212 : /************************************************************************/
213 :
214 1870 : const char* OGRSQLiteTableLayer::GetName()
215 : {
216 1870 : return osLayerName.c_str();
217 : }
218 :
219 : /************************************************************************/
220 : /* EstablishFeatureDefn() */
221 : /************************************************************************/
222 :
223 733 : CPLErr OGRSQLiteTableLayer::EstablishFeatureDefn()
224 : {
225 733 : sqlite3 *hDB = poDS->GetDB();
226 : int rc;
227 : const char *pszSQL;
228 733 : 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 733 : pszEscapedTableName );
236 :
237 733 : rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
238 733 : if( rc != SQLITE_OK )
239 : {
240 : CPLError( CE_Failure, CPLE_AppDefined,
241 : "Unable to query table %s for column definitions : %s.",
242 0 : pszTableName, sqlite3_errmsg(hDB) );
243 :
244 0 : return CE_Failure;
245 : }
246 :
247 733 : rc = sqlite3_step( hColStmt );
248 733 : 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 733 : pszFIDColumn = CPLStrdup(OGRSQLiteParamsUnquote(sqlite3_column_name( hColStmt, 0 )));
268 :
269 : /* -------------------------------------------------------------------- */
270 : /* Collect the rest of the fields. */
271 : /* -------------------------------------------------------------------- */
272 733 : BuildFeatureDefn( osLayerName, hColStmt, poDS->GetGeomColsForTable(pszTableName) );
273 733 : sqlite3_finalize( hColStmt );
274 :
275 : /* -------------------------------------------------------------------- */
276 : /* Set the geometry type if we know it. */
277 : /* -------------------------------------------------------------------- */
278 733 : if( eGeomType != wkbUnknown )
279 198 : poFeatureDefn->SetGeomType( eGeomType );
280 :
281 733 : return CE_None;
282 : }
283 :
284 : /************************************************************************/
285 : /* GetLayerDefn() */
286 : /************************************************************************/
287 :
288 4055 : OGRFeatureDefn* OGRSQLiteTableLayer::GetLayerDefn()
289 : {
290 4055 : if (poFeatureDefn)
291 3322 : return poFeatureDefn;
292 :
293 733 : EstablishFeatureDefn();
294 :
295 733 : if (poFeatureDefn == NULL)
296 : {
297 0 : bLayerDefnError = TRUE;
298 :
299 0 : poFeatureDefn = new OGRFeatureDefn( osLayerName );
300 0 : poFeatureDefn->Reference();
301 : }
302 :
303 733 : return poFeatureDefn;
304 : }
305 :
306 : /************************************************************************/
307 : /* ResetStatement() */
308 : /************************************************************************/
309 :
310 300 : OGRErr OGRSQLiteTableLayer::ResetStatement()
311 :
312 : {
313 : int rc;
314 300 : CPLString osSQL;
315 :
316 300 : ClearStatement();
317 :
318 300 : iNextShapeId = 0;
319 :
320 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' %s",
321 : pszEscapedTableName,
322 300 : 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 300 : &hStmt, NULL );
331 : //#endif
332 :
333 300 : if( rc == SQLITE_OK )
334 : {
335 298 : 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 1121 : OGRFeature *OGRSQLiteTableLayer::GetNextFeature()
352 :
353 : {
354 1121 : if (HasLayerDefnError())
355 0 : return NULL;
356 :
357 1121 : 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 89 : OGRErr OGRSQLiteTableLayer::SetAttributeFilter( const char *pszQuery )
420 :
421 : {
422 89 : if( pszQuery == NULL )
423 43 : osQuery = "";
424 : else
425 46 : osQuery = pszQuery;
426 :
427 89 : BuildWhere();
428 :
429 89 : ResetReading();
430 :
431 89 : 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 135 : CPLString OGRSQLiteTableLayer::GetSpatialWhere(OGRGeometry* poFilterGeom)
494 : {
495 135 : CPLString osSpatialWHERE;
496 :
497 135 : if( !poDS->IsSpatialiteDB() )
498 87 : 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 118 : void OGRSQLiteTableLayer::BuildWhere()
543 :
544 : {
545 118 : osWHERE = "";
546 :
547 118 : CPLString osSpatialWHERE = GetSpatialWhere(m_poFilterGeom);
548 236 : if (osSpatialWHERE.size() != 0)
549 : {
550 11 : osWHERE = "WHERE ";
551 11 : osWHERE += osSpatialWHERE;
552 : }
553 :
554 118 : if( osQuery.size() > 0 )
555 : {
556 42 : if( osWHERE.size() == 0 )
557 : {
558 42 : osWHERE = "WHERE ";
559 42 : osWHERE += osQuery;
560 : }
561 : else
562 : {
563 0 : osWHERE += " AND (";
564 0 : osWHERE += osQuery;
565 0 : osWHERE += ")";
566 : }
567 118 : }
568 118 : }
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 3659 : CPLString OGRSQLiteFieldDefnToSQliteFieldDefn( OGRFieldDefn* poFieldDefn )
769 : {
770 3659 : switch( poFieldDefn->GetType() )
771 : {
772 799 : case OFTInteger: return "INTEGER"; break;
773 443 : case OFTReal : return "FLOAT"; break;
774 382 : case OFTBinary : return "BLOB"; break;
775 : case OFTString :
776 : {
777 913 : if( poFieldDefn->GetWidth() > 0 )
778 118 : return CPLSPrintf("VARCHAR(%d)", poFieldDefn->GetWidth());
779 : else
780 795 : return "VARCHAR";
781 : break;
782 : }
783 374 : case OFTDateTime: return "TIMESTAMP"; break;
784 374 : case OFTDate : return "DATE"; break;
785 374 : case OFTTime : return "TIME"; break;
786 0 : default: return"VARCHAR"; break;
787 : }
788 : }
789 :
790 : /************************************************************************/
791 : /* CreateField() */
792 : /************************************************************************/
793 :
794 67 : OGRErr OGRSQLiteTableLayer::CreateField( OGRFieldDefn *poFieldIn,
795 : int bApproxOK )
796 :
797 : {
798 67 : OGRFieldDefn oField( poFieldIn );
799 :
800 67 : if (HasLayerDefnError())
801 0 : return OGRERR_FAILURE;
802 :
803 67 : ResetReading();
804 :
805 67 : if (!poDS->GetUpdate())
806 : {
807 : CPLError( CE_Failure, CPLE_NotSupported,
808 : UNSUPPORTED_OP_READ_ONLY,
809 0 : "CreateField");
810 0 : return OGRERR_FAILURE;
811 : }
812 :
813 67 : ClearInsertStmt();
814 :
815 : /* -------------------------------------------------------------------- */
816 : /* Do we want to "launder" the column names into SQLite */
817 : /* friendly format? */
818 : /* -------------------------------------------------------------------- */
819 67 : if( bLaunderColumnNames )
820 : {
821 65 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
822 :
823 65 : oField.SetName( pszSafeName );
824 65 : CPLFree( pszSafeName );
825 : }
826 :
827 :
828 67 : if( (oField.GetType() == OFTTime || oField.GetType() == OFTDate ||
829 : oField.GetType() == OFTDateTime) &&
830 : !(CSLTestBoolean(
831 : CPLGetConfigOption("OGR_SQLITE_ENABLE_DATETIME", "YES"))) )
832 : {
833 0 : oField.SetType(OFTString);
834 : }
835 :
836 : /* ADD COLUMN only avaliable since sqlite 3.1.3 */
837 67 : if (CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_USE_ADD_COLUMN", "YES")) &&
838 : sqlite3_libversion_number() > 3 * 1000000 + 1 * 1000 + 3)
839 : {
840 : int rc;
841 67 : char *pszErrMsg = NULL;
842 67 : sqlite3 *hDB = poDS->GetDB();
843 67 : CPLString osCommand;
844 :
845 67 : CPLString osFieldType(OGRSQLiteFieldDefnToSQliteFieldDefn(&oField));
846 : osCommand.Printf("ALTER TABLE '%s' ADD COLUMN '%s' %s",
847 : pszEscapedTableName,
848 : OGRSQLiteEscape(oField.GetNameRef()).c_str(),
849 67 : osFieldType.c_str());
850 :
851 : #ifdef DEBUG
852 67 : CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
853 : #endif
854 :
855 67 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
856 67 : if( rc != SQLITE_OK )
857 : {
858 : CPLError( CE_Failure, CPLE_AppDefined,
859 : "Failed to add field %s to table %s:\n %s",
860 : oField.GetNameRef(), poFeatureDefn->GetName(),
861 0 : pszErrMsg );
862 0 : sqlite3_free( pszErrMsg );
863 0 : return OGRERR_FAILURE;
864 0 : }
865 : }
866 : else
867 : {
868 0 : OGRErr eErr = AddColumnAncientMethod(oField);
869 0 : if (eErr != OGRERR_NONE)
870 0 : return eErr;
871 : }
872 :
873 : /* -------------------------------------------------------------------- */
874 : /* Add the field to the OGRFeatureDefn. */
875 : /* -------------------------------------------------------------------- */
876 : int iNewField;
877 67 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
878 :
879 67 : if( poFeatureDefn->GetGeomType() != wkbNone )
880 : {
881 67 : iNextOrdinal++;
882 : }
883 :
884 156 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
885 : {
886 89 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
887 :
888 : // we already added OGC_FID so don't do it again
889 89 : if( EQUAL(poFldDefn->GetNameRef(),"OGC_FID") )
890 0 : continue;
891 :
892 89 : iNextOrdinal++;
893 : }
894 :
895 67 : poFeatureDefn->AddFieldDefn( &oField );
896 :
897 67 : iNewField = poFeatureDefn->GetFieldCount() - 1;
898 : panFieldOrdinals = (int *)
899 67 : CPLRealloc(panFieldOrdinals, (iNewField+1) * sizeof(int) );
900 67 : panFieldOrdinals[iNewField] = iNextOrdinal;
901 :
902 67 : return OGRERR_NONE;
903 : }
904 :
905 : /************************************************************************/
906 : /* InitFieldListForRecrerate() */
907 : /************************************************************************/
908 :
909 23 : void OGRSQLiteTableLayer::InitFieldListForRecrerate(char* & pszNewFieldList,
910 : char* & pszFieldListForSelect,
911 : int nExtraSpace)
912 : {
913 23 : int iField, nFieldListLen = 100 + 2 * nExtraSpace;
914 :
915 118 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
916 : {
917 : nFieldListLen +=
918 95 : 2 * strlen(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()) + 50;
919 : }
920 :
921 23 : nFieldListLen += 50 + (pszFIDColumn ? 2 * strlen(pszFIDColumn) : strlen("OGC_FID"));
922 23 : if( poFeatureDefn->GetGeomType() != wkbNone )
923 : {
924 23 : nFieldListLen += 50 + 2 * strlen(osGeomColumn);
925 : }
926 :
927 23 : pszFieldListForSelect = (char *) CPLCalloc(1,nFieldListLen);
928 23 : pszNewFieldList = (char *) CPLCalloc(1,nFieldListLen);
929 :
930 : /* -------------------------------------------------------------------- */
931 : /* Build list of old fields, and the list of new fields. */
932 : /* -------------------------------------------------------------------- */
933 23 : sprintf( pszFieldListForSelect, "\"%s\"", pszFIDColumn ? OGRSQLiteEscapeName(pszFIDColumn).c_str() : "OGC_FID" );
934 23 : sprintf( pszNewFieldList, "\"%s\" INTEGER PRIMARY KEY",pszFIDColumn ? OGRSQLiteEscapeName(pszFIDColumn).c_str() : "OGC_FID" );
935 :
936 23 : if( poFeatureDefn->GetGeomType() != wkbNone )
937 : {
938 23 : strcat( pszFieldListForSelect, "," );
939 23 : strcat( pszNewFieldList, "," );
940 :
941 23 : strcat( pszFieldListForSelect, "\"");
942 23 : strcat( pszFieldListForSelect, OGRSQLiteEscapeName(osGeomColumn) );
943 23 : strcat( pszFieldListForSelect, "\"");
944 :
945 23 : strcat( pszNewFieldList, "\"");
946 46 : strcat( pszNewFieldList, OGRSQLiteEscapeName(osGeomColumn) );
947 23 : strcat( pszNewFieldList, "\"");
948 :
949 23 : if ( eGeomFormat == OSGF_WKT )
950 0 : strcat( pszNewFieldList, " VARCHAR" );
951 : else
952 23 : strcat( pszNewFieldList, " BLOB" );
953 : }
954 23 : }
955 :
956 : /************************************************************************/
957 : /* AddColumnAncientMethod() */
958 : /************************************************************************/
959 :
960 0 : OGRErr OGRSQLiteTableLayer::AddColumnAncientMethod( OGRFieldDefn& oField)
961 : {
962 :
963 : /* -------------------------------------------------------------------- */
964 : /* How much space do we need for the list of fields. */
965 : /* -------------------------------------------------------------------- */
966 : int iField;
967 : char *pszOldFieldList, *pszNewFieldList;
968 :
969 : InitFieldListForRecrerate(pszNewFieldList, pszOldFieldList,
970 0 : strlen( oField.GetNameRef() ));
971 :
972 : /* -------------------------------------------------------------------- */
973 : /* Build list of old fields, and the list of new fields. */
974 : /* -------------------------------------------------------------------- */
975 :
976 0 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
977 :
978 0 : if( poFeatureDefn->GetGeomType() != wkbNone )
979 : {
980 0 : iNextOrdinal++;
981 : }
982 :
983 0 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
984 : {
985 0 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
986 :
987 : // we already added OGC_FID so don't do it again
988 0 : if( EQUAL(poFldDefn->GetNameRef(),pszFIDColumn ? pszFIDColumn : "OGC_FID") )
989 0 : continue;
990 :
991 : sprintf( pszOldFieldList+strlen(pszOldFieldList),
992 0 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
993 :
994 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
995 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
996 0 : OGRSQLiteFieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
997 :
998 0 : iNextOrdinal++;
999 : }
1000 :
1001 : /* -------------------------------------------------------------------- */
1002 : /* Add the new field. */
1003 : /* -------------------------------------------------------------------- */
1004 :
1005 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1006 : ", '%s' %s", OGRSQLiteEscape(oField.GetNameRef()).c_str(),
1007 0 : OGRSQLiteFieldDefnToSQliteFieldDefn(&oField).c_str() );
1008 :
1009 : /* ==================================================================== */
1010 : /* Backup, destroy, recreate and repopulate the table. SQLite */
1011 : /* has no ALTER TABLE so we have to do all this to add a */
1012 : /* column. */
1013 : /* ==================================================================== */
1014 :
1015 : /* -------------------------------------------------------------------- */
1016 : /* Do this all in a transaction. */
1017 : /* -------------------------------------------------------------------- */
1018 0 : poDS->SoftStartTransaction();
1019 :
1020 : /* -------------------------------------------------------------------- */
1021 : /* Save existing related triggers and index */
1022 : /* -------------------------------------------------------------------- */
1023 : int rc;
1024 0 : char *pszErrMsg = NULL;
1025 0 : sqlite3 *hDB = poDS->GetDB();
1026 0 : CPLString osSQL;
1027 :
1028 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
1029 0 : pszEscapedTableName );
1030 :
1031 : int nRowTriggerIndexCount, nColTriggerIndexCount;
1032 0 : char **papszTriggerIndexResult = NULL;
1033 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
1034 0 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
1035 :
1036 : /* -------------------------------------------------------------------- */
1037 : /* Make a backup of the table. */
1038 : /* -------------------------------------------------------------------- */
1039 :
1040 0 : if( rc == SQLITE_OK )
1041 : rc = sqlite3_exec( hDB,
1042 : CPLSPrintf( "CREATE TEMPORARY TABLE t1_back(%s)",
1043 : pszOldFieldList ),
1044 0 : NULL, NULL, &pszErrMsg );
1045 :
1046 0 : if( rc == SQLITE_OK )
1047 : rc = sqlite3_exec( hDB,
1048 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
1049 : pszOldFieldList,
1050 : pszEscapedTableName ),
1051 0 : NULL, NULL, &pszErrMsg );
1052 :
1053 :
1054 : /* -------------------------------------------------------------------- */
1055 : /* Drop the original table, and recreate with new field. */
1056 : /* -------------------------------------------------------------------- */
1057 0 : if( rc == SQLITE_OK )
1058 : rc = sqlite3_exec( hDB,
1059 : CPLSPrintf( "DROP TABLE '%s'",
1060 : pszEscapedTableName ),
1061 0 : NULL, NULL, &pszErrMsg );
1062 :
1063 0 : if( rc == SQLITE_OK )
1064 : {
1065 : const char *pszCmd =
1066 : CPLSPrintf( "CREATE TABLE '%s' (%s)",
1067 : pszEscapedTableName,
1068 0 : pszNewFieldList );
1069 : rc = sqlite3_exec( hDB, pszCmd,
1070 0 : NULL, NULL, &pszErrMsg );
1071 :
1072 0 : CPLDebug( "OGR_SQLITE", "exec(%s)", pszCmd );
1073 : }
1074 :
1075 : /* -------------------------------------------------------------------- */
1076 : /* Copy backup field values into new table. */
1077 : /* -------------------------------------------------------------------- */
1078 :
1079 0 : if( rc == SQLITE_OK )
1080 : rc = sqlite3_exec( hDB,
1081 : CPLSPrintf( "INSERT INTO '%s' SELECT %s, NULL FROM t1_back",
1082 : pszEscapedTableName,
1083 : pszOldFieldList ),
1084 0 : NULL, NULL, &pszErrMsg );
1085 :
1086 0 : CPLFree( pszOldFieldList );
1087 0 : CPLFree( pszNewFieldList );
1088 :
1089 : /* -------------------------------------------------------------------- */
1090 : /* Cleanup backup table. */
1091 : /* -------------------------------------------------------------------- */
1092 :
1093 0 : if( rc == SQLITE_OK )
1094 : rc = sqlite3_exec( hDB,
1095 : CPLSPrintf( "DROP TABLE t1_back" ),
1096 0 : NULL, NULL, &pszErrMsg );
1097 :
1098 : /* -------------------------------------------------------------------- */
1099 : /* Recreate existing related tables, triggers and index */
1100 : /* -------------------------------------------------------------------- */
1101 :
1102 0 : if( rc == SQLITE_OK )
1103 : {
1104 : int i;
1105 :
1106 0 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
1107 : {
1108 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
1109 : rc = sqlite3_exec( hDB,
1110 0 : papszTriggerIndexResult[i],
1111 0 : NULL, NULL, &pszErrMsg );
1112 : }
1113 : }
1114 :
1115 : /* -------------------------------------------------------------------- */
1116 : /* COMMIT on success or ROLLBACK on failuire. */
1117 : /* -------------------------------------------------------------------- */
1118 :
1119 0 : sqlite3_free_table( papszTriggerIndexResult );
1120 :
1121 0 : if( rc == SQLITE_OK )
1122 : {
1123 0 : poDS->SoftCommit();
1124 : }
1125 : else
1126 : {
1127 : CPLError( CE_Failure, CPLE_AppDefined,
1128 : "Failed to add field %s to table %s:\n %s",
1129 : oField.GetNameRef(), poFeatureDefn->GetName(),
1130 0 : pszErrMsg );
1131 0 : sqlite3_free( pszErrMsg );
1132 :
1133 0 : poDS->SoftRollback();
1134 :
1135 0 : return OGRERR_FAILURE;
1136 : }
1137 :
1138 0 : return OGRERR_NONE;
1139 : }
1140 :
1141 : /************************************************************************/
1142 : /* RecreateTable() */
1143 : /************************************************************************/
1144 :
1145 23 : OGRErr OGRSQLiteTableLayer::RecreateTable(const char* pszFieldListForSelect,
1146 : const char* pszNewFieldList,
1147 : const char* pszGenericErrorMessage)
1148 : {
1149 : /* -------------------------------------------------------------------- */
1150 : /* Do this all in a transaction. */
1151 : /* -------------------------------------------------------------------- */
1152 23 : poDS->SoftStartTransaction();
1153 :
1154 : /* -------------------------------------------------------------------- */
1155 : /* Save existing related triggers and index */
1156 : /* -------------------------------------------------------------------- */
1157 : int rc;
1158 23 : char *pszErrMsg = NULL;
1159 23 : sqlite3 *hDB = poDS->GetDB();
1160 23 : CPLString osSQL;
1161 :
1162 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
1163 23 : pszEscapedTableName );
1164 :
1165 : int nRowTriggerIndexCount, nColTriggerIndexCount;
1166 23 : char **papszTriggerIndexResult = NULL;
1167 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
1168 23 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
1169 :
1170 : /* -------------------------------------------------------------------- */
1171 : /* Make a backup of the table. */
1172 : /* -------------------------------------------------------------------- */
1173 :
1174 23 : if( rc == SQLITE_OK )
1175 : rc = sqlite3_exec( hDB,
1176 : CPLSPrintf( "CREATE TABLE t1_back(%s)",
1177 : pszNewFieldList ),
1178 23 : NULL, NULL, &pszErrMsg );
1179 :
1180 23 : if( rc == SQLITE_OK )
1181 : rc = sqlite3_exec( hDB,
1182 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
1183 : pszFieldListForSelect,
1184 : pszEscapedTableName ),
1185 23 : NULL, NULL, &pszErrMsg );
1186 :
1187 :
1188 : /* -------------------------------------------------------------------- */
1189 : /* Drop the original table */
1190 : /* -------------------------------------------------------------------- */
1191 23 : if( rc == SQLITE_OK )
1192 : rc = sqlite3_exec( hDB,
1193 : CPLSPrintf( "DROP TABLE '%s'",
1194 : pszEscapedTableName ),
1195 23 : NULL, NULL, &pszErrMsg );
1196 :
1197 : /* -------------------------------------------------------------------- */
1198 : /* Rename backup table as new table */
1199 : /* -------------------------------------------------------------------- */
1200 23 : if( rc == SQLITE_OK )
1201 : {
1202 : const char *pszCmd =
1203 : CPLSPrintf( "ALTER TABLE t1_back RENAME TO '%s'",
1204 23 : pszEscapedTableName);
1205 : rc = sqlite3_exec( hDB, pszCmd,
1206 23 : NULL, NULL, &pszErrMsg );
1207 : }
1208 :
1209 : /* -------------------------------------------------------------------- */
1210 : /* Recreate existing related tables, triggers and index */
1211 : /* -------------------------------------------------------------------- */
1212 :
1213 23 : if( rc == SQLITE_OK )
1214 : {
1215 : int i;
1216 :
1217 23 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
1218 : {
1219 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
1220 : rc = sqlite3_exec( hDB,
1221 0 : papszTriggerIndexResult[i],
1222 0 : NULL, NULL, &pszErrMsg );
1223 : }
1224 : }
1225 :
1226 : /* -------------------------------------------------------------------- */
1227 : /* COMMIT on success or ROLLBACK on failuire. */
1228 : /* -------------------------------------------------------------------- */
1229 :
1230 23 : sqlite3_free_table( papszTriggerIndexResult );
1231 :
1232 23 : if( rc == SQLITE_OK )
1233 : {
1234 23 : poDS->SoftCommit();
1235 :
1236 23 : return OGRERR_NONE;
1237 : }
1238 : else
1239 : {
1240 : CPLError( CE_Failure, CPLE_AppDefined,
1241 : "%s:\n %s",
1242 : pszGenericErrorMessage,
1243 0 : pszErrMsg );
1244 0 : sqlite3_free( pszErrMsg );
1245 :
1246 0 : poDS->SoftRollback();
1247 :
1248 0 : return OGRERR_FAILURE;
1249 0 : }
1250 : }
1251 :
1252 : /************************************************************************/
1253 : /* DeleteField() */
1254 : /************************************************************************/
1255 :
1256 8 : OGRErr OGRSQLiteTableLayer::DeleteField( int iFieldToDelete )
1257 : {
1258 8 : if (HasLayerDefnError())
1259 0 : return OGRERR_FAILURE;
1260 :
1261 8 : if (!poDS->GetUpdate())
1262 : {
1263 : CPLError( CE_Failure, CPLE_NotSupported,
1264 : UNSUPPORTED_OP_READ_ONLY,
1265 0 : "DeleteField");
1266 0 : return OGRERR_FAILURE;
1267 : }
1268 :
1269 8 : if (iFieldToDelete < 0 || iFieldToDelete >= poFeatureDefn->GetFieldCount())
1270 : {
1271 : CPLError( CE_Failure, CPLE_NotSupported,
1272 2 : "Invalid field index");
1273 2 : return OGRERR_FAILURE;
1274 : }
1275 :
1276 6 : ResetReading();
1277 :
1278 : /* -------------------------------------------------------------------- */
1279 : /* Build list of old fields, and the list of new fields. */
1280 : /* -------------------------------------------------------------------- */
1281 : int iField;
1282 : char *pszNewFieldList, *pszFieldListForSelect;
1283 6 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1284 :
1285 26 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1286 : {
1287 20 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1288 :
1289 20 : if (iField == iFieldToDelete)
1290 6 : continue;
1291 :
1292 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1293 14 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1294 :
1295 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1296 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1297 28 : OGRSQLiteFieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1298 : }
1299 :
1300 : /* -------------------------------------------------------------------- */
1301 : /* Recreate table. */
1302 : /* -------------------------------------------------------------------- */
1303 6 : CPLString osErrorMsg;
1304 : osErrorMsg.Printf("Failed to remove field %s from table %s",
1305 : poFeatureDefn->GetFieldDefn(iFieldToDelete)->GetNameRef(),
1306 6 : poFeatureDefn->GetName());
1307 :
1308 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1309 : pszNewFieldList,
1310 6 : osErrorMsg.c_str());
1311 :
1312 6 : CPLFree( pszFieldListForSelect );
1313 6 : CPLFree( pszNewFieldList );
1314 :
1315 6 : if (eErr != OGRERR_NONE)
1316 0 : return eErr;
1317 :
1318 : /* -------------------------------------------------------------------- */
1319 : /* Finish */
1320 : /* -------------------------------------------------------------------- */
1321 6 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
1322 :
1323 6 : if( poFeatureDefn->GetGeomType() != wkbNone )
1324 : {
1325 6 : iNextOrdinal++;
1326 : }
1327 :
1328 6 : int iNewField = 0;
1329 26 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1330 : {
1331 20 : if (iField == iFieldToDelete)
1332 6 : continue;
1333 :
1334 14 : panFieldOrdinals[iNewField ++] = iNextOrdinal++;
1335 : }
1336 :
1337 6 : return poFeatureDefn->DeleteFieldDefn( iFieldToDelete );
1338 : }
1339 :
1340 : /************************************************************************/
1341 : /* AlterFieldDefn() */
1342 : /************************************************************************/
1343 :
1344 9 : OGRErr OGRSQLiteTableLayer::AlterFieldDefn( int iFieldToAlter, OGRFieldDefn* poNewFieldDefn, int nFlags )
1345 : {
1346 9 : if (HasLayerDefnError())
1347 0 : return OGRERR_FAILURE;
1348 :
1349 9 : if (!poDS->GetUpdate())
1350 : {
1351 : CPLError( CE_Failure, CPLE_NotSupported,
1352 : UNSUPPORTED_OP_READ_ONLY,
1353 0 : "AlterFieldDefn");
1354 0 : return OGRERR_FAILURE;
1355 : }
1356 :
1357 9 : if (iFieldToAlter < 0 || iFieldToAlter >= poFeatureDefn->GetFieldCount())
1358 : {
1359 : CPLError( CE_Failure, CPLE_NotSupported,
1360 2 : "Invalid field index");
1361 2 : return OGRERR_FAILURE;
1362 : }
1363 :
1364 7 : ClearInsertStmt();
1365 7 : ResetReading();
1366 :
1367 : /* -------------------------------------------------------------------- */
1368 : /* Build list of old fields, and the list of new fields. */
1369 : /* -------------------------------------------------------------------- */
1370 : int iField;
1371 : char *pszNewFieldList, *pszFieldListForSelect;
1372 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect,
1373 7 : strlen(poNewFieldDefn->GetNameRef()));
1374 :
1375 40 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1376 : {
1377 33 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1378 :
1379 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1380 33 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1381 :
1382 33 : if (iField == iFieldToAlter)
1383 : {
1384 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1385 : ", '%s' %s",
1386 : OGRSQLiteEscape((nFlags & ALTER_NAME_FLAG) ? poNewFieldDefn->GetNameRef() :
1387 : poFldDefn->GetNameRef()).c_str(),
1388 : OGRSQLiteFieldDefnToSQliteFieldDefn((nFlags & ALTER_TYPE_FLAG) ?
1389 7 : poNewFieldDefn : poFldDefn).c_str() );
1390 : }
1391 : else
1392 : {
1393 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1394 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1395 26 : OGRSQLiteFieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1396 : }
1397 : }
1398 :
1399 : /* -------------------------------------------------------------------- */
1400 : /* Recreate table. */
1401 : /* -------------------------------------------------------------------- */
1402 7 : CPLString osErrorMsg;
1403 : osErrorMsg.Printf("Failed to alter field %s from table %s",
1404 : poFeatureDefn->GetFieldDefn(iFieldToAlter)->GetNameRef(),
1405 7 : poFeatureDefn->GetName());
1406 :
1407 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1408 : pszNewFieldList,
1409 7 : osErrorMsg.c_str());
1410 :
1411 7 : CPLFree( pszFieldListForSelect );
1412 7 : CPLFree( pszNewFieldList );
1413 :
1414 7 : if (eErr != OGRERR_NONE)
1415 0 : return eErr;
1416 :
1417 : /* -------------------------------------------------------------------- */
1418 : /* Finish */
1419 : /* -------------------------------------------------------------------- */
1420 :
1421 7 : OGRFieldDefn* poFieldDefn = poFeatureDefn->GetFieldDefn(iFieldToAlter);
1422 :
1423 7 : if (nFlags & ALTER_TYPE_FLAG)
1424 7 : poFieldDefn->SetType(poNewFieldDefn->GetType());
1425 7 : if (nFlags & ALTER_NAME_FLAG)
1426 7 : poFieldDefn->SetName(poNewFieldDefn->GetNameRef());
1427 7 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
1428 : {
1429 7 : poFieldDefn->SetWidth(poNewFieldDefn->GetWidth());
1430 7 : poFieldDefn->SetPrecision(poNewFieldDefn->GetPrecision());
1431 : }
1432 7 : return OGRERR_NONE;
1433 : }
1434 :
1435 : /************************************************************************/
1436 : /* ReorderFields() */
1437 : /************************************************************************/
1438 :
1439 12 : OGRErr OGRSQLiteTableLayer::ReorderFields( int* panMap )
1440 : {
1441 12 : if (HasLayerDefnError())
1442 0 : return OGRERR_FAILURE;
1443 :
1444 12 : if (!poDS->GetUpdate())
1445 : {
1446 : CPLError( CE_Failure, CPLE_NotSupported,
1447 : UNSUPPORTED_OP_READ_ONLY,
1448 0 : "ReorderFields");
1449 0 : return OGRERR_FAILURE;
1450 : }
1451 :
1452 12 : if (poFeatureDefn->GetFieldCount() == 0)
1453 1 : return OGRERR_NONE;
1454 :
1455 11 : OGRErr eErr = OGRCheckPermutation(panMap, poFeatureDefn->GetFieldCount());
1456 11 : if (eErr != OGRERR_NONE)
1457 1 : return eErr;
1458 :
1459 10 : ClearInsertStmt();
1460 10 : ResetReading();
1461 :
1462 : /* -------------------------------------------------------------------- */
1463 : /* Build list of old fields, and the list of new fields. */
1464 : /* -------------------------------------------------------------------- */
1465 : int iField;
1466 : char *pszNewFieldList, *pszFieldListForSelect;
1467 10 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1468 :
1469 104 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1470 : {
1471 42 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(panMap[iField]);
1472 :
1473 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1474 42 : ", \"%s\"", OGRSQLiteEscapeName(poFldDefn->GetNameRef()).c_str() );
1475 :
1476 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1477 : ", '%s' %s", OGRSQLiteEscape(poFldDefn->GetNameRef()).c_str(),
1478 84 : OGRSQLiteFieldDefnToSQliteFieldDefn(poFldDefn).c_str() );
1479 : }
1480 :
1481 : /* -------------------------------------------------------------------- */
1482 : /* Recreate table. */
1483 : /* -------------------------------------------------------------------- */
1484 10 : CPLString osErrorMsg;
1485 : osErrorMsg.Printf("Failed to reorder fields from table %s",
1486 10 : poFeatureDefn->GetName());
1487 :
1488 : eErr = RecreateTable(pszFieldListForSelect,
1489 : pszNewFieldList,
1490 10 : osErrorMsg.c_str());
1491 :
1492 10 : CPLFree( pszFieldListForSelect );
1493 10 : CPLFree( pszNewFieldList );
1494 :
1495 10 : if (eErr != OGRERR_NONE)
1496 0 : return eErr;
1497 :
1498 : /* -------------------------------------------------------------------- */
1499 : /* Finish */
1500 : /* -------------------------------------------------------------------- */
1501 :
1502 10 : return poFeatureDefn->ReorderFieldDefns( panMap );
1503 : }
1504 :
1505 : /************************************************************************/
1506 : /* BindValues() */
1507 : /************************************************************************/
1508 :
1509 : /* the bBindNullValues is set to TRUE by SetFeature() for UPDATE statements, */
1510 : /* and to FALSE by CreateFeature() for INSERT statements; */
1511 :
1512 456 : OGRErr OGRSQLiteTableLayer::BindValues( OGRFeature *poFeature,
1513 : sqlite3_stmt* hStmt,
1514 : int bBindNullValues )
1515 : {
1516 : int rc;
1517 456 : sqlite3 *hDB = poDS->GetDB();
1518 :
1519 : /* -------------------------------------------------------------------- */
1520 : /* Bind the geometry */
1521 : /* -------------------------------------------------------------------- */
1522 456 : int nBindField = 1;
1523 :
1524 456 : if( osGeomColumn.size() != 0 &&
1525 : eGeomFormat != OSGF_FGF )
1526 : {
1527 429 : OGRGeometry* poGeom = poFeature->GetGeometryRef();
1528 429 : if ( poGeom != NULL )
1529 : {
1530 405 : if ( eGeomFormat == OSGF_WKT )
1531 : {
1532 2 : char *pszWKT = NULL;
1533 2 : poGeom->exportToWkt( &pszWKT );
1534 2 : rc = sqlite3_bind_text( hStmt, nBindField++, pszWKT, -1, CPLFree );
1535 : }
1536 403 : else if( eGeomFormat == OSGF_WKB )
1537 : {
1538 170 : int nWKBLen = poGeom->WkbSize();
1539 170 : GByte *pabyWKB = (GByte *) CPLMalloc(nWKBLen + 1);
1540 :
1541 170 : poGeom->exportToWkb( wkbNDR, pabyWKB );
1542 170 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabyWKB, nWKBLen, CPLFree );
1543 : }
1544 233 : else if ( eGeomFormat == OSGF_SpatiaLite )
1545 : {
1546 : int nBLOBLen;
1547 : GByte *pabySLBLOB;
1548 :
1549 233 : if( nSRSId == UNINITIALIZED_SRID )
1550 0 : nSRSId = poDS->GetUndefinedSRID();
1551 :
1552 : ExportSpatiaLiteGeometry( poGeom, nSRSId, wkbNDR, bHasM,
1553 233 : bSpatialite2D, bUseComprGeom, &pabySLBLOB, &nBLOBLen );
1554 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabySLBLOB,
1555 233 : nBLOBLen, CPLFree );
1556 : }
1557 : else
1558 : {
1559 0 : rc = SQLITE_OK;
1560 0 : CPLAssert(0);
1561 : }
1562 : }
1563 : else
1564 : {
1565 24 : if (bBindNullValues)
1566 2 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1567 : else
1568 22 : rc = SQLITE_OK;
1569 : }
1570 :
1571 429 : if( rc != SQLITE_OK )
1572 : {
1573 : CPLError( CE_Failure, CPLE_AppDefined,
1574 : "sqlite3_bind_blob/text() failed:\n %s",
1575 0 : sqlite3_errmsg(hDB) );
1576 0 : return OGRERR_FAILURE;
1577 : }
1578 : }
1579 :
1580 : /* -------------------------------------------------------------------- */
1581 : /* Bind field values. */
1582 : /* -------------------------------------------------------------------- */
1583 : int iField;
1584 456 : int nFieldCount = poFeatureDefn->GetFieldCount();
1585 1440 : for( iField = 0; iField < nFieldCount; iField++ )
1586 : {
1587 : const char *pszRawValue;
1588 :
1589 984 : if( !poFeature->IsFieldSet( iField ) )
1590 : {
1591 342 : if (bBindNullValues)
1592 8 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1593 : else
1594 334 : rc = SQLITE_OK;
1595 : }
1596 : else
1597 : {
1598 642 : switch( poFeatureDefn->GetFieldDefn(iField)->GetType() )
1599 : {
1600 : case OFTInteger:
1601 : {
1602 143 : int nFieldVal = poFeature->GetFieldAsInteger( iField );
1603 143 : rc = sqlite3_bind_int(hStmt, nBindField++, nFieldVal);
1604 143 : break;
1605 : }
1606 :
1607 : case OFTReal:
1608 : {
1609 210 : double dfFieldVal = poFeature->GetFieldAsDouble( iField );
1610 210 : rc = sqlite3_bind_double(hStmt, nBindField++, dfFieldVal);
1611 210 : break;
1612 : }
1613 :
1614 : case OFTBinary:
1615 : {
1616 24 : int nDataLength = 0;
1617 : GByte* pabyData =
1618 24 : poFeature->GetFieldAsBinary( iField, &nDataLength );
1619 : rc = sqlite3_bind_blob(hStmt, nBindField++,
1620 24 : pabyData, nDataLength, SQLITE_TRANSIENT);
1621 24 : break;
1622 : }
1623 :
1624 : case OFTDateTime:
1625 : {
1626 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1627 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1628 2 : &nHour, &nMinute, &nSecond, &nTZ);
1629 : char szBuffer[64];
1630 : sprintf(szBuffer, "%04d-%02d-%02dT%02d:%02d:%02d",
1631 2 : nYear, nMonth, nDay, nHour, nMinute, nSecond);
1632 : rc = sqlite3_bind_text(hStmt, nBindField++,
1633 2 : szBuffer, -1, SQLITE_TRANSIENT);
1634 2 : break;
1635 : }
1636 :
1637 : case OFTDate:
1638 : {
1639 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1640 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1641 2 : &nHour, &nMinute, &nSecond, &nTZ);
1642 : char szBuffer[64];
1643 2 : sprintf(szBuffer, "%04d-%02d-%02dT", nYear, nMonth, nDay);
1644 : rc = sqlite3_bind_text(hStmt, nBindField++,
1645 2 : szBuffer, -1, SQLITE_TRANSIENT);
1646 2 : break;
1647 : }
1648 :
1649 : case OFTTime:
1650 : {
1651 : int nYear, nMonth, nDay, nHour, nMinute, nSecond, nTZ;
1652 : poFeature->GetFieldAsDateTime(iField, &nYear, &nMonth, &nDay,
1653 2 : &nHour, &nMinute, &nSecond, &nTZ);
1654 : char szBuffer[64];
1655 2 : sprintf(szBuffer, "%02d:%02d:%02d", nHour, nMinute, nSecond);
1656 : rc = sqlite3_bind_text(hStmt, nBindField++,
1657 2 : szBuffer, -1, SQLITE_TRANSIENT);
1658 2 : break;
1659 : }
1660 :
1661 : default:
1662 : {
1663 259 : pszRawValue = poFeature->GetFieldAsString( iField );
1664 : rc = sqlite3_bind_text(hStmt, nBindField++,
1665 259 : pszRawValue, -1, SQLITE_TRANSIENT);
1666 : break;
1667 : }
1668 : }
1669 : }
1670 :
1671 984 : if( rc != SQLITE_OK )
1672 : {
1673 : CPLError( CE_Failure, CPLE_AppDefined,
1674 : "sqlite3_bind_() for column %s failed:\n %s",
1675 : poFeatureDefn->GetFieldDefn(iField)->GetNameRef(),
1676 0 : sqlite3_errmsg(hDB) );
1677 0 : return OGRERR_FAILURE;
1678 : }
1679 : }
1680 :
1681 456 : return OGRERR_NONE;
1682 : }
1683 :
1684 : /************************************************************************/
1685 : /* SetFeature() */
1686 : /************************************************************************/
1687 :
1688 21 : OGRErr OGRSQLiteTableLayer::SetFeature( OGRFeature *poFeature )
1689 :
1690 : {
1691 21 : if (HasLayerDefnError())
1692 0 : return OGRERR_FAILURE;
1693 :
1694 21 : if( pszFIDColumn == NULL )
1695 : {
1696 : CPLError( CE_Failure, CPLE_AppDefined,
1697 0 : "SetFeature() without any FID column." );
1698 0 : return OGRERR_FAILURE;
1699 : }
1700 :
1701 21 : if( poFeature->GetFID() == OGRNullFID )
1702 : {
1703 : CPLError( CE_Failure, CPLE_AppDefined,
1704 0 : "SetFeature() with unset FID fails." );
1705 0 : return OGRERR_FAILURE;
1706 : }
1707 :
1708 21 : if (!poDS->GetUpdate())
1709 : {
1710 : CPLError( CE_Failure, CPLE_NotSupported,
1711 : UNSUPPORTED_OP_READ_ONLY,
1712 0 : "SetFeature");
1713 0 : return OGRERR_FAILURE;
1714 : }
1715 :
1716 21 : sqlite3 *hDB = poDS->GetDB();
1717 21 : CPLString osCommand;
1718 21 : int bNeedComma = FALSE;
1719 :
1720 21 : ResetReading();
1721 :
1722 : /* -------------------------------------------------------------------- */
1723 : /* Form the UPDATE command. */
1724 : /* -------------------------------------------------------------------- */
1725 21 : osCommand += CPLSPrintf( "UPDATE '%s' SET ", pszEscapedTableName );
1726 :
1727 : /* -------------------------------------------------------------------- */
1728 : /* Add geometry field name. */
1729 : /* -------------------------------------------------------------------- */
1730 21 : if( osGeomColumn.size() != 0 &&
1731 : eGeomFormat != OSGF_FGF )
1732 : {
1733 21 : osCommand += "\"";
1734 21 : osCommand += OGRSQLiteEscapeName(osGeomColumn);
1735 21 : osCommand += "\" = ?";
1736 :
1737 21 : bNeedComma = TRUE;
1738 : }
1739 :
1740 : /* -------------------------------------------------------------------- */
1741 : /* Add field names. */
1742 : /* -------------------------------------------------------------------- */
1743 : int iField;
1744 21 : int nFieldCount = poFeatureDefn->GetFieldCount();
1745 :
1746 89 : for( iField = 0; iField < nFieldCount; iField++ )
1747 : {
1748 68 : if( bNeedComma )
1749 68 : osCommand += ",";
1750 :
1751 68 : osCommand += "\"";
1752 68 : osCommand += OGRSQLiteEscapeName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
1753 68 : osCommand += "\" = ?";
1754 :
1755 68 : bNeedComma = TRUE;
1756 : }
1757 :
1758 21 : if (!bNeedComma)
1759 0 : return OGRERR_NONE;
1760 :
1761 : /* -------------------------------------------------------------------- */
1762 : /* Merge final command. */
1763 : /* -------------------------------------------------------------------- */
1764 21 : osCommand += " WHERE \"";
1765 21 : osCommand += OGRSQLiteEscapeName(pszFIDColumn);
1766 21 : osCommand += CPLSPrintf("\" = %ld", poFeature->GetFID());
1767 :
1768 : /* -------------------------------------------------------------------- */
1769 : /* Prepare the statement. */
1770 : /* -------------------------------------------------------------------- */
1771 : int rc;
1772 : sqlite3_stmt *hUpdateStmt;
1773 :
1774 : #ifdef DEBUG
1775 21 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
1776 : #endif
1777 :
1778 21 : rc = sqlite3_prepare( hDB, osCommand, -1, &hUpdateStmt, NULL );
1779 21 : if( rc != SQLITE_OK )
1780 : {
1781 : CPLError( CE_Failure, CPLE_AppDefined,
1782 : "In SetFeature(): sqlite3_prepare(%s):\n %s",
1783 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
1784 :
1785 0 : return OGRERR_FAILURE;
1786 : }
1787 :
1788 : /* -------------------------------------------------------------------- */
1789 : /* Bind values. */
1790 : /* -------------------------------------------------------------------- */
1791 21 : OGRErr eErr = BindValues( poFeature, hUpdateStmt, TRUE );
1792 21 : if (eErr != OGRERR_NONE)
1793 : {
1794 0 : sqlite3_finalize( hUpdateStmt );
1795 0 : return eErr;
1796 : }
1797 :
1798 : /* -------------------------------------------------------------------- */
1799 : /* Execute the update. */
1800 : /* -------------------------------------------------------------------- */
1801 21 : rc = sqlite3_step( hUpdateStmt );
1802 :
1803 21 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
1804 : {
1805 : CPLError( CE_Failure, CPLE_AppDefined,
1806 : "sqlite3_step() failed:\n %s",
1807 0 : sqlite3_errmsg(hDB) );
1808 :
1809 0 : sqlite3_finalize( hUpdateStmt );
1810 0 : return OGRERR_FAILURE;
1811 : }
1812 :
1813 21 : sqlite3_finalize( hUpdateStmt );
1814 :
1815 21 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1816 22 : if( bCachedExtentIsValid &&
1817 1 : poGeom != NULL && !poGeom->IsEmpty() )
1818 : {
1819 1 : OGREnvelope sGeomEnvelope;
1820 1 : poGeom->getEnvelope(&sGeomEnvelope);
1821 1 : oCachedExtent.Merge(sGeomEnvelope);
1822 : }
1823 21 : bStatisticsNeedsToBeFlushed = TRUE;
1824 :
1825 21 : return OGRERR_NONE;
1826 : }
1827 :
1828 : /************************************************************************/
1829 : /* CreateFeature() */
1830 : /************************************************************************/
1831 :
1832 435 : OGRErr OGRSQLiteTableLayer::CreateFeature( OGRFeature *poFeature )
1833 :
1834 : {
1835 435 : sqlite3 *hDB = poDS->GetDB();
1836 435 : CPLString osCommand;
1837 435 : CPLString osValues;
1838 435 : int bNeedComma = FALSE;
1839 :
1840 435 : if (HasLayerDefnError())
1841 0 : return OGRERR_FAILURE;
1842 :
1843 435 : if (!poDS->GetUpdate())
1844 : {
1845 : CPLError( CE_Failure, CPLE_NotSupported,
1846 : UNSUPPORTED_OP_READ_ONLY,
1847 0 : "CreateFeature");
1848 0 : return OGRERR_FAILURE;
1849 : }
1850 :
1851 435 : ResetReading();
1852 :
1853 : /* -------------------------------------------------------------------- */
1854 : /* Form the INSERT command. */
1855 : /* -------------------------------------------------------------------- */
1856 435 : osCommand += CPLSPrintf( "INSERT INTO '%s' (", pszEscapedTableName );
1857 :
1858 : /* -------------------------------------------------------------------- */
1859 : /* Add FID if we have a cleartext FID column. */
1860 : /* -------------------------------------------------------------------- */
1861 435 : if( pszFIDColumn != NULL // && !EQUAL(pszFIDColumn,"OGC_FID")
1862 : && poFeature->GetFID() != OGRNullFID )
1863 : {
1864 100 : osCommand += "\"";
1865 100 : osCommand += OGRSQLiteEscapeName(pszFIDColumn);
1866 100 : osCommand += "\"";
1867 :
1868 100 : osValues += CPLSPrintf( "%ld", poFeature->GetFID() );
1869 100 : bNeedComma = TRUE;
1870 : }
1871 :
1872 : /* -------------------------------------------------------------------- */
1873 : /* Add geometry. */
1874 : /* -------------------------------------------------------------------- */
1875 435 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1876 :
1877 435 : if( osGeomColumn.size() != 0 &&
1878 : poGeom != NULL &&
1879 : eGeomFormat != OSGF_FGF )
1880 : {
1881 :
1882 386 : if( bNeedComma )
1883 : {
1884 100 : osCommand += ",";
1885 100 : osValues += ",";
1886 : }
1887 :
1888 386 : osCommand += "\"";
1889 386 : osCommand += OGRSQLiteEscapeName(osGeomColumn);
1890 386 : osCommand += "\"";
1891 :
1892 386 : osValues += "?";
1893 :
1894 386 : bNeedComma = TRUE;
1895 : }
1896 :
1897 : /* -------------------------------------------------------------------- */
1898 : /* Add field values. */
1899 : /* -------------------------------------------------------------------- */
1900 : int iField;
1901 435 : int nFieldCount = poFeatureDefn->GetFieldCount();
1902 :
1903 1351 : for( iField = 0; iField < nFieldCount; iField++ )
1904 : {
1905 916 : if( !poFeature->IsFieldSet( iField ) )
1906 334 : continue;
1907 :
1908 582 : if( bNeedComma )
1909 : {
1910 535 : osCommand += ",";
1911 535 : osValues += ",";
1912 : }
1913 :
1914 582 : osCommand += "\"";
1915 582 : osCommand += OGRSQLiteEscapeName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef());
1916 582 : osCommand += "\"";
1917 :
1918 582 : osValues += "?";
1919 :
1920 582 : bNeedComma = TRUE;
1921 : }
1922 :
1923 : /* -------------------------------------------------------------------- */
1924 : /* Merge final command. */
1925 : /* -------------------------------------------------------------------- */
1926 435 : osCommand += ") VALUES (";
1927 435 : osCommand += osValues;
1928 435 : osCommand += ")";
1929 :
1930 435 : if (bNeedComma == FALSE)
1931 2 : osCommand = CPLSPrintf( "INSERT INTO '%s' DEFAULT VALUES", pszEscapedTableName );
1932 :
1933 : /* -------------------------------------------------------------------- */
1934 : /* Prepare the statement. */
1935 : /* -------------------------------------------------------------------- */
1936 : int rc;
1937 :
1938 435 : if (hInsertStmt == NULL ||
1939 : osCommand != osLastInsertStmt)
1940 : {
1941 : #ifdef DEBUG
1942 224 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
1943 : #endif
1944 :
1945 224 : ClearInsertStmt();
1946 224 : osLastInsertStmt = osCommand;
1947 :
1948 : #ifdef HAVE_SQLITE3_PREPARE_V2
1949 224 : rc = sqlite3_prepare_v2( hDB, osCommand, -1, &hInsertStmt, NULL );
1950 : #else
1951 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
1952 : #endif
1953 224 : if( rc != SQLITE_OK )
1954 : {
1955 : CPLError( CE_Failure, CPLE_AppDefined,
1956 : "In CreateFeature(): sqlite3_prepare(%s):\n %s",
1957 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
1958 :
1959 0 : ClearInsertStmt();
1960 0 : return OGRERR_FAILURE;
1961 : }
1962 : }
1963 :
1964 : /* -------------------------------------------------------------------- */
1965 : /* Bind values. */
1966 : /* -------------------------------------------------------------------- */
1967 435 : OGRErr eErr = BindValues( poFeature, hInsertStmt, FALSE );
1968 435 : if (eErr != OGRERR_NONE)
1969 : {
1970 0 : sqlite3_reset( hInsertStmt );
1971 0 : return eErr;
1972 : }
1973 :
1974 : /* -------------------------------------------------------------------- */
1975 : /* Execute the insert. */
1976 : /* -------------------------------------------------------------------- */
1977 435 : rc = sqlite3_step( hInsertStmt );
1978 :
1979 435 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
1980 : {
1981 : CPLError( CE_Failure, CPLE_AppDefined,
1982 : "sqlite3_step() failed:\n %s (%d)",
1983 0 : sqlite3_errmsg(hDB), rc );
1984 0 : sqlite3_reset( hInsertStmt );
1985 0 : return OGRERR_FAILURE;
1986 : }
1987 :
1988 : /* -------------------------------------------------------------------- */
1989 : /* Capture the FID/rowid. */
1990 : /* -------------------------------------------------------------------- */
1991 435 : const sqlite_int64 nFID = sqlite3_last_insert_rowid( hDB );
1992 435 : if(nFID > 0)
1993 : {
1994 433 : poFeature->SetFID( (long)nFID ); /* Possible truncation if nFID is 64bit */
1995 : }
1996 :
1997 435 : sqlite3_reset( hInsertStmt );
1998 :
1999 776 : if( (bCachedExtentIsValid || nFeatureCount == 0) &&
2000 341 : poGeom != NULL && !poGeom->IsEmpty() )
2001 : {
2002 317 : OGREnvelope sGeomEnvelope;
2003 317 : poGeom->getEnvelope(&sGeomEnvelope);
2004 317 : oCachedExtent.Merge(sGeomEnvelope);
2005 317 : bCachedExtentIsValid = TRUE;
2006 317 : bStatisticsNeedsToBeFlushed = TRUE;
2007 : }
2008 435 : if( nFeatureCount >= 0 )
2009 : {
2010 376 : bStatisticsNeedsToBeFlushed = TRUE;
2011 376 : nFeatureCount ++;
2012 : }
2013 :
2014 435 : return OGRERR_NONE;
2015 : }
2016 :
2017 : /************************************************************************/
2018 : /* DeleteFeature() */
2019 : /************************************************************************/
2020 :
2021 8 : OGRErr OGRSQLiteTableLayer::DeleteFeature( long nFID )
2022 :
2023 : {
2024 8 : CPLString osSQL;
2025 : int rc;
2026 8 : char *pszErrMsg = NULL;
2027 :
2028 8 : if (HasLayerDefnError())
2029 0 : return OGRERR_FAILURE;
2030 :
2031 8 : if( pszFIDColumn == NULL )
2032 : {
2033 : CPLError( CE_Failure, CPLE_NotSupported,
2034 0 : "Can't delete feature on a layer without FID column.");
2035 0 : return OGRERR_FAILURE;
2036 : }
2037 :
2038 8 : if (!poDS->GetUpdate())
2039 : {
2040 : CPLError( CE_Failure, CPLE_NotSupported,
2041 : UNSUPPORTED_OP_READ_ONLY,
2042 0 : "DeleteFeature");
2043 0 : return OGRERR_FAILURE;
2044 : }
2045 :
2046 8 : ResetReading();
2047 :
2048 : osSQL.Printf( "DELETE FROM '%s' WHERE \"%s\" = %ld",
2049 : pszEscapedTableName,
2050 8 : OGRSQLiteEscapeName(pszFIDColumn).c_str(), nFID );
2051 :
2052 8 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
2053 :
2054 8 : rc = sqlite3_exec( poDS->GetDB(), osSQL, NULL, NULL, &pszErrMsg );
2055 8 : if( rc != SQLITE_OK )
2056 : {
2057 : CPLError( CE_Failure, CPLE_AppDefined,
2058 : "In DeleteFeature(): sqlite3_exec(%s):\n %s",
2059 0 : osSQL.c_str(), pszErrMsg );
2060 0 : sqlite3_free( pszErrMsg );
2061 0 : return OGRERR_FAILURE;
2062 : }
2063 :
2064 8 : int nChanged = sqlite3_changes( poDS->GetDB() );
2065 :
2066 8 : if( nChanged == 1 )
2067 : {
2068 8 : bCachedExtentIsValid = FALSE;
2069 8 : nFeatureCount --;
2070 8 : bStatisticsNeedsToBeFlushed = TRUE;
2071 : }
2072 :
2073 8 : return OGRERR_NONE;
2074 : }
2075 :
2076 : /************************************************************************/
2077 : /* CreateSpatialIndex() */
2078 : /************************************************************************/
2079 :
2080 70 : int OGRSQLiteTableLayer::CreateSpatialIndex()
2081 : {
2082 70 : CPLString osCommand;
2083 :
2084 : osCommand.Printf("SELECT CreateSpatialIndex('%s', '%s')",
2085 70 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2086 :
2087 70 : char* pszErrMsg = NULL;
2088 70 : sqlite3 *hDB = poDS->GetDB();
2089 : #ifdef DEBUG
2090 70 : CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
2091 : #endif
2092 70 : int rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
2093 70 : if( rc != SQLITE_OK )
2094 : {
2095 : CPLError( CE_Failure, CPLE_AppDefined,
2096 0 : "Unable to create spatial index:\n%s", pszErrMsg );
2097 0 : sqlite3_free( pszErrMsg );
2098 0 : return FALSE;
2099 : }
2100 :
2101 70 : bHasSpatialIndex = TRUE;
2102 70 : return TRUE;
2103 : }
2104 :
2105 : /************************************************************************/
2106 : /* HasSpatialIndex() */
2107 : /************************************************************************/
2108 :
2109 68 : int OGRSQLiteTableLayer::HasSpatialIndex()
2110 : {
2111 68 : if( bDeferedSpatialIndexCreation )
2112 : {
2113 0 : bDeferedSpatialIndexCreation = FALSE;
2114 0 : bHasSpatialIndex = CreateSpatialIndex();
2115 : }
2116 :
2117 68 : return bHasSpatialIndex;
2118 : }
2119 :
2120 : /************************************************************************/
2121 : /* InitFeatureCount() */
2122 : /************************************************************************/
2123 :
2124 112 : void OGRSQLiteTableLayer::InitFeatureCount()
2125 : {
2126 112 : nFeatureCount = 0;
2127 112 : bStatisticsNeedsToBeFlushed = TRUE;
2128 112 : }
2129 :
2130 : /************************************************************************/
2131 : /* InvalidateCachedFeatureCountAndExtent() */
2132 : /************************************************************************/
2133 :
2134 338 : void OGRSQLiteTableLayer::InvalidateCachedFeatureCountAndExtent()
2135 : {
2136 338 : nFeatureCount = -1;
2137 338 : bCachedExtentIsValid = FALSE;
2138 338 : bStatisticsNeedsToBeFlushed = TRUE;
2139 338 : }
2140 :
2141 : /************************************************************************/
2142 : /* DoStatisticsNeedToBeFlushed() */
2143 : /************************************************************************/
2144 :
2145 3 : int OGRSQLiteTableLayer::DoStatisticsNeedToBeFlushed()
2146 : {
2147 : return bStatisticsNeedsToBeFlushed &&
2148 : poDS->IsSpatialiteDB() &&
2149 3 : OGRSQLiteIsSpatialiteLoaded();
2150 : }
2151 :
2152 : /************************************************************************/
2153 : /* ForceStatisticsToBeFlushed() */
2154 : /************************************************************************/
2155 :
2156 3 : void OGRSQLiteTableLayer::ForceStatisticsToBeFlushed()
2157 : {
2158 3 : bStatisticsNeedsToBeFlushed = TRUE;
2159 3 : }
2160 :
2161 : /************************************************************************/
2162 : /* AreStatisticsValid() */
2163 : /************************************************************************/
2164 :
2165 3 : int OGRSQLiteTableLayer::AreStatisticsValid()
2166 : {
2167 3 : return nFeatureCount >= 0;
2168 : }
2169 :
2170 : /************************************************************************/
2171 : /* LoadStatisticsSpatialite4DB() */
2172 : /************************************************************************/
2173 :
2174 2 : void OGRSQLiteTableLayer::LoadStatisticsSpatialite4DB()
2175 : {
2176 2 : CPLString osSQL;
2177 2 : CPLString osLastEvtDate;
2178 : osSQL.Printf("SELECT MAX(last_insert, last_update, last_delete) FROM geometry_columns_time WHERE "
2179 : "f_table_name = '%s' AND f_geometry_column = '%s'",
2180 2 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2181 :
2182 2 : sqlite3 *hDB = poDS->GetDB();
2183 2 : int nRowCount = 0, nColCount = 0;
2184 2 : char **papszResult = NULL;
2185 :
2186 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2187 2 : &nRowCount, &nColCount, NULL );
2188 :
2189 : /* Make it a Unix timestamp */
2190 : int nYear, nMonth, nDay, nHour, nMinute;
2191 : float fSecond;
2192 4 : if( nRowCount == 1 && nColCount == 1 && papszResult[1] != NULL &&
2193 2 : sscanf( papszResult[1], "%04d-%02d-%02dT%02d:%02d:%f",
2194 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &fSecond ) == 6 )
2195 : {
2196 2 : osLastEvtDate = papszResult[1];
2197 : }
2198 :
2199 2 : sqlite3_free_table( papszResult );
2200 2 : papszResult = NULL;
2201 :
2202 2 : if( osLastEvtDate.size() == 0 )
2203 : return;
2204 :
2205 : osSQL.Printf("SELECT last_verified, row_count, extent_min_x, extent_min_y, "
2206 : "extent_max_x, extent_max_y FROM geometry_columns_statistics WHERE "
2207 : "f_table_name = '%s' AND f_geometry_column = '%s'",
2208 2 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2209 :
2210 2 : nRowCount = 0;
2211 2 : nColCount = 0;
2212 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2213 2 : &nRowCount, &nColCount, NULL );
2214 :
2215 4 : if( nRowCount == 1 && nColCount == 6 && papszResult[6] != NULL &&
2216 2 : sscanf( papszResult[6], "%04d-%02d-%02dT%02d:%02d:%f",
2217 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &fSecond ) == 6 )
2218 : {
2219 2 : CPLString osLastVerified(papszResult[6]);
2220 :
2221 : /* Check that the information in geometry_columns_statistics is more */
2222 : /* recent than geometry_columns_time */
2223 2 : if( osLastVerified.compare(osLastEvtDate) > 0 )
2224 : {
2225 2 : char **papszRow = papszResult + 6;
2226 2 : const char* pszRowCount = papszRow[1];
2227 2 : const char* pszMinX = papszRow[2];
2228 2 : const char* pszMinY = papszRow[3];
2229 2 : const char* pszMaxX = papszRow[4];
2230 2 : const char* pszMaxY = papszRow[5];
2231 :
2232 2 : CPLDebug("SQLITE", "Loading statistics for %s", pszTableName);
2233 :
2234 2 : if( pszRowCount != NULL )
2235 : {
2236 2 : nFeatureCount = (GIntBig) CPLScanUIntBig( pszRowCount, 32 );
2237 2 : if( nFeatureCount == 0)
2238 : {
2239 0 : nFeatureCount = -1;
2240 0 : pszMinX = NULL;
2241 : }
2242 : else
2243 : {
2244 : CPLDebug("SQLite", "Layer %s feature count : " CPL_FRMT_GIB,
2245 2 : pszTableName, nFeatureCount);
2246 : }
2247 : }
2248 :
2249 2 : if( pszMinX != NULL && pszMinY != NULL &&
2250 : pszMaxX != NULL && pszMaxY != NULL )
2251 : {
2252 2 : bCachedExtentIsValid = TRUE;
2253 2 : oCachedExtent.MinX = CPLAtof(pszMinX);
2254 2 : oCachedExtent.MinY = CPLAtof(pszMinY);
2255 2 : oCachedExtent.MaxX = CPLAtof(pszMaxX);
2256 2 : oCachedExtent.MaxY = CPLAtof(pszMaxY);
2257 : CPLDebug("SQLite", "Layer %s extent : %s,%s,%s,%s",
2258 2 : pszTableName, pszMinX,pszMinY,pszMaxX,pszMaxY);
2259 : }
2260 2 : }
2261 : }
2262 :
2263 2 : sqlite3_free_table( papszResult );
2264 2 : papszResult = NULL;
2265 : }
2266 :
2267 : /************************************************************************/
2268 : /* LoadStatistics() */
2269 : /************************************************************************/
2270 :
2271 1396 : void OGRSQLiteTableLayer::LoadStatistics()
2272 : {
2273 1396 : if( !poDS->IsSpatialiteDB() || !OGRSQLiteIsSpatialiteLoaded() )
2274 240 : return;
2275 :
2276 1156 : if( poDS->HasSpatialite4Layout() )
2277 : {
2278 2 : LoadStatisticsSpatialite4DB();
2279 2 : return;
2280 : }
2281 :
2282 1154 : GIntBig nFileTimestamp = poDS->GetFileTimestamp();
2283 1154 : if( nFileTimestamp == 0 )
2284 70 : return;
2285 :
2286 : /* Find the most recent event in the 'spatialite_history' that is */
2287 : /* a UpdateLayerStatistics event on all tables and geometry columns */
2288 1084 : CPLString osSQL;
2289 : osSQL.Printf("SELECT MAX(timestamp) FROM spatialite_history WHERE "
2290 : "((table_name = '%s' AND geometry_column = '%s') OR "
2291 : "(table_name = 'ALL-TABLES' AND geometry_column = 'ALL-GEOMETRY-COLUMNS')) AND "
2292 : "event = 'UpdateLayerStatistics'",
2293 1084 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2294 :
2295 1084 : sqlite3 *hDB = poDS->GetDB();
2296 1084 : int nRowCount = 0, nColCount = 0;
2297 1084 : char **papszResult = NULL, *pszErrMsg = NULL;
2298 :
2299 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2300 1084 : &nRowCount, &nColCount, &pszErrMsg );
2301 :
2302 : /* Make it a Unix timestamp */
2303 : int nYear, nMonth, nDay, nHour, nMinute, nSecond;
2304 : struct tm brokendown;
2305 1084 : GIntBig nTS = -1;
2306 1954 : if( nRowCount >= 1 && nColCount == 1 && papszResult[1] != NULL &&
2307 870 : sscanf( papszResult[1], "%04d-%02d-%02d %02d:%02d:%02d",
2308 : &nYear, &nMonth, &nDay, &nHour, &nMinute, &nSecond ) == 6 )
2309 : {
2310 870 : brokendown.tm_year = nYear - 1900;
2311 870 : brokendown.tm_mon = nMonth - 1;
2312 870 : brokendown.tm_mday = nDay;
2313 870 : brokendown.tm_hour = nHour;
2314 870 : brokendown.tm_min = nMinute;
2315 870 : brokendown.tm_sec = nSecond;
2316 870 : nTS = CPLYMDHMSToUnixTime(&brokendown);
2317 : }
2318 :
2319 : /* If it is equal to the modified timestamp of the DB (as a file) */
2320 : /* then we can safely use the data from the layer_statistics, since */
2321 : /* it will be up-to-date */
2322 1084 : if( nFileTimestamp == nTS || nFileTimestamp == nTS + 1 )
2323 : {
2324 : osSQL.Printf("SELECT row_count, extent_min_x, extent_min_y, extent_max_x, extent_max_y "
2325 : "FROM layer_statistics WHERE table_name = '%s' AND geometry_column = '%s'",
2326 870 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2327 :
2328 870 : sqlite3_free_table( papszResult );
2329 870 : papszResult = NULL;
2330 :
2331 : sqlite3_get_table( hDB, osSQL.c_str(), &papszResult,
2332 870 : &nRowCount, &nColCount, &pszErrMsg );
2333 :
2334 870 : if( nRowCount == 1 )
2335 : {
2336 126 : char **papszRow = papszResult + 5;
2337 126 : const char* pszRowCount = papszRow[0];
2338 126 : const char* pszMinX = papszRow[1];
2339 126 : const char* pszMinY = papszRow[2];
2340 126 : const char* pszMaxX = papszRow[3];
2341 126 : const char* pszMaxY = papszRow[4];
2342 :
2343 : CPLDebug("SQLITE", "File timestamp matches layer statistics timestamp. "
2344 126 : "Loading statistics for %s", pszTableName);
2345 :
2346 126 : if( pszRowCount != NULL )
2347 : {
2348 126 : nFeatureCount = (GIntBig) CPLScanUIntBig( pszRowCount, 32 );
2349 : CPLDebug("SQLite", "Layer %s feature count : " CPL_FRMT_GIB,
2350 126 : pszTableName, nFeatureCount);
2351 : }
2352 :
2353 126 : if( pszMinX != NULL && pszMinY != NULL &&
2354 : pszMaxX != NULL && pszMaxY != NULL )
2355 : {
2356 88 : bCachedExtentIsValid = TRUE;
2357 88 : oCachedExtent.MinX = CPLAtof(pszMinX);
2358 88 : oCachedExtent.MinY = CPLAtof(pszMinY);
2359 88 : oCachedExtent.MaxX = CPLAtof(pszMaxX);
2360 88 : oCachedExtent.MaxY = CPLAtof(pszMaxY);
2361 : CPLDebug("SQLite", "Layer %s extent : %s,%s,%s,%s",
2362 88 : pszTableName, pszMinX,pszMinY,pszMaxX,pszMaxY);
2363 : }
2364 : }
2365 : }
2366 :
2367 1084 : if( pszErrMsg )
2368 41 : sqlite3_free( pszErrMsg );
2369 :
2370 1084 : sqlite3_free_table( papszResult );
2371 : }
2372 :
2373 : /************************************************************************/
2374 : /* SaveStatistics() */
2375 : /************************************************************************/
2376 :
2377 994 : int OGRSQLiteTableLayer::SaveStatistics()
2378 : {
2379 994 : if( !bStatisticsNeedsToBeFlushed || !poDS->IsSpatialiteDB() || !OGRSQLiteIsSpatialiteLoaded() )
2380 850 : return -1;
2381 :
2382 144 : CPLString osSQL;
2383 144 : sqlite3 *hDB = poDS->GetDB();
2384 144 : char* pszErrMsg = NULL;
2385 :
2386 144 : if( nFeatureCount >= 0 )
2387 : {
2388 : /* Update or add entry in the layer_statistics table */
2389 95 : if( bCachedExtentIsValid )
2390 : {
2391 : osSQL.Printf("INSERT OR REPLACE INTO layer_statistics (raster_layer, "
2392 : "table_name, geometry_column, row_count, extent_min_x, "
2393 : "extent_min_y, extent_max_x, extent_max_y) VALUES ("
2394 : "0, '%s', '%s', " CPL_FRMT_GIB ", %.18g, %.18g, %.18g, %.18g)",
2395 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
2396 : nFeatureCount,
2397 : oCachedExtent.MinX, oCachedExtent.MinY,
2398 76 : oCachedExtent.MaxX, oCachedExtent.MaxY);
2399 : }
2400 : else
2401 : {
2402 : osSQL.Printf("INSERT OR REPLACE INTO layer_statistics (raster_layer, "
2403 : "table_name, geometry_column, row_count, extent_min_x, "
2404 : "extent_min_y, extent_max_x, extent_max_y) VALUES ("
2405 : "0, '%s', '%s', " CPL_FRMT_GIB ", NULL, NULL, NULL, NULL)",
2406 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str(),
2407 19 : nFeatureCount);
2408 : }
2409 : }
2410 : else
2411 : {
2412 : /* Remove any existing entry in layer_statistics if for some reason */
2413 : /* we know that it will out-of-sync */
2414 : osSQL.Printf("DELETE FROM layer_statistics WHERE "
2415 : "table_name = '%s' AND geometry_column = '%s'",
2416 49 : pszEscapedTableName, OGRSQLiteEscape(osGeomColumn).c_str());
2417 : }
2418 :
2419 144 : int rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, &pszErrMsg );
2420 144 : if( rc != SQLITE_OK )
2421 : {
2422 13 : CPLDebug("SQLITE", "Error %s", pszErrMsg ? pszErrMsg : "unknown");
2423 13 : sqlite3_free( pszErrMsg );
2424 13 : return FALSE;
2425 : }
2426 : else
2427 131 : return TRUE;
2428 : }
|
