Thread: Problem with getImportedKeys / getExportedKeys

Problem with getImportedKeys / getExportedKeys

From
"Vianen, Jeroen van"
Date:
Hi,

I have a problem with the getImportedKeys() / getExportedKeys() pair in
current JDBC sources.

Given the following data model:

create table users (
    userid integer not null,
    username varchar(50) not null,
    password varchar(50) not null,
    name1 varchar(20) not null,
    name2 varchar(10),
    name3 varchar(40) not null,
    email varchar(50) not null,
--
    constraint pk_users primary key (userid)
);


create table roles (
    rolename varchar(5) not null,
    description varchar(50) not null,
--
    constraint pk_roles primary key (rolename)
);


create table users_roles (
    userid      integer not null,
    rolename    varchar(5) not null,
--
    constraint pk_users_roles primary key (userid, rolename),
    constraint fk_users_roles__userid foreign key (userid) references
users(userid),
    constraint fk_users_roles__rolename foreign key (rolename) references
roles(rolename)
);


I.e. the following datamodel

+-------+      +-------------+      +-------+
| users |-----<| users_roles |>-----| roles |
+-------+      +-------------+      +-------+

and the following Java application (TestKeys.java):

import java.sql.*;

public class TestKeys {
    static public void main(String[] args) {
        if (args.length > 0) {
            String table = args[0].toUpperCase();
            Connection conn = null;
            try {
                Class.forName("org.postgresql.Driver");
                conn =
DriverManager.getConnection("<YOUR_URL_HERE>", "<USERNAME>", "<PASSWORD>");
                DatabaseMetaData dbmd = conn.getMetaData();

                System.out.println("Imported keys:");
                ResultSet rs = dbmd.getImportedKeys(null,
null, table);
                while (rs.next()) {
                    System.out.println("PKTABLE_NAME = "
+ rs.getString("PKTABLE_NAME"));
                    System.out.println("PKCOLUMN_NAME =
" + rs.getString("PKCOLUMN_NAME"));
                    System.out.println("FKTABLE_NAME = "
+ rs.getString("FKTABLE_NAME"));
                    System.out.println("FKCOLUMN_NAME =
" + rs.getString("FKCOLUMN_NAME"));
                }
                rs.close();

                System.out.println("Exported keys:");
                rs = dbmd.getExportedKeys(null, null,
table);
                while (rs.next()) {
                    System.out.println("PKTABLE_NAME = "
+ rs.getString("PKTABLE_NAME"));
                    System.out.println("PKCOLUMN_NAME =
" + rs.getString("PKCOLUMN_NAME"));
                    System.out.println("FKTABLE_NAME = "
+ rs.getString("FKTABLE_NAME"));
                    System.out.println("FKCOLUMN_NAME =
" + rs.getString("FKCOLUMN_NAME"));
                }
                rs.close();
                conn.close();
            } catch (Exception ex) {
                ex.printStackTrace(System.err);
            }
            System.exit(0);
        } else {
            System.err.println("Please specify a tablename");
        }
    }
}



In Oracle I get:

# java -classpath classes12.zip:. TestKeys users
Imported keys:
Exported keys:
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID

# java -classpath classes12.zip:. TestKeys users_roles
Imported keys:
PKTABLE_NAME = ROLES
PKCOLUMN_NAME = ROLENAME
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = ROLENAME
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID
Exported keys:


In PostgreSQL 7.2 (current sources) I get:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
Imported keys:
Exported keys:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
Imported keys:
Exported keys:

As might be guessed, Oracle is right here, and PostgreSQL is not. I do
recall that this once worked in a previous version of the 7.2 development
sources.

Any clue?


Jeroen

Re: Problem with getImportedKeys / getExportedKeys

From
"Dave Cramer"
Date:
Jeroen,

Thanks for the great test case, I will have a look as soon as possible.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Vianen, Jeroen van
Sent: Monday, January 14, 2002 8:06 AM
To: pgsql-jdbc
Subject: [JDBC] Problem with getImportedKeys / getExportedKeys


Hi,

I have a problem with the getImportedKeys() / getExportedKeys() pair in
current JDBC sources.

Given the following data model:

create table users (
    userid integer not null,
    username varchar(50) not null,
    password varchar(50) not null,
    name1 varchar(20) not null,
    name2 varchar(10),
    name3 varchar(40) not null,
    email varchar(50) not null,
--
    constraint pk_users primary key (userid)
);


create table roles (
    rolename varchar(5) not null,
    description varchar(50) not null,
--
    constraint pk_roles primary key (rolename)
);


create table users_roles (
    userid      integer not null,
    rolename    varchar(5) not null,
--
    constraint pk_users_roles primary key (userid, rolename),
    constraint fk_users_roles__userid foreign key (userid) references
users(userid),
    constraint fk_users_roles__rolename foreign key (rolename)
references
roles(rolename)
);


I.e. the following datamodel

+-------+      +-------------+      +-------+
| users |-----<| users_roles |>-----| roles |
+-------+      +-------------+      +-------+

and the following Java application (TestKeys.java):

import java.sql.*;

public class TestKeys {
    static public void main(String[] args) {
        if (args.length > 0) {
            String table = args[0].toUpperCase();
            Connection conn = null;
            try {
                Class.forName("org.postgresql.Driver");
                conn =
DriverManager.getConnection("<YOUR_URL_HERE>", "<USERNAME>",
"<PASSWORD>");
                DatabaseMetaData dbmd =
conn.getMetaData();

                System.out.println("Imported keys:");
                ResultSet rs =
dbmd.getImportedKeys(null,
null, table);
                while (rs.next()) {
                    System.out.println("PKTABLE_NAME
= "
+ rs.getString("PKTABLE_NAME"));

System.out.println("PKCOLUMN_NAME =
" + rs.getString("PKCOLUMN_NAME"));
                    System.out.println("FKTABLE_NAME
= "
+ rs.getString("FKTABLE_NAME"));

System.out.println("FKCOLUMN_NAME =
" + rs.getString("FKCOLUMN_NAME"));
                }
                rs.close();

                System.out.println("Exported keys:");
                rs = dbmd.getExportedKeys(null, null,
table);
                while (rs.next()) {
                    System.out.println("PKTABLE_NAME
= "
+ rs.getString("PKTABLE_NAME"));

System.out.println("PKCOLUMN_NAME =
" + rs.getString("PKCOLUMN_NAME"));
                    System.out.println("FKTABLE_NAME
= "
+ rs.getString("FKTABLE_NAME"));

System.out.println("FKCOLUMN_NAME =
" + rs.getString("FKCOLUMN_NAME"));
                }
                rs.close();
                conn.close();
            } catch (Exception ex) {
                ex.printStackTrace(System.err);
            }
            System.exit(0);
        } else {
            System.err.println("Please specify a
tablename");
        }
    }
}



In Oracle I get:

# java -classpath classes12.zip:. TestKeys users
Imported keys:
Exported keys:
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID

# java -classpath classes12.zip:. TestKeys users_roles
Imported keys:
PKTABLE_NAME = ROLES
PKCOLUMN_NAME = ROLENAME
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = ROLENAME
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID
Exported keys:


In PostgreSQL 7.2 (current sources) I get:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
Imported keys:
Exported keys:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles Imported
keys: Exported keys:

As might be guessed, Oracle is right here, and PostgreSQL is not. I do
recall that this once worked in a previous version of the 7.2
development sources.

Any clue?


Jeroen

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Re: Problem with getImportedKeys / getExportedKeys

From
Jason Davies
Date:
Hi,

I noticed a couple of things. First of all, there is a small bug in the code which can be easily fixed. The other thing
isthat your program converts table names to uppercase. I've written the code so it is expecting case-sensitive names.
Shouldthis be changed - I'm not up to date on whether things should all be converted to lowercase or not- ? Once this
isresolved I'll issue a patch :-) 

Thanks,

Jason Davies

On Mon, Jan 14, 2002 at 02:05:52PM +0100, Vianen, Jeroen van wrote:
> Hi,
>
> I have a problem with the getImportedKeys() / getExportedKeys() pair in
> current JDBC sources.
>
> Given the following data model:
>
> create table users (
>     userid integer not null,
>     username varchar(50) not null,
>     password varchar(50) not null,
>     name1 varchar(20) not null,
>     name2 varchar(10),
>     name3 varchar(40) not null,
>     email varchar(50) not null,
> --
>     constraint pk_users primary key (userid)
> );
>
>
> create table roles (
>     rolename varchar(5) not null,
>     description varchar(50) not null,
> --
>     constraint pk_roles primary key (rolename)
> );
>
>
> create table users_roles (
>     userid      integer not null,
>     rolename    varchar(5) not null,
> --
>     constraint pk_users_roles primary key (userid, rolename),
>     constraint fk_users_roles__userid foreign key (userid) references
> users(userid),
>     constraint fk_users_roles__rolename foreign key (rolename) references
> roles(rolename)
> );
>
>
> I.e. the following datamodel
>
> +-------+      +-------------+      +-------+
> | users |-----<| users_roles |>-----| roles |
> +-------+      +-------------+      +-------+
>
> and the following Java application (TestKeys.java):
>
> import java.sql.*;
>
> public class TestKeys {
>     static public void main(String[] args) {
>         if (args.length > 0) {
>             String table = args[0].toUpperCase();
>             Connection conn = null;
>             try {
>                 Class.forName("org.postgresql.Driver");
>                 conn =
> DriverManager.getConnection("<YOUR_URL_HERE>", "<USERNAME>", "<PASSWORD>");
>                 DatabaseMetaData dbmd = conn.getMetaData();
>
>                 System.out.println("Imported keys:");
>                 ResultSet rs = dbmd.getImportedKeys(null,
> null, table);
>                 while (rs.next()) {
>                     System.out.println("PKTABLE_NAME = "
> + rs.getString("PKTABLE_NAME"));
>                     System.out.println("PKCOLUMN_NAME =
> " + rs.getString("PKCOLUMN_NAME"));
>                     System.out.println("FKTABLE_NAME = "
> + rs.getString("FKTABLE_NAME"));
>                     System.out.println("FKCOLUMN_NAME =
> " + rs.getString("FKCOLUMN_NAME"));
>                 }
>                 rs.close();
>
>                 System.out.println("Exported keys:");
>                 rs = dbmd.getExportedKeys(null, null,
> table);
>                 while (rs.next()) {
>                     System.out.println("PKTABLE_NAME = "
> + rs.getString("PKTABLE_NAME"));
>                     System.out.println("PKCOLUMN_NAME =
> " + rs.getString("PKCOLUMN_NAME"));
>                     System.out.println("FKTABLE_NAME = "
> + rs.getString("FKTABLE_NAME"));
>                     System.out.println("FKCOLUMN_NAME =
> " + rs.getString("FKCOLUMN_NAME"));
>                 }
>                 rs.close();
>                 conn.close();
>             } catch (Exception ex) {
>                 ex.printStackTrace(System.err);
>             }
>             System.exit(0);
>         } else {
>             System.err.println("Please specify a tablename");
>         }
>     }
> }
>
>
>
> In Oracle I get:
>
> # java -classpath classes12.zip:. TestKeys users
> Imported keys:
> Exported keys:
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
>
> # java -classpath classes12.zip:. TestKeys users_roles
> Imported keys:
> PKTABLE_NAME = ROLES
> PKCOLUMN_NAME = ROLENAME
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = ROLENAME
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
> Exported keys:
>
>
> In PostgreSQL 7.2 (current sources) I get:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
> Imported keys:
> Exported keys:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
> Imported keys:
> Exported keys:
>
> As might be guessed, Oracle is right here, and PostgreSQL is not. I do
> recall that this once worked in a previous version of the 7.2 development
> sources.
>
> Any clue?
>
>
> Jeroen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Jason Davies

jason@netspade.com

Re: Problem with getImportedKeys / getExportedKeys

From
"Vianen, Jeroen van"
Date:
> -----Original Message-----
> From: Jason Davies [mailto:jason@netspade.com]
> Sent: Monday, January 14, 2002 21:12
> To: Vianen, Jeroen van
> Cc: PostgreSQL JDBC
> Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys
>
>
> Hi,
>
> I noticed a couple of things. First of all, there is a small
> bug in the code which can be easily fixed. The other thing is
> that your program converts table names to uppercase. I've
> written the code so it is expecting case-sensitive names.
> Should this be changed - I'm not up to date on whether things
> should all be converted to lowercase or not- ? Once this is
> resolved I'll issue a patch :-)

Oh, that was to get it working on Oracle :-( But glad you pointed it out,
'cause the output is way different now:

PostgreSQL says:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
Imported keys:
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid
Exported keys:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
Imported keys:
Exported keys:
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid


Whereas Oracle says:

# java -classpath classes12.zip:. TestKeys USERS
Imported keys:
Exported keys:
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID

# java -classpath classes12.zip:. TestKeys USERS_ROLES
Imported keys:
PKTABLE_NAME = ROLES
PKCOLUMN_NAME = ROLENAME
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = ROLENAME
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID
Exported keys:


The new TestKeys.java is attached.

Cheers,


Jeroen


Attachment

Re: Problem with getImportedKeys / getExportedKeys

From
"Vianen, Jeroen van"
Date:
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Tuesday, January 15, 2002 14:37
> To: 'Vianen, Jeroen van'; 'Jason Davies'
> Cc: 'PostgreSQL JDBC'
> Subject: RE: [JDBC] Problem with getImportedKeys / getExportedKeys
>
>
> So the big question is?
>
> How should postgres be handling this? Should we do a case-insensitive
> search?

Maybe.

Please look closely which columns are reported as PK or as FK in my new
PostgreSQL output, and whether they are reported as imported or exported
keys. There are a lot of differences ;-)

> PostgreSQL says:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
> Imported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> Exported keys:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
> Imported keys:
> Exported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
>
>
> Whereas Oracle says:
>
> # java -classpath classes12.zip:. TestKeys USERS
> Imported keys:
> Exported keys:
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
>
> # java -classpath classes12.zip:. TestKeys USERS_ROLES
> Imported keys:
> PKTABLE_NAME = ROLES
> PKCOLUMN_NAME = ROLENAME
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = ROLENAME
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
> Exported keys:

Cheers,


Jeroen

Re: Problem with getImportedKeys / getExportedKeys

From
"Dave Cramer"
Date:
So the big question is?

How should postgres be handling this? Should we do a case-insensitive
search?

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Vianen, Jeroen van
Sent: Tuesday, January 15, 2002 3:50 AM
To: 'Jason Davies'; Vianen, Jeroen van
Cc: PostgreSQL JDBC
Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys


> -----Original Message-----
> From: Jason Davies [mailto:jason@netspade.com]
> Sent: Monday, January 14, 2002 21:12
> To: Vianen, Jeroen van
> Cc: PostgreSQL JDBC
> Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys
>
>
> Hi,
>
> I noticed a couple of things. First of all, there is a small
> bug in the code which can be easily fixed. The other thing is
> that your program converts table names to uppercase. I've
> written the code so it is expecting case-sensitive names.
> Should this be changed - I'm not up to date on whether things
> should all be converted to lowercase or not- ? Once this is
> resolved I'll issue a patch :-)

Oh, that was to get it working on Oracle :-( But glad you pointed it
out, 'cause the output is way different now:

PostgreSQL says:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
Imported keys:
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid
Exported keys:

# java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles Imported
keys: Exported keys: PKTABLE_NAME = users_roles PKCOLUMN_NAME = userid
FKTABLE_NAME = users FKCOLUMN_NAME = userid PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid FKTABLE_NAME = users FKCOLUMN_NAME = userid


Whereas Oracle says:

# java -classpath classes12.zip:. TestKeys USERS
Imported keys:
Exported keys:
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID

# java -classpath classes12.zip:. TestKeys USERS_ROLES
Imported keys:
PKTABLE_NAME = ROLES
PKCOLUMN_NAME = ROLENAME
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = ROLENAME
PKTABLE_NAME = USERS
PKCOLUMN_NAME = USERID
FKTABLE_NAME = USERS_ROLES
FKCOLUMN_NAME = USERID
Exported keys:


The new TestKeys.java is attached.

Cheers,


Jeroen



Re: Problem with getImportedKeys / getExportedKeys

From
Jason Davies
Date:
Yes, at the moment they are the wrong way round because of a misinterpretation on my part. Easily fixed, by changing
theSQL part so that the Java code reads: 

...

+ ((primaryTable != null) ? "AND c.relname='" + primaryTable + "' " : "")
+ ((foreignTable != null) ? "AND c2.relname='" + foreignTable + "' " : "")

...

I've attached a patch for this for now. But maybe case-sensitive stuff needs to be resolved?

Thanks,
Jason Davies

>
> Oh, that was to get it working on Oracle :-( But glad you pointed it out,
> 'cause the output is way different now:
>
> PostgreSQL says:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
> Imported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> Exported keys:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
> Imported keys:
> Exported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
>
>
> Whereas Oracle says:
>
> # java -classpath classes12.zip:. TestKeys USERS
> Imported keys:
> Exported keys:
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
>
> # java -classpath classes12.zip:. TestKeys USERS_ROLES
> Imported keys:
> PKTABLE_NAME = ROLES
> PKCOLUMN_NAME = ROLENAME
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = ROLENAME
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
> Exported keys:
>
>
> The new TestKeys.java is attached.
>
> Cheers,
>
>
> Jeroen
>


>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


--
Jason Davies

jason@netspade.com

Attachment

Re: Problem with getImportedKeys / getExportedKeys

From
"Vianen, Jeroen van"
Date:
Jason,

Thanks for the patch. I will try it out soon.

Regarding the case sensitivity, I think this is an issue that needs to be
resolved in many more places in JDBC (if at all).

Thanks,


Jeroen

-----Original Message-----
From: Jason Davies
To: Vianen, Jeroen van
Cc: PostgreSQL JDBC
Sent: 15-1-02 21:26
Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys

Yes, at the moment they are the wrong way round because of a
misinterpretation on my part. Easily fixed, by changing the SQL part so
that the Java code reads:

...

+ ((primaryTable != null) ? "AND c.relname='" + primaryTable + "' " :
"")
+ ((foreignTable != null) ? "AND c2.relname='" + foreignTable + "' " :
"")

...

I've attached a patch for this for now. But maybe case-sensitive stuff
needs to be resolved?

Thanks,
Jason Davies

Re: Problem with getImportedKeys / getExportedKeys

From
Jeroen van Vianen
Date:
Hi Jason,

At 20:06 01-16-2002 +0100, Vianen, Jeroen van wrote:
>Thanks for the patch. I will try it out soon.

I've tried your patch and the output is as follows:

java -classpath .:postgresql.jar TestKeys users
Imported keys:
Exported keys:
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid

# java -classpath .:postgresql.jar TestKeys users_roles
Imported keys:
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid
PKTABLE_NAME = users_roles
PKCOLUMN_NAME = userid
FKTABLE_NAME = users
FKCOLUMN_NAME = userid
Exported keys:

Comparing this with Oracle's output I noticed that all PK* and FK* columns
have been exchanged and that the two imported keys for table users_roles
are the same. Oracle correctly reports a key from users_roles to users and
from users_roles to roles.

Any clue?


Jeroen


Re: Problem with getImportedKeys / getExportedKeys

From
Jason Davies
Date:
Hi,

I've fixed it completely now (I think) - it took more changes than I thought would be necessary. Here is a new patch
(againstcurrent CVS). Once it has been tested it should be commited to CVS as it is quite an important fix. 

This patch makes getImportedKeys() and getExportedKeys() report the correct relationships (they were the wrong way
roundbefore) and correct number of keys. 

Thanks,
Jason Davies

On Thu, Jan 17, 2002 at 08:00:48PM +0100, Jeroen van Vianen wrote:
> Hi Jason,
>
> At 20:06 01-16-2002 +0100, Vianen, Jeroen van wrote:
> >Thanks for the patch. I will try it out soon.
>
> I've tried your patch and the output is as follows:
>
> java -classpath .:postgresql.jar TestKeys users
> Imported keys:
> Exported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
>
> # java -classpath .:postgresql.jar TestKeys users_roles
> Imported keys:
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> PKTABLE_NAME = users_roles
> PKCOLUMN_NAME = userid
> FKTABLE_NAME = users
> FKCOLUMN_NAME = userid
> Exported keys:
>
> Comparing this with Oracle's output I noticed that all PK* and FK* columns
> have been exchanged and that the two imported keys for table users_roles
> are the same. Oracle correctly reports a key from users_roles to users and
> from users_roles to roles.
>
> Any clue?
>
>
> Jeroen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
Jason Davies

jason@netspade.com

Attachment

Re: Problem with getImportedKeys / getExportedKeys

From
Justin Clift
Date:
Hi Bruce,

Can we get this tested and committed before RC1 is released?

All of the Database Admin/Management tools out there which use JDBC and
support RI need this in order to work correctly.

Making a point of it as I think it's pretty important.

:-)

Regards and best wishes,

Justin Clift


Jason Davies wrote:
>
> Hi,
>
> I've fixed it completely now (I think) - it took more changes than I thought would be necessary. Here is a new patch
(againstcurrent CVS). Once it has been tested it should be commited to CVS as it is quite an important fix. 
>
> This patch makes getImportedKeys() and getExportedKeys() report the correct relationships (they were the wrong way
roundbefore) and correct number of keys. 
>
> Thanks,
> Jason Davies
>
> On Thu, Jan 17, 2002 at 08:00:48PM +0100, Jeroen van Vianen wrote:
> > Hi Jason,
> >
> > At 20:06 01-16-2002 +0100, Vianen, Jeroen van wrote:
> > >Thanks for the patch. I will try it out soon.
> >
> > I've tried your patch and the output is as follows:
> >
> > java -classpath .:postgresql.jar TestKeys users
> > Imported keys:
> > Exported keys:
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> >
> > # java -classpath .:postgresql.jar TestKeys users_roles
> > Imported keys:
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > Exported keys:
> >
> > Comparing this with Oracle's output I noticed that all PK* and FK* columns
> > have been exchanged and that the two imported keys for table users_roles
> > are the same. Oracle correctly reports a key from users_roles to users and
> > from users_roles to roles.
> >
> > Any clue?
> >
> >
> > Jeroen
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> --
> Jason Davies
>
> jason@netspade.com
>
>   ------------------------------------------------------------------------
>
>    DatabaseMetaData.java.diffName: DatabaseMetaData.java.diff
>                              Type: Plain Text (text/plain)
>
>   ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

Re: Problem with getImportedKeys / getExportedKeys

From
Jeroen van Vianen
Date:
Hi,

At 18:06 01-17-2002 -0600, you wrote:
>I've fixed it completely now (I think) - it took more changes than I
>thought would be necessary. Here is a new patch (against current CVS).
>Once it has been tested it should be commited to CVS as it is quite an
>important fix.
>
>This patch makes getImportedKeys() and getExportedKeys() report the
>correct relationships (they were the wrong way round before) and correct
>number of keys.

Yes, this is the correct output that I expected. As Justin Clift noticed in
a nearby mail this patch should be in 7.2. Correct JDBC
get{Im,Ex}portedKeys are very important for many DB tools.

Maybe some other people should test this patch as well.

Thanks,


Jeroen


Re: Problem with getImportedKeys / getExportedKeys

From
"Dave Cramer"
Date:
I will try to patch this sometime today

DAve

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Justin Clift
Sent: Friday, January 18, 2002 4:38 AM
To: Bruce Momjian
Cc: Jason Davies; Jeroen van Vianen; PostgreSQL JDBC
Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys


Hi Bruce,

Can we get this tested and committed before RC1 is released?

All of the Database Admin/Management tools out there which use JDBC and
support RI need this in order to work correctly.

Making a point of it as I think it's pretty important.

:-)

Regards and best wishes,

Justin Clift


Jason Davies wrote:
>
> Hi,
>
> I've fixed it completely now (I think) - it took more changes than I
> thought would be necessary. Here is a new patch (against current CVS).

> Once it has been tested it should be commited to CVS as it is quite an

> important fix.
>
> This patch makes getImportedKeys() and getExportedKeys() report the
> correct relationships (they were the wrong way round before) and
> correct number of keys.
>
> Thanks,
> Jason Davies
>
> On Thu, Jan 17, 2002 at 08:00:48PM +0100, Jeroen van Vianen wrote:
> > Hi Jason,
> >
> > At 20:06 01-16-2002 +0100, Vianen, Jeroen van wrote:
> > >Thanks for the patch. I will try it out soon.
> >
> > I've tried your patch and the output is as follows:
> >
> > java -classpath .:postgresql.jar TestKeys users
> > Imported keys:
> > Exported keys:
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> >
> > # java -classpath .:postgresql.jar TestKeys users_roles Imported
> > keys: PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > Exported keys:
> >
> > Comparing this with Oracle's output I noticed that all PK* and FK*
> > columns have been exchanged and that the two imported keys for table

> > users_roles are the same. Oracle correctly reports a key from
> > users_roles to users and from users_roles to roles.
> >
> > Any clue?
> >
> >
> > Jeroen
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> --
> Jason Davies
>
> jason@netspade.com
>
>
> ----------------------------------------------------------------------
> --
>
>    DatabaseMetaData.java.diffName: DatabaseMetaData.java.diff
>                              Type: Plain Text (text/plain)
>
>
> ----------------------------------------------------------------------
> --
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Re: Problem with getImportedKeys / getExportedKeys

From
Bruce Momjian
Date:
Justin Clift wrote:
> Hi Bruce,
>
> Can we get this tested and committed before RC1 is released?
>
> All of the Database Admin/Management tools out there which use JDBC and
> support RI need this in order to work correctly.
>
> Making a point of it as I think it's pretty important.

Looks like Dave has this handled.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Problem with getImportedKeys / getExportedKeys

From
"Dave Cramer"
Date:
This patch has been committed.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: Friday, January 18, 2002 8:34 AM
To: 'Justin Clift'; 'Bruce Momjian'
Cc: 'Jason Davies'; 'Jeroen van Vianen'; 'PostgreSQL JDBC'
Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys


I will try to patch this sometime today

DAve

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Justin Clift
Sent: Friday, January 18, 2002 4:38 AM
To: Bruce Momjian
Cc: Jason Davies; Jeroen van Vianen; PostgreSQL JDBC
Subject: Re: [JDBC] Problem with getImportedKeys / getExportedKeys


Hi Bruce,

Can we get this tested and committed before RC1 is released?

All of the Database Admin/Management tools out there which use JDBC and
support RI need this in order to work correctly.

Making a point of it as I think it's pretty important.

:-)

Regards and best wishes,

Justin Clift


Jason Davies wrote:
>
> Hi,
>
> I've fixed it completely now (I think) - it took more changes than I
> thought would be necessary. Here is a new patch (against current CVS).

> Once it has been tested it should be commited to CVS as it is quite an

> important fix.
>
> This patch makes getImportedKeys() and getExportedKeys() report the
> correct relationships (they were the wrong way round before) and
> correct number of keys.
>
> Thanks,
> Jason Davies
>
> On Thu, Jan 17, 2002 at 08:00:48PM +0100, Jeroen van Vianen wrote:
> > Hi Jason,
> >
> > At 20:06 01-16-2002 +0100, Vianen, Jeroen van wrote:
> > >Thanks for the patch. I will try it out soon.
> >
> > I've tried your patch and the output is as follows:
> >
> > java -classpath .:postgresql.jar TestKeys users
> > Imported keys:
> > Exported keys:
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> >
> > # java -classpath .:postgresql.jar TestKeys users_roles Imported
> > keys: PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > PKTABLE_NAME = users_roles
> > PKCOLUMN_NAME = userid
> > FKTABLE_NAME = users
> > FKCOLUMN_NAME = userid
> > Exported keys:
> >
> > Comparing this with Oracle's output I noticed that all PK* and FK*
> > columns have been exchanged and that the two imported keys for table

> > users_roles are the same. Oracle correctly reports a key from
> > users_roles to users and from users_roles to roles.
> >
> > Any clue?
> >
> >
> > Jeroen
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> --
> Jason Davies
>
> jason@netspade.com
>
>
> ----------------------------------------------------------------------
> --
>
>    DatabaseMetaData.java.diffName: DatabaseMetaData.java.diff
>                              Type: Plain Text (text/plain)
>
>
> ----------------------------------------------------------------------
> --
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Re: Problem with getImportedKeys / getExportedKeys

From
Justin Clift
Date:
Thanks Dave.

This should mean that 7.2 will be the first release of PostgreSQL which
"Officially" works properly with Java tools such as DbVisualizer, etc.

That should make it easier for people to use PostgreSQL well.

:-)

Regards and best wishes

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi