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\PropertyNotDefined;
15
16 /**
17 * A connection to a databse.
18 *
19 * @property-read string $charset The character set used to communicate with the database. Defaults to "utf8".
20 * @property-read string $collate The collation of the character set. Defaults to "utf8_general_ci".
21 * @property-read string $driver_name Name of the PDO driver.
22 * @property-read string $id Identifier of the database connection.
23 * @property-read string $table_name_prefix The prefix to prepend to every table name.
24 */
25 class Connection extends \PDO
26 {
27 // TODO-20130216: deprecate all T_*
28
29 const T_ID = '#id';
30 const T_TABLE_NAME_PREFIX = '#table_name_prefix';
31 const T_CHARSET = '#charset';
32 const T_COLLATE = '#collate';
33 const T_TIMEZONE = '#timezone';
34
35 const ID = '#id';
36 const TABLE_NAME_PREFIX = '#table_name_prefix';
37 const CHARSET = '#charset';
38 const COLLATE = '#collate';
39 const TIMEZONE = '#timezone';
40
41 /**
42 * Connection identifier.
43 *
44 * @var string
45 */
46 protected $id;
47
48 /**
49 * Prefix to prepend to every table name.
50 *
51 * So if set to "dev", all table names will be named like "dev_nodes", "dev_contents", etc.
52 * This is a convenient way of creating a namespace for tables in a shared database.
53 * By default, the prefix is the empty string.
54 *
55 * @var string
56 */
57 protected $table_name_prefix = '';
58
59 /**
60 * Charset for the connection. Also used to specify the charset while creating tables.
61 *
62 * @var string
63 */
64 protected $charset = 'utf8';
65
66 /**
67 * Used to specify the collate while creating tables.
68 *
69 * @var string
70 */
71 protected $collate = 'utf8_general_ci';
72
73 /**
74 * Driver name for the connection.
75 *
76 * @var string
77 */
78 protected $driver_name;
79
80 /**
81 * The number of database queries and executions, used for statistics purpose.
82 *
83 * @var int
84 */
85 public $queries_count = 0;
86
87 /**
88 * The number of micro seconds spent per request.
89 *
90 * @var array[]array
91 */
92 public $profiling = [];
93
94 /**
95 * Establish a connection to a database.
96 *
97 * Custom options can be specified using the driver-specific connection options:
98 *
99 * - {@link ID}: Connection identifier.
100 * - {@link TABLE_NAME_PREFIX}: Prefix for the database tables.
101 * - {@link CHARSET} and {@link COLLATE}: Charset and collate used for the connection
102 * to the database, and to create tables.
103 * - {@link TIMEZONE}: Timezone for the connection.
104 *
105 * @link http://www.php.net/manual/en/pdo.construct.php
106 * @link http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
107 *
108 * @param string $dsn
109 * @param string $username
110 * @param string $password
111 * @param array $options
112 */
113 public function __construct($dsn, $username=null, $password=null, $options=[])
114 {
115 list($driver_name) = explode(':', $dsn, 2);
116
117 $this->driver_name = $driver_name;
118
119 $timezone = null;
120
121 foreach ($options as $option => $value)
122 {
123 switch ($option)
124 {
125 case self::ID: $this->id = $value; break;
126 case '#prefix': // COMPAT-20120913
127 case self::TABLE_NAME_PREFIX: $this->table_name_prefix = $value ? $value . '_' : null; break;
128 case self::CHARSET: $this->charset = $value; $this->collate = null; break;
129 case self::COLLATE: $this->collate = $value; break;
130 case self::TIMEZONE: $timezone = $value; break;
131 }
132 }
133
134 if ($driver_name == 'mysql')
135 {
136 $init_command = 'SET NAMES ' . $this->charset;
137
138 if ($timezone)
139 {
140 $init_command .= ', time_zone = "' . $timezone . '"';
141 }
142
143 $options += [
144
145 self::MYSQL_ATTR_INIT_COMMAND => $init_command
146 ];
147 }
148
149 parent::__construct($dsn, $username, $password, $options);
150
151 $this->setAttribute(self::ATTR_ERRMODE, self::ERRMODE_EXCEPTION);
152 $this->setAttribute(self::ATTR_STATEMENT_CLASS, [ 'ICanBoogie\ActiveRecord\Statement' ]);
153
154 if ($driver_name == 'oci')
155 {
156 $this->exec("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
157 }
158 }
159
160 /**
161 * Alias to {@link exec}.
162 *
163 * @return mixed
164 */
165 public function __invoke()
166 {
167 return call_user_func_array([ $this, 'query' ], func_get_args());
168 }
169
170 public function __get($property)
171 {
172 switch ($property)
173 {
174 case 'charset':
175 case 'collate':
176 case 'driver_name':
177 case 'id':
178 case 'table_name_prefix':
179 return $this->$property;
180 case 'prefix': // COMPAT-20120913
181 return $this->table_name_prefix;
182 }
183
184 throw new PropertyNotDefined([ $property, $this ]);
185 }
186
187 /**
188 * Overrides the method to resolve the statement before it is prepared, then set its fetch
189 * mode and connection.
190 *
191 * @param string $statement Query statement.
192 * @param array $options
193 *
194 * @return Database\Statement The prepared statement.
195 *
196 * @throws StatementInvalid if the statement cannot be prepared.
197 */
198 public function prepare($statement, $options=[])
199 {
200 $statement = $this->resolve_statement($statement);
201
202 try
203 {
204 $statement = parent::prepare($statement, $options);
205 }
206 catch (\PDOException $e)
207 {
208 throw new StatementInvalid($statement, 500, $e);
209 }
210
211 $statement->connection = $this;
212
213 if (isset($options['mode']))
214 {
215 $mode = (array) $options['mode'];
216
217 call_user_func_array([ $statement, 'setFetchMode' ], $mode);
218 }
219
220 return $statement;
221 }
222
223 /**
224 * Overrides the method in order to prepare (and resolve) the statement and execute it with
225 * the specified arguments and options.
226 *
227 * @return Statement
228 */
229 public function query($statement, array $args=[], array $options=[])
230 {
231 $statement = $this->prepare($statement, $options);
232 $statement->execute($args);
233
234 return $statement;
235 }
236
237 /**
238 * Executes a statement.
239 *
240 * The statement is resolved using the {@link resolve_statement()} method before it is
241 * executed.
242 *
243 * The execution of the statement is wrapped in a try/catch block. {@link PDOException} are
244 * caught and {@link StatementInvalid} exception are thrown with additional information
245 * instead.
246 *
247 * Using this method increments the `queries_by_connection` stat.
248 *
249 * @throws StatementInvalid if the statement cannot be executed.
250 */
251 public function exec($statement)
252 {
253 $statement = $this->resolve_statement($statement);
254
255 try
256 {
257 $this->queries_count++;
258
259 return parent::exec($statement);
260 }
261 catch (\PDOException $e)
262 {
263 throw new StatementInvalid($statement, 500, $e);
264 }
265 }
266
267 /**
268 * Places quotes around the identifier.
269 *
270 * @param string|array $identifier
271 *
272 * @return string|array
273 */
274 public function quote_identifier($identifier)
275 {
276 $quote = $this->driver_name == 'oci' ? '"' : '`';
277
278 if (is_array($identifier))
279 {
280 return array_map
281 (
282 function($v) use ($quote)
283 {
284 return $quote . $v . $quote;
285 },
286
287 $identifier
288 );
289 }
290
291 return $quote . $identifier . $quote;
292 }
293
294 /**
295 * Replaces placeholders with their value.
296 *
297 * The following placeholders are supported:
298 *
299 * - `{prefix}`: replaced by the {@link $table_name_prefix} property.
300 * - `{charset}`: replaced by the {@link $charset} property.
301 * - `{collate}`: replaced by the {@link $collate} property.
302 *
303 * @param string $statement
304 *
305 * @return string The resolved statement.
306 */
307 public function resolve_statement($statement)
308 {
309 return strtr($statement, [
310
311 '{prefix}' => $this->table_name_prefix,
312 '{charset}' => $this->charset,
313 '{collate}' => $this->collate
314 ]);
315 }
316
317 /**
318 * Alias for the `beginTransaction()` method.
319 *
320 * @see PDO::beginTransaction()
321 */
322 public function begin()
323 {
324 return $this->beginTransaction();
325 }
326
327 /**
328 * Parses a schema to create a schema with low level definitions.
329 *
330 * For example, a column defined as 'serial' is parsed as :
331 *
332 * 'type' => 'integer', 'serial' => true, 'size' => 'big', 'unsigned' => true,
333 * 'primary' => true
334 *
335 * @param array $schema
336 *
337 * @return array
338 */
339 public function parse_schema(array $schema)
340 {
341 $driver_name = $this->driver_name;
342
343 $schema['primary'] = [];
344 $schema['indexes'] = [];
345
346 foreach ($schema['fields'] as $identifier => &$definition)
347 {
348 $definition = (array) $definition;
349
350 #
351 # translate special indexes to keys
352 #
353
354 if (isset($definition[0]))
355 {
356 $definition['type'] = $definition[0];
357
358 unset($definition[0]);
359 }
360
361 if (isset($definition[1]))
362 {
363 $definition['size'] = $definition[1];
364
365 unset($definition[1]);
366 }
367
368 #
369 # handle special types
370 #
371
372 switch($definition['type'])
373 {
374 case 'serial':
375 {
376 $definition['type'] = 'integer';
377
378 #
379 # because auto increment only works on "INTEGER AUTO INCREMENT" in SQLite
380 #
381
382 if ($driver_name != 'sqlite')
383 {
384 $definition += [ 'size' => 'big', 'unsigned' => true ];
385 }
386
387 $definition += [ 'auto increment' => true, 'primary' => true ];
388 }
389 break;
390
391 case 'foreign':
392 {
393 $definition['type'] = 'integer';
394
395 if ($driver_name != 'sqlite')
396 {
397 $definition += [ 'size' => 'big', 'unsigned' => true ];
398 }
399
400 $definition += [ 'indexed' => true ];
401 }
402 break;
403
404 case 'varchar':
405 {
406 $definition += [ 'size' => 255 ];
407 }
408 break;
409 }
410
411 #
412 # primary
413 #
414
415 if (isset($definition['primary']) && !in_array($identifier, $schema['primary']))
416 {
417 $schema['primary'][] = $identifier;
418 }
419
420 #
421 # indexed
422 #
423
424 if (!empty($definition['indexed']) && empty($definition['unique']))
425 {
426 $index = $definition['indexed'];
427
428 if (is_string($index))
429 {
430 if (isset($schema['indexes'][$index]) && in_array($identifier, $schema['indexes'][$index]))
431 {
432 # $identifier is already defined in $index
433 }
434 else
435 {
436 $schema['indexes'][$index][] = $identifier;
437 }
438 }
439 else
440 {
441 if (!in_array($identifier, $schema['indexes']))
442 {
443 $schema['indexes'][$identifier] = $identifier;
444 }
445 }
446 }
447 }
448
449 #
450 # indexes that are part of the primary key are deleted
451 #
452
453 if ($schema['indexes'] && $schema['primary'])
454 {
455 // echo "<h3>DIFF</h3>";
456
457 // var_dump($schema['primary'], $schema['indexes'], array_diff($schema['indexes'], $schema['primary']));
458
459 $schema['indexes'] = array_diff($schema['indexes'], $schema['primary']);
460
461 /*
462 $primary = (array) $schema['primary'];
463
464 foreach ($schema['indexes'] as $identifier => $dummy)
465 {
466 if (!in_array($identifier, $primary))
467 {
468 continue;
469 }
470
471 unset($schema['indexes'][$identifier]);
472 }
473 */
474 }
475
476 if (count($schema['primary']) == 1)
477 {
478 $schema['primary'] = $schema['primary'][0];
479 }
480
481 return $schema;
482 }
483
484 /**
485 * Creates a table of the specified name and schema.
486 *
487 * @param string $unprefixed_name The unprefixed name of the table.
488 * @param array $schema The schema of the table.
489 *
490 * @return bool
491 */
492 public function create_table($unprefixed_name, array $schema)
493 {
494 // FIXME-20091201: I don't think 'UNIQUE' is properly implemented
495
496 $collate = $this->collate;
497 $driver_name = $this->driver_name;
498
499 $schema = $this->parse_schema($schema);
500
501 $parts = [];
502
503 foreach ($schema['fields'] as $identifier => $params)
504 {
505 $definition = '`' . $identifier . '`';
506
507 $type = $params['type'];
508 $size = isset($params['size']) ? $params['size'] : 0;
509
510 switch ($type)
511 {
512 case 'blob':
513 case 'char':
514 case 'integer':
515 case 'text':
516 case 'varchar':
517 case 'bit':
518 {
519 if ($size)
520 {
521 if (is_string($size))
522 {
523 $definition .= ' ' . strtoupper($size) . ($type == 'integer' ? 'INT' : $type);
524 }
525 else
526 {
527 $definition .= ' ' . $type . '(' . $size . ')';
528 }
529 }
530 else
531 {
532 $definition .= ' ' . $type;
533 }
534
535 if (($type == 'integer') && !empty($params['unsigned']))
536 {
537 $definition .= ' UNSIGNED';
538 }
539 }
540 break;
541
542 case 'boolean':
543 case 'date':
544 case 'datetime':
545 case 'time':
546 case 'timestamp':
547 case 'year':
548 {
549 $definition .= ' ' . $type;
550 }
551 break;
552
553 case 'enum':
554 {
555 $enum = [];
556
557 foreach ($size as $identifier)
558 {
559 $enum[] = '\'' . $identifier . '\'';
560 }
561
562 $definition .= ' ' . $type . '(' . implode(', ', $enum) . ')';
563 }
564 break;
565
566 case 'double':
567 case 'float':
568 {
569 $definition .= ' ' . $type;
570
571 if ($size)
572 {
573 $definition .= '(' . implode(', ', (array) $size) . ')';
574 }
575 }
576 break;
577
578 default:
579 {
580 throw new \InvalidArgumentException("Unsupported type <q>{$type}</q> for row <q>{$identifier}</q>.");
581 }
582 break;
583 }
584
585 #
586 # null
587 #
588
589 if (empty($params['null']))
590 {
591 $definition .= ' NOT NULL';
592 }
593 else
594 {
595 $definition .= ' NULL';
596 }
597
598 #
599 # default
600 #
601
602 if (!empty($params['default']))
603 {
604 $default = $params['default'];
605
606 $definition .= ' DEFAULT ' . ($default{strlen($default) - 1} == ')' || $default == 'CURRENT_TIMESTAMP' ? $default : '"' . $default . '"');
607 }
608
609 #
610 # serial, unique
611 #
612
613 if (!empty($params['auto increment']))
614 {
615 if ($driver_name == 'mysql')
616 {
617 $definition .= ' AUTO_INCREMENT';
618 }
619 else if ($driver_name == 'sqlite')
620 {
621 // $definition .= ' PRIMARY KEY';
622 // unset($schema['primary']);
623 }
624 }
625 else if (!empty($params['unique']))
626 {
627 $definition .= ' UNIQUE';
628 }
629
630 $parts[] = $definition;
631 }
632
633 #
634 # primary key
635 #
636
637 if ($schema['primary'])
638 {
639 $keys = (array) $schema['primary'];
640
641 $parts[] = 'PRIMARY KEY (' . implode(', ', $this->quote_identifier($keys)) . ')';
642 }
643
644 #
645 # indexes
646 #
647
648 if (isset($schema['indexes']) && $driver_name == 'mysql')
649 {
650 foreach ($schema['indexes'] as $key => $identifiers)
651 {
652 $definition = 'INDEX ';
653
654 if (!is_numeric($key))
655 {
656 $definition .= $this->quote_identifier($key) . ' ';
657 }
658
659 $definition .= '(' . implode(',', $this->quote_identifier((array) $identifiers)) . ')';
660
661 $parts[] = $definition;
662 }
663 }
664
665 $table_name = $this->prefix . $unprefixed_name;
666 $statement = 'CREATE TABLE `' . $table_name . '` (' . implode(', ', $parts) . ')';
667
668 if ($driver_name == 'mysql')
669 {
670 $statement .= ' CHARACTER SET ' . $this->charset . ' COLLATE ' . $this->collate;
671 }
672
673 $rc = ($this->exec($statement) !== false);
674
675 if (!$rc)
676 {
677 return $rc;
678 }
679
680 if (isset($schema['indexes']) && $driver_name == 'sqlite')
681 {
682 #
683 # SQLite: now that the table has been created, we can add indexes
684 #
685
686 foreach ($schema['indexes'] as $key => $identifiers)
687 {
688 $statement = 'CREATE INDEX IF NOT EXISTS `' . $key . '` ON ' . $table_name;
689
690 $identifiers = (array) $identifiers;
691
692 foreach ($identifiers as &$identifier)
693 {
694 $identifier = '`' . $identifier . '`';
695 }
696
697 $statement .= ' (' . implode(',', $identifiers) . ')';
698
699 $this->exec($statement);
700 }
701 }
702
703 return $rc;
704 }
705
706 /**
707 * Checks if a specified table exists in the database.
708 *
709 * @param string $unprefixed_name The unprefixed name of the table.
710 *
711 * @return bool `true` if the table exists, `false` otherwise.
712 */
713 public function table_exists($unprefixed_name)
714 {
715 $name = $this->prefix . $unprefixed_name;
716
717 if ($this->driver_name == 'sqlite')
718 {
719 $tables = $this
720 ->query('SELECT name FROM sqlite_master WHERE type = "table" AND name = ?', [ $name ])
721 ->fetchAll(self::FETCH_COLUMN);
722
723 return !empty($tables);
724 }
725 else
726 {
727 $tables = $this->query('SHOW TABLES')->fetchAll(self::FETCH_COLUMN);
728
729 return in_array($name, $tables);
730 }
731
732 return false;
733 }
734
735 /**
736 * Optimizes the tables of the database.
737 */
738 public function optimize()
739 {
740 if ($this->driver_name == 'sqlite')
741 {
742 $this->exec('VACUUM');
743 }
744 else if ($this->driver_name == 'mysql')
745 {
746 $tables = $this->query('SHOW TABLES')->fetchAll(self::FETCH_COLUMN);
747
748 $this->exec('OPTIMIZE TABLE ' . implode(', ', $tables));
749 }
750 }
751 }