Skip to content

Instantly share code, notes, and snippets.

@paranoiq
Created October 11, 2011 10:29
Show Gist options
  • Save paranoiq/1277781 to your computer and use it in GitHub Desktop.
Save paranoiq/1277781 to your computer and use it in GitHub Desktop.
radostnější řazení paremetrů v Nette\Database\Connection

NENÍ DOSTATECNĚ OTESTOVÁNO!

nový SqlPreprocessor pro Nette\Database

funkčnost SQL preprocesoru usnadňuje zápis a čtení přímo psaných dotazů a automatické generování dotazů u metod query(), exec() a fetch…()

má dva režimy:

  • režim 'placeholder' (zpětně kompatibilní), ve kterém je prvním parametrem celý SQL dotaz a pozice argumentů je v něm označena znakem ?, stejně jako v PDO a současném Nette\Database

např.: $db->exec("UPDATE x SET y = ? WHERE z = ?", $y, $z);

  • režim 'alternate', ve kterém se útržky SQL dotazu střídají s argumenty. pozice argumentů není vyznačena nijak. není to totiž třeba. zápis kódu v tomto režimu je mnohem přehlednější, hlavně při větším množství parametrů

např.: $db->exec("UPDATE x SET y = ", $y, "WHERE z = ", $z);

o který režim jde pozná podle toho, zda je ve statementu placeholder ?

umí chytře rozeznávat jak naložit s argumentem typu pole. režimy zpracování jsou:

  • insert: pro INSERT a REPLACE (a multi-insert)
  • assoc: pro UPDATE a SET
  • where: pro použití za klauzulí WHERE a HAVING nebo za některým z logických operátorů. generuje trojice klíč, operátor (=, LIKE, IS NULL a IN), hodnota, spojené operátorem AND
  • order: pro ORDER BY a GROUP BY. klíč je název sloupce, hodnota je směr řazení - FALSE a <= 0 je DESC
  • in: pro použití za IN, ANY, SOME, ALL. generuje seznam hodnot v závorce
  • pokud není rozpoznám žádný vhodný mód, vytvoří se seznam hodnot oddělený čárkou. např. pro: $db->exec("CALL fnc(?)", $arr);

dál preprocesor ošetřuje typ hodnoty (int) pro LIMIT, OFFSET a TOP

SqlFragment je obdoba SqlLiteral, ale krom samotného literálu mu lze předat i argumenty. lze tedy jednodušeji vytvářet správně naformátované a ošetřené útržky SQL kódu

<?php
namespace Dogma\Database;
/**
* query(), fetch?(), exec():
* New preprocesor makes dibi-like syntax possible.
* Question mark (?) is not required and *not allowed* in alternative syntax.
* eg. $db->exec("UPDATE x SET y = ", $y, "WHERE z = ", $z);
*/
class Connection extends \Nette\Database\Connection {
/** @var SqlPreprocessor */
private $preprocessor;
public function __construct($dsn, $username = NULL, $password = NULL, array $options = NULL, Reflection\DatabaseReflection $databaseReflection = NULL) {
parent::__construct($dsn, $username, $password, $options, $databaseReflection);
$this->preprocessor = new SqlPreprocessor($this);
}
public function queryArgs($statement, $params) {
if ($this->preprocessor && (count($params) || strpos($statement, ':') !== FALSE))
list($statement, $params) = $this->preprocessor->process($statement, $params);
return $this->prepare($statement)->execute($params);
}
/**
* Alias for beginTransaction()
*/
public function begin() {
return $this->beginTransaction();
}
}
<?php
namespace Dogma\Database;
class SqlFragment {
/** @var string */
public $statement;
/** @var array */
public $args = array();
/**
* SQL query Fragment.
* @param string statement
* @param mixed [parameters, ...]
*/
public function __construct($statement) {
$args = func_get_args();
$this->statement = (string) array_shift($args);
$this->args($args);
}
}
<?php
namespace Dogma\Database;
use Nette\Utils\Strings;
use Nette\Database\SqlLiteral;
class SqlPreprocessor {
/** @var Connection */
private $connection;
/** @var ISupplementalDriver */
private $driver;
/** @var string */
private $sql;
/** @var array of parameters to be processed by PDO */
private $remaining;
public function __construct(Connection $connection) {
$this->connection = $connection;
$this->driver = $connection->getSupplementalDriver();
}
public function process($sql, $params) {
$this->sql = '';
$this->remaining = array();
if (strpos($sql2 = Strings::replace($sql, '~\'.*?\'|".*?"|\?~s',
array($this, 'splitCb')), chr(0)) !== FALSE) { // placeholder mode
$args = $params;
$bits = explode(chr(0), $sql2);
} else { // alternate mode
$args = array();
$bits[0] = $sql;
foreach ($params as $param) {
if (count($bits) > count($args)) {
$args[] = $param;
} else {
$bits[] = $param;
}
}
}
foreach ($bits as $i => $sql) {
$this->sql .= $sql;
if (!array_key_exists($i, $args)) continue;
$this->processArg($args[$i], $sql);
}
$this->sql = Strings::replace($this->sql, '~\'.*?\'|".*?"|:[a-zA-Z0-9_]+:~s', array($this, 'substituteCb'));
return array($this->sql, $this->remaining);
}
/** @internal */
public function splitCb($m) {
$m = $m[0];
if ($m[0] === "'" || $m[0] === '"') { // string
return $m;
} elseif ($m[0] === '?') { // placeholder
return chr(0);
}
}
/** @internal */
public function substituteCb($m) {
$m = $m[0];
if ($m[0] === "'" || $m[0] === '"') { // string
return $m;
} elseif ($m[0] === ':') { // substitution
$s = substr($m, 1, -1);
return isset($this->connection->substitutions[$s]) ? $this->connection->substitutions[$s] : $m;
}
}
/**
* Format each argument depending on its context.
* @param mixed
* @param string
*/
private function processArg($arg, $sql) {
if ((is_array($arg) || $arg instanceof \Traversable) && $mode = $this->detectArrayMode($sql)) {
$this->processArray($arg, $mode);
} elseif (Strings::match($sql, '/(?:LIMIT|OFFSET|TOP)\\s*$/i')) {
$this->sql .= (int) $arg;
} else {
$this->sql .= $this->formatValue($arg);
}
}
/**
* Detect array mode from previous part of statement.
* @param string
* @return string
*/
private function detectArrayMode($sql) {
$sql = strtoupper($sql);
if (Strings::match($sql, '/(?:SET|UPDATE)\\s*$/')) {
return 'assoc';
} elseif (Strings::match($sql, '/\\s(?:WHERE|HAVING|AND|&&|OR|\\|\\||XOR|NOT|!)[(\\s]*$/')) {
return 'where';
} elseif (Strings::match($sql, '/\\s(?:ANY|SOME|ALL|IN)\\s*$/')) {
return 'in';
} elseif (Strings::match($sql, '/\\s(?:ORDER\\sBY|GROUP\\sBY)\\s*$/')) {
return 'order';
} elseif (Strings::match($sql, '/INSERT|REPLACE/')) {
return 'insert';
} else {
return '';
}
}
/**
* Process array argument.
* @param array
* @param string
*/
private function processArray($array, $mode) {
$vx = $kx = array();
if ($mode === 'insert') { // (key, key, ...) VALUES (value, value, ...)
// multiinsert?
reset($array);
if (is_array(current($array)) || current($array) instanceof \Traversable) {
$all = $array;
$array = array_shift($all);
}
foreach ($array as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
$this->sql .= '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
if (isset($all)) {
foreach ($all as $array) {
$this->processArray($array, 'values');
}
}
} elseif ($mode === 'values') { // , (value, value, ...)
$this->sql .= ', (' . $this->formatValue($array) . ')';
} elseif ($mode === 'in') { // (value, value, ...)
$this->sql .= '(' . $this->formatValue($array) . ')';
} elseif ($mode === 'assoc') { // key=value, key=value, ...
foreach ($array as $k => $v) {
$vx[] = $this->driver->delimite($k) . ' = ' . $this->formatValue($v);
}
$this->sql .= implode(', ', $vx);
} elseif ($mode === 'order') { // key, key DESC, ...
foreach ($array as $k => $v) {
$desc = $v === FALSE || (is_numeric($v) && $v <= 0);
$vx[] = $this->driver->delimite($k) . ($desc ? ' DESC' : '');
}
$this->sql .= implode(', ', $vx);
} elseif ($mode === 'where') { // key=value AND key=value AND ...
foreach ($array as $k => $v) {
if (is_string($v)) {
$vx[] = $this->driver->delimite($k) . ' LIKE ' . $this->formatValue($v);
} elseif (is_null($v)) {
$vx[] = $this->driver->delimite($k) . ' IS NULL';
/* // MySQL 5+, PostgreSQL
} elseif (is_bool($v)) {
$vx[] = $this->driver->delimite($k) . ($v ? ' IS TRUE' : ' IS FALSE');
*/
} elseif (is_array($v) || $v instanceof \Traversable) {
$vx[] = $this->driver->delimite($k) . ' IN (' . $this->formatValue($v) . ')';
} else {
$vx[] = $this->driver->delimite($k) . ' = ' . $this->formatValue($v);
}
}
$this->sql .= '(' . implode(' AND ', $vx) . ')';
}
}
/**
* Format a value for use in statement.
* @param mixed
* @return string
*/
private function formatValue($value) {
if (is_string($value)) {
if (strlen($value) > 20) {
$this->remaining[] = $value;
return '?';
} else {
return $this->connection->quote($value);
}
} elseif (is_int($value)) {
return (string) $value;
} elseif (is_float($value)) {
return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
} elseif (is_bool($value)) {
$this->remaining[] = $value;
return '?';
} elseif (is_null($value)) {
return 'NULL';
} elseif (is_array($value) || $value instanceof \Traversable) {
// non-associative; value, value, value
$vx = array();
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($value instanceof \DateTime) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof SqlLiteral) {
return $value->value;
} elseif ($value instanceof SqlFragment) {
$pp = new self/*static*/($this->connection);
list($sql, $remaining) = $pp->process($value->statement, $value->params);
if ($remaining) foreach ($remaining as $r) {
$this->remaining[] = $r;
}
return $sql;
} else {
$this->remaining[] = $value;
return '?';
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment