ORM Database Abstraction

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

ORM Database Abstraction

Post by VirtuosiMedia »

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 :roll: ) 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:
  • 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).
The ORM layer that extends the base class looks like:

Code: Select all

class users extends DBObject {
    function __construct() {
        global $DB1;
        parent::__construct($DB1, 'users', array('userId', 'username', 'firstName', 'lastName', 'age'), 'none');
    }
 
}
The construction method parameters are (PDO connection, table name, an array of table columns, schema).

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 query
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:

Code: Select all

 
echo $user->{'firstName'};
echo $user->{'lastName'};
echo $user->{'age'};
 
If the mode isn't set to single, then the values would have to be accessed through a loop.

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
 
An update example:

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
 
A delete example:

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
 
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.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: ORM Database Abstraction

Post by allspiritseve »

Just to clarify-- ORM is an entirely different thing than a database abstraction layer. They can work together, but what you are describing is a DAL and not an ORM.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: ORM Database Abstraction

Post by Christopher »

Actually is looks like Active Record...
(#10850)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: ORM Database Abstraction

Post by John Cartwright »

One a small note,

Code: Select all

echo $user->{'firstName'};
can be written as

Code: Select all

echo $user->firstName;
Much cleaner, in my opinion.
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: ORM Database Abstraction

Post by VirtuosiMedia »

Thanks for the comments, guys.
allspiritseve wrote:Just to clarify-- ORM is an entirely different thing than a database abstraction layer. They can work together, but what you are describing is a DAL and not an ORM.
I may not always get my terms right because I'm still learning, but I had thought that ORM could be described as one object per database table or per database query. This is a DAL, but there is also one class per table in the database that extends the base database class. Maybe that's not ORM, but I'm not quite sure what to call it.
One a small note,

1. echo $user->{'firstName'};

can be written as

1. echo $user->firstName;

Much cleaner, in my opinion.
It would be much cleaner, but it's dynamic and corresponds to a column in the database. The classes that extend the base database object pass in the columns to the constructor and these are created using get and set. If there is a way to make them dynamic without the curly braces, I'm all ears.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: ORM Database Abstraction

Post by John Cartwright »

Code: Select all

 
$column = 'firstname';
$user->$column = 'John';
Is that what you meant?
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: ORM Database Abstraction

Post by VirtuosiMedia »

arborint wrote:Actually is looks like Active Record...
I hadn't been familiar with the Active Record pattern before you wrote that, but it seems like there are some commonalities. At first glance, though, I think mine works a little bit different because it also builds SQL queries through methods to serve as an abstraction layer. You aren't limited to using just the users table because you can use joins and subqueries. It takes a bit different approach and might be a little more flexible in retrieving data without having to write a new method.
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: ORM Database Abstraction

Post by VirtuosiMedia »

Jcart wrote:

Code: Select all

 
$column = 'firstname';
$user->$column = 'John';
Is that what you meant?
I'm not completely sure, but I don't think so because its actually a key in an array. I have a base class called DBObject that has all the methods for generating the SQL and performing your basic CRUD functions. Every table in the DB also has a class that extends the DBObject and populates it with its column names. The column names are then stored in an array and can be given values, either for display by the class when a select query is used or for inclusion in the database when an insert or update query is used. In the case of the users table that has columns called userId, username, firstName, lastName, and age; it would look like this:

Code: Select all

class users extends DBObject {
     function __construct() {
        global $DB1;
        parent::__construct($DB1, 'users', array('userId', 'username', 'firstName', 'lastName', 'age'), 'none');
     }
}
However, for a table named articles with columns named articleId, title, body, and date, there would be another separate class that looks like this:

Code: Select all

class articles extends DBObject {
     function __construct() {
        global $DB1;
        parent::__construct($DB1, 'articles', array('articleId', 'title', 'body', 'date'), 'none');
     }
}
For your example, wouldn't I have to set the column name every time I am using the users class rather than declaring it once in the class constructor? If not, how would I incorporate it into the class?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: ORM Database Abstraction

Post by Christopher »

VirtuosiMedia wrote:At first glance, though, I think mine works a little bit different because it also builds SQL queries through methods to serve as an abstraction layer. You aren't limited to using just the users table because you can use joins and subqueries. It takes a bit different approach and might be a little more flexible in retrieving data without having to write a new method.
Many Active Record implementation create a set of finder methods that do exactly what you describe. It is a pretty standard and popular pattern.
(#10850)
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: ORM Database Abstraction

Post by VirtuosiMedia »

arborint wrote:Many Active Record implementation create a set of finder methods that do exactly what you describe. It is a pretty standard and popular pattern.
Okay, that's good to know, thank you. Like I said, I just glanced at it, so I'll have to look into it more. I was pretty sure I wasn't creating anything new, but you don't always find something when you don't know what term to look for. Does there seem like anything horribly wrong with my implementation of it, at least in so far as using it?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: ORM Database Abstraction

Post by Christopher »

No ... other than what Jcart pointed out, and that most system have moved to $users->where('userId=', $userId); style parameters.
(#10850)
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: ORM Database Abstraction

Post by allspiritseve »

VirtuosiMedia wrote:I may not always get my terms right because I'm still learning, but I had thought that ORM could be described as one object per database table or per database query. This is a DAL, but there is also one class per table in the database that extends the base database class. Maybe that's not ORM, but I'm not quite sure what to call it.
Well, maybe I was a little hasty in posting, but... an ORM is an Object Relational Mapper. The idea is that you works in terms of objects, and are not aware that the object is being persisted in the database. What I see your code doing is building a query, and then allowing the results to be accessed in the same way you would deal with an array. It is similar to ActiveRecord, but I think the crucial difference is that you are aware you are working with a query and query results. If you internalize the query construction, and your methods to work with the object deal with user methods and not query construction methods, your class will be much closer to ActiveRecord.

Now, I am sure you will say that is moving away from your original goal of database abstraction. My suggestion would be to split these into two separate classes-- one that is a User object that constructs a DAL object inside its methods. As it is, you're trying to do two different things with the same object, and OOP works best if one object handles one thing and one thing only.

Now, I much prefer Gateways and Data Mappers over ActiveRecord, but that's a different story.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: ORM Database Abstraction

Post by Christopher »

allspiritseve makes a good point that you are mixing up your concepts and terms. I would recommend looking into the ActiveRecord and TableDataGateway patterns.
(#10850)
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: ORM Database Abstraction

Post by VirtuosiMedia »

allspiritseve wrote:
VirtuosiMedia wrote:I may not always get my terms right because I'm still learning, but I had thought that ORM could be described as one object per database table or per database query. This is a DAL, but there is also one class per table in the database that extends the base database class. Maybe that's not ORM, but I'm not quite sure what to call it.
Well, maybe I was a little hasty in posting, but... an ORM is an Object Relational Mapper. The idea is that you works in terms of objects, and are not aware that the object is being persisted in the database. What I see your code doing is building a query, and then allowing the results to be accessed in the same way you would deal with an array. It is similar to ActiveRecord, but I think the crucial difference is that you are aware you are working with a query and query results. If you internalize the query construction, and your methods to work with the object deal with user methods and not query construction methods, your class will be much closer to ActiveRecord.

Now, I am sure you will say that is moving away from your original goal of database abstraction. My suggestion would be to split these into two separate classes-- one that is a User object that constructs a DAL object inside its methods. As it is, you're trying to do two different things with the same object, and OOP works best if one object handles one thing and one thing only.

Now, I much prefer Gateways and Data Mappers over ActiveRecord, but that's a different story.
Thanks for your post, you definitely gave me some food for thought. I did some Googling and found this thread in which a TableDataGateway was defined as "one object [that] acts as a gateway to one table in your database. All access to this tables goes through this object."

I don't know for sure if that's an accurate definition, but I think it better describes my code than the way I first described it. Still, from what I've read so far, it looks like parts of my code are also similar to a light (or flexible maybe?) ActiveRecord. However, I didn't want to internalize the queries because I felt that doing so would take away from the flexibility. Because my application is a CMS and I have a goal/hope of having other people eventually develop modules or plugins for it, I don't know how the data will be used in the future, so I tried to make it as accessible and flexible as I knew how. Making it like ActiveRecord wasn't a goal. Beyond the abstraction, it wasn't really meant to be black boxy, just easy to write.

Your suggestion about using two classes has me thinking. What do you mean exactly by having it construct the DAL object? In a way, I already do have two classes and the user class extends and populates the DAL object through the parent constructor. Is that what you're describing? My abstraction layer is my DBObject class and then my users (I'm not really sure how to describe it at this point...TableDataGateway/ActiveRecord?) class extends the abstraction layer by passing in the table specific information. Each table in the database will also extend the abstraction layer in the same way, but I won't have to hardcode the methods for each (like I think you would have to for true ActiveRecord, but feel free to correct me on that) because the methods are created by the table data passed into the constructor.

I think I'm mixing a few different patterns into this, but it also seems like there are a lot of benefits to this method: I have most of the flexibility of basic SQL; I'm able to write the code for the queries once and have it work on multiple database types, as long as I have a corresponding DBObject class for that specific database type; I don't have to hardcode my methods, so I can save a lot of code writing; because of the dynamic methods, if the table structure changes, all I have to do to add/delete a method is to type or remove the column name in the extending class; the syntax is fairly simple (aside from the curly braces and quotes) and is similar to SQL; and the data is accessible and isn't locked behind black box methods and it can be used in multiple ways by future developers.

There are disadvantages as well that I mentioned earlier, but functionally it meets the goals that I needed it to meet. All of that said, I'd like to do it better if I can. I know I still have to optimize the internal code, but if there is a different approach that will serve better, I'd love to explore it. Could you elaborate a little more about your suggestion and what advantages/disadvantages it might offer over this method?
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: ORM Database Abstraction

Post by inghamn »

I just got done writing our CMS over here. For my stuff I ended up doing my own ORM that uses both ActiveRecord style and Collection style stuff. It meant having two classes per table, though. one for the ActiveRecord and one for the collection.

I ended up going this route because I liked the style of code that they enabled.
Stuff like:

Code: Select all

 
$user = new User($id);
$user->setName('Ellen');
$user->save();
 
$user = new User($username);
echo $user->getName();
 
$userList = new UserList(array('department'=>'Help Desk'));
foreach($userList as $user)
{
    echo $user->getUsername();
}
 
The List classes handle the selecting of sets by criteria, handle the iteration, and return the ActiveRecord objects as they iterate.

This came about, really, just because I've never felt quite comfortable writing stuff in the style of

Code: Select all

 
$user = User::loadByID('id'=>$id);
$users = User::findByDepartment('Help Desk');
 
Post Reply