<?php
/**
* Query2 is a minimalist MySQL layer which can get maximum of MySQL database in minimal code
*
* @author Adam Zivner <hide@address.com>
* @license http://www.opensource.org/licenses/bsd-license.php New BSD license
* @package Query2
* @link http://query2.0o.cz
*/
defined("QUERY2_VERSION") || define("QUERY2_VERSION", "1.0.1");
class Query2 {
/** @var string - Query string */
public $sql;
/** @var resource - MySQL connection */
public $con; // MySQL connection
/** @var callback - see setLogCallback() */
protected $log_callback = null;
/**
* Create object and optionally connect do database server and select database.
* If no arguments are given, we don't try to connect, if $database is empty, we
* don't select database. See also connect() method for details.
*
* @param string $host
* @param string $login
* @param string $password
* @param string $database
* @param boolean $new_link - enforce creation of the new link
* @param integer $client_flags - see mysql_connect() documentation
*
* @throws Query2Exception - codes 100 (can't connect to server), 101 (can't select database)
* @return Query2
*/
public function __construct($host = '', $login = '', $password = '', $database = '', $new_link = false, $client_flags = 0)
{
if(func_num_args() > 0)
$this->connect($host, $login, $password, $database, $new_link, $client_flags);
}
public function connect($host, $login, $password, $database = '', $new_link = false, $client_flags = 0)
{
if(!$this->con = mysql_connect($host, $login, $password, $new_link, $client_flags))
throw new Query2Exception(100, "Can't connect to database server.");
else if(strlen($database) && !mysql_select_db($database, $this->con))
throw new Query2Exception(101, "Can't select database.");
return $this;
}
/**
* Close database connections
*
* @return boolean - success/failure
*/
public function close()
{
return $this->con && mysql_close($this->con);
}
/**
* Callback is called right after execution of every query. Function should
* expect 3 arguments: success (bool), query (string), elapsed time (in ms)
*
* @param callback $callback
*/
public function setLogCallback($callback) { $this->log_callback = $callback; }
public function getLogCallback() { return $this->log_callback; }
// Transaction handling routines
public function beginTransaction()
{
$this->query("SET AUTOCOMMIT=0");
$this->query("START TRANSACTION");
}
public function commit() { $this->query("COMMIT"); $this->query("SET AUTOCOMMIT=1"); }
public function rollBack() { $this->query("ROLLBACK"); $this->query("SET AUTOCOMMIT=1"); }
public function affectedRows() { return mysql_affected_rows($this->con); }
public function lastInsertId() { return mysql_insert_id($this->con); }
/**
* Compose SQL query from given arguments. Apply modifiers etc.
* Used by query and pquery.
*
* @throws Query2Exception - codes 200 (wrong argument list/order) and 201 (unknown modifier)
* @return string - final, ready to be executed SQL query
*/
public function composeQuery()
{
$args = array(); // SQL chunks/arguments
$first = func_get_arg(0); // first argument
// if first argument is array, take it as a list of arguments
foreach(is_array($first) ? $first : func_get_args() as $arg)
if(is_object($arg)) // is this Query2Builder?
$args = array_merge($args, $first->mergeQuery());
else
$args[] = $arg;
$sql = "";
// Now, let's deal with modifiers
for($i = 0, $argc = count($args); $i < $argc; $i++) {
$part = $args[$i];
if(!is_string($part))
throw new Query2Exception(200, 'Wrong argument list/order to query() function: ' . var_export($args, true));
// find modifiers
preg_match_all("/%(?:%|([a-zA-Z]*)(?![a-zA-Z]))/", $part, $matches, PREG_SET_ORDER);
foreach($matches as $m) {
if($m[0] == '%%')
$arg = '%';
else {
$arg = $args[++$i];
if($arg === null)
$arg = 'NULL';
else if($m[1] == 'i') // integer
$arg = (int) $arg;
else if($m[1] == 'f') // float
$arg = (float) $arg;
else if(strtolower($m[1]) == 's') // string
$arg = "'" . ($m[1] == 's' ? $this->escape($arg) : $arg) . "'";
else if(strtolower($m[1]) == 't') // table or column name
$arg = $this->escapeTableName($arg, $m[1] == 't');
else if($m[1] == 'q'); // insert pure SQL query, no escaping
else if(strtolower($m[1]) == 'in' || strtolower($m[1]) == 'nin') // inserts IN ('a', 'b', 'c')
$arg = $this->in($arg, strtolower($m[1]) == 'in', $m[1] == 'in' || $m[1] == 'nin');
else if(strtolower($m[1]) == 'a') // update modifier, returns name='John', surname='Black'
$arg = $this->assocUpdate($arg, $m[1] == 'a');
else if(strtolower($m[1]) == 'v') // insert modifier, returns (name, surname) VALUES ('John', 'Black')
$arg = $this->assocInsert($arg, $m[1] == 'v');
else if(strtolower($m[1]) == 'va') // INSERT ... ON DUPLICATE KEY UPDATE
$arg = $this->assocInsertUpdate($arg, $m[1] == 'va');
else
throw new Query2Exception(201, "Wrong modifier '$m[1]' to query() function.");
}
// Replace just one occurence (that's why we use preg_replace instead of str_replace)
$part = preg_replace("/$m[0]/", $arg, $part, 1);
}
$sql .= $part . ' ';
}
return trim($sql);
}
/**
* Main Query function. Query is composed by helper function composeQuery()
*
* Arguments can be:
* - chunks of SQL (with modifiers)
* - modifier arguments
* - instances of Query2Builder
* - an array as first argument containing anything from this list
*
* @throws Query2Exception - code 300 (MySQL error) and the same one as composeQuery()
* @return Query2Result|Query2 - Query2Result for SELECT, DESCRIBE etc. and Query2 for INSERT, UPDATE etc.
*/
public function query()
{
$args = func_get_args();
// get pure SQL with composeQuery, call it with exactly the same arguments we got
$this->sql = call_user_func_array(array($this, "composeQuery"), $args);
$started_time = microtime(); // we'll measure how much time execution of a query took
$res = mysql_query($this->sql, $this->con); // here we go!
if($this->log_callback) // calling the callback (useful for logging, performance tuning etc.)
call_user_func($this->log_callback, !mysql_error($this->con), $this->sql, microtime() - $started_time);
if(mysql_error($this->con)) // oops, query failed
throw new Query2Exception(300, mysql_error($this->con), $this->sql);
return is_resource($res) ? new Query2Result($res) : $this;
}
/**
* Prints query and then executes it. It's intended for in-place use instead
* of query, just replace "query(" with "pquery(".
*
* If you just want to get query string (e.g. to write to file), use composeQuery()
*
* @throws Query2Exception - same codes as query()
* @return Query2Result|Query2
*/
public function pquery()
{
$args = func_get_args();
echo call_user_func_array(array($this, "composeQuery"), $args);
return call_user_func_array(array($this, "query"), $args);
}
/**
* Factory method which returns newly created instance of Query2Builder
*
* @return Query2Builder
*/
public function builder()
{
return new Query2Builder();
}
/**
* Escape schema object names. ` is escaped by doubling it, ' and " are not because the don't have to be escaped.
* E.g. table.column => `table`.`column`
*
* @param string $str
* @param boolean $escape
* @return string
*/
public function escapeTableName($str, $escape)
{
if($escape)
$str = str_replace("`", "``", $str);
$arr = explode(".", $str);
return "`" . implode("`.`", $arr) . "`";
}
/**
* @param string $str
* @return string
*/
public function escape($str) { return mysql_real_escape_string($str, $this->con); }
/**
* Escapes value according to its type and $escape
*
* @param mixed $v
* @param boolean $escape
* @return string
*/
protected function escapeValue($v, $escape)
{
if($v === null)
return "NULL";
else if(is_object($v) && get_class($v) == "Query2Statement")
return $v->statement;
else
return "'" . ($escape ? $this->escape($v) : $v) . "'";
}
/**
* Generate IN('a', 'b', 'c') from given array. It works also with an empty array.
*
* @param array $arr
* @param boolean $in - is it IN or NOT IN?
* @param boolean $escape
* @return string - either "FALSE" or "IN(...)"
*/
protected function in($arr, $in, $escape)
{
if(count($arr)) {
foreach($arr as $key => $value)
$arr[$key] = $this->escapeValue($value, $escape);
return ($in ? '' : 'NOT ') . 'IN (' . implode(", ", $arr) . ')';
}
else
return $in ? 'AND FALSE' : 'IS NOT NULL';
}
/**
* Transforms associative array into something like this:
* name = 'John', surname = 'Black', age = '33'
*
* @params array $assoc
* @params boolean $escape - escape contents?
* @return string
*/
protected function assocUpdate($assoc, $escape)
{
$arr = array();
foreach($assoc as $key => $value)
$arr[] = "`" . $this->escape($key) . "` = " . $this->escapeValue($value, $escape);
return implode(", ", $arr);
}
/**
* Transforms associative array into something like this:
* (name, surname, age) VALUES ('John', 'Black', '33')
*
* @params array $assoc
* @params boolean $escape - escape contents?
* @return string
*/
protected function assocInsert($arr, $escape)
{
$vars = $ret = array();
foreach((isset($arr[0]) && is_array($arr[0])) ? $arr : array($arr) as $assoc) {
$vars = $values = array();
foreach($assoc as $key => $value) {
$vars[] = "`" . $this->escape($key) . "`";
$values[] = $this->escapeValue($value, $escape);
}
$ret[] = "(" . implode(", ", $values) . ")";
}
return "(" . implode(", ", $vars) . ") VALUES " . implode(", ", $ret);
}
/**
* This method implements useful INSERT ... ON DUPLICATE KEY UPDATE.
* There are two ways to use this function, simple and "complex".
* See http://query2.0o.cz for explanation.
*
* @param array $arr - two possibilities
* @param boolean $escape - escape content?
* @return string - SQL part
*/
protected function assocInsertUpdate($arr, $escape)
{
$complex = isset($arr["data"]) && is_array($arr["data"]);
$update = ($complex && isset($arr["update"])) ? $arr["update"] : array_keys($complex ? $arr["data"] : $arr);
foreach($update as $idx => $name) // we'll do it in-place
$update[$idx] = "`$name` = VALUES(`$name`)";
if($complex && isset($arr["auto_increment"]))
$update[] = "`$arr[auto_increment]` = LAST_INSERT_ID(`$arr[auto_increment]`)";
return $this->assocInsert($complex ? $arr["data"] : $arr, $escape) . " ON DUPLICATE KEY UPDATE " . implode(", ", $update);
}
}
class Query2Exception extends Exception {
public $code, $error, $sql;
function __construct($code, $error, $sql = "")
{
$this->code = $code; // error code (e.g. 300)
$this->error = $error;
$this->sql = $sql;
parent::__construct($code . " " . $error . (strlen($sql) ? ": $sql" : ""), $code);
}
}
/**
* QueryStatement provides a way to use unescaped value like (MySQL NOW()) in
* otherwise escaped role (in %v, %V and %a, %A).
*/
class Query2Statement {
public $statement;
function __construct($statement)
{
$this->statement = $statement;
}
}