radostnější řazení paremetrů v Nette\Database\Connection

_info.md

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

Connection.php

<?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();
    }
    
}

SqlFragment.php

<?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);
    }
    
}

SqlPreprocessor.php

<?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 '?';
        }
    }
    
}