001: /*
002: * DeleteScriptGenerator.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db;
013:
014: import java.sql.SQLException;
015: import java.util.ArrayList;
016: import java.util.Collections;
017: import java.util.LinkedList;
018: import java.util.List;
019: import java.util.Map;
020: import java.util.Map.Entry;
021: import workbench.WbManager;
022: import workbench.gui.components.WbTable;
023: import workbench.gui.dbobjects.ObjectScripterUI;
024: import workbench.interfaces.ScriptGenerationMonitor;
025: import workbench.interfaces.Scripter;
026: import workbench.log.LogMgr;
027: import workbench.resource.ResourceMgr;
028: import workbench.resource.Settings;
029: import workbench.sql.formatter.SqlFormatter;
030: import workbench.storage.ColumnData;
031: import workbench.storage.DataStore;
032: import workbench.storage.SqlLiteralFormatter;
033: import workbench.util.SqlUtil;
034:
035: /**
036: * Generates a SQL script to delete a record from the given table and
037: * any dependent tables.
038: *
039: * @author support@sql-workbench.net
040: */
041: public class DeleteScriptGenerator implements Scripter {
042: private WbConnection connection;
043: private List<ColumnData> columnValues;
044: private TableDependency dependency;
045: private DbMetadata meta;
046: private DataStore tableDefinition;
047: private TableIdentifier rootTable = null;
048: private WbTable sourceTable = null;
049: private ScriptGenerationMonitor monitor;
050: private List<String> statements = new LinkedList<String>();
051: private SqlLiteralFormatter formatter;
052:
053: public DeleteScriptGenerator(WbConnection aConnection)
054: throws SQLException {
055: this .connection = aConnection;
056: this .meta = this .connection.getMetadata();
057: this .formatter = new SqlLiteralFormatter(this .connection);
058: }
059:
060: public void useJdbcLiterals() {
061: this .formatter.setProduct("jdbc");
062: }
063:
064: public void setSource(WbTable aTable) {
065: this .sourceTable = aTable;
066: }
067:
068: public void setTable(TableIdentifier table) throws SQLException {
069: if (table == null)
070: throw new IllegalArgumentException(
071: "The table name may not be empty");
072:
073: // Make sure we are using a completely filled TableIdentifier
074: // otherwise comparisons won't work correctly
075: this .rootTable = this .meta.findTable(table);
076: this .dependency = new TableDependency(this .connection,
077: this .rootTable);
078: this .tableDefinition = this .meta
079: .getTableDefinition(this .rootTable);
080: }
081:
082: public void setValues(List<ColumnData> colValues) {
083: this .columnValues = colValues;
084: }
085:
086: public boolean isCancelled() {
087: // not implemented yet
088: return false;
089: }
090:
091: public void cancel() {
092: // not implemented yet
093: }
094:
095: private void createStatements(boolean includeRoot) {
096: ArrayList<DependencyNode> parents = new ArrayList<DependencyNode>();
097: List<DependencyNode> visitedTables = new ArrayList<DependencyNode>();
098: this .dependency.readDependencyTree(true);
099: List<DependencyNode> leafs = this .dependency.getLeafs();
100:
101: for (DependencyNode node : leafs) {
102: if (visitedTables.contains(node))
103: continue;
104: statements.add(createDeleteStatement(node));
105: visitedTables.add(node);
106: DependencyNode p = node.getParent();
107: while (p != null) {
108: if (!isMasterTable(p) && !parents.contains(p)
109: && !leafs.contains(p)) {
110: parents.add(p);
111: }
112: p = p.getParent();
113: }
114: }
115:
116: for (DependencyNode pnode : parents) {
117: if (visitedTables.contains(pnode))
118: continue;
119: statements.add(createDeleteStatement(pnode));
120: visitedTables.add(pnode);
121: }
122:
123: if (includeRoot) {
124: DependencyNode root = this .dependency.getRootNode();
125: StringBuilder rootSql = new StringBuilder(100);
126: rootSql.append("DELETE FROM ");
127: rootSql.append(root.getTable().getTableExpression(
128: this .connection));
129: rootSql.append(" WHERE ");
130: this .addRootTableWhere(rootSql);
131: statements.add(formatSql(rootSql));
132: }
133: }
134:
135: private String formatSql(StringBuilder sql) {
136: try {
137: SqlFormatter f = new SqlFormatter(sql, Settings
138: .getInstance().getFormatterMaxSubselectLength());
139: String formatted = f.getFormattedSql().toString() + "\n";
140: return formatted;
141: } catch (Exception e) {
142: return sql.toString();
143: }
144: }
145:
146: private String createDeleteStatement(DependencyNode node) {
147: if (node == null)
148: return null;
149: StringBuilder sql = new StringBuilder(200);
150: sql.append("DELETE FROM ");
151: sql.append(node.getTable().getTableExpression(this .connection));
152: sql.append(" WHERE ");
153:
154: this .addParentWhere(sql, node);
155: return formatSql(sql);
156: }
157:
158: private void addParentWhere(StringBuilder sql, DependencyNode node) {
159: try {
160: DependencyNode parent = node.getParent();
161:
162: Map<String, String> columns = node.getColumns();
163: int count = 0;
164: for (Entry<String, String> entry : columns.entrySet()) {
165: String column = entry.getKey();
166: String parentColumn = entry.getValue();
167:
168: boolean addRootWhere = this .rootTable.equals(parent
169: .getTable());
170:
171: if (count > 0)
172: sql.append(" AND ");
173:
174: if (!addRootWhere) {
175: sql.append(" (");
176: sql.append(column);
177: sql.append(" IN ( SELECT ");
178: sql.append(parentColumn);
179: sql.append(" FROM ");
180: sql.append(parent.getTable().getTableExpression(
181: this .connection));
182: sql.append(" WHERE ");
183: this .addParentWhere(sql, parent);
184: sql.append("))");
185: } else {
186: this .addRootTableWhere(sql, parentColumn, column);
187: }
188: count++;
189: }
190: } catch (Throwable th) {
191: LogMgr.logError("DeleteScriptGenerator.addParentWhere()",
192: "Error during script generation", th);
193: }
194: }
195:
196: private boolean isMasterTable(DependencyNode node) {
197: TableIdentifier table = node.getTable();
198: return (this .rootTable.equals(table));
199: }
200:
201: private void addRootTableWhere(StringBuilder sql) {
202: boolean first = true;
203: for (ColumnData col : this .columnValues) {
204: if (!first) {
205: sql.append(" AND ");
206: } else {
207: first = false;
208: }
209: appendColumnData(sql, col.getIdentifier().getColumnName(),
210: col);
211: }
212: }
213:
214: private ColumnData findColData(String column) {
215: for (ColumnData col : this .columnValues) {
216: if (col.getIdentifier().getColumnName().equalsIgnoreCase(
217: column))
218: return col;
219: }
220: return null;
221: }
222:
223: private void addRootTableWhere(StringBuilder sql,
224: String parentColumn, String childColumn) {
225: ColumnData data = findColData(parentColumn);
226: appendColumnData(sql, childColumn, data);
227: }
228:
229: private void appendColumnData(StringBuilder sql, String column,
230: ColumnData data) {
231: sql.append(SqlUtil.quoteObjectname(column));
232: if (data.isNull() || data == null) {
233: sql.append(" IS NULL");
234: } else {
235: sql.append(" = ");
236: sql.append(formatter.getDefaultLiteral(data));
237: }
238: }
239:
240: public void startGenerate() {
241: ObjectScripterUI ui = new ObjectScripterUI(this );
242: ui.show(WbManager.getInstance().getCurrentWindow());
243: }
244:
245: public void setProgressMonitor(ScriptGenerationMonitor aMonitor) {
246: this .monitor = aMonitor;
247: }
248:
249: public String getScript() {
250: if (this .statements.size() == 0) {
251: this .generateScript();
252: }
253: StringBuilder script = new StringBuilder();
254:
255: for (String dml : statements) {
256: script.append(dml);
257: script.append(";\n\n");
258: }
259:
260: return script.toString();
261: }
262:
263: public CharSequence getScriptForValues(List<ColumnData> values)
264: throws SQLException {
265: this .statements.clear();
266: this .setValues(values);
267: this .createStatements(true);
268: return getScript();
269: }
270:
271: public List<String> getStatementsForValues(List<ColumnData> values,
272: boolean includeRoot) {
273: this .statements.clear();
274: this .setValues(values);
275: this .createStatements(includeRoot);
276: return Collections.unmodifiableList(statements);
277: }
278:
279: public void generateScript() {
280: if (this .sourceTable == null)
281: return;
282:
283: DataStore ds = this .sourceTable.getDataStore();
284: if (ds == null)
285: return;
286:
287: int[] rows = this .sourceTable.getSelectedRows();
288: if (rows.length == 0) {
289: return;
290: }
291:
292: if (this .connection.isBusy()) {
293: Exception e = new Exception("Connection is busy");
294: LogMgr.logError("DeleteScriptGenerator.generateScript()",
295: "Connection is busy!", e);
296: }
297:
298: ds.checkUpdateTable();
299: TableIdentifier tbl = ds.getUpdateTable();
300:
301: int numRows = rows.length;
302:
303: try {
304: connection.setBusy(true);
305: this .setTable(tbl);
306:
307: for (int i = 0; i < numRows; i++) {
308: List<ColumnData> pkvalues = ds.getPkValues(rows[i]);
309: this .setValues(pkvalues);
310: if (monitor != null)
311: this .monitor.setCurrentObject(ResourceMgr
312: .getString("MsgGeneratingScriptForRow")
313: + " " + (i + 1));
314: this .createStatements(true);
315: }
316: } catch (Exception e) {
317: LogMgr.logError("SqlPanel.generateDeleteScript",
318: "Error generating delete script", e);
319: } finally {
320: connection.setBusy(false);
321: }
322: }
323:
324: }
|