ORM Database Abstraction
Posted: Sat Jun 14, 2008 4:51 pm
I've been working on a sort of ORM database abstraction class and I'm curious to get some opinions on the idea of it.
To start, I'll try to explain what I wanted it to do. I'm writing a CMS (isn't and hasn't everyone
) and I would like to have it have the ability to work with different database management systems. I also wanted to have extensions or modules for the software that could be installed easily. However, I didn't want to have to write a different version of each extension or the core system for each database type.
After a lot of discussion, from which I learned a lot, I decided to try writing my own abstraction layer instead of using one of the preexisting solutions. The solution I came up with is fairly heavy-handed, but I think it should be able to do what I need it to do. It's inspired a little bit from the Zend DB, but it's different as well.
I based my abstraction layer on PDO for the connection, but it dynamically creates the SQL from methods. There is a base class for each type of database (MySQL, PostgreSQL, Oracle, etc). Which base class should be used is determined by a constant in the connection script. The base class is extended by a thin ORM-type layer that corresponds to each table in the database. There is also another class that handles the DDL operations, but it isn't currently extended in any way.
I did explore the possibility of generating the SQL from stored procedures, but it didn't meet my goal of only having to write code once, and so I chose to generate it using PHP instead. However, There will also be some disadvantages to using this method:
The construction method parameters are (PDO connection, table name, an array of table columns, schema).
Example usage for the select() method:
The select method also takes an optional parameter called mode, which determines what kind of result set will be returned. Options include: single, assoc, num, obj, lazy, subquery (returns the query for insertion as a subquery), count, and debug (which just prints the compiled query). The default return is PDO::FETCH_BOTH. There are also methods for creating joins, aliases, and having and group by clauses.
I'll just point out something that I think is kind of cool. If you set the mode to 'single', you can access the values like this:
If the mode isn't set to single, then the values would have to be accessed through a loop.
As an insertion example:
An update example:
A delete example:
Each of the insert(), update(), and delete() methods also have an optional debug mode parameter.
This is kind of long already, so I won't include any examples for table creation, etc. I'm curious to see what people think, though. I'm still working on it and have only completed the MySQL base class (which works). I think it will accomplish what I want it to do, but is it the right approach? Is there a better way that still meets my goals? Is it going to add too much overhead (I'll probably need to test it before I can answer that question)? Would you like or hate using something like this?
Thanks for the read and I look forward to hearing your suggestions and comments.
To start, I'll try to explain what I wanted it to do. I'm writing a CMS (isn't and hasn't everyone
After a lot of discussion, from which I learned a lot, I decided to try writing my own abstraction layer instead of using one of the preexisting solutions. The solution I came up with is fairly heavy-handed, but I think it should be able to do what I need it to do. It's inspired a little bit from the Zend DB, but it's different as well.
I based my abstraction layer on PDO for the connection, but it dynamically creates the SQL from methods. There is a base class for each type of database (MySQL, PostgreSQL, Oracle, etc). Which base class should be used is determined by a constant in the connection script. The base class is extended by a thin ORM-type layer that corresponds to each table in the database. There is also another class that handles the DDL operations, but it isn't currently extended in any way.
I did explore the possibility of generating the SQL from stored procedures, but it didn't meet my goal of only having to write code once, and so I chose to generate it using PHP instead. However, There will also be some disadvantages to using this method:
- I had to limit the base classes to characteristics that are shared or can be emulated across DBMS's (which took a little bit of research). As such, the base class is pretty much just CRUD, but it can be extended.
- Different database types will still need to be optimized in their own way.
- The abstraction layer will introduce portability at the price of overhead (though I'm not sure how much just yet because I still have to run some benchmarks).
Code: Select all
class users extends DBObject {
function __construct() {
global $DB1;
parent::__construct($DB1, 'users', array('userId', 'username', 'firstName', 'lastName', 'age'), 'none');
}
}Example usage for the select() method:
Code: Select all
$lastName = 'Smith';
$users = new users();
$users->where('lastName', '=', $lastName);
$users->order_by('lastName', 'ASC');
$users->limit(5);
$users->offset(5);
$users->select(); //Compiles and runs the queryI'll just point out something that I think is kind of cool. If you set the mode to 'single', you can access the values like this:
Code: Select all
echo $user->{'firstName'};
echo $user->{'lastName'};
echo $user->{'age'};
As an insertion example:
Code: Select all
$users->{'username'} = $username;
$users->{'firstName'} = $firstName;
$users->{'lastName'} = $lastName;
$users->{'age'} = $age;
$number = $users->insert(); //returns the last insert id
Code: Select all
$users->where('userId', '=', $userId);
$users->{'username'} = $username;
$users->{'firstName'} = $firstName;
$users->{'lastName'} = $lastName;
$users->{'age'} = $age;
$number = $users->update(); //Returns the number of affected rows
Code: Select all
$users->where('userId', '=', $userId); //if no where clause is used, it will delete all rows in the users table
$number = $users->delete(); //returns the number of affected rows
This is kind of long already, so I won't include any examples for table creation, etc. I'm curious to see what people think, though. I'm still working on it and have only completed the MySQL base class (which works). I think it will accomplish what I want it to do, but is it the right approach? Is there a better way that still meets my goals? Is it going to add too much overhead (I'll probably need to test it before I can answer that question)? Would you like or hate using something like this?
Thanks for the read and I look forward to hearing your suggestions and comments.