There is Propel (http://propel.phpdb.org), which “is a full-service object persistence and query toolkit for PHP5″, which “allows you to treat your database as a set of objects, providing a simple API for storing and querying data.”
Follow the SQL 99 syntax. PostgreSQL, Oracle and MSSQL Server are mostly SQL 99 compliant. PostgreSQL itself can be seen as a reference development for SQL 99 compliancy.
If your application runs under PostgreSQL, it should run anywhere. Do not use too many server-side objects and programming.
The problem with MySQL is that it is not compliant with SQL 99. Therefore, everything written is likely to be incompatible with legacy SQL 99 systems.
Well I suppose a DB abstraction layer does mean that someone else (another programmer) will know exactly what modules or classes to to change when a new database needs to be supported… theoretically. Wouldn’t all the SQL to change be in the classes that comprise the abstracton layer?
It’s a pattern. Engineers love design patterns.
I’m not sure about the PHP world, but I wouldn’t say that DB abstraction (and I’m talking _only_ about sql syntax) is impossible (or even unlikely) …
I started down this road with my own solution for ColdFusion a while ago and am currently putting another revision on it actually right now.
Concatenation - easy - the API accepts || and converts that (which isn’t standard sql syntax for anything else afaik) into the appropriate + (mssql,mysql) or & (access) if necessary.
Left joins were handled about the same time - all data coming into the abstraction layer is “meta-data", so the layer can do whatever it likes with the array of join statements to produce the appropriate syntax.
I don’t use regular expressions in SQL because they’re not widely enough supported.
I handle “replace into” in my update mechanism by optionally performing an equivalent select statement before the update to determine if the record exists.
There are a host of other issues I’ve covered, like automatically converting all the variables on the url (or any other structure) into ORM-mapped filters in your select query, so you don’t have to edit the sql-code when you add a column to your table or escaping reserved words (mssql/access , oracle “", mysql“) which I didn’t want to implement initially, but ended up doing it anyway because I found myself in an office where I’m working on a legacy system designed by someone who didn’t know better. If you didn’t want to use that feature, all you’d have to do is extend sqlagent.cfc or the appropriate cfc (class) for your db server (mssql.cfc, mysql.cfc, etc) and override the escape method.
The latest revision makes the abstraction layer even much easier to use… datasource.getStatement("select").init("mytable","columns").collectionFilter(url).filter("column",content).execute() … there’s also a getSyntax() method to return the generated syntax instead of executing the query… so … long and short of it is, I just think you haven’t seen the right abstraction layer.
I just realized the last couple paragraphs might not have been entirely intuitive… :) with regard to not changing the sql-syntax when you add a column to your table, I’m talking about making search results functionality easier to produce. Once I have a search page made, I can narrow the results however I like just by adding the column name to the url. I still have to add the column to the table in my search display, but I only have to make 2 changes (html & database) not 3 (select statement).
Sure… of course you can add tons of code to abstract everything including the syntax like ODBC does. (ODBC is actually a fairly intersting abstraction layer…)
You could also code regular expressions into the abstraction layer in order to handle those lousy DBMSes which still don’t support them yet.
At the end of the day, my point is that there is too much variations between DBMSes for DB abstraction to work in an efficient manner.
In other words, you’re bound to end up with a major implementation of what Joel Spolsky calls a leaky abstraction.
That’s an argument for never doing anything to make your life (programming work) easier, because doing something to make things easier is an abstraction and therefore would leak…
I’ll stick to shortening my development times…
Form is loading...