Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfc1dc0c

Browse files
author
Dave Cramer
committed
patch to deal with unique foreign keys in 7.4 from Kris Jurka
1 parent6ae740d commitfc1dc0c

File tree

2 files changed

+123
-2
lines changed

2 files changed

+123
-2
lines changed

‎src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java

Lines changed: 53 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2966,7 +2966,59 @@ protected java.sql.ResultSet getImportedExportedKeys(String primaryCatalog, Stri
29662966
* the PK_NAME field.
29672967
*/
29682968

2969-
if (connection.haveMinimumServerVersion("7.3")) {
2969+
if (connection.haveMinimumServerVersion("7.4")) {
2970+
Stringsql ="SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEMA, pkc.relname AS PKTABLE_NAME, pka.attname AS PKCOLUMN_NAME, "+
2971+
"NULL::text AS FK_TABLE_CAT, fkn.nspname AS FKTABLE_SCHEMA, fkc.relname AS FKTABLE_NAME, fka.attname AS FKCOLUMN_NAME, "+
2972+
"pos.n AS KEY_SEQ, "+
2973+
"CASE con.confupdtype "+
2974+
" WHEN 'c' THEN " +DatabaseMetaData.importedKeyCascade +
2975+
" WHEN 'n' THEN " +DatabaseMetaData.importedKeySetNull +
2976+
" WHEN 'd' THEN " +DatabaseMetaData.importedKeySetDefault +
2977+
" WHEN 'r' THEN " +DatabaseMetaData.importedKeyRestrict +
2978+
" WHEN 'a' THEN " +DatabaseMetaData.importedKeyNoAction +
2979+
" ELSE NULL END AS UPDATE_RULE, "+
2980+
"CASE con.confdeltype "+
2981+
" WHEN 'c' THEN " +DatabaseMetaData.importedKeyCascade +
2982+
" WHEN 'n' THEN " +DatabaseMetaData.importedKeySetNull +
2983+
" WHEN 'd' THEN " +DatabaseMetaData.importedKeySetDefault +
2984+
" WHEN 'r' THEN " +DatabaseMetaData.importedKeyRestrict +
2985+
" WHEN 'a' THEN " +DatabaseMetaData.importedKeyNoAction +
2986+
" ELSE NULL END AS DELETE_RULE, "+
2987+
"con.conname AS FK_NAME, pkic.relname AS PK_NAME, "+
2988+
"CASE "+
2989+
" WHEN con.condeferrable AND con.condeferred THEN " +DatabaseMetaData.importedKeyInitiallyDeferred +
2990+
" WHEN con.condeferrable THEN " +DatabaseMetaData.importedKeyInitiallyImmediate +
2991+
" ELSE " +DatabaseMetaData.importedKeyNotDeferrable+
2992+
" END AS DEFERRABILITY "+
2993+
" FROM "+
2994+
" pg_catalog.pg_namespace pkn, pg_catalog.pg_class pkc, pg_catalog.pg_attribute pka, "+
2995+
" pg_catalog.pg_namespace fkn, pg_catalog.pg_class fkc, pg_catalog.pg_attribute fka, "+
2996+
" pg_catalog.pg_constraint con, information_schema._pg_keypositions() pos(n), "+
2997+
" pg_catalog.pg_depend dep, pg_catalog.pg_class pkic "+
2998+
" WHERE pkn.oid = pkc.relnamespace AND pkc.oid = pka.attrelid AND pka.attnum = con.confkey[pos.n] AND con.confrelid = pkc.oid "+
2999+
" AND fkn.oid = fkc.relnamespace AND fkc.oid = fka.attrelid AND fka.attnum = con.conkey[pos.n] AND con.conrelid = fkc.oid "+
3000+
" AND con.contype = 'f' AND con.oid = dep.objid AND pkic.oid = dep.refobjid AND pkic.relkind = 'i' AND dep.classid = 'pg_constraint'::regclass::oid AND dep.refclassid = 'pg_class'::regclass::oid ";
3001+
if (primarySchema !=null && !"".equals(primarySchema)) {
3002+
sql +=" AND pkn.nspname = '"+escapeQuotes(primarySchema)+"' ";
3003+
}
3004+
if (foreignSchema !=null && !"".equals(foreignSchema)) {
3005+
sql +=" AND fkn.nspname = '"+escapeQuotes(foreignSchema)+"' ";
3006+
}
3007+
if (primaryTable !=null && !"".equals(primaryTable)) {
3008+
sql +=" AND pkc.relname = '"+escapeQuotes(primaryTable)+"' ";
3009+
}
3010+
if (foreignTable !=null && !"".equals(foreignTable)) {
3011+
sql +=" AND fkc.relname = '"+escapeQuotes(foreignTable)+"' ";
3012+
}
3013+
3014+
if (primaryTable !=null) {
3015+
sql +=" ORDER BY fkn.nspname,fkc.relname,pos.n";
3016+
}else {
3017+
sql +=" ORDER BY pkn.nspname,pkc.relname,pos.n";
3018+
}
3019+
3020+
returnconnection.createStatement().executeQuery(sql);
3021+
}elseif (connection.haveMinimumServerVersion("7.3")) {
29703022
select ="SELECT DISTINCT n1.nspname as pnspname,n2.nspname as fnspname, ";
29713023
from =" FROM pg_catalog.pg_namespace n1 "+
29723024
" JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "+

‎src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java

Lines changed: 70 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
* PS: Do you know how difficult it is to type on a train? ;-)
1111
*
12-
* $PostgreSQL: pgsql/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v 1.20 2003/11/29 22:41:23 pgsql Exp $
12+
* $PostgreSQL: pgsql/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v 1.21 2003/12/11 15:50:20 davec Exp $
1313
*/
1414

1515
publicclassDatabaseMetaDataTestextendsTestCase
@@ -169,6 +169,75 @@ public void testForeignKeyActions()
169169
}
170170
}
171171

172+
publicvoidtestForeignKeysToUniqueIndexes()
173+
{
174+
try
175+
{
176+
if (!TestUtil.haveMinimumServerVersion(con,"7.4"))
177+
return;
178+
179+
Connectioncon1 =TestUtil.openDB();
180+
TestUtil.createTable(con1,"pkt","a int not null, b int not null, CONSTRAINT pkt_pk_a PRIMARY KEY (a), CONSTRAINT pkt_un_b UNIQUE (b)");
181+
TestUtil.createTable(con1,"fkt","c int, d int, CONSTRAINT fkt_fk_c FOREIGN KEY (c) REFERENCES pkt(b)");
182+
183+
DatabaseMetaDatadbmd =con.getMetaData();
184+
ResultSetrs =dbmd.getImportedKeys("","","fkt");
185+
intj =0;
186+
for (;rs.next();j++)
187+
{
188+
assertTrue("pkt".equals(rs.getString("PKTABLE_NAME")));
189+
assertTrue("fkt".equals(rs.getString("FKTABLE_NAME")));
190+
assertTrue("pkt_un_b".equals(rs.getString("PK_NAME")));
191+
assertTrue("b".equals(rs.getString("PKCOLUMN_NAME")));
192+
}
193+
assertTrue(j ==1);
194+
195+
TestUtil.dropTable(con1,"fkt");
196+
TestUtil.dropTable(con1,"pkt");
197+
con1.close();
198+
}
199+
catch (SQLExceptionex)
200+
{
201+
fail(ex.getMessage());
202+
}
203+
}
204+
205+
publicvoidtestMultiColumnForeignKeys()
206+
{
207+
try
208+
{
209+
Connectioncon1 =TestUtil.openDB();
210+
TestUtil.createTable(con1,"pkt","a int not null, b int not null, CONSTRAINT pkt_pk PRIMARY KEY (a,b)");
211+
TestUtil.createTable(con1,"fkt","c int, d int, CONSTRAINT fkt_fk_pkt FOREIGN KEY (c,d) REFERENCES pkt(b,a)");
212+
213+
DatabaseMetaDatadbmd =con.getMetaData();
214+
ResultSetrs =dbmd.getImportedKeys("","","fkt");
215+
intj =0;
216+
for (;rs.next();j++)
217+
{
218+
assertTrue("pkt".equals(rs.getString("PKTABLE_NAME")));
219+
assertTrue("fkt".equals(rs.getString("FKTABLE_NAME")));
220+
assertTrue(j+1 ==rs.getInt("KEY_SEQ"));
221+
if (j ==0) {
222+
assertTrue("b".equals(rs.getString("PKCOLUMN_NAME")));
223+
assertTrue("c".equals(rs.getString("FKCOLUMN_NAME")));
224+
}else {
225+
assertTrue("a".equals(rs.getString("PKCOLUMN_NAME")));
226+
assertTrue("d".equals(rs.getString("FKCOLUMN_NAME")));
227+
}
228+
}
229+
assertTrue(j ==2);
230+
231+
TestUtil.dropTable(con1,"fkt");
232+
TestUtil.dropTable(con1,"pkt");
233+
con1.close();
234+
}
235+
catch (SQLExceptionex)
236+
{
237+
fail(ex.getMessage());
238+
}
239+
}
240+
172241
publicvoidtestForeignKeys()
173242
{
174243
try

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp