SnippetBank.Net: PHP - Simple Database ORM

DataContext is a simple ORM class that can be used to manage CRUD operations on a database. Standard PHP objects or arrays are used to insert/update data.

DataContext

The DataContext class is an abstract class and cannot be instantiated directly, it must be inherited from. Once inherited, you must overwrite the initialize() method of the DataContext class and add tables to the context using the addSet() method. A class that inherits from the DataContext class will inherit methods that can then be used to insert,update and query a database. The DataContext class and it's related DbSet class can be found at the end of this article.

Usage

class BlogContext extends DataContext {
/*
* BlogContext is a concrete class.
* DbSets must be added to the context using the addSet() method.
*/
protected function initialize(){
$this->addSet('users', 'user_id');
$this->addSet('posts', 'post_id');
}
}

The addSet() method is used to add a table to the context and accepts two parameters. The first, is the database table name and the second is the table primarykey. In the above code sample, a new concrete class BlogContext has been defined with two datasets that have been added to the context. The following code examples, show how to connect to a MySQL database and perform insert,update and query operations using the BlogContext

Create a new instance of the BlogContext.

$db = new BlogContext();

$db = new BlogContext("driver=mysql;host=localhost;dbname=blog;uid=root;pwd=");

By default, a parameterless constructor will attempt to connect to a local MySQL server. The name of the database is the lowercase concrete class name (blogcontext). Alternatively a connection string can be supplied to the constructor.
The following code sample shows how to add a user to the context. Note that the context exposes a magic users property. This property only exists because a DbSet 'users' was added to the context in the initialize() method.

$user = new stdClass();
$user->username = 'John';
$user->password = 'pass123';
$user->email = 'j.smith@mailxyz.com';
$db = new BlogContext();
$db->users->insert($user);
$db->saveChanges();

Multiple users can be added to the 'users' DbSet then saved at once.

$db->users
->insert($user1);
->insert($user2);
$db->saveChanges();

The following code sample below shows how to update a user that exists in the users table. Note that the $user object has a 'user_id' property with a value. This value is used to update the record.

$user = new stdClass();
$user->user_id = 1;
$user->username = "John";
$user->password = "pass43211";
$user->email = "john.smith@mailxyz.com";
$db = new BlogContext();
$db->users->update($user);
$db->saveChanges();

The BlogContext provides a simple association for inserting and updating data entities. The example code below shows how to create a new user and a new blog post using association.

$user = new stdClass();
$user->first_name = "Linda";
$user->last_name = "Baker";
$user->email = "l.baker@mailxyz.com";
$post = new stdClass();
$post->user_id = $user;
$post->title = "My First Post";
$post->body = "This is my first blog post";
$post->created_date = date("Y-d-m");
$db = new BlogContext();
$db->users->insert($user);
$db->posts->insert($post);
$db->saveChanges();

Notice that the $post object has a 'user_id' property which has been assigned the $user object. The BlogContext internally assigns the last insert id from the $user object to the user_id property of the $post object.

The DataContext and it's related DbSet class is shown below.

DataContext

<?php
abstract class DataContext {
/*
* Database connection settings
*/
protected $dbConfig;
/*
* PDo instance
*/
protected $pdo;
/*
* Collection of datasets
*/
protected $dbSets = array();
/*
* Stores a collection of persisted entities for lookup
*/
protected $entities = array();
/*
* A parameterless constructor will attempt to connect on localhost with
* default local settings.
*/
public function __construct($connectionString = null){
/*
* Example connection string
* driver:mysql;host=localhost;uid=root;pwd=password
*/
if($connectionString){
$pairs = explode(';', $connectionString);
foreach($pairs as $pair){
if(strpos($pair, '=')){
list($name, $value) = explode('=', $pair);
$this->dbConfig[$name] = $value;
}
}
}else{
$this->dbConfig['driver'] = 'mysql';
$this->dbConfig['host'] = 'localhost';
$this->dbConfig['dbname'] = strtolower(get_called_class());
$this->dbConfig['uid'] = 'root';
}
$dsn = sprintf('%s:host=%s;dbname=%s;',
$this->getDbConfig('driver'),
$this->getDbConfig('host'),
$this->getDbConfig('dbname')
);
$this->pdo = new Pdo($dsn,
$this->getDbConfig('uid'),
$this->getDbConfig('pwd')
);
/*
* Throw exceptions instead of warnings
*/
$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$this->initialize();
}
private function getDbConfig($name){
if(isset($this->dbConfig[$name])){
return $this->dbConfig[$name];
}
}
/*
* This method is intended to be overwritten in a base class.
* It is used to add DbSets.
*/
protected function initialize(){}
/*
* Adds a DbSet to the Context. The setName is the table name.
*/
protected function addSet($setName, $primaryKey){
$dbSet = new DbSet($this, $setName, $primaryKey);
$this->dbSets[$setName] = $dbSet;
}
/*
* Returns a DbSet.
*/
public function getDbSet($setName){
if(isset($this->dbSets[$setName])){
return $this->dbSets[$setName];
}
}
/*
* Magic method alias to getDbSet.
*/
public function __get($setName){
return $this->getDbSet($setName);
}
/*
* Issues an SQL statement and returns a PDOStatement instance
*/
public function query($sql, array $params = array()){
$stm = $this->pdo->prepare($sql);
$stm->execute($params);
return $stm;
}
/*
* Issues an SQL statement used to fetch a single table row.
*/
public function fetch($sql, $params = array(), $fetchSytle = \PDO::FETCH_OBJ){
$stm = $this->query($sql, $params);
return $stm->fetch($fetchSytle);
}
/*
* Issues an SQL statement used to fetch table rows.
*/
public function fetchAll($sql, $params = array(), $fetchSytle = \PDO::FETCH_OBJ){
$stm = $this->query($sql, $params);
return $stm->fetchAll($fetchSytle);
}
/*
* Issues an SQL statement used to fetch a single row column.
*/
public function fetchOne($sql, $params = array()){
$stm = $this->query($sql, $params);
return $stm->fetchColumn();
}
/*
* Inserts data into a table. $data can be either an array or an object.
*/
public function insert($tableName, $data){
if(is_object($data)){
$data = get_object_vars($data);
}
$placeHolders = trim(str_repeat('?,', count($data)), ',');
$sql = 'INSERT INTO ' . $tableName . ' (' . implode(',', array_keys($data));
$sql .= ') VALUES (' . $placeHolders . ')';
$stm = $this->query($sql, array_values($data));
if($stm->rowCount() > 0){
return true;
}
return false;
}
/*
* Updates data in a table. $data can be either an array or an object.
* $conditions is the search criteria used to update the record.
*/
public function update($tableName, $data, array $conditions){
if(is_object($data)){
$data = get_object_vars($data);
}
$params = array();
$sql = 'UPDATE ' . $tableName . ' SET ';
foreach($data as $field=>$value){
$sql.= $field.'=:'.$field.',';
$params[':'.$field] = $value;
}
$sql = rtrim($sql, ',') . ' WHERE ';
$idx=0;
foreach($conditions as $field=>$value){
if($idx > 0){
$sql.= ' AND ';
}
$sql.= $field.'=:c_'.$field;
$params[':c_'.$field] = $value;
++$idx;
}
$stm = $this->query($sql, $params);
if($stm->rowCount() > 0){
return true;
}
return false;
}
/*
* Returns the last insert id.
*/
public function getInsertId($field = null){
return $this->pdo->lastInsertId($field);
}
/*
* Persists, all data objects to the database.
*/
public function saveChanges(){
foreach($this->dbSets as $dbSetName=>$dbSet){
$data = $dbSet->getData();
if(isset($data['INSERT'])){
$insertDataSets = $data['INSERT'];
foreach($insertDataSets as $entity){
foreach($entity as $propertyName=>$value){
if(is_object($value)){
$hash = spl_object_hash($value);
if(isset($this->entities[$hash])){
$assocDataEntry = $this->entities[$hash];
$assocDbSet = $this->getDbSet($assocDataEntry['setName']);
$assocPrimaryKey = $assocDbSet->getPrimaryKey();
$assocData = $assocDataEntry['entity'];
$value = $assocData->{$assocPrimaryKey};
$entity->{$propertyName} = $value;
}
}
}
$insert = $this->insert($dbSetName, $entity);
if($insert){
$primaryKey = $dbSet->getPrimaryKey();
$insertId = $this->getInsertId($primaryKey);
$entity->{$primaryKey} = $insertId;
$hash = spl_object_hash($entity);
$this->entities[$hash] = array(
'setName' => $dbSetName,
'entity' => $entity
);
}
}
}
if(isset($data['UPDATE'])){
$insertDataSets = $data['UPDATE'];
foreach($insertDataSets as $entity){
foreach($entity as $propertyName=>$value){
if(is_object($value)){
$hash = spl_object_hash($value);
if(isset($this->entities[$hash])){
$assocDataEntry = $this->entities[$hash];
$assocDbSet = $this->getDbSet($assocDataEntry['setName']);
$assocPrimaryKey = $assocDbSet->getPrimaryKey();
$assocData = $assocDataEntry['entity'];
$value = $assocData->{$assocPrimaryKey};
$entity->{$propertyName} = $value;
}
}
}
$primaryKey = $dbSet->getPrimaryKey();
if(isset($entity->{$primaryKey})){
$value = $entity->{$primaryKey};
$this->update($dbSetName, $entity, array($primaryKey => $value));
}else{
throw new Exception(sprintf("Unable to update entity '%s'. Primarykey '%s' is not present in entity data.", $dbSetName, $primaryKey));
}
}
}
}
}
}
/*
* Class that represents a table and stores a list of data to persist.
*/
class DbSet {
protected $dbContext;
protected $dbSetName;
protected $primaryKey;
protected $data = array();
public function __construct($dbContext, $dbSetName, $primaryKey){
$this->dbContext = $dbContext;
$this->dbSetName = $dbSetName;
$this->primaryKey = $primaryKey;
}
public function getPrimaryKey(){
return $this->primaryKey;
}
public function insert($data){
$this->data['INSERT'][] = $data;
return $this;
}
public function update($data){
$this->data['UPDATE'][] = $data;
return $this;
}
public function find($value){
return $this->dbContext->fetch(
"SELECT * FROM ". $this->dbSetName . " WHERE " . $this->primaryKey . "=:value",
array(":value"=>$value)
);
}
public function getData(){
return $this->data;
}
}
?>

PHP - Simple Database ORM

DataContext

Usage

DataContext

No comments:

Post a Comment