Re: 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
>