Loading Please wait...

mysql.php

Go to the documentation of this file.

<?php
include_once(ZIGROOT.DRS.MOD_KEY_NAME_EXPLICIT.DRS.'orm'.DRS.'driver'.DRS.'driver_core.php');
class OrmDriver_mysql extends OrmDriver{
  function connect(){
    $hostStr = (isset($this->connData['port']) && strlen($this->connData['port'])>0) ?  $this->connData['host'].':'.$this->connData['port'] : $this->connData['host'];
    if(!$this->conn = mysql_connect($this->connData['host'], $this->connData['user'], $this->connData['psw'])){
      perror("<code>ZigOrm.Drivers.MySQL.Connection.Initialization</code><br />Unable to establish connection to database for ".$this->connAlias);
      return false;
    }
    if(!mysql_select_db($this->connData['database'], $this->conn)){
      perror("<code>ZigOrm.Drivers.MySQL.Connection.Initialization</code><br />Couldn't select Database ".$this->connData['database']);
      return false;
    }
    return true;
  }
  function __generate(){
    global $__ZigOrm;
    if(strlen($this->lex['where']) < 3 && is_array($this->lex['whereLex']) && count($this->lex['whereLex']) >= 1){
      //Experimental Block
      //foreach($this->lex['whereLex'] as $i => $wObj){
      //  $this->lex['whereLex'][$i]->colName = $this->caller->realTableName.'.'.$wObj->colName;
      //}
      //Exp
      $this->lex['where'] = $this->whereLexToStr($this->lex['whereLex']);
      //var_dump($this->lex);
    }
    if($this->caller->isJoined){
      if(is_array($this->caller->joiner->lex['whereLex']) && count($this->caller->joiner->lex['whereLex']) >= 1){
        foreach($this->caller->joiner->lex['whereLex'] as $i => $wObj){
          $this->caller->joiner->lex['whereLex'][$i]->colName = $this->caller->joiner->realTableName.'.'.$wObj->colName;
        }
        if(strlen($this->lex['where']) <= 3){
          $this->lex['where'] = $this->whereLexToStr($this->caller->joiner->lex['whereLex']);
        }else{
          $this->lex['where'] .= " and ".$this->whereLexToStr($this->caller->joiner->lex['whereLex']);
        }
        if(strlen($this->lex['where']) > 3){
          foreach($this->caller->joiner->fk as $colAlias => $rObj){
            if($rObj->table == $this->caller->realTableName){
              $this->lex['where'] .= " and ".$this->caller->realTableName.".".$rObj->col." = ".$this->caller->joiner->realTableName.".".$colAlias." ";
            }
          }
        }
        //var_dump($this->lex);
      }
    }
    switch($this->qType){
      case ZIGORM_Q_SELECT:
          //{ Grab the items to select
          if(!$this->lex['distinct']){
            $select_str = "select\n\t";
          }else{
            $select_str = "select distinct\n\t";
          }
          foreach($this->lex['select'] as $attr => $flag){
            if($flag){
              $select_str .= "`".$attr."` as ".$this->lex['aliasProperCaps'][$attr].", \n\t";
            }
          }
          $select_str = rtrim($select_str, ", \n\t");
          //}
          if($this->lex['table'])$select_str .= "\n\tfrom `".$this->lex['table']."`";//from table_name
          if($this->caller->isJoined && strlen($this->lex['where']) >= 3){
            $select_str .=  ", `".$this->caller->joiner->realTableName."`";
          }
          if(strlen($this->lex['where']) >= 4)$select_str .= "\n\twhere\n\t\t".$this->lex['where'];//set the Where Query
          //{ Order
          //if(strlen($this->lex['order']) > 0)$select_str .= "\n\torder by\n\t\t".$this->lex['order'];//set The order By
          $oStr = 'order by ';
          foreach($this->lex['order'] as $i => $ord){
            $oStr .= "`$ord[col]` $ord[way]";
            if($i != count($this->lex['order'])-1)$oStr .= ',';
          }
          if(count($this->lex['order']) >= 1)$select_str .= "\n".$oStr;
          //}
          if(strlen($this->lex['limit']) > 0)$select_str .= "\n\tlimit\n\t\t".$this->lex['limit'];//Set the Limit
          if(strlen($this->lex['group']) > 0)$select_str .= "\n\tgroup by\n\t\t".$this->lex['group'];//set The Group By
          if(strlen($this->lex['having']) > 0)$select_str .= "\n\thaving\n\t\t".$this->lex['having'];//set The Having
          $ret_sql = $select_str;
        break;
      case ZIGORM_Q_JOIN:
          $select_str = ($this->lex['distinct']) ? "select distinct\n\t" : "select\n\t";
          //{ Fetch the select Cols of the Currssnt Table
          foreach($this->lex['select'] as $attr => $flag){
            if($flag){
              $select_str .= $this->lex['table'].'.'.$attr." as ".$this->lex['aliasProperCaps'][$attr].", \n\t";
            }
          }
          //}
          //{ Fetch the select Cols of all foreign tables
          if(is_array($this->caller->fk)){
            foreach($this->caller->fk as $srcCol => $fkObj){
              $fkRes =& $this->caller->{$__ZigOrm->dataDict->tables[$fkObj->table]};
              if(is_array($fkRes->lex['select'])){
                foreach($fkRes->lex['select'] as $colName => $selFlag){
                  if($selFlag){
                    $select_str .= "\n\t".$fkRes->lex['table'].".".$colName." as ".$fkRes->lex['aliasProperCaps'][$colName].", ";
                  }
                }
              }
            }
          }
          //}
          $localWhereStr = '';
          //{ Build the Where Query for this table
          if(is_array($this->lex['whereLex'])){
            foreach($this->lex['whereLex'] as $i => $whereObj){
              if(!strlen($whereObj->cnj))$whereObj->cnj = 'and ';
              if(strlen(trim($localWhereStr)) < 4)$whereObj->cnj = "";
              $localWhereStr .= $whereObj->cnj.' '.$this->lex['table'].".$whereObj->colName $whereObj->op '$whereObj->value'\n\t";
            }
          }
          //}
          //{ Local Order
          $localOrderStr = '';
          if(is_array($this->lex['order']) && count($this->lex['order'])){
            foreach($this->lex['order'] as $i => $ord){
              $localOrderStr .= $this->lex['table'].".$ord[col] $ord[way]";
              if($i != count($this->lex['order'])-1)$localOrderStr .= ',';
            }
          }
          //}
          $formStr = '';
          //{ Build the Where Query for Foreign table and build the form String and order Query Too
          $foreignWhereStr = '';
          if(is_array($this->caller->fk)){
            foreach($this->caller->fk as $srcCol => $fkObj){
              $formStr .= $fkObj->table.",\n\t";
              $fkRes =& $this->caller->{$__ZigOrm->dataDict->tables[$fkObj->table]};
              if(is_array($fkRes->lex['whereLex'])){
                foreach($fkRes->lex['whereLex'] as $i => $whereObj){
                  if(!strlen($whereObj->cnj))$whereObj->cnj = 'and ';
                  if(strlen(trim($foreignWhereStr)) < 4)$whereObj->cnj = "";
                  $foreignWhereStr .= $whereObj->cnj." ".$fkObj->table.".$whereObj->colName $whereObj->op '$whereObj->value'\n\t";
                }
              }
              //{{ Foreign Order
              $foreignOrderStr = '';
              if(is_array($fkRes->lex['order']) && count($fkRes->lex['order'])){
                foreach($fkRes->lex['order'] as $i => $ord){
                  $foreignOrderStr .= $fkObj->table.".$ord[col] $ord[way]";
                  if($i != count($fkRes->lex['order'])-1)$foreignOrderStr .= ',';
                }
              }
              //}}
            }
          }
          //}
          switch($this->lex['join']['type']){
            case ZIGORM_Q_AUTOJOIN:
                //{ Add the Join on to the where Syntax
                $joinWhereStr = '';
                foreach($this->lex['join']['on'] as $tableName => $colrelR){
                  if(is_array($colrelR)){
                    foreach($colrelR as $localCol => $foreignCol){
                      if(strlen(trim($joinWhereStr)) > 4)$conj = 'and'; else $conj = '';
                      $joinWhereStr .= "$conj ".$this->lex['table']."."."$localCol = $tableName.$foreignCol\n\t\t";
                    }
                  }
                }
                //}
                //{ Prepare the ultimate Where Query
                if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)";
                if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)";
                if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "(\n\t\t".trim($joinWhereStr)."\n\t)";
                $whereStr = implode("and", $Where);
                //}
                //{ Order Str
                $orderStr = '';
                if(strlen($localOrderStr))$orderStr = $localOrderStr;
                if(strlen($foreignOrderStr))$orderStr .= ",$foreignOrderStr";
                if(strlen($orderStr) >= 2)$orderStr = "order by $orderStr";
                //}
                $formStr = "\n\t".$this->lex['table'].",\n\t".trim($formStr, " ,\n");
                $ret_sql = trim($select_str, "\n\r\t ,")."\nfrom\n\t".trim($formStr, "\n\t, ")."\nwhere\n\t".$whereStr."\n$orderStr";
              break;
            case ZIGORM_JOIN_INNER:
            case ZIGORM_JOIN_LEFT:
            case ZIGORM_JOIN_RIGHT:
                //{ Reconstruct teh Form Query
                $formStr = $this->lex['table'];
                //}
                //{ make the join Query
                switch($this->lex['join']['type']){
                  case ZIGORM_JOIN_INNER:
                      $joinClause = 'inner join';
                    break;
                  case ZIGORM_JOIN_LEFT:
                      $joinClause = 'left join';
                    break;
                  case ZIGORM_JOIN_RIGHT:
                      $joinClause = 'right join';
                    break;
                }
                $joinWhereStr = '';
                foreach($this->lex['join']['on'] as $tableName => $colrelR){
                  foreach($colrelR as $localCol => $foreignCol){
                    $joinWhereStr .= "$joinClause $tableName on\n\t".$this->lex['table'].".$localCol = $tableName.$foreignCol\n";
                  }
                }
                //}
                $whereStr = trim($joinWhereStr);
                $tmpWhere = '';
                if(strlen(trim($localWhereStr)))$tmpWhere .= trim($localWhereStr);
                if(strlen(trim($foreignWhereStr))){
                  if(!strlen(trim($tmpWhere))){
                    $tmpWhere .= "(".trim($foreignWhereStr).")";
                  }else{
                    $tmpWhere .= "and (".trim($foreignWhereStr).")";
                  }
                }
                if(strlen(trim($tmpWhere)))$tmpWhere = "\nwhere\t".$tmpWhere;
                $whereStr .= "\n".$tmpWhere;
                //}
                $ret_sql = trim($select_str, "\n\r\t ,")."\nfrom\n\t".trim($formStr, "\n\t, ")."\n".trim($whereStr);
                //var_dump($ret_sql);
              break;
            case ZIGORM_JOIN_CROSS:
                //{ Reconstruct teh Form Query
                $formStr = $this->lex['table'];
                //}
                //{ make the join Query
                $joinWhereStr = 'cross join ';
                foreach($this->lex['join']['on'] as $tableName => $colrelR){
                    $joinWhereStr .= $tableName."\n";
                }
                //}
                //{ Prepare the ultimate Where Query
                if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)";
                if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)";
                if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "\n".trim($joinWhereStr)."\n\t";
                $whereStr = implode("and", $Where);
                //}
                $ret_sql = $select_str."\nfrom\n\t".trim($formStr, "\n\t, ").$whereStr;
              break;
            case ZIGORM_JOIN_NATURAL:
                //{ Reconstruct teh Form Query
                $formStr = $this->lex['table'];
                //}
                //{ make the join Query
                $joinWhereStr = 'natural join ';
                foreach($this->lex['join']['on'] as $tableName => $colrelR){
                    $joinWhereStr .= $tableName."\n";
                }
                //}
                //{ Prepare the ultimate Where Query
                if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)";
                if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)";
                if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "\n".trim($joinWhereStr)."\n\t";
                $whereStr = implode("and", $Where);
                //}
                $ret_sql = $select_str."\nfrom\n\t".trim($formStr, "\n\t, ").$whereStr;
              break;
            default:
              perror('<code>ZigOrm.Drivers.MySQL</code><br />Invalid join Type '.$this->lex['join']['type']);
              exit;
          }
        break;
      case ZIGORM_Q_INSERT:
          //{ Grab The attributes and their Values
          $insert_str = "insert into `".$this->lex['table']."`(";
          foreach($this->lex['attributes'] as $attr => $value){
            if(strlen($value) > 0){
              $val[] = $value;
              $insert_str .= "`".$attr."`, ";
            }
          }
          $insert_str = rtrim($insert_str, ', ');
          $insert_str .= ")\nvalues(";
          if(isset($val) && is_array($val) && count($val) >= 1){
            foreach($val as $value){
              $insert_str .= "".$value.", ";
            }
          }else{
            perror('<code>ZigOrm.Drivers.MySQL.Query.Insert</code><br />Nothing to Insert');
          }
          $insert_str = rtrim($insert_str, ', ');
          $insert_str .= ')';
          $ret_sql = $insert_str;
          //}
        break;
      case ZIGORM_Q_UPDATE:
          $update_str = 'update '.$this->lex['table']."\n\tset\n\t";
          //{ Grab the Attributes and their Values
          foreach($this->lex['attributes'] as $attr => $value){
            if(strlen($value) > 0){
              $update_str .= '`'.$attr."` = $value,\n\t";
            }
          }
          $update_str = rtrim($update_str, ",\n\t");
          //}
          if(strlen($this->lex['where']) >= 4)$update_str .= "\n\t where ".$this->lex['where'];//set the Where Query
          if(strlen($this->lex['limit']) > 0)$update_str .= "\n\t limit ".$this->lex['limit'];//Set the Limit
          $oStr = 'order by ';
          foreach($this->lex['order'] as $i => $ord){
            $oStr .= "`$ord[col]` $ord[way]";
            if($i != count($this->lex['order'])-1)$oStr .= ',';
          }
          if(count($this->lex['order']) >= 1)$update_str .= "\n".$oStr;
          $ret_sql = $update_str;
        break;
      case ZIGORM_Q_REMOVE:
          $remove_str = "delete from ".$this->lex['table']."\n\t";
          if(strlen($this->lex['where']) >= 4)$remove_str .= "\n\t where ".$this->lex['where'];//set the Where Query
          if(strlen($this->lex['limit']) > 0)$remove_str .= "\n\t limit ".$this->lex['limit'];//Set the Limit
          $oStr = 'order by ';
          foreach($this->lex['order'] as $i => $ord){
            $oStr .= "`$ord[col]` $ord[way]";
            if($i != count($this->lex['order'])-1)$oStr .= ',';
          }
          if(count($this->lex['order']) >= 1)$remove_str .= "\n".$oStr;
          $ret_sql = $remove_str;
        break;
      case ZIGORM_Q_TRUNCATE:
          $ret_sql = "truncate table `".$this->lex['table']."`\n\t";
        break;
      case ZIGORM_Q_SQL:
          $ret_sql = $this->sql;
        break;
      default:
        perror('<code>ZigOrm.Drivers.MySQL</code><br />Invalid Query Type '.$this->qType);
        exit;
    }
    if(!isset($ret_sql))return false;
    $this->sql = $ret_sql;
    //echo $this->sql;
  }
  function execute(){
    $this->__generate();
    zLogger::debug("Executing Query (Base64Encoded) `".base64_encode($this->sql)."`", "orm.driver.MySql");
    if(!$this->res = mysql_query($this->sql, $this->conn)){
      perror('<code>ZigOrm.Drivers.MySQL.Execute</code><br />Failed to Execute Query<pre style="background-color: #FFFFFF;font-weight: normal;border: 1px dashed #B52D1E;padding-left: 2px;margin-left: 2px;">'.trim($this->sql).'</pre>MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>');
      return false;
    }
    $this->numRows = @mysql_num_rows($this->res);
    $this->affected_rows = mysql_affected_rows($this->conn);
    return true;
  }
  function exportRaw(){
    global $app_data;
    //$modelClassName = get_class($this->caller).'RowSet';
    $modelClassName = 'stdClass';
    if($this->numRows > 0){
      $record = array();
      while($row = mysql_fetch_array($this->res, MYSQL_ASSOC)){
        $i = count($record);
        foreach($row as $col => $val){
          if(!isset($record[$i]) || !is_object($record[$i])){
            $record[$i] = new $modelClassName;
            $record[$i]->colList = array();
            //$record[$i]->handle =& $this;
          }
          $record[$i]->colList[$col] = $val;
        }
      }
      return $record;
    }
  }
  function getForeignKey(){
    zLogger::debug("Finding Foreign Key of Table ".$this->lex['table']." On Database ".$this->connData['database'], 'orm.driver.MySql');
    $sql = "SELECT
              u.column_name as src_col,
              u.referenced_table_name as ref_table,
              u.referenced_column_name as ref_col
            FROM information_schema.table_constraints AS c
            INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name )
            WHERE
              c.constraint_type = 'FOREIGN KEY' AND
              c.table_schema='".$this->connData['database']."' AND
              c.table_name='".$this->lex['table']."'";
    if(!$res = mysql_query($sql, $this->conn)){
      perror('<code>Zigmoyd.orm.Drivers.MySQL.getForeignKey</code><br />Failed to get Foreign key of Table '.$this->lex['table'].'<br />MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>');
      return false;
    }
    if(@mysql_num_rows($res) <= 0){
      return false;
    }
    $record = array();
    while($row = mysql_fetch_object($res)){
      $record[] = $row;
    }
    return $record;
  }
  function getPrimaryKey(){
    zLogger::debug("Finding Primary Key of Table ".$this->lex['table']." On Database ".$this->connData['database'], 'orm.driver.MySql');
    /*$sql = "select
              `column_name`
            from
              information_schema.KEY_COLUMN_USAGE
            where
              `table_schema` = '".$this->connData['database']."' and
              `table_name` = '".$this->lex['table']."' and
              `constraint_name` = 'primary'";*/
    $sql = "desc ".$this->lex['table'];
    if(!$res = mysql_query($sql, $this->conn)){
      perror('<code>Zigmoyd.orm.Drivers.MySQL.getPrimaryKey</code><br />Failed to get Foreign key of Table '.$this->lex['table'].'<br />MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>');
      return false;
    }
    if(@mysql_num_rows($res) <= 0){
      return false;
    }
    $record = array();
    while($row = mysql_fetch_object($res)){
      if($row->Key == "PRI"){
        $record[] = $row->Field;
      }
    }
    return $record;
  }
  function disConnect(){
    return mysql_close($this->conn);
  }
  function listCols($table){
    global $__ZigOrm;
    $colRes = mysql_list_fields($this->connData['database'], $table, $this->conn);
    if(!$colRes)return false;
    $cols = array();
    $i = 0;
    while($i < mysql_num_fields($colRes)){
      $cols[] = mysql_field_name($colRes, $i);
      ++$i;
    }
    if(count($cols) <= 0)return false;
    return $cols;
  }
}
?>

---