[Axiom] PostgreSQL backend incompatibilities

Hi,

to make Axiom use PostgreSQL (and perhaps other backends in the future), 
following trival and non-trival changes are required:

 * dropping the dependency on OID mechanisms in SQLite (PostgreSQL has OIDs, 
   but their usage should be avoided).

   So, storeID would be treated as every other attribute, having column type
   "SERIAL UNIQUE PRIMARY KEY NOT NULL"

   To fetch the last value from a given SERIAL's field sequence in PgSQL, 
   you need table name for a given INSERT query. You can extract it from the
   SQL, but it would be better to just pass it as a paramteter.

   For example:

 	CREATE TABLE foo_v1( _oid SERIAL PRIMARY KEY UNIQUE, x INT);
	INSERT INTO foo_v1 (x) VALUES(10);
	SELECT last_value FROM foo_v1__oid_seq;

   This suggests exporting everything SQL-related from Item, Store
   to the backend module. axiom._schema should live in the backend module
   too. The API for doing queries should be changed, so the table parameter
   is passed.

 * column names: no '[' and ']' in the column name is allowed; this suggests
   handling column name generation by the backend module

 * sqltype incompatibilities - it's "BYTEA" in PostgreSQL, not "BLOB"; this 
   suggests declaration of Axiom's sqltypes in some other module and passing
   those objects directly to the backend module, when creating tables; backend
   would convert them to SQL strings

 * dbapi2 module for pygresql does raise an exception when trying to fetch 
   rows from a query, that did not return any rows (like "CREATE TABLE"); 
   this suggests different API for the fetching and non-fetching queries
   (should be easy).

 * pygresql's cursor.execute won't accept '?' as a formatting character. 

 * different data types stored and returned in checkTypeSchemaConsistency
   [ [ ... ], [ ... ] ] != [ ( ... ), ( ... ) ]

 * tests need to cleanup after themselves (remove all tables); this suggests
   creating a AxiomTestCase class which would handle that manually

All the differences suggest, that porting to different SQLite backend, as 
Glyph suggested, won't give Axiom enough backend portability -- the new 
backend should be different enough to give us a feeling of what should be 
done. 

There should exist a thin layer between Axiom and the database, taking care of 
query formatting. I'm not sure if the sqltype column in axiom_attributes 
should be a string name of the attribute for a given backend or should it 
rather be a t.p.r.qual name of a class in Axiom backend module. I'm also 
unsure about string formatting and Comparable class.

I think I'd like to work on PostgreSQL backend for Axiom. Proposed layout for 
the changes would be:

 * create axiom.backend.sqlite and axiom.backend.pygresql modules
 * make Store use axiom.backend for SQL-specific tasks
 * make Item use axiom.backend for SQL-specific tasks
 * attribute.Comparable - no idea, perhaps also should use string formatting
   in backend module, this would make it less readable, I think

And, in the end, an utility to convert databases using different backends 
would be a nice thing to have. As it is impossible to have backend 
compatibility at the database dump level, backends can have as different 
database format as required and compatibility would be maintained at higher 
levels.

Ideas / comments / suggestions / hints? I'm not sure if I still have commit 
privs for divmod, but I'd like to start working on axiom backend support 
ASAP.

-- 
Michal Pasternak