Re: JDBC
От | Russell Black |
---|---|
Тема | Re: JDBC |
Дата | |
Msg-id | 0a4501c1b31e$2f2fb110$0464a8c0@iarchives.com обсуждение исходный текст |
Ответ на | JDBC ("Russell Black" <russell.black@iarchives.com>) |
Список | 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 > > > >
В списке pgsql-patches по дате отправления: