tutorial
Not logged in
Home Timeline Branches Tags Tickets Wiki Login
Help

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);
    • this first API will return the current version of the library.
  2. VIRTUALPG_DECLARE int virtualpg_extension_init (sqlite3 *db_handle);
    • this second API is intened to initialize the VirtualPG extension module:
      • db_handle must be a valid handle to some already established SQLite connection.
      • the return value will be SQLITE_OK if the extension module has been succesfully initialized.
Notes:

a very skeletal C source sample 
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <virtualpg.h>

int
main (int argc, char *argv[])
{
    const char *path = ... some value ....
    sqlite3 *handle;
    virtualPQ virtual_api;
    int ret;

/* opening the SQLite connection */
    ret = sqlite3_open_v2 (path, &handle, SQLITE_OPEN_READWRITE, NULL);
    if (ret != SQLITE_OK)
    {
        ... error handling ...
    }

/* initializing the PQ virtual API */
    virtual_api.PQclear = ...
    virtual_api.PQconnectdb = ...
    ...

/* initializing VirtualPG */
    ret = virtualpg_extension_init (handle, &virtual_api);
    if (ret != SQLITE_OK)
    {
        ... error handling ...
    }
    else
        fprintf (stderr, "ok, VirtualPG succesfully initialized\n");

/* disconnecting from SQLite and exiting */
    sqlite3_close (handle);
    return 0;
}

about virtualPQ

libvirtualpg depends on the PostgreSQL client library (libpq) but you can freely choose between two alternative mechanisms for linking libpq:
  1. hard-linking (aka early-binding): this is the most familiar one.
    you just have to directly link -lpq to the application calling libvirtualpg; then your code, when calling virtualpg_extension_init(), will directly pass all PQ API pointers to the extension module.
    This way your application will fully depend from the dynamic library libpq; if this should ever fail to be loaded for any reason your application will be completely unable to start.
  2. soft-linking (aka late-binding): your application will always start without loading libpq; this dynamic library will be actually loaded only when really required.
    If, for any reason, libpq should ever fail to be loaded your application will survive without negative effects (except than PostgreSQL integration will be obviously unavailable).
    virtualPQ virtual_api;

*/ initialization of the virtual API based on hard-linked LibPQ */
    virtual_api.PQclear = PQclear;
    virtual_api.PQconnectdb = PQconnectdb;
    virtual_api.PQerrorMessage = PQerrorMessage;
    virtual_api.PQexec = PQexec;
    virtual_api.PQfinish = PQfinish;
    virtual_api.PQgetisnull = PQgetisnull;
    virtual_api.PQgetvalue = PQgetvalue;
    virtual_api.PQlibVersion = PQlibVersion;
    virtual_api.PQnfields = PQnfields;
    virtual_api.PQntuples = PQntuples;
    virtual_api.PQresultStatus = PQresultStatus;
    virtual_api.PQstatus = PQstatus;
The above code snippet exemplifies how-to initialize the Virtual API in the hard-linked scenario.
Note: in this case your application is expected to be linked by specifying (at least) -lvirtualpg -lsqlite3 -lpq. 
    virtualPQ virtual_api; 
    wxDynamicLibrary DynamicLibPQ;

*/ initialization of the virtual API based on dynamically loaded LibPQ */
    if (DynamicLibPQ.Load(path, wxDL_QUIET))
    {
      bool ok;
      virtual_api.PQclear = (void (*)(PGresult *))       
             DynamicLibPQ.GetSymbol(wxT("PQclear"), &ok);
      virtual_api.PQconnectdb = (PGconn * (*)(const char *conninfo))
             DynamicLibPQ.GetSymbol(wxT("PQconnectdb"), &ok);
      virtual_api.PQerrorMessage = (char *(*)(const PGconn * conn))
             DynamicLibPQ.GetSymbol(wxT("PQerrorMessage"), &ok);
      virtual_api.PQexec = (PGresult * (*)(PGconn * conn, const char *command))
             DynamicLibPQ.GetSymbol(wxT("PQexec"), &ok);
      virtual_api.PQfinish = (void (*)(PGconn * conn))
             DynamicLibPQ.GetSymbol(wxT("PQfinish"), &ok);
      virtual_api.PQgetisnull = (int (*)(const PGresult * res, int row_number, int column_number))
             DynamicLibPQ.GetSymbol(wxT("PQgetisnull"), &ok);
      virtual_api.PQgetvalue = (char *(*)(const PGresult * res, int row_number, int column_number))
             DynamicLibPQ.GetSymbol(wxT("PQgetvalue"), &ok);
      virtual_api.PQlibVersion = (int (*)(void))
             DynamicLibPQ.GetSymbol(wxT("PQlibVersion"), &ok);
      virtual_api.PQnfields = (int (*)(const PGresult * res))
             DynamicLibPQ.GetSymbol(wxT("PQnfields"), &ok);
      virtual_api.PQntuples = (int (*)(const PGresult * res))
             DynamicLibPQ.GetSymbol(wxT("PQntuples"), &ok);
      virtual_api.PQresultStatus = (ExecStatusType(*)(const PGresult * res))
             DynamicLibPQ.GetSymbol(wxT("PQresultStatus"), &ok);
      virtual_api.PQstatus = (ConnStatusType(*)(const PGconn * conn))
             DynamicLibPQ.GetSymbol(wxT("PQstatus"), &ok);
    }
This second code snippet exemplifies how-to initialize the Virtual API in the late binding scenario.
The code is based on the cross-platform class wxDynamicLibrary supported by wxWidgets.
Linux natively supports dlopen() and dlsym(), whilst Windows supports its own LoadLibrary() and GetProcAddress(); all them are very similar in design, and the overall approach is almost the same.
Note: in this case your application is expected to be linked by specifying only -lvirtualpg -lsqlite3, but not -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):
    • W: the Table will support both read and write operations, enabling SELECT / INSERT / UPDATE / DELETE statements.
    • -: the Table will just support read operations: SELECT statements will be enabled, but INSERT / UPDATE / DELETE will be disabled.
      Note: this is the default setting. VirtualPG always requires an explicit authorization in order to enable write operations.
  5. optional: julian_flag (Text String):
    • J: DATE / TIME / TIMESTAMP values will be translated into the corresponding Julian Day Numbers (FLOAT values).
    • -: DATE / TIME / TIMESTAMP values will be translated into the corresponding standard SQL Text Strings..
      Note: this is the default setting. VirtualPG always requires an explicit authorization in order to enable Julian Days.


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 AsEWKB().

Back to VirtualPG home page
Keep in touch ... join the community, subscribe the SpatiaLite Users mailing list
VirtualPG is developed and maintained by Alessandro Furieri a.furieri@lqt.it
Fossil version 2.13 [e7bba4ff36] 2020-11-01 00:13:51