2 // +----------------------------------------------------------------------+
3 // | PHP versions 4 and 5 |
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) 1998-2008 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 // | Authors: Lukas Smith <smith@pooteeweet.org> |
43 // | Lorenzo Alberton <l.alberton@quipo.it> |
44 // +----------------------------------------------------------------------+
46 // $Id: Common.php 327310 2012-08-27 15:16:18Z danielc $
52 * @author Lukas Smith <smith@pooteeweet.org>
53 * @author Lorenzo Alberton <l.alberton@quipo.it>
57 * Base class for the management modules that is extended by each MDB2 driver
59 * To load this module in the MDB2 object:
60 * $mdb->loadModule('Manager');
64 * @author Lukas Smith <smith@pooteeweet.org>
66 class MDB2_Driver_Manager_Common extends MDB2_Module_Common
68 // {{{ splitTableSchema()
71 * Split the "[owner|schema].table" notation into an array
73 * @param string $table [schema and] table name
75 * @return array array(schema, table)
78 function splitTableSchema($table)
81 if (strpos($table, '.') !== false) {
82 return explode('.', $table);
84 return array(null, $table);
88 // {{{ getFieldDeclarationList()
91 * Get declaration of a number of field in bulk
93 * @param array $fields a multidimensional associative array.
94 * The first dimension determines the field name, while the second
95 * dimension is keyed with the name of the properties
96 * of the field being declared as array indexes. Currently, the types
97 * of supported field properties are as follows:
100 * Boolean value to be used as default for this field.
103 * Boolean flag that indicates whether this field is constrained
104 * to not be set to null.
106 * @return mixed string on success, a MDB2 error on failure
109 function getFieldDeclarationList($fields)
111 $db = $this->getDBInstance();
112 if (MDB2::isError($db)) {
116 if (!is_array($fields) || empty($fields)) {
117 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
118 'missing any fields', __FUNCTION__);
120 foreach ($fields as $field_name => $field) {
121 $query = $db->getDeclaration($field['type'], $field_name, $field);
122 if (MDB2::isError($query)) {
125 $query_fields[] = $query;
127 return implode(', ', $query_fields);
131 // {{{ _fixSequenceName()
134 * Removes any formatting in an sequence name using the 'seqname_format' option
136 * @param string $sqn string that containts name of a potential sequence
137 * @param bool $check if only formatted sequences should be returned
138 * @return string name of the sequence with possible formatting removed
141 function _fixSequenceName($sqn, $check = false)
143 $db = $this->getDBInstance();
144 if (MDB2::isError($db)) {
148 $seq_pattern = '/^'.preg_replace('/%s/', '([a-z0-9_]+)', $db->options['seqname_format']).'$/i';
149 $seq_name = preg_replace($seq_pattern, '\\1', $sqn);
150 if ($seq_name && !strcasecmp($sqn, $db->getSequenceName($seq_name))) {
160 // {{{ _fixIndexName()
163 * Removes any formatting in an index name using the 'idxname_format' option
165 * @param string $idx string that containts name of anl index
166 * @return string name of the index with eventual formatting removed
169 function _fixIndexName($idx)
171 $db = $this->getDBInstance();
172 if (MDB2::isError($db)) {
176 $idx_pattern = '/^'.preg_replace('/%s/', '([a-z0-9_]+)', $db->options['idxname_format']).'$/i';
177 $idx_name = preg_replace($idx_pattern, '\\1', $idx);
178 if ($idx_name && !strcasecmp($idx, $db->getIndexName($idx_name))) {
185 // {{{ createDatabase()
188 * create a new database
190 * @param string $name name of the database that should be created
191 * @param array $options array with charset, collation info
193 * @return mixed MDB2_OK on success, a MDB2 error on failure
196 function createDatabase($database, $options = array())
198 $db = $this->getDBInstance();
199 if (MDB2::isError($db)) {
203 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
204 'method not implemented', __FUNCTION__);
208 // {{{ alterDatabase()
211 * alter an existing database
213 * @param string $name name of the database that should be created
214 * @param array $options array with charset, collation info
216 * @return mixed MDB2_OK on success, a MDB2 error on failure
219 function alterDatabase($database, $options = array())
221 $db = $this->getDBInstance();
222 if (MDB2::isError($db)) {
226 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
227 'method not implemented', __FUNCTION__);
231 // {{{ dropDatabase()
234 * drop an existing database
236 * @param string $name name of the database that should be dropped
237 * @return mixed MDB2_OK on success, a MDB2 error on failure
240 function dropDatabase($database)
242 $db = $this->getDBInstance();
243 if (MDB2::isError($db)) {
247 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
248 'method not implemented', __FUNCTION__);
252 // {{{ _getCreateTableQuery()
255 * Create a basic SQL query for a new table creation
257 * @param string $name Name of the database that should be created
258 * @param array $fields Associative array that contains the definition of each field of the new table
259 * @param array $options An associative array of table options
261 * @return mixed string (the SQL query) on success, a MDB2 error on failure
264 function _getCreateTableQuery($name, $fields, $options = array())
266 $db = $this->getDBInstance();
267 if (MDB2::isError($db)) {
272 return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null,
273 'no valid table name specified', __FUNCTION__);
275 if (empty($fields)) {
276 return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null,
277 'no fields specified for table "'.$name.'"', __FUNCTION__);
279 $query_fields = $this->getFieldDeclarationList($fields);
280 if (MDB2::isError($query_fields)) {
281 return $query_fields;
283 if (!empty($options['primary'])) {
284 $query_fields.= ', PRIMARY KEY ('.implode(', ', array_keys($options['primary'])).')';
287 $name = $db->quoteIdentifier($name, true);
289 if (!empty($options['temporary'])) {
290 $result .= $this->_getTemporaryTableQuery();
292 $result .= " TABLE $name ($query_fields)";
297 // {{{ _getTemporaryTableQuery()
300 * A method to return the required SQL string that fits between CREATE ... TABLE
301 * to create the table as a temporary table.
303 * Should be overridden in driver classes to return the correct string for the
304 * specific database type.
306 * The default is to return the string "TEMPORARY" - this will result in a
307 * SQL error for any database that does not support temporary tables, or that
308 * requires a different SQL command from "CREATE TEMPORARY TABLE".
310 * @return string The string required to be placed between "CREATE" and "TABLE"
311 * to generate a temporary table, if possible.
313 function _getTemporaryTableQuery()
324 * @param string $name Name of the database that should be created
325 * @param array $fields Associative array that contains the definition of each field of the new table
326 * The indexes of the array entries are the names of the fields of the table an
327 * the array entry values are associative arrays like those that are meant to be
328 * passed with the field definitions to get[Type]Declaration() functions.
331 * 'type' => 'integer',
340 * 'password' => array(
345 * @param array $options An associative array of table options:
347 * 'comment' => 'Foo',
348 * 'temporary' => true|false,
350 * @return mixed MDB2_OK on success, a MDB2 error on failure
353 function createTable($name, $fields, $options = array())
355 $query = $this->_getCreateTableQuery($name, $fields, $options);
356 if (MDB2::isError($query)) {
359 $db = $this->getDBInstance();
360 if (MDB2::isError($db)) {
363 $result = $db->exec($query);
364 if (MDB2::isError($result)) {
374 * drop an existing table
376 * @param string $name name of the table that should be dropped
377 * @return mixed MDB2_OK on success, a MDB2 error on failure
380 function dropTable($name)
382 $db = $this->getDBInstance();
383 if (MDB2::isError($db)) {
387 $name = $db->quoteIdentifier($name, true);
388 $result = $db->exec("DROP TABLE $name");
389 if (MDB2::isError($result)) {
396 // {{{ truncateTable()
399 * Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
400 * it falls back to a DELETE FROM TABLE query)
402 * @param string $name name of the table that should be truncated
403 * @return mixed MDB2_OK on success, a MDB2 error on failure
406 function truncateTable($name)
408 $db = $this->getDBInstance();
409 if (MDB2::isError($db)) {
413 $name = $db->quoteIdentifier($name, true);
414 $result = $db->exec("DELETE FROM $name");
415 if (MDB2::isError($result)) {
425 * Optimize (vacuum) all the tables in the db (or only the specified table)
426 * and optionally run ANALYZE.
428 * @param string $table table name (all the tables if empty)
429 * @param array $options an array with driver-specific options:
430 * - timeout [int] (in seconds) [mssql-only]
431 * - analyze [boolean] [pgsql and mysql]
432 * - full [boolean] [pgsql-only]
433 * - freeze [boolean] [pgsql-only]
435 * @return mixed MDB2_OK success, a MDB2 error on failure
438 function vacuum($table = null, $options = array())
440 $db = $this->getDBInstance();
441 if (MDB2::isError($db)) {
445 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
446 'method not implemented', __FUNCTION__);
453 * alter an existing table
455 * @param string $name name of the table that is intended to be changed.
456 * @param array $changes associative array that contains the details of each type
457 * of change that is intended to be performed. The types of
458 * changes that are currently supported are defined as follows:
462 * New name for the table.
466 * Associative array with the names of fields to be added as
467 * indexes of the array. The value of each entry of the array
468 * should be set to another associative array with the properties
469 * of the fields to be added. The properties of the fields should
470 * be the same as defined by the MDB2 parser.
475 * Associative array with the names of fields to be removed as indexes
476 * of the array. Currently the values assigned to each entry are ignored.
477 * An empty array should be used for future compatibility.
481 * Associative array with the names of fields to be renamed as indexes
482 * of the array. The value of each entry of the array should be set to
483 * another associative array with the entry named name with the new
484 * field name and the entry named Declaration that is expected to contain
485 * the portion of the field declaration already in DBMS specific SQL code
486 * as it is used in the CREATE TABLE statement.
490 * Associative array with the names of the fields to be changed as indexes
491 * of the array. Keep in mind that if it is intended to change either the
492 * name of a field and any other properties, the change array entries
493 * should have the new names of the fields as array indexes.
495 * The value of each entry of the array should be set to another associative
496 * array with the properties of the fields to that are meant to be changed as
497 * array entries. These entries should be assigned to the new values of the
498 * respective properties. The properties of the fields should be the same
499 * as defined by the MDB2 parser.
503 * 'name' => 'userlist',
506 * 'type' => 'integer',
511 * 'file_limit' => array(),
512 * 'time_limit' => array()
517 * 'definition' => array(
525 * 'name' => 'gender',
526 * 'definition' => array(
535 * @param boolean $check indicates whether the function should just check if the DBMS driver
536 * can perform the requested table alterations if the value is true or
537 * actually perform them otherwise.
540 * @return mixed MDB2_OK on success, a MDB2 error on failure
542 function alterTable($name, $changes, $check)
544 $db = $this->getDBInstance();
545 if (MDB2::isError($db)) {
549 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
550 'method not implemented', __FUNCTION__);
554 // {{{ listDatabases()
559 * @return mixed array of database names on success, a MDB2 error on failure
562 function listDatabases()
564 $db = $this->getDBInstance();
565 if (MDB2::isError($db)) {
569 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
570 'method not implementedd', __FUNCTION__);
579 * @return mixed array of user names on success, a MDB2 error on failure
584 $db = $this->getDBInstance();
585 if (MDB2::isError($db)) {
589 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
590 'method not implemented', __FUNCTION__);
597 * list all views in the current database
599 * @param string database, the current is default
600 * NB: not all the drivers can get the view names from
601 * a database other than the current one
602 * @return mixed array of view names on success, a MDB2 error on failure
605 function listViews($database = null)
607 $db = $this->getDBInstance();
608 if (MDB2::isError($db)) {
612 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
613 'method not implemented', __FUNCTION__);
617 // {{{ listTableViews()
620 * list the views in the database that reference a given table
622 * @param string table for which all referenced views should be found
623 * @return mixed array of view names on success, a MDB2 error on failure
626 function listTableViews($table)
628 $db = $this->getDBInstance();
629 if (MDB2::isError($db)) {
633 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
634 'method not implemented', __FUNCTION__);
638 // {{{ listTableTriggers()
641 * list all triggers in the database that reference a given table
643 * @param string table for which all referenced triggers should be found
644 * @return mixed array of trigger names on success, a MDB2 error on failure
647 function listTableTriggers($table = null)
649 $db = $this->getDBInstance();
650 if (MDB2::isError($db)) {
654 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
655 'method not implemented', __FUNCTION__);
659 // {{{ listFunctions()
662 * list all functions in the current database
664 * @return mixed array of function names on success, a MDB2 error on failure
667 function listFunctions()
669 $db = $this->getDBInstance();
670 if (MDB2::isError($db)) {
674 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
675 'method not implemented', __FUNCTION__);
682 * list all tables in the current database
684 * @param string database, the current is default.
685 * NB: not all the drivers can get the table names from
686 * a database other than the current one
687 * @return mixed array of table names on success, a MDB2 error on failure
690 function listTables($database = null)
692 $db = $this->getDBInstance();
693 if (MDB2::isError($db)) {
697 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
698 'method not implemented', __FUNCTION__);
702 // {{{ listTableFields()
705 * list all fields in a table in the current database
707 * @param string $table name of table that should be used in method
708 * @return mixed array of field names on success, a MDB2 error on failure
711 function listTableFields($table)
713 $db = $this->getDBInstance();
714 if (MDB2::isError($db)) {
718 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
719 'method not implemented', __FUNCTION__);
726 * Get the stucture of a field into an array
728 * @param string $table name of the table on which the index is to be created
729 * @param string $name name of the index to be created
730 * @param array $definition associative array that defines properties of the index to be created.
731 * Currently, only one property named FIELDS is supported. This property
732 * is also an associative with the names of the index fields as array
733 * indexes. Each entry of this array is set to another type of associative
734 * array that specifies properties of the index that are specific to
737 * Currently, only the sorting property is supported. It should be used
738 * to define the sorting direction of the index. It may be set to either
739 * ascending or descending.
741 * Not all DBMS support index sorting direction configuration. The DBMS
742 * drivers of those that do not support it ignore this property. Use the
743 * function supports() to determine whether the DBMS driver can manage indexes.
748 * 'user_name' => array(
749 * 'sorting' => 'ascending'
751 * 'last_login' => array()
754 * @return mixed MDB2_OK on success, a MDB2 error on failure
757 function createIndex($table, $name, $definition)
759 $db = $this->getDBInstance();
760 if (MDB2::isError($db)) {
764 $table = $db->quoteIdentifier($table, true);
765 $name = $db->quoteIdentifier($db->getIndexName($name), true);
766 $query = "CREATE INDEX $name ON $table";
768 foreach (array_keys($definition['fields']) as $field) {
769 $fields[] = $db->quoteIdentifier($field, true);
771 $query .= ' ('. implode(', ', $fields) . ')';
772 $result = $db->exec($query);
773 if (MDB2::isError($result)) {
783 * drop existing index
785 * @param string $table name of table that should be used in method
786 * @param string $name name of the index to be dropped
787 * @return mixed MDB2_OK on success, a MDB2 error on failure
790 function dropIndex($table, $name)
792 $db = $this->getDBInstance();
793 if (MDB2::isError($db)) {
797 $name = $db->quoteIdentifier($db->getIndexName($name), true);
798 $result = $db->exec("DROP INDEX $name");
799 if (MDB2::isError($result)) {
806 // {{{ listTableIndexes()
809 * list all indexes in a table
811 * @param string $table name of table that should be used in method
812 * @return mixed array of index names on success, a MDB2 error on failure
815 function listTableIndexes($table)
817 $db = $this->getDBInstance();
818 if (MDB2::isError($db)) {
822 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
823 'method not implemented', __FUNCTION__);
827 // {{{ _getAdvancedFKOptions()
830 * Return the FOREIGN KEY query section dealing with non-standard options
831 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
833 * @param array $definition
837 function _getAdvancedFKOptions($definition)
843 // {{{ createConstraint()
846 * create a constraint on a table
848 * @param string $table name of the table on which the constraint is to be created
849 * @param string $name name of the constraint to be created
850 * @param array $definition associative array that defines properties of the constraint to be created.
851 * The full structure of the array looks like this:
858 * [fields] => array (
859 * [field1name] => array() // one entry per each field covered
860 * [field2name] => array() // by the index
861 * [field3name] => array(
862 * [sorting] => ascending
866 * [references] => array(
869 * [field1name] => array( //one entry per each referenced field
875 * [initiallydeferred] => 0
876 * [onupdate] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
877 * [ondelete] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
878 * [match] => SIMPLE|PARTIAL|FULL
881 * @return mixed MDB2_OK on success, a MDB2 error on failure
884 function createConstraint($table, $name, $definition)
886 $db = $this->getDBInstance();
887 if (MDB2::isError($db)) {
890 $table = $db->quoteIdentifier($table, true);
891 $name = $db->quoteIdentifier($db->getIndexName($name), true);
892 $query = "ALTER TABLE $table ADD CONSTRAINT $name";
893 if (!empty($definition['primary'])) {
894 $query.= ' PRIMARY KEY';
895 } elseif (!empty($definition['unique'])) {
897 } elseif (!empty($definition['foreign'])) {
898 $query.= ' FOREIGN KEY';
901 foreach (array_keys($definition['fields']) as $field) {
902 $fields[] = $db->quoteIdentifier($field, true);
904 $query .= ' ('. implode(', ', $fields) . ')';
905 if (!empty($definition['foreign'])) {
906 $query.= ' REFERENCES ' . $db->quoteIdentifier($definition['references']['table'], true);
907 $referenced_fields = array();
908 foreach (array_keys($definition['references']['fields']) as $field) {
909 $referenced_fields[] = $db->quoteIdentifier($field, true);
911 $query .= ' ('. implode(', ', $referenced_fields) . ')';
912 $query .= $this->_getAdvancedFKOptions($definition);
914 $result = $db->exec($query);
915 if (MDB2::isError($result)) {
922 // {{{ dropConstraint()
925 * drop existing constraint
927 * @param string $table name of table that should be used in method
928 * @param string $name name of the constraint to be dropped
929 * @param string $primary hint if the constraint is primary
930 * @return mixed MDB2_OK on success, a MDB2 error on failure
933 function dropConstraint($table, $name, $primary = false)
935 $db = $this->getDBInstance();
936 if (MDB2::isError($db)) {
940 $table = $db->quoteIdentifier($table, true);
941 $name = $db->quoteIdentifier($db->getIndexName($name), true);
942 $result = $db->exec("ALTER TABLE $table DROP CONSTRAINT $name");
943 if (MDB2::isError($result)) {
950 // {{{ listTableConstraints()
953 * list all constraints in a table
955 * @param string $table name of table that should be used in method
956 * @return mixed array of constraint names on success, a MDB2 error on failure
959 function listTableConstraints($table)
961 $db = $this->getDBInstance();
962 if (MDB2::isError($db)) {
966 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
967 'method not implemented', __FUNCTION__);
971 // {{{ createSequence()
976 * @param string $seq_name name of the sequence to be created
977 * @param string $start start value of the sequence; default is 1
978 * @return mixed MDB2_OK on success, a MDB2 error on failure
981 function createSequence($seq_name, $start = 1)
983 $db = $this->getDBInstance();
984 if (MDB2::isError($db)) {
988 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
989 'method not implemented', __FUNCTION__);
993 // {{{ dropSequence()
996 * drop existing sequence
998 * @param string $seq_name name of the sequence to be dropped
999 * @return mixed MDB2_OK on success, a MDB2 error on failure
1002 function dropSequence($name)
1004 $db = $this->getDBInstance();
1005 if (MDB2::isError($db)) {
1009 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
1010 'method not implemented', __FUNCTION__);
1014 // {{{ listSequences()
1017 * list all sequences in the current database
1019 * @param string database, the current is default
1020 * NB: not all the drivers can get the sequence names from
1021 * a database other than the current one
1022 * @return mixed array of sequence names on success, a MDB2 error on failure
1025 function listSequences($database = null)
1027 $db = $this->getDBInstance();
1028 if (MDB2::isError($db)) {
1032 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
1033 'method not implemented', __FUNCTION__);