7.2.1 getImportedKeys/getExportedKeys Problems
От | Aaron Mulder |
---|---|
Тема | 7.2.1 getImportedKeys/getExportedKeys Problems |
Дата | |
Msg-id | Pine.LNX.4.44.0206180949320.26776-100000@gentoo.CS.Princeton.EDU обсуждение исходный текст |
Ответы |
Re: 7.2.1 getImportedKeys/getExportedKeys Problems
|
Список | pgsql-jdbc |
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) );
В списке pgsql-jdbc по дате отправления: