001: /*
002:
003: Derby - Class org.apache.derby.impl.tools.dblook.DB_Table
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to you under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derby.impl.tools.dblook;
023:
024: import java.sql.Connection;
025: import java.sql.Statement;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029: import java.sql.ResultSetMetaData;
030:
031: import java.util.HashMap;
032: import java.util.Set;
033: import java.util.Iterator;
034:
035: import org.apache.derby.tools.dblook;
036:
037: public class DB_Table {
038:
039: // Prepared statements use throughout the DDL
040: // generation process.
041: private static PreparedStatement getColumnInfoStmt;
042: private static PreparedStatement getColumnTypeStmt;
043: private static PreparedStatement getAutoIncStmt;
044:
045: /* ************************************************
046: * Generate the DDL for all user tables in a given
047: * database.
048: * @param conn Connection to the source database.
049: * @param tableIdToNameMap Mapping of table ids to table
050: * names, for quicker reference.
051: * @return The DDL for the tables has been written
052: * to output via Logs.java.
053: ****/
054:
055: public static void doTables(Connection conn,
056: HashMap tableIdToNameMap) throws SQLException {
057:
058: // Prepare some statements for general use by this class.
059:
060: getColumnInfoStmt = conn
061: .prepareStatement("SELECT C.COLUMNNAME, C.REFERENCEID, "
062: + "C.COLUMNNUMBER FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T WHERE T.TABLEID = ? "
063: + "AND T.TABLEID = C.REFERENCEID ORDER BY C.COLUMNNUMBER");
064:
065: getColumnTypeStmt = conn
066: .prepareStatement("SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS "
067: + "WHERE REFERENCEID = ? AND COLUMNNAME = ?");
068:
069: getAutoIncStmt = conn
070: .prepareStatement("SELECT AUTOINCREMENTSTART, "
071: + "AUTOINCREMENTINC, COLUMNNAME, REFERENCEID, COLUMNDEFAULT FROM SYS.SYSCOLUMNS "
072: + "WHERE COLUMNNAME = ? AND REFERENCEID = ?");
073:
074: // Walk through list of tables and generate the DDL for
075: // each one.
076:
077: boolean firstTime = true;
078: Set tableIds = tableIdToNameMap.keySet();
079: for (Iterator itr = tableIds.iterator(); itr.hasNext();) {
080:
081: String tableId = (String) itr.next();
082: String tableName = (String) (tableIdToNameMap.get(tableId));
083: if (dblook.isExcludedTable(tableName))
084: // table isn't included in user-given list; skip it.
085: continue;
086:
087: if (firstTime) {
088: Logs
089: .reportString("----------------------------------------------");
090: Logs.reportMessage("DBLOOK_TablesHeader");
091: Logs
092: .reportString("----------------------------------------------\n");
093: }
094:
095: Logs.writeToNewDDL("CREATE TABLE " + tableName + " (");
096:
097: // Get column list, and write DDL for each column.
098: boolean firstCol = true;
099: getColumnInfoStmt.setString(1, tableId);
100: ResultSet columnRS = getColumnInfoStmt.executeQuery();
101: while (columnRS.next()) {
102: String colName = dblook
103: .addQuotes(columnRS.getString(1));
104: String createColString = createColumn(colName, columnRS
105: .getString(2), columnRS.getInt(3));
106: if (!firstCol)
107: createColString = ", " + createColString;
108:
109: Logs.writeToNewDDL(createColString);
110: firstCol = false;
111: }
112:
113: columnRS.close();
114: Logs.writeToNewDDL(")");
115: Logs.writeStmtEndToNewDDL();
116: Logs.writeNewlineToNewDDL();
117: firstTime = false;
118:
119: } // outer while.
120:
121: getColumnInfoStmt.close();
122: getColumnTypeStmt.close();
123: getAutoIncStmt.close();
124:
125: }
126:
127: /* ************************************************
128: * Generate the DDL for a specific column of the
129: * the table corresponding to the received tableId.
130: * @param colName the name of the column to generate.
131: * @param tableId Which table the column belongs to.
132: * @param colNum the number of the column to generate (1 =>
133: * 1st column, 2 => 2nd column, etc)
134: * @return The generated DDL, as a string.
135: ****/
136:
137: private static String createColumn(String colName, String tableId,
138: int colNum) throws SQLException {
139:
140: getColumnTypeStmt.setString(1, tableId);
141: getColumnTypeStmt.setString(2, dblook.stripQuotes(colName));
142:
143: ResultSet rs = getColumnTypeStmt.executeQuery();
144: StringBuffer colDef = new StringBuffer();
145: if (rs.next()) {
146:
147: colDef.append(dblook.addQuotes(dblook
148: .expandDoubleQuotes(dblook.stripQuotes(colName))));
149: colDef.append(" ");
150: colDef.append(rs.getString(1));
151: if (!reinstateAutoIncrement(colName, tableId, colDef)
152: && rs.getString(2) != null) {
153: colDef.append(" DEFAULT ");
154: colDef.append(rs.getString(2));
155: }
156: }
157:
158: rs.close();
159: return colDef.toString();
160:
161: }
162:
163: /* ************************************************
164: * Generate autoincrement DDL for a given column and write it to
165: * received StringBuffer
166: * @param colName: Name of column that is autoincrement.
167: * @param tableId: Id of table in which column exists.
168: * @param colDef: StringBuffer to which DDL will be added.
169: * @return True if autoincrement DDL has been generated.
170: ****/
171:
172: public static boolean reinstateAutoIncrement(String colName,
173: String tableId, StringBuffer colDef) throws SQLException {
174:
175: getAutoIncStmt.setString(1, dblook.stripQuotes(colName));
176: getAutoIncStmt.setString(2, tableId);
177: ResultSet autoIncCols = getAutoIncStmt.executeQuery();
178: if (autoIncCols.next()) {
179:
180: long start = autoIncCols.getLong(1);
181: if (!autoIncCols.wasNull()) {
182: colDef.append(" GENERATED ");
183: colDef
184: .append(autoIncCols.getObject(5) == null ? "ALWAYS "
185: : "BY DEFAULT ");
186: colDef.append("AS IDENTITY (START WITH ");
187: colDef.append(autoIncCols.getLong(1));
188: colDef.append(", INCREMENT BY ");
189: colDef.append(autoIncCols.getLong(2));
190: colDef.append(")");
191: return true;
192: }
193: }
194:
195: return false;
196:
197: }
198:
199: }
|