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.Reader;
   7 import java.net.URL;
   8 import java.net.URLClassLoader;
   9 import java.sql.Connection;
  10 import java.sql.DatabaseMetaData;
  11 import java.sql.Driver;
  12 import java.sql.ResultSet;
  13 import java.sql.ResultSetMetaData;
  14 import java.sql.SQLException;
  15 import java.sql.Statement;
  16 import java.util.ArrayList;
  17 import java.util.Hashtable;
  18 import java.util.List;
  19 import java.util.Properties;
  20 import java.util.StringTokenizer;
  21 import java.util.Vector;
  22
  23 import org.apache.xml.utils.IntVector;
  24
  25 import net.sourceforge.phpdt.sql.bookmarks.Bookmark;
  26 import net.sourceforge.phpdt.sql.sql.metadata.MetaDataJDBCInterface;
  27 import net.sourceforge.phpdt.sql.sql.metadata.ObjectMetaData;
  28 import net.sourceforge.phpdt.sql.view.LogProxy;
  29 import net.sourceforge.phpdt.sql.view.bookmark.TableNode;
  30 import net.sourceforge.phpdt.sql.view.bookmark.TreeNode;
  31
  32 /**
  33  *
  34  * MultiSQLServer is a Singleton, used  as a interface with the sql drivers
  35  * Use MultiSQLServer.getInstance() to get the object
  36  */
  37 public class MultiSQLServer implements ConnectionEstablisher {
  38         private static final int STREAM = 1024 * 2;
  39         public static final String USERNAME = "user"; //$NON-NLS-1$
  40         public static final String PASSWORD = "password"; //$NON-NLS-1$
  41         private static MultiSQLServer instance = null;
  42         private Hashtable classLoaderCache = new Hashtable();
  43         boolean running = true;
  44
  45         public MultiSQLServer() {
  46                 //start();
  47         }
  48         public synchronized static MultiSQLServer getInstance() {
  49                 if (instance == null) {
  50                         instance = new MultiSQLServer();
  51                 }
  52                 return instance;
  53         }
  54
  55         public void commit(Connection con) {
  56                 LogProxy log = LogProxy.getInstance();
  57                 try {
  58                         con.commit();
  59                 } catch (SQLException e) {
  60                         log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
  61                 }
  62         }
  63
  64         public void rollback(Connection con) {
  65                 LogProxy log = LogProxy.getInstance();
  66                 try {
  67                         con.rollback();
  68                 } catch (SQLException e) {
  69                         log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
  70                 }
  71         }
  72
  73         public void setAutoCommit(Connection con, boolean enabled) {
  74                 LogProxy log = LogProxy.getInstance();
  75                 try {
  76                         if (con != null) {
  77                                 con.setAutoCommit(enabled);
  78                         } else {
  79                                 log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$
  80                         }
  81                 } catch (SQLException e) {
  82                         log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$
  83                 }
  84         }
  85
  86         public void disconnect(Bookmark b, Connection con) {
  87                 LogProxy log = LogProxy.getInstance();
  88                 try {
  89                         con.close();
  90                         b.setConnection(null);
  91                         log.addText(LogProxy.RESULTS, "Disconnected from: " + b.getName()); //$NON-NLS-1$
  92                 } catch (Exception e) {
  93                         log.addText(
  94                                 LogProxy.ERROR,
  95                                 "Error Disonnecting to: " + b.getName() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
  96                 }
  97         }
  98
  99         public void dumpDatabaseData(Connection con) {
 100                 LogProxy log = LogProxy.getInstance();
 101                 try {
 102                         DatabaseMetaData meta = con.getMetaData();
 103                         log.addText(LogProxy.WARNING, "[METADATA] Database type: " + meta.getDatabaseProductName()); //$NON-NLS-1$
 104                         if (meta.supportsCatalogsInDataManipulation()) {
 105                                 log.addText(LogProxy.WARNING, "[METADATA] Database does support catalog in data manipulation"); //$NON-NLS-1$
 106                         } else {
 107                                 log.addText(LogProxy.WARNING, "[METADATA] Database does not support catalog in data manipulation"); //$NON-NLS-1$
 108                         }
 109                         if (meta.supportsSchemasInDataManipulation()) {
 110                                 log.addText(LogProxy.WARNING, "[METADATA] Database does support schema in data manipulation"); //$NON-NLS-1$
 111                         } else {
 112                                 log.addText(LogProxy.WARNING, "[METADATA] Database does not support schema in data manipulation"); //$NON-NLS-1$
 113                         }
 114                         if (meta.supportsCatalogsInTableDefinitions()) {
 115                                 log.addText(LogProxy.WARNING, "[METADATA] Database does support catalogs in table definitions"); //$NON-NLS-1$
 116                         } else {
 117                                 log.addText(LogProxy.WARNING, "[METADATA] Database does not support catalogs in table definitions"); //$NON-NLS-1$
 118                         }
 119                         log.addText(LogProxy.WARNING, "[METADATA] Catalog Separator: " + meta.getCatalogSeparator()); //$NON-NLS-1$
 120                         log.addText(LogProxy.WARNING, "[METADATA] Catalog Term: " + meta.getCatalogTerm()); //$NON-NLS-1$
 121                         ResultSet set = meta.getCatalogs();
 122                         ArrayList catalogList = new ArrayList();
 123                         catalogList.add(null);
 124                         while (set.next()) {
 125                                 catalogList.add(set.getString(1));
 126                         }
 127                         set.close();
 128                         StringBuffer catalogOutput = new StringBuffer();
 129                         catalogOutput.append("[CATALOG LIST] ["); //$NON-NLS-1$
 130                         for (int i = 0; i < catalogList.size(); i++) {
 131                                 String name = (String) catalogList.get(i);
 132                                 catalogOutput.append(name + ", "); //$NON-NLS-1$
 133                         }
 134                         catalogOutput.append("]"); //$NON-NLS-1$
 135                         log.addText(LogProxy.WARNING, catalogOutput.toString());
 136
 137                         set = meta.getSchemas();
 138                         ArrayList schemaList = new ArrayList();
 139                         schemaList.add(""); //$NON-NLS-1$
 140                         while (set.next()) {
 141                                 schemaList.add(set.getString(1));
 142                         }
 143                         set.close();
 144                         StringBuffer schemaOutput = new StringBuffer();
 145                         schemaOutput.append("[SCHEMA LIST] ["); //$NON-NLS-1$
 146                         for (int i = 0; i < schemaList.size(); i++) {
 147                                 String name = (String) schemaList.get(i);
 148                                 schemaOutput.append(name + ", "); //$NON-NLS-1$
 149                         }
 150                         schemaOutput.append("]"); //$NON-NLS-1$
 151                         log.addText(LogProxy.WARNING, schemaOutput.toString());
 152
 153             List tableTypes = getTableTypes(meta);
 154
 155                         StringBuffer tableListOutput = new StringBuffer();
 156                         tableListOutput.append("[TABLE LIST] ["); //$NON-NLS-1$
 157                         for (int i = 0; i < tableTypes.size(); i++) {
 158                                 String name = (String) tableTypes.get(i);
 159                                 tableListOutput.append(name + ", "); //$NON-NLS-1$
 160                         }
 161                         tableListOutput.append("]"); //$NON-NLS-1$
 162                         log.addText(LogProxy.WARNING, tableListOutput.toString());
 163
 164
 165                 } catch (Exception e) {
 166             log.addText(LogProxy.ERROR, e);
 167                 }
 168         }
 169     private List getTableTypes(DatabaseMetaData meta) throws SQLException {
 170         ArrayList tableTypes = new ArrayList();
 171         ResultSet set = meta.getTableTypes();
 172         while (set.next()) {
 173             String type = set.getString(1);
 174             if (type != null) {
 175                         tableTypes.add(type.trim());
 176             }
 177         }
 178         set.close();
 179         return tableTypes;
 180     }
 181 //      /**
 182 //       * @param con
 183 //       * @param schema
 184 //       * @param type  of the element "TABLE", "VIEW", "SEQUENCE"
 185 //       * @return
 186 //       */
 187 //      public Vector listElements(Connection con, String schema, String type) {
 188 //              LogProxy log = LogProxy.getInstance();
 189 //              Vector retVal = new Vector(50,5);
 190 //              log.addText(LogProxy.QUERY, "Retrieving list [" + type + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 191 //              try {
 192 //                      DatabaseMetaData meta = con.getMetaData();
 193 //                      List types = getTableTypes(meta);
 194 //                      if (types.contains(type)) {
 195 //                              ResultSet set = meta.getTables(
 196 //                    null, null, "%", new String[] {type}); //$NON-NLS-1$
 197 //                              while (set.next()) {
 198 //                                      String tableSchema = set.getString("TABLE_SCHEM");
 199 //                    tableSchema = (tableSchema == null) ? "" : tableSchema.trim();
 200 //                                      String tableName = set.getString("TABLE_NAME").trim(); //$NON-NLS-1$
 201 //                    if (tableName.length() > 0)
 202 //                        retVal.addElement(((tableSchema.length() > 0) ? tableSchema + "." : "") + tableName);
 203 //                              }
 204 //                              set.close();
 205 //                      }
 206 //                      log.addText(LogProxy.RESULTS, "Success"); //$NON-NLS-1$
 207 //
 208 //              } catch (SQLException e) {
 209 //                      log.addText(LogProxy.ERROR, e);
 210 //              }
 211 //              return retVal;
 212 //      }
 213
 214         /**
 215          * Makes a connection to a JDBC driver based on the data from a bookmark
 216          * @param b The Bookmark with the data needed to make the connection
 217          * @return The Connection object if everything went OK
 218          */
 219         public Connection connect(Bookmark b) {
 220                 Connection con;
 221                 LogProxy log = LogProxy.getInstance();
 222                 log.addText(LogProxy.QUERY, "Connecting to: " + b.getName()); //$NON-NLS-1$
 223                 URL urls[] = new URL[1];
 224                 try {
 225                         String driverFile = b.getDriverFile();
 226                         URLClassLoader loader = (URLClassLoader) classLoaderCache.get(driverFile);
 227                         if (loader == null) {
 228                                 urls[0] = new File(driverFile).toURL();
 229                                 loader = new URLClassLoader(urls);
 230                                 classLoaderCache.put(driverFile, loader);
 231                                 System.out.println("Creating new classloader"); //$NON-NLS-1$
 232                         } else {
 233                                 System.out.println("Using classloader in cache"); //$NON-NLS-1$
 234                         }
 235                         Class driverClass = loader.loadClass(b.getDriver());
 236                         Driver driver = (Driver) driverClass.newInstance();
 237                         Properties props = new Properties();
 238                         props.put(USERNAME, b.getUsername());
 239                         props.put(PASSWORD, b.getPassword());
 240                         con = driver.connect(b.getConnect(), props);
 241                         if (con == null) {
 242                                 throw new Exception("Error: Driver returned a null connection: " + b.toString()); //$NON-NLS-1$
 243                         }
 244                         log.addText(LogProxy.RESULTS, "Connected to: " + b.getName()); //$NON-NLS-1$
 245                         System.out.println("Connected"); //$NON-NLS-1$
 246                         return con;
 247                 } catch (Exception e) {
 248                         //log.addText(
 249                         //      LogProxy.ERROR,
 250                         //      "Error Connecting to: " + b.getName() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
 251                 }
 252                 return null;
 253         }
 254         public SQLResults execute(Connection con, String s) {
 255                 return execute(con, s, -1, -1);
 256         }
 257         public SQLResults execute(Connection con, String s, int startRow, int endRow) {
 258                 return execute(con, s, -1, -1, Integer.MAX_VALUE);
 259         }
 260         public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) {
 261                 return execute(con, s, startRow, endRow, maxLength, ""); //$NON-NLS-1$
 262         }
 263         public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength, String encoding) {
 264                 SQLResults results = new SQLResults();
 265
 266                 System.out.println("Executing"); //$NON-NLS-1$
 267                 LogProxy log = LogProxy.getInstance();
 268                 log.addText(LogProxy.QUERY, "Executing Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 269                 boolean metadata = false;
 270                 if (s.startsWith("METADATA")) { //$NON-NLS-1$
 271                         metadata = true;
 272                 }
 273                 if (metadata) {
 274                         results.setQuery(s);
 275                         String table = s.substring(s.indexOf(':') + 1);
 276                         String query = "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
 277                         s = query;
 278                         log.addText(LogProxy.QUERY, "Metadata Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 279         } else {
 280                         results.setQuery(s);
 281                 }
 282                 try {
 283                         Statement stmt = con.createStatement();
 284                         boolean flag = stmt.execute(s);
 285                         results.setResultSet(flag);
 286                         if (!flag) {
 287                                 int updates = stmt.getUpdateCount();
 288                                 results.setUpdateCount(updates);
 289                                 log.addText(
 290                                         LogProxy.RESULTS,
 291                                         "Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$
 292
 293                         } else {
 294                                 if (metadata) {
 295                                         ResultSet set = stmt.getResultSet();
 296                                         ResultSetMetaData metaData = set.getMetaData();
 297                                         int columnCount = metaData.getColumnCount();
 298                                         Vector columnNames = new Vector();
 299                                         columnNames.addElement("ColumnName"); //$NON-NLS-1$
 300                                         columnNames.addElement("Type"); //$NON-NLS-1$
 301                                         columnNames.addElement("Size"); //$NON-NLS-1$
 302                                         columnNames.addElement("Nullable"); //$NON-NLS-1$
 303                                         columnNames.addElement("AutoIncrement"); //$NON-NLS-1$
 304                                         results.setColumnNames(columnNames);
 305                                         for (int i = 1; i <= columnCount; i++) {
 306                                                 Vector row = new Vector();
 307                                                 row.addElement(metaData.getColumnName(i));
 308                                                 row.addElement(metaData.getColumnTypeName(i));
 309                                                 int textSize = metaData.getColumnDisplaySize(i);
 310                                                 int precision = metaData.getPrecision(i);
 311                                                 int scale = metaData.getScale(i);
 312                                                 if (scale == 0 && precision == 0) {
 313                                                         row.addElement(Integer.toString(precision));
 314                                                 } else {
 315                                                         row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$
 316                                                 }
 317                                                 int nullable = metaData.isNullable(i);
 318                                                 if (nullable == ResultSetMetaData.columnNoNulls) {
 319                                                         row.addElement("Not Null"); //$NON-NLS-1$
 320                                                 } else if (nullable == ResultSetMetaData.columnNullable) {
 321                                                         row.addElement("Nullable"); //$NON-NLS-1$
 322                                                 } else if (nullable == ResultSetMetaData.columnNullableUnknown) {
 323                                                         row.addElement("Nullable"); //$NON-NLS-1$
 324                                                 } else {
 325                                                         row.addElement("<Error>"); //$NON-NLS-1$
 326                                                 }
 327                                                 row.addElement(Boolean.toString(metaData.isAutoIncrement(i)));
 328                                                 results.addRow(row);
 329                                         }
 330                                         results.setHasMore(false);
 331                                         set.close();
 332                                 } else {
 333                                         ResultSet set = stmt.getResultSet();
 334                                         ResultSetMetaData metaData = set.getMetaData();
 335                                         int columnCount = metaData.getColumnCount();
 336                                         Vector columnNames = new Vector();
 337                                         for (int i = 1; i <= columnCount; i++) {
 338                                                 columnNames.addElement(metaData.getColumnName(i));
 339                                         }
 340                                         results.setColumnNames(columnNames);
 341                                         Vector columnTypes = new Vector();
 342                                         for (int i = 1; i <= columnCount; i++) {
 343                                                 columnTypes.addElement(metaData.getColumnTypeName(i));
 344                                         }
 345                                         results.setColumnTypes(columnTypes);
 346                                         IntVector columnSizes = new IntVector();
 347                                         for (int i = 1; i <= columnCount; i++) {
 348                                                 columnSizes.addElement(metaData.getColumnDisplaySize(i));
 349                                         }
 350                                         results.setColumnSizes(columnSizes);
 351 //                                      int columnSizes[] = new int[columnCount];
 352 //                                      for (int i = 1; i <= columnCount; i++) {
 353 //                                              columnSizes[i - 1] = metaData.getColumnDisplaySize(i);
 354 //                                      }
 355                                         int rowCount = 1;
 356                                         boolean exitEarly = false;
 357                                         while (set.next()) {
 358                                                 boolean disable = startRow < 1 || endRow < 1;
 359                                                 boolean start = rowCount >= startRow;
 360                                                 boolean end = rowCount <= endRow;
 361                                                 if (disable || (start && end)) {
 362                                                         Vector row = new Vector();
 363                                                         for (int i = 1; i <= columnCount; i++) {
 364                                                                 String value;
 365                                                                 if (columnSizes.elementAt(i - 1) < STREAM && columnSizes.elementAt(i - 1) < maxLength) {
 366                                                                         if (encoding.equals("")) { //$NON-NLS-1$
 367                                                                                 value = set.getString(i);
 368                                                                         } else {
 369                                                                                 value = new String(set.getBytes(i), encoding);
 370                                                                         }
 371                                                                 } else {
 372                                                                         try {
 373                                                                                 if (encoding.equals("")) { //$NON-NLS-1$
 374                                                                                         Reader reader = set.getCharacterStream(i);
 375                                                                                         StringBuffer buffer = new StringBuffer();
 376                                                                                         if (reader != null) {
 377                                                                                                 int retVal = reader.read();
 378                                                                                                 int count = 0;
 379                                                                                                 while (retVal >= 0) {
 380                                                                                                         buffer.append((char) retVal);
 381                                                                                                         retVal = reader.read();
 382                                                                                                         count++;
 383                                                                                                         if (count > maxLength) {
 384                                                                                                                 buffer.append("...>>>"); //$NON-NLS-1$
 385                                                                                                                 break;
 386                                                                                                         }
 387                                                                                                 }
 388                                                                                                 reader.close();
 389                                                                                         }
 390                                                                                         value = buffer.toString();
 391                                                                                 } else {
 392                                                                                         InputStream binaryStream = set.getBinaryStream(i);
 393                                                                                         ByteArrayOutputStream baos = new ByteArrayOutputStream();
 394                                                                                         if (binaryStream != null) {
 395                                                                                                 int retVal = binaryStream.read();
 396                                                                                                 int count = 0;
 397                                                                                                 while (retVal >= 0) {
 398                                                                                                         baos.write(retVal);
 399                                                                                                         retVal = binaryStream.read();
 400                                                                                                         count++;
 401                                                                                                         if (count > maxLength) {
 402                                                                                                                 break;
 403                                                                                                         }
 404                                                                                                 }
 405                                                                                                 binaryStream.close();
 406                                                                                         }
 407                                                                                         value = new String(baos.toByteArray(), encoding);
 408                                                                                 }
 409                                                                         } catch (Throwable e) {
 410                                                                                 // hack for mysql which doesn't implement
 411                                                                                 // character streams
 412                                                                                 value = set.getString(i);
 413                                                                         }
 414                                                                 }
 415                                                                 if (set.wasNull()) {
 416                                                                         row.addElement("<NULL>"); //$NON-NLS-1$
 417                                                                 } else {
 418                                                                         row.addElement(value);
 419                                                                 }
 420                                                         }
 421                                                         results.addRow(row);
 422                                                 }
 423                                                 rowCount++;
 424                                                 if (!disable && (rowCount > endRow)) {
 425                                                         exitEarly = true;
 426                                                         break;
 427                                                 }
 428                                         }
 429                                         if (exitEarly) {
 430                                                 results.setHasMore(set.next());
 431                                         } else {
 432                                                 results.setMaxSize(rowCount);
 433                                                 results.setHasMore(false);
 434                                         }
 435                                         set.close();
 436                                 }
 437                                 log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
 438                         }
 439                         stmt.close();
 440                         System.out.println("Executed"); //$NON-NLS-1$
 441                         System.out.println();
 442                 } catch (Exception e) {
 443                         results.setIsError(true);
 444                         log.addText(LogProxy.ERROR, e); //$NON-NLS-1$
 445                 }
 446                 return results;
 447         }
 448
 449                 public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException {
 450                 ObjectMetaData metadata = new ObjectMetaData();
 451                 String table = node.getName();
 452                 metadata.setColumns(MetaDataJDBCInterface.getColumns(con, getSchemaName(table), getTableName(table)));
 453                 if (node instanceof TableNode) {
 454                         String schema = getSchemaName(table);
 455                         String tableName = getTableName(table);
 456                         metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName));
 457                         metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true));
 458                         metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName));
 459                 }
 460                 return metadata;
 461         }
 462
 463         public String getTableName(String table) {
 464                 StringTokenizer st = new StringTokenizer(table, "."); //$NON-NLS-1$
 465                 if (st.countTokens() == 2) {
 466                         st.nextToken();
 467                         return st.nextToken();
 468                 } else if (st.countTokens() == 1){
 469                         return st.nextToken();
 470                 } else
 471                         return null;
 472         }
 473         public String getSchemaName(String table) {
 474                 StringTokenizer st = new StringTokenizer(table, "."); //$NON-NLS-1$
 475                 if (st.countTokens() == 2) {
 476                         return st.nextToken();
 477                 } else
 478                         return null;
 479         }
 480
 481 }