Re: JDBC - Mailing list pgsql-patches

Thanks,

I have been working of the source that comes with cygwin, which is a bit
behind, apparantly.

Russell

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Russell Black" <russell.black@iarchives.com>
Cc: <pgsql-patches@postgresql.org>
Sent: Friday, February 08, 2002 9:14 PM
Subject: Re: [PATCHES] JDBC


> Russell,
>
> Thanks for the patch, but these methods have already been implemented in
> current sources, so your patch is not needed.  Since as you say you are
> new to open source, it is generally a good idea to look at current
> sources before submitting a patch to avoid duplication of work.  You can
> access the current source code via anon cvs by following the
> instructions located on the developer.postgresql.org website.
>
> thanks,
> --Barry
>
>
>
> Russell Black wrote:
>
> > I've made implementations for
> >
> >
> >
> > DatabaseMetaData.getImportedKeys,
> >
> > DatabaseMetaData.getExportedKeys, and
> >
> > DatabaseMetaData.getCrossReference.
> >
> >
> >
> > I don't have a .patch file, because I'm new to open-sourcing and don't
> > know how to make one, but I have attatched the relevant code segment.
> >
> >
> >
> > Please tell me if there's something else I need to do to have the patch
> > applied.
> >
> >
> >
> > Thanks,
> >
> > Russell
> >
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> > These are implementations for functions in DatabaseMetaData (jdbc1 and
jdbc2)
> >
> >   private java.sql.ResultSet getFKResultSet(java.sql.ResultSet results)
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", iBoolOid, 1);
> >
> >     Map deleteRules = new HashMap();
> >     Map updateRules = new HashMap();
> >     Map deferrabilityMap = new HashMap();
> >     final Map keySeqs = new HashMap();
> >
> >     while ( results.next() ) {
> >
> >       String proname = results.getString(1);
> >       int tgtype = results.getInt(2);
> >       boolean deferrable = results.getBoolean(3);
> >       boolean initDeferred = results.getBoolean(4);
> >       byte[] bytes = results.getBytes(5);
> >
> >       TGArgs args = new TGArgs(bytes);
> >
> >       int rule = -1;
> >       if ( proname.indexOf("noaction") >= 0) {
> >         rule = importedKeyNoAction;
> >       } // end of if ()
> >       else if ( proname.indexOf("restrict") >= 0 ) {
> >         rule = importedKeyRestrict;
> >       } // end of if ()
> >       else if ( proname.indexOf("cascade") >= 0 ) {
> >         rule = importedKeyCascade;
> >       } // end of if ()
> >       else if ( proname.indexOf("setnull") >= 0) {
> >         rule = importedKeySetNull;
> >       } // end of if ()
> >       else if ( proname.indexOf("setdefault") >= 0) {
> >         rule = importedKeySetDefault;
> >       } // end of if ()
> >
> >       switch ( tgtype ) {
> >
> >       case 9:
> >         deferrabilityMap.put(args, new Integer
> >           (deferrable?
> >            ( initDeferred ? importedKeyInitiallyDeferred :
importedKeyInitiallyImmediate )
> >            : importedKeyNotDeferrable));
> >         deleteRules.put(args, new Integer(rule));
> >         break;
> >
> >       case 17:
> >         updateRules.put(args, new Integer(rule));
> >         break;
> >       }
> >     }
> >
> >     List keys = new ArrayList(deleteRules.keySet());
> >
> >     // get sequence
> >     for ( Iterator i = keys.iterator(); i.hasNext();  ) {
> >       TGArgs args = (TGArgs)i.next();
> >       results = connection.createStatement().executeQuery("select attnum
from pg_attribute, pg_class where attname = '" + args.fkColumn + "' and
attrelid = pg_class.oid and relname = '"+args.fkTable +"';");
> >       results.next();
> >       keySeqs.put(args, new Integer(results.getInt(1)));
> >     } // end of for ()
> >
> >     // sort the list
> >     Collections.sort(keys, new Comparator() {
> >         public int compare(Object o1, Object o2) {
> >           return
((Integer)keySeqs.get(o1)).compareTo((Integer)keySeqs.get(o2));
> >         }
> >       });
> >
> >     Vector v = new Vector();
> >
> >     for ( Iterator i = keys.iterator(); i.hasNext();  ) {
> >       TGArgs args = (TGArgs)i.next();
> >       Integer updateRule = (Integer)updateRules.get(args);
> >       Integer deleteRule = (Integer)deleteRules.get(args);
> >       Integer keySeq = (Integer)keySeqs.get(args);
> >       Integer deferrability = (Integer)deferrabilityMap.get(args);
> >
> >       byte[][] tuple = new byte[14][0];
> >
> >       tuple[0] = null;
> >       tuple[1] = null;
> >       tuple[2] = args.pkTable.getBytes(); // Primary key table name
> >       tuple[3] = args.pkColumn.getBytes();     // Primary key column
name
> >       tuple[4] = null;
> >       tuple[5] = null;
> >       tuple[6] = args.fkTable.getBytes(); // foreign key table name
> >       tuple[7] = args.fkColumn.getBytes();     // foreign key column
name
> >       tuple[8] = keySeq.toString().getBytes();  //key_seq
> >       tuple[9] = updateRule.toString().getBytes();
> >       tuple[10] = deleteRule.toString().getBytes();
> >       tuple[11] = args.name.getBytes(); // fk name
> >       tuple[12] = null; // pk name
> >       tuple[13] = deferrability.toString().getBytes(); // deferrability
> >
> >       v.addElement(tuple);
> >
> >     } // end of while ()
> >
> >     return new ResultSet(connection, f, v, "OK", 1);;
> >   }
> >
> >   private static class TGArgs {
> >     String name;
> >     String fkTable;
> >     String pkTable;
> >     String matchType;
> >     String fkColumn;
> >     String pkColumn;
> >
> >     TGArgs(byte[] tgargs) {
> >       ArrayList list = new ArrayList();
> >       // tokenize on \000
> >       int beginning = 0;
> >       for ( int i=0; i<tgargs.length; i++) {
> >         byte b = tgargs[i];
> >         if ( b == (byte)'\\' ) {
> >           list.add(new String(tgargs, beginning, i - beginning));
> >           i += 3;
> >           beginning = i+1;
> >         } // end of if ()
> >       } // end of for ()
> >       String[] args = (String[])list.toArray(new String[list.size()]);
> >       name = args[0];
> >       fkTable = args[1];
> >       pkTable = args[2];
> >       matchType = args[3];
> >       fkColumn = args[4];
> >       pkColumn = args[5];
> >     }
> >     public boolean equals(Object o) {
> >       if ( ! (o instanceof TGArgs) ) return false;
> >       TGArgs other = (TGArgs)o;
> >       return name.equals(other.name)
> >         && fkTable.equals(other.fkTable)
> >         && pkTable.equals(other.pkTable)
> >         && matchType.equals(other.matchType)
> >         && fkColumn.equals(other.fkColumn)
> >         && pkColumn.equals(other.pkColumn);
> >     }
> >     public int hashCode() {
> >       return fkTable.hashCode()
> >         + pkTable.hashCode()
> >         + fkColumn.hashCode()
> >         + pkColumn.hashCode();
> >     }
> >   }
> >
> >   /**
> >    * Get a description of the primary key columns that are
> >    * referenced by a table's foreign key columns (the primary keys
> >    * imported by a table).  They are ordered by PKTABLE_CAT,
> >    * PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
> >    *
> >    * <P>Each primary key column description has the following columns:
> >    *  <OL>
> >    * <LI><B>PKTABLE_CAT</B> String => primary key table catalog
> >    *      being imported (may be null)
> >    * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
> >    *      being imported (may be null)
> >    * <LI><B>PKTABLE_NAME</B> String => primary key table name
> >    *      being imported
> >    * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
> >    *      being imported
> >    * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
null)
> >    * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
null)
> >    * <LI><B>FKTABLE_NAME</B> String => foreign key table name
> >    * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
> >    * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
> >    * <LI><B>UPDATE_RULE</B> short => What happens to
> >    *       foreign key when primary is updated:
> >    *      <UL>
> >    *      <LI> importedKeyCascade - change imported key to agree
> >    *               with primary key update
> >    *      <LI> importedKeyRestrict - do not allow update of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been updated
> >    *      </UL>
> >    * <LI><B>DELETE_RULE</B> short => What happens to
> >    *      the foreign key when primary is deleted.
> >    *      <UL>
> >    *      <LI> importedKeyCascade - delete rows that import a deleted
key
> >    *      <LI> importedKeyRestrict - do not allow delete of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been deleted
> >    *      </UL>
> >    * <LI><B>FK_NAME</B> String => foreign key name (may be null)
> >    * <LI><B>PK_NAME</B> String => primary key name (may be null)
> >    *    <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign
key constraints be deferred until commit
> >    *       <UL>
> >    *       <LI>importedKeyInitiallyDeferred - see SQL92 for definition
> >    *       <LI>importedKeyInitiallyImmediate - see SQL92 for definition
> >    *       <LI>importedKeyNotDeferrable - see SQL92 for definition
> >    *       </UL>
> >    *  </OL>
> >    *
> >    * @param catalog a catalog name; "" retrieves those without a catalog
> >    * @param schema a schema name pattern; "" retrieves those
> >    * without a schema
> >    * @param table a table name
> >    * @return ResultSet each row is a primary key column description
> >    * @see #getExportedKeys
> >    */
> >   public java.sql.ResultSet getImportedKeys(String catalog, String
schema, String table) throws SQLException
> >   {
> >     java.sql.ResultSet results =
connection.createStatement().executeQuery("select proname, tgtype,
tgdeferrable, tginitdeferred, tgargs  from pg_class, pg_trigger, pg_proc
where pg_class.oid = tgconstrrelid and relname='" + table + "' and tgfoid =
pg_proc.oid and (tgtype = 9 or tgtype = 17)");
> >
> >     return getFKResultSet(results);
> >   }
> >
> >
> >   /**
> >    * Get a description of a foreign key columns that reference a
> >    * table's primary key columns (the foreign keys exported by a
> >    * table).  They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
> >    * FKTABLE_NAME, and KEY_SEQ.
> >    *
> >    * <P>Each foreign key column description has the following columns:
> >    *  <OL>
> >    * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
null)
> >    * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
null)
> >    * <LI><B>PKTABLE_NAME</B> String => primary key table name
> >    * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
> >    * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
null)
> >    *      being exported (may be null)
> >    * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
null)
> >    *      being exported (may be null)
> >    * <LI><B>FKTABLE_NAME</B> String => foreign key table name
> >    *      being exported
> >    * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
> >    *      being exported
> >    * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
> >    * <LI><B>UPDATE_RULE</B> short => What happens to
> >    *       foreign key when primary is updated:
> >    *      <UL>
> >    *      <LI> importedKeyCascade - change imported key to agree
> >    *               with primary key update
> >    *      <LI> importedKeyRestrict - do not allow update of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been updated
> >    *      </UL>
> >    * <LI><B>DELETE_RULE</B> short => What happens to
> >    *      the foreign key when primary is deleted.
> >    *      <UL>
> >    *      <LI> importedKeyCascade - delete rows that import a deleted
key
> >    *      <LI> importedKeyRestrict - do not allow delete of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been deleted
> >    *      </UL>
> >    * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
> >    * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
> >    *    <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign
key constraints be deferred until commit
> >    *       <UL>
> >    *       <LI>importedKeyInitiallyDeferred - see SQL92 for definition
> >    *       <LI>importedKeyInitiallyImmediate - see SQL92 for definition
> >    *       <LI>importedKeyNotDeferrable - see SQL92 for definition
> >    *       </UL>
> >    *  </OL>
> >    *
> >    * @param catalog a catalog name; "" retrieves those without a catalog
> >    * @param schema a schema name pattern; "" retrieves those
> >    * without a schema
> >    * @param table a table name
> >    * @return ResultSet each row is a foreign key column description
> >    * @see #getImportedKeys
> >    */
> >   public java.sql.ResultSet getExportedKeys(String catalog, String
schema, String table) throws SQLException
> >   {
> >     java.sql.ResultSet results =
connection.createStatement().executeQuery("select proname, tgtype,
tgdeferrable, tginitdeferred, tgargs  from pg_class, pg_trigger, pg_proc
where pg_class.oid = tgrelid and relname='" + table + "' and tgfoid =
pg_proc.oid and (tgtype = 9 or tgtype = 17)");
> >
> >     return getFKResultSet(results);
> >   }
> >
> >   /**
> >    * Get a description of the foreign key columns in the foreign key
> >    * table that reference the primary key columns of the primary key
> >    * table (describe how one table imports another's key.) This
> >    * should normally return a single foreign key/primary key pair
> >    * (most tables only import a foreign key from a table once.)  They
> >    * are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
> >    * KEY_SEQ.
> >    *
> >    * <P>Each foreign key column description has the following columns:
> >    *  <OL>
> >    * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
null)
> >    * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
null)
> >    * <LI><B>PKTABLE_NAME</B> String => primary key table name
> >    * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
> >    * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
null)
> >    *      being exported (may be null)
> >    * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
null)
> >    *      being exported (may be null)
> >    * <LI><B>FKTABLE_NAME</B> String => foreign key table name
> >    *      being exported
> >    * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
> >    *      being exported
> >    * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
> >    * <LI><B>UPDATE_RULE</B> short => What happens to
> >    *       foreign key when primary is updated:
> >    *      <UL>
> >    *      <LI> importedKeyCascade - change imported key to agree
> >    *               with primary key update
> >    *      <LI> importedKeyRestrict - do not allow update of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been updated
> >    *      </UL>
> >    * <LI><B>DELETE_RULE</B> short => What happens to
> >    *      the foreign key when primary is deleted.
> >    *      <UL>
> >    *      <LI> importedKeyCascade - delete rows that import a deleted
key
> >    *      <LI> importedKeyRestrict - do not allow delete of primary
> >    *               key if it has been imported
> >    *      <LI> importedKeySetNull - change imported key to NULL if
> >    *               its primary key has been deleted
> >    *      </UL>
> >    * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
> >    * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
> >    *    <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign
key constraints be deferred until commit
> >    *       <UL>
> >    *       <LI>importedKeyInitiallyDeferred - see SQL92 for definition
> >    *       <LI>importedKeyInitiallyImmediate - see SQL92 for definition
> >    *       <LI>importedKeyNotDeferrable - see SQL92 for definition
> >    *       </UL>
> >    *  </OL>
> >    *
> >    * @param catalog a catalog name; "" retrieves those without a catalog
> >    * @param schema a schema name pattern; "" retrieves those
> >    * without a schema
> >    * @param table a table name
> >    * @return ResultSet each row is a foreign key column description
> >    * @see #getImportedKeys
> >    */
> >   public java.sql.ResultSet getCrossReference(String primaryCatalog,
String primarySchema, String primaryTable, String foreignCatalog, String
foreignSchema, String foreignTable) throws SQLException
> >   {
> >     java.sql.ResultSet results =
connection.createStatement().executeQuery("select proname, tgtype,
tgdeferrable, tginitdeferred, tgargs from pg_trigger, pg_proc where
tgconstrrelid = (select oid from pg_class where relname =
'"+foreignTable+"') and tgrelid = (select oid from pg_class where relname =
'" + primaryTable + "') and tgfoid = pg_proc.oid and (tgtype = 9 or tgtype =
17) ;");
> >     return getFKResultSet(results);
> >   }
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>