← Back to branch summary

~ubuntu-branches/debian/sid/gdal/sid

« back to all changes in this revision

Viewing changes to ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp

  • Committer: Package Import Robot
  • Author(s): Francesco Paolo Lovergine
  • Date: 2012-05-07 15:04:42 UTC
  • mfrom: (5.5.16 experimental)
  • Revision ID: package-import@ubuntu.com-20120507150442-2eks97loeh6rq005
Tags: 1.9.0-1
* Ready for sid, starting transition.
* All symfiles updated to latest builds.
* Added dh_numpy call in debian/rules to depend on numpy ABI.
* Policy bumped to 3.9.3, no changes required.

Show diffs side-by-side

added added

removed removed

Lines of Context:
ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp
1
1
/******************************************************************************
2
 
 * $Id: ogrsqlitedatasource.cpp 19238 2010-03-28 17:22:40Z rouault $
 
2
 * $Id: ogrsqlitedatasource.cpp 23676 2012-01-01 16:10:36Z rouault $
3
3
 *
4
4
 * Project:  OpenGIS Simple Features Reference Implementation
5
5
 * Purpose:  Implements OGRSQLiteDataSource class.
6
6
 * Author:   Frank Warmerdam, warmerdam@pobox.com
7
7
 *
8
8
 ******************************************************************************
 
9
 *
 
10
 * Contributor: Alessandro Furieri, a.furieri@lqt.it
 
11
 * Portions of this module properly supporting SpatiaLite Table/Geom creation
 
12
 * Developed for Faunalia ( http://www.faunalia.it) with funding from 
 
13
 * Regione Toscana - Settore SISTEMA INFORMATIVO TERRITORIALE ED AMBIENTALE
 
14
 *
 
15
 ******************************************************************************
9
16
 * Copyright (c) 2003, Frank Warmerdam <warmerdam@pobox.com>
10
17
 *
11
18
 * Permission is hereby granted, free of charge, to any person obtaining a
31
38
#include "cpl_conv.h"
32
39
#include "cpl_string.h"
33
40
#include "cpl_hash_set.h"
 
41
#include "cpl_csv.h"
34
42
 
35
43
#ifdef HAVE_SPATIALITE
36
44
#include "spatialite.h"
38
46
 
39
47
static int bSpatialiteLoaded = FALSE;
40
48
 
41
 
CPL_CVSID("$Id: ogrsqlitedatasource.cpp 19238 2010-03-28 17:22:40Z rouault $");
 
49
CPL_CVSID("$Id: ogrsqlitedatasource.cpp 23676 2012-01-01 16:10:36Z rouault $");
 
50
 
 
51
/************************************************************************/
 
52
/*                      OGRSQLiteInitSpatialite()                       */
 
53
/************************************************************************/
 
54
 
 
55
static int OGRSQLiteInitSpatialite()
 
56
{
 
57
/* -------------------------------------------------------------------- */
 
58
/*      Try loading SpatiaLite.                                         */
 
59
/* -------------------------------------------------------------------- */
 
60
#ifdef HAVE_SPATIALITE
 
61
    if (!bSpatialiteLoaded && CSLTestBoolean(CPLGetConfigOption("SPATIALITE_LOAD", "TRUE")))
 
62
    {
 
63
        bSpatialiteLoaded = TRUE;
 
64
        spatialite_init(CSLTestBoolean(CPLGetConfigOption("SPATIALITE_INIT_VERBOSE", "FALSE")));
 
65
    }
 
66
#endif
 
67
    return bSpatialiteLoaded;
 
68
}
 
69
 
 
70
/************************************************************************/
 
71
/*               OGRSQLiteGetSpatialiteVersionNumber()                  */
 
72
/************************************************************************/
 
73
 
 
74
int OGRSQLiteGetSpatialiteVersionNumber()
 
75
{
 
76
    double v = 0.0;
 
77
#ifdef HAVE_SPATIALITE
 
78
    v = ( atof( spatialite_version() ) + 0.001 )  * 10.0;
 
79
#endif
 
80
    return (int)v;
 
81
}
42
82
 
43
83
/************************************************************************/
44
84
/*                        OGRSQLiteDataSource()                         */
59
99
 
60
100
    bHaveGeometryColumns = FALSE;
61
101
    bIsSpatiaLite = FALSE;
 
102
    bUpdate = FALSE;
 
103
 
 
104
    hDB = NULL;
 
105
 
 
106
#ifdef HAVE_SQLITE_VFS
 
107
    pMyVFS = NULL;
 
108
#endif
62
109
}
63
110
 
64
111
/************************************************************************/
87
134
 
88
135
    if( hDB != NULL )
89
136
        sqlite3_close( hDB );
90
 
}
91
 
 
92
 
/************************************************************************/
93
 
/*                     SpatiaLiteToOGRGeomType()                        */
94
 
/*      Map SpatiaLite geometry format strings to corresponding         */
95
 
/*      OGR constants.                                                  */
96
 
/************************************************************************/
97
 
 
98
 
OGRwkbGeometryType
99
 
OGRSQLiteDataSource::SpatiaLiteToOGRGeomType( const char *pszGeomType )
100
 
{
101
 
    if ( EQUAL(pszGeomType, "POINT") )
102
 
        return wkbPoint;
103
 
    else if ( EQUAL(pszGeomType, "LINESTRING") )
104
 
        return wkbLineString;
105
 
    else if ( EQUAL(pszGeomType, "POLYGON") )
106
 
        return wkbPolygon;
107
 
    else if ( EQUAL(pszGeomType, "MULTIPOINT") )
108
 
        return wkbMultiPoint;
109
 
    else if ( EQUAL(pszGeomType, "MULTILINESTRING") )
110
 
        return wkbMultiLineString;
111
 
    else if ( EQUAL(pszGeomType, "MULTIPOLYGON") )
112
 
        return wkbMultiPolygon;
113
 
    else if ( EQUAL(pszGeomType, "GEOMETRYCOLLECTION") )
114
 
        return wkbGeometryCollection;
115
 
    else
116
 
        return wkbUnknown;
117
 
}
118
 
 
119
 
/************************************************************************/
120
 
/*                     OGRToSpatiaLiteGeomType()                        */
121
 
/*      Map OGR geometry format constants to corresponding              */
122
 
/*      SpatiaLite strings                                              */
123
 
/************************************************************************/
124
 
 
125
 
const char *
126
 
OGRSQLiteDataSource::OGRToSpatiaLiteGeomType( OGRwkbGeometryType eGeomType )
127
 
{
128
 
    switch ( wkbFlatten(eGeomType) )
129
 
    {
130
 
        case wkbUnknown:
131
 
            return "GEOMETRY";
132
 
        case wkbPoint:
133
 
            return "POINT";
134
 
        case wkbLineString:
135
 
            return "LINESTRING";
136
 
        case wkbPolygon:
137
 
            return "POLYGON";
138
 
        case wkbMultiPoint:
139
 
            return "MULTIPOINT";
140
 
        case wkbMultiLineString:
141
 
            return "MULTILINESTRING";
142
 
        case wkbMultiPolygon:
143
 
            return "MULTIPOLYGON";
144
 
        case wkbGeometryCollection:
145
 
            return "GEOMETRYCOLLECTION";
146
 
        default:
147
 
            return "";
148
 
    }
 
137
 
 
138
#ifdef HAVE_SQLITE_VFS
 
139
    if (pMyVFS)
 
140
    {
 
141
        sqlite3_vfs_unregister(pMyVFS);
 
142
        CPLFree(pMyVFS);
 
143
    }
 
144
#endif
 
145
}
 
146
 
 
147
/************************************************************************/
 
148
/*                              SetSynchronous()                        */
 
149
/************************************************************************/
 
150
 
 
151
int OGRSQLiteDataSource::SetSynchronous()
 
152
{
 
153
    int rc;
 
154
    const char* pszSqliteSync = CPLGetConfigOption("OGR_SQLITE_SYNCHRONOUS", NULL);
 
155
    if (pszSqliteSync != NULL)
 
156
    {
 
157
        char* pszErrMsg = NULL;
 
158
        if (EQUAL(pszSqliteSync, "OFF") || EQUAL(pszSqliteSync, "0") ||
 
159
            EQUAL(pszSqliteSync, "FALSE"))
 
160
            rc = sqlite3_exec( hDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg );
 
161
        else if (EQUAL(pszSqliteSync, "NORMAL") || EQUAL(pszSqliteSync, "1"))
 
162
            rc = sqlite3_exec( hDB, "PRAGMA synchronous = NORMAL", NULL, NULL, &pszErrMsg );
 
163
        else if (EQUAL(pszSqliteSync, "ON") || EQUAL(pszSqliteSync, "FULL") ||
 
164
            EQUAL(pszSqliteSync, "2") || EQUAL(pszSqliteSync, "TRUE"))
 
165
            rc = sqlite3_exec( hDB, "PRAGMA synchronous = FULL", NULL, NULL, &pszErrMsg );
 
166
        else
 
167
        {
 
168
            CPLError( CE_Warning, CPLE_AppDefined, "Unrecognized value for OGR_SQLITE_SYNCHRONOUS : %s",
 
169
                      pszSqliteSync);
 
170
            rc = SQLITE_OK;
 
171
        }
 
172
 
 
173
        if( rc != SQLITE_OK )
 
174
        {
 
175
            CPLError( CE_Failure, CPLE_AppDefined,
 
176
                      "Unable to run PRAGMA synchronous : %s",
 
177
                      pszErrMsg );
 
178
            sqlite3_free( pszErrMsg );
 
179
            return FALSE;
 
180
        }
 
181
    }
 
182
    return TRUE;
 
183
}
 
184
 
 
185
/************************************************************************/
 
186
/*                              SetCacheSize()                          */
 
187
/************************************************************************/
 
188
 
 
189
int OGRSQLiteDataSource::SetCacheSize()
 
190
{
 
191
    int rc;
 
192
    const char* pszSqliteCacheMB = CPLGetConfigOption("OGR_SQLITE_CACHE", NULL);
 
193
    if (pszSqliteCacheMB != NULL)
 
194
    {
 
195
        char* pszErrMsg = NULL;
 
196
        char **papszResult;
 
197
        int nRowCount, nColCount;
 
198
        int iSqliteCachePages;
 
199
        int iSqlitePageSize = -1;
 
200
        int iSqliteCacheBytes = atoi( pszSqliteCacheMB ) * 1024 * 1024;
 
201
 
 
202
        /* querying the current PageSize */
 
203
        rc = sqlite3_get_table( hDB, "PRAGMA page_size",
 
204
                                &papszResult, &nRowCount, &nColCount,
 
205
                                &pszErrMsg );
 
206
        if( rc == SQLITE_OK )
 
207
        {
 
208
            int iRow;
 
209
            for (iRow = 1; iRow <= nRowCount; iRow++)
 
210
            {
 
211
                iSqlitePageSize = atoi( papszResult[(iRow * nColCount) + 0] );
 
212
            }
 
213
            sqlite3_free_table(papszResult);
 
214
        }
 
215
        if( iSqlitePageSize < 0 )
 
216
        {
 
217
            CPLError( CE_Failure, CPLE_AppDefined,
 
218
                      "Unable to run PRAGMA page_size : %s",
 
219
                      pszErrMsg );
 
220
            sqlite3_free( pszErrMsg );
 
221
            return TRUE;
 
222
        }
 
223
                
 
224
        /* computing the CacheSize as #Pages */
 
225
        iSqliteCachePages = iSqliteCacheBytes / iSqlitePageSize;
 
226
        if( iSqliteCachePages <= 0)
 
227
            return TRUE;
 
228
 
 
229
        rc = sqlite3_exec( hDB, CPLSPrintf( "PRAGMA cache_size = %d",
 
230
                                            iSqliteCachePages ),
 
231
                           NULL, NULL, &pszErrMsg );
 
232
        if( rc != SQLITE_OK )
 
233
        {
 
234
            CPLError( CE_Warning, CPLE_AppDefined,
 
235
                      "Unrecognized value for PRAGMA cache_size : %s",
 
236
                      pszErrMsg );
 
237
            sqlite3_free( pszErrMsg );
 
238
            rc = SQLITE_OK;
 
239
        }
 
240
    }
 
241
    return TRUE;
 
242
}
 
243
 
 
244
/************************************************************************/
 
245
/*                            OpenOrCreateDB()                          */
 
246
/************************************************************************/
 
247
 
 
248
int OGRSQLiteDataSource::OpenOrCreateDB(int flags)
 
249
{
 
250
    int rc;
 
251
 
 
252
#ifdef HAVE_SQLITE_VFS
 
253
    int bUseOGRVFS = CSLTestBoolean(CPLGetConfigOption("SQLITE_USE_OGR_VFS", "NO"));
 
254
    if (bUseOGRVFS || strncmp(pszName, "/vsi", 4) == 0)
 
255
    {
 
256
        pMyVFS = OGRSQLiteCreateVFS();
 
257
        sqlite3_vfs_register(pMyVFS, 0);
 
258
        rc = sqlite3_open_v2( pszName, &hDB, flags, pMyVFS->zName );
 
259
    }
 
260
    else
 
261
        rc = sqlite3_open_v2( pszName, &hDB, flags, NULL );
 
262
#else
 
263
    rc = sqlite3_open( pszName, &hDB );
 
264
#endif
 
265
    if( rc != SQLITE_OK )
 
266
    {
 
267
        CPLError( CE_Failure, CPLE_OpenFailed,
 
268
                  "sqlite3_open(%s) failed: %s",
 
269
                  pszName, sqlite3_errmsg( hDB ) );
 
270
        return FALSE;
 
271
    }
 
272
 
 
273
    if (!SetCacheSize())
 
274
        return FALSE;
 
275
 
 
276
    if (!SetSynchronous())
 
277
        return FALSE;
 
278
 
 
279
    return TRUE;
 
280
}
 
281
 
 
282
/************************************************************************/
 
283
/*                               Create()                               */
 
284
/************************************************************************/
 
285
 
 
286
int OGRSQLiteDataSource::Create( const char * pszNameIn, char **papszOptions )
 
287
{
 
288
    int rc;
 
289
    CPLString osCommand;
 
290
    char *pszErrMsg = NULL;
 
291
 
 
292
    pszName = CPLStrdup( pszNameIn );
 
293
 
 
294
/* -------------------------------------------------------------------- */
 
295
/*      Check that spatialite extensions are loaded if required to      */
 
296
/*      create a spatialite database                                    */
 
297
/* -------------------------------------------------------------------- */
 
298
    int bSpatialite = CSLFetchBoolean( papszOptions, "SPATIALITE", FALSE );
 
299
    int bMetadata = CSLFetchBoolean( papszOptions, "METADATA", TRUE );
 
300
 
 
301
    if (bSpatialite == TRUE)
 
302
    {
 
303
#ifdef HAVE_SPATIALITE
 
304
        int bSpatialiteLoaded = OGRSQLiteInitSpatialite();
 
305
        if (!bSpatialiteLoaded)
 
306
        {
 
307
            CPLError( CE_Failure, CPLE_NotSupported,
 
308
                    "Creating a Spatialite database, but Spatialite extensions are not loaded." );
 
309
            return FALSE;
 
310
        }
 
311
#else
 
312
        CPLError( CE_Failure, CPLE_NotSupported,
 
313
            "OGR was built without libspatialite support\n"
 
314
            "... sorry, creating/writing any SpatiaLite DB is unsupported\n" );
 
315
 
 
316
        return FALSE;
 
317
#endif
 
318
    }
 
319
 
 
320
    bIsSpatiaLite = bSpatialite;
 
321
 
 
322
/* -------------------------------------------------------------------- */
 
323
/*      Create the database file.                                       */
 
324
/* -------------------------------------------------------------------- */
 
325
#ifdef HAVE_SQLITE_VFS
 
326
    if (!OpenOrCreateDB(SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE))
 
327
#else
 
328
    if (!OpenOrCreateDB(0))
 
329
#endif
 
330
        return FALSE;
 
331
 
 
332
/* -------------------------------------------------------------------- */
 
333
/*      Create the SpatiaLite metadata tables.                          */
 
334
/* -------------------------------------------------------------------- */
 
335
    if ( bSpatialite )
 
336
    {
 
337
        /*
 
338
        / SpatiaLite full support: calling InitSpatialMetadata()
 
339
        /
 
340
        / IMPORTANT NOTICE: on SpatiaLite any attempt aimed
 
341
        / to directly CREATE "geometry_columns" and "spatial_ref_sys"
 
342
        / [by-passing InitSpatialMetadata() as absolutely required]
 
343
        / will severely [and irremediably] corrupt the DB !!!
 
344
        */
 
345
        osCommand =  "SELECT InitSpatialMetadata()";
 
346
        rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
 
347
        if( rc != SQLITE_OK )
 
348
        {
 
349
            CPLError( CE_Failure, CPLE_AppDefined,
 
350
                "Unable to Initialize SpatiaLite Metadata: %s",
 
351
                    pszErrMsg );
 
352
            sqlite3_free( pszErrMsg );
 
353
            return FALSE;
 
354
        }
 
355
    }
 
356
 
 
357
/* -------------------------------------------------------------------- */
 
358
/*  Create the geometry_columns and spatial_ref_sys metadata tables.    */
 
359
/* -------------------------------------------------------------------- */
 
360
    else if( bMetadata )
 
361
    {
 
362
        osCommand =
 
363
            "CREATE TABLE geometry_columns ("
 
364
            "     f_table_name VARCHAR, "
 
365
            "     f_geometry_column VARCHAR, "
 
366
            "     geometry_type INTEGER, "
 
367
            "     coord_dimension INTEGER, "
 
368
            "     srid INTEGER,"
 
369
            "     geometry_format VARCHAR )";
 
370
        rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
 
371
        if( rc != SQLITE_OK )
 
372
        {
 
373
            CPLError( CE_Failure, CPLE_AppDefined,
 
374
                      "Unable to create table geometry_columns: %s",
 
375
                      pszErrMsg );
 
376
            sqlite3_free( pszErrMsg );
 
377
            return FALSE;
 
378
        }
 
379
 
 
380
        osCommand =
 
381
            "CREATE TABLE spatial_ref_sys        ("
 
382
            "     srid INTEGER UNIQUE,"
 
383
            "     auth_name TEXT,"
 
384
            "     auth_srid TEXT,"
 
385
            "     srtext TEXT)";
 
386
        rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
 
387
        if( rc != SQLITE_OK )
 
388
        {
 
389
            CPLError( CE_Failure, CPLE_AppDefined,
 
390
                      "Unable to create table spatial_ref_sys: %s",
 
391
                      pszErrMsg );
 
392
            sqlite3_free( pszErrMsg );
 
393
            return FALSE;
 
394
        }
 
395
    }
 
396
 
 
397
/* -------------------------------------------------------------------- */
 
398
/*      Optionnaly initialize the content of the spatial_ref_sys table  */
 
399
/*      with the EPSG database                                          */
 
400
/* -------------------------------------------------------------------- */
 
401
    if ( (bSpatialite || bMetadata) &&
 
402
         CSLFetchBoolean( papszOptions, "INIT_WITH_EPSG", FALSE ) )
 
403
    {
 
404
        if (!InitWithEPSG())
 
405
            return FALSE;
 
406
    }
 
407
 
 
408
    return Open(pszName, TRUE);
 
409
}
 
410
 
 
411
/************************************************************************/
 
412
/*                           InitWithEPSG()                             */
 
413
/************************************************************************/
 
414
 
 
415
int OGRSQLiteDataSource::InitWithEPSG()
 
416
{
 
417
    CPLString osCommand;
 
418
    char* pszErrMsg = NULL;
 
419
 
 
420
    if ( bIsSpatiaLite )
 
421
    {
 
422
        /*
 
423
        / if v.2.4.0 (or any subsequent) InitWithEPSG make no sense at all
 
424
        / because the EPSG dataset is already self-initialized at DB creation
 
425
        */
 
426
        int iSpatialiteVersion = OGRSQLiteGetSpatialiteVersionNumber();
 
427
        if ( iSpatialiteVersion >= 24 )
 
428
            return TRUE;
 
429
    }
 
430
 
 
431
    int rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, &pszErrMsg );
 
432
    if( rc != SQLITE_OK )
 
433
    {
 
434
        CPLError( CE_Failure, CPLE_AppDefined,
 
435
                "Unable to insert into spatial_ref_sys: %s",
 
436
                pszErrMsg );
 
437
        sqlite3_free( pszErrMsg );
 
438
        return FALSE;
 
439
    }
 
440
 
 
441
    FILE* fp;
 
442
    int i;
 
443
    for(i=0;i<2 && rc == SQLITE_OK;i++)
 
444
    {
 
445
        const char* pszFilename = (i == 0) ? "gcs.csv" : "pcs.csv";
 
446
        fp = VSIFOpen(CSVFilename(pszFilename), "rt");
 
447
        if (fp == NULL)
 
448
        {
 
449
            CPLError( CE_Failure, CPLE_OpenFailed,
 
450
                "Unable to open EPSG support file %s.\n"
 
451
                "Try setting the GDAL_DATA environment variable to point to the\n"
 
452
                "directory containing EPSG csv files.",
 
453
                pszFilename );
 
454
 
 
455
            continue;
 
456
        }
 
457
 
 
458
        OGRSpatialReference oSRS;
 
459
        char** papszTokens;
 
460
        CSLDestroy(CSVReadParseLine( fp ));
 
461
        while ( (papszTokens = CSVReadParseLine( fp )) != NULL && rc == SQLITE_OK)
 
462
        {
 
463
            int nSRSId = atoi(papszTokens[0]);
 
464
            CSLDestroy(papszTokens);
 
465
 
 
466
            CPLPushErrorHandler(CPLQuietErrorHandler);
 
467
            oSRS.importFromEPSG(nSRSId);
 
468
            CPLPopErrorHandler();
 
469
 
 
470
            if (bIsSpatiaLite)
 
471
            {
 
472
                char    *pszProj4 = NULL;
 
473
 
 
474
                CPLPushErrorHandler(CPLQuietErrorHandler);
 
475
                OGRErr eErr = oSRS.exportToProj4( &pszProj4 );
 
476
                CPLPopErrorHandler();
 
477
 
 
478
                char    *pszWKT = NULL;
 
479
                if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
 
480
                {
 
481
                    CPLFree(pszWKT);
 
482
                    pszWKT = NULL;
 
483
                }
 
484
 
 
485
                if( eErr == OGRERR_NONE )
 
486
                {
 
487
                    const char  *pszProjCS = oSRS.GetAttrValue("PROJCS");
 
488
                    if (pszProjCS == NULL)
 
489
                        pszProjCS = oSRS.GetAttrValue("GEOGCS");
 
490
 
 
491
                    int bHasSrsWkt = FALSE;
 
492
 
 
493
                /* testing for SRS_WKT column presence */
 
494
                    char **papszResult;
 
495
                    int nRowCount, nColCount;
 
496
                    rc = sqlite3_get_table( hDB, "PRAGMA table_info(spatial_ref_sys)",
 
497
                                            &papszResult, &nRowCount, &nColCount,
 
498
                                            &pszErrMsg );
 
499
 
 
500
                    if( rc == SQLITE_OK )
 
501
                    {
 
502
                        int iRow;
 
503
                        for (iRow = 1; iRow <= nRowCount; iRow++)
 
504
                        {
 
505
                            if (EQUAL("srs_wkt",
 
506
                                      papszResult[(iRow * nColCount) + 1]))
 
507
                                bHasSrsWkt = TRUE;
 
508
                        }
 
509
                        sqlite3_free_table(papszResult);
 
510
                    }
 
511
 
 
512
                    if (bHasSrsWkt == TRUE)
 
513
                    {
 
514
                    /* the SPATIAL_REF_SYS table supports a SRS_WKT column */
 
515
                        if ( pszProjCS )
 
516
                            osCommand.Printf(
 
517
                                "INSERT INTO spatial_ref_sys "
 
518
                                "(srid, auth_name, auth_srid, ref_sys_name, proj4text, srs_wkt) "
 
519
                                "VALUES (%d, 'EPSG', '%d', ?, ?, ?)",
 
520
                                nSRSId, nSRSId);
 
521
                        else
 
522
                            osCommand.Printf(
 
523
                                "INSERT INTO spatial_ref_sys "
 
524
                                "(srid, auth_name, auth_srid, proj4text, srs_wkt) "
 
525
                                "VALUES (%d, 'EPSG', '%d', ?, ?)",
 
526
                                nSRSId, nSRSId);
 
527
                    }
 
528
                    else
 
529
                    {
 
530
                    /* the SPATIAL_REF_SYS table does not support a SRS_WKT column */
 
531
                        if ( pszProjCS )
 
532
                            osCommand.Printf(
 
533
                                "INSERT INTO spatial_ref_sys "
 
534
                                "(srid, auth_name, auth_srid, ref_sys_name, proj4text) "
 
535
                                "VALUES (%d, 'EPSG', '%d', ?, ?)",
 
536
                                nSRSId, nSRSId);
 
537
                        else
 
538
                            osCommand.Printf(
 
539
                                "INSERT INTO spatial_ref_sys "
 
540
                                "(srid, auth_name, auth_srid, proj4text) "
 
541
                                "VALUES (%d, 'EPSG', '%d', ?)",
 
542
                                nSRSId, nSRSId);
 
543
                    }
 
544
 
 
545
                    sqlite3_stmt *hInsertStmt = NULL;
 
546
                    rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
 
547
 
 
548
                    if ( pszProjCS )
 
549
                    {
 
550
                        if( rc == SQLITE_OK)
 
551
                            rc = sqlite3_bind_text( hInsertStmt, 1, pszProjCS, -1, SQLITE_STATIC );
 
552
                        if( rc == SQLITE_OK)
 
553
                            rc = sqlite3_bind_text( hInsertStmt, 2, pszProj4, -1, SQLITE_STATIC );
 
554
                        if (bHasSrsWkt == TRUE)
 
555
                        {
 
556
                        /* the SPATIAL_REF_SYS table supports a SRS_WKT column */
 
557
                            if( rc == SQLITE_OK && pszWKT != NULL)
 
558
                                rc = sqlite3_bind_text( hInsertStmt, 3, pszWKT, -1, SQLITE_STATIC );
 
559
                        }
 
560
                    }
 
561
                    else
 
562
                    {
 
563
                        if( rc == SQLITE_OK)
 
564
                            rc = sqlite3_bind_text( hInsertStmt, 1, pszProj4, -1, SQLITE_STATIC );
 
565
                        if (bHasSrsWkt == TRUE)
 
566
                        {
 
567
                        /* the SPATIAL_REF_SYS table supports a SRS_WKT column */
 
568
                            if( rc == SQLITE_OK && pszWKT != NULL)
 
569
                                rc = sqlite3_bind_text( hInsertStmt, 2, pszWKT, -1, SQLITE_STATIC );
 
570
                        }
 
571
                    }
 
572
 
 
573
                    if( rc == SQLITE_OK)
 
574
                        rc = sqlite3_step( hInsertStmt );
 
575
 
 
576
                    if( rc != SQLITE_OK && rc != SQLITE_DONE )
 
577
                    {
 
578
                        CPLError( CE_Failure, CPLE_AppDefined,
 
579
                                    "Cannot insert %s into spatial_ref_sys : %s",
 
580
                                    pszProj4,
 
581
                                    sqlite3_errmsg(hDB) );
 
582
 
 
583
                        sqlite3_finalize( hInsertStmt );
 
584
                        CPLFree(pszProj4);
 
585
                        CPLFree(pszWKT);
 
586
                        break;
 
587
                    }
 
588
                    rc = SQLITE_OK;
 
589
 
 
590
                    sqlite3_finalize( hInsertStmt );
 
591
                }
 
592
 
 
593
                CPLFree(pszProj4);
 
594
                CPLFree(pszWKT);
 
595
            }
 
596
            else
 
597
            {
 
598
                char    *pszWKT = NULL;
 
599
                if( oSRS.exportToWkt( &pszWKT ) == OGRERR_NONE )
 
600
                {
 
601
                    osCommand.Printf(
 
602
                        "INSERT INTO spatial_ref_sys "
 
603
                        "(srid, auth_name, auth_srid, srtext) "
 
604
                        "VALUES (%d, 'EPSG', '%d', ?)",
 
605
                        nSRSId, nSRSId );
 
606
 
 
607
                    sqlite3_stmt *hInsertStmt = NULL;
 
608
                    rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
 
609
 
 
610
                    if( rc == SQLITE_OK)
 
611
                        rc = sqlite3_bind_text( hInsertStmt, 1, pszWKT, -1, SQLITE_STATIC );
 
612
 
 
613
                    if( rc == SQLITE_OK)
 
614
                        rc = sqlite3_step( hInsertStmt );
 
615
 
 
616
                    if( rc != SQLITE_OK && rc != SQLITE_DONE )
 
617
                    {
 
618
                        CPLError( CE_Failure, CPLE_AppDefined,
 
619
                                    "Cannot insert %s into spatial_ref_sys : %s",
 
620
                                    pszWKT,
 
621
                                    sqlite3_errmsg(hDB) );
 
622
 
 
623
                        sqlite3_finalize( hInsertStmt );
 
624
                        CPLFree(pszWKT);
 
625
                        break;
 
626
                    }
 
627
                    rc = SQLITE_OK;
 
628
 
 
629
                    sqlite3_finalize( hInsertStmt );
 
630
                }
 
631
 
 
632
                CPLFree(pszWKT);
 
633
            }
 
634
        }
 
635
        VSIFClose(fp);
 
636
    }
 
637
 
 
638
    if (rc == SQLITE_OK)
 
639
        rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, &pszErrMsg );
 
640
    else
 
641
        rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, &pszErrMsg );
 
642
 
 
643
    if( rc != SQLITE_OK )
 
644
    {
 
645
        CPLError( CE_Failure, CPLE_AppDefined,
 
646
                "Unable to insert into spatial_ref_sys: %s",
 
647
                pszErrMsg );
 
648
        sqlite3_free( pszErrMsg );
 
649
    }
 
650
 
 
651
    return (rc == SQLITE_OK);
149
652
}
150
653
 
151
654
/************************************************************************/
152
655
/*                                Open()                                */
153
 
/*                                                                      */
154
 
/*      Note, the Open() will implicitly create the database if it      */
155
 
/*      does not already exist.                                         */
156
656
/************************************************************************/
157
657
 
158
 
int OGRSQLiteDataSource::Open( const char * pszNewName )
 
658
int OGRSQLiteDataSource::Open( const char * pszNewName, int bUpdateIn )
159
659
 
160
660
{
161
661
    CPLAssert( nLayers == 0 );
162
662
 
163
 
    pszName = CPLStrdup( pszNewName );
164
 
 
165
 
/* -------------------------------------------------------------------- */
166
 
/*      Try loading SpatiaLite.                                         */
167
 
/* -------------------------------------------------------------------- */
168
 
#ifdef HAVE_SPATIALITE
169
 
    if (!bSpatialiteLoaded && CSLTestBoolean(CPLGetConfigOption("SPATIALITE_LOAD", "TRUE")))
170
 
    {
171
 
        bSpatialiteLoaded = TRUE;
172
 
        spatialite_init(CSLTestBoolean(CPLGetConfigOption("SPATIALITE_INIT_VERBOSE", "FALSE")));
173
 
    }
174
 
#endif
 
663
    if (pszName == NULL)
 
664
        pszName = CPLStrdup( pszNewName );
 
665
    bUpdate = bUpdateIn;
175
666
 
176
667
    int bListAllTables = CSLTestBoolean(CPLGetConfigOption("SQLITE_LIST_ALL_TABLES", "NO"));
177
668
 
178
669
/* -------------------------------------------------------------------- */
179
670
/*      Try to open the sqlite database properly now.                   */
180
671
/* -------------------------------------------------------------------- */
181
 
    int rc;
182
 
 
183
 
    hDB = NULL;
184
 
    rc = sqlite3_open( pszNewName, &hDB );
185
 
    if( rc != SQLITE_OK )
 
672
    if (hDB == NULL)
186
673
    {
187
 
        CPLError( CE_Failure, CPLE_OpenFailed, 
188
 
                  "sqlite3_open(%s) failed: %s", 
189
 
                  pszNewName, sqlite3_errmsg( hDB ) );
190
 
                  
191
 
        return FALSE;
 
674
        OGRSQLiteInitSpatialite();
 
675
 
 
676
#ifdef HAVE_SQLITE_VFS
 
677
        if (!OpenOrCreateDB((bUpdateIn) ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY) )
 
678
#else
 
679
        if (!OpenOrCreateDB(0))
 
680
#endif
 
681
            return FALSE;
192
682
    }
193
 
    
 
683
 
 
684
    int rc;
 
685
    char *pszErrMsg = NULL;
 
686
 
194
687
    CPLHashSet* hSet = CPLHashSetNew(CPLHashSetHashStr, CPLHashSetEqualStr, CPLFree);
195
688
 
196
689
/* -------------------------------------------------------------------- */
199
692
/* -------------------------------------------------------------------- */
200
693
    char **papszResult;
201
694
    int nRowCount, iRow, nColCount;
202
 
    char *pszErrMsg;
203
695
 
204
696
    rc = sqlite3_get_table( 
205
697
        hDB,
263
755
 
264
756
    if ( rc == SQLITE_OK )
265
757
    {
266
 
        CPLDebug("SQLITE", "SpatiaLite-style SQLite DB found !");
267
758
        
268
759
        bIsSpatiaLite = TRUE;
269
760
        bHaveGeometryColumns = TRUE;
270
761
 
 
762
        int bSpatialiteReadOnly = TRUE;
 
763
        int iSpatialiteVersion = -1;
 
764
 
 
765
        /* Only enables write-mode if linked against SpatiaLite */
 
766
        if( bSpatialiteLoaded == TRUE )
 
767
        {
 
768
            iSpatialiteVersion = OGRSQLiteGetSpatialiteVersionNumber();
 
769
            bSpatialiteReadOnly = FALSE;
 
770
        }
 
771
 
 
772
        if (bSpatialiteReadOnly && bUpdate)
 
773
        {
 
774
            CPLDebug("SQLITE", "SpatiaLite-style SQLite DB found, but updating tables disabled because no linking against spatialite library !");
 
775
        }
 
776
        else
 
777
        {
 
778
            CPLDebug("SQLITE", "SpatiaLite-style SQLite DB found !");
 
779
        }
 
780
 
 
781
        /*
 
782
        / SpatiaLite v.2.4.0 (or any subsequent) is required
 
783
        / to support 2.5D: if an obsolete version of the library
 
784
        / is found we'll unconditionally activate 2D casting mode
 
785
        */
 
786
        int bForce2D = FALSE;
 
787
        iSpatialiteVersion = OGRSQLiteGetSpatialiteVersionNumber();
 
788
        if ( iSpatialiteVersion < 24)
 
789
            bForce2D = TRUE;
 
790
 
271
791
        for ( iRow = 0; iRow < nRowCount; iRow++ )
272
792
        {
273
793
            char **papszRow = papszResult + iRow * 6 + 6;
274
794
            OGRwkbGeometryType eGeomType;
275
795
            int nSRID = 0;
 
796
            int bHasM = FALSE;
276
797
            int bHasSpatialIndex = FALSE;
277
798
 
278
799
            if (papszRow[0] == NULL ||
281
802
                papszRow[3] == NULL)
282
803
                continue;
283
804
 
284
 
            eGeomType = SpatiaLiteToOGRGeomType(papszRow[2]);
 
805
            eGeomType = OGRFromOGCGeomType(papszRow[2]);
285
806
 
286
 
            if( atoi(papszRow[3]) > 2 )
 
807
            if( strcmp ( papszRow[3], "XYZ" ) == 0 || 
 
808
                strcmp ( papszRow[3], "XYZM" ) == 0 || 
 
809
                strcmp ( papszRow[3], "3" ) == 0) // SpatiaLite's own 3D geometries 
287
810
                eGeomType = (OGRwkbGeometryType) (((int)eGeomType) | wkb25DBit);
288
811
 
 
812
            if( strcmp ( papszRow[3], "XYM" ) == 0 || 
 
813
                strcmp ( papszRow[3], "XYZM" ) == 0 ) // M coordinate declared 
 
814
                bHasM = TRUE;
 
815
 
 
816
 
289
817
            if( papszRow[4] != NULL )
290
818
                nSRID = atoi(papszRow[4]);
291
819
 
294
822
                bHasSpatialIndex = atoi(papszRow[5]);
295
823
 
296
824
            OpenTable( papszRow[0], papszRow[1], eGeomType, "SpatiaLite",
297
 
                       FetchSRS( nSRID ), nSRID, bHasSpatialIndex );
 
825
                       FetchSRS( nSRID ), nSRID, bHasSpatialIndex, bHasM, 
 
826
                       bSpatialiteReadOnly, bSpatialiteLoaded,
 
827
                       iSpatialiteVersion, bForce2D );
298
828
                       
299
829
            if (bListAllTables)
300
830
                CPLHashSetInsert(hSet, CPLStrdup(papszRow[0]));
309
839
        if (bSpatialiteLoaded)
310
840
        {
311
841
            rc = sqlite3_get_table( hDB,
312
 
                                "SELECT name FROM sqlite_master WHERE sql LIKE 'CREATE VIRTUAL TABLE % USING %VirtualShape%'",
 
842
                                "SELECT name, sql FROM sqlite_master WHERE sql LIKE 'CREATE VIRTUAL TABLE %'",
313
843
                                &papszResult, &nRowCount, 
314
844
                                &nColCount, &pszErrMsg );
315
845
 
317
847
            {
318
848
                for( iRow = 0; iRow < nRowCount; iRow++ )
319
849
                {
320
 
                    OpenTable( papszResult[iRow+1] );
321
 
                    
322
 
                    if (bListAllTables)
323
 
                        CPLHashSetInsert(hSet, CPLStrdup(papszResult[iRow+1]));
 
850
                    char **papszRow = papszResult + iRow * 2 + 2;
 
851
                    const char *pszName = papszRow[0];
 
852
                    const char *pszSQL = papszRow[1];
 
853
                    if( pszName == NULL || pszSQL == NULL )
 
854
                        continue;
 
855
 
 
856
                    if( strstr(pszSQL, "VirtualShape") || strstr(pszSQL, "VirtualXL") )
 
857
                    {
 
858
                        OpenVirtualTable(pszName, pszSQL);
 
859
 
 
860
                        if (bListAllTables)
 
861
                            CPLHashSetInsert(hSet, CPLStrdup(pszName));
 
862
                    }
324
863
                }
325
864
            }
326
865
            else
335
874
        }
336
875
#endif
337
876
 
 
877
/* -------------------------------------------------------------------- */
 
878
/*      Detect spatial views                                            */
 
879
/* -------------------------------------------------------------------- */
 
880
        rc = sqlite3_get_table( hDB,
 
881
                                "SELECT view_name, view_geometry, view_rowid, f_table_name, f_geometry_column FROM views_geometry_columns",
 
882
                                &papszResult, &nRowCount,
 
883
                                &nColCount, &pszErrMsg );
 
884
        if ( rc == SQLITE_OK )
 
885
        {
 
886
            for( iRow = 0; iRow < nRowCount; iRow++ )
 
887
            {
 
888
                char **papszRow = papszResult + iRow * 5 + 5;
 
889
                const char* pszViewName = papszRow[0];
 
890
                const char* pszViewGeometry = papszRow[1];
 
891
                const char* pszViewRowid = papszRow[2];
 
892
                const char* pszTableName = papszRow[3];
 
893
                const char* pszGeometryColumn = papszRow[4];
 
894
 
 
895
                if (pszViewName == NULL ||
 
896
                    pszViewGeometry == NULL ||
 
897
                    pszViewRowid == NULL ||
 
898
                    pszTableName == NULL ||
 
899
                    pszGeometryColumn == NULL)
 
900
                    continue;
 
901
 
 
902
                OpenView( pszViewName, pszViewGeometry, pszViewRowid,
 
903
                          pszTableName, pszGeometryColumn, bSpatialiteLoaded );
 
904
 
 
905
                if (bListAllTables)
 
906
                    CPLHashSetInsert(hSet, CPLStrdup(pszViewName));
 
907
            }
 
908
            sqlite3_free_table(papszResult);
 
909
        }
 
910
 
 
911
 
338
912
        if (bListAllTables)
339
913
            goto all_tables;
340
914
 
383
957
}
384
958
 
385
959
/************************************************************************/
 
960
/*                          OpenVirtualTable()                          */
 
961
/************************************************************************/
 
962
 
 
963
int OGRSQLiteDataSource::OpenVirtualTable(const char* pszName, const char* pszSQL)
 
964
{
 
965
    int nSRID = -1;
 
966
    const char* pszVirtualShape = strstr(pszSQL, "VirtualShape");
 
967
    if (pszVirtualShape != NULL)
 
968
    {
 
969
        const char* pszParenthesis = strchr(pszVirtualShape, '(');
 
970
        if (pszParenthesis)
 
971
        {
 
972
            /* CREATE VIRTUAL TABLE table_name VirtualShape(shapename, codepage, srid) */
 
973
            /* Extract 3rd parameter */
 
974
            char** papszTokens = CSLTokenizeString2( pszParenthesis + 1, ",", CSLT_HONOURSTRINGS );
 
975
            if (CSLCount(papszTokens) == 3)
 
976
            {
 
977
                nSRID = atoi(papszTokens[2]);
 
978
            }
 
979
            CSLDestroy(papszTokens);
 
980
        }
 
981
    }
 
982
 
 
983
    if (OpenTable(pszName, NULL, wkbUnknown, NULL,
 
984
                 (nSRID > 0) ? FetchSRS( nSRID ) : NULL, nSRID,
 
985
                  FALSE, FALSE, TRUE, FALSE, -1, FALSE,
 
986
                  pszVirtualShape != NULL))
 
987
    {
 
988
        OGRSQLiteLayer* poLayer = papoLayers[nLayers-1];
 
989
        OGRFeature* poFeature = poLayer->GetNextFeature();
 
990
        if (poFeature)
 
991
        {
 
992
            OGRGeometry* poGeom = poFeature->GetGeometryRef();
 
993
            if (poGeom)
 
994
                poLayer->GetLayerDefn()->SetGeomType(poGeom->getGeometryType());
 
995
            delete poFeature;
 
996
        }
 
997
        poLayer->ResetReading();
 
998
        return TRUE;
 
999
    }
 
1000
    return FALSE;
 
1001
}
 
1002
 
 
1003
/************************************************************************/
386
1004
/*                             OpenTable()                              */
387
1005
/************************************************************************/
388
1006
 
391
1009
                                    OGRwkbGeometryType eGeomType,
392
1010
                                    const char *pszGeomFormat,
393
1011
                                    OGRSpatialReference *poSRS, int nSRID,
394
 
                                    int bHasSpatialIndex)
 
1012
                                    int bHasSpatialIndex, int bHasM, 
 
1013
                                    int bSpatialiteReadOnly,
 
1014
                                    int bSpatialiteLoaded,
 
1015
                                    int iSpatialiteVersion,
 
1016
                                    int bForce2D,
 
1017
                                    int bIsVirtualShapeIn )
395
1018
 
396
1019
{
397
1020
/* -------------------------------------------------------------------- */
403
1026
 
404
1027
    if( poLayer->Initialize( pszNewName, pszGeomCol, 
405
1028
                             eGeomType, pszGeomFormat,
406
 
                             poSRS, nSRID, bHasSpatialIndex ) )
 
1029
                             poSRS, nSRID, bHasSpatialIndex, 
 
1030
                             bHasM, bSpatialiteReadOnly,
 
1031
                             bSpatialiteLoaded,
 
1032
                             iSpatialiteVersion,
 
1033
                             bIsVirtualShapeIn) != CE_None )
407
1034
    {
408
1035
        delete poLayer;
409
1036
        return FALSE;
410
1037
    }
 
1038
    poLayer->SetSpatialite2D ( bForce2D );
411
1039
 
412
1040
/* -------------------------------------------------------------------- */
413
1041
/*      Add layer to data source layer list.                            */
420
1048
}
421
1049
 
422
1050
/************************************************************************/
 
1051
/*                             OpenView()                               */
 
1052
/************************************************************************/
 
1053
 
 
1054
int OGRSQLiteDataSource::OpenView( const char *pszViewName,
 
1055
                                   const char *pszViewGeometry,
 
1056
                                   const char *pszViewRowid,
 
1057
                                   const char *pszTableName,
 
1058
                                   const char *pszGeometryColumn,
 
1059
                                   int bSpatialiteLoaded)
 
1060
 
 
1061
{
 
1062
/* -------------------------------------------------------------------- */
 
1063
/*      Create the layer object.                                        */
 
1064
/* -------------------------------------------------------------------- */
 
1065
    OGRSQLiteViewLayer  *poLayer;
 
1066
 
 
1067
    poLayer = new OGRSQLiteViewLayer( this );
 
1068
 
 
1069
    if( poLayer->Initialize( pszViewName, pszViewGeometry,
 
1070
                             pszViewRowid, pszTableName, pszGeometryColumn,
 
1071
                             bSpatialiteLoaded ) != CE_None )
 
1072
    {
 
1073
        delete poLayer;
 
1074
        return FALSE;
 
1075
    }
 
1076
 
 
1077
/* -------------------------------------------------------------------- */
 
1078
/*      Add layer to data source layer list.                            */
 
1079
/* -------------------------------------------------------------------- */
 
1080
    papoLayers = (OGRSQLiteLayer **)
 
1081
        CPLRealloc( papoLayers,  sizeof(OGRSQLiteLayer *) * (nLayers+1) );
 
1082
    papoLayers[nLayers++] = poLayer;
 
1083
 
 
1084
    return TRUE;
 
1085
}
 
1086
 
 
1087
/************************************************************************/
423
1088
/*                           TestCapability()                           */
424
1089
/************************************************************************/
425
1090
 
427
1092
 
428
1093
{
429
1094
    if( EQUAL(pszCap,ODsCCreateLayer) )
430
 
        return TRUE;
 
1095
        return bUpdate;
 
1096
    else if( EQUAL(pszCap,ODsCDeleteLayer) )
 
1097
        return bUpdate;
431
1098
    else
432
1099
        return FALSE;
433
1100
}
449
1116
/*                             ExecuteSQL()                             */
450
1117
/************************************************************************/
451
1118
 
 
1119
static const char* apszSpatialiteFuncs[] =
 
1120
{
 
1121
    "InitSpatialMetaData",
 
1122
    "AddGeometryColumn",
 
1123
    "RecoverGeometryColumn",
 
1124
    "DiscardGeometryColumn",
 
1125
    "CreateSpatialIndex",
 
1126
    "CreateMbrCache",
 
1127
    "DisableSpatialIndex"
 
1128
};
 
1129
 
452
1130
OGRLayer * OGRSQLiteDataSource::ExecuteSQL( const char *pszSQLCommand,
453
1131
                                          OGRGeometry *poSpatialFilter,
454
1132
                                          const char *pszDialect )
508
1186
                  "In ExecuteSQL(): sqlite3_step(%s):\n  %s", 
509
1187
                  pszSQLCommand, sqlite3_errmsg(GetDB()) );
510
1188
        }
 
1189
 
 
1190
        if( EQUALN(pszSQLCommand, "CREATE ", 7) )
 
1191
        {
 
1192
            char **papszTokens = CSLTokenizeString( pszSQLCommand );
 
1193
            if ( CSLCount(papszTokens) >= 4 &&
 
1194
                 EQUAL(papszTokens[1], "VIRTUAL") &&
 
1195
                 EQUAL(papszTokens[2], "TABLE") )
 
1196
            {
 
1197
                OpenVirtualTable(papszTokens[3], pszSQLCommand);
 
1198
            }
 
1199
            CSLDestroy(papszTokens);
 
1200
        }
 
1201
 
511
1202
        sqlite3_finalize( hSQLStmt );
512
1203
        return NULL;
513
1204
    }
514
1205
    
515
1206
/* -------------------------------------------------------------------- */
 
1207
/*      Special case for some spatialite functions which must be run    */
 
1208
/*      only once                                                       */
 
1209
/* -------------------------------------------------------------------- */
 
1210
    if( EQUALN(pszSQLCommand,"SELECT ",7) &&
 
1211
        bIsSpatiaLite && bSpatialiteLoaded )
 
1212
    {
 
1213
        unsigned int i;
 
1214
        for(i=0;i<sizeof(apszSpatialiteFuncs)/
 
1215
                  sizeof(apszSpatialiteFuncs[0]);i++)
 
1216
        {
 
1217
            if( EQUALN(apszSpatialiteFuncs[i], pszSQLCommand + 7,
 
1218
                       strlen(apszSpatialiteFuncs[i])) )
 
1219
            {
 
1220
                if (sqlite3_column_count( hSQLStmt ) == 1 &&
 
1221
                    sqlite3_column_type( hSQLStmt, 0 ) == SQLITE_INTEGER )
 
1222
                {
 
1223
                    int ret = sqlite3_column_int( hSQLStmt, 0 );
 
1224
 
 
1225
                    sqlite3_finalize( hSQLStmt );
 
1226
 
 
1227
                    return new OGRSQLiteSingleFeatureLayer
 
1228
                                        ( apszSpatialiteFuncs[i], ret );
 
1229
                }
 
1230
            }
 
1231
        }
 
1232
    }
 
1233
 
 
1234
/* -------------------------------------------------------------------- */
516
1235
/*      Create layer.                                                   */
517
1236
/* -------------------------------------------------------------------- */
518
1237
    OGRSQLiteSelectLayer *poLayer = NULL;
519
1238
        
520
 
    poLayer = new OGRSQLiteSelectLayer( this, hSQLStmt );
 
1239
    CPLString osSQL = pszSQLCommand;
 
1240
    poLayer = new OGRSQLiteSelectLayer( this, osSQL, hSQLStmt );
521
1241
 
522
1242
    if( poSpatialFilter != NULL )
523
1243
        poLayer->SetSpatialFilter( poSpatialFilter );
547
1267
 
548
1268
{
549
1269
    char                *pszLayerName;
 
1270
    int                  bForce2D = FALSE;
550
1271
    const char          *pszGeomFormat;
551
1272
 
 
1273
/* -------------------------------------------------------------------- */
 
1274
/*      Verify we are in update mode.                                   */
 
1275
/* -------------------------------------------------------------------- */
 
1276
    if( !bUpdate )
 
1277
    {
 
1278
        CPLError( CE_Failure, CPLE_NoWriteAccess,
 
1279
                  "Data source %s opened read-only.\n"
 
1280
                  "New layer %s cannot be created.\n",
 
1281
                  pszName, pszLayerNameIn );
 
1282
 
 
1283
        return NULL;
 
1284
    }
 
1285
 
552
1286
    if( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) )
553
1287
        pszLayerName = LaunderName( pszLayerNameIn );
554
1288
    else
555
1289
        pszLayerName = CPLStrdup( pszLayerNameIn );
 
1290
 
 
1291
    CPLString osEscapedLayerName = OGRSQLiteEscape(pszLayerName);
 
1292
    const char* pszEscapedLayerName = osEscapedLayerName.c_str();
556
1293
    
557
1294
    pszGeomFormat = CSLFetchNameValue( papszOptions, "FORMAT" );
558
1295
    if( pszGeomFormat == NULL )
567
1304
        && !EQUAL(pszGeomFormat,"WKB")
568
1305
        && !EQUAL(pszGeomFormat, "SpatiaLite") )
569
1306
    {
570
 
        CPLError( CE_Failure, CPLE_AppDefined, 
 
1307
        CPLError( CE_Failure, CPLE_NotSupported, 
571
1308
                  "FORMAT=%s not recognised or supported.", 
572
1309
                  pszGeomFormat );
573
1310
        return NULL;
574
1311
    }
575
1312
 
 
1313
    if (bIsSpatiaLite && !EQUAL(pszGeomFormat, "SpatiaLite") )
 
1314
    {
 
1315
        CPLError( CE_Failure, CPLE_NotSupported,
 
1316
                  "FORMAT=%s not support on a SpatiaLite enabled database.",
 
1317
                  pszGeomFormat );
 
1318
        return NULL;
 
1319
    }
 
1320
    if (bIsSpatiaLite && !bSpatialiteLoaded)
 
1321
    {
 
1322
        CPLError( CE_Failure, CPLE_NotSupported,
 
1323
                  "Creating layers on a SpatiaLite enabled database, "
 
1324
                  "without Spatialite extensions loaded, is not supported." );
 
1325
        return NULL;
 
1326
    }
 
1327
 
576
1328
/* -------------------------------------------------------------------- */
577
1329
/*      Do we already have this layer?  If so, should we blow it        */
578
1330
/*      away?                                                           */
622
1374
    if( eType == wkbNone )
623
1375
        osCommand.Printf( 
624
1376
            "CREATE TABLE '%s' ( OGC_FID INTEGER PRIMARY KEY )", 
625
 
            pszLayerName );
 
1377
            pszEscapedLayerName );
626
1378
    else
627
1379
    {
628
1380
        if( EQUAL(pszGeomFormat,"WKT") )
632
1384
                "CREATE TABLE '%s' ( "
633
1385
                "  OGC_FID INTEGER PRIMARY KEY,"
634
1386
                "  %s VARCHAR )", 
635
 
                pszLayerName, pszGeomCol );
 
1387
                pszEscapedLayerName, pszGeomCol );
636
1388
        }
637
1389
        else
638
1390
        {
639
1391
            pszGeomCol = "GEOMETRY";
640
 
            osCommand.Printf(
641
 
                "CREATE TABLE '%s' ( "
642
 
                "  OGC_FID INTEGER PRIMARY KEY,"
643
 
                "  %s BLOB )", 
644
 
                pszLayerName, pszGeomCol );
 
1392
 
 
1393
            /* Only if was created as a SpatiaLite DB */
 
1394
            if ( bIsSpatiaLite )
 
1395
            {
 
1396
                /* 
 
1397
                / SpatiaLite full support: we must create the 
 
1398
                / Geometry in a second time using AddGeometryColumn()
 
1399
                /
 
1400
                / IMPORTANT NOTICE: on SpatiaLite any attempt aimed
 
1401
                / to directly creating some Geometry column 
 
1402
                / [by-passing AddGeometryColumn() as absolutely required]
 
1403
                / will severely [and irremediably] corrupt the DB !!!
 
1404
                */
 
1405
                osCommand.Printf( "CREATE TABLE '%s' ( "
 
1406
                                  "  OGC_FID INTEGER PRIMARY KEY)",
 
1407
                                  pszLayerName);
 
1408
            }
 
1409
            else
 
1410
            {
 
1411
                osCommand.Printf( "CREATE TABLE '%s' ( "
 
1412
                                  "  OGC_FID INTEGER PRIMARY KEY,"
 
1413
                                  "  %s BLOB )", 
 
1414
                                  pszLayerName, pszGeomCol );
 
1415
            }
645
1416
        }
646
1417
    }
647
1418
 
674
1445
         */
675
1446
        osCommand.Printf(
676
1447
            "DELETE FROM geometry_columns WHERE f_table_name = '%s'", 
677
 
            pszLayerName );
 
1448
            pszEscapedLayerName );
678
1449
                 
679
1450
#ifdef DEBUG
680
1451
        CPLDebug( "OGR_SQLITE", "exec(%s)", osCommand.c_str() );
691
1462
            nCoordDim = 2;
692
1463
        else
693
1464
            nCoordDim = 3;
694
 
 
695
 
        if( nSRSId > 0 )
696
 
        {
697
 
            if ( bIsSpatiaLite )
698
 
                osCommand.Printf(
699
 
                    "INSERT INTO geometry_columns "
700
 
                    "(f_table_name, f_geometry_column, type, "
701
 
                    "coord_dimension, srid, spatial_index_enabled) "
702
 
                    "VALUES ('%s','%s', '%s', %d, %d, 0)", 
703
 
                    pszLayerName, pszGeomCol, OGRToSpatiaLiteGeomType(eType),
704
 
                    nCoordDim, nSRSId );
705
 
            else
 
1465
        
 
1466
        if ( bIsSpatiaLite )
 
1467
        {
 
1468
            /*
 
1469
            / SpatiaLite full support: calling AddGeometryColumn()
 
1470
            /
 
1471
            / IMPORTANT NOTICE: on SpatiaLite any attempt aimed
 
1472
            / to directly INSERT a row into GEOMETRY_COLUMNS
 
1473
            / [by-passing AddGeometryColumn() as absolutely required]
 
1474
            / will severely [and irremediably] corrupt the DB !!!
 
1475
            */
 
1476
            const char *pszType = OGRToOGCGeomType(eType);
 
1477
            if (pszType[0] == '\0')
 
1478
                pszType = "GEOMETRY";
 
1479
 
 
1480
            /*
 
1481
            / SpatiaLite v.2.4.0 (or any subsequent) is required
 
1482
            / to support 2.5D: if an obsolete version of the library
 
1483
            / is found we'll unconditionally activate 2D casting mode
 
1484
            */
 
1485
            int iSpatialiteVersion = OGRSQLiteGetSpatialiteVersionNumber();
 
1486
            if ( iSpatialiteVersion < 24 && nCoordDim == 3 )
 
1487
            {
 
1488
                CPLDebug("SQLITE", "Spatialite < 2.4.0 --> 2.5D geometry not supported. Casting to 2D");
 
1489
                nCoordDim = 2;
 
1490
                bForce2D = TRUE;
 
1491
            }
 
1492
 
 
1493
            osCommand.Printf( "SELECT AddGeometryColumn("
 
1494
                              "'%s', '%s', %d, '%s', %d)",
 
1495
                              pszLayerName, pszGeomCol, nSRSId,
 
1496
                              pszType, nCoordDim );
 
1497
        }
 
1498
        else
 
1499
        {
 
1500
            if( nSRSId > 0 )
 
1501
            {
706
1502
                osCommand.Printf(
707
1503
                    "INSERT INTO geometry_columns "
708
1504
                    "(f_table_name, f_geometry_column, geometry_format, "
709
1505
                    "geometry_type, coord_dimension, srid) VALUES "
710
1506
                    "('%s','%s','%s', %d, %d, %d)", 
711
 
                    pszLayerName, pszGeomCol, pszGeomFormat,
 
1507
                    pszEscapedLayerName, pszGeomCol, pszGeomFormat,
712
1508
                    (int) wkbFlatten(eType), nCoordDim, nSRSId );
713
 
        }
714
 
        else
715
 
        {
716
 
            if ( bIsSpatiaLite )
717
 
                osCommand.Printf(
718
 
                    "INSERT INTO geometry_columns "
719
 
                    "(f_table_name, f_geometry_column, type, "
720
 
                    "coord_dimension, spatial_index_enabled) "
721
 
                    "VALUES ('%s','%s', '%s', %d, 0)", 
722
 
                    pszLayerName, pszGeomCol, OGRToSpatiaLiteGeomType(eType),
723
 
                    nCoordDim );
 
1509
            }
724
1510
            else
 
1511
            {
725
1512
                osCommand.Printf(
726
1513
                    "INSERT INTO geometry_columns "
727
1514
                    "(f_table_name, f_geometry_column, geometry_format, "
728
1515
                    "geometry_type, coord_dimension) VALUES "
729
 
                    "('%s','%s','%s', %d, %d)", 
730
 
                    pszLayerName, pszGeomCol, pszGeomFormat,
 
1516
                    "('%s','%s','%s', %d, %d)",
 
1517
                    pszEscapedLayerName, pszGeomCol, pszGeomFormat,
731
1518
                    (int) wkbFlatten(eType), nCoordDim );
 
1519
            }
732
1520
        }
733
1521
 
734
1522
#ifdef DEBUG
751
1539
/*      We're doing this before we add geometry and record to the table */
752
1540
/*      so this may not be exactly the best way to do it.               */
753
1541
/* -------------------------------------------------------------------- */
 
1542
 
 
1543
        const char* pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
 
1544
        if ( pszSI != NULL && CSLTestBoolean(pszSI) &&
 
1545
             (bIsSpatiaLite || EQUAL(pszGeomFormat, "SpatiaLite")) && !bSpatialiteLoaded )
 
1546
        {
 
1547
            CPLError( CE_Warning, CPLE_OpenFailed,
 
1548
                    "Cannot create a spatial index when Spatialite extensions are not loaded." );
 
1549
        }
 
1550
 
754
1551
#ifdef HAVE_SPATIALITE
755
1552
        /* Only if linked against SpatiaLite and the datasource was created as a SpatiaLite DB */
756
1553
        if ( bIsSpatiaLite && bSpatialiteLoaded )
758
1555
        if ( 0 )
759
1556
#endif
760
1557
        {
761
 
            const char* pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
762
1558
            if( pszSI == NULL || CSLTestBoolean(pszSI) )
763
1559
            {
764
1560
                osCommand.Printf("SELECT CreateSpatialIndex('%s', '%s')",
765
 
                                 pszLayerName, pszGeomCol);
766
 
 
767
 
            /* -------------------------------------------------------------------- */
768
 
            /*      Prepare statement.                                              */
769
 
            /* -------------------------------------------------------------------- */
770
 
                int rc;
771
 
                sqlite3_stmt *hSQLStmt = NULL;
772
 
 
773
 
                rc = sqlite3_prepare( GetDB(), osCommand, strlen(osCommand),
774
 
                                      &hSQLStmt, NULL );
775
 
 
 
1561
                                 pszEscapedLayerName, pszGeomCol);
 
1562
 
 
1563
                rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
776
1564
                if( rc != SQLITE_OK )
777
1565
                {
778
1566
                    CPLError( CE_Failure, CPLE_AppDefined, 
779
 
                            "In CreateLayer(): sqlite3_prepare(%s):\n  %s", 
780
 
                            osCommand.c_str(), sqlite3_errmsg(GetDB()) );
781
 
                }
782
 
                else
783
 
                {
784
 
                    /* -------------------------------------------------------------------- */
785
 
                    /*      Do we get a resultset?                                          */
786
 
                    /* -------------------------------------------------------------------- */
787
 
                    rc = sqlite3_step( hSQLStmt );
788
 
                    if( rc != SQLITE_ROW )
789
 
                    {
790
 
                        if ( rc != SQLITE_DONE )
791
 
                        {
792
 
                            CPLError( CE_Failure, CPLE_AppDefined, 
793
 
                                "In CreateLayer(): sqlite3_step(%s):\n  %s", 
794
 
                                osCommand.c_str(), sqlite3_errmsg(GetDB()) );
795
 
                        }
796
 
                    }
797
 
                    else
798
 
                    {
799
 
                        if (sqlite3_column_count(hSQLStmt) != 1 &&
800
 
                            sqlite3_column_int(hSQLStmt, 0) != 1)
801
 
                        {
802
 
                            CPLError( CE_Failure, CPLE_AppDefined, 
803
 
                                "In CreateLayer(): sqlite3_step(%s): did not get expected result", 
804
 
                                osCommand.c_str() );
805
 
                        }
806
 
                    }
807
 
                }
808
 
 
809
 
                if( hSQLStmt != NULL )
810
 
                {
811
 
                    sqlite3_finalize( hSQLStmt );
 
1567
                            "Unable to create spatial index:\n%s", pszErrMsg );
 
1568
                    sqlite3_free( pszErrMsg );
 
1569
                    return FALSE;
812
1570
                }
813
1571
            }
814
1572
        }
821
1579
 
822
1580
    poLayer = new OGRSQLiteTableLayer( this );
823
1581
 
824
 
    poLayer->Initialize( pszLayerName, pszGeomCol, eType, pszGeomFormat, 
825
 
                         FetchSRS(nSRSId), nSRSId );
 
1582
    if ( poLayer->Initialize( pszLayerName, pszGeomCol, eType, pszGeomFormat,
 
1583
                         FetchSRS(nSRSId), nSRSId ) != CE_None )
 
1584
    {
 
1585
        delete poLayer;
 
1586
        CPLFree( pszLayerName );
 
1587
        return NULL;
 
1588
    }
826
1589
 
827
1590
    poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
 
1591
    if ( CSLFetchBoolean(papszOptions,"COMPRESS_GEOM",FALSE) )
 
1592
        poLayer->SetUseCompressGeom( TRUE );
 
1593
    poLayer->SetSpatialite2D ( bForce2D );
828
1594
 
829
1595
/* -------------------------------------------------------------------- */
830
1596
/*      Add layer to data source layer list.                            */
852
1618
    for( i = 0; pszSafeName[i] != '\0'; i++ )
853
1619
    {
854
1620
        pszSafeName[i] = (char) tolower( pszSafeName[i] );
855
 
        if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
 
1621
        if( pszSafeName[i] == '\'' || pszSafeName[i] == '-' || pszSafeName[i] == '#' )
856
1622
            pszSafeName[i] = '_';
857
1623
    }
858
1624
 
860
1626
}
861
1627
 
862
1628
/************************************************************************/
 
1629
/*                          OGRSQLiteEscape()                           */
 
1630
/************************************************************************/
 
1631
 
 
1632
CPLString OGRSQLiteEscape( const char *pszSrcName )
 
1633
{
 
1634
    CPLString osVal;
 
1635
    for( int i = 0; pszSrcName[i] != '\0'; i++ )
 
1636
    {
 
1637
        if ( pszSrcName[i] == '\'' )
 
1638
            osVal += '\'';
 
1639
        osVal += pszSrcName[i];
 
1640
    }
 
1641
    return osVal;
 
1642
}
 
1643
 
 
1644
/************************************************************************/
863
1645
/*                            DeleteLayer()                             */
864
1646
/************************************************************************/
865
1647
 
869
1651
    int iLayer;
870
1652
 
871
1653
/* -------------------------------------------------------------------- */
 
1654
/*      Verify we are in update mode.                                   */
 
1655
/* -------------------------------------------------------------------- */
 
1656
    if( !bUpdate )
 
1657
    {
 
1658
        CPLError( CE_Failure, CPLE_NoWriteAccess,
 
1659
                  "Data source %s opened read-only.\n"
 
1660
                  "Layer %s cannot be deleted.\n",
 
1661
                  pszName, pszLayerName );
 
1662
 
 
1663
        return;
 
1664
    }
 
1665
 
 
1666
/* -------------------------------------------------------------------- */
872
1667
/*      Try to find layer.                                              */
873
1668
/* -------------------------------------------------------------------- */
874
1669
    for( iLayer = 0; iLayer < nLayers; iLayer++ )
885
1680
        return;
886
1681
    }
887
1682
 
 
1683
    DeleteLayer(iLayer);
 
1684
}
 
1685
 
 
1686
/************************************************************************/
 
1687
/*                            DeleteLayer()                             */
 
1688
/************************************************************************/
 
1689
 
 
1690
OGRErr OGRSQLiteDataSource::DeleteLayer(int iLayer)
 
1691
{
 
1692
    if( iLayer < 0 || iLayer >= nLayers )
 
1693
    {
 
1694
        CPLError( CE_Failure, CPLE_AppDefined,
 
1695
                  "Layer %d not in legal range of 0 to %d.",
 
1696
                  iLayer, nLayers-1 );
 
1697
        return OGRERR_FAILURE;
 
1698
    }
 
1699
 
 
1700
    CPLString osLayerName = GetLayer(iLayer)->GetName();
 
1701
    CPLString osGeometryColumn = GetLayer(iLayer)->GetGeometryColumn();
 
1702
 
888
1703
/* -------------------------------------------------------------------- */
889
1704
/*      Blow away our OGR structures related to the layer.  This is     */
890
1705
/*      pretty dangerous if anything has a reference to this layer!     */
891
1706
/* -------------------------------------------------------------------- */
892
 
    CPLDebug( "OGR_SQLITE", "DeleteLayer(%s)", pszLayerName );
 
1707
    CPLDebug( "OGR_SQLITE", "DeleteLayer(%s)", osLayerName.c_str() );
893
1708
 
894
1709
    delete papoLayers[iLayer];
895
1710
    memmove( papoLayers + iLayer, papoLayers + iLayer + 1, 
902
1717
    int rc;
903
1718
    char *pszErrMsg;
904
1719
 
905
 
    rc = sqlite3_exec( hDB, CPLSPrintf( "DROP TABLE '%s'", pszLayerName ),
 
1720
    CPLString osEscapedLayerName = OGRSQLiteEscape(osLayerName);
 
1721
    const char* pszEscapedLayerName = osEscapedLayerName.c_str();
 
1722
    const char* pszGeometryColumn = osGeometryColumn.size() ? osGeometryColumn.c_str() : NULL;
 
1723
 
 
1724
    rc = sqlite3_exec( hDB, CPLSPrintf( "DROP TABLE '%s'", pszEscapedLayerName ),
906
1725
                       NULL, NULL, &pszErrMsg );
907
1726
    if( rc != SQLITE_OK )
908
1727
    {
909
1728
        CPLError( CE_Failure, CPLE_AppDefined, 
910
1729
                  "Unable to drop table %s: %s",
911
 
                  pszLayerName, pszErrMsg );
 
1730
                  osLayerName.c_str(), pszErrMsg );
912
1731
        sqlite3_free( pszErrMsg );
913
 
        return;
 
1732
        return OGRERR_FAILURE;
914
1733
    }
915
1734
 
916
1735
/* -------------------------------------------------------------------- */
922
1741
 
923
1742
        osCommand.Printf( 
924
1743
            "DELETE FROM geometry_columns WHERE f_table_name = '%s'",
925
 
            pszLayerName );
 
1744
            pszEscapedLayerName );
926
1745
        
927
1746
        rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
928
1747
        if( rc != SQLITE_OK )
931
1750
                      "Removal from geometry_columns failed.\n%s: %s", 
932
1751
                      osCommand.c_str(), pszErrMsg );
933
1752
            sqlite3_free( pszErrMsg );
 
1753
            return OGRERR_FAILURE;
 
1754
        }
 
1755
 
 
1756
/* -------------------------------------------------------------------- */
 
1757
/*      Drop spatialite spatial index tables                            */
 
1758
/* -------------------------------------------------------------------- */
 
1759
        if( bIsSpatiaLite && pszGeometryColumn )
 
1760
        {
 
1761
            osCommand.Printf( "DROP TABLE idx_%s_%s", pszEscapedLayerName,
 
1762
                              pszGeometryColumn);
 
1763
            rc = sqlite3_exec( hDB, osCommand, NULL, NULL, NULL );
 
1764
 
 
1765
            osCommand.Printf( "DROP TABLE idx_%s_%s_node", pszEscapedLayerName,
 
1766
                              pszGeometryColumn);
 
1767
            rc = sqlite3_exec( hDB, osCommand, NULL, NULL, NULL );
 
1768
 
 
1769
            osCommand.Printf( "DROP TABLE idx_%s_%s_parent", pszEscapedLayerName,
 
1770
                              pszGeometryColumn);
 
1771
            rc = sqlite3_exec( hDB, osCommand, NULL, NULL, NULL );
 
1772
 
 
1773
            osCommand.Printf( "DROP TABLE idx_%s_%s_rowid", pszEscapedLayerName,
 
1774
                              pszGeometryColumn);
 
1775
            rc = sqlite3_exec( hDB, osCommand, NULL, NULL, NULL );
934
1776
        }
935
1777
    }
 
1778
    return OGRERR_NONE;
936
1779
}
937
1780
 
938
1781
/************************************************************************/
1070
1913
}
1071
1914
 
1072
1915
/************************************************************************/
 
1916
/*                            DetectSRSWktColumn()                            */
 
1917
/************************************************************************/
 
1918
 
 
1919
int OGRSQLiteDataSource::DetectSRSWktColumn()
 
1920
{
 
1921
    int bHasSrsWkt = FALSE;
 
1922
 
 
1923
/* testing for SRS_WKT column presence */
 
1924
    char **papszResult;
 
1925
    int nRowCount, nColCount;
 
1926
    char *pszErrMsg = NULL;
 
1927
    int rc = sqlite3_get_table( hDB, "PRAGMA table_info(spatial_ref_sys)",
 
1928
                            &papszResult, &nRowCount, &nColCount,
 
1929
                            &pszErrMsg );
 
1930
 
 
1931
    if( rc == SQLITE_OK )
 
1932
    {
 
1933
        int iRow;
 
1934
        for (iRow = 1; iRow <= nRowCount; iRow++)
 
1935
        {
 
1936
            if (EQUAL("srs_wkt",
 
1937
                        papszResult[(iRow * nColCount) + 1]))
 
1938
                bHasSrsWkt = TRUE;
 
1939
        }
 
1940
        sqlite3_free_table(papszResult);
 
1941
    }
 
1942
    else
 
1943
    {
 
1944
        sqlite3_free( pszErrMsg );
 
1945
    }
 
1946
 
 
1947
    return bHasSrsWkt;
 
1948
}
 
1949
 
 
1950
/************************************************************************/
1073
1951
/*                             FetchSRSId()                             */
1074
1952
/*                                                                      */
1075
1953
/*      Fetch the id corresponding to an SRS, and if not found, add     */
1188
2066
/*      Search for existing record using either WKT definition or       */
1189
2067
/*      PROJ.4 string (SpatiaLite variant).                             */
1190
2068
/* -------------------------------------------------------------------- */
1191
 
    CPLString   osSRS;
 
2069
    CPLString   osWKT, osProj4;
1192
2070
 
1193
 
    if ( !bIsSpatiaLite )
1194
 
    {
1195
2071
/* -------------------------------------------------------------------- */
1196
2072
/*      Translate SRS to WKT.                                           */
1197
2073
/* -------------------------------------------------------------------- */
1198
 
        char    *pszWKT = NULL;
1199
 
 
1200
 
        if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1201
 
            return -1;
1202
 
 
1203
 
        osSRS = pszWKT;
1204
 
        CPLFree( pszWKT );
1205
 
        pszWKT = NULL;
1206
 
 
 
2074
    char    *pszWKT = NULL;
 
2075
 
 
2076
    if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
 
2077
    {
 
2078
        CPLFree(pszWKT);
 
2079
        return -1;
 
2080
    }
 
2081
 
 
2082
    osWKT = pszWKT;
 
2083
    CPLFree( pszWKT );
 
2084
    pszWKT = NULL;
 
2085
 
 
2086
    if ( !bIsSpatiaLite )
 
2087
    {
1207
2088
/* -------------------------------------------------------------------- */
1208
2089
/*      Try to find based on the WKT match.                             */
1209
2090
/* -------------------------------------------------------------------- */
1210
 
        osCommand.Printf( "SELECT srid FROM spatial_ref_sys WHERE srtext = '%s'",
1211
 
                          osSRS.c_str());
1212
 
        
1213
 
        rc = sqlite3_get_table( hDB, osCommand, &papszResult,
1214
 
                                &nRowCount, &nColCount, &pszErrMsg );
1215
 
        if( rc != SQLITE_OK )
1216
 
        {
1217
 
            CPLError( CE_Failure, CPLE_AppDefined,
1218
 
                      "Search for existing SRS by WKT failed: %s", pszErrMsg );
1219
 
            sqlite3_free( pszErrMsg );
1220
 
        }
1221
 
        else if( nRowCount == 1 )
1222
 
        {
1223
 
            nSRSId = (papszResult[1] != NULL) ? atoi(papszResult[1]) : -1;
1224
 
            sqlite3_free_table(papszResult);
1225
 
            return nSRSId;
1226
 
        }
1227
 
        sqlite3_free_table(papszResult);
 
2091
        osCommand.Printf( "SELECT srid FROM spatial_ref_sys WHERE srtext = ?");
1228
2092
    }
1229
2093
 
1230
2094
/* -------------------------------------------------------------------- */
1238
2102
        char    *pszProj4 = NULL;
1239
2103
 
1240
2104
        if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
 
2105
        {
 
2106
            CPLFree(pszProj4);
1241
2107
            return -1;
 
2108
        }
1242
2109
 
1243
 
        osSRS = pszProj4;
 
2110
        osProj4 = pszProj4;
1244
2111
        CPLFree( pszProj4 );
1245
2112
        pszProj4 = NULL;
1246
2113
 
1247
2114
/* -------------------------------------------------------------------- */
1248
 
/*      Try to find based on the WKT match.                             */
 
2115
/*      Try to find based on the PROJ.4 match.                          */
1249
2116
/* -------------------------------------------------------------------- */
1250
2117
        osCommand.Printf(
1251
 
            "SELECT srid FROM spatial_ref_sys WHERE proj4text = '%s'",
1252
 
            osSRS.c_str());
1253
 
        
1254
 
        rc = sqlite3_get_table( hDB, osCommand, &papszResult,
1255
 
                                &nRowCount, &nColCount, &pszErrMsg );
1256
 
        if( rc != SQLITE_OK )
1257
 
        {
1258
 
            CPLError( CE_Failure, CPLE_AppDefined,
1259
 
                      "Search for existing SRS by PROJ.4 string failed: %s",
1260
 
                      pszErrMsg );
1261
 
            sqlite3_free( pszErrMsg );
1262
 
        }
1263
 
        else if( nRowCount == 1 )
1264
 
        {
1265
 
            nSRSId = (papszResult[1] != NULL) ? atoi(papszResult[1]) : -1;
1266
 
            sqlite3_free_table(papszResult);
1267
 
            return nSRSId;
1268
 
        }
1269
 
        sqlite3_free_table(papszResult);
 
2118
            "SELECT srid FROM spatial_ref_sys WHERE proj4text = ?");
 
2119
    }
 
2120
 
 
2121
    sqlite3_stmt *hSelectStmt = NULL;
 
2122
    rc = sqlite3_prepare( hDB, osCommand, -1, &hSelectStmt, NULL );
 
2123
 
 
2124
    if( rc == SQLITE_OK)
 
2125
        rc = sqlite3_bind_text( hSelectStmt, 1, ( !bIsSpatiaLite ) ? osWKT.c_str() : osProj4.c_str(), -1, SQLITE_STATIC );
 
2126
 
 
2127
    if( rc == SQLITE_OK)
 
2128
        rc = sqlite3_step( hSelectStmt );
 
2129
 
 
2130
    if (rc == SQLITE_ROW)
 
2131
    {
 
2132
        if (sqlite3_column_type( hSelectStmt, 0 ) == SQLITE_INTEGER)
 
2133
            nSRSId = sqlite3_column_int( hSelectStmt, 0 );
 
2134
        else
 
2135
            nSRSId = -1;
 
2136
 
 
2137
        sqlite3_finalize( hSelectStmt );
 
2138
        return nSRSId;
1270
2139
    }
1271
2140
 
1272
2141
/* -------------------------------------------------------------------- */
1273
2142
/*      If the command actually failed, then the metadata table is      */
1274
2143
/*      likely missing, so we give up.                                  */
1275
2144
/* -------------------------------------------------------------------- */
1276
 
    if( rc != SQLITE_OK )
 
2145
    if (rc != SQLITE_DONE && rc != SQLITE_ROW)
 
2146
    {
 
2147
        sqlite3_finalize( hSelectStmt );
1277
2148
        return -1;
 
2149
    }
 
2150
 
 
2151
    sqlite3_finalize( hSelectStmt );
1278
2152
 
1279
2153
/* -------------------------------------------------------------------- */
1280
2154
/*      If we have an authority code try to assign SRS ID the same      */
1330
2204
/* -------------------------------------------------------------------- */
1331
2205
/*      Try adding the SRS to the SRS table.                            */
1332
2206
/* -------------------------------------------------------------------- */
 
2207
 
 
2208
    const char* apszToInsert[] = { NULL, NULL, NULL, NULL, NULL, NULL };
 
2209
 
1333
2210
    if ( !bIsSpatiaLite )
1334
2211
    {
1335
2212
        if( pszAuthorityName != NULL )
1336
2213
        {
1337
2214
            osCommand.Printf(
1338
2215
                "INSERT INTO spatial_ref_sys (srid,srtext,auth_name,auth_srid) "
1339
 
                "                     VALUES (%d, '%s', '%s', '%s')",
1340
 
                nSRSId, osSRS.c_str(), 
1341
 
                pszAuthorityName, pszAuthorityCode );
 
2216
                "                     VALUES (%d, ?, ?, ?)",
 
2217
                nSRSId );
 
2218
            apszToInsert[0] = osWKT.c_str();
 
2219
            apszToInsert[1] = pszAuthorityName;
 
2220
            apszToInsert[2] = pszAuthorityCode;
1342
2221
        }
1343
2222
        else
1344
2223
        {
1345
2224
            osCommand.Printf(
1346
2225
                "INSERT INTO spatial_ref_sys (srid,srtext) "
1347
 
                "                     VALUES (%d, '%s')",
1348
 
                nSRSId, osSRS.c_str() );
 
2226
                "                     VALUES (%d, ?)",
 
2227
                nSRSId );
 
2228
            apszToInsert[0] = osWKT.c_str();
1349
2229
        }
1350
2230
    }
1351
2231
    else
1352
2232
    {
 
2233
        int bHasSrsWkt = DetectSRSWktColumn();
 
2234
 
1353
2235
        const char  *pszProjCS = oSRS.GetAttrValue("PROJCS");
1354
2236
        if (pszProjCS == NULL)
1355
2237
            pszProjCS = oSRS.GetAttrValue("GEOGCS");
1357
2239
        if( pszAuthorityName != NULL )
1358
2240
        {
1359
2241
            if ( pszProjCS )
 
2242
            {
1360
2243
                osCommand.Printf(
1361
2244
                    "INSERT INTO spatial_ref_sys "
1362
 
                    "(srid, auth_name, auth_srid, ref_sys_name, proj4text) "
1363
 
                    "VALUES (%d, '%s', '%s', '%s', '%s')",
1364
 
                    nSRSId, pszAuthorityName,
1365
 
                    pszAuthorityCode, pszProjCS, osSRS.c_str() );
 
2245
                    "(srid, auth_name, auth_srid, ref_sys_name, proj4text%s) "
 
2246
                    "VALUES (%d, ?, ?, ?, ?%s)",
 
2247
                    bHasSrsWkt ? ", srs_wkt" : "",
 
2248
                    nSRSId,
 
2249
                    bHasSrsWkt ? ", ?" : "");
 
2250
                apszToInsert[0] = pszAuthorityName;
 
2251
                apszToInsert[1] = pszAuthorityCode;
 
2252
                apszToInsert[2] = pszProjCS;
 
2253
                apszToInsert[3] = osProj4.c_str();
 
2254
                apszToInsert[4] = bHasSrsWkt ? osWKT.c_str() : NULL;
 
2255
            }
1366
2256
            else
 
2257
            {
1367
2258
                osCommand.Printf(
1368
2259
                    "INSERT INTO spatial_ref_sys "
1369
 
                    "(srid, auth_name, auth_srid, proj4text) "
1370
 
                    "VALUES (%d, '%s', '%s', '%s')",
1371
 
                    nSRSId, pszAuthorityName,
1372
 
                    pszAuthorityCode, osSRS.c_str() );
 
2260
                    "(srid, auth_name, auth_srid, proj4text%s) "
 
2261
                    "VALUES (%d, ?, ?, ?%s)",
 
2262
                    bHasSrsWkt ? ", srs_wkt" : "",
 
2263
                    nSRSId,
 
2264
                    bHasSrsWkt ? ", ?" : "");
 
2265
                apszToInsert[0] = pszAuthorityName;
 
2266
                apszToInsert[1] = pszAuthorityCode;
 
2267
                apszToInsert[2] = osProj4.c_str();
 
2268
                apszToInsert[3] = bHasSrsWkt ? osWKT.c_str() : NULL;
 
2269
            }
1373
2270
        }
1374
2271
        else
1375
2272
        {
1376
2273
            /* SpatiaLite spatial_ref_sys auth_name and auth_srid columns must be NOT NULL */
1377
2274
            /* so insert within a fake OGR "authority" */
1378
2275
            if ( pszProjCS )
 
2276
            {
1379
2277
                osCommand.Printf(
1380
2278
                    "INSERT INTO spatial_ref_sys "
1381
 
                    "(srid, auth_name, auth_srid, ref_sys_name, proj4text) VALUES (%d, '%s', %d, '%s', '%s')",
1382
 
                    nSRSId, "OGR", nSRSId, pszProjCS, osSRS.c_str() );
 
2279
                    "(srid, auth_name, auth_srid, ref_sys_name, proj4text%s) VALUES (%d, 'OGR', %d, ?, ?%s)",
 
2280
                    bHasSrsWkt ? ", srs_wkt" : "",
 
2281
                    nSRSId, nSRSId,
 
2282
                    bHasSrsWkt ? ", ?" : "");
 
2283
                apszToInsert[0] = pszProjCS;
 
2284
                apszToInsert[1] = osProj4.c_str();
 
2285
                apszToInsert[2] = bHasSrsWkt ? osWKT.c_str() : NULL;
 
2286
            }
1383
2287
            else
 
2288
            {
1384
2289
                osCommand.Printf(
1385
2290
                    "INSERT INTO spatial_ref_sys "
1386
 
                    "(srid, auth_name, auth_srid, proj4text) VALUES (%d, '%s', %d, '%s')",
1387
 
                    nSRSId, "OGR", nSRSId, osSRS.c_str() );
 
2291
                    "(srid, auth_name, auth_srid, proj4text%s) VALUES (%d, 'OGR', %d, ?%s)",
 
2292
                    bHasSrsWkt ? ", srs_wkt" : "",
 
2293
                    nSRSId, nSRSId,
 
2294
                    bHasSrsWkt ? ", ?" : "");
 
2295
                apszToInsert[0] = osProj4.c_str();
 
2296
                apszToInsert[1] = bHasSrsWkt ? osWKT.c_str() : NULL;
 
2297
            }
1388
2298
        }
1389
2299
    }
1390
2300
 
1391
 
    rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
1392
 
    if( rc != SQLITE_OK )
 
2301
    sqlite3_stmt *hInsertStmt = NULL;
 
2302
    rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
 
2303
 
 
2304
    int i;
 
2305
    for(i=0;apszToInsert[i]!=NULL;i++)
 
2306
    {
 
2307
        if( rc == SQLITE_OK)
 
2308
            rc = sqlite3_bind_text( hInsertStmt, i+1, apszToInsert[i], -1, SQLITE_STATIC );
 
2309
    }
 
2310
 
 
2311
    if( rc == SQLITE_OK)
 
2312
        rc = sqlite3_step( hInsertStmt );
 
2313
 
 
2314
    if( rc != SQLITE_OK && rc != SQLITE_DONE )
1393
2315
    {
1394
2316
        CPLError( CE_Failure, CPLE_AppDefined, 
1395
2317
                  "Unable to insert SRID (%s): %s",
1396
 
                  osCommand.c_str(), pszErrMsg );
1397
 
        sqlite3_free( pszErrMsg );
 
2318
                  osCommand.c_str(), sqlite3_errmsg(hDB) );
 
2319
 
 
2320
        sqlite3_finalize( hInsertStmt );
1398
2321
        return FALSE;
1399
2322
    }
1400
2323
 
 
2324
    sqlite3_finalize( hInsertStmt );
 
2325
 
1401
2326
    return nSRSId;
1402
2327
}
1403
2328
 
1472
2397
 
1473
2398
/* -------------------------------------------------------------------- */
1474
2399
/*      Next try SpatiaLite flavour. SpatiaLite uses PROJ.4 strings     */
1475
 
/*      in 'proj4text' column instead of WKT in 'srtext'.               */
 
2400
/*      in 'proj4text' column instead of WKT in 'srtext'. Note: recent  */
 
2401
/*      versions of spatialite have a srs_wkt column too                */
1476
2402
/* -------------------------------------------------------------------- */
1477
2403
    else
1478
2404
    {
1479
2405
        sqlite3_free( pszErrMsg );
1480
2406
        pszErrMsg = NULL;
1481
2407
 
 
2408
        int bHasSrsWkt = DetectSRSWktColumn();
 
2409
 
1482
2410
        osCommand.Printf(
1483
 
            "SELECT proj4text, auth_name, auth_srid FROM spatial_ref_sys WHERE srid = %d", nId );
 
2411
            "SELECT proj4text, auth_name, auth_srid%s FROM spatial_ref_sys WHERE srid = %d",
 
2412
            bHasSrsWkt ? ", srs_wkt" : "", nId );
1484
2413
        rc = sqlite3_get_table( hDB, osCommand, 
1485
2414
                                &papszResult, &nRowCount,
1486
2415
                                &nColCount, &pszErrMsg );
1498
2427
            char** papszRow = papszResult + nColCount;
1499
2428
 
1500
2429
            const char* pszProj4Text = papszRow[0];
1501
 
            if (pszProj4Text != NULL)
1502
 
            {
1503
 
                const char* pszAuthName = papszRow[1];
1504
 
                int nAuthSRID = (papszRow[2] != NULL) ? atoi(papszRow[2]) : 0;
1505
 
 
1506
 
                poSRS = new OGRSpatialReference();
1507
 
 
1508
 
                /* Try first from EPSG code */
1509
 
                if (pszAuthName != NULL &&
1510
 
                    EQUAL(pszAuthName, "EPSG") &&
1511
 
                    poSRS->importFromEPSG( nAuthSRID ) == OGRERR_NONE)
1512
 
                {
1513
 
                    /* Do nothing */
1514
 
                }
1515
 
                /* Then from Proj4 string */
1516
 
                else if( poSRS->importFromProj4( pszProj4Text ) != OGRERR_NONE )
1517
 
                {
1518
 
                    delete poSRS;
1519
 
                    poSRS = NULL;
1520
 
                }
 
2430
            const char* pszAuthName = papszRow[1];
 
2431
            int nAuthSRID = (papszRow[2] != NULL) ? atoi(papszRow[2]) : 0;
 
2432
            char* pszWKT = (bHasSrsWkt) ? (char*) papszRow[3] : NULL;
 
2433
 
 
2434
            poSRS = new OGRSpatialReference();
 
2435
 
 
2436
            /* Try first from EPSG code */
 
2437
            if (pszAuthName != NULL &&
 
2438
                EQUAL(pszAuthName, "EPSG") &&
 
2439
                poSRS->importFromEPSG( nAuthSRID ) == OGRERR_NONE)
 
2440
            {
 
2441
                /* Do nothing */
 
2442
            }
 
2443
            /* Then from WKT string */
 
2444
            else if( pszWKT != NULL &&
 
2445
                     poSRS->importFromWkt( &pszWKT ) == OGRERR_NONE )
 
2446
            {
 
2447
                /* Do nothing */
 
2448
            }
 
2449
            /* Finally from Proj4 string */
 
2450
            else if( pszProj4Text != NULL &&
 
2451
                     poSRS->importFromProj4( pszProj4Text ) == OGRERR_NONE )
 
2452
            {
 
2453
                /* Do nothing */
 
2454
            }
 
2455
            else
 
2456
            {
 
2457
                delete poSRS;
 
2458
                poSRS = NULL;
1521
2459
            }
1522
2460
 
1523
2461
            sqlite3_free_table(papszResult);
1547
2485
 
1548
2486
    return poSRS;
1549
2487
}
 
2488
 
 
2489
/************************************************************************/
 
2490
/*                              SetName()                               */
 
2491
/************************************************************************/
 
2492
 
 
2493
void OGRSQLiteDataSource::SetName(const char* pszNameIn)
 
2494
{
 
2495
    CPLFree(pszName);
 
2496
    pszName = CPLStrdup(pszNameIn);
 
2497
}