A database-abstraction package?

[Istvan Albert]

Thomas Weholt wrote:

> I need to make my project work with SQLite, MySQL and Postgresql, but I
> don't want to create and maintain a set of table-schemas for all of them.
> I'd rather specify this information in a common format and then let some
> piece of code generate and run the sql-statements to create the tables in
> the database. When I insert, update or delete records I also want a common
> interface. In my project I use alot of auto-increment primary keys so this
> would have to be handled as well.

That common format that you are looking for is called SQL. Make sure that
what you are trying to do is part of the SQL standard and that it is
supported in your engine. Usually you only need to worry about fancy
constructs such as nested selects or constraint checks.

It is not that hard to make a portable schema that can be loaded in all three,
but with MySQL in the picture the least common denominator is a very
simplistic one. Here are some hints:

http://home.fnal.gov/~dbox/SQL_API_Portability.html

There is a DB abstraction library called ADOdb:

http://php.weblogs.com/adodb_python

But I think your biggest worry should be that trying to support three
databases, you have no way of leveraging the strengths of the
more simplistic engines (MySQL and SQLlite), yet you have to account
for all of their weaknesses. This will lead to a  hodge-podge,
high-maintenance yet low performing data model that will be the weakest link.

i.