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