Re: DatabaseMetaData.getTables() problem

From: Dave Cramer <Dave(at)micro-automation(dot)net>
To: Barry Lind <blind(at)xythos(dot)com>
Cc: Kris Jurka <jurka(at)ejurka(dot)com>, "w(dot)winter" <w(dot)winter(at)logitags(dot)com>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>, pgsql-patches(at)postgresql(dot)org
Subject: Re: DatabaseMetaData.getTables() problem
Date: 2002-11-04 15:09:26
Message-ID: 1036422567.20258.386.camel@inspiron.cramers
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc pgsql-patches

While I fully endorse this patch, I'm willing to bet this is going to
break some code out there.

Regards,

Dave
On Mon, 2002-11-04 at 02:44, Barry Lind wrote:
> Patch applied.
>
>
> Kris Jurka wrote:
> > I have given this some further thought and believe that we should not do
> > any case folding in the driver for DatabaseMetaData calls. We cannot
> > guess what the caller's intention is regarding case handling, so instead
> > the driver provides the necessary methods to let the user know what the
> > database does with regard to case. The following methods allow the
> > caller to do their own case folding if necessary:
> >
> > storesLowerCaseIdentifiers
> > storesLowerCaseQuotedIdentifiers
> > storesMixedCaseIdentifiers
> > storesMixedCaseQuotedIdentifiers
> > storesUpperCaseIdentifiers
> > storesUpperCaseQuotedIdentifiers
> > supportsMixedCaseIdentifiers
> > supportsMixedCaseQuotedIdentifiers
> >
> > The attached patch removes the existing case folding in the driver.
> >
> >
> > Barry Lind wrote:
> >
> >>Kris,
> >>
> >>I can't think of anything better. So yes please submit a patch for this.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Kris Jurka wrote:
> >>
> >>>This has not been fixed. Currently the driver is doing something along
> >>>the lines of
> >>>
> >>>" WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
> >>>
> >>>This masks the problem of a user supplying an uppercase version of a
> >>>tablename that was case folded to lower, but does not work when they
> >>>really want an uppercase table name.
> >>>
> >>>I believe that adding a LOWER() to c.relname is the best temporary
> >>>solution until we figure out a better way to handle case sensitivity.
> >>>
> >>>Barry, If you believe this is the way to go I will work up a patch for
> >>>this an similar places in the driver.
> >>>
> >>>Kris Jurka
> >>>
> >>>
> >>>On Tue, 29 Oct 2002, Barry Lind wrote:
> >>>
> >>>
> >>>
> >>>>Wolfgang,
> >>>>
> >>>>Can you try this test using the 7.3beta3 build of the driver from
> >>>>jdbc.postgresql.org? I think this should be fixed in the latest build.
> >>>>
> >>>>thanks,
> >>>>--Barry
> >>>>
> >>>>
> >>>>w.winter wrote:
> >>>>
> >>>>
> >>>>>Hi,
> >>>>>
> >>>>>on testing our auto-configuration persistence framework ACP against
> >>>>>PostgreSQL we found the following problem:
> >>>>>
> >>>>>There is a table A625431658_Person1, created as quoted mixed case
> >>>>>identifier.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
> >>>>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
> >>>>>{"TABLE"})
> >>>>>--> A625431658_Person1 is NOT in the ResultSet.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
> >>>>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>>>
> >>>>>Seems there is a problem with mixed case ?
> >>>>>
> >>>>>
> >>>>>Wolfgang
> >>>>>
> >>>>>___________________
> >>>>>Dr. Wolfgang Winter
> >>>>>LogiTags Systems
> >>>>>www.logitags.com
> >>>>>
> >>>>>
> >>>>>------------------------------------------------------------------------
> >>>>>
> >>>>>? src/interfaces/jdbc/org/postgresql/Driver.java
> >>>>>Index: src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java
> >>>>>===================================================================
> >>>>>RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
> >>>>>retrieving revision 1.9
> >>>>>diff -c -r1.9 AbstractJdbc1DatabaseMetaData.java
> >>>>>*** src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/10/25 02:54:37 1.9
> >>>>>--- src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/11/02 22:26:58
> >>>>>***************
> >>>>>*** 314,323 ****
> >>>>> * as case sensitive and as a result store them in mixed case?
> >>>>> * A JDBC-Compliant driver will always return false.
> >>>>> *
> >>>>>- * <p>Predicament - what do they mean by "SQL identifiers" - if it
> >>>>>- * means the names of the tables and columns, then the answers
> >>>>>- * given below are correct - otherwise I don't know.
> >>>>>- *
> >>>>> * @return true if so
> >>>>> * @exception SQLException if a database access error occurs
> >>>>> */
> >>>>>--- 314,319 ----
> >>>>>***************
> >>>>>*** 1725,1734 ****
> >>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
> >>>>> " WHERE p.pronamespace=n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>>--- 1721,1730 ----
> >>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
> >>>>> " WHERE p.pronamespace=n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>>***************
> >>>>>*** 1737,1750 ****
> >>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
> >>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> } else {
> >>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
> >>>>> " FROM pg_proc p ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> }
> >>>>>--- 1733,1746 ----
> >>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
> >>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> } else {
> >>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
> >>>>> " FROM pg_proc p ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> }
> >>>>>***************
> >>>>>*** 1822,1831 ****
> >>>>> " FROM pg_catalog.pg_proc p,pg_catalog.pg_namespace n, pg_catalog.pg_type t "+
> >>>>> " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY n.nspname, p.proname ";
> >>>>> } else {
> >>>>>--- 1818,1827 ----
> >>>>> " FROM pg_catalog.pg_proc p,pg_catalog.pg_namespace n, pg_catalog.pg_type t "+
> >>>>> " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY n.nspname, p.proname ";
> >>>>> } else {
> >>>>>***************
> >>>>>*** 1833,1839 ****
> >>>>> " FROM pg_proc p,pg_type t "+
> >>>>> " WHERE p.prorettype=t.oid ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY p.proname ";
> >>>>> }
> >>>>>--- 1829,1835 ----
> >>>>> " FROM pg_proc p,pg_type t "+
> >>>>> " WHERE p.prorettype=t.oid ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY p.proname ";
> >>>>> }
> >>>>>***************
> >>>>>*** 1997,2006 ****
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
> >>>>> " WHERE c.relnamespace = n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (tableNamePattern != null) {
> >>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
> >>>>> } else {
> >>>>>--- 1993,2002 ----
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
> >>>>> " WHERE c.relnamespace = n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (tableNamePattern != null) {
> >>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
> >>>>> } else {
> >>>>>***************
> >>>>>*** 2290,2296 ****
> >>>>> " 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.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "+
> >>>>>--- 2286,2292 ----
> >>>>> " 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)+"' ";
> >>>>> }
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "+
> >>>>>***************
> >>>>>*** 2308,2317 ****
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname,relname,attname ";
> >>>>>
> >>>>>--- 2304,2313 ----
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname,relname,attname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2410,2417 ****
> >>>>>
> >>>>> if (columnNamePattern == null)
> >>>>> columnNamePattern = "%";
> >>>>>- else
> >>>>>- columnNamePattern = columnNamePattern.toLowerCase();
> >>>>>
> >>>>> f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, getMaxNameLength());
> >>>>> f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, getMaxNameLength());
> >>>>>--- 2406,2411 ----
> >>>>>***************
> >>>>>*** 2432,2438 ****
> >>>>> " AND c.relkind = 'r' "+
> >>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
> >>>>>--- 2426,2432 ----
> >>>>> " AND c.relkind = 'r' "+
> >>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
> >>>>>***************
> >>>>>*** 2443,2451 ****
> >>>>> " AND c.relkind = 'r' ";
> >>>>> }
> >>>>>
> >>>>>! sql += " AND c.relname = '"+escapeQuotes(table.toLowerCase())+"' ";
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY attname ";
> >>>>>
> >>>>>--- 2437,2445 ----
> >>>>> " AND c.relkind = 'r' ";
> >>>>> }
> >>>>>
> >>>>>! sql += " AND c.relname = '"+escapeQuotes(table)+"' ";
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY attname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2538,2544 ****
> >>>>> " AND u.usesysid = c.relowner "+
> >>>>> " AND c.relkind = 'r' ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
> >>>>>--- 2532,2538 ----
> >>>>> " AND u.usesysid = c.relowner "+
> >>>>> " AND c.relkind = 'r' ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
> >>>>>***************
> >>>>>*** 2548,2554 ****
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname, relname ";
> >>>>>
> >>>>>--- 2542,2548 ----
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname, relname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2752,2758 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
> >>>>>--- 2746,2752 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
> >>>>>***************
> >>>>>*** 2761,2767 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
> >>>>> where+
> >>>>> " ORDER BY a.attnum ";
> >>>>>
> >>>>>--- 2755,2761 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
> >>>>> where+
> >>>>> " ORDER BY a.attnum ";
> >>>>>
> >>>>>***************
> >>>>>*** 2882,2888 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>--- 2876,2882 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>***************
> >>>>>*** 2896,2902 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
> >>>>> where+
> >>>>> " ORDER BY table_name, pk_name, key_seq";
> >>>>> return connection.createStatement().executeQuery(sql);
> >>>>>--- 2890,2896 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
> >>>>> where+
> >>>>> " ORDER BY table_name, pk_name, key_seq";
> >>>>> return connection.createStatement().executeQuery(sql);
> >>>>>***************
> >>>>>*** 3008,3017 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
> >>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> >>>>> if (primarySchema != null && !"".equals(primarySchema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
> >>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> >>>>>--- 3002,3011 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
> >>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> >>>>> if (primarySchema != null && !"".equals(primarySchema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
> >>>>> }
> >>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
> >>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> >>>>>***************
> >>>>>*** 3052,3061 ****
> >>>>> + where;
> >>>>>
> >>>>> if (primaryTable != null) {
> >>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable.toLowerCase()) + "' ";
> >>>>> }
> >>>>> if (foreignTable != null) {
> >>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable.toLowerCase()) + "' ";
> >>>>> }
> >>>>>
> >>>>> sql += "ORDER BY ";
> >>>>>--- 3046,3055 ----
> >>>>> + where;
> >>>>>
> >>>>> if (primaryTable != null) {
> >>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
> >>>>> }
> >>>>> if (foreignTable != null) {
> >>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> >>>>> }
> >>>>>
> >>>>> sql += "ORDER BY ";
> >>>>>***************
> >>>>>*** 3548,3554 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_index i, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
> >>>>> where = " AND n.oid = ct.relnamespace ";
> >>>>> if (schema != null && ! "".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>--- 3542,3548 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_index i, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
> >>>>> where = " AND n.oid = ct.relnamespace ";
> >>>>> if (schema != null && ! "".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>***************
> >>>>>*** 3573,3579 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
> >>>>> where+
> >>>>>! " AND ct.relname = '"+escapeQuotes(tableName.toLowerCase())+"' ";
> >>>>>
> >>>>> if (unique) {
> >>>>> sql += " AND i.indisunique ";
> >>>>>--- 3567,3573 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
> >>>>> where+
> >>>>>! " AND ct.relname = '"+escapeQuotes(tableName)+"' ";
> >>>>>
> >>>>> if (unique) {
> >>>>> sql += " AND i.indisunique ";
> >>>>>
> >>>>>
> >>>>>------------------------------------------------------------------------
> >>>>>
> >>>>>
> >>>>>---------------------------(end of broadcast)---------------------------
> >>>>>TIP 4: Don't 'kill -9' the postmaster
> >>>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Karl Goldstein 2002-11-04 17:05:52
Previous Message Barry Lind 2002-11-04 07:45:24 Re: not fully fixed

Browse pgsql-patches by date

  From Date Subject
Next Message Barry Lind 2002-11-04 17:49:18 Re: DatabaseMetaData.getTables() problem
Previous Message Barry Lind 2002-11-04 07:45:24 Re: not fully fixed