monetdatabasemetadata.java

这个是内存数据库的客户端

		String columnNamePattern
	) throws SQLException
	{
		String cat = getEnv("gdk_dbname");
		String query =
		"SELECT '" + cat + "' AS \"TABLE_CAT\", " +
			"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
			"\"tables\".\"name\" AS \"TABLE_NAME\", " +
			"\"columns\".\"name\" AS \"COLUMN_NAME\", " +
			"\"grantors\".\"name\" AS \"GRANTOR\", " +
			"\"grantees\".\"name\" AS \"GRANTEE\", " +
			"CASE \"privileges\".\"privileges\" " +
				"WHEN 1 THEN cast('SELECT' AS varchar(7)) " +
				"WHEN 2 THEN cast('UPDATE' AS varchar(7)) " +
				"WHEN 4 THEN cast('INSERT' AS varchar(7)) " +
				"WHEN 8 THEN cast('DELETE' AS varchar(7)) " +
				"WHEN 16 THEN cast('EXECUTE' AS varchar(7)) " +
				"WHEN 32 THEN cast('GRANT' AS varchar(7)) " +
			"END AS \"PRIVILEGE\", " +
			"CASE \"privileges\".\"grantable\" " +
				"WHEN 0 THEN cast('NO' AS varchar(3)) " +
				"WHEN 1 THEN cast('YES' AS varchar(3)) " +
			"END AS \"IS_GRANTABLE\" " +
		"FROM \"sys\".\"privileges\" AS \"privileges\", " +
			"\"sys\".\"tables\" AS \"tables\", " +
			"\"sys\".\"schemas\" AS \"schemas\", " +
			"\"sys\".\"columns\" AS \"columns\", " +
			"\"sys\".\"auths\" AS \"grantors\", " +
			"\"sys\".\"auths\" AS \"grantees\" " +
		"WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
			"AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
			"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
			"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
			"AND \"privileges\".\"grantor\" = \"grantors\".\"id\" ";
		
		if (schemaPattern != null) {
			query += "AND LOWER(\"schemas\".\"name\") LIKE '" + escapeQuotes(schemaPattern).toLowerCase() + "' ";
		}
		if (tableNamePattern != null) {
			query += "AND LOWER(\"tables\".\"name\") LIKE '" + escapeQuotes(tableNamePattern).toLowerCase() + "' ";
		}
		if (columnNamePattern != null) {
			query += "AND LOWER(\"columns\".\"name\") LIKE '" + escapeQuotes(columnNamePattern).toLowerCase() + "' ";
		}

		query += "ORDER BY \"COLUMN_NAME\", \"PRIVILEGE\"";

		return(getStmt().executeQuery(query));
	}

	/**
	 * 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 privilege 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>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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getTablePrivileges(
		String catalog,
		String schemaPattern,
		String tableNamePattern
	) throws SQLException
	{
		String cat = getEnv("gdk_dbname");
		String query =
		"SELECT '" + cat + "' AS \"TABLE_CAT\", " +
			"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
			"\"tables\".\"name\" AS \"TABLE_NAME\", " +
			"\"grantors\".\"name\" AS \"GRANTOR\", " +
			"\"grantees\".\"name\" AS \"GRANTEE\", " +
			"CASE \"privileges\".\"privileges\" " +
				"WHEN 1 THEN cast('SELECT' AS varchar(7)) " +
				"WHEN 2 THEN cast('UPDATE' AS varchar(7)) " +
				"WHEN 4 THEN cast('INSERT' AS varchar(7)) " +
				"WHEN 8 THEN cast('DELETE' AS varchar(7)) " +
				"WHEN 16 THEN cast('EXECUTE' AS varchar(7)) " +
				"WHEN 32 THEN cast('GRANT' AS varchar(7)) " +
			"END AS \"PRIVILEGE\", " +
			"CASE \"privileges\".\"grantable\" " +
				"WHEN 0 THEN cast('NO' AS varchar(3)) " +
				"WHEN 1 THEN cast('YES' AS varchar(3)) " +
			"END AS \"IS_GRANTABLE\" " +
		"FROM \"sys\".\"privileges\" AS \"privileges\", " +
			"\"sys\".\"tables\" AS \"tables\", " +
			"\"sys\".\"schemas\" AS \"schemas\", " +
			"\"sys\".\"auths\" AS \"grantors\", " +
			"\"sys\".\"auths\" AS \"grantees\" " +
		"WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
			"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
			"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
			"AND \"privileges\".\"grantor\" = \"grantors\".\"id\" ";
		
		if (schemaPattern != null) {
			query += "AND LOWER(\"schemas\".\"name\") LIKE '" + escapeQuotes(schemaPattern).toLowerCase() + "' ";
		}
		if (tableNamePattern != null) {
			query += "AND LOWER(\"tables\".\"name\") LIKE '" + escapeQuotes(tableNamePattern).toLowerCase() + "' ";
		}

		query += "ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\"";

		return(getStmt().executeQuery(query));
	}

	/**
	 * 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 Oracle ROWID
	 *		<UL>
	 *		<LI> bestRowUnknown - may or may not be pseudo column
	 *		<LI> bestRowNotPseudo - is NOT a pseudo column
	 *		<LI> bestRowPseudo - is a pseudo column
	 *		</UL>
	 *	</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 scope the scope of interest; use same values as SCOPE
	 * @param nullable include columns that are nullable?
	 * @return ResultSet each row is a column description
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getBestRowIdentifier(
		String catalog,
		String schema,
		String table,
		int scope,
		boolean nullable
	) throws SQLException
	{
		String query =
		"SELECT \"columns\".\"name\" AS \"COLUMN_NAME\", \"columns\".\"type\" AS \"TYPE_NAME\", " +
			"\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", 0 AS \"BUFFER_LENGTH\", " +
			"\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", \"keys\".\"type\" AS \"keytype\" " +
				"FROM \"sys\".\"keys\" AS \"keys\", " +
					"\"sys\".\"keycolumns\" AS \"keycolumns\", " +
					"\"sys\".\"columns\" AS \"columns\", " +
					"\"sys\".\"tables\" AS \"tables\", " +
					"\"sys\".\"schemas\" AS \"schemas\" " +
				"WHERE \"keys\".\"id\" = \"keycolumns\".\"id\" AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
					"AND \"keys\".\"table_id\" = \"columns\".\"table_id\" " +
					"AND \"keycolumns\".\"column\" = \"columns\".\"name\" " +
					"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
					"AND \"keys\".\"type\" IN (0, 1) ";

		// SCOPE, DATA_TYPE, PSEUDO_COLUMN have to be generated with Java logic

		if (schema != null) {
			query += "AND LOWER(\"schemas\".\"name\") LIKE '" + escapeQuotes(schema).toLowerCase() + "' ";
		}
		if (table != null) {
			query += "AND LOWER(\"tables\".\"name\") LIKE '" + escapeQuotes(table).toLowerCase() + "' ";
		}
		if (!nullable) {
			query += "AND \"columns\".\"null\" = false ";
		}

		query += "ORDER BY \"keytype\"";

		String columns[] = {
			"SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE",
			"BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"
		};

		String types[] = {
			"int", "varchar", "int", "varchar", "int",
			"int", "int", "int"
		};

		String[][] results;
		ArrayList tmpRes = new ArrayList();

		ResultSet rs = getStmt().executeQuery(query);
		while (rs.next()) {
			String[] result = new String[8];
			result[0]  = "" + DatabaseMetaData.bestRowSession;
			result[1]  = rs.getString("column_name");
			result[2]  = "" + MonetDriver.getJavaType(rs.getString("type_name"));
			result[3]  = rs.getString("type_name");
			result[4]  = rs.getString("column_size");
			result[5]  = rs.getString("buffer_length");
			result[6]  = rs.getString("decimal_digits");
			result[7]  = "" + DatabaseMetaData.bestRowNotPseudo;
			tmpRes.add(result);
		}
		rs.close();

		results = (String[][])tmpRes.toArray(new String[tmpRes.size()][]);

		try {
			return(new MonetVirtualResultSet(columns, types, results));
		} catch (IllegalArgumentException e) {
			throw new SQLException("Internal driver error: " + e.getMessage());
		}
	}

	/**
	 * Get a description of a table's columns that are automatically
	 * updated when any value in a row is updated.	They are
	 * unordered.
	 *
	 * <P>Each column description has the following columns:
	 *	<OL>
	 *	<LI><B>SCOPE</B> short => is not used
	 *	<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 => length of column value in bytes
	 *	<LI><B>DECIMAL_DIGITS</B> short  => scale
	 *	<LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
	 *		like an Oracle ROWID
	 *		<UL>
	 *		<LI> versionColumnUnknown - may or may not be pseudo column
	 *		<LI> versionColumnNotPseudo - is NOT a pseudo column
	 *		<LI> versionColumnPseudo - is a pseudo column
	 *		</UL>
	 *	</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
	 * @return ResultSet each row is a column description
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getVersionColumns(
		String catalog,
		String schema,
		String table
	) throws SQLException
	{
		// I don't know of columns which update themselves, except maybe on the
		// system tables

		String columns[] = {
			"SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE",
			"BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"
		};

		String types[] = {
			"int", "varchar", "int", "varchar", "int",
			"int", "int", "int"
		};

		String[][] results = new String[0][columns.length];

		try {
			return(new MonetVirtualResultSet(columns, types, results));
		} catch (IllegalArgumentException e) {
			throw new SQLException("Internal driver error: " + e.getMessage());
		}
	}

	/**
	 * Get a description of a table's primary key columns.  They
	 * are ordered by COLUMN_NAME.
	 *
	 * <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>KEY_SEQ</B> short => sequence number within primary key
	 *	<LI><B>PK_NAME</B> String => primary key name (may be null)
	 *	</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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getPrimaryKeys(
		String catalog,
		String schema,
		String table
	) throws SQLException
	{
		String query =
		"SELECT cast(null AS varchar(1)) AS \"TABLE_CAT\", " +
			"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
			"\"tables\".\"name\" AS \"TABLE_NAME\", " +
			"\"keycolumns\".\"column\" AS \"COLUMN_NAME\", " +
			"\"keycolumns\".\"nr\" AS \"KEY_SEQ\", \"keys\".\"name\" AS \"PK_NAME\" " +
		"FROM \"sys\".\"keys\" AS \"keys\", " +
			"\"sys\".\"keycolumns\" AS \"keycolumns\", " +
			"\"sys\".\"tables\" AS \"tables\", " +
			"\"sys\".\"schemas\" AS \"schemas\" " +
		"WHERE \"keys\".\"id\" = \"keycolumns\".\"id\" " +
			"AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
			"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
			"AND \"keys\".\"type\" = 0 ";

		if (schema != null) {
			query += "AND LOWER(\"schemas\".\"name\") LIKE '" + escapeQuotes(schema).toLowerCase() + "' ";
		}
		if (table != null) {
			query += "AND LOWER(\"tables\".\"name\") LIKE '" + escapeQuotes(table).toLowerCase() + "' ";
		}

		query += "ORDER BY \"COLUMN_NAME\"";

		return(getStmt().executeQuery(query));
	}

	final static String keyQuery =
		", \"pkschema\".\"name\" AS \"PKTABLE_SCHEM\", " +
		"\"pktable\".\"name\" AS \"PKTABLE_NAME\", \"pkkeycol\".\"column\" AS \"PKCOLUMN_NAME\", " +
		"\"fkschema\".\"name\" AS \"FKTABLE_SCHEM\", " +
		"\"fktable\".\"name\" AS \"FKTABLE_NAME\", \"fkkeycol\".\"column\" AS \"FKCOLUMN_NAME\", " +
		"((\"fkkeycol\".\"nr\" * 121) + \"pkkeycol\".\"nr\") AS \"KEY_SEQ\", " +
		DatabaseMetaData.importedKeyNoAction + " AS \"UPDATE_RULE\", " +
		"" + DatabaseMetaData.importedKeyNoAction + " AS \"DELETE_RULE\", " +
		"\"fkkey\".\"name\" AS \"FK_NAME\", \"pkkey\".\"name\" AS \"PK_NAME\", " +
		"" + DatabaseMetaData.importedKeyNotDeferrable + " AS \"DEFERRABILITY\" " +
			"FROM \"sys\".\"keys\" AS \"fkkey\", \"sys\".\"keys\" AS \"pkkey\", \"sys\".\"keycolumns\" AS \"fkkeycol\", " +
			"\"sys\".\"keycolumns\" AS \"pkkeycol\", \"sys\".\"tables\" AS \"fktable\", \"sys\".\"tables\" AS \"pktable\", " +
			"\"sys\".\"schemas\" AS \"fkschema\", \"sys\".\"schemas\" AS \"pkschema\" " +
			"WHERE \"fktable\".\"id\" = \"fkkey\".\"table_id\" AND \"pktable\".\"id\" = \"pkkey\".\"table_id\" AND " +
			"\"fkkey\".\"id\" = \"fkkeycol\".\"id\" AND \"pkkey\".\"id\" = \"pkkeycol\".\"id\" AND " +
			"\"fkschema\".\"id\" = \"fktable\".\"schema_id\" AND \"pkschema\".\"id\" = \"pktable\".\"schema_id\" AND " +
			"\"fkkey\".\"rkey\" > -1 AND \"fkkey\".\"rkey\" = \"pkkey\".\"id\" ";

	static String keyQuery(String cat) {
		return("SELECT '" + cat + "' AS \"PKTABLE_CAT\", '" + cat + "' AS \"FKTABLE_CAT\"" + keyQuery);
	}

	/**
	 * 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)
	 *	</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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getImportedKeys(String catalog, String schema, String table)
		throws SQLException
	{
		String cat = getEnv("gdk_dbname");
		String query = keyQuery(cat);

		if (schema != null) {
			query += "AND LOWER(\"fkschema\".\"name\") LIKE '" + escapeQuotes(schema).toLowerCase() + "' ";
		}
		if (table != null) {
			query += "AND LOWER(\"fktable\".\"name\") LIKE '" + escapeQuotes(table).toLowerCase() + "' ";
		}

		query += "ORDER BY \"PKTABLE_CAT\", \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"KEY_SEQ\"";

		return(getStmt().executeQuery(que