classes/pldbtableentry.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 PlDBBadValueException extends PlException
  23 {
  24     public function __construct($value, PlDBTableField $field, $reason)
  25     {
  26         parent::__construct('Erreur lors de l\'accès à la base de données',
  27                             'Illegal value '. (is_null($value) ? '(null)' : '(\'' . $value . '\')')
  28                             . ' for field (\'' . $field->table->table . '.' . $field->name . '\'): '
  29                             . $reason);
  30     }
  31 }
  32
  33 class PlDBNoSuchFieldException extends PlException
  34 {
  35     public function __construct($field, PlDBTable $table)
  36     {
  37         parent::__construct('Erreur lors de l\'accès à la base de données',
  38                             'No such field ' . $field . ' in table ' . $table->table);
  39     }
  40 }
  41
  42 class PlDBNoSuchKeyException extends PlException
  43 {
  44     public function __construct($key, PlDBTable $table)
  45     {
  46         parent::__construct('Erreur lors de l\'accès à la base de données',
  47                             'No such key ' . $key . ' in table ' . $table->table);
  48     }
  49 }
  50
  51
  52 class PlDBIncompleteEntryDescription extends PlException
  53 {
  54     public function __construct($field, PlDBTable $table)
  55     {
  56         parent::__construct('Erreur lors de l\'accès à la base de données',
  57                             'The field ' . $field . ' is required to describe an entry in table '
  58                             . $table->table);
  59     }
  60 }
  61
  62 class PlDBTableField
  63 {
  64     public $table;
  65
  66     public $name;
  67     public $inPrimaryKey;
  68
  69     public $type;
  70     public $typeLength;
  71     public $typeParameters;
  72
  73     public $allowNull;
  74     public $defaultValue;
  75     public $autoIncrement;
  76
  77     private $formatter;
  78
  79     public function __construct(array $column)
  80     {
  81         $this->name = $column['Field'];
  82         $this->typeParameters = explode(' ', str_replace(array('(', ')', ',', '\''), ' ',
  83                                                          $column['Type']));
  84         $this->type = array_shift($this->typeParameters);
  85         if ($this->type == 'enum' || $this->type == 'set') {
  86             $this->typeParameters = new PlFlagSet(implode(',', $this->typeParameters));
  87         } else if (ctype_digit($this->typeParameters[0])) {
  88             $this->typeLength = intval($this->typeParameters[0]);
  89             array_shift($this->typeParameters);
  90         }
  91         $this->allowNull = ($column['Null'] === 'YES');
  92         $this->autoIncrement = (strpos($column['Extra'], 'auto_increment') !== false);
  93         $this->inPrimaryKey  = ($column['Key'] == 'PRI');
  94
  95         try {
  96             $this->defaultValue = $this->format($column['Default']);
  97         } catch (PlDBBadValueException $e) {
  98             $this->defaultValue = null;
  99         }
 100     }
 101
 102     public function registerFormatter($class)
 103     {
 104         $this->formatter = $class;
 105     }
 106
 107     public function format($value, $badNullFallbackToDefault = false)
 108     {
 109         if (is_null($value)) {
 110             if ($this->allowNull || $this->autoIncrement) {
 111                 return $value;
 112             }
 113             if ($badNullFallbackToDefault) {
 114                 return $this->defaultValue;
 115             }
 116             throw new PlDBBadValueException($value, $this, 'null not allowed');
 117         } else if (!is_null($this->formatter)) {
 118             $class = $this->formatter;
 119             $value = new $class($this, $value);
 120         } else if ($this->type == 'enum') {
 121             if (!$this->typeParameters->hasFlag($value)) {
 122                 throw new PlDBBadValueException($value, $this, 'invalid value for enum ' . $this->typeParameters->flags());
 123             }
 124             return $value;
 125         } else if ($this->type == 'set') {
 126             $value = new PlFlagSet($value);
 127             foreach ($value as $flag) {
 128                 if (!$this->typeParameters->hasFlag($flag)) {
 129                     throw new PlDBBadValueException($value, $this, 'invalid flag for set ' . $this->typeParameters->flags());
 130                 }
 131             }
 132             return $value;
 133         } else if (ends_with($this->type, 'int')) {
 134             if (!is_int($value) && !ctype_digit($value)) {
 135                 throw new PlDBBadValueException($value, $this, 'value is not an integer');
 136             }
 137             $value = intval($value);
 138             if (count($this->typeParameters) > 0 && $this->typeParameters[0] == 'unsigned') {
 139                 if ($value < 0) {
 140                     throw new PlDBBadValueException($value, $this, 'value is negative in an unsigned field');
 141                 }
 142             }
 143             /* TODO: Check bounds */
 144             return $value;
 145         } else if (ends_with($this->type, 'char')) {
 146             if (strlen($value) > $this->typeLength) {
 147                 throw new PlDBBadValueException($value, $this, 'value is expected to be at most ' . $this->typeLength . ' characters long, ' . strlen($value) . ' given');
 148             }
 149             return $value;
 150         } else if (starts_with($this->type, 'date') || $this->type == 'timestamp') {
 151             return new DateFieldFormatter($this, $value);
 152         }
 153         return $value;
 154     }
 155 }
 156
 157 interface PlDBTableFieldFormatter extends XDBFormat
 158 {
 159     public function __construct(PlDBTableField $field, $value);
 160 }
 161
 162 class DateFieldFormatter implements PlDBTableFieldFormatter
 163 {
 164     private $datetime;
 165     private $storageFormat;
 166
 167     public function __construct(PlDBTableField $field, $date)
 168     {
 169         $date = $value;
 170         $this->datetime = make_datetime($value);
 171         if (is_null($this->datetime)) {
 172             throw new PlDBBadValueException($date, $field, 'value is expected to be a date/time, ' . $date . ' given');
 173         }
 174         if ($field->type == 'date') {
 175             $this->storageFormat = 'Y-m-d';
 176         } else if ($field->type == 'datetime') {
 177             $this->storageFormat = 'Y-m-d H:i:s';
 178         } else {
 179             $this->storageFormat = 'U';
 180         }
 181     }
 182
 183     public function format()
 184     {
 185         return XDB::escape($this->datetime->format($this->storageFormat));
 186     }
 187
 188     public function date($format)
 189     {
 190         return $this->datetime->format($format);
 191     }
 192 }
 193
 194 class JSonFieldFormatter implements PlDBTableFieldFormatter, ArrayAccess
 195 {
 196     private $data;
 197
 198     public function __construct(PlDBTableField $field, $data)
 199     {
 200         if (strpos($field->type, 'text') === false) {
 201             throw new PlDBBadValueException($data, $field, 'json formatting requires a text field');
 202         }
 203
 204         if (is_string($data)) {
 205             $this->data = json_decode($data, true);
 206         } else if (is_object($data)) {
 207             $this->data = json_decode(json_encode($data), true);
 208         } else if (is_array($data)) {
 209             $this->data = $data;
 210         }
 211
 212         if (is_null($this->data)) {
 213             throw new PlDBBadValueException($data, $field, 'cannot interpret data as json: ' . $data);
 214         }
 215     }
 216
 217     public function format()
 218     {
 219         return XDB::escape(json_encode($this->data));
 220     }
 221
 222     public function offsetExists($offset)
 223     {
 224         return isset($this->data[$offset]);
 225     }
 226
 227     public function offsetGet($offset)
 228     {
 229         return $this->data[$offset];
 230     }
 231
 232     public function offsetSet($offset, $value)
 233     {
 234         $this->data[$offset] = $value;
 235     }
 236
 237     public function offsetUnset($offset)
 238     {
 239         unset($this->data[$offset]);
 240     }
 241 }
 242
 243
 244 /** This class aims at providing a simple interface to interact with a single
 245  * table of a database. It is implemented as a wrapper around XDB.
 246  */
 247 class PlDBTable
 248 {
 249     const PRIMARY_KEY = 'PRIMARY';
 250
 251     public $table;
 252
 253     private $schema;
 254     private $primaryKey;
 255     private $uniqueKeys;
 256     private $multipleKeys;
 257     private $mutableFields;
 258
 259     public function __construct($table)
 260     {
 261         $this->table = $table;
 262         $this->schema();
 263     }
 264
 265     private function parseSchema(PlIterator $schema, PlIterator $keys)
 266     {
 267         $this->schema = array();
 268         $this->primaryKey = array();
 269         $this->uniqueKeys = array();
 270         $this->multipleKeys = array();
 271         $this->mutableFields = array();
 272         while ($column = $schema->next()) {
 273             $field = new PlDBTableField($column);
 274             $this->schema[$field->name] = $field;
 275             if (!$field->inPrimaryKey) {
 276                 $this->mutableFields[] = $field->name;
 277             }
 278         }
 279         while ($column = $keys->next()) {
 280             $name     = $column['Key_name'];
 281             $multiple = intval($column['Non_unique']) != 0;
 282             $field    = $column['Column_name'];
 283             if ($multiple) {
 284                 if (!isset($this->multipleKeys[$name])) {
 285                     $this->multipleKeys[$name] = array();
 286                 }
 287                 $this->multipleKeys[$name][] = $field;
 288             } else if ($name == self::PRIMARY_KEY) {
 289                 $this->primaryKey[] = $field;
 290             } else {
 291                 if (!isset($this->uniqueKeys[$name])) {
 292                     $this->uniqueKeys[$name] = array();
 293                 }
 294                 $this->uniqueKeys[$name][] = $field;
 295             }
 296         }
 297     }
 298
 299
 300     private function schema()
 301     {
 302         if (!$this->schema) {
 303             $schema = XDB::iterator('DESCRIBE ' . $this->table);
 304             $keys   = XDB::iterator('SHOW INDEX FROM ' . $this->table);
 305             $this->parseSchema($schema, $keys);
 306         }
 307         return $this->schema;
 308     }
 309
 310     private function field($field)
 311     {
 312         $schema = $this->schema();
 313         if (!isset($schema[$field])) {
 314             throw new PlDBNoSuchFieldException($field, $this);
 315         }
 316         return $schema[$field];
 317     }
 318
 319     public function formatField($field, $value)
 320     {
 321         return $this->field($field)->format($value);
 322     }
 323
 324     public function registerFieldFormatter($field, $class)
 325     {
 326         return $this->field($field)->registerFormatter($class);
 327     }
 328
 329     public function defaultValue($field)
 330     {
 331         return $this->field($field)->defaultValue;
 332     }
 333
 334     private function hasKeyField(PlDBTableEntry $entry, array $fields)
 335     {
 336         foreach ($fields as $field) {
 337             if (isset($entry->$field)) {
 338                 return true;
 339             }
 340         }
 341         return false;
 342     }
 343
 344     private function keyFields($keyName)
 345     {
 346         if ($keyName == self::PRIMARY_KEY) {
 347             return $this->primaryKey;
 348         } else if (isset($this->uniqueKeys[$keyName])) {
 349             return $this->uniqueKeys[$keyName];
 350         } else if (isset($this->multipleKeys[$keyName])) {
 351             return $this->multipleKeys[$keyName];
 352         }
 353         throw new PlDBNoSuchKeyException($keyName, $this);
 354     }
 355
 356     private function bestKeyFields(PlDBTableEntry $entry, $allowMultiple)
 357     {
 358         if ($this->hasKeyField($entry, $this->primaryKey)) {
 359             return $this->primaryKey;
 360         }
 361         foreach ($this->uniqueKeys as $fields) {
 362             if ($this->hasKeyField($entry, $fields)) {
 363                 return $fields;
 364             }
 365         }
 366         if ($allowMultiple) {
 367             foreach ($this->multipleKeys as $fields) {
 368                 if ($this->hasKeyField($entry, $fields)) {
 369                     return $fields;
 370                 }
 371             }
 372         }
 373         return $this->primaryKey;
 374     }
 375
 376     public function key(PlDBTableEntry $entry, array $keyFields)
 377     {
 378         $key = array();
 379         foreach ($keyFields as $field) {
 380             if (!isset($entry->$field)) {
 381                 throw new PlDBIncompleteEntryDescription($field, $this);
 382             } else {
 383                 $key[] = XDB::escape($this->$field);
 384             }
 385         }
 386         return implode('-', $key);
 387     }
 388
 389     public function primaryKey(PlDBTableEntry $entry)
 390     {
 391         return $this->key($this->keyFields(self::PRIMARY_KEY));
 392     }
 393
 394     private function buildKeyCondition(PlDBTableEntry $entry, array $keyFields, $allowIncomplete)
 395     {
 396         $condition = array();
 397         foreach ($keyFields as $field) {
 398             if (!isset($entry->$field)) {
 399                 if (!$allowIncomplete) {
 400                     throw new PlDBIncompleteEntryDescription($field, $this);
 401                 }
 402             } else {
 403                 $condition[] = XDB::format($field . ' = {?}', $entry->$field);
 404             }
 405         }
 406         return implode(' AND ', $condition);
 407     }
 408
 409     public function fetchEntry(PlDBTableEntry $entry)
 410     {
 411         $result = XDB::rawFetchOneAssoc('SELECT  *
 412                                            FROM  ' . $this->table . '
 413                                           WHERE  ' . $this->buildKeyCondition($entry,
 414                                                                               $this->bestKeyFields($entry, false),
 415                                                                               false));
 416         if (!$result) {
 417             return false;
 418         }
 419         return $entry->fillFromDBData($result);
 420     }
 421
 422     public function iterateOnCondition(PlDBTableEntry $entry, $condition, $sortField)
 423     {
 424         if (empty($sortField)) {
 425             $sortField = $this->primaryKey;
 426         }
 427         if (!is_array($sortField)) {
 428             $sortField = array($sortField);
 429         }
 430         $sort = ' ORDER BY ' . implode(', ', $sortField);
 431         $it = XDB::rawIterator('SELECT  *
 432                                   FROM  ' . $this->table . '
 433                                  WHERE  ' . $condition . '
 434                                         ' . $sort);
 435         return PlIteratorUtils::map($it, array($entry, 'cloneAndFillFromDBData'));
 436     }
 437
 438     public function iterateOnEntry(PlDBTableEntry $entry, $sortField)
 439     {
 440         return $this->iterateOnCondition($entry,
 441                                          $this->buildKeyCondition($entry,
 442                                                                   $this->bestKeyFields($entry, true),
 443                                                                   true),
 444                                          $sortField);
 445     }
 446
 447     const SAVE_INSERT_MISSING   = 0x01;
 448     const SAVE_UPDATE_EXISTING  = 0x02;
 449     const SAVE_IGNORE_DUPLICATE = 0x04;
 450     public function saveEntry(PlDBTableEntry $entry, $flags)
 451     {
 452         $flags &= (self::SAVE_INSERT_MISSING | self::SAVE_UPDATE_EXISTING | self::SAVE_IGNORE_DUPLICATE);
 453         Platal::assert($flags != 0, "Hey, the flags ($flags) here are so stupid, don't know what to do");
 454         if ($flags == self::SAVE_UPDATE_EXISTING) {
 455             $values = array();
 456             foreach ($this->mutableFields as $field) {
 457                 if ($entry->hasChanged($field)) {
 458                     $values[] = XDB::format($field . ' = {?}', $entry->$field);
 459                 }
 460             }
 461             if (count($values) > 0) {
 462                 XDB::rawExecute('UPDATE ' . $this->table . '
 463                                     SET ' . implode(', ', $values) . '
 464                                   WHERE ' . $this->buildKeyCondition($entry,
 465                                                                      $this->keyFields(self::PRIMARY_KEY),
 466                                                                      false));
 467             }
 468         } else {
 469             $values = array();
 470             foreach ($this->schema as $field=>$type) {
 471                 if ($entry->hasChanged($field)) {
 472                     $values[$field] = XDB::escape($entry->$field);
 473                 }
 474             }
 475             if (count($values) > 0) {
 476                 $query = $this->table . ' (' . implode(', ', array_keys($values)) . ')
 477                                VALUES  (' . implode(', ', $values) . ')';
 478                 if (($flags & self::SAVE_UPDATE_EXISTING)) {
 479                     $update = array();
 480                     foreach ($this->mutableFields as $field) {
 481                         if (isset($values[$field])) {
 482                             $update[] = "$field = VALUES($field)";
 483                         }
 484                     }
 485                     if (count($update) > 0) {
 486                         $query = 'INSERT ' . $query;
 487                         $query .= "\n  ON DUPLICATE KEY UPDATE " . implode(', ', $update);
 488                     } else {
 489                         $query = 'INSERT IGNORE ' . $query;
 490                     }
 491                 } else if (($flags & self::SAVE_IGNORE_DUPLICATE)) {
 492                     $query = 'INSERT IGNORE ' . $query;
 493                 } else {
 494                     $query = 'INSERT ' . $query;
 495                 }
 496                 XDB::rawExecute($query);
 497                 $id = XDB::insertId();
 498                 if ($id) {
 499                     foreach ($this->primaryKey as $field) {
 500                         if ($this->schema[$field]->autoIncrement) {
 501                             $entry->$field = $id;
 502                             break;
 503                         }
 504                     }
 505                 }
 506             }
 507         }
 508     }
 509
 510     public function deleteEntry(PlDBTableEntry $entry, $allowIncomplete)
 511     {
 512         XDB::rawExecute('DELETE FROM ' . $this->table . '
 513                                WHERE ' . $this->buildKeyCondition($entry,
 514                                                                   $this->bestKeyFields($entry, $allowIncomplete),
 515                                                                   $allowIncomplete));
 516     }
 517
 518     public static function get($name)
 519     {
 520         return new PlDBTable($name);
 521     }
 522 }
 523
 524 class PlDBTableEntry extends PlAbstractIterable
 525 {
 526     private $table;
 527     private $changed;
 528     private $fetched = false;
 529     private $autoFetch;
 530
 531     private $data = array();
 532
 533     public function __construct($table, $autoFetch = false)
 534     {
 535         if ($table instanceof PlDBTable) {
 536             $this->table = $table;
 537         } else {
 538             $this->table = PlCache::getGlobal('pldbtable_' . $table, array('PlDBTable', 'get'), array($table));
 539         }
 540         $this->autoFetch = $autoFetch;
 541         $this->changed = new PlFlagSet();
 542     }
 543
 544     /** Register a custom formatter for a field.
 545      *
 546      * A formatter can be used to perform on-the-fly conversion from db storage to a user-friendly format.
 547      * For example, if you have a textual field that contain json, you can use a JSonFieldFormatter on this
 548      * field to perform automatic decoding when reading from the database (or when assigning the field)
 549      * and automatic json_encoding when storing the object back to the db.
 550      */
 551     protected function registerFieldFormatter($field, $formatterClass)
 552     {
 553         $this->table->registerFieldFormatter($field, $formatterClass);
 554     }
 555
 556     /** This hook is called when the entry is going to be updated in the db.
 557      *
 558      * A typical usecase is a class that stores low-level representation of
 559      * an object in db and perform a conversion between this low-level representation
 560      * and a higher-level representation.
 561      *
 562      * @return true in case of success
 563      */
 564     protected function preSave()
 565     {
 566         return true;
 567     }
 568
 569     /** This hook is called when the entry has been save in the database.
 570      *
 571      * It can be used to perform post-actions on save like storing extra data
 572      * in database or sending a notification.
 573      */
 574     protected function postSave()
 575     {
 576     }
 577
 578     /** This hook is called when the entry is going to be deleted from the db.
 579      *
 580      * Default behavior is to call preSave().
 581      *
 582      * @return true in case of success.
 583      */
 584     protected function preDelete()
 585     {
 586         return $this->preSave();
 587     }
 588
 589     /** This hook is called when the entry has just been fetched from the db.
 590      *
 591      * This is the counterpart of @ref preSave and a typical use-case is the conversion
 592      * from a high-level representation of the objet to a representation suitable for
 593      * storage in the database.
 594      *
 595      * @return true in case of success.
 596      */
 597     protected function postFetch()
 598     {
 599         return true;
 600     }
 601
 602     public function __get($field)
 603     {
 604         if (isset($this->data[$field])) {
 605             return $this->data[$field];
 606         } else if (!$this->fetched && $this->autoFetch) {
 607             $this->fetch();
 608             if (isset($this->data[$field])) {
 609                 return $this->data[$field];
 610             }
 611         }
 612         return $this->table->defaultValue($field);
 613     }
 614
 615     public function __set($field, $value)
 616     {
 617         $this->data[$field] = $this->table->formatField($field, $value);
 618         $this->changed->addFlag($field);
 619     }
 620
 621     public function __isset($field)
 622     {
 623         return isset($this->data[$field]);
 624     }
 625
 626     public function primaryKey()
 627     {
 628         $this->table->primaryKey($this);
 629     }
 630
 631     public function hasChanged($field)
 632     {
 633         return $this->changed->hasFlag($field);
 634     }
 635
 636     public function fillFromArray(array $data)
 637     {
 638         foreach ($data as $field => $value) {
 639             $this->$field = $value;
 640         }
 641     }
 642
 643     public function fillFromDBData(array $data)
 644     {
 645         $this->fillFromArray($data);
 646         $this->changed->clear();
 647         return $this->postFetch();
 648     }
 649
 650     public function copy(PlDBTableEntry $other)
 651     {
 652         Platal::assert($this->table == $other->table,
 653                        "Trying to fill an entry of table {$this->table} with content of {$other->table}.");
 654         $this->changed = $other->changed;
 655         $this->fetched = $other->fetched;
 656         $this->data    = $other->data;
 657     }
 658
 659     public function cloneAndFillFromDBData(array $data)
 660     {
 661         $clone = clone $this;
 662         $clone->fillFromDBData($data);
 663         return $clone;
 664     }
 665
 666     public function fetch()
 667     {
 668         return $this->table->fetchEntry($this);
 669     }
 670
 671     public function iterate($sortField = null)
 672     {
 673         return $this->table->iterateOnEntry($this, $sortField);
 674     }
 675
 676     public function iterateOnCondition($condition, $sortField = null)
 677     {
 678         return $this->table->iterateOnCondition($this, $condition, $sortField);
 679     }
 680
 681     public function save($flags)
 682     {
 683         if (!$this->preSave()) {
 684             return false;
 685         }
 686         $this->table->saveEntry($this, $flags);
 687         $this->changed->clear();
 688         $this->postSave();
 689         return true;
 690     }
 691
 692     public function update($insertMissing = false)
 693     {
 694         $flags = PlDBTable::SAVE_UPDATE_EXISTING;
 695         if ($insertMissing) {
 696             $flags = PlDBTable::SAVE_INSERT_MISSING;
 697         }
 698         return $this->save($flags);
 699     }
 700
 701     public function insert($allowUpdate = false)
 702     {
 703         $flags = PlDBTable::SAVE_INSERT_MISSING;
 704         if ($allowUpdate) {
 705             $flags |= PlDBTable::SAVE_UPDATE_EXISTING;
 706         }
 707         return $this->save($flags);
 708     }
 709
 710     public function delete()
 711     {
 712         if (!$this->preDelete()) {
 713             return 0;
 714         }
 715         return $this->table->deleteEntry($this, true);
 716     }
 717 }
 718
 719 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 720 ?>