|
1
21
22 package org.apache.derby.impl.tools.dblook;
23
24 import java.sql.Connection  ;
25 import java.sql.Statement ;
26 import java.sql.PreparedStatement ;
27 import java.sql.ResultSet ;
28 import java.sql.SQLException ;
29
30 import java.util.HashMap ;
31 import java.util.ArrayList ;
32 import java.util.StringTokenizer ;
33
34 import org.apache.derby.tools.dblook;
35
36 public class DB_Key {
37
38 private static PreparedStatement getReferenceCols;
41 private static boolean printedHeader;
42
43
50
51 public static void doKeys(Connection conn)
52 throws SQLException
53 {
54
55 printedHeader = false;
56 getReferenceCols = conn.prepareStatement("SELECT CG.TABLEID, " +
57 "CG.DESCRIPTOR FROM SYS.SYSCONGLOMERATES CG, SYS.SYSKEYS K WHERE " +
58 "K.CONSTRAINTID = ? AND K.CONGLOMERATEID = CG.CONGLOMERATEID");
59
60 Statement stmt = conn.createStatement();
62 ResultSet rs = stmt.executeQuery("SELECT CS.CONSTRAINTNAME, CS.TYPE, " +
63 "CS.TABLEID, CS.CONSTRAINTID, CS.SCHEMAID, CG.DESCRIPTOR, CG.ISCONSTRAINT " +
64 "FROM SYS.SYSCONSTRAINTS CS, SYS.SYSCONGLOMERATES CG, SYS.SYSKEYS K " +
65 "WHERE CS.STATE != 'D' AND CS.CONSTRAINTID = K.CONSTRAINTID AND " +
66 "CG.CONGLOMERATEID = K.CONGLOMERATEID ORDER BY CS.TABLEID");
67 createKeysFrom(rs);
68
69 rs = stmt.executeQuery("SELECT CS.CONSTRAINTNAME, CS.TYPE, CS.TABLEID, " +
71 "CS.CONSTRAINTID, CS.SCHEMAID, CG.DESCRIPTOR, CG.ISCONSTRAINT, " +
72 "K.DELETERULE, K.UPDATERULE, K.KEYCONSTRAINTID FROM SYS.SYSCONSTRAINTS CS, " +
73 "SYS.SYSCONGLOMERATES CG, SYS.SYSFOREIGNKEYS K WHERE CS.STATE != 'D' " +
74 "AND CS.CONSTRAINTID = K.CONSTRAINTID AND CG.CONGLOMERATEID = " +
75 "K.CONGLOMERATEID ORDER BY CS.TABLEID");
76 createKeysFrom(rs);
77
78 getReferenceCols.close();
79 stmt.close();
80 rs.close();
81 return;
82
83 }
84
85
94
95 private static void createKeysFrom (ResultSet rs)
96 throws SQLException
97 {
98
99 boolean firstTime = true;
100 while (rs.next()) {
101
102 if (!rs.getBoolean(7))
103 continue;
105
106 String tableId = rs.getString(3);
107 String tableName = dblook.lookupTableId(tableId);
108 if (dblook.isExcludedTable(tableName))
109 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, rs);
121
122 if (rs.getString(2).equals("F")) {
123 kString.append(makeFKReferenceClause(rs.getString(10),
125 rs.getString(8).charAt(0), rs.getString(9).charAt(0)));
126 }
127
128 Logs.writeToNewDDL(kString.toString());
129 Logs.writeStmtEndToNewDDL();
130 Logs.writeNewlineToNewDDL();
131 firstTime = false;
132
133 }
134
135 return;
136
137 }
138
139
147
148 private static StringBuffer createKeyString (String tableId,
149 String tableName, ResultSet aKey)
150 throws SQLException
151 {
152
153 StringBuffer sb = new StringBuffer ("ALTER TABLE ");
154 sb.append(tableName);
155 sb.append(" ADD");
156
157 String constraintName = dblook.addQuotes(
158 dblook.expandDoubleQuotes(aKey.getString(1)));
159 sb.append(" CONSTRAINT ");
160 sb.append(constraintName);
161 sb.append(expandKeyType(aKey.getString(2).charAt(0)));
162
163 sb.append("(");
165 sb.append(dblook.getColumnListFromDescription(
166 tableId, aKey.getString(6)));
167 sb.append(")");
168
169 return sb;
170
171 }
172
173
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 Logs.debug("INTERNAL ERROR: unexpected key type" +
193 keyType, (String )null);
194 return "";
195 }
196
197 }
198
199
208
209 private static String makeFKReferenceClause(String constraintId,
210 char deleteChar, char updateChar)
211 throws SQLException
212 {
213
214 StringBuffer refClause = new StringBuffer ();
215
216 getReferenceCols.setString(1, constraintId);
217 ResultSet colsRS = getReferenceCols.executeQuery();
218 colsRS.next();
219 refClause.append(" REFERENCES ");
220 refClause.append(dblook.lookupTableId(colsRS.getString(1)));
221 refClause.append(" (");
222 refClause.append(dblook.getColumnListFromDescription(
223 colsRS.getString(1), colsRS.getString(2)));
224 refClause.append(")");
225
226 refClause.append(" ON DELETE ");
228 switch (deleteChar) {
229 case 'R': refClause.append("NO ACTION"); break;
230 case 'S': refClause.append("RESTRICT"); break;
231 case 'C': refClause.append("CASCADE"); break;
232 case 'U': refClause.append("SET NULL"); break;
233 default: Logs.debug("INTERNAL ERROR: unexpected 'on-delete' action: " +
235 deleteChar, (String )null);
236 break;
237 }
238
239 refClause.append(" ON UPDATE ");
241 switch (updateChar) {
242 case 'R': refClause.append("NO ACTION"); break;
243 case 'S': refClause.append("RESTRICT"); break;
244 default: Logs.debug("INTERNAL ERROR: unexpected 'on-update' action: " +
246 updateChar, (String )null);
247 break;
248 }
249
250 colsRS.close();
251 return refClause.toString();
252
253 }
254
255
258
259 private static void printHeader() {
260
261 if (printedHeader)
262 return;
263
264 Logs.reportString("----------------------------------------------");
265 Logs.reportMessage("DBLOOK_KeysHeader");
266 Logs.reportString("----------------------------------------------\n");
267 printedHeader = true;
268
269 }
270
271 }
272 |
Popular Tags |
Java API By Example, From Geeks To Geeks.