Thread: 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
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
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
> -----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
> -----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
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
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
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
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
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
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
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
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
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
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
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