X-Git-Url: http://git.phpeclipse.com diff --git a/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java index 843d57f..1572da0 100644 --- a/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java +++ b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java @@ -1,33 +1,23 @@ package com.quantum.sql; -import java.io.ByteArrayOutputStream; -import java.io.File; -import java.io.InputStream; -import java.io.Reader; -import java.io.UnsupportedEncodingException; -import java.net.MalformedURLException; -import java.net.URL; -import java.net.URLClassLoader; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.Driver; import java.sql.ResultSet; -import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; -import java.util.Hashtable; import java.util.Properties; -import java.util.Vector; +import com.quantum.Messages; +import com.quantum.adapters.AdapterFactory; +import com.quantum.adapters.DatabaseAdapter; import com.quantum.model.Bookmark; import com.quantum.model.ConnectionException; import com.quantum.model.Entity; +import com.quantum.model.JDBCDriver; import com.quantum.model.PasswordFinder; -import com.quantum.sql.metadata.MetaDataJDBCInterface; -import com.quantum.sql.metadata.ObjectMetaData; +import com.quantum.util.sql.SQLInstructionBuilder; import com.quantum.view.LogProxy; -import com.quantum.view.bookmark.EntityNode; -import com.quantum.view.bookmark.TreeNode; /** @@ -35,15 +25,11 @@ import com.quantum.view.bookmark.TreeNode; * Use MultiSQLServer.getInstance() to get the object. */ public class MultiSQLServer implements ConnectionEstablisher { - private static final int STREAM = 1024 * 2; public static final String USERNAME = "user"; //$NON-NLS-1$ public static final String PASSWORD = "password"; //$NON-NLS-1$ private static MultiSQLServer instance = null; - private Hashtable classLoaderCache = new Hashtable(); - boolean running = true; - public MultiSQLServer() { - //start(); + private MultiSQLServer() { } public synchronized static MultiSQLServer getInstance() { if (instance == null) { @@ -52,21 +38,23 @@ public class MultiSQLServer implements ConnectionEstablisher { return instance; } - public void commit(Connection con) { + public void commit(Connection con) throws SQLException { LogProxy log = LogProxy.getInstance(); try { con.commit(); } catch (SQLException e) { log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$ + throw e; } } - public void rollback(Connection con) { + public void rollback(Connection con) throws SQLException { LogProxy log = LogProxy.getInstance(); try { con.rollback(); } catch (SQLException e) { log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$ + throw e; } } @@ -83,32 +71,12 @@ public class MultiSQLServer implements ConnectionEstablisher { } } - public void disconnect(Connection connection) throws ConnectionException { - try { - if (connection != null) { - connection.close(); - } - } catch (SQLException e) { - throw new ConnectionException(e); + public void disconnect(Connection connection) throws SQLException { + if (connection != null) { + connection.close(); } } - public Vector getSchemas(Connection con) { - ResultSet set; - Vector schemaList = new Vector(); - try { - DatabaseMetaData meta = con.getMetaData(); - set = meta.getSchemas(); - while (set.next()) { - schemaList.add(set.getString("TABLE_SCHEM")); //$NON-NLS-1$ - } - set.close(); - } catch (SQLException e) { - LogProxy log = LogProxy.getInstance(); - log.addText(LogProxy.ERROR, e); - } - return schemaList; - } /** * Makes a connection to a JDBC driver based on the data from a bookmark * @param bookmark - @@ -128,299 +96,146 @@ public class MultiSQLServer implements ConnectionEstablisher { bookmark.setPassword(password); } } - Connection con; + if (password != null) { - con = connect(bookmark, password); + Connection connection = connect(bookmark, password); + if (connection != null) { + // Set the autoCommit state of the bookmark to the default on new connections + bookmark.setAutoCommit(bookmark.getDefaultAutoCommit()); + // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec + setAutoCommit(connection, bookmark.isAutoCommit()); + } + return connection; } else { return null; } - // Set the autoCommit state of the bookmark to the default on new connections - bookmark.setAutoCommit(bookmark.getDefaultAutoCommit()); - // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec - setAutoCommit(con, bookmark.isAutoCommit()); - return con; } private Connection connect(Bookmark bookmark, String password) throws ConnectionException { LogProxy log = LogProxy.getInstance(); log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$ - URL urls[] = new URL[1]; try { - String driverFile = bookmark.getDriverFile(); - URLClassLoader loader = - (URLClassLoader) classLoaderCache.get(driverFile); - if (loader == null) { - urls[0] = new File(driverFile).toURL(); - loader = new URLClassLoader(urls); - classLoaderCache.put(driverFile, loader); - System.out.println("Creating new classloader"); //$NON-NLS-1$ + JDBCDriver jdbcDriver = bookmark.getJDBCDriver(); + Driver driver = jdbcDriver.getDriver(); + if (driver != null) { + Properties props = new Properties(); + props.put(USERNAME, bookmark.getUsername()); + props.put(PASSWORD, password); + // TODO: This kind of things should really be made general-purpose + if (jdbcDriver.getType().equals(AdapterFactory.ORACLE)){ + // remarksReporting will make the JDBC driver return the remarks for the tables and + // the columns. It'll make getting the tables and columns much slower, so it should + // really be made into an option + // TODO: Make remark reporting into an option + props.put("remarksReporting", "true"); + // includeSynonyms will make the JDBC driver return the proper columns when querying + // about a synonym. If not given, synonyms will appear with no columns, exports of data + // containing synonyms will break, etc.n So it's needed from the moment you add the synonyms + // with the getSynonymsList() in the Database.getEntities() function. That could also be + // made into an option, but more logically when more databases are addedd + // TODO: Make including synonyms into an option + props.put("includeSynonyms", "true"); + } + Connection connection = + driver.connect(bookmark.getConnect(), props); + if (connection == null) { + throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$ + } + + DatabaseMetaData metaData = connection.getMetaData(); + jdbcDriver.setName(metaData.getDriverName()); + jdbcDriver.setVersion(metaData.getDriverVersion()); + log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$ + System.out.println("Connected"); //$NON-NLS-1$ + return connection; } else { - System.out.println("Using classloader in cache"); //$NON-NLS-1$ + throw new ConnectionException(Messages.getString( + ConnectionException.class, "couldNotInstantiateDriver", + new Object[] { jdbcDriver.getClassName(), bookmark.getName() })); } - Class driverClass = loader.loadClass(bookmark.getDriver()); - Driver driver = (Driver) driverClass.newInstance(); - Properties props = new Properties(); - props.put(USERNAME, bookmark.getUsername()); - props.put(PASSWORD, password); - Connection connection = - driver.connect(bookmark.getConnect(), props); - if (connection == null) { - throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$ - } - log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$ - System.out.println("Connected"); //$NON-NLS-1$ - return connection; } catch (SQLException e) { throw new ConnectionException(e); - } catch (MalformedURLException e) { - throw new ConnectionException(e); - } catch (ClassNotFoundException e) { - throw new ConnectionException(e); - } catch (InstantiationException e) { - throw new ConnectionException(e); - } catch (IllegalAccessException e) { - throw new ConnectionException(e); } } - public SQLResults execute(Connection con, String s) throws SQLException { - return execute(con, s, -1, -1); - } - public SQLResults execute(Connection con, String s, int startRow, int endRow) throws SQLException { - return execute(con, s, -1, -1, Integer.MAX_VALUE); + public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s) + throws SQLException { + return execute(bookmark, con, entity, s, 200); } - public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) throws SQLException { - return execute(con, s, startRow, endRow, maxLength, ""); //$NON-NLS-1$ + public SQLResults execute(Bookmark bookmark, Connection con, String s) + throws SQLException { + return execute(bookmark, con, null, s, 200); } - public SQLResults execute( - Connection con, - String s, - int startRow, - int endRow, - int maxLength, - String encoding) - throws SQLException { - - SQLResults results = new SQLResults(); - - System.out.println("Executing"); //$NON-NLS-1$ - LogProxy log = LogProxy.getInstance(); - log.addText(LogProxy.QUERY, "Executing Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$ - boolean metadata = false; - if (s.startsWith("METADATA")) { //$NON-NLS-1$ - metadata = true; - } - if (metadata) { - results.setQuery(s); - String table = s.substring(s.indexOf(':') + 1); - String query = "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$ - s = query; - log.addText(LogProxy.QUERY, "Metadata Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$ - } else { - results.setQuery(s); - } - - Statement stmt = con.createStatement(); - boolean flag = stmt.execute(s); - results.setResultSet(flag); - if (metadata) { - genMetadataResultSet(results, stmt); - return results; - } - if (!flag) { - int updates = stmt.getUpdateCount(); - results.setUpdateCount(updates); - log.addText(LogProxy.RESULTS, "Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$ - - } else { - ResultSet set = stmt.getResultSet(); - ResultSetMetaData metaData = set.getMetaData(); - int columnCount = metaData.getColumnCount(); - Vector columnNames = new Vector(); - for (int i = 1; i <= columnCount; i++) { - columnNames.addElement(metaData.getColumnName(i)); - } - results.setColumnNames(columnNames); - Vector columnTypes = new Vector(); - for (int i = 1; i <= columnCount; i++) { - columnTypes.addElement(metaData.getColumnTypeName(i)); - } - results.setColumnTypes(columnTypes); - int columnSizes[] = new int[columnCount]; - for (int i = 1; i <= columnCount; i++) { - columnSizes[i - 1] = metaData.getColumnDisplaySize(i); - } - int rowCount = 1; - boolean exitEarly = false; - while (set.next()) { - boolean disable = startRow < 1 || endRow < 1; - boolean start = rowCount >= startRow; - boolean end = rowCount <= endRow; - if (disable || (start && end)) { - Vector row = new Vector(); - for (int i = 1; i <= columnCount; i++) { - String value; - if (columnSizes[i - 1] < STREAM - && columnSizes[i - 1] < maxLength) { - if (encoding.equals("")) { //$NON-NLS-1$ - value = set.getString(i); - } else { - try { - value = - new String(set.getBytes(i), encoding); - } catch (UnsupportedEncodingException e) { - log.addText(LogProxy.ERROR, "Error Unsupported encoding " + encoding.toString() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$ - value = new String(set.getBytes(i)); - } - } - } else { - try { - if (encoding.equals("")) { //$NON-NLS-1$ - Reader reader = set.getCharacterStream(i); - StringBuffer buffer = new StringBuffer(); - if (reader != null) { - int retVal = reader.read(); - int count = 0; - while (retVal >= 0) { - buffer.append((char) retVal); - retVal = reader.read(); - count++; - if (count > maxLength) { - buffer.append("...>>>"); //$NON-NLS-1$ - break; - } - } - reader.close(); - } - value = buffer.toString(); - } else { - InputStream binaryStream = - set.getBinaryStream(i); - ByteArrayOutputStream baos = - new ByteArrayOutputStream(); - if (binaryStream != null) { - int retVal = binaryStream.read(); - int count = 0; - while (retVal >= 0) { - baos.write(retVal); - retVal = binaryStream.read(); - count++; - if (count > maxLength) { - break; - } - } - binaryStream.close(); - } - value = - new String( - baos.toByteArray(), - encoding); - } - } catch (Throwable e) { - // hack for mysql which doesn't implement - // character streams - value = set.getString(i); - } - } - if (set.wasNull()) { - row.addElement(""); //$NON-NLS-1$ - } else { - row.addElement(value); - } - } - results.addRow(row); - } - rowCount++; - if (!disable && (rowCount > endRow)) { - exitEarly = true; - break; + public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException { + String query = SQLInstructionBuilder.buildSelectAllColumnsNoRows(entity); + SQLResultSetResults results = null; + if (connection != null) { + Statement statement = connection.createStatement(); + try { + ResultSet set = statement.executeQuery(query); + try { + results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity); + } finally { + set.close(); } + } finally { + statement.close(); } - if (exitEarly) { - results.setHasMore(set.next()); - } else { - results.setMaxSize(rowCount); - results.setHasMore(false); - } - set.close(); } - log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$ - stmt.close(); - System.out.println("Executed"); //$NON-NLS-1$ - System.out.println(); return results; } - private void genMetadataResultSet(SQLResults results, Statement stmt) - throws SQLException { - ResultSet set = stmt.getResultSet(); - ResultSetMetaData metaData = set.getMetaData(); - int columnCount = metaData.getColumnCount(); - Vector columnNames = new Vector(); - columnNames.addElement("ColumnName"); //$NON-NLS-1$ - columnNames.addElement("Type"); //$NON-NLS-1$ - columnNames.addElement("Size"); //$NON-NLS-1$ - columnNames.addElement("Nullable"); //$NON-NLS-1$ - columnNames.addElement("AutoIncrement"); //$NON-NLS-1$ - results.setColumnNames(columnNames); - for (int i = 1; i <= columnCount; i++) { - Vector row = new Vector(); - row.addElement(metaData.getColumnName(i)); - row.addElement(metaData.getColumnTypeName(i)); - int textSize = metaData.getColumnDisplaySize(i); - int precision = metaData.getPrecision(i); - int scale = metaData.getScale(i); - if (scale == 0 && precision == 0) { - row.addElement(Integer.toString(precision)); + + public SQLResults execute(Bookmark bookmark, Connection con, String sql, + int numberOfRowsPerPage) throws SQLException { + return execute(bookmark, con, null, sql, numberOfRowsPerPage); + } + + + public SQLResults execute( + Bookmark bookmark, + Connection con, + Entity entity, + String sql, + int numberOfRowsPerPage) + throws SQLException { + + long startTime = System.currentTimeMillis(); + System.out.println("Executing"); //$NON-NLS-1$ + LogProxy log = LogProxy.getInstance(); + log.addText(LogProxy.QUERY, "SQL (" + bookmark.getName() + ") [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$ + Statement statement = con.createStatement(); + try { + SQLResults results; + if (statement.execute(sql)) { + ResultSet set = statement.getResultSet(); + try { + results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage); + } finally { + set.close(); + } } else { - row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$ + int updates = statement.getUpdateCount(); + results = new SQLUpdateResults(updates); } - int nullable = metaData.isNullable(i); - if (nullable == ResultSetMetaData.columnNoNulls) { - row.addElement("Not Null"); //$NON-NLS-1$ - } else if (nullable == ResultSetMetaData.columnNullable) { - row.addElement("Nullable"); //$NON-NLS-1$ - } else if ( - nullable == ResultSetMetaData.columnNullableUnknown) { - row.addElement("Nullable"); //$NON-NLS-1$ - } else { - row.addElement(""); //$NON-NLS-1$ + log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$ + if (results != null) { + results.setTime(System.currentTimeMillis() - startTime); } - row.addElement( - (metaData.isAutoIncrement(i) - ? Boolean.TRUE - : Boolean.FALSE) - .toString()); - results.addRow(row); - } - results.setHasMore(false); - set.close(); - } - - /** - * Returns an ObjectMetadata object got from the connection 'con' using the name and schema of the node. - * @param con - * @param node - * @return - * @throws SQLException - */ - public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException { - ObjectMetaData metadata = new ObjectMetaData(); - if (!(node instanceof Entity)) return metadata; - - String schema = ((Entity)node).getSchema(); - String tableName = node.getName(); - - if (schema.length() == 0) schema = null; - metadata.setColumns(MetaDataJDBCInterface.getColumns(con, schema, tableName)); - if (node instanceof EntityNode && ((EntityNode) node).isTable()) { - metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName)); - metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true)); - metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName)); - metadata.setBestRowId(MetaDataJDBCInterface.getBestRowId(con, schema, tableName)); + return results; + } finally { + statement.close(); } - return metadata; } + public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException { + SQLResultSetResults results = (SQLResultSetResults) execute( + bookmark, connection, adapter.getCountQuery(tableName)); + if (results.getRowCount() > 0 && results.getColumnCount() > 0) { + return Integer.parseInt(results.getElement(1, 1).toString()); + } else { + return -1; + } + } } \ No newline at end of file