This is an automated email from the git hooks/post-receive script. ebourg-guest pushed a commit to tag REL8_4_702 in repository libpostgresql-jdbc-java.
commit 19c3c0448cadab20f10d8d39039a0f824d374da2 Author: Kris Jurka <[email protected]> Date: Wed Dec 9 01:06:41 2009 +0000 Fix the ORDINAL_POSITION in the DatabaseMetaData.getColumns. Previously we were returning simply pg_attribute.attnum, but that doesn't work in the presence of dropped columns because later columns don't get their attnum decremented if a preceding column is dropped. Instead use the row_number window function for 8.4 and later servers to figure out the live column position. I got this idea after reading a -hackers message suggesting doing the same thing to the information_schema. --- .../jdbc2/AbstractJdbc2DatabaseMetaData.java | 40 +++++++++++++++++++--- .../test/jdbc2/DatabaseMetaDataTest.java | 37 +++++++++++++++++++- 2 files changed, 71 insertions(+), 6 deletions(-) diff --git a/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java b/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java index 8af40e9..90e14fb 100644 --- a/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java +++ b/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java @@ -3,7 +3,7 @@ * Copyright (c) 2004-2008, PostgreSQL Global Development Group * * IDENTIFICATION -* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v 1.51.2.1 2009/12/04 19:48:01 jurka Exp $ +* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v 1.51.2.2 2009/12/04 21:22:02 jurka Exp $ * *------------------------------------------------------------------------- */ @@ -2249,7 +2249,27 @@ public abstract class AbstractJdbc2DatabaseMetaData String sql; if (connection.haveMinimumServerVersion("7.3")) { - sql = "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype " + + // a.attnum isn't decremented when preceding columns are dropped, + // so the only way to calculate the correct column number is with + // window functions, new in 8.4. + // + // We want to push as much predicate information below the window + // function as possible (schema/table names), but must leave + // column name outside so we correctly count the other columns. + // + if (connection.haveMinimumServerVersion("8.4")) + sql = "SELECT * FROM ("; + else + sql = ""; + + sql += "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,"; + + if (connection.haveMinimumServerVersion("8.4")) + sql += "row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, "; + else + sql += "a.attnum,"; + + sql += "pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype " + " FROM pg_catalog.pg_namespace n " + " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) " + " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) " + @@ -2259,10 +2279,20 @@ public abstract class AbstractJdbc2DatabaseMetaData " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') " + " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') " + " WHERE a.attnum > 0 AND NOT a.attisdropped "; + if (schemaPattern != null && !"".equals(schemaPattern)) { sql += " AND n.nspname LIKE '" + escapeQuotes(schemaPattern) + "' "; } + + if (tableNamePattern != null && !"".equals(tableNamePattern)) + { + sql += " AND c.relname LIKE '" + escapeQuotes(tableNamePattern) + "' "; + } + + if (connection.haveMinimumServerVersion("8.4")) + sql += ") v WHERE true "; + } else if (connection.haveMinimumServerVersion("7.2")) { @@ -2292,13 +2322,13 @@ public abstract class AbstractJdbc2DatabaseMetaData " WHERE a.attrelid=c.oid AND a.attnum > 0 "; } - if (tableNamePattern != null && !"".equals(tableNamePattern)) + if (!connection.haveMinimumServerVersion("7.3") && tableNamePattern != null && !"".equals(tableNamePattern)) { - sql += " AND c.relname LIKE '" + escapeQuotes(tableNamePattern) + "' "; + sql += " AND relname LIKE '" + escapeQuotes(tableNamePattern) + "' "; } if (columnNamePattern != null && !"".equals(columnNamePattern)) { - sql += " AND a.attname LIKE '" + escapeQuotes(columnNamePattern) + "' "; + sql += " AND attname LIKE '" + escapeQuotes(columnNamePattern) + "' "; } sql += " ORDER BY nspname,relname,attnum "; diff --git a/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java b/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java index 7e96add..9b40d5c 100644 --- a/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java +++ b/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java @@ -3,7 +3,7 @@ * Copyright (c) 2004-2008, PostgreSQL Global Development Group * * IDENTIFICATION -* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v 1.44 2008/11/07 09:11:37 jurka Exp $ +* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v 1.44.2.1 2009/12/04 21:22:02 jurka Exp $ * *------------------------------------------------------------------------- */ @@ -326,6 +326,41 @@ public class DatabaseMetaDataTest extends TestCase rs.close(); } + public void testDroppedColumns() throws SQLException + { + if (!TestUtil.haveMinimumServerVersion(con, "8.4")) + return; + + Statement stmt = con.createStatement(); + stmt.execute("ALTER TABLE testmetadata DROP name"); + stmt.execute("ALTER TABLE testmetadata DROP colour"); + stmt.close(); + + DatabaseMetaData dbmd = con.getMetaData(); + ResultSet rs = dbmd.getColumns(null, null, "testmetadata", null); + + assertTrue(rs.next()); + assertEquals("id", rs.getString("COLUMN_NAME")); + assertEquals(1, rs.getInt("ORDINAL_POSITION")); + + assertTrue(rs.next()); + assertEquals("updated", rs.getString("COLUMN_NAME")); + assertEquals(2, rs.getInt("ORDINAL_POSITION")); + + assertTrue(rs.next()); + assertEquals("quest", rs.getString("COLUMN_NAME")); + assertEquals(3, rs.getInt("ORDINAL_POSITION")); + + rs.close(); + + rs = dbmd.getColumns(null, null, "testmetadata", "quest"); + assertTrue(rs.next()); + assertEquals("quest", rs.getString("COLUMN_NAME")); + assertEquals(3, rs.getInt("ORDINAL_POSITION")); + assertTrue(!rs.next()); + rs.close(); + } + public void testSerialColumns() throws SQLException { DatabaseMetaData dbmd = con.getMetaData(); -- Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/pkg-java/libpostgresql-jdbc-java.git _______________________________________________ pkg-java-commits mailing list [email protected] http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-java-commits
