artemp/trac2github — Issue #822: auto-detection of sqlite primary key field
Author: artemp
Created: 2011-10-09 20:52:21+00:00
State: open
Labels: Core_Library

Currently unless the `key_field` is supplied by the user the special, built in `rowid` is used: http://www.sqlite.org/lang_createtable.html#rowid

Rowid is critical for spatial index usage, and assuming 'rowid' works great (http://trac.mapnik.org/browser/trunk/plugins/input/sqlite/sqlite_datasource.cpp#L72) for using spatial indexes when table is a flat table name.

But subqueries are commonly used and subqueries like: `(select * from table)` drops the rowid. So, even if table has a rowid which references `myid` the results of the subquery can no longer be referenced by `rowid` and must rather be referenced by `myid`.

Hence it would be really nice if we could auto-detect the actual name of the field which rowid refers to. This practically means using `pragma table_info` to query the metadata and determine which, of any, fields are PRIMARY KEY AUTOINCREMENTING http://www.sqlite.org/c3ref/table_column_metadata.html