1 : /******************************************************************************
2 : * $Id: ogrsqliteselectlayer.cpp 25694 2013-03-01 17:26:40Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRSQLiteSelectLayer class, layer access to the results
6 : * of a SELECT statement executed via ExecuteSQL().
7 : * Author: Frank Warmerdam, warmerdam@pobox.com
8 : *
9 : ******************************************************************************
10 : * Copyright (c) 2004, Frank Warmerdam
11 : *
12 : * Permission is hereby granted, free of charge, to any person obtaining a
13 : * copy of this software and associated documentation files (the "Software"),
14 : * to deal in the Software without restriction, including without limitation
15 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
16 : * and/or sell copies of the Software, and to permit persons to whom the
17 : * Software is furnished to do so, subject to the following conditions:
18 : *
19 : * The above copyright notice and this permission notice shall be included
20 : * in all copies or substantial portions of the Software.
21 : *
22 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
23 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
24 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
25 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
26 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
27 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
28 : * DEALINGS IN THE SOFTWARE.
29 : ****************************************************************************/
30 :
31 : #include "cpl_conv.h"
32 : #include "ogr_sqlite.h"
33 :
34 : CPL_CVSID("$Id: ogrsqliteselectlayer.cpp 25694 2013-03-01 17:26:40Z rouault $");
35 : /************************************************************************/
36 : /* OGRSQLiteSelectLayer() */
37 : /************************************************************************/
38 :
39 807 : OGRSQLiteSelectLayer::OGRSQLiteSelectLayer( OGRSQLiteDataSource *poDSIn,
40 : CPLString osSQLIn,
41 : sqlite3_stmt *hStmtIn,
42 : int bUseStatementForGetNextFeature,
43 807 : int bEmptyLayer )
44 :
45 : {
46 807 : poDS = poDSIn;
47 :
48 807 : iNextShapeId = 0;
49 807 : poFeatureDefn = NULL;
50 807 : bAllowResetReadingEvenIfIndexAtZero = FALSE;
51 :
52 807 : std::set<CPLString> aosEmpty;
53 807 : BuildFeatureDefn( "SELECT", hStmtIn, aosEmpty );
54 :
55 807 : if( bUseStatementForGetNextFeature )
56 : {
57 652 : hStmt = hStmtIn;
58 652 : bDoStep = FALSE;
59 :
60 : // Try to extract SRS from first geometry
61 652 : if( !bEmptyLayer && osGeomColumn.size() != 0 )
62 : {
63 202 : int nRawColumns = sqlite3_column_count( hStmt );
64 750 : for( int iCol = 0; iCol < nRawColumns; iCol++ )
65 : {
66 : int nBytes;
67 699 : if( sqlite3_column_type( hStmt, iCol ) == SQLITE_BLOB &&
68 : strcmp(OGRSQLiteParamsUnquote(sqlite3_column_name( hStmt, iCol )).c_str(), osGeomColumn.c_str()) == 0 &&
69 : (nBytes = sqlite3_column_bytes( hStmt, iCol )) > 39 )
70 : {
71 151 : const GByte* pabyBlob = (const GByte*)sqlite3_column_blob( hStmt, iCol );
72 151 : int eByteOrder = pabyBlob[1];
73 437 : if( pabyBlob[0] == 0x00 &&
74 : (eByteOrder == wkbNDR || eByteOrder == wkbXDR) &&
75 143 : pabyBlob[38] == 0x7C )
76 : {
77 143 : memcpy(&nSRSId, pabyBlob + 2, 4);
78 : #ifdef CPL_LSB
79 143 : if( eByteOrder != wkbNDR)
80 0 : CPL_SWAP32PTR(&nSRSId);
81 : #else
82 : if( eByteOrder == wkbNDR)
83 : CPL_SWAP32PTR(&nSRSId);
84 : #endif
85 143 : CPLPushErrorHandler(CPLQuietErrorHandler);
86 143 : poSRS = poDS->FetchSRS( nSRSId );
87 143 : CPLPopErrorHandler();
88 143 : if( poSRS != NULL )
89 116 : poSRS->Reference();
90 : else
91 27 : CPLErrorReset();
92 : }
93 : #ifdef SQLITE_HAS_COLUMN_METADATA
94 : else
95 : {
96 8 : const char* pszTableName = sqlite3_column_table_name( hStmt, iCol );
97 8 : if( pszTableName != NULL )
98 : {
99 8 : OGRLayer* poLayer = poDS->GetLayerByName(pszTableName);
100 8 : if( poLayer != NULL )
101 : {
102 8 : poSRS = poLayer->GetSpatialRef();
103 8 : if( poSRS != NULL )
104 4 : poSRS->Reference();
105 : }
106 : }
107 : }
108 : #endif
109 151 : break;
110 : }
111 : }
112 : }
113 : }
114 : else
115 155 : sqlite3_finalize( hStmtIn );
116 :
117 807 : osSQLBase = osSQLIn;
118 807 : osSQLCurrent = osSQLIn;
119 807 : this->bEmptyLayer = bEmptyLayer;
120 807 : bSpatialFilterInSQL = TRUE;
121 807 : }
122 :
123 : /************************************************************************/
124 : /* ResetReading() */
125 : /************************************************************************/
126 :
127 144 : void OGRSQLiteSelectLayer::ResetReading()
128 :
129 : {
130 144 : if( iNextShapeId > 0 || bAllowResetReadingEvenIfIndexAtZero )
131 : {
132 91 : OGRSQLiteLayer::ResetReading();
133 91 : bAllowResetReadingEvenIfIndexAtZero = FALSE;
134 : }
135 144 : }
136 :
137 : /************************************************************************/
138 : /* GetNextFeature() */
139 : /************************************************************************/
140 :
141 1630 : OGRFeature *OGRSQLiteSelectLayer::GetNextFeature()
142 : {
143 1630 : if( bEmptyLayer )
144 116 : return NULL;
145 :
146 1514 : return OGRSQLiteLayer::GetNextFeature();
147 : }
148 :
149 : /************************************************************************/
150 : /* GetNextFeature() */
151 : /************************************************************************/
152 :
153 84 : int OGRSQLiteSelectLayer::GetFeatureCount( int bForce )
154 : {
155 84 : if( bEmptyLayer )
156 0 : return 0;
157 :
158 84 : if( m_poAttrQuery == NULL &&
159 : EQUALN(osSQLCurrent, "SELECT COUNT(*) FROM", strlen("SELECT COUNT(*) FROM")) &&
160 : osSQLCurrent.ifind(" GROUP BY ") == std::string::npos &&
161 : osSQLCurrent.ifind(" UNION ") == std::string::npos &&
162 : osSQLCurrent.ifind(" INTERSECT ") == std::string::npos &&
163 : osSQLCurrent.ifind(" EXCEPT ") == std::string::npos )
164 2 : return 1;
165 :
166 82 : if( m_poAttrQuery != NULL || (m_poFilterGeom != NULL && !bSpatialFilterInSQL) )
167 12 : return OGRLayer::GetFeatureCount(bForce);
168 :
169 70 : CPLString osFeatureCountSQL("SELECT COUNT(*) FROM (");
170 70 : osFeatureCountSQL += osSQLCurrent;
171 70 : osFeatureCountSQL += ")";
172 :
173 70 : CPLDebug("SQLITE", "Running %s", osFeatureCountSQL.c_str());
174 :
175 : /* -------------------------------------------------------------------- */
176 : /* Execute. */
177 : /* -------------------------------------------------------------------- */
178 70 : char *pszErrMsg = NULL;
179 : char **papszResult;
180 : int nRowCount, nColCount;
181 70 : int nResult = -1;
182 :
183 70 : if( sqlite3_get_table( poDS->GetDB(), osFeatureCountSQL, &papszResult,
184 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
185 : {
186 1 : CPLDebug("SQLITE", "Error: %s", pszErrMsg);
187 1 : sqlite3_free(pszErrMsg);
188 1 : return OGRLayer::GetFeatureCount(bForce);
189 : }
190 :
191 69 : if( nRowCount == 1 && nColCount == 1 )
192 : {
193 69 : nResult = atoi(papszResult[1]);
194 : }
195 :
196 69 : sqlite3_free_table( papszResult );
197 :
198 69 : return nResult;
199 : }
200 :
201 : /************************************************************************/
202 : /* ResetStatement() */
203 : /************************************************************************/
204 :
205 110 : OGRErr OGRSQLiteSelectLayer::ResetStatement()
206 :
207 : {
208 : int rc;
209 :
210 110 : ClearStatement();
211 :
212 110 : iNextShapeId = 0;
213 110 : bDoStep = TRUE;
214 :
215 : #ifdef DEBUG
216 110 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osSQLCurrent.c_str() );
217 : #endif
218 :
219 : rc = sqlite3_prepare( poDS->GetDB(), osSQLCurrent, osSQLCurrent.size(),
220 110 : &hStmt, NULL );
221 :
222 110 : if( rc == SQLITE_OK )
223 : {
224 110 : return OGRERR_NONE;
225 : }
226 : else
227 : {
228 : CPLError( CE_Failure, CPLE_AppDefined,
229 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
230 0 : osSQLCurrent.c_str(), sqlite3_errmsg(poDS->GetDB()) );
231 0 : hStmt = NULL;
232 0 : return OGRERR_FAILURE;
233 : }
234 : }
235 :
236 : /************************************************************************/
237 : /* SetSpatialFilter() */
238 : /************************************************************************/
239 :
240 25 : void OGRSQLiteSelectLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
241 :
242 : {
243 25 : bAllowResetReadingEvenIfIndexAtZero = TRUE;
244 :
245 25 : if( InstallFilter( poGeomIn ) )
246 : {
247 17 : bSpatialFilterInSQL = RebuildSQLWithSpatialClause();
248 :
249 17 : ResetReading();
250 : }
251 25 : }
252 :
253 : /************************************************************************/
254 : /* GetBaseLayer() */
255 : /************************************************************************/
256 :
257 17 : OGRSQLiteLayer* OGRSQLiteSelectLayer::GetBaseLayer(size_t& i)
258 : {
259 17 : char** papszTokens = CSLTokenizeString(osSQLBase.c_str());
260 17 : int bCanInsertSpatialFilter = TRUE;
261 17 : int nCountSelect = 0, nCountFrom = 0, nCountWhere = 0;
262 :
263 110 : for(int iToken = 0; papszTokens[iToken] != NULL; iToken++)
264 : {
265 93 : if (EQUAL(papszTokens[iToken], "SELECT"))
266 17 : nCountSelect ++;
267 76 : else if (EQUAL(papszTokens[iToken], "FROM"))
268 17 : nCountFrom ++;
269 59 : else if (EQUAL(papszTokens[iToken], "WHERE"))
270 4 : nCountWhere ++;
271 220 : else if (EQUAL(papszTokens[iToken], "UNION") ||
272 55 : EQUAL(papszTokens[iToken], "JOIN") ||
273 55 : EQUAL(papszTokens[iToken], "INTERSECT") ||
274 55 : EQUAL(papszTokens[iToken], "EXCEPT"))
275 : {
276 0 : bCanInsertSpatialFilter = FALSE;
277 : }
278 : }
279 17 : CSLDestroy(papszTokens);
280 :
281 17 : if (!(bCanInsertSpatialFilter && nCountSelect == 1 && nCountFrom == 1 && nCountWhere <= 1))
282 : {
283 0 : CPLDebug("SQLITE", "SQL expression too complex to analyse");
284 0 : return NULL;
285 : }
286 :
287 17 : size_t nFromPos = osSQLBase.ifind(" from ");
288 17 : if (nFromPos == std::string::npos)
289 : {
290 0 : return NULL;
291 : }
292 :
293 17 : int bInSingleQuotes = (osSQLBase[nFromPos + 6] == '\'');
294 17 : CPLString osBaseLayerName;
295 197 : for( i = nFromPos + 6 + (bInSingleQuotes ? 1 : 0);
296 : i < osSQLBase.size(); i++ )
297 : {
298 188 : if (osSQLBase[i] == '\'' && i + 1 < osSQLBase.size() &&
299 : osSQLBase[i + 1] == '\'' )
300 : {
301 0 : osBaseLayerName += osSQLBase[i];
302 0 : i++;
303 : }
304 188 : else if (osSQLBase[i] == '\'' && bInSingleQuotes)
305 : {
306 2 : i++;
307 2 : break;
308 : }
309 186 : else if (osSQLBase[i] == ' ' && !bInSingleQuotes)
310 6 : break;
311 : else
312 180 : osBaseLayerName += osSQLBase[i];
313 : }
314 :
315 17 : OGRSQLiteLayer* poUnderlyingLayer = NULL;
316 17 : if( strchr(osBaseLayerName, '(') == NULL &&
317 : osGeomColumn.size() != 0 )
318 : {
319 17 : CPLString osNewUnderlyingTableName;
320 : osNewUnderlyingTableName.Printf("%s(%s)",
321 : osBaseLayerName.c_str(),
322 17 : osGeomColumn.c_str());
323 : poUnderlyingLayer =
324 17 : (OGRSQLiteLayer*) poDS->GetLayerByName(osNewUnderlyingTableName);
325 : }
326 17 : if( poUnderlyingLayer == NULL )
327 16 : poUnderlyingLayer = (OGRSQLiteLayer*) poDS->GetLayerByName(osBaseLayerName);
328 :
329 35 : if( poUnderlyingLayer != NULL && poSRS != NULL &&
330 9 : poUnderlyingLayer->GetSpatialRef() != NULL &&
331 9 : poSRS != poUnderlyingLayer->GetSpatialRef() &&
332 0 : !poSRS->IsSame(poUnderlyingLayer->GetSpatialRef()) )
333 : {
334 0 : CPLDebug("SQLITE", "Result layer and base layer don't have the same SRS.");
335 0 : return NULL;
336 : }
337 :
338 17 : return poUnderlyingLayer;
339 : }
340 :
341 : /************************************************************************/
342 : /* RebuildSQLWithSpatialClause() */
343 : /************************************************************************/
344 :
345 17 : int OGRSQLiteSelectLayer::RebuildSQLWithSpatialClause()
346 :
347 : {
348 17 : osSQLCurrent = osSQLBase;
349 :
350 17 : if (m_poFilterGeom == NULL)
351 : {
352 4 : return TRUE;
353 : }
354 :
355 13 : size_t i = 0;
356 13 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
357 13 : if (poBaseLayer == NULL)
358 : {
359 0 : CPLDebug("SQLITE", "Cannot find base layer");
360 0 : return FALSE;
361 : }
362 :
363 13 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(m_poFilterGeom);
364 13 : if (osSpatialWhere.size() == 0)
365 : {
366 4 : CPLDebug("SQLITE", "Cannot get spatial where clause");
367 4 : return FALSE;
368 : }
369 :
370 21 : while (i < osSQLBase.size() && osSQLBase[i] == ' ')
371 3 : i ++;
372 :
373 9 : if (i < osSQLBase.size() && EQUALN(osSQLBase.c_str() + i, "WHERE ", 6))
374 : {
375 2 : osSQLCurrent = osSQLBase.substr(0, i + 6);
376 2 : osSQLCurrent += osSpatialWhere;
377 2 : osSQLCurrent += " AND (";
378 :
379 2 : size_t nEndOfWhere = osSQLBase.ifind(" GROUP ");
380 2 : if (nEndOfWhere == std::string::npos)
381 2 : nEndOfWhere = osSQLBase.ifind(" ORDER ");
382 2 : if (nEndOfWhere == std::string::npos)
383 1 : nEndOfWhere = osSQLBase.ifind(" LIMIT ");
384 :
385 2 : if (nEndOfWhere == std::string::npos)
386 : {
387 1 : osSQLCurrent += osSQLBase.substr(i + 6);
388 1 : osSQLCurrent += ")";
389 : }
390 : else
391 : {
392 1 : osSQLCurrent += osSQLBase.substr(i + 6, nEndOfWhere - (i + 6));
393 1 : osSQLCurrent += ")";
394 1 : osSQLCurrent += osSQLBase.substr(nEndOfWhere);
395 : }
396 : }
397 7 : else if (i < osSQLBase.size() &&
398 : (EQUALN(osSQLBase.c_str() + i, "GROUP ", 6) ||
399 : EQUALN(osSQLBase.c_str() + i, "ORDER ", 6) ||
400 : EQUALN(osSQLBase.c_str() + i, "LIMIT ", 6)))
401 : {
402 1 : osSQLCurrent = osSQLBase.substr(0, i);
403 1 : osSQLCurrent += " WHERE ";
404 1 : osSQLCurrent += osSpatialWhere;
405 1 : osSQLCurrent += " ";
406 1 : osSQLCurrent += osSQLBase.substr(i);
407 : }
408 6 : else if (i == osSQLBase.size())
409 : {
410 6 : osSQLCurrent = osSQLBase.substr(0, i);
411 6 : osSQLCurrent += " WHERE ";
412 6 : osSQLCurrent += osSpatialWhere;
413 : }
414 : else
415 : {
416 0 : CPLDebug("SQLITE", "SQL expression too complex for the driver to insert spatial filter in it");
417 0 : return FALSE;
418 : }
419 :
420 9 : return TRUE;
421 : }
422 :
423 : /************************************************************************/
424 : /* TestCapability() */
425 : /************************************************************************/
426 :
427 40 : int OGRSQLiteSelectLayer::TestCapability( const char * pszCap )
428 :
429 : {
430 40 : if (EQUAL(pszCap,OLCFastSpatialFilter))
431 : {
432 8 : if (osSQLCurrent != osSQLBase)
433 4 : return TRUE;
434 :
435 4 : size_t i = 0;
436 4 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
437 4 : if (poBaseLayer == NULL)
438 : {
439 0 : CPLDebug("SQLITE", "Cannot find base layer");
440 0 : return FALSE;
441 : }
442 :
443 4 : OGRPolygon oFakePoly;
444 4 : const char* pszWKT = "POLYGON((0 0,0 1,1 1,1 0,0 0))";
445 4 : oFakePoly.importFromWkt((char**) &pszWKT);
446 4 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(&oFakePoly);
447 :
448 4 : return osSpatialWhere.size() != 0;
449 : }
450 : else
451 32 : return OGRSQLiteLayer::TestCapability( pszCap );
452 : }
453 :
454 : /************************************************************************/
455 : /* GetExtent() */
456 : /************************************************************************/
457 :
458 14 : OGRErr OGRSQLiteSelectLayer::GetExtent(OGREnvelope *psExtent, int bForce)
459 : {
460 14 : if (GetGeomType() == wkbNone)
461 0 : return OGRERR_FAILURE;
462 :
463 : /* Caching of extent by SQL string is interesting to speed-up the */
464 : /* establishment of the WFS GetCapabilities document for a MapServer mapfile */
465 : /* which has several layers, only differing by scale rules */
466 14 : const OGREnvelope* psCachedExtent = poDS->GetEnvelopeFromSQL(osSQLBase);
467 14 : if (psCachedExtent)
468 : {
469 3 : memcpy(psExtent, psCachedExtent, sizeof(*psCachedExtent));
470 3 : return OGRERR_NONE;
471 : }
472 :
473 11 : CPLString osSQLCommand = osSQLBase;
474 :
475 : /* ORDER BY are costly to evaluate and are not necessary to establish */
476 : /* the layer extent. */
477 11 : size_t nOrderByPos = osSQLCommand.ifind(" ORDER BY ");
478 11 : if( osSQLCommand.ifind("SELECT ") == 0 &&
479 : nOrderByPos != std::string::npos &&
480 : osSQLCommand.ifind(" LIMIT ") == std::string::npos &&
481 : osSQLCommand.ifind(" UNION ") == std::string::npos &&
482 : osSQLCommand.ifind(" INTERSECT ") == std::string::npos &&
483 : osSQLCommand.ifind(" EXCEPT ") == std::string::npos)
484 : {
485 4 : osSQLCommand.resize(nOrderByPos);
486 :
487 4 : OGRLayer* poTmpLayer = poDS->ExecuteSQL(osSQLCommand.c_str(), NULL, NULL);
488 4 : if (poTmpLayer)
489 : {
490 4 : OGRErr eErr = poTmpLayer->GetExtent(psExtent, bForce);
491 4 : poDS->ReleaseResultSet(poTmpLayer);
492 4 : return eErr;
493 : }
494 : }
495 :
496 7 : OGRErr eErr = OGRSQLiteLayer::GetExtent(psExtent, bForce);
497 7 : if (eErr == OGRERR_NONE && poDS->GetUpdate() == FALSE)
498 7 : poDS->SetEnvelopeForSQL(osSQLBase, *psExtent);
499 7 : return eErr;
500 : }
|
