D 2018-07-16T11:18:01.587 L tutorial P 701eea8e48f630d8fc398fd44d6736e83c3c7bb2 U sandro W 21046 Back to VirtualPG home page

Updated documentation supporting VirtualPG v2

Warning

The following informations correspond to the most recent Version 2 of VirtualPG, and could not always adequately cover the previous versions, that are now declared obsolete.
If you are still using any previous version you are warmly reccommended to quickly update your software.


Short summary of differences between earlier versions and v2

  1. now VirtualPG is available as both a standard C/C++ library (libvirtualpg) and a dynamic extension module to SQLite (mod_virtualpg).
  2. now the user can freely choose his/her preferred format for DATE / TIME / TIMESTAMP values.
  3. Transactions (BEGIN / COMMIT / ROLLBACK) are now fully honoured also on the PostgreSQL side.
  4. Write operations (INSER / UPDATE / DELETE) are now allowed only if the PostgreSQL user has the corresponding GRANT permissions.
  5. Write operations can be enabled only for PostgreSQL Tables explicitly declaring a PRIMARY KEY.

about VirtualPG

VirtualPG 2 is available both as a classic C/C++ library and as a loadable dynamic extension module for both SQLite and SpatiaLite.
The intended scope of VirtualPG is supporting direct data exchange form/to PostgreSQL / PostGIS targets and SQLite simply using basic, plain SQL statements.

VirtualPG as a standard C/C++ library

There is very little to say about VirtualPG as a library, because it implements just two C functions:
  1. VIRTUALPG_DECLARE const char *virtualpg_version (void);
  2. VIRTUALPG_DECLARE int virtualpg_extension_init (sqlite3 *db_handle);
Notes:

a very skeletal C source sample #include #include #include #include int main (int argc, char *argv[]) { const char *path = ... some value .... sqlite3 *handle; int ret; /* opening the SQLite connection */ ret = sqlite3_open_v2 (path, &handle, SQLITE_OPEN_READWRITE, NULL); if (ret != SQLITE_OK) { ... error handling ... } /* initializing VirtualPG */ ret = virtualpg_extension_init (handle); if (ret != SQLITE_OK) { ... error handling ... } else fprintf (stderr, "ok, VirtualPG succesfully initialized\n"); /* disconnecting from SQLite and exiting */ sqlite3_close (handle); return 0; }
Link directives
the above C sample obviously requires to link both libsqlite3 and libvirtualpg but this later depends on libpq, the PostgreSQL client library.
so these are the required directives for a succesful linking: -lvirtualpg -lsqlite3 -lpq

VirtualPG as a dynamic extension module

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 Note:
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 extensions 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.
    The default behaviour of load_extension() is the one to search the required dynamic library following the standard platform specific rules; please consult the appropriate documentation for more specific details.
  • 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 or spatialite 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: starting since version 3.7.17 SQLite 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 the following ones to load_extension() while using some obsolete SQLite3 version.
    • SELECT load_extension('/usr/local/lib/mod_virtualpg.so', 'sqlite3_virtualpg_init'); ------ NULL

Using VirtualPG: an SQL perspective

Creating 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 wrapping the PostgreSQL table named my_schema.my_table.
Data for this table will continue to be stored within the Postgres own data space, but will be now accessible for SQLite: 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, this time establishing a connection to a Postgres instance running on some different computer on the same local area network.
CREATE VIRTUAL TABLE pg_in USING VirtualPostgres ('host=www.humptydumpty.com port=5432 dbname=gis user=sandro password=secret', my_schema, my_table); In this third variation we'll connect to a Postgres instance running on some remote server accessed though a wide area network.
Please consult the Postgres own documentation for any other detail about connection strings.

Creating VirtualPostgres tables: full syntax explained

CREATE VIRTUAL TABLE somename USING VirtualPostgres {conn_info, schema, table [ , read_write_flag [ , julian_flag ]] }; Supported arguments:
  1. mandatory: conn_info (Text String)
    any valid connection string accepted by PostgreSQL. For further details please consult the PostgreSQL own documentation.
  2. mandatory: schema (Text String)
    name of the PosgreSQL Schema containing the Table to be wrapped.
  3. mandatory: table (Text String)
    name of the PosgreSQL Table (or View) to be wrapped.
  4. optional: read_write_flag (Text String):
  5. optional: julian_flag (Text String):


Useful hints: connecting to remote PostgreSQL istances via Port Forwarding / SSH Tunneling
Whenever you require to connect some remote PostgreSQL istance (i.e. one actually running on some remote server requiring to establish a LAN or WAN connection) you should always seriously consider using SSH Tunneling / Port Forwarding.
This could greatly simplify your approach, ensuring at the same time an uncompromised safety.

Please read this Wiki page for more specific details about SSH Tunneling and Port Forwarding.

Basic SQL operations

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. 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; You can obviously create and populate a local copy of the remote Postgres table: now all data are permanently stored within your SQLite DB. BEGIN; COMMIT; ROLLBACK; Transactions are honoured on the PostgreSQL side as well, when a VirtualPostgres Table supports INSERT / UPDATE / DELETE statements. DROP TABLE pg_in; Finally, you can 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:

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:

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); A special W flag must be specified when creating a VirtualPostgres Table in order to allow for unconstrained read-write operations, this including any INSERT, UPDATE or DELETE statement affecting the Postgres DBMS. BEGIN; 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; COMMIT; 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.

Note: starting a Transaction (BEGIN/COMMIT) on the PosgreSQL side always is a suggested good practice when writing many data on the PostgreSQL target, and will have a positive impact on overall performances.

Caveat: AsEWKB()
All earlier versions of SpatiaLite released before 4.2.0 supported a buggish AsEWKB() implementation completely unable to create valid EWKB Geometries to be passed to PostGIS.
Using version 4.2.0 (or any later) is strictly required so to really get a working implementation of AsEWKK().

Back to VirtualPG home page Z 6e02c496f0f494178a7a14669c0bf1df