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