cvs: pear /MDB2 package_fbsql.php package_ibase.php package_mssql.php package_oci8.php /MDB2/MDB2/Driver/Manager Common.php mysql.php mysqli.php
pgsql.php sqlite.php /MDB2/MDB2/Driver/Reverse mysql.php mysqli.php pgsql.php sqlite.php /MDB2/tests MDB2_manager_testcase.php
| From: | Lukas Smith | Date: | Thu, 15 Dec 2005 22:52:56 +0000 |
| Subject: | cvs: pear /MDB2 package_fbsql.php package_ibase.php package_mssql.php package_oci8.php /MDB2/MDB2/Driver/Manager Common.php mysql.php mysqli.php pgsql.php sqlite.php /MDB2/MDB2/Driver/Reverse mysql.php mysqli.php pgsql.php sqlite.php /MDB2/tests MDB2_manager_testcase.php |
||
| Groups: | php.pear.cvs | ||
| Request: | Send a blank email to pear-cvs+get-36888@lists.php.net to get a copy of this message | ||
lsmith Thu Dec 15 22:52:56 2005 EDT
Modified files:
/pear/MDB2 package_fbsql.php package_ibase.php package_mssql.php
package_oci8.php
/pear/MDB2/MDB2/Driver/Manager Common.php mysql.php mysqli.php
pgsql.php sqlite.php
/pear/MDB2/MDB2/Driver/Reverse mysql.php mysqli.php pgsql.php
sqlite.php
/pear/MDB2/tests MDB2_manager_testcase.php
Log:
- ensure that all primary/unique/foreign key handling is only in the contraint methods
http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_fbsql.php?r1=1.14&r2=1.15&diff_format=u Index: pear/MDB2/package_fbsql.php diff -u pear/MDB2/package_fbsql.php:1.14 pear/MDB2/package_fbsql.php:1.15 --- pear/MDB2/package_fbsql.php:1.14 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_fbsql.php Thu Dec 15 22:52:56 2005 @@ -23,6 +23,7 @@ open todo items: - this driver needs a serious update as it's currently unmaintained/untested +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_ibase.php?r1=1.22&r2=1.23&diff_format=u Index: pear/MDB2/package_ibase.php diff -u pear/MDB2/package_ibase.php:1.22 pear/MDB2/package_ibase.php:1.23 --- pear/MDB2/package_ibase.php:1.22 Mon Dec 12 14:50:32 2005 +++ pear/MDB2/package_ibase.php Thu Dec 15 22:52:56 2005 @@ -36,6 +36,7 @@ open todo items: - handle autoincremement fields in alterTable() +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_mssql.php?r1=1.15&r2=1.16&diff_format=u Index: pear/MDB2/package_mssql.php diff -u pear/MDB2/package_mssql.php:1.15 pear/MDB2/package_mssql.php:1.16 --- pear/MDB2/package_mssql.php:1.15 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_mssql.php Thu Dec 15 22:52:56 2005 @@ -23,6 +23,7 @@ open todo items: - added missing createIndex() and reverse module methods +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_oci8.php?r1=1.17&r2=1.18&diff_format=u Index: pear/MDB2/package_oci8.php diff -u pear/MDB2/package_oci8.php:1.17 pear/MDB2/package_oci8.php:1.18 --- pear/MDB2/package_oci8.php:1.17 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_oci8.php Thu Dec 15 22:52:56 2005 @@ -31,6 +31,7 @@ - added missing reverse module methods - there are still a number of missing methods in the reverse and datatype module - there are still severe stability issues due to ext/oci8, especially on windows +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/Common.php?r1=1.38&r2=1.39&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/Common.php diff -u pear/MDB2/MDB2/Driver/Manager/Common.php:1.38 pear/MDB2/MDB2/Driver/Manager/Common.php:1.39 --- pear/MDB2/MDB2/Driver/Manager/Common.php:1.38 Thu Dec 15 16:54:36 2005 +++ pear/MDB2/MDB2/Driver/Manager/Common.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: Common.php,v 1.38 2005/12/15 16:54:36 lsmith Exp $ +// $Id: Common.php,v 1.39 2005/12/15 22:52:56 lsmith Exp $ // /** @@ -544,11 +544,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach (array_keys($definition['fields']) as $field) { $fields[] = $db->quoteIdentifier($field, true); @@ -636,6 +632,8 @@ $query = "ALTER TABLE $table ADD CONSTRAINT $name"; if (array_key_exists('primary', $definition) && $definition['primary']) { $query.= ' PRIMARY KEY'; + } elseif (array_key_exists('unique', $definition) && $definition['unique']) { + $query.= ' UNIQUE'; } $fields = array(); foreach (array_keys($definition['fields']) as $field) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/mysql.php?r1=1.56&r2=1.57&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/mysql.php diff -u pear/MDB2/MDB2/Driver/Manager/mysql.php:1.56 pear/MDB2/MDB2/Driver/Manager/mysql.php:1.57 --- pear/MDB2/MDB2/Driver/Manager/mysql.php:1.56 Wed Dec 14 12:08:44 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysql.php,v 1.56 2005/12/14 12:08:44 dufuz Exp $ +// $Id: mysql.php,v 1.57 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -580,11 +580,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field => $fieldinfo) { if (array_key_exists('length', $fieldinfo)) { @@ -638,24 +634,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $index_data) { + if ($index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } @@ -708,6 +708,9 @@ $name = 'KEY'; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); + if (array_key_exists('unique', $definition) && $definition['unique']) { + $type = ' UNIQUE'; + } } $table = $db->quoteIdentifier($table, true); @@ -743,7 +746,7 @@ $query = "ALTER TABLE $table DROP PRIMARY KEY"; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = "ALTER TABLE $table DROP FOREIGN KEY $name"; + $query = "ALTER TABLE $table DROP INDEX $name"; } return $db->exec($query); } @@ -766,24 +769,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index == 'PRIMARY') { + foreach ($indexes as $index_data) { + if (!$index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/mysqli.php?r1=1.42&r2=1.43&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/mysqli.php diff -u pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.42 pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.43 --- pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.42 Wed Dec 14 12:08:44 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysqli.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysqli.php,v 1.42 2005/12/14 12:08:44 dufuz Exp $ +// $Id: mysqli.php,v 1.43 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -580,11 +580,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field => $fieldinfo) { if (array_key_exists('length', $fieldinfo)) { @@ -638,24 +634,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $index_data) { + if ($index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } @@ -708,6 +708,9 @@ $name = 'KEY'; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); + if (array_key_exists('unique', $definition) && $definition['unique']) { + $type = ' UNIQUE'; + } } $table = $db->quoteIdentifier($table, true); @@ -743,7 +746,7 @@ $query = "ALTER TABLE $table DROP PRIMARY KEY"; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = "ALTER TABLE $table DROP FOREIGN KEY $name"; + $query = "ALTER TABLE $table DROP INDEX $name"; } return $db->exec($query); } @@ -766,24 +769,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index == 'PRIMARY') { + foreach ($indexes as $index_data) { + if (!$index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/pgsql.php?r1=1.45&r2=1.46&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/pgsql.php diff -u pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.45 pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.46 --- pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.45 Thu Dec 15 16:54:53 2005 +++ pear/MDB2/MDB2/Driver/Manager/pgsql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Paul Cooper <pgc@ucecom.com> | // +----------------------------------------------------------------------+ // -// $Id: pgsql.php,v 1.45 2005/12/15 16:54:53 lsmith Exp $ +// $Id: pgsql.php,v 1.46 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2/Driver/Manager/Common.php'; @@ -508,7 +508,7 @@ } $subquery = "SELECT indexrelid FROM pg_index, pg_class"; - $subquery.= " WHERE pg_class.relname='$table' AND pg_class.oid=pg_index.indrelid AND indisprimary != 't'"; + $subquery.= " WHERE pg_class.relname='$table' AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'"; $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)"; $indexes = $db->queryCol($query, 'text'); if (PEAR::isError($indexes)) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/sqlite.php?r1=1.39&r2=1.40&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/sqlite.php diff -u pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.39 pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.40 --- pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.39 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Manager/sqlite.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: sqlite.php,v 1.39 2005/12/14 11:57:55 lsmith Exp $ +// $Id: sqlite.php,v 1.40 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -404,11 +404,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->getIndexName($name); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field_name => $field) { $field_string = $field_name; @@ -467,15 +463,17 @@ return $db; } - $query = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; + $query = "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; $indexes = $db->queryCol($query, 'text'); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $sql) { + $sql = strtolower($sql); + if (preg_match("/^create index ([^ ]*) on /", $sql, $tmp)) { + $index = $this->_isIndexName($tmp[1]); $result[$index] = true; } } @@ -518,8 +516,31 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'createConstraint: Creating Constraints is not supported'); + if (array_key_exists('primary', $definition) && $definition['primary']) { + return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, + 'createConstraint: Creating Primary Constraints is not supported'); + } + + $table = $db->quoteIdentifier($table, true); + $name = $db->getIndexName($name); + $query = "CREATE UNIQUE INDEX $name ON $table"; + $fields = array(); + foreach ($definition['fields'] as $field_name => $field) { + $field_string = $field_name; + if (array_key_exists('sorting', $field)) { + switch ($field['sorting']) { + case 'ascending': + $field_string.= ' ASC'; + break; + case 'descending': + $field_string.= ' DESC'; + break; + } + } + $fields[] = $field_string; + } + $query .= ' ('.implode(', ', $fields) . ')'; + return $db->exec($query); } // }}} @@ -540,8 +561,13 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'dropConstraints: Drop Constraints is not supported'); + if ($name == 'PRIMARY') { + return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, + 'dropConstraints: Dropping Primary Constraints is not supported'); + } + + $name = $db->getIndexName($name); + return $db->exec("DROP INDEX $name"); } // }}} @@ -561,8 +587,25 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'listTableConstraints: List Constraints is not supported'); + $query = "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; + $indexes = $db->queryCol($query, 'text'); + if (PEAR::isError($indexes)) { + return $indexes; + } + + $result = array(); + foreach ($indexes as $sql) { + $sql = strtolower($sql); + if (preg_match("/^create unique index ([^ ]*) on /", $sql, $tmp)) { + $index = $this->_isIndexName($tmp[1]); + $result[$index] = true; + } + } + + if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { + $result = array_change_key_case($result, $db->options['field_case']); + } + return array_keys($result); } // }}} http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/mysql.php?r1=1.38&r2=1.39&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/mysql.php diff -u pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.38 pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.39 --- pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.38 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/mysql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysql.php,v 1.38 2005/12/14 11:57:55 lsmith Exp $ +// $Id: mysql.php,v 1.39 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -176,7 +176,7 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY' || !$row['non_unique']) { + if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -241,13 +241,15 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY') { - $definition['primary'] = true; - } - if (!$row['non_unique']) { + if ($row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + if ($row['key_name'] == 'PRIMARY') { + $definition['primary'] = true; + } else { + $definition['unique'] = true; + } $column_name = $row['column_name']; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/mysqli.php?r1=1.24&r2=1.25&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/mysqli.php diff -u pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.24 pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.25 --- pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.24 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/mysqli.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysqli.php,v 1.24 2005/12/14 11:57:55 lsmith Exp $ +// $Id: mysqli.php,v 1.25 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -229,7 +229,7 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY' || !$row['non_unique']) { + if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -294,13 +294,15 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY') { - $definition['primary'] = true; - } if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + if ($row['key_name'] == 'PRIMARY') { + $definition['primary'] = true; + } else { + $definition['unique'] = true; + } $column_name = $row['column_name']; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/pgsql.php?r1=1.32&r2=1.33&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/pgsql.php diff -u pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.32 pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.33 --- pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.32 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/pgsql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Paul Cooper <pgc@ucecom.com> | // +----------------------------------------------------------------------+ // -// $Id: pgsql.php,v 1.32 2005/12/14 11:57:55 lsmith Exp $ +// $Id: pgsql.php,v 1.33 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2/Driver/Reverse/Common.php'; @@ -155,12 +155,13 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class - WHERE (pg_class.relname='".$index_name."') AND (pg_class.oid=pg_index.indexrelid)"; + WHERE pg_class.relname='$index_name' AND pg_class.oid=pg_index.indexrelid + AND indisunique != 't' AND indisprimary != 't'"; $row = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($row)) { return $row; } - if ($row['relname'] != $index_name) { + if (!$row) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -169,13 +170,6 @@ $columns = $db->manager->listTableFields($table); $definition = array(); - if ($row['indisprimary'] == 't') { - return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableIndexDefinition: it was not specified an existing table index'); - } - if ($row['indisunique'] == 't') { - $definition['unique'] = true; - } $index_column_numbers = explode(' ', $row['indkey']); @@ -204,12 +198,13 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class - WHERE (pg_class.relname='$index_name') AND (pg_class.oid=pg_index.indexrelid)"; + WHERE pg_class.relname='$index_name' AND pg_class.oid=pg_index.indexrelid + AND (indisunique = 't' OR indisprimary = 't')"; $row = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($row)) { return $row; } - if ($row['relname'] != $index_name) { + if (!$row) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } @@ -220,10 +215,8 @@ $definition = array(); if ($row['indisprimary'] == 't') { $definition['primary'] = true; - } - if ($row['indisunique'] == 't') { - return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableConstraintDefinition: it was not specified an existing table constraint'); + } elseif ($row['indisunique'] == 't') { + $definition['unique'] = true; } $index_column_numbers = explode(' ', $row['indkey']); http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/sqlite.php?r1=1.30&r2=1.31&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/sqlite.php diff -u pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.30 pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.31 --- pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.30 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/sqlite.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: sqlite.php,v 1.30 2005/12/14 11:57:55 lsmith Exp $ +// $Id: sqlite.php,v 1.31 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -199,33 +199,27 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT sql FROM sqlite_master WHERE type='index' AND name='$index_name' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; - $result = $db->query($query); - if (PEAR::isError($result)) { - return $result; + $sql = $db->queryOne($query, 'text'); + if (PEAR::isError($sql)) { + return $sql; } - $columns = $result->getColumnNames(); - $column = 'sql'; - if (!isset($columns[$column])) { - $result->free(); + if (!$sql) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } - $sql = strtolower($result->fetchOne()); - $key_name = $index_name; + $sql = strtolower($sql); $start_pos = strpos($sql, '('); $end_pos = strrpos($sql, ')'); $column_names = substr($sql, $start_pos+1, $end_pos-$start_pos-1); $column_names = split(',', $column_names); - $definition = array(); - if (strstr($sql, ' primary ')) { + if (preg_match("/^create unique/", $sql)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } - if (strstr($sql, ' unique ')) { - $definition['unique'] = true; - } + + $definition = array(); $count = count($column_names); for ($i=0; $i<$count; ++$i) { $column_name = strtok($column_names[$i]," "); @@ -237,7 +231,6 @@ } } - $result->free(); if (!array_key_exists('fields', $definition)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); @@ -265,33 +258,28 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT sql FROM sqlite_master WHERE type='index' AND name='$index_name' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; - $result = $db->query($query); - if (PEAR::isError($result)) { - return $result; + $sql = $db->queryOne($query, 'text'); + if (PEAR::isError($sql)) { + return $sql; } - $columns = $result->getColumnNames(); - $column = 'sql'; - if (!isset($columns[$column])) { - $result->free(); + if (!$sql) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableConstraintDefinition: it was not specified an existing table constraint'); + 'getTableIndexDefinition: it was not specified an existing table index'); } - $sql = strtolower($result->fetchOne()); - $key_name = $index_name; + $sql = strtolower($sql); $start_pos = strpos($sql, '('); $end_pos = strrpos($sql, ')'); $column_names = substr($sql, $start_pos+1, $end_pos-$start_pos-1); $column_names = split(',', $column_names); - $definition = array(); - if (strstr($sql, ' primary ')) { - $definition['primary'] = true; - } - if (strstr($sql, ' unique ')) { + if (!preg_match("/^create unique/", $sql)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + + $definition = array(); + $definition['unique'] = true; $count = count($column_names); for ($i=0; $i<$count; ++$i) { $column_name = strtok($column_names[$i]," "); http://cvs.php.net/viewcvs.cgi/pear/MDB2/tests/MDB2_manager_testcase.php?r1=1.35&r2=1.36&diff_format=u Index: pear/MDB2/tests/MDB2_manager_testcase.php diff -u pear/MDB2/tests/MDB2_manager_testcase.php:1.35 pear/MDB2/tests/MDB2_manager_testcase.php:1.36 --- pear/MDB2/tests/MDB2_manager_testcase.php:1.35 Wed Dec 14 12:10:20 2005 +++ pear/MDB2/tests/MDB2_manager_testcase.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Lorenzo Alberton <l dot alberton at quipo dot it> | // +----------------------------------------------------------------------+ // -// $Id: MDB2_manager_testcase.php,v 1.35 2005/12/14 12:10:20 dufuz Exp $ +// $Id: MDB2_manager_testcase.php,v 1.36 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2_testcase.php'; @@ -195,26 +195,6 @@ /** * */ - function testCreateUniqueIndex() { - if (!$this->methodExists($this->db->manager, 'createIndex')) { - return; - } - $index = array( - 'fields' => array( - 'somename' => array( - 'sorting' => 'ascending', - ), - ), - 'unique' => true, - ); - $name = 'uniqueindex'; - $result = $this->db->manager->createIndex($this->table, $name, $index); - $this->assertFalse(PEAR::isError($result), 'Error creating unique index'); - } - - /** - * - */ function testDropIndex() { if (!$this->methodExists($this->db->manager, 'dropIndex')) { return; @@ -292,6 +272,26 @@ /** * */ + function testCreateUniqueConstraint() { + if (!$this->methodExists($this->db->manager, 'createIndex')) { + return; + } + $index = array( + 'fields' => array( + 'somename' => array( + 'sorting' => 'ascending', + ), + ), + 'unique' => true, + ); + $name = 'uniqueindex'; + $result = $this->db->manager->createConstraint($this->table, $name, $index); + $this->assertFalse(PEAR::isError($result), 'Error creating unique index'); + } + + /** + * + */ function testDropPrimaryKey() { if (!$this->methodExists($this->db->manager, 'dropConstraint')) { return; @@ -332,21 +332,16 @@ 'sorting' => 'ascending', ), ), - 'primary' => true, + 'unique' => true, ); - $name = 'pkindex'; + $name = 'uniqueindex'; $result = $this->db->manager->createConstraint($this->table, $name, $index); if (PEAR::isError($result)) { - echo 'Error creating primary index, trying with name "primary" instead .. '; - $name = 'primary'; - $result = $this->db->manager->createConstraint($this->table, $name, $index); - } - if (PEAR::isError($result)) { - $this->assertFalse(true, 'Error creating primary index'); + $this->assertFalse(true, 'Error creating unique index'); } else { $constraints = $this->db->manager->listTableConstraints($this->table); $this->assertFalse(PEAR::isError($constraints), 'Error listing constraints'); - $this->assertTrue(in_array($name, $constraints), 'Error listing primary key index'); + $this->assertTrue(in_array($name, $constraints), 'Error listing unique key index'); } }
http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_fbsql.php?r1=1.14&r2=1.15&diff_format=u Index: pear/MDB2/package_fbsql.php diff -u pear/MDB2/package_fbsql.php:1.14 pear/MDB2/package_fbsql.php:1.15 --- pear/MDB2/package_fbsql.php:1.14 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_fbsql.php Thu Dec 15 22:52:56 2005 @@ -23,6 +23,7 @@ open todo items: - this driver needs a serious update as it's currently unmaintained/untested +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_ibase.php?r1=1.22&r2=1.23&diff_format=u Index: pear/MDB2/package_ibase.php diff -u pear/MDB2/package_ibase.php:1.22 pear/MDB2/package_ibase.php:1.23 --- pear/MDB2/package_ibase.php:1.22 Mon Dec 12 14:50:32 2005 +++ pear/MDB2/package_ibase.php Thu Dec 15 22:52:56 2005 @@ -36,6 +36,7 @@ open todo items: - handle autoincremement fields in alterTable() +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_mssql.php?r1=1.15&r2=1.16&diff_format=u Index: pear/MDB2/package_mssql.php diff -u pear/MDB2/package_mssql.php:1.15 pear/MDB2/package_mssql.php:1.16 --- pear/MDB2/package_mssql.php:1.15 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_mssql.php Thu Dec 15 22:52:56 2005 @@ -23,6 +23,7 @@ open todo items: - added missing createIndex() and reverse module methods +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/package_oci8.php?r1=1.17&r2=1.18&diff_format=u Index: pear/MDB2/package_oci8.php diff -u pear/MDB2/package_oci8.php:1.17 pear/MDB2/package_oci8.php:1.18 --- pear/MDB2/package_oci8.php:1.17 Sun Dec 11 18:23:42 2005 +++ pear/MDB2/package_oci8.php Thu Dec 15 22:52:56 2005 @@ -31,6 +31,7 @@ - added missing reverse module methods - there are still a number of missing methods in the reverse and datatype module - there are still severe stability issues due to ext/oci8, especially on windows +- ensure that all primary/unique/foreign key handling is only in the contraint methods EOT; $package = new PEAR_PackageFileManager(); http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/Common.php?r1=1.38&r2=1.39&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/Common.php diff -u pear/MDB2/MDB2/Driver/Manager/Common.php:1.38 pear/MDB2/MDB2/Driver/Manager/Common.php:1.39 --- pear/MDB2/MDB2/Driver/Manager/Common.php:1.38 Thu Dec 15 16:54:36 2005 +++ pear/MDB2/MDB2/Driver/Manager/Common.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: Common.php,v 1.38 2005/12/15 16:54:36 lsmith Exp $ +// $Id: Common.php,v 1.39 2005/12/15 22:52:56 lsmith Exp $ // /** @@ -544,11 +544,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach (array_keys($definition['fields']) as $field) { $fields[] = $db->quoteIdentifier($field, true); @@ -636,6 +632,8 @@ $query = "ALTER TABLE $table ADD CONSTRAINT $name"; if (array_key_exists('primary', $definition) && $definition['primary']) { $query.= ' PRIMARY KEY'; + } elseif (array_key_exists('unique', $definition) && $definition['unique']) { + $query.= ' UNIQUE'; } $fields = array(); foreach (array_keys($definition['fields']) as $field) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/mysql.php?r1=1.56&r2=1.57&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/mysql.php diff -u pear/MDB2/MDB2/Driver/Manager/mysql.php:1.56 pear/MDB2/MDB2/Driver/Manager/mysql.php:1.57 --- pear/MDB2/MDB2/Driver/Manager/mysql.php:1.56 Wed Dec 14 12:08:44 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysql.php,v 1.56 2005/12/14 12:08:44 dufuz Exp $ +// $Id: mysql.php,v 1.57 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -580,11 +580,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field => $fieldinfo) { if (array_key_exists('length', $fieldinfo)) { @@ -638,24 +634,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $index_data) { + if ($index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } @@ -708,6 +708,9 @@ $name = 'KEY'; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); + if (array_key_exists('unique', $definition) && $definition['unique']) { + $type = ' UNIQUE'; + } } $table = $db->quoteIdentifier($table, true); @@ -743,7 +746,7 @@ $query = "ALTER TABLE $table DROP PRIMARY KEY"; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = "ALTER TABLE $table DROP FOREIGN KEY $name"; + $query = "ALTER TABLE $table DROP INDEX $name"; } return $db->exec($query); } @@ -766,24 +769,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index == 'PRIMARY') { + foreach ($indexes as $index_data) { + if (!$index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/mysqli.php?r1=1.42&r2=1.43&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/mysqli.php diff -u pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.42 pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.43 --- pear/MDB2/MDB2/Driver/Manager/mysqli.php:1.42 Wed Dec 14 12:08:44 2005 +++ pear/MDB2/MDB2/Driver/Manager/mysqli.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysqli.php,v 1.42 2005/12/14 12:08:44 dufuz Exp $ +// $Id: mysqli.php,v 1.43 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -580,11 +580,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field => $fieldinfo) { if (array_key_exists('length', $fieldinfo)) { @@ -638,24 +634,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $index_data) { + if ($index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } @@ -708,6 +708,9 @@ $name = 'KEY'; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); + if (array_key_exists('unique', $definition) && $definition['unique']) { + $type = ' UNIQUE'; + } } $table = $db->quoteIdentifier($table, true); @@ -743,7 +746,7 @@ $query = "ALTER TABLE $table DROP PRIMARY KEY"; } else { $name = $db->quoteIdentifier($db->getIndexName($name), true); - $query = "ALTER TABLE $table DROP FOREIGN KEY $name"; + $query = "ALTER TABLE $table DROP INDEX $name"; } return $db->exec($query); } @@ -766,24 +769,28 @@ } $key_name = 'Key_name'; + $non_unique = 'Non_unique'; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { $key_name = strtolower($key_name); + $non_unique = strtolower($non_unique); } else { $key_name = strtoupper($key_name); + $non_unique = strtoupper($non_unique); } } $table = $db->quoteIdentifier($table, true); $query = "SHOW INDEX FROM $table"; - $indexes = $db->queryCol($query, 'text', $key_name); + $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index == 'PRIMARY') { + foreach ($indexes as $index_data) { + if (!$index_data[$non_unique]) { + $index = $this->_isIndexName($index_data[$key_name]); $result[$index] = true; } } http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/pgsql.php?r1=1.45&r2=1.46&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/pgsql.php diff -u pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.45 pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.46 --- pear/MDB2/MDB2/Driver/Manager/pgsql.php:1.45 Thu Dec 15 16:54:53 2005 +++ pear/MDB2/MDB2/Driver/Manager/pgsql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Paul Cooper <pgc@ucecom.com> | // +----------------------------------------------------------------------+ // -// $Id: pgsql.php,v 1.45 2005/12/15 16:54:53 lsmith Exp $ +// $Id: pgsql.php,v 1.46 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2/Driver/Manager/Common.php'; @@ -508,7 +508,7 @@ } $subquery = "SELECT indexrelid FROM pg_index, pg_class"; - $subquery.= " WHERE pg_class.relname='$table' AND pg_class.oid=pg_index.indrelid AND indisprimary != 't'"; + $subquery.= " WHERE pg_class.relname='$table' AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'"; $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)"; $indexes = $db->queryCol($query, 'text'); if (PEAR::isError($indexes)) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Manager/sqlite.php?r1=1.39&r2=1.40&diff_format=u Index: pear/MDB2/MDB2/Driver/Manager/sqlite.php diff -u pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.39 pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.40 --- pear/MDB2/MDB2/Driver/Manager/sqlite.php:1.39 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Manager/sqlite.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: sqlite.php,v 1.39 2005/12/14 11:57:55 lsmith Exp $ +// $Id: sqlite.php,v 1.40 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Manager/Common.php'; @@ -404,11 +404,7 @@ $table = $db->quoteIdentifier($table, true); $name = $db->getIndexName($name); - $query = 'CREATE'; - if (array_key_exists('unique', $definition) && $definition['unique']) { - $query.= ' UNIQUE'; - } - $query .= " INDEX $name ON $table"; + $query = "CREATE INDEX $name ON $table"; $fields = array(); foreach ($definition['fields'] as $field_name => $field) { $field_string = $field_name; @@ -467,15 +463,17 @@ return $db; } - $query = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; + $query = "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; $indexes = $db->queryCol($query, 'text'); if (PEAR::isError($indexes)) { return $indexes; } $result = array(); - foreach ($indexes as $index) { - if ($index != 'PRIMARY' && $index = $this->_isIndexName($index)) { + foreach ($indexes as $sql) { + $sql = strtolower($sql); + if (preg_match("/^create index ([^ ]*) on /", $sql, $tmp)) { + $index = $this->_isIndexName($tmp[1]); $result[$index] = true; } } @@ -518,8 +516,31 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'createConstraint: Creating Constraints is not supported'); + if (array_key_exists('primary', $definition) && $definition['primary']) { + return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, + 'createConstraint: Creating Primary Constraints is not supported'); + } + + $table = $db->quoteIdentifier($table, true); + $name = $db->getIndexName($name); + $query = "CREATE UNIQUE INDEX $name ON $table"; + $fields = array(); + foreach ($definition['fields'] as $field_name => $field) { + $field_string = $field_name; + if (array_key_exists('sorting', $field)) { + switch ($field['sorting']) { + case 'ascending': + $field_string.= ' ASC'; + break; + case 'descending': + $field_string.= ' DESC'; + break; + } + } + $fields[] = $field_string; + } + $query .= ' ('.implode(', ', $fields) . ')'; + return $db->exec($query); } // }}} @@ -540,8 +561,13 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'dropConstraints: Drop Constraints is not supported'); + if ($name == 'PRIMARY') { + return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, + 'dropConstraints: Dropping Primary Constraints is not supported'); + } + + $name = $db->getIndexName($name); + return $db->exec("DROP INDEX $name"); } // }}} @@ -561,8 +587,25 @@ return $db; } - return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, - 'listTableConstraints: List Constraints is not supported'); + $query = "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; + $indexes = $db->queryCol($query, 'text'); + if (PEAR::isError($indexes)) { + return $indexes; + } + + $result = array(); + foreach ($indexes as $sql) { + $sql = strtolower($sql); + if (preg_match("/^create unique index ([^ ]*) on /", $sql, $tmp)) { + $index = $this->_isIndexName($tmp[1]); + $result[$index] = true; + } + } + + if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { + $result = array_change_key_case($result, $db->options['field_case']); + } + return array_keys($result); } // }}} http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/mysql.php?r1=1.38&r2=1.39&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/mysql.php diff -u pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.38 pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.39 --- pear/MDB2/MDB2/Driver/Reverse/mysql.php:1.38 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/mysql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysql.php,v 1.38 2005/12/14 11:57:55 lsmith Exp $ +// $Id: mysql.php,v 1.39 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -176,7 +176,7 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY' || !$row['non_unique']) { + if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -241,13 +241,15 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY') { - $definition['primary'] = true; - } - if (!$row['non_unique']) { + if ($row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + if ($row['key_name'] == 'PRIMARY') { + $definition['primary'] = true; + } else { + $definition['unique'] = true; + } $column_name = $row['column_name']; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/mysqli.php?r1=1.24&r2=1.25&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/mysqli.php diff -u pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.24 pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.25 --- pear/MDB2/MDB2/Driver/Reverse/mysqli.php:1.24 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/mysqli.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: mysqli.php,v 1.24 2005/12/14 11:57:55 lsmith Exp $ +// $Id: mysqli.php,v 1.25 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -229,7 +229,7 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY' || !$row['non_unique']) { + if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -294,13 +294,15 @@ } } if ($index_name == $key_name) { - if ($row['key_name'] == 'PRIMARY') { - $definition['primary'] = true; - } if (!$row['non_unique']) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + if ($row['key_name'] == 'PRIMARY') { + $definition['primary'] = true; + } else { + $definition['unique'] = true; + } $column_name = $row['column_name']; if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) { if ($db->options['field_case'] == CASE_LOWER) { http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/pgsql.php?r1=1.32&r2=1.33&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/pgsql.php diff -u pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.32 pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.33 --- pear/MDB2/MDB2/Driver/Reverse/pgsql.php:1.32 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/pgsql.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Paul Cooper <pgc@ucecom.com> | // +----------------------------------------------------------------------+ // -// $Id: pgsql.php,v 1.32 2005/12/14 11:57:55 lsmith Exp $ +// $Id: pgsql.php,v 1.33 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2/Driver/Reverse/Common.php'; @@ -155,12 +155,13 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class - WHERE (pg_class.relname='".$index_name."') AND (pg_class.oid=pg_index.indexrelid)"; + WHERE pg_class.relname='$index_name' AND pg_class.oid=pg_index.indexrelid + AND indisunique != 't' AND indisprimary != 't'"; $row = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($row)) { return $row; } - if ($row['relname'] != $index_name) { + if (!$row) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } @@ -169,13 +170,6 @@ $columns = $db->manager->listTableFields($table); $definition = array(); - if ($row['indisprimary'] == 't') { - return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableIndexDefinition: it was not specified an existing table index'); - } - if ($row['indisunique'] == 't') { - $definition['unique'] = true; - } $index_column_numbers = explode(' ', $row['indkey']); @@ -204,12 +198,13 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class - WHERE (pg_class.relname='$index_name') AND (pg_class.oid=pg_index.indexrelid)"; + WHERE pg_class.relname='$index_name' AND pg_class.oid=pg_index.indexrelid + AND (indisunique = 't' OR indisprimary = 't')"; $row = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC); if (PEAR::isError($row)) { return $row; } - if ($row['relname'] != $index_name) { + if (!$row) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } @@ -220,10 +215,8 @@ $definition = array(); if ($row['indisprimary'] == 't') { $definition['primary'] = true; - } - if ($row['indisunique'] == 't') { - return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableConstraintDefinition: it was not specified an existing table constraint'); + } elseif ($row['indisunique'] == 't') { + $definition['unique'] = true; } $index_column_numbers = explode(' ', $row['indkey']); http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Reverse/sqlite.php?r1=1.30&r2=1.31&diff_format=u Index: pear/MDB2/MDB2/Driver/Reverse/sqlite.php diff -u pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.30 pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.31 --- pear/MDB2/MDB2/Driver/Reverse/sqlite.php:1.30 Wed Dec 14 11:57:55 2005 +++ pear/MDB2/MDB2/Driver/Reverse/sqlite.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Author: Lukas Smith <smith@pooteeweet.org> | // +----------------------------------------------------------------------+ // -// $Id: sqlite.php,v 1.30 2005/12/14 11:57:55 lsmith Exp $ +// $Id: sqlite.php,v 1.31 2005/12/15 22:52:56 lsmith Exp $ // require_once 'MDB2/Driver/Reverse/Common.php'; @@ -199,33 +199,27 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT sql FROM sqlite_master WHERE type='index' AND name='$index_name' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; - $result = $db->query($query); - if (PEAR::isError($result)) { - return $result; + $sql = $db->queryOne($query, 'text'); + if (PEAR::isError($sql)) { + return $sql; } - $columns = $result->getColumnNames(); - $column = 'sql'; - if (!isset($columns[$column])) { - $result->free(); + if (!$sql) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } - $sql = strtolower($result->fetchOne()); - $key_name = $index_name; + $sql = strtolower($sql); $start_pos = strpos($sql, '('); $end_pos = strrpos($sql, ')'); $column_names = substr($sql, $start_pos+1, $end_pos-$start_pos-1); $column_names = split(',', $column_names); - $definition = array(); - if (strstr($sql, ' primary ')) { + if (preg_match("/^create unique/", $sql)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); } - if (strstr($sql, ' unique ')) { - $definition['unique'] = true; - } + + $definition = array(); $count = count($column_names); for ($i=0; $i<$count; ++$i) { $column_name = strtok($column_names[$i]," "); @@ -237,7 +231,6 @@ } } - $result->free(); if (!array_key_exists('fields', $definition)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableIndexDefinition: it was not specified an existing table index'); @@ -265,33 +258,28 @@ $index_name = $db->getIndexName($index_name); $query = "SELECT sql FROM sqlite_master WHERE type='index' AND name='$index_name' AND tbl_name='$table' AND sql NOT NULL ORDER BY name"; - $result = $db->query($query); - if (PEAR::isError($result)) { - return $result; + $sql = $db->queryOne($query, 'text'); + if (PEAR::isError($sql)) { + return $sql; } - $columns = $result->getColumnNames(); - $column = 'sql'; - if (!isset($columns[$column])) { - $result->free(); + if (!$sql) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, - 'getTableConstraintDefinition: it was not specified an existing table constraint'); + 'getTableIndexDefinition: it was not specified an existing table index'); } - $sql = strtolower($result->fetchOne()); - $key_name = $index_name; + $sql = strtolower($sql); $start_pos = strpos($sql, '('); $end_pos = strrpos($sql, ')'); $column_names = substr($sql, $start_pos+1, $end_pos-$start_pos-1); $column_names = split(',', $column_names); - $definition = array(); - if (strstr($sql, ' primary ')) { - $definition['primary'] = true; - } - if (strstr($sql, ' unique ')) { + if (!preg_match("/^create unique/", $sql)) { return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'getTableConstraintDefinition: it was not specified an existing table constraint'); } + + $definition = array(); + $definition['unique'] = true; $count = count($column_names); for ($i=0; $i<$count; ++$i) { $column_name = strtok($column_names[$i]," "); http://cvs.php.net/viewcvs.cgi/pear/MDB2/tests/MDB2_manager_testcase.php?r1=1.35&r2=1.36&diff_format=u Index: pear/MDB2/tests/MDB2_manager_testcase.php diff -u pear/MDB2/tests/MDB2_manager_testcase.php:1.35 pear/MDB2/tests/MDB2_manager_testcase.php:1.36 --- pear/MDB2/tests/MDB2_manager_testcase.php:1.35 Wed Dec 14 12:10:20 2005 +++ pear/MDB2/tests/MDB2_manager_testcase.php Thu Dec 15 22:52:56 2005 @@ -42,7 +42,7 @@ // | Lorenzo Alberton <l dot alberton at quipo dot it> | // +----------------------------------------------------------------------+ // -// $Id: MDB2_manager_testcase.php,v 1.35 2005/12/14 12:10:20 dufuz Exp $ +// $Id: MDB2_manager_testcase.php,v 1.36 2005/12/15 22:52:56 lsmith Exp $ require_once 'MDB2_testcase.php'; @@ -195,26 +195,6 @@ /** * */ - function testCreateUniqueIndex() { - if (!$this->methodExists($this->db->manager, 'createIndex')) { - return; - } - $index = array( - 'fields' => array( - 'somename' => array( - 'sorting' => 'ascending', - ), - ), - 'unique' => true, - ); - $name = 'uniqueindex'; - $result = $this->db->manager->createIndex($this->table, $name, $index); - $this->assertFalse(PEAR::isError($result), 'Error creating unique index'); - } - - /** - * - */ function testDropIndex() { if (!$this->methodExists($this->db->manager, 'dropIndex')) { return; @@ -292,6 +272,26 @@ /** * */ + function testCreateUniqueConstraint() { + if (!$this->methodExists($this->db->manager, 'createIndex')) { + return; + } + $index = array( + 'fields' => array( + 'somename' => array( + 'sorting' => 'ascending', + ), + ), + 'unique' => true, + ); + $name = 'uniqueindex'; + $result = $this->db->manager->createConstraint($this->table, $name, $index); + $this->assertFalse(PEAR::isError($result), 'Error creating unique index'); + } + + /** + * + */ function testDropPrimaryKey() { if (!$this->methodExists($this->db->manager, 'dropConstraint')) { return; @@ -332,21 +332,16 @@ 'sorting' => 'ascending', ), ), - 'primary' => true, + 'unique' => true, ); - $name = 'pkindex'; + $name = 'uniqueindex'; $result = $this->db->manager->createConstraint($this->table, $name, $index); if (PEAR::isError($result)) { - echo 'Error creating primary index, trying with name "primary" instead .. '; - $name = 'primary'; - $result = $this->db->manager->createConstraint($this->table, $name, $index); - } - if (PEAR::isError($result)) { - $this->assertFalse(true, 'Error creating primary index'); + $this->assertFalse(true, 'Error creating unique index'); } else { $constraints = $this->db->manager->listTableConstraints($this->table); $this->assertFalse(PEAR::isError($constraints), 'Error listing constraints'); - $this->assertTrue(in_array($name, $constraints), 'Error listing primary key index'); + $this->assertTrue(in_array($name, $constraints), 'Error listing unique key index'); } }