7.2.1 getImportedKeys/getExportedKeys Problems

I'm having trouble with the DatabaseMetaData calls to
getImportedKeys and getExportedKeys with PostgreSQL 7.2.1 server and JDBC
packages as distributed with Red Hat Linux 7.3 (running Blackdown JDK
1.3.1_02a-FCS). I've put together a set of DDL and a short Java program
that demonstrates a number of problems. But before I get into that, let
me review my understanding of the way these two methods _should_ work.

http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html

Let's imagine we have three tables: user, role, and user_role
where user_role has foreign keys to user and role.
Both getImportedKeys and getExportedKeys return a primary key
table and column and a foreign key table and column.
getImportedKeys should return results for the foreign keys defined
on a table and referencing the primary key of another table. That is, for
the user table, it should list nothing, and for the user_role table there
should be an imported key where the primary key table is user and another
imported key where the primary key table is role. In both cases the
foreign key table should be user_role.
getExportedKeys should return results for the foreign keys defined
on other tables which reference the primary key of the specified table.
That is, for the user table, there should be an exported key where the
primary key table is user and the foreign key table is user_role, and for
the user_role table there should be no exported keys.
If you examine all the tables, each foreign key should appear
twice, once as an exported key and once as an imported key. In both
cases, the primary key tables should be the same, and the foreign key
tables should be the same. That is, in the example FK from user_role to
user, the PK table should be user regardless of whether it's imported or
exported, and the FK table should be user_role.

Do we agree on all that?

If so, try out the test program and DDL below. Some of the sample
output that I think is mistaken:

Table project
Exported Key: PK=project.project_id FK=task.id
Imported Key: PK=task.project_id FK=project.id

Here, there exported key mixes up the table and column. That is
to say, the correct values are "task.project_id" and "project.id", not
"project.project_id" and "task.id" (there is no such column as
project.project_id).
The imported key mixes up the PK/FK values. That is, the foreign
key is on the table "task" (task.project_id references project.id) and
points to the primary key of the table "project".
Also, these two rows appear to be referring to the same foreign
key, which is incorrect -- since the foreign key is "task.project_id
references project.id" it should be an exported key on the project table
and an imported key on the task table. The (only) correct imported key on
the project table is "project.leader_id references person.id".

Table time_block
Exported Key: PK=time_block.task_id FK=task.id
Imported Key: PK=task.task_id FK=time_block.id

Here, the situation is sort of reversed. The correct table/column
pairs are "time_block.task_id" references "task.id", which means the
exported key table/column pairing is correct and the imported key is
wrong. Also, the exported key mixes up the PK and FK as the imported key
did before. Also, this key should not be exported from the time_block
table, only imported (it should be exported from task).

So the long and the short of it is that both methods appear to be
doing the wrong things, with some regularity. I also notice the problem
reported before where only one of many keys is listed (see, for example,
"task" which declares 5 foreign keys but only gets one imported and one
exported key instead of 5 imported and by coincidence 5 *different*
exported keys [issue, task, time_block, and task_dependency twice]).

I'd be happy to help with testing any fixes. I could put some
time into proposing patches, but I'd be starting from scratch as I've
never worked with PostgreSQL at the code level before.

Thanks,
Aaron

----------------------------------

import java.sql.*;
import java.util.*;

public class PostgresTest {
public static void main(String args[]) {
try {Class.forName("org.postgresql.Driver");}catch(ClassNotFoundException e) {e.printStackTrace();}
try {
Connection con = DriverManager.getConnection("jdbc:postgresql://xxx/xxx", "xxx", "xxx");
DatabaseMetaData data = con.getMetaData();
List list = new ArrayList();
ResultSet rs = data.getTables(null, "", "%", new String[]{"TABLE", "VIEW"});
while(rs.next()) {
list.add(rs.getString(3));
}
rs.close();
Collections.sort(list);
System.out.println("Found "+list.size()+" tables and views.");
String[] tables = (String[])list.toArray(new String[list.size()]);

for(int i=0; i<tables.length; i++) {
rs = data.getExportedKeys(null, "", tables[i]);
System.out.println("Table "+tables[i]);
while(rs.next()) {
System.out.println("Exported Key: PK="+rs.getString(3)+"."+rs.getString(4)+" FK="+rs.getString(7)+"."+rs.getString(8));
}
rs.close();
rs = data.getImportedKeys(null, "", tables[i]);
while(rs.next()) {
System.out.println("Imported Key: PK="+rs.getString(3)+"."+rs.getString(4)+" FK="+rs.getString(7)+"."+rs.getString(8));
}
rs.close();
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

------------------------------------------

DROP TABLE schedule;
DROP TABLE time_block;
DROP TABLE issue;
DROP TABLE issue_priority;
DROP TABLE issue_status;
DROP TABLE task_dependency;
DROP TABLE task;
DROP TABLE project_release;
DROP TABLE project;
DROP TABLE person_role;
DROP TABLE role;
DROP TABLE person;

CREATE TABLE person (
id INTEGER NOT NULL PRIMARY KEY,
username VARCHAR(20) NOT NULL,
password VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
active CHAR(1) NOT NULL DEFAULT 'N',
cvs_username VARCHAR(20)
);

CREATE TABLE role (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
description VARCHAR(100) NOT NULL
);

CREATE TABLE person_role (
person_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (person_id, role_id),
FOREIGN KEY (person_id) REFERENCES person (id),
FOREIGN KEY (role_id) REFERENCES role(id)
);

CREATE TABLE project (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
description VARCHAR(250),
leader_id INTEGER NOT NULL,
web_page VARCHAR(100),
cvs_repository VARCHAR(100),
FOREIGN KEY (leader_id) REFERENCES person (id)
);

CREATE TABLE project_release (
id INTEGER NOT NULL PRIMARY KEY,
project_id INTEGER NOT NULL,
name VARCHAR(30) NOT NULL,
description VARCHAR(250),
release_date DATETIME,
FOREIGN KEY (project_id) REFERENCES project (id),
UNIQUE(project_id, name)
);

CREATE TABLE task (
id INTEGER NOT NULL,
parent_task_id INTEGER,
project_id INTEGER NOT NULL,
name VARCHAR(30) NOT NULL,
description VARCHAR(250),
creation_date DATETIME NOT NULL,
creator_id INTEGER NOT NULL,
leader_id INTEGER NOT NULL,
time_estimate DECIMAL,
time_spent DECIMAL,
target_release_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY (project_id) REFERENCES project (id),
FOREIGN KEY (parent_task_id) REFERENCES task (id),
FOREIGN KEY (creator_id) REFERENCES person (id),
FOREIGN KEY (leader_id) REFERENCES person (id),
FOREIGN KEY (target_release_id) REFERENCES project_release (id)
);

CREATE TABLE task_dependency (
predecessor_task_id INTEGER NOT NULL,
dependent_task_id INTEGER NOT NULL,
PRIMARY KEY (predecessor_task_id, dependent_task_id),
FOREIGN KEY (predecessor_task_id) REFERENCES task (id),
FOREIGN KEY (dependent_task_id) REFERENCES task (id)
);

CREATE TABLE issue_status (
id INTEGER NOT NULL PRIMARY KEY,
sort_order INTEGER NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(250),
UNIQUE (sort_order)
);

CREATE TABLE issue_priority (
id INTEGER NOT NULL PRIMARY KEY,
sort_order INTEGER NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(250),
UNIQUE (sort_order)
);

CREATE TABLE issue (
id INTEGER NOT NULL PRIMARY KEY,
task_id INTEGER NOT NULL,
/* Same as task fields */
project_id INTEGER NOT NULL,
name VARCHAR(30) NOT NULL,
description VARCHAR(250),
creation_date DATETIME NOT NULL,
creator_id INTEGER NOT NULL,
leader_id INTEGER NOT NULL,
time_estimate DECIMAL,
time_spent DECIMAL,
target_release_id INTEGER,
/* End same as task fields */
priority_id INTEGER NOT NULL,
status_id INTEGER NOT NULL,
platform VARCHAR(50),
issue_in_version VARCHAR(30),
fixed_in_version VARCHAR(30),
prevents_testing CHAR(1) NOT NULL DEFAULT 'N',
FOREIGN KEY (task_id) REFERENCES task(id),
FOREIGN KEY (status_id) REFERENCES issue_status(id),
FOREIGN KEY (priority_id) REFERENCES issue_priority (id)
);

CREATE TABLE time_block (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
start DATETIME,
duration_minutes INTEGER,
task_id INTEGER,
FOREIGN KEY (task_id) REFERENCES task (id)
);

CREATE TABLE schedule (
id INTEGER NOT NULL PRIMARY KEY,
time_block_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY (time_block_id) REFERENCES time_block (id),
FOREIGN KEY (person_id) REFERENCES person (id)
);