Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.5.0b4

Bug #12376 MDB2_Driver_Reverse_pgsql::getTableConstraintDefinition() uses wrong index
Submitted: 2007-11-03 16:36 UTC Modified: 2007-11-09 15:35 UTC
From: birne Assigned: quipo
Status: Closed Package: MDB2_Driver_pgsql
PHP Version: 5.2.1 OS: Linux 2.6.20-16-generic
Roadmaps: (Not assigned)    
Subscription  


 [2007-11-03 16:36 UTC] birne (Dennis Birne)
Description: ------------ The Reverse module of MDB2 allows to gather information about a database schema. The MDB2_Driver_Reverse_pgsql::getTableConstraintDefinition() does that for Constraints of a certain table. It uses the $row[constraint_key] to determine the index of all columns involved in a Constraint. Under certain conditions these column indexes do not match with the indexes returned by the drivers listTableFields() method. That will result in a wrong access to the array holding all columns of the currently inspected array. This is because PostgreSQL internally gives each colum (in PgSQL's terms a attribute) an auto-incremented number (PgSQL: "attnum"). But if a column of a table gets dropped, the upfollowing columns will not be renumbered. MDB2_Driver_Reverse_pgsql::getTableConstraintDefinition()relies on the PostgreSQL internal column number, while MDB2_Driver_pgsql::listTableFields() numbers the tables columns as found. Quick workaround: Dumping the whole database, dropping the database, creating a new database and loading the schema + data from the dump will fixes this issue because PostgreSQL does not include the column numbers in the dump and thus renumber the columns from scratch. A simple "VACUUM;" will not help here. MDB2_Driver_pgsql::listTableFields() works as I would expect. MDB2_Driver_Reverse_pgsql::getTableConstraintDefinition() must not rely on the array indexes being identical to PostgreSQL's attribute number (attnum). Test script: --------------- Create a table in a PostgreSQL database with two or more columns. Embed the *last* column in any kind of constraint; in my case it is a UNIQUE constraint but I assume this bug affects all types of constraints. DROP the second last column of that table. Call $mdb2->tableInfo($nameofyourtable) with $mdb2 being an established MDB2 database connection to your database. Expected result: ---------------- An array containing information on the given table Actual result: -------------- PHP Notice: Undefined offset: 35 in [..]/MDB2/MDB2/Driver/Reverse/pgsql.php (343): Backtrace: ( ! ) Notice: Undefined offset: 35 in MDB2/MDB2/Driver/Reverse/pgsql.php on line 343 Call Stack # Time Memory Function Location 1 0.2396 61676 {main}( ) ../index.php:0 [..] 8 2.4068 5178556 MDB2_Driver_pgsql->tableInfo( ??? ) ../Metadata.php:21 9 2.4069 5178556 MDB2_Driver_Common->__call( ???, ??? ) ../MDB2.php:0 10 2.4070 5178556 call_user_func_array ( ???, ??? ) ../MDB2.php:1973 11 2.4070 5178556 MDB2_Driver_Reverse_pgsql->tableInfo( ???, ??? ) ../MDB2.php:0 12 2.4071 5178556 MDB2_Driver_Reverse_Common->tableInfo( ???, ??? ) ../pgsql.php:454 13 2.4431 5185080 MDB2_Driver_Reverse_pgsql->getTableConstraintDefinition( ???, ??? ) ../Common.php:452 MDB2/MDB2/Driver/Reverse/pgsql.php, line 343-346: $definition['fields'][$columns[($number - 1)]] = array( 'position' => $colpos++, 'sorting' => 'ascending', ); (Hint: the "undefined offset: 35" is my column number; this number will of course be 3 if you do the above example with only three columns.)

Comments

 [2007-11-04 06:49 UTC] birne (Dennis Birne)
Moved to package MDB2_Driver_pgsql
 [2007-11-08 02:09 UTC] quipo (Lorenzo Alberton)
Hi Dennis, thanks for the report. The patch isn't 100% correct, if you run the testsuite [1] you'll see that the 2nd query returns the fields for the referencing table instead of the referenced one. If you have time, please post a new patch, otherwise I'll fix it during the weekend. Thanks a lot for your help! [1] MDB2_reverse_testcase::testGetTableConstraintDefinition()
 [2007-11-09 15:35 UTC] quipo (Lorenzo Alberton)
I found the error in the patch and committed a fix. Please check the CVS version.