classes/xdb.php

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