= 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||Traian Stanev 2008-03-19||
||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 provider is to allow access to SQLite databases in as open and standard way as possible.
Here is the minimum set of characteristics the provider will expect in SQLite database:

 * geometry_columns table, as per SF spec, Part 2, SQL Option, section 6.2.1;
 * spatial_ref_sys table as per SF spec;
 * g_geometry_format column in geometry_columns table, set to one of WKB. FGF or WKT. If not present, WKB will be assumed as geometry format.


== 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 as BLOBs in FDO geometry format (FGF), WKB , or WKT 1.2 format. The type of geometry encoding will be specified in the geometry_columns table (see below) in a column named g_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, according to Section 6.2.1 of the OGC Simple Feature spec, Part 2;
 * 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.


== geometry_columns ==

|| Column Name || Type || Notes ||
|| f_table_catalog || VARCHAR || blank ||
|| f_table_schema || VARCHAR || blank ||
|| 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 ||
|| f_geometry_format || VARCHAR || One of "WKT", "WKB", "FGF", ... (should we differentiate between 1.0 and 1.2 geometries?) ||
|| coord_dimension || ? || 2, 3 or 4 ||
|| srid || ? || shared key with spatial_ref_sys table ||

== Implications ==

This is a new provider, so there will be no issues with backwards compatibility. We need to coordinate with the GDAL/OGR project
to make sure the metadata and geometry formats are usable by both FDO and OGR.

== 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)