As the Active Record class has become increasingly popular due to it’s wild success in Ruby on Rails, it seems as though every framework has an implementation of it. It is no surprise that inside the covers of the MyEPICS framework lives an active record class that I had created. This class has evolved and changed over time, which I am going to share my experiences writing it. It is far from finished, but it has been quite a great learning experience along the way.
2.0 Implementation
The MyEPICS 2.0 implementation contained one master database class (ME_DB) which contained generic CRUD functions, and each table contained its own class which would implement the specific CRUD functions for each table (ME_DBO). A sample way to read users with the name ‘bob’ would be the following:
$user=new ME_DBO_User();
$user->read(array('name'=>'bob'));
And to update a person’s last name to “foobar”, you would do the following:
$user->lastname="foobar";
$user->update();
About 90% of each of the ME_DBO classes were the same code, and there was even a script to create a DBO class given a SQL create statement. A major problem with this approach is that for every new table created, you needed to make sure you created a new ME_DBO file, and that the file really only contained such things as which columns were in the table and their default values. We would have about 2k lines of code between 30 files, of which only about 50 lines were different… YUCK! Over Christmas break, I decided that this was a major, major issue that needed to be worked out before MyEPICS 2.1.
MyEPICS 2.1 Implementation
With the 2.1 implementation, I took a step back and attempted to create two classes which would fix the pitfalls of the 2.0 implementation. What I ended up doing was using MBD2′s reverse engineering module in order to find out which fields were available and what their default values were. This reduced the need for a separate file for each table and removed about 1500 lines of code from the Active Record implementation. Now, instead of
$user=new ME_DBO_User();
You would do this instead
$user=ME_Db::factory('User');
This has the main database create a Dbo object with just the right properties that you need.
The only major gripe I have with the current implementation is that I don’t implement anything for relationships. If you need information from a relationship, then you have three options.
- Cry
- Write Manual SQL
- Write many lines of code which does a lot of extra sql queries and is inefficient and crappy
I mainly want to create something so that the relationship table is transparent. A lot of the time, your relationship table doesn’t have anything of value in it, and it only exists for a many to many relationship.
SQL Command Functions
About
One such feature that I see in a lot of Active Record implementations is the ability to use functions named after SQL commands, such as:
$db=new Some_Active_Record_Class();
$db->select('firstname')->from('User')->where('id=5');
Mostly, I had disregarded this need in my Active Record implementation because it seemed like it was a lot of work for very little gain. Why would you need to allow such functionality when creating a simple sql statement would do?
Well, I think I had just stumbled upon the reason for its need.
The Problem
I have a class for a user and contains information pertaining to the user such as first name, last name, e-mail address etc… It has some very useful functions such as getUserWithRole($role) and getPriviliges($user). What I need to be able to do, is extend this information through one of the MyEPICS modules. Such an example is in my Roommate Finder application, which extends the user to give them traits. The user module does not know about the Roommate Finder application, nor should it have to. Now, here’s the problem. What I need to be able to do, is return a result set of all the users’ traits, where the user has a certain role. I already have the code to return all users with a certain role in the Users module, now I just need to attach the trait information to the output of it.
I have come up with a few different solutions that would plug this hole, but not fix the problem, with most of them being some variation of typing up a single sql statement to get me the information. What I don’t want to do, is getUsersWithRole(), then iterate through all of theses picking out the traits for each individual user. This would result in at least one sql statement per user, which is highly innefficient.
I want to be able to execute this functionality in one sql query, without having to tightly couple the User module with the Roommate Finder module, and without duplicating functionality already existing in the User module.
A Solution?
What I have been thinking about that would solve this problem would end up having me creating something like the database command functions. Then, what the getUserWithRoel() function would do, is instead of returning the results, it would return a Dbo object with the sql already added. So that if getUsersWithRole() would normally result in some query like “select role from roles where role=?”, then it would now return an object that represents this statement. What this would allow me to do is chain these functions together, then only execute the query at the end. For example:
//fetch results in an array
$dbo=$userModule->withRole('Tenant')->join('Traits')->fetchArray();
This has several features which I haven’t implemented. Easy relationships, chaining of functions, and the sql commands.
Is it bad that I am never satisfied with what I already have, and always want to refactor to make it better?
