1: <?php
2: /**
3: * Class for MySQL-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_Mysql_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_Mysql_Column A column object.
49: */
50: public function makeColumn($name, $default, $sqlType = null, $null = true)
51: {
52: return new Horde_Db_Adapter_Mysql_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 form of the table name.
74: *
75: * Defaults to column name quoting.
76: *
77: * @param string $name A table name.
78: *
79: * @return string The quoted table name.
80: */
81: public function quoteTableName($name)
82: {
83: return str_replace('.', '`.`', $this->quoteColumnName($name));
84: }
85:
86: /**
87: * Returns a quoted boolean true.
88: *
89: * @return string The quoted boolean true.
90: */
91: public function quoteTrue()
92: {
93: return '1';
94: }
95:
96: /**
97: * Returns a quoted boolean false.
98: *
99: * @return string The quoted boolean false.
100: */
101: public function quoteFalse()
102: {
103: return '0';
104: }
105:
106:
107: /*##########################################################################
108: # Schema Statements
109: ##########################################################################*/
110:
111: /**
112: * Returns a hash of mappings from the abstract data types to the native
113: * database types.
114: *
115: * See TableDefinition::column() for details on the recognized abstract
116: * data types.
117: *
118: * @see TableDefinition::column()
119: *
120: * @return array A database type map.
121: */
122: public function nativeDatabaseTypes()
123: {
124: return array(
125: 'autoincrementKey' => 'int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
126: 'string' => array('name' => 'varchar', 'limit' => 255),
127: 'text' => array('name' => 'text', 'limit' => null),
128: 'mediumtext' => array('name' => 'mediumtext', 'limit' => null),
129: 'longtext' => array('name' => 'longtext', 'limit' => null),
130: 'integer' => array('name' => 'int', 'limit' => 11),
131: 'float' => array('name' => 'float', 'limit' => null),
132: 'decimal' => array('name' => 'decimal', 'limit' => null),
133: 'datetime' => array('name' => 'datetime', 'limit' => null),
134: 'timestamp' => array('name' => 'datetime', 'limit' => null),
135: 'time' => array('name' => 'time', 'limit' => null),
136: 'date' => array('name' => 'date', 'limit' => null),
137: 'binary' => array('name' => 'blob', 'limit' => null),
138: 'boolean' => array('name' => 'tinyint', 'limit' => 1),
139: );
140: }
141:
142: /**
143: * Returns a list of all tables of the current database.
144: *
145: * @return array A table list.
146: */
147: public function tables()
148: {
149: return $this->selectValues('SHOW TABLES');
150: }
151:
152: /**
153: * Returns a table's primary key.
154: *
155: * @param string $tableName A table name.
156: * @param string $name (can be removed?)
157: *
158: * @return Horde_Db_Adapter_Base_Index The primary key index object.
159: */
160: public function primaryKey($tableName, $name = null)
161: {
162: // Share the column cache with the columns() method
163: $rows = @unserialize($this->_cache->get("tables/columns/$tableName"));
164:
165: if (!$rows) {
166: $rows = $this->selectAll(
167: 'SHOW FIELDS FROM ' . $this->quoteTableName($tableName),
168: $name);
169:
170: $this->_cache->set("tables/columns/$tableName", serialize($rows));
171: }
172:
173: $pk = $this->makeIndex($tableName, 'PRIMARY', true, true, array());
174: foreach ($rows as $row) {
175: if ($row['Key'] == 'PRI') {
176: $pk->columns[] = $row['Field'];
177: }
178: }
179:
180: return $pk;
181: }
182:
183: /**
184: * Returns a list of tables indexes.
185: *
186: * @param string $tableName A table name.
187: * @param string $name (can be removed?)
188: *
189: * @return array A list of Horde_Db_Adapter_Base_Index objects.
190: */
191: public function indexes($tableName, $name=null)
192: {
193: $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName"));
194:
195: if (!$indexes) {
196: $indexes = array();
197: $currentIndex = null;
198: foreach ($this->select('SHOW KEYS FROM ' . $this->quoteTableName($tableName)) as $row) {
199: if ($currentIndex != $row['Key_name']) {
200: if ($row['Key_name'] == 'PRIMARY') {
201: continue;
202: }
203: $currentIndex = $row['Key_name'];
204: $indexes[] = $this->makeIndex(
205: $tableName, $row['Key_name'], false, $row['Non_unique'] == '0', array());
206: }
207: $indexes[count($indexes) - 1]->columns[] = $row['Column_name'];
208: }
209:
210: $this->_cache->set("tables/indexes/$tableName", serialize($indexes));
211: }
212:
213: return $indexes;
214: }
215:
216: /**
217: * Returns a list of table columns.
218: *
219: * @param string $tableName A table name.
220: * @param string $name (can be removed?)
221: *
222: * @return array A list of Horde_Db_Adapter_Base_Column objects.
223: */
224: public function columns($tableName, $name=null)
225: {
226: $rows = @unserialize($this->_cache->get("tables/columns/$tableName"));
227:
228: if (!$rows) {
229: $rows = $this->selectAll('SHOW FIELDS FROM ' . $this->quoteTableName($tableName), $name);
230:
231: $this->_cache->set("tables/columns/$tableName", serialize($rows));
232: }
233:
234: // Create columns from rows.
235: $columns = array();
236: foreach ($rows as $row) {
237: $columns[$row['Field']] = $this->makeColumn(
238: $row['Field'], $row['Default'], $row['Type'], $row['Null'] == 'YES');
239: }
240:
241: return $columns;
242: }
243:
244: /**
245: * Finishes and executes table creation.
246: *
247: * @param string|Horde_Db_Adapter_Base_TableDefinition $name
248: * A table name or object.
249: * @param array $options
250: * A list of options. See createTable().
251: */
252: public function endTable($name, $options = array())
253: {
254: if ($name instanceof Horde_Db_Adapter_Base_TableDefinition) {
255: $options = array_merge($name->getOptions(), $options);
256: }
257: if (isset($options['options'])) {
258: $opts = $options['options'];
259: } else {
260: if (empty($options['charset'])) {
261: $options['charset'] = $this->getCharset();
262: }
263: $opts = 'ENGINE=InnoDB DEFAULT CHARSET=' . $options['charset'];
264: }
265: return parent::endTable($name, array_merge(array('options' => $opts), $options));
266: }
267:
268: /**
269: * Renames a table.
270: *
271: * @param string $name A table name.
272: * @param string $newName The new table name.
273: */
274: public function renameTable($name, $newName)
275: {
276: $this->_clearTableCache($name);
277: $sql = sprintf('ALTER TABLE %s RENAME %s',
278: $this->quoteTableName($name),
279: $this->quoteTableName($newName));
280: return $this->execute($sql);
281: }
282:
283: /**
284: * Changes an existing column's definition.
285: *
286: * @param string $tableName A table name.
287: * @param string $columnName A column name.
288: * @param string $type A data type.
289: * @param array $options Column options. See
290: * Horde_Db_Adapter_Base_TableDefinition#column()
291: * for details.
292: */
293: public function changeColumn($tableName, $columnName, $type,
294: $options = array())
295: {
296: $this->_clearTableCache($tableName);
297:
298: $quotedTableName = $this->quoteTableName($tableName);
299: $quotedColumnName = $this->quoteColumnName($columnName);
300:
301: $options = array_merge(
302: array('limit' => null,
303: 'precision' => null,
304: 'scale' => null,
305: 'unsigned' => null),
306: $options);
307:
308: $sql = sprintf('SHOW COLUMNS FROM %s LIKE %s',
309: $quotedTableName,
310: $this->quoteString($columnName));
311: $row = $this->selectOne($sql);
312: if (!array_key_exists('default', $options)) {
313: $options['default'] = $row['Default'];
314: $options['column'] = $this->makeColumn($columnName,
315: $row['Default'],
316: $row['Type'],
317: $row['Null'] == 'YES');
318: }
319:
320: $typeSql = $this->typeToSql($type, $options['limit'],
321: $options['precision'], $options['scale'],
322: $options['unsigned']);
323: $dropPk = ($type == 'autoincrementKey' && $row['Key'] == 'PRI')
324: ? 'DROP PRIMARY KEY,'
325: : '';
326:
327: $sql = sprintf('ALTER TABLE %s %s CHANGE %s %s %s',
328: $quotedTableName,
329: $dropPk,
330: $quotedColumnName,
331: $quotedColumnName,
332: $typeSql);
333: if ($type != 'autoincrementKey') {
334: $sql = $this->addColumnOptions($sql, $options);
335: }
336:
337: $this->execute($sql);
338: }
339:
340: /**
341: * Sets a new default value for a column.
342: *
343: * If you want to set the default value to NULL, you are out of luck. You
344: * need to execute the apppropriate SQL statement yourself.
345: *
346: * @param string $tableName A table name.
347: * @param string $columnName A column name.
348: * @param mixed $default The new default value.
349: */
350: public function changeColumnDefault($tableName, $columnName, $default)
351: {
352: $this->_clearTableCache($tableName);
353:
354: $quotedTableName = $this->quoteTableName($tableName);
355: $quotedColumnName = $this->quoteColumnName($columnName);
356:
357: $sql = sprintf('SHOW COLUMNS FROM %s LIKE %s',
358: $quotedTableName,
359: $this->quoteString($columnName));
360: $res = $this->selectOne($sql);
361: $column = $this->makeColumn($columnName, $res['Default'], $res['Type'], $res['Null'] == 'YES');
362:
363: $default = $this->quote($default, $column);
364: $sql = sprintf('ALTER TABLE %s CHANGE %s %s %s DEFAULT %s',
365: $quotedTableName,
366: $quotedColumnName,
367: $quotedColumnName,
368: $res['Type'],
369: $default);
370: return $this->execute($sql);
371: }
372:
373: /**
374: * Renames a column.
375: *
376: * @param string $tableName A table name.
377: * @param string $columnName A column name.
378: * @param string $newColumnName The new column name.
379: */
380: public function renameColumn($tableName, $columnName, $newColumnName)
381: {
382: $this->_clearTableCache($tableName);
383:
384: $quotedTableName = $this->quoteTableName($tableName);
385: $quotedColumnName = $this->quoteColumnName($columnName);
386:
387: $sql = sprintf('SHOW COLUMNS FROM %s LIKE %s',
388: $quotedTableName,
389: $this->quoteString($columnName));
390: $res = $this->selectOne($sql);
391: $currentType = $res['Type'];
392:
393: $sql = sprintf('ALTER TABLE %s CHANGE %s %s %s',
394: $quotedTableName,
395: $quotedColumnName,
396: $this->quoteColumnName($newColumnName),
397: $currentType);
398:
399: return $this->execute($sql);
400: }
401:
402: /**
403: * Removes a primary key from a table.
404: *
405: * @since Horde_Db 1.1.0
406: *
407: * @param string $tableName A table name.
408: *
409: * @throws Horde_Db_Exception
410: */
411: public function removePrimaryKey($tableName)
412: {
413: $this->_clearTableCache($tableName);
414: $sql = sprintf('ALTER TABLE %s DROP PRIMARY KEY',
415: $this->quoteTableName($tableName));
416: return $this->execute($sql);
417: }
418:
419: /**
420: * Builds the name for an index.
421: *
422: * Cuts the index name to the maximum length of 64 characters limited by
423: * MySQL.
424: *
425: * @param string $tableName A table name.
426: * @param string|array $options Either a column name or index options:
427: * - column: (string|array) column name(s).
428: * - name: (string) the index name to fall
429: * back to if no column names specified.
430: */
431: public function indexName($tableName, $options=array())
432: {
433: $indexName = parent::indexName($tableName, $options);
434: if (strlen($indexName) > 64) {
435: $indexName = substr($indexName, 0, 64);
436: }
437: return $indexName;
438: }
439:
440: /**
441: * Creates a database.
442: *
443: * @param string $name A database name.
444: * @param array $options Database options.
445: */
446: public function createDatabase($name, $options = array())
447: {
448: return $this->execute("CREATE DATABASE `$name`");
449: }
450:
451: /**
452: * Drops a database.
453: *
454: * @param string $name A database name.
455: */
456: public function dropDatabase($name)
457: {
458: return $this->execute("DROP DATABASE IF EXISTS `$name`");
459: }
460:
461: /**
462: * Returns the name of the currently selected database.
463: *
464: * @return string The database name.
465: */
466: public function currentDatabase()
467: {
468: return $this->selectValue('SELECT DATABASE() AS db');
469: }
470:
471: /**
472: * Generates the SQL definition for a column type.
473: *
474: * @param string $type A column type.
475: * @param integer $limit Maximum column length (non decimal type only)
476: * @param integer $precision The number precision (decimal type only).
477: * @param integer $scale The number scaling (decimal columns only).
478: * @param boolean $unsigned Whether the column is an unsigned number
479: * (non decimal columns only).
480: *
481: * @return string The SQL definition. If $type is not one of the
482: * internally supported types, $type is returned unchanged.
483: */
484: public function typeToSql($type, $limit = null, $precision = null,
485: $scale = null, $unsigned = null)
486: {
487: // If there is no explicit limit, adjust $nativeLimit for unsigned
488: // integers.
489: if ($type == 'integer' && !empty($unsigned) && empty($limit)) {
490: $natives = $this->nativeDatabaseTypes();
491: $native = isset($natives[$type]) ? $natives[$type] : null;
492: if (empty($native)) {
493: return $type;
494: }
495:
496: $nativeLimit = is_array($native) ? $native['limit'] : null;
497: if (is_integer($nativeLimit)) {
498: $limit = $nativeLimit - 1;
499: }
500: }
501:
502: $sql = parent::typeToSql($type, $limit, $precision, $scale, $unsigned);
503:
504: if (!empty($unsigned)) {
505: $sql .= ' UNSIGNED';
506: }
507:
508: return $sql;
509: }
510:
511: /**
512: * Adds default/null options to column SQL definitions.
513: *
514: * @param string $sql Existing SQL definition for a column.
515: * @param array $options Column options:
516: * - null: (boolean) Whether to allow NULL values.
517: * - default: (mixed) Default column value.
518: * - autoincrement: (boolean) Whether the column is
519: * an autoincrement column. Driver depedendent.
520: * - after: (string) Insert column after this one.
521: * MySQL specific.
522: *
523: * @return string The manipulated SQL definition.
524: */
525: public function addColumnOptions($sql, $options)
526: {
527: $sql = parent::addColumnOptions($sql, $options);
528: if (isset($options['after'])) {
529: $sql .= ' AFTER ' . $this->quoteColumnName($options['after']);
530: }
531: if (!empty($options['autoincrement'])) {
532: $sql .= ' AUTO_INCREMENT';
533: }
534: return $sql;
535: }
536:
537: /**
538: * Returns an expression using the specified operator.
539: *
540: * @param string $lhs The column or expression to test.
541: * @param string $op The operator.
542: * @param string $rhs The comparison value.
543: * @param boolean $bind If true, the method returns the query and a list
544: * of values suitable for binding as an array.
545: * @param array $params Any additional parameters for the operator.
546: *
547: * @return string|array The SQL test fragment, or an array containing the
548: * query and a list of values if $bind is true.
549: */
550: public function buildClause($lhs, $op, $rhs, $bind = false,
551: $params = array())
552: {
553: switch ($op) {
554: case '~':
555: if ($bind) {
556: return array($lhs . ' REGEXP ?', array($rhs));
557: } else {
558: return $lhs . ' REGEXP ' . $rhs;
559: }
560: }
561: return parent::buildClause($lhs, $op, $rhs, $bind, $params);
562: }
563:
564:
565: /*##########################################################################
566: # MySQL specific methods
567: ##########################################################################*/
568:
569: /**
570: * Returns the character set of query results.
571: *
572: * @return string The result's charset.
573: */
574: public function getCharset()
575: {
576: return $this->showVariable('character_set_results');
577: }
578:
579: /**
580: * Sets the client and result charset.
581: *
582: * @param string $charset The character set to use for client queries and
583: * results.
584: */
585: public function setCharset($charset)
586: {
587: $charset = $this->_mysqlCharsetName($charset);
588: $this->execute('SET NAMES ' . $this->quoteString($charset));
589: }
590:
591: /**
592: * Returns the MySQL name of a character set.
593: *
594: * @param string $charset A charset name.
595: *
596: * @return string MySQL-normalized charset.
597: */
598: public function _mysqlCharsetName($charset)
599: {
600: $charset = preg_replace(array('/[^a-z0-9]/', '/iso8859(\d)/'),
601: array('', 'latin$1'),
602: Horde_String::lower($charset));
603: $validCharsets = $this->selectValues('SHOW CHARACTER SET');
604: if (!in_array($charset, $validCharsets)) {
605: throw new Horde_Db_Exception($charset . ' is not supported by MySQL (' . implode(', ', $validCharsets) . ')');
606: }
607:
608: return $charset;
609: }
610:
611: /**
612: * Returns the database collation strategy.
613: *
614: * @return string Database collation.
615: */
616: public function getCollation()
617: {
618: return $this->showVariable('collation_database');
619: }
620:
621: /**
622: * Returns a database variable.
623: *
624: * Convenience wrapper around "SHOW VARIABLES LIKE 'name'".
625: *
626: * @param string $name A variable name.
627: *
628: * @return string The variable value.
629: * @throws Horde_Db_Exception
630: */
631: public function showVariable($name)
632: {
633: $value = $this->selectOne('SHOW VARIABLES LIKE ' . $this->quoteString($name));
634: if ($value['Variable_name'] == $name) {
635: return $value['Value'];
636: } else {
637: throw new Horde_Db_Exception($name . ' is not a recognized variable');
638: }
639: }
640:
641: /**
642: */
643: public function caseSensitiveEqualityOperator()
644: {
645: return '= BINARY';
646: }
647:
648: /**
649: */
650: public function limitedUpdateConditions($whereSql, $quotedTableName,
651: $quotedPrimaryKey)
652: {
653: return $whereSql;
654: }
655: }
656: