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