1: <?php
2: /**
3: * Base class for managing database schemes and handling database-specific 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: abstract class Horde_Db_Adapter_Base_Schema
30: {
31: /**
32: * A Horde_Db_Adapter instance.
33: *
34: * @var Horde_Db_Adapter_Base
35: */
36: protected $_adapter = null;
37:
38: /**
39: * List of public methods supported by the attached adapter.
40: *
41: * Method names are in the keys.
42: *
43: * @var array
44: */
45: protected $_adapterMethods = array();
46:
47:
48: /*##########################################################################
49: # Construct/Destruct
50: ##########################################################################*/
51:
52: /**
53: * Constructor.
54: *
55: * @param Horde_Db_Adapter_Base $adapter A Horde_Db_Adapter instance.
56: */
57: public function __construct(Horde_Db_Adapter $adapter)
58: {
59: $this->setAdapter($adapter);
60: }
61:
62: /**
63: * Setter for a Horde_Db_Adapter instance.
64: *
65: * @param Horde_Db_Adapter $adapter A Horde_Db_Adapter instance.
66: */
67: public function setAdapter(Horde_Db_Adapter $adapter)
68: {
69: $this->_adapter = $adapter;
70: $this->_adapterMethods = array_flip(get_class_methods($adapter));
71: }
72:
73:
74: /*##########################################################################
75: # Object factories
76: ##########################################################################*/
77:
78: /**
79: * Factory for Column objects.
80: *
81: * @param string $name The column's name, such as "supplier_id" in
82: * "supplier_id int(11)".
83: * @param string $default The type-casted default value, such as "new" in
84: * "sales_stage varchar(20) default 'new'".
85: * @param string $sqlType Used to extract the column's type, length and
86: * signed status, if necessary. For example
87: * "varchar" and "60" in "company_name varchar(60)"
88: * or "unsigned => true" in "int(10) UNSIGNED".
89: * @param boolean $null Whether this column allows NULL values.
90: *
91: * @return Horde_Db_Adapter_Base_Column A column object.
92: */
93: public function makeColumn($name, $default, $sqlType = null, $null = true)
94: {
95: return new Horde_Db_Adapter_Base_Column($name, $default, $sqlType, $null);
96: }
97:
98: /**
99: * Factory for ColumnDefinition objects.
100: *
101: * @return Horde_Db_Adapter_Base_ColumnDefinition A column definition
102: * object.
103: */
104: public function makeColumnDefinition(
105: $base, $name, $type, $limit = null, $precision = null, $scale = null,
106: $unsigned = null, $default = null, $null = null, $autoincrement = null)
107: {
108: return new Horde_Db_Adapter_Base_ColumnDefinition(
109: $base, $name, $type, $limit, $precision, $scale, $unsigned,
110: $default, $null, $autoincrement);
111: }
112:
113: /**
114: * Factory for Index objects.
115: *
116: * @param string $table The table the index is on.
117: * @param string $name The index's name.
118: * @param boolean $primary Is this a primary key?
119: * @param boolean $unique Is this a unique index?
120: * @param array $columns The columns this index covers.
121: *
122: * @return Horde_Db_Adapter_Base_Index An index object.
123: */
124: public function makeIndex($table, $name, $primary, $unique, $columns)
125: {
126: return new Horde_Db_Adapter_Base_Index($table, $name, $primary, $unique, $columns);
127: }
128:
129: /**
130: * Factory for Table objects.
131: *
132: * @return Horde_Db_Adapter_Base_Table A table object.
133: */
134: public function makeTable($name, $primaryKey, $columns, $indexes)
135: {
136: return new Horde_Db_Adapter_Base_Table($name, $primaryKey, $columns, $indexes);
137: }
138:
139: /**
140: * Factory for TableDefinition objects.
141: *
142: * @return Horde_Db_Adapter_Base_TableDefinition A table definition object.
143: */
144: public function makeTableDefinition($name, $base, $options = array())
145: {
146: return new Horde_Db_Adapter_Base_TableDefinition($name, $base, $options);
147: }
148:
149:
150: /*##########################################################################
151: # Object composition
152: ##########################################################################*/
153:
154: /**
155: * Delegates calls to the adapter object.
156: *
157: * @param string $method A method name.
158: * @param array $args Method parameters.
159: *
160: * @return mixed The method call result.
161: * @throws BadMethodCallException if method doesn't exist in the adapter.
162: */
163: public function __call($method, $args)
164: {
165: if (isset($this->_adapterMethods[$method])) {
166: return call_user_func_array(array($this->_adapter, $method), $args);
167: }
168:
169: throw new BadMethodCallException('Call to undeclared method "' . $method . '"');
170: }
171:
172: /**
173: * Delegates access to $_cache and $_logger to the adapter object.
174: *
175: * @param string $key Property name. Only '_cache' and '_logger' are
176: * supported.
177: *
178: * @return object The request property object.
179: */
180: public function __get($key)
181: {
182: if ($key == '_cache' || $key == '_logger') {
183: $getter = 'get' . ucfirst(substr($key, 1));
184: return $this->_adapter->$getter();
185: }
186: }
187:
188:
189: /*##########################################################################
190: # Quoting
191: ##########################################################################*/
192:
193: /**
194: * Quotes the column value to help prevent SQL injection attacks.
195: *
196: * This method makes educated guesses on the scalar type based on the
197: * passed value. Make sure to correctly cast the value and/or pass the
198: * $column parameter to get the best results.
199: *
200: * @param mixed $value The scalar value to quote, a Horde_Db_Value,
201: * Horde_Date, or DateTime instance, or an object
202: * implementing quotedId().
203: * @param object $column An object implementing getType().
204: *
205: * @return string The correctly quoted value.
206: */
207: public function quote($value, $column = null)
208: {
209: if (is_object($value) && is_callable(array($value, 'quotedId'))) {
210: return $value->quotedId();
211: }
212:
213: if ($value instanceof Horde_Db_Value) {
214: return $value->quote($this->_adapter);
215: }
216:
217: $type = isset($column) ? $column->getType() : null;
218:
219: if (is_null($value)) {
220: return 'NULL';
221: } elseif ($value === true) {
222: return $type == 'integer' ? '1' : $this->quoteTrue();
223: } elseif ($value === false) {
224: return $type == 'integer' ? '0' : $this->quoteFalse();
225: } elseif (is_float($value)) {
226: return sprintf('%F', $value);
227: } elseif (is_int($value)) {
228: return $value;
229: } elseif ($value instanceof DateTime || $value instanceof Horde_Date) {
230: return $this->_adapter->quoteString($type == 'integer'
231: ? $value->format('U')
232: : $value->format('Y-m-d H:i:s'));
233: } elseif ($type == 'integer') {
234: return (int)$value;
235: } elseif ($type == 'float') {
236: return sprintf('%F', $value);
237: } else {
238: return $this->_adapter->quoteString($value);
239: }
240: }
241:
242: /**
243: * Quotes a string, escaping any ' (single quote) and \ (backslash)
244: * characters.
245: *
246: * @param string $string A string to escape.
247: *
248: * @return string The escaped and quoted string.
249: */
250: public function quoteString($string)
251: {
252: return "'" . str_replace(array('\\', '\''), array('\\\\', '\\\''), $string) . "'";
253: }
254:
255: /**
256: * Returns a quoted form of the column name.
257: *
258: * @param string $name A column name.
259: *
260: * @return string The quoted column name.
261: */
262: abstract public function quoteColumnName($name);
263:
264: /**
265: * Returns a quoted form of the table name.
266: *
267: * Defaults to column name quoting.
268: *
269: * @param string $name A table name.
270: *
271: * @return string The quoted table name.
272: */
273: public function quoteTableName($name)
274: {
275: return $this->quoteColumnName($name);
276: }
277:
278: /**
279: * Returns a quoted boolean true.
280: *
281: * @return string The quoted boolean true.
282: */
283: public function quoteTrue()
284: {
285: return "'t'";
286: }
287:
288: /**
289: * Returns a quoted boolean false.
290: *
291: * @return string The quoted boolean false.
292: */
293: public function quoteFalse()
294: {
295: return "'f'";
296: }
297:
298: /**
299: * Returns a quoted date value.
300: *
301: * @param mixed A date value that can be casted to string.
302: *
303: * @return string The quoted date value.
304: */
305: public function quoteDate($value)
306: {
307: return $this->quoteString((string)$value);
308: }
309:
310: /**
311: * Returns a quoted binary value.
312: *
313: * @param mixed A binary value.
314: *
315: * @return string The quoted binary value.
316: */
317: public function quoteBinary($value)
318: {
319: return $this->quoteString($value);
320: }
321:
322: /*##########################################################################
323: # Schema Statements
324: ##########################################################################*/
325:
326: /**
327: * Returns a hash of mappings from the abstract data types to the native
328: * database types.
329: *
330: * See TableDefinition::column() for details on the recognized abstract
331: * data types.
332: *
333: * @see TableDefinition::column()
334: *
335: * @return array A database type map.
336: */
337: public function nativeDatabaseTypes()
338: {
339: return array();
340: }
341:
342: /**
343: * Returns the maximum length a table alias can have.
344: *
345: * @return integer The maximum table alias length.
346: */
347: public function tableAliasLength()
348: {
349: return 255;
350: }
351:
352: /**
353: * Converts a table name into a suitable table alias.
354: *
355: * @param string $tableName A table name.
356: *
357: * @return string A possible alias name for the table.
358: */
359: public function tableAliasFor($tableName)
360: {
361: $alias = substr($tableName, 0, $this->tableAliasLength());
362: return str_replace('.', '_', $alias);
363: }
364:
365: /**
366: * Returns a list of all tables of the current database.
367: *
368: * @return array A table list.
369: */
370: abstract public function tables();
371:
372: /**
373: * Returns a Horde_Db_Adapter_Base_Table object for a table.
374: *
375: * @param string $tableName A table name.
376: * @param string $name (can be removed?)
377: *
378: * @return Horde_Db_Adapter_Base_Table A table object.
379: */
380: public function table($tableName, $name = null)
381: {
382: return $this->makeTable(
383: $tableName,
384: $this->primaryKey($tableName),
385: $this->columns($tableName, $name),
386: $this->indexes($tableName, $name)
387: );
388: }
389:
390: /**
391: * Returns a table's primary key.
392: *
393: * @param string $tableName A table name.
394: * @param string $name (can be removed?)
395: *
396: * @return Horde_Db_Adapter_Base_Index The primary key index object.
397: */
398: abstract public function primaryKey($tableName, $name = null);
399:
400: /**
401: * Returns a list of tables indexes.
402: *
403: * @param string $tableName A table name.
404: * @param string $name (can be removed?)
405: *
406: * @return array A list of Horde_Db_Adapter_Base_Index objects.
407: */
408: abstract public function indexes($tableName, $name = null);
409:
410: /**
411: * Returns a list of table columns.
412: *
413: * @param string $tableName A table name.
414: * @param string $name (can be removed?)
415: *
416: * @return array A list of Horde_Db_Adapter_Base_Column objects.
417: */
418: abstract public function columns($tableName, $name = null);
419:
420: /**
421: * Creates a new table.
422: *
423: * The $options hash can include the following keys:
424: * - autoincrementKey (string|array):
425: * The name of the autoincrementing primary key, if one is to be added
426: * automatically. Defaults to "id".
427: * - options (array):
428: * Any extra options you want appended to the table definition.
429: * - temporary (boolean):
430: * Make a temporary table.
431: * - force (boolean):
432: * Set to true or false to drop the table before creating it.
433: * Defaults to false.
434: *
435: * Examples:
436: * <code>
437: * // Add a backend specific option to the generated SQL (MySQL)
438: * $schema->createTable('suppliers', array('options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8')));
439: * </code>
440: * generates:
441: * <pre>
442: * CREATE TABLE suppliers (
443: * id int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
444: * ) ENGINE=InnoDB DEFAULT CHARSET=utf8
445: * </pre>
446: *
447: * <code>
448: * // Rename the primary key column
449: * $table = $schema->createTable('objects', array('autoincrementKey' => 'guid'));
450: * $table->column('name', 'string', array('limit' => 80));
451: * $table->end();
452: * </code>
453: * generates:
454: * <pre>
455: * CREATE TABLE objects (
456: * guid int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
457: * name varchar(80)
458: * )
459: * </pre>
460: *
461: * <code>
462: * // Do not add a primary key column, use fluent interface, use type
463: * // method.
464: * $schema->createTable('categories_suppliers', array('autoincrementKey' => false))
465: * ->column('category_id', 'integer')
466: * ->integer('supplier_id')
467: * ->end();
468: * </code>
469: * generates:
470: * <pre>
471: * CREATE TABLE categories_suppliers (
472: * category_id int(11),
473: * supplier_id int(11)
474: * )
475: * </pre>
476: *
477: * See also Horde_Db_Adapter_Base_TableDefinition::column() for details on
478: * how to create columns.
479: *
480: * @param string $name A table name.
481: * @param array $options A list of table options, see the method
482: * description.
483: *
484: * @return Horde_Db_Adapter_Base_TableDefinition The definition of the
485: * created table.
486: */
487: public function createTable($name, $options = array())
488: {
489: $tableDefinition = $this->makeTableDefinition($name, $this, $options);
490:
491: if (isset($options['autoincrementKey'])) {
492: if ($options['autoincrementKey'] === true ||
493: $options['autoincrementKey'] === 'true' ||
494: $options['autoincrementKey'] === 't' ||
495: $options['autoincrementKey'] === 1 ||
496: $options['autoincrementKey'] === '1') {
497: $pk = 'id';
498: } elseif ($options['autoincrementKey'] === false ||
499: $options['autoincrementKey'] === 'false' ||
500: $options['autoincrementKey'] === 'f' ||
501: $options['autoincrementKey'] === 0 ||
502: $options['autoincrementKey'] === '0') {
503: $pk = false;
504: } else {
505: $pk = $options['autoincrementKey'];
506: }
507: } else {
508: $pk = 'id';
509: }
510:
511: if ($pk != false) {
512: $tableDefinition->primaryKey($pk);
513: }
514:
515: return $tableDefinition;
516: }
517:
518: /**
519: * Finishes and executes table creation.
520: *
521: * @param string|Horde_Db_Adapter_Base_TableDefinition $name
522: * A table name or object.
523: * @param array $options
524: * A list of options. See createTable().
525: */
526: public function endTable($name, $options = array())
527: {
528: if ($name instanceof Horde_Db_Adapter_Base_TableDefinition) {
529: $tableDefinition = $name;
530: $options = array_merge($tableDefinition->getOptions(), $options);
531: } else {
532: $tableDefinition = $this->createTable($name, $options);
533: }
534:
535: // Drop previous table.
536: if (isset($options['force'])) {
537: $this->dropTable($tableDefinition->getName(), $options);
538: }
539:
540: $temp = !empty($options['temporary']) ? 'TEMPORARY' : null;
541: $opts = !empty($options['options']) ? $options['options'] : null;
542: $sql = sprintf("CREATE %s TABLE %s (\n%s\n) %s",
543: $temp,
544: $this->quoteTableName($tableDefinition->getName()),
545: $tableDefinition->toSql(),
546: $opts);
547:
548: return $this->execute($sql);
549: }
550:
551: /**
552: * Renames a table.
553: *
554: * @param string $name A table name.
555: * @param string $newName The new table name.
556: */
557: abstract public function renameTable($name, $newName);
558:
559: /**
560: * Drops a table from the database.
561: *
562: * @param string $name A table name.
563: */
564: public function dropTable($name)
565: {
566: $this->_clearTableCache($name);
567: return $this->execute('DROP TABLE ' . $this->quoteTableName($name));
568: }
569:
570: /**
571: * Adds a new column to a table.
572: *
573: * @param string $tableName A table name.
574: * @param string $columnName A column name.
575: * @param string $type A data type.
576: * @param array $options Column options. See
577: * Horde_Db_Adapter_Base_TableDefinition#column()
578: * for details.
579: */
580: public function addColumn($tableName, $columnName, $type,
581: $options = array())
582: {
583: $this->_clearTableCache($tableName);
584:
585: $options = array_merge(
586: array('limit' => null,
587: 'precision' => null,
588: 'scale' => null,
589: 'unsigned' => null),
590: $options);
591:
592: $sql = sprintf('ALTER TABLE %s ADD %s %s',
593: $this->quoteTableName($tableName),
594: $this->quoteColumnName($columnName),
595: $this->typeToSql($type,
596: $options['limit'],
597: $options['precision'],
598: $options['scale'],
599: $options['unsigned']));
600: $sql = $this->addColumnOptions($sql, $options);
601:
602: return $this->execute($sql);
603: }
604:
605: /**
606: * Removes a column from a table.
607: *
608: * @param string $tableName A table name.
609: * @param string $columnName A column name.
610: */
611: public function removeColumn($tableName, $columnName)
612: {
613: $this->_clearTableCache($tableName);
614: $sql = sprintf('ALTER TABLE %s DROP %s',
615: $this->quoteTableName($tableName),
616: $this->quoteColumnName($columnName));
617: return $this->execute($sql);
618: }
619:
620: /**
621: * Changes an existing column's definition.
622: *
623: * @param string $tableName A table name.
624: * @param string $columnName A column name.
625: * @param string $type A data type.
626: * @param array $options Column options. See
627: * Horde_Db_Adapter_Base_TableDefinition#column()
628: * for details.
629: */
630: abstract public function changeColumn($tableName, $columnName, $type, $options = array());
631:
632: /**
633: * Sets a new default value for a column.
634: *
635: * If you want to set the default value to NULL, you are out of luck. You
636: * need to execute the apppropriate SQL statement yourself.
637: *
638: * @param string $tableName A table name.
639: * @param string $columnName A column name.
640: * @param mixed $default The new default value.
641: */
642: abstract public function changeColumnDefault($tableName, $columnName, $default);
643:
644: /**
645: * Renames a column.
646: *
647: * @param string $tableName A table name.
648: * @param string $columnName A column name.
649: * @param string $newColumnName The new column name.
650: */
651: abstract public function renameColumn($tableName, $columnName, $newColumnName);
652:
653: /**
654: * Adds a primary key to a table.
655: *
656: * @since Horde_Db 1.1.0
657: *
658: * @param string $tableName A table name.
659: * @param string|array $columnName One or more column names.
660: *
661: * @throws Horde_Db_Exception
662: */
663: public function addPrimaryKey($tableName, $columns)
664: {
665: $this->_clearTableCache($tableName);
666: $columns = (array)$columns;
667: $sql = sprintf('ALTER TABLE %s ADD PRIMARY KEY (%s)',
668: $this->quoteTableName($tableName),
669: implode(', ', $columns));
670: return $this->execute($sql);
671: }
672:
673: /**
674: * Removes a primary key from a table.
675: *
676: * @since Horde_Db 1.1.0
677: *
678: * @param string $tableName A table name.
679: *
680: * @throws Horde_Db_Exception
681: */
682: abstract public function removePrimaryKey($tableName);
683:
684: /**
685: * Adds a new index to a table.
686: *
687: * The index will be named after the table and the first column names,
688: * unless you pass 'name' as an option.
689: *
690: * When creating an index on multiple columns, the first column is used as
691: * a name for the index. For example, when you specify an index on two
692: * columns 'first' and 'last', the DBMS creates an index for both columns
693: * as well as an index for the first colum 'first'. Using just the first
694: * name for this index makes sense, because you will never have to create a
695: * singular index with this name.
696: *
697: * Examples:
698: *
699: * Creating a simple index
700: * <code>
701: * $schema->addIndex('suppliers', 'name');
702: * </code>
703: * generates
704: * <code>
705: * CREATE INDEX suppliers_name_index ON suppliers(name)
706: * </code>
707: *
708: * Creating a unique index
709: * <code>
710: * $schema->addIndex('accounts',
711: * array('branch_id', 'party_id'),
712: * array('unique' => true));
713: * </code>
714: * generates
715: * <code>
716: * CREATE UNIQUE INDEX accounts_branch_id_index ON accounts(branch_id, party_id)
717: * </code>
718: *
719: * Creating a named index
720: * <code>
721: * $schema->addIndex('accounts',
722: * array('branch_id', 'party_id'),
723: * array('unique' => true, 'name' => 'by_branch_party'));
724: * </code>
725: * generates
726: * <code>
727: * CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id)
728: * </code>
729: *
730: * @param string $tableName A table name.
731: * @param string|array $columnName One or more column names.
732: * @param array $options Index options:
733: * - name: (string) the index name.
734: * - unique: (boolean) create a unique
735: * index?
736: */
737: public function addIndex($tableName, $columnName, $options = array())
738: {
739: $this->_clearTableCache($tableName);
740:
741: $columnNames = (array)$columnName;
742: $indexName = empty($options['name'])
743: ? $this->indexName($tableName, array('column' => $columnNames))
744: : $options['name'];
745: foreach ($columnNames as &$colName) {
746: $colName = $this->quoteColumnName($colName);
747: }
748:
749: $sql = sprintf('CREATE %s INDEX %s ON %s (%s)',
750: empty($options['unique']) ? null : 'UNIQUE',
751: $this->quoteColumnName($indexName),
752: $this->quoteTableName($tableName),
753: implode(', ', $columnNames));
754:
755: return $this->execute($sql);
756: }
757:
758: /**
759: * Removes an index from a table.
760: *
761: * Examples:
762: *
763: * Remove the suppliers_name_index in the suppliers table:
764: * <code>
765: * $schema->removeIndex('suppliers', 'name');
766: * </code>
767: *
768: * Remove the index named accounts_branch_id in the accounts table:
769: * <code>
770: * $schema->removeIndex('accounts', array('column' => 'branch_id'));
771: * </code>
772: *
773: * Remove the index named by_branch_party in the accounts table:
774: * <code>
775: * $schema->removeIndex('accounts', array('name' => 'by_branch_party'));
776: * </code>
777: *
778: * You can remove an index on multiple columns by specifying the first
779: * column:
780: * <code>
781: * $schema->addIndex('accounts', array('username', 'password'))
782: * $schema->removeIndex('accounts', 'username');
783: * </code>
784: *
785: * @param string $tableName A table name.
786: * @param string|array $options Either a column name or index options:
787: * - name: (string) the index name.
788: * - column: (string|array) column name(s).
789: */
790: public function removeIndex($tableName, $options = array())
791: {
792: $this->_clearTableCache($tableName);
793:
794: $index = $this->indexName($tableName, $options);
795: $sql = sprintf('DROP INDEX %s ON %s',
796: $this->quoteColumnName($index),
797: $this->quoteTableName($tableName));
798:
799: return $this->execute($sql);
800: }
801:
802: /**
803: * Builds the name for an index.
804: *
805: * @param string $tableName A table name.
806: * @param string|array $options Either a column name or index options:
807: * - column: (string|array) column name(s).
808: * - name: (string) the index name to fall
809: * back to if no column names specified.
810: */
811: public function indexName($tableName, $options = array())
812: {
813: if (!is_array($options)) {
814: $options = array('column' => $options);
815: }
816: if (isset($options['column'])) {
817: $columns = (array)$options['column'];
818: return "index_{$tableName}_on_" . implode('_and_', $columns);
819: }
820: if (isset($options['name'])) {
821: return $options['name'];
822: }
823: throw new Horde_Db_Exception('You must specify the index name');
824: }
825:
826: /**
827: * Recreates, i.e. drops then creates a database.
828: *
829: * @param string $name A database name.
830: */
831: public function recreateDatabase($name)
832: {
833: $this->dropDatabase($name);
834: return $this->createDatabase($name);
835: }
836:
837: /**
838: * Creates a database.
839: *
840: * @param string $name A database name.
841: * @param array $options Database options.
842: */
843: abstract public function createDatabase($name, $options = array());
844:
845: /**
846: * Drops a database.
847: *
848: * @param string $name A database name.
849: */
850: abstract public function dropDatabase($name);
851:
852: /**
853: * Returns the name of the currently selected database.
854: *
855: * @return string The database name.
856: */
857: abstract public function currentDatabase();
858:
859: /**
860: * Generates the SQL definition for a column type.
861: *
862: * @param string $type A column type.
863: * @param integer $limit Maximum column length (non decimal type only)
864: * @param integer $precision The number precision (decimal type only).
865: * @param integer $scale The number scaling (decimal columns only).
866: * @param boolean $unsigned Whether the column is an unsigned number
867: * (non decimal columns only).
868: *
869: * @return string The SQL definition. If $type is not one of the
870: * internally supported types, $type is returned unchanged.
871: */
872: public function typeToSql($type, $limit = null, $precision = null,
873: $scale = null, $unsigned = null)
874: {
875: $natives = $this->nativeDatabaseTypes();
876: $native = isset($natives[$type]) ? $natives[$type] : null;
877: if (empty($native)) {
878: return $type;
879: }
880:
881: $sql = is_array($native) ? $native['name'] : $native;
882: if ($type == 'decimal') {
883: $nativePrec = isset($native['precision']) ? $native['precision'] : null;
884: $nativeScale = isset($native['scale']) ? $native['scale'] : null;
885:
886: $precision = !empty($precision) ? $precision : $nativePrec;
887: $scale = !empty($scale) ? $scale : $nativeScale;
888: if ($precision) {
889: $sql .= $scale ? "($precision, $scale)" : "($precision)";
890: }
891: } else {
892: $nativeLimit = is_array($native) ? $native['limit'] : null;
893:
894: // If there is no explicit limit, adjust $nativeLimit for unsigned
895: // integers.
896: if (!empty($unsigned) && empty($limit) && is_integer($nativeLimit)) {
897: $nativeLimit--;
898: }
899:
900: if ($limit = !empty($limit) ? $limit : $nativeLimit) {
901: $sql .= "($limit)";
902: }
903: }
904:
905: return $sql;
906: }
907:
908: /**
909: * Adds default/null options to column SQL definitions.
910: *
911: * @param string $sql Existing SQL definition for a column.
912: * @param array $options Column options:
913: * - null: (boolean) Whether to allow NULL values.
914: * - default: (mixed) Default column value.
915: * - autoincrement: (boolean) Whether the column is
916: * an autoincrement column. Driver depedendent.
917: *
918: * @return string The manipulated SQL definition.
919: */
920: public function addColumnOptions($sql, $options)
921: {
922: /* 'autoincrement' is not handled here - it varies too much between
923: * DBs. Do autoincrement-specific handling in the driver. */
924:
925: if (isset($options['null']) && $options['null'] === false) {
926: $sql .= ' NOT NULL';
927: }
928:
929: if (isset($options['default'])) {
930: $default = $options['default'];
931: $column = isset($options['column']) ? $options['column'] : null;
932: $sql .= ' DEFAULT ' . $this->quote($default, $column);
933: }
934:
935: return $sql;
936: }
937:
938: /**
939: * Generates a DISTINCT clause for SELECT queries.
940: *
941: * <code>
942: * $connection->distinct('posts.id', 'posts.created_at DESC')
943: * </code>
944: *
945: * @param string $columns A column list.
946: * @param string $orderBy An ORDER clause.
947: *
948: * @return string The generated DISTINCT clause.
949: */
950: public function distinct($columns, $orderBy = null)
951: {
952: return 'DISTINCT ' . $columns;
953: }
954:
955: /**
956: * Adds an ORDER BY clause to an existing query.
957: *
958: * @param string $sql An SQL query to manipulate.
959: * @param array $options Options:
960: * - order: Order column an direction.
961: *
962: * @return string The manipulated SQL query.
963: */
964: public function addOrderByForAssocLimiting($sql, $options)
965: {
966: return $sql . 'ORDER BY ' . $options['order'];
967: }
968:
969: /**
970: * Generates an INTERVAL clause for SELECT queries.
971: *
972: * @deprecated since version 1.2.0. This function does not work with SQLite
973: * as a backend so you should avoid using it. Use "modifyDate()" instead.
974: *
975: * @param string $interval The interval.
976: * @param string $precision The precision.
977: *
978: * @return string The generated INTERVAL clause.
979: */
980: public function interval($interval, $precision)
981: {
982: return 'INTERVAL ' . $precision . ' ' . $interval;
983: }
984:
985: /**
986: * Generates a modified date for SELECT queries.
987: *
988: * @since Horde_Db 1.2.0
989: *
990: * @param string $reference The reference date - this is a column
991: * referenced in the SELECT.
992: * @param string $operator Add or subtract time? (+/-)
993: * @param integer $amount The shift amount (number of days if $interval
994: * is DAY, etc).
995: * @param string $interval The interval (SECOND, MINUTE, HOUR, DAY,
996: * MONTH, YEAR).
997: *
998: * @return string The generated INTERVAL clause.
999: */
1000: public function modifyDate($reference, $operator, $amount, $interval)
1001: {
1002: if (!is_int($amount)) {
1003: throw new InvalidArgumentException('$amount parameter must be an integer');
1004: }
1005: return sprintf('%s %s INTERVAL \'%s\' %s',
1006: $reference,
1007: $operator,
1008: $amount,
1009: $interval);
1010: }
1011:
1012: /**
1013: * Returns an expression using the specified operator.
1014: *
1015: * @param string $lhs The column or expression to test.
1016: * @param string $op The operator.
1017: * @param string $rhs The comparison value.
1018: * @param boolean $bind If true, the method returns the query and a list
1019: * of values suitable for binding as an array.
1020: * @param array $params Any additional parameters for the operator.
1021: *
1022: * @return string|array The SQL test fragment, or an array containing the
1023: * query and a list of values if $bind is true.
1024: */
1025: public function buildClause($lhs, $op, $rhs, $bind = false,
1026: $params = array())
1027: {
1028: $lhs = $this->_escapePrepare($lhs);
1029: switch ($op) {
1030: case '|':
1031: case '&':
1032: if ($bind) {
1033: return array($lhs . ' ' . $op . ' ?',
1034: array((int)$rhs));
1035: }
1036: return $lhs . ' ' . $op . ' ' . (int)$rhs;
1037:
1038: case '~':
1039: if ($bind) {
1040: return array($lhs . ' ' . $op . ' ?', array($rhs));
1041: }
1042: return $lhs . ' ' . $op . ' ' . $rhs;
1043:
1044: case 'IN':
1045: if ($bind) {
1046: if (is_array($rhs)) {
1047: return array($lhs . ' IN (?' . str_repeat(', ?', count($rhs) - 1) . ')', $rhs);
1048: }
1049: /* We need to bind each member of the IN clause separately to
1050: * ensure proper quoting. */
1051: if (substr($rhs, 0, 1) == '(') {
1052: $rhs = substr($rhs, 1);
1053: }
1054: if (substr($rhs, -1) == ')') {
1055: $rhs = substr($rhs, 0, -1);
1056: }
1057:
1058: $ids = preg_split('/\s*,\s*/', $rhs);
1059:
1060: return array($lhs . ' IN (?' . str_repeat(', ?', count($ids) - 1) . ')', $ids);
1061: }
1062: if (is_array($rhs)) {
1063: return $lhs . ' IN ' . implode(', ', $rhs);
1064: }
1065: return $lhs . ' IN ' . $rhs;
1066:
1067: case 'LIKE':
1068: $query = 'LOWER(%s) LIKE LOWER(%s)';
1069: if ($bind) {
1070: if (empty($params['begin'])) {
1071: return array(sprintf($query, $lhs, '?'),
1072: array('%' . $rhs . '%'));
1073: }
1074: return array(sprintf('(' . $query . ' OR ' . $query . ')',
1075: $lhs, '?', $lhs, '?'),
1076: array($rhs . '%', '% ' . $rhs . '%'));
1077: }
1078: if (empty($params['begin'])) {
1079: return sprintf($query,
1080: $lhs,
1081: $this->_escapePrepare($this->quote('%' . $rhs . '%')));
1082: }
1083: return sprintf('(' . $query . ' OR ' . $query . ')',
1084: $lhs,
1085: $this->_escapePrepare($this->quote($rhs . '%')),
1086: $lhs,
1087: $this->_escapePrepare($this->quote('% ' . $rhs . '%')));
1088:
1089: default:
1090: if ($bind) {
1091: return array($lhs . ' ' . $this->_escapePrepare($op) . ' ?', array($rhs));
1092: }
1093: return $lhs . ' ' . $this->_escapePrepare($op . ' ' . $this->quote($rhs));
1094: }
1095: }
1096:
1097: /**
1098: * Escapes all characters in a string that are placeholders for
1099: * prepare/execute methods.
1100: *
1101: * @param string $query A string to escape.
1102: *
1103: * @return string The correctly escaped string.
1104: */
1105: protected function _escapePrepare($query)
1106: {
1107: return preg_replace('/[?!&]/', '\\\\$0', $query);
1108: }
1109:
1110:
1111: /*##########################################################################
1112: # Protected
1113: ##########################################################################*/
1114:
1115: /**
1116: * Clears the cache for tables when altering them.
1117: *
1118: * @param string $tableName A table name.
1119: */
1120: protected function _clearTableCache($tableName)
1121: {
1122: $this->_cache->set('tables/columns/' . $tableName, '');
1123: $this->_cache->set('tables/indexes/' . $tableName, '');
1124: }
1125: }
1126: