001: /*
002:
003: Derby - Class org.apache.derby.impl.tools.dblook.DB_Key
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:
030: import java.util.HashMap;
031: import java.util.ArrayList;
032: import java.util.StringTokenizer;
033:
034: import org.apache.derby.tools.dblook;
035:
036: public class DB_Key {
037:
038: // Prepared statements used throughout the DDL
039: // generation process.
040: private static PreparedStatement getReferenceCols;
041: private static boolean printedHeader;
042:
043: /* ************************************************
044: * Generate the DDL for all keys in a given
045: * database.
046: * @param conn Connection to the source database.
047: * @return The DDL for the keys has been written
048: * to output via Logs.java.
049: ****/
050:
051: public static void doKeys(Connection conn) throws SQLException {
052:
053: printedHeader = false;
054: getReferenceCols = conn
055: .prepareStatement("SELECT CG.TABLEID, "
056: + "CG.DESCRIPTOR FROM SYS.SYSCONGLOMERATES CG, SYS.SYSKEYS K WHERE "
057: + "K.CONSTRAINTID = ? AND K.CONGLOMERATEID = CG.CONGLOMERATEID");
058:
059: // Non-foreign keys, first.
060: Statement stmt = conn.createStatement();
061: ResultSet rs = stmt
062: .executeQuery("SELECT CS.CONSTRAINTNAME, CS.TYPE, "
063: + "CS.TABLEID, CS.CONSTRAINTID, CS.SCHEMAID, CG.DESCRIPTOR, CG.ISCONSTRAINT "
064: + "FROM SYS.SYSCONSTRAINTS CS, SYS.SYSCONGLOMERATES CG, SYS.SYSKEYS K "
065: + "WHERE CS.STATE != 'D' AND CS.CONSTRAINTID = K.CONSTRAINTID AND "
066: + "CG.CONGLOMERATEID = K.CONGLOMERATEID ORDER BY CS.TABLEID");
067: createKeysFrom(rs);
068:
069: // Now, foreign keys.
070: rs = stmt
071: .executeQuery("SELECT CS.CONSTRAINTNAME, CS.TYPE, CS.TABLEID, "
072: + "CS.CONSTRAINTID, CS.SCHEMAID, CG.DESCRIPTOR, CG.ISCONSTRAINT, "
073: + "K.DELETERULE, K.UPDATERULE, K.KEYCONSTRAINTID FROM SYS.SYSCONSTRAINTS CS, "
074: + "SYS.SYSCONGLOMERATES CG, SYS.SYSFOREIGNKEYS K WHERE CS.STATE != 'D' "
075: + "AND CS.CONSTRAINTID = K.CONSTRAINTID AND CG.CONGLOMERATEID = "
076: + "K.CONGLOMERATEID ORDER BY CS.TABLEID");
077: createKeysFrom(rs);
078:
079: getReferenceCols.close();
080: stmt.close();
081: rs.close();
082: return;
083:
084: }
085:
086: /* ************************************************
087: * Generate the DDL for the a set of keys in the
088: * source database.
089: * @param rs Info on keys to dump; either a set of non-
090: * foreign keys (primary and unique), or a set of
091: * foreign keys.
092: * @return DDL for the receive set of keys has
093: * been written to output via Logs.java.
094: ****/
095:
096: private static void createKeysFrom(ResultSet rs)
097: throws SQLException {
098:
099: boolean firstTime = true;
100: while (rs.next()) {
101:
102: if (!rs.getBoolean(7))
103: // this row is NOT for a constraint, so skip it.
104: continue;
105:
106: String tableId = rs.getString(3);
107: String tableName = dblook.lookupTableId(tableId);
108: if (dblook.isExcludedTable(tableName))
109: // table isn't included in user-given list; skip it.
110: continue;
111:
112: if (firstTime) {
113: printHeader();
114: if (rs.getString(2).equals("F"))
115: Logs.reportMessage("DBLOOK_ForeignHeader");
116: else
117: Logs.reportMessage("DBLOOK_PrimUniqueHeader");
118: }
119:
120: StringBuffer kString = createKeyString(tableId, tableName,
121: rs);
122:
123: if (rs.getString(2).equals("F")) {
124: // foreign key; we have to figure out the references info.
125: kString.append(makeFKReferenceClause(rs.getString(10),
126: rs.getString(8).charAt(0), rs.getString(9)
127: .charAt(0)));
128: }
129:
130: Logs.writeToNewDDL(kString.toString());
131: Logs.writeStmtEndToNewDDL();
132: Logs.writeNewlineToNewDDL();
133: firstTime = false;
134:
135: }
136:
137: return;
138:
139: }
140:
141: /* ************************************************
142: * Generate DDL for a specific key.
143: * @param tableId Id of table on which the key exists.
144: * @param tableName Name of table on which the key exists.
145: * @param aKey Info on the key to generate.
146: * @return DDL for the specified key is returned as
147: * a string.
148: ****/
149:
150: private static StringBuffer createKeyString(String tableId,
151: String tableName, ResultSet aKey) throws SQLException {
152:
153: StringBuffer sb = new StringBuffer("ALTER TABLE ");
154: sb.append(tableName);
155: sb.append(" ADD");
156:
157: String constraintName = dblook.addQuotes(dblook
158: .expandDoubleQuotes(aKey.getString(1)));
159: sb.append(" CONSTRAINT ");
160: sb.append(constraintName);
161: sb.append(expandKeyType(aKey.getString(2).charAt(0)));
162:
163: // For keys, we need to get the column list.
164: sb.append("(");
165: sb.append(dblook.getColumnListFromDescription(tableId, aKey
166: .getString(6)));
167: sb.append(")");
168:
169: return sb;
170:
171: }
172:
173: /* ************************************************
174: * Takes a character representing a key type and
175: * returns the full type name (as it will appear in
176: * in the DDL).
177: * @param keyType Key type as a char.
178: * @return Key type as a full string.
179: ****/
180:
181: private static String expandKeyType(char keyType) {
182:
183: switch (keyType) {
184: case 'P':
185: return " PRIMARY KEY ";
186: case 'U':
187: return " UNIQUE ";
188: case 'F':
189: return " FOREIGN KEY ";
190: default:
191: // shouldn't happen.
192: Logs.debug("INTERNAL ERROR: unexpected key type" + keyType,
193: (String) null);
194: return "";
195: }
196:
197: }
198:
199: /* ************************************************
200: * Generate the DDL for a foreign key's "REFERENCES"
201: * clause.
202: * @param constraintId Id of the foreign key constraint.
203: * @param deleteChar What action to take on delete.
204: * @param updateChar What action to take on update.
205: * @return The DDL for the references clause of the
206: * foreign key, returned as a string.
207: ****/
208:
209: private static String makeFKReferenceClause(String constraintId,
210: char deleteChar, char updateChar) throws SQLException {
211:
212: StringBuffer refClause = new StringBuffer();
213:
214: getReferenceCols.setString(1, constraintId);
215: ResultSet colsRS = getReferenceCols.executeQuery();
216: colsRS.next();
217: refClause.append(" REFERENCES ");
218: refClause.append(dblook.lookupTableId(colsRS.getString(1)));
219: refClause.append(" (");
220: refClause.append(dblook.getColumnListFromDescription(colsRS
221: .getString(1), colsRS.getString(2)));
222: refClause.append(")");
223:
224: // On delete.
225: refClause.append(" ON DELETE ");
226: switch (deleteChar) {
227: case 'R':
228: refClause.append("NO ACTION");
229: break;
230: case 'S':
231: refClause.append("RESTRICT");
232: break;
233: case 'C':
234: refClause.append("CASCADE");
235: break;
236: case 'U':
237: refClause.append("SET NULL");
238: break;
239: default: // shouldn't happen.
240: Logs.debug(
241: "INTERNAL ERROR: unexpected 'on-delete' action: "
242: + deleteChar, (String) null);
243: break;
244: }
245:
246: // On update
247: refClause.append(" ON UPDATE ");
248: switch (updateChar) {
249: case 'R':
250: refClause.append("NO ACTION");
251: break;
252: case 'S':
253: refClause.append("RESTRICT");
254: break;
255: default: // shouldn't happen.
256: Logs.debug(
257: "INTERNAL ERROR: unexpected 'on-update' action: "
258: + updateChar, (String) null);
259: break;
260: }
261:
262: colsRS.close();
263: return refClause.toString();
264:
265: }
266:
267: /* ************************************************
268: * Print a simple header to output.
269: ****/
270:
271: private static void printHeader() {
272:
273: if (printedHeader)
274: return;
275:
276: Logs
277: .reportString("----------------------------------------------");
278: Logs.reportMessage("DBLOOK_KeysHeader");
279: Logs
280: .reportString("----------------------------------------------\n");
281: printedHeader = true;
282:
283: }
284:
285: }
|