<?php
/**
 * api: php
 * title: Procedural pdo_* interface mimicking mysql_* functions
 * version: 0.9.3
 * priority: EXPERIMENTAL
 * license: Public Domain
 * type: functions
 * suggests: upgradephp
 * config:  <const name="PDO_HELPFUL" type="select" value="0=None|1=Notice|2=Syntax" help="Provide additional notices for common pitfalls"/>   <const name="PDO_SEEKABLE" type="int" value="0" help="Adds a faux cursor wrapper atop PDOStatement to allow seeking across result rows. Set to 1 to enable, or any larger integer for caching rows."/>
 * category: library
 * url: http://fossil.include-once.org/upgradephp/finfo?name=ext/contrib/pdo_mysql.php
 * requires: php (>= 5.1.3), php:pdo
 * doc: http://stackoverflow.com/a/20767765
 *
 *
 * The dated mysql_* functions are chaperoned by deprecation nagging and cursorily
 * mentions of PDO and MSQLI. Forced-switching APIs is kinda pointless however when
 * bound parameters go unmentioned and code rewriting remains prohibitive.
 *
 * This collection of functions provide pdo_ lookalikes for most mysql_* functions.

 *     If you need arbitrary  pdo_result(), _fetch_lengths(), _data_seek()
 *     access, please enable PDO_SEEKABLE. It does refetch if need be for
 *     out-of-order retrieval. (Mysqld itself provides enough buffering.)
 *
 *   BOUND PARAMETERS    In SQL only values can be bound parameters with ?
 *     placeholders. Identifiers like table names, column names can't. You
 *     still have to use interpolation (with withelisting) for those.
 *     Likewise can't LIMIT ?,? clauses be used in pdo_query(). Use
 *     typecasted interpolated numbers there.
 *
 *   NAMED PARAMETERS    Besides ? placeholders there are also :named_keys.
 *     pdo_query() supports those too if passed as a single array with
 *     :key => value pairs.  You can only use either or.
 *
 *   SOMETHING DOESN'T WORK    Before asking for personal support anywhere
 *     on the Internet, please have the courtesy to `print pdo_error();`

 *
 *
 */




// provide development tips (deprecated and redundant functions), set to 2 for SQL syntax warnings about unbound strings
defined("PDO_HELPFUL") or
define("PDO_HELPFUL", 1);


// apply PDO wrapper for random-access row seeking / faux cursor
defined("PDO_SEEKABLE") or
define("PDO_SEEKABLE", 1);  // set this to a higher integer (e.g. 500) to enable a cache for random access


// upgradephp
defined("E_USER_DEPRECATED") or
define("E_USER_DEPRECATED", E_USER_NOTICE);

  function pdo_close($link=NULL) {
     PDO_HELPFUL and pdo_trigger_error("pdo_close() Does not need to be called usually. Just unset() your database handle.", E_USER_DEPRECATED);

     if ($link) {
        unset($link);
     }
     else {
        $GLOBALS["pdo"] = /*unset with*/ new pdo_dummy("No database connection. You had just disconnected using pdo_close()!");
     }
  }




  /**
   * Connect to database.
   *
   * Also stores the newest PDO handle in the global `$pdo` variable.
   *
   */  
  function pdo_connect($server=NULL, $user=NULL, $pass=NULL, $new_link=FALSE, $client_flags=0x0000, $pconnect=0x0000) {
     
     // get params
     $server or $server = ini_get("mysql.default_host");
     $user or $user = ini_get("mysql.default_user");
     $pass or $pass = ini_get("mysql.default_password");


     // prepare Data Source Name
     $dsn = "mysql:";
     
     // servername contains a socket path
     if (strpos($server, "/")) {
        strpos($server, ":") and $server = substr($server, strpos($server, ":") + 1);

        }
        else {
           $dsn .= "host=$server";
        }
     }
     //("we don't have a dbname= at this point");
     

     // driver flags
     $flags = array(
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
        #PDO::MYSQL_ATTR_INIT_COMMAND => '',   // first SQL command after connect
        #PDO::MYSQL_ATTR_DIRECT_QUERY => true, // direct queries, no prepared statements
        #PDO::MYSQL_ATTR_FOUND_ROWS => true,   // rowCount() for SELECTs, not sure if this is an init parameter, or Statement setting
     );
     if ($client_flags & MYSQL_CLIENT_COMPRESS) {
        $flags[PDO::MYSQL_ATTR_COMPRESS] = 1;
     }
     if ($client_flags & MYSQL_CLIENT_SSL) {

     if ($client_flags & 128) {
        $flags[PDO::MYSQL_ATTR_LOCAL_INFILE] = 1;
     }
     if ($pconnect) {
        $flags[PDO::ATTR_PERSISTENT] = 1;
     }
     

     // instantiate connection
     try {
        $pdo = new PDO($dsn, $user, $pass, $flags);
     }
     catch (RuntimeException $pdo) {
        PDO_HELPFUL and pdo_trigger_error("pdo_connect() Failed. {$pdo->getMessage()}", E_USER_WARNING);
        return new pdo_dummy("Database connection had failed [ErrCode{$pdo->getCode()}].");
     }
     
     // set PDO flags
     $pdo->setAttribute(PDO::ATTR_ERRMODE, (PDO_HELPFUL ? PDO::ERRMODE_WARNING : PDO::ERRMODE_SILENT)); //or PDO::ERRMODE_EXCEPTION
     $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
     $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
     $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
     $pdo->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
     $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);


     // done     
     return pdo_handle($pdo, "SET_HANDLE_AS_DEFAULT");
  }

   * Unallocating a query result.
   *
   * This call has little practical use.
   * Rather just unset() your pdo_query $result object variables.
   *
   */
  function pdo_free_result($stmt) {
     ###PDO_HELPFUL and trigger_error("pdo_free_result() usage is mostly redundant. PHP cleans up resource handles/objects if you just unset() them, or move out of scope.", E_USER_DEPRECATED);

     // free up some resources
     try {
        pdo_stmt($stmt)->fetchAll();
        pdo_stmt($stmt)->closeCursor();
     }
     catch (PDOException $e) {

        // or the last
        elseif (is_object(end($params))) {
           $link = array_pop($params);
        }
     }
     // or we use the default $pdo
     $link = pdo_handle($link);

     
     // is $params a list to pdo_query(), or just one array with :key=>value pairs?
     if (count($params)==1 && is_array($params[0])) {
        $params = array_shift($params);
     }


     // add PDO_MySQL driver flag / workaround for specific query types
     switch (strtoupper(substr($sql, 0, strspn(strtoupper($sql), "SELECT,USE,CREATE")))) {

        // ought to make ->rowCount() work
        case "SELECT":
           $flags[PDO::MYSQL_ATTR_FOUND_ROWS] = true;
           break;

        // temporarily disable prepared statement mode for unbindable directives
        case "USE":
           $direct = true;
           $link->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
           #$flags[PDO::MYSQL_ATTR_DIRECT_QUERY] = true; // has no effect as stmt flag, also overwritten by setAttr EMULATE_PREPARES
           break;

        default:
     }
     
     
     // add syntax notices?
     if (PDO_HELPFUL>=2) {
        strpos($sql, "'") and pdo_trigger_error("pdo_query() It looks like you're still using single ' quotes in your SQL. This indicates plain or interpolated strings or variables in SQL context. Investigate if parameter binding `pdo_query(\"SELECT ?, ?\", \$var1, \$var2)` would fit.");
        strpos($sql, '"') and pdo_trigger_error("pdo_query() You are using double \" quotes in your SQL. Beware that this is ambiguous. In MySQLs default mode these indicate interpolated string variables (don't do that, use bound parameters), while in MySQL ANSI mode they quote identifiers (table or column name).");
     }


     // unparameterized query()
     if ($direct) {
        $stmt = $link->query($sql);
        $link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
     }
     // or prepare() and execute()
     else {
        if ($stmt = $link->prepare($sql, $flags)) {   // no try-catch in _WARNING mode
           $stmt->execute($params);
        }
     }

     
     // result
     if (!$stmt and PDO_HELPFUL) {
        pdo_trigger_error("pdo_query() SQL query failed, see pdo_error()", E_USER_WARNING);
     }
     elseif (PDO_SEEKABLE & !$direct) {
        return new PDOStatement_Seekable($stmt, $params);

     // workaround: fetch everything, works just once
     else {
        pdo_trigger_error("pdo_result() Can currently only be used once, for fetching a value from the first \$row. Scrolling only works with PDO_SEEKABLE enabled.", E_USER_WARNING);

        $rows = pdo_stmt($stmt)->fetchAll(PDO::FETCH_BOTH);
        #static $rows[hash(stmt)]; //would otherwise bind the result set here
     }

     
     // check if found
     if (isset($rows[$row][$field])) {
        return $rows[$row][$field];
     }
     else {
        pdo_trigger_error("pdo_result() Couldn't find row [$row] and column `$field`.");
     }
     
  }

  /**
   * Return server status and some settings.
   *
   * @requires: php (>= 5.5) | upgradephp
   */
  function pdo_stat() {
     return array_column(pdo_query("SHOW STATUS")->fetchAll(), "Value", "Variable_name");
  }

  class PDOStatement_Seekable implements Iterator {

     // the actual PDOStatement
     private $stmt = NULL;
     // bound parameters for reexecution
     private $params = array();

     // row cursor
     private $i = -1;
     // available max key / rowcount
     private $max;
     // last fetched rows
     private $cache = array();


     // keep proxied PDOStatement and data necessary to rerun it
     function __construct($stmt, $params) {
        $this->stmt = $stmt;
        $this->params = $params;
        $this->max = $stmt->rowCount(/*PDO::MYSQL_ATTR_FOUND_ROWS*/) - 1;
     }


     // invoke wrapped PDOStatement
     function __call($func, $args) {
        return call_user_func_array(array($this->stmt, $func), $args);
     }


     // repopulate PDOStatement, reset cache and cursor
     function execute($params = NULL) {
        # Superposes the actual stmt::execute(), in case it's really a new query.
        # Note that rewinding also has to reset the cache, in case the table
        # contents changed meanwhile. So may result in a different set of result
        # rows alltogether. Which is really not wanted since this is all just
        # meant for seeking. (No way around that in a userland faux cursor.)
        $this->stmt->execute($params ? $params : $this->params);
        $this->max = $stmt->rowCount(/*PDO::MYSQL_ATTR_FOUND_ROWS*/) - 1;
        $this->i = -1;   // last fetched row
        $this->cache = array();
     }


     // fetch a single row
     function fetch($type=PDO::FETCH_ASSOC, $ori=PDO::FETCH_ORI_NEXT, $offset=0) {


        // calculate offset
        $last = & $this->i;
        $target = array(                                    
            PDO::FETCH_ORI_NEXT => $last + 1,
            PDO::FETCH_ORI_PRIOR => $last,
            PDO::FETCH_ORI_REL => $last + $offset,
            PDO::FETCH_ORI_FIRST => -1,
            PDO::FETCH_ORI_LAST => $this->max,
            PDO::FETCH_ORI_ABS => $offset,
        );
        $target = $target[$ori];
#print "seek($last->$target) ";

        
        // last row? got that covered!
        if (isset($this->cache[$target])) {
#print "seek(==) ";
            return $this->rowType($type, $this->cache[$target]);
        }
        

        // moving farther backwards
        if ($target < $last) {
#print "seek(<<) ";
            $this->execute();
        }

        
        // jump forwards
        while ($target > $last + 1) {
#print "seek(>>) ";
            $row = $this->stmt->fetch(PDO::FETCH_ASSOC);
            $last++;
            if (!$row) {
               return pdo_trigger_error("PDOStatement_Seekable::fetch() Scrolling past last row.", E_USER_WARNING) and $row;
            }
        }


        // actually fetch next row
        if ($row = $this->stmt->fetch(PDO::FETCH_ASSOC)) {
#print "seek(ft) ";
           assert($target == ++$last);
           // keep last row(s)
           if (count($this->cache) > PDO_SEEKABLE) {
              $this->cache = array_slice($this->cache, $last, - PDO_SEEKABLE, true);
           }
           $this->cache[$last] = $row;
        }
        return $this->rowType($type, $row);
     }

     // Iterator handling
     function current() {
        return $this->fetch();
     }
     function rewind() {
        if ($this->i >= 0) {
           $this->execute();
        }
     }
     // PDOStatement itself implements just Traversable internally, not Iterator, so we have to handle the keys ourselves
     function key() {
        return $this->i >= 0 ? $this->i : NULL;
     }
     function next() {