The moral here is to be aware of performance considerations as you go forward and try and be DB independent. Remember too that people will bend from need and if what you're doing provides enough value to them, they will support the DB you design and optimize for because they need what you are doing.

"A man's maturity -- consists in having found again the
seriousness one had as a child, at play."   --Nietzsche
[download]

One answer: use SPOPS, which is probably getting a new release within a week. Basically, you get the normal operations (create/update, remove, fetch record) super easy and searching/fetching multiple records just easy. You don't need to deal with datatypes, quoting, whether you're using an AUTO_INCREMENT field (MySQL) or an IDENTITY (Sybase/MS SQL) field -- it just works. For a simple example of how it works, see Re: Re: Re: Object Persistence to PostGRESQL.

Chris
M-x auto-bs-mode

Simple SQL functions are okay (insert, delete, update). Outer joins have different (non-standard) syntax everywhere they are supported (Postgres does not yet support it, mysql does not). Some DBs support subqueries using standard SQL syntax (mysql does not). Some DBs support autoincrement fields (mysql, postgresql) others (Oracle) have sequences that you can use to do the same thing. Aggregate functions (min, max, count, ...) are pretty standard. Other functions are not. Cursors are completely nonstandard. Dates and time are nonstandard. All bets are off for stored procedures.

For defining the schema, each RDBMS has a different set of datatypes that it supports. You can use the standard SQL types if you want since most RDBMS map them to their internal types. Basically it is a mess.

If you want to write generic queries then you can use an ODBC driver since it will map 'standard' SQL to the DB flavor. But it is pretty slow.

What I do is wrap all code that accesses the DB in a subroutine so that my main code makes a standard call and passes in a DB handle that it got from another subroutine I have. Then inside the accessor routine the can do whatever it needs to get the data in a useful form and return it. I try to write 'generic' SQL and avoid any DB specific stuff, but at some point you will need to rely on something DB specific. At that point you can have the subroutine determine which RDBMS it is talking to and execute the correct code. I like to wrap anything that needs access to an autoincrement call with a stored procedure since the way you call them is somewhat standard and I can hide the details of getting the next ID in there.

For handling the table definition, you could write standard SQL and then have a perl script translate all of the datatypes from the generic form to the DB specific one. The problem here is deciding how to handle the autoincrement columns.

Good luck. The problem is not insurmountable, but it is twisty. You also have to make sure that you don't become DB dependent due to convenience. If you must do something that you know is DB dependent, flag it so you can find it later.

-ben