DBO
/**
* The master of the database objects.
*/
class DBO {
// Some tables name their primary keys something else
public $pk = 'id';
// The name of the database table itself
protected $table;
// The escaped name of the database table
private $table_mysql;
// An associative array of the table fields to hold the values
protected $fields = array('id' => null);
// An array with the keys of $fields escaped, for internal processing only
private $fields_mysql;
// An array describing the type and size constraint of each field
protected $fields_constraints = array();
// An array of the fields updated in a given instantiation
protected $updated = array();
// A flag for whether to call insert() or update() on save()
protected $inserted = false;
/**
* If the field exists, return the current value of the field,
* otherwise, return false for a non-existant field.
*/
public function get($var) {
if (array_key_exists($var, $this->fields)) {
return $this->fields[$var];
} else {
return null;
}
}
/**
* When the field exists, update the value and mark its place in the
* updated array so the update script knows what to work with.
*/
public function set($field, $value) {
if (array_key_exists($field, $this->fields)) {
if ($this->fields[$field] != $value) {
// Throws an exception
if ($this->meetsFieldConstraints($field, $value)) {
$this->fields[$field] = $value;
$this->updated[$field] = true;
} else {
return false;
}
}
return true;
} else {
return false;
}
}
/**
* Check the constraints of the field to determine whether or
* not the supplied value meets the requirements. Either returns
* true having made it through the assertions, or passes the
* Exception thrown from the failed assertion off to the caller.
*/
protected function meetsFieldConstraints($field, $value) {
// If not constraint defined, then it does not fail anything
if (isset($this->fields_constraints[$field])) {
// First, check the type
if (isset($this->fields_constraints[$field]['type'])) {
Utilities::assertDataType($this->fields_constraints[$field]['type'], $value);
}
// Then, check the size
if (isset($this->fields_constraints[$field]['size'])) {
Utilities::assertDataSize($this->fields_constraints[$field]['size'], $value);
}
}
return true;
}
/**
* A convenience method to allow the setting of multiple fields
* at once via an associative array.
*/
public function setAssoc($array) {
if (is_array($array)) {
foreach ($array as $field => $value) {
$this->set($field, $value);
}
} else {
return false;
}
}
/**
* save() checks the inserted flag to decide whether to insert
* a new record or update an existing record.
*/
public function save() {
if ($this->inserted) {
return $this->update();
} else {
return $this->insert();
}
}
/**
* Delete a record based on its primary key
*/
public function delete() {
$statement = $this->database->prepare(
'DELETE FROM ' . $this->table_mysql . ' WHERE ' . $this->fields_mysql[$this->pk] . ' = ?'
);
if ($statement->execute(array($this->fields[$this->pk]))) {
$this->inserted = false;
return true;
} else {
return false;
}
}
/**
* Set the updated fields of the record to their new values
*/
protected function update() {
if (!in_array(true, $this->updated)) {
return true;
}
$qry = 'UPDATE ' . $this->table_mysql . ' SET ';
$f = false;
foreach ($this->updated as $field => $value) {
if (!$f) {
$f = true;
} else {
$qry .= ', ';
}
$qry .= $this->fields_mysql[$field] . ' = ? ';
}
$qry .= ' WHERE ' . $this->fields_mysql[$this->pk] . ' = ? ';
$statement = $this->database->prepare($qry);
// Get the updated field values, and add the primary key for the WHERE clause
$parameters = array_push(
array_intersect_key($this->fields, $this->updated),
$this->fields[$this->pk]
);
if ($statement->execute($parameters)) {
return true;
} else {
return false;
}
}
/**
* Insert the current values into a new database record
*/
public function insert() {
$qry = 'INSERT INTO ' . $this->table_mysql . ' ('
. implode(', ', $this->fields_mysql)
. ') VALUES (' . str_repeat('?,', count($this->fields) - 1) . '?)';
$statement = $this->database->prepare($qry);
if ($statement->execute($this->fields)) {
$this->inserted = true;
$this->fields[$this->pk] = mysql_insert_id();
return true;
} else {
$GLOBALS['messenger']->addError($this->database->errorInfo());
return false;
}
}
/**
* Alias to DBO::select($pk, $id);
*/
public function load($id) {
$fields = array($this->pk);
$values = array($id);
return $this->select($fields, $values);
}
/**
* Select a record based on an array of fields to match against an array of values
*/
public function select($fields, $values) {
global $config;
if (is_array($fields) && is_array($values)) {
$qry = 'SELECT ('
. implode(', ', $this->fields_mysql)
. ') FROM ' . $this->table_mysql . ' WHERE ';
$f = false;
foreach ($fields as $i => $field) {
if (isset($this->fields_mysql[$field])) {
if (!$f) {
$f = true;
} else {
$qry .= ' AND ';
}
$qry .= $this->fields_mysql[$field] . ' = ? ';
}
}
$statement = $this->database->prepare($qry);
if ($statement->execute($values)) {
if ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$this->fields = $row;
$this->inserted = true;
return true;
}
} else {
$error = $statement->errorInfo();
$GLOBALS['messenger']->add($error[2], 'error');
}
}
return false;
}
/**
* Since PDO does not escape table and field identifiers,
* this method creates a private, escaped and quoted copy
* of the table and field identifiers for use in the SQL
*/
protected function escapeIdentifiers() {
$this->table_mysql = $this->escapeTable($this->table);
foreach ($this->fields as $field => $value) {
$this->fields_mysql[$field] = $this->escapeIdentifier($field);
}
}
/**
* Table names can have different naming restrictions, and
* in MySQL, table names cannot end in a space or contain
* the characters "/", "\", or "."
*/
protected function escapeTable($string) {
// Table names in MySQL have slightly different naming requirements
$temp = preg_replace('/[\/\\.]/D', '', $string);
$temp = str_replace('`', '``', $temp);
return '`' . trim($temp) . '`';
}
/**
* Field names simply have all existing backticks escaped
*/
protected function escapeIdentifier($string) {
return '`' . str_replace('`', '``', $string) . '`';
}
/**
* When the caller specifies an ID, call DBO::load() to load the record
*/
public function __construct($id = null) {
global $controller;
$this->database = $controller->getDatabaseHandle();
if (!is_null($id)) {
$this->load($id);
}
$this->escapeIdentifiers();
}
}