cvs: pear /MDB2/MDB2/Driver/Manager Common.php fbsql.php ibase.php mssql.php mysql.php mysqli.php oci8.php pgsql.php sqlite.php

dufuz Fri Jun 10 11:59:07 2005 EDT Modified files: /pear/MDB2/MDB2/Driver/Manager Common.php fbsql.php ibase.php mssql.php mysqli.php mysql.php oci8.php pgsql.php sqlite.php Log: All: alterTable now does not need declaration, it now fetches it via the database driver. Mysql(i): Primary key and auto increment support

http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/Common.php?r1=1.15&r2=1.16&ty=u Index: pear/MDB2/MDB2/Driver/Manager/Common.php diff -u pear/MDB2/MDB2/Driver/Manager/Common.php:1.15 pear/MDB2/MDB2/Driver/Manager/Common.php:1.16 --- pear/MDB2/MDB2/Driver/Manager/Common.php:1.15 Tue Apr 19 08:53:42 2005 +++ pear/MDB2/MDB2/Driver/Manager/Common.php Fri Jun 10 11:59:04 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ // -// $Id: Common.php,v 1.15 2005/04/19 12:53:42 lsmith Exp $ +// $Id: Common.php,v 1.16 2005/06/10 15:59:04 dufuz Exp $ // /** @@ -263,9 +263,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -300,9 +297,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -310,7 +304,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -321,13 +314,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -548,10 +539,12 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($name, $start = 1) + function createSequence($name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/fbsql.php?r1=1.21&r2=1.22&ty=u Index: pear/MDB2/MDB2/Driver/Manager/fbsql.php diff -u pear/MDB2/MDB2/Driver/Manager/fbsql.php:1.21 pear/MDB2/MDB2/Driver/Manager/fbsql.php:1.22 --- pear/MDB2/MDB2/Driver/Manager/fbsql.php:1.21 Sat Apr 16 18:27:48 2005 +++ pear/MDB2/MDB2/Driver/Manager/fbsql.php Fri Jun 10 11:59:04 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ // -// $Id: fbsql.php,v 1.21 2005/04/16 22:27:48 lsmith Exp $ +// $Id: fbsql.php,v 1.22 2005/06/10 15:59:04 dufuz Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -143,9 +143,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -180,9 +177,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -190,7 +184,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -201,13 +194,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -235,28 +226,33 @@ 'alterTable: change type "'.$change_name.'" not yet supported'); } } + if ($check) { return MDB2_OK; } + $query = (isset($changes['name']) ? 'RENAME AS '.$changes['name'] : ''); + if (isset($changes['added_fields'])) { $fields = $changes['added_fields']; foreach ($fields as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } - $query .= 'ADD '.$field['declaration']; + $query .= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field); } } + if (isset($changes['removed_fields'])) { $fields = $changes['removed_fields']; foreach ($fields as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } - $query .= 'DROP '.$field_name; + $query .= 'DROP ' . $field_name; } } + $renamed_fields = array(); if (isset($changes['renamed_fields'])) { $fields = $changes['renamed_fields']; @@ -264,11 +260,12 @@ $renamed_fields[$field['name']] = $field_name; } } + if (isset($changes['changed_fields'])) { $fields = $changes['changed_fields']; foreach ($fields as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } if (isset($renamed_fields[$field_name])) { $old_field_name = $renamed_fields[$field_name]; @@ -276,21 +273,25 @@ } else { $old_field_name = $field_name; } - $query .= "CHANGE $old_field_name ".$field['declaration']; + $query .= "CHANGE $old_field_name " . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (count($renamed_fields)) { foreach ($renamed_fields as $renamed_field_name => $renamed_field) { if ($query) { - $query .= ','; + $query .= ', '; } $old_field_name = $renamed_fields[$renamed_field_name]; - $query .= "CHANGE $old_field_name ".$changes['renamed_fields'][$old_field_name]['declaration']; + $field = $changes['renamed_fields'][$old_field_name]; + $query .= 'CHANGE ' . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (!$query) { return MDB2_OK; } + return $db->query("ALTER TABLE $name $query"); } @@ -477,10 +478,12 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name); http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/ibase.php?r1=1.18&r2=1.19&ty=u Index: pear/MDB2/MDB2/Driver/Manager/ibase.php diff -u pear/MDB2/MDB2/Driver/Manager/ibase.php:1.18 pear/MDB2/MDB2/Driver/Manager/ibase.php:1.19 --- pear/MDB2/MDB2/Driver/Manager/ibase.php:1.18 Fri Apr 1 07:53:34 2005 +++ pear/MDB2/MDB2/Driver/Manager/ibase.php Fri Jun 10 11:59:04 2005 @@ -42,7 +42,7 @@ // | Author: Lorenzo Alberton <l.alberton@quipo.it> | // +----------------------------------------------------------------------+ // -// $Id: ibase.php,v 1.18 2005/04/01 12:53:34 lsmith Exp $ +// $Id: ibase.php,v 1.19 2005/06/10 15:59:04 dufuz Exp $ require_once 'MDB2/Driver/Manager/Common.php'; @@ -152,9 +152,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -189,9 +186,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -199,7 +193,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -210,13 +203,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -258,9 +249,10 @@ if ($query) { $query .= ', '; } - $query .= 'ADD ' . $field['declaration']; + $query .= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field); } } + if (isset($changes['removed_fields'])) { $fields = $changes['removed_fields']; foreach ($fields as $field_name => $field) { @@ -270,6 +262,7 @@ $query .= 'DROP ' . $field_name; } } + if (isset($changes['renamed_fields'])) { $fields = $changes['renamed_fields']; foreach ($fields as $field_name => $field) { @@ -279,6 +272,7 @@ $query .= 'ALTER ' . $field_name . ' TO ' . $field['name']; } } + if (isset($changes['changed_fields'])) { $fields = $changes['changed_fields']; foreach ($fields as $field_name => $field) { @@ -289,12 +283,14 @@ $query .= ', '; } $db->loadModule('Datatype'); - $query .= 'ALTER '.$field_name.' TYPE '.$db->datatype->getTypeDeclaration($field['definition']); + $query .= 'ALTER ' . $field_name.' TYPE ' . $db->datatype->getTypeDeclaration($field['definition']); } } + if (!$query) { return MDB2_OK; } + return $db->query("ALTER TABLE $name $query"); } @@ -408,10 +404,12 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public **/ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name); http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/mssql.php?r1=1.25&r2=1.26&ty=u Index: pear/MDB2/MDB2/Driver/Manager/mssql.php diff -u pear/MDB2/MDB2/Driver/Manager/mssql.php:1.25 pear/MDB2/MDB2/Driver/Manager/mssql.php:1.26 --- pear/MDB2/MDB2/Driver/Manager/mssql.php:1.25 Fri Jun 10 10:19:12 2005 +++ pear/MDB2/MDB2/Driver/Manager/mssql.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Frank M. Kromann <frank@kromann.info> | // +----------------------------------------------------------------------+ // -// $Id: mssql.php,v 1.25 2005/06/10 14:19:12 alexmerz Exp $ +// $Id: mssql.php,v 1.26 2005/06/10 15:59:05 dufuz Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -117,9 +117,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -154,9 +151,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -164,7 +158,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -175,13 +168,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -209,9 +200,11 @@ 'alterTable: change type "'.$change_name.'" not yet supported'); } } + if ($check) { return MDB2_OK; } + $query = ''; if (isset($changes['added_fields'])) { if ($query) { @@ -219,16 +212,18 @@ } $query.= 'ADD '; $fields = $changes['added_fields']; - foreach ($fields as $field) { + foreach ($fields as $field_name => $field) { if ($query) { $query.= ', '; } - $query.= $field['declaration']; + $query .= $db->getDeclaration($field['type'], $field_name, $field); } } + if (!$query) { return MDB2_OK; } + return $db->query("ALTER TABLE $name $query"); } @@ -332,10 +327,12 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name); http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/mysqli.php?r1=1.7&r2=1.8&ty=u Index: pear/MDB2/MDB2/Driver/Manager/mysqli.php diff -u pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.7 pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.8 --- pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.7 Tue Apr 19 08:53:42 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysqli.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ // -// $Id: mysqli.php,v 1.7 2005/04/19 12:53:42 lsmith Exp $ +// $Id: mysqli.php,v 1.8 2005/06/10 15:59:05 dufuz Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -101,7 +101,7 @@ return MDB2_OK; } $not_supported = false; - for ($i=0, $j=count($check); $i<$j; ++$i) { + for ($i = 0, $j = count($check); $i < $j; ++$i) { $query = 'SHOW VARIABLES LIKE '.$db->quote($check[$i], 'text'); $has = $db->queryRow($query, null, MDB2_FETCHMODE_ORDERED); if (PEAR::isError($has)) { @@ -250,10 +250,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. - * * removed_fields * * Associative array with the names of fields to be removed as indexes @@ -287,9 +283,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -297,7 +290,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -308,13 +300,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -342,19 +332,23 @@ 'alterTable: change type "'.$change_name.'" not yet supported'); } } + if ($check) { return MDB2_OK; } + $query = (isset($changes['name']) ? 'RENAME AS '.$changes['name'] : ''); + if (isset($changes['added_fields'])) { $fields = $changes['added_fields']; foreach ($fields as $field) { if ($query) { - $query .= ','; + $query .= ', '; } - $query .= 'ADD '.$field['declaration']; + $query .= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field); } } + if (isset($changes['removed_fields'])) { $fields = $changes['removed_fields']; foreach ($fields as $field_name => $field) { @@ -364,6 +358,7 @@ $query .= 'DROP '.$field_name; } } + $renamed_fields = array(); if (isset($changes['renamed_fields'])) { $fields = $changes['renamed_fields']; @@ -371,11 +366,12 @@ $renamed_fields[$field['name']] = $field_name; } } + if (isset($changes['changed_fields'])) { $fields = $changes['changed_fields']; foreach ($fields as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } if (isset($renamed_fields[$field_name])) { $old_field_name = $renamed_fields[$field_name]; @@ -383,21 +379,25 @@ } else { $old_field_name = $field_name; } - $query .= "CHANGE $old_field_name ".$field['declaration']; + $query .= "CHANGE $field_name " . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (count($renamed_fields)) { foreach ($renamed_fields as $renamed_fields_name => $renamed_field) { if ($query) { $query .= ','; } $old_field_name = $renamed_field; - $query .= "CHANGE $old_field_name ".$changes['renamed_fields'][$old_field_name]['declaration']; + $field = $changes['renamed_fields'][$old_field_name]; + $query .= 'CHANGE ' . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (!$query) { return MDB2_OK; } + return $db->query("ALTER TABLE $name $query"); } @@ -524,16 +524,20 @@ function createIndex($table, $name, $definition) { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; - $query = "ALTER TABLE $table ADD ".(isset($definition['unique']) ? 'UNIQUE' : 'INDEX')." $name ("; - $skipped_first = false; - foreach ($definition['fields'] as $field_name => $field) { - if ($skipped_first) { - $query .= ','; - } - $query .= $field_name; - $skipped_first = true; + + if (isset($definition['primary'])) { + $type = 'PRIMARY KEY'; + $name = ''; + } elseif (isset($definition['unique'])) { + $type = 'UNIQUE'; + } else { + $type = 'INDEX'; } + + $query = "ALTER TABLE $table ADD $type $name ("; + $query .= implode(', ', array_keys($definition['fields'])); $query .= ')'; + return $db->query($query); } @@ -571,11 +575,13 @@ if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $key_name = strtolower($key_name); } + $query = "SHOW INDEX FROM $table"; $indexes_all = $db->queryCol($query, 'text', $key_name); if (PEAR::isError($indexes_all)) { return $indexes_all; } + $found = $indexes = array(); for ($index = 0, $j = count($indexes_all); $index < $j; ++$index) { if ($indexes_all[$index] != 'PRIMARY' @@ -585,9 +591,11 @@ $found[$indexes_all[$index]] = true; } } + if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $indexes = array_flip(array_change_key_case(array_flip($indexes), CASE_LOWER)); } + return $indexes; } @@ -599,18 +607,31 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; + + if ($auto_increment) { + $result = $this->_addAutoIncrement($field, $seq_name, $start); + if (PEAR::isError($result)) { + return $result; + } + + return MDB2_OK; + } + $sequence_name = $db->getSequenceName($seq_name); $seqcol_name = $db->options['seqcol_name']; $result = $this->_verifyTableType($db->options['default_table_type']); if (PEAR::isError($result)) { return $result; } + $res = $db->query("CREATE TABLE $sequence_name". "($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))". (strlen($db->options['default_table_type']) ? ' TYPE='.$db->options['default_table_type'] : '') @@ -618,13 +639,16 @@ if (PEAR::isError($res)) { return $res; } + if ($start == 1) { return MDB2_OK; } + $res = $db->query("INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')'); if (!PEAR::isError($res)) { return MDB2_OK; } + // Handle error $result = $db->query("DROP TABLE $sequence_name"); if (PEAR::isError($result)) { @@ -640,6 +664,47 @@ // }}} // {{{ dropSequence() + + function _addAutoIncrement($field, $table, $start) + { + $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; + if (!$db->supports('auto_increment')) { + $db->debug('Auto increment is not supported'); + return MDB2_OK; + } + + $definition = array( + 'primary' => true, + 'fields' => array( + $field => array(), + ), + ); + $result = $this->createIndex($table, 'PRIMARY', $definition); + if (PEAR::isError($result)) { + return $result; + } + + $db->loadModule('Reverse'); + $field_info = $db->reverse->getTableFieldDefinition($table, $field); + if (PEAR::isError($field_info)) { + return $field_info; + } + + $changes = array(); + $changes['changed_fields'][$field] = $field_info[0][0]; + $changes['changed_fields'][$field]['autoincrement'] = $start; + + $result = $db->manager->alterTable($table, $changes, false); + if (PEAR::isError($result)) { + return $result; + } + + return true; + } + + // }}} + // {{{ dropSequence() + /** * drop existing sequence * @@ -670,11 +735,14 @@ if (PEAR::isError($table_names)) { return $table_names; } + $sequences = array(); for ($i = 0, $j = count($table_names); $i < $j; ++$i) { - if ($sqn = $this->_isSequenceName($table_names[$i])) + if ($sqn = $this->_isSequenceName($table_names[$i])) { $sequences[] = $sqn; + } } + return $sequences; } http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/mysql.php?r1=1.20&r2=1.21&ty=u Index: pear/MDB2/MDB2/Driver/Manager/mysql.php diff -u pear/MDB2/MDB2/Driver/Manager/mysql.php:1.20 pear/MDB2/MDB2/Driver/Manager/mysql.php:1.21 --- pear/MDB2/MDB2/Driver/Manager/mysql.php:1.20 Tue Jun 7 06:12:16 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysql.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ // -// $Id: mysql.php,v 1.20 2005/06/07 10:12:16 lsmith Exp $ +// $Id: mysql.php,v 1.21 2005/06/10 15:59:05 dufuz Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -101,7 +101,7 @@ return MDB2_OK; } $not_supported = false; - for ($i=0, $j=count($check); $i<$j; ++$i) { + for ($i = 0, $j = count($check); $i < $j; ++$i) { $query = 'SHOW VARIABLES LIKE '.$db->quote($check[$i], 'text'); $has = $db->queryRow($query, null, MDB2_FETCHMODE_ORDERED); if (PEAR::isError($has)) { @@ -216,7 +216,7 @@ return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null, 'createTable: '.$this->getUserinfo()); } - if (isset($db->supported['transactions']) + if ($db->supports('transactions') && ($db->options['default_table_type'] == 'BDB' || $db->options['default_table_type'] == 'BERKELEYDB') ) { $query_fields .= ', '.$db->dummy_primary_key.' INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ('.$db->dummy_primary_key.')'; @@ -250,9 +250,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -287,9 +284,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -297,7 +291,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -308,13 +301,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -329,6 +320,7 @@ function alterTable($name, $changes, $check) { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; + foreach ($changes as $change_name => $change) { switch ($change_name) { case 'added_fields': @@ -342,40 +334,42 @@ 'alterTable: change type "'.$change_name.'" not yet supported'); } } + if ($check) { return MDB2_OK; } + $query = (isset($changes['name']) ? 'RENAME AS '.$changes['name'] : ''); + if (isset($changes['added_fields'])) { - $fields = $changes['added_fields']; - foreach ($fields as $field) { + foreach ($changes['added_fields'] as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } - $query .= 'ADD '.$field['declaration']; + $query .= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field); } } + if (isset($changes['removed_fields'])) { - $fields = $changes['removed_fields']; - foreach ($fields as $field_name => $field) { + foreach ($changes['removed_fields'] as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } - $query .= 'DROP '.$field_name; + $query .= 'DROP ' . $field_name; } } + $renamed_fields = array(); if (isset($changes['renamed_fields'])) { - $fields = $changes['renamed_fields']; - foreach ($fields as $field_name => $field) { + foreach ($changes['renamed_fields'] as $field_name => $field) { $renamed_fields[$field['name']] = $field_name; } } + if (isset($changes['changed_fields'])) { - $fields = $changes['changed_fields']; - foreach ($fields as $field_name => $field) { + foreach ($changes['changed_fields'] as $field_name => $field) { if ($query) { - $query .= ','; + $query .= ', '; } if (isset($renamed_fields[$field_name])) { $old_field_name = $renamed_fields[$field_name]; @@ -383,21 +377,25 @@ } else { $old_field_name = $field_name; } - $query .= "CHANGE $old_field_name ".$field['declaration']; + $query .= "CHANGE $field_name " . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (count($renamed_fields)) { foreach ($renamed_fields as $renamed_fields_name => $renamed_field) { if ($query) { - $query .= ','; + $query .= ', '; } $old_field_name = $renamed_field; - $query .= "CHANGE $old_field_name ".$changes['renamed_fields'][$old_field_name]['declaration']; + $field = $changes['renamed_fields'][$old_field_name]; + $query .= 'CHANGE ' . $db->getDeclaration($field['type'], $old_field_name, $field); } } + if (!$query) { return MDB2_OK; } + return $db->query("ALTER TABLE $name $query"); } @@ -449,14 +447,17 @@ if (PEAR::isError($table_names)) { return $table_names; } + $tables = array(); for ($i = 0, $j = count($table_names); $i < $j; ++$i) { if (!$this->_isSequenceName($table_names[$i])) $tables[] = $table_names[$i]; } + if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $tables = array_flip(array_change_key_case(array_flip($tables), CASE_LOWER)); } + return $tables; } @@ -477,12 +478,15 @@ if (PEAR::isError($fields)) { return $fields; } + if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $fields = array_flip(array_change_key_case(array_flip($fields), CASE_LOWER)); } + if (is_array($fields)) { return array_diff($fields, array($db->dummy_primary_key)); } + return array(); } @@ -524,16 +528,20 @@ function createIndex($table, $name, $definition) { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; - $query = "ALTER TABLE $table ADD ".(isset($definition['unique']) ? 'UNIQUE' : 'INDEX')." $name ("; - $skipped_first = false; - foreach ($definition['fields'] as $field_name => $field) { - if ($skipped_first) { - $query .= ','; - } - $query .= $field_name; - $skipped_first = true; + + if (isset($definition['primary'])) { + $type = 'PRIMARY KEY'; + $name = ''; + } elseif (isset($definition['unique'])) { + $type = 'UNIQUE'; + } else { + $type = 'INDEX'; } + + $query = "ALTER TABLE $table ADD $type $name ("; + $query .= implode(', ', array_keys($definition['fields'])); $query .= ')'; + return $db->query($query); } @@ -571,11 +579,13 @@ if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $key_name = strtolower($key_name); } + $query = "SHOW INDEX FROM $table"; $indexes_all = $db->queryCol($query, 'text', $key_name); if (PEAR::isError($indexes_all)) { return $indexes_all; } + $found = $indexes = array(); for ($index = 0, $j = count($indexes_all); $index < $j; ++$index) { if ($indexes_all[$index] != 'PRIMARY' @@ -585,9 +595,11 @@ $found[$indexes_all[$index]] = true; } } + if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) { $indexes = array_flip(array_change_key_case(array_flip($indexes), CASE_LOWER)); } + return $indexes; } @@ -599,32 +611,49 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; + + if ($auto_increment) { + $result = $this->_addAutoIncrement($field, $seq_name, $start); + if (PEAR::isError($result)) { + return $result; + } + + return MDB2_OK; + } + $sequence_name = $db->getSequenceName($seq_name); $seqcol_name = $db->options['seqcol_name']; $result = $this->_verifyTableType($db->options['default_table_type']); if (PEAR::isError($result)) { return $result; } + $res = $db->query("CREATE TABLE $sequence_name". "($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))". (strlen($db->options['default_table_type']) ? ' TYPE='.$db->options['default_table_type'] : '') ); + if (PEAR::isError($res)) { return $res; } + if ($start == 1) { return MDB2_OK; } + $res = $db->query("INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')'); if (!PEAR::isError($res)) { return MDB2_OK; } + // Handle error $result = $db->query("DROP TABLE $sequence_name"); if (PEAR::isError($result)) { @@ -632,6 +661,7 @@ 'createSequence: could not drop inconsistent sequence table ('. $result->getMessage().' ('.$result->getUserinfo().'))'); } + return $db->raiseError(MDB2_ERROR, null, null, 'createSequence: could not create sequence table ('. $res->getMessage().' ('.$res->getUserinfo().'))'); @@ -640,6 +670,47 @@ // }}} // {{{ dropSequence() + + function _addAutoIncrement($field, $table, $start) + { + $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; + if (!$db->supports('auto_increment')) { + $db->debug('Auto increment is not supported'); + return MDB2_OK; + } + + $definition = array( + 'primary' => true, + 'fields' => array( + $field => array(), + ), + ); + $result = $this->createIndex($table, 'PRIMARY', $definition); + if (PEAR::isError($result)) { + return $result; + } + + $db->loadModule('Reverse'); + $field_info = $db->reverse->getTableFieldDefinition($table, $field); + if (PEAR::isError($field_info)) { + return $field_info; + } + + $changes = array(); + $changes['changed_fields'][$field] = $field_info[0][0]; + $changes['changed_fields'][$field]['autoincrement'] = $start; + + $result = $db->manager->alterTable($table, $changes, false); + if (PEAR::isError($result)) { + return $result; + } + + return true; + } + + // }}} + // {{{ dropSequence() + /** * drop existing sequence * @@ -670,11 +741,14 @@ if (PEAR::isError($table_names)) { return $table_names; } + $sequences = array(); for ($i = 0, $j = count($table_names); $i < $j; ++$i) { - if ($sqn = $this->_isSequenceName($table_names[$i])) + if ($sqn = $this->_isSequenceName($table_names[$i])) { $sequences[] = $sqn; + } } + return $sequences; } http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/oci8.php?r1=1.17&r2=1.18&ty=u Index: pear/MDB2/MDB2/Driver/Manager/oci8.php diff -u pear/MDB2/MDB2/Driver/Manager/oci8.php:1.17 pear/MDB2/MDB2/Driver/Manager/oci8.php:1.18 --- pear/MDB2/MDB2/Driver/Manager/oci8.php:1.17 Mon Jun 6 05:01:38 2005 +++ pear/MDB2/MDB2/Driver/Manager/oci8.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ -// $Id: oci8.php,v 1.17 2005/06/06 09:01:38 lsmith Exp $ +// $Id: oci8.php,v 1.18 2005/06/10 15:59:05 dufuz Exp $ require_once 'MDB2/Driver/Manager/Common.php'; @@ -136,9 +136,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -173,9 +170,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -183,7 +177,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -194,13 +187,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -226,9 +217,11 @@ 'alterTable: change type "'.$change_name.'" not yet supported'); } } + if ($check) { return MDB2_OK; } + if (isset($changes['removed_fields'])) { $query = ' DROP ('; $fields = $changes['removed_fields']; @@ -246,13 +239,16 @@ } $query = ''; } + $query = (isset($changes['name']) ? 'RENAME TO '.$changes['name'] : ''); + if (isset($changes['added_fields'])) { $fields = $changes['added_fields']; - foreach ($fields as $field) { - $query .= ' ADD ('.$field['declaration'].')'; + foreach ($fields as $field_name => $field) { + $query .= ' ADD (' . $db->getDeclaration($field['type'], $field_name, $field) . ')'; } } + if (isset($changes['changed_fields'])) { $fields = $changes['changed_fields']; foreach ($fields as $field_name => $field) { @@ -289,10 +285,13 @@ } } } + if (!$query) { return MDB2_OK; } - return $db->query("ALTER TABLE $name $query");} + + return $db->query("ALTER TABLE $name $query"); + } // }}} // {{{ listDatabases() @@ -446,10 +445,12 @@ * @param object $db database object that is extended by this class * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name); http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/pgsql.php?r1=1.18&r2=1.19&ty=u Index: pear/MDB2/MDB2/Driver/Manager/pgsql.php diff -u pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.18 pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.19 --- pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.18 Mon Apr 4 04:08:35 2005 +++ pear/MDB2/MDB2/Driver/Manager/pgsql.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Paul Cooper <pgc@ucecom.com> | // +----------------------------------------------------------------------+ // -// $Id: pgsql.php,v 1.18 2005/04/04 08:08:35 lsmith Exp $ +// $Id: pgsql.php,v 1.19 2005/06/10 15:59:05 dufuz Exp $ require_once 'MDB2/Driver/Manager/Common.php'; @@ -109,9 +109,6 @@ * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * - * Additionally, there should be an entry named Declaration that - * is expected to contain the portion of the field declaration already - * in DBMS specific SQL code as it is used in the CREATE TABLE statement. * * removed_fields * @@ -146,9 +143,6 @@ * if the notnull constraint is to be added or removed, there should also be * an entry with index ChangedNotNull assigned to 1. * - * Additionally, there should be an entry named Declaration that is expected - * to contain the portion of the field changed declaration already in DBMS - * specific SQL code as it is used in the CREATE TABLE statement. * Example * array( * 'name' => 'userlist', @@ -156,7 +150,6 @@ * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 - * 'declaration' => 'quota INT' * ) * ), * 'removed_fields' => array( @@ -167,13 +160,11 @@ * 'gender' => array( * 'default' => 'M', * 'change_default' => 1, - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ), * 'renamed_fields' => array( * 'sex' => array( * 'name' => 'gender', - * 'declaration' => "gender CHAR(1) DEFAULT 'M'" * ) * ) * ) @@ -201,28 +192,34 @@ 'alterTable: change type "'.$change_name.'\" not yet supported'); } } + if ($check) { return MDB2_OK; } + if (isset($changes['added_fields'])) { - $fields = $changes['added_fields']; - foreach ($fields as $field) { - $result = $db->query("ALTER TABLE $name ADD ".$field['declaration']); - if (PEAR::isError($result)) { - return $result; + foreach ($changes['added_fields'] as $field_name => $field) { + if ($query) { + $query .= ', '; } + $query .= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field); } } + if (isset($changes['removed_fields'])) { - $fields = $changes['removed_fields']; - foreach ($fields as $field_name => $field) { - $result = $db->query("ALTER TABLE $name DROP ".$field_name); - if (PEAR::isError($result)) { - return $result; + foreach ($changes['removed_fields'] as $field_name => $field) { + if ($query) { + $query .= ', '; } + $query .= 'DROP ' . $field_name; } } - return MDB2_OK; + + if (!$query) { + return MDB2_OK; + } + + return $db->query("ALTER TABLE $name $query"); } // }}} @@ -400,10 +397,12 @@ * * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @return mixed MDB2_OK on success, a MDB2 error on failure * @access public **/ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name); http://cvs.php.net/diff.php/pear/MDB2/MDB2/Driver/Manager/sqlite.php?r1=1.17&r2=1.18&ty=u Index: pear/MDB2/MDB2/Driver/Manager/sqlite.php diff -u pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.17 pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.18 --- pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.17 Sat Apr 16 18:27:48 2005 +++ pear/MDB2/MDB2/Driver/Manager/sqlite.php Fri Jun 10 11:59:05 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@backendmedia.com> | // +----------------------------------------------------------------------+ // -// $Id: sqlite.php,v 1.17 2005/04/16 22:27:48 lsmith Exp $ +// $Id: sqlite.php,v 1.18 2005/06/10 15:59:05 dufuz Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -297,9 +297,11 @@ * @param string $seq_name name of the sequence to be created * @param string $start start value of the sequence; default is 1 * @return mixed MDB2_OK on success, a MDB2 error on failure + * @param boolean $auto_increment if the seq should be auto inc or not; default is false + * @param string $field name of the field that's being turned into auto increment * @access public */ - function createSequence($seq_name, $start = 1) + function createSequence($seq_name, $start = 1, $auto_increment = false, $field = '') { $db =& $GLOBALS['_MDB2_databases'][$this->db_index]; $sequence_name = $db->getSequenceName($seq_name);