databasemetadatausinginfoschema.java

开发MySql数据库的最新JDBC驱动。

				+ "A.CONSTRAINT_NAME AS FK_NAME,"
				+ "NULL AS PK_NAME,"
				+ importedKeyNotDeferrable
				+ " AS DEFERRABILITY "
				+ "FROM "
				+ "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,"
				+ "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
				+ "WHERE "
				+ "A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_NAME=B.TABLE_NAME "
				+ "AND "
				+ "A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL "
				+ "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
				+ "AND A.TABLE_SCHEMA LIKE ? AND A.TABLE_NAME=? " + "ORDER BY "
				+ "A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";

		PreparedStatement pStmt = null;

		try {
			pStmt = prepareMetaDataSafeStatement(sql);
			if (primaryCatalog != null) {
				pStmt.setString(1, primaryCatalog);
			} else {
				pStmt.setString(1, "%");
			}
			
			pStmt.setString(2, primaryTable);
			
			if (foreignCatalog != null) {
				pStmt.setString(3, foreignCatalog);
			} else {
				pStmt.setString(3, "%");
			}
			
			pStmt.setString(4, foreignTable);

			ResultSet rs = executeMetadataQuery(pStmt);
			((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
					new Field("", "PKTABLE_CAT", Types.CHAR, 255),
					new Field("", "PKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "PKTABLE_NAME", Types.CHAR, 255),
					new Field("", "PKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "FKTABLE_CAT", Types.CHAR, 255),
					new Field("", "FKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "FKTABLE_NAME", Types.CHAR, 255),
					new Field("", "FKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "KEY_SEQ", Types.SMALLINT, 2),
					new Field("", "UPDATE_RULE", Types.SMALLINT, 2),
					new Field("", "DELETE_RULE", Types.SMALLINT, 2),
					new Field("", "FK_NAME", Types.CHAR, 0),
					new Field("", "PK_NAME", Types.CHAR, 0),
					new Field("", "DEFERRABILITY", Types.INTEGER, 2) });

			return rs;
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * 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>
	 * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be
	 * null) </li>
	 * <li> <B>PKTABLE_NAME</B> String => primary key table name </li>
	 * <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li>
	 * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
	 * null) being exported (may be null) </li>
	 * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
	 * null) being exported (may be null) </li>
	 * <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported
	 * </li>
	 * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being
	 * exported </li>
	 * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
	 * <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>
	 * <li> importedKeyRestrict - do not allow update of primary key if it has
	 * been imported </li>
	 * <li> importedKeySetNull - change imported key to NULL if its primary key
	 * has been updated </li>
	 * </ul>
	 * </li>
	 * <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>
	 * <li> importedKeyRestrict - do not allow delete of primary key if it has
	 * been imported </li>
	 * <li> importedKeySetNull - change imported key to NULL if its primary key
	 * has been deleted </li>
	 * </ul>
	 * </li>
	 * <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li>
	 * <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li>
	 * </ol>
	 * </p>
	 * 
	 * @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
	 * @throws SQLException
	 *             if a database access error occurs
	 * @see #getImportedKeys
	 */
	public java.sql.ResultSet getExportedKeys(String catalog, String schema,
			String table) throws SQLException {
		// TODO: Can't determine actions using INFORMATION_SCHEMA yet...

		if (table == null) {
			throw SQLError.createSQLException("Table not specified.",
					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
		}

		if (catalog == null) {
			if (this.conn.getNullCatalogMeansCurrent()) {
				catalog = this.database;
			}	
		}

		String sql = "SELECT "
				+ "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
				+ "NULL AS PKTABLE_SCHEM,"
				+ "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, "
				+ "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, "
				+ "A.TABLE_SCHEMA AS FKTABLE_CAT,"
				+ "NULL AS FKTABLE_SCHEM,"
				+ "A.TABLE_NAME AS FKTABLE_NAME,"
				+ "A.COLUMN_NAME AS FKCOLUMN_NAME, "
				+ "A.ORDINAL_POSITION AS KEY_SEQ,"
				+ importedKeyRestrict
				+ " AS UPDATE_RULE,"
				+ importedKeyRestrict
				+ " AS DELETE_RULE,"
				+ "A.CONSTRAINT_NAME AS FK_NAME,"
				+ "NULL AS PK_NAME,"
				+ importedKeyNotDeferrable
				+ " AS DEFERRABILITY "
				+ "FROM "
				+ "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,"
				+ "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
				+ "WHERE "
				+ "A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_NAME=B.TABLE_NAME "
				+ "AND "
				+ "A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL "
				+ "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
				+ "ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";

		PreparedStatement pStmt = null;

		try {
			pStmt = prepareMetaDataSafeStatement(sql);
			
			if (catalog != null) {
				pStmt.setString(1, catalog);
			} else {
				pStmt.setString(1, "%");
			}
			
			pStmt.setString(2, table);

			ResultSet rs = executeMetadataQuery(pStmt);

			((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
					new Field("", "PKTABLE_CAT", Types.CHAR, 255),
					new Field("", "PKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "PKTABLE_NAME", Types.CHAR, 255),
					new Field("", "PKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "FKTABLE_CAT", Types.CHAR, 255),
					new Field("", "FKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "FKTABLE_NAME", Types.CHAR, 255),
					new Field("", "FKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "KEY_SEQ", Types.SMALLINT, 2),
					new Field("", "UPDATE_RULE", Types.SMALLINT, 2),
					new Field("", "DELETE_RULE", Types.SMALLINT, 2),
					new Field("", "FK_NAME", Types.CHAR, 255),
					new Field("", "PK_NAME", Types.CHAR, 0),
					new Field("", "DEFERRABILITY", Types.INTEGER, 2) });

			return rs;
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}

	}

	/*
	 * 
	 * getTablePrivileges
	 * 
	 * if (getMysqlVersion() > 49999) { if (!strcasecmp("localhost",
	 * m_pSettings->pConnection->host)) { sprintf(user, "A.GRANTEE =
	 * \"'%s'@'localhost'\" OR A.GRANTEE LIKE \"'%'@'localhost'\"",
	 * m_pSettings->pConnection->user, m_pSettings->pConnection->user); } else {
	 * sprintf(user, "\"'%s'@'%s'\" LIKE A.GRANTEE",
	 * m_pSettings->pConnection->user, m_pSettings->pConnection->host); }
	 * 
	 * sprintf(query, "SELECT DISTINCT A.TABLE_CATALOG, B.TABLE_SCHEMA,
	 * B.TABLE_NAME, CURRENT_USER(), " \ "A.PRIVILEGE_TYPE FROM
	 * INFORMATION_SCHEMA.USER_PRIVILEGES A, INFORMATION_SCHEMA.TABLES B " \
	 * "WHERE B.TABLE_SCHEMA LIKE '%s' AND B.TABLE_NAME LIKE '%s' AND (%s) " \
	 * "UNION " \ "SELECT DISTINCT A.TABLE_CATALOG, B.TABLE_SCHEMA,
	 * B.TABLE_NAME, CURRENT_USER(), A.PRIVILEGE_TYPE " \ "FROM
	 * INFORMATION_SCHEMA.SCHEMA_PRIVILEGES A, INFORMATION_SCHEMA.TABLES B WHERE " \
	 * "B.TABLE_SCHEMA LIKE '%s' AND B.TABLE_NAME LIKE '%s' AND (%s) " \ "UNION "\
	 * "SELECT DISTINCT A.TABLE_CATALOG, A.TABLE_SCHEMA, A.TABLE_NAME,
	 * CURRENT_USER, A.PRIVILEGE_TYPE FROM " \
	 * "INFORMATION_SCHEMA.TABLE_PRIVILEGES A WHERE A.TABLE_SCHEMA LIKE '%s' AND
	 * A.TABLE_NAME LIKE '%s' " \ "AND (%s)", schemaName, tableName, user,
	 * schemaName, tableName, user, schemaName, tableName, user );
	 */

	/**
	 * 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>
	 * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema being
	 * imported (may be null) </li>
	 * <li> <B>PKTABLE_NAME</B> String => primary key table name being imported
	 * </li>
	 * <li> <B>PKCOLUMN_NAME</B> String => primary key column name being
	 * imported </li>
	 * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
	 * null) </li>
	 * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
	 * null) </li>
	 * <li> <B>FKTABLE_NAME</B> String => foreign key table name </li>
	 * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name </li>
	 * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
	 * <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>
	 * <li> importedKeyRestrict - do not allow update of primary key if it has
	 * been imported </li>
	 * <li> importedKeySetNull - change imported key to NULL if its primary key
	 * has been updated </li>
	 * </ul>
	 * </li>
	 * <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>
	 * <li> importedKeyRestrict - do not allow delete of primary key if it has
	 * been imported </li>
	 * <li> importedKeySetNull - change imported key to NULL if its primary key
	 * has been deleted </li>
	 * </ul>
	 * </li>
	 * <li> <B>FK_NAME</B> String => foreign key name (may be null) </li>
	 * <li> <B>PK_NAME</B> String => primary key name (may be null) </li>
	 * </ol>
	 * </p>
	 * 
	 * @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 access error occurs
	 * @see #getExportedKeys
	 */
	public java.sql.ResultSet getImportedKeys(String catalog, String schema,
			String table) throws SQLException {
		if (table == null) {
			throw SQLError.createSQLException("Table not specified.",
					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
		}

		if (catalog == null) {
			if (this.conn.getNullCatalogMeansCurrent()) {
				catalog = this.database;
			}
		}

		String sql = "SELECT "
				+ "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
				+ "NULL AS PKTABLE_SCHEM,"
				+ "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,"
				+ "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,"
				+ "A.TABLE_SCHEMA AS FKTABLE_CAT,"
				+ "NULL AS FKTABLE_SCHEM,"
				+ "A.TABLE_NAME AS FKTABLE_NAME, "
				+ "A.COLUMN_NAME AS FKCOLUMN_NAME, "
				+ "A.ORDINAL_POSITION AS KEY_SEQ,"
				+ importedKeyRestrict
				+ " AS UPDATE_RULE,"
				+ importedKeyRestrict
				+ " AS DELETE_RULE,"
				+ "A.CONSTRAINT_NAME AS FK_NAME,"
				+ "NULL AS PK_NAME, "
				+ importedKeyNotDeferrable
				+ " AS DEFERRABILITY "
				+ "FROM "
				+ "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, "
				+ "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B WHERE A.TABLE_SCHEMA LIKE ? "
				+ "AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.TABLE_NAME=? "
				+ "AND "
				+ "B.TABLE_NAME=? AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL "
				+ " ORDER BY "
				+ "A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, "
				+ "A.ORDINAL_POSITION";

		PreparedStatement pStmt = null;

		try {
			pStmt = prepareMetaDataSafeStatement(sql);
			
			if (catalog != null) {
				pStmt.setString(1, catalog);
			} else {
				pStmt.setString(1, "%");
			}
			
			pStmt.setString(2, table);
			pStmt.setString(3, table);

			ResultSet rs = executeMetadataQuery(pStmt);

			((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
					new Field("", "PKTABLE_CAT", Types.CHAR, 255),
					new Field("", "PKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "PKTABLE_NAME", Types.CHAR, 255),
					new Field("", "PKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "FKTABLE_CAT", Types.CHAR, 255),
					new Field("", "FKTABLE_SCHEM", Types.CHAR, 0),
					new Field("", "FKTABLE_NAME", Types.CHAR, 255),
					new Field("", "FKCOLUMN_NAME", Types.CHAR, 32),
					new Field("", "KEY_SEQ", Types.SMALLINT, 2),
					new Field("", "UPDATE_RULE", Types.SMALLINT, 2),
					new Field("", "DELETE_RULE", Types.SMALLINT, 2),
					new Field("", "FK_NAME", Types.CHAR, 255),
					new Field("", "PK_NAME", Types.CHAR, 0),
					new Field("", "DEFERRABILITY", Types.INTEGER, 2) });

			return rs;
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * Get a description of a table's indices and statistics. They are ordered
	 * by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
	 * <P>
	 * Each index column description has the following columns:
	 * <OL>
	 * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
	 * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
	 * <li> <B>TABLE_NAME</B> String => table name </li>
	 * <li> <B>NON_UNIQUE</B> boolean => Can index values be non-unique? false
	 * when TYPE is tableIndexStatistic </li>
	 * <li> <B>INDEX_QUALIFIER</B> String => index catalog (may be null); null
	 * when TYPE is tableIndexStatistic </li>
	 * <li> <B>INDEX_NAME</B> String => index name; null when TYPE is
	 * tableIndexStatistic </li>
	 * <li> <B>TYPE</B> short => index type:
	 * <UL>
	 * <li> tableIndexStatistic - this identifies table statistics that are
	 * returned in conjuction with a table's index descriptions </li>
	 * <li> tableIndexClustered - this is a clustered index </li>
	 * <li> tableIndexHashed - this is a hashed index </li>
	 * <li> tableIndexOther - this is some other style of index </li>
	 * </ul>
	 * </li>
	 * <li> <B>ORDINAL_POSITION</B> short => column sequence number within
	 * index; zero when TYPE is tableIndexStatistic </li>
	 * <li> <B>COLUMN_NAME</B> String => column name; null when TYPE is
	 * tableIndexStatistic </li>
	 * <li> <B>ASC_OR_DESC</B> String => column sort sequence, "A" =>
	 * ascending, "D" => descending, may be null if sort sequence is not
	 * supported; null when TYPE is tableIndexStatistic </li>
	 * <li> <B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then this
	 * is the number of rows in the table; otherwise it is the number of unique
	 * values in the index. </li>