Re: 7.2.1 getImportedKeys/getExportedKeys Problems

Aaron,

there has been quite a bit of work done recently on exactly this.

Try downloading the dev driver from jdbc.postgresql.org and let me know
if you get the same results.

Dave
On Tue, 2002-06-18 at 10:50, Aaron Mulder wrote:
> 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)
> );
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>