monetdatabasemetadata.java

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

	 * @return ResultSet - each row is a procedure description
	 * @throws SQLException if a database access error occurs
	 */
	public ResultSet getProcedures(
		String catalog,
		String schemaPattern,
		String procedureNamePattern
	) throws SQLException
	{
		String query =
			"SELECT cast(null AS varchar(1)) AS \"PROCEDURE_CAT\", " +
				"cast(null AS varchar(1)) AS \"PROCEDURE_SCHEM\", " +
				"'' AS \"PROCEDURE_NAME\", cast(null AS varchar(1)) AS \"Field4\", " +
				"cast(null AS varchar(1)) AS \"Field5\", " +
				"cast(null AS varchar(1)) AS \"Field6\", " +
				"'' AS \"REMARKS\", cast(0 AS smallint) AS \"PROCEDURE_TYPE\" " +
			"WHERE 1 = 0";

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

	/**
	 * Get a description of a catalog's stored procedure parameters
	 * and result columns.
	 * Currently not applicable and not implemented, returns null
	 *
	 * <p>Only descriptions matching the schema, procedure and parameter
	 * name criteria are returned. They are ordered by PROCEDURE_SCHEM
	 * and PROCEDURE_NAME. Within this, the return value, if any, is
	 * first. Next are the parameter descriptions in call order. The
	 * column descriptions follow in column number order.
	 *
	 * <p>Each row in the ResultSet is a parameter description or column
	 * description with the following fields:
	 * <ol>
	 * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
	 * <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
	 * <li><b>PROCEDURE_NAME</b> String => procedure name
	 * <li><b>COLUMN_NAME</b> String => column/parameter name
	 * <li><b>COLUMN_TYPE</b> Short => kind of column/parameter:
	 * <ul><li>procedureColumnUnknown - nobody knows
	 * <li>procedureColumnIn - IN parameter
	 * <li>procedureColumnInOut - INOUT parameter
	 * <li>procedureColumnOut - OUT parameter
	 * <li>procedureColumnReturn - procedure return value
	 * <li>procedureColumnResult - result column in ResultSet
	 * </ul>
	 * <li><b>DATA_TYPE</b> short => SQL type from java.sql.Types
	 * <li><b>TYPE_NAME</b> String => Data source specific type name
	 * <li><b>PRECISION</b> int => precision
	 * <li><b>LENGTH</b> int => length in bytes of data
	 * <li><b>SCALE</b> short => scale
	 * <li><b>RADIX</b> short => radix
	 * <li><b>NULLABLE</b> short => can it contain NULL?
	 * <ul><li>procedureNoNulls - does not allow NULL values
	 * <li>procedureNullable - allows NULL values
	 * <li>procedureNullableUnknown - nullability unknown
	 * <li><b>REMARKS</b> String => comment describing parameter/column
	 * </ol>
	 * @param catalog 
	 * @param schemaPattern
	 * @param procedureNamePattern a procedure name pattern
	 * @param columnNamePattern a column name pattern
	 * @return each row is a stored procedure parameter or column description
	 * @throws SQLException if a database-access error occurs
	 * @see #getSearchStringEscape
	 */
	public ResultSet getProcedureColumns(
		String catalog,
		String schemaPattern,
		String procedureNamePattern,
		String columnNamePattern
	) throws SQLException
	{
		String query =
			"SELECT cast(null AS varchar(1)) AS \"PROCEDURE_CAT\", " +
				"cast(null AS varchar(1)) AS \"PROCEDURE_SCHEM\", " +
				"'' AS \"PROCEDURE_NAME\", '' AS \"COLUMN_NAME\", " +
				"cast(0 AS smallint) AS \"COLUMN_TYPE\", " +
				"cast(0 AS smallint) AS \"DATA_TYPE\", " +
				"'' AS \"TYPE_NAME\", 0 AS \"PRECISION\", " +
				"0 AS \"LENGTH\", 0 AS \"SCALE\", 0 AS \"RADIX\", " +
				"cast(0 AS smallint) AS \"NULLABLE\", '' AS \"REMARKS\" " +
			"WHERE 1 = 0";

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

	//== this is a helper method which does not belong to the interface

	/**
	 * Returns the given string where all slashes and single quotes are
	 * escaped with a slash.
	 *
	 * @param in the string to escape
	 * @return the escaped string
	 */
	private static final String escapeQuotes(String in) {
		return(in.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'"));
	}

	/**
	 * Returns the given string between two double quotes for usage as
	 * exact column or table name in SQL queries.
	 *
	 * @param in the string to quote
	 * @return the quoted string
	 */
	private static final String dq(String in) {
		return("\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"");
	}

	//== end helper methods

	/**
	 * Get a description of tables available in a catalog.
	 *
	 * <p>Only table descriptions matching the catalog, schema, table
	 * name and type criteria are returned. They are ordered by
	 * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
	 *
	 * <p>Each table 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>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
	 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
	 * TEMPORARY", "ALIAS", "SYNONYM".
	 * <li><b>REMARKS</b> String => explanatory comment on the table
	 * </ol>
	 *
	 * <p>The valid values for the types parameter are:
	 * "TABLE", "INDEX", "SEQUENCE", "VIEW",
	 * "SYSTEM TABLE", "SYSTEM INDEX", "SYSTEM VIEW",
	 * "SYSTEM TOAST TABLE", "SYSTEM TOAST INDEX",
	 * "TEMPORARY TABLE", and "TEMPORARY VIEW"
	 *
	 * @param catalog a catalog name; this parameter is currently ignored
	 * @param schemaPattern a schema name pattern
	 * @param tableNamePattern a table name pattern. For all tables this should be "%"
	 * @param types a list of table types to include; null returns all types;
	 *              this parameter is currently ignored
	 * @return each row is a table description
	 * @throws SQLException if a database-access error occurs.
	 */
	public ResultSet getTables(
		String catalog,
		String schemaPattern,
		String tableNamePattern,
		String types[]
	) throws SQLException
	{
		String select;
		String orderby;
		String cat = getEnv("gdk_dbname");

		select =
			"SELECT * FROM ( " +
			"SELECT '" + cat + "' AS \"TABLE_CAT\", \"schemas\".\"name\" AS \"TABLE_SCHEM\", \"tables\".\"name\" AS \"TABLE_NAME\", " +
				"CASE WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM TABLE' " +
				"	WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM VIEW' " +
				"	WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 0 THEN 'TABLE' " +
				"	WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 0 THEN 'VIEW' " +
				"   WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION TABLE' " +
				"	WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION VIEW' " +
				"	WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 1 THEN 'SESSION TABLE' " +
				"	WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 1 THEN 'SESSION VIEW' " +
				"END AS \"TABLE_TYPE\", \"tables\".\"query\" AS \"REMARKS\", null AS \"TYPE_CAT\", null AS \"TYPE_SCHEM\", " +
				"null AS \"TYPE_NAME\", 'rowid' AS \"SELF_REFERENCING_COL_NAME\", 'SYSTEM' AS \"REF_GENERATION\" " +
			"FROM \"sys\".\"tables\" AS \"tables\", \"sys\".\"schemas\" AS \"schemas\" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
			") AS \"tables\" WHERE 1 = 1 ";

		if (tableNamePattern != null) {
			select += "AND LOWER(\"TABLE_NAME\") LIKE '" + escapeQuotes(tableNamePattern).toLowerCase() + "' ";
		}
		if (schemaPattern != null) {
			select += "AND LOWER(\"TABLE_SCHEM\") LIKE '" + escapeQuotes(schemaPattern).toLowerCase() + "' ";
		}
		if (types != null) {
			select += "AND (";
			for (int i = 0; i < types.length; i++) {
				select += (i == 0 ? "" : " OR ") + "LOWER(\"TABLE_TYPE\") LIKE '" + escapeQuotes(types[i]).toLowerCase() + "'";
			}
			select += ") ";
		}

		orderby = "ORDER BY \"TABLE_TYPE\", \"TABLE_SCHEM\", \"TABLE_NAME\" ";

		return(getStmt().executeQuery(select + orderby));
	}

	/**
	 * 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
	 *	<LI><B>TABLE_CATALOG</B> String => catalog name (may be null)
	 *	</OL>
	 *
	 * @return ResultSet each row has a single String column that is a
	 *         schema name
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getSchemas() throws SQLException {
		String cat = getEnv("gdk_dbname");
		String query =
			"SELECT \"name\" AS \"TABLE_SCHEM\", " +
				"'" + cat + "' AS \"TABLE_CATALOG\", " +
				"'" + cat + "' AS \"TABLE_CAT\" " +	// SquirrelSQL requests this one...
			"FROM \"sys\".\"schemas\" " +
			"ORDER BY \"TABLE_SCHEM\"";

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

	/**
	 * 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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getCatalogs() throws SQLException {
		/*
		// doing this with a VirtualResultSet is much more efficient...
		String query =
			"SELECT '" + ((String)env.get("gdk_dbname")) + "' AS \"TABLE_CAT\"";
			// some applications need a database or catalog...

		return(getStmt().executeQuery(query));
		*/
		
		String[] columns, types;
		String[][] results;

		columns = new String[1];
		types = new String[1];
		results = new String[1][1];

		columns[0] = "TABLE_TYPE";
		types[0] = "varchar";
		results[0][0] = getEnv("gdk_dbname");

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

	/**
	 * 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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getTableTypes() throws SQLException {
		String[] columns, types;
		String[][] results;

		columns = new String[1];
		types = new String[1];
		results = new String[8][1];

		columns[0] = "TABLE_TYPE";
		types[0] = "varchar";
		results[0][0] = "SYSTEM TABLE";
		results[1][0] = "TABLE";
		results[2][0] = "SYSTEM VIEW";
		results[3][0] = "VIEW";
		results[4][0] = "SYSTEM SESSION TABLE";
		results[5][0] = "SESSION TABLE";
		results[6][0] = "SYSTEM SESSION VIEW";
		results[7][0] = "SESSION VIEW";

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

	/**
	 * 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;
	 *                currently ignored
	 * @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
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getColumns(
		String catalog,
		String schemaPattern,
		String tableNamePattern,
		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\", " +
			"cast(" + MonetDriver.getSQLTypeMap("\"columns\".\"type\"") + " " +
			"AS smallint) AS \"DATA_TYPE\", " +
			"\"columns\".\"type\" AS \"TYPE_NAME\", " +
			"\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " +
			"\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", 0 AS \"BUFFER_LENGTH\", " +
			"10 AS \"NUM_PREC_RADIX\", " +
			"cast(CASE \"null\" " +
				"WHEN true THEN " + ResultSetMetaData.columnNullable + " " +
				"WHEN false THEN " + ResultSetMetaData.columnNoNulls + " " +
			"END AS int) AS \"NULLABLE\", cast(null AS varchar(1)) AS \"REMARKS\", " +
			"\"columns\".\"default\" AS \"COLUMN_DEF\", 0 AS \"SQL_DATA_TYPE\", " +
			"0 AS \"SQL_DATETIME_SUB\", 0 AS \"CHAR_OCTET_LENGTH\", " +
			"\"columns\".\"number\" + 1 AS \"ORDINAL_POSITION\", " +
			"cast(null AS varchar(1)) AS \"SCOPE_CATALOG\", " +
			"cast(null AS varchar(1)) AS \"SCOPE_SCHEMA\", " +
			"cast(null AS varchar(1)) AS \"SCOPE_TABLE\", " +
			"cast(" + MonetDriver.getJavaType("other") + " AS smallint) AS \"SOURCE_DATA_TYPE\", " +
			"CASE \"null\" " +
				"WHEN true THEN CAST ('YES' AS varchar(3)) " +
				"WHEN false THEN CAST ('NO' AS varchar(3)) " +
			"END AS \"IS_NULLABLE\" " +
				"FROM \"sys\".\"columns\" AS \"columns\", " +
					"\"sys\".\"tables\" AS \"tables\", " +
					"\"sys\".\"schemas\" AS \"schemas\" " +
				"WHERE \"columns\".\"table_id\" = \"tables\".\"id\" " +
					"AND \"tables\".\"schema_id\" = \"schemas\".\"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 \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"";

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

	/**
	 * Get a description of the access rights for a table's columns.
	 * MonetDB doesn't have this level of access rights.
	 *
	 * <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 schemaPattern a schema name; "" retrieves those without a schema
	 * @param tableNamePattern a table name
	 * @param columnNamePattern a column name pattern
	 * @return ResultSet each row is a column privilege description
	 * @see #getSearchStringEscape
	 * @throws SQLException if a database error occurs
	 */
	public ResultSet getColumnPrivileges(
		String catalog,
		String schemaPattern,
		String tableNamePattern,