<?
require_once('column.class.php');
/**
* Table Class - part of MySQL2Object set of classes
*
* @author Arkadiusz Maliński <hide@address.com>
* @package mysql2object
*/
class Table {
/**
* Table name
*
* @var string
*/
private $_name;
/**
* Array of description values
*
* @var array
*/
private $_description;
/**
* datasource object
*
* @var Datasource
*/
private $_db = false;
/**
* Array of Column objects
*
* @var array
*/
private $_arColumns = array();
/**
* Status of composing statement to gets data
*
* @var integer
*/
private $_statement = array();
/**
* Results of composed statement
*
* @var array
*/
private $_statementResults = array();
/**
* Last created statement name
*
* @var string
*/
private $_lastCreatedStatement = null;
/**
* Array of valid comparision operands
*
* @var array
*/
private $_validOperators = array('==', '>=', '<=', '<', '>', '<>', '!=');
/**
* Constructor, on start creates all table structures, inserts data and gets table description.
*
* @param array $arrTable
*/
public function __construct($arrTable, DB $DBobj) {
$this->_db = $DBobj;
$this->_name = $arrTable['name'];
$this->_description = $arrTable['tblDescription'][0];
for($i=0; $i<count($arrTable['colDescription']); $i++) {
$col['name'] = $arrTable['colDescription'][$i]['Field'];
$col['description'] = $arrTable['colDescription'][$i];
$col['values'] = $arrTable['colValues'][$i];
$this->_addColumn(new Column($col));
}
}
/**
* Returns table name
*
* @return string
*/
public function __toString() {
return $this->_name;
}
/**
* Handling calls
*
* @param string $func
* @return mixed
*/
public function __get($func) {
if(array_key_exists($func, $this->_arColumns)) {
return $this->_arColumns[$func];
} elseif(array_key_exists($func, $this->_description)) {
return $this->_description[$func];
} elseif(array_key_exists($func, $this->_statementResults) && $this->_lastCreatedStatement == null) {
return $this->_statementResults[$func];
} else {
switch (strtolower($func)) {
case 'rows':
return $this->_description['Rows'];
break;
case 'columns':
return count($this->_arColumns);
break;
case 'all':
return array_keys($this->_arColumns);
break;
case 'lastid':
return $this->_db->lastid();
break;
default:
throw new TableException('Unknown call "'.$func.'"');
}
}
}
/**
* Handling functions calls
*
* @param string $func
* @param mixed $args
* @return mixed
*/
public function __call($func, $args) {
if(array_key_exists($func, $this->_arColumns)) {
switch (count($args)) {
case 1:
switch (gettype($args[0])) {
case 'integer':
if (array_key_exists($args[0], $this->_arColumns[$func]->all)) {
return $this->_arColumns[$func]->getValue($args[0]);
} else
throw new TableException('Index out of bounds in "'.$func.'"');
break;
default:
throw new TableException('Unknown method parameter in "'.$func.'"');
break;
}
break;
}
} else {
switch (strtolower($func)) {
case 'createcomposedcondition':
if(func_num_args()!=2)
throw new TableException('Invalid number of parameters in method "createComposedCondition()"');
elseif(array_key_exists($func, $this->_arColumns)) {
throw new TableException('Cannot create condition with name "'.$func.'" because exist columnt with that name.');
} else {
if($this->_statement[$args[0]] || array_key_exists($func, $this->_statementResults))
throw new TableException('Cannot create condition with name "'.$func.'" because it\'s already created.');
else {
$this->_statement[$args[0]] = 1;
foreach ($this->_arColumns as $key=>$column) {
$ret[$key] = $column->all;
}
$this->_statementResults[$args[0]] = $ret;
$this->_lastCreatedStatement = $args[0];
return null;
}
}
break;
case 'endcomposedcondition':
if(func_num_args()!=2)
throw new TableException('Invalid number of parameters in method "endComposedCondition()"');
elseif(!strcmp($args[0],$this->_lastCreatedStatement) && $this->_statement[$args[0]]) {
$this->_statement[$args[0]] = 0;
$this->_statementResults[$args[0]];
$this->_lastCreatedStatement = null;
} else
throw new TableException('Cannot end condition "'.$func.'" because isn\'t started');
break;
default:
throw new TableException('Unknown call "'.$func.'"');
break;
}
}
}
/**
* Add values into table
*
* @param array $values
* @return int
*/
public function add($values = array()) {
if(count($values)) {
foreach ($values as $fieldname=>$value) {
if(!array_key_exists($fieldname, $this->_arColumns))
throw new TableException('Unknown column name "'.$fieldname.'" in "add()" method');
}
$sql = 'INSERT INTO '.$this->_name.'(';
foreach ($values as $fieldname=>$value) {
$sql .= '`'.$fieldname.'`, ';
}
$sql = substr($sql, 0, -2);
$sql .= ') VALUES (';
foreach ($values as $fieldname=>$value) {
if(is_string($value) && !preg_match('/\(.*\)/', $value))
$sql .= '"'.$value.'", ';
else
$sql .= $value.', ';
}
$sql = substr($sql, 0, -2);
$sql .= ')';
return $this->_db->query($sql);
} else
throw new TableException('Unknown values in "add()" method.');
}
/**
* Update/Replace values in table based on SQL condition
*
* @param array $values
* @param string $SQLCondition
* @param bool $replace
* @return integer
*/
public function modify($values = array(), $SQLCondition, $replace = false) {
if(count($values)) {
foreach ($values as $fieldname=>$value) {
if(!array_key_exists($fieldname, $this->_arColumns))
throw new TableException('Unknown column name "'.$fieldname.'" in "modify()" method');
}
$sql = ($replace ? 'REPLACE' : 'UPDATE').' '.$this->_name.' SET ';
foreach ($values as $fieldname=>$value) {
$sql .= '`'.$fieldname.'` = ';
if(is_string($value) && !preg_match('/\(.*\)/', $value))
$sql .= '"'.$value.'", ';
else
$sql .= $value.', ';
}
$sql = substr($sql, 0, -2);
if(strlen($SQLCondition))
$sql .= ' WHERE '.$SQLCondition;
return $this->_db->query($sql);
}
}
/**
* Select columns to be vieved
*
* @return mixed
*/
public function columns() {
$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
foreach (func_get_args() as $column) {
if(!array_key_exists($column, $check))
throw new TableException('Unknown column name "'.$column.'" in "columns()" method');
}
foreach (func_get_args() as $column) {
$ret[$column] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $check[$column] : $check[$column]->all);
}
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
$this->_statementResults[$this->_lastCreatedStatement] = $ret;
return null;
} else
return $ret;
}
/**
* Select values from table on defined conditions.
* As a parameters uses array(fieldname, operator, value_for_comparision) for each condition.
*
* @param array $condition1,...
* @return array of ColumnValues
*/
public function select() {
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement])
$ret = $this->_statementResults[$this->_lastCreatedStatement];
else
foreach ($this->_arColumns as $key=>$column) {
$ret[$key] = $column->all;
}
if(func_num_args()) {
foreach (func_get_args() as $param) {
if(!array_key_exists($param[0], $ret))
throw new TableException('Unknown column name "'.$param[0].'" in "select()" method');
if(!in_array($param[1], $this->_validOperators))
throw new TableException('Invalid comparision operator "'.$param[1].'"');
}
foreach (func_get_args() as $param) {
$ret = $this->_test($param, $ret);
}
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
$this->_statementResults[$this->_lastCreatedStatement] = $ret;
return null;
} else
return $ret;
} else {
throw new TableException('No condition definied in "select()" method');
}
}
/**
* Returns data sorted on defined columns
*
* @param array $columnName,...
* @return mixed
*/
public function sort() {
if(!func_num_args())
throw new TableException('Required at least one column name in "sort()" method');
else {
$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
foreach (func_get_args() as $arg) {
list($field, $sortMode) = explode(' ', $arg);
if(!array_key_exists($field, $check))
throw new TableException('Unknown column "'.$field.'" name in "sort()" method');
else {
$evalstr .= '$ret[\''.$field.'\'],';
switch (strtolower($sortMode)) {
default:
case 'asc': $evalstr .= 'SORT_ASC,'; break;
case 'desc': $evalstr .= 'SORT_DESC,'; break;
}
}
}
foreach ($check as $key=>$column) {
$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
if(!preg_match('/'.$key.'/', $evalstr))
$evalstr .= '$ret[\''.$key.'\'],';
}
eval('array_multisort('.substr($evalstr,0,-1).');');
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
$this->_statementResults[$this->_lastCreatedStatement] = $ret;
return null;
} else
return $ret;
}
}
/**
* Returns data from two joined tables connected by one column from each
*
* @param Table $foreignTable
* @param Column $thisColumn
* @param Column $foreignColumn
* @param array $foreignColumnNames
* @return array
*/
public function join(Table $foreignTable, Column $thisColumn, Column $foreignColumn, $foreignColumnNames=array()) {
if(array_key_exists($thisColumn->name, $this->_arColumns) && in_array($foreignColumn->name, $foreignTable->all)) {
$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
foreach ($check as $key=>$column) {
$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
}
for($i=0;$i<count($ret[$thisColumn->name]);$i++) {
$findValue = $ret[$thisColumn->name][$i];
$otherKey = array_keys($foreignColumn->all,$findValue);
if(!count($foreignColumnNames)) {
$columnsToAdd = $foreignTable->all;
} else
$columnsToAdd = $foreignColumnNames;
foreach ($columnsToAdd as $columnToAdd) {
$ret[$columnToAdd][$i] = $foreignTable->$columnToAdd($otherKey[0]);
}
}
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
$this->_statementResults[$this->_lastCreatedStatement] = $ret;
return null;
} else
return $ret;
} else
throw new TableException('Invalid columns in "join()" statement.');
}
/**
* Returns union data from two tables. If tables has the same number of columnt second parameter isn't necessary.
*
* @param Table $foreignTable
* @param array $fieldConnections
* @return array
*/
public function union(Table $foreignTable, $fieldConnections = array()) {
$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
foreach ($check as $key=>$column) {
$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
}
if(count($fieldConnections)) {
foreach ($fieldConnections as $thisField=>$foreignField)
if(!array_key_exists($thisField, $ret) || !in_array($foreignField, $foreignTable->all))
throw new TableException('Unknown columns connection "'.$thisField.'" <==> "'.$foreignField.'" in "union()" method.');
foreach ($fieldConnections as $thisField=>$foreignField)
$newret[$thisField] = array_merge($ret[$thisField], $foreignTable->$foreignField->all);
$ret = $newret;
} elseif($foreignTable->columns == count(array_keys($ret))) {
$foreignColumns = $foreignTable->all; $index = 0;
foreach ($ret as $key=>$item) {
$ret[$key] = array_merge($item, $foreignTable->$foreignColumns[$index++]->all);
}
} else
throw new TableException('Number of columns in both tables are not identical.');
if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
$this->_statementResults[$this->_lastCreatedStatement] = $ret;
return null;
} else
return $ret;
}
/**
* Test values and returns data which passes tests
*
* @param array $param
* @param array $arValues
* @return array
*/
private function _test($param, $arValues) {
foreach ($arValues[$param[0]] as $index=>$value) {
eval("\$returnedValue = ('$value' $param[1] '$param[2]');");
if($returnedValue) $indexOK[] = $index;
}
foreach ($arValues as $k=>$v) {
for ($i=0; $i<count($indexOK); $i++) {
$return[$k][] = $v[$indexOK[$i]];
}
}
return $return;
}
/**
* Adds column to table
*
* @param Column $col
*/
private function _addColumn(Column $col) {
$this->_arColumns[$col->__toString()] = $col;
}
}
class TableException extends Exception {}
?>