1 : /******************************************************************************
2 : * $Id: ogrsqlitetablelayer.cpp 17771 2009-10-07 20:52:43Z 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 <string>
34 :
35 : CPL_CVSID("$Id: ogrsqlitetablelayer.cpp 17771 2009-10-07 20:52:43Z rouault $");
36 :
37 : /************************************************************************/
38 : /* OGRSQLiteTableLayer() */
39 : /************************************************************************/
40 :
41 112 : OGRSQLiteTableLayer::OGRSQLiteTableLayer( OGRSQLiteDataSource *poDSIn )
42 :
43 : {
44 112 : poDS = poDSIn;
45 :
46 112 : bUpdateAccess = TRUE;
47 :
48 112 : iNextShapeId = 0;
49 :
50 112 : nSRSId = -1;
51 :
52 112 : poFeatureDefn = NULL;
53 112 : }
54 :
55 : /************************************************************************/
56 : /* ~OGRSQLiteTableLayer() */
57 : /************************************************************************/
58 :
59 224 : OGRSQLiteTableLayer::~OGRSQLiteTableLayer()
60 :
61 : {
62 112 : ClearStatement();
63 224 : }
64 :
65 : /************************************************************************/
66 : /* Initialize() */
67 : /************************************************************************/
68 :
69 112 : CPLErr OGRSQLiteTableLayer::Initialize( const char *pszTableName,
70 : const char *pszGeomCol,
71 : OGRwkbGeometryType eGeomType,
72 : const char *pszGeomFormat,
73 : OGRSpatialReference *poSRS,
74 : int nSRSId,
75 : int bHasSpatialIndex)
76 :
77 : {
78 : int rc;
79 112 : sqlite3 *hDB = poDS->GetDB();
80 :
81 112 : if( pszGeomCol == NULL )
82 67 : osGeomColumn = "";
83 : else
84 45 : osGeomColumn = pszGeomCol;
85 :
86 112 : if( pszGeomFormat )
87 : {
88 45 : if ( EQUAL(pszGeomFormat, "WKT") )
89 6 : eGeomFormat = OSGF_WKT;
90 39 : else if ( EQUAL(pszGeomFormat,"WKB") )
91 28 : eGeomFormat = OSGF_WKB;
92 11 : else if ( EQUAL(pszGeomFormat,"FGF") )
93 1 : eGeomFormat = OSGF_FGF;
94 10 : else if( EQUAL(pszGeomFormat,"SpatiaLite") )
95 10 : eGeomFormat = OSGF_SpatiaLite;
96 : }
97 :
98 112 : CPLFree( pszFIDColumn );
99 112 : pszFIDColumn = NULL;
100 :
101 112 : this->poSRS = poSRS;
102 112 : this->nSRSId = nSRSId;
103 112 : this->bHasSpatialIndex = bHasSpatialIndex;
104 :
105 112 : if( poSRS )
106 16 : poSRS->Reference();
107 :
108 : /* -------------------------------------------------------------------- */
109 : /* Get the column definitions for this table. */
110 : /* -------------------------------------------------------------------- */
111 : CPLErr eErr;
112 112 : sqlite3_stmt *hColStmt = NULL;
113 : const char *pszSQL = CPLSPrintf( "SELECT _rowid_, * FROM '%s' LIMIT 1",
114 112 : pszTableName );
115 :
116 112 : rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
117 112 : if( rc != SQLITE_OK )
118 : {
119 : CPLError( CE_Failure, CPLE_AppDefined,
120 : "Unable to query table %s for column definitions : %s.",
121 0 : pszTableName, sqlite3_errmsg(hDB) );
122 :
123 0 : return CE_Failure;
124 : }
125 :
126 112 : rc = sqlite3_step( hColStmt );
127 112 : if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
128 : {
129 : CPLError( CE_Failure, CPLE_AppDefined,
130 : "In Initialize(): sqlite3_step(%s):\n %s",
131 0 : pszSQL, sqlite3_errmsg(hDB) );
132 0 : sqlite3_finalize( hColStmt );
133 0 : return CE_Failure;
134 : }
135 :
136 : /* -------------------------------------------------------------------- */
137 : /* What should we use as FID? If there is a primary key */
138 : /* integer field, then this will be used as the _rowid_, and we */
139 : /* will pick up the real column name here. Otherwise, we will */
140 : /* just use fid. */
141 : /* */
142 : /* Note that the select _rowid_ will return the real column */
143 : /* name if the rowid corresponds to another primary key */
144 : /* column. */
145 : /* -------------------------------------------------------------------- */
146 112 : pszFIDColumn = CPLStrdup(sqlite3_column_name( hColStmt, 0 ));
147 :
148 : /* -------------------------------------------------------------------- */
149 : /* Collect the rest of the fields. */
150 : /* -------------------------------------------------------------------- */
151 112 : eErr = BuildFeatureDefn( pszTableName, hColStmt );
152 112 : sqlite3_finalize( hColStmt );
153 :
154 112 : if( eErr != CE_None )
155 0 : return eErr;
156 :
157 :
158 : /* -------------------------------------------------------------------- */
159 : /* Set the geometry type if we know it. */
160 : /* -------------------------------------------------------------------- */
161 112 : if( eGeomType != wkbUnknown )
162 4 : poFeatureDefn->SetGeomType( eGeomType );
163 :
164 112 : return CE_None;
165 : }
166 :
167 : /************************************************************************/
168 : /* ClearStatement() */
169 : /************************************************************************/
170 :
171 242 : void OGRSQLiteTableLayer::ClearStatement()
172 :
173 : {
174 242 : if( hStmt != NULL )
175 : {
176 32 : CPLDebug( "OGR_SQLITE", "finalize %p", hStmt );
177 32 : sqlite3_finalize( hStmt );
178 32 : hStmt = NULL;
179 : }
180 242 : }
181 :
182 : /************************************************************************/
183 : /* GetStatement() */
184 : /************************************************************************/
185 :
186 111 : sqlite3_stmt *OGRSQLiteTableLayer::GetStatement()
187 :
188 : {
189 111 : if( hStmt == NULL )
190 31 : ResetStatement();
191 :
192 111 : return hStmt;
193 : }
194 :
195 : /************************************************************************/
196 : /* ResetStatement() */
197 : /************************************************************************/
198 :
199 31 : OGRErr OGRSQLiteTableLayer::ResetStatement()
200 :
201 : {
202 : int rc;
203 31 : CPLString osSQL;
204 :
205 31 : ClearStatement();
206 :
207 31 : iNextShapeId = 0;
208 :
209 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' %s",
210 : poFeatureDefn->GetName(),
211 31 : osWHERE.c_str() );
212 :
213 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
214 31 : &hStmt, NULL );
215 :
216 31 : if( rc == SQLITE_OK )
217 : {
218 31 : return OGRERR_NONE;
219 : }
220 : else
221 : {
222 : CPLError( CE_Failure, CPLE_AppDefined,
223 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
224 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
225 0 : hStmt = NULL;
226 0 : return OGRERR_FAILURE;
227 0 : }
228 : }
229 :
230 : /************************************************************************/
231 : /* ResetReading() */
232 : /************************************************************************/
233 :
234 90 : void OGRSQLiteTableLayer::ResetReading()
235 :
236 : {
237 90 : ClearStatement();
238 90 : OGRSQLiteLayer::ResetReading();
239 90 : }
240 :
241 : /************************************************************************/
242 : /* GetFeature() */
243 : /************************************************************************/
244 :
245 1 : OGRFeature *OGRSQLiteTableLayer::GetFeature( long nFeatureId )
246 :
247 : {
248 : /* -------------------------------------------------------------------- */
249 : /* If we don't have an explicit FID column, just read through */
250 : /* the result set iteratively to find our target. */
251 : /* -------------------------------------------------------------------- */
252 1 : if( pszFIDColumn == NULL )
253 0 : return OGRSQLiteLayer::GetFeature( nFeatureId );
254 :
255 : /* -------------------------------------------------------------------- */
256 : /* Setup explicit query statement to fetch the record we want. */
257 : /* -------------------------------------------------------------------- */
258 1 : CPLString osSQL;
259 : int rc;
260 :
261 1 : ClearStatement();
262 :
263 1 : iNextShapeId = nFeatureId;
264 :
265 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' WHERE \"%s\" = %d",
266 : poFeatureDefn->GetName(),
267 1 : pszFIDColumn, (int) nFeatureId );
268 :
269 1 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
270 :
271 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
272 1 : &hStmt, NULL );
273 1 : if( rc != SQLITE_OK )
274 : {
275 : CPLError( CE_Failure, CPLE_AppDefined,
276 : "In GetFeature(): sqlite3_prepare(%s):\n %s",
277 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
278 :
279 0 : return NULL;
280 : }
281 : /* -------------------------------------------------------------------- */
282 : /* Get the feature if possible. */
283 : /* -------------------------------------------------------------------- */
284 1 : OGRFeature *poFeature = NULL;
285 :
286 1 : poFeature = GetNextRawFeature();
287 :
288 1 : ResetReading();
289 :
290 1 : return poFeature;
291 : }
292 :
293 : /************************************************************************/
294 : /* SetAttributeFilter() */
295 : /************************************************************************/
296 :
297 22 : OGRErr OGRSQLiteTableLayer::SetAttributeFilter( const char *pszQuery )
298 :
299 : {
300 22 : if( pszQuery == NULL )
301 8 : osQuery = "";
302 : else
303 14 : osQuery = pszQuery;
304 :
305 22 : BuildWhere();
306 :
307 22 : ResetReading();
308 :
309 22 : return OGRERR_NONE;
310 : }
311 :
312 :
313 : /************************************************************************/
314 : /* SetSpatialFilter() */
315 : /************************************************************************/
316 :
317 2 : void OGRSQLiteTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
318 :
319 : {
320 2 : if( InstallFilter( poGeomIn ) )
321 : {
322 2 : BuildWhere();
323 :
324 2 : ResetReading();
325 : }
326 2 : }
327 :
328 : /************************************************************************/
329 : /* BuildWhere() */
330 : /* */
331 : /* Build the WHERE statement appropriate to the current set of */
332 : /* criteria (spatial and attribute queries). */
333 : /************************************************************************/
334 :
335 24 : void OGRSQLiteTableLayer::BuildWhere()
336 :
337 : {
338 24 : osWHERE = "";
339 :
340 27 : if( m_poFilterGeom != NULL && bHasSpatialIndex )
341 : {
342 0 : OGREnvelope sEnvelope;
343 :
344 0 : m_poFilterGeom->getEnvelope( &sEnvelope );
345 : osWHERE.Printf("WHERE MBRWithin(\"%s\", BuildMBR(%.12f, %.12f, %.12f, %.12f, %d)) ",
346 : osGeomColumn.c_str(),
347 : sEnvelope.MinX, sEnvelope.MinY,
348 : sEnvelope.MaxX, sEnvelope.MaxY,
349 0 : nSRSId);
350 : }
351 :
352 24 : if( strlen(osQuery) > 0 )
353 : {
354 14 : if( strlen(osWHERE) == 0 )
355 : {
356 14 : osWHERE.Printf( "WHERE %s ", osQuery.c_str() );
357 : }
358 : else
359 : {
360 0 : osWHERE += "AND ";
361 0 : osWHERE += osQuery;
362 : }
363 : }
364 24 : }
365 :
366 : /************************************************************************/
367 : /* TestCapability() */
368 : /************************************************************************/
369 :
370 4 : int OGRSQLiteTableLayer::TestCapability( const char * pszCap )
371 :
372 : {
373 4 : if (EQUAL(pszCap,OLCFastFeatureCount))
374 : return m_poFilterGeom == NULL || osGeomColumn.size() == 0 ||
375 4 : bHasSpatialIndex;
376 :
377 0 : else if (EQUAL(pszCap,OLCFastSpatialFilter))
378 0 : return bHasSpatialIndex;
379 :
380 0 : else if( EQUAL(pszCap,OLCRandomRead) )
381 0 : return pszFIDColumn != NULL;
382 :
383 0 : else if( EQUAL(pszCap,OLCSequentialWrite)
384 : || EQUAL(pszCap,OLCRandomWrite) )
385 0 : return bUpdateAccess;
386 :
387 0 : else if( EQUAL(pszCap,OLCCreateField) )
388 0 : return bUpdateAccess;
389 :
390 : else
391 0 : return OGRSQLiteLayer::TestCapability( pszCap );
392 : }
393 :
394 : /************************************************************************/
395 : /* GetFeatureCount() */
396 : /* */
397 : /* If a spatial filter is in effect, we turn control over to */
398 : /* the generic counter. Otherwise we return the total count. */
399 : /* Eventually we should consider implementing a more efficient */
400 : /* way of counting features matching a spatial query. */
401 : /************************************************************************/
402 :
403 4 : int OGRSQLiteTableLayer::GetFeatureCount( int bForce )
404 :
405 : {
406 4 : if( !TestCapability(OLCFastFeatureCount) )
407 2 : return OGRSQLiteLayer::GetFeatureCount( bForce );
408 :
409 : /* -------------------------------------------------------------------- */
410 : /* Form count SQL. */
411 : /* -------------------------------------------------------------------- */
412 : const char *pszSQL;
413 :
414 : pszSQL = CPLSPrintf( "SELECT count(*) FROM '%s' %s",
415 2 : poFeatureDefn->GetName(), osWHERE.c_str() );
416 :
417 : /* -------------------------------------------------------------------- */
418 : /* Execute. */
419 : /* -------------------------------------------------------------------- */
420 : char **papszResult, *pszErrMsg;
421 : int nRowCount, nColCount;
422 2 : int nResult = -1;
423 :
424 2 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
425 : &nColCount, &nRowCount, &pszErrMsg ) != SQLITE_OK )
426 0 : return -1;
427 :
428 2 : if( nRowCount == 1 && nColCount == 1 )
429 2 : nResult = atoi(papszResult[1]);
430 :
431 2 : sqlite3_free_table( papszResult );
432 :
433 2 : return nResult;
434 : }
435 :
436 : /************************************************************************/
437 : /* GetSpatialRef() */
438 : /* */
439 : /* We override this to try and fetch the table SRID from the */
440 : /* geometry_columns table if the srsid is -2 (meaning we */
441 : /* haven't yet even looked for it). */
442 : /************************************************************************/
443 :
444 5 : OGRSpatialReference *OGRSQLiteTableLayer::GetSpatialRef()
445 :
446 : {
447 5 : return OGRSQLiteLayer::GetSpatialRef();
448 : }
449 :
450 : /************************************************************************/
451 : /* CreateField() */
452 : /************************************************************************/
453 :
454 9 : OGRErr OGRSQLiteTableLayer::CreateField( OGRFieldDefn *poFieldIn,
455 : int bApproxOK )
456 :
457 : {
458 9 : OGRFieldDefn oField( poFieldIn );
459 :
460 9 : ResetReading();
461 :
462 : /* -------------------------------------------------------------------- */
463 : /* Do we want to "launder" the column names into SQLite */
464 : /* friendly format? */
465 : /* -------------------------------------------------------------------- */
466 9 : if( bLaunderColumnNames )
467 : {
468 9 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
469 :
470 9 : oField.SetName( pszSafeName );
471 9 : CPLFree( pszSafeName );
472 : }
473 :
474 : /* -------------------------------------------------------------------- */
475 : /* How much space do we need for the list of fields. */
476 : /* -------------------------------------------------------------------- */
477 9 : int iField, nFieldListLen = 100;
478 : char *pszOldFieldList, *pszNewFieldList;
479 :
480 25 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
481 : {
482 : nFieldListLen +=
483 16 : strlen(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()) + 50;
484 : }
485 :
486 9 : nFieldListLen += strlen( oField.GetNameRef() );
487 :
488 9 : pszOldFieldList = (char *) CPLCalloc(1,nFieldListLen);
489 9 : pszNewFieldList = (char *) CPLCalloc(1,nFieldListLen);
490 :
491 : /* -------------------------------------------------------------------- */
492 : /* Build list of old fields, and the list of new fields. */
493 : /* -------------------------------------------------------------------- */
494 : const char *pszType;
495 9 : sprintf( pszOldFieldList, "%s", "OGC_FID" );
496 9 : sprintf( pszNewFieldList, "%s", "OGC_FID INTEGER PRIMARY KEY" );
497 :
498 9 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
499 :
500 9 : if( poFeatureDefn->GetGeomType() != wkbNone )
501 : {
502 9 : strcat( pszOldFieldList, "," );
503 9 : strcat( pszNewFieldList, "," );
504 :
505 9 : strcat( pszOldFieldList, osGeomColumn );
506 9 : strcat( pszNewFieldList, osGeomColumn );
507 :
508 9 : if ( eGeomFormat == OSGF_WKB )
509 9 : strcat( pszNewFieldList, " BLOB" );
510 : else
511 0 : strcat( pszNewFieldList, " VARCHAR" );
512 :
513 9 : iNextOrdinal++;
514 : }
515 :
516 25 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
517 : {
518 16 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
519 :
520 : // we already added OGC_FID so don't do it again
521 16 : if( EQUAL(poFldDefn->GetNameRef(),"OGC_FID") )
522 0 : continue;
523 :
524 16 : if( poFldDefn->GetType() == OFTInteger )
525 6 : pszType = "INTEGER";
526 10 : else if( poFldDefn->GetType() == OFTReal )
527 6 : pszType = "FLOAT";
528 4 : else if( poFldDefn->GetType() == OFTBinary )
529 1 : pszType = "BLOB";
530 : else
531 3 : pszType = "VARCHAR";
532 :
533 : sprintf( pszOldFieldList+strlen(pszOldFieldList),
534 16 : ", '%s'", poFldDefn->GetNameRef() );
535 :
536 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
537 16 : ", '%s' %s", poFldDefn->GetNameRef(), pszType );
538 :
539 16 : iNextOrdinal++;
540 : }
541 :
542 : /* -------------------------------------------------------------------- */
543 : /* Add the new field. */
544 : /* -------------------------------------------------------------------- */
545 9 : if( oField.GetType() == OFTInteger )
546 2 : pszType = "INTEGER";
547 7 : else if( oField.GetType() == OFTReal )
548 2 : pszType = "FLOAT";
549 5 : else if( oField.GetType() == OFTBinary )
550 3 : pszType = "BLOB";
551 : else
552 2 : pszType = "VARCHAR";
553 :
554 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
555 9 : ", '%s' %s", oField.GetNameRef(), pszType );
556 :
557 : /* ==================================================================== */
558 : /* Backup, destroy, recreate and repopulate the table. SQLite */
559 : /* has no ALTER TABLE so we have to do all this to add a */
560 : /* column. */
561 : /* ==================================================================== */
562 :
563 : /* -------------------------------------------------------------------- */
564 : /* Do this all in a transaction. */
565 : /* -------------------------------------------------------------------- */
566 9 : poDS->SoftStartTransaction();
567 :
568 : /* -------------------------------------------------------------------- */
569 : /* Make a backup of the table. */
570 : /* -------------------------------------------------------------------- */
571 : int rc;
572 9 : char *pszErrMsg = NULL;
573 9 : sqlite3 *hDB = poDS->GetDB();
574 :
575 : rc = sqlite3_exec( hDB,
576 : CPLSPrintf( "CREATE TEMPORARY TABLE t1_back(%s)",
577 : pszOldFieldList ),
578 9 : NULL, NULL, &pszErrMsg );
579 :
580 9 : if( rc == SQLITE_OK )
581 : rc = sqlite3_exec( hDB,
582 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
583 : pszOldFieldList,
584 : poFeatureDefn->GetName() ),
585 9 : NULL, NULL, &pszErrMsg );
586 :
587 :
588 : /* -------------------------------------------------------------------- */
589 : /* Drop the original table, and recreate with new field. */
590 : /* -------------------------------------------------------------------- */
591 9 : if( rc == SQLITE_OK )
592 : rc = sqlite3_exec( hDB,
593 : CPLSPrintf( "DROP TABLE '%s'",
594 : poFeatureDefn->GetName() ),
595 9 : NULL, NULL, &pszErrMsg );
596 :
597 9 : if( rc == SQLITE_OK )
598 : {
599 : const char *pszCmd =
600 : CPLSPrintf( "CREATE TABLE '%s' (%s)",
601 : poFeatureDefn->GetName(),
602 9 : pszNewFieldList );
603 : rc = sqlite3_exec( hDB, pszCmd,
604 9 : NULL, NULL, &pszErrMsg );
605 :
606 9 : CPLDebug( "OGR_SQLITE", "exec(%s)", pszCmd );
607 : }
608 :
609 : /* -------------------------------------------------------------------- */
610 : /* Copy backup field values into new table. */
611 : /* -------------------------------------------------------------------- */
612 :
613 9 : if( rc == SQLITE_OK )
614 : rc = sqlite3_exec( hDB,
615 : CPLSPrintf( "INSERT INTO '%s' SELECT %s, NULL FROM t1_back",
616 : poFeatureDefn->GetName(),
617 : pszOldFieldList ),
618 9 : NULL, NULL, &pszErrMsg );
619 :
620 9 : CPLFree( pszOldFieldList );
621 9 : CPLFree( pszNewFieldList );
622 :
623 : /* -------------------------------------------------------------------- */
624 : /* Cleanup backup table. */
625 : /* -------------------------------------------------------------------- */
626 :
627 9 : if( rc == SQLITE_OK )
628 : rc = sqlite3_exec( hDB,
629 : CPLSPrintf( "DROP TABLE t1_back" ),
630 9 : NULL, NULL, &pszErrMsg );
631 :
632 : /* -------------------------------------------------------------------- */
633 : /* COMMIT on success or ROLLBACK on failuire. */
634 : /* -------------------------------------------------------------------- */
635 9 : if( rc == SQLITE_OK )
636 : {
637 9 : poDS->SoftCommit();
638 : }
639 : else
640 : {
641 : CPLError( CE_Failure, CPLE_AppDefined,
642 : "Failed to add field %s to table %s:\n %s",
643 : oField.GetNameRef(), poFeatureDefn->GetName(),
644 0 : pszErrMsg );
645 0 : sqlite3_free( pszErrMsg );
646 :
647 0 : poDS->SoftRollback();
648 :
649 0 : return OGRERR_FAILURE;
650 : }
651 :
652 : /* -------------------------------------------------------------------- */
653 : /* Add the field to the OGRFeatureDefn. */
654 : /* -------------------------------------------------------------------- */
655 : int iNewField;
656 :
657 9 : poFeatureDefn->AddFieldDefn( &oField );
658 :
659 9 : iNewField = poFeatureDefn->GetFieldCount() - 1;
660 : panFieldOrdinals = (int *)
661 9 : CPLRealloc(panFieldOrdinals, (iNewField+1) * sizeof(int) );
662 9 : panFieldOrdinals[iNewField] = iNextOrdinal;
663 :
664 9 : return OGRERR_NONE;
665 : }
666 :
667 : /************************************************************************/
668 : /* SetFeature() */
669 : /************************************************************************/
670 :
671 1 : OGRErr OGRSQLiteTableLayer::SetFeature( OGRFeature *poFeature )
672 :
673 : {
674 1 : if( pszFIDColumn == NULL )
675 : {
676 : CPLError( CE_Failure, CPLE_AppDefined,
677 0 : "SetFeature() without any FID column." );
678 0 : return OGRERR_FAILURE;
679 : }
680 :
681 1 : if( poFeature->GetFID() == OGRNullFID )
682 : {
683 : CPLError( CE_Failure, CPLE_AppDefined,
684 0 : "SetFeature() with unset FID fails." );
685 0 : return OGRERR_FAILURE;
686 : }
687 : /* -------------------------------------------------------------------- */
688 : /* Drop the record with this FID. */
689 : /* -------------------------------------------------------------------- */
690 : int rc;
691 1 : char *pszErrMsg = NULL;
692 : const char *pszSQL;
693 :
694 : pszSQL =
695 : CPLSPrintf( "DELETE FROM '%s' WHERE \"%s\" = %ld",
696 : poFeatureDefn->GetName(),
697 : pszFIDColumn,
698 1 : poFeature->GetFID() );
699 :
700 1 : CPLDebug( "OGR_SQLITE", "exec(%s)", pszSQL );
701 :
702 : rc = sqlite3_exec( poDS->GetDB(), pszSQL,
703 1 : NULL, NULL, &pszErrMsg );
704 :
705 1 : if( rc != SQLITE_OK )
706 : {
707 : CPLError( CE_Failure, CPLE_AppDefined,
708 : "Attempt to delete old feature with FID %d failed.\n%s",
709 0 : (int) poFeature->GetFID(), pszErrMsg );
710 0 : return OGRERR_FAILURE;
711 : }
712 :
713 : /* -------------------------------------------------------------------- */
714 : /* Recreate the feature. */
715 : /* -------------------------------------------------------------------- */
716 1 : return CreateFeature( poFeature );
717 : }
718 :
719 : /************************************************************************/
720 : /* CreateFeature() */
721 : /************************************************************************/
722 :
723 40 : OGRErr OGRSQLiteTableLayer::CreateFeature( OGRFeature *poFeature )
724 :
725 : {
726 40 : sqlite3 *hDB = poDS->GetDB();
727 40 : CPLString osCommand;
728 40 : CPLString osValues;
729 40 : int bNeedComma = FALSE;
730 :
731 40 : ResetReading();
732 :
733 : /* -------------------------------------------------------------------- */
734 : /* Form the INSERT command. */
735 : /* -------------------------------------------------------------------- */
736 40 : osCommand += CPLSPrintf( "INSERT INTO '%s' (", poFeatureDefn->GetName() );
737 :
738 : /* -------------------------------------------------------------------- */
739 : /* Add FID if we have a cleartext FID column. */
740 : /* -------------------------------------------------------------------- */
741 40 : if( pszFIDColumn != NULL // && !EQUAL(pszFIDColumn,"OGC_FID")
742 : && poFeature->GetFID() != OGRNullFID )
743 : {
744 2 : osCommand += pszFIDColumn;
745 :
746 2 : osValues += CPLSPrintf( "%ld", poFeature->GetFID() );
747 2 : bNeedComma = TRUE;
748 : }
749 :
750 : /* -------------------------------------------------------------------- */
751 : /* Add geometry. */
752 : /* -------------------------------------------------------------------- */
753 40 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
754 :
755 40 : if( osGeomColumn.size() != 0 &&
756 : poGeom != NULL &&
757 : eGeomFormat != OSGF_FGF )
758 : {
759 :
760 38 : if( bNeedComma )
761 : {
762 2 : osCommand += ",";
763 2 : osValues += ",";
764 : }
765 :
766 38 : osCommand += osGeomColumn;
767 :
768 38 : osValues += "?";
769 :
770 38 : bNeedComma = TRUE;
771 : }
772 :
773 : /* -------------------------------------------------------------------- */
774 : /* Add field values. */
775 : /* -------------------------------------------------------------------- */
776 : int iField;
777 40 : int nFieldCount = poFeatureDefn->GetFieldCount();
778 :
779 130 : for( iField = 0; iField < nFieldCount; iField++ )
780 : {
781 90 : if( !poFeature->IsFieldSet( iField ) )
782 41 : continue;
783 :
784 49 : if( bNeedComma )
785 : {
786 47 : osCommand += ",";
787 47 : osValues += ",";
788 : }
789 :
790 49 : osCommand += "'";
791 49 : osCommand +=poFeatureDefn->GetFieldDefn(iField)->GetNameRef();
792 49 : osCommand += "'";
793 :
794 49 : osValues += "?";
795 :
796 49 : bNeedComma = TRUE;
797 : }
798 :
799 : /* -------------------------------------------------------------------- */
800 : /* Merge final command. */
801 : /* -------------------------------------------------------------------- */
802 40 : osCommand += ") VALUES (";
803 40 : osCommand += osValues;
804 40 : osCommand += ")";
805 :
806 : /* -------------------------------------------------------------------- */
807 : /* Prepare the statement. */
808 : /* -------------------------------------------------------------------- */
809 : int rc;
810 : sqlite3_stmt *hInsertStmt;
811 :
812 : #ifdef DEBUG
813 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
814 : #endif
815 :
816 40 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
817 40 : if( rc != SQLITE_OK )
818 : {
819 : CPLError( CE_Failure, CPLE_AppDefined,
820 : "In CreateFeature(): sqlite3_prepare(%s):\n %s",
821 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
822 :
823 0 : return OGRERR_FAILURE;
824 : }
825 :
826 : /* -------------------------------------------------------------------- */
827 : /* Bind the geometry */
828 : /* -------------------------------------------------------------------- */
829 40 : int nBindField = 1;
830 :
831 40 : if( osGeomColumn.size() != 0 &&
832 : poGeom != NULL &&
833 : eGeomFormat != OSGF_FGF )
834 : {
835 38 : if ( eGeomFormat == OSGF_WKT )
836 : {
837 1 : char *pszWKT = NULL;
838 1 : poGeom->exportToWkt( &pszWKT );
839 1 : rc = sqlite3_bind_text( hInsertStmt, nBindField++, pszWKT, -1, CPLFree );
840 : }
841 37 : else if( eGeomFormat == OSGF_WKB )
842 : {
843 21 : int nWKBLen = poGeom->WkbSize();
844 21 : GByte *pabyWKB = (GByte *) CPLMalloc(nWKBLen + 1);
845 :
846 21 : poGeom->exportToWkb( wkbNDR, pabyWKB );
847 21 : rc = sqlite3_bind_blob( hInsertStmt, nBindField++, pabyWKB, nWKBLen, CPLFree );
848 : }
849 16 : else if ( eGeomFormat == OSGF_SpatiaLite )
850 : {
851 : int nBLOBLen;
852 : GByte *pabySLBLOB;
853 :
854 : ExportSpatiaLiteGeometry( poGeom, nSRSId, wkbNDR,
855 16 : &pabySLBLOB, &nBLOBLen );
856 16 : rc = sqlite3_bind_blob( hInsertStmt, nBindField++, pabySLBLOB, nBLOBLen, CPLFree );
857 : }
858 : else
859 : {
860 : CPLAssert(0);
861 : }
862 :
863 38 : if( rc != SQLITE_OK )
864 : {
865 : CPLError( CE_Failure, CPLE_AppDefined,
866 : "sqlite3_bind_blob/text() failed:\n %s",
867 0 : sqlite3_errmsg(hDB) );
868 :
869 0 : sqlite3_finalize( hInsertStmt );
870 0 : return OGRERR_FAILURE;
871 : }
872 : }
873 :
874 : /* -------------------------------------------------------------------- */
875 : /* Bind field values. */
876 : /* -------------------------------------------------------------------- */
877 :
878 130 : for( iField = 0; iField < nFieldCount; iField++ )
879 : {
880 : const char *pszRawValue;
881 :
882 90 : if( !poFeature->IsFieldSet( iField ) )
883 41 : continue;
884 :
885 49 : switch( poFeatureDefn->GetFieldDefn(iField)->GetType() )
886 : {
887 : case OFTInteger:
888 : {
889 13 : int nFieldVal = poFeature->GetFieldAsInteger( iField );
890 13 : rc = sqlite3_bind_int(hInsertStmt, nBindField++, nFieldVal);
891 13 : break;
892 : }
893 :
894 : case OFTReal:
895 : {
896 13 : double dfFieldVal = poFeature->GetFieldAsDouble( iField );
897 13 : rc = sqlite3_bind_double(hInsertStmt, nBindField++, dfFieldVal);
898 13 : break;
899 : }
900 :
901 : case OFTBinary:
902 : {
903 1 : int nDataLength = 0;
904 : GByte* pabyData =
905 1 : poFeature->GetFieldAsBinary( iField, &nDataLength );
906 : rc = sqlite3_bind_blob(hInsertStmt, nBindField++,
907 1 : pabyData, nDataLength, SQLITE_TRANSIENT);
908 1 : break;
909 : }
910 :
911 : default:
912 : {
913 22 : pszRawValue = poFeature->GetFieldAsString( iField );
914 : rc = sqlite3_bind_text(hInsertStmt, nBindField++,
915 22 : pszRawValue, -1, SQLITE_TRANSIENT);
916 : break;
917 : }
918 : }
919 :
920 49 : if( rc != SQLITE_OK )
921 : {
922 : CPLError( CE_Failure, CPLE_AppDefined,
923 : "sqlite3_bind_() for column %s failed:\n %s",
924 : poFeatureDefn->GetFieldDefn(iField)->GetNameRef(),
925 0 : sqlite3_errmsg(hDB) );
926 :
927 0 : sqlite3_finalize( hInsertStmt );
928 0 : return OGRERR_FAILURE;
929 : }
930 : }
931 :
932 : /* -------------------------------------------------------------------- */
933 : /* Execute the insert. */
934 : /* -------------------------------------------------------------------- */
935 40 : rc = sqlite3_step( hInsertStmt );
936 :
937 40 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
938 : {
939 : CPLError( CE_Failure, CPLE_AppDefined,
940 : "sqlite3_step() failed:\n %s",
941 0 : sqlite3_errmsg(hDB) );
942 :
943 0 : sqlite3_finalize( hInsertStmt );
944 0 : return OGRERR_FAILURE;
945 : }
946 :
947 : /* -------------------------------------------------------------------- */
948 : /* Capture the FID/rowid. */
949 : /* -------------------------------------------------------------------- */
950 40 : const sqlite_int64 nFID = sqlite3_last_insert_rowid( hDB );
951 40 : if(nFID > 0)
952 : {
953 40 : poFeature->SetFID( nFID );
954 : }
955 :
956 40 : sqlite3_finalize( hInsertStmt );
957 :
958 40 : return OGRERR_NONE;
959 : }
960 :
|