Active Record - advice and features
Posted: Sun Oct 08, 2006 11:38 pm
I put together a few classes for an active-record system I am working on. I realize active record has some issues, but the idea sounded interesting so I thought I'd try building one. I would like some critique on how I'm doing so far, and then maybe some advice on features that would make it useful to more people than just myself.
My classes make use of D11wqt's Mysql Iterator... slightly modified of course
Here is the main class MC2_Mysql_Model:
And it's child class, MC2_Mysql_Model_Field
My classes make use of D11wqt's Mysql Iterator... slightly modified of course
Here is the main class MC2_Mysql_Model:
Code: Select all
<?php
class MC2_Mysql_Model{
/**
* Mysql connection class
* Must be set statically to a connected MC2_Mysql object
* @static
*/
private static $mysql;
/**
* Table's primary key
* @string
*/
protected $primaryKey;
/**
* Name of associtated table
* @string
*/
protected $table;
/**
* Fields in this table
* @array
*/
protected $fields = array();
/**
* Constructor
* @param Mysql connection class
*/
public function __construct(){
if(!self::$mysql){
throw new MC2_Mysql_Model_Exception('You must set Mysql_Model::mysql to a Mysql object to use Mysql_Model');
return;
}
$this->defineFields();
}
/**
* Sets static $mysql property to a MC2_Mysql object
* @param Mysql connection class
*/
static public function setConnection($Mysql){
if($Mysql instanceof MC2_Mysql){
self::$mysql = $Mysql;
return;
}
throw new MC2_Mysql_Model_Exception('You must set Mysql_Model::mysql to a Mysql object to use Mysql_Model');
}
/**
* Load field definitions
*/
protected function defineFields(){
$sql = "SHOW COLUMNS FROM `" . $this->table . "`";
$result = self::$mysql->query($sql);
for($result->first(); !$result->end(); $result->next()){
$field = new MC2_Mysql_Model_Field(
$result->Field,
$result->Type,
$result->Null,
$result->Key,
$result->Default,
$result->Extra
);
$this->fields[$result->Field] = $field;
if($field->isPk()) $this->primaryKey = $result->Field;
}
}
/**
* Get total rows in database
* @returns integer
*/
public function count($conditions=null){
$sql = "SELECT COUNT(*) as `count` FROM `" . $this->table . "`";
if($conditions) $sql .= "WHERE " . $conditions;
$result = self::$mysql->query($sql);
return $result->count;
}
/**
* Loads a record
* @param string $conditions Where clause (optional)
* @param array $fields fields to load (optional)
*/
public function load($conditions, $fieldsArray=null){
$select_fields = is_array($fieldsArray) ? implode(",", $fieldsArray) : '*';
$sql = "SELECT " . $select_fields . " FROM `" . $this->table . "` WHERE " . $conditions . " LIMIT 1";
$results = self::$mysql->query($sql);
if($results->length()){
foreach($this->fields as $field){
$field->setValue($results->{$field->name});
}
return true;
}
return false;
}
/**
* Inserts a record
*/
public function insert(){
// Set default arrays for insert values
$names = array();
$values = array();
// Loop through fields to perform all pre-insertion logic
foreach($this->fields as $field){
// Store array fields and values in arrays
array_push($names, $field->getName());
array_push($values, $field->getValue());
// If an auto-increment field has been set for some reason, reset it
$autoIncrement = false;
if($field->isAutoIncrement()){
$field->setValue(null);
$autoIncrement = $field;
}
}
// Prepare SQL query
$sql = "INSERT INTO `" . $this->table . "` (" . implode(",", $names) . ") ";
$sql .= "VALUES ('" . implode("','", $values) . "')";
// Execute query and store result
$result = self::$mysql->query($sql);
if($id = $result->id()){
// update object to reflect auto incrememnt value (if any)
if($autoIncrement) $autoIncrement->setValue($id);
// Insert successful
return true;
}
throw new MC2_Mysql_Model_Exception(self::$mysql->getError());
}
/**
* Saves a record (uptate)
*/
public function save(){
//NOTE: Mysql will not update if no data has changed
//TODO: Figure out a way to deal with this
// Prepare SQL query
$sql = "UPDATE `" . $this->table . "` SET ";
foreach($this->fields as $field){
// Store all but primary key into the query set
if(!$field->isPk()){
$name = $field->getName();
$value = $field->getValue();
$assignments[] = "`" . $name . "` = '" . $value . "'";
}
}
$sql .= implode(",", $assignments);
$sql .= " WHERE " . $this->primaryKey . " = '" . $this->fields[$this->primaryKey]->getValue() . "'";
// Execute query and store result
$result = self::$mysql->query($sql);
if($result->affectedRows() > 0){
// Insert successful
return true;
}
// Otherwise, find out why record wasn't inserted...
throw new MC2_Mysql_Model_Exception(self::$mysql->getError());
return false;
}
/**
* This provides a way to find records.
* @param $conditions (WHERE $conditions)
* @param $fieldsArray array of fields you would like returned
* @param $start record you would like results to start at
* @param $limit record limit
* @returns an array full of MC2_Mysql_Model Objects
*/
public function find($conditions, $fieldsArray=null, $start=null, $limit=null/*, $association=null*/){
$className = get_class($this);
$select_fields = is_array($fieldsArray) ? implode(",", $fieldsArray) : '*';
$start_limit = '';
if(!is_null($limit)){
$start_limit .= ' LIMIT ';
$start_limit .= !is_null($start) ? $start . ', ' : '';
$start_limit .= $limit;
}
$sql = "SELECT " . $select_fields . " FROM `" . $this->table . "` WHERE " . $conditions . $start_limit;
$results = self::$mysql->query($sql);
if($results->length()){
$returnArray = array();
for($results->first(); !$results->end(); $results->next()){
$resultArray = $results->getCurrentRecord();
$record = new $className;
$record->loadFromArray($resultArray, $fieldsArray);
array_push($returnArray, $record);
}
return $returnArray;
}
return false;
}
/**
* Loads a record into the object from an associative array
* Use keys as column names and values as values
*/
public function loadFromArray($array, $fieldsArray=array()){
if(!is_array($array)){
throw new MC2_Mysql_Model_Exception('Passed wrong parameter type to MC2_Mysql_Model::loadFromArray');
return false;
}
foreach($array as $key => $val){
$checkFieldsArray = true;
if(!is_null($fieldsArray)){
$checkFieldsArray = in_array($key, $fieldsArray);
}
if(array_key_exists($key, $this->fields) && $checkFieldsArray){
$this->fields[$key]->setValue($val);
}
}
return true;
}
/**
* Get a single value
*/
public function get($key){
return array_key_exists($key, $this->fields) ? stripslashes($this->fields[$key]->getValue()) : null;
}
/**
* Set a single value
*/
public function set($key, $value){
if(array_key_exists($key, $this->fields)){
$this->fields[$key]->setValue($value);
$this->saved = false;
return true;
}
return false;
}
public function __get($key){
return $this->get($key);
}
public function __set($key, $value){
$this->set($key, $value);
}
}
?>Code: Select all
<?php
class MC2_Mysql_Model_Field{
public function __construct($name, $type, $null, $key, $default, $extra){
$this->name = $name;
$this->type = $type;
$this->null = (strtoupper($null) == 'YES');
$this->setKey($key);
$this->setValue($default);
$this->extra = $extra;
}
public function setValue($value){
$this->value = $value;
}
protected function setKey($key){
$this->key = $key;
}
public function isValid(){
if(!$this->null && !$this->isAutoIncrement()){
if(is_null($value)) return false;
}
}
public function isPk(){
return strtoupper($this->key) == 'PRI';
}
public function isAutoIncrement(){
return $this->extra == 'auto_increment';
}
public function getName(){
return $this->name;
}
public function getValue(){
return $this->value;
}
}
?>