hybrid7 libraries: Artifact [17885954f5]

Artifact 17885954f53b0bb1c3f87b084627621c7e92ae1a:

File db.php — part of check-in [1cfd351bc9] at 2015-01-07 15:32:08 on branch trunk — Silently return NULL for absent ->column data from first result row. (user: mario size: 19127)
<?php
/**
 * title: PDO wrapper
 * description: Hybrid db() interface for extended SQL parameterization and result folding
 * api: php
 * type: database
 * version: 0.9.9
 * depends: php:pdo
 * license: Public Domain
 * author: Mario Salzer
 * doc: http://fossil.include-once.org/hybrid7/wiki/db
 *
 *
 * QUERY
 *
 * Provides simple database queries with enumerated / named parameters. It's
 * flexible in accepting plain PDO scalar arguments or arrays. Array args get
 * merged, or transcribed when special placeholders are present:
 *
 *   $r = db("SELECT * FROM tbl WHERE a>=? AND b IN (??)", $a, array($b, $c));
 *
 * Extended placeholder syntax and common uses:
 *
 *  ┌─────┬──────────────┬─────────┬────────────────────────────────────────┐
 *  │PlcH │  Expands to  │ Context │                Purpose                 │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ ??  │ ?, ?, ?      │ IN      │ Expansion of indexed arrays.           │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ ::  │ :a, :b       │ VALUES  │ Expand  associative  arrays into named │
 *  │     │              │         │ value list.                            │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ :?  │ ‘a‘, ‘b‘     │ Names   │ Interpolates key names (does  not  pa‐ │
 *  │     │              │         │ rameterize values).                    │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ :&  │ x=:x         │ WHERE   │ Becomes AND‐joined name=:value list.   │
 *  │     │  AND y=:y    │         │                                        │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ :|  │ x=:x         │ WHERE   │ Becomes OR‐joined name=:value list.    │
 *  │     │  OR y=:y     │         │                                        │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ :,  │ x=:x, y=:y   │ UPDATE  │ Becomes comma‐joined name=:value list. │
 *  ├─────┼──────────────┼─────────┼────────────────────────────────────────┤
 *  │ :*  │ SQL + params │ Expr    │ Expression  placeholder, where the as‐ │
 *  │     │              │         │ sociated argument  should  contain  an │
 *  │     │              │         │ array  ["AND  foo  IN (??)", $params]. │
 *  │     │              │         │ Which  only  interpolates  if  $params │
 *  │     │              │         │ contains any value. Can be nested.     │
 *  └─────┴──────────────┴─────────┴────────────────────────────────────────┘
 *
 * Configurable {TOKENS} from db()->tokens[] are also substituted..
 *
 *
 * RESULT
 *
 * The returned result can be accessed as single data row, when fetching just
 * one:
 *       $result->column
 *       $result["column"]
 *
 * Or just traversed row-wise as usual by iteration
 *
 *       foreach (db("...") as $row)
 *
 * Alternatively by object-wrapping (unlike plain PDO->fetchObject() this
 * hydrates the object using its normal constructor) the result set with:
 *
 *       foreach ($result->into("ArrayObject") as $row)
 *
 * Yet all PDO ->fetch() methods are still available for use on the result obj.
 *
 *
 * CONNECT  
 *
 * The db() interface binds the global "$db" variable. It ought to be
 * initialized with:
 *
 *       db(new PDO(...));
 *
 * It's wrapped PDO handle can also be retrieved with just $pdo = db(); then.
 * 
 *
 * RECORD WRAPPER
 *
 * There's also a simple table data gateway wrapper implemented here. It
 * accepts db() queries for single entries, and allows ->save()ing back, or
 * to ->delete() records.
 * You should only use it in conjuction with sql2php and its simpler wrappers.
 *
 */



/**
 * Hybrid instantiation / query function.
 * Couples `$db` in the shared/global scope.
 *
 */
function db($sql=NULL, $params=NULL) {

    #-- shared PDO handle
    $db = & $GLOBALS["db"];   // Alternatively could be just static to hide it behind db()
    
    #-- open database
    if (is_object($sql)) {
    
        // use passed param
        $db = new db_wrap($sql);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, is_int(stripos($db->getAttribute(PDO::ATTR_DRIVER_NAME), "mysql")));
        $db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
        $db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
    
        // save settings
        $db->tokens = array("PREFIX"=>""); // or reference global $config
        $db->in_clause = $db->getAttribute(PDO::ATTR_DRIVER_NAME) == "sqlite"
                     and $db->getAttribute(PDO::ATTR_CLIENT_VERSION) < 3.6;
    }
    
    #-- return PDO handle
    elseif (empty($sql)) {
        return $db;
    }
    
    #-- just dispatch to the wrapper
    else {
        $args = array_slice(func_get_args(), 1);
        return $db($sql, $args);
    }

}


/**
 * Binds PDO handle, allows original calls and extended placeholder use.
 *
 */
class db_wrap {


    /**
     * Keep PDO handle.
     *
     */
    public $pdo = NULL;
    function __construct($pdo) {
        $this->pdo = $pdo;
    }


    /**
     * Chain to plain PDO if any other method invoked.
     *
     */
    function __call($func, $args) {
        return call_user_func_array(array($this->pdo, $func), $args);
    }


    /**
     * Prepares and executes query after extended placeholders and parameter unpacking.
     *
     */
    function __invoke($sql, $args=array()) {

        // $sql may contain associative SQL parts and parameters
        if (is_array($sql)) {
            list($sql, $args) = $this->join($sql);
        }

        // reject plain strings in SQL
        if (strpos($sql, "'")) {
            trigger_error("SQL query contained raw data. DO NOT WANT", E_USER_WARNING);
            return NULL;
        }

        // flatten array arguments and extended placeholders
        list($sql, $args) = $this->fold($sql, $args);
        
        // placeholders
        if (!empty($this->tokens) && strpos($sql, "{")) {
            $sql = preg_replace_callback("/\{(\w+)(.*?)\}/", array($this, "token"), $sql);
        }
        // older SQLite workaround
        if (!empty($this->in_clause) && strpos($sql, " IN (")) { // only for ?,?,?,? enum params
            $sql = preg_replace_callback("/(\S+)\s+IN\s+\(([?,]+)\)/", array($this, "in_clause"), $sql);
        }
        // just debug output
        if (!empty($this->test)) { 
            print json_encode($args)." => " . trim($sql) . "\n"; return;
        }
    
        // run
        $s = $this->prepare($sql)
        and
        $r = $s->execute($args);

        // wrap        
        return $s && $r ? new db_result($s) : $s;
    }


    /**
     * Expands the extended placeholders and flattens arrays from parameter list.
     *
     */
    function fold($sql, $args) {
    
        // output parameter list
        $flat_params = array();
        
        #-- flattening sub-arrays (works for ? enumarated and :named params)
        foreach ($args as $i=>$a) {

            // subarray that corresponds to special syntax placeholder?
            if (is_array($a)
            and preg_match("/  \?\?  |  : [?:*  &,|]  /x", $sql, $capture, PREG_OFFSET_CAPTURE))
            {
                list($token, $pos) = current($capture);

                // placeholder substitution, possibly changing $a params
                $replace = $this->{self::$expand[$token]}($a);

                // update SQL string
                $sql = substr($sql, 0, $pos) . $replace . substr($sql, $pos + strlen($token));
            }

            // unfold into plain parameter list
            if (is_array($a)) {
                $flat_params = array_merge($flat_params, $a);
            }
            else {
                $flat_params[] = $a;
            }
        }
        
        return array($sql, $flat_params);
    }


    /**
     * Syntax expansion callbacks
     *
     */
    static $expand = array(
        "??" => "expand_list",
        ":?" => "expand_keys",
        "::" => "expand_named",
        ":," => "expand_assoc_comma",
        ":&" => "expand_assoc_and",
        ":|" => "expand_assoc_or",
        ":*" => "expand_expr",
    );

    // ?? array placeholders
    function expand_list($a) {
        return implode(",", array_fill(0, count($a), "?"));
    }

    // :? name placeholders, transforms list into enumerated params
    function expand_keys(&$a) {
        $enum = array_keys($a) === range(0, count($a) - 1);
        $r = implode(",", $this->db_identifier($enum ? $a : array_keys($a), "`"));
        $a = array();
        return $r;
    }

    // :: becomes :named,:value,:list
    function expand_named($a) {
        return ":" . implode(",:", $this->db_identifier(array_keys($a)) );
    }

    // for :, expand COMMA-separated key=:key,bar=:bar associative array
    function expand_assoc_comma($a, $fill = ", ", $replace=array()) {
        foreach ($this->db_identifier(array_keys($a)) as $key) {
            $replace[] = "`$key` = :$key";
        }
        return implode($fill, $replace);
    }
    // for :& AND-chained assoc foo=:foo AND bar=:bar
    function expand_assoc_and($a) {
        return $this->expand_assoc_comma($a, " AND ");
    }
    // for :| OR-chained assoc foo=:foo OR bar=:bar
    function expand_assoc_or($a) {
        return $this->expand_assoc_comma($a, " OR ");
    }

    /**
     * While :* holds an optional expression and subvalue list. Which only gets
     * interpolated if the params list is non-empty.
     * which may be provided as alternative pairs ["AND :&", $and, "OR :|", $or].
     * 
     * While each value list should be a list itself, it's common to just pass
     * one array param for a single ::/?? extended placeholder. (Which then will
     * be auto-wrapped.)
     *
     */
    function expand_expr(&$a) {
        foreach (array_chunk($a, 2) as $pair) if (list($sql, $args) = $pair) {
            // substitute subexpression as if it were a regular SQL string
            if (is_array($args) && count($args)) {
                // rewrap simple value lists into param-args list
                $args = array_sum(array_map("is_array", $args)) ? $args : array($args);
                list ($replace, $a) = $this->fold($sql, $args);
                return $replace;
            }
        }
        $a = array();  // else replace with nothing and omit current data for flattened $params2
    }

    
    
    /**
     * For readability input SQL may come as associative clause => params list.
     *   ["SELECT ?" => $num,
     *    "FROM :?"  => [$tbl],
     *    "WHERE :&" => $match
     *   ]
     * Which is separated here into keys as $sql string and $args from values.
     *
     */
    function join($sql_args, $sql="", $args=array()) {
        foreach ($sql_args as $key=>$val) {
            // Key itself is not an SQL part
            if (is_int($key)) {
                // Value then can be an SQL string, or a param
                if (is_string($val)) {
                    $sql .= $val;
                }
                else {
                    $args[] = $val;
                }
            }
            // Plain SQL => Value
            else {
                $sql .= $key . "\n  ";
                $args[] = $val;
            }
        }
        return array($sql, $args);
    }



    // This is a restrictive filter function for column/table name identifiers.
    // Can only be foregone if it's ensured that none of the passed named db() $arg keys originated from http/user input.
    function db_identifier($as, $wrap="") {
        return preg_replace(array("/[^\w\d_.]/", "/^|$/"), array("_", $wrap), $as);
    }

    
    // Regex callbacks
    function token($m) {
        list($m, $tok, $ext) = $m;
        return isset($this->token[$tok]) ? $this->token[$tok].$ext : $this->token["$tok$ext"];
    }
    function in_clause($m) {
        list($m, $key, $vals) = $m;
        $num = substr_count($vals, "?");
        return "($key=" . implode("OR $key=", array_fill(0, $num, "? ")) . ")";
    }

}



/**
 * Allows traversing result sets as arrays or hydrated objects,
 * or fetches only first result row on ->column_name accesses.
 *
 */
class db_result extends ArrayObject implements IteratorAggregate {

    protected $results = NULL;

    function __construct($results) {
        parent::__construct(array(), 2);
        $this->results = $results;
    }
    // used as PDO statement
    function __call($func, $args) {
        return call_user_func_array(array($this->results, $func), $args);
    }

    // Single column access
    function offsetGet($name) {
    
        // get first result, transfuse into $this
        if (is_object($r = $this->results)) {
            unset($this->results);
            if ($row = $r->fetch()) {
                $this->exchangeArray($row);
            }
            // no row returned, silently return
            else {
                return NULL;
            }
        }
        
        // suffice __get
        return parent::offsetGet($name);
    }

    // Just let PDOStatement handle the Traversable
    function getIterator() {
        return isset($this->results)
             ? $this->results
             : new ArrayIterator($this);
    }

    // Or hydrate specific result objects ourselves
    function into() {
        $into = func_get_args() ?: array("ArrayObject", 2);
        return new db_result_iter($this->results, $into);
    }
}


/**
 * More wrapping for hydrated iteration.
 *
 */
class db_result_iter implements Iterator {

    // Again keep PDOStatement and class specifier
    protected $results = NULL;
    protected $into = array();
    function __construct($results, $into) {
        $this->results = $results;
        $this->into = $into;
    }
    
    // Iterator just fetches and converts on traversal
    protected $row = NULL;
    public function current()
    {
        list($class, $a2, $a3, $a4, $a5) = array_merge($this->into, [NULL, NULL, NULL, NULL]);
        return new $class($this->row, $a2);
    }
    function valid() {
        return !empty($this->row = $this->results->fetch());
    }
    
    // unused for normal `foreach` operation
    function next() { return NULL; }
    function rewind() { return NULL; }
    function key() { return NULL; }
}



/**
 * Table data gateway. Don't use directly.
 *
 * Keeps ->_meta->table name and ->_meta->fields,
 * uses extendable tables with [ext] field serialization.
 * Doesn't cope with table joins. (yet?)
 *
 * Allows to ->set() and ->save() record back.
 */
class db_record /*resembles db_result*/ extends ArrayObject {

    // this is not purposelessly private, but to not pollute (array) typecasts with decorative data
    private $_meta;

    // initialize from db() result or array
    function __construct($results, $table, $fields, $keys) {
        
        // meta
        $this->_meta = new stdClass();
        $this->_meta->table = $table;
        $this->_meta->fields = array_unique(array_merge(array_keys($fields), array_keys($results)));
        $this->_meta->keys = $keys;
        
        // db query result
        if (is_array($results)) {
            $this->_meta->new = 1;  // instantiate from defaults or given row values
        }
        else {
            //if (is_string($results)) {   // queries are handled in wrapper
            //    $results = db($results);
            //}
            $results = $results->fetch();  // just get first result row
            $this->_meta->new = 0;
        }

        // unfold .ext
        if ($this->_meta->ext = isset($results["ext"])) {
            $results = array_merge($results, unserialize($results["ext"]));
        }

        // copy data
        // and turn object==array
        parent::__construct((array)$results, 2); //ArrayObject::ARRAY_AS_PROPS

        // fluent (hybrid constructor wrapper)
        return $this;
    }
    
    // set field
    function set($key, $val) {
        $this->{$key} = $val;
        return $this;  // fluent
    }

    // store table back to DB
    function save($row=NULL) {
    
        // source
        if (empty($row)) {
            $row = $this->getArrayCopy();
        }
        else {
            $row = array_merge($this->getArrayCopy(), is_array($row) ? $row : $row->getArrayCopy());
        }
    
        // fold .ext
        if ($this->_meta->ext) {
            $ext = array();
            foreach ($row as $key=>$val) {
                if (!in_array($key, $this->_meta->fields)) {
                    $ext[$key] = $val;
                    unset($row[$key]);
                }
            }
            $row["ext"] = serialize($ext);
        }
        
        // store
        if ($this->_meta->new) {
            db("INSERT INTO {$this->_meta->table} (:?) VALUES (??)", $row, $row);
            $this->_meta->new = 0;
        }
        // update
        else {
            $keys = $this->keys($row, 1);
            db("UPDATE {$this->_meta->table} SET :, WHERE :&", $row, $keys);
        }
        
        return $this;  // fluent
    }

    // split $keys from $row/$this
    function keys(&$row, $unset=0) {
        $keys = array();
        foreach ($this->_meta->keys as $key) { 
            $keys[$key] = $row[$key];
            if ($unset) unset($row[$key]);
        } 
        return $keys;
    }
    
    // oh noooes
    function delete() {
        db("DELETE FROM {$this->_meta->table} WHERE :&", $this->keys($this));
        return $this;  // well
    }
}




?>