?? databasemetadatausinginfoschema.java
字號:
+ "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=? "
+ "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>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -