Re: JDBC

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(at)xythos(dot)com>
To: "Russell Black" <russell(dot)black(at)iarchives(dot)com>
Cc: <pgsql-patches(at)postgresql(dot)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
> >
>
>