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: mysqli.php 327310 2012-08-27 15:16:18Z danielc $
48 require_once 'MDB2/Driver/Reverse/Common.php';
51 * MDB2 MySQLi 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_mysqli extends MDB2_Driver_Reverse_Common
61 * Array for converting MYSQLI_*_FLAG constants to text values
66 MYSQLI_NOT_NULL_FLAG => 'not_null',
67 MYSQLI_PRI_KEY_FLAG => 'primary_key',
68 MYSQLI_UNIQUE_KEY_FLAG => 'unique_key',
69 MYSQLI_MULTIPLE_KEY_FLAG => 'multiple_key',
70 MYSQLI_BLOB_FLAG => 'blob',
71 MYSQLI_UNSIGNED_FLAG => 'unsigned',
72 MYSQLI_ZEROFILL_FLAG => 'zerofill',
73 MYSQLI_AUTO_INCREMENT_FLAG => 'auto_increment',
74 MYSQLI_TIMESTAMP_FLAG => 'timestamp',
75 MYSQLI_SET_FLAG => 'set',
76 // MYSQLI_NUM_FLAG => 'numeric', // unnecessary
77 // MYSQLI_PART_KEY_FLAG => 'multiple_key', // duplicatvie
78 MYSQLI_GROUP_FLAG => 'group_by'
82 * Array for converting MYSQLI_TYPE_* constants to text values
87 MYSQLI_TYPE_DECIMAL => 'decimal',
89 MYSQLI_TYPE_TINY => 'tinyint',
90 MYSQLI_TYPE_SHORT => 'int',
91 MYSQLI_TYPE_LONG => 'int',
92 MYSQLI_TYPE_FLOAT => 'float',
93 MYSQLI_TYPE_DOUBLE => 'double',
94 // MYSQLI_TYPE_NULL => 'DEFAULT NULL', // let flags handle it
95 MYSQLI_TYPE_TIMESTAMP => 'timestamp',
96 MYSQLI_TYPE_LONGLONG => 'bigint',
97 MYSQLI_TYPE_INT24 => 'mediumint',
98 MYSQLI_TYPE_DATE => 'date',
99 MYSQLI_TYPE_TIME => 'time',
100 MYSQLI_TYPE_DATETIME => 'datetime',
101 MYSQLI_TYPE_YEAR => 'year',
102 MYSQLI_TYPE_NEWDATE => 'date',
103 MYSQLI_TYPE_ENUM => 'enum',
104 MYSQLI_TYPE_SET => 'set',
105 MYSQLI_TYPE_TINY_BLOB => 'tinyblob',
106 MYSQLI_TYPE_MEDIUM_BLOB => 'mediumblob',
107 MYSQLI_TYPE_LONG_BLOB => 'longblob',
108 MYSQLI_TYPE_BLOB => 'blob',
109 MYSQLI_TYPE_VAR_STRING => 'varchar',
110 MYSQLI_TYPE_STRING => 'char',
111 MYSQLI_TYPE_GEOMETRY => 'geometry',
114 // {{{ getTableFieldDefinition()
117 * Get the structure of a field into an array
119 * @param string $table_name name of table that should be used in method
120 * @param string $field_name name of field that should be used in method
121 * @return mixed data array on success, a MDB2 error on failure
124 function getTableFieldDefinition($table_name, $field_name)
126 $db = $this->getDBInstance();
127 if (MDB2::isError($db)) {
131 $result = $db->loadModule('Datatype', null, true);
132 if (MDB2::isError($result)) {
136 list($schema, $table) = $this->splitTableSchema($table_name);
138 $table = $db->quoteIdentifier($table, true);
139 $query = "SHOW FULL COLUMNS FROM $table LIKE ".$db->quote($field_name);
140 $columns = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);
141 if (MDB2::isError($columns)) {
144 foreach ($columns as $column) {
145 $column = array_change_key_case($column, CASE_LOWER);
146 $column['name'] = $column['field'];
147 unset($column['field']);
148 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
149 if ($db->options['field_case'] == CASE_LOWER) {
150 $column['name'] = strtolower($column['name']);
152 $column['name'] = strtoupper($column['name']);
155 $column = array_change_key_case($column, $db->options['field_case']);
157 if ($field_name == $column['name']) {
158 $mapped_datatype = $db->datatype->mapNativeDatatype($column);
159 if (MDB2::isError($mapped_datatype)) {
160 return $mapped_datatype;
162 list($types, $length, $unsigned, $fixed) = $mapped_datatype;
164 if (empty($column['null']) || $column['null'] !== 'YES') {
168 if (array_key_exists('default', $column)) {
169 $default = $column['default'];
170 if ((null === $default) && $notnull) {
174 $definition[0] = array(
175 'notnull' => $notnull,
176 'nativetype' => preg_replace('/^([a-z]+)[^a-z].*/i', '\\1', $column['type'])
178 $autoincrement = false;
179 if (!empty($column['extra'])) {
180 if ($column['extra'] == 'auto_increment') {
181 $autoincrement = true;
183 $definition[0]['extra'] = $column['extra'];
187 if (!empty($column['collation'])) {
188 $collate = $column['collation'];
189 $charset = preg_replace('/(.+?)(_.+)?/', '$1', $collate);
192 if (null !== $length) {
193 $definition[0]['length'] = $length;
195 if (null !== $unsigned) {
196 $definition[0]['unsigned'] = $unsigned;
198 if (null !== $fixed) {
199 $definition[0]['fixed'] = $fixed;
201 if ($default !== false) {
202 $definition[0]['default'] = $default;
204 if ($autoincrement !== false) {
205 $definition[0]['autoincrement'] = $autoincrement;
207 if (null !== $collate) {
208 $definition[0]['collate'] = $collate;
209 $definition[0]['charset'] = $charset;
211 foreach ($types as $key => $type) {
212 $definition[$key] = $definition[0];
213 if ($type == 'clob' || $type == 'blob') {
214 unset($definition[$key]['default']);
215 } elseif ($type == 'timestamp' && $notnull && empty($definition[$key]['default'])) {
216 $definition[$key]['default'] = '0000-00-00 00:00:00';
218 $definition[$key]['type'] = $type;
219 $definition[$key]['mdb2type'] = $type;
225 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
226 'it was not specified an existing table column', __FUNCTION__);
230 // {{{ getTableIndexDefinition()
233 * Get the structure of an index into an array
235 * @param string $table_name name of table that should be used in method
236 * @param string $index_name name of index that should be used in method
237 * @return mixed data array on success, a MDB2 error on failure
240 function getTableIndexDefinition($table_name, $index_name)
242 $db = $this->getDBInstance();
243 if (MDB2::isError($db)) {
247 list($schema, $table) = $this->splitTableSchema($table_name);
249 $table = $db->quoteIdentifier($table, true);
250 $query = "SHOW INDEX FROM $table /*!50002 WHERE Key_name = %s */";
251 $index_name_mdb2 = $db->getIndexName($index_name);
252 $result = $db->queryRow(sprintf($query, $db->quote($index_name_mdb2)));
253 if (!MDB2::isError($result) && (null !== $result)) {
254 // apply 'idxname_format' only if the query succeeded, otherwise
255 // fallback to the given $index_name, without transformation
256 $index_name = $index_name_mdb2;
258 $result = $db->query(sprintf($query, $db->quote($index_name)));
259 if (MDB2::isError($result)) {
263 $definition = array();
264 while (is_array($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))) {
265 $row = array_change_key_case($row, CASE_LOWER);
266 $key_name = $row['key_name'];
267 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
268 if ($db->options['field_case'] == CASE_LOWER) {
269 $key_name = strtolower($key_name);
271 $key_name = strtoupper($key_name);
274 if ($index_name == $key_name) {
275 if (!$row['non_unique']) {
276 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
277 $index_name . ' is not an existing table index', __FUNCTION__);
279 $column_name = $row['column_name'];
280 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
281 if ($db->options['field_case'] == CASE_LOWER) {
282 $column_name = strtolower($column_name);
284 $column_name = strtoupper($column_name);
287 $definition['fields'][$column_name] = array(
288 'position' => $colpos++
290 if (!empty($row['collation'])) {
291 $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
292 ? 'ascending' : 'descending');
297 if (empty($definition['fields'])) {
298 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
299 $index_name . ' is not an existing table index', __FUNCTION__);
305 // {{{ getTableConstraintDefinition()
308 * Get the structure of a constraint into an array
310 * @param string $table_name name of table that should be used in method
311 * @param string $constraint_name name of constraint that should be used in method
312 * @return mixed data array on success, a MDB2 error on failure
315 function getTableConstraintDefinition($table_name, $constraint_name)
317 $db = $this->getDBInstance();
318 if (MDB2::isError($db)) {
322 list($schema, $table) = $this->splitTableSchema($table_name);
323 $constraint_name_original = $constraint_name;
325 $table = $db->quoteIdentifier($table, true);
326 $query = "SHOW INDEX FROM $table /*!50002 WHERE Key_name = %s */";
327 if (strtolower($constraint_name) != 'primary') {
328 $constraint_name_mdb2 = $db->getIndexName($constraint_name);
329 $result = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2)));
330 if (!MDB2::isError($result) && (null !== $result)) {
331 // apply 'idxname_format' only if the query succeeded, otherwise
332 // fallback to the given $index_name, without transformation
333 $constraint_name = $constraint_name_mdb2;
336 $result = $db->query(sprintf($query, $db->quote($constraint_name)));
337 if (MDB2::isError($result)) {
341 //default values, eventually overridden
348 'references' => array(
355 'deferrable' => false,
356 'initiallydeferred' => false,
358 while (is_array($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))) {
359 $row = array_change_key_case($row, CASE_LOWER);
360 $key_name = $row['key_name'];
361 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
362 if ($db->options['field_case'] == CASE_LOWER) {
363 $key_name = strtolower($key_name);
365 $key_name = strtoupper($key_name);
368 if ($constraint_name == $key_name) {
369 if ($row['non_unique']) {
371 return $this->_getTableFKConstraintDefinition($table, $constraint_name_original, $definition);
373 if ($row['key_name'] == 'PRIMARY') {
374 $definition['primary'] = true;
375 } elseif (!$row['non_unique']) {
376 $definition['unique'] = true;
378 $column_name = $row['column_name'];
379 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
380 if ($db->options['field_case'] == CASE_LOWER) {
381 $column_name = strtolower($column_name);
383 $column_name = strtoupper($column_name);
386 $definition['fields'][$column_name] = array(
387 'position' => $colpos++
389 if (!empty($row['collation'])) {
390 $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
391 ? 'ascending' : 'descending');
396 if (empty($definition['fields'])) {
397 return $this->_getTableFKConstraintDefinition($table, $constraint_name_original, $definition);
403 // {{{ _getTableFKConstraintDefinition()
406 * Get the FK definition from the CREATE TABLE statement
408 * @param string $table table name
409 * @param string $constraint_name constraint name
410 * @param array $definition default values for constraint definition
412 * @return array|PEAR_Error
415 function _getTableFKConstraintDefinition($table, $constraint_name, $definition)
417 $db = $this->getDBInstance();
418 if (MDB2::isError($db)) {
421 //Use INFORMATION_SCHEMA instead?
423 // FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
424 // WHERE CONSTRAINT_SCHEMA = '$dbname'
425 // AND TABLE_NAME = '$table'
426 // AND CONSTRAINT_NAME = '$constraint_name';
427 $query = 'SHOW CREATE TABLE '. $db->escape($table);
428 $constraint = $db->queryOne($query, 'text', 1);
429 if (!MDB2::isError($constraint) && !empty($constraint)) {
430 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
431 if ($db->options['field_case'] == CASE_LOWER) {
432 $constraint = strtolower($constraint);
434 $constraint = strtoupper($constraint);
437 $constraint_name_original = $constraint_name;
438 $constraint_name = $db->getIndexName($constraint_name);
439 $pattern = '/\bCONSTRAINT\s+'.$constraint_name.'\s+FOREIGN KEY\s+\(([^\)]+)\) \bREFERENCES\b ([^\s]+) \(([^\)]+)\)(?: ON DELETE ([^\s]+))?(?: ON UPDATE ([^\s]+))?/i';
440 if (!preg_match($pattern, str_replace('`', '', $constraint), $matches)) {
441 //fallback to original constraint name
442 $pattern = '/\bCONSTRAINT\s+'.$constraint_name_original.'\s+FOREIGN KEY\s+\(([^\)]+)\) \bREFERENCES\b ([^\s]+) \(([^\)]+)\)(?: ON DELETE ([^\s]+))?(?: ON UPDATE ([^\s]+))?/i';
444 if (preg_match($pattern, str_replace('`', '', $constraint), $matches)) {
445 $definition['foreign'] = true;
446 $column_names = explode(',', $matches[1]);
447 $referenced_cols = explode(',', $matches[3]);
448 $definition['references'] = array(
449 'table' => $matches[2],
453 foreach ($column_names as $column_name) {
454 $definition['fields'][trim($column_name)] = array(
455 'position' => $colpos++
459 foreach ($referenced_cols as $column_name) {
460 $definition['references']['fields'][trim($column_name)] = array(
461 'position' => $colpos++
464 $definition['ondelete'] = empty($matches[4]) ? 'RESTRICT' : strtoupper($matches[4]);
465 $definition['onupdate'] = empty($matches[5]) ? 'RESTRICT' : strtoupper($matches[5]);
466 $definition['match'] = 'SIMPLE';
470 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
471 $constraint_name . ' is not an existing table constraint', __FUNCTION__);
475 // {{{ getTriggerDefinition()
478 * Get the structure of a trigger into an array
482 * WARNING: this function is experimental and may change the returned value
483 * at any time until labelled as non-experimental
485 * @param string $trigger name of trigger that should be used in method
486 * @return mixed data array on success, a MDB2 error on failure
489 function getTriggerDefinition($trigger)
491 $db = $this->getDBInstance();
492 if (MDB2::isError($db)) {
496 $query = 'SELECT trigger_name,
497 event_object_table AS table_name,
498 action_statement AS trigger_body,
499 action_timing AS trigger_type,
500 event_manipulation AS trigger_event
501 FROM information_schema.triggers
502 WHERE trigger_name = '. $db->quote($trigger, 'text');
504 'trigger_name' => 'text',
505 'table_name' => 'text',
506 'trigger_body' => 'text',
507 'trigger_type' => 'text',
508 'trigger_event' => 'text',
510 $def = $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
511 if (MDB2::isError($def)) {
514 $def['trigger_comment'] = '';
515 $def['trigger_enabled'] = true;
523 * Returns information about a table or a result set
525 * @param object|string $result MDB2_result object from a query or a
526 * string containing the name of a table.
527 * While this also accepts a query result
528 * resource identifier, this behavior is
530 * @param int $mode a valid tableInfo mode
532 * @return array an associative array with the information requested.
533 * A MDB2_Error object on failure.
535 * @see MDB2_Driver_Common::setOption()
537 function tableInfo($result, $mode = null)
539 if (is_string($result)) {
540 return parent::tableInfo($result, $mode);
543 $db = $this->getDBInstance();
544 if (MDB2::isError($db)) {
548 $resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
549 if (!is_object($resource)) {
550 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
551 'Could not generate result resource', __FUNCTION__);
554 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
555 if ($db->options['field_case'] == CASE_LOWER) {
556 $case_func = 'strtolower';
558 $case_func = 'strtoupper';
561 $case_func = 'strval';
564 $count = @mysqli_num_fields($resource);
567 $res['num_fields'] = $count;
570 $db->loadModule('Datatype', null, true);
571 for ($i = 0; $i < $count; $i++) {
572 $tmp = @mysqli_fetch_field($resource);
575 foreach ($this->flags as $const => $means) {
576 if ($tmp->flags & $const) {
577 $flags.= $means . ' ';
581 $flags.= 'default_' . rawurlencode($tmp->def);
583 $flags = trim($flags);
586 'table' => $case_func($tmp->table),
587 'name' => $case_func($tmp->name),
588 'type' => isset($this->types[$tmp->type])
589 ? $this->types[$tmp->type] : 'unknown',
590 // http://bugs.php.net/?id=36579
591 'length' => $tmp->length,
594 $mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
595 if (MDB2::isError($mdb2type_info)) {
596 return $mdb2type_info;
598 $res[$i]['mdb2type'] = $mdb2type_info[0][0];
599 if ($mode & MDB2_TABLEINFO_ORDER) {
600 $res['order'][$res[$i]['name']] = $i;
602 if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
603 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;