fixed ID bug
[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.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 }