Re: [Axiom] PostgreSQL backend incompatibilities

Let me just fill in the others watching this discussion as to what has transpired behind the scenes :).

Commercially speaking - and this is very important, as you must understand that Divmod is a tiny company with no money that already spends more time than our investors are altogether comfortable with contributing to open source - Divmod has no interest in supporting alternative database backends.

We are going to run our service on SQLite, and SQLite has proven to be a *VERY* nice piece of software.  Therefore, spending our scarce resources (such as my time) to make database portability happen is unlikely.

Furthermore, we really want to focus outside developer efforts on stuff *above* our stack.  I will write more about "Divmod Avenue" later, but the elevator pitch is that we wish to deploy open source Mantissa applications written by other developers on our servers and split the revenue with their authors.  However, as we need to keep our maintenance costs low (we hope to provide a wide catalog of applications) we will not be offering non-SQLite databases on our servers.  That means that we further wish to discourage the use of Axiom outside the context of Mantissa as a framework, which means substores, which means SQLite.

That said, anecdotal evidence suggests that a lot of people think that Axiom is a very good ORM and its delightful effervescent flavor should be accessible to those who use databases other than PostgreSQL.  Since the aforementioned time we spend contributing to open source is, I believe, building some community good-will for our company, I do believe that is worth something, so I do plan to spend at least enough time to review patches, offer commentary, and provide suggestions as to how to get such multi-database support merged.  It also wouldn't be bad to give experienced developers a more gradual slope to learn our technologies than requiring them to learn all of Mantissa up front, even if we do want them to learn it all eventually.  Finally, a fork is never good for anybody, so we would like to keep in-demand features like this in the mainline.

Michal contacted me a few days ago about this, and in a private email exchange, I made a few suggestions.  In order to more closely align multi-database support with our current corporate objectives, and thereby give me a better excuse to spend more time on it, I suggested to Michal that we probably want to switch to APSW at some point in the future, rather than PySQLite2.  It would therefore be beneficial to work first on porting to APSW rather than PostgreSQL first, since that would give Divmod a better incentive to get some backend-factoring accepted into the Axiom trunk.

I am writing all this here because it seems there is growing interest in database portability, and these would be good things to keep in mind if you are writing patches that you would like to get accepted :).

Now, onto Michal's email...

On Thu, 5 Jan 2006 22:39:54 +0100, Michal Pasternak <[email protected]> wrote:

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

Clearly portability between different SQLite backends is not as much portability as between a PostgreSQL and SQLite backend.  Although it may not be "enough" backend portability (in my opinion, we don't have "enough" until we have escaped SQL entirely and have at least one backend that relies directly upon a database engine and does not use SQL), it is certainly "more" portability than we have now.

Starting to provide a backend interface simply for database API would be a good starting point: the patch to add multiple backends would be merged faster, and it would start to prevent changes that would break simple multi-backend support sooner.  It would also be useful since I am sure that different PostgreSQL users will want to choose among the 35 different PostgreSQL drivers out there.

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

It should be neither.  An important thing to keep in mind: it must be possible to keep two Axiom databases open simultaneously in the same process.  Otherwise migrating data between databases of different backends is going to be impossible.

So, the sqltype should be a constant.  There should be a backend-neutral module that provides an interface between implementation modules such as item, attributes, and store, and the actual backend that was selected.  The actual backend in use will know about the list of supported types, and it will check against the list provided in the aforementioned backend-neutral module.

So for example:

from axiom.datatypes import UNICODE_STRING

class text(SQLAttribute):
    texttype = UNICODE_STRING
    def getDataType(self):
        return texttype

Other examples:

from axiom.datatypes import INT64
from axiom.datatypes import IEEE754D
from axiom.datatypes import DECIMAL_FIXEDPOINT
from axiom.datatypes import DECIMAL_FLOATINGPOINT
from axiom.datatypes import BINARY

This could possibly be extended to parameterize the basic types, to provide optimization hints and such; for example, "I have a unicode string which must be capable of holding at least 2k of data, but will generally be smaller than that" or "this is a fixed-length string" or "I expect the platform encoding to be latin-1".  Possibly this could be done by expanding the 'attribute' interface to cover a basic type (represented by the axiom.datatypes constant)

>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

These all sound like good things to work on.

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

Let's get to the point where we can get those two connections open as I mentioned before :).  It shouldn't be too hard to write an axiom-specific dump and restore tool, although it might be kind of a pain.

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

I believe you do, although I don't know if you have permission for the full repository.  If you don't, we should add you back to it though: pretty soon, there should be a branch in the repository as well as tickets in the tracker, as I suspect this will be a pretty major undertaking (and it sounds like at least dialtone would be interested in helping).