Re: JDBC patch for DatabaseMetaData

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