Database abstraction layers

Any experiences?

[ergophobe]

Hmm.. I realize this is dangerously close to a typical opening post to tag-team forum spam, but I've been wondering about this for a while and am curious what people have to say.

I use a simple, self-made DB class abstraction layer. I've been thinking that maybe I would get more out of one of the off-the-shelf abstraction layers.

There are occasional mentions of the PEAR database abstraction layer here, but I've never seen anyone report on plusses and minusses (performance hits, coding ease, real-life portability).

Also, there are numerous other choices, some of which claim to be superior (faster, easier, whatever) than PEAR. Ones I've seen so far are

PEAR

ADOdb from John Lim (now a Sourceforge project)

ezSQL by Justin Vincent (there are multiple projects by this name).

From a quick look it seems like ezSQL caches result sets in arrays (not certain that I have this right) which might reduce DB overhead, but I fear that with large result sets that would end up being bad.

Tom

[jatar_k]

We use a lib that was handed down from coders before me. It suits it's purpose though sometimes is a bit limited.

I have to say I haven't used anything off the shelf.

but I fear that with large result sets that would end up being bad

I agree, gives you that foreboding feeling.

[coopster]

I'm using my own as well. It's minimal, but that's what I want. I like having that control. If I feel it's too fat, I trim things back. If I need more out of it, I add more to it.

The toughest part about portability, in my humble opinion, is in the server setup and the hosting partner (if applicable, such as contract work). What if PEAR isn't compiled on the client's shared server? Will the host allow it? A fellow member just ran into this problem [webmasterworld.com]. Of course, this is all overcome with your own server, and there are plenty of affordable solutions avaiable today. I am merely referring to applying portions of your previously well-thought-out, tested and true solutions that rely on the methods you expect to be there which may not because of circumstances outside of your control.

Coding ease...? I'm a bit perplexed by your viewpoint/concern here. Coding is somewhat easier in that you are calling the same functions rather than database-specific functions. Now, if you were to be concerned about the HOW TO's and documentation (or lack thereof) in any given solution, I feel your pain (including some of my own at times, he shamefully admits).

I did some very minor performance measurements when I first moved over to an abstraction layer but nothing that can be nor should be presented as valuable to this discussion. I do, however, await your summaries :)

---

I just read this post and thought maybe it sounded as if I were negative toward PEAR or any other third party solutions. Quite the opposite. I just wanted to clarify. I've merely created my own and tend to keep using them. I'm not afraid of change, I just prefer spaghetti code ;)

[Netizen]

I/we use the PEAR DB module on a set of high traffic sites with no problems. It is simple to use and you can set up error handling routines fairly simply within the PEAR structure.

[ergophobe]

Coding ease...? I'm a bit perplexed by your viewpoint/concern here.

It's not really a viewpoint or a concern, just an open question. One abstraction layer mentioned (ezSQL) makes claims that it is easier to code than others. As near as I can tell, that's because it does as much processing as possible with PHP and doesn't have the facility to use a lot of the native SQL functions of a dbms. For me, that sounds like it would be frustrating and *hard* to use, not easier.

So that's what I was asking - do any of these make you do gymnastics just to achieve basic database operations?

That's a good point about portability, though. However, isn't PEAR compiled into PHP by default and, according to the PEAR Documentation you can install a "local" (i.e. personal) copy of PEAR on a shared server [pear.php.net].

I like my abstraction layer in that it's simple

$db->query("SELECT SQL query here LIMIT 1")

But I was sort of wonder if I was "missing out" on something.

Followup question for those of you using your own abstraction layers. Have you ever switched DBMS? If so, how hard was it?

Tom

[jatar_k]

I haven't actually switched but we use this for oracle presently and I have looked at using it for both oracle and mysql at the same time (I must be bored) and it didn't seem like any great stretch to get it converted.

Having it drive both seemed a bit painful though. ;)

[coopster]

>>doesn't have the facility to use a lot of the native SQL functions of a dbms

Such as? I would download ezSQL and look at it myself, but I can't stand it when somebody forces me to enter my email address to get their *open source* code. If it was truly open, then give a link to the stinking zip file for crying out loud!
(boy it felt good to get that out)

>>according to the PEAR Documentation you can install...

Unfortunately, the hosting provider would not allow this in the case used for explanation, and for whatever reasons, they also chose not to compile PEAR by default. In those particular instances, it will boil down to whether or not you really want the contract and how you will go about coding and implementation.

[ergophobe]

>>doesn't have the facility to use a lot of the native SQL functions of a dbms

Such as?

Not sure I have that right, but in his documentation [justinvincent.com] he says things like

The point is this, if you extract your results into an array like the ones shown above, you no longer need to play around with lots of different types of database specific functions in order to work with extracted data.

The only functions you need to use (99.9% of the time) are PHP functions. The really great thing about this is that you can be darn sure that your code is much more portable between databases.

In any case, I think this is something I'll worry about later. I think converting from my homespun abstraction layer to another won't be that bad if I find mine lacking or I have a project that absolutely requires something more complex. The key thing is avoiding the switch from thousands of mysql-specific functions to something else.

Tom