<?php/** * $header * @copyright Copyright 2007, Peter Goodman * @package valkyrie * @subpackage Database.activerecord * @author Peter Goodman */if(!defined('IN_VALKYRIE')) { exit;}/** * Class to handle table-level operations. This is a decorator class to the database * abstraction layer that gives a more intuitive interface to finding rows in the database. */class DatabaseFinder implements VK_Finder { /** * A reference to the current database abstraction layer. * @see Database */ protected $dba; /** * A reference to a table driver for this database. */ private $table; /** * A reference to a model definition for this table. * @see DatabaseModelDefinition */ private $definition; /** * An array of transactions currently going on. * @see Database::begin(), Database::end(), Database::commit(), Database::rollback() * @internal */ private $transactions = array(); /** * Constructor, bring in the database layer and model definition. * @param mixed $dba The database abstraction layer. * @param mixed $model The model definition for the table that we're working with. * @see Database, DatabaseModelDefinition */ public function __construct(DatabaseDriver $dba, DatabaseModelDefinition $definition) { $this->dba = $dba; $this->definition = $definition; // bring in the table driver for table-level operations $table_driver = $this->dba->layer . 'Table'; $this->table = new $table_driver($this->dba, $this->definition); } /** * Find a single row in the database by the value of its primary key. * @param mixed $primary_key The value of a row's primary key. * @return A new VK_DatabaseRecord */ public function find($primary_key = '') { return $this->findBy($this->definition->getPrimaryKey(), $primary_key); } /** * Find a single row in the database given a table column name and a corresponding value. * @param string $col The column name. * @param mixed $val The value corresponding to the column name. * @return A new VK_DatabaseRecord */ public function findBy($col = '', $val = '') { return $this->findWhere($col .'=?', array($val)); } /** * Find a single row in the database given conditions. * @param string $where Everything that goes into a SQL WHERE clause. * @param array $args Values to substitute into the SQL query for every question mark. * @return A new VK_DatabaseRecord */ public function findWhere($where = '', array $args = array()) { return $this->findByQuery($this->buildQuery('*', $where, '', 1), $args); } /** * Find the most recent entry into the db for this table. */ public function findLast() { return $this->findByQuery("SELECT * FROM ". $this->definition->getTable('name') ." GROUP BY ". $this->definition->getPrimaryKey() ." HAVING MAX(". $this->definition->getPrimaryKey() .") LIMIT 1"); } public function findLastWhere($table, $value) { return $this->findByQuery("SELECT * FROM ". $this->definition->getTable('name') ." WHERE ". $table ."=". $value ." GROUP BY ". $this->definition->getPrimaryKey() ." HAVING MAX(". $this->definition->getPrimaryKey() .") LIMIT 1"); } /** * Find a single row in the databse by a query. * @param string $stmt The query to execute. * @param array $args Values to substitute into the SQL query for every question mark. * @return A new VK_DatabaseRecord */ public function findByQuery($stmt = '', array $args = array()) { $record = $this->definition->getName() .'Record'; if(!class_exists($record, FALSE)) { $record = 'VK_DatabaseRecord'; } // add in any custom select things if($stmt instanceof VK_SelectQuery) { $this->definition->prepareSelect($stmt); $stmt = $stmt->getSql(); } $result = $this->dba->select($stmt, $args)->fetch(); $ret = NULL; if(!empty($result)) { $ret = new $record($result, $this->dba, $this->definition); } return $ret; } /** * Find all rows */ public function findAll($limit = '', $order = '') { if($this->definition->getPrimaryKey() && $this->definition->getPrimaryKey() != '') { $order = $this->definition->getPrimaryKey() .' DESC'; } return $this->findAllWhere('', array(), $order, $limit); } /** * Find multiple rows in the database given a column name and a value for it. * @param string $col The column name. * @param mixed $val The value corresponding to the column name. * @return A new VK_DatabaseRecordSet */ public function findAllBy($col = '', $val = '', $order = '', $limit = '') { return $this->findAllWhere($col .'=?', array($val), $order, $limit); } /** * Find multiple rows in the database given conditions. * @param string $where Everything that goes into a SQL WHERE clause. * @param array $args Values to substitute into the SQL query for every question mark. * @param string $order Everything that goes in the SQL ORDER BY clause * @param string $limit Everything that goes in the SQL LIMIT clause * @return A new VK_DatabaseRecordSet */ public function findAllWhere($where = '',$args = array(), $order = '', $limit = '') { return $this->findAllByQuery($this->buildQuery('*', $where, $order, $limit), $args); } /** * Find multiple rows in the databse by a query. * @param string $stmt The query to execute. * @param array $args Values to substitute into the SQL query for every question mark. * @return A new VK_DatabaseRecordSet */ public function findAllByQuery($stmt, array $args = array()) { // add in any custom select things if($stmt instanceof VK_SelectQuery) { $this->definition->prepareSelect($stmt); $stmt = $stmt->getSql(); } $record = $this->definition->getName() .'RecordSet'; if(!class_exists($record, FALSE)) { $record = 'VK_DatabaseRecordSet'; } $result = $this->dba->select($stmt, $args); $ret = NULL; if($result->valid()) { $ret = new $record($result, $this->dba, $this->definition); } return $ret; } /** * Find a single row in the databse given conditions and the columns that are to be selected. * @param string $select A comma separated list of the columns that are to be selected. * @param string $where Everything that goes into a SQL WHERE clause. * @param array $args Values to substitute into the SQL query for every question mark. * @param string $order Everything that goes in the SQL ORDER BY clause * @param string $limit Everything that goes in the SQL LIMIT clause * @return A new VK_DatabaseRecord */ public function selectWhere($select = '', $where = '', array $args = array(), $order = '') { return $this->findByQuery($this->buildQuery($select, $where, $order, 1), $args); } /** * Find multiple rows in the databse given conditions and the columns that are to be selected. * @param string $select A comma separated list of the columns that are to be selected. * @param string $where Everything that goes into a SQL WHERE clause. * @param array $args Values to substitute into the SQL query for every question mark. * @param string $order Everything that goes in the SQL ORDER BY clause * @param string $limit Everything that goes in the SQL LIMIT clause * @return A new VK_DatabaseRecordSet */ public function selectAllWhere($select = '', $where = '', array $args = array(), $order = '', $limit = '') { return $this->findAllByQuery($this->buildQuery($select, $where, $order, $limit), $args); } /** * Build a SQL query given all sorts of stuff. * @param string $select A comma separated list of the columns that are to be selected. * @param string $where Everything that goes into a SQL WHERE clause. * @param array $args Values to substitute into the SQL query for every question mark. * @param string $order Everything that goes in the SQL ORDER BY clause * @param string $limit Everything that goes in the SQL LIMIT clause * @return string A SQL query. * @internal */ private function buildQuery($select = '', $where = '', $order = '', $limit = '') { $query = new VK_SelectQuery($select, $this->definition->getTable('name'), $where); $query->addOrderBy($order, '') ->addLimit($limit); $this->definition->prepareSelect($query); return $query; } /** * Create the SQL table. */ public function create() { return $this->table->create(); } /** * Drop this SQL table. */ public function drop() { return $this->table->drop(); } /** * Optimize this SQL table. */ public function optimize() { return $this->table->optimize(); } /** * Create a new database record. * @return mixed A new VK_DatabaseRecord object. */ public function createRecord() { $record = $this->definition->getName() .'Record'; if(!class_exists($record, FALSE)) { $record = 'VK_DatabaseRecord'; } return new $record(array(), $this->dba, $this->definition); } /** * Insert a row into a database. * @param array $keys An array of column names of this table that will be used in the insert. * @param array $vals An array of corresponding values to the column names. * @return boolean */ public function insert(array $keys = array(), array $vals = array()) { // for convenience if(!empty($keys) && empty($vals)) { $keys = array_keys($keys); $vals = array_values($keys); } return $this->dba->update($this->dba->buildInsert($this->definition->getTable('name'), $keys, $vals), $vals); } /** * Insert multiple rows into a database. * @param array $keys An array of column names of this table that will be used in the insert. * @param array $vals A miltidimensional array of corresponding values to the column names. * @return boolean */ public function insertMany(array $keys = array(), array $vals = array()) { if(!$this->definition->extended_inserts) { foreach($vals as $val_set) { $this->insertOne($keys, $vals); } } else { $sql = $this->dba->buildInsert($this->definition->getTable('name'), $keys, $vals[0]); $sql .= str_repeat(',(' . substr(str_repeat('?,', count($keys)), 0, -1) . ')', count($vals) - 1); $this->dba->update($sql, array_flatten($vals)); } } /** * Begin a new SQL Transaction. If the transaction exists, return it. The return part of this is useful * for if you are modifying the same transaction over multiple functions. * @param string $id The ID of this transaction. * @param string $type The type of this transaction. (deferred, immediate, exclusive) * @param string $on_conflict The conflict algorithm to use. Defaults to ROLLBACK */ final public function beginTransaction($id = '', $on_conflict = 'ROLLBACK') { if(!isset($this->transactions[$id])) { $class = $this->dba->layer .'QueryTransaction'; $this->transactions[$id] = new $class($this->dba, $this->definition, $id, $on_conflict); } return $this->transactions[$id]; }}?>