databasemetadata.java

关系型数据库 Postgresql 6.5.2

    StringBuffer sql = new StringBuffer("select relname,oid from pg_class where (");
    boolean notFirst=false;
    for(int i=0;i<types.length;i++) {
      if(notFirst)
	sql.append(" or ");
      for(int j=0;j<getTableTypes.length;j++)
	if(getTableTypes[j][0].equals(types[i])) {
	  sql.append(getTableTypes[j][1]);
	  notFirst=true;
	}
    }
    
    // Added by Stefan Andreasen <[e-mail]>
    // Now take the pattern into account
    sql.append(") and relname like '");
    sql.append(tableNamePattern.toLowerCase());
    sql.append("'");
    
    // Now run the query
    r = connection.ExecSQL(sql.toString());
    
    byte remarks[];
    
    while (r.next())
      {
	byte[][] tuple = new byte[5][0];
	
	// Fetch the description for the table (if any)
	java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(2));
	if(((postgresql.ResultSet)dr).getTupleCount()==1) {
	  dr.next();
	  remarks = dr.getBytes(1);
	} else
	  remarks = defaultRemarks;
	dr.close();
	
	tuple[0] = null;		// Catalog name
	tuple[1] = null;		// Schema name
	tuple[2] = r.getBytes(1);	// Table name
	tuple[3] = null;		// Table type
	tuple[4] = remarks;		// Remarks
	v.addElement(tuple);
      }
    r.close();
    return new ResultSet(connection, f, v, "OK", 1);
  }
  
  // This array contains the valid values for the types argument
  // in getTables().
  //
  // Each supported type consists of it's name, and the sql where
  // clause to retrieve that value.
  //
  // IMPORTANT: the query must be enclosed in ( )
  private static final String getTableTypes[][] = {
    {"TABLE",		"(relkind='r' and relname !~ '^pg_' and relname !~ '^xinv')"},
    {"INDEX",		"(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"},
    {"LARGE OBJECT",	"(relkind='r' and relname ~ '^xinv')"},
    {"SEQUENCE",	"(relkind='S' and relname !~ '^pg_')"},
    {"SYSTEM TABLE",	"(relkind='r' and relname ~ '^pg_')"},
    {"SYSTEM INDEX",	"(relkind='i' and relname ~ '^pg_')"}
  };
  
  // These are the default tables, used when NULL is passed to getTables
  // The choice of these provide the same behaviour as psql's \d
  private static final String defaultTableTypes[] = {
    "TABLE","INDEX","SEQUENCE"
  };
  
  /**
   * Get the schema names available in this database.  The results
   * are ordered by schema name.
   *
   * <P>The schema column is:
   *  <OL>
   *	<LI><B>TABLE_SCHEM</B> String => schema name
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * schema name
   */
  public java.sql.ResultSet getSchemas() throws SQLException
  {
    // We don't use schemas, so we simply return a single schema name "".
    //
    Field f[] = new Field[1];
    Vector v = new Vector();
    byte[][] tuple = new byte[1][0];
    f[0] = new Field(connection,new String("TABLE_SCHEM"),iVarcharOid,32);
    tuple[0] = "".getBytes();
    v.addElement(tuple);
    return new ResultSet(connection,f,v,"OK",1);
  }
  
  /**
   * Get the catalog names available in this database.  The results
   * are ordered by catalog name.
   *
   * <P>The catalog column is:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => catalog name
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * catalog name
   */
  public java.sql.ResultSet getCatalogs() throws SQLException
  {
    // We don't use catalogs, so we simply return a single catalog name "".
    Field f[] = new Field[1];
    Vector v = new Vector();
    byte[][] tuple = new byte[1][0];
    f[0] = new Field(connection,new String("TABLE_CAT"),iVarcharOid,32);
    tuple[0] = "".getBytes();
    v.addElement(tuple);
    return new ResultSet(connection,f,v,"OK",1);
  }
  
  /**
   * Get the table types available in this database.  The results
   * are ordered by table type.
   *
   * <P>The table type is:
   *  <OL>
   *	<LI><B>TABLE_TYPE</B> String => table type.  Typical types are "TABLE",
   *			"VIEW",	"SYSTEM TABLE", "GLOBAL TEMPORARY",
   *			"LOCAL TEMPORARY", "ALIAS", "SYNONYM".
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * table type
   */
  public java.sql.ResultSet getTableTypes() throws SQLException
  {
    Field f[] = new Field[1];
    Vector v = new Vector();
    byte[][] tuple = new byte[1][0];
    f[0] = new Field(connection,new String("TABLE_TYPE"),iVarcharOid,32);
    for(int i=0;i<getTableTypes.length;i++) {
      tuple[0] = getTableTypes[i][0].getBytes();
      v.addElement(tuple);
    }
    return new ResultSet(connection,f,v,"OK",1);
  }
  
  /**
   * Get a description of table columns available in a catalog.
   *
   * <P>Only column descriptions matching the catalog, schema, table
   * and column name criteria are returned.  They are ordered by
   * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
   *
   * <P>Each column description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
   *	<LI><B>TYPE_NAME</B> String => Data source dependent type name
   *	<LI><B>COLUMN_SIZE</B> int => column size.  For char or date
   *	    types this is the maximum number of characters, for numeric or
   *	    decimal types this is precision.
   *	<LI><B>BUFFER_LENGTH</B> is not used.
   *	<LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
   *	<LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
   *	<LI><B>NULLABLE</B> int => is NULL allowed?
   *      <UL>
   *      <LI> columnNoNulls - might not allow NULL values
   *      <LI> columnNullable - definitely allows NULL values
   *      <LI> columnNullableUnknown - nullability unknown
   *      </UL>
   *	<LI><B>REMARKS</B> String => comment describing column (may be null)
   * 	<LI><B>COLUMN_DEF</B> String => default value (may be null)
   *	<LI><B>SQL_DATA_TYPE</B> int => unused
   *	<LI><B>SQL_DATETIME_SUB</B> int => unused
   *	<LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
   *       maximum number of bytes in the column
   *	<LI><B>ORDINAL_POSITION</B> int	=> index of column in table
   *      (starting at 1)
   *	<LI><B>IS_NULLABLE</B> String => "NO" means column definitely
   *      does not allow NULL values; "YES" means the column might
   *      allow NULL values.  An empty string means nobody knows.
   *  </OL>
   *
   * @param catalog a catalog name; "" retrieves those without a catalog
   * @param schemaPattern a schema name pattern; "" retrieves those
   * without a schema
   * @param tableNamePattern a table name pattern
   * @param columnNamePattern a column name pattern
   * @return ResultSet each row is a column description
   * @see #getSearchStringEscape
   */
  public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException
  {
    // the field descriptors for the new ResultSet
    Field f[] = new Field[18];
    java.sql.ResultSet r;	// ResultSet for the SQL query that we need to do
    Vector v = new Vector();		// The new ResultSet tuple stuff
    
    f[0] = new Field(connection, new String("TABLE_CAT"), iVarcharOid, 32);
    f[1] = new Field(connection, new String("TABLE_SCHEM"), iVarcharOid, 32);
    f[2] = new Field(connection, new String("TABLE_NAME"), iVarcharOid, 32);
    f[3] = new Field(connection, new String("COLUMN_NAME"), iVarcharOid, 32);
    f[4] = new Field(connection, new String("DATA_TYPE"), iInt2Oid, 2);
    f[5] = new Field(connection, new String("TYPE_NAME"), iVarcharOid, 32);
    f[6] = new Field(connection, new String("COLUMN_SIZE"), iInt4Oid, 4);
    f[7] = new Field(connection, new String("BUFFER_LENGTH"), iVarcharOid, 32);
    f[8] = new Field(connection, new String("DECIMAL_DIGITS"), iInt4Oid, 4);
    f[9] = new Field(connection, new String("NUM_PREC_RADIX"), iInt4Oid, 4);
    f[10] = new Field(connection, new String("NULLABLE"), iInt4Oid, 4);
    f[11] = new Field(connection, new String("REMARKS"), iVarcharOid, 32);
    f[12] = new Field(connection, new String("COLUMN_DEF"), iVarcharOid, 32);
    f[13] = new Field(connection, new String("SQL_DATA_TYPE"), iInt4Oid, 4);
    f[14] = new Field(connection, new String("SQL_DATETIME_SUB"), iInt4Oid, 4);
    f[15] = new Field(connection, new String("CHAR_OCTET_LENGTH"), iVarcharOid, 32);
    f[16] = new Field(connection, new String("ORDINAL_POSITION"), iInt4Oid,4);
    f[17] = new Field(connection, new String("IS_NULLABLE"), iVarcharOid, 32);
    
    // Added by Stefan Andreasen <[e-mail]>
    // If the pattern are  null then set them to %
    if (tableNamePattern == null) tableNamePattern="%";
    if (columnNamePattern == null) columnNamePattern="%";
    
    // Now form the query
    // Modified by Stefan Andreasen <[e-mail]>
    r = connection.ExecSQL("select a.oid,c.relname,a.attname,a.atttypid,a.attnum,a.attnotnull,a.attlen,a.atttypmod from pg_class c, pg_attribute a where a.attrelid=c.oid and c.relname like '"+tableNamePattern.toLowerCase()+"' and a.attname like '"+columnNamePattern.toLowerCase()+"' and a.attnum>0 order by c.relname,a.attnum");
    
    byte remarks[];
    
    while(r.next()) {
	byte[][] tuple = new byte[18][0];
	
	// Fetch the description for the table (if any)
	java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(1));
	if(((postgresql.ResultSet)dr).getTupleCount()==1) {
	  dr.next();
	  tuple[11] = dr.getBytes(1);
	} else
	  tuple[11] = defaultRemarks;
	
	dr.close();
	
	tuple[0] = "".getBytes();	// Catalog name
	tuple[1] = "".getBytes();	// Schema name
	tuple[2] = r.getBytes(2);	// Table name
	tuple[3] = r.getBytes(3);	// Column name
	
	dr = connection.ExecSQL("select typname from pg_type where oid = "+r.getString(4));
	dr.next();
	String typname=dr.getString(1);
	dr.close();
	tuple[4] = Integer.toString(Field.getSQLType(typname)).getBytes();	// Data type
	tuple[5] = typname.getBytes();	// Type name
	
	// Column size
	// Looking at the psql source,
	// I think the length of a varchar as specified when the table was created
	// should be extracted from atttypmod which contains this length + sizeof(int32)
	if (typname.equals("bpchar") || typname.equals("varchar")) {
	  int atttypmod = r.getInt(8);
	  tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod - VARHDRSZ : 0).getBytes();
	} else
	  tuple[6] = r.getBytes(7);
	
	tuple[7] = null;	// Buffer length
	
	tuple[8] = "0".getBytes();	// Decimal Digits - how to get this?
	tuple[9] = "10".getBytes();	// Num Prec Radix - assume decimal
	
	// tuple[10] is below
	// tuple[11] is above
	
	tuple[12] = null;	// column default
	
	tuple[13] = null;	// sql data type (unused)
	tuple[14] = null;	// sql datetime sub (unused)
	
	tuple[15] = tuple[6];	// char octet length
	
	tuple[16] = r.getBytes(5);	// ordinal position
	
	String nullFlag = r.getString(6);
	tuple[10] = Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes();	// Nullable
	tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes();	// is nullable
	
	v.addElement(tuple);
      }
    r.close();
    return new ResultSet(connection, f, v, "OK", 1);
  }
  
  /**
   * Get a description of the access rights for a table's columns.
   *
   * <P>Only privileges matching the column name criteria are
   * returned.  They are ordered by COLUMN_NAME and PRIVILEGE.
   *
   * <P>Each privilige description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>GRANTOR</B> => grantor of access (may be null)
   *	<LI><B>GRANTEE</B> String => grantee of access
   *	<LI><B>PRIVILEGE</B> String => name of access (SELECT,
   *      INSERT, UPDATE, REFRENCES, ...)
   *	<LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
   *      to grant to others; "NO" if not; null if unknown
   *  </OL>
   *
   * @param catalog a catalog name; "" retrieves those without a catalog
   * @param schema a schema name; "" retrieves those without a schema
   * @param table a table name
   * @param columnNamePattern a column name pattern
   * @return ResultSet each row is a column privilege description
   * @see #getSearchStringEscape
   */
  public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException
  {
    Field f[] = new Field[8];
    Vector v = new Vector();
    
    if(table==null)
      table="%";
    
    if(columnNamePattern==null)
      columnNamePattern="%";
    else
      columnNamePattern=columnNamePattern.toLowerCase();
    
    f[0] = new Field(connection,new String("TABLE_CAT"),iVarcharOid,32);
    f[1] = new Field(connection,new String("TABLE_SCHEM"),iVarcharOid,32);
    f[2] = new Field(connection,new String("TABLE_NAME"),iVarcharOid,32);
    f[3] = new Field(connection,new String("COLUMN_NAME"),iVarcharOid,32);
    f[4] = new Field(connection,new String("GRANTOR"),iVarcharOid,32);
    f[5] = new Field(connection,new String("GRANTEE"),iVarcharOid,32);
    f[6] = new Field(connection,new String("PRIVILEGE"),iVarcharOid,32);
    f[7] = new Field(connection,new String("IS_GRANTABLE"),iVarcharOid,32);
    
    // This is taken direct from the psql source
    java.sql.ResultSet r = connection.ExecSQL("SELECT relname, relacl FROM pg_class, pg_user WHERE ( relkind = 'r' OR relkind = 'i') and relname !~ '^pg_' and relname !~ '^xin[vx][0-9]+' and usesysid = relowner and relname like '"+table.toLowerCase()+"' ORDER BY relname");
    while(r.next()) {
      byte[][] tuple = new byte[8][0];
      tuple[0] = tuple[1]= "".getBytes();
      DriverManager.println("relname=\""+r.getString(1)+"\" relacl=\""+r.getString(2)+"\"");
      
      // For now, don't add to the result as relacl needs to be processed.
      //v.addElement(tuple);
    }
    
    return new ResultSet(connection,f,v,"OK",1);
  }
  
  /**
   * Get a description of the access rights for each table available
   * in a catalog.
   *
   * <P>Only privileges matching the schema and table name
   * criteria are returned.  They are ordered by TABLE_SCHEM,
   * TABLE_NAME, and PRIVILEGE.
   *
   * <P>Each privilige description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>GRANTOR</B> => grantor of access (may be null)
   *	<LI><B>GRANTEE</B> String => grantee of access
   *	<LI><B>PRIVILEGE</B> String => name of access (SELECT,
   *      INSERT, UPDATE, REFRENCES, ...)
   *	<LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
   *      to grant to others; "NO" if not; null if unknown
   *  </OL>
   *
   * @param catalog a catalog name; "" retrieves those without a catalog
   * @param schemaPattern a schema name pattern; "" retrieves those
   * without a schema
   * @param tableNamePattern a table name pattern
   * @return ResultSet each row is a table privilege description
   * @see #getSearchStringEscape
   */
  public java.sql.ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern) throws SQLException
  {
    // XXX-Not Implemented
    return null;
  }
  
  /**
   * Get a description of a table's optimal set of columns that
   * uniquely identifies a row. They are ordered by SCOPE.
   *
   * <P>Each column description has the following columns:
   *  <OL>
   *	<LI><B>SCOPE</B> short => actual scope of result
   *      <UL>
   *      <LI> bestRowTemporary - very temporary, while using row
   *      <LI> bestRowTransaction - valid for remainder of current transaction
   *      <LI> bestRowSession - valid for remainder of current session
   *      </UL>
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
   *	<LI><B>TYPE_NAME</B> String => Data source dependent type name
   *	<LI><B>COLUMN_SIZE</B> int => precision
   *	<LI><B>BUFFER_LENGTH</B> int => not used
   *	<LI><B>DECIMAL_DIGITS</B> short	 => scale
   *	<LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
   *      like an Or