1: <?php
2: /**
3: * Class for SQLite-specific managing of database schemes and handling of SQL
4: * dialects and quoting.
5: *
6: * Copyright 2007 Maintainable Software, LLC
7: * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
8: *
9: * @author Mike Naberezny <mike@maintainable.com>
10: * @author Derek DeVries <derek@maintainable.com>
11: * @author Chuck Hagenbuch <chuck@horde.org>
12: * @author Jan Schneider <jan@horde.org>
13: * @license http://www.horde.org/licenses/bsd
14: * @category Horde
15: * @package Db
16: * @subpackage Adapter
17: */
18:
19: /**
20: * @author Mike Naberezny <mike@maintainable.com>
21: * @author Derek DeVries <derek@maintainable.com>
22: * @author Chuck Hagenbuch <chuck@horde.org>
23: * @author Jan Schneider <jan@horde.org>
24: * @license http://www.horde.org/licenses/bsd
25: * @category Horde
26: * @package Db
27: * @subpackage Adapter
28: */
29: class Horde_Db_Adapter_Sqlite_Schema extends Horde_Db_Adapter_Base_Schema
30: {
31: /*##########################################################################
32: # Object factories
33: ##########################################################################*/
34:
35: /**
36: * Factory for Column objects.
37: *
38: * @param string $name The column's name, such as "supplier_id" in
39: * "supplier_id int(11)".
40: * @param string $default The type-casted default value, such as "new" in
41: * "sales_stage varchar(20) default 'new'".
42: * @param string $sqlType Used to extract the column's type, length and
43: * signed status, if necessary. For example
44: * "varchar" and "60" in "company_name varchar(60)"
45: * or "unsigned => true" in "int(10) UNSIGNED".
46: * @param boolean $null Whether this column allows NULL values.
47: *
48: * @return Horde_Db_Adapter_Base_Column A column object.
49: */
50: public function makeColumn($name, $default, $sqlType = null, $null = true)
51: {
52: return new Horde_Db_Adapter_Sqlite_Column($name, $default, $sqlType, $null);
53: }
54:
55:
56: /*##########################################################################
57: # Quoting
58: ##########################################################################*/
59:
60: /**
61: * Returns a quoted form of the column name.
62: *
63: * @param string $name A column name.
64: *
65: * @return string The quoted column name.
66: */
67: public function quoteColumnName($name)
68: {
69: return '"' . str_replace('"', '""', $name) . '"';
70: }
71:
72: /**
73: * Returns a quoted boolean true.
74: *
75: * @return string The quoted boolean true.
76: */
77: public function quoteTrue()
78: {
79: return '1';
80: }
81:
82: /**
83: * Returns a quoted boolean false.
84: *
85: * @return string The quoted boolean false.
86: */
87: public function quoteFalse()
88: {
89: return '0';
90: }
91:
92: /**
93: * Returns a quoted binary value.
94: *
95: * @param mixed A binary value.
96: *
97: * @return string The quoted binary value.
98: */
99: public function quoteBinary($value)
100: {
101: return "'" . str_replace(array("'", '%', "\0"), array("''", '%25', '%00'), $value) . "'";
102: }
103:
104:
105: /*##########################################################################
106: # Schema Statements
107: ##########################################################################*/
108:
109: /**
110: * Returns a hash of mappings from the abstract data types to the native
111: * database types.
112: *
113: * See TableDefinition::column() for details on the recognized abstract
114: * data types.
115: *
116: * @see TableDefinition::column()
117: *
118: * @return array A database type map.
119: */
120: public function nativeDatabaseTypes()
121: {
122: return array(
123: 'autoincrementKey' => $this->_defaultPrimaryKeyType(),
124: 'string' => array('name' => 'varchar', 'limit' => 255),
125: 'text' => array('name' => 'text', 'limit' => null),
126: 'mediumtext' => array('name' => 'text', 'limit' => null),
127: 'longtext' => array('name' => 'text', 'limit' => null),
128: 'integer' => array('name' => 'int', 'limit' => null),
129: 'float' => array('name' => 'float', 'limit' => null),
130: 'decimal' => array('name' => 'decimal', 'limit' => null),
131: 'datetime' => array('name' => 'datetime', 'limit' => null),
132: 'timestamp' => array('name' => 'datetime', 'limit' => null),
133: 'time' => array('name' => 'time', 'limit' => null),
134: 'date' => array('name' => 'date', 'limit' => null),
135: 'binary' => array('name' => 'blob', 'limit' => null),
136: 'boolean' => array('name' => 'boolean', 'limit' => null),
137: );
138: }
139:
140: /**
141: * Returns a list of all tables of the current database.
142: *
143: * @return array A table list.
144: */
145: public function tables()
146: {
147: return $this->selectValues("SELECT name FROM sqlite_master WHERE type = 'table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type = 'table' AND name != 'sqlite_sequence' ORDER BY name");
148: }
149:
150: /**
151: * Returns a table's primary key.
152: *
153: * @param string $tableName A table name.
154: * @param string $name (can be removed?)
155: *
156: * @return Horde_Db_Adapter_Base_Index The primary key index object.
157: */
158: public function primaryKey($tableName, $name = null)
159: {
160: // Share the columns cache with the columns() method
161: $rows = @unserialize($this->_cache->get("tables/columns/$tableName"));
162:
163: if (!$rows) {
164: $rows = $this->selectAll('PRAGMA table_info(' . $this->quoteTableName($tableName) . ')', $name);
165:
166: $this->_cache->set("tables/columns/$tableName", serialize($rows));
167: }
168:
169: $pk = $this->makeIndex($tableName, 'PRIMARY', true, true, array());
170: foreach ($rows as $row) {
171: if ($row['pk'] == 1) {
172: $pk->columns[] = $row['name'];
173: }
174: }
175:
176: return $pk;
177: }
178:
179: /**
180: * Returns a list of tables indexes.
181: *
182: * @param string $tableName A table name.
183: * @param string $name (can be removed?)
184: *
185: * @return array A list of Horde_Db_Adapter_Base_Index objects.
186: */
187: public function indexes($tableName, $name = null)
188: {
189: $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName"));
190:
191: if (!$indexes) {
192: $indexes = array();
193: foreach ($this->select('PRAGMA index_list(' . $this->quoteTableName($tableName) . ')') as $row) {
194: if (strpos($row['name'], 'sqlite_') !== false) {
195: // ignore internal sqlite_* index tables
196: continue;
197: }
198: $index = $this->makeIndex(
199: $tableName, $row['name'], false, (bool)$row['unique'], array());
200: foreach ($this->select('PRAGMA index_info(' . $this->quoteColumnName($index->name) . ')') as $field) {
201: $index->columns[] = $field['name'];
202: }
203:
204: $indexes[] = $index;
205: }
206:
207: $this->_cache->set("tables/indexes/$tableName", serialize($indexes));
208: }
209:
210: return $indexes;
211: }
212:
213: /**
214: * Returns a list of table columns.
215: *
216: * @param string $tableName A table name.
217: * @param string $name (can be removed?)
218: *
219: * @return array A list of Horde_Db_Adapter_Base_Column objects.
220: */
221: public function columns($tableName, $name = null)
222: {
223: $rows = @unserialize($this->_cache->get("tables/columns/$tableName"));
224:
225: if (!$rows) {
226: $rows = $this->selectAll('PRAGMA table_info(' . $this->quoteTableName($tableName) . ')', $name);
227: $this->_cache->set("tables/columns/$tableName", serialize($rows));
228: }
229:
230: // create columns from rows
231: $columns = array();
232: foreach ($rows as $row) {
233: $columns[$row['name']] = $this->makeColumn(
234: $row['name'], $row['dflt_value'], $row['type'], !(bool)$row['notnull']);
235: }
236:
237: return $columns;
238: }
239:
240: /**
241: * Renames a table.
242: *
243: * @param string $name A table name.
244: * @param string $newName The new table name.
245: */
246: public function renameTable($name, $newName)
247: {
248: $this->_clearTableCache($name);
249: $sql = sprintf('ALTER TABLE %s RENAME TO %s',
250: $this->quoteTableName($name),
251: $this->quoteTableName($newName));
252: return $this->execute($sql);
253: }
254:
255: /**
256: * Adds a new column to a table.
257: *
258: * @param string $tableName A table name.
259: * @param string $columnName A column name.
260: * @param string $type A data type.
261: * @param array $options Column options. See
262: * Horde_Db_Adapter_Base_TableDefinition#column()
263: * for details.
264: */
265: public function addColumn($tableName, $columnName, $type, $options=array())
266: {
267: /* Ignore ':autoincrement' - it is handled automatically by SQLite
268: * for any 'INTEGER PRIMARY KEY' column. */
269: if ($this->transactionStarted()) {
270: throw new Horde_Db_Exception('Cannot add columns to a SQLite database while inside a transaction');
271: }
272:
273: parent::addColumn($tableName, $columnName, $type, $options);
274:
275: // See last paragraph on http://www.sqlite.org/lang_altertable.html
276: $this->execute('VACUUM');
277: }
278:
279: /**
280: * Removes a column from a table.
281: *
282: * @param string $tableName A table name.
283: * @param string $columnName A column name.
284: */
285: public function removeColumn($tableName, $columnName)
286: {
287: $this->_clearTableCache($tableName);
288:
289: return $this->_alterTable(
290: $tableName,
291: array(),
292: create_function('$definition',
293: 'unset($definition["' . $columnName . '"]);'));
294: }
295:
296: /**
297: * Changes an existing column's definition.
298: *
299: * @param string $tableName A table name.
300: * @param string $columnName A column name.
301: * @param string $type A data type.
302: * @param array $options Column options. See
303: * Horde_Db_Adapter_Base_TableDefinition#column()
304: * for details.
305: */
306: public function changeColumn($tableName, $columnName, $type, $options=array())
307: {
308: $this->_clearTableCache($tableName);
309:
310: $defs = array(sprintf('$definition["%s"]->setType("%s"); if ("%s" == "autoincrementKey") $definition->primaryKey(false);', $columnName, $type, $type));
311: if (isset($options['limit'])) {
312: $defs[] = sprintf('$definition["%s"]->setLimit("%s");', $columnName, $options['limit']);
313: }
314: if (isset($options['null'])) {
315: $defs[] = sprintf('$definition["%s"]->setNull("%s");', $columnName, $options['null']);
316: }
317: if (isset($options['precision'])) {
318: $defs[] = sprintf('$definition["%s"]->setPrecision("%s");', $columnName, $options['precision']);
319: }
320: if (isset($options['scale'])) {
321: $defs[] = sprintf('$definition["%s"]->setScale("%s");', $columnName, $options['scale']);
322: }
323:
324: if (array_key_exists('default', $options)) {
325: if ($options['default'] === true) {
326: $default = 'true';
327: } elseif ($options['default'] === false) {
328: $default = 'false';
329: } elseif ($options['default'] === null) {
330: $default = 'null';
331: } else {
332: $default = '"' . $options['default'] . '"';
333: }
334: $defs[] = sprintf('$definition["%s"]->setDefault(%s);', $columnName, $default);
335: }
336:
337: return $this->_alterTable(
338: $tableName,
339: array(),
340: create_function('$definition', implode("\n", $defs)));
341: }
342:
343: /**
344: * Sets a new default value for a column.
345: *
346: * If you want to set the default value to NULL, you are out of luck. You
347: * need to execute the apppropriate SQL statement yourself.
348: *
349: * @param string $tableName A table name.
350: * @param string $columnName A column name.
351: * @param mixed $default The new default value.
352: */
353: public function changeColumnDefault($tableName, $columnName, $default)
354: {
355: $this->_clearTableCache($tableName);
356:
357: $default = is_null($default) ? 'null' : '"' . $default . '"';
358: return $this->_alterTable(
359: $tableName,
360: array(),
361: create_function('$definition',
362: sprintf('$definition["%s"]->setDefault(%s);',
363: $columnName, $default)));
364: }
365:
366: /**
367: * Renames a column.
368: *
369: * @param string $tableName A table name.
370: * @param string $columnName A column name.
371: * @param string $newColumnName The new column name.
372: */
373: public function renameColumn($tableName, $columnName, $newColumnName)
374: {
375: $this->_clearTableCache($tableName);
376:
377: return $this->_alterTable(
378: $tableName,
379: array('rename' => array($columnName => $newColumnName)));
380: }
381:
382: /**
383: * Adds a primary key to a table.
384: *
385: * @since Horde_Db 1.1.0
386: *
387: * @param string $tableName A table name.
388: * @param string|array $columnName One or more column names.
389: *
390: * @throws Horde_Db_Exception
391: */
392: public function addPrimaryKey($tableName, $columns)
393: {
394: $this->_clearTableCache($tableName);
395: $columns = (array)$columns;
396: foreach ($columns as &$column) {
397: $column = '"' . $column . '"';
398: }
399: $callback = create_function(
400: '$definition',
401: sprintf('$definition->primaryKey(array(%s));',
402: implode(', ', $columns)));
403: $this->_alterTable($tableName, array(), $callback);
404: }
405:
406: /**
407: * Removes a primary key from a table.
408: *
409: * @since Horde_Db 1.1.0
410: *
411: * @param string $tableName A table name.
412: *
413: * @throws Horde_Db_Exception
414: */
415: public function removePrimaryKey($tableName)
416: {
417: $this->_clearTableCache($tableName);
418: $callback = create_function('$definition',
419: '$definition->primaryKey(false);');
420: $this->_alterTable($tableName, array(), $callback);
421: }
422:
423: /**
424: * Removes an index from a table.
425: *
426: * See parent class for examples.
427: *
428: * @param string $tableName A table name.
429: * @param string|array $options Either a column name or index options:
430: * - name: (string) the index name.
431: * - column: (string|array) column name(s).
432: */
433: public function removeIndex($tableName, $options=array())
434: {
435: $this->_clearTableCache($tableName);
436:
437: $index = $this->indexName($tableName, $options);
438: $sql = 'DROP INDEX ' . $this->quoteColumnName($index);
439: return $this->execute($sql);
440: }
441:
442: /**
443: * Creates a database.
444: *
445: * @param string $name A database name.
446: * @param array $options Database options.
447: */
448: public function createDatabase($name, $options = array())
449: {
450: return new PDO('sqlite:' . $name);
451: }
452:
453: /**
454: * Drops a database.
455: *
456: * @param string $name A database name.
457: */
458: public function dropDatabase($name)
459: {
460: if (!@file_exists($name)) {
461: throw new Horde_Db_Exception('database does not exist');
462: }
463:
464: if (!@unlink($name)) {
465: throw new Horde_Db_Exception('could not remove the database file');
466: }
467: }
468:
469: /**
470: * Returns the name of the currently selected database.
471: *
472: * @return string The database name.
473: */
474: public function currentDatabase()
475: {
476: return $this->_config['dbname'];
477: }
478:
479: /**
480: * Generates a modified date for SELECT queries.
481: *
482: * @since Horde_Db 1.2.0
483: *
484: * @param string $reference The reference date - this is a column
485: * referenced in the SELECT.
486: * @param string $operator Add or subtract time? (+/-)
487: * @param integer $amount The shift amount (number of days if $interval
488: * is DAY, etc).
489: * @param string $interval The interval (SECOND, MINUTE, HOUR, DAY,
490: * MONTH, YEAR).
491: *
492: * @return string The generated INTERVAL clause.
493: */
494: public function modifyDate($reference, $operator, $amount, $interval)
495: {
496: if (!is_int($amount)) {
497: throw new InvalidArgumentException('$amount parameter must be an integer');
498: }
499: switch ($interval) {
500: case 'YEAR':
501: $interval = 'years';
502: break;
503: case 'MONTH':
504: $interval = 'months';
505: break;
506: case 'DAY':
507: $interval = 'days';
508: break;
509: case 'HOUR':
510: $interval = 'hours';
511: break;
512: case 'MINUTE':
513: $interval = 'minutes';
514: break;
515: case 'SECOND':
516: $interval = 'seconds';
517: break;
518: default:
519: break;
520: }
521:
522: return 'datetime(' . $reference . ', \'' . $operator . $amount . ' '
523: . $interval . '\')';
524: }
525:
526:
527: /*##########################################################################
528: # Protected
529: ##########################################################################*/
530:
531: /**
532: * Returns a column type definition to be use for primary keys.
533: *
534: * @return string Primary key type definition.
535: */
536: protected function _defaultPrimaryKeyType()
537: {
538: if ($this->supportsAutoIncrement()) {
539: return 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL';
540: } else {
541: return 'INTEGER PRIMARY KEY NOT NULL';
542: }
543: }
544:
545: /**
546: * Alters a table.
547: *
548: * This is done by creating a temporary copy, applying changes and callback
549: * methods, and copying the table back.
550: *
551: * @param string $tableName A table name.
552: * @param array $options Any options to apply when creating the
553: * temporary table. Supports a 'rename' key for
554: * the new table name, additionally to the
555: * options in createTable().
556: * @param function $callback A callback function that can manipulate the
557: * Horde_Db_Adapter_Base_TableDefinition object
558: * available in $definition. See _copyTable().
559: */
560: protected function _alterTable($tableName, $options = array(), $callback = null)
561: {
562: $this->beginDbTransaction();
563:
564: $alteredTableName = 'altered_' . $tableName;
565: $this->_moveTable($tableName,
566: $alteredTableName,
567: array_merge($options, array('temporary' => true)));
568: $this->_moveTable($alteredTableName,
569: $tableName,
570: array(),
571: $callback);
572:
573: $this->commitDbTransaction();
574: }
575:
576: /**
577: * Moves a table.
578: *
579: * This is done by creating a temporary copy, applying changes and callback
580: * methods, and dropping the original table.
581: *
582: * @param string $from The name of the source table.
583: * @param string $to The name of the target table.
584: * @param array $options Any options to apply when creating the
585: * temporary table. Supports a 'rename' key for
586: * the new table name, additionally to the
587: * options in createTable().
588: * @param function $callback A callback function that can manipulate the
589: * Horde_Db_Adapter_Base_TableDefinition object
590: * available in $definition. See _copyTable().
591: */
592: protected function _moveTable($from, $to, $options = array(),
593: $callback = null)
594: {
595: $this->_copyTable($from, $to, $options, $callback);
596: $this->dropTable($from);
597: }
598:
599: /**
600: * Copies a table.
601: *
602: * Also applies changes and callback methods before creating the new table.
603: *
604: * @param string $from The name of the source table.
605: * @param string $to The name of the target table.
606: * @param array $options Any options to apply when creating the
607: * temporary table. Supports a 'rename' key for
608: * the new table name, additionally to the
609: * options in createTable().
610: * @param function $callback A callback function that can manipulate the
611: * Horde_Db_Adapter_Base_TableDefinition object
612: * available in $definition.
613: */
614: protected function _copyTable($from, $to, $options = array(),
615: $callback = null)
616: {
617: $fromColumns = $this->columns($from);
618: $pk = $this->primaryKey($from);
619: if ($pk && count($pk->columns) == 1) {
620: /* A primary key is not necessarily what matches the pseudo type
621: * "autoincrementKey". We need to parse the table definition to
622: * find out if the column is AUTOINCREMENT too. */
623: $tableDefinition = $this->selectValue('SELECT sql FROM sqlite_master WHERE name = ? UNION ALL SELECT sql FROM sqlite_temp_master WHERE name = ?',
624: array($from, $from));
625: if (strpos($tableDefinition, $this->quoteColumnName($pk->columns[0]) . ' INTEGER PRIMARY KEY AUTOINCREMENT')) {
626: $pkColumn = $pk->columns[0];
627: } else {
628: $pkColumn = null;
629: }
630: } else {
631: $pkColumn = null;
632: }
633: $options = array_merge($options, array('autoincrementKey' => false));
634:
635: $copyPk = true;
636: $definition = $this->createTable($to, $options);
637: foreach ($fromColumns as $column) {
638: $columnName = isset($options['rename'][$column->getName()])
639: ? $options['rename'][$column->getName()]
640: : $column->getName();
641: $columnType = $column->getName() == $pkColumn
642: ? 'autoincrementKey'
643: : $column->getType();
644:
645: if ($columnType == 'autoincrementKey') {
646: $copyPk = false;
647: }
648: $definition->column($columnName, $columnType,
649: array('limit' => $column->getLimit(),
650: 'default' => $column->getDefault(),
651: 'null' => $column->isNull()));
652: }
653:
654: if ($pkColumn && count($pk->columns) && $copyPk) {
655: $definition->primaryKey($pk->columns);
656: }
657:
658: if (is_callable($callback)) {
659: call_user_func($callback, $definition);
660: }
661:
662: $definition->end();
663:
664: $this->_copyTableIndexes(
665: $from,
666: $to,
667: isset($options['rename']) ? $options['rename'] : array());
668: $this->_copyTableContents(
669: $from,
670: $to,
671: array_map(create_function('$c', 'return $c->getName();'),
672: iterator_to_array($definition)),
673: isset($options['rename']) ? $options['rename'] : array());
674: }
675:
676: /**
677: * Copies indexes from one table to another.
678: *
679: * @param string $from The name of the source table.
680: * @param string $to The name of the target table.
681: * @param array $rename A hash of columns to rename during the copy, with
682: * original names as keys and the new names as values.
683: */
684: protected function _copyTableIndexes($from, $to, $rename = array())
685: {
686: $toColumnNames = array();
687: foreach ($this->columns($to) as $c) {
688: $toColumnNames[$c->getName()] = true;
689: }
690:
691: foreach ($this->indexes($from) as $index) {
692: $name = $index->getName();
693: if ($to == 'altered_' . $from) {
694: $name = 'temp_' . $name;
695: } elseif ($from == 'altered_' . $to) {
696: $name = substr($name, 5);
697: }
698:
699: $columns = array();
700: foreach ($index->columns as $c) {
701: if (isset($rename[$c])) {
702: $c = $rename[$c];
703: }
704: if (isset($toColumnNames[$c])) {
705: $columns[] = $c;
706: }
707: }
708:
709: if (!empty($columns)) {
710: // Index name can't be the same
711: $opts = array('name' => str_replace('_' . $from . '_', '_' . $to . '_', $name));
712: if ($index->unique) {
713: $opts['unique'] = true;
714: }
715: $this->addIndex($to, $columns, $opts);
716: }
717: }
718: }
719:
720: /**
721: * Copies the content of one table to another.
722: *
723: * @param string $from The name of the source table.
724: * @param string $to The name of the target table.
725: * @param array $columns A list of columns to copy.
726: * @param array $rename A hash of columns to rename during the copy, with
727: * original names as keys and the new names as
728: * values.
729: */
730: protected function _copyTableContents($from, $to, $columns,
731: $rename = array())
732: {
733: $columnMappings = array_combine($columns, $columns);
734:
735: foreach ($rename as $renameFrom => $renameTo) {
736: $columnMappings[$renameTo] = $renameFrom;
737: }
738:
739: $fromColumns = array();
740: foreach ($this->columns($from) as $col) {
741: $fromColumns[] = $col->getName();
742: }
743:
744: $tmpColumns = array();
745: foreach ($columns as $col) {
746: if (in_array($columnMappings[$col], $fromColumns)) {
747: $tmpColumns[] = $col;
748: }
749: }
750: $columns = $tmpColumns;
751:
752: $fromColumns = array();
753: foreach ($columns as $col) {
754: $fromColumns[] = $columnMappings[$col];
755: }
756:
757: $quotedTo = $this->quoteTableName($to);
758: $quotedToColumns = implode(', ', array_map(array($this, 'quoteColumnName'), $columns));
759:
760: $quotedFrom = $this->quoteTableName($from);
761: $quotedFromColumns = implode(', ', array_map(array($this, 'quoteColumnName'), $fromColumns));
762:
763: $sql = sprintf('INSERT INTO %s (%s) SELECT %s FROM %s',
764: $quotedTo,
765: $quotedToColumns,
766: $quotedFromColumns,
767: $quotedFrom);
768: $this->execute($sql);
769: }
770: }
771: