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

Commit88e5240

Browse files
author
Barry Lind
committed
Patch from Kris Jurka to improve the performance of getImportedKeys().
Use explicit joins to avoid using the genetic query optimizer. Also fixeda regression test that was failing to compile. This change also cleans uphow key names are reported as per:A change to the value of the FK_NAME column. Currently the returned valueis the triggers arguments which look like"<unnamed>\000t2\000t1\000UNSPECIFIED\000a\000a\000"This was required for server versions < 7.3 when a user did not supplyconstraint names. Every constraint was named "<unnamed>". 7.3 has enforced unique constraint names per table so unnamed foreignkeys will have different names "$1", "$2" and so on. I've used logicalong the lines of the following to preserve the unique names in theoriginal scheme, but allow people who go to the trouble of naming theirconstraints to see them:if (triggerargs.startsWith("<unnamed>")) {fkname = [the whole ugly trigger args name originally used];} else {fkname = [the actual fk name];} Modified Files: jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java jdbc/org/postgresql/test/jdbc2/optional/ConnectionPoolTest.java
1 parent956762a commit88e5240

File tree

2 files changed

+56
-117
lines changed

2 files changed

+56
-117
lines changed

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

Lines changed: 55 additions & 84 deletions
Original file line numberDiff line numberDiff line change
@@ -2912,62 +2912,6 @@ public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, String t
29122912
returnconnection.createStatement().executeQuery(sql);
29132913
}
29142914

2915-
/*
2916-
SELECT
2917-
c.relname as primary,
2918-
c2.relname as foreign,
2919-
t.tgconstrname,
2920-
ic.relname as fkeyname,
2921-
af.attnum as fkeyseq,
2922-
ipc.relname as pkeyname,
2923-
ap.attnum as pkeyseq,
2924-
t.tgdeferrable,
2925-
t.tginitdeferred,
2926-
t.tgnargs,t.tgargs,
2927-
p1.proname as updaterule,
2928-
p2.proname as deleterule
2929-
FROM
2930-
pg_trigger t,
2931-
pg_trigger t1,
2932-
pg_class c,
2933-
pg_class c2,
2934-
pg_class ic,
2935-
pg_class ipc,
2936-
pg_proc p1,
2937-
pg_proc p2,
2938-
pg_index if,
2939-
pg_index ip,
2940-
pg_attribute af,
2941-
pg_attribute ap
2942-
WHERE
2943-
(t.tgrelid=c.oid
2944-
AND t.tgisconstraint
2945-
AND t.tgconstrrelid=c2.oid
2946-
AND t.tgfoid=p1.oid
2947-
and p1.proname like '%%upd')
2948-
2949-
and
2950-
(t1.tgrelid=c.oid
2951-
and t1.tgisconstraint
2952-
and t1.tgconstrrelid=c2.oid
2953-
AND t1.tgfoid=p2.oid
2954-
and p2.proname like '%%del')
2955-
2956-
AND c2.relname='users'
2957-
2958-
AND
2959-
(if.indrelid=c.oid
2960-
AND if.indexrelid=ic.oid
2961-
and ic.oid=af.attrelid
2962-
AND if.indisprimary)
2963-
2964-
and
2965-
(ip.indrelid=c2.oid
2966-
and ip.indexrelid=ipc.oid
2967-
and ipc.oid=ap.attrelid
2968-
and ip.indisprimary)
2969-
2970-
*/
29712915
/**
29722916
*
29732917
* @param catalog
@@ -3014,55 +2958,68 @@ protected java.sql.ResultSet getImportedExportedKeys(String primaryCatalog, Stri
30142958
*/
30152959

30162960
if (connection.haveMinimumServerVersion("7.3")) {
3017-
select ="SELECT DISTINCT n.nspname as pnspname,n2.nspname as fnspname, ";
3018-
from =" FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
3019-
where =" AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
2961+
select ="SELECT n1.nspname as pnspname,n2.nspname as fnspname, ";
2962+
from =" FROM pg_catalog.pg_namespace n1 "+
2963+
" JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "+
2964+
" JOIN pg_catalog.pg_index i ON (c1.oid=i.indrelid) "+
2965+
" JOIN pg_catalog.pg_class ic ON (i.indexrelid=ic.oid) "+
2966+
" JOIN pg_catalog.pg_attribute a ON (ic.oid=a.attrelid), "+
2967+
" pg_catalog.pg_namespace n2 "+
2968+
" JOIN pg_catalog.pg_class c2 ON (c2.relnamespace=n2.oid), "+
2969+
" pg_catalog.pg_trigger t1 "+
2970+
" JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid=p1.oid), "+
2971+
" pg_catalog.pg_trigger t2 "+
2972+
" JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid=p2.oid) ";
30202973
if (primarySchema !=null && !"".equals(primarySchema)) {
3021-
where +=" ANDn.nspname = '"+escapeQuotes(primarySchema)+"' ";
2974+
where +=" ANDn1.nspname = '"+escapeQuotes(primarySchema)+"' ";
30222975
}
30232976
if (foreignSchema !=null && !"".equals(foreignSchema)) {
30242977
where +=" AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
30252978
}
30262979
}else {
3027-
select ="SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
3028-
from =" FROM pg_trigger t, pg_trigger t1, pg_class c, pg_class c2, pg_class ic, pg_proc p1, pg_proc p2, pg_index i, pg_attribute a ";
2980+
select ="SELECT NULL::text as pnspname, NULL::text as fnspname, ";
2981+
from =" FROM pg_class c1 "+
2982+
" JOIN pg_index i ON (c1.oid=i.indrelid) "+
2983+
" JOIN pg_class ic ON (i.indexrelid=ic.oid) "+
2984+
" JOIN pg_attribute a ON (ic.oid=a.attrelid), "+
2985+
" pg_class c2, "+
2986+
" pg_trigger t1 "+
2987+
" JOIN pg_proc p1 ON (t1.tgfoid=p1.oid), "+
2988+
" pg_trigger t2 "+
2989+
" JOIN pg_proc p2 ON (t2.tgfoid=p2.oid) ";
30292990
}
30302991

30312992
Stringsql =select
3032-
+"c.relname as prelname, "
2993+
+"c1.relname as prelname, "
30332994
+"c2.relname as frelname, "
3034-
+"t.tgconstrname, "
2995+
+"t1.tgconstrname, "
30352996
+"a.attnum as keyseq, "
30362997
+"ic.relname as fkeyname, "
3037-
+"t.tgdeferrable, "
3038-
+"t.tginitdeferred, "
3039-
+"t.tgnargs,t.tgargs, "
2998+
+"t1.tgdeferrable, "
2999+
+"t1.tginitdeferred, "
3000+
+"t1.tgnargs,t1.tgargs, "
30403001
+"p1.proname as updaterule, "
30413002
+"p2.proname as deleterule "
30423003
+from
30433004
+"WHERE "
30443005
// isolate the update rule
3045-
+"(t.tgrelid=c.oid "
3046-
+"AND t.tgisconstraint "
3047-
+"AND t.tgconstrrelid=c2.oid "
3048-
+"AND t.tgfoid=p1.oid "
3049-
+"and p1.proname like 'RI\\\\_FKey\\\\_%\\\\_upd') "
3006+
+"(t1.tgrelid=c1.oid "
3007+
+"AND t1.tgisconstraint "
3008+
+"AND t1.tgconstrrelid=c2.oid "
3009+
+"AND p1.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_upd') "
30503010

3051-
+"and "
3011+
+"AND "
30523012
// isolate the delete rule
3053-
+"(t1.tgrelid=c.oid "
3054-
+"and t1.tgisconstraint "
3055-
+"and t1.tgconstrrelid=c2.oid "
3056-
+"AND t1.tgfoid=p2.oid "
3057-
+"and p2.proname like 'RI\\\\_FKey\\\\_%\\\\_del') "
3058-
+"AND i.indrelid=c.oid "
3059-
+"AND i.indexrelid=ic.oid "
3060-
+"AND ic.oid=a.attrelid "
3013+
+"(t2.tgrelid=c1.oid "
3014+
+"AND t2.tgisconstraint "
3015+
+"AND t2.tgconstrrelid=c2.oid "
3016+
+"AND p2.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_del') "
3017+
30613018
+"AND i.indisprimary "
30623019
+where;
30633020

30643021
if (primaryTable !=null) {
3065-
sql +="ANDc.relname='" +escapeQuotes(primaryTable) +"' ";
3022+
sql +="ANDc1.relname='" +escapeQuotes(primaryTable) +"' ";
30663023
}
30673024
if (foreignTable !=null) {
30683025
sql +="AND c2.relname='" +escapeQuotes(foreignTable) +"' ";
@@ -3076,8 +3033,14 @@ protected java.sql.ResultSet getImportedExportedKeys(String primaryCatalog, Stri
30763033
// since when getting crossreference, primaryTable will be defined
30773034

30783035
if (primaryTable !=null) {
3036+
if (connection.haveMinimumServerVersion("7.3")) {
3037+
sql +="fnspname,";
3038+
}
30793039
sql +="frelname";
30803040
}else {
3041+
if (connection.haveMinimumServerVersion("7.3")) {
3042+
sql +="pnspname,";
3043+
}
30813044
sql +="prelname";
30823045
}
30833046

@@ -3160,6 +3123,7 @@ else if ("setdefault".equals(rule))
31603123
// Parse the tgargs data
31613124
StringfkeyColumn ="";
31623125
StringpkeyColumn ="";
3126+
StringfkName ="";
31633127
// Note, I am guessing at most of this, but it should be close
31643128
// if not, please correct
31653129
// the keys are in pairs and start after the first four arguments
@@ -3172,9 +3136,16 @@ else if ("setdefault".equals(rule))
31723136
// we are primarily interested in the column names which are the last items in the string
31733137

31743138
StringTokenizerst =newStringTokenizer(targs,"\\000");
3139+
if (st.hasMoreTokens()) {
3140+
fkName =st.nextToken();
3141+
}
3142+
3143+
if (fkName.startsWith("<unnamed>")) {
3144+
fkName =targs;
3145+
}
31753146

31763147
intadvance =4 + (keySequence -1) *2;
3177-
for (inti =0;st.hasMoreTokens() &&i <advance ;i++ )
3148+
for (inti =1;st.hasMoreTokens() &&i <advance ;i++ )
31783149
st.nextToken();// advance to the key column of interest
31793150

31803151
if (st.hasMoreTokens() )
@@ -3190,7 +3161,7 @@ else if ("setdefault".equals(rule))
31903161
tuple[7] =fkeyColumn.getBytes();//FKCOLUMN_NAME
31913162

31923163
tuple[8] =rs.getBytes(6);//KEY_SEQ
3193-
tuple[11] =targs.getBytes();//FK_NAME this will give us a unique name for the foreign key
3164+
tuple[11] =fkName.getBytes();//FK_NAME this will give us a unique name for the foreign key
31943165
tuple[12] =rs.getBytes(7);//PK_NAME
31953166

31963167
// DEFERRABILITY

‎src/interfaces/jdbc/org/postgresql/test/jdbc2/optional/ConnectionPoolTest.java‎

Lines changed: 1 addition & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
* interface to the PooledConnection is through the CPDS.
1212
*
1313
* @author Aaron Mulder (ammulder@chariotsolutions.com)
14-
* @version $Revision: 1.5 $
14+
* @version $Revision: 1.6 $
1515
*/
1616
publicclassConnectionPoolTestextendsBaseDataSourceTest
1717
{
@@ -391,38 +391,6 @@ public void testStatementProxy() {
391391
}
392392
}
393393

394-
/**
395-
* Ensures that the Statement proxy generated by the Connection handle
396-
* throws the correct kind of exception.
397-
*/
398-
publicvoidtestStatementProxy() {
399-
Statements =null;
400-
try
401-
{
402-
PooledConnectionpc =getPooledConnection();
403-
Connectioncon =pc.getConnection();
404-
s =con.createStatement();
405-
}
406-
catch (SQLExceptione)
407-
{
408-
fail(e.getMessage());
409-
}
410-
try
411-
{
412-
s.executeQuery("SELECT * FROM THIS_TABLE_SHOULD_NOT_EXIST");
413-
fail("An SQL exception was not thrown that should have been");
414-
}
415-
catch (SQLExceptione)
416-
{
417-
;// This is the expected and correct path
418-
}
419-
catch (Exceptione)
420-
{
421-
fail("bad exception; was expecting SQLException, not" +
422-
e.getClass().getName());
423-
}
424-
}
425-
426394
/**
427395
* Ensures that a prepared statement generated by a proxied connection
428396
* returns the proxied connection from getConnection() [not the physical

0 commit comments

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