API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 12th, 2016
50
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 35.87 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. /*
  3.     Copyright (C) 2008-2012 Sergey Tsalkov (stsalkov@gmail.com)
  4.  
  5.     This program is free software: you can redistribute it and/or modify
  6.     it under the terms of the GNU Lesser General Public License as published by
  7.     the Free Software Foundation, either version 3 of the License, or
  8.     (at your option) any later version.
  9.  
  10.     This program is distributed in the hope that it will be useful,
  11.     but WITHOUT ANY WARRANTY; without even the implied warranty of
  12.     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13.     GNU Lesser General Public License for more details.
  14.  
  15.     You should have received a copy of the GNU Lesser General Public License
  16.     along with this program.  If not, see <http://www.gnu.org/licenses/>.
  17. */
  18.  
  19.  
  20. class DB {
  21.   // initial connection
  22.   public static $dbName = '';
  23.   public static $user = '';
  24.   public static $password = '';
  25.   public static $host = 'localhost';
  26.   public static $port = null;
  27.   public static $encoding = 'latin1';
  28.  
  29.   // configure workings
  30.   public static $param_char = '%';
  31.   public static $named_param_seperator = '_';
  32.   public static $success_handler = false;
  33.   public static $error_handler = true;
  34.   public static $throw_exception_on_error = false;
  35.   public static $nonsql_error_handler = null;
  36.   public static $throw_exception_on_nonsql_error = false;
  37.   public static $nested_transactions = false;
  38.   public static $usenull = true;
  39.   public static $ssl = array('key' => '', 'cert' => '', 'ca_cert' => '', 'ca_path' => '', 'cipher' => '');
  40.   public static $connect_options = array(MYSQLI_OPT_CONNECT_TIMEOUT => 30);
  41.  
  42.   // internal
  43.   protected static $mdb = null;
  44.  
  45.   public static function getMDB() {
  46.     $mdb = DB::$mdb;
  47.    
  48.     if ($mdb === null) {
  49.       $mdb = DB::$mdb = new MeekroDB();
  50.     }
  51.  
  52.     static $variables_to_sync = array('param_char', 'named_param_seperator', 'success_handler', 'error_handler', 'throw_exception_on_error',
  53.       'nonsql_error_handler', 'throw_exception_on_nonsql_error', 'nested_transactions', 'usenull', 'ssl', 'connect_options');
  54.  
  55.     $db_class_vars = get_class_vars('DB'); // the DB::$$var syntax only works in 5.3+
  56.  
  57.     foreach ($variables_to_sync as $variable) {
  58.       if ($mdb->$variable !== $db_class_vars[$variable]) {
  59.         $mdb->$variable = $db_class_vars[$variable];
  60.       }
  61.     }
  62.    
  63.     return $mdb;
  64.   }
  65.  
  66.   // yes, this is ugly. __callStatic() only works in 5.3+
  67.   public static function get() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'get'), $args); }
  68.   public static function disconnect() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'disconnect'), $args); }
  69.   public static function query() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'query'), $args); }
  70.   public static function queryFirstRow() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryFirstRow'), $args); }
  71.   public static function queryOneRow() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryOneRow'), $args); }
  72.   public static function queryAllLists() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryAllLists'), $args); }
  73.   public static function queryFullColumns() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryFullColumns'), $args); }
  74.   public static function queryFirstList() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryFirstList'), $args); }
  75.   public static function queryOneList() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryOneList'), $args); }
  76.   public static function queryFirstColumn() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryFirstColumn'), $args); }
  77.   public static function queryOneColumn() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryOneColumn'), $args); }
  78.   public static function queryFirstField() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryFirstField'), $args); }
  79.   public static function queryOneField() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryOneField'), $args); }
  80.   public static function queryRaw() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryRaw'), $args); }
  81.   public static function queryRawUnbuf() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'queryRawUnbuf'), $args); }
  82.  
  83.   public static function insert() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'insert'), $args); }
  84.   public static function insertIgnore() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'insertIgnore'), $args); }
  85.   public static function insertUpdate() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'insertUpdate'), $args); }
  86.   public static function replace() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'replace'), $args); }
  87.   public static function update() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'update'), $args); }
  88.   public static function delete() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'delete'), $args); }
  89.  
  90.   public static function insertId() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'insertId'), $args); }
  91.   public static function count() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'count'), $args); }
  92.   public static function affectedRows() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'affectedRows'), $args); }
  93.  
  94.   public static function useDB() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'useDB'), $args); }
  95.   public static function startTransaction() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'startTransaction'), $args); }
  96.   public static function commit() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'commit'), $args); }
  97.   public static function rollback() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'rollback'), $args); }
  98.   public static function tableList() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'tableList'), $args); }
  99.   public static function columnList() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'columnList'), $args); }
  100.  
  101.   public static function sqlEval() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'sqlEval'), $args); }
  102.   public static function nonSQLError() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'nonSQLError'), $args); }
  103.  
  104.   public static function serverVersion() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'serverVersion'), $args); }
  105.   public static function transactionDepth() { $args = func_get_args(); return call_user_func_array(array(DB::getMDB(), 'transactionDepth'), $args); }
  106.  
  107.  
  108.   public static function debugMode($handler = true) {
  109.     DB::$success_handler = $handler;
  110.   }
  111.  
  112. }
  113.  
  114.  
  115. class MeekroDB {
  116.   // initial connection
  117.   public $dbName = '';
  118.   public $user = '';
  119.   public $password = '';
  120.   public $host = 'localhost';
  121.   public $port = null;
  122.   public $encoding = 'latin1';
  123.  
  124.   // configure workings
  125.   public $param_char = '%';
  126.   public $named_param_seperator = '_';
  127.   public $success_handler = false;
  128.   public $error_handler = true;
  129.   public $throw_exception_on_error = false;
  130.   public $nonsql_error_handler = null;
  131.   public $throw_exception_on_nonsql_error = false;
  132.   public $nested_transactions = false;
  133.   public $usenull = true;
  134.   public $ssl = array('key' => '', 'cert' => '', 'ca_cert' => '', 'ca_path' => '', 'cipher' => '');
  135.   public $connect_options = array(MYSQLI_OPT_CONNECT_TIMEOUT => 30);
  136.  
  137.   // internal
  138.   public $internal_mysql = null;
  139.   public $server_info = null;
  140.   public $insert_id = 0;
  141.   public $num_rows = 0;
  142.   public $affected_rows = 0;
  143.   public $current_db = null;
  144.   public $nested_transactions_count = 0;
  145.  
  146.  
  147.   public function __construct($host=null, $user=null, $password=null, $dbName=null, $port=null, $encoding=null) {
  148.     if ($host === null) $host = DB::$host;
  149.     if ($user === null) $user = DB::$user;
  150.     if ($password === null) $password = DB::$password;
  151.     if ($dbName === null) $dbName = DB::$dbName;
  152.     if ($port === null) $port = DB::$port;
  153.     if ($encoding === null) $encoding = DB::$encoding;
  154.    
  155.     $this->host = $host;
  156.     $this->user = $user;
  157.     $this->password = $password;
  158.     $this->dbName = $dbName;
  159.     $this->port = $port;
  160.     $this->encoding = $encoding;
  161.   }
  162.  
  163.   public function get() {
  164.     $mysql = $this->internal_mysql;
  165.    
  166.     if (!($mysql instanceof MySQLi)) {
  167.       if (! $this->port) $this->port = ini_get('mysqli.default_port');
  168.       $this->current_db = $this->dbName;
  169.       $mysql = new mysqli();
  170.  
  171.       $connect_flags = 0;
  172.       if ($this->ssl['key']) {
  173.         $mysql->ssl_set($this->ssl['key'], $this->ssl['cert'], $this->ssl['ca_cert'], $this->ssl['ca_path'], $this->ssl['cipher']);
  174.         $connect_flags |= MYSQLI_CLIENT_SSL;
  175.       }
  176.       foreach ($this->connect_options as $key => $value) {
  177.         $mysql->options($key, $value);
  178.       }
  179.  
  180.       // suppress warnings, since we will check connect_error anyway
  181.       @$mysql->real_connect($this->host, $this->user, $this->password, $this->dbName, $this->port, null, $connect_flags);
  182.      
  183.       if ($mysql->connect_error) {
  184.         $this->nonSQLError('Unable to connect to MySQL server! Error: ' . $mysql->connect_error);
  185.       }
  186.      
  187.       $mysql->set_charset($this->encoding);
  188.       $this->internal_mysql = $mysql;
  189.       $this->server_info = $mysql->server_info;
  190.     }
  191.    
  192.     return $mysql;
  193.   }
  194.  
  195.   public function disconnect() {
  196.     $mysqli = $this->internal_mysql;
  197.     if ($mysqli instanceof MySQLi) {
  198.       if ($thread_id = $mysqli->thread_id) $mysqli->kill($thread_id);
  199.       $mysqli->close();
  200.     }
  201.     $this->internal_mysql = null;
  202.   }
  203.  
  204.   public function nonSQLError($message) {
  205.     if ($this->throw_exception_on_nonsql_error) {
  206.       $e = new MeekroDBException($message);
  207.       throw $e;
  208.     }
  209.    
  210.     $error_handler = is_callable($this->nonsql_error_handler) ? $this->nonsql_error_handler : 'meekrodb_error_handler';
  211.        
  212.     call_user_func($error_handler, array(
  213.       'type' => 'nonsql',
  214.       'error' => $message
  215.     ));
  216.   }
  217.  
  218.   public function debugMode($handler = true) {
  219.     $this->success_handler = $handler;
  220.   }
  221.  
  222.   public function serverVersion() { $this->get(); return $this->server_info; }
  223.   public function transactionDepth() { return $this->nested_transactions_count; }
  224.   public function insertId() { return $this->insert_id; }
  225.   public function affectedRows() { return $this->affected_rows; }
  226.   public function count() { $args = func_get_args(); return call_user_func_array(array($this, 'numRows'), $args); }
  227.   public function numRows() { return $this->num_rows; }
  228.  
  229.   public function useDB() { $args = func_get_args(); return call_user_func_array(array($this, 'setDB'), $args); }
  230.   public function setDB($dbName) {
  231.     $db = $this->get();
  232.     if (! $db->select_db($dbName)) $this->nonSQLError("Unable to set database to $dbName");
  233.     $this->current_db = $dbName;
  234.   }
  235.  
  236.  
  237.   public function startTransaction() {
  238.     if ($this->nested_transactions && $this->serverVersion() < '5.5') {
  239.       return $this->nonSQLError("Nested transactions are only available on MySQL 5.5 and greater. You are using MySQL " . $this->serverVersion());
  240.     }
  241.    
  242.     if (!$this->nested_transactions || $this->nested_transactions_count == 0) {
  243.       $this->query('START TRANSACTION');
  244.       $this->nested_transactions_count = 1;
  245.     } else {
  246.       $this->query("SAVEPOINT LEVEL{$this->nested_transactions_count}");
  247.       $this->nested_transactions_count++;
  248.     }
  249.    
  250.     return $this->nested_transactions_count;
  251.   }
  252.  
  253.   public function commit($all=false) {
  254.     if ($this->nested_transactions && $this->serverVersion() < '5.5') {
  255.       return $this->nonSQLError("Nested transactions are only available on MySQL 5.5 and greater. You are using MySQL " . $this->serverVersion());
  256.     }
  257.    
  258.     if ($this->nested_transactions && $this->nested_transactions_count > 0)
  259.       $this->nested_transactions_count--;
  260.    
  261.     if (!$this->nested_transactions || $all || $this->nested_transactions_count == 0) {
  262.       $this->nested_transactions_count = 0;
  263.       $this->query('COMMIT');
  264.     } else {
  265.       $this->query("RELEASE SAVEPOINT LEVEL{$this->nested_transactions_count}");
  266.     }
  267.    
  268.     return $this->nested_transactions_count;
  269.   }
  270.  
  271.   public function rollback($all=false) {
  272.     if ($this->nested_transactions && $this->serverVersion() < '5.5') {
  273.       return $this->nonSQLError("Nested transactions are only available on MySQL 5.5 and greater. You are using MySQL " . $this->serverVersion());
  274.     }
  275.    
  276.     if ($this->nested_transactions && $this->nested_transactions_count > 0)
  277.       $this->nested_transactions_count--;
  278.    
  279.     if (!$this->nested_transactions || $all || $this->nested_transactions_count == 0) {
  280.       $this->nested_transactions_count = 0;
  281.       $this->query('ROLLBACK');
  282.     } else {
  283.       $this->query("ROLLBACK TO SAVEPOINT LEVEL{$this->nested_transactions_count}");
  284.     }
  285.    
  286.     return $this->nested_transactions_count;
  287.   }
  288.  
  289.   protected function formatTableName($table) {
  290.     $table = trim($table, '`');
  291.    
  292.     if (strpos($table, '.')) return implode('.', array_map(array($this, 'formatTableName'), explode('.', $table)));
  293.     else return '`' . str_replace('`', '``', $table) . '`';
  294.   }
  295.  
  296.   public function update() {
  297.     $args = func_get_args();
  298.     $table = array_shift($args);
  299.     $params = array_shift($args);
  300.     $where = array_shift($args);
  301.    
  302.     $query = str_replace('%', $this->param_char, "UPDATE %b SET %? WHERE ") . $where;
  303.    
  304.     array_unshift($args, $params);
  305.     array_unshift($args, $table);
  306.     array_unshift($args, $query);
  307.     return call_user_func_array(array($this, 'query'), $args);
  308.   }
  309.  
  310.   public function insertOrReplace($which, $table, $datas, $options=array()) {
  311.     $datas = unserialize(serialize($datas)); // break references within array
  312.     $keys = $values = array();
  313.    
  314.     if (isset($datas[0]) && is_array($datas[0])) {
  315.       foreach ($datas as $datum) {
  316.         ksort($datum);
  317.         if (! $keys) $keys = array_keys($datum);
  318.         $values[] = array_values($datum);  
  319.       }
  320.      
  321.     } else {
  322.       $keys = array_keys($datas);
  323.       $values = array_values($datas);
  324.     }
  325.    
  326.     if (isset($options['ignore']) && $options['ignore']) $which = 'INSERT IGNORE';
  327.    
  328.     if (isset($options['update']) && is_array($options['update']) && $options['update'] && strtolower($which) == 'insert') {
  329.       if (array_values($options['update']) !== $options['update']) {
  330.         return $this->query(
  331.           str_replace('%', $this->param_char, "INSERT INTO %b %lb VALUES %? ON DUPLICATE KEY UPDATE %?"),
  332.           $table, $keys, $values, $options['update']);
  333.       } else {
  334.         $update_str = array_shift($options['update']);
  335.         $query_param = array(
  336.           str_replace('%', $this->param_char, "INSERT INTO %b %lb VALUES %? ON DUPLICATE KEY UPDATE ") . $update_str,
  337.           $table, $keys, $values);
  338.         $query_param = array_merge($query_param, $options['update']);
  339.         return call_user_func_array(array($this, 'query'), $query_param);
  340.       }
  341.      
  342.     }
  343.    
  344.     return $this->query(
  345.       str_replace('%', $this->param_char, "%l INTO %b %lb VALUES %?"),
  346.       $which, $table, $keys, $values);
  347.   }
  348.  
  349.   public function insert($table, $data) { return $this->insertOrReplace('INSERT', $table, $data); }
  350.   public function insertIgnore($table, $data) { return $this->insertOrReplace('INSERT', $table, $data, array('ignore' => true)); }
  351.   public function replace($table, $data) { return $this->insertOrReplace('REPLACE', $table, $data); }
  352.  
  353.   public function insertUpdate() {
  354.     $args = func_get_args();
  355.     $table = array_shift($args);
  356.     $data = array_shift($args);
  357.    
  358.     if (! isset($args[0])) { // update will have all the data of the insert
  359.       if (isset($data[0]) && is_array($data[0])) { //multiple insert rows specified -- failing!
  360.         $this->nonSQLError("Badly formatted insertUpdate() query -- you didn't specify the update component!");
  361.       }
  362.      
  363.       $args[0] = $data;
  364.     }
  365.    
  366.     if (is_array($args[0])) $update = $args[0];
  367.     else $update = $args;
  368.    
  369.     return $this->insertOrReplace('INSERT', $table, $data, array('update' => $update));
  370.   }
  371.  
  372.   public function delete() {
  373.     $args = func_get_args();
  374.     $table = $this->formatTableName(array_shift($args));
  375.     $where = array_shift($args);
  376.     $buildquery = "DELETE FROM $table WHERE $where";
  377.     array_unshift($args, $buildquery);
  378.     return call_user_func_array(array($this, 'query'), $args);
  379.   }
  380.  
  381.   public function sqleval() {
  382.     $args = func_get_args();
  383.     $text = call_user_func_array(array($this, 'parseQueryParams'), $args);
  384.     return new MeekroDBEval($text);
  385.   }
  386.  
  387.   public function columnList($table) {
  388.     return $this->queryOneColumn('Field', "SHOW COLUMNS FROM %b", $table);
  389.   }
  390.  
  391.   public function tableList($db = null) {
  392.     if ($db) {
  393.       $olddb = $this->current_db;
  394.       $this->useDB($db);
  395.     }
  396.  
  397.     $result = $this->queryFirstColumn('SHOW TABLES');
  398.     if (isset($olddb)) $this->useDB($olddb);
  399.     return $result;
  400.   }
  401.  
  402.   protected function preparseQueryParams() {
  403.     $args = func_get_args();
  404.     $sql = trim(strval(array_shift($args)));
  405.     $args_all = $args;
  406.    
  407.     if (count($args_all) == 0) return array($sql);
  408.      
  409.     $param_char_length = strlen($this->param_char);
  410.     $named_seperator_length = strlen($this->named_param_seperator);
  411.    
  412.     $types = array(
  413.       $this->param_char . 'll', // list of literals
  414.       $this->param_char . 'ls', // list of strings
  415.       $this->param_char . 'l',  // literal
  416.       $this->param_char . 'li', // list of integers
  417.       $this->param_char . 'ld', // list of decimals
  418.       $this->param_char . 'lb', // list of backticks
  419.       $this->param_char . 'lt', // list of timestamps
  420.       $this->param_char . 's',  // string
  421.       $this->param_char . 'i',  // integer
  422.       $this->param_char . 'd',  // double / decimal
  423.       $this->param_char . 'b',  // backtick
  424.       $this->param_char . 't',  // timestamp
  425.       $this->param_char . '?',  // infer type
  426.       $this->param_char . 'ss'  // search string (like string, surrounded with %'s)
  427.     );
  428.    
  429.     // generate list of all MeekroDB variables in our query, and their position
  430.     // in the form "offset => variable", sorted by offsets
  431.     $posList = array();
  432.     foreach ($types as $type) {
  433.       $lastPos = 0;
  434.       while (($pos = strpos($sql, $type, $lastPos)) !== false) {
  435.         $lastPos = $pos + 1;
  436.         if (isset($posList[$pos]) && strlen($posList[$pos]) > strlen($type)) continue;
  437.         $posList[$pos] = $type;
  438.       }
  439.     }
  440.    
  441.     ksort($posList);
  442.    
  443.     // for each MeekroDB variable, substitute it with array(type: i, value: 53) or whatever
  444.     $chunkyQuery = array(); // preparsed query
  445.     $pos_adj = 0; // how much we've added or removed from the original sql string
  446.     foreach ($posList as $pos => $type) {
  447.       $type = substr($type, $param_char_length); // variable, without % in front of it
  448.       $length_type = strlen($type) + $param_char_length; // length of variable w/o %
  449.      
  450.       $new_pos = $pos + $pos_adj; // position of start of variable
  451.       $new_pos_back = $new_pos + $length_type; // position of end of variable
  452.       $arg_number_length = 0; // length of any named or numbered parameter addition
  453.      
  454.       // handle numbered parameters
  455.       if ($arg_number_length = strspn($sql, '0123456789', $new_pos_back)) {
  456.         $arg_number = substr($sql, $new_pos_back, $arg_number_length);
  457.         if (! array_key_exists($arg_number, $args_all)) $this->nonSQLError("Non existent argument reference (arg $arg_number): $sql");
  458.        
  459.         $arg = $args_all[$arg_number];
  460.        
  461.       // handle named parameters
  462.       } else if (substr($sql, $new_pos_back, $named_seperator_length) == $this->named_param_seperator) {
  463.         $arg_number_length = strspn($sql, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_',
  464.           $new_pos_back + $named_seperator_length) + $named_seperator_length;
  465.        
  466.         $arg_number = substr($sql, $new_pos_back + $named_seperator_length, $arg_number_length - $named_seperator_length);
  467.         if (count($args_all) != 1 || !is_array($args_all[0])) $this->nonSQLError("If you use named parameters, the second argument must be an array of parameters");
  468.         if (! array_key_exists($arg_number, $args_all[0])) $this->nonSQLError("Non existent argument reference (arg $arg_number): $sql");
  469.        
  470.         $arg = $args_all[0][$arg_number];
  471.        
  472.       } else {
  473.         $arg_number = 0;
  474.         $arg = array_shift($args);
  475.       }
  476.      
  477.       if ($new_pos > 0) $chunkyQuery[] = substr($sql, 0, $new_pos);
  478.      
  479.       if (is_object($arg) && ($arg instanceof WhereClause)) {
  480.         list($clause_sql, $clause_args) = $arg->textAndArgs();
  481.         array_unshift($clause_args, $clause_sql);
  482.         $preparsed_sql = call_user_func_array(array($this, 'preparseQueryParams'), $clause_args);
  483.         $chunkyQuery = array_merge($chunkyQuery, $preparsed_sql);  
  484.       } else {
  485.         $chunkyQuery[] = array('type' => $type, 'value' => $arg);
  486.       }
  487.      
  488.       $sql = substr($sql, $new_pos_back + $arg_number_length);
  489.       $pos_adj -= $new_pos_back + $arg_number_length;
  490.     }
  491.    
  492.     if (strlen($sql) > 0) $chunkyQuery[] = $sql;
  493.    
  494.     return $chunkyQuery;
  495.   }
  496.  
  497.   protected function escape($str) { return "'" . $this->get()->real_escape_string(strval($str)) . "'"; }
  498.  
  499.   protected function sanitize($value) {
  500.     if (is_object($value)) {
  501.       if ($value instanceof MeekroDBEval) return $value->text;
  502.       else if ($value instanceof DateTime) return $this->escape($value->format('Y-m-d H:i:s'));
  503.       else return '';
  504.     }
  505.    
  506.     if (is_null($value)) return $this->usenull ? 'NULL' : "''";
  507.     else if (is_bool($value)) return ($value ? 1 : 0);
  508.     else if (is_int($value)) return $value;
  509.     else if (is_float($value)) return $value;
  510.    
  511.     else if (is_array($value)) {
  512.       // non-assoc array?
  513.       if (array_values($value) === $value) {
  514.         if (is_array($value[0])) return implode(', ', array_map(array($this, 'sanitize'), $value));
  515.         else return '(' . implode(', ', array_map(array($this, 'sanitize'), $value)) . ')';
  516.       }
  517.      
  518.       $pairs = array();
  519.       foreach ($value as $k => $v) {
  520.         $pairs[] = $this->formatTableName($k) . '=' . $this->sanitize($v);
  521.       }
  522.      
  523.       return implode(', ', $pairs);
  524.     }
  525.     else return $this->escape($value);
  526.   }
  527.  
  528.   protected function parseTS($ts) {
  529.     if (is_string($ts)) return date('Y-m-d H:i:s', strtotime($ts));
  530.     else if (is_object($ts) && ($ts instanceof DateTime)) return $ts->format('Y-m-d H:i:s');
  531.   }
  532.  
  533.   protected function intval($var) {
  534.     if (PHP_INT_SIZE == 8) return intval($var);
  535.     return floor(doubleval($var));
  536.   }
  537.  
  538.   protected function parseQueryParams() {
  539.     $args = func_get_args();
  540.     $chunkyQuery = call_user_func_array(array($this, 'preparseQueryParams'), $args);
  541.    
  542.     $query = '';
  543.     $array_types = array('ls', 'li', 'ld', 'lb', 'll', 'lt');
  544.    
  545.     foreach ($chunkyQuery as $chunk) {
  546.       if (is_string($chunk)) {
  547.         $query .= $chunk;
  548.         continue;
  549.       }
  550.      
  551.       $type = $chunk['type'];
  552.       $arg = $chunk['value'];
  553.       $result = '';
  554.      
  555.       if ($type != '?') {
  556.         $is_array_type = in_array($type, $array_types, true);
  557.         if ($is_array_type && !is_array($arg)) $this->nonSQLError("Badly formatted SQL query: Expected array, got scalar instead!");
  558.         else if (!$is_array_type && is_array($arg)) $this->nonSQLError("Badly formatted SQL query: Expected scalar, got array instead!");
  559.       }
  560.      
  561.       if ($type == 's') $result = $this->escape($arg);
  562.       else if ($type == 'i') $result = $this->intval($arg);
  563.       else if ($type == 'd') $result = doubleval($arg);
  564.       else if ($type == 'b') $result = $this->formatTableName($arg);
  565.       else if ($type == 'l') $result = $arg;
  566.       else if ($type == 'ss') $result = $this->escape("%" . str_replace(array('%', '_'), array('\%', '\_'), $arg) . "%");
  567.       else if ($type == 't') $result = $this->escape($this->parseTS($arg));
  568.      
  569.       else if ($type == 'ls') $result = array_map(array($this, 'escape'), $arg);
  570.       else if ($type == 'li') $result = array_map(array($this, 'intval'), $arg);
  571.       else if ($type == 'ld') $result = array_map('doubleval', $arg);
  572.       else if ($type == 'lb') $result = array_map(array($this, 'formatTableName'), $arg);
  573.       else if ($type == 'll') $result = $arg;
  574.       else if ($type == 'lt') $result = array_map(array($this, 'escape'), array_map(array($this, 'parseTS'), $arg));
  575.      
  576.       else if ($type == '?') $result = $this->sanitize($arg);
  577.      
  578.       else $this->nonSQLError("Badly formatted SQL query: Invalid MeekroDB param $type");
  579.      
  580.       if (is_array($result)) $result = '(' . implode(',', $result) . ')';
  581.      
  582.       $query .= $result;
  583.     }
  584.      
  585.     return $query;
  586.   }
  587.  
  588.   protected function prependCall($function, $args, $prepend) { array_unshift($args, $prepend); return call_user_func_array($function, $args); }
  589.   public function query() { $args = func_get_args(); return $this->prependCall(array($this, 'queryHelper'), $args, 'assoc'); }
  590.   public function queryAllLists() { $args = func_get_args(); return $this->prependCall(array($this, 'queryHelper'), $args, 'list'); }
  591.   public function queryFullColumns() { $args = func_get_args(); return $this->prependCall(array($this, 'queryHelper'), $args, 'full'); }
  592.  
  593.   public function queryRaw() { $args = func_get_args(); return $this->prependCall(array($this, 'queryHelper'), $args, 'raw_buf'); }
  594.   public function queryRawUnbuf() { $args = func_get_args(); return $this->prependCall(array($this, 'queryHelper'), $args, 'raw_unbuf'); }
  595.  
  596.   protected function queryHelper() {
  597.     $args = func_get_args();
  598.     $type = array_shift($args);
  599.     $db = $this->get();
  600.  
  601.     $is_buffered = true;
  602.     $row_type = 'assoc'; // assoc, list, raw
  603.     $full_names = false;
  604.  
  605.     switch ($type) {
  606.       case 'assoc':
  607.         break;
  608.       case 'list':
  609.         $row_type = 'list';
  610.         break;
  611.       case 'full':
  612.         $row_type = 'list';
  613.         $full_names = true;
  614.         break;
  615.       case 'raw_buf':
  616.         $row_type = 'raw';
  617.         break;
  618.       case 'raw_unbuf':
  619.         $is_buffered = false;
  620.         $row_type = 'raw';
  621.         break;
  622.       default:
  623.         $this->nonSQLError('Error -- invalid argument to queryHelper!');
  624.     }
  625.  
  626.     $sql = call_user_func_array(array($this, 'parseQueryParams'), $args);
  627.    
  628.     if ($this->success_handler) $starttime = microtime(true);
  629.     $result = $db->query($sql, $is_buffered ? MYSQLI_STORE_RESULT : MYSQLI_USE_RESULT);
  630.     if ($this->success_handler) $runtime = microtime(true) - $starttime;
  631.     else $runtime = 0;
  632.  
  633.     // ----- BEGIN ERROR HANDLING
  634.     if (!$sql || $db->error) {
  635.       if ($this->error_handler) {
  636.         $error_handler = is_callable($this->error_handler) ? $this->error_handler : 'meekrodb_error_handler';
  637.        
  638.         call_user_func($error_handler, array(
  639.           'type' => 'sql',
  640.           'query' => $sql,
  641.           'error' => $db->error,
  642.           'code' => $db->errno
  643.         ));
  644.       }
  645.      
  646.       if ($this->throw_exception_on_error) {
  647.         $e = new MeekroDBException($db->error, $sql, $db->errno);
  648.         throw $e;
  649.       }
  650.     } else if ($this->success_handler) {
  651.       $runtime = sprintf('%f', $runtime * 1000);
  652.       $success_handler = is_callable($this->success_handler) ? $this->success_handler : 'meekrodb_debugmode_handler';
  653.      
  654.       call_user_func($success_handler, array(
  655.         'query' => $sql,
  656.         'runtime' => $runtime,
  657.         'affected' => $db->affected_rows
  658.       ));
  659.     }
  660.  
  661.     // ----- END ERROR HANDLING
  662.  
  663.     $this->insert_id = $db->insert_id;
  664.     $this->affected_rows = $db->affected_rows;
  665.  
  666.     // mysqli_result->num_rows won't initially show correct results for unbuffered data
  667.     if ($is_buffered && ($result instanceof MySQLi_Result)) $this->num_rows = $result->num_rows;
  668.     else $this->num_rows = null;
  669.  
  670.     if ($row_type == 'raw' || !($result instanceof MySQLi_Result)) return $result;
  671.  
  672.     $return = array();
  673.  
  674.     if ($full_names) {
  675.       $infos = array();
  676.       foreach ($result->fetch_fields() as $info) {
  677.         if (strlen($info->table)) $infos[] = $info->table . '.' . $info->name;
  678.         else $infos[] = $info->name;
  679.       }
  680.     }
  681.  
  682.     while ($row = ($row_type == 'assoc' ? $result->fetch_assoc() : $result->fetch_row())) {
  683.       if ($full_names) $row = array_combine($infos, $row);
  684.       $return[] = $row;
  685.     }
  686.  
  687.     // free results
  688.     $result->free();
  689.     while ($db->more_results()) {
  690.       $db->next_result();
  691.       if ($result = $db->use_result()) $result->free();
  692.     }
  693.    
  694.     return $return;
  695.   }
  696.  
  697.   public function queryOneRow() { $args = func_get_args(); return call_user_func_array(array($this, 'queryFirstRow'), $args); }
  698.   public function queryFirstRow() {
  699.     $args = func_get_args();
  700.     $result = call_user_func_array(array($this, 'query'), $args);
  701.     if (!$result || !is_array($result)) return null;
  702.     return reset($result);
  703.   }
  704.  
  705.   public function queryOneList() { $args = func_get_args(); return call_user_func_array(array($this, 'queryFirstList'), $args); }
  706.   public function queryFirstList() {
  707.     $args = func_get_args();
  708.     $result = call_user_func_array(array($this, 'queryAllLists'), $args);
  709.     if (!$result || !is_array($result)) return null;
  710.     return reset($result);
  711.   }
  712.  
  713.   public function queryFirstColumn() {
  714.     $args = func_get_args();
  715.     $results = call_user_func_array(array($this, 'queryAllLists'), $args);
  716.     $ret = array();
  717.    
  718.     if (!count($results) || !count($results[0])) return $ret;
  719.    
  720.     foreach ($results as $row) {
  721.       $ret[] = $row[0];
  722.     }
  723.    
  724.     return $ret;
  725.   }
  726.  
  727.   public function queryOneColumn() {
  728.     $args = func_get_args();
  729.     $column = array_shift($args);
  730.     $results = call_user_func_array(array($this, 'query'), $args);
  731.     $ret = array();
  732.    
  733.     if (!count($results) || !count($results[0])) return $ret;
  734.     if ($column === null) {
  735.       $keys = array_keys($results[0]);
  736.       $column = $keys[0];
  737.     }
  738.    
  739.     foreach ($results as $row) {
  740.       $ret[] = $row[$column];
  741.     }
  742.    
  743.     return $ret;
  744.   }
  745.  
  746.   public function queryFirstField() {
  747.     $args = func_get_args();
  748.     $row = call_user_func_array(array($this, 'queryFirstList'), $args);
  749.     if ($row == null) return null;    
  750.     return $row[0];
  751.   }
  752.  
  753.   public function queryOneField() {
  754.     $args = func_get_args();
  755.     $column = array_shift($args);
  756.    
  757.     $row = call_user_func_array(array($this, 'queryOneRow'), $args);
  758.     if ($row == null) {
  759.       return null;
  760.     } else if ($column === null) {
  761.       $keys = array_keys($row);
  762.       $column = $keys[0];
  763.     }  
  764.    
  765.     return $row[$column];
  766.   }
  767. }
  768.  
  769. class WhereClause {
  770.   public $type = 'and'; //AND or OR
  771.   public $negate = false;
  772.   public $clauses = array();
  773.  
  774.   function __construct($type) {
  775.     $type = strtolower($type);
  776.     if ($type !== 'or' && $type !== 'and') DB::nonSQLError('you must use either WhereClause(and) or WhereClause(or)');
  777.     $this->type = $type;
  778.   }
  779.  
  780.   function add() {
  781.     $args = func_get_args();
  782.     $sql = array_shift($args);
  783.    
  784.     if ($sql instanceof WhereClause) {
  785.       $this->clauses[] = $sql;
  786.     } else {
  787.       $this->clauses[] = array('sql' => $sql, 'args' => $args);
  788.     }
  789.   }
  790.  
  791.   function negateLast() {
  792.     $i = count($this->clauses) - 1;
  793.     if (!isset($this->clauses[$i])) return;
  794.    
  795.     if ($this->clauses[$i] instanceof WhereClause) {
  796.       $this->clauses[$i]->negate();
  797.     } else {
  798.       $this->clauses[$i]['sql'] = 'NOT (' . $this->clauses[$i]['sql'] . ')';
  799.     }
  800.   }
  801.  
  802.   function negate() {
  803.     $this->negate = ! $this->negate;
  804.   }
  805.  
  806.   function addClause($type) {
  807.     $r = new WhereClause($type);
  808.     $this->add($r);
  809.     return $r;
  810.   }
  811.  
  812.   function count() {
  813.     return count($this->clauses);
  814.   }
  815.  
  816.   function textAndArgs() {
  817.     $sql = array();
  818.     $args = array();
  819.    
  820.     if (count($this->clauses) == 0) return array('(1)', $args);
  821.    
  822.     foreach ($this->clauses as $clause) {
  823.       if ($clause instanceof WhereClause) {
  824.         list($clause_sql, $clause_args) = $clause->textAndArgs();
  825.       } else {
  826.         $clause_sql = $clause['sql'];
  827.         $clause_args = $clause['args'];
  828.       }
  829.      
  830.       $sql[] = "($clause_sql)";
  831.       $args = array_merge($args, $clause_args);
  832.     }
  833.    
  834.     if ($this->type == 'and') $sql = implode(' AND ', $sql);
  835.     else $sql = implode(' OR ', $sql);
  836.    
  837.     if ($this->negate) $sql = '(NOT ' . $sql . ')';
  838.     return array($sql, $args);
  839.   }
  840.  
  841.   // backwards compatability
  842.   // we now return full WhereClause object here and evaluate it in preparseQueryParams
  843.   function text() { return $this; }
  844. }
  845.  
  846. class DBTransaction {
  847.   private $committed = false;
  848.  
  849.   function __construct() {
  850.     DB::startTransaction();
  851.   }
  852.   function __destruct() {
  853.     if (! $this->committed) DB::rollback();
  854.   }
  855.   function commit() {
  856.     DB::commit();
  857.     $this->committed = true;
  858.   }
  859.  
  860.  
  861. }
  862.  
  863. class MeekroDBException extends Exception {
  864.   protected $query = '';
  865.  
  866.   function __construct($message='', $query='', $code = 0) {
  867.     parent::__construct($message);
  868.     $this->query = $query;
  869.     $this->code = $code;
  870.   }
  871.  
  872.   public function getQuery() { return $this->query; }
  873. }
  874.  
  875. class DBHelper {
  876.   /*
  877.     verticalSlice
  878.     1. For an array of assoc rays, return an array of values for a particular key
  879.     2. if $keyfield is given, same as above but use that hash key as the key in new array
  880.   */
  881.  
  882.   public static function verticalSlice($array, $field, $keyfield = null) {
  883.     $array = (array) $array;
  884.    
  885.     $R = array();
  886.     foreach ($array as $obj) {
  887.       if (! array_key_exists($field, $obj)) die("verticalSlice: array doesn't have requested field\n");
  888.      
  889.       if ($keyfield) {
  890.         if (! array_key_exists($keyfield, $obj)) die("verticalSlice: array doesn't have requested field\n");  
  891.         $R[$obj[$keyfield]] = $obj[$field];
  892.       } else {
  893.         $R[] = $obj[$field];
  894.       }
  895.     }
  896.     return $R;
  897.   }
  898.  
  899.   /*
  900.     reIndex
  901.     For an array of assoc rays, return a new array of assoc rays using a certain field for keys
  902.   */
  903.  
  904.   public static function reIndex() {
  905.     $fields = func_get_args();
  906.     $array = array_shift($fields);
  907.     $array = (array) $array;
  908.    
  909.     $R = array();
  910.     foreach ($array as $obj) {
  911.       $target =& $R;
  912.      
  913.       foreach ($fields as $field) {
  914.         if (! array_key_exists($field, $obj)) die("reIndex: array doesn't have requested field\n");
  915.        
  916.         $nextkey = $obj[$field];
  917.         $target =& $target[$nextkey];
  918.       }
  919.       $target = $obj;
  920.     }
  921.     return $R;
  922.   }
  923. }
  924.  
  925. function meekrodb_error_handler($params) {
  926.   if (isset($params['query'])) $out[] = "QUERY: " . $params['query'];
  927.   if (isset($params['error'])) $out[] = "ERROR: " . $params['error'];
  928.   $out[] = "";
  929.  
  930.   if (php_sapi_name() == 'cli' && empty($_SERVER['REMOTE_ADDR'])) {
  931.     echo implode("\n", $out);
  932.   } else {
  933.     echo implode("<br>\n", $out);
  934.   }
  935.  
  936.   die;
  937. }
  938.  
  939. function meekrodb_debugmode_handler($params) {
  940.   echo "QUERY: " . $params['query'] . " [" . $params['runtime'] . " ms]";
  941.   if (php_sapi_name() == 'cli' && empty($_SERVER['REMOTE_ADDR'])) {
  942.     echo "\n";
  943.   } else {
  944.     echo "<br>\n";
  945.   }
  946. }
  947.  
  948. class MeekroDBEval {
  949.   public $text = '';
  950.  
  951.   function __construct($text) {
  952.     $this->text = $text;
  953.   }
  954. }
  955.  
  956. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!