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

Commit07ce9fe

Browse files
committed
Per the recent discussion there's been some code changes in JDBC's
DatabaseMetaData.getColumn(). I proposed a patch that would change thenumber of queries to find out all columns in a table from 2 * N + 1 to 1 (Nbeing the number of columns reported) by using some outer joins. I alsofixed the fact that getColumns() only returned columns that had a defaultdefined. OTOH, I did not use to change the code required for obtaining acolumn's remarks (by using col_description() for 7.2 and requested by TomLane).Finally, I have found a way to get all the column details in a single query*and* use col_description() for 7.2 servers. A patch is attached. Itoverrules Ren? Pijlman's fix for this that was committed just today, butstill used N + 1 queries (sorry Ren? ;-) )I also fixed the return values for TABLE_CAT and TABLE_SCHEM from "" tonull, to be more standard compliant (and requested in Ren?'s mail found athttp://fts.postgresql.org/db/mw/msg.html?mid=1034253).As always, the JDBC1 version has not been tested as I have no JDK 1.1Jeroen van Vianen
1 parent5b32850 commit07ce9fe

File tree

2 files changed

+216
-196
lines changed

2 files changed

+216
-196
lines changed

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

Lines changed: 108 additions & 98 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
/**
1414
* This class provides information about the database as a whole.
1515
*
16-
* $Id: DatabaseMetaData.java,v 1.32 2001/09/10 14:55:08 momjian Exp $
16+
* $Id: DatabaseMetaData.java,v 1.33 2001/09/29 03:08:01 momjian Exp $
1717
*
1818
* <p>Many of the methods here return lists of information in ResultSets. You
1919
* can use the normal ResultSet methods such as getString and getInt to
@@ -1895,21 +1895,19 @@ public java.sql.ResultSet getTableTypes() throws SQLException
18951895
*/
18961896
publicjava.sql.ResultSetgetColumns(Stringcatalog,StringschemaPattern,StringtableNamePattern,StringcolumnNamePattern)throwsSQLException
18971897
{
1898-
// the field descriptors for the new ResultSet
1899-
Fieldf[] =newField[18];
1900-
java.sql.ResultSetr;// ResultSet for the SQL query that we need to do
19011898
Vectorv =newVector();// The new ResultSet tuple stuff
1902-
1903-
f[0] =newField(connection,"TABLE_CAT",iVarcharOid,32);
1904-
f[1] =newField(connection,"TABLE_SCHEM",iVarcharOid,32);
1905-
f[2] =newField(connection,"TABLE_NAME",iVarcharOid,32);
1906-
f[3] =newField(connection,"COLUMN_NAME",iVarcharOid,32);
1907-
f[4] =newField(connection,"DATA_TYPE",iInt2Oid,2);
1908-
f[5] =newField(connection,"TYPE_NAME",iVarcharOid,32);
1909-
f[6] =newField(connection,"COLUMN_SIZE",iInt4Oid,4);
1910-
f[7] =newField(connection,"BUFFER_LENGTH",iVarcharOid,32);
1911-
f[8] =newField(connection,"DECIMAL_DIGITS",iInt4Oid,4);
1912-
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);
19131911
f[10] =newField(connection,"NULLABLE",iInt4Oid,4);
19141912
f[11] =newField(connection,"REMARKS",iVarcharOid,32);
19151913
f[12] =newField(connection,"COLUMN_DEF",iVarcharOid,32);
@@ -1919,93 +1917,105 @@ public java.sql.ResultSet getColumns(String catalog, String schemaPattern, Strin
19191917
f[16] =newField(connection,"ORDINAL_POSITION",iInt4Oid,4);
19201918
f[17] =newField(connection,"IS_NULLABLE",iVarcharOid,32);
19211919

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

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

2000-
tuple[16] =r.getBytes(5);// ordinal position
1950+
sql.append(
1951+
" and a.attnum > 0" +
1952+
" )" +
1953+
" ) inner join pg_type t on" +
1954+
" (" +
1955+
" t.oid = a.atttypid" +
1956+
" )" +
1957+
" )" +
1958+
" left outer join pg_attrdef d on" +
1959+
" (" +
1960+
" c.oid = d.adrelid" +
1961+
" and a.attnum = d.adnum" +
1962+
" ) ");
1963+
1964+
if (!connection.haveMinimumServerVersion("7.2")) {
1965+
/* Only for 7.1 */
1966+
sql.append(
1967+
" left outer join pg_description e on" +
1968+
" (" +
1969+
" e.objoid = a.oid" +
1970+
" ) ");
1971+
}
20011972

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

2006-
v.addElement(tuple);
2007-
}
2008-
r.close();
20092019
returnnewResultSet(connection,f,v,"OK",1);
20102020
}
20112021

0 commit comments

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