Cleaning test messages (re #379)
[phpeclipse.git] / archive / net.sourceforge.phpeclipse.quantum.sql / src / com / quantum / sql / MultiSQLServer.java
1 package com.quantum.sql;
2
3 import java.sql.Connection;
4 import java.sql.DatabaseMetaData;
5 import java.sql.Driver;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.Properties;
10
11 import com.quantum.Messages;
12 import com.quantum.adapters.AdapterFactory;
13 import com.quantum.adapters.DatabaseAdapter;
14 import com.quantum.model.Bookmark;
15 import com.quantum.model.ConnectionException;
16 import com.quantum.model.Entity;
17 import com.quantum.model.JDBCDriver;
18 import com.quantum.model.PasswordFinder;
19 import com.quantum.util.sql.SQLInstructionBuilder;
20 import com.quantum.view.LogProxy;
21
22
23 /**
24  * MultiSQLServer is a Singleton, used  as a interface with the sql drivers.
25  * Use MultiSQLServer.getInstance() to get the object.
26  */
27 public class MultiSQLServer implements ConnectionEstablisher {
28     public static final String USERNAME = "user"; //$NON-NLS-1$
29     public static final String PASSWORD = "password"; //$NON-NLS-1$
30     private static MultiSQLServer instance = null;
31
32     private MultiSQLServer() {
33     }
34     public synchronized static MultiSQLServer getInstance() {
35         if (instance == null) {
36             instance = new MultiSQLServer();
37         }
38         return instance;
39     }
40
41     public void commit(Connection con) throws SQLException {
42         LogProxy log = LogProxy.getInstance();
43         try {
44             con.commit();
45         } catch (SQLException e) {
46             log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
47             throw e;
48         }
49     }
50
51     public void rollback(Connection con) throws SQLException {
52         LogProxy log = LogProxy.getInstance();
53         try {
54             con.rollback();
55         } catch (SQLException e) {
56             log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
57             throw e;
58         }
59     }
60
61     public void setAutoCommit(Connection con, boolean enabled) {
62         LogProxy log = LogProxy.getInstance();
63         try {
64             if (con != null) {
65                 con.setAutoCommit(enabled);
66             } else {
67                 log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$
68             }
69         } catch (SQLException e) {
70             log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$
71         }
72     }
73
74     public void disconnect(Connection connection) throws SQLException {
75         if (connection != null) {
76             connection.close();
77         }
78     }
79
80     /**
81      * Makes a connection to a JDBC driver based on the data from a bookmark
82      * @param bookmark - 
83      *     The Bookmark with the data needed to make the connection
84      * @param passwordFinder - 
85      *     A utility class that can be invoked if the bookmark does not 
86      *     include a password
87      * @return The Connection object if everything went OK
88      */
89     public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
90         throws ConnectionException {
91
92         String password = bookmark.getPassword();
93         if (bookmark.getPromptForPassword()) {
94             password = passwordFinder.getPassword();
95             if (passwordFinder.isPasswordMeantToBeSaved()) {
96                 bookmark.setPassword(password);
97             }
98         }
99         
100         if (password != null) {
101             Connection connection = connect(bookmark, password);
102             if (connection != null) {
103                         // Set the autoCommit state of the bookmark to the default on new connections
104                                 bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
105                                 // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
106                         setAutoCommit(connection, bookmark.isAutoCommit());
107             }
108                 return connection;
109         } else {
110             return null;
111         }
112         
113     }
114     private Connection connect(Bookmark bookmark, String password)
115         throws ConnectionException {
116         LogProxy log = LogProxy.getInstance();
117         log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
118         try {
119                 JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
120                 Driver driver = jdbcDriver.getDriver();
121             if (driver != null) {
122                     Properties props = new Properties();
123                     props.put(USERNAME, bookmark.getUsername());
124                     props.put(PASSWORD, password);
125                     // TODO: This kind of things should really be made general-purpose
126                     if (jdbcDriver.getType().equals(AdapterFactory.ORACLE)){ 
127                         // remarksReporting will make the JDBC driver return the remarks for the tables and
128                         // the columns. It'll make getting the tables and columns much slower, so it should
129                         // really be made into an option
130                         // TODO: Make remark reporting into an option
131                         props.put("remarksReporting", "true");
132                         // includeSynonyms will make the JDBC driver return the proper columns when querying
133                         // about a synonym. If not given, synonyms will appear with no columns, exports of data
134                         // containing synonyms will break, etc.n So it's needed from the moment you add the synonyms
135                         // with the getSynonymsList() in the Database.getEntities() function. That could also be
136                         // made into an option, but more logically when more databases are addedd
137                         // TODO: Make including synonyms into an option
138                         props.put("includeSynonyms", "true");
139                     }
140                     Connection connection =
141                         driver.connect(bookmark.getConnect(), props);
142                     if (connection == null) {
143                         throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
144                     }
145                     
146                     DatabaseMetaData metaData = connection.getMetaData();
147                     jdbcDriver.setName(metaData.getDriverName());
148                     jdbcDriver.setVersion(metaData.getDriverVersion());
149                     log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
150                     System.out.println("Connected"); //$NON-NLS-1$
151                     return connection;
152             } else {
153                 throw new ConnectionException(Messages.getString(
154                                 ConnectionException.class, "couldNotInstantiateDriver", 
155                                                 new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
156             }
157         } catch (SQLException e) {
158             throw new ConnectionException(e);
159         }
160     }
161         public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
162                         throws SQLException {
163                 return execute(bookmark, con, entity, s, 200);
164         }
165
166         public SQLResults execute(Bookmark bookmark, Connection con, String s)
167                         throws SQLException {
168                 return execute(bookmark, con, null, s, 200);
169         }
170         
171         public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
172                 String query = SQLInstructionBuilder.buildSelectAllColumnsNoRows(entity);
173                 SQLResultSetResults results = null;
174                 if (connection != null) {
175                         Statement statement = connection.createStatement();
176                         try {
177                                 ResultSet set = statement.executeQuery(query);
178                                 try {
179                                         results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
180                                 } finally {
181                                         set.close();
182                                 }
183                         } finally {
184                                 statement.close();
185                         }
186                 }
187                 return results;
188         }
189         
190         public SQLResults execute(Bookmark bookmark, Connection con, String sql,
191                         int numberOfRowsPerPage) throws SQLException {
192                 return execute(bookmark, con, null, sql, numberOfRowsPerPage);
193         }
194
195         
196         public SQLResults execute(
197                         Bookmark bookmark, 
198                         Connection con,
199                         Entity entity, 
200                         String sql,
201                         int numberOfRowsPerPage)
202                         throws SQLException {
203
204                 long startTime = System.currentTimeMillis();
205                 System.out.println("Executing"); //$NON-NLS-1$
206                 LogProxy log = LogProxy.getInstance();
207                 log.addText(LogProxy.QUERY, "SQL (" + bookmark.getName() + ") [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
208                 Statement statement = con.createStatement();
209                 try {
210                         SQLResults results;
211                         if (statement.execute(sql)) {
212                                 ResultSet set = statement.getResultSet();
213                                 try {
214                                         results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
215                                 } finally {
216                                         set.close();
217                                 }
218                         } else {
219                                 int updates = statement.getUpdateCount();
220                                 results = new SQLUpdateResults(updates);
221                         }
222                         log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
223                         if (results != null) {
224                                 results.setTime(System.currentTimeMillis() - startTime);
225                         }
226                         return results;
227                 } finally {
228                         statement.close();
229                 }
230         }
231         
232         public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
233             SQLResultSetResults results = (SQLResultSetResults) execute( 
234                     bookmark, connection, adapter.getCountQuery(tableName));
235             if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
236                 return Integer.parseInt(results.getElement(1, 1).toString());
237             } else {
238                 return -1;
239             }
240         }
241 }