1 : /******************************************************************************
2 : * $Id: ogrsqliteexecutesql.cpp 24969 2012-09-24 22:25:50Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Run SQL requests with SQLite SQL engine
6 : * Author: Even Rouault, even dot rouault at mines dash paris dot org
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2012, Even Rouault <even dot rouault at mines dash paris dot org>
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 "ogr_api.h"
32 : #include "ogrsqlitevirtualogr.h"
33 : #include "ogrsqliteexecutesql.h"
34 : #include "cpl_multiproc.h"
35 :
36 : #ifdef HAVE_SQLITE_VFS
37 :
38 : /************************************************************************/
39 : /* OGRSQLiteExecuteSQLLayer */
40 : /************************************************************************/
41 :
42 : class OGRSQLiteExecuteSQLLayer: public OGRSQLiteSelectLayer
43 : {
44 : OGR2SQLITEModule *poModule;
45 : char *pszTmpDBName;
46 :
47 : public:
48 : OGRSQLiteExecuteSQLLayer(OGR2SQLITEModule* poModule,
49 : char* pszTmpDBName,
50 : OGRSQLiteDataSource* poDS,
51 : CPLString osSQL,
52 : sqlite3_stmt * hStmt,
53 : int bUseStatementForGetNextFeature,
54 : int bEmptyLayer );
55 : virtual ~OGRSQLiteExecuteSQLLayer();
56 : };
57 :
58 : /************************************************************************/
59 : /* OGRSQLiteExecuteSQLLayer() */
60 : /************************************************************************/
61 :
62 197 : OGRSQLiteExecuteSQLLayer::OGRSQLiteExecuteSQLLayer(OGR2SQLITEModule* poModule,
63 : char* pszTmpDBName,
64 : OGRSQLiteDataSource* poDS,
65 : CPLString osSQL,
66 : sqlite3_stmt * hStmt,
67 : int bUseStatementForGetNextFeature,
68 : int bEmptyLayer ) :
69 :
70 : OGRSQLiteSelectLayer(poDS, osSQL, hStmt,
71 : bUseStatementForGetNextFeature,
72 197 : bEmptyLayer)
73 : {
74 197 : this->poModule = poModule;
75 197 : this->pszTmpDBName = pszTmpDBName;
76 197 : }
77 :
78 : /************************************************************************/
79 : /* ~OGRSQLiteExecuteSQLLayer() */
80 : /************************************************************************/
81 :
82 197 : OGRSQLiteExecuteSQLLayer::~OGRSQLiteExecuteSQLLayer()
83 : {
84 : /* This is a bit peculiar: we must "finalize" the OGRLayer, since */
85 : /* it has objects that depend on the datasource, that we are just */
86 : /* going to destroy afterwards. The issue here is that we destroy */
87 : /* our own datasource ! */
88 197 : Finalize();
89 :
90 197 : delete poDS;
91 197 : delete poModule;
92 197 : VSIUnlink(pszTmpDBName);
93 197 : CPLFree(pszTmpDBName);
94 197 : }
95 :
96 : /************************************************************************/
97 : /* OGR2SQLITEExtractUnquotedString() */
98 : /************************************************************************/
99 :
100 244 : static CPLString OGR2SQLITEExtractUnquotedString(const char **ppszSQLCommand)
101 : {
102 244 : CPLString osRet;
103 244 : const char *pszSQLCommand = *ppszSQLCommand;
104 244 : char chQuoteChar = 0;
105 :
106 244 : if( *pszSQLCommand == '"' || *pszSQLCommand == '\'' )
107 : {
108 17 : chQuoteChar = *pszSQLCommand;
109 17 : pszSQLCommand ++;
110 : }
111 :
112 2406 : while( *pszSQLCommand != '\0' )
113 : {
114 2158 : if( *pszSQLCommand == chQuoteChar &&
115 17 : pszSQLCommand[1] == chQuoteChar )
116 : {
117 0 : pszSQLCommand ++;
118 0 : osRet += chQuoteChar;
119 : }
120 2141 : else if( *pszSQLCommand == chQuoteChar )
121 : {
122 17 : pszSQLCommand ++;
123 17 : break;
124 : }
125 2124 : else if( chQuoteChar == '\0' &&
126 : (isspace((int)*pszSQLCommand) ||
127 : *pszSQLCommand == '.' ||
128 : *pszSQLCommand == ')' ||
129 : *pszSQLCommand == ',') )
130 206 : break;
131 : else
132 1918 : osRet += *pszSQLCommand;
133 :
134 1918 : pszSQLCommand ++;
135 : }
136 :
137 244 : *ppszSQLCommand = pszSQLCommand;
138 :
139 0 : return osRet;
140 : }
141 :
142 : /************************************************************************/
143 : /* OGR2SQLITEExtractLayerDesc() */
144 : /************************************************************************/
145 :
146 : static
147 230 : LayerDesc OGR2SQLITEExtractLayerDesc(const char **ppszSQLCommand)
148 : {
149 230 : CPLString osStr;
150 230 : const char *pszSQLCommand = *ppszSQLCommand;
151 230 : LayerDesc oLayerDesc;
152 :
153 460 : while( isspace((int)*pszSQLCommand) )
154 0 : pszSQLCommand ++;
155 :
156 230 : const char* pszOriginalStrStart = pszSQLCommand;
157 230 : oLayerDesc.osOriginalStr = pszSQLCommand;
158 :
159 230 : osStr = OGR2SQLITEExtractUnquotedString(&pszSQLCommand);
160 :
161 230 : if( *pszSQLCommand == '.' )
162 : {
163 6 : oLayerDesc.osDSName = osStr;
164 6 : pszSQLCommand ++;
165 : oLayerDesc.osLayerName =
166 6 : OGR2SQLITEExtractUnquotedString(&pszSQLCommand);
167 : }
168 : else
169 : {
170 224 : oLayerDesc.osLayerName = osStr;
171 : }
172 :
173 230 : oLayerDesc.osOriginalStr.resize(pszSQLCommand - pszOriginalStrStart);
174 :
175 230 : *ppszSQLCommand = pszSQLCommand;
176 :
177 230 : return oLayerDesc;
178 : }
179 :
180 : /************************************************************************/
181 : /* OGR2SQLITEAddLayer() */
182 : /************************************************************************/
183 :
184 230 : static void OGR2SQLITEAddLayer( const char*& pszStart, int& nNum,
185 : const char*& pszSQLCommand,
186 : std::set<LayerDesc>& oSet,
187 : CPLString& osModifiedSQL )
188 : {
189 230 : CPLString osTruncated(pszStart);
190 230 : osTruncated.resize(pszSQLCommand - pszStart);
191 230 : osModifiedSQL += osTruncated;
192 230 : pszStart = pszSQLCommand;
193 230 : LayerDesc oLayerDesc = OGR2SQLITEExtractLayerDesc(&pszSQLCommand);
194 230 : int bInsert = TRUE;
195 230 : if( oLayerDesc.osDSName.size() == 0 )
196 : {
197 224 : osTruncated = pszStart;
198 224 : osTruncated.resize(pszSQLCommand - pszStart);
199 224 : osModifiedSQL += osTruncated;
200 : }
201 : else
202 : {
203 6 : std::set<LayerDesc>::iterator oIter = oSet.find(oLayerDesc);
204 6 : if( oIter == oSet.end() )
205 : {
206 6 : oLayerDesc.osSubstitutedName = CPLString().Printf("_OGR_%d", nNum ++);
207 6 : osModifiedSQL += "\"";
208 6 : osModifiedSQL += oLayerDesc.osSubstitutedName;
209 6 : osModifiedSQL += "\"";
210 : }
211 : else
212 : {
213 0 : osModifiedSQL += (*oIter).osSubstitutedName;
214 0 : bInsert = FALSE;
215 : }
216 : }
217 230 : if( bInsert )
218 : {
219 230 : oSet.insert(oLayerDesc);
220 : }
221 230 : pszStart = pszSQLCommand;
222 230 : }
223 :
224 : /************************************************************************/
225 : /* StartsAsSQLITEKeyWord() */
226 : /************************************************************************/
227 :
228 : static const char* apszKeywords[] = {
229 : "WHERE", "GROUP", "ORDER", "JOIN", "UNION", "INTERSECT", "EXCEPT", "LIMIT"
230 : };
231 :
232 182 : static int StartsAsSQLITEKeyWord(const char* pszStr)
233 : {
234 : int i;
235 264 : for(i=0;i<(int)(sizeof(apszKeywords) / sizeof(char*));i++)
236 : {
237 257 : if( EQUALN(pszStr, apszKeywords[i], strlen(apszKeywords[i])) )
238 175 : return TRUE;
239 : }
240 7 : return FALSE;
241 : }
242 :
243 : /************************************************************************/
244 : /* OGR2SQLITEGetPotentialLayerNames() */
245 : /************************************************************************/
246 :
247 225 : static void OGR2SQLITEGetPotentialLayerNamesInternal(const char **ppszSQLCommand,
248 : std::set<LayerDesc>& oSetLayers,
249 : std::set<CPLString>& oSetSpatialIndex,
250 : CPLString& osModifiedSQL,
251 : int& nNum)
252 : {
253 225 : const char *pszSQLCommand = *ppszSQLCommand;
254 225 : const char* pszStart = pszSQLCommand;
255 : char ch;
256 225 : int nParenthesisLevel = 0;
257 225 : int bLookforFTableName = FALSE;
258 :
259 7905 : while( (ch = *pszSQLCommand) != '\0' )
260 : {
261 7457 : if( ch == '(' )
262 31 : nParenthesisLevel ++;
263 7426 : else if( ch == ')' )
264 : {
265 42 : nParenthesisLevel --;
266 42 : if( nParenthesisLevel < 0 )
267 : {
268 2 : pszSQLCommand ++;
269 2 : break;
270 : }
271 : }
272 :
273 : /* Skip literals and strings */
274 7560 : if( ch == '\'' || ch == '"' )
275 : {
276 105 : char chEscapeChar = ch;
277 105 : pszSQLCommand ++;
278 745 : while( (ch = *pszSQLCommand) != '\0' )
279 : {
280 640 : if( ch == chEscapeChar && pszSQLCommand[1] == chEscapeChar )
281 0 : pszSQLCommand ++;
282 640 : else if( ch == chEscapeChar )
283 : {
284 105 : pszSQLCommand ++;
285 105 : break;
286 : }
287 535 : pszSQLCommand ++;
288 : }
289 : }
290 :
291 7359 : else if( EQUALN(pszSQLCommand, "ogr_layer_extent", strlen("ogr_layer_extent")) ||
292 : EQUALN(pszSQLCommand, "ogr_layer_srid", strlen("ogr_layer_srid")) ||
293 : EQUALN(pszSQLCommand, "ogr_layer_geometrytype", strlen("ogr_layer_geometrytype")) )
294 : {
295 168 : while( *pszSQLCommand != '\0' && *pszSQLCommand != '(' )
296 150 : pszSQLCommand ++;
297 :
298 9 : if( *pszSQLCommand != '(' )
299 0 : break;
300 :
301 9 : pszSQLCommand ++;
302 9 : nParenthesisLevel ++;
303 :
304 18 : while( isspace((int)*pszSQLCommand) )
305 0 : pszSQLCommand ++;
306 :
307 : OGR2SQLITEAddLayer(pszStart, nNum,
308 9 : pszSQLCommand, oSetLayers, osModifiedSQL);
309 : }
310 :
311 7344 : else if( bLookforFTableName &&
312 : EQUALN(pszSQLCommand, "f_table_name", strlen("f_table_name")) &&
313 1 : (pszSQLCommand[strlen("f_table_name")] == '=' ||
314 1 : isspace((int)pszSQLCommand[strlen("f_table_name")])) )
315 : {
316 1 : pszSQLCommand += strlen("f_table_name");
317 :
318 3 : while( isspace((int)*pszSQLCommand) )
319 1 : pszSQLCommand ++;
320 :
321 1 : if( *pszSQLCommand == '=' )
322 : {
323 1 : pszSQLCommand ++;
324 :
325 3 : while( isspace((int)*pszSQLCommand) )
326 1 : pszSQLCommand ++;
327 :
328 1 : oSetSpatialIndex.insert(OGR2SQLITEExtractUnquotedString(&pszSQLCommand));
329 : }
330 :
331 1 : bLookforFTableName = FALSE;
332 : }
333 :
334 7747 : else if( EQUALN(pszSQLCommand, "FROM", strlen("FROM")) &&
335 204 : isspace(pszSQLCommand[strlen("FROM")]) )
336 : {
337 204 : pszSQLCommand += strlen("FROM") + 1;
338 :
339 408 : while( isspace((int)*pszSQLCommand) )
340 0 : pszSQLCommand ++;
341 :
342 205 : if( EQUALN(pszSQLCommand, "SpatialIndex", strlen("SpatialIndex")) &&
343 1 : isspace((int)pszSQLCommand[strlen("SpatialIndex")]) )
344 : {
345 1 : pszSQLCommand += strlen("SpatialIndex") + 1;
346 :
347 1 : bLookforFTableName = TRUE;
348 :
349 1 : continue;
350 : }
351 :
352 203 : if( *pszSQLCommand == '(' )
353 : {
354 2 : pszSQLCommand++;
355 :
356 2 : CPLString osTruncated(pszStart);
357 2 : osTruncated.resize(pszSQLCommand - pszStart);
358 2 : osModifiedSQL += osTruncated;
359 :
360 : OGR2SQLITEGetPotentialLayerNamesInternal(
361 : &pszSQLCommand, oSetLayers, oSetSpatialIndex,
362 2 : osModifiedSQL, nNum);
363 :
364 2 : pszStart = pszSQLCommand;
365 : }
366 : else
367 : OGR2SQLITEAddLayer(pszStart, nNum,
368 201 : pszSQLCommand, oSetLayers, osModifiedSQL);
369 :
370 415 : while( *pszSQLCommand != '\0' )
371 : {
372 188 : if( isspace((int)*pszSQLCommand) )
373 : {
374 182 : pszSQLCommand ++;
375 364 : while( isspace((int)*pszSQLCommand) )
376 0 : pszSQLCommand ++;
377 :
378 182 : if( EQUALN(pszSQLCommand, "AS", 2) )
379 : {
380 0 : pszSQLCommand += 2;
381 0 : while( isspace((int)*pszSQLCommand) )
382 0 : pszSQLCommand ++;
383 : }
384 :
385 : /* Skip alias */
386 182 : if( *pszSQLCommand != '\0' &&
387 : *pszSQLCommand != ',' )
388 : {
389 182 : if ( StartsAsSQLITEKeyWord(pszSQLCommand) )
390 175 : break;
391 7 : OGR2SQLITEExtractUnquotedString(&pszSQLCommand);
392 : }
393 : }
394 6 : else if (*pszSQLCommand == ',' )
395 : {
396 2 : pszSQLCommand ++;
397 6 : while( isspace((int)*pszSQLCommand) )
398 2 : pszSQLCommand ++;
399 :
400 2 : if( *pszSQLCommand == '(' )
401 : {
402 0 : pszSQLCommand++;
403 :
404 0 : CPLString osTruncated(pszStart);
405 0 : osTruncated.resize(pszSQLCommand - pszStart);
406 0 : osModifiedSQL += osTruncated;
407 :
408 : OGR2SQLITEGetPotentialLayerNamesInternal(
409 : &pszSQLCommand, oSetLayers,
410 : oSetSpatialIndex,
411 0 : osModifiedSQL, nNum);
412 :
413 0 : pszStart = pszSQLCommand;
414 : }
415 : else
416 : OGR2SQLITEAddLayer(pszStart, nNum,
417 : pszSQLCommand, oSetLayers,
418 2 : osModifiedSQL);
419 : }
420 : else
421 4 : break;
422 : }
423 : }
424 7144 : else if ( EQUALN(pszSQLCommand, "JOIN", strlen("JOIN")) &&
425 4 : isspace(pszSQLCommand[strlen("JOIN")]) )
426 : {
427 4 : pszSQLCommand += strlen("JOIN") + 1;
428 : OGR2SQLITEAddLayer(pszStart, nNum, pszSQLCommand,
429 4 : oSetLayers, osModifiedSQL);
430 : }
431 7148 : else if( EQUALN(pszSQLCommand, "INTO", strlen("INTO")) &&
432 8 : isspace(pszSQLCommand[strlen("INTO")]) )
433 : {
434 8 : pszSQLCommand += strlen("INTO") + 1;
435 : OGR2SQLITEAddLayer(pszStart, nNum, pszSQLCommand,
436 8 : oSetLayers, osModifiedSQL);
437 : }
438 7136 : else if( EQUALN(pszSQLCommand, "UPDATE", strlen("UPDATE")) &&
439 6 : isspace(pszSQLCommand[strlen("UPDATE")]) )
440 : {
441 6 : pszSQLCommand += strlen("UPDATE") + 1;
442 : OGR2SQLITEAddLayer(pszStart, nNum, pszSQLCommand,
443 6 : oSetLayers, osModifiedSQL);
444 : }
445 7118 : else if ( EQUALN(pszSQLCommand, "DROP TABLE ", strlen("DROP TABLE ")) )
446 : {
447 0 : pszSQLCommand += strlen("DROP TABLE") + 1;
448 : OGR2SQLITEAddLayer(pszStart, nNum, pszSQLCommand,
449 0 : oSetLayers, osModifiedSQL);
450 : }
451 : else
452 7118 : pszSQLCommand ++;
453 : }
454 :
455 225 : CPLString osTruncated(pszStart);
456 225 : osTruncated.resize(pszSQLCommand - pszStart);
457 225 : osModifiedSQL += osTruncated;
458 :
459 225 : *ppszSQLCommand = pszSQLCommand;
460 225 : }
461 :
462 223 : static void OGR2SQLITEGetPotentialLayerNames(const char *pszSQLCommand,
463 : std::set<LayerDesc>& oSetLayers,
464 : std::set<CPLString>& oSetSpatialIndex,
465 : CPLString& osModifiedSQL)
466 : {
467 223 : int nNum = 1;
468 : OGR2SQLITEGetPotentialLayerNamesInternal(&pszSQLCommand, oSetLayers,
469 : oSetSpatialIndex,
470 223 : osModifiedSQL, nNum);
471 223 : }
472 :
473 : /************************************************************************/
474 : /* OGR2SQLITE_IgnoreAllFieldsExceptGeometry() */
475 : /************************************************************************/
476 :
477 : static
478 2 : void OGR2SQLITE_IgnoreAllFieldsExceptGeometry(OGRLayer* poLayer)
479 : {
480 2 : char** papszIgnored = NULL;
481 2 : papszIgnored = CSLAddString(papszIgnored, "OGR_STYLE");
482 2 : OGRFeatureDefn* poFeatureDefn = poLayer->GetLayerDefn();
483 4 : for(int i=0; i < poFeatureDefn->GetFieldCount(); i++)
484 : {
485 : papszIgnored = CSLAddString(papszIgnored,
486 2 : poFeatureDefn->GetFieldDefn(i)->GetNameRef());
487 : }
488 2 : poLayer->SetIgnoredFields((const char**)papszIgnored);
489 2 : CSLDestroy(papszIgnored);
490 2 : }
491 :
492 : /************************************************************************/
493 : /* OGRSQLiteExecuteSQL() */
494 : /************************************************************************/
495 :
496 222 : OGRLayer * OGRSQLiteExecuteSQL( OGRDataSource* poDS,
497 : const char *pszStatement,
498 : OGRGeometry *poSpatialFilter,
499 : const char *pszDialect )
500 : {
501 222 : char* pszTmpDBName = (char*) CPLMalloc(256);
502 222 : sprintf(pszTmpDBName, "/vsimem/ogr2sqlite/temp_%p.db", pszTmpDBName);
503 :
504 222 : OGRSQLiteDataSource* poSQLiteDS = NULL;
505 : int nRet;
506 222 : int bSpatialiteDB = FALSE;
507 :
508 222 : OGR2SQLITE_Register();
509 :
510 : /* -------------------------------------------------------------------- */
511 : /* Create in-memory sqlite/spatialite DB */
512 : /* -------------------------------------------------------------------- */
513 :
514 : #ifdef HAVE_SPATIALITE
515 :
516 : /* -------------------------------------------------------------------- */
517 : /* Creating an empty spatialite DB (with spatial_ref_sys populated */
518 : /* has a non-neglectable cost. So at the first attempt, let's make */
519 : /* one and cache it for later use. */
520 : /* -------------------------------------------------------------------- */
521 : #if 1
522 : static vsi_l_offset nEmptyDBSize = 0;
523 : static GByte* pabyEmptyDB = NULL;
524 : {
525 : static void* hMutex = NULL;
526 222 : CPLMutexHolder oMutexHolder(&hMutex);
527 : static int bTried = FALSE;
528 222 : if( !bTried )
529 : {
530 1 : bTried = TRUE;
531 1 : char* pszCachedFilename = (char*) CPLMalloc(256);
532 1 : sprintf(pszCachedFilename, "/vsimem/ogr2sqlite/reference_%p.db",pszCachedFilename);
533 1 : char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
534 1 : OGRSQLiteDataSource* poCachedDS = new OGRSQLiteDataSource();
535 2 : nRet = poCachedDS->Create( pszCachedFilename, papszOptions );
536 1 : CSLDestroy(papszOptions);
537 1 : papszOptions = NULL;
538 1 : delete poCachedDS;
539 1 : if( nRet )
540 : /* Note: the reference file keeps the ownership of the data, so that */
541 : /* it gets released with VSICleanupFileManager() */
542 1 : pabyEmptyDB = VSIGetMemFileBuffer( pszCachedFilename, &nEmptyDBSize, FALSE );
543 1 : CPLFree( pszCachedFilename );
544 222 : }
545 : }
546 :
547 : /* The following configuration option is usefull mostly for debugging/testing */
548 444 : if( pabyEmptyDB != NULL && CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) )
549 : {
550 123 : GByte* pabyEmptyDBClone = (GByte*)VSIMalloc(nEmptyDBSize);
551 123 : if( pabyEmptyDBClone == NULL )
552 : {
553 0 : CPLFree(pszTmpDBName);
554 0 : return NULL;
555 : }
556 123 : memcpy(pabyEmptyDBClone, pabyEmptyDB, nEmptyDBSize);
557 123 : VSIFCloseL(VSIFileFromMemBuffer( pszTmpDBName, pabyEmptyDBClone, nEmptyDBSize, TRUE ));
558 :
559 123 : poSQLiteDS = new OGRSQLiteDataSource();
560 123 : if( !poSQLiteDS->Open( pszTmpDBName, TRUE ) )
561 : {
562 : /* should not happen really ! */
563 0 : delete poSQLiteDS;
564 0 : VSIUnlink(pszTmpDBName);
565 0 : CPLFree(pszTmpDBName);
566 0 : return NULL;
567 : }
568 123 : bSpatialiteDB = TRUE;
569 : }
570 : #else
571 : /* No caching version */
572 : poSQLiteDS = new OGRSQLiteDataSource();
573 : char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
574 : nRet = poSQLiteDS->Create( pszTmpDBName, papszOptions );
575 : CSLDestroy(papszOptions);
576 : papszOptions = NULL;
577 : if( nRet )
578 : {
579 : bSpatialiteDB = TRUE;
580 : }
581 : #endif
582 :
583 : else
584 : {
585 99 : delete poSQLiteDS;
586 99 : poSQLiteDS = NULL;
587 : #else
588 : if( TRUE )
589 : {
590 : #endif
591 99 : poSQLiteDS = new OGRSQLiteDataSource();
592 99 : nRet = poSQLiteDS->Create( pszTmpDBName, NULL );
593 99 : if( !nRet )
594 : {
595 0 : delete poSQLiteDS;
596 0 : VSIUnlink(pszTmpDBName);
597 0 : CPLFree(pszTmpDBName);
598 0 : return NULL;
599 : }
600 : }
601 :
602 : /* -------------------------------------------------------------------- */
603 : /* Attach the Virtual Table OGR2SQLITE module to it. */
604 : /* -------------------------------------------------------------------- */
605 222 : OGR2SQLITEModule* poModule = new OGR2SQLITEModule(poDS);
606 222 : poModule->Setup(poSQLiteDS);
607 :
608 : /* -------------------------------------------------------------------- */
609 : /* Analysze the statement to determine which tables will be used. */
610 : /* -------------------------------------------------------------------- */
611 222 : std::set<LayerDesc> oSetLayers;
612 222 : std::set<CPLString> oSetSpatialIndex;
613 222 : CPLString osModifiedSQL;
614 : OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
615 222 : oSetSpatialIndex, osModifiedSQL);
616 222 : std::set<LayerDesc>::iterator oIter = oSetLayers.begin();
617 :
618 222 : if( strcmp(pszStatement, osModifiedSQL.c_str()) != 0 )
619 5 : CPLDebug("OGR", "Modified SQL: %s", osModifiedSQL.c_str());
620 222 : pszStatement = osModifiedSQL.c_str(); /* do not use it anymore */
621 :
622 222 : int bFoundOGRStyle = ( osModifiedSQL.ifind("OGR_STYLE") != std::string::npos );
623 :
624 : /* -------------------------------------------------------------------- */
625 : /* For each of those tables, create a Virtual Table. */
626 : /* -------------------------------------------------------------------- */
627 222 : for(; oIter != oSetLayers.end(); ++oIter)
628 : {
629 227 : const LayerDesc& oLayerDesc = *oIter;
630 : /*CPLDebug("OGR", "Layer desc : %s, %s, %s, %s",
631 : oLayerDesc.osOriginalStr.c_str(),
632 : oLayerDesc.osSubstitutedName.c_str(),
633 : oLayerDesc.osDSName.c_str(),
634 : oLayerDesc.osLayerName.c_str());*/
635 :
636 227 : CPLString osSQL;
637 227 : OGRLayer* poLayer = NULL;
638 227 : CPLString osTableName;
639 : int nExtraDS;
640 227 : if( oLayerDesc.osDSName.size() == 0 )
641 : {
642 221 : poLayer = poDS->GetLayerByName(oLayerDesc.osLayerName);
643 : /* Might be a false positive (unlikely) */
644 221 : if( poLayer == NULL )
645 6 : continue;
646 :
647 215 : osTableName = oLayerDesc.osLayerName;
648 :
649 215 : nExtraDS = -1;
650 :
651 : osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d)",
652 : OGRSQLiteEscapeName(osTableName).c_str(),
653 : nExtraDS,
654 : OGRSQLiteEscape(osTableName).c_str(),
655 215 : bFoundOGRStyle);
656 : }
657 : else
658 : {
659 : OGRDataSource* poOtherDS = (OGRDataSource* )
660 6 : OGROpen(oLayerDesc.osDSName, FALSE, NULL);
661 6 : if( poOtherDS == NULL )
662 : {
663 : CPLError(CE_Failure, CPLE_AppDefined,
664 : "Cannot open datasource '%s'",
665 1 : oLayerDesc.osDSName.c_str() );
666 1 : delete poSQLiteDS;
667 1 : delete poModule;
668 1 : VSIUnlink(pszTmpDBName);
669 1 : CPLFree(pszTmpDBName);
670 1 : return NULL;
671 : }
672 :
673 5 : poLayer = poOtherDS->GetLayerByName(oLayerDesc.osLayerName);
674 5 : if( poLayer == NULL )
675 : {
676 : CPLError(CE_Failure, CPLE_AppDefined,
677 : "Cannot find layer '%s' in '%s'",
678 : oLayerDesc.osLayerName.c_str(),
679 1 : oLayerDesc.osDSName.c_str() );
680 1 : delete poOtherDS;
681 1 : delete poSQLiteDS;
682 1 : delete poModule;
683 1 : VSIUnlink(pszTmpDBName);
684 1 : CPLFree(pszTmpDBName);
685 1 : return NULL;
686 : }
687 :
688 4 : osTableName = oLayerDesc.osSubstitutedName;
689 :
690 4 : nExtraDS = poModule->AddExtraDS(poOtherDS);
691 :
692 : osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d)",
693 : OGRSQLiteEscapeName(osTableName).c_str(),
694 : nExtraDS,
695 : OGRSQLiteEscape(oLayerDesc.osLayerName).c_str(),
696 4 : bFoundOGRStyle);
697 : }
698 :
699 219 : sqlite3* hDB = poSQLiteDS->GetDB();
700 :
701 219 : char* pszErrMsg = NULL;
702 : int rc = sqlite3_exec( hDB, osSQL.c_str(),
703 219 : NULL, NULL, &pszErrMsg );
704 219 : if( rc != SQLITE_OK )
705 : {
706 : CPLError(CE_Failure, CPLE_AppDefined,
707 : "Cannot create virtual table for layer '%s' : %s",
708 0 : osTableName.c_str(), pszErrMsg);
709 0 : sqlite3_free(pszErrMsg);
710 0 : continue;
711 : }
712 :
713 219 : if( poLayer->GetGeomType() == wkbNone )
714 98 : continue;
715 :
716 121 : CPLString osGeomColRaw(OGR2SQLITE_GetNameForGeometryColumn(poLayer));
717 121 : const char* pszGeomColRaw = osGeomColRaw.c_str();
718 :
719 121 : CPLString osGeomColEscaped(OGRSQLiteEscape(pszGeomColRaw));
720 121 : const char* pszGeomColEscaped = osGeomColEscaped.c_str();
721 :
722 121 : CPLString osLayerNameEscaped(OGRSQLiteEscape(osTableName));
723 121 : const char* pszLayerNameEscaped = osLayerNameEscaped.c_str();
724 :
725 : CPLString osIdxNameRaw(CPLSPrintf("idx_%s_%s",
726 121 : oLayerDesc.osLayerName.c_str(), pszGeomColRaw));
727 121 : CPLString osIdxNameEscaped(OGRSQLiteEscapeName(osIdxNameRaw));
728 :
729 : /* Make sure that the SRS is injected in spatial_ref_sys */
730 121 : OGRSpatialReference* poSRS = poLayer->GetSpatialRef();
731 121 : int nSRSId = poSQLiteDS->GetUndefinedSRID();
732 121 : if( poSRS != NULL )
733 24 : nSRSId = poSQLiteDS->FetchSRSId(poSRS);
734 :
735 121 : int bCreateSpatialIndex = FALSE;
736 121 : if( !bSpatialiteDB )
737 : {
738 : osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
739 : "f_geometry_column, geometry_format, geometry_type, "
740 : "coord_dimension, srid) "
741 : "VALUES ('%s','%s','SpatiaLite',%d,%d,%d)",
742 : pszLayerNameEscaped,
743 : pszGeomColEscaped,
744 52 : (int) wkbFlatten(poLayer->GetGeomType()),
745 52 : ( poLayer->GetGeomType() & wkb25DBit ) ? 3 : 2,
746 104 : nSRSId);
747 : }
748 : #ifdef HAVE_SPATIALITE
749 : else
750 : {
751 : /* We detect the need for creating a spatial index by 2 means : */
752 :
753 : /* 1) if there's an explicit reference to a 'idx_layername_geometrycolumn' */
754 : /* table in the SQL --> old/traditionnal way of requesting spatial indices */
755 : /* with spatialite. */
756 :
757 69 : std::set<LayerDesc>::iterator oIter2 = oSetLayers.begin();
758 144 : for(; oIter2 != oSetLayers.end(); ++oIter2)
759 : {
760 76 : const LayerDesc& oLayerDescIter = *oIter2;
761 76 : if( EQUAL(oLayerDescIter.osLayerName, osIdxNameRaw) )
762 : {
763 1 : bCreateSpatialIndex = TRUE;
764 1 : break;
765 : }
766 : }
767 :
768 : /* 2) or if there's a SELECT FROM SpatialIndex WHERE f_table_name = 'layername' */
769 69 : if( !bCreateSpatialIndex )
770 : {
771 68 : std::set<CPLString>::iterator oIter3 = oSetSpatialIndex.begin();
772 69 : for(; oIter3 != oSetSpatialIndex.end(); ++oIter3)
773 : {
774 2 : const CPLString& osNameIter = *oIter3;
775 2 : if( EQUAL(osNameIter, oLayerDesc.osLayerName) )
776 : {
777 1 : bCreateSpatialIndex = TRUE;
778 1 : break;
779 : }
780 : }
781 : }
782 :
783 69 : if( poSQLiteDS->HasSpatialite4Layout() )
784 : {
785 0 : int nGeomType = poLayer->GetGeomType();
786 0 : int nCoordDimension = 2;
787 0 : if( nGeomType & wkb25DBit )
788 : {
789 0 : nGeomType += 1000;
790 0 : nCoordDimension = 3;
791 : }
792 :
793 : osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
794 : "f_geometry_column, geometry_type, coord_dimension, "
795 : "srid, spatial_index_enabled) "
796 : "VALUES ('%s',Lower('%s'),%d ,%d ,%d, %d)",
797 : pszLayerNameEscaped,
798 : pszGeomColEscaped, nGeomType,
799 : nCoordDimension,
800 0 : nSRSId, bCreateSpatialIndex );
801 : }
802 : else
803 : {
804 69 : const char *pszGeometryType = OGRToOGCGeomType(poLayer->GetGeomType());
805 69 : if (pszGeometryType[0] == '\0')
806 0 : pszGeometryType = "GEOMETRY";
807 :
808 : osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
809 : "f_geometry_column, type, coord_dimension, "
810 : "srid, spatial_index_enabled) "
811 : "VALUES ('%s','%s','%s','%s',%d, %d)",
812 : pszLayerNameEscaped,
813 : pszGeomColEscaped, pszGeometryType,
814 69 : ( poLayer->GetGeomType() & wkb25DBit ) ? "XYZ" : "XY",
815 69 : nSRSId, bCreateSpatialIndex );
816 : }
817 : }
818 : #endif // HAVE_SPATIALITE
819 121 : sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
820 :
821 : #ifdef HAVE_SPATIALITE
822 : /* -------------------------------------------------------------------- */
823 : /* Should we create a spatial index ?. */
824 : /* -------------------------------------------------------------------- */
825 121 : if( !bSpatialiteDB || !bCreateSpatialIndex )
826 119 : continue;
827 :
828 2 : CPLDebug("SQLITE", "Create spatial index %s", osIdxNameRaw.c_str());
829 :
830 : /* ENABLE_VIRTUAL_OGR_SPATIAL_INDEX is not defined */
831 : #ifdef ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
832 : osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING "
833 : "VirtualOGRSpatialIndex(%d, '%s', pkid, xmin, xmax, ymin, ymax)",
834 : osIdxNameEscaped.c_str(),
835 : nExtraDS,
836 : OGRSQLiteEscape(oLayerDesc.osLayerName).c_str());
837 :
838 : rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
839 : if( rc != SQLITE_OK )
840 : {
841 : CPLDebug("SQLITE",
842 : "Error occured during spatial index creation : %s",
843 : sqlite3_errmsg(hDB));
844 : }
845 : #else // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
846 2 : rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, NULL );
847 :
848 : osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" "
849 : "USING rtree(pkid, xmin, xmax, ymin, ymax)",
850 2 : osIdxNameEscaped.c_str());
851 :
852 2 : if( rc == SQLITE_OK )
853 2 : rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
854 :
855 2 : sqlite3_stmt *hStmt = NULL;
856 2 : if( rc == SQLITE_OK )
857 : {
858 : const char* pszInsertInto = CPLSPrintf(
859 : "INSERT INTO \"%s\" (pkid, xmin, xmax, ymin, ymax) "
860 2 : "VALUES (?,?,?,?,?)", osIdxNameEscaped.c_str());
861 2 : rc = sqlite3_prepare(hDB, pszInsertInto, -1, &hStmt, NULL);
862 : }
863 :
864 : OGRFeature* poFeature;
865 2 : OGREnvelope sEnvelope;
866 2 : OGR2SQLITE_IgnoreAllFieldsExceptGeometry(poLayer);
867 2 : poLayer->ResetReading();
868 :
869 14 : while( rc == SQLITE_OK &&
870 6 : (poFeature = poLayer->GetNextFeature()) != NULL )
871 : {
872 4 : OGRGeometry* poGeom = poFeature->GetGeometryRef();
873 4 : if( poGeom != NULL && !poGeom->IsEmpty() )
874 : {
875 4 : poGeom->getEnvelope(&sEnvelope);
876 : sqlite3_bind_int64(hStmt, 1,
877 4 : (sqlite3_int64) poFeature->GetFID() );
878 4 : sqlite3_bind_double(hStmt, 2, sEnvelope.MinX);
879 4 : sqlite3_bind_double(hStmt, 3, sEnvelope.MaxX);
880 4 : sqlite3_bind_double(hStmt, 4, sEnvelope.MinY);
881 4 : sqlite3_bind_double(hStmt, 5, sEnvelope.MaxY);
882 4 : rc = sqlite3_step(hStmt);
883 4 : if( rc == SQLITE_OK || rc == SQLITE_DONE )
884 4 : rc = sqlite3_reset(hStmt);
885 : }
886 4 : delete poFeature;
887 : }
888 :
889 2 : poLayer->SetIgnoredFields(NULL);
890 :
891 2 : sqlite3_finalize(hStmt);
892 :
893 2 : if( rc == SQLITE_OK )
894 2 : rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, NULL );
895 : else
896 : {
897 : CPLDebug("SQLITE",
898 : "Error occured during spatial index creation : %s",
899 0 : sqlite3_errmsg(hDB));
900 0 : rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, NULL );
901 : }
902 : #endif // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
903 :
904 : #endif // HAVE_SPATIALITE
905 :
906 : }
907 :
908 220 : delete poSQLiteDS;
909 :
910 : /* -------------------------------------------------------------------- */
911 : /* Re-open so that virtual tables are recognized */
912 : /* -------------------------------------------------------------------- */
913 220 : poSQLiteDS = new OGRSQLiteDataSource();
914 440 : if( !poSQLiteDS->Open(pszTmpDBName, TRUE) )
915 : {
916 0 : delete poSQLiteDS;
917 0 : delete poModule;
918 0 : VSIUnlink(pszTmpDBName);
919 0 : CPLFree(pszTmpDBName);
920 0 : return NULL;
921 : }
922 220 : sqlite3* hDB = poSQLiteDS->GetDB();
923 220 : poModule->Setup(poSQLiteDS);
924 :
925 : /* -------------------------------------------------------------------- */
926 : /* Prepare the statement. */
927 : /* -------------------------------------------------------------------- */
928 : /* This will speed-up layer creation */
929 : /* ORDER BY are costly to evaluate and are not necessary to establish */
930 : /* the layer definition. */
931 220 : int bUseStatementForGetNextFeature = TRUE;
932 220 : int bEmptyLayer = FALSE;
933 :
934 220 : sqlite3_stmt *hSQLStmt = NULL;
935 : int rc = sqlite3_prepare( hDB,
936 : pszStatement, strlen(pszStatement),
937 220 : &hSQLStmt, NULL );
938 :
939 220 : if( rc != SQLITE_OK )
940 : {
941 : CPLError( CE_Failure, CPLE_AppDefined,
942 : "In ExecuteSQL(): sqlite3_prepare(%s):\n %s",
943 1 : pszStatement, sqlite3_errmsg(hDB) );
944 :
945 1 : if( hSQLStmt != NULL )
946 : {
947 0 : sqlite3_finalize( hSQLStmt );
948 : }
949 :
950 1 : delete poSQLiteDS;
951 1 : delete poModule;
952 1 : VSIUnlink(pszTmpDBName);
953 1 : CPLFree(pszTmpDBName);
954 :
955 1 : return NULL;
956 : }
957 :
958 : /* -------------------------------------------------------------------- */
959 : /* Do we get a resultset? */
960 : /* -------------------------------------------------------------------- */
961 219 : rc = sqlite3_step( hSQLStmt );
962 219 : if( rc != SQLITE_ROW )
963 : {
964 96 : if ( rc != SQLITE_DONE )
965 : {
966 : CPLError( CE_Failure, CPLE_AppDefined,
967 : "In ExecuteSQL(): sqlite3_step(%s):\n %s",
968 0 : pszStatement, sqlite3_errmsg(hDB) );
969 :
970 0 : sqlite3_finalize( hSQLStmt );
971 :
972 0 : delete poSQLiteDS;
973 0 : delete poModule;
974 0 : VSIUnlink(pszTmpDBName);
975 0 : CPLFree(pszTmpDBName);
976 :
977 0 : return NULL;
978 : }
979 :
980 96 : if( !EQUALN(pszStatement, "SELECT ", 7) )
981 : {
982 :
983 22 : sqlite3_finalize( hSQLStmt );
984 :
985 22 : delete poSQLiteDS;
986 22 : delete poModule;
987 22 : VSIUnlink(pszTmpDBName);
988 22 : CPLFree(pszTmpDBName);
989 :
990 22 : return NULL;
991 : }
992 :
993 74 : bUseStatementForGetNextFeature = FALSE;
994 74 : bEmptyLayer = TRUE;
995 : }
996 :
997 : /* -------------------------------------------------------------------- */
998 : /* Create layer. */
999 : /* -------------------------------------------------------------------- */
1000 197 : OGRSQLiteSelectLayer *poLayer = NULL;
1001 :
1002 : poLayer = new OGRSQLiteExecuteSQLLayer( poModule, pszTmpDBName,
1003 : poSQLiteDS, pszStatement, hSQLStmt,
1004 197 : bUseStatementForGetNextFeature, bEmptyLayer );
1005 :
1006 197 : if( poSpatialFilter != NULL )
1007 0 : poLayer->SetSpatialFilter( poSpatialFilter );
1008 :
1009 197 : return poLayer;
1010 : }
1011 :
1012 : /************************************************************************/
1013 : /* OGRSQLiteGetReferencedLayers() */
1014 : /************************************************************************/
1015 :
1016 1 : std::set<LayerDesc> OGRSQLiteGetReferencedLayers(const char* pszStatement)
1017 : {
1018 : /* -------------------------------------------------------------------- */
1019 : /* Analysze the statement to determine which tables will be used. */
1020 : /* -------------------------------------------------------------------- */
1021 1 : std::set<LayerDesc> oSetLayers;
1022 1 : std::set<CPLString> oSetSpatialIndex;
1023 1 : CPLString osModifiedSQL;
1024 : OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
1025 1 : oSetSpatialIndex, osModifiedSQL);
1026 :
1027 1 : return oSetLayers;
1028 : }
1029 :
1030 : #else // HAVE_SQLITE_VFS
1031 :
1032 : /************************************************************************/
1033 : /* OGRSQLiteExecuteSQL() */
1034 : /************************************************************************/
1035 :
1036 : OGRLayer * OGRSQLiteExecuteSQL( OGRDataSource* poDS,
1037 : const char *pszStatement,
1038 : OGRGeometry *poSpatialFilter,
1039 : const char *pszDialect )
1040 : {
1041 : CPLError(CE_Failure, CPLE_NotSupported,
1042 : "The SQLite version is to old to support the SQLite SQL dialect");
1043 : return NULL;
1044 : }
1045 :
1046 : /************************************************************************/
1047 : /* OGRSQLiteGetReferencedLayers() */
1048 : /************************************************************************/
1049 :
1050 : std::set<LayerDesc> OGRSQLiteGetReferencedLayers(const char* pszStatement)
1051 : {
1052 : std::set<LayerDesc> oSetLayers;
1053 : return oSetLayers;
1054 : }
1055 :
1056 : #endif // HAVE_SQLITE_VFS
|
