1 : /******************************************************************************
2 : * $Id: ogrsqlitedriver.cpp 19239 2010-03-28 17:26:06Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRSQLiteDriver class.
6 : * Author: Frank Warmerdam, warmerdam@pobox.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2004, Frank Warmerdam <warmerdam@pobox.com>
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 "ogr_sqlite.h"
31 : #include "cpl_conv.h"
32 : #include "cpl_csv.h"
33 :
34 : CPL_CVSID("$Id: ogrsqlitedriver.cpp 19239 2010-03-28 17:26:06Z rouault $");
35 :
36 : /************************************************************************/
37 : /* ~OGRSQLiteDriver() */
38 : /************************************************************************/
39 :
40 72 : OGRSQLiteDriver::~OGRSQLiteDriver()
41 :
42 : {
43 72 : }
44 :
45 : /************************************************************************/
46 : /* GetName() */
47 : /************************************************************************/
48 :
49 3327 : const char *OGRSQLiteDriver::GetName()
50 :
51 : {
52 3327 : return "SQLite";
53 : }
54 :
55 : /************************************************************************/
56 : /* Open() */
57 : /************************************************************************/
58 :
59 : OGRDataSource *OGRSQLiteDriver::Open( const char * pszFilename,
60 202 : int bUpdate )
61 :
62 : {
63 : /* -------------------------------------------------------------------- */
64 : /* Verify that the target is a real file, and has an */
65 : /* appropriate magic string at the beginning. */
66 : /* -------------------------------------------------------------------- */
67 : FILE *fpDB;
68 : char szHeader[16];
69 :
70 202 : fpDB = VSIFOpen( pszFilename, "rb" );
71 202 : if( fpDB == NULL )
72 69 : return NULL;
73 :
74 133 : if( VSIFRead( szHeader, 1, 16, fpDB ) != 16 )
75 2 : memset( szHeader, 0, 16 );
76 :
77 133 : VSIFClose( fpDB );
78 :
79 133 : if( strncmp( szHeader, "SQLite format 3", 15 ) != 0 )
80 44 : return NULL;
81 :
82 : /* -------------------------------------------------------------------- */
83 : /* We think this is really an SQLite database, go ahead and try */
84 : /* and open it. */
85 : /* -------------------------------------------------------------------- */
86 : OGRSQLiteDataSource *poDS;
87 :
88 89 : poDS = new OGRSQLiteDataSource();
89 :
90 89 : if( !poDS->Open( pszFilename ) )
91 : {
92 0 : delete poDS;
93 0 : return NULL;
94 : }
95 : else
96 89 : return poDS;
97 : }
98 :
99 : /************************************************************************/
100 : /* CreateDataSource() */
101 : /************************************************************************/
102 :
103 : OGRDataSource *OGRSQLiteDriver::CreateDataSource( const char * pszName,
104 24 : char **papszOptions )
105 :
106 : {
107 : /* -------------------------------------------------------------------- */
108 : /* First, ensure there isn't any such file yet. */
109 : /* -------------------------------------------------------------------- */
110 : VSIStatBuf sStatBuf;
111 :
112 24 : if( VSIStat( pszName, &sStatBuf ) == 0 )
113 : {
114 : CPLError( CE_Failure, CPLE_AppDefined,
115 : "It seems a file system object called '%s' already exists.",
116 0 : pszName );
117 :
118 0 : return NULL;
119 : }
120 :
121 : /* -------------------------------------------------------------------- */
122 : /* Create the database file. */
123 : /* -------------------------------------------------------------------- */
124 : sqlite3 *hDB;
125 : int rc;
126 :
127 24 : hDB = NULL;
128 24 : rc = sqlite3_open( pszName, &hDB );
129 24 : if( rc != SQLITE_OK )
130 : {
131 : CPLError( CE_Failure, CPLE_OpenFailed,
132 : "sqlite3_open(%s) failed: %s",
133 0 : pszName, sqlite3_errmsg( hDB ) );
134 0 : return NULL;
135 : }
136 :
137 24 : int bSpatialite = CSLFetchBoolean( papszOptions, "SPATIALITE", FALSE );
138 24 : int bMetadata = CSLFetchBoolean( papszOptions, "METADATA", TRUE );
139 :
140 24 : CPLString osCommand;
141 24 : char *pszErrMsg = NULL;
142 :
143 : /* -------------------------------------------------------------------- */
144 : /* Create the SpatiaLite metadata tables. */
145 : /* -------------------------------------------------------------------- */
146 24 : if ( bSpatialite )
147 : {
148 : osCommand =
149 : "CREATE TABLE geometry_columns ("
150 : " f_table_name VARCHAR NOT NULL, "
151 : " f_geometry_column VARCHAR NOT NULL, "
152 : " type VARCHAR NOT NULL, "
153 : " coord_dimension INTEGER NOT NULL, "
154 : " srid INTEGER,"
155 21 : " spatial_index_enabled INTEGER NOT NULL)";
156 21 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
157 21 : if( rc != SQLITE_OK )
158 : {
159 : CPLError( CE_Failure, CPLE_AppDefined,
160 : "Unable to create table geometry_columns: %s",
161 0 : pszErrMsg );
162 0 : sqlite3_free( pszErrMsg );
163 0 : return NULL;
164 : }
165 :
166 : osCommand =
167 : "CREATE TABLE spatial_ref_sys ("
168 : " srid INTEGER NOT NULL PRIMARY KEY,"
169 : " auth_name VARCHAR NOT NULL,"
170 : " auth_srid INTEGER NOT NULL,"
171 : " ref_sys_name VARCHAR,"
172 21 : " proj4text VARCHAR NOT NULL)";
173 21 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
174 21 : if( rc != SQLITE_OK )
175 : {
176 : CPLError( CE_Failure, CPLE_AppDefined,
177 : "Unable to create table spatial_ref_sys: %s",
178 0 : pszErrMsg );
179 0 : sqlite3_free( pszErrMsg );
180 0 : return NULL;
181 : }
182 :
183 : osCommand =
184 : "CREATE VIEW geom_cols_ref_sys AS"
185 : " SELECT f_table_name, f_geometry_column, type,"
186 : " coord_dimension, spatial_ref_sys.srid AS srid,"
187 : " auth_name, auth_srid, ref_sys_name, proj4text"
188 : " FROM geometry_columns, spatial_ref_sys"
189 21 : " WHERE geometry_columns.srid = spatial_ref_sys.srid";
190 21 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
191 21 : if( rc != SQLITE_OK )
192 : {
193 : CPLError( CE_Failure, CPLE_AppDefined,
194 : "Unable to create view geom_cols_ref_sys: %s",
195 0 : pszErrMsg );
196 0 : sqlite3_free( pszErrMsg );
197 0 : return NULL;
198 : }
199 : }
200 :
201 : /* -------------------------------------------------------------------- */
202 : /* Create the geometry_columns and spatial_ref_sys metadata tables. */
203 : /* -------------------------------------------------------------------- */
204 3 : else if( bMetadata )
205 : {
206 : osCommand =
207 : "CREATE TABLE geometry_columns ("
208 : " f_table_name VARCHAR, "
209 : " f_geometry_column VARCHAR, "
210 : " geometry_type INTEGER, "
211 : " coord_dimension INTEGER, "
212 : " srid INTEGER,"
213 3 : " geometry_format VARCHAR )";
214 3 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
215 3 : if( rc != SQLITE_OK )
216 : {
217 : CPLError( CE_Failure, CPLE_AppDefined,
218 : "Unable to create table geometry_columns: %s",
219 0 : pszErrMsg );
220 0 : sqlite3_free( pszErrMsg );
221 0 : return NULL;
222 : }
223 :
224 : osCommand =
225 : "CREATE TABLE spatial_ref_sys ("
226 : " srid INTEGER UNIQUE,"
227 : " auth_name TEXT,"
228 : " auth_srid TEXT,"
229 3 : " srtext TEXT)";
230 3 : rc = sqlite3_exec( hDB, osCommand, NULL, NULL, &pszErrMsg );
231 3 : if( rc != SQLITE_OK )
232 : {
233 : CPLError( CE_Failure, CPLE_AppDefined,
234 : "Unable to create table spatial_ref_sys: %s",
235 0 : pszErrMsg );
236 0 : sqlite3_free( pszErrMsg );
237 24 : return NULL;
238 : }
239 : }
240 : else
241 : {
242 : /* -------------------------------------------------------------------- */
243 : /* Close the DB file so we can reopen it normally. */
244 : /* -------------------------------------------------------------------- */
245 0 : sqlite3_close( hDB );
246 :
247 : OGRSQLiteDataSource *poDS;
248 0 : poDS = new OGRSQLiteDataSource();
249 :
250 0 : if( !poDS->Open( pszName ) )
251 : {
252 0 : delete poDS;
253 0 : return NULL;
254 : }
255 : else
256 0 : return poDS;
257 : }
258 :
259 : /* -------------------------------------------------------------------- */
260 : /* Optionnaly initialize the content of the spatial_ref_sys table */
261 : /* with the EPSG database */
262 : /* -------------------------------------------------------------------- */
263 24 : if ( (bSpatialite || bMetadata) &&
264 : CSLFetchBoolean( papszOptions, "INIT_WITH_EPSG", FALSE ) )
265 : {
266 2 : InitWithEPSG(hDB, bSpatialite);
267 : }
268 :
269 : /* -------------------------------------------------------------------- */
270 : /* Close the DB file so we can reopen it normally. */
271 : /* -------------------------------------------------------------------- */
272 24 : sqlite3_close( hDB );
273 :
274 24 : return Open( pszName, TRUE );
275 : }
276 :
277 : /************************************************************************/
278 : /* InitWithEPSG() */
279 : /************************************************************************/
280 :
281 2 : int OGRSQLiteDriver::InitWithEPSG(sqlite3* hDB, int bSpatialite)
282 : {
283 2 : CPLString osCommand;
284 2 : char* pszErrMsg = NULL;
285 :
286 2 : int rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, &pszErrMsg );
287 2 : if( rc != SQLITE_OK )
288 : {
289 : CPLError( CE_Failure, CPLE_AppDefined,
290 : "Unable to insert into spatial_ref_sys: %s",
291 0 : pszErrMsg );
292 0 : sqlite3_free( pszErrMsg );
293 0 : return FALSE;
294 : }
295 :
296 : FILE* fp;
297 : int i;
298 6 : for(i=0;i<2 && rc == SQLITE_OK;i++)
299 : {
300 4 : const char* pszFilename = (i == 0) ? "gcs.csv" : "pcs.csv";
301 4 : fp = VSIFOpen(CSVFilename(pszFilename), "rt");
302 4 : if (fp == NULL)
303 : {
304 : CPLError( CE_Failure, CPLE_OpenFailed,
305 : "Unable to open EPSG support file %s.\n"
306 : "Try setting the GDAL_DATA environment variable to point to the\n"
307 : "directory containing EPSG csv files.",
308 0 : pszFilename );
309 :
310 0 : continue;
311 : }
312 :
313 4 : OGRSpatialReference oSRS;
314 : char** papszTokens;
315 4 : CSLDestroy(CSVReadParseLine( fp ));
316 7898 : while ( (papszTokens = CSVReadParseLine( fp )) != NULL && rc == SQLITE_OK)
317 : {
318 7890 : int nSRSId = atoi(papszTokens[0]);
319 7890 : CSLDestroy(papszTokens);
320 :
321 7890 : CPLPushErrorHandler(CPLQuietErrorHandler);
322 7890 : oSRS.importFromEPSG(nSRSId);
323 7890 : CPLPopErrorHandler();
324 :
325 7890 : if (bSpatialite)
326 : {
327 3945 : char *pszProj4 = NULL;
328 :
329 3945 : CPLPushErrorHandler(CPLQuietErrorHandler);
330 3945 : OGRErr eErr = oSRS.exportToProj4( &pszProj4 );
331 3945 : CPLPopErrorHandler();
332 :
333 3945 : if( eErr == OGRERR_NONE )
334 : {
335 3915 : const char *pszProjCS = oSRS.GetAttrValue("PROJCS");
336 3915 : if (pszProjCS == NULL)
337 438 : pszProjCS = oSRS.GetAttrValue("GEOGCS");
338 :
339 3915 : if ( pszProjCS )
340 : osCommand.Printf(
341 : "INSERT INTO spatial_ref_sys "
342 : "(srid, auth_name, auth_srid, ref_sys_name, proj4text) "
343 : "VALUES (%d, 'EPSG', '%d', ?, ?)",
344 3915 : nSRSId, nSRSId);
345 : else
346 : osCommand.Printf(
347 : "INSERT INTO spatial_ref_sys "
348 : "(srid, auth_name, auth_srid, proj4text) "
349 : "VALUES (%d, 'EPSG', '%d', ?)",
350 0 : nSRSId, nSRSId);
351 :
352 3915 : sqlite3_stmt *hInsertStmt = NULL;
353 3915 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
354 :
355 3915 : if ( pszProjCS )
356 : {
357 3915 : if( rc == SQLITE_OK)
358 3915 : rc = sqlite3_bind_text( hInsertStmt, 1, pszProjCS, -1, SQLITE_STATIC );
359 3915 : if( rc == SQLITE_OK)
360 3915 : rc = sqlite3_bind_text( hInsertStmt, 2, pszProj4, -1, SQLITE_STATIC );
361 : }
362 : else
363 : {
364 0 : if( rc == SQLITE_OK)
365 0 : rc = sqlite3_bind_text( hInsertStmt, 1, pszProj4, -1, SQLITE_STATIC );
366 : }
367 :
368 3915 : if( rc == SQLITE_OK)
369 3915 : rc = sqlite3_step( hInsertStmt );
370 :
371 3915 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
372 : {
373 : CPLError( CE_Failure, CPLE_AppDefined,
374 : "Cannot insert %s into spatial_ref_sys : %s",
375 : pszProj4,
376 0 : sqlite3_errmsg(hDB) );
377 :
378 0 : sqlite3_finalize( hInsertStmt );
379 0 : CPLFree(pszProj4);
380 0 : break;
381 : }
382 3915 : rc = SQLITE_OK;
383 :
384 3915 : sqlite3_finalize( hInsertStmt );
385 : }
386 :
387 3945 : CPLFree(pszProj4);
388 : }
389 : else
390 : {
391 3945 : char *pszWKT = NULL;
392 3945 : if( oSRS.exportToWkt( &pszWKT ) == OGRERR_NONE )
393 : {
394 : osCommand.Printf(
395 : "INSERT INTO spatial_ref_sys "
396 : "(srid, auth_name, auth_srid, srtext) "
397 : "VALUES (%d, 'EPSG', '%d', ?)",
398 3945 : nSRSId, nSRSId );
399 :
400 3945 : sqlite3_stmt *hInsertStmt = NULL;
401 3945 : rc = sqlite3_prepare( hDB, osCommand, -1, &hInsertStmt, NULL );
402 :
403 3945 : if( rc == SQLITE_OK)
404 3945 : rc = sqlite3_bind_text( hInsertStmt, 1, pszWKT, -1, SQLITE_STATIC );
405 :
406 3945 : if( rc == SQLITE_OK)
407 3945 : rc = sqlite3_step( hInsertStmt );
408 :
409 3945 : if( rc != SQLITE_OK && rc != SQLITE_DONE )
410 : {
411 : CPLError( CE_Failure, CPLE_AppDefined,
412 : "Cannot insert %s into spatial_ref_sys : %s",
413 : pszWKT,
414 0 : sqlite3_errmsg(hDB) );
415 :
416 0 : sqlite3_finalize( hInsertStmt );
417 0 : CPLFree(pszWKT);
418 0 : break;
419 : }
420 3945 : rc = SQLITE_OK;
421 :
422 3945 : sqlite3_finalize( hInsertStmt );
423 : }
424 :
425 3945 : CPLFree(pszWKT);
426 : }
427 : }
428 4 : VSIFClose(fp);
429 : }
430 :
431 2 : if (rc == SQLITE_OK)
432 2 : rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, &pszErrMsg );
433 : else
434 0 : rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, &pszErrMsg );
435 :
436 2 : if( rc != SQLITE_OK )
437 : {
438 : CPLError( CE_Failure, CPLE_AppDefined,
439 : "Unable to insert into spatial_ref_sys: %s",
440 0 : pszErrMsg );
441 0 : sqlite3_free( pszErrMsg );
442 : }
443 :
444 2 : return (rc == SQLITE_OK);
445 : }
446 :
447 : /************************************************************************/
448 : /* TestCapability() */
449 : /************************************************************************/
450 :
451 0 : int OGRSQLiteDriver::TestCapability( const char * pszCap )
452 :
453 : {
454 0 : if( EQUAL(pszCap,ODrCCreateDataSource) )
455 0 : return TRUE;
456 : else
457 0 : return FALSE;
458 : }
459 :
460 : /************************************************************************/
461 : /* RegisterOGRSQLite() */
462 : /************************************************************************/
463 :
464 80 : void RegisterOGRSQLite()
465 :
466 : {
467 80 : if (! GDAL_CHECK_VERSION("SQLite driver"))
468 0 : return;
469 80 : OGRSFDriverRegistrar::GetRegistrar()->RegisterDriver( new OGRSQLiteDriver );
470 : }
471 :
|
