001: /*
002: * TableSearcher.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.ResultSet;
015: import java.sql.SQLException;
016: import java.sql.Savepoint;
017: import java.sql.Statement;
018: import java.sql.Types;
019: import workbench.WbManager;
020: import workbench.interfaces.TableSearchDisplay;
021: import workbench.log.LogMgr;
022: import workbench.storage.DataStore;
023: import workbench.util.ExceptionUtil;
024: import workbench.util.SqlUtil;
025: import workbench.util.SqlUtil;
026: import workbench.util.StringUtil;
027: import workbench.util.WbThread;
028:
029: /**
030: * @author support@sql-workbench.net
031: */
032: public class TableSearcher {
033: private TableIdentifier[] tablesToSearch;
034: private String columnFunction;
035: private TableSearchDisplay display;
036: private String criteria;
037: private WbConnection connection;
038: private boolean cancelSearch = false;
039: private boolean isRunning = false;
040: private Statement query = null;
041: private Thread searchThread;
042: private int maxRows = 0;
043: private boolean excludeLobColumns = true;
044: private DataStore result = null;
045:
046: public TableSearcher() {
047: }
048:
049: public void search() {
050: this .cancelSearch = false;
051: this .searchThread = new WbThread("TableSearcher Thread") {
052: public void run() {
053: doSearch();
054: }
055: };
056: this .searchThread.start();
057: }
058:
059: public void cancelSearch() {
060: this .cancelSearch = true;
061: try {
062: if (this .searchThread != null)
063: this .searchThread.interrupt();
064: if (this .query != null) {
065: this .query.cancel();
066: }
067: if (this .result != null) {
068: result.cancelRetrieve();
069: }
070: } catch (Throwable e) {
071: LogMgr.logWarning("TableSearcher.cancelSearc()",
072: "Error when cancelling", e);
073: }
074: }
075:
076: private void setRunning(boolean aFlag) {
077: synchronized (this ) {
078: this .isRunning = aFlag;
079: }
080: if (this .display != null) {
081: if (aFlag)
082: this .display.searchStarted();
083: else
084: this .display.searchEnded();
085: }
086: if (!aFlag)
087: this .cancelSearch = false;
088: }
089:
090: public synchronized boolean isRunning() {
091: return this .isRunning;
092: }
093:
094: protected void doSearch() {
095: if (this .tablesToSearch == null
096: || this .tablesToSearch.length == 0)
097: return;
098: this .setRunning(true);
099: try {
100: this .connection.setBusy(true);
101: for (int i = 0; i < this .tablesToSearch.length; i++) {
102: TableIdentifier tbl = tablesToSearch[i];
103: this .searchTable(tbl);
104: if (this .cancelSearch)
105: break;
106: }
107: if (this .display != null)
108: this .display.setStatusText("");
109: } catch (Throwable th) {
110: LogMgr.logError("TableSearcher.doSearch()",
111: "Error searching database", th);
112: } finally {
113: this .setRunning(false);
114: this .connection.setBusy(false);
115: }
116: }
117:
118: public void setExcludeLobColumns(boolean flag) {
119: this .excludeLobColumns = flag;
120: }
121:
122: private void searchTable(TableIdentifier table) {
123: ResultSet rs = null;
124: Savepoint sp = null;
125: boolean useSavepoint = connection.getDbSettings()
126: .useSavePointForDML();
127:
128: try {
129: String sql = this .buildSqlForTable(table);
130: if (this .display != null)
131: this .display.setCurrentTable(
132: table.getTableExpression(), sql);
133: if (sql == null)
134: return;
135:
136: if (!connection.getAutoCommit() && useSavepoint) {
137: try {
138: sp = connection.setSavepoint();
139: } catch (SQLException e) {
140: LogMgr.logWarning("TableSearcher.searchTable()",
141: "Could not create savepoint", e);
142: sp = null;
143: useSavepoint = false;
144: }
145: }
146: this .query = this .connection.createStatementForQuery();
147: this .query.setMaxRows(this .maxRows);
148:
149: rs = this .query.executeQuery(sql);
150: result = new DataStore(rs, true);
151: result.setGeneratingSql(sql);
152: result.setOriginalConnection(connection);
153: result.setUpdateTableToBeUsed(table);
154:
155: if (this .display != null)
156: this .display.tableSearched(table, result);
157: result = null;
158:
159: if (sp != null) {
160: connection.releaseSavepoint(sp);
161: sp = null;
162: }
163: } catch (OutOfMemoryError mem) {
164: WbManager.getInstance().showOutOfMemoryError();
165: } catch (Exception e) {
166: LogMgr.logError("TableSearcher.searchTable()",
167: "Error retrieving data for "
168: + table.getTableExpression(), e);
169: if (this .display != null)
170: this .display.error(ExceptionUtil.getDisplay(e));
171: if (sp != null) {
172: connection.rollback(sp);
173: }
174: } finally {
175: SqlUtil.closeAll(rs, query);
176: this .query = null;
177: if (sp != null) {
178: connection.releaseSavepoint(sp);
179: }
180: }
181: }
182:
183: private String buildSqlForTable(TableIdentifier tbl)
184: throws SQLException {
185: DbMetadata meta = this .connection.getMetadata();
186:
187: DataStore def = meta.getTableDefinition(tbl);
188: int cols = def.getRowCount();
189: StringBuilder sql = new StringBuilder(cols * 120);
190: sql.append("SELECT ");
191:
192: if (this .excludeLobColumns) {
193: int added = 0;
194: for (int i = 0; i < cols; i++) {
195: String column = def
196: .getValueAsString(
197: i,
198: DbMetadata.COLUMN_IDX_TABLE_DEFINITION_COL_NAME);
199: int type = def
200: .getValueAsInt(
201: i,
202: DbMetadata.COLUMN_IDX_TABLE_DEFINITION_JAVA_SQL_TYPE,
203: Types.OTHER);
204: if (!SqlUtil.isClobType(type)
205: && !SqlUtil.isBlobType(type)) {
206: if (added > 0)
207: sql.append(", ");
208: sql.append(this .connection.getMetadata()
209: .quoteObjectname(column));
210: added++;
211: }
212: }
213: } else {
214: sql.append("*");
215: }
216: sql.append(" FROM ");
217: sql.append(tbl.getTableExpression(this .connection));
218: sql.append("\n WHERE ");
219: boolean first = true;
220: int colcount = 0;
221: for (int i = 0; i < cols; i++) {
222: String column = def.getValueAsString(i,
223: DbMetadata.COLUMN_IDX_TABLE_DEFINITION_COL_NAME);
224: int sqlType = def
225: .getValueAsInt(
226: i,
227: DbMetadata.COLUMN_IDX_TABLE_DEFINITION_JAVA_SQL_TYPE,
228: Types.OTHER);
229: if (sqlType == Types.VARCHAR || sqlType == Types.CHAR) {
230: column = this .connection.getMetadata().quoteObjectname(
231: column);
232:
233: colcount++;
234: if (!first) {
235: sql.append(" OR ");
236: }
237:
238: if (this .columnFunction != null) {
239: sql.append(StringUtil.replace(this .columnFunction,
240: "$col$", column));
241: } else {
242: sql.append(column);
243: }
244: sql.append(" LIKE '");
245: sql.append(this .criteria);
246: sql.append('\'');
247: if (i < cols - 1)
248: sql.append('\n');
249:
250: first = false;
251: }
252: }
253: if (colcount == 0) {
254: LogMgr
255: .logWarning(
256: "TableSearcher.buildSqlForTable()",
257: "Table "
258: + tbl.getTableExpression()
259: + " not beeing searched because no character columns were found");
260: return null;
261: } else {
262: return sql.toString();
263: }
264: }
265:
266: public boolean getCriteriaMightBeCaseInsensitive() {
267: if (this .columnFunction == null)
268: return false;
269: if (this .criteria == null)
270: return false;
271: String func = this .columnFunction.toLowerCase();
272:
273: // upper() lower() is for Oracle, Postgres, Firebird/Interbase and MS SQL Server
274: // lcase, ucase is for Access and HSQLDB
275: if (func.indexOf("upper") > -1 || func.indexOf("ucase") > -1) {
276: return (this .criteria.toUpperCase().equals(this .criteria));
277: }
278: if (func.indexOf("lower") > -1 || func.indexOf("lcase") > -1) {
279: return (this .criteria.toLowerCase().equals(this .criteria));
280: }
281: return false;
282: }
283:
284: public boolean setColumnFunction(String aColFunc) {
285: this .columnFunction = null;
286: boolean result = false;
287: if (aColFunc != null && aColFunc.trim().length() > 0) {
288: if (aColFunc.equalsIgnoreCase("$col$")) {
289: this .columnFunction = null;
290: result = true;
291: } else if (aColFunc.indexOf("$col$") > -1) {
292: this .columnFunction = aColFunc;
293: result = true;
294: } else if (aColFunc.indexOf("$COL$") > -1) {
295: this .columnFunction = StringUtil.replace(aColFunc,
296: "$COL$", "$col$");
297: result = true;
298: }
299: }
300: return result;
301: }
302:
303: public void setTableNames(TableIdentifier[] tables) {
304: this .tablesToSearch = tables;
305: }
306:
307: public TableSearchDisplay getDisplay() {
308: return display;
309: }
310:
311: public void setDisplay(TableSearchDisplay searchDisplay) {
312: this .display = searchDisplay;
313: }
314:
315: public String getCriteria() {
316: return criteria;
317: }
318:
319: public void setCriteria(String aText) {
320: if (aText == null)
321: return;
322: this .criteria = StringUtil.trimQuotes(aText);
323: return;
324: }
325:
326: public void setConnection(WbConnection conn) {
327: this .connection = conn;
328: }
329:
330: public void setMaxRows(int maxRows) {
331: this.maxRows = maxRows;
332: }
333:
334: }
|