I'm beginning to look into database abstraction and just wondering if anyone has used it in their applications and what their experience has been. Is it worth it? What are some of the advantages and disadvantages? If you were to use one, which would you recommend?

At this point, I think I'm leaning toward not using a DBA layer, but I'd like just a little more information before I make that decision. Some of the factors I'm concerned about: writing queries that work across multiple database types, site performance, and the actual need for a DB port from MySQL to something else.

What do you mean by database abstraction, specificially? That can mean a lot of different things to a lot of different people.

The DB layer is still there if your using a database. You would just be adding an abstraction layer on top of it. Some PHP frameworks, Ruby on Rails, Perl, and others have DB abstraction options.

Originally Posted by Learning Newbie

What do you mean by database abstraction, specificially? That can mean a lot of different things to a lot of different people.

To me, it means writing your application with a database connection layer that allows your application to run with a variety of database types such as MySQL, SQL, etc. The queries are run through the DBA class and then executed according to which DB type is being used. Writing your application this way would give it greater portability across systems with different database configurations.

Originally Posted by joder

The DB layer is still there if your using a database. You would just be adding an abstraction layer on top of it. Some PHP frameworks, Ruby on Rails, Perl, and others have DB abstraction options.

Yep, I understand that. I was just looking for advice and comments about implementing an abstraction layer.

Originally Posted by VirtuosiMedia

Yep, I understand that. I was just looking for advice and comments about implementing an abstraction layer.

Then you need to let us know what language you are going to use.

I'll be using PHP.

Originally Posted by VirtuosiMedia

To me, it means writing your application with a database connection layer that allows your application to run with a variety of database types such as MySQL, SQL, etc. The queries are run through the DBA class and then executed according to which DB type is being used. Writing your application this way would give it greater portability across systems with different database configurations.

Great way to sandbag performance. If every RDBMS has its own strengths and weaknesses.

Since only MySQL supports limit 25 on the end of a query, this approach says you shouldn't use it. Oracle lets you say where rownum < 25 and SQL Server has Select Top 25. So at best you can write lots of different versions of a query or its fragments, increasing your workload probably for no good reason.

Will you even know if the SQL Server version works if you're running MySQL?

My inability to effectively test anything outside of MySQL is making me lean toward not trying it at all. And, as you mentioned, performance is an issue as well. At this point, I've just been exploring different options in a lot of different areas for my application. However, as far as database abstraction is concerned, I think I'll probably pass on it at this point because MySQL is popular enough so that I don't need to develop for other DB types. I was just trying to be forward thinking, but it seems like the costs outweigh the benefits here.

You wouldn't be able to add query hints, like to use a nested loop join instead of a merge or hash join when you don't have too many rows, and have supporting indexes. I don't know if MySQL even supports query hints?

The better way to go is abstraction in the OOP sense. Instead of your logic code hitting the database directly, use a DAL. Then make another copy of that DAL for Oracle when the need comes up. Design your applicaiton in a way that one DAL can be swapped out for another.

DAL = Data Abstraction Layer. Not abstract in the sense that it tries not to be aware of the specific nature of the database it winds up talking to, but in the sense that it's abstracted away from the rest of your code.

I'd reconsider MySQL if I were you, but I'd always reconsider using MySQL in place of a more, ahem, "real" database server. Although v 5 made some tremendous strides.

Originally Posted by Learning Newbie

You wouldn't be able to add query hints, like to use a nested loop join instead of a merge or hash join when you don't have too many rows, and have supporting indexes. I don't know if MySQL even supports query hints?

The better way to go is abstraction in the OOP sense. Instead of your logic code hitting the database directly, use a DAL. Then make another copy of that DAL for Oracle when the need comes up. Design your applicaiton in a way that one DAL can be swapped out for another.

DAL = Data Abstraction Layer. Not abstract in the sense that it tries not to be aware of the specific nature of the database it winds up talking to, but in the sense that it's abstracted away from the rest of your code.

I'd reconsider MySQL if I were you, but I'd always reconsider using MySQL in place of a more, ahem, "real" database server. Although v 5 made some tremendous strides.

Just to get you right, you're suggesting doing something similar to the following code (in either a class or a function) and then adding it to my database connection file:

PHP Code:

function query_foo ($query) {
     $query = mysql_query($query);
     return $query;
}

function fetch_array_foo ($result) {
     $result = mysql_fetch_array($result);
     return $result;
} 


Then in my actual application, I would use the functions I created rather than the database specific functions. If I wanted to change it later to a different DB type, I would just then edit the one file with all the functions rather than every single file. Is that what you're saying, or am I off the mark? I haven't had much experience yet outside of MySQL...

Originally Posted by VirtuosiMedia

Then in my actual application, I would use the functions I created rather than the database specific functions. If I wanted to change it later to a different DB type, I would just then edit the one file with all the functions rather than every single file. Is that what you're saying, or am I off the mark? I haven't had much experience yet outside of MySQL...

Yup, thats what Learning Newbie suggested. You might also wanna have a look at this : http://pear.php.net/package/MDB2

I personally don't like pear. I wrote my own database class and put the sql as variables in another file. But you could find a better class at phpclasses.org

Originally Posted by joder

I personally don't like pear. I wrote my own database class and put the sql as variables in another file. But you could find a better class at phpclasses.org

But does that mean I have to visit that site? I cringe every time I log in to it.

Thanks, though, everyone, for the advice.

Originally Posted by joder

I personally don't like pear. I wrote my own database class and put the sql as variables in another file. But you could find a better class at phpclasses.org

Any specific reason for your dislike for pear, that you'd like to share with us or its just a personal preference.

My personal experience. I find it clumsy and limiting. I think it is easier just to call the php/mysql functions themselves or use a class.

Originally Posted by VirtuosiMedia

Just to get you right, you're suggesting doing something similar to the following code (in either a class or a function) and then adding it to my database connection file:

Yeah. I'd suggest using a class, or several, that map database values into whatever objects your core code will use. Then if it just uses its own native objects (without caring from where they come) you can swap the one file out with the db specific stuff any time you need.

That way you have a maintainable app, you can take advantage of full awareness of the database for best perf, and you can expand in future.

So how would you suggest handling writing the queries themselves? Simply replacing the function itself with my own function wouldn't really benefit me if my goal is to later add support for more databases, would it? If I have queries that use MySQL's 'LIMIT', I'd have to go back and rewrite that query. If I write my function in a way that allows me to change that part, I'm basically writing my own DB abstraction layer and I might as well save myself time and use one that's already developed and tested.

I found an interesting link (http://troels.arvin.dk/db/rdbms/) that highlights the differences between some of the different databases. It says it was updated this month, but I have no idea how accurate it is. Looking it over, though, didn't make me want to try to develop a database abstraction layer on my own.

I'd follow the industry best practice and put the queries in the database as stored procedures, views, user defined functions (UDFs) and so on. As much as possible, move the logic to the data, instead of the other way around. That's for a couple of reasons, from obfuscating the underlying architecture of your database, security, and performance. Plus it really tends to make things more manageable in the long run.

Your choices are these - you can rewrite queries somewhere in your system if they use something like for example SELECT STUFF LIMIT 10, or you can not use the LIMIT keyword. If you only need 10 rows, asking for more is going to hurt performance. That means the query itself will run more slowly, but more importantly, the query will make the entire database perform more slowly for all callers while it's running.

It sounds like when you talk about using an abstraction mechanism that's already gone through it's development lifecycle, I can't imagine that working from off the shelf unless it uses dynamic SQL. It sounds like you want a component to rewrite your queries for you so you don't' have to? In that case tho, the software won't have the same knowledge you do of the database and how best to use it. At the end of the day, if this is a commercial application, I'm guessing your customers will be more concerned with performance (because poor perf is fixed with more expensive hardware over time) than with the effort that went into developing it.

On the other hand if you're coding for MySQL and don't have any immediate plans to port the applicatoin, you can cross that bridge when you get to it, so long as you do a little planning beforehand. Which you're clearly doing.

After researching it quite a bit, I think I'm going to go with Data Access Objects and Value Objects rather than stored procedures. I'll start with this as my connection class and then build my DAO's on top of it.

PHP Code:

<?php 
//This is the base class for database connection

class DatabaseConnection {

    /*
    The constructor class 
    It accepts only MySQL for now, but can be expanded later for other database types
    
    Parameter:             Definition:                                                            Acceptable Values
        $db_type            The type of database to be used (required)                            'MYSQL'
        $host                The database host (required)                                        A string containing the host name
        $user                The database user name (required)                                    A string containing the user name
        $password            The database user password (required)                                A string containing the user password
        $db_name            The name of the database (required)                                    A string containing the database name
        $persistant            The persistance of the DB connection (optional)                        1 = Persistant, 0 = Not Persistant
        $client_flags        Sets the client flags for a persistant MYSQL connection (optional)    Integer
    */

    function DatabaseConnection ($db_type, $host, $user, $password, $db_name, $persistant=0, $client_flags=NULL) {
        
        var $db_name
        
        //If the database is MYSQL
        if ($db_type == 'MYSQL') { 

            if ($persistant == 0) { //If the query is not persistant, use mysql_connect

                //Make the connection
                $dbc = @mysql_connect($host, $user, $password) OR die(CustomErrorHandler(mysql_error()));
            
            } else if ($persistant == 1) { //If the query is persistant, use mysql_pconnect

                if ($client_flags) { //If client flags exist

                    //Make the connection
                    $dbc = @mysql_pconnect($host, $user, $password, $client_flags) OR die(CustomErrorHandler(mysql_error()));

                } else { //No client flags exist

                    //Make the connection
                    $dbc = @mysql_pconnect($host, $user, $password, $client_flags) OR die(CustomErrorHandler(mysql_error()));
                
                }

            }
                        
            //Select the database
            @mysql_select_db($db_name, $dbc) OR die(CustomErrorHandler(mysql_error()));
        }
    }

}
?>

Does this sound like a good plan or am I way off track?