1 /******************************************************************************
2 * $Id: ogrsqlitesqlfunctions.cpp 28382 2015-01-30 15:29:41Z rouault $
3 *
4 * Project: OpenGIS Simple Features Reference Implementation
5 * Purpose: Extension SQL functions
6 * Author: Even Rouault, even dot rouault at mines dash paris dot org
7 *
8 ******************************************************************************
9 * Copyright (c) 2012-2013, Even Rouault <even dot rouault at mines-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 /* WARNING: VERY IMPORTANT NOTE: This file MUST not be directly compiled as */
31 /* a standalone object. It must be included from ogrsqlitevirtualogr.cpp */
32 #ifndef COMPILATION_ALLOWED
33 #error See comment in file
34 #endif
35
36 #include "ogrsqlitesqlfunctions.h"
37 #include "ogr_geocoding.h"
38
39 #include "ogrsqliteregexp.cpp" /* yes the .cpp file, to make it work on Windows with load_extension('gdalXX.dll') */
40 #include "swq.h"
41
42 #ifndef HAVE_SPATIALITE
43 #define MINIMAL_SPATIAL_FUNCTIONS
44 #endif
45
46 class OGRSQLiteExtensionData
47 {
48 #ifdef DEBUG
49 void* pDummy; /* to track memory leaks */
50 #endif
51 std::map< std::pair<int,int>, OGRCoordinateTransformation*> oCachedTransformsMap;
52
53 void* hRegExpCache;
54
55 OGRGeocodingSessionH hGeocodingSession;
56
57 public:
58 OGRSQLiteExtensionData(sqlite3* hDB);
59 ~OGRSQLiteExtensionData();
60
61 OGRCoordinateTransformation* GetTransform(int nSrcSRSId, int nDstSRSId);
62
GetGeocodingSession()63 OGRGeocodingSessionH GetGeocodingSession() { return hGeocodingSession; }
SetGeocodingSession(OGRGeocodingSessionH hGeocodingSessionIn)64 void SetGeocodingSession(OGRGeocodingSessionH hGeocodingSessionIn) { hGeocodingSession = hGeocodingSessionIn; }
65
SetRegExpCache(void * hRegExpCacheIn)66 void SetRegExpCache(void* hRegExpCacheIn) { hRegExpCache = hRegExpCacheIn; }
67 };
68
69 /************************************************************************/
70 /* OGRSQLiteExtensionData() */
71 /************************************************************************/
72
OGRSQLiteExtensionData(CPL_UNUSED sqlite3 * hDB)73 OGRSQLiteExtensionData::OGRSQLiteExtensionData(CPL_UNUSED sqlite3* hDB) :
74 hRegExpCache(NULL), hGeocodingSession(NULL)
75 {
76 #ifdef DEBUG
77 pDummy = CPLMalloc(1);
78 #endif
79 }
80
81 /************************************************************************/
82 /* ~OGRSQLiteExtensionData() */
83 /************************************************************************/
84
~OGRSQLiteExtensionData()85 OGRSQLiteExtensionData::~OGRSQLiteExtensionData()
86 {
87 #ifdef DEBUG
88 CPLFree(pDummy);
89 #endif
90
91 std::map< std::pair<int,int>, OGRCoordinateTransformation*>::iterator oIter =
92 oCachedTransformsMap.begin();
93 for(; oIter != oCachedTransformsMap.end(); ++oIter)
94 delete oIter->second;
95
96 OGRSQLiteFreeRegExpCache(hRegExpCache);
97
98 OGRGeocodeDestroySession(hGeocodingSession);
99 }
100
101 /************************************************************************/
102 /* GetTransform() */
103 /************************************************************************/
104
GetTransform(int nSrcSRSId,int nDstSRSId)105 OGRCoordinateTransformation* OGRSQLiteExtensionData::GetTransform(int nSrcSRSId,
106 int nDstSRSId)
107 {
108 std::map< std::pair<int,int>, OGRCoordinateTransformation*>::iterator oIter =
109 oCachedTransformsMap.find(std::pair<int,int>(nSrcSRSId, nDstSRSId));
110 if( oIter == oCachedTransformsMap.end() )
111 {
112 OGRCoordinateTransformation* poCT = NULL;
113 OGRSpatialReference oSrcSRS, oDstSRS;
114 if (oSrcSRS.importFromEPSG(nSrcSRSId) == OGRERR_NONE &&
115 oDstSRS.importFromEPSG(nDstSRSId) == OGRERR_NONE )
116 {
117 poCT = OGRCreateCoordinateTransformation( &oSrcSRS, &oDstSRS );
118 }
119 oCachedTransformsMap[std::pair<int,int>(nSrcSRSId, nDstSRSId)] = poCT;
120 return poCT;
121 }
122 else
123 return oIter->second;
124 }
125
126 /************************************************************************/
127 /* OGR2SQLITE_ogr_version() */
128 /************************************************************************/
129
130 static
OGR2SQLITE_ogr_version(sqlite3_context * pContext,int argc,sqlite3_value ** argv)131 void OGR2SQLITE_ogr_version(sqlite3_context* pContext,
132 int argc, sqlite3_value** argv)
133 {
134 if( argc == 0 || sqlite3_value_type (argv[0]) != SQLITE_TEXT )
135 {
136 sqlite3_result_text( pContext, GDAL_RELEASE_NAME, -1, SQLITE_STATIC );
137 }
138 else
139 {
140 sqlite3_result_text( pContext,
141 GDALVersionInfo((const char*)sqlite3_value_text(argv[0])),
142 -1, SQLITE_TRANSIENT );
143 }
144 }
145
146 /************************************************************************/
147 /* OGR2SQLITE_Transform() */
148 /************************************************************************/
149
150 static
OGR2SQLITE_Transform(sqlite3_context * pContext,int argc,sqlite3_value ** argv)151 void OGR2SQLITE_Transform(sqlite3_context* pContext,
152 int argc, sqlite3_value** argv)
153 {
154 if( argc != 3 )
155 {
156 sqlite3_result_null (pContext);
157 return;
158 }
159
160 if( sqlite3_value_type (argv[0]) != SQLITE_BLOB )
161 {
162 sqlite3_result_null (pContext);
163 return;
164 }
165
166 if( sqlite3_value_type (argv[1]) != SQLITE_INTEGER )
167 {
168 sqlite3_result_null (pContext);
169 return;
170 }
171
172 if( sqlite3_value_type (argv[2]) != SQLITE_INTEGER )
173 {
174 sqlite3_result_null (pContext);
175 return;
176 }
177
178 int nSrcSRSId = sqlite3_value_int(argv[1]);
179 int nDstSRSId = sqlite3_value_int(argv[2]);
180
181 OGRSQLiteExtensionData* poModule =
182 (OGRSQLiteExtensionData*) sqlite3_user_data(pContext);
183 OGRCoordinateTransformation* poCT =
184 poModule->GetTransform(nSrcSRSId, nDstSRSId);
185 if( poCT == NULL )
186 {
187 sqlite3_result_null (pContext);
188 return;
189 }
190
191 GByte* pabySLBLOB = (GByte *) sqlite3_value_blob (argv[0]);
192 int nBLOBLen = sqlite3_value_bytes (argv[0]);
193 OGRGeometry* poGeom = NULL;
194 if( OGRSQLiteLayer::ImportSpatiaLiteGeometry(
195 pabySLBLOB, nBLOBLen, &poGeom ) == CE_None &&
196 poGeom->transform(poCT) == OGRERR_NONE &&
197 OGRSQLiteLayer::ExportSpatiaLiteGeometry(
198 poGeom, nDstSRSId, wkbNDR, FALSE,
199 FALSE, FALSE, &pabySLBLOB, &nBLOBLen ) == CE_None )
200 {
201 sqlite3_result_blob(pContext, pabySLBLOB, nBLOBLen, CPLFree);
202 }
203 else
204 {
205 sqlite3_result_null (pContext);
206 }
207 delete poGeom;
208 }
209
210 /************************************************************************/
211 /* OGR2SQLITE_ogr_deflate() */
212 /************************************************************************/
213
214 static
OGR2SQLITE_ogr_deflate(sqlite3_context * pContext,int argc,sqlite3_value ** argv)215 void OGR2SQLITE_ogr_deflate(sqlite3_context* pContext,
216 int argc, sqlite3_value** argv)
217 {
218 int nLevel = -1;
219 if( !(argc == 1 || argc == 2) ||
220 !(sqlite3_value_type (argv[0]) == SQLITE_TEXT ||
221 sqlite3_value_type (argv[0]) == SQLITE_BLOB) )
222 {
223 sqlite3_result_null (pContext);
224 return;
225 }
226 if( argc == 2 )
227 {
228 if( sqlite3_value_type (argv[1]) != SQLITE_INTEGER )
229 {
230 sqlite3_result_null (pContext);
231 return;
232 }
233 nLevel = sqlite3_value_int(argv[1]);
234 }
235
236 size_t nOutBytes = 0;
237 void* pOut;
238 if( sqlite3_value_type (argv[0]) == SQLITE_TEXT )
239 {
240 const char* pszVal = (const char*)sqlite3_value_text(argv[0]);
241 pOut = CPLZLibDeflate( pszVal, strlen(pszVal) + 1, nLevel, NULL, 0, &nOutBytes);
242 }
243 else
244 {
245 const void* pSrc = sqlite3_value_blob (argv[0]);
246 int nLen = sqlite3_value_bytes (argv[0]);
247 pOut = CPLZLibDeflate( pSrc, nLen, nLevel, NULL, 0, &nOutBytes);
248 }
249 if( pOut != NULL )
250 {
251 sqlite3_result_blob (pContext, pOut, nOutBytes, VSIFree);
252 }
253 else
254 {
255 sqlite3_result_null (pContext);
256 }
257
258 return;
259 }
260
261 /************************************************************************/
262 /* OGR2SQLITE_ogr_inflate() */
263 /************************************************************************/
264
265 static
OGR2SQLITE_ogr_inflate(sqlite3_context * pContext,int argc,sqlite3_value ** argv)266 void OGR2SQLITE_ogr_inflate(sqlite3_context* pContext,
267 int argc, sqlite3_value** argv)
268 {
269 if( argc != 1 ||
270 sqlite3_value_type (argv[0]) != SQLITE_BLOB )
271 {
272 sqlite3_result_null (pContext);
273 return;
274 }
275
276 size_t nOutBytes = 0;
277 void* pOut;
278
279 const void* pSrc = sqlite3_value_blob (argv[0]);
280 int nLen = sqlite3_value_bytes (argv[0]);
281 pOut = CPLZLibInflate( pSrc, nLen, NULL, 0, &nOutBytes);
282
283 if( pOut != NULL )
284 {
285 sqlite3_result_blob (pContext, pOut, nOutBytes, VSIFree);
286 }
287 else
288 {
289 sqlite3_result_null (pContext);
290 }
291
292 return;
293 }
294
295 /************************************************************************/
296 /* OGR2SQLITE_ogr_geocode_set_result() */
297 /************************************************************************/
298
299 static
OGR2SQLITE_ogr_geocode_set_result(sqlite3_context * pContext,OGRLayerH hLayer,const char * pszField)300 void OGR2SQLITE_ogr_geocode_set_result(sqlite3_context* pContext,
301 OGRLayerH hLayer,
302 const char* pszField)
303 {
304 if( hLayer == NULL )
305 sqlite3_result_null (pContext);
306 else
307 {
308 OGRLayer* poLayer = (OGRLayer*)hLayer;
309 OGRFeatureDefn* poFDefn = poLayer->GetLayerDefn();
310 OGRFeature* poFeature = poLayer->GetNextFeature();
311 int nIdx = -1;
312 if( poFeature == NULL )
313 sqlite3_result_null (pContext);
314 else if( strcmp(pszField, "geometry") == 0 &&
315 poFeature->GetGeometryRef() != NULL )
316 {
317 GByte* pabyGeomBLOB = NULL;
318 int nGeomBLOBLen = 0;
319 if( OGRSQLiteLayer::ExportSpatiaLiteGeometry(
320 poFeature->GetGeometryRef(), 4326, wkbNDR, FALSE, FALSE, FALSE,
321 &pabyGeomBLOB,
322 &nGeomBLOBLen ) != CE_None )
323 {
324 sqlite3_result_null (pContext);
325 }
326 else
327 {
328 sqlite3_result_blob (pContext, pabyGeomBLOB, nGeomBLOBLen, CPLFree);
329 }
330 }
331 else if( (nIdx = poFDefn->GetFieldIndex(pszField)) >= 0 &&
332 poFeature->IsFieldSet(nIdx) )
333 {
334 OGRFieldType eType = poFDefn->GetFieldDefn(nIdx)->GetType();
335 if( eType == OFTInteger )
336 sqlite3_result_int(pContext,
337 poFeature->GetFieldAsInteger(nIdx));
338 else if( eType == OFTInteger64 )
339 sqlite3_result_int64(pContext,
340 poFeature->GetFieldAsInteger64(nIdx));
341 else if( eType == OFTReal )
342 sqlite3_result_double(pContext,
343 poFeature->GetFieldAsDouble(nIdx));
344 else
345 sqlite3_result_text(pContext,
346 poFeature->GetFieldAsString(nIdx),
347 -1, SQLITE_TRANSIENT);
348 }
349 else
350 sqlite3_result_null (pContext);
351 delete poFeature;
352 OGRGeocodeFreeResult(hLayer);
353 }
354 }
355
356 /************************************************************************/
357 /* OGR2SQLITE_ogr_geocode() */
358 /************************************************************************/
359
360 static
OGR2SQLITE_ogr_geocode(sqlite3_context * pContext,int argc,sqlite3_value ** argv)361 void OGR2SQLITE_ogr_geocode(sqlite3_context* pContext,
362 int argc, sqlite3_value** argv)
363 {
364 OGRSQLiteExtensionData* poModule =
365 (OGRSQLiteExtensionData*) sqlite3_user_data(pContext);
366
367 if( argc < 1 || sqlite3_value_type (argv[0]) != SQLITE_TEXT )
368 {
369 sqlite3_result_null (pContext);
370 return;
371 }
372 const char* pszQuery = (const char*)sqlite3_value_text(argv[0]);
373
374 CPLString osField = "geometry";
375 if( argc >= 2 && sqlite3_value_type (argv[1]) == SQLITE_TEXT )
376 {
377 osField = (const char*)sqlite3_value_text(argv[1]);
378 }
379
380 int i;
381 char** papszOptions = NULL;
382 for(i = 2; i < argc; i++)
383 {
384 if( sqlite3_value_type (argv[i]) == SQLITE_TEXT )
385 {
386 papszOptions = CSLAddString(papszOptions,
387 (const char*)sqlite3_value_text(argv[i]));
388 }
389 }
390
391 OGRGeocodingSessionH hSession = poModule->GetGeocodingSession();
392 if( hSession == NULL )
393 {
394 hSession = OGRGeocodeCreateSession(papszOptions);
395 if( hSession == NULL )
396 {
397 sqlite3_result_null (pContext);
398 CSLDestroy(papszOptions);
399 return;
400 }
401 poModule->SetGeocodingSession(hSession);
402 }
403
404 if( osField == "raw" )
405 papszOptions = CSLAddString(papszOptions, "RAW_FEATURE=YES");
406
407 if( CSLFindString(papszOptions, "LIMIT") == -1 )
408 papszOptions = CSLAddString(papszOptions, "LIMIT=1");
409
410 OGRLayerH hLayer = OGRGeocode(hSession, pszQuery, NULL, papszOptions);
411
412 OGR2SQLITE_ogr_geocode_set_result(pContext, hLayer, osField);
413
414 CSLDestroy(papszOptions);
415
416 return;
417 }
418
419 /************************************************************************/
420 /* OGR2SQLITE_GetValAsDouble() */
421 /************************************************************************/
422
OGR2SQLITE_GetValAsDouble(sqlite3_value * val,int * pbGotVal)423 static double OGR2SQLITE_GetValAsDouble(sqlite3_value* val, int* pbGotVal)
424 {
425 switch(sqlite3_value_type(val))
426 {
427 case SQLITE_FLOAT:
428 if( pbGotVal ) *pbGotVal = TRUE;
429 return sqlite3_value_double(val);
430
431 case SQLITE_INTEGER:
432 if( pbGotVal ) *pbGotVal = TRUE;
433 return (double) sqlite3_value_int64(val);
434
435 default:
436 if( pbGotVal ) *pbGotVal = FALSE;
437 return 0.0;
438 }
439 }
440
441 /************************************************************************/
442 /* OGR2SQLITE_GetGeom() */
443 /************************************************************************/
444
OGR2SQLITE_GetGeom(CPL_UNUSED sqlite3_context * pContext,CPL_UNUSED int argc,sqlite3_value ** argv,int * pnSRSId)445 static OGRGeometry* OGR2SQLITE_GetGeom(CPL_UNUSED sqlite3_context* pContext,
446 CPL_UNUSED int argc,
447 sqlite3_value** argv,
448 int* pnSRSId)
449 {
450 if( sqlite3_value_type (argv[0]) != SQLITE_BLOB )
451 {
452 return NULL;
453 }
454
455 GByte* pabySLBLOB = (GByte *) sqlite3_value_blob (argv[0]);
456 int nBLOBLen = sqlite3_value_bytes (argv[0]);
457 OGRGeometry* poGeom = NULL;
458 if( OGRSQLiteLayer::ImportSpatiaLiteGeometry(
459 pabySLBLOB, nBLOBLen, &poGeom, pnSRSId) != CE_None )
460 {
461 return NULL;
462 }
463
464 return poGeom;
465 }
466
467 /************************************************************************/
468 /* OGR2SQLITE_ogr_geocode_reverse() */
469 /************************************************************************/
470
471 static
OGR2SQLITE_ogr_geocode_reverse(sqlite3_context * pContext,int argc,sqlite3_value ** argv)472 void OGR2SQLITE_ogr_geocode_reverse(sqlite3_context* pContext,
473 int argc, sqlite3_value** argv)
474 {
475 OGRSQLiteExtensionData* poModule =
476 (OGRSQLiteExtensionData*) sqlite3_user_data(pContext);
477
478 double dfLon = 0.0, dfLat = 0.0;
479 int iAfterGeomIdx = 0;
480 int bGotLon = FALSE, bGotLat = FALSE;
481
482 if( argc >= 2 )
483 {
484 dfLon = OGR2SQLITE_GetValAsDouble(argv[0], &bGotLon);
485 dfLat = OGR2SQLITE_GetValAsDouble(argv[1], &bGotLat);
486 }
487
488 if( argc >= 3 && bGotLon && bGotLat &&
489 sqlite3_value_type (argv[2]) == SQLITE_TEXT )
490 {
491 iAfterGeomIdx = 2;
492 }
493 else if( argc >= 2 &&
494 sqlite3_value_type (argv[0]) == SQLITE_BLOB &&
495 sqlite3_value_type (argv[1]) == SQLITE_TEXT )
496 {
497 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, NULL);
498 if( poGeom != NULL && wkbFlatten(poGeom->getGeometryType()) == wkbPoint )
499 {
500 OGRPoint* poPoint = (OGRPoint*) poGeom;
501 dfLon = poPoint->getX();
502 dfLat = poPoint->getY();
503 delete poGeom;
504 }
505 else
506 {
507 delete poGeom;
508 sqlite3_result_null (pContext);
509 return;
510 }
511 iAfterGeomIdx = 1;
512 }
513 else
514 {
515 sqlite3_result_null (pContext);
516 return;
517 }
518
519 const char* pszField = (const char*)sqlite3_value_text(argv[iAfterGeomIdx]);
520
521 int i;
522 char** papszOptions = NULL;
523 for(i = iAfterGeomIdx + 1; i < argc; i++)
524 {
525 if( sqlite3_value_type (argv[i]) == SQLITE_TEXT )
526 {
527 papszOptions = CSLAddString(papszOptions,
528 (const char*)sqlite3_value_text(argv[i]));
529 }
530 }
531
532 OGRGeocodingSessionH hSession = poModule->GetGeocodingSession();
533 if( hSession == NULL )
534 {
535 hSession = OGRGeocodeCreateSession(papszOptions);
536 if( hSession == NULL )
537 {
538 sqlite3_result_null (pContext);
539 CSLDestroy(papszOptions);
540 return;
541 }
542 poModule->SetGeocodingSession(hSession);
543 }
544
545 if( strcmp(pszField, "raw") == 0 )
546 papszOptions = CSLAddString(papszOptions, "RAW_FEATURE=YES");
547
548 OGRLayerH hLayer = OGRGeocodeReverse(hSession, dfLon, dfLat, papszOptions);
549
550 OGR2SQLITE_ogr_geocode_set_result(pContext, hLayer, pszField);
551
552 CSLDestroy(papszOptions);
553
554 return;
555 }
556
557 /************************************************************************/
558 /* OGR2SQLITE_ogr_datasource_load_layers() */
559 /************************************************************************/
560
561 static
OGR2SQLITE_ogr_datasource_load_layers(sqlite3_context * pContext,int argc,sqlite3_value ** argv)562 void OGR2SQLITE_ogr_datasource_load_layers(sqlite3_context* pContext,
563 int argc, sqlite3_value** argv)
564 {
565 sqlite3* hDB = (sqlite3*) sqlite3_user_data(pContext);
566
567 if( (argc < 1 || argc > 3) || sqlite3_value_type (argv[0]) != SQLITE_TEXT )
568 {
569 sqlite3_result_int (pContext, 0);
570 return;
571 }
572 const char* pszDataSource = (const char*) sqlite3_value_text(argv[0]);
573
574 int bUpdate = FALSE;
575 if( argc >= 2 )
576 {
577 if( sqlite3_value_type(argv[1]) != SQLITE_INTEGER )
578 {
579 sqlite3_result_int (pContext, 0);
580 return;
581 }
582 bUpdate = sqlite3_value_int(argv[1]);
583 }
584
585 const char* pszPrefix = NULL;
586 if( argc >= 3 )
587 {
588 if( sqlite3_value_type(argv[2]) != SQLITE_TEXT )
589 {
590 sqlite3_result_int (pContext, 0);
591 return;
592 }
593 pszPrefix = (const char*) sqlite3_value_text(argv[2]);
594 }
595
596 OGRDataSource* poDS = (OGRDataSource*)OGROpenShared(pszDataSource, bUpdate, NULL);
597 if( poDS == NULL )
598 {
599 CPLError(CE_Failure, CPLE_AppDefined, "Cannot open %s", pszDataSource);
600 sqlite3_result_int (pContext, 0);
601 return;
602 }
603
604 CPLString osEscapedDataSource = OGRSQLiteEscape(pszDataSource);
605 for(int i=0;i<poDS->GetLayerCount();i++)
606 {
607 const char* pszLayerName = poDS->GetLayer(i)->GetName();
608 CPLString osEscapedLayerName = OGRSQLiteEscape(pszLayerName);
609 CPLString osTableName;
610 if( pszPrefix != NULL )
611 {
612 osTableName = pszPrefix;
613 osTableName += "_";
614 osTableName += OGRSQLiteEscapeName(pszLayerName);
615 }
616 else
617 {
618 osTableName = OGRSQLiteEscapeName(pszLayerName);
619 }
620
621 char* pszErrMsg = NULL;
622 if( sqlite3_exec(hDB, CPLSPrintf(
623 "CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR('%s', %d, '%s')",
624 osTableName.c_str(),
625 osEscapedDataSource.c_str(),
626 bUpdate,
627 osEscapedLayerName.c_str()),
628 NULL, NULL, &pszErrMsg) != SQLITE_OK )
629 {
630 CPLError(CE_Failure, CPLE_AppDefined,
631 "Cannot create table \"%s\" : %s",
632 osTableName.c_str(), pszErrMsg);
633 sqlite3_free(pszErrMsg);
634 }
635 }
636
637 poDS->Release();
638 sqlite3_result_int (pContext, 1);
639 }
640
641 #ifdef notdef
642 /************************************************************************/
643 /* OGR2SQLITE_ogr_GetConfigOption() */
644 /************************************************************************/
645
646 static
OGR2SQLITE_ogr_GetConfigOption(sqlite3_context * pContext,int argc,sqlite3_value ** argv)647 void OGR2SQLITE_ogr_GetConfigOption(sqlite3_context* pContext,
648 int argc, sqlite3_value** argv)
649 {
650 if( sqlite3_value_type (argv[0]) != SQLITE_TEXT )
651 {
652 sqlite3_result_null (pContext);
653 return;
654 }
655
656 const char* pszKey = (const char*)sqlite3_value_text(argv[0]);
657 const char* pszVal = CPLGetConfigOption(pszKey, NULL);
658 if( pszVal == NULL )
659 sqlite3_result_null (pContext);
660 else
661 sqlite3_result_text( pContext, pszVal, -1, SQLITE_TRANSIENT );
662 }
663
664 /************************************************************************/
665 /* OGR2SQLITE_ogr_SetConfigOption() */
666 /************************************************************************/
667
668 static
OGR2SQLITE_ogr_SetConfigOption(sqlite3_context * pContext,int argc,sqlite3_value ** argv)669 void OGR2SQLITE_ogr_SetConfigOption(sqlite3_context* pContext,
670 int argc, sqlite3_value** argv)
671 {
672 if( sqlite3_value_type (argv[0]) != SQLITE_TEXT )
673 {
674 sqlite3_result_null (pContext);
675 return;
676 }
677 if( sqlite3_value_type (argv[1]) != SQLITE_TEXT &&
678 sqlite3_value_type (argv[1]) != SQLITE_NULL )
679 {
680 sqlite3_result_null (pContext);
681 return;
682 }
683
684 const char* pszKey = (const char*)sqlite3_value_text(argv[0]);
685 const char* pszVal = (sqlite3_value_type (argv[1]) == SQLITE_TEXT) ?
686 (const char*)sqlite3_value_text(argv[1]) : NULL;
687 CPLSetConfigOption(pszKey, pszVal);
688 sqlite3_result_null (pContext);
689 }
690 #endif // notdef
691
692 #ifdef MINIMAL_SPATIAL_FUNCTIONS
693
694 /************************************************************************/
695 /* OGR2SQLITE_ST_AsText() */
696 /************************************************************************/
697
698 static
OGR2SQLITE_ST_AsText(sqlite3_context * pContext,int argc,sqlite3_value ** argv)699 void OGR2SQLITE_ST_AsText(sqlite3_context* pContext,
700 int argc, sqlite3_value** argv)
701 {
702 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, NULL);
703 if( poGeom != NULL )
704 {
705 char* pszWKT = NULL;
706 if( poGeom->exportToWkt(&pszWKT) == OGRERR_NONE )
707 sqlite3_result_text( pContext, pszWKT, -1, CPLFree);
708 else
709 sqlite3_result_null (pContext);
710 delete poGeom;
711 }
712 else
713 sqlite3_result_null (pContext);
714 }
715
716 /************************************************************************/
717 /* OGR2SQLITE_ST_AsBinary() */
718 /************************************************************************/
719
720 static
OGR2SQLITE_ST_AsBinary(sqlite3_context * pContext,int argc,sqlite3_value ** argv)721 void OGR2SQLITE_ST_AsBinary(sqlite3_context* pContext,
722 int argc, sqlite3_value** argv)
723 {
724 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, NULL);
725 if( poGeom != NULL )
726 {
727 int nBLOBLen = poGeom->WkbSize();
728 GByte* pabyGeomBLOB = (GByte*) VSIMalloc(nBLOBLen);
729 if( pabyGeomBLOB != NULL )
730 {
731 if( poGeom->exportToWkb(wkbNDR, pabyGeomBLOB) == OGRERR_NONE )
732 sqlite3_result_blob( pContext, pabyGeomBLOB, nBLOBLen, CPLFree);
733 else
734 {
735 VSIFree(pabyGeomBLOB);
736 sqlite3_result_null (pContext);
737 }
738 }
739 else
740 sqlite3_result_null (pContext);
741 delete poGeom;
742 }
743 else
744 sqlite3_result_null (pContext);
745 }
746
747 /************************************************************************/
748 /* OGR2SQLITE_SetGeom_AndDestroy() */
749 /************************************************************************/
750
OGR2SQLITE_SetGeom_AndDestroy(sqlite3_context * pContext,OGRGeometry * poGeom,int nSRSId)751 static void OGR2SQLITE_SetGeom_AndDestroy(sqlite3_context* pContext,
752 OGRGeometry* poGeom,
753 int nSRSId)
754 {
755 GByte* pabySLBLOB = NULL;
756 int nBLOBLen = 0;
757 if( poGeom != NULL && OGRSQLiteLayer::ExportSpatiaLiteGeometry(
758 poGeom, nSRSId, wkbNDR, FALSE,
759 FALSE, FALSE, &pabySLBLOB, &nBLOBLen ) == CE_None )
760 {
761 sqlite3_result_blob(pContext, pabySLBLOB, nBLOBLen, CPLFree);
762 }
763 else
764 {
765 sqlite3_result_null(pContext);
766 }
767 delete poGeom;
768 }
769
770 /************************************************************************/
771 /* OGR2SQLITE_ST_GeomFromText() */
772 /************************************************************************/
773
774 static
OGR2SQLITE_ST_GeomFromText(sqlite3_context * pContext,int argc,sqlite3_value ** argv)775 void OGR2SQLITE_ST_GeomFromText(sqlite3_context* pContext,
776 int argc, sqlite3_value** argv)
777 {
778 if( sqlite3_value_type (argv[0]) != SQLITE_TEXT )
779 {
780 sqlite3_result_null (pContext);
781 return;
782 }
783 char* pszWKT = (char*) sqlite3_value_text( argv[0] );
784
785 int nSRID = -1;
786 if( argc == 2 && sqlite3_value_type (argv[1]) == SQLITE_INTEGER )
787 nSRID = sqlite3_value_int( argv[1] );
788
789 OGRGeometry* poGeom = NULL;
790 if( OGRGeometryFactory::createFromWkt(&pszWKT, NULL, &poGeom) == OGRERR_NONE )
791 {
792 OGR2SQLITE_SetGeom_AndDestroy(pContext, poGeom, nSRID);
793 }
794 else
795 sqlite3_result_null (pContext);
796 }
797
798 /************************************************************************/
799 /* OGR2SQLITE_ST_GeomFromWKB() */
800 /************************************************************************/
801
802 static
OGR2SQLITE_ST_GeomFromWKB(sqlite3_context * pContext,int argc,sqlite3_value ** argv)803 void OGR2SQLITE_ST_GeomFromWKB(sqlite3_context* pContext,
804 int argc, sqlite3_value** argv)
805 {
806 if( sqlite3_value_type (argv[0]) != SQLITE_BLOB )
807 {
808 sqlite3_result_null (pContext);
809 return;
810 }
811
812 int nSRID = -1;
813 if( argc == 2 && sqlite3_value_type (argv[1]) == SQLITE_INTEGER )
814 nSRID = sqlite3_value_int( argv[1] );
815
816 GByte* pabySLBLOB = (GByte *) sqlite3_value_blob (argv[0]);
817 int nBLOBLen = sqlite3_value_bytes (argv[0]);
818 OGRGeometry* poGeom = NULL;
819
820 if( OGRGeometryFactory::createFromWkb(pabySLBLOB, NULL, &poGeom, nBLOBLen)
821 == OGRERR_NONE )
822 {
823 OGR2SQLITE_SetGeom_AndDestroy(pContext, poGeom, nSRID);
824 }
825 else
826 sqlite3_result_null (pContext);
827 }
828
829 /************************************************************************/
830 /* CheckSTFunctions() */
831 /************************************************************************/
832
CheckSTFunctions(sqlite3_context * pContext,int argc,sqlite3_value ** argv,OGRGeometry ** ppoGeom1,OGRGeometry ** ppoGeom2,int * pnSRSId)833 static int CheckSTFunctions(sqlite3_context* pContext,
834 int argc, sqlite3_value** argv,
835 OGRGeometry** ppoGeom1,
836 OGRGeometry** ppoGeom2,
837 int *pnSRSId )
838 {
839 *ppoGeom1 = NULL;
840 *ppoGeom2 = NULL;
841
842 if( argc != 2)
843 {
844 return FALSE;
845 }
846
847 *ppoGeom1 = OGR2SQLITE_GetGeom(pContext, argc, argv, pnSRSId);
848 if( *ppoGeom1 == NULL )
849 return FALSE;
850
851 *ppoGeom2 = OGR2SQLITE_GetGeom(pContext, argc - 1, argv + 1, NULL);
852 if( *ppoGeom2 == NULL )
853 {
854 delete *ppoGeom1;
855 *ppoGeom1 = NULL;
856 return FALSE;
857 }
858
859 return TRUE;
860 }
861
862 /************************************************************************/
863 /* OGR2SQLITE_ST_int_geomgeom_op() */
864 /************************************************************************/
865
866 #define OGR2SQLITE_ST_int_geomgeom_op(op) \
867 static \
868 void OGR2SQLITE_ST_##op (sqlite3_context* pContext, \
869 int argc, sqlite3_value** argv) \
870 { \
871 OGRGeometry* poGeom1 = NULL; \
872 OGRGeometry* poGeom2 = NULL; \
873 if( !CheckSTFunctions(pContext, argc, argv, &poGeom1, &poGeom2, NULL) ) \
874 { \
875 sqlite3_result_int(pContext, 0); \
876 return; \
877 } \
878 \
879 sqlite3_result_int( pContext, poGeom1-> op (poGeom2) ); \
880 \
881 delete poGeom1; \
882 delete poGeom2; \
883 }
884
885 OGR2SQLITE_ST_int_geomgeom_op(Intersects)
OGR2SQLITE_ST_int_geomgeom_op(Equals)886 OGR2SQLITE_ST_int_geomgeom_op(Equals)
887 OGR2SQLITE_ST_int_geomgeom_op(Disjoint)
888 OGR2SQLITE_ST_int_geomgeom_op(Touches)
889 OGR2SQLITE_ST_int_geomgeom_op(Crosses)
890 OGR2SQLITE_ST_int_geomgeom_op(Within)
891 OGR2SQLITE_ST_int_geomgeom_op(Contains)
892 OGR2SQLITE_ST_int_geomgeom_op(Overlaps)
893
894 /************************************************************************/
895 /* OGR2SQLITE_ST_int_geom_op() */
896 /************************************************************************/
897
898 #define OGR2SQLITE_ST_int_geom_op(op) \
899 static \
900 void OGR2SQLITE_ST_##op (sqlite3_context* pContext, \
901 int argc, sqlite3_value** argv) \
902 { \
903 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, NULL); \
904 if( poGeom != NULL ) \
905 sqlite3_result_int( pContext, poGeom-> op () ); \
906 else \
907 sqlite3_result_int( pContext, 0 ); \
908 \
909 delete poGeom; \
910 }
911
912 OGR2SQLITE_ST_int_geom_op(IsEmpty)
913 OGR2SQLITE_ST_int_geom_op(IsSimple)
914 OGR2SQLITE_ST_int_geom_op(IsValid)
915
916 /************************************************************************/
917 /* OGR2SQLITE_ST_geom_geomgeom_op() */
918 /************************************************************************/
919
920 #define OGR2SQLITE_ST_geom_geomgeom_op(op) \
921 static \
922 void OGR2SQLITE_ST_##op (sqlite3_context* pContext, \
923 int argc, sqlite3_value** argv) \
924 { \
925 OGRGeometry* poGeom1 = NULL; \
926 OGRGeometry* poGeom2 = NULL; \
927 int nSRSId = -1; \
928 if( !CheckSTFunctions(pContext, argc, argv, &poGeom1, &poGeom2, &nSRSId) ) \
929 { \
930 sqlite3_result_null(pContext); \
931 return; \
932 } \
933 \
934 OGR2SQLITE_SetGeom_AndDestroy(pContext, \
935 poGeom1-> op (poGeom2), \
936 nSRSId); \
937 \
938 delete poGeom1; \
939 delete poGeom2; \
940 }
941
942 OGR2SQLITE_ST_geom_geomgeom_op(Intersection)
943 OGR2SQLITE_ST_geom_geomgeom_op(Difference)
944 OGR2SQLITE_ST_geom_geomgeom_op(Union)
945 OGR2SQLITE_ST_geom_geomgeom_op(SymDifference)
946
947 /************************************************************************/
948 /* OGR2SQLITE_ST_SRID() */
949 /************************************************************************/
950
951 static
952 void OGR2SQLITE_ST_SRID(sqlite3_context* pContext,
953 int argc, sqlite3_value** argv)
954 {
955 int nSRSId = -1;
956 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, &nSRSId);
957 if( poGeom != NULL )
958 {
959 CPLPushErrorHandler(CPLQuietErrorHandler);
960 sqlite3_result_int( pContext, nSRSId );
961 CPLPopErrorHandler();
962 }
963 else
964 sqlite3_result_null(pContext);
965 delete poGeom;
966 }
967
968 /************************************************************************/
969 /* OGR2SQLITE_ST_Area() */
970 /************************************************************************/
971
972 static
OGR2SQLITE_ST_Area(sqlite3_context * pContext,int argc,sqlite3_value ** argv)973 void OGR2SQLITE_ST_Area(sqlite3_context* pContext,
974 int argc, sqlite3_value** argv)
975 {
976 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, NULL);
977 if( poGeom != NULL )
978 {
979 CPLPushErrorHandler(CPLQuietErrorHandler);
980 sqlite3_result_double( pContext, OGR_G_Area((OGRGeometryH)poGeom) );
981 CPLPopErrorHandler();
982 }
983 else
984 sqlite3_result_null(pContext);
985 delete poGeom;
986 }
987
988 /************************************************************************/
989 /* OGR2SQLITE_ST_Buffer() */
990 /************************************************************************/
991
992 static
OGR2SQLITE_ST_Buffer(sqlite3_context * pContext,int argc,sqlite3_value ** argv)993 void OGR2SQLITE_ST_Buffer(sqlite3_context* pContext,
994 int argc, sqlite3_value** argv)
995 {
996 int nSRSId = -1;
997 OGRGeometry* poGeom = OGR2SQLITE_GetGeom(pContext, argc, argv, &nSRSId);
998 int bGotVal;
999 double dfDist = OGR2SQLITE_GetValAsDouble(argv[1], &bGotVal);
1000 if( poGeom != NULL && bGotVal )
1001 OGR2SQLITE_SetGeom_AndDestroy(pContext, poGeom->Buffer(dfDist), nSRSId);
1002 else
1003 sqlite3_result_null(pContext);
1004 delete poGeom;
1005 }
1006
1007 /************************************************************************/
1008 /* OGR2SQLITE_ST_MakePoint() */
1009 /************************************************************************/
1010
1011 static
OGR2SQLITE_ST_MakePoint(sqlite3_context * pContext,int argc,sqlite3_value ** argv)1012 void OGR2SQLITE_ST_MakePoint(sqlite3_context* pContext,
1013 int argc, sqlite3_value** argv)
1014 {
1015 double dfX, dfY = 0.0;
1016 int bGotVal;
1017 dfX = OGR2SQLITE_GetValAsDouble(argv[0], &bGotVal);
1018 if( bGotVal )
1019 dfY = OGR2SQLITE_GetValAsDouble(argv[1], &bGotVal);
1020 if( !bGotVal )
1021 {
1022 sqlite3_result_null(pContext);
1023 return;
1024 }
1025
1026 OGRPoint* poPoint;
1027 if( argc == 3 )
1028 {
1029 double dfZ = OGR2SQLITE_GetValAsDouble(argv[2], &bGotVal);
1030 if( !bGotVal )
1031 {
1032 sqlite3_result_null(pContext);
1033 return;
1034 }
1035
1036 poPoint = new OGRPoint(dfX, dfY, dfZ);
1037 }
1038 else
1039 poPoint = new OGRPoint(dfX, dfY);
1040
1041 OGR2SQLITE_SetGeom_AndDestroy(pContext, poPoint, -1);
1042 }
1043
1044 #endif // #ifdef MINIMAL_SPATIAL_FUNCTIONS
1045
1046
1047 /************************************************************************/
1048 /* OGRSQLITE_hstore_get_value() */
1049 /************************************************************************/
1050
1051 static
OGRSQLITE_hstore_get_value(sqlite3_context * pContext,CPL_UNUSED int argc,sqlite3_value ** argv)1052 void OGRSQLITE_hstore_get_value(sqlite3_context* pContext,
1053 CPL_UNUSED int argc,
1054 sqlite3_value** argv)
1055 {
1056 if( sqlite3_value_type (argv[0]) != SQLITE_TEXT ||
1057 sqlite3_value_type (argv[1]) != SQLITE_TEXT )
1058 {
1059 sqlite3_result_null (pContext);
1060 return;
1061 }
1062
1063 const char* pszHStore = (const char*)sqlite3_value_text(argv[0]);
1064 const char* pszSearchedKey = (const char*)sqlite3_value_text(argv[1]);
1065 char* pszValue = OGRHStoreGetValue(pszHStore, pszSearchedKey);
1066 if( pszValue != NULL )
1067 sqlite3_result_text( pContext, pszValue, -1, CPLFree );
1068 else
1069 sqlite3_result_null( pContext );
1070 }
1071
1072 /************************************************************************/
1073 /* OGRSQLiteRegisterSQLFunctions() */
1074 /************************************************************************/
1075
1076 static
OGRSQLiteRegisterSQLFunctions(sqlite3 * hDB)1077 void* OGRSQLiteRegisterSQLFunctions(sqlite3* hDB)
1078 {
1079 OGRSQLiteExtensionData* pData = new OGRSQLiteExtensionData(hDB);
1080
1081 sqlite3_create_function(hDB, "ogr_version", 0, SQLITE_ANY, NULL,
1082 OGR2SQLITE_ogr_version, NULL, NULL);
1083
1084 sqlite3_create_function(hDB, "ogr_version", 1, SQLITE_ANY, NULL,
1085 OGR2SQLITE_ogr_version, NULL, NULL);
1086
1087 sqlite3_create_function(hDB, "ogr_deflate", 1, SQLITE_ANY, NULL,
1088 OGR2SQLITE_ogr_deflate, NULL, NULL);
1089
1090 sqlite3_create_function(hDB, "ogr_deflate", 2, SQLITE_ANY, NULL,
1091 OGR2SQLITE_ogr_deflate, NULL, NULL);
1092
1093 sqlite3_create_function(hDB, "ogr_inflate", 1, SQLITE_ANY, NULL,
1094 OGR2SQLITE_ogr_inflate, NULL, NULL);
1095
1096 sqlite3_create_function(hDB, "ogr_geocode", -1, SQLITE_ANY, pData,
1097 OGR2SQLITE_ogr_geocode, NULL, NULL);
1098
1099 sqlite3_create_function(hDB, "ogr_geocode_reverse", -1, SQLITE_ANY, pData,
1100 OGR2SQLITE_ogr_geocode_reverse, NULL, NULL);
1101
1102 sqlite3_create_function(hDB, "ogr_datasource_load_layers", 1, SQLITE_ANY, hDB,
1103 OGR2SQLITE_ogr_datasource_load_layers, NULL, NULL);
1104
1105 sqlite3_create_function(hDB, "ogr_datasource_load_layers", 2, SQLITE_ANY, hDB,
1106 OGR2SQLITE_ogr_datasource_load_layers, NULL, NULL);
1107
1108 sqlite3_create_function(hDB, "ogr_datasource_load_layers", 3, SQLITE_ANY, hDB,
1109 OGR2SQLITE_ogr_datasource_load_layers, NULL, NULL);
1110
1111 #if notdef
1112 sqlite3_create_function(hDB, "ogr_GetConfigOption", 1, SQLITE_ANY, NULL,
1113 OGR2SQLITE_ogr_GetConfigOption, NULL, NULL);
1114
1115 sqlite3_create_function(hDB, "ogr_SetConfigOption", 2, SQLITE_ANY, NULL,
1116 OGR2SQLITE_ogr_SetConfigOption, NULL, NULL);
1117 #endif
1118
1119 // Custom and undocumented function, not sure I'll keep it.
1120 sqlite3_create_function(hDB, "Transform3", 3, SQLITE_ANY, pData,
1121 OGR2SQLITE_Transform, NULL, NULL);
1122
1123 // HSTORE functions
1124 sqlite3_create_function(hDB, "hstore_get_value", 2, SQLITE_ANY, NULL,
1125 OGRSQLITE_hstore_get_value, NULL, NULL);
1126
1127 #ifdef MINIMAL_SPATIAL_FUNCTIONS
1128 /* Check if spatialite is available */
1129 int rc = sqlite3_exec(hDB, "SELECT spatialite_version()", NULL, NULL, NULL);
1130
1131 /* Reset error flag */
1132 sqlite3_exec(hDB, "SELECT 1", NULL, NULL, NULL);
1133
1134 if( rc != SQLITE_OK &&
1135 CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_SPATIAL_FUNCTIONS", "YES")) )
1136 {
1137 CPLDebug("SQLITE",
1138 "Spatialite not available. Implementing a few functions");
1139
1140 #define REGISTER_ST_op(argc, op) \
1141 sqlite3_create_function(hDB, #op, argc, SQLITE_ANY, NULL, \
1142 OGR2SQLITE_ST_##op, NULL, NULL); \
1143 sqlite3_create_function(hDB, "ST_" #op, argc, SQLITE_ANY, NULL, \
1144 OGR2SQLITE_ST_##op, NULL, NULL);
1145
1146 REGISTER_ST_op(1, AsText);
1147 REGISTER_ST_op(1, AsBinary);
1148 REGISTER_ST_op(1, GeomFromText);
1149 REGISTER_ST_op(2, GeomFromText);
1150 REGISTER_ST_op(1, GeomFromWKB);
1151 REGISTER_ST_op(2, GeomFromWKB);
1152
1153 REGISTER_ST_op(1, IsEmpty);
1154 REGISTER_ST_op(1, IsSimple);
1155 REGISTER_ST_op(1, IsValid);
1156
1157 REGISTER_ST_op(2, Intersects);
1158 REGISTER_ST_op(2, Equals);
1159 REGISTER_ST_op(2, Disjoint);
1160 REGISTER_ST_op(2, Touches);
1161 REGISTER_ST_op(2, Crosses);
1162 REGISTER_ST_op(2, Within);
1163 REGISTER_ST_op(2, Contains);
1164 REGISTER_ST_op(2, Overlaps);
1165
1166 REGISTER_ST_op(2, Intersection);
1167 REGISTER_ST_op(2, Difference);
1168 // Union() is invalid
1169 sqlite3_create_function(hDB, "ST_Union", 2, SQLITE_ANY, NULL,
1170 OGR2SQLITE_ST_Union, NULL, NULL);
1171 REGISTER_ST_op(2, SymDifference);
1172
1173 REGISTER_ST_op(1, SRID);
1174 REGISTER_ST_op(1, Area);
1175 REGISTER_ST_op(2, Buffer);
1176 REGISTER_ST_op(2, MakePoint);
1177 REGISTER_ST_op(3, MakePoint);
1178 }
1179 #endif // #ifdef MINIMAL_SPATIAL_FUNCTIONS
1180
1181 pData->SetRegExpCache(OGRSQLiteRegisterRegExpFunction(hDB));
1182
1183 return pData;
1184 }
1185
1186 /************************************************************************/
1187 /* OGRSQLiteUnregisterSQLFunctions() */
1188 /************************************************************************/
1189
1190 static
OGRSQLiteUnregisterSQLFunctions(void * hHandle)1191 void OGRSQLiteUnregisterSQLFunctions(void* hHandle)
1192 {
1193 OGRSQLiteExtensionData* pData = (OGRSQLiteExtensionData* )hHandle;
1194 delete pData;
1195 }
1196