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@postgresql.org)
>
>