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