= FDO RFC 16 - FDO Provider for SQLite =

This page contains a request for comments document (RFC) for the FDO Open Source project.  
More FDO RFCs can be found on the [wiki:FDORfcs RFCs] page.

== Status == 
 
||RFC Template Version||(1.0)|| 
||Submission Date||2008-03-18||
||Last Modified||Jason Birch 2008-03-24||
||Author||Traian Stanev||
||RFC Status||draft||
||Implementation Status||under development||
||Proposed Milestone||||
||Assigned PSC guide(s)||(when determined)||
||'''Voting History'''||(vote date)||
||+1||||
||+0||||
||-0||||
||-1||||
 
== Overview ==

This RFC proposes a new FDO provider for accessing (spatial) data stored in SQLite database files.

== Motivation ==

SQLite is an efficient and popular way to store data. Support for accessing SQLite databases via FDO would expand the range of data sources supported by FDO.
A SQLite format for storing spatial data would make a good interchange format, similar to, but more flexible than Shapefiles.

== What makes a valid spatial SQLite database? ==

The goal of this simple specification is to allow access to SQLite databases in as open and standard way as possible.  This document does ''not'' conform to the OGC Simple Features for SQL implementation specification ([http://www.opengeospatial.org/standards/sfs 06-104r3]), but it does leverage the specification where practical.

Here is the minimum set of characteristics expected in SQLite database:

 * well-defined subset of the geometry_columns table (as per SF spec, section 7.1.3.2, 7.1.3.3); see below for specific column requirements,
 * spatial_ref_sys table (as per SF spec, section 7.1.2.2, 7.1.2.3).  Valid WKT spatial reference systems must be defined for each unique coordinate system used in the database.
 * geometry_format column extending the geometry_columns table, set to one of WKB, WKT, WKB12, WKT12 or FGF. If not present, WKB will be assumed.
 * one or more tables to store the geometry and associated features.  Although in general [http://www.sqlite.org/datatype3.html type does not matter in SQLite], BLOB type is recommended at least for WKB and FGF formats as it does not perform character translation on the data.

Note that this implementation does ''not'' currently include support for the enhanced multi-dimensional WKB and WKT formats that are defined in the OGC Simple Feature Access specification [http://www.opengeospatial.org/standards/sfa 06-103r3].  The tags WKB12 and WKT12 are reserved for future use in support of this enhanced format.  FGF refers to the [http://fdo.osgeo.org/files/fdo/docs/FDG_FDODevGuide/files/WSfacf1429558a55de8821c21057fbebc2b-789.htm FDO Binary Geometry Format].

== geometry_columns Subset ==

SQLite databases require the following fields in their geometry_columns table.  Other fields are permitted, but will not affect data access.  For detailed explanations of all of the standard fields (everything but geometry_format), see the SFSQL [http://www.opengeospatial.org/standards/sfs 06-104r3] document, section 7.1.3.3.

|| Column Name || Type || Notes ||
|| f_table_name || VARCHAR || name of a feature table with a geometry column. ||
|| f_geometry_column || VARCHAR || name of a geometry column in the feature table ||
|| geometry_type || INTEGER || integer value representing the form of geometry (1=POINT, 2=LINESTRING, etc).  See Table 4 on Page 29-31 of the SFSQL [http://www.opengeospatial.org/standards/sfs 06-104r3] spec for a full list. ||
|| coord_dimension || INTEGER || dimensionality of the geometric features: 2, 3 or 4 ||
|| srid || INTEGER || foreign key into the spatial_ref_sys table ||
|| geometry_format || VARCHAR || One of "WKB", "WKT", "WKB12", "WKT12", "FGF" ||

The suggested definition is:

{{{
CREATE TABLE geometry_columns (
            f_table_name VARCHAR, 
            f_geometry_column VARCHAR, 
            geometry_type INTEGER, 
            coord_dimension INTEGER, 
            srid INTEGER,
            geometry_format VARCHAR )
}}}

== Proposed FDO Provider ==

The proposed SQLite provider will have the following features:

 * Data stored and consumed in native SQLite format, and therefore accessible to other SQLite-based applications;
 * Geometry will be serialized in FDO geometry format (FGF), WKB, or WKT and stored in a BLOB column.  The type of geometry encoding will be specified in the geometry_columns table in a column named geometry_format. In addition, the provider may support point geometry with coordinates stored in non-geometric columns, via a connection string setting.
 * A non-persistent spatial index usable for BBOX queries, built on the fly and valid for the duration of the FDO connection;
 * Execution of arbitrary SQL commands;
 * Metadata about feature geometry and coordinate systems will be stored in OGC geometry_columns and spatial_ref_sys tables;
 * A tentative list of supported FDO commands : !DescribeSchema, Select, !SelectAggregates, Insert, Update, Delete, !GetSpatialContexts, arbitrary SQL, !ApplySchema, !CreateDataStore;

FDO features the provider will initially *not* support:

 * Feature class inheritance;
 * Geometry filters other than BBOX;
 * FDO Transactions -- but you can still manually execute BEGIN/COMMIT around other FDO commands;
 * Associations;
 * Constraints;
 * Any other exotic feature you can think of.

== Implications ==

This is a new provider, so there will be no issues with backwards compatibility. We need to coordinate with the GDAL/OGR project (and any other data access libraries that express an interest) to make sure the metadata and geometry formats are usable cross-platform.

== Test Plan ==

There will be a small test suite that tests often used functionality. Arbitrary SQL execution will not be tested extensively.

== Funding/Resources ==


== References ==

 * [http://www.sqlite.org/ SQLite]
 * [http://www.gaia-gis.it/spatialite/ SpatialLite] - a spatial extension for SQLite
 * [http://fdo.osgeo.org/files/fdo/docs/FDG_FDODevGuide/files/WSfacf1429558a55de8821c21057fbebc2b-789.htm FGF Binary Specification]
 * [http://www.opengeospatial.org/standards/sfs OGC Simple Features for SQL] (see 06-104r3)
 * [http://www.opengeospatial.org/standards/sfa OGC Simple Feature Access] (see 06-103r3)