archive/net.sourceforge.phpeclipse.sql/src/net/sourceforge/phpdt/sql/sql/MultiSQLServer.java

   1 package net.sourceforge.phpdt.sql.sql;
   2
   3 import java.io.ByteArrayOutputStream;
   4 import java.io.File;
   5 import java.io.InputStream;
   6 import java.io.PrintWriter;
   7 import java.io.Reader;
   8 import java.io.StringWriter;
   9 import java.net.URL;
  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;
  22
  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;
  28
  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() {
  39                 //start();
  40         }
  41         public synchronized static MultiSQLServer getInstance() {
  42                 if (instance == null) {
  43                         instance = new MultiSQLServer();
  44                 }
  45                 return instance;
  46         }
  47         public Bookmark getConnected() {
  48                 return current;
  49         }
  50
  51         public void commit() {
  52                 LogProxy log = LogProxy.getInstance();
  53                 try {
  54                         con.commit();
  55                 } catch (SQLException e) {
  56                         log.addText(log.ERROR, "Error commiting: " + e);
  57                         StringWriter writer = new StringWriter();
  58                         e.printStackTrace(new PrintWriter(writer));
  59                         log.addText(
  60                                 log.ERROR,
  61                                 writer.toString());
  62                 }
  63         }
  64
  65         public void rollback() {
  66                 LogProxy log = LogProxy.getInstance();
  67                 try {
  68                         con.rollback();
  69                 } catch (SQLException e) {
  70                         log.addText(log.ERROR, "Error rolling back: " + e);
  71                         StringWriter writer = new StringWriter();
  72                         e.printStackTrace(new PrintWriter(writer));
  73                         log.addText(
  74                                 log.ERROR,
  75                                 writer.toString());
  76                 }
  77         }
  78
  79         public void setAutoCommit(boolean enabled) {
  80                 LogProxy log = LogProxy.getInstance();
  81                 try {
  82                         if (con != null) {
  83                                 con.setAutoCommit(enabled);
  84                         } else {
  85                                 log.addText(log.ERROR, "Please connect before setting autocommit");
  86                         }
  87                 } catch (SQLException e) {
  88                         log.addText(log.ERROR, "Error setting autocommit: " + e);
  89                         StringWriter writer = new StringWriter();
  90                         e.printStackTrace(new PrintWriter(writer));
  91                         log.addText(
  92                                 log.ERROR,
  93                                 writer.toString());
  94                 }
  95         }
  96
  97         public DatabaseAdapter getCurrentAdapter() {
  98                 LogProxy log = LogProxy.getInstance();
  99                 try {
 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() + "<-");
 104                 }
 105                 return null;
 106         }
 107         public void disconnect(Bookmark b) {
 108                 current = null;
 109                 LogProxy log = LogProxy.getInstance();
 110
 111                 try {
 112                         con.close();
 113                         con = null;
 114                         log.addText(log.RESULTS, "Disconnected from: " + b.getName());
 115                 } catch (Exception e) {
 116                         log.addText(
 117                                 log.ERROR,
 118                                 "Error Disonnecting to: " + b.getName() + ":" + e.toString());
 119                         StringWriter writer = new StringWriter();
 120                         e.printStackTrace(new PrintWriter(writer));
 121                         log.addText(
 122                                 log.ERROR,
 123                                 writer.toString());
 124
 125                 }
 126         }
 127         public void shutdown() {
 128                 LogProxy log = LogProxy.getInstance();
 129                 try {
 130                         if (con != null) {
 131                                 con.close();
 132                         }
 133                         con = null;
 134                 } catch (SQLException e) {
 135                         StringWriter writer = new StringWriter();
 136                         e.printStackTrace(new PrintWriter(writer));
 137                         log.addText(
 138                                 log.ERROR,
 139                                 writer.toString());
 140                 }
 141         }
 142
 143         public void dumpDatabaseData() {
 144                 LogProxy log = LogProxy.getInstance();
 145                 try {
 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");
 150                         } else {
 151                                 log.addText(log.WARNING, "[METADATA] Database does not support catalog in data manipulation");
 152                         }
 153                         if (metadata.supportsSchemasInDataManipulation()) {
 154                                 log.addText(log.WARNING, "[METADATA] Database does support schema in data manipulation");
 155                         } else {
 156                                 log.addText(log.WARNING, "[METADATA] Database does not support schema in data manipulation");
 157                         }
 158                         if (metadata.supportsCatalogsInTableDefinitions()) {
 159                                 log.addText(log.WARNING, "[METADATA] Database does support catalogs in table definitions");
 160                         } else {
 161                                 log.addText(log.WARNING, "[METADATA] Database does not support catalogs in table definitions");
 162                         }
 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);
 168                         while (set.next()) {
 169                                 catalogList.add(set.getString(1));
 170                         }
 171                         set.close();
 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 + ", ");
 177                         }
 178                         catalogOutput.append("]");
 179                         log.addText(log.WARNING, catalogOutput.toString());
 180
 181                         set = metadata.getSchemas();
 182                         ArrayList schemaList = new ArrayList();
 183                         schemaList.add("");
 184                         while (set.next()) {
 185                                 schemaList.add(set.getString(1));
 186                         }
 187                         set.close();
 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 + ", ");
 193                         }
 194                         schemaOutput.append("]");
 195                         log.addText(log.WARNING, schemaOutput.toString());
 196
 197                         ArrayList tableTypes = new ArrayList();
 198                         set = metadata.getTableTypes();
 199                         while (set.next()) {
 200                                 tableTypes.add(set.getString(1));
 201                         }
 202                         set.close();
 203
 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 + ", ");
 209                         }
 210                         tableListOutput.append("]");
 211                         log.addText(log.WARNING, tableListOutput.toString());
 212
 213
 214                 } catch (Exception e) {
 215                         log.addText(log.ERROR, "Error occured: " + e);
 216                         StringWriter writer = new StringWriter();
 217                         e.printStackTrace(new PrintWriter(writer));
 218                         log.addText(
 219                                 log.ERROR,
 220                                 writer.toString());
 221                 }
 222         }
 223         /**
 224          * type = "TABLE" "VIEW" "SEQUENCE"
 225          */
 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 + "]");
 230                 try {
 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");
 236                         }
 237                         System.out.println();
 238                         while (set.next()) {
 239                                 for (int i = 1; i <= columnCount; i++) {
 240                                         System.out.print(set.getString(i) + "\t");
 241                                 }
 242                                 System.out.println();
 243                         }
 244                         Vector types = new Vector();
 245                         set = meta.getTableTypes();
 246                         while (set.next()) {
 247                                 types.add(set.getString(1));
 248                         }
 249                         set.close();
 250                         if (types.contains(type)) {
 251                                 set = meta.getTables(null, schema, "%", new String[] {type});
 252                                 while (set.next()) {
 253                                         String name = set.getString("TABLE_NAME");
 254                                         String tableType = set.getString("TABLE_TYPE");
 255                                         //System.out.println(name + ":" + tableType);
 256                                         retVal.addElement(name);
 257                                 }
 258                                 set.close();
 259                         }
 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));
 265                         log.addText(
 266                                 log.ERROR,
 267                                 writer.toString());
 268                 }
 269                 return retVal;
 270         }
 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];
 275                 try {
 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");
 283                         } else {
 284                                 System.out.println("Using classloader in cache");
 285                         }
 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);
 292                         if (con == null) {
 293                                 throw new Exception("Error: Driver returned a null connection: " + b.toString());
 294                         }
 295                         current = b;
 296                         log.addText(log.RESULTS, "Connected to: " + b.getName());
 297                         System.out.println("Connected");
 298                         return true;
 299                 } catch (Exception e) {
 300                         log.addText(
 301                                 log.ERROR,
 302                                 "Error Connecting to: " + b.getName() + ":" + e.toString());
 303                         StringWriter writer = new StringWriter();
 304                         e.printStackTrace(new PrintWriter(writer));
 305                         log.addText(
 306                                 log.ERROR,
 307                                 writer.toString());
 308                 }
 309                 return false;
 310         }
 311         public SQLResults execute(String s) {
 312                 return execute(s, -1, -1);
 313         }
 314         public SQLResults execute(String s, int startRow, int endRow) {
 315                 return execute(s, -1, -1, Integer.MAX_VALUE);
 316         }
 317         public SQLResults execute(String s, int startRow, int endRow, int maxLength) {
 318                 return execute(s, startRow, endRow, maxLength, "");
 319         }
 320         public SQLResults execute(String s, int startRow, int endRow, int maxLength, String encoding) {
 321                 SQLResults results = new SQLResults();
 322
 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")) {
 328                         metadata = true;
 329                 }
 330                 if (metadata) {
 331                         results.setQuery(s);
 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;
 337                         }
 338                         s = query;
 339                         log.addText(log.QUERY, "Metadata Request [" + s + "]");
 340         } else {
 341                         results.setQuery(s);
 342                 }
 343                 try {
 344                         Statement stmt = con.createStatement();
 345                         boolean flag = stmt.execute(s);
 346                         results.setResultSet(flag);
 347                         if (!flag) {
 348                                 int updates = stmt.getUpdateCount();
 349                                 results.setUpdateCount(updates);
 350                                 log.addText(
 351                                         log.RESULTS,
 352                                         "Success: " + updates + " records updated");
 353
 354                         } else {
 355                                 if (metadata) {
 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));
 375                                                 } else {
 376                                                         row.addElement(textSize + ", " + precision + ", " + scale);
 377                                                 }
 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");
 385                                                 } else {
 386                                                         row.addElement("<Error>");
 387                                                 }
 388                                                 row.addElement(new Boolean(metaData.isAutoIncrement(i)).toString());
 389                                                 results.addRow(row);
 390                                         }
 391                                         results.setHasMore(false);
 392                                 } else {
 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));
 399                                         }
 400                                         results.setColumnNames(columnNames);
 401                                         Vector columnTypes = new Vector();
 402                                         for (int i = 1; i <= columnCount; i++) {
 403                                                 columnTypes.addElement(metaData.getColumnTypeName(i));
 404                                         }
 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);
 409                                         }
 410                                         int rowCount = 1;
 411                                         boolean exitEarly = false;
 412                                         while (set.next()) {
 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++) {
 419                                                                 String value;
 420                                                                 if (columnSizes[i - 1] < STREAM && columnSizes[i - 1] < maxLength) {
 421                                                                         if (encoding.equals("")) {
 422                                                                                 value = set.getString(i);
 423                                                                         } else {
 424                                                                                 value = new String(set.getBytes(i), encoding);
 425                                                                         }
 426                                                                 } else {
 427                                                                         try {
 428                                                                                 if (encoding.equals("")) {
 429                                                                                         Reader reader = set.getCharacterStream(i);
 430                                                                                         StringBuffer buffer = new StringBuffer();
 431                                                                                         if (reader != null) {
 432                                                                                                 int retVal = reader.read();
 433                                                                                                 int count = 0;
 434                                                                                                 while (retVal >= 0) {
 435                                                                                                         buffer.append((char) retVal);
 436                                                                                                         retVal = reader.read();
 437                                                                                                         count++;
 438                                                                                                         if (count > maxLength) {
 439                                                                                                                 buffer.append("...>>>");
 440                                                                                                                 break;
 441                                                                                                         }
 442                                                                                                 }
 443                                                                                                 reader.close();
 444                                                                                         }
 445                                                                                         value = buffer.toString();
 446                                                                                 } else {
 447                                                                                         InputStream binaryStream = set.getBinaryStream(i);
 448                                                                                         ByteArrayOutputStream baos = new ByteArrayOutputStream();
 449                                                                                         if (binaryStream != null) {
 450                                                                                                 int retVal = binaryStream.read();
 451                                                                                                 int count = 0;
 452                                                                                                 while (retVal >= 0) {
 453                                                                                                         baos.write(retVal);
 454                                                                                                         retVal = binaryStream.read();
 455                                                                                                         count++;
 456                                                                                                         if (count > maxLength) {
 457                                                                                                                 break;
 458                                                                                                         }
 459                                                                                                 }
 460                                                                                                 binaryStream.close();
 461                                                                                         }
 462                                                                                         value = new String(baos.toByteArray(), encoding);
 463                                                                                 }
 464                                                                         } catch (Throwable e) {
 465                                                                                 // hack for mysql which doesn't implement
 466                                                                                 // character streams
 467                                                                                 value = set.getString(i);
 468                                                                         }
 469                                                                 }
 470                                                                 if (set.wasNull()) {
 471                                                                         row.addElement("<NULL>");
 472                                                                 } else {
 473                                                                         row.addElement(value);
 474                                                                 }
 475                                                         }
 476                                                         results.addRow(row);
 477                                                 }
 478                                                 rowCount++;
 479                                                 if (!disable && (rowCount > endRow)) {
 480                                                         exitEarly = true;
 481                                                         break;
 482                                                 }
 483                                         }
 484                                         if (exitEarly) {
 485                                                 results.setHasMore(set.next());
 486                                         } else {
 487                                                 results.setMaxSize(rowCount);
 488                                                 results.setHasMore(false);
 489                                         }
 490                                 }
 491                                 log.addText(log.RESULTS, "Success: result set displayed");
 492                         }
 493                         stmt.close();
 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));
 501                         log.addText(
 502                                 log.ERROR,
 503                                 writer.toString());
 504                 }
 505                 return results;
 506         }
 507 }