1 : /******************************************************************************
2 : * $Id: ogrsqliteselectlayer.cpp 24973 2012-09-25 17:34:00Z 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 24973 2012-09-25 17:34:00Z rouault $");
35 : /************************************************************************/
36 : /* OGRSQLiteSelectLayer() */
37 : /************************************************************************/
38 :
39 542 : OGRSQLiteSelectLayer::OGRSQLiteSelectLayer( OGRSQLiteDataSource *poDSIn,
40 : CPLString osSQLIn,
41 : sqlite3_stmt *hStmtIn,
42 : int bUseStatementForGetNextFeature,
43 542 : int bEmptyLayer )
44 :
45 : {
46 542 : poDS = poDSIn;
47 :
48 542 : iNextShapeId = 0;
49 542 : poFeatureDefn = NULL;
50 :
51 542 : std::set<CPLString> aosEmpty;
52 542 : BuildFeatureDefn( "SELECT", hStmtIn, aosEmpty );
53 :
54 542 : if( bUseStatementForGetNextFeature )
55 : {
56 387 : hStmt = hStmtIn;
57 387 : bDoStep = FALSE;
58 :
59 : // Try to extract SRS from first geometry
60 387 : if( !bEmptyLayer && osGeomColumn.size() != 0 )
61 : {
62 145 : int nRawColumns = sqlite3_column_count( hStmt );
63 678 : for( int iCol = 0; iCol < nRawColumns; iCol++ )
64 : {
65 : int nBytes;
66 627 : if( sqlite3_column_type( hStmt, iCol ) == SQLITE_BLOB &&
67 : strcmp(OGRSQLiteParamsUnquote(sqlite3_column_name( hStmt, iCol )).c_str(), osGeomColumn.c_str()) == 0 &&
68 : (nBytes = sqlite3_column_bytes( hStmt, iCol )) > 39 )
69 : {
70 94 : const GByte* pabyBlob = (const GByte*)sqlite3_column_blob( hStmt, iCol );
71 94 : int eByteOrder = pabyBlob[1];
72 182 : if( pabyBlob[0] == 0x00 &&
73 : (eByteOrder == wkbNDR || eByteOrder == wkbXDR) &&
74 88 : pabyBlob[38] == 0x7C )
75 : {
76 88 : memcpy(&nSRSId, pabyBlob + 2, 4);
77 : #ifdef CPL_LSB
78 88 : if( eByteOrder != wkbNDR)
79 0 : CPL_SWAP32PTR(&nSRSId);
80 : #else
81 : if( eByteOrder == wkbNDR)
82 : CPL_SWAP32PTR(&nSRSId);
83 : #endif
84 88 : poSRS = poDS->FetchSRS( nSRSId );
85 88 : if( poSRS != NULL )
86 82 : poSRS->Reference();
87 : }
88 94 : break;
89 : }
90 : }
91 : }
92 : }
93 : else
94 155 : sqlite3_finalize( hStmtIn );
95 :
96 542 : osSQLBase = osSQLIn;
97 542 : osSQLCurrent = osSQLIn;
98 542 : this->bEmptyLayer = bEmptyLayer;
99 542 : bSpatialFilterInSQL = TRUE;
100 542 : }
101 :
102 : /************************************************************************/
103 : /* GetNextFeature() */
104 : /************************************************************************/
105 :
106 1447 : OGRFeature *OGRSQLiteSelectLayer::GetNextFeature()
107 : {
108 1447 : if( bEmptyLayer )
109 116 : return NULL;
110 :
111 1331 : return OGRSQLiteLayer::GetNextFeature();
112 : }
113 :
114 : /************************************************************************/
115 : /* GetNextFeature() */
116 : /************************************************************************/
117 :
118 81 : int OGRSQLiteSelectLayer::GetFeatureCount( int bForce )
119 : {
120 81 : if( bEmptyLayer )
121 0 : return 0;
122 :
123 81 : if( m_poAttrQuery == NULL &&
124 : EQUALN(osSQLCurrent, "SELECT COUNT(*) FROM", strlen("SELECT COUNT(*) FROM")) &&
125 : osSQLCurrent.ifind(" GROUP BY ") == std::string::npos &&
126 : osSQLCurrent.ifind(" UNION ") == std::string::npos &&
127 : osSQLCurrent.ifind(" INTERSECT ") == std::string::npos &&
128 : osSQLCurrent.ifind(" EXCEPT ") == std::string::npos )
129 2 : return 1;
130 :
131 79 : if( m_poAttrQuery != NULL || (m_poFilterGeom != NULL && !bSpatialFilterInSQL) )
132 12 : return OGRLayer::GetFeatureCount(bForce);
133 :
134 67 : CPLString osFeatureCountSQL("SELECT COUNT(*) FROM (");
135 67 : osFeatureCountSQL += osSQLCurrent;
136 67 : osFeatureCountSQL += ")";
137 :
138 67 : CPLDebug("SQLITE", "Running %s", osFeatureCountSQL.c_str());
139 :
140 : /* -------------------------------------------------------------------- */
141 : /* Execute. */
142 : /* -------------------------------------------------------------------- */
143 67 : char *pszErrMsg = NULL;
144 : char **papszResult;
145 : int nRowCount, nColCount;
146 67 : int nResult = -1;
147 :
148 67 : if( sqlite3_get_table( poDS->GetDB(), osFeatureCountSQL, &papszResult,
149 : &nRowCount, &nColCount, &pszErrMsg ) != SQLITE_OK )
150 : {
151 1 : CPLDebug("SQLITE", "Error: %s", pszErrMsg);
152 1 : sqlite3_free(pszErrMsg);
153 1 : return OGRLayer::GetFeatureCount(bForce);
154 : }
155 :
156 66 : if( nRowCount == 1 && nColCount == 1 )
157 : {
158 66 : nResult = atoi(papszResult[1]);
159 : }
160 :
161 66 : sqlite3_free_table( papszResult );
162 :
163 66 : return nResult;
164 : }
165 :
166 : /************************************************************************/
167 : /* ResetStatement() */
168 : /************************************************************************/
169 :
170 123 : OGRErr OGRSQLiteSelectLayer::ResetStatement()
171 :
172 : {
173 : int rc;
174 :
175 123 : ClearStatement();
176 :
177 123 : iNextShapeId = 0;
178 123 : bDoStep = TRUE;
179 :
180 : #ifdef DEBUG
181 123 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osSQLCurrent.c_str() );
182 : #endif
183 :
184 : rc = sqlite3_prepare( poDS->GetDB(), osSQLCurrent, osSQLCurrent.size(),
185 123 : &hStmt, NULL );
186 :
187 123 : if( rc == SQLITE_OK )
188 : {
189 123 : return OGRERR_NONE;
190 : }
191 : else
192 : {
193 : CPLError( CE_Failure, CPLE_AppDefined,
194 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
195 0 : osSQLCurrent.c_str(), sqlite3_errmsg(poDS->GetDB()) );
196 0 : hStmt = NULL;
197 0 : return OGRERR_FAILURE;
198 : }
199 : }
200 :
201 : /************************************************************************/
202 : /* SetSpatialFilter() */
203 : /************************************************************************/
204 :
205 25 : void OGRSQLiteSelectLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
206 :
207 : {
208 25 : if( InstallFilter( poGeomIn ) )
209 : {
210 17 : bSpatialFilterInSQL = RebuildSQLWithSpatialClause();
211 :
212 17 : ResetReading();
213 : }
214 25 : }
215 :
216 : /************************************************************************/
217 : /* GetBaseLayer() */
218 : /************************************************************************/
219 :
220 17 : OGRSQLiteLayer* OGRSQLiteSelectLayer::GetBaseLayer(size_t& i)
221 : {
222 17 : char** papszTokens = CSLTokenizeString(osSQLBase.c_str());
223 17 : int bCanInsertSpatialFilter = TRUE;
224 17 : int nCountSelect = 0, nCountFrom = 0, nCountWhere = 0;
225 :
226 110 : for(int iToken = 0; papszTokens[iToken] != NULL; iToken++)
227 : {
228 93 : if (EQUAL(papszTokens[iToken], "SELECT"))
229 17 : nCountSelect ++;
230 76 : else if (EQUAL(papszTokens[iToken], "FROM"))
231 17 : nCountFrom ++;
232 59 : else if (EQUAL(papszTokens[iToken], "WHERE"))
233 4 : nCountWhere ++;
234 220 : else if (EQUAL(papszTokens[iToken], "UNION") ||
235 55 : EQUAL(papszTokens[iToken], "JOIN") ||
236 55 : EQUAL(papszTokens[iToken], "INTERSECT") ||
237 55 : EQUAL(papszTokens[iToken], "EXCEPT"))
238 : {
239 0 : bCanInsertSpatialFilter = FALSE;
240 : }
241 : }
242 17 : CSLDestroy(papszTokens);
243 :
244 17 : if (!(bCanInsertSpatialFilter && nCountSelect == 1 && nCountFrom == 1 && nCountWhere <= 1))
245 : {
246 0 : CPLDebug("SQLITE", "SQL expression too complex to analyse");
247 0 : return NULL;
248 : }
249 :
250 17 : size_t nFromPos = osSQLBase.ifind(" from ");
251 17 : if (nFromPos == std::string::npos)
252 : {
253 0 : return NULL;
254 : }
255 :
256 17 : int bInSingleQuotes = (osSQLBase[nFromPos + 6] == '\'');
257 17 : CPLString osBaseLayerName;
258 197 : for( i = nFromPos + 6 + (bInSingleQuotes ? 1 : 0);
259 : i < osSQLBase.size(); i++ )
260 : {
261 188 : if (osSQLBase[i] == '\'' && i + 1 < osSQLBase.size() &&
262 : osSQLBase[i + 1] == '\'' )
263 : {
264 0 : osBaseLayerName += osSQLBase[i];
265 0 : i++;
266 : }
267 188 : else if (osSQLBase[i] == '\'' && bInSingleQuotes)
268 : {
269 2 : i++;
270 2 : break;
271 : }
272 186 : else if (osSQLBase[i] == ' ' && !bInSingleQuotes)
273 6 : break;
274 : else
275 180 : osBaseLayerName += osSQLBase[i];
276 : }
277 :
278 : OGRSQLiteLayer* poUnderlyingLayer =
279 17 : (OGRSQLiteLayer*) poDS->GetLayerByName(osBaseLayerName);
280 :
281 17 : if( poUnderlyingLayer == NULL &&
282 : strchr(osBaseLayerName, '(') == NULL &&
283 : osGeomColumn.size() != 0 )
284 : {
285 1 : CPLString osNewUnderlyingTableName;
286 : osNewUnderlyingTableName.Printf("%s(%s)",
287 : osBaseLayerName.c_str(),
288 1 : osGeomColumn.c_str());
289 : poUnderlyingLayer =
290 1 : (OGRSQLiteLayer*) poDS->GetLayerByName(osNewUnderlyingTableName);
291 : }
292 :
293 27 : if( poUnderlyingLayer != NULL && poSRS != NULL &&
294 5 : poUnderlyingLayer->GetSpatialRef() != NULL &&
295 5 : poSRS != poUnderlyingLayer->GetSpatialRef() &&
296 0 : !poSRS->IsSame(poUnderlyingLayer->GetSpatialRef()) )
297 : {
298 0 : CPLDebug("SQLITE", "Result layer and base layer don't have the same SRS.");
299 0 : return NULL;
300 : }
301 :
302 17 : return poUnderlyingLayer;
303 : }
304 :
305 : /************************************************************************/
306 : /* RebuildSQLWithSpatialClause() */
307 : /************************************************************************/
308 :
309 17 : int OGRSQLiteSelectLayer::RebuildSQLWithSpatialClause()
310 :
311 : {
312 17 : osSQLCurrent = osSQLBase;
313 :
314 17 : if (m_poFilterGeom == NULL)
315 : {
316 4 : return TRUE;
317 : }
318 :
319 13 : size_t i = 0;
320 13 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
321 13 : if (poBaseLayer == NULL)
322 : {
323 0 : CPLDebug("SQLITE", "Cannot find base layer");
324 0 : return FALSE;
325 : }
326 :
327 13 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(m_poFilterGeom);
328 13 : if (osSpatialWhere.size() == 0)
329 : {
330 4 : CPLDebug("SQLITE", "Cannot get spatial where clause");
331 4 : return FALSE;
332 : }
333 :
334 21 : while (i < osSQLBase.size() && osSQLBase[i] == ' ')
335 3 : i ++;
336 :
337 9 : if (i < osSQLBase.size() && EQUALN(osSQLBase.c_str() + i, "WHERE ", 6))
338 : {
339 2 : osSQLCurrent = osSQLBase.substr(0, i + 6);
340 2 : osSQLCurrent += osSpatialWhere;
341 2 : osSQLCurrent += " AND (";
342 :
343 2 : size_t nEndOfWhere = osSQLBase.ifind(" GROUP ");
344 2 : if (nEndOfWhere == std::string::npos)
345 2 : nEndOfWhere = osSQLBase.ifind(" ORDER ");
346 2 : if (nEndOfWhere == std::string::npos)
347 1 : nEndOfWhere = osSQLBase.ifind(" LIMIT ");
348 :
349 2 : if (nEndOfWhere == std::string::npos)
350 : {
351 1 : osSQLCurrent += osSQLBase.substr(i + 6);
352 1 : osSQLCurrent += ")";
353 : }
354 : else
355 : {
356 1 : osSQLCurrent += osSQLBase.substr(i + 6, nEndOfWhere - (i + 6));
357 1 : osSQLCurrent += ")";
358 1 : osSQLCurrent += osSQLBase.substr(nEndOfWhere);
359 : }
360 : }
361 7 : else if (i < osSQLBase.size() &&
362 : (EQUALN(osSQLBase.c_str() + i, "GROUP ", 6) ||
363 : EQUALN(osSQLBase.c_str() + i, "ORDER ", 6) ||
364 : EQUALN(osSQLBase.c_str() + i, "LIMIT ", 6)))
365 : {
366 1 : osSQLCurrent = osSQLBase.substr(0, i);
367 1 : osSQLCurrent += " WHERE ";
368 1 : osSQLCurrent += osSpatialWhere;
369 1 : osSQLCurrent += " ";
370 1 : osSQLCurrent += osSQLBase.substr(i);
371 : }
372 6 : else if (i == osSQLBase.size())
373 : {
374 6 : osSQLCurrent = osSQLBase.substr(0, i);
375 6 : osSQLCurrent += " WHERE ";
376 6 : osSQLCurrent += osSpatialWhere;
377 : }
378 : else
379 : {
380 0 : CPLDebug("SQLITE", "SQL expression too complex for the driver to insert spatial filter in it");
381 0 : return FALSE;
382 : }
383 :
384 9 : return TRUE;
385 : }
386 :
387 : /************************************************************************/
388 : /* TestCapability() */
389 : /************************************************************************/
390 :
391 40 : int OGRSQLiteSelectLayer::TestCapability( const char * pszCap )
392 :
393 : {
394 40 : if (EQUAL(pszCap,OLCFastSpatialFilter))
395 : {
396 8 : if (osSQLCurrent != osSQLBase)
397 4 : return TRUE;
398 :
399 4 : size_t i = 0;
400 4 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
401 4 : if (poBaseLayer == NULL)
402 : {
403 0 : CPLDebug("SQLITE", "Cannot find base layer");
404 0 : return FALSE;
405 : }
406 :
407 4 : OGRPolygon oFakePoly;
408 4 : const char* pszWKT = "POLYGON((0 0,0 1,1 1,1 0,0 0))";
409 4 : oFakePoly.importFromWkt((char**) &pszWKT);
410 4 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(&oFakePoly);
411 :
412 4 : return osSpatialWhere.size() != 0;
413 : }
414 : else
415 32 : return OGRSQLiteLayer::TestCapability( pszCap );
416 : }
417 :
418 : /************************************************************************/
419 : /* GetExtent() */
420 : /************************************************************************/
421 :
422 14 : OGRErr OGRSQLiteSelectLayer::GetExtent(OGREnvelope *psExtent, int bForce)
423 : {
424 14 : if (GetGeomType() == wkbNone)
425 0 : return OGRERR_FAILURE;
426 :
427 : /* Caching of extent by SQL string is interesting to speed-up the */
428 : /* establishment of the WFS GetCapabilities document for a MapServer mapfile */
429 : /* which has several layers, only differing by scale rules */
430 14 : const OGREnvelope* psCachedExtent = poDS->GetEnvelopeFromSQL(osSQLBase);
431 14 : if (psCachedExtent)
432 : {
433 3 : memcpy(psExtent, psCachedExtent, sizeof(*psCachedExtent));
434 3 : return OGRERR_NONE;
435 : }
436 :
437 11 : CPLString osSQLCommand = osSQLBase;
438 :
439 : /* ORDER BY are costly to evaluate and are not necessary to establish */
440 : /* the layer extent. */
441 11 : size_t nOrderByPos = osSQLCommand.ifind(" ORDER BY ");
442 11 : if( osSQLCommand.ifind("SELECT ") == 0 &&
443 : nOrderByPos != std::string::npos &&
444 : osSQLCommand.ifind(" LIMIT ") == std::string::npos &&
445 : osSQLCommand.ifind(" UNION ") == std::string::npos &&
446 : osSQLCommand.ifind(" INTERSECT ") == std::string::npos &&
447 : osSQLCommand.ifind(" EXCEPT ") == std::string::npos)
448 : {
449 4 : osSQLCommand.resize(nOrderByPos);
450 :
451 4 : OGRLayer* poTmpLayer = poDS->ExecuteSQL(osSQLCommand.c_str(), NULL, NULL);
452 4 : if (poTmpLayer)
453 : {
454 4 : OGRErr eErr = poTmpLayer->GetExtent(psExtent, bForce);
455 4 : poDS->ReleaseResultSet(poTmpLayer);
456 4 : return eErr;
457 : }
458 : }
459 :
460 7 : OGRErr eErr = OGRSQLiteLayer::GetExtent(psExtent, bForce);
461 7 : if (eErr == OGRERR_NONE && poDS->GetUpdate() == FALSE)
462 7 : poDS->SetEnvelopeForSQL(osSQLBase, *psExtent);
463 7 : return eErr;
464 : }
|
