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 run($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             throw new XDBException(XDB::reformatQuery($query), XDB::$mysqli->error);
 111         }
 112         return $res;
 113     }
 114
 115     private static function queryv($query)
 116     {
 117         return new XDBResult(self::prepare($query));
 118     }
 119
 120     public static function query()
 121     {
 122         return self::queryv(func_get_args());
 123     }
 124
 125     public static function format()
 126     {
 127         return self::prepare(func_get_args());
 128     }
 129
 130     // Produce the SQL statement for setting/unsetting a flag
 131     public static function changeFlag($fieldname, $flagname, $state)
 132     {
 133         if ($state) {
 134             return XDB::format($fieldname . ' = CONCAT({?}, \',\', ' . $fieldname . ')', $flagname);
 135         } else {
 136             return XDB::format($fieldname . ' = REPLACE(' . $fieldname . ', {?}, \'\')', $flagname);
 137         }
 138     }
 139
 140     // Produce the SQL statement representing an array
 141     public static function formatArray(array $array)
 142     {
 143         return self::escape($array);
 144     }
 145
 146     const WILDCARD_EXACT    = 0x00;
 147     const WILDCARD_PREFIX   = 0x01;
 148     const WILDCARD_SUFFIX   = 0x02;
 149     const WILDCARD_CONTAINS = 0x03; // WILDCARD_PREFIX | WILDCARD_SUFFIX
 150
 151     // Produce a valid XDB argument that get formatted as a wildcard
 152     // according to the given mode.
 153     //
 154     // Example:
 155     // XDB::query("SELECT * FROM table WHERE field {?}", XDB::wildcard($text, WILDCARD_EXACT));
 156     public static function wildcard($mode, $value)
 157     {
 158       return new XDBWildcard($value, $mode);
 159     }
 160
 161     // Returns the SQL statement for a wildcard search.
 162     public static function formatWildcards($mode, $text)
 163     {
 164         return XDB::wildcard($mode, $text)->format();
 165     }
 166
 167     // Returns a FIELD(blah, 3, 1, 2) for use in an order with custom orders
 168     public static function formatCustomOrder($field, $values)
 169     {
 170         return 'FIELD( ' . $field . ', ' . implode(', ', array_map(array('XDB', 'escape'), $values)) . ')';
 171     }
 172
 173     public static function execute()
 174     {
 175         global $globals;
 176         $args = func_get_args();
 177         if ($globals->mode != 'rw' && !strpos($args[0], 'logger')) {
 178             return;
 179         }
 180         return self::run(XDB::prepare($args));
 181     }
 182
 183     public static function iterator()
 184     {
 185         return new XDBIterator(self::prepare(func_get_args()));
 186     }
 187
 188     public static function iterRow()
 189     {
 190         return new XDBIterator(self::prepare(func_get_args()), MYSQL_NUM);
 191     }
 192
 193     private static function findQuery($params, $default = array())
 194     {
 195         for ($i = 0 ; $i < count($default) ; ++$i) {
 196             $is_query = false;
 197             foreach (array('insert', 'select', 'replace', 'delete', 'update') as $kwd) {
 198                 if (stripos($params[0], $kwd) !== false) {
 199                     $is_query = true;
 200                     break;
 201                 }
 202             }
 203             if ($is_query) {
 204                 break;
 205             } else {
 206                 $default[$i] = array_shift($params);
 207             }
 208         }
 209         return array($default, $params);
 210     }
 211
 212     /** Fetch all rows returned by the given query.
 213      * This functions can take 2 optional arguments (cf XDBResult::fetchAllRow()).
 214      * Optional arguments are given *before* the query.
 215      */
 216     public static function fetchAllRow()
 217     {
 218         list($args, $query) = self::findQuery(func_get_args(), array(false, false));
 219         return self::queryv($query)->fetchAllRow($args[0], $args[1]);
 220     }
 221
 222     /** Fetch all rows returned by the given query.
 223      * This functions can take 2 optional arguments (cf XDBResult::fetchAllAssoc()).
 224      * Optional arguments are given *before* the query.
 225      */
 226     public static function fetchAllAssoc()
 227     {
 228         list($args, $query) = self::findQuery(func_get_args(), array(false, false));
 229         return self::queryv($query)->fetchAllAssoc($args[0], $args[1]);
 230     }
 231
 232     public static function fetchOneCell()
 233     {
 234         list($args, $query) = self::findQuery(func_get_args());
 235         return self::queryv($query)->fetchOneCell();
 236     }
 237
 238     public static function fetchOneRow()
 239     {
 240         list($args, $query) = self::findQuery(func_get_args());
 241         return self::queryv($query)->fetchOneRow();
 242     }
 243
 244     public static function fetchOneAssoc()
 245     {
 246         list($args, $query) = self::findQuery(func_get_args());
 247         return self::queryv($query)->fetchOneAssoc();
 248     }
 249
 250     /** Fetch a column from the result of the given query.
 251      * This functions can take 1 optional arguments (cf XDBResult::fetchColumn()).
 252      * Optional arguments are given *before* the query.
 253      */
 254     public static function fetchColumn()
 255     {
 256         list($args, $query) = self::findQuery(func_get_args(), array(0));
 257         return self::queryv($query)->fetchColumn();
 258     }
 259
 260     public static function insertId()
 261     {
 262         return self::$mysqli->insert_id;
 263     }
 264
 265     public static function errno()
 266     {
 267         return self::$mysqli->errno;
 268     }
 269
 270     public static function error()
 271     {
 272         return self::$mysqli->error;
 273     }
 274
 275     public static function affectedRows()
 276     {
 277         return self::$mysqli->affected_rows;
 278     }
 279
 280     public static function escape($var)
 281     {
 282         switch (gettype($var)) {
 283           case 'boolean':
 284             return $var ? 1 : 0;
 285
 286           case 'integer':
 287           case 'double':
 288           case 'float':
 289             return $var;
 290
 291           case 'string':
 292             return "'".addslashes($var)."'";
 293
 294           case 'NULL':
 295             return 'NULL';
 296
 297           case 'object':
 298             if ($var instanceof XDBFormat) {
 299                 return $var->format();
 300             } else {
 301                 return "'".addslashes(serialize($var))."'";
 302             }
 303
 304           case 'array':
 305             return '(' . implode(', ', array_map(array('XDB', 'escape'), $var)) . ')';
 306
 307           default:
 308             die(var_export($var, true).' is not a valid for a database entry');
 309         }
 310     }
 311 }
 312
 313 class XDBException extends PlException
 314 {
 315     public function __construct($query, $error)
 316     {
 317         if (strpos($query, 'INSERT') === false && strpos($query, 'UPDATE') === false
 318             && strpos($query, 'REPLACE') === false && strpos($query, 'DELETE') === false) {
 319             $text = 'Erreur lors de l\'interrogation de la base de données';
 320         } else {
 321             $text = 'Erreur lors de l\'écriture dans la base de données';
 322         }
 323         parent::__construct($text, $query . "\n" . $error);
 324     }
 325 }
 326
 327 interface XDBFormat
 328 {
 329     public function format();
 330 }
 331
 332 class XDBWildcard implements XDBFormat
 333 {
 334     private $value;
 335     private $mode;
 336
 337     public function __construct($value, $mode)
 338     {
 339         $this->value = $value;
 340         $this->mode  = $mode;
 341     }
 342
 343     public function format()
 344     {
 345         if ($this->mode == XDB::WILDCARD_EXACT) {
 346             return XDB::format(' = {?}', $this->value);
 347         } else {
 348             $text = str_replace(array('%', '_'), array('\%', '\_'), $this->value);
 349             if ($this->mode & XDB::WILDCARD_PREFIX) {
 350                 $text = $text . '%';
 351             }
 352             if ($this->mode & XDB::WILDCARD_SUFFIX) {
 353                 $text = '%' . $text;
 354             }
 355             return XDB::format(" LIKE {?}", $text);
 356         }
 357     }
 358 }
 359
 360
 361 class XDBResult
 362 {
 363     private $res;
 364
 365     public function __construct($query)
 366     {
 367         $this->res = XDB::run($query);
 368     }
 369
 370     public function free()
 371     {
 372         if ($this->res) {
 373             $this->res->free();
 374         }
 375         unset($this);
 376     }
 377
 378     protected function fetchRow()
 379     {
 380         return $this->res ? $this->res->fetch_row() : null;
 381     }
 382
 383     protected function fetchAssoc()
 384     {
 385         return $this->res ? $this->res->fetch_assoc() : null;
 386     }
 387
 388     public function fetchAllRow($id = false, $keep_array = false)
 389     {
 390         $result = Array();
 391         if (!$this->res) {
 392             return $result;
 393         }
 394         while (($data = $this->res->fetch_row())) {
 395             if ($id !== false) {
 396                 $key = $data[$id];
 397                 unset($data[$id]);
 398                 if (!$keep_array && count($data) == 1) {
 399                     reset($data);
 400                     $result[$key] = current($data);
 401                 } else {
 402                     $result[$key] = $data;
 403                 }
 404             } else {
 405                 $result[] = $data;
 406             }
 407         }
 408         $this->free();
 409         return $result;
 410     }
 411
 412     public function fetchAllAssoc($id = false, $keep_array = false)
 413     {
 414         $result = Array();
 415         if (!$this->res) {
 416             return $result;
 417         }
 418         while (($data = $this->res->fetch_assoc())) {
 419             if ($id !== false) {
 420                 $key = $data[$id];
 421                 unset($data[$id]);
 422                 if (!$keep_array && count($data) == 1) {
 423                     reset($data);
 424                     $result[$key] = current($data);
 425                 } else {
 426                     $result[$key] = $data;
 427                 }
 428             } else {
 429                 $result[] = $data;
 430             }
 431         }
 432         $this->free();
 433         return $result;
 434     }
 435
 436     public function fetchOneAssoc()
 437     {
 438         $tmp = $this->fetchAssoc();
 439         $this->free();
 440         return $tmp;
 441     }
 442
 443     public function fetchOneRow()
 444     {
 445         $tmp = $this->fetchRow();
 446         $this->free();
 447         return $tmp;
 448     }
 449
 450     public function fetchOneCell()
 451     {
 452         $tmp = $this->fetchRow();
 453         $this->free();
 454         return $tmp[0];
 455     }
 456
 457     public function fetchColumn($key = 0)
 458     {
 459         $res = Array();
 460         if (is_numeric($key)) {
 461             while($tmp = $this->fetchRow()) {
 462                 $res[] = $tmp[$key];
 463             }
 464         } else {
 465             while($tmp = $this->fetchAssoc()) {
 466                 $res[] = $tmp[$key];
 467             }
 468         }
 469         $this->free();
 470         return $res;
 471     }
 472
 473     public function fetchOneField()
 474     {
 475         return $this->res ? $this->res->fetch_field() : null;
 476     }
 477
 478     public function fetchFields()
 479     {
 480         $res = array();
 481         while ($res[] = $this->fetchOneField());
 482         return $res;
 483     }
 484
 485     public function numRows()
 486     {
 487         return $this->res ? $this->res->num_rows : 0;
 488     }
 489
 490     public function fieldCount()
 491     {
 492         return $this->res ? $this->res->field_count : 0;
 493     }
 494 }
 495
 496
 497 class XDBIterator extends XDBResult implements PlIterator
 498 {
 499     private $result;
 500     private $pos;
 501     private $total;
 502     private $fpos;
 503     private $fields;
 504     private $mode = MYSQL_ASSOC;
 505
 506     public function __construct($query, $mode = MYSQL_ASSOC)
 507     {
 508         parent::__construct($query);
 509         $this->pos    = 0;
 510         $this->total  = $this->numRows();
 511         $this->fpost  = 0;
 512         $this->fields = $this->fieldCount();
 513         $this->mode   = $mode;
 514     }
 515
 516     public function next()
 517     {
 518         $this->pos ++;
 519         if ($this->pos > $this->total) {
 520             $this->free();
 521             unset($this);
 522             return null;
 523         }
 524         return $this->mode != MYSQL_ASSOC ? $this->fetchRow() : $this->fetchAssoc();
 525     }
 526
 527     public function first()
 528     {
 529         return $this->pos == 1;
 530     }
 531
 532     public function last()
 533     {
 534         return $this->pos == $this->total;
 535     }
 536
 537     public function total()
 538     {
 539         return $this->total;
 540     }
 541
 542     public function nextField()
 543     {
 544         $this->fpos++;
 545         if ($this->fpos > $this->fields) {
 546             return null;
 547         }
 548         return $this->fetchOneField();
 549     }
 550
 551     public function firstField()
 552     {
 553         return $this->fpos == 1;
 554     }
 555
 556     public function lastField()
 557     {
 558         return $this->fpos == $this->fields;
 559     }
 560
 561     public function totalFields()
 562     {
 563         return $this->fields;
 564     }
 565 }
 566
 567 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 568 ?>