1 <?php
  2 
  3 /*
  4  * This file is part of the ICanBoogie package.
  5  *
  6  * (c) Olivier Laviale <olivier.laviale@gmail.com>
  7  *
  8  * For the full copyright and license information, please view the LICENSE
  9  * file that was distributed with this source code.
 10  */
 11 
 12 namespace ICanBoogie\ActiveRecord;
 13 
 14 use ICanBoogie\DateTime;
 15 use ICanBoogie\PropertyNotWritable;
 16 
 17 /**
 18  * A representation of a database table.
 19  *
 20  * @property-read Connection $connection Connection used by the table.
 21  * @property-read array $schema The schema of the table.
 22  * @property-read array $extended_schema The extended schema of the table.
 23  * @property-read string $name_unprefixed Unprefixed name of the table.
 24  * @property-read mixed $primary Primary key of the table, or `null` if there is none.
 25  * @property-read string $alias The alias name of the table.
 26  * @property-read Table|null $parent The parent of the table.
 27  */
 28 class Table extends \ICanBoogie\Object
 29 {
 30     // TODO-20130216: deprecate all T_*
 31 
 32     const T_ALIAS = 'alias';
 33     const T_CONNECTION = 'connection';
 34     const T_EXTENDS = 'extends';
 35     const T_IMPLEMENTS = 'implements';
 36     const T_NAME = 'name';
 37     const T_SCHEMA = 'schema';
 38 
 39     const ALIAS = 'alias';
 40     const CONNECTION = 'connection';
 41     const EXTENDING = 'extends';
 42     const IMPLEMENTING = 'implements';
 43     const NAME = 'name';
 44     const SCHEMA = 'schema';
 45 
 46     /**
 47      * A database connection.
 48      *
 49      * @var Connection
 50      */
 51     protected $connection;
 52 
 53     /**
 54      * Returns the connection used by the table.
 55      *
 56      * @return \ICanBoogie\ActiveRecord\Database
 57      */
 58     protected function get_connection()
 59     {
 60         return $this->connection;
 61     }
 62 
 63     /**
 64      * Name of the table, including the prefix defined by the model's connection.
 65      *
 66      * @var string
 67      */
 68     protected $name;
 69 
 70     /**
 71      * Returns the name of the table.
 72      *
 73      * @return string
 74      */
 75     protected function get_name()
 76     {
 77         return $this->name;
 78     }
 79 
 80     /**
 81      * Unprefixed version of the table's name.
 82      *
 83      * The "{self}" placeholder used in queries is replaced by the properties value.
 84      *
 85      * @var string
 86      */
 87     protected $name_unprefixed;
 88 
 89     /**
 90      * Returns the unprefixed name of the table.
 91      *
 92      * @return string
 93      */
 94     protected function get_name_unprefixed()
 95     {
 96         return $this->name_unprefixed;
 97     }
 98 
 99     /**
100      * Primary key of the table, retrieved from the schema defined using the {@link SCHEMA} attribute.
101      *
102      * @var mixed
103      */
104     protected $primary;
105 
106     /**
107      * Returns the name of the primary key.
108      *
109      * @return \ICanBoogie\ActiveRecord\mixed
110      */
111     protected function get_primary()
112     {
113         return $this->primary;
114     }
115 
116     /**
117      * Alias for the table's name, which can be defined using the {@link ALIAS} attribute
118      * or automatically created.
119      *
120      * The "{primary}" placeholder used in queries is replaced by the properties value.
121      *
122      * @var string
123      */
124     protected $alias;
125 
126     /**
127      * Returns the alias of the table.
128      *
129      * @return string
130      */
131     protected function get_alias()
132     {
133         return $this->alias;
134     }
135 
136     /**
137      * Schema for the table.
138      *
139      * The "{alias}" placeholder used in queries is replaced by the properties value.
140      *
141      * @var array
142      */
143     protected $schema;
144 
145     /**
146      * Returns the schema of the table.
147      *
148      * @return array
149      */
150     protected function get_schema()
151     {
152         return $this->schema;
153     }
154 
155     /**
156      * The parent is used when the table is in a hierarchy, which is the case if the table
157      * extends another table.
158      *
159      * @var Table
160      */
161     protected $parent;
162 
163     /**
164      * Returns the parent of the table.
165      *
166      * @return string
167      */
168     protected function get_parent()
169     {
170         return $this->parent;
171     }
172 
173     protected $implements = [];
174 
175     /**
176      * SQL fragment for the FROM clause of the query, made of the table's name and alias and those
177      * of the hierarchy.
178      *
179      * @var string
180      */
181     protected $update_join;
182 
183     /**
184      * SQL fragment for the FROM clause of the query, made of the table's name and alias and those
185      * of the related tables, inherited and implemented.
186      *
187      * The "{self_and_related}" placeholder used in queries is replaced by the properties value.
188      *
189      * @var string
190      */
191     protected $select_join;
192 
193     /**
194      * Initialiazes the following properties: {@link $alias}, {@link $connection},
195      * {@link implements}, {@link $name_unprefixed}, {@link $schema} and {@link $parent}.
196      *
197      * @param array $attributes
198      *
199      * @throws \InvalidArgumentException if the {@link CONNECTION} attribute is empty.
200      */
201     public function __construct(array $attributes)
202     {
203         foreach ($attributes as $attribute => $value)
204         {
205             switch ($attribute)
206             {
207                 case self::ALIAS: $this->alias = $value; break;
208                 case self::CONNECTION: $this->connection = $value; break;
209                 case self::IMPLEMENTING: $this->implements = $value; break;
210                 case self::NAME: $this->name_unprefixed = $value; break;
211                 case self::SCHEMA: $this->schema = $value; break;
212                 case self::EXTENDING: $this->parent = $value; break;
213             }
214         }
215 
216         if (!$this->name_unprefixed)
217         {
218             throw new \InvalidArgumentException('The <code>NAME</code> attribute is empty.');
219         }
220 
221         if (preg_match('#([^0-9,a-z,A-Z$_])#', $this->name_unprefixed, $matches))
222         {
223             throw new \InvalidArgumentException("Invalid character in table name \"$this->name_unprefixed\": {$matches[0]}.");
224         }
225 
226         if (!$this->schema)
227         {
228             throw new \InvalidArgumentException('The <code>SCHEMA</code> attribute is empty.');
229         }
230 
231         if (empty($this->schema['fields']))
232         {
233             throw new \InvalidArgumentException("Schema fields are empty for table \"{$this->name_unprefixed}\".");
234         }
235 
236         if ($this->parent && !($this->parent instanceof self))
237         {
238             throw new \InvalidArgumentException("EXTENDING must be an instance of " . __CLASS__ . ". Given: {$this->parent}.");
239         }
240 
241         #
242         # alias
243         #
244 
245         if (!$this->alias)
246         {
247             $alias = $this->name_unprefixed;
248 
249             $pos = strrpos($alias, '_');
250 
251             if ($pos !== false)
252             {
253                 $alias = substr($alias, $pos + 1);
254             }
255 
256             $alias = \ICanBoogie\singularize($alias);
257 
258             $this->alias = $alias;
259         }
260 
261         #
262         # if we have a parent, we need to extend our fields with our parent primary key
263         #
264 
265         $parent = $this->parent;
266 
267         if ($parent)
268         {
269             $this->connection = $parent->connection;
270 
271             $primary = $parent->primary;
272             $primary_definition = $parent->schema['fields'][$primary];
273 
274             unset($primary_definition['serial']);
275 
276             $this->schema['fields'] = [ $primary => $primary_definition ] + $this->schema['fields'];
277 
278             #
279             # implements are inherited too
280             #
281 
282             if ($parent->implements)
283             {
284                 $this->implements = array_merge($parent->implements, $this->implements);
285             }
286         }
287 
288         $connection = $this->connection;
289 
290         if (!($connection instanceof Connection))
291         {
292             throw new \InvalidArgumentException
293             (
294                 '<code>connection</code> must be an instance of Connection, given: '
295                 . (is_object($connection) ? 'instance of ' . get_class($connection) : gettype($connection))
296                 . '.'
297             );
298         }
299 
300         $this->name = $connection->prefix . $this->name_unprefixed;
301 
302         #
303         # parse definition schema to have a complete schema
304         #
305 
306         $this->schema = $connection->parse_schema($this->schema);
307 
308         #
309         # retrieve primary key
310         #
311 
312         if (!empty($this->schema['primary']))
313         {
314             $this->primary = $this->schema['primary'];
315         }
316 
317         #
318         # resolve inheritance and create a lovely _inner join_ string
319         #
320 
321         $join = '';
322 
323         $parent = $this->parent;
324 
325         while ($parent)
326         {
327             $join .= "INNER JOIN `{$parent->name}` `{$parent->alias}` USING(`{$this->primary}`) ";
328 
329             $parent = $parent->parent;
330         }
331 
332         $this->update_join = $join;
333 
334         $join = "`{$this->alias}`" . ($join ? " $join" : '');
335 
336         #
337         # resolve implements
338         #
339 
340         if ($this->implements)
341         {
342             if (!is_array($this->implements))
343             {
344                 throw new \InvalidArgumentException('<code>IMPLEMENTING</code> must be an array.');
345             }
346 
347             $i = 1;
348 
349             foreach ($this->implements as $implement)
350             {
351                 if (!is_array($implement))
352                 {
353                     throw new \InvalidArgumentException('<code>IMPLEMENTING</code> must be an array.');
354                 }
355 
356                 $table = $implement['table'];
357 
358                 if (!($table instanceof Table))
359                 {
360                     throw new \InvalidArgumentException(sprintf('Implements table must be an instance of ICanBoogie\ActiveRecord\Table: %s given.', get_class($table)));
361                 }
362 
363                 $name = $table->name;
364                 $primary = $table->primary;
365 
366                 $join .= empty($implement['loose']) ? 'INNER' : 'LEFT';
367                 $join .= " JOIN `$name` AS {$table->alias} USING(`$primary`) ";
368 
369                 $i++;
370             }
371         }
372 
373         $this->select_join = $join;
374     }
375 
376     /**
377      * Alias to the {@link query()} method.
378      */
379     public function __invoke($query, array $args=[], array $options=[])
380     {
381         return $this->query($query, $args, $options);
382     }
383 
384     /*
385     **
386 
387     INSTALL
388 
389     **
390     */
391 
392     public function install()
393     {
394         if (!$this->schema)
395         {
396             throw new \Exception("Missing schema to install table {$this->name_unprefixed}.");
397         }
398 
399         return $this->connection->create_table($this->name_unprefixed, $this->schema);
400     }
401 
402     public function uninstall()
403     {
404         return $this->drop();
405     }
406 
407     /**
408      * Checks whether the table is installed.
409      *
410      * @return bool `true` if the table exists, `false` otherwise.
411      */
412     public function is_installed()
413     {
414         return $this->connection->table_exists($this->name_unprefixed);
415     }
416 
417     /**
418      * Returns the extended schema.
419      *
420      * @return array
421      */
422     protected function lazy_get_extended_schema()
423     {
424         $table = $this;
425         $schemas = [];
426 
427         while ($table)
428         {
429             $schemas[] = $table->schema;
430 
431             $table = $table->parent;
432         }
433 
434         $schemas = array_reverse($schemas);
435         $schema = call_user_func_array('\ICanBoogie\array_merge_recursive', $schemas);
436 
437         $this->connection->parse_schema($schema);
438 
439         return $schema;
440     }
441 
442     /**
443      * Resolve statement placeholders.
444      *
445      * The following placeholder are replaced:
446      *
447      * - `{alias}`: The alias of the table.
448      * - `{prefix}`: The prefix used for the tables of the connection.
449      * - `{primary}`: The primary key of the table.
450      * - `{self}`: The name of the table.
451      * - `{self_and_related}`: The escaped name of the table and the possible JOIN clauses.
452      *
453      * Note: If the table has a multi-column primary keys `{primary}` is replaced by
454      * `__multicolumn_primary__<concatened_columns>` where `<concatened_columns>` is a the columns
455      * concatenated with an underscore ("_") as separator. For instance, if a table primary key is
456      * made of columns "p1" and "p2", `{primary}` is replaced by `__multicolumn_primary__p1_p2`.
457      * It's not very helpful, but we still have to decide what to do with this.
458      *
459      * @param string $statement The statement to resolve.
460      *
461      * @return string
462      */
463     public function resolve_statement($statement)
464     {
465         $primary = $this->primary;
466         $primary = is_array($primary) ? '__multicolumn_primary__' . implode('_', $primary) : $primary;
467 
468         return strtr($statement, [
469 
470             '{alias}' => $this->alias,
471             '{prefix}' => $this->connection->prefix,
472             '{primary}' => $primary,
473             '{self}' => $this->name,
474             '{self_and_related}' => "`$this->name`" . ($this->select_join ? " $this->select_join" : '')
475         ]);
476     }
477 
478     /**
479      * Interface to the connection's prepare method.
480      *
481      * The statement is resolved by the {@link resolve_statement()} method before the call is
482      * forwarded.
483      *
484      * @return Database\Statement
485      */
486     public function prepare($query, $options=[])
487     {
488         $query = $this->resolve_statement($query);
489 
490         return $this->connection->prepare($query, $options);
491     }
492 
493     public function quote($string, $parameter_type=\PDO::PARAM_STR)
494     {
495         return $this->connection->quote($string, $parameter_type);
496     }
497 
498     /**
499      * Executes a statement.
500      *
501      * The statement is prepared by the {@link prepare()} method before it is executed.
502      *
503      * @return mixed
504      */
505     public function execute($query, array $args=[], array $options=[])
506     {
507         $statement = $this->prepare($query, $options);
508 
509         return $statement->execute($args);
510     }
511 
512     /**
513      * Interface to the connection's query() method.
514      *
515      * The statement is resolved using the resolve_statement() method and prepared.
516      *
517      * @param string $query
518      * @param array $args
519      * @param array $options
520      *
521      * @return Database\Statement
522      */
523     public function query($query, array $args=[], array $options=[])
524     {
525         $query = $this->resolve_statement($query);
526 
527         $statement = $this->prepare($query, $options);
528         $statement->execute($args);
529 
530         return $statement;
531     }
532 
533     protected function filter_values(array $values, $extended=false)
534     {
535         $filtered = [];
536         $holders = [];
537         $identifiers = [];
538 
539         $schema = $extended ? $this->extended_schema : $this->schema;
540         $fields = $schema['fields'];
541 
542         foreach ($values as $identifier => $value)
543         {
544             if (!array_key_exists($identifier, $fields))
545             {
546                 continue;
547             }
548 
549             if ($value instanceof \DateTime)
550             {
551                 $value = DateTime::from($value);
552                 $value = $value->utc->as_db;
553             }
554 
555             $filtered[] = $value;
556             $holders[$identifier] = '`' . $identifier . '` = ?';
557             $identifiers[] = '`' . $identifier . '`';
558         }
559 
560         return [ $filtered, $holders, $identifiers ];
561     }
562 
563     public function save(array $values, $id=null, array $options=[])
564     {
565         if ($id)
566         {
567             return $this->update($values, $id) ? $id : false;
568         }
569 
570         return $this->save_callback($values, $id, $options);
571     }
572 
573     protected function save_callback(array $values, $id=null, array $options=[])
574     {
575         if ($id)
576         {
577             $this->update($values, $id);
578 
579             return $id;
580         }
581 
582         if (empty($this->schema['fields']))
583         {
584             throw new \Exception('Missing fields in schema');
585         }
586 
587         $parent_id = 0;
588 
589         if ($this->parent)
590         {
591             $parent_id = $this->parent->save_callback($values, $id, $options);
592 
593             if (!$parent_id)
594             {
595                 throw new \Exception("Parent save failed: {$this->parent->name} returning {$parent_id}.");
596             }
597         }
598 
599         $driver_name = $this->connection->driver_name;
600 
601         list($filtered, $holders) = $this->filter_values($values);
602 
603         // FIXME: ALL THIS NEED REWRITE !
604 
605         if ($holders)
606         {
607             // faire attention à l'id, si l'on revient du parent qui a inséré, on doit insérer aussi, avec son id
608 
609             if ($id)
610             {
611                 $filtered[] = $id;
612 
613                 $statement = 'UPDATE `{self}` SET ' . implode(', ', $holders) . ' WHERE `{primary}` = ?';
614 
615                 $statement = $this->prepare($statement);
616 
617                 $rc = $statement->execute($filtered);
618             }
619             else
620             {
621                 if ($driver_name == 'mysql')
622                 {
623                     if ($parent_id && empty($holders[$this->primary]))
624                     {
625                         $filtered[] = $parent_id;
626                         $holders[] = '`{primary}` = ?';
627                     }
628 
629                     $statement = 'INSERT INTO `{self}` SET ' . implode(', ', $holders);
630 
631                     $statement = $this->prepare($statement);
632 
633                     $rc = $statement->execute($filtered);
634                 }
635                 else if ($driver_name == 'sqlite')
636                 {
637                     $rc = $this->insert($values, $options);
638                 }
639             }
640         }
641         else if ($parent_id && !$id)
642         {
643             #
644             # a new entry has been created, but we don't have any other fields then the primary key
645             #
646 
647             if (empty($holders[$this->primary]))
648             {
649                 $filtered[] = $parent_id;
650                 $holders[] = '`{primary}` = ?';
651             }
652 
653             $statement = 'INSERT INTO `{self}` SET ' . implode(', ', $holders);
654 
655             $statement = $this->prepare($statement);
656 
657             $rc = $statement->execute($filtered);
658         }
659         else
660         {
661             $rc = true;
662         }
663 
664         if ($parent_id)
665         {
666             return $parent_id;
667         }
668 
669         if (!$rc)
670         {
671             return false;
672         }
673 
674         if (!$id)
675         {
676             $id = $this->connection->lastInsertId();
677         }
678 
679         return $id;
680     }
681 
682     /**
683      * Inserts values into the table.
684      *
685      * @param array $values The values to insert.
686      * @param array $options The folowing options can be used:
687      * - `ignore`: Ignore duplicate errors.
688      * - `on duplicate`: specifies the column to update on duplicate, and the values to update
689      * them. If `true` the `$values` array is used, after the primary keys has been removed.
690      *
691      * @return mixed
692      */
693     public function insert(array $values, array $options=[])
694     {
695         list($values, $holders, $identifiers) = $this->filter_values($values);
696 
697         if (!$values)
698         {
699             return;
700         }
701 
702         $driver_name = $this->connection->driver_name;
703 
704         $on_duplicate = isset($options['on duplicate']) ? $options['on duplicate'] : null;
705 
706         if ($driver_name == 'mysql')
707         {
708             $query = 'INSERT';
709 
710             if (!empty($options['ignore']))
711             {
712                 $query .= ' IGNORE ';
713             }
714 
715             $query .= ' INTO `{self}` SET ' . implode(', ', $holders);
716 
717             if ($on_duplicate)
718             {
719                 if ($on_duplicate === true)
720                 {
721                     #
722                     # if 'on duplicate' is true, we use the same input values, but we take care of
723                     # removing the primary key and its corresponding value
724                     #
725 
726                     $update_values = array_combine(array_keys($holders), $values);
727                     $update_holders = $holders;
728 
729                     $primary = $this->primary;
730 
731                     if (is_array($primary))
732                     {
733                         $flip = array_flip($primary);
734 
735                         $update_holders = array_diff_key($update_holders, $flip);
736                         $update_values = array_diff_key($update_values, $flip);
737                     }
738                     else
739                     {
740                         unset($update_holders[$primary]);
741                         unset($update_values[$primary]);
742                     }
743 
744                     $update_values = array_values($update_values);
745                 }
746                 else
747                 {
748                     list($update_values, $update_holders) = $this->filter_values($on_duplicate);
749                 }
750 
751                 $query .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $update_holders);
752 
753                 $values = array_merge($values, $update_values);
754             }
755         }
756         else if ($driver_name == 'sqlite')
757         {
758             $holders = array_fill(0, count($identifiers), '?');
759 
760             $query = 'INSERT' . ($on_duplicate ? ' OR REPLACE' : '') . ' INTO `{self}` (' . implode(', ', $identifiers) . ') VALUES (' . implode(', ', $holders) . ')';
761         }
762 
763         return $this->execute($query, $values);
764     }
765 
766     /**
767      * Update the values of an entry.
768      *
769      * Even if the entry is spread over multiple tables, all the tables are updated in a single
770      * step.
771      *
772      * @param array $values
773      * @param mixed $key
774      *
775      * @return bool
776      */
777     public function update(array $values, $key)
778     {
779         #
780         # SQLite doesn't support UPDATE with INNER JOIN.
781         #
782 
783         if ($this->connection->driver_name == 'sqlite')
784         {
785             $table = $this;
786             $rc = true;
787 
788             while ($table)
789             {
790                 list($table_values, $holders) = $table->filter_values($values);
791 
792                 if ($holders)
793                 {
794                     $query = 'UPDATE `{self}` SET ' . implode(', ', $holders) . ' WHERE `{primary}` = ?';
795                     $table_values[] = $key;
796 
797                     $rc = $table->execute($query, $table_values);
798 
799                     if (!$rc)
800                     {
801                         return $rc;
802                     }
803                 }
804 
805                 $table = $table->parent;
806             }
807 
808             return $rc;
809         }
810 
811         list($values, $holders) = $this->filter_values($values, true);
812 
813         $query = 'UPDATE `{self}` ' . $this->update_join . ' SET ' . implode(', ', $holders) . ' WHERE `{primary}` = ?';
814         $values[] = $key;
815 
816         return $this->execute($query, $values);
817     }
818 
819     /**
820      * Deletes a record.
821      *
822      * @param mixed $key Identifier of the record.
823      *
824      * @return bool
825      */
826     public function delete($key)
827     {
828         if ($this->parent)
829         {
830             $this->parent->delete($key);
831         }
832 
833         $where = 'where ';
834 
835         if (is_array($this->primary))
836         {
837             $parts = [];
838 
839             foreach ($this->primary as $identifier)
840             {
841                 $parts[] = '`' . $identifier . '` = ?';
842             }
843 
844             $where .= implode(' and ', $parts);
845         }
846         else
847         {
848             $where .= '`{primary}` = ?';
849         }
850 
851         $statement = $this->prepare('delete from `{self}` ' . $where);
852         $statement((array) $key);
853 
854         return !!$statement->rowCount();
855     }
856 
857     // FIXME-20081223: what about extends ?
858 
859     public function truncate()
860     {
861         if ($this->connection->driver_name == 'sqlite')
862         {
863             $rc = $this->execute('delete from {self}');
864 
865             $this->execute('vacuum');
866 
867             return $rc;
868         }
869 
870         return $this->execute('truncate table `{self}`');
871     }
872 
873     public function drop(array $options=[])
874     {
875         $query = 'drop table ';
876 
877         if (!empty($options['if exists']))
878         {
879             $query .= 'if exists ';
880         }
881 
882         $query .= '`{self}`';
883 
884         return $this->execute($query);
885     }
886 }