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