001: package net.sourceforge.squirrel_sql.fw.sql;
002:
003: import java.io.Serializable;
004: import java.sql.ResultSet;
005: import java.sql.SQLException;
006: import java.sql.Statement;
007: import java.util.ArrayList;
008: import java.util.Collections;
009: import java.util.Comparator;
010: import java.util.HashSet;
011: import java.util.List;
012:
013: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
014: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
015:
016: public class SQLUtilities {
017:
018: /** Logger for this class. */
019: private final static ILogger s_log = LoggerController
020: .createLogger(SQLUtilities.class);
021:
022: /**
023: * Contributed by Thomas Mueller to handle doubling quote characters
024: * found in an identifier. In H2 and other dbs, the following statement
025: * creates a table with an embedded quote character:
026: *
027: * CREATE TABLE "foo""bar" (someid int);
028: *
029: * However, what is returned by the driver for table name is:
030: *
031: * foo"bar
032: *
033: * The reason is simple. Just like embedded quotes in SQL strings, such as:
034: *
035: * select 'I don''t know' from test
036: *
037: * Similarly, embedded quote characters can also appear in identifiers
038: * such as table names, by doubling (or quoting, if you will) the quote.
039: *
040: * @param s the string to have embedded quotes expanded.
041: *
042: * @return a new string with any embedded quotes doubled, or null if null is
043: * passed.
044: */
045: public static String quoteIdentifier(String s) {
046: if (s == null) {
047: return null;
048: }
049: StringBuilder buff = null;
050: buff = new StringBuilder();
051: for (int i = 0; i < s.length(); i++) {
052: char c = s.charAt(i);
053: if (c == '"' && i != 0 && i != s.length() - 1) {
054: buff.append(c);
055: }
056: buff.append(c);
057: }
058: String result = buff.toString();
059: return result;
060: }
061:
062: /**
063: * Reverses the insertion order list. Just a convenience method.
064: *
065: * @param md
066: * @param tables
067: * @return
068: * @throws SQLException
069: */
070: public static List<ITableInfo> getDeletionOrder(
071: List<ITableInfo> tables, SQLDatabaseMetaData md,
072: ProgressCallBack callback) throws SQLException {
073: List<ITableInfo> insertionOrder = getInsertionOrder(tables, md,
074: callback);
075: Collections.reverse(insertionOrder);
076: return insertionOrder;
077: }
078:
079: /**
080: * Returns the specified list of tables in an order such that insertions into
081: * all tables will satisfy any foreign key constraints. This will not
082: * correctly handle recursive constraints.
083: *
084: * This algorthim was adapted from SchemaSpy class/method:
085: *
086: * net.sourceforge.schemaspy.SchemaSpy.sortTablesByRI()
087: *
088: * unattached - tables that have no dependencies on other tables
089: * parents - tables that only have children
090: * children - tables that only have parents
091: * sandwiches - tables that have both parents and children - as in the
092: * "sandwich" generation.
093: *
094: * The first SQLException encountered while attempting to get FK information
095: * on any table will cause this to bail it's effort to re-order the list and
096: * the list will be returned as it came in - there's no point in spewing
097: * exceptions to end up with a flawed result; just give up.
098: *
099: * @param md
100: * @param tables
101: * @param listener
102: * @return
103: * @throws SQLException
104: */
105: public static List<ITableInfo> getInsertionOrder(
106: List<ITableInfo> tables, SQLDatabaseMetaData md,
107: ProgressCallBack callback) throws SQLException {
108: List<ITableInfo> result = new ArrayList<ITableInfo>();
109: // tables that are netiher children nor parents - utility tables
110: List<ITableInfo> unattached = new ArrayList<ITableInfo>();
111: // tables that have at least one parent table
112: List<ITableInfo> children = new ArrayList<ITableInfo>();
113: // tables that have at least one child table
114: List<ITableInfo> parents = new ArrayList<ITableInfo>();
115: // tables that have at least one child table and have a least one parent table
116: List<ITableInfo> sandwiches = new ArrayList<ITableInfo>();
117: ITableInfo lastTable = null;
118: try {
119: for (ITableInfo table : tables) {
120: lastTable = table;
121: callback.currentlyLoading(table.getSimpleName());
122: ForeignKeyInfo[] importedKeys = getImportedKeys(table,
123: md);
124: ForeignKeyInfo[] exportedKeys = getExportedKeys(table,
125: md);
126:
127: if (importedKeys != null && importedKeys.length == 0
128: && exportedKeys.length == 0) {
129: unattached.add(table);
130: continue;
131: }
132: if (exportedKeys != null && exportedKeys.length > 0) {
133: if (importedKeys != null && importedKeys.length > 0) {
134: sandwiches.add(table);
135: } else {
136: parents.add(table);
137: }
138: continue;
139: }
140: if (importedKeys != null && importedKeys.length > 0) {
141: children.add(table);
142: }
143: }
144: reorderTables(sandwiches);
145:
146: for (ITableInfo info : unattached) {
147: result.add(info);
148: }
149: for (ITableInfo info : parents) {
150: result.add(info);
151: }
152: for (ITableInfo info : sandwiches) {
153: result.add(info);
154: }
155: for (ITableInfo info : children) {
156: result.add(info);
157: }
158: if (result.size() != tables.size()) {
159: s_log
160: .error("getInsertionOrder(): failed to obtain a result table list "
161: + "("
162: + result.size()
163: + ") that is the same size as the input table "
164: + "list ("
165: + tables.size()
166: + ") - returning the original unordered "
167: + "list");
168: result = tables;
169: }
170: } catch (Exception e) {
171: if (lastTable != null) {
172: String tablename = lastTable.getSimpleName();
173: s_log.error(
174: "Unexpected exception while getting foreign key info for "
175: + "table " + tablename, e);
176: } else {
177: s_log
178: .error(
179: "Unexpected exception while getting foreign key info ",
180: e);
181: }
182: result = tables;
183: }
184: return result;
185: }
186:
187: public static ForeignKeyInfo[] getImportedKeys(ITableInfo ti,
188: SQLDatabaseMetaData md) throws SQLException {
189: ForeignKeyInfo[] result = ti.getImportedKeys();
190: if (result == null) {
191: result = md.getImportedKeysInfo(ti);
192: // Avoid the hit next time
193: ti.setImportedKeys(result);
194: }
195: return result;
196: }
197:
198: public static ForeignKeyInfo[] getExportedKeys(ITableInfo ti,
199: SQLDatabaseMetaData md) throws SQLException {
200: ForeignKeyInfo[] result = ti.getExportedKeys();
201: if (result == null) {
202: result = md.getExportedKeysInfo(ti);
203: // Avoid the hit next time
204: ti.setExportedKeys(result);
205: }
206: return result;
207: }
208:
209: private static void reorderTables(List<ITableInfo> sandwiches) {
210: Collections.sort(sandwiches, new TableComparator());
211: }
212:
213: private static class TableComparator implements
214: Comparator<ITableInfo>, Serializable {
215:
216: private static final long serialVersionUID = 1L;
217:
218: public int compare(ITableInfo t1, ITableInfo t2) {
219: ForeignKeyInfo[] t1ImportedKeys = t1.getImportedKeys();
220: for (int i = 0; i < t1ImportedKeys.length; i++) {
221: ForeignKeyInfo info = t1ImportedKeys[i];
222: if (info.getPrimaryKeyTableName().equals(
223: t2.getSimpleName())) {
224: // t1 depends on t2
225: return 1;
226: }
227: }
228: ForeignKeyInfo[] t2ImportedKeys = t2.getImportedKeys();
229: for (int i = 0; i < t2ImportedKeys.length; i++) {
230: ForeignKeyInfo info = t2ImportedKeys[i];
231: if (info.getPrimaryKeyTableName().equals(
232: t1.getSimpleName())) {
233: // t2 depends on t1
234: return -1;
235: }
236: }
237: if (t1.getImportedKeys().length > t2ImportedKeys.length) {
238: return 1;
239: }
240: if (t1.getImportedKeys().length < t2ImportedKeys.length) {
241: return -1;
242: }
243: return 0;
244: }
245:
246: }
247:
248: /**
249: * Returns a list of table names that have Primary Keys that are referenced by
250: * foreign key constraints on columns in the specified list of tables, that
251: * are not also contained in the specified list
252: *
253: * @param md
254: * @param tables
255: * @return
256: * @throws SQLException
257: */
258: public static List<String> getExtFKParents(SQLDatabaseMetaData md,
259: List<ITableInfo> tables) throws SQLException {
260: List<String> result = new ArrayList<String>();
261: HashSet<String> tableNames = new HashSet<String>();
262:
263: for (ITableInfo table : tables) {
264: tableNames.add(table.getSimpleName());
265: }
266:
267: for (ITableInfo table : tables) {
268: ForeignKeyInfo[] importedKeys = md
269: .getImportedKeysInfo(table);
270: for (int i = 0; i < importedKeys.length; i++) {
271: ForeignKeyInfo info = importedKeys[i];
272: String pkTable = info.getPrimaryKeyTableName();
273: if (!tableNames.contains(pkTable)) {
274: result.add(pkTable);
275: }
276: }
277: }
278: return result;
279: }
280:
281: /**
282: * Returns a list of table names that have Foreign keys that reference
283: * Primary Keys in the specified List of tables, but that are not also
284: * contained in the list of tables.
285: *
286: * @param md
287: * @param tables
288: * @return
289: * @throws SQLException
290: */
291: public static List<String> getExtFKChildren(SQLDatabaseMetaData md,
292: List<ITableInfo> tables) throws SQLException {
293: List<String> result = new ArrayList<String>();
294: HashSet<String> tableNames = new HashSet<String>();
295:
296: for (ITableInfo table : tables) {
297: tableNames.add(table.getSimpleName());
298: }
299:
300: for (ITableInfo table : tables) {
301: ForeignKeyInfo[] exportedKeys = md
302: .getExportedKeysInfo(table);
303: for (int i = 0; i < exportedKeys.length; i++) {
304: ForeignKeyInfo info = exportedKeys[i];
305: String fkTable = info.getForeignKeyTableName();
306: if (!tableNames.contains(fkTable)) {
307: result.add(fkTable);
308: }
309: }
310: }
311: return result;
312: }
313:
314: /**
315: * Closes the specified ResultSet safely (with no exceptions) and logs a
316: * debug message if SQLException is encountered. This will not close the
317: * Statement that created the ResultSet.
318: *
319: * @param rs the ResultSet to close - it can be null.
320: */
321: public static void closeResultSet(ResultSet rs) {
322: closeResultSet(rs, false);
323: }
324:
325: /**
326: * Closes the specified ResultSet safely (with no exceptions) and logs a
327: * debug message if SQLException is encountered. This will also close the
328: * Statement that created the ResultSet if closeStatement boolean is true.
329: *
330: * @param rs the ResultSet to close - it can be null.
331: * @param closeStatement if true, will close the Statement that created this
332: * ResultSet; false - will not close the Statement.
333: */
334: public static void closeResultSet(ResultSet rs,
335: boolean closeStatement) {
336: if (rs == null) {
337: return;
338: }
339: // Close the ResultSet
340: try {
341: rs.close();
342: } catch (SQLException e) {
343: if (s_log.isDebugEnabled()) {
344: s_log.debug(
345: "Unexpected exception while closing ResultSet: "
346: + e.getMessage(), e);
347: }
348: }
349: if (closeStatement) {
350: // Close the ResultSet's Statement if it is non-null. This frees open
351: // cursors.
352:
353: try {
354: Statement stmt = rs.getStatement();
355: if (stmt != null) {
356: stmt.close();
357: }
358: } catch (SQLException e) {
359: if (s_log.isDebugEnabled()) {
360: s_log.debug("Unexpected exception while closing "
361: + "Statement: " + e.getMessage(), e);
362: }
363: }
364: }
365: }
366:
367: /**
368: * Closes the specified Statement safely (with no exceptions) and logs a
369: * debug message if SQLException is encountered.
370: *
371: * @param stmt the Statement to close - it can be null.
372: */
373: public static void closeStatement(Statement stmt) {
374: if (stmt == null) {
375: return;
376: }
377: try {
378: stmt.close();
379: } catch (SQLException e) {
380: if (s_log.isDebugEnabled()) {
381: s_log.error(
382: "Unexpected exception while closing Statement: "
383: + e.getMessage(), e);
384: }
385: }
386: }
387:
388: }
|