2 // +----------------------------------------------------------------------+
3 // | PHP versions 4 and 5 |
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) 1998-2007 Manuel Lemos, Tomas V.V.Cox, |
6 // | Stig. S. Bakken, Lukas Smith |
7 // | All rights reserved. |
8 // +----------------------------------------------------------------------+
9 // | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
10 // | API as well as database abstraction for PHP applications. |
11 // | This LICENSE is in the BSD license style. |
13 // | Redistribution and use in source and binary forms, with or without |
14 // | modification, are permitted provided that the following conditions |
17 // | Redistributions of source code must retain the above copyright |
18 // | notice, this list of conditions and the following disclaimer. |
20 // | Redistributions in binary form must reproduce the above copyright |
21 // | notice, this list of conditions and the following disclaimer in the |
22 // | documentation and/or other materials provided with the distribution. |
24 // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
25 // | Lukas Smith nor the names of his contributors may be used to endorse |
26 // | or promote products derived from this software without specific prior|
27 // | written permission. |
29 // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
30 // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
31 // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
32 // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
33 // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
34 // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
35 // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
36 // | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
37 // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
38 // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
39 // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
40 // | POSSIBILITY OF SUCH DAMAGE. |
41 // +----------------------------------------------------------------------+
42 // | Author: Lukas Smith <smith@pooteeweet.org> |
43 // +----------------------------------------------------------------------+
45 // $Id: mysql.php 327310 2012-08-27 15:16:18Z danielc $
48 require_once 'MDB2/Driver/Reverse/Common.php';
51 * MDB2 MySQL driver for the schema reverse engineering module
55 * @author Lukas Smith <smith@pooteeweet.org>
56 * @author Lorenzo Alberton <l.alberton@quipo.it>
58 class MDB2_Driver_Reverse_mysql extends MDB2_Driver_Reverse_Common
60 // {{{ getTableFieldDefinition()
63 * Get the structure of a field into an array
65 * @param string $table_name name of table that should be used in method
66 * @param string $field_name name of field that should be used in method
67 * @return mixed data array on success, a MDB2 error on failure
70 function getTableFieldDefinition($table_name, $field_name)
72 $db = $this->getDBInstance();
73 if (MDB2::isError($db)) {
77 $result = $db->loadModule('Datatype', null, true);
78 if (MDB2::isError($result)) {
82 list($schema, $table) = $this->splitTableSchema($table_name);
84 $table = $db->quoteIdentifier($table, true);
85 $query = "SHOW FULL COLUMNS FROM $table LIKE ".$db->quote($field_name);
86 $columns = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);
87 if (MDB2::isError($columns)) {
90 foreach ($columns as $column) {
91 $column = array_change_key_case($column, CASE_LOWER);
92 $column['name'] = $column['field'];
93 unset($column['field']);
94 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
95 if ($db->options['field_case'] == CASE_LOWER) {
96 $column['name'] = strtolower($column['name']);
98 $column['name'] = strtoupper($column['name']);
101 $column = array_change_key_case($column, $db->options['field_case']);
103 if ($field_name == $column['name']) {
104 $mapped_datatype = $db->datatype->mapNativeDatatype($column);
105 if (MDB2::isError($mapped_datatype)) {
106 return $mapped_datatype;
108 list($types, $length, $unsigned, $fixed) = $mapped_datatype;
110 if (empty($column['null']) || $column['null'] !== 'YES') {
114 if (array_key_exists('default', $column)) {
115 $default = $column['default'];
116 if ((null === $default) && $notnull) {
120 $definition[0] = array(
121 'notnull' => $notnull,
122 'nativetype' => preg_replace('/^([a-z]+)[^a-z].*/i', '\\1', $column['type'])
124 $autoincrement = false;
125 if (!empty($column['extra'])) {
126 if ($column['extra'] == 'auto_increment') {
127 $autoincrement = true;
129 $definition[0]['extra'] = $column['extra'];
133 if (!empty($column['collation'])) {
134 $collate = $column['collation'];
135 $charset = preg_replace('/(.+?)(_.+)?/', '$1', $collate);
138 if (null !== $length) {
139 $definition[0]['length'] = $length;
141 if (null !== $unsigned) {
142 $definition[0]['unsigned'] = $unsigned;
144 if (null !== $fixed) {
145 $definition[0]['fixed'] = $fixed;
147 if ($default !== false) {
148 $definition[0]['default'] = $default;
150 if ($autoincrement !== false) {
151 $definition[0]['autoincrement'] = $autoincrement;
153 if (null !== $collate) {
154 $definition[0]['collate'] = $collate;
155 $definition[0]['charset'] = $charset;
157 foreach ($types as $key => $type) {
158 $definition[$key] = $definition[0];
159 if ($type == 'clob' || $type == 'blob') {
160 unset($definition[$key]['default']);
161 } elseif ($type == 'timestamp' && $notnull && empty($definition[$key]['default'])) {
162 $definition[$key]['default'] = '0000-00-00 00:00:00';
164 $definition[$key]['type'] = $type;
165 $definition[$key]['mdb2type'] = $type;
171 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
172 'it was not specified an existing table column', __FUNCTION__);
176 // {{{ getTableIndexDefinition()
179 * Get the structure of an index into an array
181 * @param string $table_name name of table that should be used in method
182 * @param string $index_name name of index that should be used in method
183 * @return mixed data array on success, a MDB2 error on failure
186 function getTableIndexDefinition($table_name, $index_name)
188 $db = $this->getDBInstance();
189 if (MDB2::isError($db)) {
193 list($schema, $table) = $this->splitTableSchema($table_name);
195 $table = $db->quoteIdentifier($table, true);
196 $query = "SHOW INDEX FROM $table /*!50002 WHERE Key_name = %s */";
197 $index_name_mdb2 = $db->getIndexName($index_name);
198 $result = $db->queryRow(sprintf($query, $db->quote($index_name_mdb2)));
199 if (!MDB2::isError($result) && (null !== $result)) {
200 // apply 'idxname_format' only if the query succeeded, otherwise
201 // fallback to the given $index_name, without transformation
202 $index_name = $index_name_mdb2;
204 $result = $db->query(sprintf($query, $db->quote($index_name)));
205 if (MDB2::isError($result)) {
209 $definition = array();
210 while (is_array($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))) {
211 $row = array_change_key_case($row, CASE_LOWER);
212 $key_name = $row['key_name'];
213 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
214 if ($db->options['field_case'] == CASE_LOWER) {
215 $key_name = strtolower($key_name);
217 $key_name = strtoupper($key_name);
220 if ($index_name == $key_name) {
221 if (!$row['non_unique']) {
222 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
223 $index_name . ' is not an existing table index', __FUNCTION__);
225 $column_name = $row['column_name'];
226 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
227 if ($db->options['field_case'] == CASE_LOWER) {
228 $column_name = strtolower($column_name);
230 $column_name = strtoupper($column_name);
233 $definition['fields'][$column_name] = array(
234 'position' => $colpos++
236 if (!empty($row['collation'])) {
237 $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
238 ? 'ascending' : 'descending');
243 if (empty($definition['fields'])) {
244 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
245 $index_name . ' is not an existing table index', __FUNCTION__);
251 // {{{ getTableConstraintDefinition()
254 * Get the structure of a constraint into an array
256 * @param string $table_name name of table that should be used in method
257 * @param string $constraint_name name of constraint that should be used in method
258 * @return mixed data array on success, a MDB2 error on failure
261 function getTableConstraintDefinition($table_name, $constraint_name)
263 $db = $this->getDBInstance();
264 if (MDB2::isError($db)) {
268 list($schema, $table) = $this->splitTableSchema($table_name);
269 $constraint_name_original = $constraint_name;
271 $table = $db->quoteIdentifier($table, true);
272 $query = "SHOW INDEX FROM $table /*!50002 WHERE Key_name = %s */";
273 if (strtolower($constraint_name) != 'primary') {
274 $constraint_name_mdb2 = $db->getIndexName($constraint_name);
275 $result = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2)));
276 if (!MDB2::isError($result) && (null !== $result)) {
277 // apply 'idxname_format' only if the query succeeded, otherwise
278 // fallback to the given $index_name, without transformation
279 $constraint_name = $constraint_name_mdb2;
282 $result = $db->query(sprintf($query, $db->quote($constraint_name)));
283 if (MDB2::isError($result)) {
287 //default values, eventually overridden
294 'references' => array(
301 'deferrable' => false,
302 'initiallydeferred' => false,
304 while (is_array($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))) {
305 $row = array_change_key_case($row, CASE_LOWER);
306 $key_name = $row['key_name'];
307 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
308 if ($db->options['field_case'] == CASE_LOWER) {
309 $key_name = strtolower($key_name);
311 $key_name = strtoupper($key_name);
314 if ($constraint_name == $key_name) {
315 if ($row['non_unique']) {
317 return $this->_getTableFKConstraintDefinition($table, $constraint_name_original, $definition);
319 if ($row['key_name'] == 'PRIMARY') {
320 $definition['primary'] = true;
321 } elseif (!$row['non_unique']) {
322 $definition['unique'] = true;
324 $column_name = $row['column_name'];
325 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
326 if ($db->options['field_case'] == CASE_LOWER) {
327 $column_name = strtolower($column_name);
329 $column_name = strtoupper($column_name);
332 $definition['fields'][$column_name] = array(
333 'position' => $colpos++
335 if (!empty($row['collation'])) {
336 $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
337 ? 'ascending' : 'descending');
342 if (empty($definition['fields'])) {
343 return $this->_getTableFKConstraintDefinition($table, $constraint_name_original, $definition);
349 // {{{ _getTableFKConstraintDefinition()
352 * Get the FK definition from the CREATE TABLE statement
354 * @param string $table table name
355 * @param string $constraint_name constraint name
356 * @param array $definition default values for constraint definition
358 * @return array|PEAR_Error
361 function _getTableFKConstraintDefinition($table, $constraint_name, $definition)
363 $db = $this->getDBInstance();
364 if (MDB2::isError($db)) {
367 //Use INFORMATION_SCHEMA instead?
369 // FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
370 // WHERE CONSTRAINT_SCHEMA = '$dbname'
371 // AND TABLE_NAME = '$table'
372 // AND CONSTRAINT_NAME = '$constraint_name';
373 $query = 'SHOW CREATE TABLE '. $db->escape($table);
374 $constraint = $db->queryOne($query, 'text', 1);
375 if (!MDB2::isError($constraint) && !empty($constraint)) {
376 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
377 if ($db->options['field_case'] == CASE_LOWER) {
378 $constraint = strtolower($constraint);
380 $constraint = strtoupper($constraint);
383 $constraint_name_original = $constraint_name;
384 $constraint_name = $db->getIndexName($constraint_name);
385 $pattern = '/\bCONSTRAINT\s+'.$constraint_name.'\s+FOREIGN KEY\s+\(([^\)]+)\) \bREFERENCES\b ([^\s]+) \(([^\)]+)\)(?: ON DELETE ([^\s]+))?(?: ON UPDATE ([^\s]+))?/i';
386 if (!preg_match($pattern, str_replace('`', '', $constraint), $matches)) {
387 //fallback to original constraint name
388 $pattern = '/\bCONSTRAINT\s+'.$constraint_name_original.'\s+FOREIGN KEY\s+\(([^\)]+)\) \bREFERENCES\b ([^\s]+) \(([^\)]+)\)(?: ON DELETE ([^\s]+))?(?: ON UPDATE ([^\s]+))?/i';
390 if (preg_match($pattern, str_replace('`', '', $constraint), $matches)) {
391 $definition['foreign'] = true;
392 $column_names = explode(',', $matches[1]);
393 $referenced_cols = explode(',', $matches[3]);
394 $definition['references'] = array(
395 'table' => $matches[2],
399 foreach ($column_names as $column_name) {
400 $definition['fields'][trim($column_name)] = array(
401 'position' => $colpos++
405 foreach ($referenced_cols as $column_name) {
406 $definition['references']['fields'][trim($column_name)] = array(
407 'position' => $colpos++
410 $definition['ondelete'] = empty($matches[4]) ? 'RESTRICT' : strtoupper($matches[4]);
411 $definition['onupdate'] = empty($matches[5]) ? 'RESTRICT' : strtoupper($matches[5]);
412 $definition['match'] = 'SIMPLE';
416 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
417 $constraint_name . ' is not an existing table constraint', __FUNCTION__);
421 // {{{ getTriggerDefinition()
424 * Get the structure of a trigger into an array
428 * WARNING: this function is experimental and may change the returned value
429 * at any time until labelled as non-experimental
431 * @param string $trigger name of trigger that should be used in method
432 * @return mixed data array on success, a MDB2 error on failure
435 function getTriggerDefinition($trigger)
437 $db = $this->getDBInstance();
438 if (MDB2::isError($db)) {
442 $query = 'SELECT trigger_name,
443 event_object_table AS table_name,
444 action_statement AS trigger_body,
445 action_timing AS trigger_type,
446 event_manipulation AS trigger_event
447 FROM information_schema.triggers
448 WHERE trigger_name = '. $db->quote($trigger, 'text');
450 'trigger_name' => 'text',
451 'table_name' => 'text',
452 'trigger_body' => 'text',
453 'trigger_type' => 'text',
454 'trigger_event' => 'text',
456 $def = $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
457 if (MDB2::isError($def)) {
460 $def['trigger_comment'] = '';
461 $def['trigger_enabled'] = true;
469 * Returns information about a table or a result set
471 * @param object|string $result MDB2_result object from a query or a
472 * string containing the name of a table.
473 * While this also accepts a query result
474 * resource identifier, this behavior is
476 * @param int $mode a valid tableInfo mode
478 * @return array an associative array with the information requested.
479 * A MDB2_Error object on failure.
481 * @see MDB2_Driver_Common::setOption()
483 function tableInfo($result, $mode = null)
485 if (is_string($result)) {
486 return parent::tableInfo($result, $mode);
489 $db = $this->getDBInstance();
490 if (MDB2::isError($db)) {
494 $resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
495 if (!is_resource($resource)) {
496 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
497 'Could not generate result resource', __FUNCTION__);
500 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
501 if ($db->options['field_case'] == CASE_LOWER) {
502 $case_func = 'strtolower';
504 $case_func = 'strtoupper';
507 $case_func = 'strval';
510 $count = @mysql_num_fields($resource);
513 $res['num_fields'] = $count;
516 $db->loadModule('Datatype', null, true);
517 for ($i = 0; $i < $count; $i++) {
519 'table' => $case_func(@mysql_field_table($resource, $i)),
520 'name' => $case_func(@mysql_field_name($resource, $i)),
521 'type' => @mysql_field_type($resource, $i),
522 'length' => @mysql_field_len($resource, $i),
523 'flags' => @mysql_field_flags($resource, $i),
525 if ($res[$i]['type'] == 'string') {
526 $res[$i]['type'] = 'char';
527 } elseif ($res[$i]['type'] == 'unknown') {
528 $res[$i]['type'] = 'decimal';
530 $mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
531 if (MDB2::isError($mdb2type_info)) {
532 return $mdb2type_info;
534 $res[$i]['mdb2type'] = $mdb2type_info[0][0];
535 if ($mode & MDB2_TABLEINFO_ORDER) {
536 $res['order'][$res[$i]['name']] = $i;
538 if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
539 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;