Thread: DatabaseMetaData.getImported/ExportedKeys() patch
Hi, Here is a diff for DatabaseMetaData.getImportedKeys() and DatabaseMetaData.getExportedKeys(). Please check that it works :) Thanks, Jason Davies jason@netspade.com
Attachment
Jason,
Can you explain what this patch is attempting to fix? Given that we are
in beta with 7.2, I want to fully understand the problem here before
applying this patch. (Especially given that the diff is pretty large and
not a one or two line change).
Also what testing have you done on this? (When you ask us to 'check
that it works', it doesn't give me a high level of confidence that this
is well tested).
thanks,
--Barry
Jason Davies wrote:
> Hi,
>
> Here is a diff for DatabaseMetaData.getImportedKeys() and
> DatabaseMetaData.getExportedKeys(). Please check that it works :)
>
> Thanks,
> Jason Davies
>
> jason@netspade.com
>
>
> ------------------------------------------------------------------------
>
> Index: jdbc1/DatabaseMetaData.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
> retrieving revision 1.35
> diff -c -r1.35 DatabaseMetaData.java
> *** jdbc1/DatabaseMetaData.java 2001/10/25 05:59:59 1.35
> --- jdbc1/DatabaseMetaData.java 2001/10/26 01:21:02
> ***************
> *** 2260,2345 ****
> );
> }
>
> ! private void importLoop(Vector tuples, java.sql.ResultSet keyRelation) throws SQLException
> ! {
> ! String s, s2;
> ! String origTable = null, primTable = new String(""), schema;
> ! int i;
> ! Vector v = new Vector();
> !
> ! s = keyRelation.getString(1);
> ! s2 = s;
> ! //System.out.println(s);
> !
> ! for (i = 0;;i++)
> ! {
> ! s = s.substring(s.indexOf("\\000") + 4);
> ! if (s.compareTo("") == 0)
> ! {
> ! //System.out.println();
> ! break;
> ! }
> ! s2 = s.substring(0, s.indexOf("\\000"));
> ! switch (i)
> ! {
> ! case 0:
> ! origTable = s2;
> ! break;
> ! case 1:
> ! primTable = s2;
> ! break;
> ! case 2:
> ! schema = s2;
> ! break;
> ! default:
> ! v.addElement(s2);
> ! }
> ! }
> !
> ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> !
> ! String stmp;
> ! // Vector tuples=new Vector();
> ! byte tuple[][];
> !
> ! // the foreign keys are only on even positions in the Vector.
> ! for (i = 0;i < v.size();i += 2)
> {
> ! stmp = (String)v.elementAt(i);
> !
> ! for (int j = 1;j <= origCols.getColumnCount();j++)
> {
> ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> {
> ! tuple = new byte[14][0];
> !
> ! for (int k = 0;k < 14;k++)
> ! tuple[k] = null;
> !
> ! //PKTABLE_NAME
> ! tuple[2] = primTable.getBytes();
> ! //PKTABLE_COLUMN
> ! stmp = (String)v.elementAt(i + 1);
> ! tuple[3] = stmp.getBytes();
> ! //FKTABLE_NAME
> ! tuple[6] = origTable.getBytes();
> ! //FKCOLUMN_NAME
> ! tuple[7] = origCols.getColumnName(j).getBytes();
> ! //KEY_SEQ
> ! tuple[8] = Integer.toString(j).getBytes();
> !
> ! tuples.addElement(tuple);
> !
> ! //System.out.println(origCols.getColumnName(j)+
> ! //": "+j+" -> "+primTable+": "+
> ! //(String)v.elementAt(i+1));
> ! break;
> }
> }
> }
>
> ! //return tuples;
> }
>
> /**
> --- 2260,2330 ----
> );
> }
>
> ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> ! {
> ! byte tuple[][]=new byte[14][0];
> ! for (int k = 0;k < 14;k++)
> ! tuple[k] = null;
> ! String s=keyRelation.getString(1);
> ! int pos=s.indexOf("\\000");
> ! if(pos>-1)
> ! {
> ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> ! int pos2=s.indexOf("\\000", pos+1);
> ! if(pos2>-1)
> {
> ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> ! pos=s.indexOf("\\000", pos2+1);
> ! if(pos>-1)
> ! {
> ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> ! if(pos>-1)
> {
> ! pos2=s.indexOf("\\000",pos+1);
> ! if(pos2>-1)
> ! {
> ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> ! pos=s.indexOf("\\000", pos2+1);
> ! if(pos>-1)
> {
> ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> }
> + }
> }
> + }
> }
> + }
> +
> + // UPDATE_RULE
> + String rule=keyRelation.getString(2);
> + int action=importedKeyNoAction;
> + if("cascade".equals(rule)) action=importedKeyCascade;
> + else if("setnull".equals(rule)) action=importedKeySetNull;
> + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> + tuple[9]=Integer.toString(action).getBytes();
> +
> + // DELETE_RULE
> + rule=keyRelation.getString(3);
> + action=importedKeyNoAction;
> + if("cascade".equals(rule)) action=importedKeyCascade;
> + else if("setnull".equals(rule)) action=importedKeySetNull;
> + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> + tuple[10]=Integer.toString(action).getBytes();
> +
> + // DEFERRABILITY
> + int deferrability=importedKeyNotDeferrable;
> + boolean deferrable=keyRelation.getBoolean(4);
> + if(deferrable)
> + {
> + if(keyRelation.getBoolean(5))
> + deferrability=importedKeyInitiallyDeferred;
> + else
> + deferrability=importedKeyInitiallyImmediate;
> + }
> + tuple[13]=Integer.toString(deferrability).getBytes();
>
> ! return tuple;
> }
>
> /**
> ***************
> *** 2395,2405 ****
> */
> public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> {
> - // Added by Ola Sundell <ola@miranda.org>
> - // FIXME: error checking galore!
> - java.sql.ResultSet rsret;
> Field f[] = new Field[14];
> - byte tuple[][];
>
> f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> --- 2380,2386 ----
> ***************
> *** 2416,2434 ****
> f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
>
> ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> ! "from pg_class as c, pg_trigger as t " +
> ! "where c.relname like '" + table + "' and c.relfilenode=t.tgrelid");
> Vector tuples = new Vector();
>
> while (rs.next())
> {
> ! importLoop(tuples, rs);
> }
>
> ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> !
> ! return rsret;
> }
>
> /**
> --- 2397,2429 ----
> f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
>
> ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> ! + "a.tgdeferrable,"
> ! + "a.tginitdeferred "
> ! + "FROM "
> ! + "(SELECT t.tgargs, t.tgconstrname, p.proname, t.tgdeferrable,"
> ! + "t.tginitdeferred "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> ! + "(SELECT t.tgconstrname, p.proname "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid) as c "
> ! + "WHERE a.tgconstrname=b.tgconstrname AND a.tgconstrname=c.tgconstrname"
> ! );
> Vector tuples = new Vector();
>
> while (rs.next())
> {
> ! tuples.add(parseConstraint(rs));
> }
>
> ! return new ResultSet(connection, f, tuples, "OK", 1);
> }
>
> /**
> ***************
> *** 2486,2492 ****
> */
> public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> {
> ! throw org.postgresql.Driver.notImplemented();
> }
>
> /**
> --- 2481,2527 ----
> */
> public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> {
> ! Field f[] = new Field[14];
> !
> ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> !
> ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> ! + "a.tgdeferrable,"
> ! + "a.tginitdeferred "
> ! + "FROM "
> ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> ! + "t.tgdeferrable, t.tginitdeferred "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_upd') as a, "
> ! + "(SELECT t.tgconstrname, p.proname "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_del') as b "
> ! + "WHERE a.tgconstrname=b.tgconstrname");
> ! Vector tuples = new Vector();
> !
> ! while (rs.next())
> ! {
> ! tuples.add(parseConstraint(rs));
> ! }
> !
> ! return new ResultSet(connection, f, tuples, "OK", 1);
> }
>
> /**
> Index: jdbc2/DatabaseMetaData.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
> retrieving revision 1.40
> diff -c -r1.40 DatabaseMetaData.java
> *** jdbc2/DatabaseMetaData.java 2001/10/25 05:59:59 1.40
> --- jdbc2/DatabaseMetaData.java 2001/10/26 01:21:10
> ***************
> *** 2273,2359 ****
> );
> }
>
> ! private Vector importLoop(java.sql.ResultSet keyRelation) throws SQLException
> ! {
> ! String s, s2;
> ! String origTable = null, primTable = new String(""), schema;
> ! int i;
> ! Vector v;
> !
> ! s = keyRelation.getString(1);
> ! s2 = s;
> ! // System.out.println(s);
> ! v = new Vector();
> ! for (i = 0;;i++)
> ! {
> ! s = s.substring(s.indexOf("\\000") + 4);
> ! if (s.compareTo("") == 0)
> ! {
> ! //System.out.println();
> ! break;
> ! }
> ! s2 = s.substring(0, s.indexOf("\\000"));
> ! switch (i)
> ! {
> ! case 0:
> ! origTable = s2;
> ! break;
> ! case 1:
> ! primTable = s2;
> ! break;
> ! case 2:
> ! schema = s2;
> ! break;
> ! default:
> ! v.add(s2);
> ! }
> ! }
> !
> ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> !
> ! String stmp;
> ! Vector tuples = new Vector();
> ! byte tuple[][];
> !
> ! // the foreign keys are only on even positions in the Vector.
> ! for (i = 0;i < v.size();i += 2)
> {
> ! stmp = (String)v.elementAt(i);
> !
> ! for (int j = 1;j <= origCols.getColumnCount();j++)
> {
> ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> {
> ! tuple = new byte[14][0];
> !
> ! for (int k = 0;k < 14;k++)
> ! tuple[k] = null;
> !
> ! //PKTABLE_NAME
> ! tuple[2] = primTable.getBytes();
> ! //PKTABLE_COLUMN
> ! stmp = (String)v.elementAt(i + 1);
> ! tuple[3] = stmp.getBytes();
> ! //FKTABLE_NAME
> ! tuple[6] = origTable.getBytes();
> ! //FKCOLUMN_NAME
> ! tuple[7] = origCols.getColumnName(j).getBytes();
> ! //KEY_SEQ
> ! tuple[8] = Integer.toString(j).getBytes();
> !
> ! tuples.add(tuple);
> ! /*
> ! System.out.println(origCols.getColumnName(j)+
> ! ": "+j+" -> "+primTable+": "+
> ! (String)v.elementAt(i+1));
> ! */
> ! break;
> }
> }
> }
>
> ! return tuples;
> }
>
> /**
> --- 2273,2343 ----
> );
> }
>
> ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> ! {
> ! byte tuple[][]=new byte[14][0];
> ! for (int k = 0;k < 14;k++)
> ! tuple[k] = null;
> ! String s=keyRelation.getString(1);
> ! int pos=s.indexOf("\\000");
> ! if(pos>-1)
> ! {
> ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> ! int pos2=s.indexOf("\\000", pos+1);
> ! if(pos2>-1)
> {
> ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> ! pos=s.indexOf("\\000", pos2+1);
> ! if(pos>-1)
> ! {
> ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> ! if(pos>-1)
> {
> ! pos2=s.indexOf("\\000",pos+1);
> ! if(pos2>-1)
> ! {
> ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> ! pos=s.indexOf("\\000", pos2+1);
> ! if(pos>-1)
> {
> ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> }
> + }
> }
> + }
> }
> + }
> +
> + // UPDATE_RULE
> + String rule=keyRelation.getString(2);
> + int action=importedKeyNoAction;
> + if("cascade".equals(rule)) action=importedKeyCascade;
> + else if("setnull".equals(rule)) action=importedKeySetNull;
> + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> + tuple[9]=Integer.toString(action).getBytes();
> +
> + // DELETE_RULE
> + rule=keyRelation.getString(3);
> + action=importedKeyNoAction;
> + if("cascade".equals(rule)) action=importedKeyCascade;
> + else if("setnull".equals(rule)) action=importedKeySetNull;
> + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> + tuple[10]=Integer.toString(action).getBytes();
> +
> + // DEFERRABILITY
> + int deferrability=importedKeyNotDeferrable;
> + boolean deferrable=keyRelation.getBoolean(4);
> + if(deferrable)
> + {
> + if(keyRelation.getBoolean(5))
> + deferrability=importedKeyInitiallyDeferred;
> + else
> + deferrability=importedKeyInitiallyImmediate;
> + }
> + tuple[13]=Integer.toString(deferrability).getBytes();
>
> ! return tuple;
> }
>
> /**
> ***************
> *** 2409,2419 ****
> */
> public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> {
> - // Added by Ola Sundell <ola@miranda.org>
> - // FIXME: error checking galore!
> - java.sql.ResultSet rsret;
> Field f[] = new Field[14];
> - byte tuple[][];
>
> f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> --- 2393,2399 ----
> ***************
> *** 2430,2448 ****
> f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
>
> ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> ! "from pg_class as c, pg_trigger as t " +
> ! "where c.relname like '" + table + "' and c.relfilenode=t.tgrelid");
> Vector tuples = new Vector();
>
> while (rs.next())
> {
> ! tuples.addAll(importLoop(rs));
> }
>
> ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> !
> ! return rsret;
> }
>
> /**
> --- 2410,2442 ----
> f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
>
> ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> ! + "a.tgdeferrable,"
> ! + "a.tginitdeferred "
> ! + "FROM "
> ! + "(SELECT t.tgargs, t.tgconstrname, p.proname, t.tgdeferrable,"
> ! + "t.tginitdeferred "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> ! + "(SELECT t.tgconstrname, p.proname "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid) as c "
> ! + "WHERE a.tgconstrname=b.tgconstrname AND a.tgconstrname=c.tgconstrname"
> ! );
> Vector tuples = new Vector();
>
> while (rs.next())
> {
> ! tuples.add(parseConstraint(rs));
> }
>
> ! return new ResultSet(connection, f, tuples, "OK", 1);
> }
>
> /**
> ***************
> *** 2500,2506 ****
> */
> public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> {
> ! throw org.postgresql.Driver.notImplemented();
> }
>
> /**
> --- 2494,2540 ----
> */
> public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> {
> ! Field f[] = new Field[14];
> !
> ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> !
> ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> ! + "a.tgdeferrable,"
> ! + "a.tginitdeferred "
> ! + "FROM "
> ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> ! + "t.tgdeferrable, t.tginitdeferred "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_upd') as a, "
> ! + "(SELECT t.tgconstrname, p.proname "
> ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_del') as b "
> ! + "WHERE a.tgconstrname=b.tgconstrname");
> ! Vector tuples = new Vector();
> !
> ! while (rs.next())
> ! {
> ! tuples.add(parseConstraint(rs));
> ! }
> !
> ! return new ResultSet(connection, f, tuples, "OK", 1);
> }
>
> /**
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
On Fri, Oct 26, 2001 at 09:52:11AM -0700, Barry Lind wrote:
> Jason,
>
> Can you explain what this patch is attempting to fix? Given that we are
> in beta with 7.2, I want to fully understand the problem here before
> applying this patch. (Especially given that the diff is pretty large and
> not a one or two line change).
I guess "fix" is slightly misleading. This patch implements DatabaseMetaData.getExportedKeys() which was previously not
implemented,and provides a better implementation of DatabaseMetaData.getImportedKeys() which was previously
half-implemented.
The methods are identical for both jdbc1 and jdbc2 so that's why the diff is quite large. Perhaps identical code should
beput into a single class and have the code which differs in jdbc1 and jdbc2 in subclasses?
> Also what testing have you done on this? (When you ask us to 'check
> that it works', it doesn't give me a high level of confidence that this
> is well tested).
:-) I have tested it but only with 7.1.3 at the moment. What I meant to say is please check in case something major has
beenchanged in 7.2. Also if the queries I'm using can be optimized in any way that would be great.
At the moment a value for PK_NAME (primary key name) is not returned but the main part is implemented.
Sorry for the lack of info, I'll try and be more helpful next time :)
Jason Davies
> thanks,
> --Barry
>
>
> Jason Davies wrote:
>
> > Hi,
> >
> > Here is a diff for DatabaseMetaData.getImportedKeys() and
> > DatabaseMetaData.getExportedKeys(). Please check that it works :)
> >
> > Thanks,
> > Jason Davies
> >
> > jason@netspade.com
> >
> >
> > ------------------------------------------------------------------------
> >
> > Index: jdbc1/DatabaseMetaData.java
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
> > retrieving revision 1.35
> > diff -c -r1.35 DatabaseMetaData.java
> > *** jdbc1/DatabaseMetaData.java 2001/10/25 05:59:59 1.35
> > --- jdbc1/DatabaseMetaData.java 2001/10/26 01:21:02
> > ***************
> > *** 2260,2345 ****
> > );
> > }
> >
> > ! private void importLoop(Vector tuples, java.sql.ResultSet keyRelation) throws SQLException
> > ! {
> > ! String s, s2;
> > ! String origTable = null, primTable = new String(""), schema;
> > ! int i;
> > ! Vector v = new Vector();
> > !
> > ! s = keyRelation.getString(1);
> > ! s2 = s;
> > ! //System.out.println(s);
> > !
> > ! for (i = 0;;i++)
> > ! {
> > ! s = s.substring(s.indexOf("\\000") + 4);
> > ! if (s.compareTo("") == 0)
> > ! {
> > ! //System.out.println();
> > ! break;
> > ! }
> > ! s2 = s.substring(0, s.indexOf("\\000"));
> > ! switch (i)
> > ! {
> > ! case 0:
> > ! origTable = s2;
> > ! break;
> > ! case 1:
> > ! primTable = s2;
> > ! break;
> > ! case 2:
> > ! schema = s2;
> > ! break;
> > ! default:
> > ! v.addElement(s2);
> > ! }
> > ! }
> > !
> > ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> > ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> > !
> > ! String stmp;
> > ! // Vector tuples=new Vector();
> > ! byte tuple[][];
> > !
> > ! // the foreign keys are only on even positions in the Vector.
> > ! for (i = 0;i < v.size();i += 2)
> > {
> > ! stmp = (String)v.elementAt(i);
> > !
> > ! for (int j = 1;j <= origCols.getColumnCount();j++)
> > {
> > ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> > {
> > ! tuple = new byte[14][0];
> > !
> > ! for (int k = 0;k < 14;k++)
> > ! tuple[k] = null;
> > !
> > ! //PKTABLE_NAME
> > ! tuple[2] = primTable.getBytes();
> > ! //PKTABLE_COLUMN
> > ! stmp = (String)v.elementAt(i + 1);
> > ! tuple[3] = stmp.getBytes();
> > ! //FKTABLE_NAME
> > ! tuple[6] = origTable.getBytes();
> > ! //FKCOLUMN_NAME
> > ! tuple[7] = origCols.getColumnName(j).getBytes();
> > ! //KEY_SEQ
> > ! tuple[8] = Integer.toString(j).getBytes();
> > !
> > ! tuples.addElement(tuple);
> > !
> > ! //System.out.println(origCols.getColumnName(j)+
> > ! //": "+j+" -> "+primTable+": "+
> > ! //(String)v.elementAt(i+1));
> > ! break;
> > }
> > }
> > }
> >
> > ! //return tuples;
> > }
> >
> > /**
> > --- 2260,2330 ----
> > );
> > }
> >
> > ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> > ! {
> > ! byte tuple[][]=new byte[14][0];
> > ! for (int k = 0;k < 14;k++)
> > ! tuple[k] = null;
> > ! String s=keyRelation.getString(1);
> > ! int pos=s.indexOf("\\000");
> > ! if(pos>-1)
> > ! {
> > ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> > ! int pos2=s.indexOf("\\000", pos+1);
> > ! if(pos2>-1)
> > {
> > ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> > ! pos=s.indexOf("\\000", pos2+1);
> > ! if(pos>-1)
> > ! {
> > ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> > ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> > ! if(pos>-1)
> > {
> > ! pos2=s.indexOf("\\000",pos+1);
> > ! if(pos2>-1)
> > ! {
> > ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> > ! pos=s.indexOf("\\000", pos2+1);
> > ! if(pos>-1)
> > {
> > ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> > }
> > + }
> > }
> > + }
> > }
> > + }
> > +
> > + // UPDATE_RULE
> > + String rule=keyRelation.getString(2);
> > + int action=importedKeyNoAction;
> > + if("cascade".equals(rule)) action=importedKeyCascade;
> > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > + tuple[9]=Integer.toString(action).getBytes();
> > +
> > + // DELETE_RULE
> > + rule=keyRelation.getString(3);
> > + action=importedKeyNoAction;
> > + if("cascade".equals(rule)) action=importedKeyCascade;
> > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > + tuple[10]=Integer.toString(action).getBytes();
> > +
> > + // DEFERRABILITY
> > + int deferrability=importedKeyNotDeferrable;
> > + boolean deferrable=keyRelation.getBoolean(4);
> > + if(deferrable)
> > + {
> > + if(keyRelation.getBoolean(5))
> > + deferrability=importedKeyInitiallyDeferred;
> > + else
> > + deferrability=importedKeyInitiallyImmediate;
> > + }
> > + tuple[13]=Integer.toString(deferrability).getBytes();
> >
> > ! return tuple;
> > }
> >
> > /**
> > ***************
> > *** 2395,2405 ****
> > */
> > public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > - // Added by Ola Sundell <ola@miranda.org>
> > - // FIXME: error checking galore!
> > - java.sql.ResultSet rsret;
> > Field f[] = new Field[14];
> > - byte tuple[][];
> >
> > f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > --- 2380,2386 ----
> > ***************
> > *** 2416,2434 ****
> > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> >
> > ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> > ! "from pg_class as c, pg_trigger as t " +
> > ! "where c.relname like '" + table + "' and c.relfilenode=t.tgrelid");
> > Vector tuples = new Vector();
> >
> > while (rs.next())
> > {
> > ! importLoop(tuples, rs);
> > }
> >
> > ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> > !
> > ! return rsret;
> > }
> >
> > /**
> > --- 2397,2429 ----
> > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> >
> > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> > ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> > ! + "a.tgdeferrable,"
> > ! + "a.tginitdeferred "
> > ! + "FROM "
> > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname, t.tgdeferrable,"
> > ! + "t.tginitdeferred "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> > ! + "(SELECT t.tgconstrname, p.proname "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> > ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid) as c "
> > ! + "WHERE a.tgconstrname=b.tgconstrname AND a.tgconstrname=c.tgconstrname"
> > ! );
> > Vector tuples = new Vector();
> >
> > while (rs.next())
> > {
> > ! tuples.add(parseConstraint(rs));
> > }
> >
> > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > }
> >
> > /**
> > ***************
> > *** 2486,2492 ****
> > */
> > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > ! throw org.postgresql.Driver.notImplemented();
> > }
> >
> > /**
> > --- 2481,2527 ----
> > */
> > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > ! Field f[] = new Field[14];
> > !
> > ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> > ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> > ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> > ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> > ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> > ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> > ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> > ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> > ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> > ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> > ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > !
> > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> > ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> > ! + "a.tgdeferrable,"
> > ! + "a.tginitdeferred "
> > ! + "FROM "
> > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> > ! + "t.tgdeferrable, t.tginitdeferred "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> > ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_upd') as a, "
> > ! + "(SELECT t.tgconstrname, p.proname "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> > ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_del') as b "
> > ! + "WHERE a.tgconstrname=b.tgconstrname");
> > ! Vector tuples = new Vector();
> > !
> > ! while (rs.next())
> > ! {
> > ! tuples.add(parseConstraint(rs));
> > ! }
> > !
> > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > }
> >
> > /**
> > Index: jdbc2/DatabaseMetaData.java
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
> > retrieving revision 1.40
> > diff -c -r1.40 DatabaseMetaData.java
> > *** jdbc2/DatabaseMetaData.java 2001/10/25 05:59:59 1.40
> > --- jdbc2/DatabaseMetaData.java 2001/10/26 01:21:10
> > ***************
> > *** 2273,2359 ****
> > );
> > }
> >
> > ! private Vector importLoop(java.sql.ResultSet keyRelation) throws SQLException
> > ! {
> > ! String s, s2;
> > ! String origTable = null, primTable = new String(""), schema;
> > ! int i;
> > ! Vector v;
> > !
> > ! s = keyRelation.getString(1);
> > ! s2 = s;
> > ! // System.out.println(s);
> > ! v = new Vector();
> > ! for (i = 0;;i++)
> > ! {
> > ! s = s.substring(s.indexOf("\\000") + 4);
> > ! if (s.compareTo("") == 0)
> > ! {
> > ! //System.out.println();
> > ! break;
> > ! }
> > ! s2 = s.substring(0, s.indexOf("\\000"));
> > ! switch (i)
> > ! {
> > ! case 0:
> > ! origTable = s2;
> > ! break;
> > ! case 1:
> > ! primTable = s2;
> > ! break;
> > ! case 2:
> > ! schema = s2;
> > ! break;
> > ! default:
> > ! v.add(s2);
> > ! }
> > ! }
> > !
> > ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> > ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> > !
> > ! String stmp;
> > ! Vector tuples = new Vector();
> > ! byte tuple[][];
> > !
> > ! // the foreign keys are only on even positions in the Vector.
> > ! for (i = 0;i < v.size();i += 2)
> > {
> > ! stmp = (String)v.elementAt(i);
> > !
> > ! for (int j = 1;j <= origCols.getColumnCount();j++)
> > {
> > ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> > {
> > ! tuple = new byte[14][0];
> > !
> > ! for (int k = 0;k < 14;k++)
> > ! tuple[k] = null;
> > !
> > ! //PKTABLE_NAME
> > ! tuple[2] = primTable.getBytes();
> > ! //PKTABLE_COLUMN
> > ! stmp = (String)v.elementAt(i + 1);
> > ! tuple[3] = stmp.getBytes();
> > ! //FKTABLE_NAME
> > ! tuple[6] = origTable.getBytes();
> > ! //FKCOLUMN_NAME
> > ! tuple[7] = origCols.getColumnName(j).getBytes();
> > ! //KEY_SEQ
> > ! tuple[8] = Integer.toString(j).getBytes();
> > !
> > ! tuples.add(tuple);
> > ! /*
> > ! System.out.println(origCols.getColumnName(j)+
> > ! ": "+j+" -> "+primTable+": "+
> > ! (String)v.elementAt(i+1));
> > ! */
> > ! break;
> > }
> > }
> > }
> >
> > ! return tuples;
> > }
> >
> > /**
> > --- 2273,2343 ----
> > );
> > }
> >
> > ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> > ! {
> > ! byte tuple[][]=new byte[14][0];
> > ! for (int k = 0;k < 14;k++)
> > ! tuple[k] = null;
> > ! String s=keyRelation.getString(1);
> > ! int pos=s.indexOf("\\000");
> > ! if(pos>-1)
> > ! {
> > ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> > ! int pos2=s.indexOf("\\000", pos+1);
> > ! if(pos2>-1)
> > {
> > ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> > ! pos=s.indexOf("\\000", pos2+1);
> > ! if(pos>-1)
> > ! {
> > ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> > ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> > ! if(pos>-1)
> > {
> > ! pos2=s.indexOf("\\000",pos+1);
> > ! if(pos2>-1)
> > ! {
> > ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> > ! pos=s.indexOf("\\000", pos2+1);
> > ! if(pos>-1)
> > {
> > ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> > }
> > + }
> > }
> > + }
> > }
> > + }
> > +
> > + // UPDATE_RULE
> > + String rule=keyRelation.getString(2);
> > + int action=importedKeyNoAction;
> > + if("cascade".equals(rule)) action=importedKeyCascade;
> > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > + tuple[9]=Integer.toString(action).getBytes();
> > +
> > + // DELETE_RULE
> > + rule=keyRelation.getString(3);
> > + action=importedKeyNoAction;
> > + if("cascade".equals(rule)) action=importedKeyCascade;
> > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > + tuple[10]=Integer.toString(action).getBytes();
> > +
> > + // DEFERRABILITY
> > + int deferrability=importedKeyNotDeferrable;
> > + boolean deferrable=keyRelation.getBoolean(4);
> > + if(deferrable)
> > + {
> > + if(keyRelation.getBoolean(5))
> > + deferrability=importedKeyInitiallyDeferred;
> > + else
> > + deferrability=importedKeyInitiallyImmediate;
> > + }
> > + tuple[13]=Integer.toString(deferrability).getBytes();
> >
> > ! return tuple;
> > }
> >
> > /**
> > ***************
> > *** 2409,2419 ****
> > */
> > public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > - // Added by Ola Sundell <ola@miranda.org>
> > - // FIXME: error checking galore!
> > - java.sql.ResultSet rsret;
> > Field f[] = new Field[14];
> > - byte tuple[][];
> >
> > f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > --- 2393,2399 ----
> > ***************
> > *** 2430,2448 ****
> > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> >
> > ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> > ! "from pg_class as c, pg_trigger as t " +
> > ! "where c.relname like '" + table + "' and c.relfilenode=t.tgrelid");
> > Vector tuples = new Vector();
> >
> > while (rs.next())
> > {
> > ! tuples.addAll(importLoop(rs));
> > }
> >
> > ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> > !
> > ! return rsret;
> > }
> >
> > /**
> > --- 2410,2442 ----
> > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> >
> > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> > ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> > ! + "a.tgdeferrable,"
> > ! + "a.tginitdeferred "
> > ! + "FROM "
> > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname, t.tgdeferrable,"
> > ! + "t.tginitdeferred "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> > ! + "(SELECT t.tgconstrname, p.proname "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> > ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid) as c "
> > ! + "WHERE a.tgconstrname=b.tgconstrname AND a.tgconstrname=c.tgconstrname"
> > ! );
> > Vector tuples = new Vector();
> >
> > while (rs.next())
> > {
> > ! tuples.add(parseConstraint(rs));
> > }
> >
> > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > }
> >
> > /**
> > ***************
> > *** 2500,2506 ****
> > */
> > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > ! throw org.postgresql.Driver.notImplemented();
> > }
> >
> > /**
> > --- 2494,2540 ----
> > */
> > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > {
> > ! Field f[] = new Field[14];
> > !
> > ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> > ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> > ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> > ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> > ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> > ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> > ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> > ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> > ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> > ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> > ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > !
> > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > ! + "substring(a.proname from 9 for (char_length(a.proname)-12)),"
> > ! + "substring(b.proname from 9 for (char_length(b.proname)-12)),"
> > ! + "a.tgdeferrable,"
> > ! + "a.tginitdeferred "
> > ! + "FROM "
> > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> > ! + "t.tgdeferrable, t.tginitdeferred "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> > ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_upd') as a, "
> > ! + "(SELECT t.tgconstrname, p.proname "
> > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > ! + "WHERE c.relname like '"+table+"' AND c.relfilenode=t.tgrelid "
> > ! + "AND t.tgfoid = p.oid AND p.proname LIKE 'RI_FKey_%_del') as b "
> > ! + "WHERE a.tgconstrname=b.tgconstrname");
> > ! Vector tuples = new Vector();
> > !
> > ! while (rs.next())
> > ! {
> > ! tuples.add(parseConstraint(rs));
> > ! }
> > !
> > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > }
> >
> > /**
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
Hi all,
I'm not sure, but these are the methods which allow an external
application (i.e. an ERD tool) to query a database and know the
relationships between the tables aren't they?
If so, this is a very important feature which, once implemented
properly, will allow Java tools and programs (for example the ERD tool
"DbDesigner", etc) to work correctly with PostgreSQL.
Given that these are the methods I'm thinking about, I hope this patch
is completed properly and accepted into the JDBC driver asap, even
though we are in beta. We have plenty of time to ensure things work
properly before final release.
:-)
Regards and best wishes,
Justin Clift
Jason Davies wrote:
>
> On Fri, Oct 26, 2001 at 09:52:11AM -0700, Barry Lind wrote:
> > Jason,
> >
> > Can you explain what this patch is attempting to fix? Given that we are
> > in beta with 7.2, I want to fully understand the problem here before
> > applying this patch. (Especially given that the diff is pretty large and
> > not a one or two line change).
>
> I guess "fix" is slightly misleading. This patch implements DatabaseMetaData.getExportedKeys() which was previously
notimplemented, and provides a better implementation of DatabaseMetaData.getImportedKeys() which was previously
half-implemented.
>
> The methods are identical for both jdbc1 and jdbc2 so that's why the diff is quite large. Perhaps identical code
shouldbe put into a single class and have the code which differs in jdbc1 and jdbc2 in subclasses?
>
> > Also what testing have you done on this? (When you ask us to 'check
> > that it works', it doesn't give me a high level of confidence that this
> > is well tested).
>
> :-) I have tested it but only with 7.1.3 at the moment. What I meant to say is please check in case something major
hasbeen changed in 7.2. Also if the queries I'm using can be optimized in any way that would be great.
>
> At the moment a value for PK_NAME (primary key name) is not returned but the main part is implemented.
>
> Sorry for the lack of info, I'll try and be more helpful next time :)
>
> Jason Davies
>
> > thanks,
> > --Barry
> >
> >
> > Jason Davies wrote:
> >
> > > Hi,
> > >
> > > Here is a diff for DatabaseMetaData.getImportedKeys() and
> > > DatabaseMetaData.getExportedKeys(). Please check that it works :)
> > >
> > > Thanks,
> > > Jason Davies
> > >
> > > jason@netspade.com
> > >
> > >
> > > ------------------------------------------------------------------------
> > >
> > > Index: jdbc1/DatabaseMetaData.java
> > > ===================================================================
> > > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
> > > retrieving revision 1.35
> > > diff -c -r1.35 DatabaseMetaData.java
> > > *** jdbc1/DatabaseMetaData.java 2001/10/25 05:59:59 1.35
> > > --- jdbc1/DatabaseMetaData.java 2001/10/26 01:21:02
> > > ***************
> > > *** 2260,2345 ****
> > > );
> > > }
> > >
> > > ! private void importLoop(Vector tuples, java.sql.ResultSet keyRelation) throws SQLException
> > > ! {
> > > ! String s, s2;
> > > ! String origTable = null, primTable = new String(""), schema;
> > > ! int i;
> > > ! Vector v = new Vector();
> > > !
> > > ! s = keyRelation.getString(1);
> > > ! s2 = s;
> > > ! //System.out.println(s);
> > > !
> > > ! for (i = 0;;i++)
> > > ! {
> > > ! s = s.substring(s.indexOf("\\000") + 4);
> > > ! if (s.compareTo("") == 0)
> > > ! {
> > > ! //System.out.println();
> > > ! break;
> > > ! }
> > > ! s2 = s.substring(0, s.indexOf("\\000"));
> > > ! switch (i)
> > > ! {
> > > ! case 0:
> > > ! origTable = s2;
> > > ! break;
> > > ! case 1:
> > > ! primTable = s2;
> > > ! break;
> > > ! case 2:
> > > ! schema = s2;
> > > ! break;
> > > ! default:
> > > ! v.addElement(s2);
> > > ! }
> > > ! }
> > > !
> > > ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> > > ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> > > !
> > > ! String stmp;
> > > ! // Vector tuples=new Vector();
> > > ! byte tuple[][];
> > > !
> > > ! // the foreign keys are only on even positions in the Vector.
> > > ! for (i = 0;i < v.size();i += 2)
> > > {
> > > ! stmp = (String)v.elementAt(i);
> > > !
> > > ! for (int j = 1;j <= origCols.getColumnCount();j++)
> > > {
> > > ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> > > {
> > > ! tuple = new byte[14][0];
> > > !
> > > ! for (int k = 0;k < 14;k++)
> > > ! tuple[k] = null;
> > > !
> > > ! //PKTABLE_NAME
> > > ! tuple[2] = primTable.getBytes();
> > > ! //PKTABLE_COLUMN
> > > ! stmp = (String)v.elementAt(i + 1);
> > > ! tuple[3] = stmp.getBytes();
> > > ! //FKTABLE_NAME
> > > ! tuple[6] = origTable.getBytes();
> > > ! //FKCOLUMN_NAME
> > > ! tuple[7] = origCols.getColumnName(j).getBytes();
> > > ! //KEY_SEQ
> > > ! tuple[8] = Integer.toString(j).getBytes();
> > > !
> > > ! tuples.addElement(tuple);
> > > !
> > > ! //System.out.println(origCols.getColumnName(j)+
> > > ! //": "+j+" -> "+primTable+": "+
> > > ! //(String)v.elementAt(i+1));
> > > ! break;
> > > }
> > > }
> > > }
> > >
> > > ! //return tuples;
> > > }
> > >
> > > /**
> > > --- 2260,2330 ----
> > > );
> > > }
> > >
> > > ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> > > ! {
> > > ! byte tuple[][]=new byte[14][0];
> > > ! for (int k = 0;k < 14;k++)
> > > ! tuple[k] = null;
> > > ! String s=keyRelation.getString(1);
> > > ! int pos=s.indexOf("\\000");
> > > ! if(pos>-1)
> > > ! {
> > > ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> > > ! int pos2=s.indexOf("\\000", pos+1);
> > > ! if(pos2>-1)
> > > {
> > > ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> > > ! pos=s.indexOf("\\000", pos2+1);
> > > ! if(pos>-1)
> > > ! {
> > > ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> > > ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> > > ! if(pos>-1)
> > > {
> > > ! pos2=s.indexOf("\\000",pos+1);
> > > ! if(pos2>-1)
> > > ! {
> > > ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> > > ! pos=s.indexOf("\\000", pos2+1);
> > > ! if(pos>-1)
> > > {
> > > ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> > > }
> > > + }
> > > }
> > > + }
> > > }
> > > + }
> > > +
> > > + // UPDATE_RULE
> > > + String rule=keyRelation.getString(2);
> > > + int action=importedKeyNoAction;
> > > + if("cascade".equals(rule)) action=importedKeyCascade;
> > > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > > + tuple[9]=Integer.toString(action).getBytes();
> > > +
> > > + // DELETE_RULE
> > > + rule=keyRelation.getString(3);
> > > + action=importedKeyNoAction;
> > > + if("cascade".equals(rule)) action=importedKeyCascade;
> > > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > > + tuple[10]=Integer.toString(action).getBytes();
> > > +
> > > + // DEFERRABILITY
> > > + int deferrability=importedKeyNotDeferrable;
> > > + boolean deferrable=keyRelation.getBoolean(4);
> > > + if(deferrable)
> > > + {
> > > + if(keyRelation.getBoolean(5))
> > > + deferrability=importedKeyInitiallyDeferred;
> > > + else
> > > + deferrability=importedKeyInitiallyImmediate;
> > > + }
> > > + tuple[13]=Integer.toString(deferrability).getBytes();
> > >
> > > ! return tuple;
> > > }
> > >
> > > /**
> > > ***************
> > > *** 2395,2405 ****
> > > */
> > > public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > - // Added by Ola Sundell <ola@miranda.org>
> > > - // FIXME: error checking galore!
> > > - java.sql.ResultSet rsret;
> > > Field f[] = new Field[14];
> > > - byte tuple[][];
> > >
> > > f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > > f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > > --- 2380,2386 ----
> > > ***************
> > > *** 2416,2434 ****
> > > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > >
> > > ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> > > ! "from pg_class as c, pg_trigger as t " +
> > > ! "where c.relname like '" + table + "' and
c.relfilenode=t.tgrelid");
> > > Vector tuples = new Vector();
> > >
> > > while (rs.next())
> > > {
> > > ! importLoop(tuples, rs);
> > > }
> > >
> > > ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> > > !
> > > ! return rsret;
> > > }
> > >
> > > /**
> > > --- 2397,2429 ----
> > > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > >
> > > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > > ! + "substring(a.proname from 9 for
(char_length(a.proname)-12)),"
> > > ! + "substring(b.proname from 9 for
(char_length(b.proname)-12)),"
> > > ! + "a.tgdeferrable,"
> > > ! + "a.tginitdeferred "
> > > ! + "FROM "
> > > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,
t.tgdeferrable,"
> > > ! + "t.tginitdeferred "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > > ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> > > ! + "(SELECT t.tgconstrname, p.proname "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > > ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> > > ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger
ast "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid)as c "
> > > ! + "WHERE a.tgconstrname=b.tgconstrname AND
a.tgconstrname=c.tgconstrname"
> > > ! );
> > > Vector tuples = new Vector();
> > >
> > > while (rs.next())
> > > {
> > > ! tuples.add(parseConstraint(rs));
> > > }
> > >
> > > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > > }
> > >
> > > /**
> > > ***************
> > > *** 2486,2492 ****
> > > */
> > > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > ! throw org.postgresql.Driver.notImplemented();
> > > }
> > >
> > > /**
> > > --- 2481,2527 ----
> > > */
> > > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > ! Field f[] = new Field[14];
> > > !
> > > ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > > ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > > ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> > > ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> > > ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> > > ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> > > ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> > > ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> > > ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> > > ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> > > ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> > > ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> > > ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > > !
> > > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > > ! + "substring(a.proname from 9 for
(char_length(a.proname)-12)),"
> > > ! + "substring(b.proname from 9 for
(char_length(b.proname)-12)),"
> > > ! + "a.tgdeferrable,"
> > > ! + "a.tginitdeferred "
> > > ! + "FROM "
> > > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> > > ! + "t.tgdeferrable, t.tginitdeferred "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid"
> > > ! + "AND t.tgfoid = p.oid AND p.proname LIKE
'RI_FKey_%_upd')as a, "
> > > ! + "(SELECT t.tgconstrname, p.proname "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid"
> > > ! + "AND t.tgfoid = p.oid AND p.proname LIKE
'RI_FKey_%_del')as b "
> > > ! + "WHERE a.tgconstrname=b.tgconstrname");
> > > ! Vector tuples = new Vector();
> > > !
> > > ! while (rs.next())
> > > ! {
> > > ! tuples.add(parseConstraint(rs));
> > > ! }
> > > !
> > > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > > }
> > >
> > > /**
> > > Index: jdbc2/DatabaseMetaData.java
> > > ===================================================================
> > > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
> > > retrieving revision 1.40
> > > diff -c -r1.40 DatabaseMetaData.java
> > > *** jdbc2/DatabaseMetaData.java 2001/10/25 05:59:59 1.40
> > > --- jdbc2/DatabaseMetaData.java 2001/10/26 01:21:10
> > > ***************
> > > *** 2273,2359 ****
> > > );
> > > }
> > >
> > > ! private Vector importLoop(java.sql.ResultSet keyRelation) throws SQLException
> > > ! {
> > > ! String s, s2;
> > > ! String origTable = null, primTable = new String(""), schema;
> > > ! int i;
> > > ! Vector v;
> > > !
> > > ! s = keyRelation.getString(1);
> > > ! s2 = s;
> > > ! // System.out.println(s);
> > > ! v = new Vector();
> > > ! for (i = 0;;i++)
> > > ! {
> > > ! s = s.substring(s.indexOf("\\000") + 4);
> > > ! if (s.compareTo("") == 0)
> > > ! {
> > > ! //System.out.println();
> > > ! break;
> > > ! }
> > > ! s2 = s.substring(0, s.indexOf("\\000"));
> > > ! switch (i)
> > > ! {
> > > ! case 0:
> > > ! origTable = s2;
> > > ! break;
> > > ! case 1:
> > > ! primTable = s2;
> > > ! break;
> > > ! case 2:
> > > ! schema = s2;
> > > ! break;
> > > ! default:
> > > ! v.add(s2);
> > > ! }
> > > ! }
> > > !
> > > ! java.sql.ResultSet rstmp = connection.ExecSQL("select * from " + origTable + " where 1=0");
> > > ! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
> > > !
> > > ! String stmp;
> > > ! Vector tuples = new Vector();
> > > ! byte tuple[][];
> > > !
> > > ! // the foreign keys are only on even positions in the Vector.
> > > ! for (i = 0;i < v.size();i += 2)
> > > {
> > > ! stmp = (String)v.elementAt(i);
> > > !
> > > ! for (int j = 1;j <= origCols.getColumnCount();j++)
> > > {
> > > ! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
> > > {
> > > ! tuple = new byte[14][0];
> > > !
> > > ! for (int k = 0;k < 14;k++)
> > > ! tuple[k] = null;
> > > !
> > > ! //PKTABLE_NAME
> > > ! tuple[2] = primTable.getBytes();
> > > ! //PKTABLE_COLUMN
> > > ! stmp = (String)v.elementAt(i + 1);
> > > ! tuple[3] = stmp.getBytes();
> > > ! //FKTABLE_NAME
> > > ! tuple[6] = origTable.getBytes();
> > > ! //FKCOLUMN_NAME
> > > ! tuple[7] = origCols.getColumnName(j).getBytes();
> > > ! //KEY_SEQ
> > > ! tuple[8] = Integer.toString(j).getBytes();
> > > !
> > > ! tuples.add(tuple);
> > > ! /*
> > > ! System.out.println(origCols.getColumnName(j)+
> > > ! ": "+j+" -> "+primTable+": "+
> > > ! (String)v.elementAt(i+1));
> > > ! */
> > > ! break;
> > > }
> > > }
> > > }
> > >
> > > ! return tuples;
> > > }
> > >
> > > /**
> > > --- 2273,2343 ----
> > > );
> > > }
> > >
> > > ! private byte[][] parseConstraint(java.sql.ResultSet keyRelation) throws SQLException
> > > ! {
> > > ! byte tuple[][]=new byte[14][0];
> > > ! for (int k = 0;k < 14;k++)
> > > ! tuple[k] = null;
> > > ! String s=keyRelation.getString(1);
> > > ! int pos=s.indexOf("\\000");
> > > ! if(pos>-1)
> > > ! {
> > > ! tuple[11]=s.substring(0,pos).getBytes();; // FK_NAME
> > > ! int pos2=s.indexOf("\\000", pos+1);
> > > ! if(pos2>-1)
> > > {
> > > ! tuple[2]=s.substring(pos+4, pos2).getBytes();; // PKTABLE_NAME
> > > ! pos=s.indexOf("\\000", pos2+1);
> > > ! if(pos>-1)
> > > ! {
> > > ! tuple[6]=s.substring(pos2+4, pos).getBytes();; // FKTABLE_NAME
> > > ! pos=s.indexOf("\\000", pos+1); // Ignore MATCH type
> > > ! if(pos>-1)
> > > {
> > > ! pos2=s.indexOf("\\000",pos+1);
> > > ! if(pos2>-1)
> > > ! {
> > > ! tuple[3]=s.substring(pos+4, pos2).getBytes();; // PKCOLUMN_NAME
> > > ! pos=s.indexOf("\\000", pos2+1);
> > > ! if(pos>-1)
> > > {
> > > ! tuple[7]=s.substring(pos2+4, pos).getBytes(); //FKCOLUMN_NAME
> > > }
> > > + }
> > > }
> > > + }
> > > }
> > > + }
> > > +
> > > + // UPDATE_RULE
> > > + String rule=keyRelation.getString(2);
> > > + int action=importedKeyNoAction;
> > > + if("cascade".equals(rule)) action=importedKeyCascade;
> > > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > > + tuple[9]=Integer.toString(action).getBytes();
> > > +
> > > + // DELETE_RULE
> > > + rule=keyRelation.getString(3);
> > > + action=importedKeyNoAction;
> > > + if("cascade".equals(rule)) action=importedKeyCascade;
> > > + else if("setnull".equals(rule)) action=importedKeySetNull;
> > > + else if("setdefault".equals(rule)) action=importedKeySetDefault;
> > > + tuple[10]=Integer.toString(action).getBytes();
> > > +
> > > + // DEFERRABILITY
> > > + int deferrability=importedKeyNotDeferrable;
> > > + boolean deferrable=keyRelation.getBoolean(4);
> > > + if(deferrable)
> > > + {
> > > + if(keyRelation.getBoolean(5))
> > > + deferrability=importedKeyInitiallyDeferred;
> > > + else
> > > + deferrability=importedKeyInitiallyImmediate;
> > > + }
> > > + tuple[13]=Integer.toString(deferrability).getBytes();
> > >
> > > ! return tuple;
> > > }
> > >
> > > /**
> > > ***************
> > > *** 2409,2419 ****
> > > */
> > > public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > - // Added by Ola Sundell <ola@miranda.org>
> > > - // FIXME: error checking galore!
> > > - java.sql.ResultSet rsret;
> > > Field f[] = new Field[14];
> > > - byte tuple[][];
> > >
> > > f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > > f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > > --- 2393,2399 ----
> > > ***************
> > > *** 2430,2448 ****
> > > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > >
> > > ! java.sql.ResultSet rs = connection.ExecSQL("select t.tgargs " +
> > > ! "from pg_class as c, pg_trigger as t " +
> > > ! "where c.relname like '" + table + "' and
c.relfilenode=t.tgrelid");
> > > Vector tuples = new Vector();
> > >
> > > while (rs.next())
> > > {
> > > ! tuples.addAll(importLoop(rs));
> > > }
> > >
> > > ! rsret = new ResultSet(connection, f, tuples, "OK", 1);
> > > !
> > > ! return rsret;
> > > }
> > >
> > > /**
> > > --- 2410,2442 ----
> > > f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > >
> > > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > > ! + "substring(a.proname from 9 for
(char_length(a.proname)-12)),"
> > > ! + "substring(b.proname from 9 for
(char_length(b.proname)-12)),"
> > > ! + "a.tgdeferrable,"
> > > ! + "a.tginitdeferred "
> > > ! + "FROM "
> > > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,
t.tgdeferrable,"
> > > ! + "t.tginitdeferred "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > > ! + "AND p.proname LIKE 'RI_FKey_%_upd') as a,"
> > > ! + "(SELECT t.tgconstrname, p.proname "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relfilenode=t.tgrelid AND t.tgfoid = p.oid "
> > > ! + "AND p.proname LIKE 'RI_FKey_%_del') as b,"
> > > ! + "(SELECT t.tgconstrname FROM pg_class as c, pg_trigger
ast "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid)as c "
> > > ! + "WHERE a.tgconstrname=b.tgconstrname AND
a.tgconstrname=c.tgconstrname"
> > > ! );
> > > Vector tuples = new Vector();
> > >
> > > while (rs.next())
> > > {
> > > ! tuples.add(parseConstraint(rs));
> > > }
> > >
> > > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > > }
> > >
> > > /**
> > > ***************
> > > *** 2500,2506 ****
> > > */
> > > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > ! throw org.postgresql.Driver.notImplemented();
> > > }
> > >
> > > /**
> > > --- 2494,2540 ----
> > > */
> > > public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
> > > {
> > > ! Field f[] = new Field[14];
> > > !
> > > ! f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
> > > ! f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
> > > ! f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
> > > ! f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
> > > ! f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
> > > ! f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
> > > ! f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
> > > ! f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
> > > ! f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
> > > ! f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
> > > ! f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
> > > ! f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
> > > ! f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
> > > ! f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
> > > !
> > > ! java.sql.ResultSet rs = connection.ExecSQL("SELECT a.tgargs,"
> > > ! + "substring(a.proname from 9 for
(char_length(a.proname)-12)),"
> > > ! + "substring(b.proname from 9 for
(char_length(b.proname)-12)),"
> > > ! + "a.tgdeferrable,"
> > > ! + "a.tginitdeferred "
> > > ! + "FROM "
> > > ! + "(SELECT t.tgargs, t.tgconstrname, p.proname,"
> > > ! + "t.tgdeferrable, t.tginitdeferred "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid"
> > > ! + "AND t.tgfoid = p.oid AND p.proname LIKE
'RI_FKey_%_upd')as a, "
> > > ! + "(SELECT t.tgconstrname, p.proname "
> > > ! + "FROM pg_class as c, pg_proc as p, pg_trigger as t "
> > > ! + "WHERE c.relname like '"+table+"' AND
c.relfilenode=t.tgrelid"
> > > ! + "AND t.tgfoid = p.oid AND p.proname LIKE
'RI_FKey_%_del')as b "
> > > ! + "WHERE a.tgconstrname=b.tgconstrname");
> > > ! Vector tuples = new Vector();
> > > !
> > > ! while (rs.next())
> > > ! {
> > > ! tuples.add(parseConstraint(rs));
> > > ! }
> > > !
> > > ! return new ResultSet(connection, f, tuples, "OK", 1);
> > > }
> > >
> > > /**
> > >
> > >
> > > ------------------------------------------------------------------------
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
On Sat, Oct 27, 2001 at 03:14:48PM +1000, Justin Clift wrote: > Hi all, > > I'm not sure, but these are the methods which allow an external > application (i.e. an ERD tool) to query a database and know the > relationships between the tables aren't they? Yes. > If so, this is a very important feature which, once implemented > properly, will allow Java tools and programs (for example the ERD tool > "DbDesigner", etc) to work correctly with PostgreSQL. > > Given that these are the methods I'm thinking about, I hope this patch > is completed properly and accepted into the JDBC driver asap, even > though we are in beta. We have plenty of time to ensure things work > properly before final release. > > :-) Could this patch be applied then, before it's forgotten? > Regards and best wishes, > > Justin Clift > > Thanks :) -- Jason Davies jason@netspade.com
Attachment
> > If so, this is a very important feature which, once implemented > > properly, will allow Java tools and programs (for example the ERD tool > > "DbDesigner", etc) to work correctly with PostgreSQL. > > > > Given that these are the methods I'm thinking about, I hope this patch > > is completed properly and accepted into the JDBC driver asap, even > > though we are in beta. We have plenty of time to ensure things work > > properly before final release. > > > > :-) > > Could this patch be applied then, before it's forgotten? So this was applied, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, Nov 02, 2001 at 09:11:13PM -0500, Bruce Momjian wrote: > > > If so, this is a very important feature which, once implemented > > > properly, will allow Java tools and programs (for example the ERD tool > > > "DbDesigner", etc) to work correctly with PostgreSQL. > > > > > > Given that these are the methods I'm thinking about, I hope this patch > > > is completed properly and accepted into the JDBC driver asap, even > > > though we are in beta. We have plenty of time to ensure things work > > > properly before final release. > > > > > > :-) > > > > Could this patch be applied then, before it's forgotten? > > So this was applied, right? Yes, thanks. > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Jason Davies jason@netspade.com