1 package net.sourceforge.phpdt.sql.sql;
3 import java.io.ByteArrayOutputStream;
5 import java.io.InputStream;
6 import java.io.PrintWriter;
8 import java.io.StringWriter;
10 import java.net.URLClassLoader;
11 import java.sql.Connection;
12 import java.sql.DatabaseMetaData;
13 import java.sql.Driver;
14 import java.sql.ResultSet;
15 import java.sql.ResultSetMetaData;
16 import java.sql.SQLException;
17 import java.sql.Statement;
18 import java.util.ArrayList;
19 import java.util.Hashtable;
20 import java.util.Properties;
21 import java.util.Vector;
23 import net.sourceforge.phpdt.sql.adapters.AdapterFactory;
24 import net.sourceforge.phpdt.sql.adapters.DatabaseAdapter;
25 import net.sourceforge.phpdt.sql.adapters.NoSuchAdapterException;
26 import net.sourceforge.phpdt.sql.bookmarks.Bookmark;
27 import net.sourceforge.phpdt.sql.view.LogProxy;
29 public class MultiSQLServer extends Thread {
30 private static final int STREAM = 1024 * 2;
31 public static final String USERNAME = "user";
32 public static final String PASSWORD = "password";
33 private static MultiSQLServer instance = null;
34 private Hashtable classLoaderCache = new Hashtable();
35 private Connection con = null;
36 boolean running = true;
37 private Bookmark current = null;
38 private MultiSQLServer() {
41 public synchronized static MultiSQLServer getInstance() {
42 if (instance == null) {
43 instance = new MultiSQLServer();
47 public Bookmark getConnected() {
51 public void commit() {
52 LogProxy log = LogProxy.getInstance();
55 } catch (SQLException e) {
56 log.addText(log.ERROR, "Error commiting: " + e);
57 StringWriter writer = new StringWriter();
58 e.printStackTrace(new PrintWriter(writer));
65 public void rollback() {
66 LogProxy log = LogProxy.getInstance();
69 } catch (SQLException e) {
70 log.addText(log.ERROR, "Error rolling back: " + e);
71 StringWriter writer = new StringWriter();
72 e.printStackTrace(new PrintWriter(writer));
79 public void setAutoCommit(boolean enabled) {
80 LogProxy log = LogProxy.getInstance();
83 con.setAutoCommit(enabled);
85 log.addText(log.ERROR, "Please connect before setting autocommit");
87 } catch (SQLException e) {
88 log.addText(log.ERROR, "Error setting autocommit: " + e);
89 StringWriter writer = new StringWriter();
90 e.printStackTrace(new PrintWriter(writer));
97 public DatabaseAdapter getCurrentAdapter() {
98 LogProxy log = LogProxy.getInstance();
100 AdapterFactory factory = AdapterFactory.getInstance();
101 return factory.getAdapter(current.getType());
102 } catch (NoSuchAdapterException e) {
103 log.addText(log.ERROR, "Invalid database type: ->" + current.getType() + "<-");
107 public void disconnect(Bookmark b) {
109 LogProxy log = LogProxy.getInstance();
114 log.addText(log.RESULTS, "Disconnected from: " + b.getName());
115 } catch (Exception e) {
118 "Error Disonnecting to: " + b.getName() + ":" + e.toString());
119 StringWriter writer = new StringWriter();
120 e.printStackTrace(new PrintWriter(writer));
127 public void shutdown() {
128 LogProxy log = LogProxy.getInstance();
134 } catch (SQLException e) {
135 StringWriter writer = new StringWriter();
136 e.printStackTrace(new PrintWriter(writer));
143 public void dumpDatabaseData() {
144 LogProxy log = LogProxy.getInstance();
146 DatabaseMetaData metadata = con.getMetaData();
147 log.addText(log.WARNING, "[METADATA] Database type: " + metadata.getDatabaseProductName());
148 if (metadata.supportsCatalogsInDataManipulation()) {
149 log.addText(log.WARNING, "[METADATA] Database does support catalog in data manipulation");
151 log.addText(log.WARNING, "[METADATA] Database does not support catalog in data manipulation");
153 if (metadata.supportsSchemasInDataManipulation()) {
154 log.addText(log.WARNING, "[METADATA] Database does support schema in data manipulation");
156 log.addText(log.WARNING, "[METADATA] Database does not support schema in data manipulation");
158 if (metadata.supportsCatalogsInTableDefinitions()) {
159 log.addText(log.WARNING, "[METADATA] Database does support catalogs in table definitions");
161 log.addText(log.WARNING, "[METADATA] Database does not support catalogs in table definitions");
163 log.addText(log.WARNING, "[METADATA] Catalog Separator: " + metadata.getCatalogSeparator());
164 log.addText(log.WARNING, "[METADATA] Catalog Term: " + metadata.getCatalogTerm());
165 ResultSet set = metadata.getCatalogs();
166 ArrayList catalogList = new ArrayList();
167 catalogList.add(null);
169 catalogList.add(set.getString(1));
172 StringBuffer catalogOutput = new StringBuffer();
173 catalogOutput.append("[CATALOG LIST] [");
174 for (int i = 0; i < catalogList.size(); i++) {
175 String name = (String) catalogList.get(i);
176 catalogOutput.append(name + ", ");
178 catalogOutput.append("]");
179 log.addText(log.WARNING, catalogOutput.toString());
181 set = metadata.getSchemas();
182 ArrayList schemaList = new ArrayList();
185 schemaList.add(set.getString(1));
188 StringBuffer schemaOutput = new StringBuffer();
189 schemaOutput.append("[SCHEMA LIST] [");
190 for (int i = 0; i < schemaList.size(); i++) {
191 String name = (String) schemaList.get(i);
192 schemaOutput.append(name + ", ");
194 schemaOutput.append("]");
195 log.addText(log.WARNING, schemaOutput.toString());
197 ArrayList tableTypes = new ArrayList();
198 set = metadata.getTableTypes();
200 tableTypes.add(set.getString(1));
204 StringBuffer tableListOutput = new StringBuffer();
205 tableListOutput.append("[TABLE LIST] [");
206 for (int i = 0; i < tableTypes.size(); i++) {
207 String name = (String) tableTypes.get(i);
208 tableListOutput.append(name + ", ");
210 tableListOutput.append("]");
211 log.addText(log.WARNING, tableListOutput.toString());
214 } catch (Exception e) {
215 log.addText(log.ERROR, "Error occured: " + e);
216 StringWriter writer = new StringWriter();
217 e.printStackTrace(new PrintWriter(writer));
224 * type = "TABLE" "VIEW" "SEQUENCE"
226 public Vector listTables(String schema, String type) {
227 LogProxy log = LogProxy.getInstance();
228 Vector retVal = new Vector();
229 log.addText(log.QUERY, "Retrieving list [" + type + "]");
231 DatabaseMetaData meta = con.getMetaData();
232 ResultSet set = meta.getTableTypes();
233 int columnCount = set.getMetaData().getColumnCount();
234 for (int i = 1; i <= columnCount; i++) {
235 System.out.print(set.getMetaData().getColumnName(i) + "\t");
237 System.out.println();
239 for (int i = 1; i <= columnCount; i++) {
240 System.out.print(set.getString(i) + "\t");
242 System.out.println();
244 Vector types = new Vector();
245 set = meta.getTableTypes();
247 types.add(set.getString(1));
250 if (types.contains(type)) {
251 set = meta.getTables(null, schema, "%", new String[] {type});
253 String name = set.getString("TABLE_NAME");
254 String tableType = set.getString("TABLE_TYPE");
255 //System.out.println(name + ":" + tableType);
256 retVal.addElement(name);
260 log.addText(log.RESULTS, "Success");
261 } catch (SQLException e) {
262 log.addText(log.ERROR, "Error occured: " + e);
263 StringWriter writer = new StringWriter();
264 e.printStackTrace(new PrintWriter(writer));
271 public boolean connect(Bookmark b) {
272 LogProxy log = LogProxy.getInstance();
273 log.addText(log.QUERY, "Connecting to: " + b.getName());
274 URL urls[] = new URL[1];
276 String driverFile = b.getDriverFile();
277 URLClassLoader loader = (URLClassLoader) classLoaderCache.get(driverFile);
278 if (loader == null) {
279 urls[0] = new File(driverFile).toURL();
280 loader = new URLClassLoader(urls);
281 classLoaderCache.put(driverFile, loader);
282 System.out.println("Creating new classloader");
284 System.out.println("Using classloader in cache");
286 Class driverClass = loader.loadClass(b.getDriver());
287 Driver driver = (Driver) driverClass.newInstance();
288 Properties props = new Properties();
289 props.put(USERNAME, b.getUsername());
290 props.put(PASSWORD, b.getPassword());
291 con = driver.connect(b.getConnect(), props);
293 throw new Exception("Error: Driver returned a null connection: " + b.toString());
296 log.addText(log.RESULTS, "Connected to: " + b.getName());
297 System.out.println("Connected");
299 } catch (Exception e) {
302 "Error Connecting to: " + b.getName() + ":" + e.toString());
303 StringWriter writer = new StringWriter();
304 e.printStackTrace(new PrintWriter(writer));
311 public SQLResults execute(String s) {
312 return execute(s, -1, -1);
314 public SQLResults execute(String s, int startRow, int endRow) {
315 return execute(s, -1, -1, Integer.MAX_VALUE);
317 public SQLResults execute(String s, int startRow, int endRow, int maxLength) {
318 return execute(s, startRow, endRow, maxLength, "");
320 public SQLResults execute(String s, int startRow, int endRow, int maxLength, String encoding) {
321 SQLResults results = new SQLResults();
323 System.out.println("Executing");
324 LogProxy log = LogProxy.getInstance();
325 log.addText(log.QUERY, "Executing Request [" + s + "]");
326 boolean metadata = false;
327 if (s.startsWith("METADATA")) {
332 String table = s.substring(s.indexOf(':') + 1);
333 String schema = current.getSchema();
334 String query = "SELECT * FROM " + schema + "." + table;
335 if (schema.equals("")) {
336 query = "SELECT * FROM " + table;
339 log.addText(log.QUERY, "Metadata Request [" + s + "]");
344 Statement stmt = con.createStatement();
345 boolean flag = stmt.execute(s);
346 results.setResultSet(flag);
348 int updates = stmt.getUpdateCount();
349 results.setUpdateCount(updates);
352 "Success: " + updates + " records updated");
356 ResultSet set = stmt.getResultSet();
357 ResultSetMetaData metaData = set.getMetaData();
358 int columnCount = metaData.getColumnCount();
359 Vector columnNames = new Vector();
360 columnNames.addElement("ColumnName");
361 columnNames.addElement("Type");
362 columnNames.addElement("Size");
363 columnNames.addElement("Nullable");
364 columnNames.addElement("AutoIncrement");
365 results.setColumnNames(columnNames);
366 for (int i = 1; i <= columnCount; i++) {
367 Vector row = new Vector();
368 row.addElement(metaData.getColumnName(i));
369 row.addElement(metaData.getColumnTypeName(i));
370 int textSize = metaData.getColumnDisplaySize(i);
371 int precision = metaData.getPrecision(i);
372 int scale = metaData.getScale(i);
373 if (scale == 0 && precision == 0) {
374 row.addElement(Integer.toString(precision));
376 row.addElement(textSize + ", " + precision + ", " + scale);
378 int nullable = metaData.isNullable(i);
379 if (nullable == metaData.columnNoNulls) {
380 row.addElement("Not Null");
381 } else if (nullable == metaData.columnNullable) {
382 row.addElement("Nullable");
383 } else if (nullable == metaData.columnNullableUnknown) {
384 row.addElement("Nullable");
386 row.addElement("<Error>");
388 row.addElement(new Boolean(metaData.isAutoIncrement(i)).toString());
391 results.setHasMore(false);
393 ResultSet set = stmt.getResultSet();
394 ResultSetMetaData metaData = set.getMetaData();
395 int columnCount = metaData.getColumnCount();
396 Vector columnNames = new Vector();
397 for (int i = 1; i <= columnCount; i++) {
398 columnNames.addElement(metaData.getColumnName(i));
400 results.setColumnNames(columnNames);
401 Vector columnTypes = new Vector();
402 for (int i = 1; i <= columnCount; i++) {
403 columnTypes.addElement(metaData.getColumnTypeName(i));
405 results.setColumnsTypes(columnTypes);
406 int columnSizes[] = new int[columnCount];
407 for (int i = 1; i <= columnCount; i++) {
408 columnSizes[i - 1] = metaData.getColumnDisplaySize(i);
411 boolean exitEarly = false;
413 boolean disable = startRow < 1 || endRow < 1;
414 boolean start = rowCount >= startRow;
415 boolean end = rowCount <= endRow;
416 if (disable || (start && end)) {
417 Vector row = new Vector();
418 for (int i = 1; i <= columnCount; i++) {
420 if (columnSizes[i - 1] < STREAM && columnSizes[i - 1] < maxLength) {
421 if (encoding.equals("")) {
422 value = set.getString(i);
424 value = new String(set.getBytes(i), encoding);
428 if (encoding.equals("")) {
429 Reader reader = set.getCharacterStream(i);
430 StringBuffer buffer = new StringBuffer();
431 if (reader != null) {
432 int retVal = reader.read();
434 while (retVal >= 0) {
435 buffer.append((char) retVal);
436 retVal = reader.read();
438 if (count > maxLength) {
439 buffer.append("...>>>");
445 value = buffer.toString();
447 InputStream binaryStream = set.getBinaryStream(i);
448 ByteArrayOutputStream baos = new ByteArrayOutputStream();
449 if (binaryStream != null) {
450 int retVal = binaryStream.read();
452 while (retVal >= 0) {
454 retVal = binaryStream.read();
456 if (count > maxLength) {
460 binaryStream.close();
462 value = new String(baos.toByteArray(), encoding);
464 } catch (Throwable e) {
465 // hack for mysql which doesn't implement
467 value = set.getString(i);
471 row.addElement("<NULL>");
473 row.addElement(value);
479 if (!disable && (rowCount > endRow)) {
485 results.setHasMore(set.next());
487 results.setMaxSize(rowCount);
488 results.setHasMore(false);
491 log.addText(log.RESULTS, "Success: result set displayed");
494 System.out.println("Executed");
495 System.out.println();
496 } catch (Exception e) {
497 results.setIsError(true);
498 log.addText(log.ERROR, "Error occured: " + e);
499 StringWriter writer = new StringWriter();
500 e.printStackTrace(new PrintWriter(writer));