Thread: JDBC patch for DatabaseMetaData
The following patches up the DatabaseMetaData.getTables() method:
1638c1638,1641
< if(tableNamePattern==null)
---
> if((schemaPattern==null) || (schemaPattern.length()==0))
> schemaPattern="%";
>
> if((tableNamePattern==null) || (tableNamePattern.length()==0))
1653c1656,1657
< StringBuffer sql = new StringBuffer("select relname,oid,relkind from
pg_class where (");
---
> StringBuffer sql = new StringBuffer(
> "select relname,pg_class.oid,relkind from pg_class, pg_user where
(");
1665a1670
> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
1667,1669c1672,1678
< sql.append(") and relname like '");
< sql.append(tableNamePattern.toLowerCase());
< sql.append("'");
---
> sql.append(") and relname");
> if ((tableNamePattern.indexOf("%") >= 0) ||
> (tableNamePattern.indexOf("_") >= 0))
> sql.append(" like ");
> else
> sql.append(" = ");
> sql.append("'" + tableNamePattern.toLowerCase() + "'");
1670a1680,1690
> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> // Now take the schemaPattern into account
> sql.append(" and pg_class.relowner = pg_user.usesysid");
> sql.append(" and pg_user.usename");
> if ((schemaPattern.indexOf("%") >= 0) ||
> (schemaPattern.indexOf("_") >= 0))
> sql.append(" like ");
> else
> sql.append(" = ");
> sql.append("'" + schemaPattern + "'");
>
1688a1709,1710
> // JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
> // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
1699a1722,1724
> case 'v':
> relKind = "VIEW";
> break;
1707c1732,1740
< tuple[3] = relKind.getBytes(); // Table type
---
>
> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> // Fix NullPointerException if return type is not handled in the
> // above switch statement.
> if (relKind==null)
> tuple[3] = null;
> else
> tuple[3] = relKind.getBytes(); // Table type
>
Ed Yu writes: > The following patches up the DatabaseMetaData.getTables() method: First, you should send patches in diff -c format. Second, you should explain what the patch changes and why that's better. -- Peter Eisentraut peter_e@gmx.net
It appears that this patch is trying to add schema support for the
getTables method. But since postgres doesn't yet support schemas
(perhaps in 7.3), I don't see how this is going to be possible. I
certainly don't agree with the approach here that user = schema. That
may be how Oracle does it, but certainly isn't how the ANSI standard
defines schemas.
thanks,
--Barry
Ed Yu wrote:
> The following patches up the DatabaseMetaData.getTables() method:
>
> 1638c1638,1641
> < if(tableNamePattern==null)
> ---
>
>> if((schemaPattern==null) || (schemaPattern.length()==0))
>> schemaPattern="%";
>>
>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
>>
> 1653c1656,1657
> < StringBuffer sql = new StringBuffer("select relname,oid,relkind from
> pg_class where (");
> ---
>
>> StringBuffer sql = new StringBuffer(
>> "select relname,pg_class.oid,relkind from pg_class, pg_user where
>>
> (");
> 1665a1670
>
>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>
> 1667,1669c1672,1678
> < sql.append(") and relname like '");
> < sql.append(tableNamePattern.toLowerCase());
> < sql.append("'");
> ---
>
>> sql.append(") and relname");
>> if ((tableNamePattern.indexOf("%") >= 0) ||
>> (tableNamePattern.indexOf("_") >= 0))
>> sql.append(" like ");
>> else
>> sql.append(" = ");
>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
>>
> 1670a1680,1690
>
>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>> // Now take the schemaPattern into account
>> sql.append(" and pg_class.relowner = pg_user.usesysid");
>> sql.append(" and pg_user.usename");
>> if ((schemaPattern.indexOf("%") >= 0) ||
>> (schemaPattern.indexOf("_") >= 0))
>> sql.append(" like ");
>> else
>> sql.append(" = ");
>> sql.append("'" + schemaPattern + "'");
>>
>>
> 1688a1709,1710
>
>> // JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
>> // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
>>
> 1699a1722,1724
>
>> case 'v':
>> relKind = "VIEW";
>> break;
>>
> 1707c1732,1740
> < tuple[3] = relKind.getBytes(); // Table type
> ---
>
>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>> // Fix NullPointerException if return type is not handled in the
>> // above switch statement.
>> if (relKind==null)
>> tuple[3] = null;
>> else
>> tuple[3] = relKind.getBytes(); // Table type
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
You are right on that user != schema. But I would rather have a functional
driver now than an compliance driver way down in the future.
As I can recall, the only database I've worked with that supports schema is
UDB (DB2). Schema is a label that groups database objects together. It is
functionally equivalent to a user except that login is not permitted for a
schema (in the context of security/permission granting).
Since Postgresql has the concept of a database, it would be equalvalent to
the concept of catalog and user would be functionally equalvalent to schema.
Again, I prefer to have a working JDBC driver now than a compliance driver
in the future.
What do you think guys?
----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Ed Yu" <ekyu@sc.rr.com>
Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
Sent: Monday, January 07, 2002 12:44 PM
Subject: Re: JDBC patch for DatabaseMetaData
> It appears that this patch is trying to add schema support for the
> getTables method. But since postgres doesn't yet support schemas
> (perhaps in 7.3), I don't see how this is going to be possible. I
> certainly don't agree with the approach here that user = schema. That
> may be how Oracle does it, but certainly isn't how the ANSI standard
> defines schemas.
>
> thanks,
> --Barry
>
> Ed Yu wrote:
>
> > The following patches up the DatabaseMetaData.getTables() method:
> >
> > 1638c1638,1641
> > < if(tableNamePattern==null)
> > ---
> >
> >> if((schemaPattern==null) || (schemaPattern.length()==0))
> >> schemaPattern="%";
> >>
> >> if((tableNamePattern==null) || (tableNamePattern.length()==0))
> >>
> > 1653c1656,1657
> > < StringBuffer sql = new StringBuffer("select relname,oid,relkind
from
> > pg_class where (");
> > ---
> >
> >> StringBuffer sql = new StringBuffer(
> >> "select relname,pg_class.oid,relkind from pg_class, pg_user
where
> >>
> > (");
> > 1665a1670
> >
> >> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>
> > 1667,1669c1672,1678
> > < sql.append(") and relname like '");
> > < sql.append(tableNamePattern.toLowerCase());
> > < sql.append("'");
> > ---
> >
> >> sql.append(") and relname");
> >> if ((tableNamePattern.indexOf("%") >= 0) ||
> >> (tableNamePattern.indexOf("_") >= 0))
> >> sql.append(" like ");
> >> else
> >> sql.append(" = ");
> >> sql.append("'" + tableNamePattern.toLowerCase() + "'");
> >>
> > 1670a1680,1690
> >
> >> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >> // Now take the schemaPattern into account
> >> sql.append(" and pg_class.relowner = pg_user.usesysid");
> >> sql.append(" and pg_user.usename");
> >> if ((schemaPattern.indexOf("%") >= 0) ||
> >> (schemaPattern.indexOf("_") >= 0))
> >> sql.append(" like ");
> >> else
> >> sql.append(" = ");
> >> sql.append("'" + schemaPattern + "'");
> >>
> >>
> > 1688a1709,1710
> >
> >> // JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
> >> // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
> >>
> > 1699a1722,1724
> >
> >> case 'v':
> >> relKind = "VIEW";
> >> break;
> >>
> > 1707c1732,1740
> > < tuple[3] = relKind.getBytes(); // Table type
> > ---
> >
> >> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >> // Fix NullPointerException if return type is not handled in the
> >> // above switch statement.
> >> if (relKind==null)
> >> tuple[3] = null;
> >> else
> >> tuple[3] = relKind.getBytes(); // Table type
> >>
> >>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
>
>
>
Ed,
You stated that "I prefer to have a working JDBC driver now than a
compliance driver in the future." I agree with this statement, but I
don't view making scheam = user as working. That IMHO is introducing a
bug and does not result in a "working" set of functionality. Also it
has the problem that when schema support is added in the future then
there will need to be a non-backwardly compatible change in the behavior
of the driver.
The jdbc spec was written taking into account that different databases
have different functionality. That is why the DatabaseMetaData class
exists, to tell a client what functionality is supported by the server.
The postgres jdbc driver correctly states in the metadata class that
it does not support schemas. Therefore any jdbc compliant client should
not expect schema support.
Your original patch has some good fixes in it, but I won't apply it as
is because of the concerns I have raised about the schema stuff. Can
you resubmit the patch without the schema changes?
thanks,
--Barry
Ed Yu wrote:
> You are right on that user != schema. But I would rather have a functional
> driver now than an compliance driver way down in the future.
>
> As I can recall, the only database I've worked with that supports schema is
> UDB (DB2). Schema is a label that groups database objects together. It is
> functionally equivalent to a user except that login is not permitted for a
> schema (in the context of security/permission granting).
>
> Since Postgresql has the concept of a database, it would be equalvalent to
> the concept of catalog and user would be functionally equalvalent to schema.
>
> Again, I prefer to have a working JDBC driver now than a compliance driver
> in the future.
>
> What do you think guys?
>
> ----- Original Message -----
> From: "Barry Lind" <barry@xythos.com>
> To: "Ed Yu" <ekyu@sc.rr.com>
> Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
> Sent: Monday, January 07, 2002 12:44 PM
> Subject: Re: JDBC patch for DatabaseMetaData
>
>
>
>>It appears that this patch is trying to add schema support for the
>>getTables method. But since postgres doesn't yet support schemas
>>(perhaps in 7.3), I don't see how this is going to be possible. I
>>certainly don't agree with the approach here that user = schema. That
>>may be how Oracle does it, but certainly isn't how the ANSI standard
>>defines schemas.
>>
>>thanks,
>>--Barry
>>
>>Ed Yu wrote:
>>
>>
>>>The following patches up the DatabaseMetaData.getTables() method:
>>>
>>>1638c1638,1641
>>>< if(tableNamePattern==null)
>>>---
>>>
>>>
>>>> if((schemaPattern==null) || (schemaPattern.length()==0))
>>>> schemaPattern="%";
>>>>
>>>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
>>>>
>>>>
>>>1653c1656,1657
>>>< StringBuffer sql = new StringBuffer("select relname,oid,relkind
>>>
> from
>
>>>pg_class where (");
>>>---
>>>
>>>
>>>> StringBuffer sql = new StringBuffer(
>>>> "select relname,pg_class.oid,relkind from pg_class, pg_user
>>>>
> where
>
>>>(");
>>>1665a1670
>>>
>>>
>>>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>
>>>1667,1669c1672,1678
>>>< sql.append(") and relname like '");
>>>< sql.append(tableNamePattern.toLowerCase());
>>>< sql.append("'");
>>>---
>>>
>>>
>>>> sql.append(") and relname");
>>>> if ((tableNamePattern.indexOf("%") >= 0) ||
>>>> (tableNamePattern.indexOf("_") >= 0))
>>>> sql.append(" like ");
>>>> else
>>>> sql.append(" = ");
>>>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
>>>>
>>>>
>>>1670a1680,1690
>>>
>>>
>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>> // Now take the schemaPattern into account
>>>> sql.append(" and pg_class.relowner = pg_user.usesysid");
>>>> sql.append(" and pg_user.usename");
>>>> if ((schemaPattern.indexOf("%") >= 0) ||
>>>> (schemaPattern.indexOf("_") >= 0))
>>>> sql.append(" like ");
>>>> else
>>>> sql.append(" = ");
>>>> sql.append("'" + schemaPattern + "'");
>>>>
>>>>
>>>>
>>>1688a1709,1710
>>>
>>>
>>>>// JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
>>>>// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
>>>>
>>>>
>>>1699a1722,1724
>>>
>>>
>>>>case 'v':
>>>> relKind = "VIEW";
>>>> break;
>>>>
>>>>
>>>1707c1732,1740
>>>< tuple[3] = relKind.getBytes(); // Table type
>>>---
>>>
>>>
>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>// Fix NullPointerException if return type is not handled in the
>>>>// above switch statement.
>>>>if (relKind==null)
>>>> tuple[3] = null;
>>>>else
>>>> tuple[3] = relKind.getBytes(); // Table type
>>>>
>>>>
>>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>
>>>
>>
>>
>
I don't see a problem in the equivalence of users and schemas.
Oracle is another database that supports schemas and the following is
from "Oracle 9i SQL reference" manual (documentation of the "create
schema" statement):
"
...
create_schema ::= CREATE SCHEMA AUTHORIZATION schema ...
...
*schema*
Specify the name of the schema. The schema name must be the same as your
Oracle
username.
...
Note: This statement does not actually create a schema. Oracle
automatically creates a schema when you create a user (see CREATE
USER on page 15-29).
...
"
Paulo Merson
Summa Technologies - www.summa-tech.com
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Barry Lind
Sent: Thursday, January 10, 2002 12:59 PM
To: Ed Yu
Cc: pgsql-jdbc
Subject: Re: [JDBC] JDBC patch for DatabaseMetaData
Ed,
You stated that "I prefer to have a working JDBC driver now than a
compliance driver in the future." I agree with this statement, but I
don't view making scheam = user as working. That IMHO is introducing a
bug and does not result in a "working" set of functionality. Also it
has the problem that when schema support is added in the future then
there will need to be a non-backwardly compatible change in the behavior
of the driver.
The jdbc spec was written taking into account that different databases
have different functionality. That is why the DatabaseMetaData class
exists, to tell a client what functionality is supported by the server.
The postgres jdbc driver correctly states in the metadata class that
it does not support schemas. Therefore any jdbc compliant client should
not expect schema support.
Your original patch has some good fixes in it, but I won't apply it as
is because of the concerns I have raised about the schema stuff. Can
you resubmit the patch without the schema changes?
thanks,
--Barry
Ed Yu wrote:
> You are right on that user != schema. But I would rather have a
functional
> driver now than an compliance driver way down in the future.
>
> As I can recall, the only database I've worked with that supports
schema is
> UDB (DB2). Schema is a label that groups database objects together. It
is
> functionally equivalent to a user except that login is not permitted
for a
> schema (in the context of security/permission granting).
>
> Since Postgresql has the concept of a database, it would be
equalvalent to
> the concept of catalog and user would be functionally equalvalent to
schema.
>
> Again, I prefer to have a working JDBC driver now than a compliance
driver
> in the future.
>
> What do you think guys?
>
> ----- Original Message -----
> From: "Barry Lind" <barry@xythos.com>
> To: "Ed Yu" <ekyu@sc.rr.com>
> Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
> Sent: Monday, January 07, 2002 12:44 PM
> Subject: Re: JDBC patch for DatabaseMetaData
>
>
>
>>It appears that this patch is trying to add schema support for the
>>getTables method. But since postgres doesn't yet support schemas
>>(perhaps in 7.3), I don't see how this is going to be possible. I
>>certainly don't agree with the approach here that user = schema. That
>>may be how Oracle does it, but certainly isn't how the ANSI standard
>>defines schemas.
>>
>>thanks,
>>--Barry
>>
>>Ed Yu wrote:
>>
>>
>>>The following patches up the DatabaseMetaData.getTables() method:
>>>
>>>1638c1638,1641
>>>< if(tableNamePattern==null)
>>>---
>>>
>>>
>>>> if((schemaPattern==null) || (schemaPattern.length()==0))
>>>> schemaPattern="%";
>>>>
>>>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
>>>>
>>>>
>>>1653c1656,1657
>>>< StringBuffer sql = new StringBuffer("select relname,oid,relkind
>>>
> from
>
>>>pg_class where (");
>>>---
>>>
>>>
>>>> StringBuffer sql = new StringBuffer(
>>>> "select relname,pg_class.oid,relkind from pg_class, pg_user
>>>>
> where
>
>>>(");
>>>1665a1670
>>>
>>>
>>>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>
>>>1667,1669c1672,1678
>>>< sql.append(") and relname like '");
>>>< sql.append(tableNamePattern.toLowerCase());
>>>< sql.append("'");
>>>---
>>>
>>>
>>>> sql.append(") and relname");
>>>> if ((tableNamePattern.indexOf("%") >= 0) ||
>>>> (tableNamePattern.indexOf("_") >= 0))
>>>> sql.append(" like ");
>>>> else
>>>> sql.append(" = ");
>>>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
>>>>
>>>>
>>>1670a1680,1690
>>>
>>>
>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>> // Now take the schemaPattern into account
>>>> sql.append(" and pg_class.relowner = pg_user.usesysid");
>>>> sql.append(" and pg_user.usename");
>>>> if ((schemaPattern.indexOf("%") >= 0) ||
>>>> (schemaPattern.indexOf("_") >= 0))
>>>> sql.append(" like ");
>>>> else
>>>> sql.append(" = ");
>>>> sql.append("'" + schemaPattern + "'");
>>>>
>>>>
>>>>
>>>1688a1709,1710
>>>
>>>
>>>>// JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
>>>>// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
>>>>
>>>>
>>>1699a1722,1724
>>>
>>>
>>>>case 'v':
>>>> relKind = "VIEW";
>>>> break;
>>>>
>>>>
>>>1707c1732,1740
>>>< tuple[3] = relKind.getBytes(); // Table type
>>>---
>>>
>>>
>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>// Fix NullPointerException if return type is not handled in the
>>>>// above switch statement.
>>>>if (relKind==null)
>>>> tuple[3] = null;
>>>>else
>>>> tuple[3] = relKind.getBytes(); // Table type
>>>>
>>>>
>>>>
>>>
>>>---------------------------(end of
broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>
>>>
>>
>>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Hey everyone -
Go check the HACKERS archives: how schema map to PostgreSQL DB objects was
hashed out to death, more than once. As it happens, Barry's intution on
this is exactly where we ended up: a Postgresql 'database' is equivalent
to what the SQL92 standard calls a 'catalog'. Schema are essentially a
namespace within a catalog, and will be implemented as such. I know Tom
Lane hasd said that schema support is on his roadmap for early in 7.3.
How 'other' databases implement it is not controlling.
Ross Reedstrom
On Thu, Jan 10, 2002 at 03:51:06PM -0500, Paulo Merson wrote:
> I don't see a problem in the equivalence of users and schemas.
> Oracle is another database that supports schemas and the following is
> from "Oracle 9i SQL reference" manual (documentation of the "create
> schema" statement):
>
> "
> ...
> create_schema ::= CREATE SCHEMA AUTHORIZATION schema ...
> ...
> *schema*
> Specify the name of the schema. The schema name must be the same as your
> Oracle
> username.
> ...
> Note: This statement does not actually create a schema. Oracle
> automatically creates a schema when you create a user (see CREATE
> USER on page 15-29).
> ...
> "
>
>
> Paulo Merson
> Summa Technologies - www.summa-tech.com
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Barry Lind
> Sent: Thursday, January 10, 2002 12:59 PM
> To: Ed Yu
> Cc: pgsql-jdbc
> Subject: Re: [JDBC] JDBC patch for DatabaseMetaData
>
>
> Ed,
>
> You stated that "I prefer to have a working JDBC driver now than a
> compliance driver in the future." I agree with this statement, but I
> don't view making scheam = user as working. That IMHO is introducing a
> bug and does not result in a "working" set of functionality. Also it
> has the problem that when schema support is added in the future then
> there will need to be a non-backwardly compatible change in the behavior
>
> of the driver.
>
> The jdbc spec was written taking into account that different databases
> have different functionality. That is why the DatabaseMetaData class
> exists, to tell a client what functionality is supported by the server.
> The postgres jdbc driver correctly states in the metadata class that
> it does not support schemas. Therefore any jdbc compliant client should
>
> not expect schema support.
>
> Your original patch has some good fixes in it, but I won't apply it as
> is because of the concerns I have raised about the schema stuff. Can
> you resubmit the patch without the schema changes?
>
> thanks,
> --Barry
>
>
>
> Ed Yu wrote:
>
> > You are right on that user != schema. But I would rather have a
> functional
> > driver now than an compliance driver way down in the future.
> >
> > As I can recall, the only database I've worked with that supports
> schema is
> > UDB (DB2). Schema is a label that groups database objects together. It
> is
> > functionally equivalent to a user except that login is not permitted
> for a
> > schema (in the context of security/permission granting).
> >
> > Since Postgresql has the concept of a database, it would be
> equalvalent to
> > the concept of catalog and user would be functionally equalvalent to
> schema.
> >
> > Again, I prefer to have a working JDBC driver now than a compliance
> driver
> > in the future.
> >
> > What do you think guys?
> >
> > ----- Original Message -----
> > From: "Barry Lind" <barry@xythos.com>
> > To: "Ed Yu" <ekyu@sc.rr.com>
> > Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
> > Sent: Monday, January 07, 2002 12:44 PM
> > Subject: Re: JDBC patch for DatabaseMetaData
> >
> >
> >
> >>It appears that this patch is trying to add schema support for the
> >>getTables method. But since postgres doesn't yet support schemas
> >>(perhaps in 7.3), I don't see how this is going to be possible. I
> >>certainly don't agree with the approach here that user = schema. That
> >>may be how Oracle does it, but certainly isn't how the ANSI standard
> >>defines schemas.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Ed Yu wrote:
> >>
> >>
> >>>The following patches up the DatabaseMetaData.getTables() method:
> >>>
> >>>1638c1638,1641
> >>>< if(tableNamePattern==null)
> >>>---
> >>>
> >>>
> >>>> if((schemaPattern==null) || (schemaPattern.length()==0))
> >>>> schemaPattern="%";
> >>>>
> >>>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
> >>>>
> >>>>
> >>>1653c1656,1657
> >>>< StringBuffer sql = new StringBuffer("select relname,oid,relkind
> >>>
> > from
> >
> >>>pg_class where (");
> >>>---
> >>>
> >>>
> >>>> StringBuffer sql = new StringBuffer(
> >>>> "select relname,pg_class.oid,relkind from pg_class, pg_user
> >>>>
> > where
> >
> >>>(");
> >>>1665a1670
> >>>
> >>>
> >>>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>>
> >>>1667,1669c1672,1678
> >>>< sql.append(") and relname like '");
> >>>< sql.append(tableNamePattern.toLowerCase());
> >>>< sql.append("'");
> >>>---
> >>>
> >>>
> >>>> sql.append(") and relname");
> >>>> if ((tableNamePattern.indexOf("%") >= 0) ||
> >>>> (tableNamePattern.indexOf("_") >= 0))
> >>>> sql.append(" like ");
> >>>> else
> >>>> sql.append(" = ");
> >>>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
> >>>>
> >>>>
> >>>1670a1680,1690
> >>>
> >>>
> >>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>> // Now take the schemaPattern into account
> >>>> sql.append(" and pg_class.relowner = pg_user.usesysid");
> >>>> sql.append(" and pg_user.usename");
> >>>> if ((schemaPattern.indexOf("%") >= 0) ||
> >>>> (schemaPattern.indexOf("_") >= 0))
> >>>> sql.append(" like ");
> >>>> else
> >>>> sql.append(" = ");
> >>>> sql.append("'" + schemaPattern + "'");
> >>>>
> >>>>
> >>>>
> >>>1688a1709,1710
> >>>
> >>>
> >>>>// JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
> >>>>// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
> >>>>
> >>>>
> >>>1699a1722,1724
> >>>
> >>>
> >>>>case 'v':
> >>>> relKind = "VIEW";
> >>>> break;
> >>>>
> >>>>
> >>>1707c1732,1740
> >>>< tuple[3] = relKind.getBytes(); // Table type
> >>>---
> >>>
> >>>
> >>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>>// Fix NullPointerException if return type is not handled in the
> >>>>// above switch statement.
> >>>>if (relKind==null)
> >>>> tuple[3] = null;
> >>>>else
> >>>> tuple[3] = relKind.getBytes(); // Table type
> >>>>
> >>>>
> >>>>
> >>>
> >>>---------------------------(end of
> broadcast)---------------------------
> >>>TIP 5: Have you checked our extensive FAQ?
> >>>
> >>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>>
> >>>
> >>>
> >>
> >>
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Barry Lind writes: > It appears that this patch is trying to add schema support for the > getTables method. But since postgres doesn't yet support schemas > (perhaps in 7.3), I don't see how this is going to be possible. I > certainly don't agree with the approach here that user = schema. That > may be how Oracle does it, but certainly isn't how the ANSI standard > defines schemas. It's not how ANSI defines it, but it can be shown to be equivalent. In particular, in Entry Level SQL92, schema names *must* be equal to the name of the user that creates/owns it. Furthermore, you can make the implementation-defined extension that CREATE USER automatically creates a schema for that user in each catalog. Also, make the implementation-defined restriction that you cannot give a name to a database object that is already used in another schema in the same catalog, and the restriction that you cannot explicitly qualify a name with a schema. Note that these are all extensions or restrictions compared to the standard, not changes to defined behaviour. Therefore, a patch that currently considers schema==user is correct and forward-compatible. -- Peter Eisentraut peter_e@gmx.net
> You stated that "I prefer to have a working JDBC driver now than a
> compliance driver in the future." I agree with this statement, but I
> don't view making scheam = user as working. That IMHO is introducing a
> bug and does not result in a "working" set of functionality. Also it
> has the problem that when schema support is added in the future then
> there will need to be a non-backwardly compatible change in the behavior
> of the driver.
>
I agree with that. For MSSQL 7.0 server, schema is not the user. In some of
the replies to this thread, Peter Eisentraut <peter_e@gmx.net> message
contains a statement "Entry Level SQL92, schema names *must* be equal to the
name of the user that creates/owns it" that I was not able to find in the
SQL92 specification. And I have no intention to create a bug that is no
backwardly compatible.
> The jdbc spec was written taking into account that different databases
> have different functionality. That is why the DatabaseMetaData class
> exists, to tell a client what functionality is supported by the server.
> The postgres jdbc driver correctly states in the metadata class that
> it does not support schemas. Therefore any jdbc compliant client should
> not expect schema support.
>
I also concur on the fact that if the jdbc driver correctly states that in
the DatabaseMetadata class, schema is not support, the driver should not use
anything related to schema.
But I have a delima here. I need to use DatabaseMetaData.getTables(...) to
return me all tables that I have access to. Currently, like psql, the jdbc
driver returns the entire list of tables exists in the database whether or
not I have access to them.
Unfortunately, the jdbc driver does not have any other methods for me to
determine if I have access to the tables/views causes my classes to throw an
exception because it is returned in the ResultSet from calling
DatabaseMetaData.getTables() and I do not have select privilege to those
tables/views. The only way out for me (or at least I think) is to:
a) do what I did assuming username is equivalent to schema (which I
agree is bad),
b) re-state the sql query for DatabaseMetaData.getTables() to return me
only tables/views that I have access to.
I can see your point that I should not pick option "a" so I'm seriously
considering option "b".
May be you can help me with this, the problem I have is that the column
'pg_class.relacl' is an array. I simply don't know how to extract out the
array elements such as "=r" (public select) and "username=arwR" (hack, I
don't even know the exact format of this column relacl!) and perform a
comparison to the username so that the getTables() returns only tables/views
that I have access to.
So any input is appreciated!
> Your original patch has some good fixes in it, but I won't apply it as
> is because of the concerns I have raised about the schema stuff. Can
> you resubmit the patch without the schema changes?
>
> thanks,
> --Barry
>
>
Thanks, I'll resubmit the patch once I got a chance.
>
> Ed Yu wrote:
>
> > You are right on that user != schema. But I would rather have a
functional
> > driver now than an compliance driver way down in the future.
> >
> > As I can recall, the only database I've worked with that supports schema
is
> > UDB (DB2). Schema is a label that groups database objects together. It
is
> > functionally equivalent to a user except that login is not permitted for
a
> > schema (in the context of security/permission granting).
> >
> > Since Postgresql has the concept of a database, it would be equalvalent
to
> > the concept of catalog and user would be functionally equalvalent to
schema.
> >
> > Again, I prefer to have a working JDBC driver now than a compliance
driver
> > in the future.
> >
> > What do you think guys?
> >
> > ----- Original Message -----
> > From: "Barry Lind" <barry@xythos.com>
> > To: "Ed Yu" <ekyu@sc.rr.com>
> > Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
> > Sent: Monday, January 07, 2002 12:44 PM
> > Subject: Re: JDBC patch for DatabaseMetaData
> >
> >
> >
> >>It appears that this patch is trying to add schema support for the
> >>getTables method. But since postgres doesn't yet support schemas
> >>(perhaps in 7.3), I don't see how this is going to be possible. I
> >>certainly don't agree with the approach here that user = schema. That
> >>may be how Oracle does it, but certainly isn't how the ANSI standard
> >>defines schemas.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Ed Yu wrote:
> >>
> >>
> >>>The following patches up the DatabaseMetaData.getTables() method:
> >>>
> >>>1638c1638,1641
> >>>< if(tableNamePattern==null)
> >>>---
> >>>
> >>>
> >>>> if((schemaPattern==null) || (schemaPattern.length()==0))
> >>>> schemaPattern="%";
> >>>>
> >>>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
> >>>>
> >>>>
> >>>1653c1656,1657
> >>>< StringBuffer sql = new StringBuffer("select relname,oid,relkind
> >>>
> > from
> >
> >>>pg_class where (");
> >>>---
> >>>
> >>>
> >>>> StringBuffer sql = new StringBuffer(
> >>>> "select relname,pg_class.oid,relkind from pg_class, pg_user
> >>>>
> > where
> >
> >>>(");
> >>>1665a1670
> >>>
> >>>
> >>>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>>
> >>>1667,1669c1672,1678
> >>>< sql.append(") and relname like '");
> >>>< sql.append(tableNamePattern.toLowerCase());
> >>>< sql.append("'");
> >>>---
> >>>
> >>>
> >>>> sql.append(") and relname");
> >>>> if ((tableNamePattern.indexOf("%") >= 0) ||
> >>>> (tableNamePattern.indexOf("_") >= 0))
> >>>> sql.append(" like ");
> >>>> else
> >>>> sql.append(" = ");
> >>>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
> >>>>
> >>>>
> >>>1670a1680,1690
> >>>
> >>>
> >>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>> // Now take the schemaPattern into account
> >>>> sql.append(" and pg_class.relowner = pg_user.usesysid");
> >>>> sql.append(" and pg_user.usename");
> >>>> if ((schemaPattern.indexOf("%") >= 0) ||
> >>>> (schemaPattern.indexOf("_") >= 0))
> >>>> sql.append(" like ");
> >>>> else
> >>>> sql.append(" = ");
> >>>> sql.append("'" + schemaPattern + "'");
> >>>>
> >>>>
> >>>>
> >>>1688a1709,1710
> >>>
> >>>
> >>>>// JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
> >>>>// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
> >>>>
> >>>>
> >>>1699a1722,1724
> >>>
> >>>
> >>>>case 'v':
> >>>> relKind = "VIEW";
> >>>> break;
> >>>>
> >>>>
> >>>1707c1732,1740
> >>>< tuple[3] = relKind.getBytes(); // Table type
> >>>---
> >>>
> >>>
> >>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
> >>>>// Fix NullPointerException if return type is not handled in the
> >>>>// above switch statement.
> >>>>if (relKind==null)
> >>>> tuple[3] = null;
> >>>>else
> >>>> tuple[3] = relKind.getBytes(); // Table type
> >>>>
> >>>>
> >>>>
> >>>
> >>>---------------------------(end of
broadcast)---------------------------
> >>>TIP 5: Have you checked our extensive FAQ?
> >>>
> >>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>>
> >>>
> >>>
> >>
> >>
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
"Ed Yu" <ekyu@sc.rr.com> writes:
> So any input is appreciated!
Well, FWIW, I think:
* It's too late for either feature additions or noncritical bug fixes
in the 7.2 cycle.
* ANSI-compatible schema support in the backend is a very high priority
for 7.3.
So my advice is that the JDBC code should stand pat on this point for
now. If you want to start thinking about how to do schema support
properly, on the assumption that the backend will have schema ability
in 7.3, then by all means start working on that.
regards, tom lane
Ed,
I now understand why you are trying to do this. But I don't see where
the current functionality in the jdbc is not compliant with the jdbc
spec. Does the spec say that data should only be returned that the user
has priviledges to access?
thanks,
--Barry
Ed Yu wrote:
>>You stated that "I prefer to have a working JDBC driver now than a
>>compliance driver in the future." I agree with this statement, but I
>>don't view making scheam = user as working. That IMHO is introducing a
>>bug and does not result in a "working" set of functionality. Also it
>>has the problem that when schema support is added in the future then
>>there will need to be a non-backwardly compatible change in the behavior
>>of the driver.
>>
>>
>
> I agree with that. For MSSQL 7.0 server, schema is not the user. In some of
> the replies to this thread, Peter Eisentraut <peter_e@gmx.net> message
> contains a statement "Entry Level SQL92, schema names *must* be equal to the
> name of the user that creates/owns it" that I was not able to find in the
> SQL92 specification. And I have no intention to create a bug that is no
> backwardly compatible.
>
>
>>The jdbc spec was written taking into account that different databases
>>have different functionality. That is why the DatabaseMetaData class
>>exists, to tell a client what functionality is supported by the server.
>> The postgres jdbc driver correctly states in the metadata class that
>>it does not support schemas. Therefore any jdbc compliant client should
>>not expect schema support.
>>
>>
>
> I also concur on the fact that if the jdbc driver correctly states that in
> the DatabaseMetadata class, schema is not support, the driver should not use
> anything related to schema.
>
> But I have a delima here. I need to use DatabaseMetaData.getTables(...) to
> return me all tables that I have access to. Currently, like psql, the jdbc
> driver returns the entire list of tables exists in the database whether or
> not I have access to them.
>
> Unfortunately, the jdbc driver does not have any other methods for me to
> determine if I have access to the tables/views causes my classes to throw an
> exception because it is returned in the ResultSet from calling
> DatabaseMetaData.getTables() and I do not have select privilege to those
> tables/views. The only way out for me (or at least I think) is to:
>
> a) do what I did assuming username is equivalent to schema (which I
> agree is bad),
> b) re-state the sql query for DatabaseMetaData.getTables() to return me
> only tables/views that I have access to.
>
> I can see your point that I should not pick option "a" so I'm seriously
> considering option "b".
>
> May be you can help me with this, the problem I have is that the column
> 'pg_class.relacl' is an array. I simply don't know how to extract out the
> array elements such as "=r" (public select) and "username=arwR" (hack, I
> don't even know the exact format of this column relacl!) and perform a
> comparison to the username so that the getTables() returns only tables/views
> that I have access to.
>
> So any input is appreciated!
>
>
>>Your original patch has some good fixes in it, but I won't apply it as
>>is because of the concerns I have raised about the schema stuff. Can
>>you resubmit the patch without the schema changes?
>>
>>thanks,
>>--Barry
>>
>>
>>
>
> Thanks, I'll resubmit the patch once I got a chance.
>
>
>>Ed Yu wrote:
>>
>>
>>>You are right on that user != schema. But I would rather have a
>>>
> functional
>
>>>driver now than an compliance driver way down in the future.
>>>
>>>As I can recall, the only database I've worked with that supports schema
>>>
> is
>
>>>UDB (DB2). Schema is a label that groups database objects together. It
>>>
> is
>
>>>functionally equivalent to a user except that login is not permitted for
>>>
> a
>
>>>schema (in the context of security/permission granting).
>>>
>>>Since Postgresql has the concept of a database, it would be equalvalent
>>>
> to
>
>>>the concept of catalog and user would be functionally equalvalent to
>>>
> schema.
>
>>>Again, I prefer to have a working JDBC driver now than a compliance
>>>
> driver
>
>>>in the future.
>>>
>>>What do you think guys?
>>>
>>>----- Original Message -----
>>>From: "Barry Lind" <barry@xythos.com>
>>>To: "Ed Yu" <ekyu@sc.rr.com>
>>>Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
>>>Sent: Monday, January 07, 2002 12:44 PM
>>>Subject: Re: JDBC patch for DatabaseMetaData
>>>
>>>
>>>
>>>
>>>>It appears that this patch is trying to add schema support for the
>>>>getTables method. But since postgres doesn't yet support schemas
>>>>(perhaps in 7.3), I don't see how this is going to be possible. I
>>>>certainly don't agree with the approach here that user = schema. That
>>>>may be how Oracle does it, but certainly isn't how the ANSI standard
>>>>defines schemas.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>Ed Yu wrote:
>>>>
>>>>
>>>>
>>>>>The following patches up the DatabaseMetaData.getTables() method:
>>>>>
>>>>>1638c1638,1641
>>>>>< if(tableNamePattern==null)
>>>>>---
>>>>>
>>>>>
>>>>>
>>>>>> if((schemaPattern==null) || (schemaPattern.length()==0))
>>>>>> schemaPattern="%";
>>>>>>
>>>>>> if((tableNamePattern==null) || (tableNamePattern.length()==0))
>>>>>>
>>>>>>
>>>>>>
>>>>>1653c1656,1657
>>>>>< StringBuffer sql = new StringBuffer("select relname,oid,relkind
>>>>>
>>>>>
>>>from
>>>
>>>
>>>>>pg_class where (");
>>>>>---
>>>>>
>>>>>
>>>>>
>>>>>> StringBuffer sql = new StringBuffer(
>>>>>> "select relname,pg_class.oid,relkind from pg_class, pg_user
>>>>>>
>>>>>>
>>>where
>>>
>>>
>>>>>(");
>>>>>1665a1670
>>>>>
>>>>>
>>>>>
>>>>>> // Modified by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>>>
>>>>>1667,1669c1672,1678
>>>>>< sql.append(") and relname like '");
>>>>>< sql.append(tableNamePattern.toLowerCase());
>>>>>< sql.append("'");
>>>>>---
>>>>>
>>>>>
>>>>>
>>>>>> sql.append(") and relname");
>>>>>> if ((tableNamePattern.indexOf("%") >= 0) ||
>>>>>> (tableNamePattern.indexOf("_") >= 0))
>>>>>> sql.append(" like ");
>>>>>> else
>>>>>> sql.append(" = ");
>>>>>> sql.append("'" + tableNamePattern.toLowerCase() + "'");
>>>>>>
>>>>>>
>>>>>>
>>>>>1670a1680,1690
>>>>>
>>>>>
>>>>>
>>>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>>> // Now take the schemaPattern into account
>>>>>> sql.append(" and pg_class.relowner = pg_user.usesysid");
>>>>>> sql.append(" and pg_user.usename");
>>>>>> if ((schemaPattern.indexOf("%") >= 0) ||
>>>>>> (schemaPattern.indexOf("_") >= 0))
>>>>>> sql.append(" like ");
>>>>>> else
>>>>>> sql.append(" = ");
>>>>>> sql.append("'" + schemaPattern + "'");
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>1688a1709,1710
>>>>>
>>>>>
>>>>>
>>>>>>// JDBC definition for TABLE_TYPE - "TABLE", "VIEW", "SYSTEM TABLE",
>>>>>>// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
>>>>>>
>>>>>>
>>>>>>
>>>>>1699a1722,1724
>>>>>
>>>>>
>>>>>
>>>>>>case 'v':
>>>>>> relKind = "VIEW";
>>>>>> break;
>>>>>>
>>>>>>
>>>>>>
>>>>>1707c1732,1740
>>>>>< tuple[3] = relKind.getBytes(); // Table type
>>>>>---
>>>>>
>>>>>
>>>>>
>>>>>> // Added by Ed Yu <ekyu@asgnet.psc.sc.edu>
>>>>>>// Fix NullPointerException if return type is not handled in the
>>>>>>// above switch statement.
>>>>>>if (relKind==null)
>>>>>>tuple[3] = null;
>>>>>>else
>>>>>>tuple[3] = relKind.getBytes(); // Table type
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>---------------------------(end of
>>>>>
> broadcast)---------------------------
>
>>>>>TIP 5: Have you checked our extensive FAQ?
>>>>>
>>>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>>
>