A database-abstraction package?

[Thomas Weholt]

Hi,

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.

Does anybody know if there are any such module or package available already?
If not I've got to start working on one and it would be nice to have some
pointers or guidelines if somebody has any ideas or opionions. A system
capable of handling any database would be best, but as a minimum I need to
support SQLite and MySQL, on Linux and Windows.

Thomas

 

[Istvan Albert]

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.

 

[Brian Kelley]

Hi,

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.

You might consider sqlobject

http://sqlobject.org/

I think it will handle what you want, I have used it with some success.

Brian

 

[A.M. Kuchling]

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.

Those are precisely the systems that SQLobject (www.sqlobject.org) supports.

--amk