Hi,
recently I've had a few requests to make primary key
lookup more flexible in JDBC-NG datastores, and finally I've
got someone that could be interested in sponsoring some
improvements (provided they stay inside a small budget).
The current code uses a set of heuristics to find out
what a pk is and how it should be managed:
- use the table metadata to find the pk columns
- failing that, see if there is an unique index
If no pk column is found, we assume the table is not
writable and the fids are going to be randomly generated.
Once the pk is determined, the heuristics to manage
it are:
- we try to see if the column is auto-incrementing
- we try to see if the column is backed by a sequence
- otherwise we assume the FeatureId to be inserted
will drive the creation of the PK itself
This is a bit un-flexible for a few reasons:
- people might want to get stable fids out of views
(for queriying them later)
- in most databases the mapping between sequence
and column cannot be determined easily
What I'm proposing is that we roll out and interface
for a primary key lookup strategy, and that we provide
three default implementations of it:
- one based on the current heuristics
- one based on a lookup table I'm going to describe later
- a composite one that we can use to chain in fallback
multiple strategies
The interface would be simple:
interface PrimaryKeyFinder {
PrimaryKey getPrimaryKey(String schema, String table, Connection cx);
}
(or shall we do an abstract class with no implementation to allow
adding methods in the future? Can't foresee any atm).
And JDBCDataStore would expose a getter and a setter for it.
The table based implementation would be the first in
the default chain, allowing admins to override the pk
lookup. The table would look like:
- schema: schema name
- table: table name
- column: column name
- idx: column index if pk is multicolumn (nullable)
- policy: pk assignment policy: "assigned", "sequence", "autogenerated"
- sequence: full name of the sequence to be used to generate
the next value, if any
The JDBCFactory would expose an optional PK_METADATA_TABLE
option that would name the table, with a default value of
"pk_lookup_table"
If anyone needs a different kind of lookup strategy based on
some internal rules she could just implement her own strategy.
This would be limited by the fact PrimaryKey is pure description
with no logic, but I guess we can cross that bridge another time.
How does this sound?
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geotools-devel mailing list
Geotools-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel
