I have been using Akelos both for professional and personal uses for a long time now. But sometimes I only need the ORM part of Akelos, and not the whole MVC shebang. So for a while ago, I made a ~40 lines php script that abstracted mysql queries in a easy to use class, much alike how Akelos’ AKActiveRecord works. But just much much simpler.
Then for a few days ago I thought, why not extend (rewrite) it, and add support for relations and serialization, etc; but still keep it simple enough to be one single small include for my small projects, with only two lines of configuration needed?
So here is initial version under LGPL license: SimpleActiveRecord-v0.1.tar.gz.
In the tarball above, you will find everything you need in the file “simpleactiverecord.php”, but included is also a test suite using the simpletest library.
To use the orm functions, all you need is the simpleactiverecord.php. But if you are going to extend it, or check that all the functionality is intact, you can use the included tests to check that the all the desired functionality is intact after your changes. To run the tests, just invoke ./test.sh from the command line. (if you are using debian/ubuntu, you need to have the php-cli package installed to do this)
To begin; I will add some phpDoc in the future! It’s not a finished project.
Examples
Here are some examples and explanations of how you can use this simple ORM:
First the mysql database I use for these examples:
CREATE TABLE `users` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`customer_id` INT(11) NOT NULL,
`username` VARCHAR(50) DEFAULT NULL,
`meta` text,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`)
);
INSERT INTO `users` VALUES (1,1,'someuser','N;');
CREATE TABLE `customers` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `customers` VALUES (1,'CustomerName 1');
CREATE TABLE `blogposts` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(20) DEFAULT NULL,
`title` VARCHAR(255) DEFAULT NULL,
`body` text,
`created_at` datetime DEFAULT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
);
INSERT INTO `blogposts` VALUES (1,1,'Blogpost 1','This is my first blog',NULL,'2010-08-20 12:54:02'),(2,1,'Blogpost 2','This is my second blog',NULL,'2010-08-20 12:54:09'); |
CREATE TABLE `users` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`username` varchar(50) DEFAULT NULL,
`meta` text,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`)
);
INSERT INTO `users` VALUES (1,1,'someuser','N;');
CREATE TABLE `customers` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `customers` VALUES (1,'CustomerName 1');
CREATE TABLE `blogposts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`body` text,
`created_at` datetime DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
);
INSERT INTO `blogposts` VALUES (1,1,'Blogpost 1','This is my first blog',NULL,'2010-08-20 12:54:02'),(2,1,'Blogpost 2','This is my second blog',NULL,'2010-08-20 12:54:09');
And then some code to test the functionality:
<?
require_once('simpleactiverecord.php');
class User extends SimpleActiveRecord {
public $has_many = array( 'blogposts' => 'Blogpost' );
public $belongs_to = array( 'customer' => 'Customer' );
public $serialize = 'meta';
}
class Customer extends SimpleActiveRecord {
public $has_many = array( 'users' => 'User' );
}
class Blogpost extends SimpleActiveRecord {
public $belongs_to = array( 'user' => 'User' );
}
SimpleDbAdapterWrapper::setAdapter('mysqlAdapter');
SimpleDbAdapterWrapper::connect('127.0.0.1', 'ormtest', 'passord', 'ormtestdb');
$user = new User('username', 'someuser');
/* or $user->findFirstBy('field', 'value'); */
print "Username: " . $user->username . "\n";
print "Customer: " . $user->customer->name . "\n\n";
foreach ($user->blogposts as $blogpost) {
// Dumps the whole blogpost object
print $blogpost;
// Or fetch data from it manually:
print "Blog title: " . $blogpost->title . "\n";
print "Blog written by: " . $blogpost->user->username . "\n";
print "---\n";
}
?> |
<?
require_once('simpleactiverecord.php');
class User extends SimpleActiveRecord {
public $has_many = array( 'blogposts' => 'Blogpost' );
public $belongs_to = array( 'customer' => 'Customer' );
public $serialize = 'meta';
}
class Customer extends SimpleActiveRecord {
public $has_many = array( 'users' => 'User' );
}
class Blogpost extends SimpleActiveRecord {
public $belongs_to = array( 'user' => 'User' );
}
SimpleDbAdapterWrapper::setAdapter('mysqlAdapter');
SimpleDbAdapterWrapper::connect('127.0.0.1', 'ormtest', 'passord', 'ormtestdb');
$user = new User('username', 'someuser');
/* or $user->findFirstBy('field', 'value'); */
print "Username: " . $user->username . "\n";
print "Customer: " . $user->customer->name . "\n\n";
foreach ($user->blogposts as $blogpost) {
// Dumps the whole blogpost object
print $blogpost;
// Or fetch data from it manually:
print "Blog title: " . $blogpost->title . "\n";
print "Blog written by: " . $blogpost->user->username . "\n";
print "---\n";
}
?>
This gave me the follwoing output:
Username: someuser
Customer: CustomerName 1
Blogpost(1)
Id: 1
User_id: 1
Title: Blogpost 1
Body: This is my first blog
Created_at:
Updated_at: 2010-08-20 14:54:02
User: (reference to a User object)
Blog title: Blogpost 1
Blog written by: someuser
---
Blogpost(2)
Id: 2
User_id: 1
Title: Blogpost 2
Body: This is my second blog
Created_at:
Updated_at: 2010-08-20 14:54:09
User: (reference to a User object)
Blog title: Blogpost 2
Blog written by: someuser
---
Saving / Updating
If I want to change something in for user with id 1 I can simply write:
$user = new User(1);
$user->username = 'newusername';
$user->save(); |
$user = new User(1);
$user->username = 'newusername';
$user->save();
This will update sql with the new values of your user object.
Relations
As you can see in the example; we have three databases with relations to eachother. To relate the models to eachother, all you have to do is to define the relationship in the ‘magic’ variables called $belongs_to and $has_many. In the example, I have related the Customer model to the User model, by saying “$has_many = array(‘users’ => ‘User’);” which means that the User table has a column called customer_id, which points to the “Customer” model. This means that if you have a Customer object, you can access an array of all the users to this customer, by using the $customerobject->users property. This will automatically “lazy load” all the users for your selected customer.
Custom table names or field names
If your users table haven’t defined the customer id field as “customer_id” which was automatically guessed, you can define it by saying: “var $has_many = array(‘users:custom_customer_id’ => ‘User’);”. Now instead of searching for customer_id in the Users table, it will search for the correct customer id in the custom_customer_id field of the users table. The same goes to the $belongs_to relations.
Also, if you do not define the primary key of your tables as ‘id’. Maybe you have your table’s primary key named ‘user_id’, you can define this in your model definition. The same goes if you define for example a User class, but the table is names ‘my_users’; you can explicitly define both like this:
class User extends SimpleActiveRecord {
public $tableName = 'my_users';
public $primaryKey = 'user_id';
} |
class User extends SimpleActiveRecord {
public $tableName = 'my_users';
public $primaryKey = 'user_id';
}
Serialization
A nifty feature of this library is also automatic serialization. As you can see in the definition of the User class in the first example, i have told the library that the field ‘meta’ is a serialized field. This means that it wil automatically be serialized with php’s “serialize()” function before it is saved to SQL. This enables you to add php variables and objects to your object for later use when you load the object from sql again. Let me give an example:
$user = new User(1);
$user->meta['is_logged_in'] = 1;
$user->meta['something_useful'] = array('this', 'is', 'useful', 'information', 'about', 'someuser');
$user->save(); |
$user = new User(1);
$user->meta['is_logged_in'] = 1;
$user->meta['something_useful'] = array('this', 'is', 'useful', 'information', 'about', 'someuser');
$user->save();
This will then save the serialized content of meta to sql. The following SQL query happened behind the curtains:
UPDATE `users` SET `customer_id` = 1, `username` = 'someuser', `meta` = 'a:2:{s:12:\"is_logged_in\";i:1;s:16:\"something_useful\";a:6:{i:0;s:4:\"this\";i:1;s:2:\"is\";i:2;s:6:\"useful\";i:3;s:11:\"information\";i:4;s:5:\"about\";i:5;s:8:\"someuser\";}}' WHERE `id` = 1 LIMIT 1 |
UPDATE `users` SET `customer_id` = 1, `username` = 'someuser', `meta` = 'a:2:{s:12:\"is_logged_in\";i:1;s:16:\"something_useful\";a:6:{i:0;s:4:\"this\";i:1;s:2:\"is\";i:2;s:6:\"useful\";i:3;s:11:\"information\";i:4;s:5:\"about\";i:5;s:8:\"someuser\";}}' WHERE `id` = 1 LIMIT 1
If we load user from sql again now and print the meta field:
$user = new User(1);
print_r($user->meta); |
$user = new User(1);
print_r($user->meta);
We can see that it has correctly remembered the structure of our meta variable.
Array
(
[is_logged_in] => 1
[something_useful] => Array
(
[0] => this
[1] => is
[2] => useful
[3] => information
[4] => about
[5] => someuser
)
)
You can also have more than one serialized field by specifying it in the class definition:
public $serialize = 'meta,metafield2'; |
public $serialize = 'meta,metafield2';
or
public $serialize = array('meta', 'metafield2'); |
public $serialize = array('meta', 'metafield2');
Setting / getting multiple keys at once
To set a whole bunch of variables in the object at once, you can use setAttributes(). This function sets the corresponding fields to the values in the associated array given as argument:
$user->setAttributes(array( 'name' => 'newname', 'customer_id' => 2 ));
// or fetch all data from a model as an array:
print_r($user->getAttributes()); |
$user->setAttributes(array( 'name' => 'newname', 'customer_id' => 2 ));
// or fetch all data from a model as an array:
print_r($user->getAttributes());
Searches
You can also search for specific fields, or specify a WHERE statement manually. For example. If you are searching for all customers with a specific customer name, you can do the following:
$customer = new Customer();
$customers = $customer->findBy(‘name’, ‘CustomerName’);
print_r($customers);
This would give you an array of Customer objects. You probably notice that I have defined $customer as an empty instance of the Customer object, before using it to find the customers and wonered why.. The reason for this is that PHP < 5.3.0 doesn’t support late static bindings. And I would like to support < 5.3.0 since a lot of distributions haven’t moved to 5.3.x yet.
There is also a function called findFirstBy(), which works in a similar matter, but gives you only 1 object.
The find() and findFirst() functions allow you to define the full WHERE clause yourself. But remember to escape your data when you use these two functions. These are the only two functions that does not automatically escape your input.
Model code
Inside your model (the class definition of your table), you should add functions to handle tasks for your model that is more advanced than setting a variable and saving. For example in a User model, you might want to encrypt the users password just before the object is saved to sql.
Things like this can be done by defining beforeSave(), afterLoad() and beforeDestroy() functions in your model. They do as you think they do. And if your beforeSave() or beforeDestroy() functions return a false boolean, the save- or delete-operation will be aborted!
Exceptions
The class only has two custom exceptions, SqlErrorException and InvalidDbAdapterException. I think the names describes themselves pretty good.
Remember to catch them. InvalidDbAdapterException usually only arrives if your dbAdapter is uncomplete. You should not be able to get this error with for the included mysqlAdapter.
Thats all folks!
Until I add phpDoc documentation, there’s only one way to learn about all the features included. There’s a lot of functions that is not mentioned here. But you can easily find about them by reading the source code. It shouldn’t be all too cryptic to understand.
