1 : /******************************************************************************
2 : * $Id: ogrsqliteviewlayer.cpp 23015 2011-08-31 19:48:00Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRSQLiteViewLayer class, access to an existing spatialite view.
6 : * Author: Even Rouault, <even dot rouault at mines dash paris dot org>
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2011, 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 "cpl_conv.h"
31 : #include "cpl_string.h"
32 : #include "ogr_sqlite.h"
33 : #include <string>
34 :
35 : CPL_CVSID("$Id: ogrsqliteviewlayer.cpp 23015 2011-08-31 19:48:00Z rouault $");
36 :
37 : /************************************************************************/
38 : /* OGRSQLiteViewLayer() */
39 : /************************************************************************/
40 :
41 3 : OGRSQLiteViewLayer::OGRSQLiteViewLayer( OGRSQLiteDataSource *poDSIn )
42 :
43 : {
44 3 : poDS = poDSIn;
45 :
46 3 : iNextShapeId = 0;
47 :
48 3 : nSRSId = -1;
49 :
50 3 : poFeatureDefn = NULL;
51 3 : pszEscapedTableName = NULL;
52 3 : pszEscapedUnderlyingTableName = NULL;
53 :
54 3 : bHasCheckedSpatialIndexTable = FALSE;
55 3 : }
56 :
57 : /************************************************************************/
58 : /* ~OGRSQLiteViewLayer() */
59 : /************************************************************************/
60 :
61 3 : OGRSQLiteViewLayer::~OGRSQLiteViewLayer()
62 :
63 : {
64 3 : ClearStatement();
65 3 : CPLFree(pszEscapedTableName);
66 3 : CPLFree(pszEscapedUnderlyingTableName);
67 3 : }
68 :
69 : /************************************************************************/
70 : /* Initialize() */
71 : /************************************************************************/
72 :
73 3 : CPLErr OGRSQLiteViewLayer::Initialize( const char *pszViewName,
74 : const char *pszViewGeometry,
75 : const char *pszViewRowid,
76 : const char *pszUnderlyingTableName,
77 : const char *pszUnderlyingGeometryColumn,
78 : int bSpatialiteLoaded)
79 :
80 : {
81 : int rc;
82 3 : sqlite3 *hDB = poDS->GetDB();
83 :
84 3 : OGRSQLiteLayer* poUnderlyingLayer = (OGRSQLiteLayer*) poDS->GetLayerByName(pszUnderlyingTableName);
85 3 : if (poUnderlyingLayer == NULL)
86 : {
87 : CPLError(CE_Failure, CPLE_AppDefined,
88 : "Cannot find underlying layer %s for view %s",
89 0 : pszUnderlyingTableName, pszViewName);
90 0 : return CE_Failure;
91 : }
92 3 : if ( !poUnderlyingLayer->IsTableLayer() )
93 : {
94 : CPLError(CE_Failure, CPLE_AppDefined,
95 : "Underlying layer %s for view %s is not a regular table",
96 0 : pszUnderlyingTableName, pszViewName);
97 0 : return CE_Failure;
98 : }
99 :
100 3 : const char* pszRealUnderlyingGeometryColumn = poUnderlyingLayer->GetGeometryColumn();
101 3 : if ( pszRealUnderlyingGeometryColumn == NULL ||
102 : !EQUAL(pszRealUnderlyingGeometryColumn, pszUnderlyingGeometryColumn) )
103 : {
104 : CPLError(CE_Failure, CPLE_AppDefined,
105 : "Underlying layer %s for view %s has not expected geometry column name (%s instead of %s)",
106 : pszUnderlyingTableName, pszViewName,
107 : pszRealUnderlyingGeometryColumn ? pszRealUnderlyingGeometryColumn : "(null)",
108 0 : pszUnderlyingGeometryColumn);
109 0 : return CE_Failure;
110 : }
111 :
112 3 : osGeomColumn = pszViewGeometry;
113 3 : eGeomFormat = OSGF_SpatiaLite;
114 :
115 3 : CPLFree( pszFIDColumn );
116 3 : pszFIDColumn = CPLStrdup( pszViewRowid );
117 :
118 6 : osUnderlyingTableName = pszUnderlyingTableName;
119 6 : osUnderlyingGeometryColumn = pszUnderlyingGeometryColumn;
120 :
121 3 : poSRS = poUnderlyingLayer->GetSpatialRef();
122 3 : if (poSRS)
123 3 : poSRS->Reference();
124 :
125 3 : this->bHasSpatialIndex = poUnderlyingLayer->HasSpatialIndex();
126 3 : this->bSpatialiteLoaded = bSpatialiteLoaded;
127 : //this->bHasM = bHasM;
128 :
129 3 : pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszViewName));
130 6 : pszEscapedUnderlyingTableName = CPLStrdup(OGRSQLiteEscape(pszUnderlyingTableName));
131 :
132 : CPLErr eErr;
133 3 : sqlite3_stmt *hColStmt = NULL;
134 : const char *pszSQL;
135 :
136 : /* -------------------------------------------------------------------- */
137 : /* Get the column definitions for this table. */
138 : /* -------------------------------------------------------------------- */
139 3 : hColStmt = NULL;
140 3 : pszSQL = CPLSPrintf( "SELECT %s, * FROM '%s' LIMIT 1", pszFIDColumn, pszEscapedTableName );
141 :
142 3 : rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
143 3 : if( rc != SQLITE_OK )
144 : {
145 : CPLError( CE_Failure, CPLE_AppDefined,
146 : "Unable to query table %s for column definitions : %s.",
147 0 : pszViewName, sqlite3_errmsg(hDB) );
148 :
149 0 : return CE_Failure;
150 : }
151 :
152 3 : rc = sqlite3_step( hColStmt );
153 3 : if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
154 : {
155 : CPLError( CE_Failure, CPLE_AppDefined,
156 : "In Initialize(): sqlite3_step(%s):\n %s",
157 0 : pszSQL, sqlite3_errmsg(hDB) );
158 0 : sqlite3_finalize( hColStmt );
159 0 : return CE_Failure;
160 : }
161 :
162 : /* -------------------------------------------------------------------- */
163 : /* Collect the rest of the fields. */
164 : /* -------------------------------------------------------------------- */
165 3 : eErr = BuildFeatureDefn( pszViewName, hColStmt );
166 3 : sqlite3_finalize( hColStmt );
167 :
168 3 : if( eErr != CE_None )
169 0 : return eErr;
170 :
171 : /* -------------------------------------------------------------------- */
172 : /* Set the geometry type if we know it. */
173 : /* -------------------------------------------------------------------- */
174 3 : poFeatureDefn->SetGeomType( poUnderlyingLayer->GetGeomType() );
175 :
176 3 : return CE_None;
177 : }
178 :
179 : /************************************************************************/
180 : /* ResetStatement() */
181 : /************************************************************************/
182 :
183 2 : OGRErr OGRSQLiteViewLayer::ResetStatement()
184 :
185 : {
186 : int rc;
187 2 : CPLString osSQL;
188 :
189 2 : ClearStatement();
190 :
191 2 : iNextShapeId = 0;
192 :
193 : osSQL.Printf( "SELECT %s, * FROM '%s' %s",
194 : pszFIDColumn,
195 : pszEscapedTableName,
196 2 : osWHERE.c_str() );
197 :
198 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
199 2 : &hStmt, NULL );
200 :
201 2 : if( rc == SQLITE_OK )
202 : {
203 2 : return OGRERR_NONE;
204 : }
205 : else
206 : {
207 : CPLError( CE_Failure, CPLE_AppDefined,
208 : "In ResetStatement(): sqlite3_prepare(%s):\n %s",
209 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
210 0 : hStmt = NULL;
211 0 : return OGRERR_FAILURE;
212 0 : }
213 : }
214 :
215 :
216 : /************************************************************************/
217 : /* GetFeature() */
218 : /************************************************************************/
219 :
220 1 : OGRFeature *OGRSQLiteViewLayer::GetFeature( long nFeatureId )
221 :
222 : {
223 : /* -------------------------------------------------------------------- */
224 : /* If we don't have an explicit FID column, just read through */
225 : /* the result set iteratively to find our target. */
226 : /* -------------------------------------------------------------------- */
227 1 : if( pszFIDColumn == NULL )
228 0 : return OGRSQLiteLayer::GetFeature( nFeatureId );
229 :
230 : /* -------------------------------------------------------------------- */
231 : /* Setup explicit query statement to fetch the record we want. */
232 : /* -------------------------------------------------------------------- */
233 1 : CPLString osSQL;
234 : int rc;
235 :
236 1 : ClearStatement();
237 :
238 1 : iNextShapeId = nFeatureId;
239 :
240 : osSQL.Printf( "SELECT %s, * FROM '%s' WHERE \"%s\" = %d",
241 : pszFIDColumn,
242 : pszEscapedTableName,
243 1 : pszFIDColumn, (int) nFeatureId );
244 :
245 1 : CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
246 :
247 : rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
248 1 : &hStmt, NULL );
249 1 : if( rc != SQLITE_OK )
250 : {
251 : CPLError( CE_Failure, CPLE_AppDefined,
252 : "In GetFeature(): sqlite3_prepare(%s):\n %s",
253 0 : osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
254 :
255 0 : return NULL;
256 : }
257 : /* -------------------------------------------------------------------- */
258 : /* Get the feature if possible. */
259 : /* -------------------------------------------------------------------- */
260 1 : OGRFeature *poFeature = NULL;
261 :
262 1 : poFeature = GetNextRawFeature();
263 :
264 1 : ResetReading();
265 :
266 1 : return poFeature;
267 : }
268 :
269 : /************************************************************************/
270 : /* SetAttributeFilter() */
271 : /************************************************************************/
272 :
273 2 : OGRErr OGRSQLiteViewLayer::SetAttributeFilter( const char *pszQuery )
274 :
275 : {
276 2 : if( pszQuery == NULL )
277 0 : osQuery = "";
278 : else
279 2 : osQuery = pszQuery;
280 :
281 2 : BuildWhere();
282 :
283 2 : ResetReading();
284 :
285 2 : return OGRERR_NONE;
286 : }
287 :
288 :
289 : /************************************************************************/
290 : /* SetSpatialFilter() */
291 : /************************************************************************/
292 :
293 2 : void OGRSQLiteViewLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
294 :
295 : {
296 2 : if( InstallFilter( poGeomIn ) )
297 : {
298 2 : BuildWhere();
299 :
300 2 : ResetReading();
301 : }
302 2 : }
303 :
304 : /************************************************************************/
305 : /* BuildWhere() */
306 : /* */
307 : /* Build the WHERE statement appropriate to the current set of */
308 : /* criteria (spatial and attribute queries). */
309 : /************************************************************************/
310 :
311 4 : void OGRSQLiteViewLayer::BuildWhere()
312 :
313 : {
314 4 : osWHERE = "";
315 :
316 6 : if( m_poFilterGeom != NULL && bHasSpatialIndex )
317 : {
318 1 : OGREnvelope sEnvelope;
319 :
320 1 : m_poFilterGeom->getEnvelope( &sEnvelope );
321 :
322 : /* We first check that the spatial index table exists */
323 1 : if (!bHasCheckedSpatialIndexTable)
324 : {
325 1 : bHasCheckedSpatialIndexTable = TRUE;
326 : char **papszResult;
327 : int nRowCount, nColCount;
328 1 : char *pszErrMsg = NULL;
329 :
330 1 : CPLString osSQL;
331 : osSQL.Printf("SELECT name FROM sqlite_master "
332 : "WHERE name='idx_%s_%s'",
333 1 : pszEscapedUnderlyingTableName, osUnderlyingGeometryColumn.c_str());
334 :
335 : int rc = sqlite3_get_table( poDS->GetDB(), osSQL.c_str(),
336 : &papszResult, &nRowCount,
337 1 : &nColCount, &pszErrMsg );
338 :
339 1 : if( rc != SQLITE_OK )
340 : {
341 : CPLError( CE_Failure, CPLE_AppDefined, "Error: %s",
342 0 : pszErrMsg );
343 0 : sqlite3_free( pszErrMsg );
344 0 : bHasSpatialIndex = FALSE;
345 : }
346 : else
347 : {
348 1 : if (nRowCount != 1)
349 : {
350 0 : bHasSpatialIndex = FALSE;
351 : }
352 :
353 1 : sqlite3_free_table(papszResult);
354 1 : }
355 : }
356 :
357 1 : if (bHasSpatialIndex)
358 : {
359 : osWHERE.Printf("WHERE %s IN ( SELECT pkid FROM 'idx_%s_%s' WHERE "
360 : "xmax > %.12f AND xmin < %.12f AND ymax > %.12f AND ymin < %.12f) ",
361 : pszFIDColumn,
362 : pszEscapedUnderlyingTableName, osUnderlyingGeometryColumn.c_str(),
363 : sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
364 1 : sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
365 : }
366 : else
367 : {
368 : CPLDebug("SQLITE", "Count not find idx_%s_%s layer. Disabling spatial index",
369 0 : pszEscapedUnderlyingTableName, osUnderlyingGeometryColumn.c_str());
370 : }
371 :
372 : }
373 :
374 4 : if( m_poFilterGeom != NULL && bSpatialiteLoaded && !bHasSpatialIndex )
375 : {
376 1 : OGREnvelope sEnvelope;
377 :
378 1 : m_poFilterGeom->getEnvelope( &sEnvelope );
379 :
380 : /* A bit inefficient but still faster than OGR filtering */
381 : osWHERE.Printf("WHERE MBRIntersects(\"%s\", BuildMBR(%.12f, %.12f, %.12f, %.12f)) ",
382 : osGeomColumn.c_str(),
383 : sEnvelope.MinX - 1e-11, sEnvelope.MinY - 1e-11,
384 1 : sEnvelope.MaxX + 1e-11, sEnvelope.MaxY + 1e-11);
385 : }
386 :
387 4 : if( strlen(osQuery) > 0 )
388 : {
389 4 : if( strlen(osWHERE) == 0 )
390 : {
391 2 : osWHERE.Printf( "WHERE %s ", osQuery.c_str() );
392 : }
393 : else
394 : {
395 2 : osWHERE += "AND ";
396 2 : osWHERE += osQuery;
397 : }
398 : }
399 4 : }
400 :
401 : /************************************************************************/
402 : /* TestCapability() */
403 : /************************************************************************/
404 :
405 1 : int OGRSQLiteViewLayer::TestCapability( const char * pszCap )
406 :
407 : {
408 1 : if (EQUAL(pszCap,OLCFastFeatureCount))
409 : return m_poFilterGeom == NULL || osGeomColumn.size() == 0 ||
410 1 : bHasSpatialIndex;
411 :
412 0 : else if (EQUAL(pszCap,OLCFastSpatialFilter))
413 0 : return bHasSpatialIndex;
414 :
415 : else
416 0 : return OGRSQLiteLayer::TestCapability( pszCap );
417 : }
418 :
419 : /************************************************************************/
420 : /* GetFeatureCount() */
421 : /* */
422 : /* If a spatial filter is in effect, we turn control over to */
423 : /* the generic counter. Otherwise we return the total count. */
424 : /* Eventually we should consider implementing a more efficient */
425 : /* way of counting features matching a spatial query. */
426 : /************************************************************************/
427 :
428 1 : int OGRSQLiteViewLayer::GetFeatureCount( int bForce )
429 :
430 : {
431 1 : if( !TestCapability(OLCFastFeatureCount) )
432 0 : return OGRSQLiteLayer::GetFeatureCount( bForce );
433 :
434 : /* -------------------------------------------------------------------- */
435 : /* Form count SQL. */
436 : /* -------------------------------------------------------------------- */
437 : const char *pszSQL;
438 :
439 : pszSQL = CPLSPrintf( "SELECT count(*) FROM '%s' %s",
440 1 : pszEscapedTableName, osWHERE.c_str() );
441 :
442 : /* -------------------------------------------------------------------- */
443 : /* Execute. */
444 : /* -------------------------------------------------------------------- */
445 : char **papszResult, *pszErrMsg;
446 : int nRowCount, nColCount;
447 1 : int nResult = -1;
448 :
449 1 : if( sqlite3_get_table( poDS->GetDB(), pszSQL, &papszResult,
450 : &nColCount, &nRowCount, &pszErrMsg ) != SQLITE_OK )
451 0 : return -1;
452 :
453 1 : if( nRowCount == 1 && nColCount == 1 )
454 1 : nResult = atoi(papszResult[1]);
455 :
456 1 : sqlite3_free_table( papszResult );
457 :
458 1 : return nResult;
459 : }
|
