PHP Classes
elePHPant
Icontem

File: classes/database/db-query.php

Recommend this page to a friend!
  Classes of Gonzalo Chumillas  >  Ses Query  >  classes/database/db-query.php  >  Download  
File: classes/database/db-query.php
Role: Class source
Content type: text/plain
Description: DBQuery class
Class: Ses Query
Manipulate records retrieved with select queries
Author: By
Last change:
Date: 3 years ago
Size: 27,179 bytes
 

Contents

Class file image Download
<?php
/**
 * This file contains the DBQuery class.
 * 
 * @author Gonzalo Chumillas <gonzalo@soloproyectos.com>
 * @package database
 */

require_once dirname(__DIR__) . "/library/util.php";
require_once dirname(__DIR__) . "/database/database.php";
require_once dirname(__DIR__) . "/database/db-column.php";
require_once dirname(__DIR__) . "/database/db-table.php";
require_once dirname(__DIR__) . "/database/db-parser.php";
require_once dirname(__DIR__) . "/database/exceptions/db-exception.php";
require_once dirname(__DIR__) . "/database/exceptions/ambiguous-column-db-exception.php";
require_once dirname(__DIR__) . "/database/exceptions/column-not-found-db-exception.php";
require_once dirname(__DIR__) . "/database/exceptions/query-not-editable-db-exception.php";

/**
 * class DBQuery
 * 
 * This class lets you to work with SQL statements in a natural way.
 * 
 * @package database
 */
class DBQuery implements Countable, Iterator, ArrayAccess {   
    /**
     * Database connection.
     * @var Database
     */
    private $db;
    
    /**
     * Tables.
     * @var array[DBTable]
     */
    private $tables;
    
    /**
     * Columns.
     * @var array[DBColumn]
     */
    private $columns;
    
    /**
     * Result set.
     * @var array[string]
     */
    private $rows;
    
    /**
     * Main table.
     * @var DBTable
     */
    private $main_table;
    
    /**
     * Is the mysql sentence a command?
     * @var boolean
     */
    private $is_command;
    
    /**
     * Is the mysql sentence editable?
     * @var boolean
     */
    private $is_editable;
    
    /**
     * Gets the number of affected rows.
     * @var int
     */
    private $affected_rows;
    
    /**
     * SQL Sentence.
     * @var string
     */
    private $sql;
    
    /**
     * Debug mode.
     * @var boolean
     */
    private $debug_mode = FALSE;
    
    /**
     * Creates an instance of DBQuery.
     * @param Database $db
     * @param string $sql
     * @param array $args = array()
     */
    public function __construct($db, $sql, $args = array()) {
        $this->db = $db;
        $this->tables = array();
        $this->columns = array();
        $this->rows = array();
        $this->is_command = FALSE;
        $this->is_editable = FALSE;
        $this->affected_rows = 0;
        
        if ($db instanceof DBQuery && is_array($sql)) {
            // internal use only
            $query = $db;
            $rows = $sql;
            $this->rows = $rows;
            $this->db = $query->db;
            $this->tables = $query->tables;
            $this->columns = $query->columns;
            $this->is_command = $query->is_command;
            $this->is_editable = $query->is_editable;
            $this->affected_rows = $query->affected_rows;
        } else {
            $conn = $this->db->getConnection();
            $this->sql = $this->replaceArgs($this->removeComments($sql), $args);
            $result = $this->db->execute($this->sql);
            $this->is_command = !is_object($result);
            
            if (!$this->is_command) {
                $result = mysqli_query($conn, $this->sql);
                $len = mysqli_num_fields($result);
                
                for ($i = 0; $i < $len; $i++) {
                    // gets field info
                    $info = mysqli_fetch_field_direct($result, $i);
                    $table = $info->table;
                    $name = $info->orgname;
                    $alias = $info->name;
                    $flags = $info->flags;
                    $primary_key = (2 & $flags) > 0;
                    
                    // registers a new column
                    $column = $this->registerColumn($table, $name);
                    $column->setPrimaryKey($primary_key);
                    $column->registerAlias($alias);
                    $column->setAccessible(TRUE);
                }
                
                // gets rows
                while ($row = mysqli_fetch_row($result)) {
                    array_push($this->rows, $row);
                }
                
                mysqli_free_result($result);
                
                // is the query editable?
                // TODO: Si la clave primaria es nula, quizás no debería ser editable
                $p = new DBParser($this, $sql);
                try {
                    $this->is_editable = $p->parse();
                } catch(ParserException $e) {
                    $this->is_editable = FALSE;
                }
            } else {
                $this->affected_rows = mysqli_affected_rows($conn);
            }
        }
    }
    
    /**
     * Gets database connection.
     * @return Database
     */
    public function getDatabase() {
        return $this->db;
    }
    
    /**
     * Magic '__get' method.
     * Calls the 'get' method when trying to access to an inaccessible property.
     * @param string $name
     * @return mixed
     */
    public function __get($name) {
        return $this->get($name);
    }
    
    /**
     * Magic '__set' method.
     * Calls the 'set' method when trying to set up an inaccessible property.
     * @param string $name
     * @return mixed
     */
    public function __set($name, $value) {
        $this->set($name, $value);
    }
    
    /**
     * Magic '__isset' method
     * Checks whether a column exists.
     * @param string $name
     */
    public function __isset($name) {
        try {
            $column = $this->searchColumn($name);
        } catch (ColumnNotFoundDBException $e) {
            return FALSE;
        }
        
        return TRUE;
    }
    
    /**
     * Magic '__unset' method
     * Unset a column.
     * @param string $name
     */
    public function __unset($name) {
        $column = $this->searchColumn($name);
        $column->setAccessible(FALSE);
    }
    
    /**
     * Gets the value for a specified column name.
     * @param string $name
     * @return mixed
     */
    public function get($name) {
        $column = $this->searchColumn($name);
        return $column->getValue();
    }
    
    /**
     * Sets the value for a specified column name.
     * @param string $name
     * @param mixed $value
     * @throws QueryNotEditableDBException if the column is not editable.
     */
    public function set($name, $value) {
        $column = $this->searchColumn($name);
        
        if (!$this->is_editable) {
            throw new QueryNotEditableDBException();
        }
        
        $column->setValue($value);
    }
    
    /**
     * Gets debug mode status.
     * @return boolean
     */
    public function isDebugMode() {
        return $this->debug_mode;
    }
    
    /**
     * Sets debug mode status.
     * @param boolean $value
     */
    public function setDebugMode($value) {
        $this->debug_mode = $value;
    }
    
    /**
     * Gets accessible columns.
     * @return array[DBColumn]
     */
    public function getColumns() {
        $ret = array();
        
        foreach ($this->columns as $column) {
            if ($column->isAccessible()) {
                array_push($ret, $column);
            }
        }
        
        return $ret;
    }
    
    /**
     * Gets tables.
     * @return array[DBTable]
     */
    public function getTables() {
        return $this->tables;
    }
    
    /**
     * Gets main table.
     * @return DBTable
     */
    public function getMainTable() {
        return $this->main_table;
    }
    
    /**
     * Sets main table.
     * @param DBTable $table
     */
    public function setMainTable($table) {
        $this->main_table = $table;
    }
    
    /**
     * Has column value?
     * @param DBColumn $column
     * @return boolean
     */
    public function hasOriginalValue($column) {
        $ret = FALSE;
        $row = current($this->rows);
        
        if ($row !== FALSE) {
            $index = array_search($column, $this->columns, TRUE);
            $ret = array_key_exists($index, $row);
        }
        
        return $ret;
    }
    
    /**
     * Gets column value.
     * @param DBColumn $column
     * @return string
     */
    public function getOriginalValue($column) {
        $ret = "";
        $row = current($this->rows);
        
        if ($row !== FALSE) {
            $index = array_search($column, $this->columns, TRUE);
            
            if (array_key_exists($index, $row)) {
                $ret = $row[$index];
            }
        }
        
        return $ret;
    }
    
    /**
     * Sets column value.
     * @param DBColumn $column
     * @param string $value
     */
    public function setOriginalValue($column, $value) {
        $index = array_search($column, $this->columns, TRUE);
        $pos = key($this->rows);
        
        if ($pos !== NULL && array_key_exists($index, $this->rows[$pos])) {
            $this->rows[$pos][$index] = $value;
        }
    }
    
    /**
     * Gets an slice from the query.
     * @param integer $offset
     * @param integer $length = NULL
     * @throws DBException if the column is not a result set.
     * @return DBQuery
     */
    public function getSlice($offset, $length = NULL) {
        if ($this->isCommand()) {
            throw new DBException("The query is not a resultset");
        }
        
        $rows = array_slice($this->rows, $offset, $length);
        return new DBQuery($this, $rows);
    }
    
    /**
     * Registers a column.
     * @param string $table
     * @param string $name
     * @throws DBException if the class can not determine the table of the column.
     * @return DBColumn
     */
    public function registerColumn($table, $name) {
        try {
            $ret = $this->searchColumn("`$table`.`$name`", FALSE);
        } catch (ColumnNotFoundDBException $e) {
            $ret = new DBColumn($this, $table, $name);
            array_push($this->columns, $ret);
        }
        
        return $ret;
    }
    
    /**
     * Registers a table.
     * @param string $alias Table alias.
     * @param boolean $move_top Moves = FALSE the table to the top.
     * @return DBTable
     */
    public function registerTable($alias, $move_top = FALSE) {
        $ret = $this->searchTable($alias);
        
        // creates a new table
        if ($ret == NULL) {
            $ret = new DBTable($this, $alias);
            array_push($this->tables, $ret);
        }
        
        // moves table to the beggining
        if ($move_top) {
            $offset = array_search($ret, $this->tables, TRUE);
            $slice = array_splice($this->tables, $offset, 1);
            array_splice($this->tables, 0, 0, $slice);
        }
        
        return $ret;
    }
    
    /**
     * Checks whether the query is a command.
     * @return boolean
     */
    public function isCommand() {
        return $this->is_command;
    }
    
    /**
     * Checks whether the query is editable.
     * @return boolean
     */
    public function isEditable() {
        return $this->is_editable;
    }
    
    /**
     * Deletes the current record.
     * @throws QueryNotEditableDBException if the query is not editable.
     */
    public function delete($recursive = FALSE) {
        if (!$this->is_editable) {
            throw new QueryNotEditableDBException();
        }
        
        if ($recursive) {
            $tables = $this->tables;
        } else {
            $tables = array($this->main_table);
        }
        
        // delete records
        foreach ($tables as $table) {
            $pk = $table->getPrimaryKey();
            if ($pk == NULL) {
                continue;
            }
            
            $table_name = $table->getName();
            $pk_name = $pk->getName();
            $pk_value = $pk->getOriginalValue();
            
            if (!util::isEmpty($pk_value)) {
                $sql =
                    "delete" .
                    " from " . $this->db->quotename($table_name) .
                    " where " . $this->db->quotename($pk_name) . " = " . $this->db->quote($pk_value);
                
                if ($this->debug_mode) {
                    echo "$sql\n";
                }
                
                $this->db->execute($sql);
            }
        }
    }
    
    /**
     * Refreshes the query result set.
     * @throws QueryNotEditableDBException if the query is not editable.
     * @throws DBException if the record were not found.
     */
    public function refresh() {
        if (!$this->is_editable) {
            throw new QueryNotEditableDBException();
        }
        
        // builds the select query
        $pk = $this->main_table->getPrimaryKey();
        $pk_name = $pk->getFullName();  // TODO: qué pasa si la clave primaria es nula?
        $pk_value = $pk->getOriginalValue();
        $t = new Tokenizer($this->sql);
        $condition = "";
        
        if ($t->match("where\s+", $matches, Tokenizer::SEARCH_ANYWHERE)) {
            $condition = "and $pk_name = " . $this->db->quote($pk_value);
        } else {
            $condition = "where $pk_name = " . $this->db->quote($pk_value);
        }
        
        if ($t->match("(group|having|order|limit|union)\s+", $matches, Tokenizer::SEARCH_ANYWHERE|Tokenizer::OFFSET_CAPTURE)) {
            $offset = $matches[0][1];
        } else {
            $offset = strlen($this->sql);
        }
        
        $sql = util::concat("\n", substr($this->sql, 0, $offset), $condition, substr($this->sql, $offset));
        // ..
        
        // gets the row and fills the columns
        if ($this->debug_mode) {
            echo "$sql\n";
        }
        
        $result = $this->db->execute($sql);
        $row = mysqli_fetch_row($result);
        
        if ($row) {
            foreach ($row as $i => $value) {
                $column = $this->columns[$i];
                $column->delValue();
                $column->setOriginalValue($value);
            }
        }
        // ..
    }
    
    /**
     * Saves the changes.
     * @param boolean $insert_mode Inserts a new record on empty result set.
     * @throws QueryNotEditableDBException if the query is not editable.
     * @throws DBException if the primary key of a table is not present and the table must be saved.
     */
    public function save($insert_mode = FALSE) {
        if (!$this->is_editable) {
            throw new QueryNotEditableDBException();
        }
        
        if (!$insert_mode && count($this->rows) == 0) {
            return;
        }
        
        foreach ($this->tables as $table) {
            $table_join_type = $table->getJoinType();
            $table_has_changed = $table->hasChanged();
            $table_pk = $table->getPrimaryKey();
            
            if ($table_pk == NULL && $table_join_type == DBTable::INNER_JOIN) {
                throw new DBException("The primary key of the table `" . $table->getAlias() . "` must be present");
            }
            
            if ($table_pk != null) {
                $pk_value = $table_pk->getOriginalValue();
                
                if ($table_has_changed && !util::isEmpty($pk_value)) {
                    // UPDATE
                    $this->registerDateColumns($table);
                    $sql = $this->getUpdateSentence($table);
                    
                    if ($this->debug_mode) {
                        echo "$sql\n";
                    }
                    
                    $this->db->execute($sql);
                } else
                if ($table_has_changed || util::isEmpty($pk_value) && $table_join_type == DBTable::INNER_JOIN) {
                    // INSERT
                    $this->registerDateColumns($table);
                    $sql = $this->getInsertSentence($table);
                    
                    if ($this->debug_mode) {
                        echo "$sql\n";
                    }
                    
                    $this->db->execute($sql);
                    $table_pk->setValue($this->db->getInsertId());
                }
            }
        }
        
        foreach ($this->columns as $column) {
            if ($column->hasChanged()) {
                $column->setOriginalValue($column->getValue());
            }
            
            $column->delValue();
        }
    }
    
    public function insert() {
        $this->save(TRUE);
    }
    
    /**
     * Removes comments from the sql sentence.
     * @param string $sql
     * @return string
     */
    private function removeComments($sql) {
        return trim(preg_replace(array("!/\*.*?\*/!s", "/--.*\n?/", "/#.*\n?/"), array(NULL, NULL, NULL), $sql));
    }
    
    /**
     * Replaces arguments.
     * @param string $sql
     * @param array $args = array()
     * @throws a DBException if a parameter were not found.
     */
    private function replaceArgs($sql, $args = array()) {
        if (preg_match_all("/%(\w+)/", $sql, $matches)) {
            $params = $matches[1];
            $items = array();
            $values = array();
            
            foreach ($params as $param) {
                $index = is_numeric($param)? $param - 1 : $param;
                $param_name = "%$param";
                
                if (!isset($args[$index])) {
                    throw new DBException("The parameter `$param_name` were not found");
                }
                
                array_push($items, $param_name);
                array_push($values, $this->db->quote($args[$index]));
            }
            
            $sql = str_replace($items, $values, $sql);
        }
        
        return $sql;
    }
    
    /**
     * @param DBTable $table
     */
    private function registerDateColumns($table) {
        $date_columns = array();
        $created_on = $table->getColumnByName("created_on");
        $updated_on = $table->getColumnByName("updated_on");
        
        if ($created_on == NULL) {
            array_push($date_columns, $this->db->quote("created_on"));
        }
        
        if ($updated_on == NULL) {
            array_push($date_columns, $this->db->quote("updated_on"));
        }
        
        if (count($date_columns) > 0) {
            $sql = "show columns from " . $this->db->quotename($table->getName()) . " where Field in (" . implode(", ", $date_columns). ")";
            $rows = $this->db->query($sql);
            
            foreach ($rows as $row) {
                $this->registerColumn($table->getAlias(), $row->Field);
            }
        }
    }
    
    /**
     * Gets current date in MySQL format.
     * @return string
     */
    private function getCurrentDate() {
        return date("Y-m-d H:i:s", time());
    }
    
    /**
     * Gets update sentence.
     * @param DBTable $table
     * @return string
     */
    private function getUpdateSentence($table) {
        $this->getCurrentDate();
        $table_name = $table->getName();
        $table_columns = $table->getColumns();
        $table_pk = $table->getPrimaryKey();
        $pk_name = $table_pk->getName();
        $pk_value = $table_pk->getOriginalValue();
        $column_list = "";

        foreach ($table_columns as $column) {
            $column_name = $column->getName();
            
            if ($column_name == "updated_on" && !$column->hasChanged()) {
                $column->setValue($this->getCurrentDate());
            }
            
            if ($column->hasChanged()) {
                $column_value = $column->getValue();
                $column_list = util::concat(", ", $column_list, $this->db->quotename($column_name) . " = " . $this->db->quote($column_value));
            }
        }
        
        return "update " . $this->db->quotename($table_name) . " set $column_list where " . $this->db->quotename($pk_name) . " = " . $this->db->quote($pk_value);
    }
    
    /**
     * Gets insert sentence.
     * @param DBTable $table
     * @return string
     */
    private function getInsertSentence($table) {
        $now = date("Y-m-d H:i:s", time());
        $table_name = $table->getName();
        $table_columns = $table->getColumns();
        $table_pk = $table->getPrimaryKey();
        $column_list = "";
        $value_list = "";
        
        foreach ($table_columns as $column) {
            $column_name = $column->getName();
            
            if (($column_name == "created_on" || $column_name == "updated_on") && !$column->hasChanged()) {
                $column->setValue($this->getCurrentDate());
            }
            
            if ($column->hasChanged() || $column->hasDefaultValue()) {
                $column_value = $column->hasChanged()? $column->getValue() : $column->getDefaultValue();
                $column_list = util::concat(", ", $column_list, $this->db->quotename($column_name));
                $value_list = util::concat(", ", $value_list, $this->db->quote($column_value));
            }
        }
        
        return "insert into " . $this->db->quotename($table_name) . "($column_list) values($value_list)";
    }
    
    /**
     * Searchs a table by alias.
     * Return NULL if not found.
     * @param string $name
     * @return integer
     */
    private function searchTable($name) {
        $ret = NULL;
        
        foreach ($this->tables as $table) {
            $table_alias = $table->getAlias();
            
            if ($table_alias == $name) {
                $ret = $table;
                break;
            }
        }
        
        return $ret;
    }
    
    /**
     * Searchs a column by name.
     * Throws an exception when not found
     * @param integer|string $index_or_column Index or 'table.name'
     * @param string $accessible_filter = TRUE Searches only the accessible columns
     * @throws AmbiguousColumnDBException if the column name is ambiguous.
     * @throws ColumnNotFoundDBException if the column were not found.
     * @return integer
     */
    private function searchColumn($index_or_column, $accessible_filter = TRUE) {
        $ret = NULL;
        $table = NULL;
        $column = NULL;
        $index = -1;
        
        // splits name into table and column
        if (is_numeric($index_or_column)) {
            $index = $index_or_column;
        } else
        if (preg_match("/^(`?(\w*)`?\s*\.\s*)?`?(\w+)`?$/", $index_or_column, $matches)) {
            $table = trim($matches[2]);
            $column = trim($matches[3]);
        }
        
        // searches column by index
        if (isset($this->columns[$index])) {
            $ret = $this->columns[$index];
        } else
        // searches by alias or name
        if (util::isEmpty($table)) {
            // searches by alias
            foreach ($this->columns as $col) {
                if ((!$accessible_filter || $col->isAccessible()) && $col->isAlias($column)) {
                    if ($ret != NULL) {
                        throw new AmbiguousColumnDBException($column);
                    }
                    
                    $ret = $col;
                }
            }
            
            if ($ret == NULL) {
                // searches by name
                foreach ($this->columns as $col) {
                    $colname = $col->getName();
                    
                    if ((!$accessible_filter || $col->isAccessible()) && $colname == $column) {
                        if ($ret != NULL) {
                            throw new AmbiguousColumnDBException($column);
                        }
                        
                        $ret = $col;
                    }
                }
            }
        } else
        // searches by table and column
        {
            foreach ($this->columns as $col) {
                if ($col->isComputed()) {
                    continue;
                }
                
                $colname = $col->getName();
                $coltable = $col->getTable();
                $colalias = $coltable? $coltable->getAlias() : "";
                
                if ((!$accessible_filter || $col->isAccessible()) && $colalias == $table && $colname == $column) {
                    if ($ret != NULL) {
                        throw new AmbiguousColumnDBException($column);
                    }
                    
                    $ret = $col;
                }
            }
        }
        
        if ($ret == NULL) {
            throw new ColumnNotFoundDBException($column);
        }
        
        return $ret;
    }
    
    /**
     * Resets columns.
     */
    private function reset() {
        foreach ($this->columns as $column) {
            $column->delValue();
        }
    }
    
    /***************************
     * Iterator implementation *
     ***************************/

    /**
     * Gets the current record.
     * @return DBQuery
     */
    public function current() {
        $ret = FALSE;
        $row = current($this->rows);
        
        if ($row !== FALSE) {
            $ret = $this;
        }
        
        return $ret;
    }

    /**
     * Moves forward to next record.
     * @return DBQuery
     */
    public function next() {
        $ret = FALSE;
        $row = next($this->rows);
        
        if ($row !== FALSE) {
            $this->reset();
            $ret = $this;
        }
        
        return $ret;
    }

    /**
     * Gets the internal pointer.
     * @return integer
     */
    public function key() {
        return key($this->rows);
    }

    /**
     * Rewinds the internal pointer.
     */
    public function rewind() {
        reset($this->rows);
    }

    /**
     * Check if current index is valid.
     * @return bool
     */
    public function valid() {
        return key($this->rows) !== NULL;
    }
    
    /******************************
     * ArrayAccess implementation *
     ******************************/

    /**
     * Whether or not an offset exists.
     * @param string $name
     * @return boolean
     */
    public function offsetExists($name) {
        try {
            $column = $this->searchColumn($name);
        } catch (ColumnNotFoundDBException $e) {
            return FALSE;
        }
        
        return TRUE;
    }

    /**
     * Get the value at specified column name.
     * @param string $name
     * @return mixed
     */
    public function offsetGet($name) {
        return $this->get($name);
    }

    /**
     * Assign a value to the specified column name.
     * @param string $name
     * @param mixed $value
     */
    public function offsetSet($name, $value) {
        $this->set($name, $value);
    }

    /**
     * Unset an offset.
     * @param string $name
     */
    public function offsetUnset($name) {
        $column = $this->searchColumn($name);
        $column->setAccessible(FALSE);
    }
    
    /****************************
     * Countable implementation *
     ****************************/
     
     /**
      * Gets the number of records or affected rows.
      * @return integer
      */
    public function count() {
        return $this->isCommand()? $this->affected_rows: count($this->rows);
    }
}