Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit57040f7

Browse files
committed
Attached is a patch for JDBC's getColumn() function that was broken /
flawed in the following ways:1. Only returned columns that had a default value defined, rather than allcolumns in a table2. Used 2 * N + 1 queries to find out attributes, comments and typenamesfor N columns.By using some outer join syntax it is possible to retrieve all necessaryinformation in just one SQL statement. This means this version is onlysuitable for PostgreSQL >= 7.1. Don't know whether that's a problem.I've tested this function with current sources and 7.1.3 and patched bothjdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, asI have no JDK 1.1 available.Note the discussion inhttp://fts.postgresql.org/db/mw/msg.html?mid=1029626regarding differences in obtaining comments on database object in 7.1 and7.2. I was unable to use the following syntax (or similar ones):select ..., descriptionfrom ... left outer join col_description(a.attrelid, a.attnum) descriptionorder by c.relname, a.attnum;(the error was parse error at or near '(') so I had to paste the actualcode for the col_description function into the left outer join. Maybesomeone who is more knowledgable about outer joins might provide me with abetter SQL statement.Jeroen van Vianen
1 parent0b71596 commit57040f7

File tree

2 files changed

+230
-193
lines changed

2 files changed

+230
-193
lines changed

‎src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java‎

Lines changed: 112 additions & 93 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,8 @@
1313
/**
1414
* This class provides information about the database as a whole.
1515
*
16+
* $Id: DatabaseMetaData.java,v 1.30 2001/09/06 03:20:30 momjian Exp $
17+
*
1618
* <p>Many of the methods here return lists of information in ResultSets. You
1719
* can use the normal ResultSet methods such as getString and getInt to
1820
* retrieve the data from these ResultSets. If a given form of metadata is
@@ -1893,21 +1895,19 @@ public java.sql.ResultSet getTableTypes() throws SQLException
18931895
*/
18941896
publicjava.sql.ResultSetgetColumns(Stringcatalog,StringschemaPattern,StringtableNamePattern,StringcolumnNamePattern)throwsSQLException
18951897
{
1896-
// the field descriptors for the new ResultSet
1897-
Fieldf[] =newField[18];
1898-
java.sql.ResultSetr;// ResultSet for the SQL query that we need to do
18991898
Vectorv =newVector();// The new ResultSet tuple stuff
1900-
1901-
f[0] =newField(connection,"TABLE_CAT",iVarcharOid,32);
1902-
f[1] =newField(connection,"TABLE_SCHEM",iVarcharOid,32);
1903-
f[2] =newField(connection,"TABLE_NAME",iVarcharOid,32);
1904-
f[3] =newField(connection,"COLUMN_NAME",iVarcharOid,32);
1905-
f[4] =newField(connection,"DATA_TYPE",iInt2Oid,2);
1906-
f[5] =newField(connection,"TYPE_NAME",iVarcharOid,32);
1907-
f[6] =newField(connection,"COLUMN_SIZE",iInt4Oid,4);
1908-
f[7] =newField(connection,"BUFFER_LENGTH",iVarcharOid,32);
1909-
f[8] =newField(connection,"DECIMAL_DIGITS",iInt4Oid,4);
1910-
f[9] =newField(connection,"NUM_PREC_RADIX",iInt4Oid,4);
1899+
Fieldf[] =newField[18];// The field descriptors for the new ResultSet
1900+
1901+
f[0] =newField(connection,"TABLE_CAT",iVarcharOid,32);
1902+
f[1] =newField(connection,"TABLE_SCHEM",iVarcharOid,32);
1903+
f[2] =newField(connection,"TABLE_NAME",iVarcharOid,32);
1904+
f[3] =newField(connection,"COLUMN_NAME",iVarcharOid,32);
1905+
f[4] =newField(connection,"DATA_TYPE",iInt2Oid,2);
1906+
f[5] =newField(connection,"TYPE_NAME",iVarcharOid,32);
1907+
f[6] =newField(connection,"COLUMN_SIZE",iInt4Oid,4);
1908+
f[7] =newField(connection,"BUFFER_LENGTH",iVarcharOid,32);
1909+
f[8] =newField(connection,"DECIMAL_DIGITS",iInt4Oid,4);
1910+
f[9] =newField(connection,"NUM_PREC_RADIX",iInt4Oid,4);
19111911
f[10] =newField(connection,"NULLABLE",iInt4Oid,4);
19121912
f[11] =newField(connection,"REMARKS",iVarcharOid,32);
19131913
f[12] =newField(connection,"COLUMN_DEF",iVarcharOid,32);
@@ -1917,91 +1917,110 @@ public java.sql.ResultSet getColumns(String catalog, String schemaPattern, Strin
19171917
f[16] =newField(connection,"ORDINAL_POSITION",iInt4Oid,4);
19181918
f[17] =newField(connection,"IS_NULLABLE",iVarcharOid,32);
19191919

1920-
// Added by Stefan Andreasen <stefan@linux.kapow.dk>
1921-
// If the pattern are null then set them to %
1922-
if (tableNamePattern ==null)tableNamePattern="%";
1923-
if (columnNamePattern ==null)columnNamePattern="%";
1924-
1925-
// Now form the query
1926-
Stringquery =
1927-
"select " +
1928-
(connection.haveMinimumServerVersion("7.2") ?"a.attrelid" :"a.oid") +
1929-
",c.relname,a.attname,a.atttypid," +
1930-
"a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc from pg_class c," +
1931-
"pg_attribute a,pg_attrdef d where a.attrelid=c.oid and " +
1932-
"c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
1933-
"a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
1934-
"a.attnum>0 and c.oid=d.adrelid and d.adnum=a.attnum " +
1935-
"order by c.relname,a.attnum";
1936-
1937-
r =connection.ExecSQL(query);
1938-
1939-
while(r.next()) {
1940-
byte[][]tuple =newbyte[18][0];
1941-
1942-
// Fetch the description for the table (if any)
1943-
StringgetDescriptionStatement =
1944-
connection.haveMinimumServerVersion("7.2") ?
1945-
"select col_description(" +r.getInt(1) +"," +r.getInt(5) +")" :
1946-
"select description from pg_description where objoid=" +r.getInt(1);
1947-
1948-
java.sql.ResultSetdr =connection.ExecSQL(getDescriptionStatement);
1949-
1950-
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
1951-
dr.next();
1952-
tuple[11] =dr.getBytes(1);
1953-
}else
1954-
tuple[11] =null;
1955-
dr.close();
1956-
1957-
tuple[0] ="".getBytes();// Catalog name
1958-
tuple[1] ="".getBytes();// Schema name
1959-
tuple[2] =r.getBytes(2);// Table name
1960-
tuple[3] =r.getBytes(3);// Column name
1961-
1962-
dr =connection.ExecSQL("select typname from pg_type where oid = "+r.getString(4));
1963-
dr.next();
1964-
Stringtypname=dr.getString(1);
1965-
dr.close();
1966-
tuple[4] =Integer.toString(connection.getSQLType(typname)).getBytes();// Data type
1967-
tuple[5] =typname.getBytes();// Type name
1968-
1969-
// Column size
1970-
// Looking at the psql source,
1971-
// I think the length of a varchar as specified when the table was created
1972-
// should be extracted from atttypmod which contains this length + sizeof(int32)
1973-
if (typname.equals("bpchar") ||typname.equals("varchar")) {
1974-
intatttypmod =r.getInt(8);
1975-
tuple[6] =Integer.toString(atttypmod != -1 ?atttypmod -VARHDRSZ :0).getBytes();
1976-
}else
1977-
tuple[6] =r.getBytes(7);
1978-
1979-
tuple[7] =null;// Buffer length
1980-
1981-
tuple[8] ="0".getBytes();// Decimal Digits - how to get this?
1982-
tuple[9] ="10".getBytes();// Num Prec Radix - assume decimal
1983-
1984-
// tuple[10] is below
1985-
// tuple[11] is above
1986-
1987-
tuple[12] =r.getBytes(9);// column default
1988-
1989-
tuple[13] =null;// sql data type (unused)
1990-
tuple[14] =null;// sql datetime sub (unused)
1920+
StringBuffersql =newStringBuffer(512);
1921+
1922+
sql.append("select " +
1923+
(connection.haveMinimumServerVersion("7.2") ?"a.attrelid, " :"a.oid, ") +
1924+
" c.relname, " +
1925+
" a.attname, " +
1926+
" a.atttypid, " +
1927+
" a.attnum, " +
1928+
" a.attnotnull, " +
1929+
" a.attlen, " +
1930+
" a.atttypmod, " +
1931+
" d.adsrc, " +
1932+
" t.typname, " +
1933+
" e.description " +
1934+
"from" +
1935+
" (" +
1936+
" (pg_class c inner join pg_attribute a on" +
1937+
" (" +
1938+
" a.attrelid=c.oid");
1939+
1940+
if ((tableNamePattern !=null) && !tableNamePattern.equals("%")) {
1941+
sql.append(" and c.relname like\'" +tableNamePattern +"\'");
1942+
}
19911943

1992-
tuple[15] =tuple[6];// char octet length
1944+
if ((columnNamePattern !=null) && !columnNamePattern.equals("%")) {
1945+
sql.append(" and a.attname like\'" +columnNamePattern +"\'");
1946+
}
19931947

1994-
tuple[16] =r.getBytes(5);// ordinal position
1948+
sql.append(
1949+
" and a.attnum > 0" +
1950+
" )" +
1951+
" ) inner join pg_type t on" +
1952+
" (" +
1953+
" t.oid = a.atttypid" +
1954+
" )" +
1955+
" )" +
1956+
" left outer join pg_attrdef d on" +
1957+
" (" +
1958+
" c.oid = d.adrelid" +
1959+
" and a.attnum = d.adnum" +
1960+
" )" +
1961+
" left outer join pg_description e on" +
1962+
" (" +
1963+
" e.objoid = a.attrelid");
1964+
1965+
if (connection.haveMinimumServerVersion("7.2")) {
1966+
sql.append(
1967+
" and e.objsubid = a.attnum" +
1968+
" and e.classoid = (select oid from pg_class where relname =\'pg_class\')");
1969+
}
19951970

1996-
StringnullFlag =r.getString(6);
1997-
tuple[10] =Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes();// Nullable
1998-
tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes();// is nullable
1971+
sql.append(
1972+
" ) " +
1973+
"order by" +
1974+
" c.relname, a.attnum");
1975+
1976+
System.out.println(sql);
1977+
1978+
java.sql.ResultSetr =connection.ExecSQL(sql.toString());
1979+
while (r.next()) {
1980+
byte[][]tuple =newbyte[18][0];
1981+
1982+
StringnullFlag =r.getString(6);
1983+
Stringtypname =r.getString(10);
1984+
1985+
tuple[0] ="".getBytes();// Catalog name
1986+
tuple[1] ="".getBytes();// Schema name
1987+
tuple[2] =r.getBytes(2);// Table name
1988+
tuple[3] =r.getBytes(3);// Column name
1989+
tuple[4] =Integer.toString(connection.getSQLType(typname)).getBytes();// Data type
1990+
tuple[5] =typname.getBytes();// Type name
1991+
1992+
// Column size
1993+
// Looking at the psql source,
1994+
// I think the length of a varchar as specified when the table was created
1995+
// should be extracted from atttypmod which contains this length + sizeof(int32)
1996+
if (typname.equals("bpchar") ||typname.equals("varchar")) {
1997+
intatttypmod =r.getInt(8);
1998+
tuple[6] =Integer.toString(atttypmod != -1 ?atttypmod -VARHDRSZ :0).getBytes();
1999+
}else {
2000+
tuple[6] =r.getBytes(7);
2001+
}
2002+
2003+
tuple[7] =null;// Buffer length
2004+
tuple[8] ="0".getBytes();// Decimal Digits - how to get this?
2005+
tuple[9] ="10".getBytes();// Num Prec Radix - assume decimal
2006+
tuple[10] =Integer.toString(nullFlag.equals("f") ?
2007+
java.sql.DatabaseMetaData.columnNullable :
2008+
java.sql.DatabaseMetaData.columnNoNulls).getBytes();// Nullable
2009+
tuple[11] =r.getBytes(11);// Description (if any)
2010+
tuple[12] =r.getBytes(9);// Column default
2011+
tuple[13] =null;// sql data type (unused)
2012+
tuple[14] =null;// sql datetime sub (unused)
2013+
tuple[15] =tuple[6];// char octet length
2014+
tuple[16] =r.getBytes(5);// ordinal position
2015+
tuple[17] = (nullFlag.equals("f") ?"YES" :"NO").getBytes();// Is nullable
2016+
2017+
v.addElement(tuple);
2018+
}
2019+
r.close();
19992020

2000-
v.addElement(tuple);
2001-
}
2002-
r.close();
20032021
returnnewResultSet(connection,f,v,"OK",1);
20042022
}
2023+
20052024
/**
20062025
* Get a description of the access rights for a table's columns.
20072026
*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp