Location: PHPKode > projects > Phpwebpad > phpwebpad/bin/orm/Database.php
<?
/** -------------------------------------------------------------------------------------*
* Version: 1.0                                                                           *
* License: http://phpwebpad.hafij.com @copyright from 2010                               *
* ---------------------------------------------------------------------------------------*
* DEVELOPED BY                                                                           *
* Mohammad Hafijur Rahman (Badal)                                                        *
* hide@address.com, hide@address.com                                             *
* ------------------------------------------------------------------------------------ **/

/**
* The heart of the orm.
* This is the innermost layer of the mysql database.
*/
class Database {
  private static $instance = null;
  private static $db = null;
  private static $createTable = false;
  private static $onlyOnce = true;
  private static $queryLog = array();
  private static $inTransaction = false;

  /**
  * Singleton pattern. We always take a single instance of the database.
  * If we are already connected to the database we dont try to connect
  * again.
  * @param string $host
  * @param string $db
  * @param string $username
  * @param string $pass
  * @return Database $database
  */
  public static function getInstance($host, $db, $username, $pass) {
    if(empty($host) || empty($db) || empty($username))
      throw new Exception('Missing information to connect to database.');
    
    if(self::$instance == null) {
      self::$instance = new Database($host, $db, $username, $pass);
    }
    self::$db = $db;
    return self::$instance;
  }

  /**
  * Private contstructor.
  * @param string $host
  * @param string $db
  * @param string $username
  * @param string $pass
  */
  private function __construct($host, $db, $username, $pass){
    try {
      mysql_connect($host, $username, $pass);
      mysql_select_db($db);
    } catch(Exception $e) {
      throw $e;
    }
  }

  /**
  * Responsible for creating table from a table definition.
  * @param TableDefinition $table
  * @exception if dont have permission to create table.
  * @return void.
  */
  public static function createTable(TableDefinition $table) {
    if(!self::getCreateTableMode())
      throw new Exception('Dont have enough privilage to create table.');

    if(!self::isTableExist($table->getTableName())) {
      $sql = sprintf('CREATE TABLE %s(%s INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(%s))',
                      self::wrapName($table->getTableName()),
                      self::wrapName($table->getPrimaryKeyName()),
                      self::wrapName($table->getPrimaryKeyName())
             );
      
      self::query($sql);
      self::addColumns($table);
      self::addUniqueKeys($table);
      //self::addTriggers($table);
      
    } else if(!self::$onlyOnce) {
      self::addColumns($table);
      self::addUniqueKeys($table);
      //self::addTriggers($table);
    }
  }

  /**
  * private function. Can only be used by createTable
  * Responsible for creating table's column from a table definition.
  * @param TableDefinition $table
  */
  private static function addColumns(TableDefinition $table) {
    $table_name = $table->getTableName();
    $columns = $table->getColumns();

    foreach($columns as $column) {
      if(!self::isColumnExist($table_name, $column['name'])){
        // Now we will check if the column type is nonNull or can
        // except null.        
        $nonNull = $column['non_null'];
        if($nonNull && $column['default'] === null) {
          // Yes the column is nonNull and has default value null.
          // so we dont add default value for this column.
          $sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s',
                        self::wrapName($table_name),
                        self::wrapName($column['name']),
                        $column['type']
                 );
        } else {
          // Yes the column can be nonNull and can not except null.
          // Or the column is not nonNull and in that case we dont
          // care if the default value is null or not null.
          // 
          // Now some time we dont want to wrap the value for default.
          // like for TIMESTAMP colum type if we want the default value
          // CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP then we should
          // not wrap the value.
          $wrap_default = $column['wrap_default'];
          $default = $column['default'];
          if($wrap_default) {
            $default = self::wrapValue($default);
          }

          $sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s DEFAULT %s',
                        self::wrapName($table_name),
                        self::wrapName($column['name']),
                        $column['type'],
                        $default
                 );
        }
        
        self::query($sql);
      }
    }

    $columns[] = array('name' => $table->getPrimaryKeyName());
    $cols = self::getAllColumnNames($table_name);
    foreach($cols as $c) {
      $flag = true;
      foreach($columns as $column) {
        if($c == $column['name']) {
          $flag = false;
          break;
        }
      }

      if($flag) {
        $sql = sprintf('ALTER TABLE %s DROP COLUMN %s',
                        self::wrapName($table_name),
                        self::wrapName($c)
               );
        self::query($sql);
      }
    }
  }

  /**
  * private function. Can only be used by createTable
  * Responsible for creating unique key constraints.
  * @param TableDefinition $table
  */
  private static function addUniqueKeys(TableDefinition $table) {
    $table_name = $table->getTableName();
    $uniques = $table->getUniqueKeys();
    foreach($uniques as $u) {
      $name = $u['name'];
      $cols = $u['cols'];
      if(!self::isConstraintExist($table_name, $name)) {
        $columns = "";
        foreach($cols as $c) {
          $columns = $columns. self::wrapName($c) . ", ";
        }
        $columns = rtrim($columns, ", ");
        if(!empty($columns)) {
          $sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)',
                          self::wrapName($table_name),
                          self::wrapName($name),
                          $columns
                 );
          self::query($sql);
        }
      }
    }
    $un = self::getAllConstraintNames($table_name);
    foreach($un as $u) {
      $flag = true;
      foreach($uniques as $uniq){
        if($u == $uniq['name']) {
          $flag = false;
          break;
        }
      }
      if($flag && $u != 'PRIMARY') {
        $sql = sprintf('ALTER TABLE %s DROP INDEX %s',
                        self::wrapName($table_name),
                        self::wrapName($u)
               );
        self::query($sql);
      }
    }
  }

  /**
   * Not emplemented yet.
   * @param TableDefinition $table
   * @return void
   */
  private function addTriggers(TableDefinition $table) {
    return;
    $table_name = $table->getTableName();
    $triggers = $table->getTriggers();
    foreach($triggers as $t) {
      $name = $t['name'];
      $time = $t['before'] ? 'BEFORE' : 'AFTER';
      $event = $t['event'];
      $sql = $t['sql'];
    }
  }

  /**
  * runs mysql_query and add the query to the query log.
  * @param string $sql
  * @exception if the given query is empty.
  * @return resultset array
  */
  public static function query($sql) {
    if(empty($sql)) throw Exception('Query can not be empty');
    self::$queryLog[] = $sql;
    $result =  mysql_query($sql);
    if(!$result) throw new Exception(mysql_error());
    return $result;
  }

  /**
  * return all the table name from the database.
  * @return array
  */
  public static function getAllTableNames() {
    $list = mysql_list_tables(self::getDbName());
    $tables = array();
    while($row = mysql_fetch_array($list)) {
      $tables[] = $row[0];
    }
    return $tables;
  }

  /**
  * Return all the column names of the table.
  * @param string $table_name
  * @exception if the table name does not exist.
  * @return array
  */
  public static function getAllColumnNames($table_name) {
    if(!self::isTableExist($table_name))
      throw new Exception('Table does not exist.');
    
    $list = mysql_list_fields(self::getDbName(), $table_name);
    $columns = array();
    for ($i = 0; $i < mysql_num_fields($list); $i++) {
      $columns[] = mysql_field_name($list, $i);
    }
    return $columns;
  }

  /**
  * Return all the constraints name and type.
  * @param string $table_name
  * @exception if the table does not exist.
  * @return array
  */
  public static function getAllConstraintNames($table_name) {
    if(!self::isTableExist($table_name))
      throw new Exception('Table does not exist.');

    $sql = sprintf('SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
                    TABLE_SCHEMA = schema() AND TABLE_NAME = %s',
                    self::wrapValue($table_name)
           );

    $rs = self::query($sql);
    $constraint = array();
    while($row = mysql_fetch_array($rs)) {
      $constraint[] = $row[2];
    }
    return $constraint;
  }

  /**
  * Return the database name.
  * @return string
  */
  public static function getDbName() {
    return self::$db;
  }

  /**
  * Check whether the table exist in the database or not.
  * @param string $table_name
  * @exception if the given table name is empty.
  * @return bool
  */
  public static function isTableExist($table_name) {
    if(empty($table_name))
      throw new Exception('Table name can not be empty');

    $tables = self::getAllTableNames();
    return in_array($table_name, $tables);
  }

  /**
  * Check whether the column exist in the table or not.
  * @param string $table_name
  * @param string $column_name
  * @exception if the given table name or column name is empty.
  * @return bool
  */
  public static function isColumnExist($table_name, $column_name) {
    if(empty($table_name) || empty($column_name))
      throw new Exception('Table or column name can not be empty');

    $columns = self::getAllColumnNames($table_name);
    return in_array($column_name, $columns);
  }

  /**
  * Return whether the constraint exist or not.
  * @param string $table_name
  * @param string $constraint_name
  * @exception if the table or constraint name is empty
  * @return bool
  */
  public static function isConstraintExist($table_name, $constraint_name) {
    if(empty($table_name) || empty($constraint_name))
      throw new Exception('Table or constraint name can not be empty');

    $constraints = self::getAllConstraintNames($table_name);
    return in_array($constraint_name, $constraints);
  }

  /**
  * start a mysql transaction.
  * @throws Exception if the transaction already been started.
  */
  public static function startTransaction() {
    if(self::$inTransaction)
      throw new Exception('Can not start new transaction.');

    self::query('START TRANSACTION');
    self::$inTransaction = true;
  }

  /**
  * commit a mysql transaction.
  * @throws Exception if no transaction has been started.
  */
  public static function commit() {
    if(!self::$inTransaction)
      throw new Exception('Can not commit without a transaction.');

    self::query('COMMIT');
    self::$inTransaction = false;
  }

  /**
  * rollback a mysql transaction.
  * @throws Exception if no transaction has been started.
  */
  public static function rollback() {
    if(!self::$inTransaction)
      throw new Exception('Can not rollback without a transaction.');

    self::query('ROLLBACK');
    self::$inTransaction = false;
  }

  /**
  * Return if we have any transaction open or not.
  * @return bool
  */
  public static function isInTransaction() {
    return self::$inTransaction;
  }

  /**
  * if $bool true then table can be created automatically.
  * if $onlyOnce true then table will be created for the very
  * first time. Otherwise the table will be altered every time when
  * we make any changes to the tabledefinition.
  * We set $onlyOnce to false when we are in developement mode.
  * @param bool $bool
  * @param bool $onlyOnce
  */
  public static function setCreateTableMode($bool = false, $onlyOnce = true) {
    self::$createTable = $bool;
    self::$onlyOnce = $onlyOnce;
  }

  /**
  * return the create table mode.
  * @return bool
  */
  public static function getCreateTableMode() {
    return self::$createTable;
  }

  /**
  * return a quoted name. We use the quoted name for
  * the table or column name.
  * @param string $name
  * @return string
  */
  public static function wrapName($name) {
    return '`'.$name.'`';
  }

  /**
  * Return a quated value string. We use this quoted string when
  * we insert data to the database.
  * @param string $value
  * @return string
  */
  public static function wrapValue($value) {
    return "'".$value."'";
  }

  /**
  * Escape special character in a given string
  * @param string $str
  * @return string
  */
  public static function escapeString($str) {
    return mysql_real_escape_string($str);
  }

  /**
  * Escape html special characters.
  * @param string $html
  * @return string
  */
  public static function escapeHTML($html) {
    return htmlspecialchars($html);
  }

  /**
  * List of $class_name objects
  * @param string $class_name
  * @param string | array $where
  * @param string $orderBy
  * @param int $page
  * @param int $limit
  * @return List of $class_name objects
  */
  public static function loadAll($class_name, $where = null, $orderBy = null,
                                 $page = 1, $limit = null) {
    $obj = new $class_name();
    $table_name = $obj->getTableDefinition()->getTableName();

    $where_clause = "";
    if(is_string($where)) {
      $where_clause = "WHERE ".$where;
      
    } else if(is_array($where)) {
      foreach($where as $key => $value) {
        $where_clause = $where_clause.self::wrapName($key)." = ".
        self::wrapValue($value)." AND ";
      }

      if(!empty($where_clause))
        $where_clause = "WHERE ".rtrim($where_clause, 'AND ');
      
    } else if(empty($where)) {
      // its ok.
    } else {
      throw new Exception('WHERE clause is not valid.');
    }


    $orderby_clause = "";
    if(is_string($orderBy)) {
      $orderby_clause = "ORDER BY ".$orderBy;
    } else if(empty($orderBy)) {
      // its ok.
    } else {
      throw new Exception('ORDER BY clause is not valid.');
    }

    $limit_clause = "";
    if(!empty($limit)) {
      $offset = ($page - 1) * $limit;
      $limit_clause = "LIMIT ".$offset.", ".$limit;
    }

    $sql = sprintf('SELECT * FROM %s %s %s %s',self::wrapName($table_name),
    $where_clause, $orderby_clause, $limit_clause);
    $rs = self::query($sql);
    $list = self::rsToObjects($class_name, $rs);
    return $list;
  }

  /**
  * Return a $class_name object
  * @param string $class_name
  * @param string | array $where
  * @param string $orderBy
  * @return $class_name objects || null if the object can not be found.
  */
  public static function loadOnly($class_name, $where = null, $orderBy = null) {
    $list = self::loadAll($class_name, $where, $orderBy, 1, 1);
    if(empty($list)) return null;
    return $list[0];
  }
  
  /**
  * Return an object of type $class_name
  * @param string $class_name
  * @param int $id
  * @return $class_name object
  */
  public static function loadById($class_name, $id) {
    if(empty($id))
      throw new Exception('Id can not be empty');
    
    $obj = new $class_name();
    $pk = $obj->getTableDefinition()->getPrimaryKeyName();
    $table_name = $obj->getTableDefinition()->getTableName();
    $sql = sprintf('SELECT * FROM %s WHERE %s = %s', self::wrapName($table_name),
                    self::wrapName($pk), self::wrapValue($id)
           );
    
    $rs = self::query($sql);
    $list = self::rsToObjects($class_name, $rs);
    if(count($list) != 1) 
      throw new Exception('Object not found');
    
    $obj = $list[0];
    return $obj;
  }

  /**
   * Return the total number of objects with the given where clause.
   * @param string $class_name
   * @param string | array $where
   * @return int
   */
  public static function countAll($class_name, $where) {
    $obj = new $class_name();
    $table_name = $obj->getTableDefinition()->getTableName();

    $where_clause = "";
    if(is_string($where)) {
      $where_clause = "WHERE ".$where;

    } else if(is_array($where)) {
      foreach($where as $key => $value) {
        $where_clause = $where_clause.self::wrapName($key)." = ".
        self::wrapValue($value)." AND ";
      }

      if(!empty($where_clause))
        $where_clause = "WHERE ".rtrim($where_clause, 'AND ');

    } else if(empty($where)) {
      // its ok.
    } else {
      throw new Exception('WHERE clause is not valid.');
    }


    $sql = sprintf('SELECT COUNT(*) c FROM %s %s',self::wrapName($table_name), $where_clause);
    $rs = self::query($sql);
    $row = mysql_fetch_array($rs);
    return $row['c'];
  }
  
  /**
  *
  * @param string $class_name
  * @param array $rs
  * @return List of object type $class_name
  */
  public static function rsToObjects($class_name, $rs) {
    $list = array();
    while($row = mysql_fetch_array($rs)) {
      $obj = new $class_name();
      $pk = $obj->getTableDefinition()->getPrimaryKeyName();
      $obj->setPrimaryKey($row[$pk]);
      $cols = $obj->getTableDefinition()->getColumns();
      foreach($cols as $c) {
        $obj->$c['name'] = $row[$c['name']];
      }
      $list[] = $obj;
    }
    return $list;
  }

  /**
  * Insert the model to the database and return its id.
  * @param Model $model
  * @return int id of this new inserted model.
  */
  public static function store(Model $model) {
    $table_name = $model->getTableDefinition()->getTableName();
    $pk = $model->getTableDefinition()->getPrimaryKeyName();
    $properties = $model->getAllProperties();
    $cal = "";
    $val = "";
    
    foreach($properties as $prop => $value) {
      if($prop != $pk) {
        $cal = $cal.self::wrapName($prop).", ";
        $val = $val.self::wrapValue($value).", ";
      }
    }
    
    $cal = rtrim($cal, ", ");
    $val = rtrim($val, ", ");
    $sql = sprintf('INSERT INTO %s (%s) VALUES(%s)', self::wrapName($table_name),$cal, $val);
    self::query($sql);
    $lastId = mysql_insert_id();
    if(empty($lastId))
      throw new Exception('Could not store object.');
    
    return $lastId;
  }

  /**
  * Update the model in database.
  * @param Model $model
  */
  public static function update(Model $model) {
    $table_name = $model->getTableDefinition()->getTableName();
    $pk = $model->getTableDefinition()->getPrimaryKeyName();
    $pk_value = null;
    $properties = $model->getAllProperties();
    $update = "";

    foreach($properties as $prop => $value) {
      if($prop != $pk) {
        $update = $update . self::wrapName($prop) . " = " . self::wrapValue($value) . ", ";
      } else {
        $pk_value = $value;
      }
    }
    
    if(empty($pk_value)) throw new Exception('Primary key value not found.');
      $update = rtrim($update, ", ");
      
    if(!empty($update)) {
      $sql = sprintf('UPDATE %s SET %s WHERE %s = %s',
                      self::wrapName($table_name),
                      $update,
                      self::wrapName($pk),
                      self::wrapValue($pk_value)
             );
      self::query($sql);
    }
  }

  /**
   * Delete all the rows with the given where condition.
   * @param string $class_name
   * @param array | string $where
   */
  public static function deleteAll($class_name, $where = null) {
    $obj = new $class_name();
    $table_name = $obj->getTableDefinition()->getTableName();

    $where_clause = "";
    if(is_string($where)) {
      $where_clause = "WHERE ".$where;

    } else if(is_array($where)) {
      foreach($where as $key => $value) {
        $where_clause = $where_clause . self::wrapName($key) . " = " .
        self::wrapValue($value) . " AND ";
      }

      if(!empty($where_clause))
        $where_clause = "WHERE " . rtrim($where_clause, 'AND ');

    } else if(empty($where)) {
      // its ok.
    } else {
      throw new Exception('WHERE clause is not valid.');
    }

    $sql = sprintf('DELETE FROM %s %s',self::wrapName($table_name),
                                       $where_clause
           );
    self::query($sql);
  }
  
  /**
  * Return the query log.
  * @return array
  */
  public static function getQueryLog() {
    return self::$queryLog;
  }
}
?>
Return current item: Phpwebpad