VirtualPG: Update of "tutorial"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID:	bc485d9d859c269a22c34dfce38aa483e3b39beb
Page Name:	tutorial
Date:	2017-06-03 10:43:24
Original User:	sandro
Parent:	028b24c51958dd4b9558c3e818a14c5a0413fddd (diff)
Next	701eea8e48f630d8fc398fd44d6736e83c3c7bb2

Content

Back to VirtualPG home page

about VirtualPG

VirtualPG is a loadable dynamic extension module for both SQLite and SpatiaLite; its intended scope is supporting direct data exchange form/to PostgreSQL and PostGIS targets simply using basic, plain SQL statements.
The extension module simply is an ordinary dynamic library (.dll on Windows, .so on Unix and Linux, .dylib on Mac Os X); so the first thing to be done in order to activate this extension is loading the corresponding library from a valid SQLite/SpatiaLite connection.

SELECT load_extension('mod_virtualpg');
------
NULL

Useful hints and suggestions: troubleshooting

This operation could eventually fail for several different reasons: here are the most frequents:

You are using some odd version of SQLite3/libsqlite3 forbidding to load any dynamic extensions.
This feature could eventually be disabled at build time; if this is your case you simple have to switch to some different SQLite toolkit supporting dynamic extension or build one by yourself.
The operating system could be unable to correctly locate the dynamic library to be loaded.
The most recent versions of SQLite are smart enough to automatically handle any platform specific suffix: so you are no longer required to specify any .dll or .so suffix. If you wish to do so, you can eventually specify the full path leading to the dynamic library, this including any suffix.
Anyway the default behaviour of load_extension() is the one to search the required dynamic library following the standard platform specific rules.
The dynamic library itself could be correctly located, but some further depending library could not, thus forbidding to load the main library.
Please note: VirtualPG depends on the PostgreSQL own client library (libpq).
In any case, if you are using some interactive tool such as sqlite3, spatialite or spatialite_gui some useful and meaningful error message will surely be displayed explaining the failure cause.
This could not be supported while attempting to load an extension from some language binding (e.g. .NET, Python or Java). In this case attempting to identify the real error cause could be really painful due to poor and sloppy diagnostic support.
There are no universal rules dictating where to place your extension modules; a good suggestion is to always place all them in the most appropriate system directory.

Important notice: the very recent SQLite 3.7.17 introduced many interesting advanced features to load_extension(); earlier versions of SQLite could probably be someway limited. If this is your case, please consider that:

specifying any .dll, .so or .dylib suffix could be strictly required.
as it could be required to explicitly specify the extension entry point.
accordingly to all this, you'll probably be required to pass arguments like to following ones to load_extension() while using some obsolete SQLite3 version.

SELECT load_extension('/usr/local/lib/mod_virtualpg.so', 'sqlite3_virtualpg_init');
------
NULL

cross-DBMS data exchange: SQL and VirtualPostgres tables

After successfully loading the virtualpg dynamic extension you are now ready to create VirtualPostges tables.
These are quite almost ordinary DB tables as any other; you can execute any regular SQL statement (SELECT, INSERT, UPDATE or DELETE) by specifying these Virtual Tables as the selected target.
There is only a strong difference distinguishing a VirtualPostgres table; it's physical location isn't within the currently connected SQLite DB, but is instead within some PostgreSQL DBMS.
So a VirtualPostgres table effectively acts as a bridge joining SQLite and Postgres, and allowing to perform any kind of cross data exchange (in both directions) in the easiest way.

CREATE VIRTUAL TABLE pg_in 
    USING VirtualPostgres ('host=localhost port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);

This SQL statement will create into the SQLite DB a table named pg_in; anyway the data physical storage for this table will be into the Postgres DBMS:

the first argument to VirtualPostgres is the connection string required in order to establish a connection to the Postgres DBMS server.
second and third arguments respectively are the schema name and the table name identifying the Postgres target table.

CREATE VIRTUAL TABLE pg_in 
    USING VirtualPostgres ('hostaddr=192.168.1.91 port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);

Same as above, the time establishing a connection to a Postgres instance running on some different computer on the same local area network.
Please consult the Postgres own documentation for any other detail about connection strings.

PRAGMA table_info(pg_in);

You can execute the above PRAGMA directive in order to identify all columns declared by the Postgres table, and their corresponding data-type.
If you are actually using spatialite_gui you can check the same infos by using the appropriate graphics tools.

SELECT a, b, c, Count(*) AS cnt
FROM pg_in
WHERE e IS NOT NULL
GROUP BY a, b, c
ORDER BY cnt DESC;

You can execute any kind of SELECT query using a VirtualPostgres table, exactly as if it was a genuine native table.

CREATE TABLE imported_from_pg AS
SELECT * FROM pg_in;

And you can obviously create and populate a local copy of the remote Postgres table: now all data are permanently stored within your SQLite DB.

DROP TABLE pg_in;

So you can finally drop the VirtualPostgres table, and this will immediately terminate the connection to the Postgres DBMS.

data-type mapping and related conversions

Rather obviously SQLite and Postgres supports very different kind of data-types. The few following rules apply:

any kind of INTEGER value is preserved as such.
the same is for FLOAT values.
MONEY and NUMERIC values are converted into FLOAT values.
CHAR and VARCHAR values are always converted into TEXT values.
DATE, TIME and TIMESTAMP values are always converted into FLOAT values corresponding to the JulianDay notation supported by SQLite.
You can then invoke the appropriate function between Date(), Time() or DateTime() so to get back the standard SQL notation for dates and times.
BOOL values are always converted into INTEGER values: 0 corresponding to FALSE.
Postgres supports multiple values to be stored into the same column (ARRAY): SQLite has no similar capability, so any ARRAY value will simply be converted into a TEXT string, e.g. as in '{1,2,3}' or '{alpha,beta,gamma,delta}'.
any binary value (as e.g. BYTEA) will be simply converted into a TEXT value containing the corresponding hexadecimal notation, e.g. as in '0101000020E8640000C4FC34BA8448244150C1B5D541B85341'.

handling PostGIS geometries

PostGIS own geometries simply are represented by a BYTEA value to be interpreted accordingly to EWKB format specifications.

CREATE TABLE from_postgis AS
SELECT * FROM pg_in;

UPDATE from_postgis SET geometry = GeomFromEWKB(geometry);

SELECT DISTINCT ST_GeometryType(geometry), ST_Srid(geometry), CoordDimension(geometry)
FROM from_postgis;
------------
LINESTRING|25832|XY

SELECT RecoverGeometryColumn('from_postgis', 'geometry', 25832, 'LINESTRING', 'XY');
------------
1

This sequence of SQL statements (SpatiaLite support is strictly required) will transfer a fully qualified Geometry table from PostGIS to SpatiaLite:

step 1: first we'll transfer the whole PostGIS table into SQLite; geometries are still represented by their EWKB hexadecimal notations.
step 2: then we'll use the GeomFromEWKB() SQL function so to get the corresponding SpatiaLite's BLOB for each geometry.
step 3: now we simply have to discover the Type, SRID and Dimensions declared by these geometries.
step 4: and finally we can now recover a full qualified SpatiaLite's own (native) Geometry column.

exporting data from SQLite to Postgres/PostGIS

For rather obvious security reasons, all VirtualPostgres tables are declared to be read-only by default.
This actually means that you are enabled to execute any possible SELECT query, but any INSERT, UPDATE or DELETE operation affecting the Postgres DBMS is strictly forbidden.

CREATE VIRTUAL TABLE pg_out 
    USING VirtualPostgres ('hostaddr=192.168.1.91 port=5432 dbname=gis user=sandro password=secret', my_schema, my_table, W);

Anyway you can explicitly request a VirtualPostgres table to support unconstrained read-write operations, this including any INSERT, UPDATE or DELETE statement affecting the Postgres DBMS.
In this case you simply have to pass a further fourth argument into the USING clause exactly corresponding to the letter W.

DELETE FROM pg_out;

INSERT INTO pg_out (fld_a, fld_b, fld_c, geometry)
SELECT fld_a, fld_b, fld_c, AsEWKB(geometry)
from some_table;

In the above example we'll delete first any already existing row from the PostGIS table; then we'll simply transfer the whole table from SpatiaLite into PostGIS.
Please note, we'll invoke the AsEWKB() SQL function in order to convert any SpatiaLite's own BLOB geometry into the equivalent BYTEA EWKB notation required by PostGIS.

Caveat: AsEWKB()

Any previous version of SpatiaLite (this including the current 4.1.1) supported a buggish AsEWKB() implementation completely unable to create valid EWKB Geometries to be passed to PostGIS. I presume that nobody really attempted to use this function before, so this bug passed completely unnoticed since now.
Using the latest version of the code available from the Fossil repository is strictly required so to really get a working AsEWKK().

Back to VirtualPG home page