Supporting multiple databases?

Guys,

Quick question (I hope). If you wanted to create some kind of framework that allows you to switch between different databases supported by PHP, say MySQL, MsSQL, SQLite etc,how would you go about implementing it?

I know that standard SQL is rarely actually implemented properly between different SQL servers so I’m pretty sure I can’t write standard SQL and pass it to a particular class for each database server. I thought about writing an SQL builder, but I’m concerned about the performance implications.

I’ve had a quick search around the net and I’m clearly searching for the wrong parameters because I’m not finding what I’m looking for. If anyone can just successfully Google what I need I’d be happy, but an explanation would be even better. I don’t want to hard code MySQL in again, despite it being the most likely DB that will be used.

Cheers all

PDO is probably what your looking for, it supports multiple server types, ie Oracle, MySQL, MsSQL, etc

Interesting, thanks, I’ll take a look at that. I assume there will always be a performance hit using something like this over native queries?

PDO is a data-access abstraction layer (c.f. database abstraction layer): essentially allowing you a uniform API to say “ask for data”, “fetch some data”, etc… You will still need to write database-specific queries (whether by hand or another layer on top of PDO) when using it.

Ah, so doesn’t actually give me what I want then. Still, I’ll take a look at it; never have before and now seems as good a time as any to look

You should look at something like phpDataMapper or [URL=“http://www.doctrine-project.org/”]Doctrine. They abstract most common queries from the actual SQL code and use different drivers to communicate with the different actual databases.

Hmm, was thinking more about methods that people use to write something themselves, or something that’s IN PHP already to use, not sourcing other packages that may (or may not) be subject to further licenses, but thanks for the info, it’s good stuff that may be usable in some work. Cheers

Well considering the contrasting syntax between databases the developer would need to write SQL specific to every available adapter for every query/operation. The advantage of system that abstracts the SQL is that the translation can happen at the system or library level instead using the rules for the specific adapter.

Yeah, that’s what I was planning on writing; something that I can pass a load of parameters and it’d write the SQL for me, depending on what DB I was using.

One trick I’ve seen used for supporting multiple databases was to extract sql standard queries, and rdbms specific queries into php files. An example follows:

Folder structure

+SQL

  • common.php
  • mysql.php
  • oracle.php

Files would contain something like this:

common.php

<?php

// this file contains SQL STANDARD queries

return array(

    'selectUserByName' => ' ... ',
    'selectUserByEmailAndPassword' => ' ... ',
    'selectAllUsers' => ' ... '

);

mysql.php

<?php

// This file contains MYSQL specific queries
// The query "selectUserByName" uses MYSQL specific stuff
// and overwrites the query from the common file

return array_merge(
    include dirname( __FILE__ ) . '/common.sql.php',    
    array(
        'selectUserByName' => ' ... ',
    )
);

oracle.php

<?php

// This file contains ORACLE specific queries.
// The query "selectAllUsers" uses ORACLE specific stuff
// and overwrites the query from the common file

return array_merge(
    include dirname( __FILE__ ) . '/common.sql.php',    
    array(
        'selectAllUsers' => ' ... ',
    )
);
<?php

class SqlLoader
{
    
    protected $_queries = array();

    public function __construct( $driver, $queriesFolder )
    {
        $queriesFile = sprintf( '&#37;s/%s.php', $queriesFolder, strtolower( $driver ) );
        if ( !file_exists( $queriesFile ) ) {
            throw new Exception( 'Unable to find the queries file' );
        } 

        $this->_queries = require $queriesFile;
    }

    public function __call( $method, $args )
    {
        if ( !array_key_exists( $method, $this->_queries ) ) {
            throw new Exception( 'Query does not exist' );
        }

        return $this->_queries[$method];
    }

}
$sqlLoader = new SqlLoader( 'MYSQL', '/path/to/queries/folder' );
$sql = $sqlLoader->selectAllUsers();
$stmt = $dbAdapter->query( $sql );
while( $row = $stmt->fetch() ) { ... }

Advantages of this:

  1. You have the possibility to avoid duplicating queries (put them in the common.php file).
  2. You have the possibility of defining driver specific queries.
  3. You have the possibility to write your own queries (guess that can be a disadvantage too if you are not very comfortable with SQL), instead of them being generated by an ORM or something like that.
  4. Code clarity.
$sql = $sqlLoader->selectUserByName();

makes it very clear (to me at least) of what is going on.

Anyway, hope that helps you (apologies for the bugs in the code).

I see what you’re saying. So rather than put the queries in the various code pages, we just put them in to the common file and call them. Sounds good, in theory, but I’m concerned about how to develop modules for such a system. Every module would have to include a number of files that would work in the same fashion, and let’s say that the organisation running the module is running PostgreSQL and they want to use a module that only supports MySQL and msSQL for example, they’re stuck unless the author will write a PostgreSQL version of his query code. Probably not too hard to do once he understands the differences between PostgreSQL and the other DBs that he already supports, but I’m thinking more along the lines of simplifying further development by not requiring the author to have too much SQL knowledge.

Thanks for the ideas though; they’re good and I may be able to use them… maybe… :smiley:

Cheers