Re: [Axiom] PostgreSQL backend incompatibilities

On Thu, Jan 05, 2006 at 10:39:54PM +0100, Michal Pasternak wrote:
> Hi,
> 
> to make Axiom use PostgreSQL (and perhaps other backends in the future), 
> following trival and non-trival changes are required:

For a patch that kind of worked not so long ago you can see in the list archives.

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

Correct

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

Just SERIAL PRIMARY KEY is enough. PRIMARY KEY already implies UNIQUE and NOT
NULL. To be honest currently sqlite3 can keep 2**64 rows in the table. If you
want to have the same behaviour in postgresql you need to use bigserial or
serial8 (although this is just a minor enhancement)

>    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.

Another would be to use stored procedures that return the last_value or simply
use lastrowid here too. Although probably this cannot be used. But I agree
with this (this is probably unacceptable though, 3 queries instead of 1 is far
too much).

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

This probably depends also on the database adapter module. [ and ] are
pysqlite2 dependent.

>  * 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).

When it comes to postgresql and python the only module that should be
considered is psycopg2. At least because pysqlite2 author took a lot of code
from psycopg2 and it's likely that psycopg2 and and pysqlite2 will share more
commonality than the others.

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

Here it is IIRC psycopg2 can accept it. But either way pysqlite2 can accept
%(field_name)s that behaves the same as '?'.

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

Psycopg2 returns tuples too so this won't be a problem at all.

> 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. 

I don't think that you can easily port an application from database X and
database Y without any changes. It's probably better to try keeping the API
more or less similar but adapt to postgresql as better as possible. You won't
move from postgres to sqlite (and vice versa) very often in production.

> 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.

They didn't give me any problems with the porting when I did it.
It was fairly complete except for 2 things:
it used postgresql in a sync way and didn't support substores. Unfortunately
when divmod introduced caching it also stopped working (for lastrowid
problems).

> 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

As above: psycopg2 would be better (it will remove some problems, it will be
more thread safe, it will be faster and at some point could also be used with
the async native mode).

>  * 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

I don't understand fully the problem with Comparable. I've had no problems
with it at the time.

> 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.

This is good.

HTH.

-- 
Valentino Volonghi aka Dialtone
Now Running MacOSX 10.4
Blog: http://vvolonghi.blogspot.com
http://weever.berlios.de

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDvanJrUDjHq2SJAARAshqAKCUM/amLRAZ0dSMw0jp1YDMLnkLewCbBlQg
yIv5GX/cEMFLQ6KMbXiZpEo=
=w/XQ
-----END PGP SIGNATURE-----