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