2 /***************************************************************************
3 * Copyright (C) 2003-2011 Polytechnique.org *
4 * http://opensource.polytechnique.org/ *
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. *
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. *
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 *
19 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
20 ***************************************************************************/
22 class PlDBBadValueException
extends PlException
24 public function __construct($value, PlDBTableField
$field, $reason)
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
. '\'): '
33 class PlDBNoSuchFieldException
extends PlException
35 public function __construct($field, PlDBTable
$table)
37 parent
::__construct('Erreur lors de l\'accès à la base de données',
38 'No such field ' . $field . ' in table ' . $table->table
);
42 class PlDBNoSuchKeyException
extends PlException
44 public function __construct($key, PlDBTable
$table)
46 parent
::__construct('Erreur lors de l\'accès à la base de données',
47 'No such key ' . $key . ' in table ' . $table->table
);
52 class PlDBIncompleteEntryDescription
extends PlException
54 public function __construct($field, PlDBTable
$table)
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 '
71 public $typeParameters;
75 public $autoIncrement;
80 public function __construct(array $column)
82 $this->name
= $column['Field'];
83 $this->typeParameters
= explode(' ', str_replace(array('(', ')', ',', '\''), ' ',
85 $this->type
= array_shift($this->typeParameters
);
86 if ($this->type
== 'enum' ||
$this->type
== 'set') {
87 $this->typeParameters
= new PlFlagSet(implode(',', $this->typeParameters
));
88 } else if (ctype_digit($this->typeParameters
[0])) {
89 $this->typeLength
= intval($this->typeParameters
[0]);
90 array_shift($this->typeParameters
);
92 $this->allowNull
= ($column['Null'] === 'YES');
93 $this->autoIncrement
= (strpos($column['Extra'], 'auto_increment') !== false
);
94 $this->inPrimaryKey
= ($column['Key'] == 'PRI');
97 $this->defaultValue
= $this->format($column['Default']);
98 } catch (PlDBBadValueException
$e) {
99 $this->defaultValue
= null
;
103 public function registerFormatter($class)
105 $this->formatter
= $class;
108 public function registerValidator($class)
110 $this->validator
= $class;
113 public function format($value, $badNullFallbackToDefault = false
)
115 if (is_null($value)) {
116 if ($this->allowNull ||
$this->autoIncrement
) {
119 if ($badNullFallbackToDefault) {
120 return $this->defaultValue
;
122 throw new PlDBBadValueException($value, $this, 'null not allowed');
124 if (!is_null($this->validator
)) {
125 $class = $this->validator
;
126 new $class($this, $value);
128 if (!is_null($this->formatter
)) {
129 $class = $this->formatter
;
130 $value = new $class($this, $value);
131 } else if ($this->type
== 'enum') {
132 if (!$this->typeParameters
->hasFlag($value)) {
133 throw new PlDBBadValueException($value, $this, 'invalid value for enum ' . $this->typeParameters
->flags());
136 } else if ($this->type
== 'set') {
137 $value = new PlFlagSet($value);
138 foreach ($value as $flag) {
139 if (!$this->typeParameters
->hasFlag($flag)) {
140 throw new PlDBBadValueException($value, $this, 'invalid flag for set ' . $this->typeParameters
->flags());
144 } else if (ends_with($this->type
, 'int')) {
145 if (!is_int($value) && !ctype_digit($value)) {
146 throw new PlDBBadValueException($value, $this, 'value is not an integer');
148 $value = intval($value);
149 if (count($this->typeParameters
) > 0 && $this->typeParameters
[0] == 'unsigned') {
151 throw new PlDBBadValueException($value, $this, 'value is negative in an unsigned field');
154 /* TODO: Check bounds */
156 } else if (ends_with($this->type
, 'char')) {
157 if (strlen($value) > $this->typeLength
) {
158 throw new PlDBBadValueException($value, $this, 'value is expected to be at most ' . $this->typeLength
. ' characters long, ' . strlen($value) . ' given');
161 } else if (starts_with($this->type
, 'date') ||
$this->type
== 'timestamp') {
162 return new DateFieldFormatter($this, $value);
168 interface PlDBTableFieldValidator
170 public function __construct(PlDBTableField
$field, $value);
173 interface PlDBTableFieldFormatter
extends PlDBTableFieldValidator
, XDBFormat
, PlExportable
177 class DateFieldFormatter
implements PlDBTableFieldFormatter
180 private $storageFormat;
182 public function __construct(PlDBTableField
$field, $date)
184 $this->datetime
= make_datetime($date);
185 if (is_null($this->datetime
)) {
186 throw new PlDBBadValueException($date, $field, 'value is expected to be a date/time, ' . $date . ' given');
188 if ($field->type
== 'date') {
189 $this->storageFormat
= 'Y-m-d';
190 } else if ($field->type
== 'datetime') {
191 $this->storageFormat
= 'Y-m-d H:i:s';
193 $this->storageFormat
= 'U';
197 public function format()
199 return XDB
::escape($this->export());
202 public function date($format)
204 return $this->datetime
->format($format);
207 public function export()
209 return $this->datetime
->format($this->storageFormat
);
213 class JSonFieldFormatter
implements PlDBTableFieldFormatter
, ArrayAccess
217 public function __construct(PlDBTableField
$field, $data)
219 if (strpos($field->type
, 'text') === false
) {
220 throw new PlDBBadValueException($data, $field, 'json formatting requires a text field');
223 if (is_string($data)) {
224 $this->data
= json_decode($data, true
);
225 } else if (is_object($data)) {
226 if ($data instanceof PlExportable
) {
227 $this->data
= $data->export();
229 $this->data
= json_decode(json_encode($data), true
);
231 } else if (is_array($data)) {
235 if (is_null($this->data
)) {
236 throw new PlDBBadValueException($data, $field, 'cannot interpret data as json: ' . $data);
240 public function format()
242 return XDB
::escape(json_encode($this->data
));
245 public function export()
250 public function offsetExists($offset)
252 return isset($this->data
[$offset]);
255 public function offsetGet($offset)
257 return $this->data
[$offset];
260 public function offsetSet($offset, $value)
262 $this->data
[$offset] = $value;
265 public function offsetUnset($offset)
267 unset($this->data
[$offset]);
272 /** This class aims at providing a simple interface to interact with a single
273 * table of a database. It is implemented as a wrapper around XDB.
277 const PRIMARY_KEY
= 'PRIMARY';
284 private $multipleKeys;
285 private $mutableFields;
287 public function __construct($table)
289 $this->table
= $table;
293 private function parseSchema(PlIterator
$schema, PlIterator
$keys)
295 $this->schema
= array();
296 $this->primaryKey
= array();
297 $this->uniqueKeys
= array();
298 $this->multipleKeys
= array();
299 $this->mutableFields
= array();
300 while ($column = $schema->next()) {
301 $field = new PlDBTableField($column);
302 $this->schema
[$field->name
] = $field;
303 if (!$field->inPrimaryKey
) {
304 $this->mutableFields
[] = $field->name
;
307 while ($column = $keys->next()) {
308 $name = $column['Key_name'];
309 $multiple = intval($column['Non_unique']) != 0;
310 $field = $column['Column_name'];
312 if (!isset($this->multipleKeys
[$name])) {
313 $this->multipleKeys
[$name] = array();
315 $this->multipleKeys
[$name][] = $field;
316 } else if ($name == self
::PRIMARY_KEY
) {
317 $this->primaryKey
[] = $field;
319 if (!isset($this->uniqueKeys
[$name])) {
320 $this->uniqueKeys
[$name] = array();
322 $this->uniqueKeys
[$name][] = $field;
328 private function schema()
330 if (!$this->schema
) {
331 $schema = XDB
::iterator('DESCRIBE ' . $this->table
);
332 $keys = XDB
::iterator('SHOW INDEX FROM ' . $this->table
);
333 $this->parseSchema($schema, $keys);
335 return $this->schema
;
338 private function field($field)
340 $schema = $this->schema();
341 if (!isset($schema[$field])) {
342 throw new PlDBNoSuchFieldException($field, $this);
344 return $schema[$field];
347 public function formatField($field, $value)
349 return $this->field($field)->format($value);
352 public function registerFieldFormatter($field, $class)
354 return $this->field($field)->registerFormatter($class);
357 public function registerFieldValidator($field, $class)
359 return $this->field($field)->registerValidator($class);
363 public function defaultValue($field)
365 return $this->field($field)->defaultValue
;
368 private function hasKeyField(PlDBTableEntry
$entry, array $fields)
370 foreach ($fields as $field) {
371 if (isset($entry->$field)) {
378 private function keyFields($keyName)
380 if ($keyName == self
::PRIMARY_KEY
) {
381 return $this->primaryKey
;
382 } else if (isset($this->uniqueKeys
[$keyName])) {
383 return $this->uniqueKeys
[$keyName];
384 } else if (isset($this->multipleKeys
[$keyName])) {
385 return $this->multipleKeys
[$keyName];
387 throw new PlDBNoSuchKeyException($keyName, $this);
390 private function bestKeyFields(PlDBTableEntry
$entry, $allowMultiple)
392 if ($this->hasKeyField($entry, $this->primaryKey
)) {
393 return $this->primaryKey
;
395 foreach ($this->uniqueKeys
as $fields) {
396 if ($this->hasKeyField($entry, $fields)) {
400 if ($allowMultiple) {
401 foreach ($this->multipleKeys
as $fields) {
402 if ($this->hasKeyField($entry, $fields)) {
407 return $this->primaryKey
;
410 public function key(PlDBTableEntry
$entry, array $keyFields)
413 foreach ($keyFields as $field) {
414 if (!isset($entry->$field)) {
415 throw new PlDBIncompleteEntryDescription($field, $this);
417 $key[] = XDB
::escape($this->$field);
420 return implode('-', $key);
423 public function primaryKey(PlDBTableEntry
$entry)
425 return $this->key($this->keyFields(self
::PRIMARY_KEY
));
428 private function buildKeyCondition(PlDBTableEntry
$entry, array $keyFields, $allowIncomplete)
430 $condition = array();
431 foreach ($keyFields as $field) {
432 if (!isset($entry->$field)) {
433 if (!$allowIncomplete) {
434 throw new PlDBIncompleteEntryDescription($field, $this);
437 $condition[] = XDB
::format($field . ' = {?}', $entry->$field);
440 return implode(' AND ', $condition);
443 public function fetchEntry(PlDBTableEntry
$entry)
445 $result = XDB
::rawFetchOneAssoc('SELECT *
446 FROM ' . $this->table
. '
447 WHERE ' . $this->buildKeyCondition($entry,
448 $this->bestKeyFields($entry, false
),
453 return $entry->fillFromDBData($result);
456 public function iterateOnCondition(PlDBTableEntry
$entry, $condition, $sortField)
458 if (empty($sortField)) {
459 $sortField = $this->primaryKey
;
461 if (!is_array($sortField)) {
462 $sortField = array($sortField);
464 $sort = ' ORDER BY ' . implode(', ', $sortField);
465 $it = XDB
::rawIterator('SELECT *
466 FROM ' . $this->table
. '
467 WHERE ' . $condition . '
469 return PlIteratorUtils
::map($it, array($entry, 'cloneAndFillFromDBData'));
472 public function iterateOnEntry(PlDBTableEntry
$entry, $sortField)
474 return $this->iterateOnCondition($entry,
475 $this->buildKeyCondition($entry,
476 $this->bestKeyFields($entry, true
),
481 const SAVE_INSERT_MISSING
= 0x01;
482 const SAVE_UPDATE_EXISTING
= 0x02;
483 const SAVE_IGNORE_DUPLICATE
= 0x04;
484 public function saveEntries(array $entries, $flags)
486 $flags &= (self
::SAVE_INSERT_MISSING | self
::SAVE_UPDATE_EXISTING | self
::SAVE_IGNORE_DUPLICATE
);
487 Platal
::assert($flags != 0, "Hey, the flags ($flags) here are so stupid, don't know what to do");
488 if ($flags == self
::SAVE_UPDATE_EXISTING
) {
489 foreach ($entries as $entry) {
491 foreach ($this->mutableFields
as $field) {
492 if ($entry->hasChanged($field)) {
493 $values[] = XDB
::format($field . ' = {?}', $entry->$field);
496 if (count($values) > 0) {
497 XDB
::rawExecute('UPDATE ' . $this->table
. '
498 SET ' . implode(', ', $values) . '
499 WHERE ' . $this->buildKeyCondition($entry,
500 $this->keyFields(self
::PRIMARY_KEY
),
505 $fields = new PlFlagSet();
506 foreach ($entries as $entry) {
507 foreach ($this->schema
as $field=>$type) {
508 if ($type->inPrimaryKey ||
$entry->hasChanged($field)) {
509 $fields->addFlag($field);
513 if (count($fields->export()) > 0) {
514 foreach ($entries as $entry) {
516 foreach ($fields as $field) {
517 $v[$field] = XDB
::escape($entry->$field);
519 $values[] = '(' . implode(', ', $v) . ')';
522 $query = $this->table
. ' (' . implode(', ', $fields->export()) . ')
523 VALUES ' . implode(",\n", $values);
524 if (($flags & self
::SAVE_UPDATE_EXISTING
)) {
526 foreach ($this->mutableFields
as $field) {
527 if (isset($values[$field])) {
528 $update[] = "$field = VALUES($field)";
531 if (count($update) > 0) {
532 $query = 'INSERT INTO ' . $query;
533 $query .= "\n ON DUPLICATE KEY UPDATE " . implode(', ', $update);
535 $query = 'INSERT IGNORE INTO ' . $query;
537 } else if (($flags & self
::SAVE_IGNORE_DUPLICATE
)) {
538 $query = 'INSERT IGNORE INTO ' . $query;
540 $query = 'INSERT INTO ' . $query;
542 XDB
::rawExecute($query);
543 if (count($entries) == 1) {
544 $id = XDB
::insertId();
546 $entry = end($entries);
547 foreach ($this->primaryKey
as $field) {
548 if ($this->schema
[$field]->autoIncrement
) {
549 $entry->$field = $id;
559 public function deleteEntry(PlDBTableEntry
$entry, $allowIncomplete)
561 XDB
::rawExecute('DELETE FROM ' . $this->table
. '
562 WHERE ' . $this->buildKeyCondition($entry,
563 $this->bestKeyFields($entry, $allowIncomplete),
567 public function exportEntry(PlDBTableEntry
$entry)
570 foreach ($this->schema
as $key=>$field) {
571 $value = $entry->$key;
572 if ($value instanceof PlExportable
) {
573 $value = $value->export();
575 $export[$key] = $value;
580 public static function get($name)
582 return new PlDBTable($name);
586 class PlDBTableEntry
extends PlAbstractIterable
implements PlExportable
590 private $fetched = false
;
593 private $data = array();
595 public function __construct($table, $autoFetch = false
)
597 if ($table instanceof PlDBTable
) {
598 $this->table
= $table;
600 $this->table
= PlCache
::getGlobal('pldbtable_' . $table, array('PlDBTable', 'get'), array($table));
602 $this->autoFetch
= $autoFetch;
603 $this->changed
= new PlFlagSet();
606 /** Register a custom formatter for a field.
608 * A formatter can be used to perform on-the-fly conversion from db storage to a user-friendly format.
609 * For example, if you have a textual field that contain json, you can use a JSonFieldFormatter on this
610 * field to perform automatic decoding when reading from the database (or when assigning the field)
611 * and automatic json_encoding when storing the object back to the db.
613 protected function registerFieldFormatter($field, $formatterClass)
615 $this->table
->registerFieldFormatter($field, $formatterClass);
618 /** Register a custom validator for a field.
620 * A validator perform a pre-filter on the value of a field. As opposed to the formatters, it does
621 * not affects how the value is stored in the database.
623 protected function registerFieldValidator($field, $validatorClass)
625 $this->table
->registerFieldValidator($field, $validatorClass);
628 /** This hook is called when the entry is going to be updated in the db.
630 * A typical usecase is a class that stores low-level representation of
631 * an object in db and perform a conversion between this low-level representation
632 * and a higher-level representation.
634 * @return true in case of success
636 protected function preSave()
641 /** This hook is called when the entry has been save in the database.
643 * It can be used to perform post-actions on save like storing extra data
644 * in database or sending a notification.
646 protected function postSave()
650 /** This hook is called when the entry is going to be deleted from the db.
652 * Default behavior is to call preSave().
654 * @return true in case of success.
656 protected function preDelete()
658 return $this->preSave();
661 /** This hook is called when the entry has just been fetched from the db.
663 * This is the counterpart of @ref preSave and a typical use-case is the conversion
664 * from a high-level representation of the objet to a representation suitable for
665 * storage in the database.
667 * @return true in case of success.
669 protected function postFetch()
674 public function __get($field)
676 if (isset($this->data
[$field])) {
677 return $this->data
[$field];
678 } else if (!$this->fetched
&& $this->autoFetch
) {
680 if (isset($this->data
[$field])) {
681 return $this->data
[$field];
684 return $this->table
->defaultValue($field);
687 public function __set($field, $value)
689 $this->data
[$field] = $this->table
->formatField($field, $value);
690 $this->changed
->addFlag($field);
693 public function __isset($field)
695 return isset($this->data
[$field]);
698 public function primaryKey()
700 $this->table
->primaryKey($this);
703 public function hasChanged($field)
705 return $this->changed
->hasFlag($field);
708 public function fillFromArray(array $data)
710 foreach ($data as $field => $value) {
711 $this->$field = $value;
715 public function fillFromDBData(array $data)
717 $this->fillFromArray($data);
718 $this->changed
->clear();
719 return $this->postFetch();
722 public function copy(PlDBTableEntry
$other)
724 Platal
::assert($this->table
== $other->table
,
725 "Trying to fill an entry of table {$this->table->table} with content of {$other->table->table}.");
726 $this->changed
= $other->changed
;
727 $this->fetched
= $other->fetched
;
728 $this->data
= $other->data
;
731 public function cloneAndFillFromDBData(array $data)
733 $clone = clone $this;
734 $clone->fillFromDBData($data);
738 public function fetch()
740 return $this->table
->fetchEntry($this);
743 public function iterate($sortField = null
)
745 return $this->table
->iterateOnEntry($this, $sortField);
748 public function iterateOnCondition($condition, $sortField = null
)
750 return $this->table
->iterateOnCondition($this, $condition, $sortField);
753 public function save($flags)
755 return self
::saveBatch(array($this), $flags);
758 public function update($insertMissing = false
)
760 $flags = PlDBTable
::SAVE_UPDATE_EXISTING
;
761 if ($insertMissing) {
762 $flags = PlDBTable
::SAVE_INSERT_MISSING
;
764 return $this->save($flags);
767 public function insert($allowUpdate = false
)
769 $flags = PlDBTable
::SAVE_INSERT_MISSING
;
771 $flags |
= PlDBTable
::SAVE_UPDATE_EXISTING
;
773 return $this->save($flags);
776 public function delete()
778 if (!$this->preDelete()) {
781 return $this->table
->deleteEntry($this, true
);
784 public function export()
786 return $this->table
->exportEntry($this);
789 protected static function saveBatch($entries, $flags)
792 foreach ($entries as $entry) {
793 if (is_null($table)) {
794 $table = $entry->table
;
796 Platal
::assert($table === $entry->table
, "Cannot save batch of entries of different kinds");
798 if (!$entry->preSave()) {
802 $table->saveEntries($entries, $flags);
803 foreach ($entries as $entry) {
804 $entry->changed
->clear();
810 public static function insertBatch($entries, $allowUpdate = false
)
812 $flags = PlDBTable
::SAVE_INSERT_MISSING
;
814 $flags |
= PlDBTable
::SAVE_UPDATE_EXISTING
;
816 return self
::saveBatch($entries, $flags);
820 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: