EARLib : Zero-Config PHP ORM Library
Uncategorized - April 22, 2009 6:27 am
Welcome to the EARLib page. EARLib is an ORM library for the php scripting language. It only works with MySQL databases but requires basically no configuration to set up and use. Just set the library’s connection information and it handles the back-end for you.
Features
EARLib provides table abstraction through php classes that support standard Create, Read, Update, and Delete features. Each table can be thought of as an object. Its fields can be accessed through getter and setter methods. Loading a row from the database is as simple as calling the table’s load method and saving by calling the save method. In addition to basic CRUD, EARLib also supports simple foreign key relationships. If foreign key relationships exist on the table they can be followed to retrieve collections of related tables.
Apart from the ORM table objects, EARLib also provides SQL statement classes that let you construct complicated sql statements using object method calls. These constructed statements can return an array of result rows or a collection of table objects. You can even spit back the generated sql if you wish.
The library is smart enough to recognize common pitfalls, like grouping non-aggregated fields when an aggregated field is in the field list, or forgetting to quote a string field. It’s smart enough to recognize auto-increment and primary key fields. And it’s smart enough to do all of this without any pre-generated code or xml description documents.
Zero-Config? How?
The EARLib library uses mysql DDL statements, lazy loading of schema and data, schema caching, and blind php methods to discover the database as you ask for it. The library doesn’t know anything about a table until you ask for that table. At that time it queries mysql to find out what fields exist on the table, what the keys are, what the relationships (local and remote) are, etc. It then uses that discovered schema information to produce generic row buffers that are general enough to work for any table but smart enough to prevent you from doing things you don’t want to do (like assigning values to fields that don’t exist).
How does it work?
Along with the library is a test.php script. This script uses a very basic schema and walks through most of the major features. I’ve pasted the test script in below as a sample of EARLib in action:
/*This file is a big bunch of examples using a trivial schema:
[Task]
ID int AutoIncrement PK;
ProjectID int FK->Project.ID;
Title varchar(40);
Description varchar(255);
[Project]
ID int AutoIncrement PK;
Name varchar(40);
Description varchar(255);
ClientID int;
*/
//This line includes the correct files and configures the default instance
require_once('EARSetup.php');
//ORM Object examples
echo "Inserting a Project<BR>";
$project = EARFactory::Table("Project")->RowBuffer(); //Get a Project RowBuffer
$project->SetName("PROJECT NAME"); //Set the name
$project->SetClientID(1); //Set the Client ID
$project->Save(); //Generates and executes an insert
echo "<br>Inserting a Task<BR>";
$task = EARFactory::Table("Task")->RowBuffer(); //Get a Task Rowbuffer
$task->SetTitle("TASK TITLE"); //Set title
$task->SetDescription("TASK DESCRIPTION"); //Set Description
$task->SetProjectID(1); //Set ProjectID
$task->Save(); //Generates and executes an insert
echo "<br>Update Task ID 1 Description<BR>";
unset($task); //Just to prove it works
$task = EARFactory::Table("Task")->RowBuffer()->Load(1); //Load Task with ID 1 -- load argument is PK value
$task->SetDescription("NEW DESCRIPTION"); //Change the descriptino
$task->Save(); //Generates and executes an update
echo "<br>Showing you Task ID 1 Information<BR>";
unset($task);
$task = EARFactory::Table("Task")->RowBuffer()->Load(1);
echo "ID: " . $task->GetID() . "<BR>"; //Spit out some values
echo "Description: " . $task->GetDescription() . "<BR>";
echo "<br>Creating then deleting a Task<br>";
$task = EARFactory::Table("Task")->RowBuffer(); //Get a Task Rowbuffer
$task->SetTitle("JUNK TITLE"); //Set title
$task->SetDescription("JUNK DESCRIPTION"); //Set Description
$task->SetProjectID(1); //Set ProjectID
$task->Save(); //Generates and executes an insert
$ID = $task->GetID(); //Get the auto-generated id
echo " Inserted Task ID is : " . $ID . "<BR>";
unset($task);
$task = EARFactory::Table("Task")->Rowbuffer()->Load($ID); //Load the inserted row
$task->Delete(); //and delete it
echo " Deleted Task ID : " . $ID . "<BR>";
echo "<br>Getting all tasks for Project 1<BR>";
$project = EARFactory::Table("Project")->RowBuffer()->Load(1); //Load Project 1
$task_collection = $project->RelTask(); //get collection of related task objects
foreach($task_collection as $task) //cycle through the related tasks
{
echo " TASK ID: " . $task->GetID() . "<BR>";
echo " Title: " . $task->GetTitle() . "<BR>";
}
echo "<br>Getting the project related to Task ID 1";
unset($task);
$task = EARFactory::Table("Task")->RowBuffer()->Load(1); //Load up the task
$project = $task->RelProject(); //Can only be one project but it's still in an array
echo "Project ID: " . $project[0]->GetID() . "<BR>";
//Statement builder examples:
echo "<br>Getting Task ID 1 using select builder<BR>";
$select = EARFactory::Select();
$select->From("Task")->pWhere("ID")->Equals(1); //Select Task ID 1
$task = $select->ExecuteSingleRowBuffer(); //Get it back as a row buffer
echo "Title(buffer): " . $task->GetTitle() . "<BR>";
$task = $select->ExecuteSingleArray(); //Get back an array
echo "Title(array): " . $task[2] . "<BR>";
echo "<br>Getting all Tasks using select builder<BR>";
$select = EARFactory::Select();
$select->From("Task");
$task_collection = $select->ExecuteRowBufferCollection();
foreach($task_collection as $task)
{
echo "Task ID(buffer): " . $task->GetID() . "<BR>";
echo " Title: " . $task->GetTitle() . "<BR>";
}
echo "<BR><BR>";
$task_collection = $select->ExecuteArrayCollection();
for($i = 0; $i < count($task_collection); $i++)
{
echo "Task ID(array): " . $task_collection[$i][0] . "<BR>";
echo " Title: " . $task_collection[$i][2] . "<BR>";
}
echo "<BR>Updating Task 1 title using update builder<br>";
$update = EARFactory::Update("Task"); //Create new update statement for task table
$update->Set("Title", "Another New Title"); //Set title
$update->pWhere("ID")->Equals(1); //build the where
$update->Execute(); //Run it
echo "The sql for that was: " . $update->constructSQL(); //Spit out the sql too
echo "<BR>Delete Task 1000<br>";
$delete = EARFactory::Delete();
$delete->From("Task")->pWhere("ID")->Equals(1000)->Execute(); //All in one line
//And now a fairly complicated select
$select = EARFactory::Select();
$select->From("Task")->Field("ProjectID")->Count("ID")->pWhere("ProjectID")->GreaterThan(0)->pAND("Title")->Like("%TEST%")->pNOTGroup()->pField("ID")->Equals(1)->pOR("ID")->Equals(2)->pEndGroup();
echo $select->constructSQL();
Status
This is definitely an immature product. There are bugs, there are security issues, there is poor error reporting. This is not production software. However, I’m going to improve it regularly and I’m happy to hear any bugs, comments or suggestions.
No Comments »
No comments yet.
RSS feed for comments on this post. TrackBack URI
Leave a comment