Database Abstraction with ADODB

The use of abstraction layers is a controversial topic among PHP users. There are many reasons why people use them, and likewise why they don’t. The fact is, abstraction layers will always be around and they provide a very quick way of creating applications, even though for very high traffic sites, it may be a performance hit to use large libraries if they aren’t properly scripted. In this article I will be going through the advantages and disadvantages of using the ADODB database abstraction layer. This article is biased in it’s favour but I will also be discussing the disadvantages.

Why do I use it?

Since the introduction of the object model in PHP4 and the subsequent re-write in PHP5, I have always created my own abstraction classes and have found them very useful for things such as error handling. While looking around one day, I came across a list of abstraction libraries already created. After trying many of them and benchmarking a few of the top ones (ADODB, PEAR:DB, Metabase etc.) I have settled on ADODB. I however don’t use ADODB for its portability to other databases. This is a common misconception that users are given about (DALs) database abstraction layers. Switching from one database to another will never be an easy task, mainly because of a few SQL commands that are unique to each DBMS. One could argue that developers should never use non-standard SQL anyway, but that’s for another time.

So, why do I actually use ADODB? Mainly because I’m a lazy person and don’t want to have to script all of the SQL caching functions that are already matured in ADODB. I have a thing about caching performance-hungry SQL queries and regularly called queries. This is why I like to use the caching functions that ADODB provides. They make it extremely simple to cache SQL queries and all you have to do is change the command given to ADODB, it will sort out everything else for you. The second main reason for using ADODB is the advanced database session handling capabilities. Again, whether session data should be stored in a database for a single server site is debatable, but I like doing it because it makes it easier to see who’s using the site, and can eject them easier also. They’ve made everything as easy as it possibly could.

Advantages

• Well matured library: It has been continuously developed since 2000.
• Very good performance-monitoring library.
• Easy to use caching functions helping to increase performance of high traffic sites.
• Full database session support: Allows for easy creation of user systems.
• Layered code makes ADODB a very fast library and only adds a slight overhead if performing common tasks.

Disadvantages

• Does add a slight overhead to your application which very slightly slows down queries, however this is not noticeable until your site is under very very high traffic bursts (how many “very”s was that?).
• A small learning curve for learning the new ways of doing things (will be fine if you already know the concepts of object-orientated programming), although you can get by very easily if you have never looked at it.
• If you have something about creating all of your code yourself, you’ll cry at the thought of using open source code.

How can it be used?

As I have mentioned, using ADODB is extremely simple and developers from all backgrounds (especially Microsoft). It is well documented and many examples are provided so that you can get on your way as fast as possible. Instead of writing out the documentation again here, I’ll just point you towards a few of the good places.

• ADODB SourceForge
• Session Management
• Moving from MySQL to ADODB
• Caching support
• Main Documentation

Tips

There are a few pieces of the documentation that are quite sketchy I feel, this is why I have placed a few of the things that I found hard to find in the below section. If there are any more that you think should be added, please just leave it in a comment and I will happily add then.

Flushing single records from the cache

It took me a while to find out how to flush single SQL queries from the cache. You can do this by executing the following method:

[php]
$db->CacheFlush(”SELECT * FROM table”);
?>
[/php]

Obviously replacing the SQL query with the one you would like flushed. If you want the entire cache flushed, then just don’t pass any SQL in and it will flush the entire cache.

$db->CacheFlush();

Changing the session table name

Again, something else that took me a while to find out. What if you want to change the table name of your sessions database. There doesn’t seem to be any way of doing it when looking through the documentation, but there is. Before you initialise the sessions library, put this in:

$options['table'] = “My_Table_Name”;

Changing the cache directory

Something you may want to do to get it out of the root ADODB directory is to change the cache directory. To do this, simply place this before initialising your main ADODB class.

$ADODB_CACHE_DIR = ‘my/private/directory/query_cache’;