1 : /******************************************************************************
2 : * $Id: ogrsqliteselectlayer.cpp 23957 2012-02-12 13:04:25Z 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 23957 2012-02-12 13:04:25Z rouault $");
35 : /************************************************************************/
36 : /* OGRSQLiteSelectLayer() */
37 : /************************************************************************/
38 :
39 366 : OGRSQLiteSelectLayer::OGRSQLiteSelectLayer( OGRSQLiteDataSource *poDSIn,
40 : CPLString osSQLIn,
41 366 : sqlite3_stmt *hStmtIn )
42 :
43 : {
44 366 : poDS = poDSIn;
45 :
46 366 : iNextShapeId = 0;
47 366 : nSRSId = -1;
48 366 : poFeatureDefn = NULL;
49 :
50 366 : BuildFeatureDefn( "SELECT", hStmtIn );
51 :
52 366 : sqlite3_finalize( hStmtIn );
53 :
54 366 : osSQLBase = osSQLIn;
55 366 : osSQLCurrent = osSQLIn;
56 366 : }
57 :
58 : /************************************************************************/
59 : /* ~OGRSQLiteSelectLayer() */
60 : /************************************************************************/
61 :
62 366 : OGRSQLiteSelectLayer::~OGRSQLiteSelectLayer()
63 :
64 : {
65 366 : }
66 :
67 : /************************************************************************/
68 : /* ResetStatement() */
69 : /************************************************************************/
70 :
71 360 : OGRErr OGRSQLiteSelectLayer::ResetStatement()
72 :
73 : {
74 : int rc;
75 :
76 360 : ClearStatement();
77 :
78 360 : iNextShapeId = 0;
79 :
80 : #ifdef DEBUG
81 360 : CPLDebug( "OGR_SQLITE", "prepare(%s)", osSQLCurrent.c_str() );
82 : #endif
83 :
84 : rc = sqlite3_prepare( poDS->GetDB(), osSQLCurrent, osSQLCurrent.size(),
85 360 : &hStmt, NULL );
86 :
87 360 : if( rc == SQLITE_OK )
88 : {
89 360 : return OGRERR_NONE;
90 : }
91 : else
92 : {
93 : CPLError( CE_Failure, CPLE_AppDefined,
94 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
95 0 : osSQLCurrent.c_str(), sqlite3_errmsg(poDS->GetDB()) );
96 0 : hStmt = NULL;
97 0 : return OGRERR_FAILURE;
98 : }
99 : }
100 :
101 : /************************************************************************/
102 : /* SetSpatialFilter() */
103 : /************************************************************************/
104 :
105 8 : void OGRSQLiteSelectLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
106 :
107 : {
108 8 : if( InstallFilter( poGeomIn ) )
109 : {
110 8 : RebuildSQL();
111 :
112 8 : ResetReading();
113 : }
114 8 : }
115 :
116 : /************************************************************************/
117 : /* GetBaseLayer() */
118 : /************************************************************************/
119 :
120 16 : OGRSQLiteLayer* OGRSQLiteSelectLayer::GetBaseLayer(size_t& i)
121 : {
122 16 : char** papszTokens = CSLTokenizeString(osSQLBase.c_str());
123 16 : int bCanInsertSpatialFilter = TRUE;
124 16 : int nCountSelect = 0, nCountFrom = 0, nCountWhere = 0;
125 :
126 128 : for(int iToken = 0; papszTokens[iToken] != NULL; iToken++)
127 : {
128 112 : if (EQUAL(papszTokens[iToken], "SELECT"))
129 16 : nCountSelect ++;
130 96 : else if (EQUAL(papszTokens[iToken], "FROM"))
131 16 : nCountFrom ++;
132 80 : else if (EQUAL(papszTokens[iToken], "WHERE"))
133 8 : nCountWhere ++;
134 288 : else if (EQUAL(papszTokens[iToken], "UNION") ||
135 72 : EQUAL(papszTokens[iToken], "JOIN") ||
136 72 : EQUAL(papszTokens[iToken], "INTERSECT") ||
137 72 : EQUAL(papszTokens[iToken], "EXCEPT"))
138 : {
139 0 : bCanInsertSpatialFilter = FALSE;
140 : }
141 : }
142 16 : CSLDestroy(papszTokens);
143 :
144 16 : if (!(bCanInsertSpatialFilter && nCountSelect == 1 && nCountFrom == 1 && nCountWhere <= 1))
145 : {
146 0 : CPLDebug("SQLITE", "SQL expression too complex to analyse");
147 0 : return NULL;
148 : }
149 :
150 16 : size_t nFromPos = osSQLBase.ifind(" from ");
151 16 : if (nFromPos == std::string::npos)
152 : {
153 0 : return NULL;
154 : }
155 :
156 16 : int bInSingleQuotes = (osSQLBase[nFromPos + 6] == '\'');
157 16 : CPLString osBaseLayerName;
158 304 : for( i = nFromPos + 6 + (bInSingleQuotes ? 1 : 0);
159 : i < osSQLBase.size(); i++ )
160 : {
161 304 : if (osSQLBase[i] == '\'' && i + 1 < osSQLBase.size() &&
162 : osSQLBase[i + 1] == '\'' )
163 : {
164 0 : osBaseLayerName += osSQLBase[i];
165 0 : i++;
166 : }
167 304 : else if (osSQLBase[i] == '\'' && bInSingleQuotes)
168 : {
169 4 : i++;
170 4 : break;
171 : }
172 300 : else if (osSQLBase[i] == ' ' && !bInSingleQuotes)
173 12 : break;
174 : else
175 288 : osBaseLayerName += osSQLBase[i];
176 : }
177 :
178 16 : return (OGRSQLiteLayer*) poDS->GetLayerByName(osBaseLayerName);
179 : }
180 :
181 : /************************************************************************/
182 : /* RebuildSQL() */
183 : /************************************************************************/
184 :
185 8 : void OGRSQLiteSelectLayer::RebuildSQL()
186 :
187 : {
188 8 : osSQLCurrent = osSQLBase;
189 :
190 8 : if (m_poFilterGeom == NULL)
191 : {
192 0 : return;
193 : }
194 :
195 8 : size_t i = 0;
196 8 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
197 8 : if (poBaseLayer == NULL)
198 : {
199 0 : CPLDebug("SQLITE", "Cannot find base layer");
200 0 : return;
201 : }
202 :
203 8 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(m_poFilterGeom);
204 8 : if (osSpatialWhere.size() == 0)
205 : {
206 0 : CPLDebug("SQLITE", "Cannot get spatial where clause");
207 : return;
208 : }
209 :
210 22 : while (i < osSQLBase.size() && osSQLBase[i] == ' ')
211 6 : i ++;
212 :
213 8 : if (i < osSQLBase.size() && EQUALN(osSQLBase.c_str() + i, "WHERE ", 6))
214 : {
215 4 : osSQLCurrent = osSQLBase.substr(0, i + 6);
216 4 : osSQLCurrent += osSpatialWhere;
217 4 : osSQLCurrent += " AND (";
218 :
219 4 : size_t nEndOfWhere = osSQLBase.ifind(" GROUP ");
220 4 : if (nEndOfWhere == std::string::npos)
221 4 : nEndOfWhere = osSQLBase.ifind(" ORDER ");
222 4 : if (nEndOfWhere == std::string::npos)
223 2 : nEndOfWhere = osSQLBase.ifind(" LIMIT ");
224 :
225 4 : if (nEndOfWhere == std::string::npos)
226 : {
227 2 : osSQLCurrent += osSQLBase.substr(i + 6);
228 2 : osSQLCurrent += ")";
229 : }
230 : else
231 : {
232 2 : osSQLCurrent += osSQLBase.substr(i + 6, nEndOfWhere - (i + 6));
233 2 : osSQLCurrent += ")";
234 2 : osSQLCurrent += osSQLBase.substr(nEndOfWhere);
235 : }
236 : }
237 4 : else if (i < osSQLBase.size() &&
238 : (EQUALN(osSQLBase.c_str() + i, "GROUP ", 6) ||
239 : EQUALN(osSQLBase.c_str() + i, "ORDER ", 6) ||
240 : EQUALN(osSQLBase.c_str() + i, "LIMIT ", 6)))
241 : {
242 2 : osSQLCurrent = osSQLBase.substr(0, i);
243 2 : osSQLCurrent += " WHERE ";
244 2 : osSQLCurrent += osSpatialWhere;
245 2 : osSQLCurrent += " ";
246 2 : osSQLCurrent += osSQLBase.substr(i);
247 : }
248 2 : else if (i == osSQLBase.size())
249 : {
250 2 : osSQLCurrent = osSQLBase.substr(0, i);
251 2 : osSQLCurrent += " WHERE ";
252 2 : osSQLCurrent += osSpatialWhere;
253 : }
254 : else
255 : {
256 0 : CPLDebug("SQLITE", "SQL expression too complex for the driver to insert spatial filter in it");
257 0 : }
258 : }
259 :
260 : /************************************************************************/
261 : /* TestCapability() */
262 : /************************************************************************/
263 :
264 16 : int OGRSQLiteSelectLayer::TestCapability( const char * pszCap )
265 :
266 : {
267 16 : if (EQUAL(pszCap,OLCFastSpatialFilter))
268 : {
269 16 : if (osSQLCurrent != osSQLBase)
270 8 : return TRUE;
271 :
272 8 : size_t i = 0;
273 8 : OGRSQLiteLayer* poBaseLayer = GetBaseLayer(i);
274 8 : if (poBaseLayer == NULL)
275 : {
276 0 : CPLDebug("SQLITE", "Cannot find base layer");
277 0 : return FALSE;
278 : }
279 :
280 8 : OGRPolygon oFakePoly;
281 8 : const char* pszWKT = "POLYGON((0 0,0 1,1 1,1 0,0 0))";
282 8 : oFakePoly.importFromWkt((char**) &pszWKT);
283 8 : CPLString osSpatialWhere = poBaseLayer->GetSpatialWhere(&oFakePoly);
284 :
285 8 : return osSpatialWhere.size() != 0;
286 : }
287 : else
288 0 : return OGRSQLiteLayer::TestCapability( pszCap );
289 : }
290 :
291 : /************************************************************************/
292 : /* GetExtent() */
293 : /************************************************************************/
294 :
295 20 : OGRErr OGRSQLiteSelectLayer::GetExtent(OGREnvelope *psExtent, int bForce)
296 : {
297 20 : if (GetGeomType() == wkbNone)
298 0 : return OGRERR_FAILURE;
299 :
300 : /* Caching of extent by SQL string is interesting to speed-up the */
301 : /* establishment of the WFS GetCapabilities document for a MapServer mapfile */
302 : /* which has several layers, only differing by scale rules */
303 20 : const OGREnvelope* psCachedExtent = poDS->GetEnvelopeFromSQL(osSQLBase);
304 20 : if (psCachedExtent)
305 : {
306 6 : memcpy(psExtent, psCachedExtent, sizeof(*psCachedExtent));
307 6 : return OGRERR_NONE;
308 : }
309 :
310 14 : CPLString osSQLCommand = osSQLBase;
311 :
312 : /* ORDER BY are costly to evaluate and are not necessary to establish */
313 : /* the layer extent. */
314 14 : size_t nOrderByPos = osSQLCommand.ifind(" ORDER BY ");
315 14 : if( osSQLCommand.ifind("SELECT ") == 0 &&
316 : nOrderByPos != std::string::npos &&
317 : osSQLCommand.ifind(" LIMIT ") == std::string::npos &&
318 : osSQLCommand.ifind(" UNION ") == std::string::npos &&
319 : osSQLCommand.ifind(" INTERSECT ") == std::string::npos &&
320 : osSQLCommand.ifind(" EXCEPT ") == std::string::npos)
321 : {
322 8 : osSQLCommand.resize(nOrderByPos);
323 :
324 8 : OGRLayer* poTmpLayer = poDS->ExecuteSQL(osSQLCommand.c_str(), NULL, NULL);
325 8 : if (poTmpLayer)
326 : {
327 8 : OGRErr eErr = poTmpLayer->GetExtent(psExtent, bForce);
328 8 : poDS->ReleaseResultSet(poTmpLayer);
329 8 : return eErr;
330 : }
331 : }
332 :
333 6 : OGRErr eErr = OGRSQLiteLayer::GetExtent(psExtent, bForce);
334 6 : if (eErr == OGRERR_NONE && poDS->GetUpdate() == FALSE)
335 6 : poDS->SetEnvelopeForSQL(osSQLBase, *psExtent);
336 6 : return eErr;
337 : }
|
