back to XmlBlob <a page</a>
<h2>ISO Metadata - Intro</h2>
Starting since version <b>4.1.0</b> SpatiaLite will support <b>ISO Metadata</b>; all metadata documents exactly are XML Documents, so the whole ISO Metadata implementation simply is a further specialized extension based on <b>XmlBLOB</b>.<br>
Several different standard specifications are collectively grouped under the generic name of <i>ISO Metadata</i>; the common core reference is always based on <a 19115 / 19139</a>, but many further specialized extensions exist: one of the best known being e.g. the <a EU Initiative.
<h2>DB Tables implementing ISO Metadata</h2>
<verbatim>
SELECT CreateIsoMetadataTables();
</verbatim>
The <b>CreateIsoMetadataTables()</b> SQL function will attempt to create all DB Tables required in order to enable ISO Metadata support.<br>
Any attempt to directly create or otherwise manipulate these Tables is strongly discouraged; you are always expected to properly invoke <b>CreateIsoMetadataTables()</b>, which will correctly define all required Primary / Foreign Key constraints, as will create all validating Triggers expected to support these Tables.
<verbatim>
SELECT CreateIsoMetadataTables( 1 );
</verbatim>
<u>Please note</u>: <b>CreateIsoMetadataTables()</b> will optionally accept a single (<i>boolean</i>) argument intended to choose between <b>strict</b> and <b>relaxed</b> Triggers alternative implementations:
<ul>
<li>when <b>strict mode</b> is specified (<u>default setting</u>) all Metadata documents are expected to successfully pass a formal XML Schema validation.<br>
If for any reason such Schema validation fails then the Triggers will reject any attempt to insert the offending Metadata document.</li>
<li>on the other side, when <b>relaxed mode</b> is specified a basic check will be performed so to ensure that the Styling document is <u>well-formed</u>, but no XML Schema validation will never be evaluated.</li>
</ul><br>
<table width="100%" bgcolor="#ffffcc" cellpadding="4" cellspacing="10">
<tr><td>
<u><b>Important oldest versions of <b>libxml2</b> aren't able to support a successful Schema validation for <u>ISO Metadata</u> documents.
Using the most recent <b>2.9.0</b> version of this library seems to be an absolute pre-requisite.
<ul>
<li>all pre-built binaries for Windows released by SpatiaLite itself surely support <u>libxml2 2.9.0</u>, and can be thus safely used in <u>strict
<li>while building on Linux it's very probable that you'll encounter a system pre-packaged <u>libxml2</u> corresponding to some obsolete version: in this case duly switching to <u>relaxed mode</u> is strongly suggested.</li>
</ul>

</table>
<h3>the ISO_metadata table</h3>
This table is specifically intended to store all ISO Metadata documents:
<ul>
<li><b>id</b> simply is simply intended as an internal ID (<u>Primary Key</u>): no special meaning is assumed other than ensuring uniqueness within the DB table.</li>
<li><b>md_scope</b> specifies the intended scope of each Metadata document; acceptable values are:<br>

<li><b>metadata</b> is the corresponding Metadata document (expected to be a valid <b>XmlBLOB</b> of the ISO Metadata type).</li>
<li><b>fileId</b> and <b>parentId</b> respectively correspond to the values of the <b>&lt;gmd:fileIdentifier&gt;</b> and <b>&lt;gmd:parentIdentifier&gt;</b> tags internally declared by the XML document itself.</li>
<li><b>geometry</b> is a <b>MULTIPOLYGON</b> (<u>bounding box</u>) and corresponds to the <b>&lt;gmd:EX_GeographicBoundingBox&gt;</b> tag(s) internally declared by the XML document itself.<br>
<u>Please note</u>: ISO Metadata coordinates are always expected to be expressed as <u>longitudes</u> and <u>latitudes</u>, so <b>SRID=4326 is always assumed.</li>
</ul>
<h3>the ISO_metadata_reference table</h3>
This table is specifically intended to represent any possible reference joining ISO Metadata documents and geographic features stored within the same DB (<u>layers</u>, <u>coverages</u>, <u>features</u>, <u>tiles</u> and so on):
<ul>
<li><b>reference_scope</b> specifies the intended scope of each reference; acceptable values are:<br>

<li><b>table_name</b> is the name of some DB table (<u>layer</u> or
<li><b>column_name</b> is the name of some column belonging to the same table (e.g.
<li><b>row_id_value</b> could eventually reference a single row in the same table.</li>
<li><b>timestamp<b> is intended to specify the <u>data-time</u> when the latest change to the reference occurred.</li>
<li>both <b>md_file_id</b> and <b>md_parent_id</b> (<u>Foreign Key</u>) are intended to reference the corresponding ISO Metadata documents stored within the <b>ISO_metadata</b> table.<br>
<u>Please note well</u>: such references are fully based on internal numeric IDs, not an the corresponding XML tag values.</li>
</ul><br>
<h2>A practical tutorial by real examples</h2>
In this example we'll use the samples contained into the folder.<br>
<verbatim>
export "SPATIALITE_SECURITY=relaxed"
</verbatim>
For didactic simplicity we'll use the <u>unsafe SQL functions</u> supporting direct access to external files stored into the <u>file-system</u>.<br>
you have to explicitly set the <b>SPATIALITE_SECURITY=relaxed</b> environment variable in order to enable all <u>unsafe</u> SQL Import/Export functions.
<verbatim>
SELECT CreateIsoMetadataTables();
-----
1
</verbatim>
And we'll suppose that the ISO Metadata supporting tables have been created in <b>strict mode</b>, i.e. always requiring a successful XML Schema validation.
<verbatim>
SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ) ) );
-----
0
</verbatim>
The <b>RegisterIsoMetadata</b>() SQL function inserts a registration into the <b>ISO_metadata</b> table.
Any attempt aimed to directly perform any <b>INSERT</b> or <b>UPDATE</b> SQL statement on behalf of ISO_metadata is strongly discouraged; always using the abstract function RegisterIsoMetadata() is warmly recommended, and will preserve long-term compatibility against subsequent releases.
<ul>
<li>the <b>RegisterIsoMetadata()</b> SQL function in its simpler form simply requires passing two arguments:
<ul>
<li>the first argument correspond to
<li>the second argument is expected to be an appropriate <b>XmlBLOB</b> containing the Metadata definition.<br>
So we've used the <b>XB_Create()</b> SQL function in order to create a valid <b>XmlBLOB</b>; and then in turn we've invoked <b>XB_LoadXML()</b> in order to properly load the XML Document from the external file.</li>

</ul>
Anyway this first attempt was unsuccessful: we've get a <b>0</b> result, this meaning <u>failure</u>.<br>
There is very simple reason accounting for this failure: <u>we've missed to perform any XML Schema validation</u>; and accordingly to this the safety Trigger has then rejected this first attempt.
<verbatim>
SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ),
    1, 1 ) );
-----
1
</verbatim>
This second attempt is finally successful, because this time we've correctly invoked <b>XB_Create()</b> asking for full XML Schema validation.
<verbatim>
SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ),
    1, 1 ) );
-----
0
</verbatim>
Anyway a second attempt to insert yet another time the same Metadata document will be rejected; this is because a <u>uniqueness constraint</u> forbids to insert two documents declaring the same <b>fileId</b> value.
<verbatim>
SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ),
    1, 1 ) );
-----
1

SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ),
    1, 1 ), 2 );
-----
1

SELECT RegisterIsoMetadata( 'dataset',
  XB_Create( XB_LoadXML( ),
    1, 1 ), 'f0780c41-023d-41be-9ed4-f79bdb4a7886' );
-----
1
</verbatim>
<u>Please note</u>: the <b>RegisterIsoMetadata()</b> function can be also invoked in order to update an already defined Metadata document; in this case you simply have to pass the corresponding <b>id</b> as the third argument.<br>
Alternatively, if the Metadata document to be replaced/updated declares an internal <b>&lt;fileIdentifier&gt;</b> tag value, you can pass the <b>fileID</b> value as the third argument.<br>
In any case both <b>fileId</b> and <b>parentId</b> will be correctly updated so to exactly correspond to the internal tags declared within the XML documents itself; you can safely rely on this, because a couple of Triggers will always take care to ensure a strict correspondence.<br><br>
<hr>
<verbatim>
SELECT *
FROM ISO_metadata_view;
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">


<tr><td
sz=4 UNKNOWN type</td><td
<tr><td
<td>Image2000 Product 1 (nl2) Multispectral</td>
<td>IMAGE2000 product 1 individual orthorectified scenes ...</td>
<td>BLOB sz=141
<td>NULL</td><td>XmlBLOB-ISOmetadata sz=4423 (XMLsz=18026) SchemaValidated</td>
<td align="right">1</td>

<tr><td Insediativa</td>
<td>Aree adibite all'installazione ...</td>
<td>BLOB sz=141
<td>cc27d885-2795-4998-9bae-6f6872b181ce</td>
<td>XmlBLOB-ISOmetadata sz=7355 (XMLsz=58098) SchemaValidated</td>
<td
</table><br>
The <b>ISO_metadata</b> table is supported by a corresponding VIEW (not surprisingly) named <b>ISO_metadata_view</b>.<br>
<u>Please note</u>: a <u>default row</u> identified by <b>id=0</b> is always defined in the <b>ISO_metadata</b> table (being automatically created by <b>CreateIsoMetadata()</b>); its intended scope is the one to define kind-of <u>universal-placeholder</u> allowing to define metadata references still preserving full relational integrity.<br><br>
If you are curious enough, you'll quickly discover that this VIEW exactly corresponds to the original table, and that its added values is in that further <b>title</b>, <b>abstract</b>, <b>geometry(*)</b>, <b>fileId(*)</b>, <b>parentId(*)</b>, <b>schema_validated</b> and <b>metadata_schema_uri</b> columns are made immediately available.<br>
<u>(*)Please note</u>: the <b>geometry</b>, <b>fileId</b> and <b>parentId</b> columns are always directly exposed in the base table; all them are immediately set by appropriate <b>Triggers</b>, so to always correspond to the actual tag-values internally declared by the XML document itself.<br>
The following SQL functions, once invoked on behalf of the XmlBLOB payload (<b>metadata</b>), will immediately extract the corresponding values from within the XML document: <b>XB_GetTitle()</b>, <b>XB_GetAbstract()</b>, <b>XB_GetGeometry()</b>, <b>XB_GetFileId()</b>, <b>XB_GetParentId()</b>, <b>XB_IsSchemaValidated()</b> and <b>XB_GetSchemaURI()</b>.<br><br>
<hr>
<h3>advanced SQL functions supporting ISO Metadata</h3>
<verbatim>
SELECT GetIsoMetadataId( 'cd76c9a2-8138-4e7e-ad6b-2e105c37e65c' );
-----
2
</verbatim>
The <b>GetIsoMetadataId()</b> SQL function will return the internal <b>id</b> value corresponding to some <b>fileId</b>; if no corresponding Metadata document exists, then <b>ZERO</b> will be returned.
<verbatim>
UPDATE ISO_metadata
SET metadata = XB_SetParentId ( metadata , 'new-parent-id' )
WHERE id = 2;
-----
1

UPDATE ISO_metadata
SET metadata = XB_SetParentId ( metadata , 'another-parent-id' )
WHERE id = GetIsoMetadataId( 'cd76c9a2-8138-4e7e-ad6b-2e105c37e65c' );
-----
1
</verbatim>
The <b>XB_SetFileId()</b> and <b>XB_SetParentId()</b> SQL functions are respectively intended to change the <b>&lt;fileIdentifier&gt;</b> or <b>&lt;parentIdentifier&gt;</b> tag values internally declared within the XML document itself; the supporting <b>Triggers</b> will then take care to immediately make visible this change in the corresponding table/column values.<br>
<u>Please note</u>:
<ul>
<li>the new XmlBLOB object (the one containing the modified identifier) returned by these functions will support <b>compression</b> and <b>schema validation</b> exactly as defined by the input XmlBLOB.</li>
<li>these functions simply allow to an already defined XML tag value.</li>
</ul>
<verbatim>
UPDATE ISO_metadata
SET metadata = XB_AddParentId ( metadata , CreateUUID() ,
    'gmd' , ,
    'gco' , )
WHERE id = 1;
-----
1
</verbatim>
The <b>XB_AddFileId()</b> and <b>XB_AddParentId()</b> SQL functions are respectively intended to insert a <b>&lt;fileIdentifier&gt;</b> <b>&lt;parentIdentifier&gt;</b> tag values internally declared within the XML document itself; the supporting <b>Triggers</b> will then take care to immediately make visible this change in the corresponding table/column values.<br>
<u>Please note</u>:
<ul>
<li>the new XmlBLOB object (the one containing the modified identifier) returned by these functions will support <b>compression</b> and <b>schema validation</b> exactly as defined by the input XmlBLOB.</li>
<li>these functions simply allow to <u>insert</u> a new (not already defined) XML tag value.</li>
<li>as you can easily notice, the <b>XB_Add??Id()</b> functions require more arguments than the corresponding <b>XB_Set??Id()</b> functions:
<ul>
<li>third and fourth arguments are expected to specify the <u>namespace</u> to be adopted for the <b>&lt;fileIdentifier&gt;</b> or <b>&lt;parentIdentifier&gt;</b> XML tags.</li>
<li>fifth and sixth arguments play exactly the same identical role, this time defining the <u>namespace</u> to be adopted for the <b>&lt;CharacterString&gt;</b> XML tag.</li>
<li>all these arguments could be set to <b>NULL</b> when appropriate.</li>

</ul>
<u>Remember</u>:
<ul>
<li>an <u>XML namespace</u> is always identified by an <u>URL</u> and may eventually be associated to a <u>prefix</u>.<br>
So, assuming that a <b>gco</b> namespace is already defined in the target XML document (... ...), you can simply pass <b>'gco', NULL</b> (or <b>NULL, as the fifth and sixth arguments, and in both cases a <b>&lt;gco:CharacterString&gt;</b> XML tag will be created, because you can reference an already defined namespace by <u>prefix</u> or by <u>URL</u> indifferently.</li>
<li>if a so called <u>default namespace</u> is already defined in the target XML document (... ...), you can simply pass <b>'gmd', NULL</b> (or <b>NULL, or else <b>NULL, NULL</b>) as the fourth and fifth arguments, and in any case a <b>&lt;fileIdentifier&gt;</b> XML tag (no prefix) will be created.</li>
<li>anyway: always passing a full qualified namespace (explicitly specifying both <u>prefix</u> and <u>URL</u>) is surely better and avoid many possible pitfalls; in this case an appropriate <b>xmlns</b> declaration will be automatically inserted into the XML document.</li>
</ul>
<hr><br>
back to XmlBlob <a page</a>