D 2018-07-16T11:18:01.587
L tutorial
P 701eea8e48f630d8fc398fd44d6736e83c3c7bb2
U sandro
W 21046
Back to <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/index">home page</a>
<br><br>
<h2>Updated documentation supporting VirtualPG v2</h2>
<table cellspacing="6" cellpadding="6" bgcolor="#ffd080"><tr><td>
<h3>Warning</h3>
The following informations correspond to the most recent <b>Version 2</b> of VirtualPG, and could not always adequately cover the previous versions, that are now declared obsolete.<br>
If you are still using any previous version you are warmly reccommended to quickly update your software.
</td></tr></table>
<br><br>
<table cellspacing="6" cellpadding="6" bgcolor="#d0ff80"><tr><td>
<h3>Short summary of differences between earlier versions and v2</h3>
<ol>
<li>now VirtualPG is available as both a standard C/C++ library (<b>libvirtualpg</b>) and a dynamic extension module to SQLite (<b>mod_virtualpg</b>).</li>
<li>now the user can freely choose his/her preferred format for DATE / TIME / TIMESTAMP values.</li>
<li>Transactions (BEGIN / COMMIT / ROLLBACK) are now fully honoured also on the PostgreSQL side.</li>
<li>Write operations (INSER / UPDATE / DELETE) are now allowed only if the PostgreSQL user has the corresponding GRANT permissions.</li>
<li>Write operations can be enabled only for PostgreSQL Tables explicitly declaring a PRIMARY KEY.</li>
</ol>
</td></tr></table>
<h2>about VirtualPG</h2>
<b>VirtualPG 2</b> is available both as a <i>classic <b>C/C++</b> library</i> and as a <i>loadable dynamic extension module</i> for both <b>SQLite</b> and <b>SpatiaLite</b>.<br>
The intended scope of VirtualPG is supporting direct data exchange form/to <b>PostgreSQL</b> / <b>PostGIS</b> targets and <b>SQLite</b> simply using basic, plain <i>SQL statements</i>.<br><br>
<hr>
<h3>VirtualPG as a standard C/C++ library</h3>
There is very little to say about VirtualPG as a library, because it implements just two C functions:
<ol>
<li><b>VIRTUALPG_DECLARE const char *virtualpg_version (void);</b>
<ul>
<li>this first API will return the current version of the library.</li>
</ul></li>
<li><b>VIRTUALPG_DECLARE int virtualpg_extension_init (sqlite3 *db_handle);</b>
<ul>
<li>this second API is intened to initialize the VirtualPG extension module:
<ul>
<li><b>db_handle</b> must be a valid handle to some already established SQLite connection.</li>
<li>the return value will be <b>SQLITE_OK</b> if the extension module has been succesfully initialized.</li>
</ul></li>
</ul></li>
</ol>
<b>Notes</b>:<br>
<ul>
<li>the <b>VirtualPG</b> extension does not necessarily require to be supported by the <b>SpatiaLite</b> extension; they are independent the one from the other.</li>
<li>the relative order in which the two extensions are eventually initialized is absolutely not relevant.</li>
<li>however, when VirtualPG is backed up by SpatiaLite, you'll always be able to enquiry in full detail any error message raised by PostgreSQL by calling the SQL function <b>PostgreSql_GetLastMessage()</b>.<br>
this is a really useful diagnostic function, and will greatly improve the usability of VirtualPG, allowing for a simplified and easier debugging.</li>
<li>when SpatiaLite's support is unavailable, VirtualPG will simply attempt to output the error message coming from PostgreSQL on the standard error (if available).</li>
<li><b>Note</b>: the diagnostic SQL function <b>PostgreSQL_GetLastError() </b>requires using SpatiaLite version 5.0.0 or later.</li>
</ul><br>
<b>a very skeletal C source sample</b>
<verbatim>
#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;
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;
}
</verbatim><br>
<b>Link directives</b><br>
the above C sample obviously requires to link both <b>libsqlite3</b> and <b>libvirtualpg</b> but this later depends on <b>libpq</b>, the PostgreSQL client library.<br>
so these are the required directives for a succesful linking: <b>-lvirtualpg -lsqlite3 -lpq</b>
<br><br>
<hr>
<h3>VirtualPG as a dynamic extension module</h3>
The extension module simply is an ordinary <i>dynamic library</i> (<b>.dll</b> on Windows, <b>.so</b> on Unix and Linux, <b>.dylib</b> 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.
<verbatim>
SELECT load_extension('mod_virtualpg');
------
NULL
</verbatim>
<b>Note</b>:
<ul>
<li>this mechanism just relies on the internal capabilities of SQLite, so it's expected to work on any possible language: Java, Python, PHP, C# and so on.</li>
<li>all recent versions of SQLite are smart enough to not require specifying any platform-dependent suffix.<br>
just passing <b>mod_spatialite</b> as the extension name should be enough on any correctly configured platform.</li>
<li>the <b>VirtualPG</b> extension does not necessarily require to be supported by the <b>SpatiaLite</b> extension; they are independent the one from the other.</li>
<li>the relative order in which the two extensions are eventually loadeed is absolutely not relevant.</li>
<li>however, when VirtualPG is backed up by SpatiaLite, you'll always be able to enquiry in full detail any error message raised by PostgreSQL by calling the SQL function <b>PostgreSql_GetLastMessage()</b>.<br>
this is a really useful diagnostic function, and will greatly improve the usability of VirtualPG, allowing for a simplified and easier debugging.</li>
<li>when SpatiaLite's support is unavailable, VirtualPG will simply attempt to output the error message coming from PostgreSQL on the standard error (if available).</li>
<li><b>Note</b>: the diagnostic SQL function <b>PostgreSQL_GetLastError() </b>requires using SpatiaLite version 5.0.0 or later.</li>
</ul><br>
<table cellspacing="6" cellpadding="6" bgcolor="#ffffe8" width="100%">
<tr><td align="center"><b>Useful hints and suggestions: troubleshooting</b></td></tr>
<tr><td><hr></td></tr>
<tr><td>
This operation could eventually fail for several different reasons: here are the most frequents:
<ul>
<li>You are using some odd version of SQLite3/libsqlite3 forbidding to load any dynamic extensions.<br>
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.</li>
<li>The operating system could be unable to correctly locate the dynamic library to be loaded.<br>
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 <b>.dll</b> or <b>.so</b> suffix.<br>
If you wish to do so, you can eventually specify the full path leading to the dynamic library, this including any suffix.<br>
The default behaviour of <b>load_extension()</b> is the one to search the required dynamic library following the standard platform specific rules; please consult the appropriate documentation for more specific details.
</li>
<li>The dynamic library itself could be correctly located, but some further depending library could not, thus forbidding to load the main library.<br>
<u>Please note</u>: <b>VirtualPG</b> depends on the PostgreSQL own client library (<b>libpq</b>).</li>
<li>In any case, if you are using some interactive tool such as <b>sqlite3</b> or <b>spatialite</b> some useful and meaningful error message will surely be displayed explaining the failure cause.<br>
This could not be supported while attempting to load an extension from some language binding (e.g. <b>.NET</b>, <b>Python</b> or <b>Java</b>).
In this case attempting to identify the real error cause could be really painful due to poor and sloppy diagnostic support.</li>
<li>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.</li>
</ul>
<b>Important notice</b>: starting since version <b>3.7.17</b> SQLite introduced many interesting advanced features to <b>load_extension()</b>;
earlier versions of SQLite could probably be someway limited.
If this is your case, please consider that:
<ul>
<li>specifying any <b>.dll</b>, <b>.so</b> or <b>.dylib</b> suffix could be strictly required.</li>
<li>as it could be required to explicitly specify the extension entry point.</li>
<li>accordingly to all this, you'll probably be required to pass arguments like the following ones to <b>load_extension()</b> while using some obsolete SQLite3 version.</li>
<verbatim>
SELECT load_extension('/usr/local/lib/mod_virtualpg.so', 'sqlite3_virtualpg_init');
------
NULL
</verbatim>
</ul>
</td></tr>
</table><br>
<hr>
<h2>Using VirtualPG: an SQL perspective</h2>
<h3>Creating VirtualPostgres tables</h3>
After successfully loading the <b>virtualpg</b> dynamic extension you are now ready to create <b>VirtualPostges</b> tables.<br>
These are <i>quite almost</i> ordinary DB tables as any other; you can execute any regular SQL statement (<i>SELECT</i>, <i>INSERT</i>, <i>UPDATE</i> or <i>DELETE</i>) by specifying these Virtual Tables as the selected target.<br>
There is only a strong difference distinguishing a <b>VirtualPostgres</b> table; it's physical location isn't within the currently connected SQLite DB, but is instead within some <b>PostgreSQL</b> DBMS.<br>
So a VirtualPostgres table effectively acts as a <i>bridge</i> joining SQLite and Postgres, and allowing to perform any kind of cross data exchange (<i>in both directions</i>) in the easiest way.
<verbatim>
CREATE VIRTUAL TABLE pg_in
USING VirtualPostgres ('host=localhost port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);
</verbatim>
This SQL statement will create into the SQLite DB a table named <b>pg_in</b> wrapping the PostgreSQL table named <b>my_schema</b>.<b>my_table</b>.<br>
Data for this table will continue to be stored within the Postgres own data space, but will be now accessible for SQLite:
<ul>
<li>the first argument to VirtualPostgres is the <i>connection string</i> required in order to establish a connection to the Postgres DBMS server.</li>
<li>second and third arguments respectively are the <i>schema name</i> and the <i>table name</i> identifying the Postgres target table.</li>
</ul>
<verbatim>
CREATE VIRTUAL TABLE pg_in
USING VirtualPostgres ('hostaddr=192.168.1.91 port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);
</verbatim>
Same as above, this time establishing a connection to a Postgres instance running on some different computer on the same local area network.<br>
<verbatim>
CREATE VIRTUAL TABLE pg_in
USING VirtualPostgres ('host=www.humptydumpty.com port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);
</verbatim>
In this third variation we'll connect to a Postgres instance running on some remote server accessed though a wide area network.<br>
Please consult the Postgres own documentation for any other detail about <i>connection strings</i>.
<h3>Creating VirtualPostgres tables: full syntax explained</h3>
<verbatim>
CREATE VIRTUAL TABLE somename USING VirtualPostgres {conn_info, schema, table [ , read_write_flag [ , julian_flag ]] };
</verbatim>
Supported arguments:
<ol>
<li><i>mandatory</i>: <b>conn_info</b> (<i>Text String</i>)<br>
any valid connection string accepted by PostgreSQL. For further details please consult the <a href="">PostgreSQL own</a> documentation.</li>
<li><i>mandatory</i>: <b>schema</b> (<i>Text String</i>)<br>
name of the PosgreSQL Schema containing the Table to be wrapped.</li>
<li><i>mandatory</i>: <b>table</b> (<i>Text String</i>)<br>
name of the PosgreSQL Table (or View) to be wrapped.</li>
<li><i>optional</i>: <b>read_write_flag</b> (<i>Text String</i>):
<ul>
<li><b>W</b>: the Table will support both read and write operations, enabling SELECT / INSERT / UPDATE / DELETE statements.</li>
<li><b>-</b>: the Table will just support read operations: SELECT statements will be enabled, but INSERT / UPDATE / DELETE will be disabled.<br>
<b>Note</b>: this is the default setting. VirtualPG always requires an explicit authorization in order to enable write operations.</li>
</ul></li>
<li><i>optional</i>: <b>julian_flag</b> (<i>Text String</i>):
<ul>
<li><b>J</b>: DATE / TIME / TIMESTAMP values will be translated into the corresponding <a href="https://en.wikipedia.org/wiki/Julian_day">Julian Day Numbers</a> (FLOAT values).</li>
<li><b>-</b>: DATE / TIME / TIMESTAMP values will be translated into the corresponding standard SQL Text Strings..<br>
<b>Note</b>: this is the default setting. VirtualPG always requires an explicit authorization in order to enable Julian Days.</li>
</ul></li>
</ol><br><br>
<table cellspacing="6" cellpadding="6" bgcolor="#ffffe8" width="100%">
<tr><td align="center"><b>Useful hints: connecting to remote PostgreSQL istances via Port Forwarding / SSH Tunneling</b></td></tr>
<tr><td><hr></td></tr>
<tr><td>
Whenever you require to connect some remote PostgreSQL istance (i.e. one actually running on some remote server requiring to establish a <b>LAN</b> or <b>WAN</b> connection) you should always seriously consider using <b>SSH Tunneling</b> / <b>Port Forwarding</b>.<br>
This could greatly simplify your approach, ensuring at the same time an uncompromised safety.<br><br>
Please read <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=port-forwarding">this Wiki page</a> for more specific details about SSH Tunneling and Port Forwarding.
</td></tr></table><br>
<hr>
<h3>Basic SQL operations</h3>
<verbatim>
PRAGMA table_info(pg_in);
</verbatim>
You can execute the above PRAGMA directive in order to identify all columns declared by the Postgres table, and their corresponding data-type.
<verbatim>
SELECT a, b, c, Count(*) AS cnt
FROM pg_in
WHERE e IS NOT NULL
GROUP BY a, b, c
ORDER BY cnt DESC;
</verbatim>
You can execute any kind of <b>SELECT</b> query using a VirtualPostgres table, exactly as if it was a genuine native table.
<verbatim>
CREATE TABLE imported_from_pg AS
SELECT * FROM pg_in;
</verbatim>
You can obviously create and populate a local copy of the remote Postgres table: now all data are permanently stored within your SQLite DB.
<verbatim>
BEGIN;
COMMIT;
ROLLBACK;
</verbatim>
Transactions are honoured on the PostgreSQL side as well, when a VirtualPostgres Table supports INSERT / UPDATE / DELETE statements.
<verbatim>
DROP TABLE pg_in;
</verbatim>
Finally, you can drop the VirtualPostgres table, and this will immediately terminate the connection to the Postgres DBMS.<br><br>
<hr>
<h3>data-type mapping and related conversions</h3>
Rather obviously SQLite and Postgres supports very different kind of data-types. The few following rules apply:
<ul>
<li>any kind of <b>INTEGER</b> value is preserved as such (this including <b>BIGINT</b>).</li>
<li>the same is for <b>FLOAT</b> values.</li>
<li><b>MONEY</b> and <b>NUMERIC</b> values are converted into <b>FLOAT</b> values.</li>
<li><b>CHAR</b> and <b>VARCHAR</b> values are always converted into <b>TEXT</b> values.</li>
<li>Handling of <b>DATE</b>, <b>TIME</b> and <b>TIMESTAMP</b> values depends on the specifc flag (<b>J</b>) declared in the <b>CREATE VIRTUAL TABLE</b> statement:
<ul>
<li>as <b>Text values</b><br>
DATE, TIME and TIMESTAMP values are always converted into <b>TEXT string</b> accordingly to the standard SQL representations:
<ul>
<li>an example of DATE value: <b>2018-08-16</b></li>
<li>an example of TIME value: <b>12:30:45</b></li>
<li>an example of TIMESTAMP value: <b>2018-08-16 12:30:45</b></li>
</ul></li>
<li>as <b>Julian Numbers</b><br>
DATE, TIME and TIMESTAMP values are always converted into <b>FLOAT</b> values corresponding to the <a href="http://www.sqlite.org/lang_datefunc.html">JulianDay</a> notation supported by SQLite.<br>
You can then invoke the appropriate function between <b>Date()</b>, <b>Time()</b> or <b>DateTime()</b> so to get back the standard SQL notation for dates and times.</li>
<li><b>Hint</b>: using TEXT strings is more natural and make easier reading the date-time related values.<br>
using JULIAN NUMBERS make simpler performing date-time related computations, as e.g. computing the elapsed interval (in days) between two dates.</li>
</ul></li>
<li><b>BOOL</b> values are always converted into <b>INTEGER</b> values: <b>0</b> corresponding to <b>FALSE</b>.</li>
<li>Postgres supports multiple values to be stored into the same column (<b>ARRAY</b>): SQLite has no similar capability, so any ARRAY value will simply be converted into a <b>TEXT</b> string, e.g. as in <b>'{1,2,3}'</b> or <b>'{alpha,beta,gamma,delta}'</b>.</li>
<li>any binary value (as e.g. <b>BYTEA</b>) will be simply converted into a <b>TEXT</b> value containing the corresponding hexadecimal notation, e.g. as in <b>'0101000020E8640000C4FC34BA8448244150C1B5D541B85341'</b>.</li>
</ul><br>
<hr>
<h3>handling PostGIS geometries</h3>
PostGIS own geometries simply are represented by a <b>BYTEA</b> value to be interpreted accordingly to <b>EWKB</b> format specifications.
<verbatim>
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
</verbatim>
This sequence of SQL statements (SpatiaLite support is strictly required) will transfer a fully qualified Geometry table from PostGIS to SpatiaLite:
<ul>
<li><b>step 1</b>: first we'll transfer the whole PostGIS table into SQLite; geometries are still represented by their <b>EWKB</b> hexadecimal notations.</li>
<li><b>step 2</b>: then we'll use the <b>GeomFromEWKB()</b> SQL function so to get the corresponding <b>SpatiaLite's BLOB</b> for each geometry.</li>
<li><b>step 3</b>: now we simply have to discover the Type, SRID and Dimensions declared by these geometries.</li>
<li><b>step 4</b>: and finally we can now recover a full qualified SpatiaLite's own (<i>native</i>) Geometry column.</li>
</ul><br>
<hr>
<h3>exporting data from SQLite to Postgres/PostGIS</h3>
For rather obvious security reasons, all VirtualPostgres tables are declared to be <i>read-only</i> by default.<br>
This actually means that you are enabled to execute any possible <b>SELECT</b> query, but any <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> operation affecting the Postgres DBMS is strictly forbidden.
<verbatim>
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);
</verbatim>
A special <b>W</b> flag must be specified when creating a VirtualPostgres Table in order to allow for unconstrained <i>read-write</i> operations, this including any <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement affecting the Postgres DBMS.
<verbatim>
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;
</verbatim>
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.<br>
Please note, we'll invoke the <b>AsEWKB()</b> SQL function in order to convert any SpatiaLite's own <b>BLOB</b> geometry into the equivalent <b>BYTEA EWKB</b> notation required by PostGIS.<br><br>
<b>Note</b>: starting a Transaction (<b>BEGIN/COMMIT</b>) 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.<br><br>
<table cellspacing="6" cellpadding="6" bgcolor="#ffffe8" width="100%">
<tr><td align="center"><b>Caveat: AsEWKB()</b></td></tr>
<tr><td><hr></td></tr>
<tr><td>
All earlier versions of SpatiaLite released before <b>4.2.0</b> supported a buggish AsEWKB() implementation completely unable to create valid EWKB Geometries to be passed to PostGIS.<br>
Using version <b>4.2.0</b> (or any later) is strictly required so to really get a working implementation of AsEWKK().
</td></tr>
</table>
<br>
Back to <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/index">home page</a>
Z 6e02c496f0f494178a7a14669c0bf1df