ReverseEngineeringAccess.java
上传用户:cccombo
上传日期:2021-01-31
资源大小:16445k
文件大小:15k
源码类别:
MySQL数据库
开发平台:
SQL
- package com.mysql.grt.modules;
- import java.sql.*;
- import com.mysql.grt.*;
- import com.mysql.grt.db.*;
- import java.util.regex.*;
- import java.util.List;
- import java.util.ArrayList;
- /**
- * GRT Reverse Engineering Class using generic JDBC metadata functions
- *
- * @author Mike
- * @version 1.0, 11/26/04
- *
- */
- public class ReverseEngineeringAccess extends ReverseEngineeringGeneric {
- /**
- * Static function to return information about this class to the GRT
- * environment
- *
- * @return returns a GRT XML string containing the infos about this class
- */
- public static String getModuleInfo() {
- return Grt.getModuleInfoXml(ReverseEngineeringAccess.class,
- "ReverseEngineering");
- }
- /**
- * Returns a list of all schemata from the given JDBC connection
- *
- * @param jdbcDriver
- * the class name of the JDBC driver
- * @param jdbcConnectionString
- * a JDBC connection string
- * @return returns a GRT XML string containing a list of schemata names
- */
- public static GrtStringList getSchemata(
- com.mysql.grt.db.mgmt.Connection dbConn) throws Exception {
- Grt.getInstance().addMsg(
- "Create a dummy schema list because "
- + "Access only has one schema for each file.");
- GrtStringList schemataList = new GrtStringList();
- // Filter schemaName
- String schemaName = dbConn.getParameterValues().get("databaseFile");
- Pattern p = Pattern.compile("(\w*)\.mdb", Pattern.CASE_INSENSITIVE);
- Matcher m = p.matcher(schemaName);
- if (m.find())
- schemaName = schemaName.substring(m.start(), m.end() - 4);
- else
- schemaName = "Access";
- schemataList.add(schemaName);
- Grt.getInstance().addMsg("Return schemata list.");
- return schemataList;
- }
- /**
- * Does the reverse engineering of the given schematas over the JDBC
- * connection and returns the GRT objects
- *
- * @param jdbcDriver
- * the class name of the JDBC driver
- * @param jdbcConnectionString
- * a JDBC connection string
- * @param schemataList
- * list of schematas to be reverse engineered
- * @return returns a GRT XML string containing a the reverse engineered
- * objects
- */
- public static Catalog reverseEngineer(
- com.mysql.grt.db.mgmt.Connection dbConn, GrtStringList schemataList)
- throws Exception {
- boolean reverseEngineerOnlyTableObjects = (Grt.getInstance()
- .getGrtGlobalAsInt(
- "/migration/applicationData/"
- + "reverseEngineerOnlyTableObjects") == 1);
- ReverseEngineeringAccess revEng = new ReverseEngineeringAccess();
- // connect to the database
- Connection conn = establishConnection(dbConn);
- Catalog catalog = new Catalog(null);
- catalog.setName("Catalog");
- catalog.setVersion(getVersion(dbConn));
- Grt.getInstance().addMsg("Build simple datatypes.");
- revEng.buildSimpleDatatypes(conn, catalog);
- // If an empty list was given, use a list with an empty string instead
- if (schemataList.size() == 0)
- schemataList.add("");
- for (int i = 0; i < schemataList.size(); i++) {
- Schema schema = new Schema(catalog);
- schema.setName((String) (schemataList.get(i)));
- catalog.getSchemata().add(schema);
- // Get Tables
- revEng.reverseEngineerTables(conn, catalog, schema);
- if (!reverseEngineerOnlyTableObjects) {
- // Get Views
- revEng.reverseEngineerViews(conn, catalog, schema);
- }
- }
- // make sure the Fks use real references instead of
- // text names where possible
- revEng.reverseEngineerUpdateFkReferences(catalog);
- return catalog;
- }
- protected void reverseEngineerTables(Connection conn, Catalog catalog,
- Schema schema) throws Exception {
- Grt.getInstance().addMsg("Fetch all tables of the given schemata.");
- ResultSet rset = conn.getMetaData().getTables(null, null, null,
- new String[] { "TABLE" });
- while (rset.next()) {
- String tableName = rset.getString("TABLE_NAME");
- if (tableName.startsWith("MSys"))
- continue;
- // Create new table
- Table table = new Table(schema);
- schema.getTables().add(table);
- table.setName(tableName);
- reverseEngineerTableColumns(conn, catalog, schema, table);
- reverseEngineerTableIndices(conn, catalog, schema, table);
- reverseEngineerTableFKs(conn, catalog, schema, table);
- }
- }
- protected void reverseEngineerTableColumns(Connection conn,
- Catalog catalog, Schema schema, Table table) {
- try {
- Grt.getInstance().addMsg(
- "Fetching column information of table " + table.getName()
- + ".");
- ResultSet rset = conn.getMetaData().getColumns(null, null,
- table.getName(), null);
- while (rset.next()) {
- // create new column
- Column column = new Column(table);
- column.setName(rset.getString("COLUMN_NAME"));
- column.setDatatypeName(rset.getString("TYPE_NAME"));
- if (column.getDatatypeName().equals("BIGBINARY"))
- continue;
- table.getColumns().add(column);
- // Get Simple Type
- column.setSimpleType(catalog.getSimpleDatatypes().get(
- catalog.getSimpleDatatypes().getIndexOfName(
- column.getDatatypeName())));
- column.setLength(rset.getInt("COLUMN_SIZE"));
- column.setPrecision(column.getLength());
- column.setScale(rset.getInt("DECIMAL_DIGITS"));
- if (rset.getInt("NULLABLE") == java.sql.DatabaseMetaData.columnNullable)
- column.setIsNullable(1);
- else
- column.setIsNullable(0);
- column.setDefaultValue(rset.getString("COLUMN_DEF"));
- }
- rset.close();
- } catch (Exception e) {
- Grt.getInstance().addErr(e.getMessage());
- }
- }
- protected void reverseEngineerTableIndices(Connection conn,
- Catalog catalog, Schema schema, Table table) {
- try {
- Grt.getInstance().addMsg(
- "Fetching index information of table " + table.getName()
- + ".");
- String indexName = "";
- Index index = null;
- ResultSet rset = conn.getMetaData().getIndexInfo(null, null,
- table.getName(), false, true);
- while (rset.next()) {
- String newIndexName = rset.getString("INDEX_NAME");
- if (newIndexName == null)
- continue;
- if (indexName.compareToIgnoreCase(newIndexName) != 0) {
- if (index != null)
- table.getIndices().add(index);
- index = new Index(table);
- index.setName(newIndexName);
- indexName = newIndexName;
- // do handle PrimaryKey indices
- if ((newIndexName.compareToIgnoreCase("PrimaryKey") == 0)
- || (newIndexName.compareToIgnoreCase("Primary") == 0)
- || (newIndexName
- .compareToIgnoreCase("Prim鋜schl黶sel") == 0)) {
- index.setName("PRIMARY");
- index.setIsPrimary(1);
- table.setPrimaryKey(index);
- } else
- index.setIsPrimary(0);
- if (rset.getBoolean("NON_UNIQUE"))
- index.setUnique(0);
- else
- index.setUnique(1);
- }
- IndexColumn indexColumn = new IndexColumn(index);
- indexColumn.setName(rset.getString("COLUMN_NAME"));
- indexColumn.setColumnLength(0);
- if (rset.getString("ASC_OR_DESC").compareToIgnoreCase("D") == 0)
- indexColumn.setDescend(1);
- else
- indexColumn.setDescend(0);
- // find reference table column
- for (int j = 0; j < table.getColumns().size(); j++) {
- Column column = (Column) (table.getColumns().get(j));
- if (column.getName().compareToIgnoreCase(
- indexColumn.getName()) == 0) {
- indexColumn.setReferedColumn(column);
- break;
- }
- }
- index.getColumns().add(indexColumn);
- }
- if (index != null)
- table.getIndices().add(index);
- } catch (Exception e) {
- Grt.getInstance().addErr(e.getMessage());
- }
- }
- private static String tableFKSelect = "SELECT szRelationship, "
- + " szColumn, szReferencedObject, szReferencedColumn, grbit "
- + "FROM MSysRelationships " + "WHERE szObject=? "
- + "ORDER BY szRelationship, icolumn";
- protected void reverseEngineerTableFKs(Connection conn, Catalog catalog,
- Schema schema, Table table) {
- try {
- Grt.getInstance().addMsg("Fetching FK information.");
- Grt.getInstance().addMsgDetail(tableFKSelect);
- PreparedStatement stmt = conn.prepareStatement(tableFKSelect);
- stmt.setString(1, table.getName());
- ResultSet rset = stmt.executeQuery();
- String fkName = "";
- ForeignKey foreignKey = null;
- while (rset.next()) {
- String newFkName = rset.getString("szRelationship");
- int grbit = rset.getInt("grbit");
- // no referential integrity
- if ((grbit & 2) == 1)
- continue;
- if (fkName.compareToIgnoreCase(newFkName) != 0) {
- if (foreignKey != null)
- table.getForeignKeys().add(foreignKey);
- fkName = newFkName;
- foreignKey = new ForeignKey(table);
- foreignKey.setName(newFkName);
- foreignKey.setDeferability(0);
- if ((grbit & 2) != 0) {
- foreignKey.setDeleteRule("NO ACTION");
- foreignKey.setUpdateRule("NO ACTION");
- } else {
- if ((grbit & 256) != 0)
- foreignKey.setUpdateRule("CASCADE");
- else
- foreignKey.setUpdateRule("RESTRICT");
- if ((grbit & 4096) != 0)
- foreignKey.setDeleteRule("CASCADE");
- else
- foreignKey.setDeleteRule("RESTRICT");
- }
- /*
- * foreignKey.setDeleteRule("NO ACTION");
- * foreignKey.setUpdateRule("NO ACTION");
- *
- * if ((grbit & 256) != 0)
- * foreignKey.setUpdateRule("CASCADE");
- *
- * if ((grbit & 4096) != 0)
- * foreignKey.setDeleteRule("CASCADE");
- */
- foreignKey.setReferedTableSchemaName(schema.getName());
- foreignKey.setReferedTableName(rset
- .getString("szReferencedObject"));
- }
- foreignKey.getReferedColumnNames().add(
- rset.getString("szReferencedColumn"));
- // find reference table column
- String colName = rset.getString("szColumn");
- for (int j = 0; j < table.getColumns().size(); j++) {
- Column column = (Column) (table.getColumns().get(j));
- if (column.getName().compareToIgnoreCase(colName) == 0)
- foreignKey.getColumns().add(column);
- }
- }
- if (foreignKey != null)
- table.getForeignKeys().add(foreignKey);
- stmt.close();
- } catch (Exception e) {
- Grt.getInstance().addMsg("Cannot fetch foreign key information.");
- Grt.getInstance().addMsgDetail(e.getMessage());
- }
- }
- private static String viewSelect = "SELECT DISTINCTROW o.Name, o.Id, o.Flags "
- + "FROM MSysObjects o "
- + "WHERE o.Type = 5 AND Left(o.Name, 1) <> '~'"
- + "ORDER BY o.Name;";
- private static String viewClauseSelect = "SELECT q.Attribute, q.Expression, "
- + "q.Flag, q.Name1, q.Name2 "
- + "FROM MSysQueries q "
- + "WHERE q.ObjectId = ? " + "ORDER BY q.Attribute;";
- final static int MSA_CLAUSE_SELECT = 6;
- final static int MSA_CLAUSE_FROM = 5;
- final static int MSA_CLAUSE_JOIN = 7;
- final static int MSA_CLAUSE_JOIN_INNER_JOIN = 1;
- final static int MSA_CLAUSE_WHERE = 8;
- final static int MSA_CLAUSE_ORDER = 11;
- final static int QRY_CLAUSE_SELECT = 0;
- final static int QRY_CLAUSE_FROM = 1;
- final static int QRY_CLAUSE_WHERE = 2;
- final static int QRY_CLAUSE_GROUP = 3;
- final static int QRY_CLAUSE_HAVING = 4;
- final static int QRY_CLAUSE_ORDER = 5;
- protected void reverseEngineerViews(Connection conn, Catalog catalog,
- Schema schema) throws Exception {
- Grt.getInstance().addMsg("Fetch all views of the given schemata.");
- Statement stmt = conn.createStatement();
- ResultSet rset = null;
- try {
- rset = stmt.executeQuery(viewSelect);
- } catch (SQLException e) {
- Grt.getInstance().addMsg(
- "The views cannot be "
- + "reverse engineered because the MS Access "
- + "system tables cannot be accessed. "
- + "Take a look at the manual to enable access "
- + "to the system tables. " + e.getMessage());
- return;
- }
- while (rset.next()) {
- // Create new view
- View view = new View(schema);
- schema.getViews().add(view);
- view.setName(rset.getString("Name"));
- // int viewFlags = rset.getInt("Flags");
- int Id = rset.getInt("Id");
- // Fetch query expression clauses
- StringBuffer[] clauses = new StringBuffer[6];
- List fromTables = new ArrayList();
- PreparedStatement stmtClauses = conn
- .prepareStatement(viewClauseSelect);
- stmtClauses.setInt(1, Id);
- ResultSet rsetClauses = stmtClauses.executeQuery();
- while (rsetClauses.next()) {
- int cAttr = rsetClauses.getInt("Attribute");
- String cExp = rsetClauses.getString("Expression");
- int cFlag = rsetClauses.getInt("Flag");
- String cName1 = rsetClauses.getString("Name1");
- String cName2 = rsetClauses.getString("Name2");
- if (cAttr == MSA_CLAUSE_SELECT) {
- if (clauses[QRY_CLAUSE_SELECT] == null) {
- clauses[QRY_CLAUSE_SELECT] = new StringBuffer("SELECT ");
- } else {
- clauses[QRY_CLAUSE_SELECT].append(", ");
- }
- clauses[QRY_CLAUSE_SELECT].append(cExp);
- } else if (cAttr == MSA_CLAUSE_FROM) {
- // collect from tables so they do not collide with joins
- fromTables.add(cName1);
- } else if (cAttr == MSA_CLAUSE_JOIN) {
- if (cFlag == MSA_CLAUSE_JOIN_INNER_JOIN) {
- // INNER JOIN
- if (clauses[QRY_CLAUSE_FROM] == null) {
- clauses[QRY_CLAUSE_FROM] = new StringBuffer("FROM "
- + cName1 + " INNER JOIN " + cName2 + " ON "
- + cExp);
- } else {
- clauses[QRY_CLAUSE_FROM].append(", " + cName1
- + " INNER JOIN " + cName2 + " ON " + cExp);
- }
- // remove the tables from the join from the fromTables
- // list
- fromTables.remove(cName1);
- fromTables.remove(cName2);
- }
- } else if (cAttr == MSA_CLAUSE_WHERE) {
- if (clauses[QRY_CLAUSE_WHERE] == null) {
- clauses[QRY_CLAUSE_WHERE] = new StringBuffer("WHERE ");
- } else {
- clauses[QRY_CLAUSE_WHERE].append(" AND ");
- }
- clauses[QRY_CLAUSE_WHERE].append(cExp);
- } else if (cAttr == MSA_CLAUSE_ORDER) {
- if (clauses[QRY_CLAUSE_ORDER] == null) {
- clauses[QRY_CLAUSE_ORDER] = new StringBuffer(
- "ORDER BY ");
- } else {
- clauses[QRY_CLAUSE_ORDER].append(", ");
- }
- clauses[QRY_CLAUSE_ORDER].append(cExp);
- }
- }
- stmtClauses.close();
- // Attach remaining tables to FROM clause
- for (int i = 0; i < fromTables.size(); i++) {
- if (clauses[QRY_CLAUSE_FROM] == null) {
- clauses[QRY_CLAUSE_FROM] = new StringBuffer("FROM ");
- } else {
- clauses[QRY_CLAUSE_FROM].append(", ");
- }
- clauses[QRY_CLAUSE_FROM].append(fromTables.get(i));
- }
- // Build query expression from clauses
- StringBuffer queryExpr = clauses[QRY_CLAUSE_SELECT];
- if (queryExpr == null)
- queryExpr = new StringBuffer("SELECT * ");
- for (int i = 1; i <= 5; i++) {
- if (clauses[i] != null) {
- queryExpr.append("n");
- queryExpr.append(clauses[i]);
- }
- }
- view.setQueryExpression(queryExpr.toString());
- }
- stmt.close();
- }
- }