classes/xdb.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2010 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  **************************************************************************/
  21
  22 class XDB
  23 {
  24     private static $mysqli = null;
  25
  26     public static function connect()
  27     {
  28         global $globals;
  29         self::$mysqli = new mysqli($globals->dbhost, $globals->dbuser, $globals->dbpwd, $globals->dbdb);
  30         if ($globals->debug & DEBUG_BT) {
  31             $bt = new PlBacktrace('MySQL');
  32             if (mysqli_connect_errno()) {
  33                 $bt->newEvent("MySQLI connection", 0, mysqli_connect_error());
  34                 return false;
  35             }
  36         }
  37         self::$mysqli->autocommit(true);
  38         self::$mysqli->set_charset($globals->dbcharset);
  39         return true;
  40     }
  41
  42     public static function _prepare($args)
  43     {
  44         global $globals;
  45         $query    = array_map(Array('XDB', 'escape'), $args);
  46         $query[0] = preg_replace('/#([a-z0-9]*)#/', $globals->dbprefix . '$1', $args[0]);
  47         $query[0] = str_replace('%',   '%%', $query[0]);
  48         $query[0] = str_replace('{?}', '%s', $query[0]);
  49         return call_user_func_array('sprintf', $query);
  50     }
  51
  52     public static function _reformatQuery($query)
  53     {
  54         $query  = preg_split("/\n\\s*/", trim($query));
  55         $length = 0;
  56         foreach ($query as $key=>$line) {
  57             $local = -2;
  58             if (preg_match('/^([A-Z]+(?:\s+(?:JOIN|BY|FROM|INTO))?)\s+(.*)/u', $line, $matches)
  59             && $matches[1] != 'AND' && $matches[1] != 'OR')
  60             {
  61                 $local  = strlen($matches[1]);
  62                 $line   = $matches[1] . '  ' . $matches[2];
  63                 $length = max($length, $local);
  64             }
  65             $query[$key] = array($line, $local);
  66         }
  67         $res = '';
  68         foreach ($query as $array) {
  69             list($line, $local) = $array;
  70             $local   = max(0, $length - $local);
  71             $res    .= str_repeat(' ', $local) . $line . "\n";
  72             $length += 2 * (substr_count($line, '(') - substr_count($line, ')'));
  73         }
  74         return $res;
  75     }
  76
  77     public static function _query($query)
  78     {
  79         global $globals;
  80
  81         if (!self::$mysqli && !self::connect()) {
  82             header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error');
  83             Platal::page()->kill('Impossible de se connecter à la base de données.');
  84             exit;
  85         }
  86
  87         if ($globals->debug & DEBUG_BT) {
  88             $explain = array();
  89             if (strpos($query, 'FOUND_ROWS()') === false) {
  90                 $res = self::$mysqli->query("EXPLAIN $query");
  91                 if ($res) {
  92                     while ($row = $res->fetch_assoc()) {
  93                         $explain[] = $row;
  94                     }
  95                     $res->free();
  96                 }
  97             }
  98             PlBacktrace::$bt['MySQL']->start(XDB::_reformatQuery($query));
  99         }
 100
 101         $res = XDB::$mysqli->query($query);
 102
 103         if ($globals->debug & DEBUG_BT) {
 104             PlBacktrace::$bt['MySQL']->stop(@$res->num_rows ? $res->num_rows : self::$mysqli->affected_rows,
 105                                             self::$mysqli->error,
 106                                             $explain);
 107         }
 108
 109         if ($res === false) {
 110             header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error');
 111             if (strpos($query, 'INSERT') === false && strpos($query, 'UPDATE') === false
 112                 && strpos($query, 'REPLACE') === false && strpos($query, 'DELETE') === false) {
 113                 $text = 'Erreur lors de l\'interrogation de la base de données';
 114             } else {
 115                 $text = 'Erreur lors de l\'écriture dans la base de données';
 116             }
 117             if (php_sapi_name() == 'cli') {
 118                 $text .= "\n" . XDB::_reformatQuery($query)
 119                        . "\n" . XDB::$mysqli->error;
 120             } else if ($globals->debug) {
 121                 $text .= '<pre>' . pl_entities(XDB::_reformatQuery($query)) . '</pre>';
 122             } else {
 123                 $file = fopen($globals->spoolroot . '/spool/tmp/query_errors', 'a');
 124                 fwrite($file, '<pre>' . date("Y-m-d G:i:s") . '</pre>'
 125                             . '<pre>' . pl_entities(XDB::_reformatQuery($query)) . '</pre>'
 126                             . '<pre>' . XDB::$mysqli->error . '</pre>'
 127                             . "--------------------------------------------------------------------------------\n");
 128                 fclose($file);
 129             }
 130             Platal::page()->kill($text);
 131             exit;
 132         }
 133         return $res;
 134     }
 135
 136     private static function queryv($query)
 137     {
 138         return new XOrgDBResult(self::_prepare($query));
 139     }
 140
 141     public static function query()
 142     {
 143         return self::queryv(func_get_args());
 144     }
 145
 146     public static function format()
 147     {
 148         return self::_prepare(func_get_args());
 149     }
 150
 151     // Produce the SQL statement for setting/unsetting a flag
 152     public static function changeFlag($fieldname, $flagname, $state)
 153     {
 154         if ($state) {
 155             return XDB::format($fieldname . ' = CONCAT({?}, \',\', ' . $fieldname . ')', $flagname);
 156         } else {
 157             return XDB::format($fieldname . ' = REPLACE(' . $fieldname . ', {?}, \'\')', $flagname);
 158         }
 159     }
 160
 161     // Produce the SQL statement representing an array
 162     public static function formatArray(array $array)
 163     {
 164         return self::escape($array);
 165     }
 166
 167     const WILDCARD_EXACT    = 0x00;
 168     const WILDCARD_PREFIX   = 0x01;
 169     const WILDCARD_SUFFIX   = 0x02;
 170     const WILDCARD_CONTAINS = 0x03; // WILDCARD_PREFIX | WILDCARD_SUFFIX
 171
 172     // Returns the SQL statement for a wildcard search.
 173     public static function formatWildcards($mode, $text)
 174     {
 175         if ($mode == self::WILDCARD_EXACT) {
 176             return XDB::format(' = {?}', $text);
 177         } else {
 178             $text = str_replace(array('%', '_'), array('\%', '\_'), $text);
 179             if ($mode & self::WILDCARD_PREFIX) {
 180                 $text = $text . '%';
 181             }
 182             if ($mode & self::WILDCARD_SUFFIX) {
 183                 $text = '%' . $text;
 184             }
 185             return XDB::format(" LIKE {?}", $text);
 186         }
 187     }
 188
 189     // Returns a FIELD(blah, 3, 1, 2) for use in an order with custom orders
 190     public static function formatCustomOrder($field, $values)
 191     {
 192         return 'FIELD( ' . $field . ', ' . implode(', ', array_map(array('XDB', 'escape'), $values)) . ')';
 193     }
 194
 195     public static function execute()
 196     {
 197         global $globals;
 198         $args = func_get_args();
 199         if ($globals->mode != 'rw' && !strpos($args[0], 'logger')) {
 200             return;
 201         }
 202         return self::_query(XDB::_prepare($args));
 203     }
 204
 205     public static function iterator()
 206     {
 207         return new XOrgDBIterator(self::_prepare(func_get_args()));
 208     }
 209
 210     public static function iterRow()
 211     {
 212         return new XOrgDBIterator(self::_prepare(func_get_args()), MYSQL_NUM);
 213     }
 214
 215     private static function findQuery($params, $default = array())
 216     {
 217         for ($i = 0 ; $i < count($default) ; ++$i) {
 218             $is_query = false;
 219             foreach (array('insert', 'select', 'replace', 'delete', 'update') as $kwd) {
 220                 if (stripos($params[0], $kwd) !== false) {
 221                     $is_query = true;
 222                     break;
 223                 }
 224             }
 225             if ($is_query) {
 226                 break;
 227             } else {
 228                 $default[$i] = array_shift($params);
 229             }
 230         }
 231         return array($default, $params);
 232     }
 233
 234     /** Fetch all rows returned by the given query.
 235      * This functions can take 2 optional arguments (cf XOrgDBResult::fetchAllRow()).
 236      * Optional arguments are given *before* the query.
 237      */
 238     public static function fetchAllRow()
 239     {
 240         list($args, $query) = self::findQuery(func_get_args(), array(false, false));
 241         return self::queryv($query)->fetchAllRow($args[0], $args[1]);
 242     }
 243
 244     /** Fetch all rows returned by the given query.
 245      * This functions can take 2 optional arguments (cf XOrgDBResult::fetchAllAssoc()).
 246      * Optional arguments are given *before* the query.
 247      */
 248     public static function fetchAllAssoc()
 249     {
 250         list($args, $query) = self::findQuery(func_get_args(), array(false, false));
 251         return self::queryv($query)->fetchAllAssoc($args[0], $args[1]);
 252     }
 253
 254     public static function fetchOneCell()
 255     {
 256         list($args, $query) = self::findQuery(func_get_args());
 257         return self::queryv($query)->fetchOneCell();
 258     }
 259
 260     public static function fetchOneRow()
 261     {
 262         list($args, $query) = self::findQuery(func_get_args());
 263         return self::queryv($query)->fetchOneRow();
 264     }
 265
 266     public static function fetchOneAssoc()
 267     {
 268         list($args, $query) = self::findQuery(func_get_args());
 269         return self::queryv($query)->fetchOneAssoc();
 270     }
 271
 272     /** Fetch a column from the result of the given query.
 273      * This functions can take 1 optional arguments (cf XOrgDBResult::fetchColumn()).
 274      * Optional arguments are given *before* the query.
 275      */
 276     public static function fetchColumn()
 277     {
 278         list($args, $query) = self::findQuery(func_get_args(), array(0));
 279         return self::queryv($query)->fetchColumn();
 280     }
 281
 282     public static function insertId()
 283     {
 284         return self::$mysqli->insert_id;
 285     }
 286
 287     public static function errno()
 288     {
 289         return self::$mysqli->errno;
 290     }
 291
 292     public static function error()
 293     {
 294         return self::$mysqli->error;
 295     }
 296
 297     public static function affectedRows()
 298     {
 299         return self::$mysqli->affected_rows;
 300     }
 301
 302     public static function escape($var)
 303     {
 304         switch (gettype($var)) {
 305           case 'boolean':
 306             return $var ? 1 : 0;
 307
 308           case 'integer':
 309           case 'double':
 310           case 'float':
 311             return $var;
 312
 313           case 'string':
 314             return "'".addslashes($var)."'";
 315
 316           case 'NULL':
 317             return 'NULL';
 318
 319           case 'object':
 320             if ($var instanceof PlFlagSet) {
 321                 return "'" . addslashes($var->flags()) . "'";
 322             } else {
 323                 return "'".addslashes(serialize($var))."'";
 324             }
 325
 326           case 'array':
 327             return '(' . implode(', ', array_map(array('XDB', 'escape'), $var)) . ')';
 328
 329           default:
 330             die(var_export($var, true).' is not a valid for a database entry');
 331         }
 332     }
 333 }
 334
 335 class XOrgDBResult
 336 {
 337
 338     private $_res;
 339
 340     public function __construct($query)
 341     {
 342         $this->_res = XDB::_query($query);
 343     }
 344
 345     public function free()
 346     {
 347         if ($this->_res) {
 348             $this->_res->free();
 349         }
 350         unset($this);
 351     }
 352
 353     protected function _fetchRow()
 354     {
 355         return $this->_res ? $this->_res->fetch_row() : null;
 356     }
 357
 358     protected function _fetchAssoc()
 359     {
 360         return $this->_res ? $this->_res->fetch_assoc() : null;
 361     }
 362
 363     public function fetchAllRow($id = false, $keep_array = false)
 364     {
 365         $result = Array();
 366         if (!$this->_res) {
 367             return $result;
 368         }
 369         while (($data = $this->_res->fetch_row())) {
 370             if ($id !== false) {
 371                 $key = $data[$id];
 372                 unset($data[$id]);
 373                 if (!$keep_array && count($data) == 1) {
 374                     reset($data);
 375                     $result[$key] = current($data);
 376                 } else {
 377                     $result[$key] = $data;
 378                 }
 379             } else {
 380                 $result[] = $data;
 381             }
 382         }
 383         $this->free();
 384         return $result;
 385     }
 386
 387     public function fetchAllAssoc($id = false, $keep_array = false)
 388     {
 389         $result = Array();
 390         if (!$this->_res) {
 391             return $result;
 392         }
 393         while (($data = $this->_res->fetch_assoc())) {
 394             if ($id !== false) {
 395                 $key = $data[$id];
 396                 unset($data[$id]);
 397                 if (!$keep_array && count($data) == 1) {
 398                     reset($data);
 399                     $result[$key] = current($data);
 400                 } else {
 401                     $result[$key] = $data;
 402                 }
 403             } else {
 404                 $result[] = $data;
 405             }
 406         }
 407         $this->free();
 408         return $result;
 409     }
 410
 411     public function fetchOneAssoc()
 412     {
 413         $tmp = $this->_fetchAssoc();
 414         $this->free();
 415         return $tmp;
 416     }
 417
 418     public function fetchOneRow()
 419     {
 420         $tmp = $this->_fetchRow();
 421         $this->free();
 422         return $tmp;
 423     }
 424
 425     public function fetchOneCell()
 426     {
 427         $tmp = $this->_fetchRow();
 428         $this->free();
 429         return $tmp[0];
 430     }
 431
 432     public function fetchColumn($key = 0)
 433     {
 434         $res = Array();
 435         if (is_numeric($key)) {
 436             while($tmp = $this->_fetchRow()) {
 437                 $res[] = $tmp[$key];
 438             }
 439         } else {
 440             while($tmp = $this->_fetchAssoc()) {
 441                 $res[] = $tmp[$key];
 442             }
 443         }
 444         $this->free();
 445         return $res;
 446     }
 447
 448     public function fetchOneField()
 449     {
 450         return $this->_res ? $this->_res->fetch_field() : null;
 451     }
 452
 453     public function fetchFields()
 454     {
 455         $res = array();
 456         while ($res[] = $this->fetchOneField());
 457         return $res;
 458     }
 459
 460     public function numRows()
 461     {
 462         return $this->_res ? $this->_res->num_rows : 0;
 463     }
 464
 465     public function fieldCount()
 466     {
 467         return $this->_res ? $this->_res->field_count : 0;
 468     }
 469 }
 470
 471 require_once dirname(__FILE__) . '/pliterator.php';
 472
 473 class XOrgDBIterator extends XOrgDBResult implements PlIterator
 474 {
 475     private $_result;
 476     private $_pos;
 477     private $_total;
 478     private $_fpos;
 479     private $_fields;
 480     private $_mode = MYSQL_ASSOC;
 481
 482     public function __construct($query, $mode = MYSQL_ASSOC)
 483     {
 484         parent::__construct($query);
 485         $this->_pos    = 0;
 486         $this->_total  = $this->numRows();
 487         $this->_fpost  = 0;
 488         $this->_fields = $this->fieldCount();
 489         $this->_mode   = $mode;
 490     }
 491
 492     public function next()
 493     {
 494         $this->_pos ++;
 495         if ($this->_pos > $this->_total) {
 496             $this->free();
 497             unset($this);
 498             return null;
 499         }
 500         return $this->_mode != MYSQL_ASSOC ? $this->_fetchRow() : $this->_fetchAssoc();
 501     }
 502
 503     public function first()
 504     {
 505         return $this->_pos == 1;
 506     }
 507
 508     public function last()
 509     {
 510         return $this->_pos == $this->_total;
 511     }
 512
 513     public function total()
 514     {
 515         return $this->_total;
 516     }
 517
 518     public function nextField()
 519     {
 520         $this->_fpos++;
 521         if ($this->_fpos > $this->_fields) {
 522             return null;
 523         }
 524         return $this->fetchOneField();
 525     }
 526
 527     public function firstField()
 528     {
 529         return $this->_fpos == 1;
 530     }
 531
 532     public function lastField()
 533     {
 534         return $this->_fpos == $this->_fields;
 535     }
 536
 537     public function totalFields()
 538     {
 539         return $this->_fields;
 540     }
 541 }
 542
 543 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 544 ?>