<?php
/**
*
* QMySqliDataObject
* @package Q
* @subpackage Database
* @author Thomas Sch�fer
* @since 13.07.2008 11:02:58
* @version 0.2
* @desc MySQLi adapter for data objects
*/
class QMysqliDataObject {
private $link;
private $resource;
private $sql;
private $header = array(
"AmountOfRows"=>false,
"NumRows"=>false,
"NumFields" => false,
"Limit" => false,
"Offset" => false,
"NumOfPages" => false,
"ActivePage" => false,
"ColumnNames" => false,
"FieldNames" => false,
"FieldAttributes" => false,
"FieldPositions" => false,
"FieldFlags" => false,
"FieldTypes" => false,
"FieldLengths" => false,
"TableNames" => false,
"TableAliases" => false,
"FieldAliases" => false,
"Perspectives" => false,
);
private $properties = array();
private $flags = array(
MYSQLI_NOT_NULL_FLAG => 'not_null',
MYSQLI_PRI_KEY_FLAG => 'primary_key',
MYSQLI_UNIQUE_KEY_FLAG => 'unique_key',
MYSQLI_MULTIPLE_KEY_FLAG => 'multiple_key',
MYSQLI_BLOB_FLAG => 'blob',
MYSQLI_UNSIGNED_FLAG => 'unsigned',
MYSQLI_ZEROFILL_FLAG => 'zerofill',
MYSQLI_AUTO_INCREMENT_FLAG => 'auto_increment',
MYSQLI_TIMESTAMP_FLAG => 'timestamp',
MYSQLI_SET_FLAG => 'set',
MYSQLI_NUM_FLAG => 'numeric',
MYSQLI_PART_KEY_FLAG => 'multiple_key',
MYSQLI_GROUP_FLAG => 'group_by'
);
private $types = array(
MYSQLI_TYPE_DECIMAL => 'decimal',
MYSQLI_TYPE_TINY => 'tinyint',
MYSQLI_TYPE_SHORT => 'int',
MYSQLI_TYPE_LONG => 'int',
MYSQLI_TYPE_FLOAT => 'float',
MYSQLI_TYPE_DOUBLE => 'double',
MYSQLI_TYPE_NULL => 'DEFAULT NULL',
MYSQLI_TYPE_TIMESTAMP => 'timestamp',
MYSQLI_TYPE_LONGLONG => 'bigint',
MYSQLI_TYPE_INT24 => 'mediumint',
MYSQLI_TYPE_DATE => 'date',
MYSQLI_TYPE_BIT => 'bit',
MYSQLI_TYPE_TIME => 'time',
MYSQLI_TYPE_DATETIME => 'datetime',
MYSQLI_TYPE_YEAR => 'year',
MYSQLI_TYPE_NEWDATE => 'date',
246 => 'decimal',
MYSQLI_TYPE_ENUM => 'enum',
MYSQLI_TYPE_SET => 'set',
MYSQLI_TYPE_TINY_BLOB => 'tinyblob',
MYSQLI_TYPE_MEDIUM_BLOB => 'mediumblob',
MYSQLI_TYPE_LONG_BLOB => 'longblob',
MYSQLI_TYPE_BLOB => 'blob',
MYSQLI_TYPE_VAR_STRING => 'varchar',
MYSQLI_TYPE_STRING => 'char',
MYSQLI_TYPE_GEOMETRY => 'geometry',
);
public function __construct($link, $sql) {
$this->link = $link;
$this->sql = $sql;
}
public function byLimit($offset=0, $limit=10) {
$this->header["Limit"] = $limit;
$this->header["Offset"] = $offset;
$this->init();
return $this;
}
public function byPage($page=false, $size=false) {
$this->header["ActivePage"] = empty($page) ? 1 : $page;
$this->header["Limit"] = empty($size) ? 10 : $size;
$this->init();
return $this;
}
private function prepareLimit(){
return $this->sql
. ' LIMIT '
. $this->header["Offset"]
. ","
. $this->header["Limit"]
. ";";
}
private function countRecords() {
$sql = "SELECT count(*) AS records ". substr($this->sql,strpos($this->sql,'FROM'));
$result = mysqli_query($this->link, $sql);
$resource = mysqli_fetch_object($result);
return $resource->records;
}
private function init() {
$this->setNumRows($this->countRecords());
if(empty($this->header["Offset"])) { // by page
$this->header["Offset"] = $offset = intval(($this->header["ActivePage"] - 1) * $this->header["Limit"]);
$limit = $this->header["Limit"];
$this->header["NumOfPages"] = empty($this->header["NumOfPages"])
? intval($this->header["NumRows"] / $this->header["Limit"])+1
: $this->header["NumOfPages"];
} else { // by limit
$offset = $this->header["Offset"];
$limit = $this->header["Limit"];
$this->header["NumOfPages"] = empty($this->header["NumOfPages"])
? intval($this->header["NumRows"] / $this->header["Limit"])+1
: $this->header["NumOfPages"];
$this->header["ActivePage"] = intval($this->header["Offset"] / $this->header["Limit"])+1;
}
$this->resource = mysqli_query($this->link, $this->prepareLimit());
if($this->resource instanceof mysqli_result) {
$this->header["NumRows"] = mysqli_affected_rows($this->link);
$this->header["NumFields"] = mysqli_field_count($this->link);
$keysFlags = array_values($this->flags);
$keysTypes = array_values($this->types);
foreach(mysqli_fetch_fields($this->resource) as $key => $object) {
$flags = decbin($object->flags);
$perspectiveName = self::camelize($object->name . 'Of' . ucfirst($object->orgtable));
$this->header["Perspectives"][$perspectiveName] = $object->orgtable.".".$object->name;
$this->header["FieldPositions"][$perspectiveName] = $key;
$this->header["FieldTypes"][$perspectiveName] = $keysTypes[$object->type];
$this->header["FieldLengths"][$perspectiveName] = $object->length;
$this->header["ColumnNames"][] = $object->orgtable.".".$object->name;
$this->header["FieldNames"][] = $perspectiveName;
$this->header["TableNames"][$object->orgtable] = self::camelize(ucfirst($object->orgtable));;
for($i=0;$i<strlen($flags);$i++) {
if($flags{$i}==="1" and $keysFlags[$i]!=""){
$this->header["FieldFlags"][$perspectiveName][] = $keysFlags[$i];
}
}
}
if($this->header["NumRows"] > 0) {
$tableAliases = array();
$i=0;
while($row=mysqli_fetch_object($this->resource)) {
$colIncrement=0;
foreach($row as $name => $value) {
$object = mysqli_fetch_field_direct($this->resource, $colIncrement);
$phpName = self::camelize($object->name . 'Of' . ucfirst($object->orgtable));
if($rowIncrement==0) {
$this->header["TableAliases"][$phpName] = $object->table;
$this->header["FieldAliases"][$phpName] = $object->name;
$this->header["FieldAttributes"][$phpName]["decimals"] = $object->decimals;
$this->header["FieldAttributes"][$phpName]["charset"] = $object->charsetnr;
$this->header["FieldAttributes"][$phpName]["max_length"] = $object->max_length;
$this->header["FieldAttributes"][$phpName]["def"] = $object->def;
}
$this->properties[$i][$phpName] = $value;
$colIncrement++;
}
$i++;
$this->header["AmountOfRows"] = $i;
}
}
}
mysqli_close($this->link);
return $this;
}
private static function camelize($string) {
$replace = str_replace(" ", "", ucwords(str_replace("_", " ", $string)));
return $replace;
}
private static function underscore($string) {
$tmp = self::replace($string, array('/([A-Z]+)([A-Z][a-z])/' => '\\1_\\2', '/([a-z\d])([A-Z])/' => '\\1_\\2'));
return strtolower($tmp);
}
private function replace($search, $replacePairs) {
return preg_replace(array_keys($replacePairs), array_values($replacePairs), $search);
}
public function getListByPosition($offset) {
$array = array();
if(is_integer($offset) and !empty($this->header["NumFields"]) and $offset >= 0 and $offset < $this->header["NumFields"]) {
$colName = $this->header["ColumnNames"][$offset];
$perspectives = array_flip($this->header["Perspectives"]);
$colName = isset($perspectives[$colName]) ? $perspectives[$colName] : self::camelize($colName);
foreach($this->properties as $propKey => $property) {
$array[$propKey] = $property[$colName];
}
}
return $array;
}
public function getListByName($name) {
$array = array();
$name = self::camelize($name);
if(strpos($name, "Of")) {
$col = (!empty($this->header["Perspectives"][$name])) ? $name : false;
if(is_string($col) and !empty($this->header["FieldPositions"]) and array_key_exists($col, $this->header["FieldPositions"])) {
foreach($this->properties as $propKey => $property) {
$array[$propKey] = $property[$name];
}
}
} else {
if(is_string($name) and !empty($this->header["FieldPositions"]) and array_key_exists(self::underscore($name), $this->header["FieldPositions"])) {
foreach($this->properties as $propKey => $property) {
$array[$propKey] = $property[$name];
}
}
}
return $array;
}
public function __call($funcName, $args) {
$methodType = substr($funcName, 0, 3);
switch((substr($funcName, 0, 2))) {
case "at":
case "by":
case "of":
case "is":
$methodType = (substr($funcName, 0, 2));
break;
}
switch($methodType) {
case "len":
$headerPart = "FieldLengths";
$method = self::camelize(substr($funcName, 3));
break;
case "typ":
$headerPart = "FieldTypes";
$method = self::camelize(substr($funcName, 3));
break;
case "at":
$headerPart = "FieldAttributes";
$method = self::camelize(substr($funcName, 2));
break;
case "by":
$headerPart = "FieldAliases";
$method = self::camelize(substr($funcName, 2));
break;
case "of":
$headerPart = "FieldTypes";
$method = self::camelize(substr($funcName, 2));
break;
case "is":
$headerPart = "FieldFlags";
$method = self::camelize(substr($funcName, 2));
break;
default:
$method = self::camelize(substr($funcName, 3));
break;
}
switch ($methodType)
{
case "len":
case "typ":
if(array_key_exists($method, $this->header[$headerPart])) {
return $this->header[$headerPart][$method];
} else {
return false;
}
case "by":
case "of":
$arg = !isset($this->header["Perspectives"][$args[0]]) ? $this->header["Perspectives"][$args[0]] : $args[0];
if(isset($arg) and array_key_exists($arg, $this->header[$headerPart])) {
return isset($this->header[$headerPart][$arg])==strtolower($method)?true:false;
} else {
return false;
}
case "at":
$arg = !isset($this->header["Perspectives"][$method]) ? $this->header["Perspectives"][$method] : $method;
if(isset($arg) and array_key_exists($arg, $this->header[$headerPart])) {
if(is_array($this->header[$headerPart][$arg]) and isset($args[0]) and isset($this->header[$headerPart][$arg][$args[0]])) {
return $this->header[$headerPart][$arg][$args[0]];
} else {
return $this->header[$headerPart][$arg];
}
} else {
return false;
}
case "is":
$arg = isset($this->header["Perspectives"][$args[0]]) ? $args[0] : $this->header["Perspectives"][$args[0]];
if( isset($arg) and
isset($this->header[$headerPart][$arg]) and
is_array($this->header[$headerPart][$arg]))
{
$part = array_flip($this->header[$headerPart][$arg]);
$flagMethod = self::underscore($method);
if(isset($part[$flagMethod])) {
return true;
} else {
return false;
}
} else {
return false;
}
break;
case "has":
if(array_key_exists($method, $this->header)) {
if(isset($this->header[$method]) and !empty($this->header[$method])) {
return true;
} else {
return false;
}
} elseif(isset($this->properties[$method]) and !empty($this->properties[$method])) {
return true;
} else {
return false;
}
return $this;
case "add":
if(array_key_exists($method, $this->header)) {
if(count($args)>1) {
$this->header[$args[0]][$method] = $args[1];
} else {
$this->header[$method][] = $args[0];
}
} else {
if(count($args)>1) {
$this->properties[$args[0]][$method] = $args[1];
} else {
$this->properties[$method][] = $args[0];
}
}
return $this;
case "set":
if(array_key_exists($method, $this->header)) {
$this->header[$method] = $args[0];
} else {
$this->properties[$method] = $args[0];
}
return $this;
case "get":
if(array_key_exists($method, $this->header)) {
if(isset($args[0]) and isset($this->header[$method][$args[0]]) ) {
return $this->header[$method][$args[0]];
} else {
return $this->header[$method];
}
} else {
if(empty($args) and $this->getNumRows==1 and isset($this->properties[0][$method])) {
return $this->properties[0][$method];
} elseif(isset($args[0]) and isset($this->properties[$args[0]][$method]) ) {
return $this->properties[$args[0]][$method];
} else {
return $this->properties[0][$method];
}
}
break;
}
}
}