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