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