[gdal-dev] OGR as a dynamically loadable sqlite3 extension [was Re: new "SQLite dialect" and spatialite]

[Even Rouault]

Le mardi 21 août 2012 19:38:51, a.furieri at lqt.it a écrit :
> Hi Even,
> 
> if I understand well the new "SQLite style dialect" you've
> developed for OGR is mainly based on a VirtualTable.
> 
> Do you have considered that this new feature could be
> really nice to be supported in SpatiaLite as well ?
> 
> I easily imagine that it could be really interesting
> for many users routinely using spatialite-gui or
> spatialite-cli as their preferred SQL front-end tools
> "magically" accessing any generic OGR datasource.
> 
> just few quick technical highlights:
> a) imagining that your VirtualTable OGR-driver could
>     be made available as a "dynamic extension" to SQLite
> b) then simply invoking: SELECT load_extension('even-ogr.dll');
>     will automatically enable both the CLI and the GUI tools
>     to access any OGR supported datasource ... it would be
>     simply fantastic, no ?
> c) in other words: once your extension will be loaded, then
>     an user simply has to create the appropriate OGR VirtualTable,
>     using some syntax looking more or less like:
>     CREATE VIRTUAL TABLE xxx USING VirtualOgr(connection-string);
> 
> the nicest thing I can foresee is in that this approach has
> exactly a ZERO impact on building and installing :-D
> 
> if the "ogr-virtual-extension" library (and all the GDAL/OGR depending
> libraries) could be actually loaded at run time, all is done.
> otherwise you'll simply get some error; no other consequence at all.
> 
> it fully depends on run-time configuration (and installation); there
> is absolutely no need to establish some permanent link between
> spatialite and ogr.
> 
> it looks a very exciting future perspective for me; what do
> you think about all this ?

Sandro,

This is a brillant suggestion and I've just implemented and committed it. It 
required few changes. The OGR virtual extension library is just the OGR 
library for the sake of simplicity.

So here's some things that you can do :

$ sqlite3
SELECT load_extension("libspatialite.so");
SELECT load_extension("libgdal.so");
SELECT ogr_version();
CREATE VIRTUAL TABLE poly USING VirtualOGR("poly.shp");
CREATE VIRTUAL TABLE poly2 USING VirtualOGR("poly.shp", 0);
CREATE VIRTUAL TABLE poly3 USING VirtualOGR("poly.shp", 0, "poly");
SELECT *, aswkt(geometry) FROM poly;

The syntax for VirtualOGR is the following one :

CREATE VIRTUAL TABLE xxx USING VirtualOGR \
     (datasource_name[, update_mode, [layer_name[, expose_ogr_style]]])

* If only datasource_name is specified, then it is opened in read-only mode, 
and it will check that there's only one layer in it, which it will pick up.
* update_mode can be 0 (read-only) or 1 (update mode) to select how the 
datasource is opened.
* expose_ogr_style can be 0 (disabled) or 1 (enabled) to map the special OGR 
attribute StyleString to a OGR_STYLE attribute. Defaults to 0.

This will need to be documented somewhere.

Best regards,

Even