<?php
/**
 * title: database
 * description: basic db() interface for parameterized SQL and result folding
 * api: php
 * type: database
 * version: 0.7
 * depends: pdo
 * license: Public Domain
 * author: Mario Salzer
 * url: http://php7framework.sourceforge.net/
 *
 *
 * QUERY
 *
 * Provides simple database queries with enumerated or named parameters. It's
 * flexible in accepting scalar arguments and 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));
 *
 * Two ?? are used for interpolating arrays, which is useful for IN clauses.
 * The placeholder :? interpolates key names (doesn't add values).
 * And :& or :, or :| become a name=:assign list grouped by AND, comma, OR.
 * Whereas :: turns into a simple :named,:value,:list (for IN clauses).
 * Also configurable {TOKENS} are replaced automatically (db()->tokens[]).
 *
 * RESULT
 *
 * The returned result can be accessed as single data row, with $data->column
 * or using $data["column"].
 * Or if it's a result list, foreach() can iterate over all returned rows.
 * And all PDO ->fetch() methods are still available for use on the result obj.
 * ArrayObjects cannot be used like real arrays in all contexts; typecasting
 * the data out is not possible, in string context curly braces "{$a->x}" are
 * necessary, and in sub-loops needed object syntax "foreach ($a->subarray as)"
 *
 * CONNECT  
 *
 * The db() interface utilizes the global "$db" variable. Which could also be
 * instantiated separately or using:
 * db("connect", array("mysql:host=localhost;dbname=test","username","password"));
 *
 * 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.
 *
 */



/**
 * SQL query.
 *
 */
function db($sql, $params="...") {
    global $db;
    
    #-- open database
    if ($sql == "connect") {
    
        // DSN
        $params = is_array($params) ? array_values($params) : array($params,"","");
        $db = new PDO($params[0], $params[1], $params[2]);
        
        // save settings
        $db->tokens = array("PREFIX"=>""); // or reference global $config
        $db->broken = strstr($params[0], "sqlite");
    }
    
    #-- singleton use
    elseif (empty($sql)) {
        return $db;
    }
    
    #-- reject SQL
    elseif (strpos($sql, "'")) {
        trigger_error("SQL query contained raw data. DO NOT WANT", E_USER_WARNING);
    }
    
    #-- execute SQL
    else {
    
        #-- get $params
        $params2 = array();
        $args = func_get_args();
        array_shift($args);

        #-- flattening sub-arrays (works for ? enumarated and :named params)
        foreach ($args as $i=>$a) {
            if (is_array($a)) {
                $enum = is_int(end(array_keys($a)));

                // subarray corresponds to special syntax placeholder?
                if (preg_match("/\?\?|:\?|::|:&|:,|&\|/", $sql, $uu, PREG_OFFSET_CAPTURE)) {
                    list($token, $pos) = $uu[0];
                    switch ($token) {
                        case "??":  // replace ?? array placeholders
                            $replace = implode(",", array_fill(0, count($a), "?"));
                            break;
                        case ":?":  // and :? name placeholder, transforms list into enumerated params
                            $replace = implode(",", db_identifier($enum ? $a : array_keys($a)) );
                            $enum = 1;  $a = array();   // do not actually add values
                            break;
                        case "::":  // inject :named,:value,:list
                            $replace = ":" . implode(",:", db_identifier(array_keys($a)) );
                            break;
                        case ":&":  // associative params - becomes "key=:key AND .."
                        case ":,":  // COMMA-separated
                        case ":|":  // OR-separated
                            $fill = array(":&"=>" AND ", ":,"=>" , ", ":|"=>" OR ");
                            $replace = array(); foreach (db_identifier(array_keys($a)) as $key) { $replace[] = "$key=:$key"; }
                            $replace = implode($fill[$token], $replace);
                    }
                    // update SQL string
                    $sql = substr($sql, 0, $pos) . $replace . substr($sql, $pos + strlen($token));
                }

                // unfold
                if ($enum) {
                   $params2 = array_merge($params2, $a);
                } else {
                   $params2 = array_merge($params2, $a);
                }
            }
            else {
                $params2[] = $a;
            }
        }

        #-- placeholders
        if ($db->tokens && strpos($sql, "{")) {
            $sql = preg_replace("/\{(\w+)(.*?)\}/e", 'isset($db->token["$1"]) ? $db->token["$1"]."$2" : @$db->token["$1$2"]', $sql);
        }
        
        #-- SQL incompliance workarounds
        if ($db->broken && strpos($sql, " IN (")) { // only for ?,?,?,? enum params
            $sql = preg_replace("/(`?\w+`?) IN \(([?,]+)\)/e", '"($1=" . implode("OR $1=", array_fill(0, 1+strlen("$2")/2, "? ")) . ")"', $sql);
        }

if (isset($db->test)) { print json_encode($params2)." => " . trim($sql) . "\n"; return; }
    
        #-- run
        $s = $db->prepare($sql);
        $s->setFetchMode(PDO::FETCH_ASSOC);
        $r = $s->execute($params2);

        #-- wrap        
        return $r ? new db_result($s) : $s;
    }
}

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


/**
 * Allows list access, or fetches first result[0]
 *
 */
class db_result extends ArrayObject implements IteratorAggregate {

    function __construct($results) {
        $this->results = $results;
        parent::__construct(array(), 2);
    }
    
    // single access
    function __get($name) {
    
        // get first result, transfuse into $this
        if ($this->results) {
            foreach ($this->results->fetch(PDO::FETCH_ASSOC) as $key=>$value) {
                $this->{$key} = $value;
            }
            unset($this->results);
        }
        
        // suffice __get
        return $this->{$name};
    }
    
    // used as PDO statement
    function __call($func, $args) {
        return call_user_func_array(array($this->results, $func), $args);
    }
    
    // iterator
    function getIterator() {
        if (isset($this->results)) {
            $this->results->setFetchMode(PDO::FETCH_CLASS, "ArrayObject", array(array(), 2));
            return $this->results;
        }
        else return new ArrayIterator($this);
    }

}



/**
 * 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
    }
}




?>