synchronized from quantum plugin
[phpeclipse.git] / 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 }