D 2013-03-06T10:40:09.389
L 4.1.0\sChanges
P 6c07bec5b4ee74785cb801a3f92db90d9db3e5b3
U sandro
W 14770
back to XmlBlob <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=XmlBlob+and+VirtualXPath">main page</a>
<h2>New SQL functions introduced in 4.1.0</h2>
<h3>Generic SQL functions - type casting</h3>
SQLite offers just a very basic type checking; this is sometimes useful, but could be really annoying very often.<br>
<verbatim>
SELECT '1', TypeOf( '1' ), CAST ( '1' AS INTEGER ), TypeOf( CAST ( '1' AS INTEGER ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>'1'</th><th>TypeOf( '1' )</th><th>CAST ( '1' AS INTEGER )</th><th>TypeOf( CAST ( '1' AS INTEGER ) )</th></tr>
<tr><td>1</td><td>text</td><td align="right">1</td><td>integer</td></tr></table><br>
The SQL <b>CAST</b> operator already supports the possibility to explicitly set a data-type; now starting since 4.1.0 SpatiaLite supports few SQL functions performing the same task.<br>
<u>Please note</u>: some subtle differences exists here and there; please carefully read this documentation page.
<verbatim>
SELECT CastToInteger( 1 ), CastToInteger( 1.4 ), CastToInteger( 1.6 ),
CastToInteger( '123' ), CastToInteger( 'alpha' ), CastToInteger ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToInteger( 1 )</th><th>CastToInteger( 1.4 )</th><th>CastToInteger( 1.6 )</th><th>CastToInteger( '123' )</th><th>CastToInteger( 'alpha' )</th><th>CastToInteger ( zeroblob(4) )</th></tr>
<tr><td align="right">1</td><td align="right">1</td><td align="right">2</td><td align="right">123</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToInteger()</b> SQL function will attempt to return a value of the <b>Integer</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will be returned absolutely unaffected.</li>
<li>a Double input argument will return the <u>nearest</u> Integer value (after applying <b>4/5</b> rounding).</li>
<li>a Text input argument will return the corresponding Integer value only if the text string represents a valid number (even a decimal one): if the text string cannot be converted to a number NULL will be returned.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT CastToDouble( 1 ), CastToDouble( 1.23 ), CastToDouble( '123.45' ),
CastToDouble( 'alpha' ), CastToDouble ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToDouble( 1 )</th><th>CastToDouble( 1.23 )</th><th>CastToDouble( '123.45' )</th><th>CastToDouble( 'alpha' )</th><th>CastToDouble ( zeroblob(4) )</th></tr>
<tr><td align="right">1.000000</td><td align="right">1.230000</td><td align="right">123.45000</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToDouble()</b> SQL function will attempt to return a value of the <b>Double</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Double value.</li>
<li>a Double input argument will be returned absolutely unaffected.</li>
<li>a Text input argument will return the corresponding Double value only if the text string represents a valid number: if the text string cannot be converted to a number NULL will be returned.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT CastToText( 1 ), CastToText( 1.5 ), CastToText( 'alpha' ), CastToText ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1 )</th><th>CastToText( 1.5 )</th><th>CastToText( 'alpha' )</th><th>CastToText ( zeroblob(4) )</th></tr>
<tr><td>1</td><td>1.5</td><td>alpha</td><td>NULL</td></tr></table><br>
The <b>CastToText()</b> SQL function will attempt to return a value of the <b>Text</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Text value.</li>
<li>a Double input argument will be return the corresponding Text value; no significant decimal digit will be suppressed.</li>
<li>a Text input argument will return the corresponding Double value.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<verbatim>
SELECT CastToText( 1, 4 ), CastToText( 1234, 2 ), CastToText( 1, 8 ), CastToText( 1.5, 4 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1, 4 )</th><th>CastToText( 1234, 2 )</th><th>CastToText( 1, 8 )</th><th>CastToText( 1.5, 4 )</th></tr>
<tr><td>0001</td><td>1234</td><td>00000001</td><td>0001.5</td></tr></table><br>
An overloaded version of <b>CastToText()</b> is supported as well, but only when the input argument is of the Integer or Double data-type: in this case a second Integer argument could be optionally specified, and will be interpreted ad <b>zero-padding-length</b>.<br>
i.e. the returned Text string in this case will have an integer part at least of the specified length, and eventually padded by inserting not significant leading ZEROes.<br>
<hr>
<verbatim>
SELECT CastToBlob( 1 ), CastToBlob( 1.5 ), Hex( CastToBlob( 'alpha' ) ), Hex ( CastToBlob ( zeroblob(4) ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToBlob( 1 )</th><th>CastToBlob( 1.5 )</th><th>Hex( CastToBlob( 'alpha' ) )</th><th>Hex( CastToText ( zeroblob(4) ) )</th></tr>
<tr><td>NULL</td><td>NULL</td><td>616C706861</td><td>00000000</td></tr></table><br>
The <b>CastToBlob()</b> SQL function will attempt to return a value of the <b>BLOB</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return a NULL value.</li>
<li>a Double input argument will return a NULL value.</li>
<li>a Text input argument will return the corresponding BLOB value.</li>
<li>any BLOB input argument will return the corresponding BLOB value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT ForceAsNull( 'a', 'b' ), ForceAsNull( 'abcd', 'abcd' ), ForceAsNull( 1, 1), ForceAsNull( 1.1, 1 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>ForceAsNull( 'a', 'b' )</th><th>ForceAsNull( 'abcd', 'abcd' )</th><th>ForceAsNull( 1, 1)</th><th>ForceAsNull( 1.1, -9999 )</th></tr>
<tr><td>a</td><td>NULL</td><td>NULL</td><td align="right">1.1</td></tr></table><br>
The <b>ForceAsNull()</b> SQL function will evaluate two arbitrary arguments.<br>
If they are equal and exactly of the same data-type NULL will be returned; otherwise the first argument value will be returned.<br>
Useful e.g. while processing data imported from any <b>Shapefile</b> or <b>DBF file</b> (such formats doesn't support any real NULL value, often being replaced by some conventional value e.g. <b>0</b> or <b>-9999</b> and alike).
<hr>
<h3>Generic SQL function - UUID generator</h3>
An <a href="http://en.wikipedia.org/wiki/Universally_unique_identifier">Universally unique identifier</a> (aka <b>UUID</b>) simply is a <u>128 bit</u> Integer; accordingly to <a href="http://en.wikipedia.org/wiki/UUID#Random_UUID_probability_of_duplicates">theory</a>, the probability that two randomly generated UUIDs could assume the same value is near to zero for many practical purposes. And consequently UUIDs are widely used as <u>universally unique IDs</u>.<br>
Usually UUIDs are represented in their <u>canonical form</u>, i.e. as a sequence of 32 hexadecimal digits separated into five distinct block, accordingly to a <b>8-4-4-4-12</b> schema.
<verbatim>
SELECT CreateUUID();
--------
fff5faf1-dcc0-4391-8054-6e7de75d85b1
</verbatim>
The <b>CreateUUID()</b> SQL function is built on the top the <i>High Quality Pseudo-Random Number Generator</i> <a href="http://www.sqlite.org/c3ref/randomness.html">internally implemented</a> by SQLite.<br>
The returned UUID is conformant to <u>Version 4 (random)</u> (i.e. the first digit into the third block will always be <b>4</b> and the first digit into the fourth block will always be <b>8</b>).
<hr>
<h3>Spatial SQL functions</h3>
Few more Spatial SQL functions are now supported.
<verbatim>
SELECT ST_AsText( ST_AddPoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0 )' ),
ST_GeomFromText( 'POINT( 1 1 )' ) ) );
--------
LINESTRING(0 0, 1 0, 1 1)
</verbatim>
The <b>ST_AddPoint()</b> SQL function adds a further Point/Vertex at the end of a Linestring; the first argument is always expected to be of the Linestring type, the second one must be a Point.
<verbatim>
SELECT ST_AsText( ST_AddPoint(
ST_GeomFromText( 'LINESTRING( 1 0, 1 1 )' ),
ST_GeomFromText( 'POINT( 0 0 )' ), 0 ) );
---------
LINESTRING(0 0, 1 0, 1 1)
</verbatim>
If an optional third argument (of the Integer type) is passed to <b>ST_AddPoint()</b> it's intended to specify the <u>index</u> of the new Vertex (first Vertex has index 0, second Vertex has index 1 and so on).<br>
Referencing a not existing index is an error, and will return NULL.
<verbatim>
SELECT ST_AsText( ST_AddPoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0 )' ),
ST_GeomFromText( 'POINT( 1 1 )' ), -1 ) );
--------
LINESTRING(0 0, 1 0, 1 1)
</verbatim>
Passing a negative index is a supported option, and simply corresponds to the default behaviour (i.e. <i>appending the Point to the end of the Linestring</i>).
<hr>
<verbatim>
SELECT ST_AsText( ST_SetPoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0, 1 1 )' ),
1, ST_GeomFromText( 'POINT( 2 1 )' ) ) );
---------
LINESTRING(0 0, 2 1, 1 1)
</verbatim>
The <b>ST_SetPoint()</b> SQL function can replace a Point/Vertex from within a Linestring; the first argument is always expected to be of the Linestring type, the second is an Integer specifying the <u>index</u> of the affected Vertex and the third one must be a Point.<br>
Referencing a not existing index is an error, and will return NULL.
<hr>
<verbatim>
SELECT ST_AsText( ST_RemovePoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0, 1 1 )' ), 1 ) );
---------
LINESTRING(0 0, 1 1)
</verbatim>
The <b>ST_RemovePoint()</b> SQL function can remove a Point/Vertex from within a Linestring; the first argument is always expected to be of the Linestring type, the second is an Integer specifying the <u>index</u> of the affected Vertex.<br>
Referencing a not existing index is an error, and will return NULL.
<hr>
<h3>Unsafe (but useful) Import/Export SQL functions</h3>
All the following SQL functions could be really useful in order to support Import/Export operations.<br>
Anyway you should be <u>well aware</u> that all them could be potentially <u>unsafe</u> and could be potentially used by some <u>malicious hacker</u> in order to create and exploit some <u>security breach</u>.<br>
The cause is very easy to be understood: these functions allow to transfer arbitrary payloads from / to the Database and the local File-System.
A malicious attack could be thus implemented e.g. by executing some SQL script or by defining a purposely forged Trigger, and the end users would be absolutely unaware of what is really happening.
So in order to effectively shield users against these potentially dangerous security pitfalls all these SQL functions are always <u>disabled by default</u>.
<verbatim>
export "SPATIALITE_SECURITY=relaxed"
</verbatim>
In order to really enable these functions the user is required to take an explicit action; i.e. the <u>environment variable</u> <b>SPATIALITE_SECURITY=relaxed</b> has to be defined.
<verbatim>
SELECT CountUnsafeTriggers();
---------
0
</verbatim>
The <b>CountUnsafeTriggers()</b> actually checks if the connected Database does contains any suspect Trigger; checking this version before enabling the unsafe SQL Functions is always highly recommended.<br>
A result different from ZERO implies that some malicious Trigger has been identified; in this case you can perform a deepest inspection by executing the following SQL query:
<verbatim>
SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger' AND (sql LIKE '%BlobFromFile%'
OR sql LIKE '%BlobToFile%' OR sql LIKE '%XB_LoadXML%'
OR sql LIKE '%XB_StoreXML%');
</verbatim>
this will give a full report for any malicious Trigger eventually found.
<hr>
<verbatim>
SELECT BlobFromFile( 'C:/mypictures/sunset.jpg' );
---------
BLOB-value
</verbatim>
The <b>BlobFromFile()</b> SQL function allows to import a whole file in a single step; the full file payload will be returned as a BLOB value.<br>
If the argument (<b>file-path</b>) doesn't corresponds to a valid file or if the external file cannot be accessed for any reason NULL will be returned.
<hr>
<verbatim>
SELECT BlobToFile( someBlob , 'C:/mypictures/sunset.jpg' );
---------
1
</verbatim>
The <b>BlobToFile()</b> SQL function performs the opposite task, i.e. it exports a whole BLOB into an external file; the first argument must correspond to some BLOB, the second one is the <b>file-path</b> identifying the file to be created or overwritten.<br><br>
<u>Please note</u>: both <b>BlobFromFile()</b> and <b>BlobToFile()</b> were already supported by 4.0.0, but a quick recall is anyway useful so to recall a clearer context.
<hr>
<verbatim>
SELECT XB_LoadXML( 'C:/mydocs/isometadata-sample.xml' );
--------
BLOB-value
SELECT XB_LoadXML( 'http://www.acme.com/public/isometadata-sample.xml' );
--------
BLOB-value
</verbatim>
The <b>XB_LoadXML()</b> SQL function allows to import an XML Document in a single step: the full XML Document payload will be returned as a BLOB value.<br>
The passed argument can indifferently be a <b>file-path</b> or a <b>URL</b>; if the datasource cannot be accessed, or if it doesn't contains a <u>well-formed</u> XML Document NULL will be returned.
<hr>
<verbatim>
SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml' );
---------
1
</verbatim>
The <b>XB_StoreXML()</b> SQL function performs the opposite task, i.e. it exports a whole XmlBLOB into an external file as an XML Document; the first argument must correspond to some valid XmlBLOB, the second one is the <b>file-path</b> identifying the file to be created or overwritten.
<verbatim>
SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml', 4 );
---------
1
</verbatim>
A further optional <b>indentation</b> argument can be eventually specified; this argument will be handled exactly in the same way already specified for both <b>XB_GetPayload()</b> and <b>XB_GetDocument()</b> functions.
<hr><br>
back to XmlBlob <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=XmlBlob+and+VirtualXPath">main page</a>
Z 0cc6b51c26f6219d234579663f74e250