archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java

   1 package com.quantum.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.io.UnsupportedEncodingException;
   8 import java.net.MalformedURLException;
   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.Hashtable;
  19 import java.util.Properties;
  20 import java.util.Vector;
  21
  22 import com.quantum.model.Bookmark;
  23 import com.quantum.model.ConnectionException;
  24 import com.quantum.model.Entity;
  25 import com.quantum.model.PasswordFinder;
  26 import com.quantum.sql.metadata.MetaDataJDBCInterface;
  27 import com.quantum.sql.metadata.ObjectMetaData;
  28 import com.quantum.view.LogProxy;
  29 import com.quantum.view.bookmark.EntityNode;
  30 import com.quantum.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(Connection connection) throws ConnectionException {
  87         try {
  88             if (connection != null) {
  89                 connection.close();
  90             }
  91         } catch (SQLException e) {
  92             throw new ConnectionException(e);
  93         }
  94     }
  95
  96     public Vector getSchemas(Connection con) {
  97         ResultSet set;
  98         Vector schemaList = new Vector();
  99         try {
 100             DatabaseMetaData meta = con.getMetaData();
 101             set = meta.getSchemas();
 102             while (set.next()) {
 103                 schemaList.add(set.getString("TABLE_SCHEM")); //$NON-NLS-1$
 104             }
 105             set.close();
 106         } catch (SQLException e) {
 107             LogProxy log = LogProxy.getInstance();
 108             log.addText(LogProxy.ERROR, e);
 109         }
 110         return schemaList;
 111     }
 112     /**
 113      * Makes a connection to a JDBC driver based on the data from a bookmark
 114      * @param bookmark -
 115      *     The Bookmark with the data needed to make the connection
 116      * @param passwordFinder -
 117      *     A utility class that can be invoked if the bookmark does not
 118      *     include a password
 119      * @return The Connection object if everything went OK
 120      */
 121     public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
 122         throws ConnectionException {
 123
 124         String password = bookmark.getPassword();
 125         if (bookmark.getPromptForPassword()) {
 126             password = passwordFinder.getPassword();
 127             if (passwordFinder.isPasswordMeantToBeSaved()) {
 128                 bookmark.setPassword(password);
 129             }
 130         }
 131                 Connection con;
 132         if (password != null) {
 133             con = connect(bookmark, password);
 134         } else {
 135             return null;
 136         }
 137         // Set the autoCommit state of the bookmark to the default on new connections
 138                 bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
 139                 // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
 140         setAutoCommit(con, bookmark.isAutoCommit());
 141         return con;
 142
 143     }
 144     private Connection connect(Bookmark bookmark, String password)
 145         throws ConnectionException {
 146         LogProxy log = LogProxy.getInstance();
 147         log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
 148         URL urls[] = new URL[1];
 149         try {
 150             String driverFile = bookmark.getDriverFile();
 151             URLClassLoader loader =
 152                 (URLClassLoader) classLoaderCache.get(driverFile);
 153             if (loader == null) {
 154                 urls[0] = new File(driverFile).toURL();
 155                 loader = new URLClassLoader(urls);
 156                 classLoaderCache.put(driverFile, loader);
 157                 System.out.println("Creating new classloader"); //$NON-NLS-1$
 158             } else {
 159                 System.out.println("Using classloader in cache"); //$NON-NLS-1$
 160             }
 161             Class driverClass = loader.loadClass(bookmark.getDriver());
 162             Driver driver = (Driver) driverClass.newInstance();
 163             Properties props = new Properties();
 164             props.put(USERNAME, bookmark.getUsername());
 165             props.put(PASSWORD, password);
 166             Connection connection =
 167                 driver.connect(bookmark.getConnect(), props);
 168             if (connection == null) {
 169                 throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
 170             }
 171             log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
 172             System.out.println("Connected"); //$NON-NLS-1$
 173             return connection;
 174         } catch (SQLException e) {
 175             throw new ConnectionException(e);
 176         } catch (MalformedURLException e) {
 177             throw new ConnectionException(e);
 178         } catch (ClassNotFoundException e) {
 179             throw new ConnectionException(e);
 180         } catch (InstantiationException e) {
 181             throw new ConnectionException(e);
 182         } catch (IllegalAccessException e) {
 183             throw new ConnectionException(e);
 184         }
 185     }
 186         public SQLResults execute(Connection con, String s) throws SQLException {
 187                 return execute(con, s, -1, -1);
 188         }
 189         public SQLResults execute(Connection con, String s, int startRow, int endRow) throws SQLException {
 190                 return execute(con, s, -1, -1, Integer.MAX_VALUE);
 191         }
 192
 193         public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) throws SQLException {
 194                 return execute(con, s, startRow, endRow, maxLength, ""); //$NON-NLS-1$
 195         }
 196
 197         public SQLResults execute(
 198                 Connection con,
 199                 String s,
 200                 int startRow,
 201                 int endRow,
 202                 int maxLength,
 203                 String encoding)
 204                 throws SQLException {
 205
 206                 SQLResults results = new SQLResults();
 207
 208                 System.out.println("Executing"); //$NON-NLS-1$
 209                 LogProxy log = LogProxy.getInstance();
 210                 log.addText(LogProxy.QUERY, "Executing Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 211                 boolean metadata = false;
 212                 if (s.startsWith("METADATA")) { //$NON-NLS-1$
 213                         metadata = true;
 214                 }
 215                 if (metadata) {
 216                         results.setQuery(s);
 217                         String table = s.substring(s.indexOf(':') + 1);
 218                         String query = "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
 219                         s = query;
 220                         log.addText(LogProxy.QUERY, "Metadata Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 221                 } else {
 222                         results.setQuery(s);
 223                 }
 224
 225                 Statement stmt = con.createStatement();
 226                 boolean flag = stmt.execute(s);
 227                 results.setResultSet(flag);
 228                 if (metadata) {
 229                         genMetadataResultSet(results, stmt);
 230                         return results;
 231                 }
 232                 if (!flag) {
 233                         int updates = stmt.getUpdateCount();
 234                         results.setUpdateCount(updates);
 235                         log.addText(LogProxy.RESULTS, "Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$
 236
 237                 } else {
 238                         ResultSet set = stmt.getResultSet();
 239                         ResultSetMetaData metaData = set.getMetaData();
 240                         int columnCount = metaData.getColumnCount();
 241                         Vector columnNames = new Vector();
 242                         for (int i = 1; i <= columnCount; i++) {
 243                                 columnNames.addElement(metaData.getColumnName(i));
 244                         }
 245                         results.setColumnNames(columnNames);
 246                         Vector columnTypes = new Vector();
 247                         for (int i = 1; i <= columnCount; i++) {
 248                                 columnTypes.addElement(metaData.getColumnTypeName(i));
 249                         }
 250                         results.setColumnTypes(columnTypes);
 251                         int columnSizes[] = new int[columnCount];
 252                         for (int i = 1; i <= columnCount; i++) {
 253                                 columnSizes[i - 1] = metaData.getColumnDisplaySize(i);
 254                         }
 255                         int rowCount = 1;
 256                         boolean exitEarly = false;
 257                         while (set.next()) {
 258                                 boolean disable = startRow < 1 || endRow < 1;
 259                                 boolean start = rowCount >= startRow;
 260                                 boolean end = rowCount <= endRow;
 261                                 if (disable || (start && end)) {
 262                                         Vector row = new Vector();
 263                                         for (int i = 1; i <= columnCount; i++) {
 264                                                 String value;
 265                                                 if (columnSizes[i - 1] < STREAM
 266                                                         && columnSizes[i - 1] < maxLength) {
 267                                                         if (encoding.equals("")) { //$NON-NLS-1$
 268                                                                 value = set.getString(i);
 269                                                         } else {
 270                                                                 try {
 271                                                                         value =
 272                                                                                 new String(set.getBytes(i), encoding);
 273                                                                 } catch (UnsupportedEncodingException e) {
 274                                                                         log.addText(LogProxy.ERROR, "Error Unsupported encoding " + encoding.toString() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
 275                                                                         value = new String(set.getBytes(i));
 276                                                                 }
 277                                                         }
 278                                                 } else {
 279                                                         try {
 280                                                                 if (encoding.equals("")) { //$NON-NLS-1$
 281                                                                         Reader reader = set.getCharacterStream(i);
 282                                                                         StringBuffer buffer = new StringBuffer();
 283                                                                         if (reader != null) {
 284                                                                                 int retVal = reader.read();
 285                                                                                 int count = 0;
 286                                                                                 while (retVal >= 0) {
 287                                                                                         buffer.append((char) retVal);
 288                                                                                         retVal = reader.read();
 289                                                                                         count++;
 290                                                                                         if (count > maxLength) {
 291                                                                                                 buffer.append("...>>>"); //$NON-NLS-1$
 292                                                                                                 break;
 293                                                                                         }
 294                                                                                 }
 295                                                                                 reader.close();
 296                                                                         }
 297                                                                         value = buffer.toString();
 298                                                                 } else {
 299                                                                         InputStream binaryStream =
 300                                                                                 set.getBinaryStream(i);
 301                                                                         ByteArrayOutputStream baos =
 302                                                                                 new ByteArrayOutputStream();
 303                                                                         if (binaryStream != null) {
 304                                                                                 int retVal = binaryStream.read();
 305                                                                                 int count = 0;
 306                                                                                 while (retVal >= 0) {
 307                                                                                         baos.write(retVal);
 308                                                                                         retVal = binaryStream.read();
 309                                                                                         count++;
 310                                                                                         if (count > maxLength) {
 311                                                                                                 break;
 312                                                                                         }
 313                                                                                 }
 314                                                                                 binaryStream.close();
 315                                                                         }
 316                                                                         value =
 317                                                                                 new String(
 318                                                                                         baos.toByteArray(),
 319                                                                                         encoding);
 320                                                                 }
 321                                                         } catch (Throwable e) {
 322                                                                 // hack for mysql which doesn't implement
 323                                                                 // character streams
 324                                                                 value = set.getString(i);
 325                                                         }
 326                                                 }
 327                                                 if (set.wasNull()) {
 328                                                         row.addElement("<NULL>"); //$NON-NLS-1$
 329                                                 } else {
 330                                                         row.addElement(value);
 331                                                 }
 332                                         }
 333                                         results.addRow(row);
 334                                 }
 335                                 rowCount++;
 336                                 if (!disable && (rowCount > endRow)) {
 337                                         exitEarly = true;
 338                                         break;
 339                                 }
 340                         }
 341                         if (exitEarly) {
 342                                 results.setHasMore(set.next());
 343                         } else {
 344                                 results.setMaxSize(rowCount);
 345                                 results.setHasMore(false);
 346                         }
 347                         set.close();
 348                 }
 349                 log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
 350                 stmt.close();
 351                 System.out.println("Executed"); //$NON-NLS-1$
 352                 System.out.println();
 353                 return results;
 354         }
 355         private void genMetadataResultSet(SQLResults results, Statement stmt)
 356                 throws SQLException {
 357                 ResultSet set = stmt.getResultSet();
 358                 ResultSetMetaData metaData = set.getMetaData();
 359                 int columnCount = metaData.getColumnCount();
 360                 Vector columnNames = new Vector();
 361                 columnNames.addElement("ColumnName"); //$NON-NLS-1$
 362                 columnNames.addElement("Type"); //$NON-NLS-1$
 363                 columnNames.addElement("Size"); //$NON-NLS-1$
 364                 columnNames.addElement("Nullable"); //$NON-NLS-1$
 365                 columnNames.addElement("AutoIncrement"); //$NON-NLS-1$
 366                 results.setColumnNames(columnNames);
 367                 for (int i = 1; i <= columnCount; i++) {
 368                         Vector row = new Vector();
 369                         row.addElement(metaData.getColumnName(i));
 370                         row.addElement(metaData.getColumnTypeName(i));
 371                         int textSize = metaData.getColumnDisplaySize(i);
 372                         int precision = metaData.getPrecision(i);
 373                         int scale = metaData.getScale(i);
 374                         if (scale == 0 && precision == 0) {
 375                                 row.addElement(Integer.toString(precision));
 376                         } else {
 377                                 row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$
 378                         }
 379                         int nullable = metaData.isNullable(i);
 380                         if (nullable == ResultSetMetaData.columnNoNulls) {
 381                                 row.addElement("Not Null"); //$NON-NLS-1$
 382                         } else if (nullable == ResultSetMetaData.columnNullable) {
 383                                 row.addElement("Nullable"); //$NON-NLS-1$
 384                         } else if (
 385                                 nullable == ResultSetMetaData.columnNullableUnknown) {
 386                                 row.addElement("Nullable"); //$NON-NLS-1$
 387                         } else {
 388                                 row.addElement("<Error>"); //$NON-NLS-1$
 389                         }
 390                         row.addElement(
 391                                 (metaData.isAutoIncrement(i)
 392                                         ? Boolean.TRUE
 393                                         : Boolean.FALSE)
 394                                         .toString());
 395                         results.addRow(row);
 396                 }
 397                 results.setHasMore(false);
 398                 set.close();
 399         }
 400
 401         /**
 402          * Returns an ObjectMetadata object got from the connection 'con' using the name and schema of the node.
 403          * @param con
 404          * @param node
 405          * @return
 406          * @throws SQLException
 407          */
 408         public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException {
 409                 ObjectMetaData metadata = new ObjectMetaData();
 410                 if (!(node instanceof Entity)) return metadata;
 411
 412                 String schema = ((Entity)node).getSchema();
 413                 String tableName = node.getName();
 414
 415                 if (schema.length() == 0) schema = null;
 416                 metadata.setColumns(MetaDataJDBCInterface.getColumns(con, schema, tableName));
 417                 if (node instanceof EntityNode && ((EntityNode) node).isTable()) {
 418                         metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName));
 419                         metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true));
 420                         metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName));
 421                         metadata.setBestRowId(MetaDataJDBCInterface.getBestRowId(con, schema, tableName));
 422                 }
 423                 return metadata;
 424         }
 425
 426 }