Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: OpenGIS Simple Features Reference Implementation
4 : * Purpose: Implements OGRSQLiteViewLayer class, access to an existing
5 : *spatialite view. Author: Even Rouault, <even dot rouault at spatialys.com>
6 : *
7 : ******************************************************************************
8 : * Copyright (c) 2011-2013, Even Rouault <even dot rouault at spatialys.com>
9 : *
10 : * Permission is hereby granted, free of charge, to any person obtaining a
11 : * copy of this software and associated documentation files (the "Software"),
12 : * to deal in the Software without restriction, including without limitation
13 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
14 : * and/or sell copies of the Software, and to permit persons to whom the
15 : * Software is furnished to do so, subject to the following conditions:
16 : *
17 : * The above copyright notice and this permission notice shall be included
18 : * in all copies or substantial portions of the Software.
19 : *
20 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
21 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
22 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
23 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
24 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
25 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
26 : * DEALINGS IN THE SOFTWARE.
27 : ****************************************************************************/
28 :
29 : #include "cpl_port.h"
30 : #include "ogr_sqlite.h"
31 : #include "ogrsqliteutility.h"
32 :
33 : #include <cstdlib>
34 : #include <cstring>
35 : #include <set>
36 : #include <string>
37 :
38 : #include "cpl_conv.h"
39 : #include "cpl_error.h"
40 : #include "cpl_string.h"
41 : #include "ogr_core.h"
42 : #include "ogr_feature.h"
43 : #include "ogr_geometry.h"
44 : #include "sqlite3.h"
45 :
46 : /************************************************************************/
47 : /* OGRSQLiteViewLayer() */
48 : /************************************************************************/
49 :
50 5 : OGRSQLiteViewLayer::OGRSQLiteViewLayer(OGRSQLiteDataSource *poDSIn)
51 5 : : OGRSQLiteLayer(poDSIn)
52 : {
53 5 : }
54 :
55 : /************************************************************************/
56 : /* ~OGRSQLiteViewLayer() */
57 : /************************************************************************/
58 :
59 10 : OGRSQLiteViewLayer::~OGRSQLiteViewLayer()
60 :
61 : {
62 5 : ClearStatement();
63 5 : CPLFree(m_pszViewName);
64 5 : CPLFree(m_pszEscapedTableName);
65 5 : CPLFree(m_pszEscapedUnderlyingTableName);
66 10 : }
67 :
68 : /************************************************************************/
69 : /* Initialize() */
70 : /************************************************************************/
71 :
72 5 : CPLErr OGRSQLiteViewLayer::Initialize(const char *pszViewNameIn,
73 : const char *pszViewGeometry,
74 : const char *pszViewRowid,
75 : const char *pszUnderlyingTableName,
76 : const char *pszUnderlyingGeometryColumn)
77 :
78 : {
79 5 : m_pszViewName = CPLStrdup(pszViewNameIn);
80 5 : SetDescription(m_pszViewName);
81 :
82 5 : m_osGeomColumn = pszViewGeometry;
83 5 : m_eGeomFormat = OSGF_SpatiaLite;
84 :
85 5 : CPLFree(m_pszFIDColumn);
86 5 : m_pszFIDColumn = CPLStrdup(pszViewRowid);
87 :
88 5 : m_osUnderlyingTableName = pszUnderlyingTableName;
89 5 : m_osUnderlyingGeometryColumn = pszUnderlyingGeometryColumn;
90 5 : m_poUnderlyingLayer = nullptr;
91 :
92 5 : m_pszEscapedTableName = CPLStrdup(SQLEscapeLiteral(m_pszViewName));
93 5 : m_pszEscapedUnderlyingTableName =
94 5 : CPLStrdup(SQLEscapeLiteral(pszUnderlyingTableName));
95 :
96 5 : return CE_None;
97 : }
98 :
99 : /************************************************************************/
100 : /* GetLayerDefn() */
101 : /************************************************************************/
102 :
103 31 : OGRFeatureDefn *OGRSQLiteViewLayer::GetLayerDefn()
104 : {
105 31 : if (m_poFeatureDefn)
106 27 : return m_poFeatureDefn;
107 :
108 4 : EstablishFeatureDefn();
109 :
110 4 : if (m_poFeatureDefn == nullptr)
111 : {
112 0 : m_bLayerDefnError = true;
113 :
114 0 : m_poFeatureDefn = new OGRSQLiteFeatureDefn(m_pszViewName);
115 0 : m_poFeatureDefn->Reference();
116 : }
117 :
118 4 : return m_poFeatureDefn;
119 : }
120 :
121 : /************************************************************************/
122 : /* GetUnderlyingLayer() */
123 : /************************************************************************/
124 :
125 4 : OGRSQLiteLayer *OGRSQLiteViewLayer::GetUnderlyingLayer()
126 : {
127 4 : if (m_poUnderlyingLayer == nullptr)
128 : {
129 4 : if (strchr(m_osUnderlyingTableName, '(') == nullptr)
130 : {
131 4 : CPLString osNewUnderlyingTableName;
132 : osNewUnderlyingTableName.Printf(
133 : "%s(%s)", m_osUnderlyingTableName.c_str(),
134 4 : m_osUnderlyingGeometryColumn.c_str());
135 4 : m_poUnderlyingLayer =
136 4 : (OGRSQLiteLayer *)m_poDS->GetLayerByNameNotVisible(
137 : osNewUnderlyingTableName);
138 : }
139 4 : if (m_poUnderlyingLayer == nullptr)
140 0 : m_poUnderlyingLayer =
141 0 : (OGRSQLiteLayer *)m_poDS->GetLayerByNameNotVisible(
142 : m_osUnderlyingTableName);
143 : }
144 4 : return m_poUnderlyingLayer;
145 : }
146 :
147 : /************************************************************************/
148 : /* GetGeomType() */
149 : /************************************************************************/
150 :
151 3 : OGRwkbGeometryType OGRSQLiteViewLayer::GetGeomType()
152 : {
153 3 : if (m_poFeatureDefn)
154 3 : return m_poFeatureDefn->GetGeomType();
155 :
156 0 : OGRSQLiteLayer *l_m_poUnderlyingLayer = GetUnderlyingLayer();
157 0 : if (l_m_poUnderlyingLayer)
158 0 : return l_m_poUnderlyingLayer->GetGeomType();
159 :
160 0 : return wkbUnknown;
161 : }
162 :
163 : /************************************************************************/
164 : /* EstablishFeatureDefn() */
165 : /************************************************************************/
166 :
167 4 : CPLErr OGRSQLiteViewLayer::EstablishFeatureDefn()
168 : {
169 4 : sqlite3 *hDB = m_poDS->GetDB();
170 4 : sqlite3_stmt *hColStmt = nullptr;
171 :
172 4 : OGRSQLiteLayer *l_m_poUnderlyingLayer = GetUnderlyingLayer();
173 4 : if (l_m_poUnderlyingLayer == nullptr)
174 : {
175 0 : CPLError(CE_Failure, CPLE_AppDefined,
176 : "Cannot find underlying layer %s for view %s",
177 : m_osUnderlyingTableName.c_str(), m_pszViewName);
178 0 : return CE_Failure;
179 : }
180 4 : if (!l_m_poUnderlyingLayer->IsTableLayer())
181 : {
182 0 : CPLError(CE_Failure, CPLE_AppDefined,
183 : "Underlying layer %s for view %s is not a regular table",
184 : m_osUnderlyingTableName.c_str(), m_pszViewName);
185 0 : return CE_Failure;
186 : }
187 :
188 : int nUnderlyingLayerGeomFieldIndex =
189 8 : l_m_poUnderlyingLayer->GetLayerDefn()->GetGeomFieldIndex(
190 4 : m_osUnderlyingGeometryColumn);
191 4 : if (nUnderlyingLayerGeomFieldIndex < 0)
192 : {
193 0 : CPLError(CE_Failure, CPLE_AppDefined,
194 : "Underlying layer %s for view %s has not expected geometry "
195 : "column name %s",
196 : m_osUnderlyingTableName.c_str(), m_pszViewName,
197 : m_osUnderlyingGeometryColumn.c_str());
198 0 : return CE_Failure;
199 : }
200 :
201 4 : m_bHasSpatialIndex =
202 4 : l_m_poUnderlyingLayer->HasSpatialIndex(nUnderlyingLayerGeomFieldIndex);
203 :
204 : /* -------------------------------------------------------------------- */
205 : /* Get the column definitions for this table. */
206 : /* -------------------------------------------------------------------- */
207 4 : hColStmt = nullptr;
208 4 : const char *pszSQL = CPLSPrintf("SELECT \"%s\", * FROM '%s' LIMIT 1",
209 8 : SQLEscapeName(m_pszFIDColumn).c_str(),
210 : m_pszEscapedTableName);
211 :
212 4 : int rc = sqlite3_prepare_v2(hDB, pszSQL, -1, &hColStmt, nullptr);
213 4 : if (rc != SQLITE_OK)
214 : {
215 0 : CPLError(CE_Failure, CPLE_AppDefined,
216 : "Unable to query table %s for column definitions : %s.",
217 : m_pszViewName, sqlite3_errmsg(hDB));
218 :
219 0 : return CE_Failure;
220 : }
221 :
222 4 : rc = sqlite3_step(hColStmt);
223 4 : if (rc != SQLITE_DONE && rc != SQLITE_ROW)
224 : {
225 0 : CPLError(CE_Failure, CPLE_AppDefined,
226 : "In Initialize(): sqlite3_step(%s):\n %s", pszSQL,
227 : sqlite3_errmsg(hDB));
228 0 : sqlite3_finalize(hColStmt);
229 0 : return CE_Failure;
230 : }
231 :
232 : /* -------------------------------------------------------------------- */
233 : /* Collect the rest of the fields. */
234 : /* -------------------------------------------------------------------- */
235 8 : std::set<CPLString> aosGeomCols;
236 4 : std::set<CPLString> aosIgnoredCols;
237 4 : aosGeomCols.insert(m_osGeomColumn);
238 4 : BuildFeatureDefn(m_pszViewName, false, hColStmt, &aosGeomCols,
239 : aosIgnoredCols);
240 4 : sqlite3_finalize(hColStmt);
241 :
242 : /* -------------------------------------------------------------------- */
243 : /* Set the properties of the geometry column. */
244 : /* -------------------------------------------------------------------- */
245 4 : if (m_poFeatureDefn->GetGeomFieldCount() != 0)
246 : {
247 : OGRSQLiteGeomFieldDefn *poSrcGeomFieldDefn =
248 4 : l_m_poUnderlyingLayer->myGetLayerDefn()->myGetGeomFieldDefn(
249 : nUnderlyingLayerGeomFieldIndex);
250 : OGRSQLiteGeomFieldDefn *poGeomFieldDefn =
251 4 : m_poFeatureDefn->myGetGeomFieldDefn(0);
252 4 : poGeomFieldDefn->SetType(poSrcGeomFieldDefn->GetType());
253 4 : poGeomFieldDefn->SetSpatialRef(poSrcGeomFieldDefn->GetSpatialRef());
254 4 : poGeomFieldDefn->m_nSRSId = poSrcGeomFieldDefn->m_nSRSId;
255 4 : if (m_eGeomFormat != OSGF_None)
256 4 : poGeomFieldDefn->m_eGeomFormat = m_eGeomFormat;
257 : }
258 :
259 4 : return CE_None;
260 : }
261 :
262 : /************************************************************************/
263 : /* ResetStatement() */
264 : /************************************************************************/
265 :
266 4 : OGRErr OGRSQLiteViewLayer::ResetStatement()
267 :
268 : {
269 8 : CPLString osSQL;
270 :
271 4 : ClearStatement();
272 :
273 4 : m_iNextShapeId = 0;
274 :
275 : osSQL.Printf("SELECT \"%s\", * FROM '%s' %s",
276 4 : SQLEscapeName(m_pszFIDColumn).c_str(), m_pszEscapedTableName,
277 4 : m_osWHERE.c_str());
278 :
279 : const int rc =
280 4 : sqlite3_prepare_v2(m_poDS->GetDB(), osSQL,
281 4 : static_cast<int>(osSQL.size()), &m_hStmt, nullptr);
282 :
283 4 : if (rc == SQLITE_OK)
284 : {
285 4 : return OGRERR_NONE;
286 : }
287 :
288 0 : CPLError(CE_Failure, CPLE_AppDefined,
289 : "In ResetStatement(): sqlite3_prepare_v2(%s):\n %s",
290 0 : osSQL.c_str(), sqlite3_errmsg(m_poDS->GetDB()));
291 0 : m_hStmt = nullptr;
292 0 : return OGRERR_FAILURE;
293 : }
294 :
295 : /************************************************************************/
296 : /* GetNextFeature() */
297 : /************************************************************************/
298 :
299 4 : OGRFeature *OGRSQLiteViewLayer::GetNextFeature()
300 :
301 : {
302 4 : if (HasLayerDefnError())
303 0 : return nullptr;
304 :
305 4 : return OGRSQLiteLayer::GetNextFeature();
306 : }
307 :
308 : /************************************************************************/
309 : /* GetFeature() */
310 : /************************************************************************/
311 :
312 3 : OGRFeature *OGRSQLiteViewLayer::GetFeature(GIntBig nFeatureId)
313 :
314 : {
315 3 : if (HasLayerDefnError())
316 0 : return nullptr;
317 :
318 : /* -------------------------------------------------------------------- */
319 : /* If we don't have an explicit FID column, just read through */
320 : /* the result set iteratively to find our target. */
321 : /* -------------------------------------------------------------------- */
322 3 : if (m_pszFIDColumn == nullptr)
323 0 : return OGRSQLiteLayer::GetFeature(nFeatureId);
324 :
325 : /* -------------------------------------------------------------------- */
326 : /* Setup explicit query statement to fetch the record we want. */
327 : /* -------------------------------------------------------------------- */
328 6 : CPLString osSQL;
329 :
330 3 : ClearStatement();
331 :
332 3 : m_iNextShapeId = nFeatureId;
333 :
334 : osSQL.Printf("SELECT \"%s\", * FROM '%s' WHERE \"%s\" = %d",
335 3 : SQLEscapeName(m_pszFIDColumn).c_str(), m_pszEscapedTableName,
336 6 : SQLEscapeName(m_pszFIDColumn).c_str(), (int)nFeatureId);
337 :
338 3 : CPLDebug("OGR_SQLITE", "exec(%s)", osSQL.c_str());
339 :
340 : const int rc =
341 3 : sqlite3_prepare_v2(m_poDS->GetDB(), osSQL,
342 3 : static_cast<int>(osSQL.size()), &m_hStmt, nullptr);
343 3 : if (rc != SQLITE_OK)
344 : {
345 0 : CPLError(CE_Failure, CPLE_AppDefined,
346 : "In GetFeature(): sqlite3_prepare_v2(%s):\n %s",
347 0 : osSQL.c_str(), sqlite3_errmsg(m_poDS->GetDB()));
348 :
349 0 : return nullptr;
350 : }
351 : /* -------------------------------------------------------------------- */
352 : /* Get the feature if possible. */
353 : /* -------------------------------------------------------------------- */
354 3 : OGRFeature *poFeature = GetNextRawFeature();
355 :
356 3 : ResetReading();
357 :
358 3 : return poFeature;
359 : }
360 :
361 : /************************************************************************/
362 : /* SetAttributeFilter() */
363 : /************************************************************************/
364 :
365 2 : OGRErr OGRSQLiteViewLayer::SetAttributeFilter(const char *pszQuery)
366 :
367 : {
368 2 : if (pszQuery == nullptr)
369 0 : m_osQuery = "";
370 : else
371 2 : m_osQuery = pszQuery;
372 :
373 2 : BuildWhere();
374 :
375 2 : ResetReading();
376 :
377 2 : return OGRERR_NONE;
378 : }
379 :
380 : /************************************************************************/
381 : /* SetSpatialFilter() */
382 : /************************************************************************/
383 :
384 4 : void OGRSQLiteViewLayer::SetSpatialFilter(OGRGeometry *poGeomIn)
385 :
386 : {
387 4 : if (InstallFilter(poGeomIn))
388 : {
389 4 : BuildWhere();
390 :
391 4 : ResetReading();
392 : }
393 4 : }
394 :
395 : /************************************************************************/
396 : /* GetSpatialWhere() */
397 : /************************************************************************/
398 :
399 6 : CPLString OGRSQLiteViewLayer::GetSpatialWhere(int iGeomCol,
400 : OGRGeometry *poFilterGeom)
401 : {
402 12 : if (HasLayerDefnError() || m_poFeatureDefn == nullptr || iGeomCol < 0 ||
403 6 : iGeomCol >= m_poFeatureDefn->GetGeomFieldCount())
404 0 : return "";
405 :
406 6 : if (poFilterGeom != nullptr && m_bHasSpatialIndex)
407 : {
408 3 : OGREnvelope sEnvelope;
409 :
410 3 : poFilterGeom->getEnvelope(&sEnvelope);
411 :
412 : /* We first check that the spatial index table exists */
413 3 : if (!m_bHasCheckedSpatialIndexTable)
414 : {
415 3 : m_bHasCheckedSpatialIndexTable = true;
416 3 : char **papszResult = nullptr;
417 3 : int nRowCount = 0;
418 3 : int nColCount = 0;
419 3 : char *pszErrMsg = nullptr;
420 :
421 6 : CPLString osSQL;
422 : osSQL.Printf(
423 : "SELECT name FROM sqlite_master "
424 : "WHERE name='idx_%s_%s'",
425 : m_pszEscapedUnderlyingTableName,
426 3 : SQLEscapeLiteral(m_osUnderlyingGeometryColumn).c_str());
427 :
428 : int rc =
429 3 : sqlite3_get_table(m_poDS->GetDB(), osSQL.c_str(), &papszResult,
430 : &nRowCount, &nColCount, &pszErrMsg);
431 :
432 3 : if (rc != SQLITE_OK)
433 : {
434 0 : CPLError(CE_Failure, CPLE_AppDefined, "Error: %s", pszErrMsg);
435 0 : sqlite3_free(pszErrMsg);
436 0 : m_bHasSpatialIndex = false;
437 : }
438 : else
439 : {
440 3 : if (nRowCount != 1)
441 : {
442 1 : m_bHasSpatialIndex = false;
443 : }
444 :
445 3 : sqlite3_free_table(papszResult);
446 : }
447 : }
448 :
449 3 : if (m_bHasSpatialIndex)
450 : {
451 : return FormatSpatialFilterFromRTree(
452 : poFilterGeom,
453 2 : CPLSPrintf("\"%s\"", SQLEscapeName(m_pszFIDColumn).c_str()),
454 2 : m_pszEscapedUnderlyingTableName,
455 6 : SQLEscapeLiteral(m_osUnderlyingGeometryColumn).c_str());
456 : }
457 : else
458 : {
459 1 : CPLDebug("SQLITE",
460 : "Count not find idx_%s_%s layer. Disabling spatial index",
461 : m_pszEscapedUnderlyingTableName,
462 : m_osUnderlyingGeometryColumn.c_str());
463 : }
464 : }
465 :
466 4 : if (poFilterGeom != nullptr && m_poDS->IsSpatialiteLoaded())
467 : {
468 : return FormatSpatialFilterFromMBR(
469 : poFilterGeom,
470 4 : SQLEscapeName(
471 2 : m_poFeatureDefn->GetGeomFieldDefn(iGeomCol)->GetNameRef())
472 2 : .c_str());
473 : }
474 :
475 2 : return "";
476 : }
477 :
478 : /************************************************************************/
479 : /* BuildWhere() */
480 : /* */
481 : /* Build the WHERE statement appropriate to the current set of */
482 : /* criteria (spatial and attribute queries). */
483 : /************************************************************************/
484 :
485 6 : void OGRSQLiteViewLayer::BuildWhere()
486 :
487 : {
488 6 : m_osWHERE = "";
489 :
490 : CPLString osSpatialWHERE =
491 12 : GetSpatialWhere(m_iGeomFieldFilter, m_poFilterGeom);
492 6 : if (!osSpatialWHERE.empty())
493 : {
494 4 : m_osWHERE = "WHERE ";
495 4 : m_osWHERE += osSpatialWHERE;
496 : }
497 :
498 6 : if (!m_osQuery.empty())
499 : {
500 4 : if (m_osWHERE.empty())
501 : {
502 2 : m_osWHERE = "WHERE ";
503 2 : m_osWHERE += m_osQuery;
504 : }
505 : else
506 : {
507 2 : m_osWHERE += " AND (";
508 2 : m_osWHERE += m_osQuery;
509 2 : m_osWHERE += ")";
510 : }
511 : }
512 6 : }
513 :
514 : /************************************************************************/
515 : /* TestCapability() */
516 : /************************************************************************/
517 :
518 3 : int OGRSQLiteViewLayer::TestCapability(const char *pszCap)
519 :
520 : {
521 3 : if (HasLayerDefnError())
522 0 : return FALSE;
523 :
524 3 : if (EQUAL(pszCap, OLCFastFeatureCount))
525 3 : return m_poFilterGeom == nullptr || m_osGeomColumn.empty() ||
526 3 : m_bHasSpatialIndex;
527 :
528 0 : else if (EQUAL(pszCap, OLCFastSpatialFilter))
529 0 : return m_bHasSpatialIndex;
530 :
531 : else
532 0 : return OGRSQLiteLayer::TestCapability(pszCap);
533 : }
534 :
535 : /************************************************************************/
536 : /* GetFeatureCount() */
537 : /* */
538 : /* If a spatial filter is in effect, we turn control over to */
539 : /* the generic counter. Otherwise we return the total count. */
540 : /* Eventually we should consider implementing a more efficient */
541 : /* way of counting features matching a spatial query. */
542 : /************************************************************************/
543 :
544 3 : GIntBig OGRSQLiteViewLayer::GetFeatureCount(int bForce)
545 :
546 : {
547 3 : if (HasLayerDefnError())
548 0 : return 0;
549 :
550 3 : if (!TestCapability(OLCFastFeatureCount))
551 0 : return OGRSQLiteLayer::GetFeatureCount(bForce);
552 :
553 : /* -------------------------------------------------------------------- */
554 : /* Form count SQL. */
555 : /* -------------------------------------------------------------------- */
556 3 : const char *pszSQL = CPLSPrintf("SELECT count(*) FROM '%s' %s",
557 : m_pszEscapedTableName, m_osWHERE.c_str());
558 :
559 : /* -------------------------------------------------------------------- */
560 : /* Execute. */
561 : /* -------------------------------------------------------------------- */
562 : char **papszResult, *pszErrMsg;
563 : int nRowCount, nColCount;
564 3 : int nResult = -1;
565 :
566 3 : if (sqlite3_get_table(m_poDS->GetDB(), pszSQL, &papszResult, &nColCount,
567 3 : &nRowCount, &pszErrMsg) != SQLITE_OK)
568 0 : return -1;
569 :
570 3 : if (nRowCount == 1 && nColCount == 1)
571 3 : nResult = atoi(papszResult[1]);
572 :
573 3 : sqlite3_free_table(papszResult);
574 :
575 3 : return nResult;
576 : }
|
