1 : /******************************************************************************
2 : * $Id: ogrsqlitetablelayer.cpp 23543 2011-12-12 00:25:51Z 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 <string>
35 :
36 : CPL_CVSID("$Id: ogrsqlitetablelayer.cpp 23543 2011-12-12 00:25:51Z rouault $");
37 :
38 : /************************************************************************/
39 : /* OGRSQLiteTableLayer() */
40 : /************************************************************************/
41 :
42 1020 : OGRSQLiteTableLayer::OGRSQLiteTableLayer( OGRSQLiteDataSource *poDSIn )
43 :
44 : {
45 1020 : poDS = poDSIn;
46 :
47 1020 : bSpatialite2D = FALSE;
48 1020 : bLaunderColumnNames = TRUE;
49 :
50 1020 : iNextShapeId = 0;
51 :
52 1020 : nSRSId = -1;
53 :
54 1020 : poFeatureDefn = NULL;
55 1020 : pszEscapedTableName = NULL;
56 :
57 1020 : bHasCheckedSpatialIndexTable = FALSE;
58 1020 : }
59 :
60 : /************************************************************************/
61 : /* ~OGRSQLiteTableLayer() */
62 : /************************************************************************/
63 :
64 1020 : OGRSQLiteTableLayer::~OGRSQLiteTableLayer()
65 :
66 : {
67 1020 : ClearStatement();
68 1020 : CPLFree(pszEscapedTableName);
69 1020 : }
70 :
71 : /************************************************************************/
72 : /* Initialize() */
73 : /************************************************************************/
74 :
75 1020 : CPLErr OGRSQLiteTableLayer::Initialize( const char *pszTableName,
76 : const char *pszGeomCol,
77 : OGRwkbGeometryType eGeomType,
78 : const char *pszGeomFormat,
79 : OGRSpatialReference *poSRS,
80 : int nSRSId,
81 : int bHasSpatialIndex,
82 : int bHasM,
83 : int bSpatialiteReadOnly,
84 : int bSpatialiteLoaded,
85 : int iSpatialiteVersion,
86 : int bIsVirtualShapeIn )
87 :
88 : {
89 : int rc;
90 1020 : sqlite3 *hDB = poDS->GetDB();
91 :
92 1020 : if( pszGeomCol == NULL )
93 767 : osGeomColumn = "";
94 : else
95 253 : osGeomColumn = pszGeomCol;
96 :
97 1020 : if( pszGeomFormat )
98 : {
99 253 : if ( EQUAL(pszGeomFormat, "WKT") )
100 7 : eGeomFormat = OSGF_WKT;
101 246 : else if ( EQUAL(pszGeomFormat,"WKB") )
102 41 : eGeomFormat = OSGF_WKB;
103 205 : else if ( EQUAL(pszGeomFormat,"FGF") )
104 1 : eGeomFormat = OSGF_FGF;
105 204 : else if( EQUAL(pszGeomFormat,"SpatiaLite") )
106 204 : eGeomFormat = OSGF_SpatiaLite;
107 : }
108 :
109 1020 : CPLFree( pszFIDColumn );
110 1020 : pszFIDColumn = NULL;
111 :
112 1020 : this->poSRS = poSRS;
113 1020 : this->nSRSId = nSRSId;
114 1020 : this->bHasSpatialIndex = bHasSpatialIndex;
115 1020 : this->bHasM = bHasM;
116 1020 : this->bSpatialiteReadOnly = bSpatialiteReadOnly;
117 1020 : this->bSpatialiteLoaded = bSpatialiteLoaded;
118 1020 : this->iSpatialiteVersion = iSpatialiteVersion;
119 1020 : this->bIsVirtualShape = bIsVirtualShapeIn;
120 :
121 1020 : pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszTableName));
122 :
123 : CPLErr eErr;
124 1020 : sqlite3_stmt *hColStmt = NULL;
125 : const char *pszSQL;
126 :
127 1224 : if ( eGeomFormat == OSGF_SpatiaLite &&
128 : bSpatialiteLoaded == TRUE &&
129 : iSpatialiteVersion < 24 && poDS->GetUpdate() )
130 : {
131 : // we need to test version required by Spatialite TRIGGERs
132 0 : hColStmt = NULL;
133 : pszSQL = CPLSPrintf( "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND tbl_name = '%s' AND sql LIKE '%%RTreeAlign%%'",
134 0 : pszEscapedTableName );
135 :
136 : int nRowTriggerCount, nColTriggerCount;
137 : char **papszTriggerResult, *pszErrMsg;
138 :
139 : rc = sqlite3_get_table( hDB, pszSQL, &papszTriggerResult,
140 0 : &nRowTriggerCount, &nColTriggerCount, &pszErrMsg );
141 0 : if( nRowTriggerCount >= 1 )
142 : {
143 : // obsolete library version not supporting new triggers
144 : // enforcing ReadOnly mode
145 0 : CPLDebug("SQLITE", "Enforcing ReadOnly mode : obsolete library version not supporting new triggers");
146 0 : this->bSpatialiteReadOnly = TRUE;
147 : }
148 :
149 0 : sqlite3_free_table( papszTriggerResult );
150 : }
151 :
152 1020 : if( poSRS )
153 213 : poSRS->Reference();
154 :
155 : /* -------------------------------------------------------------------- */
156 : /* Get the column definitions for this table. */
157 : /* -------------------------------------------------------------------- */
158 1020 : hColStmt = NULL;
159 : pszSQL = CPLSPrintf( "SELECT _rowid_, * FROM '%s' LIMIT 1",
160 1020 : pszEscapedTableName );
161 :
162 1020 : rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
163 1020 : if( rc != SQLITE_OK )
164 : {
165 : CPLError( CE_Failure, CPLE_AppDefined,
166 : "Unable to query table %s for column definitions : %s.",
167 0 : pszTableName, sqlite3_errmsg(hDB) );
168 :
169 0 : return CE_Failure;
170 : }
171 :
172 1020 : rc = sqlite3_step( hColStmt );
173 1020 : if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
174 : {
175 : CPLError( CE_Failure, CPLE_AppDefined,
176 : "In Initialize(): sqlite3_step(%s):\n %s",
177 0 : pszSQL, sqlite3_errmsg(hDB) );
178 0 : sqlite3_finalize( hColStmt );
179 0 : return CE_Failure;
180 : }
181 :
182 : /* -------------------------------------------------------------------- */
183 : /* What should we use as FID? If there is a primary key */
184 : /* integer field, then this will be used as the _rowid_, and we */
185 : /* will pick up the real column name here. Otherwise, we will */
186 : /* just use fid. */
187 : /* */
188 : /* Note that the select _rowid_ will return the real column */
189 : /* name if the rowid corresponds to another primary key */
190 : /* column. */
191 : /* -------------------------------------------------------------------- */
192 1020 : pszFIDColumn = CPLStrdup(sqlite3_column_name( hColStmt, 0 ));
193 :
194 : /* -------------------------------------------------------------------- */
195 : /* Collect the rest of the fields. */
196 : /* -------------------------------------------------------------------- */
197 1020 : eErr = BuildFeatureDefn( pszTableName, hColStmt );
198 1020 : sqlite3_finalize( hColStmt );
199 :
200 1020 : if( eErr != CE_None )
201 0 : return eErr;
202 :
203 : /* -------------------------------------------------------------------- */
204 : /* Set the geometry type if we know it. */
205 : /* -------------------------------------------------------------------- */
206 1020 : if( eGeomType != wkbUnknown )
207 177 : poFeatureDefn->SetGeomType( eGeomType );
208 :
209 1020 : return CE_None;
210 : }
211 :
212 : /************************************************************************/
213 : /* ResetStatement() */
214 : /************************************************************************/
215 :
216 156 : OGRErr OGRSQLiteTableLayer::ResetStatement()
217 :
218 : {
219 : int rc;
220 156 : CPLString osSQL;
221 :
222 156 : ClearStatement();
223 :
224 156 : iNextShapeId = 0;
225 :
226 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' %s",
227 : pszEscapedTableName,
228 156 : osWHERE.c_str() );
229 :
230 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
231 156 : &hStmt, NULL );
232 :
233 156 : if( rc == SQLITE_OK )
234 : {
235 155 : return OGRERR_NONE;
236 : }
237 : else
238 : {
239 : CPLError( CE_Failure, CPLE_AppDefined,
240 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
241 1 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
242 1 : hStmt = NULL;
243 1 : return OGRERR_FAILURE;
244 0 : }
245 : }
246 :
247 :
248 : /************************************************************************/
249 : /* GetFeature() */
250 : /************************************************************************/
251 :
252 1 : OGRFeature *OGRSQLiteTableLayer::GetFeature( long nFeatureId )
253 :
254 : {
255 : /* -------------------------------------------------------------------- */
256 : /* If we don't have an explicit FID column, just read through */
257 : /* the result set iteratively to find our target. */
258 : /* -------------------------------------------------------------------- */
259 1 : if( pszFIDColumn == NULL )
260 0 : return OGRSQLiteLayer::GetFeature( nFeatureId );
261 :
262 : /* -------------------------------------------------------------------- */
263 : /* Setup explicit query statement to fetch the record we want. */
264 : /* -------------------------------------------------------------------- */
265 1 : CPLString osSQL;
266 : int rc;
267 :
268 1 : ClearStatement();
269 :
270 1 : iNextShapeId = nFeatureId;
271 :
272 : osSQL.Printf( "SELECT _rowid_, * FROM '%s' WHERE \"%s\" = %d",
273 : pszEscapedTableName,
274 1 : pszFIDColumn, (int) nFeatureId );
275 :
276 1 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
277 :
278 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
279 1 : &hStmt, NULL );
280 1 : if( rc != SQLITE_OK )
281 : {
282 : CPLError( CE_Failure, CPLE_AppDefined,
283 : "In GetFeature(): sqlite3_prepare(%s):\n %s",
284 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
285 :
286 0 : return NULL;
287 : }
288 : /* -------------------------------------------------------------------- */
289 : /* Get the feature if possible. */
290 : /* -------------------------------------------------------------------- */
291 1 : OGRFeature *poFeature = NULL;
292 :
293 1 : poFeature = GetNextRawFeature();
294 :
295 1 : ResetReading();
296 :
297 1 : return poFeature;
298 : }
299 :
300 : /************************************************************************/
301 : /* SetAttributeFilter() */
302 : /************************************************************************/
303 :
304 29 : OGRErr OGRSQLiteTableLayer::SetAttributeFilter( const char *pszQuery )
305 :
306 : {
307 29 : if( pszQuery == NULL )
308 12 : osQuery = "";
309 : else
310 17 : osQuery = pszQuery;
311 :
312 29 : BuildWhere();
313 :
314 29 : ResetReading();
315 :
316 29 : return OGRERR_NONE;
317 : }
318 :
319 :
320 : /************************************************************************/
321 : /* SetSpatialFilter() */
322 : /************************************************************************/
323 :
324 11 : void OGRSQLiteTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
325 :
326 : {
327 11 : if( InstallFilter( poGeomIn ) )
328 : {
329 5 : BuildWhere();
330 :
331 5 : ResetReading();
332 : }
333 11 : }
334 :
335 : /************************************************************************/
336 : /* CheckSpatialIndexTable() */
337 : /************************************************************************/
338 :
339 31 : int OGRSQLiteTableLayer::CheckSpatialIndexTable()
340 : {
341 31 : if (bHasSpatialIndex && !bHasCheckedSpatialIndexTable)
342 : {
343 25 : bHasCheckedSpatialIndexTable = TRUE;
344 : char **papszResult;
345 : int nRowCount, nColCount;
346 25 : char *pszErrMsg = NULL;
347 :
348 25 : CPLString osSQL;
349 : osSQL.Printf("SELECT name FROM sqlite_master "
350 : "WHERE name='idx_%s_%s'",
351 25 : pszEscapedTableName, osGeomColumn.c_str());
352 :
353 : int rc = sqlite3_get_table( poDS->GetDB(), osSQL.c_str(),
354 : &papszResult, &nRowCount,
355 25 : &nColCount, &pszErrMsg );
356 :
357 25 : if( rc != SQLITE_OK )
358 : {
359 : CPLError( CE_Failure, CPLE_AppDefined, "Error: %s",
360 0 : pszErrMsg );
361 0 : sqlite3_free( pszErrMsg );
362 0 : bHasSpatialIndex = FALSE;
363 : }
364 : else
365 : {
366 25 : if (nRowCount != 1)
367 : {
368 0 : bHasSpatialIndex = FALSE;
369 : CPLDebug("SQLITE", "Count not find idx_%s_%s layer. Disabling spatial index",
370 0 : pszEscapedTableName, osGeomColumn.c_str());
371 : }
372 :
373 25 : sqlite3_free_table(papszResult);
374 25 : }
375 : }
376 :
377 31 : return bHasSpatialIndex;
378 : }
379 :
380 : /************************************************************************/
381 : /* BuildWhere() */
382 : /* */
383 : /* Build the WHERE statement appropriate to the current set of */
384 : /* criteria (spatial and attribute queries). */
385 : /************************************************************************/
386 :
387 34 : void OGRSQLiteTableLayer::BuildWhere()
388 :
389 : {
390 34 : osWHERE = "";
391 :
392 40 : if( m_poFilterGeom != NULL && CheckSpatialIndexTable() )
393 : {
394 1 : OGREnvelope sEnvelope;
395 :
396 1 : m_poFilterGeom->getEnvelope( &sEnvelope );
397 :
398 : osWHERE.Printf("WHERE ROWID IN ( SELECT pkid FROM 'idx_%s_%s' WHERE "
399 : "xmax >= %.12f AND xmin <= %.12f AND ymax >= %.12f AND ymin <= %.12f) ",
400 : pszEscapedTableName, osGeomColumn.c_str(),
401 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
402 1 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
403 : }
404 :
405 34 : if( m_poFilterGeom != NULL && bSpatialiteLoaded && !bHasSpatialIndex)
406 : {
407 1 : OGREnvelope sEnvelope;
408 :
409 1 : m_poFilterGeom->getEnvelope( &sEnvelope );
410 :
411 : /* A bit inefficient but still faster than OGR filtering */
412 : osWHERE.Printf("WHERE MBRIntersects(\"%s\", BuildMBR(%.12f, %.12f, %.12f, %.12f, %d)) ",
413 : osGeomColumn.c_str(),
414 : sEnvelope.MinX - 1e-11, sEnvelope.MinY - 1e-11,
415 : sEnvelope.MaxX + 1e-11, sEnvelope.MaxY + 1e-11,
416 1 : nSRSId);
417 : }
418 :
419 34 : if( strlen(osQuery) > 0 )
420 : {
421 15 : if( strlen(osWHERE) == 0 )
422 : {
423 15 : osWHERE.Printf( "WHERE %s ", osQuery.c_str() );
424 : }
425 : else
426 : {
427 0 : osWHERE += "AND ";
428 0 : osWHERE += osQuery;
429 : }
430 : }
431 34 : }
432 :
433 : /************************************************************************/
434 : /* TestCapability() */
435 : /************************************************************************/
436 :
437 19 : int OGRSQLiteTableLayer::TestCapability( const char * pszCap )
438 :
439 : {
440 19 : if (EQUAL(pszCap,OLCFastFeatureCount))
441 : return m_poFilterGeom == NULL || osGeomColumn.size() == 0 ||
442 12 : bHasSpatialIndex;
443 :
444 7 : else if (EQUAL(pszCap,OLCFastSpatialFilter))
445 2 : return bHasSpatialIndex;
446 :
447 5 : else if( EQUAL(pszCap,OLCRandomRead) )
448 0 : return pszFIDColumn != NULL;
449 :
450 5 : else if( EQUAL(pszCap,OLCSequentialWrite)
451 : || EQUAL(pszCap,OLCRandomWrite) )
452 : {
453 0 : if ( bSpatialiteReadOnly == TRUE)
454 0 : return FALSE;
455 0 : return poDS->GetUpdate();
456 : }
457 :
458 5 : else if( EQUAL(pszCap,OLCCreateField) )
459 0 : return poDS->GetUpdate();
460 :
461 5 : else if( EQUAL(pszCap,OLCDeleteField) )
462 1 : return poDS->GetUpdate();
463 :
464 4 : else if( EQUAL(pszCap,OLCAlterFieldDefn) )
465 1 : return poDS->GetUpdate();
466 :
467 3 : else if( EQUAL(pszCap,OLCReorderFields) )
468 1 : return poDS->GetUpdate();
469 :
470 : else
471 2 : return OGRSQLiteLayer::TestCapability( pszCap );
472 : }
473 :
474 : /************************************************************************/
475 : /* GetFeatureCount() */
476 : /* */
477 : /* If a spatial filter is in effect, we turn control over to */
478 : /* the generic counter. Otherwise we return the total count. */
479 : /* Eventually we should consider implementing a more efficient */
480 : /* way of counting features matching a spatial query. */
481 : /************************************************************************/
482 :
483 10 : int OGRSQLiteTableLayer::GetFeatureCount( int bForce )
484 :
485 : {
486 10 : if( !TestCapability(OLCFastFeatureCount) )
487 3 : return OGRSQLiteLayer::GetFeatureCount( bForce );
488 :
489 : /* -------------------------------------------------------------------- */
490 : /* Form count SQL. */
491 : /* -------------------------------------------------------------------- */
492 : const char *pszSQL;
493 :
494 7 : if (m_poFilterGeom != NULL && CheckSpatialIndexTable() &&
495 : strlen(osQuery) == 0)
496 : {
497 1 : OGREnvelope sEnvelope;
498 :
499 1 : m_poFilterGeom->getEnvelope( &sEnvelope );
500 : pszSQL = CPLSPrintf("SELECT count(*) FROM 'idx_%s_%s' WHERE "
501 : "xmax >= %.12f AND xmin <= %.12f AND ymax >= %.12f AND ymin <= %.12f",
502 : pszEscapedTableName, osGeomColumn.c_str(),
503 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
504 1 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
505 : }
506 : else
507 : {
508 : pszSQL = CPLSPrintf( "SELECT count(*) FROM '%s' %s",
509 6 : pszEscapedTableName, osWHERE.c_str() );
510 : }
511 :
512 7 : CPLDebug("SQLITE", "Running %s", pszSQL);
513 :
514 : /* -------------------------------------------------------------------- */
515 : /* Execute. */
516 : /* -------------------------------------------------------------------- */
517 : char **papszResult, *pszErrMsg;
518 : int nRowCount, nColCount;
519 7 : int nResult = -1;
520 :
521 7 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
522 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
523 0 : return -1;
524 :
525 7 : if( nRowCount == 1 && nColCount == 1 )
526 7 : nResult = atoi(papszResult[1]);
527 :
528 7 : sqlite3_free_table( papszResult );
529 :
530 7 : return nResult;
531 : }
532 :
533 : /************************************************************************/
534 : /* GetExtent() */
535 : /************************************************************************/
536 :
537 24 : OGRErr OGRSQLiteTableLayer::GetExtent(OGREnvelope *psExtent, int bForce)
538 : {
539 24 : if (CheckSpatialIndexTable())
540 : {
541 : const char* pszSQL;
542 :
543 : pszSQL = CPLSPrintf("SELECT MIN(xmin), MIN(ymin), MAX(xmax), MAX(ymax) FROM 'idx_%s_%s'",
544 24 : pszEscapedTableName, osGeomColumn.c_str());
545 :
546 24 : CPLDebug("SQLITE", "Running %s", pszSQL);
547 :
548 : /* -------------------------------------------------------------------- */
549 : /* Execute. */
550 : /* -------------------------------------------------------------------- */
551 : char **papszResult, *pszErrMsg;
552 : int nRowCount, nColCount;
553 :
554 24 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
555 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
556 0 : return OGRSQLiteLayer::GetExtent(psExtent, bForce);
557 :
558 24 : OGRErr eErr = OGRERR_FAILURE;
559 :
560 114 : if( nRowCount == 1 && nColCount == 4 &&
561 24 : papszResult[4+0] != NULL &&
562 22 : papszResult[4+1] != NULL &&
563 22 : papszResult[4+2] != NULL &&
564 22 : papszResult[4+3] != NULL)
565 : {
566 22 : psExtent->MinX = atof(papszResult[4+0]);
567 22 : psExtent->MinY = atof(papszResult[4+1]);
568 22 : psExtent->MaxX = atof(papszResult[4+2]);
569 22 : psExtent->MaxY = atof(papszResult[4+3]);
570 22 : eErr = OGRERR_NONE;
571 : }
572 :
573 24 : sqlite3_free_table( papszResult );
574 :
575 24 : if (eErr == OGRERR_NONE)
576 22 : return eErr;
577 : }
578 :
579 2 : return OGRSQLiteLayer::GetExtent(psExtent, bForce);
580 : }
581 :
582 : /************************************************************************/
583 : /* OGRFieldTypeToSQliteType() */
584 : /************************************************************************/
585 :
586 117 : static const char* OGRFieldTypeToSQliteType( OGRFieldType eType )
587 : {
588 117 : if( eType == OFTInteger )
589 12 : return "INTEGER";
590 105 : else if( eType == OFTReal )
591 5 : return "FLOAT";
592 100 : else if( eType == OFTBinary )
593 5 : return "BLOB";
594 : else
595 95 : return "VARCHAR";
596 : }
597 :
598 : /************************************************************************/
599 : /* CreateField() */
600 : /************************************************************************/
601 :
602 28 : OGRErr OGRSQLiteTableLayer::CreateField( OGRFieldDefn *poFieldIn,
603 : int bApproxOK )
604 :
605 : {
606 28 : OGRFieldDefn oField( poFieldIn );
607 :
608 28 : ResetReading();
609 :
610 28 : if (!poDS->GetUpdate())
611 : {
612 : CPLError( CE_Failure, CPLE_NotSupported,
613 0 : "Can't create fields on a read-only layer.");
614 0 : return OGRERR_FAILURE;
615 : }
616 :
617 : /* -------------------------------------------------------------------- */
618 : /* Do we want to "launder" the column names into SQLite */
619 : /* friendly format? */
620 : /* -------------------------------------------------------------------- */
621 28 : if( bLaunderColumnNames )
622 : {
623 28 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
624 :
625 28 : oField.SetName( pszSafeName );
626 28 : CPLFree( pszSafeName );
627 : }
628 :
629 : /* ADD COLUMN only avaliable since sqlite 3.1.3 */
630 28 : if (CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_USE_ADD_COLUMN", "YES")) &&
631 : sqlite3_libversion_number() > 3 * 1000000 + 1 * 1000 + 3)
632 : {
633 : int rc;
634 28 : char *pszErrMsg = NULL;
635 28 : sqlite3 *hDB = poDS->GetDB();
636 28 : CPLString osCommand;
637 :
638 : osCommand.Printf("ALTER TABLE '%s' ADD COLUMN '%s' %s",
639 : pszEscapedTableName,
640 : oField.GetNameRef(),
641 28 : OGRFieldTypeToSQliteType(oField.GetType()));
642 :
643 : #ifdef DEBUG
644 28 : CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
645 : #endif
646 :
647 28 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
648 28 : if( rc != SQLITE_OK )
649 : {
650 : CPLError( CE_Failure, CPLE_AppDefined,
651 : "Failed to add field %s to table %s:\n %s",
652 : oField.GetNameRef(), poFeatureDefn->GetName(),
653 0 : pszErrMsg );
654 0 : sqlite3_free( pszErrMsg );
655 0 : return OGRERR_FAILURE;
656 0 : }
657 : }
658 : else
659 : {
660 0 : OGRErr eErr = AddColumnAncientMethod(oField);
661 0 : if (eErr != OGRERR_NONE)
662 0 : return eErr;
663 : }
664 :
665 : /* -------------------------------------------------------------------- */
666 : /* Add the field to the OGRFeatureDefn. */
667 : /* -------------------------------------------------------------------- */
668 : int iNewField;
669 28 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
670 :
671 28 : if( poFeatureDefn->GetGeomType() != wkbNone )
672 : {
673 28 : iNextOrdinal++;
674 : }
675 :
676 71 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
677 : {
678 43 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
679 :
680 : // we already added OGC_FID so don't do it again
681 43 : if( EQUAL(poFldDefn->GetNameRef(),"OGC_FID") )
682 0 : continue;
683 :
684 43 : iNextOrdinal++;
685 : }
686 :
687 28 : poFeatureDefn->AddFieldDefn( &oField );
688 :
689 28 : iNewField = poFeatureDefn->GetFieldCount() - 1;
690 : panFieldOrdinals = (int *)
691 28 : CPLRealloc(panFieldOrdinals, (iNewField+1) * sizeof(int) );
692 28 : panFieldOrdinals[iNewField] = iNextOrdinal;
693 :
694 28 : return OGRERR_NONE;
695 : }
696 :
697 : /************************************************************************/
698 : /* InitFieldListForRecrerate() */
699 : /************************************************************************/
700 :
701 23 : void OGRSQLiteTableLayer::InitFieldListForRecrerate(char* & pszNewFieldList,
702 : char* & pszFieldListForSelect,
703 : int nExtraSpace)
704 : {
705 23 : int iField, nFieldListLen = 100 + nExtraSpace;
706 :
707 118 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
708 : {
709 : nFieldListLen +=
710 95 : strlen(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()) + 50;
711 : }
712 :
713 23 : pszFieldListForSelect = (char *) CPLCalloc(1,nFieldListLen);
714 23 : pszNewFieldList = (char *) CPLCalloc(1,nFieldListLen);
715 :
716 : /* -------------------------------------------------------------------- */
717 : /* Build list of old fields, and the list of new fields. */
718 : /* -------------------------------------------------------------------- */
719 23 : sprintf( pszFieldListForSelect, "%s", pszFIDColumn ? pszFIDColumn : "OGC_FID" );
720 23 : sprintf( pszNewFieldList, "%s INTEGER PRIMARY KEY",pszFIDColumn ? pszFIDColumn : "OGC_FID" );
721 :
722 23 : if( poFeatureDefn->GetGeomType() != wkbNone )
723 : {
724 23 : strcat( pszFieldListForSelect, "," );
725 23 : strcat( pszNewFieldList, "," );
726 :
727 23 : strcat( pszFieldListForSelect, osGeomColumn );
728 23 : strcat( pszNewFieldList, osGeomColumn );
729 :
730 23 : if ( eGeomFormat == OSGF_WKT )
731 0 : strcat( pszNewFieldList, " VARCHAR" );
732 : else
733 23 : strcat( pszNewFieldList, " BLOB" );
734 : }
735 23 : }
736 :
737 : /************************************************************************/
738 : /* AddColumnAncientMethod() */
739 : /************************************************************************/
740 :
741 0 : OGRErr OGRSQLiteTableLayer::AddColumnAncientMethod( OGRFieldDefn& oField)
742 : {
743 :
744 : /* -------------------------------------------------------------------- */
745 : /* How much space do we need for the list of fields. */
746 : /* -------------------------------------------------------------------- */
747 : int iField;
748 : char *pszOldFieldList, *pszNewFieldList;
749 :
750 : InitFieldListForRecrerate(pszNewFieldList, pszOldFieldList,
751 0 : strlen( oField.GetNameRef() ));
752 :
753 : /* -------------------------------------------------------------------- */
754 : /* Build list of old fields, and the list of new fields. */
755 : /* -------------------------------------------------------------------- */
756 :
757 0 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
758 :
759 0 : if( poFeatureDefn->GetGeomType() != wkbNone )
760 : {
761 0 : iNextOrdinal++;
762 : }
763 :
764 0 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
765 : {
766 0 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
767 :
768 : // we already added OGC_FID so don't do it again
769 0 : if( EQUAL(poFldDefn->GetNameRef(),pszFIDColumn ? pszFIDColumn : "OGC_FID") )
770 0 : continue;
771 :
772 : sprintf( pszOldFieldList+strlen(pszOldFieldList),
773 0 : ", \"%s\"", poFldDefn->GetNameRef() );
774 :
775 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
776 : ", '%s' %s", poFldDefn->GetNameRef(),
777 0 : OGRFieldTypeToSQliteType(poFldDefn->GetType()) );
778 :
779 0 : iNextOrdinal++;
780 : }
781 :
782 : /* -------------------------------------------------------------------- */
783 : /* Add the new field. */
784 : /* -------------------------------------------------------------------- */
785 :
786 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
787 : ", '%s' %s", oField.GetNameRef(),
788 0 : OGRFieldTypeToSQliteType(oField.GetType()) );
789 :
790 : /* ==================================================================== */
791 : /* Backup, destroy, recreate and repopulate the table. SQLite */
792 : /* has no ALTER TABLE so we have to do all this to add a */
793 : /* column. */
794 : /* ==================================================================== */
795 :
796 : /* -------------------------------------------------------------------- */
797 : /* Do this all in a transaction. */
798 : /* -------------------------------------------------------------------- */
799 0 : poDS->SoftStartTransaction();
800 :
801 : /* -------------------------------------------------------------------- */
802 : /* Save existing related triggers and index */
803 : /* -------------------------------------------------------------------- */
804 : int rc;
805 0 : char *pszErrMsg = NULL;
806 0 : sqlite3 *hDB = poDS->GetDB();
807 0 : CPLString osSQL;
808 :
809 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
810 0 : pszEscapedTableName );
811 :
812 : int nRowTriggerIndexCount, nColTriggerIndexCount;
813 0 : char **papszTriggerIndexResult = NULL;
814 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
815 0 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
816 :
817 : /* -------------------------------------------------------------------- */
818 : /* Make a backup of the table. */
819 : /* -------------------------------------------------------------------- */
820 :
821 0 : if( rc == SQLITE_OK )
822 : rc = sqlite3_exec( hDB,
823 : CPLSPrintf( "CREATE TEMPORARY TABLE t1_back(%s)",
824 : pszOldFieldList ),
825 0 : NULL, NULL, &pszErrMsg );
826 :
827 0 : if( rc == SQLITE_OK )
828 : rc = sqlite3_exec( hDB,
829 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
830 : pszOldFieldList,
831 : pszEscapedTableName ),
832 0 : NULL, NULL, &pszErrMsg );
833 :
834 :
835 : /* -------------------------------------------------------------------- */
836 : /* Drop the original table, and recreate with new field. */
837 : /* -------------------------------------------------------------------- */
838 0 : if( rc == SQLITE_OK )
839 : rc = sqlite3_exec( hDB,
840 : CPLSPrintf( "DROP TABLE '%s'",
841 : pszEscapedTableName ),
842 0 : NULL, NULL, &pszErrMsg );
843 :
844 0 : if( rc == SQLITE_OK )
845 : {
846 : const char *pszCmd =
847 : CPLSPrintf( "CREATE TABLE '%s' (%s)",
848 : pszEscapedTableName,
849 0 : pszNewFieldList );
850 : rc = sqlite3_exec( hDB, pszCmd,
851 0 : NULL, NULL, &pszErrMsg );
852 :
853 0 : CPLDebug( "OGR_SQLITE", "exec(%s)", pszCmd );
854 : }
855 :
856 : /* -------------------------------------------------------------------- */
857 : /* Copy backup field values into new table. */
858 : /* -------------------------------------------------------------------- */
859 :
860 0 : if( rc == SQLITE_OK )
861 : rc = sqlite3_exec( hDB,
862 : CPLSPrintf( "INSERT INTO '%s' SELECT %s, NULL FROM t1_back",
863 : pszEscapedTableName,
864 : pszOldFieldList ),
865 0 : NULL, NULL, &pszErrMsg );
866 :
867 0 : CPLFree( pszOldFieldList );
868 0 : CPLFree( pszNewFieldList );
869 :
870 : /* -------------------------------------------------------------------- */
871 : /* Cleanup backup table. */
872 : /* -------------------------------------------------------------------- */
873 :
874 0 : if( rc == SQLITE_OK )
875 : rc = sqlite3_exec( hDB,
876 : CPLSPrintf( "DROP TABLE t1_back" ),
877 0 : NULL, NULL, &pszErrMsg );
878 :
879 : /* -------------------------------------------------------------------- */
880 : /* Recreate existing related tables, triggers and index */
881 : /* -------------------------------------------------------------------- */
882 :
883 0 : if( rc == SQLITE_OK )
884 : {
885 : int i;
886 :
887 0 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
888 : {
889 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
890 : rc = sqlite3_exec( hDB,
891 0 : papszTriggerIndexResult[i],
892 0 : NULL, NULL, &pszErrMsg );
893 : }
894 : }
895 :
896 : /* -------------------------------------------------------------------- */
897 : /* COMMIT on success or ROLLBACK on failuire. */
898 : /* -------------------------------------------------------------------- */
899 :
900 0 : sqlite3_free_table( papszTriggerIndexResult );
901 :
902 0 : if( rc == SQLITE_OK )
903 : {
904 0 : poDS->SoftCommit();
905 : }
906 : else
907 : {
908 : CPLError( CE_Failure, CPLE_AppDefined,
909 : "Failed to add field %s to table %s:\n %s",
910 : oField.GetNameRef(), poFeatureDefn->GetName(),
911 0 : pszErrMsg );
912 0 : sqlite3_free( pszErrMsg );
913 :
914 0 : poDS->SoftRollback();
915 :
916 0 : return OGRERR_FAILURE;
917 : }
918 :
919 0 : return OGRERR_NONE;
920 : }
921 :
922 : /************************************************************************/
923 : /* RecreateTable() */
924 : /************************************************************************/
925 :
926 23 : OGRErr OGRSQLiteTableLayer::RecreateTable(const char* pszFieldListForSelect,
927 : const char* pszNewFieldList,
928 : const char* pszGenericErrorMessage)
929 : {
930 : /* -------------------------------------------------------------------- */
931 : /* Do this all in a transaction. */
932 : /* -------------------------------------------------------------------- */
933 23 : poDS->SoftStartTransaction();
934 :
935 : /* -------------------------------------------------------------------- */
936 : /* Save existing related triggers and index */
937 : /* -------------------------------------------------------------------- */
938 : int rc;
939 23 : char *pszErrMsg = NULL;
940 23 : sqlite3 *hDB = poDS->GetDB();
941 23 : CPLString osSQL;
942 :
943 : osSQL.Printf( "SELECT sql FROM sqlite_master WHERE type IN ('trigger','index') AND tbl_name='%s'",
944 23 : pszEscapedTableName );
945 :
946 : int nRowTriggerIndexCount, nColTriggerIndexCount;
947 23 : char **papszTriggerIndexResult = NULL;
948 : rc = sqlite3_get_table( hDB, osSQL.c_str(), &papszTriggerIndexResult,
949 23 : &nRowTriggerIndexCount, &nColTriggerIndexCount, &pszErrMsg );
950 :
951 : /* -------------------------------------------------------------------- */
952 : /* Make a backup of the table. */
953 : /* -------------------------------------------------------------------- */
954 :
955 23 : if( rc == SQLITE_OK )
956 : rc = sqlite3_exec( hDB,
957 : CPLSPrintf( "CREATE TABLE t1_back(%s)",
958 : pszNewFieldList ),
959 23 : NULL, NULL, &pszErrMsg );
960 :
961 23 : if( rc == SQLITE_OK )
962 : rc = sqlite3_exec( hDB,
963 : CPLSPrintf( "INSERT INTO t1_back SELECT %s FROM '%s'",
964 : pszFieldListForSelect,
965 : pszEscapedTableName ),
966 23 : NULL, NULL, &pszErrMsg );
967 :
968 :
969 : /* -------------------------------------------------------------------- */
970 : /* Drop the original table */
971 : /* -------------------------------------------------------------------- */
972 23 : if( rc == SQLITE_OK )
973 : rc = sqlite3_exec( hDB,
974 : CPLSPrintf( "DROP TABLE '%s'",
975 : pszEscapedTableName ),
976 23 : NULL, NULL, &pszErrMsg );
977 :
978 : /* -------------------------------------------------------------------- */
979 : /* Rename backup table as new table */
980 : /* -------------------------------------------------------------------- */
981 23 : if( rc == SQLITE_OK )
982 : {
983 : const char *pszCmd =
984 : CPLSPrintf( "ALTER TABLE t1_back RENAME TO '%s'",
985 23 : pszEscapedTableName);
986 : rc = sqlite3_exec( hDB, pszCmd,
987 23 : NULL, NULL, &pszErrMsg );
988 : }
989 :
990 : /* -------------------------------------------------------------------- */
991 : /* Recreate existing related tables, triggers and index */
992 : /* -------------------------------------------------------------------- */
993 :
994 23 : if( rc == SQLITE_OK )
995 : {
996 : int i;
997 :
998 23 : for(i = 1; i <= nRowTriggerIndexCount && nColTriggerIndexCount == 1 && rc == SQLITE_OK; i++)
999 : {
1000 0 : if (papszTriggerIndexResult[i] != NULL && papszTriggerIndexResult[i][0] != '\0')
1001 : rc = sqlite3_exec( hDB,
1002 0 : papszTriggerIndexResult[i],
1003 0 : NULL, NULL, &pszErrMsg );
1004 : }
1005 : }
1006 :
1007 : /* -------------------------------------------------------------------- */
1008 : /* COMMIT on success or ROLLBACK on failuire. */
1009 : /* -------------------------------------------------------------------- */
1010 :
1011 23 : sqlite3_free_table( papszTriggerIndexResult );
1012 :
1013 23 : if( rc == SQLITE_OK )
1014 : {
1015 23 : poDS->SoftCommit();
1016 :
1017 23 : return OGRERR_NONE;
1018 : }
1019 : else
1020 : {
1021 : CPLError( CE_Failure, CPLE_AppDefined,
1022 : "%s:\n %s",
1023 : pszGenericErrorMessage,
1024 0 : pszErrMsg );
1025 0 : sqlite3_free( pszErrMsg );
1026 :
1027 0 : poDS->SoftRollback();
1028 :
1029 0 : return OGRERR_FAILURE;
1030 0 : }
1031 : }
1032 :
1033 : /************************************************************************/
1034 : /* DeleteField() */
1035 : /************************************************************************/
1036 :
1037 8 : OGRErr OGRSQLiteTableLayer::DeleteField( int iFieldToDelete )
1038 : {
1039 8 : if (!poDS->GetUpdate())
1040 : {
1041 : CPLError( CE_Failure, CPLE_NotSupported,
1042 0 : "Can't delete fields on a read-only layer.");
1043 0 : return OGRERR_FAILURE;
1044 : }
1045 :
1046 8 : if (iFieldToDelete < 0 || iFieldToDelete >= poFeatureDefn->GetFieldCount())
1047 : {
1048 : CPLError( CE_Failure, CPLE_NotSupported,
1049 2 : "Invalid field index");
1050 2 : return OGRERR_FAILURE;
1051 : }
1052 :
1053 6 : ResetReading();
1054 :
1055 : /* -------------------------------------------------------------------- */
1056 : /* Build list of old fields, and the list of new fields. */
1057 : /* -------------------------------------------------------------------- */
1058 : int iField;
1059 : char *pszNewFieldList, *pszFieldListForSelect;
1060 6 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1061 :
1062 26 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1063 : {
1064 20 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1065 :
1066 20 : if (iField == iFieldToDelete)
1067 6 : continue;
1068 :
1069 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1070 14 : ", \"%s\"", poFldDefn->GetNameRef() );
1071 :
1072 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1073 : ", '%s' %s", poFldDefn->GetNameRef(),
1074 14 : OGRFieldTypeToSQliteType(poFldDefn->GetType()) );
1075 : }
1076 :
1077 : /* -------------------------------------------------------------------- */
1078 : /* Recreate table. */
1079 : /* -------------------------------------------------------------------- */
1080 6 : CPLString osErrorMsg;
1081 : osErrorMsg.Printf("Failed to remove field %s from table %s",
1082 : poFeatureDefn->GetFieldDefn(iFieldToDelete)->GetNameRef(),
1083 6 : poFeatureDefn->GetName());
1084 :
1085 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1086 : pszNewFieldList,
1087 6 : osErrorMsg.c_str());
1088 :
1089 6 : CPLFree( pszFieldListForSelect );
1090 6 : CPLFree( pszNewFieldList );
1091 :
1092 6 : if (eErr != OGRERR_NONE)
1093 0 : return eErr;
1094 :
1095 : /* -------------------------------------------------------------------- */
1096 : /* Finish */
1097 : /* -------------------------------------------------------------------- */
1098 6 : int iNextOrdinal = 3; /* _rowid_ is 1, OGC_FID is 2 */
1099 :
1100 6 : if( poFeatureDefn->GetGeomType() != wkbNone )
1101 : {
1102 6 : iNextOrdinal++;
1103 : }
1104 :
1105 6 : int iNewField = 0;
1106 26 : for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1107 : {
1108 20 : if (iField == iFieldToDelete)
1109 6 : continue;
1110 :
1111 14 : panFieldOrdinals[iNewField ++] = iNextOrdinal++;
1112 : }
1113 :
1114 6 : return poFeatureDefn->DeleteFieldDefn( iFieldToDelete );
1115 : }
1116 :
1117 : /************************************************************************/
1118 : /* AlterFieldDefn() */
1119 : /************************************************************************/
1120 :
1121 9 : OGRErr OGRSQLiteTableLayer::AlterFieldDefn( int iFieldToAlter, OGRFieldDefn* poNewFieldDefn, int nFlags )
1122 : {
1123 9 : if (!poDS->GetUpdate())
1124 : {
1125 : CPLError( CE_Failure, CPLE_NotSupported,
1126 0 : "Can't alter field definition on a read-only layer.");
1127 0 : return OGRERR_FAILURE;
1128 : }
1129 :
1130 9 : if (iFieldToAlter < 0 || iFieldToAlter >= poFeatureDefn->GetFieldCount())
1131 : {
1132 : CPLError( CE_Failure, CPLE_NotSupported,
1133 2 : "Invalid field index");
1134 2 : return OGRERR_FAILURE;
1135 : }
1136 :
1137 7 : ResetReading();
1138 :
1139 : /* -------------------------------------------------------------------- */
1140 : /* Build list of old fields, and the list of new fields. */
1141 : /* -------------------------------------------------------------------- */
1142 : int iField;
1143 : char *pszNewFieldList, *pszFieldListForSelect;
1144 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect,
1145 7 : strlen(poNewFieldDefn->GetNameRef()));
1146 :
1147 40 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1148 : {
1149 33 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(iField);
1150 :
1151 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1152 33 : ", \"%s\"", poFldDefn->GetNameRef() );
1153 :
1154 33 : if (iField == iFieldToAlter)
1155 : {
1156 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1157 : ", '%s' %s",
1158 : (nFlags & ALTER_NAME_FLAG) ? poNewFieldDefn->GetNameRef() :
1159 : poFldDefn->GetNameRef(),
1160 : OGRFieldTypeToSQliteType((nFlags & ALTER_TYPE_FLAG) ?
1161 7 : poNewFieldDefn->GetType() : poFldDefn->GetType()) );
1162 : }
1163 : else
1164 : {
1165 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1166 : ", '%s' %s", poFldDefn->GetNameRef(),
1167 26 : OGRFieldTypeToSQliteType(poFldDefn->GetType()) );
1168 : }
1169 : }
1170 :
1171 : /* -------------------------------------------------------------------- */
1172 : /* Recreate table. */
1173 : /* -------------------------------------------------------------------- */
1174 7 : CPLString osErrorMsg;
1175 : osErrorMsg.Printf("Failed to alter field %s from table %s",
1176 : poFeatureDefn->GetFieldDefn(iFieldToAlter)->GetNameRef(),
1177 7 : poFeatureDefn->GetName());
1178 :
1179 : OGRErr eErr = RecreateTable(pszFieldListForSelect,
1180 : pszNewFieldList,
1181 7 : osErrorMsg.c_str());
1182 :
1183 7 : CPLFree( pszFieldListForSelect );
1184 7 : CPLFree( pszNewFieldList );
1185 :
1186 7 : if (eErr != OGRERR_NONE)
1187 0 : return eErr;
1188 :
1189 : /* -------------------------------------------------------------------- */
1190 : /* Finish */
1191 : /* -------------------------------------------------------------------- */
1192 :
1193 7 : OGRFieldDefn* poFieldDefn = poFeatureDefn->GetFieldDefn(iFieldToAlter);
1194 :
1195 7 : if (nFlags & ALTER_TYPE_FLAG)
1196 7 : poFieldDefn->SetType(poNewFieldDefn->GetType());
1197 7 : if (nFlags & ALTER_NAME_FLAG)
1198 7 : poFieldDefn->SetName(poNewFieldDefn->GetNameRef());
1199 7 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
1200 : {
1201 7 : poFieldDefn->SetWidth(poNewFieldDefn->GetWidth());
1202 7 : poFieldDefn->SetPrecision(poNewFieldDefn->GetPrecision());
1203 : }
1204 7 : return OGRERR_NONE;
1205 : }
1206 :
1207 : /************************************************************************/
1208 : /* ReorderFields() */
1209 : /************************************************************************/
1210 :
1211 12 : OGRErr OGRSQLiteTableLayer::ReorderFields( int* panMap )
1212 : {
1213 12 : if (!poDS->GetUpdate())
1214 : {
1215 : CPLError( CE_Failure, CPLE_NotSupported,
1216 0 : "Can't reorder fields on a read-only layer.");
1217 0 : return OGRERR_FAILURE;
1218 : }
1219 :
1220 12 : if (poFeatureDefn->GetFieldCount() == 0)
1221 1 : return OGRERR_NONE;
1222 :
1223 11 : OGRErr eErr = OGRCheckPermutation(panMap, poFeatureDefn->GetFieldCount());
1224 11 : if (eErr != OGRERR_NONE)
1225 1 : return eErr;
1226 :
1227 10 : ResetReading();
1228 :
1229 : /* -------------------------------------------------------------------- */
1230 : /* Build list of old fields, and the list of new fields. */
1231 : /* -------------------------------------------------------------------- */
1232 : int iField;
1233 : char *pszNewFieldList, *pszFieldListForSelect;
1234 10 : InitFieldListForRecrerate(pszNewFieldList, pszFieldListForSelect);
1235 :
1236 52 : for( iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
1237 : {
1238 42 : OGRFieldDefn *poFldDefn = poFeatureDefn->GetFieldDefn(panMap[iField]);
1239 :
1240 : sprintf( pszFieldListForSelect+strlen(pszFieldListForSelect),
1241 42 : ", \"%s\"", poFldDefn->GetNameRef() );
1242 :
1243 : sprintf( pszNewFieldList+strlen(pszNewFieldList),
1244 : ", '%s' %s", poFldDefn->GetNameRef(),
1245 42 : OGRFieldTypeToSQliteType(poFldDefn->GetType()) );
1246 : }
1247 :
1248 : /* -------------------------------------------------------------------- */
1249 : /* Recreate table. */
1250 : /* -------------------------------------------------------------------- */
1251 10 : CPLString osErrorMsg;
1252 : osErrorMsg.Printf("Failed to reorder fields from table %s",
1253 10 : poFeatureDefn->GetName());
1254 :
1255 : eErr = RecreateTable(pszFieldListForSelect,
1256 : pszNewFieldList,
1257 10 : osErrorMsg.c_str());
1258 :
1259 10 : CPLFree( pszFieldListForSelect );
1260 10 : CPLFree( pszNewFieldList );
1261 :
1262 10 : if (eErr != OGRERR_NONE)
1263 0 : return eErr;
1264 :
1265 : /* -------------------------------------------------------------------- */
1266 : /* Finish */
1267 : /* -------------------------------------------------------------------- */
1268 :
1269 10 : return poFeatureDefn->ReorderFieldDefns( panMap );
1270 : }
1271 :
1272 : /************************************************************************/
1273 : /* BindValues() */
1274 : /************************************************************************/
1275 :
1276 : /* the bBindNullValues is set to TRUE by SetFeature() for UPDATE statements, */
1277 : /* and to FALSE by CreateFeature() for INSERT statements; */
1278 :
1279 295 : OGRErr OGRSQLiteTableLayer::BindValues( OGRFeature *poFeature,
1280 : sqlite3_stmt* hStmt,
1281 : int bBindNullValues )
1282 : {
1283 : int rc;
1284 295 : sqlite3 *hDB = poDS->GetDB();
1285 :
1286 : /* -------------------------------------------------------------------- */
1287 : /* Bind the geometry */
1288 : /* -------------------------------------------------------------------- */
1289 295 : int nBindField = 1;
1290 :
1291 295 : if( osGeomColumn.size() != 0 &&
1292 : eGeomFormat != OSGF_FGF )
1293 : {
1294 275 : OGRGeometry* poGeom = poFeature->GetGeometryRef();
1295 275 : if ( poGeom != NULL )
1296 : {
1297 267 : if ( eGeomFormat == OSGF_WKT )
1298 : {
1299 1 : char *pszWKT = NULL;
1300 1 : poGeom->exportToWkt( &pszWKT );
1301 1 : rc = sqlite3_bind_text( hStmt, nBindField++, pszWKT, -1, CPLFree );
1302 : }
1303 266 : else if( eGeomFormat == OSGF_WKB )
1304 : {
1305 60 : int nWKBLen = poGeom->WkbSize();
1306 60 : GByte *pabyWKB = (GByte *) CPLMalloc(nWKBLen + 1);
1307 :
1308 60 : poGeom->exportToWkb( wkbNDR, pabyWKB );
1309 60 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabyWKB, nWKBLen, CPLFree );
1310 : }
1311 206 : else if ( eGeomFormat == OSGF_SpatiaLite )
1312 : {
1313 : int nBLOBLen;
1314 : GByte *pabySLBLOB;
1315 :
1316 : ExportSpatiaLiteGeometry( poGeom, nSRSId, wkbNDR, bHasM,
1317 206 : bSpatialite2D, bUseComprGeom, &pabySLBLOB, &nBLOBLen );
1318 : rc = sqlite3_bind_blob( hStmt, nBindField++, pabySLBLOB,
1319 206 : nBLOBLen, CPLFree );
1320 : }
1321 : else
1322 : {
1323 0 : rc = SQLITE_OK;
1324 0 : CPLAssert(0);
1325 : }
1326 : }
1327 : else
1328 : {
1329 8 : if (bBindNullValues)
1330 2 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1331 : else
1332 6 : rc = SQLITE_OK;
1333 : }
1334 :
1335 275 : if( rc != SQLITE_OK )
1336 : {
1337 : CPLError( CE_Failure, CPLE_AppDefined,
1338 : "sqlite3_bind_blob/text() failed:\n %s",
1339 0 : sqlite3_errmsg(hDB) );
1340 0 : return OGRERR_FAILURE;
1341 : }
1342 : }
1343 :
1344 : /* -------------------------------------------------------------------- */
1345 : /* Bind field values. */
1346 : /* -------------------------------------------------------------------- */
1347 : int iField;
1348 295 : int nFieldCount = poFeatureDefn->GetFieldCount();
1349 698 : for( iField = 0; iField < nFieldCount; iField++ )
1350 : {
1351 : const char *pszRawValue;
1352 :
1353 403 : if( !poFeature->IsFieldSet( iField ) )
1354 : {
1355 116 : if (bBindNullValues)
1356 7 : rc = sqlite3_bind_null( hStmt, nBindField++ );
1357 : else
1358 109 : rc = SQLITE_OK;
1359 : }
1360 : else
1361 : {
1362 287 : switch( poFeatureDefn->GetFieldDefn(iField)->GetType() )
1363 : {
1364 : case OFTInteger:
1365 : {
1366 99 : int nFieldVal = poFeature->GetFieldAsInteger( iField );
1367 99 : rc = sqlite3_bind_int(hStmt, nBindField++, nFieldVal);
1368 99 : break;
1369 : }
1370 :
1371 : case OFTReal:
1372 : {
1373 41 : double dfFieldVal = poFeature->GetFieldAsDouble( iField );
1374 41 : rc = sqlite3_bind_double(hStmt, nBindField++, dfFieldVal);
1375 41 : break;
1376 : }
1377 :
1378 : case OFTBinary:
1379 : {
1380 21 : int nDataLength = 0;
1381 : GByte* pabyData =
1382 21 : poFeature->GetFieldAsBinary( iField, &nDataLength );
1383 : rc = sqlite3_bind_blob(hStmt, nBindField++,
1384 21 : pabyData, nDataLength, SQLITE_TRANSIENT);
1385 21 : break;
1386 : }
1387 :
1388 : default:
1389 : {
1390 126 : pszRawValue = poFeature->GetFieldAsString( iField );
1391 : rc = sqlite3_bind_text(hStmt, nBindField++,
1392 126 : pszRawValue, -1, SQLITE_TRANSIENT);
1393 : break;
1394 : }
1395 : }
1396 : }
1397 :
1398 403 : if( rc != SQLITE_OK )
1399 : {
1400 : CPLError( CE_Failure, CPLE_AppDefined,
1401 : "sqlite3_bind_() for column %s failed:\n %s",
1402 : poFeatureDefn->GetFieldDefn(iField)->GetNameRef(),
1403 0 : sqlite3_errmsg(hDB) );
1404 0 : return OGRERR_FAILURE;
1405 : }
1406 : }
1407 :
1408 295 : return OGRERR_NONE;
1409 : }
1410 :
1411 : /************************************************************************/
1412 : /* SetFeature() */
1413 : /************************************************************************/
1414 :
1415 4 : OGRErr OGRSQLiteTableLayer::SetFeature( OGRFeature *poFeature )
1416 :
1417 : {
1418 4 : if( pszFIDColumn == NULL )
1419 : {
1420 : CPLError( CE_Failure, CPLE_AppDefined,
1421 0 : "SetFeature() without any FID column." );
1422 0 : return OGRERR_FAILURE;
1423 : }
1424 :
1425 4 : if( poFeature->GetFID() == OGRNullFID )
1426 : {
1427 : CPLError( CE_Failure, CPLE_AppDefined,
1428 0 : "SetFeature() with unset FID fails." );
1429 0 : return OGRERR_FAILURE;
1430 : }
1431 :
1432 4 : if (bSpatialiteReadOnly || !poDS->GetUpdate())
1433 : {
1434 : CPLError( CE_Failure, CPLE_NotSupported,
1435 0 : "Can't update feature on a read-only layer.");
1436 0 : return OGRERR_FAILURE;
1437 : }
1438 :
1439 4 : sqlite3 *hDB = poDS->GetDB();
1440 4 : CPLString osCommand;
1441 4 : int bNeedComma = FALSE;
1442 :
1443 4 : ResetReading();
1444 :
1445 : /* -------------------------------------------------------------------- */
1446 : /* Form the UPDATE command. */
1447 : /* -------------------------------------------------------------------- */
1448 4 : osCommand += CPLSPrintf( "UPDATE '%s' SET ", pszEscapedTableName );
1449 :
1450 : /* -------------------------------------------------------------------- */
1451 : /* Add geometry field name. */
1452 : /* -------------------------------------------------------------------- */
1453 4 : if( osGeomColumn.size() != 0 &&
1454 : eGeomFormat != OSGF_FGF )
1455 : {
1456 4 : osCommand += "\"";
1457 4 : osCommand += osGeomColumn;
1458 4 : osCommand += "\" = ?";
1459 :
1460 4 : bNeedComma = TRUE;
1461 : }
1462 :
1463 : /* -------------------------------------------------------------------- */
1464 : /* Add field names. */
1465 : /* -------------------------------------------------------------------- */
1466 : int iField;
1467 4 : int nFieldCount = poFeatureDefn->GetFieldCount();
1468 :
1469 20 : for( iField = 0; iField < nFieldCount; iField++ )
1470 : {
1471 16 : if( bNeedComma )
1472 16 : osCommand += ",";
1473 :
1474 16 : osCommand += "\"";
1475 16 : osCommand +=poFeatureDefn->GetFieldDefn(iField)->GetNameRef();
1476 16 : osCommand += "\" = ?";
1477 :
1478 16 : bNeedComma = TRUE;
1479 : }
1480 :
1481 4 : if (!bNeedComma)
1482 0 : return OGRERR_NONE;
1483 :
1484 : /* -------------------------------------------------------------------- */
1485 : /* Merge final command. */
1486 : /* -------------------------------------------------------------------- */
1487 4 : osCommand += " WHERE \"";
1488 4 : osCommand += pszFIDColumn;
1489 4 : osCommand += CPLSPrintf("\" = %ld", poFeature->GetFID());
1490 :
1491 : /* -------------------------------------------------------------------- */
1492 : /* Prepare the statement. */
1493 : /* -------------------------------------------------------------------- */
1494 : int rc;
1495 : sqlite3_stmt *hUpdateStmt;
1496 :
1497 : #ifdef DEBUG
1498 4 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
1499 : #endif
1500 :
1501 4 : rc = sqlite3_prepare( hDB, osCommand, -1, &hUpdateStmt, NULL );
1502 4 : if( rc != SQLITE_OK )
1503 : {
1504 : CPLError( CE_Failure, CPLE_AppDefined,
1505 : "In SetFeature(): sqlite3_prepare(%s):\n %s",
1506 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
1507 :
1508 0 : return OGRERR_FAILURE;
1509 : }
1510 :
1511 : /* -------------------------------------------------------------------- */
1512 : /* Bind values. */
1513 : /* -------------------------------------------------------------------- */
1514 4 : OGRErr eErr = BindValues( poFeature, hUpdateStmt, TRUE );
1515 4 : if (eErr != OGRERR_NONE)
1516 : {
1517 0 : sqlite3_finalize( hUpdateStmt );
1518 0 : return eErr;
1519 : }
1520 :
1521 : /* -------------------------------------------------------------------- */
1522 : /* Execute the update. */
1523 : /* -------------------------------------------------------------------- */
1524 4 : rc = sqlite3_step( hUpdateStmt );
1525 :
1526 4 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
1527 : {
1528 : CPLError( CE_Failure, CPLE_AppDefined,
1529 : "sqlite3_step() failed:\n %s",
1530 0 : sqlite3_errmsg(hDB) );
1531 :
1532 0 : sqlite3_finalize( hUpdateStmt );
1533 0 : return OGRERR_FAILURE;
1534 : }
1535 :
1536 4 : sqlite3_finalize( hUpdateStmt );
1537 :
1538 4 : return OGRERR_NONE;
1539 : }
1540 :
1541 : /************************************************************************/
1542 : /* CreateFeature() */
1543 : /************************************************************************/
1544 :
1545 291 : OGRErr OGRSQLiteTableLayer::CreateFeature( OGRFeature *poFeature )
1546 :
1547 : {
1548 291 : sqlite3 *hDB = poDS->GetDB();
1549 291 : CPLString osCommand;
1550 291 : CPLString osValues;
1551 291 : int bNeedComma = FALSE;
1552 :
1553 291 : if (bSpatialiteReadOnly || !poDS->GetUpdate())
1554 : {
1555 : CPLError( CE_Failure, CPLE_NotSupported,
1556 0 : "Can't create feature on a read-only layer.");
1557 0 : return OGRERR_FAILURE;
1558 : }
1559 :
1560 291 : ResetReading();
1561 :
1562 : /* -------------------------------------------------------------------- */
1563 : /* Form the INSERT command. */
1564 : /* -------------------------------------------------------------------- */
1565 291 : osCommand += CPLSPrintf( "INSERT INTO '%s' (", pszEscapedTableName );
1566 :
1567 : /* -------------------------------------------------------------------- */
1568 : /* Add FID if we have a cleartext FID column. */
1569 : /* -------------------------------------------------------------------- */
1570 291 : if( pszFIDColumn != NULL // && !EQUAL(pszFIDColumn,"OGC_FID")
1571 : && poFeature->GetFID() != OGRNullFID )
1572 : {
1573 57 : osCommand += "\"";
1574 57 : osCommand += pszFIDColumn;
1575 57 : osCommand += "\"";
1576 :
1577 57 : osValues += CPLSPrintf( "%ld", poFeature->GetFID() );
1578 57 : bNeedComma = TRUE;
1579 : }
1580 :
1581 : /* -------------------------------------------------------------------- */
1582 : /* Add geometry. */
1583 : /* -------------------------------------------------------------------- */
1584 291 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1585 :
1586 291 : if( osGeomColumn.size() != 0 &&
1587 : poGeom != NULL &&
1588 : eGeomFormat != OSGF_FGF )
1589 : {
1590 :
1591 265 : if( bNeedComma )
1592 : {
1593 57 : osCommand += ",";
1594 57 : osValues += ",";
1595 : }
1596 :
1597 265 : osCommand += "\"";
1598 265 : osCommand += osGeomColumn;
1599 265 : osCommand += "\"";
1600 :
1601 265 : osValues += "?";
1602 :
1603 265 : bNeedComma = TRUE;
1604 : }
1605 :
1606 : /* -------------------------------------------------------------------- */
1607 : /* Add field values. */
1608 : /* -------------------------------------------------------------------- */
1609 : int iField;
1610 291 : int nFieldCount = poFeatureDefn->GetFieldCount();
1611 :
1612 678 : for( iField = 0; iField < nFieldCount; iField++ )
1613 : {
1614 387 : if( !poFeature->IsFieldSet( iField ) )
1615 109 : continue;
1616 :
1617 278 : if( bNeedComma )
1618 : {
1619 252 : osCommand += ",";
1620 252 : osValues += ",";
1621 : }
1622 :
1623 278 : osCommand += "\"";
1624 278 : osCommand +=poFeatureDefn->GetFieldDefn(iField)->GetNameRef();
1625 278 : osCommand += "\"";
1626 :
1627 278 : osValues += "?";
1628 :
1629 278 : bNeedComma = TRUE;
1630 : }
1631 :
1632 : /* -------------------------------------------------------------------- */
1633 : /* Merge final command. */
1634 : /* -------------------------------------------------------------------- */
1635 291 : osCommand += ") VALUES (";
1636 291 : osCommand += osValues;
1637 291 : osCommand += ")";
1638 :
1639 : /* -------------------------------------------------------------------- */
1640 : /* Prepare the statement. */
1641 : /* -------------------------------------------------------------------- */
1642 : int rc;
1643 : sqlite3_stmt *hInsertStmt;
1644 :
1645 : #ifdef DEBUG
1646 291 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osCommand.c_str() );
1647 : #endif
1648 :
1649 291 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
1650 291 : if( rc != SQLITE_OK )
1651 : {
1652 : CPLError( CE_Failure, CPLE_AppDefined,
1653 : "In CreateFeature(): sqlite3_prepare(%s):\n %s",
1654 0 : osCommand.c_str(), sqlite3_errmsg(hDB) );
1655 :
1656 0 : return OGRERR_FAILURE;
1657 : }
1658 :
1659 : /* -------------------------------------------------------------------- */
1660 : /* Bind values. */
1661 : /* -------------------------------------------------------------------- */
1662 291 : OGRErr eErr = BindValues( poFeature, hInsertStmt, FALSE );
1663 291 : if (eErr != OGRERR_NONE)
1664 : {
1665 0 : sqlite3_finalize( hInsertStmt );
1666 0 : return eErr;
1667 : }
1668 :
1669 : /* -------------------------------------------------------------------- */
1670 : /* Execute the insert. */
1671 : /* -------------------------------------------------------------------- */
1672 291 : rc = sqlite3_step( hInsertStmt );
1673 :
1674 291 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
1675 : {
1676 : CPLError( CE_Failure, CPLE_AppDefined,
1677 : "sqlite3_step() failed:\n %s",
1678 0 : sqlite3_errmsg(hDB) );
1679 :
1680 0 : sqlite3_finalize( hInsertStmt );
1681 0 : return OGRERR_FAILURE;
1682 : }
1683 :
1684 : /* -------------------------------------------------------------------- */
1685 : /* Capture the FID/rowid. */
1686 : /* -------------------------------------------------------------------- */
1687 291 : const sqlite_int64 nFID = sqlite3_last_insert_rowid( hDB );
1688 291 : if(nFID > 0)
1689 : {
1690 290 : poFeature->SetFID( (long)nFID ); /* Possible truncation if nFID is 64bit */
1691 : }
1692 :
1693 291 : sqlite3_finalize( hInsertStmt );
1694 :
1695 291 : return OGRERR_NONE;
1696 : }
1697 :
|
