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