ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/owl/trunk/tools/ClusterConnection.java
Revision: 78
Committed: Wed Apr 12 08:52:25 2006 UTC (18 years, 5 months ago) by duarte
File size: 19581 byte(s)
Log Message: 
Got rid of method createNewKeyMasterTbl
Didn't belong here but in DataDistribution (at least for now)
Line User Rev File contents
1 duarte 16 package tools;
2 duarte 15 import java.sql.*;
3 duarte 67 import java.util.ArrayList;
4     import java.util.HashMap;
5 duarte 15
6     /**
7     * ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs
8     * @author Jose Duarte
9     */
10    
11     public class ClusterConnection {
12    
13 duarte 34 private final String URL= "jdbc:mysql://";
14     private String MASTERDB="key_master";
15     private final String MASTERHOST="white";
16 duarte 15 private Connection nCon;
17     private Connection mCon;
18     public String keyTable;
19     public String key;
20     public String host;
21     public String db;
22     private String user;
23     private String password;
24 duarte 34
25     /**
26     * Create a ClusterConnection passing a key.
27 duarte 35 * @param db the database name
28 duarte 72 * @param key the key name: e.g. asu_id
29 duarte 35 * @param user the user name for connection to both master and nodes
30     * @param password the password for connection to both master and nodes
31 duarte 34 */
32 duarte 15 public ClusterConnection (String db,String key, String user,String password) {
33 duarte 72 loadMySQLDriver();
34     setDb(db);
35     setUser(user);
36     setPassword(password);
37     try {
38     // For nCon we create a connection to the master too.
39     // This is just a place holder because the actual node connection is not created until we create the statement
40     // If we don't do this then when closing the two connections an exception might occurr because we try to close a non existing object
41     this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
42     this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
43     }
44     catch(SQLException e){
45     System.err.println("SQLException: " + e.getMessage());
46     System.err.println("SQLState: " + e.getSQLState());
47     System.err.println("VendorError: " + e.getErrorCode());
48     System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting.");
49     System.exit(2);
50     }
51 duarte 17 this.key=key; //can't use the setKey method here before we've got the db field initialized
52 duarte 34 setKeyTable(db);
53 duarte 15 }
54    
55 duarte 34 /**
56     * Create a ClusterConnection without passing a key. The key will be set later when we call createStatement(key,idx)
57 duarte 35 * @param db the database name
58     * @param user the user name for connection to both master and nodes
59     * @param password the password for connection to both master and nodes
60 duarte 34 */
61 duarte 15 public ClusterConnection (String db, String user,String password) {
62 duarte 17 loadMySQLDriver();
63 duarte 15 setDb(db);
64     setUser(user);
65     setPassword(password);
66     try {
67     // For nCon we create a connection to the master too.
68     // This is just a place holder because the actual node connection is not created until we create the statement
69     // If we don't do this then when closing the two connections an exception might occurr because we try to close a non existing object
70 duarte 34 this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
71     this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
72 duarte 15 }
73     catch(SQLException e){
74 duarte 33 System.err.println("SQLException: " + e.getMessage());
75     System.err.println("SQLState: " + e.getSQLState());
76     System.err.println("VendorError: " + e.getErrorCode());
77 duarte 34 System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting.");
78 duarte 15 System.exit(2);
79     }
80     }
81    
82     public void loadMySQLDriver() {
83     try {
84 duarte 72 Class.forName("com.mysql.jdbc.Driver").newInstance();
85 duarte 15 }
86     catch(Exception e) {
87 duarte 33 System.err.println(e.getMessage());
88     System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
89 duarte 15 System.exit(1);
90     }
91     }
92     public void close() {
93     try {
94     this.nCon.close();
95     this.mCon.close();
96     }
97     catch(SQLException e) {
98 duarte 34 System.err.println("Couldn't close database connections for master: "+MASTERHOST+" and node: "+this.host+", exiting.");
99 duarte 33 System.err.println("SQLException: " + e.getMessage());
100     System.err.println("SQLState: " + e.getSQLState());
101 duarte 15 System.exit(3);
102     }
103     }
104    
105     public String getHost4Idx (int idx) {
106     String host="";
107     Statement S;
108     ResultSet R;
109     try {
110     S=mCon.createStatement();
111     String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
112 duarte 72 "ON (m.client_id=c.client_id) WHERE "+key+"="+idx+";";
113 duarte 15 R=S.executeQuery(query);
114     if (R.next()){
115     host=R.getString(1);
116     }
117     S.close();
118     R.close();
119     }
120     catch(SQLException e) {
121 duarte 33 System.err.println("Couldn't get the host name for idx "+idx+", exiting");
122     System.err.println("SQLException: " + e.getMessage());
123     System.err.println("SQLState: " + e.getSQLState());
124 duarte 15 System.exit(3);
125     }
126     return host;
127     }
128    
129     public void setHostFromIdx(int idx){
130     setHost(getHost4Idx(idx));
131     }
132    
133     public void setHost(String host) {
134     this.host=host;
135     try {
136 duarte 23 //Closing previous connection is essential
137     //If we don't close it a lot of connections stay open after using a ClusterConnection object for a while
138     this.nCon.close();
139 duarte 34 this.nCon=DriverManager.getConnection(URL+host+"/"+db,user,password);
140 duarte 15 }
141     catch (SQLException e){
142 duarte 33 System.err.println("SQLException: " + e.getMessage());
143     System.err.println("SQLState: " + e.getSQLState());
144 duarte 37 System.err.println("Couldn't get connection to node "+host+", database "+db+", exiting.");
145 duarte 15 System.exit(2);
146     }
147     }
148 duarte 35
149 duarte 34 /**
150     * This method is strictly private. We shouldn't call this from another class as a key might not be set when we call it
151     * and thus we can't get the client_id from the master key table. Only to be called from createStatement(key,idx)
152 duarte 35 * @param idx the value of the id for a certain key already set
153     * @return a Stament object with a connection to the node that contains idx for key
154 duarte 34 */
155     private Statement createStatement(int idx) { // to use when the field "key" is already set
156     setKeyTable();
157 duarte 15 Statement S=null;
158     this.setHostFromIdx(idx);
159     try {
160     S=this.nCon.createStatement();
161     }
162     catch (SQLException e){
163 duarte 33 System.err.println("SQLException: " + e.getMessage());
164     System.err.println("SQLState: " + e.getSQLState());
165     System.err.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
166 duarte 15 System.exit(2);
167     }
168     return S;
169     }
170 duarte 35
171 duarte 34 /**
172 duarte 35 * This method is used to create a statement passing the key and idx. It will create a connection the the right node
173     * and return a Statement for that connection
174     * @param idx the key name
175     * @param idx the id value for that key
176     * @return a Statement object with a connection to the node that contains idx for key
177 duarte 34 */
178 duarte 15 public Statement createStatement(String key,int idx) {
179     setKey(key);
180     return createStatement(idx);
181     }
182    
183 duarte 35 /**
184 duarte 43 * To execute a sql update/insert query in the right node given a query, key and idx. Just a shortcut not to have to do the create statement and execute
185     * @param query the SQL query
186     * @param key the name of the key
187     * @param idx the id value for that key
188     */
189     public void executeSql(String query,String key, int idx) {
190     Statement stmt;
191     try {
192     stmt = this.createStatement(key,idx);
193     stmt.execute(query);
194     stmt.close();
195     } catch (SQLException e) {
196     System.err.println("SQLException: " + e.getMessage());
197     System.err.println("SQLState: " + e.getSQLState());
198     System.err.println("VendorError: " + e.getErrorCode());
199     e.printStackTrace();
200     }
201     }
202 duarte 51 /**
203     * To change the MASTERDB String, i.e. the name of the key master database. To be used in testing.
204     * @param db the name of the key master db we want to use instead of the default defined in the MASTERDB field
205     */
206     public void setKeyDb(String db) {
207     this.MASTERDB=db;
208 duarte 53 try {
209     //Closing previous connection is essential
210     this.mCon.close();
211     this.mCon=DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
212     }
213     catch (SQLException e){
214     System.err.println("SQLException: " + e.getMessage());
215     System.err.println("SQLState: " + e.getSQLState());
216     System.err.println("Couldn't get connection to master host "+host+", database "+MASTERDB+", exiting.");
217     System.exit(2);
218     }
219 duarte 51 }
220 duarte 43
221     /**
222 duarte 72 * To set keyTable field in constructor (i.e. first time). Only to be used in constructor.
223 duarte 35 * @param db the database name
224     */
225     public void setKeyTable(String db) {
226 duarte 72 String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+db+"\' AND key_name=\'"+this.key+"\';";
227     try {
228     Statement S = this.mCon.createStatement();
229     ResultSet R = S.executeQuery(query);
230     if (R.next()){
231     this.keyTable=R.getString(1);
232     }
233     R.close();
234     S.close();
235     } catch (SQLException e) {
236     System.err.println("SQLException: " + e.getMessage());
237     System.err.println("SQLState: " + e.getSQLState());
238     System.err.println("Couldn't get the key_master_table from "+MASTERDB+", exiting.");
239     System.exit(2);
240     }
241 duarte 15 }
242    
243 duarte 35 /**
244 duarte 43 * To set the keyTable field when db is already set
245 duarte 72 * The value of keyTable is taken from the dbs_keys table in the database given the db and key.
246 duarte 35 */
247     public void setKeyTable() {
248 duarte 72 String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+this.db+"\' AND key_name=\'"+this.key+"\';";
249     try {
250     Statement S = this.mCon.createStatement();
251     ResultSet R = S.executeQuery(query);
252     if (R.next()){
253     this.keyTable=R.getString(1);
254     }
255     R.close();
256     S.close();
257     } catch (SQLException e) {
258     System.err.println("SQLException: " + e.getMessage());
259     System.err.println("SQLState: " + e.getSQLState());
260     System.err.println("Couldn't get the key_master_table from "+MASTERDB+", exiting.");
261     System.exit(2);
262     }
263 duarte 15 }
264    
265 duarte 34 public String getKeyTable() {
266 duarte 15 return this.keyTable;
267     }
268    
269 duarte 72 /**
270     * To get the name of the target table where splitted data is stored in nodes, e.g. for keyTable pdbgraph__asu_list, we get asu_list
271     * @return
272     */
273     public String getTableOnNode(){
274     String table="";
275     if (this.keyTable.contains("__")) {
276     String[] tokens=this.keyTable.split("__");
277     table=tokens[1];
278     }
279     else {
280     System.err.println("Error! The keyTable field is not set in this ClusterConnection object.");
281     }
282     return table;
283 duarte 15 }
284 duarte 72
285 duarte 15 public void setUser(String user) {
286     this.user=user;
287     }
288    
289     public void setPassword(String password) {
290     this.password=password;
291     }
292    
293     public void setDb(String db){
294     this.db=db;
295     }
296    
297     public void setKey(String key){
298     this.key=key;
299 duarte 34 setKeyTable();
300 duarte 15 }
301    
302     public Statement createMasterStatement() {
303     Statement S=null;
304     try {
305     S=this.mCon.createStatement();
306     }
307     catch (SQLException e){
308 duarte 33 System.err.println("SQLException: " + e.getMessage());
309     System.err.println("SQLState: " + e.getSQLState());
310     System.err.println("Couldn't create statement for the master connection, exiting.");
311 duarte 15 System.exit(2);
312     }
313     return S;
314     }
315 duarte 74
316 duarte 67 public int[] getAllIdxFromMaster(String key) {
317 duarte 15 this.setKey(key);
318 duarte 67 int[] ids=null;
319     ArrayList<Integer> idsAL=new ArrayList<Integer>();
320     try {
321 duarte 72 String query="SELECT "+key+" FROM "+keyTable+";";
322 duarte 67 Statement S=this.mCon.createStatement();
323     ResultSet R=S.executeQuery(query);
324     while (R.next()){
325     idsAL.add(R.getInt(1));
326     }
327     R.close();
328     S.close();
329 duarte 15 }
330     catch (SQLException e){
331 duarte 33 System.err.println("SQLException: " + e.getMessage());
332     System.err.println("SQLState: " + e.getSQLState());
333 duarte 72 System.err.println("Couldn't get all indices from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
334 duarte 15 System.exit(2);
335     }
336 duarte 67 ids=new int[idsAL.size()];
337     for (int i=0;i<idsAL.size();i++){
338     ids[i]=idsAL.get(i);
339     }
340     return ids;
341 duarte 15 }
342 duarte 35 /**
343 duarte 38 * To get all ids and clients_names pairs for a certain key. Useful when need to submit to all hosts using qsub -q
344     * @param key the name of the key
345 duarte 67 * @return HashMap with keys = indices, and values = node names where the corresponding index is stored
346 duarte 38 */
347 duarte 67 public HashMap<Integer,String> getAllIdxAndClients (String key){
348 duarte 38 this.setKey(key);
349 duarte 67 HashMap<Integer,String> idsAndClients=new HashMap<Integer,String>();
350 duarte 38 try {
351 duarte 72 String query="SELECT a."+key+",c.client_name FROM "+keyTable+" AS a INNER JOIN clients_names AS c ON (a.client_id=c.client_id);";
352 duarte 67 Statement S=this.mCon.createStatement();
353     ResultSet R=S.executeQuery(query);
354     while (R.next()){
355     idsAndClients.put(R.getInt(1),R.getString(2));
356     }
357     R.close();
358     S.close();
359 duarte 38 }
360     catch (SQLException e){
361     System.err.println("SQLException: " + e.getMessage());
362     System.err.println("SQLState: " + e.getSQLState());
363     System.err.println("Couldn't get all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
364     System.exit(2);
365     }
366 duarte 67 return idsAndClients;
367 duarte 38 }
368    
369     /**
370 duarte 35 * To get client_id for a certain idx and key
371     * @param key the key name
372     * @param idx the id value for that key
373     * @return the client_id for node that has the data for the idx for that key
374     */
375 duarte 15 //TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
376     public int getHostId4Idx (String key,int idx) {
377     int hostId=0;
378     this.setKey(key);
379     Statement S;
380     ResultSet R;
381     String query;
382     int countCids=0;
383     try {
384     S=mCon.createStatement();
385 duarte 72 query="SELECT count(client_id) FROM "+keyTable+" WHERE "+key+"="+idx+";";
386 duarte 15 R=S.executeQuery(query);
387     if (R.next()){
388     countCids=R.getInt(1);
389     }
390     if (countCids!=1){
391 duarte 76 System.err.println("the query was: "+query);
392 duarte 72 System.err.println("Error! the count of client_ids for idx "+key+"= "+idx+" is " +countCids+
393 duarte 52 ". It must be 1! The values were taken from host: "+MASTERHOST+", database: "+MASTERDB+", table: "+keyTable+". Check what's wrong! Exiting now.");
394 duarte 15 System.exit(2);
395     }
396     else {
397 duarte 72 query="SELECT client_id FROM "+keyTable+" WHERE "+key+"="+idx+";";
398 duarte 15 R=S.executeQuery(query);
399     if (R.next()){
400     hostId=R.getInt(1);
401     }
402     }
403     S.close();
404     R.close();
405     }
406     catch(SQLException e) {
407 duarte 72 System.err.println("Couldn't get the host id for idx "+key+"="+idx+", exiting");
408 duarte 33 System.err.println("SQLException: " + e.getMessage());
409     System.err.println("SQLState: " + e.getSQLState());
410 duarte 15 System.exit(3);
411     }
412     return hostId;
413     }
414    
415     public void insertIdxInMaster(String key, int clientId) {
416     Statement S;
417     String query;
418     this.setKey(key);
419     try {
420     S=this.mCon.createStatement();
421     query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
422     S.executeUpdate(query);
423     S.close();
424     }
425     catch (SQLException E) {
426 duarte 33 System.err.println("SQLException: " + E.getMessage());
427     System.err.println("SQLState: " + E.getSQLState());
428 duarte 72 System.err.println("Couldn't insert new "+this.key+" in master table "+this.getKeyTable()+". The client_id for it was "+clientId+". Exiting.");
429 duarte 15 System.exit(2);
430     }
431     }
432    
433     public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
434     this.setKey(keySrc);
435     int clientId=0;
436     clientId=this.getHostId4Idx(keySrc,idxSrc);
437     insertIdxInMaster(keyDest,clientId);
438     }
439    
440     public int getLastInsertId(String key) {
441     int lastIdx=0;
442     this.setKey(key);
443     Statement S;
444     ResultSet R;
445     String query = "";
446     try {
447     S = this.mCon.createStatement();
448     query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
449     R = S.executeQuery(query);
450     if (R.next()) {
451     lastIdx=R.getInt(1);
452     }
453     R.close();
454     S.close();
455     }
456     catch (SQLException E) {
457 duarte 72 System.err.println("Couldn't get the last insert id for key type "+this.key+" from table "+this.keyTable+". Exiting");
458 duarte 33 System.err.println("SQLException: " + E.getMessage());
459     System.err.println("SQLState: " + E.getSQLState());
460 duarte 15 System.exit(3);
461     } // end try/catch connection
462     return lastIdx;
463     } // end getGraphId
464 duarte 21
465     public int[][] getIdxSet(String key) {
466     int[][] indMatrix=null;
467     this.setKey(key);
468     String query;
469     Statement S;
470     ResultSet R;
471     try {
472     // STEP 1 -- getting set of all client_ids
473     S=this.mCon.createStatement();
474     query="SELECT count(distinct client_id) FROM "+keyTable+";";
475     int count=0;
476     R=S.executeQuery(query);
477     if (R.next()){
478     count=R.getInt(1);
479     }
480     query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
481     //R.close();
482     //S.close();
483     R=S.executeQuery(query);
484    
485     // STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field
486     int[] clids=new int[count]; //array to store all client_ids. To be used in loops later
487     int i=0;
488     while (R.next()){
489     Statement Sloop=this.mCon.createStatement();
490     int clid=R.getInt(1);
491 duarte 72 query="CREATE TEMPORARY TABLE c_"+clid+" (serial int(11) NOT NULL AUTO_INCREMENT,"+key+" int(11),client_id int(11), PRIMARY KEY(serial));";
492 duarte 21 Sloop.executeUpdate(query);
493 duarte 72 query="INSERT INTO c_"+clid+" ("+key+",client_id) SELECT "+key+",client_id FROM "+keyTable+" WHERE client_id="+clid+";";
494 duarte 21 Sloop.executeUpdate(query);
495     clids[i]=clid;
496     i++;
497     Sloop.close();
498     }
499    
500     // STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count
501     //query="SELECT client_id,count(*) as c FROM c_34 GROUP BY client_id UNION SELECT client_id,count(*) as c FROM c_32 GROUP BY client_id;";
502     query="DROP TABLE IF EXISTS tmp_allcs;";
503     S.executeUpdate(query);
504     //this table must be permanent! otherwise cannot do the select max(c) later
505     query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;";
506     S.executeUpdate(query);
507     String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id";
508     for (i=1;i<clids.length;i++) {
509     unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id";
510     }
511     query="INSERT INTO tmp_allcs "+unionStr+";";
512     S.executeUpdate(query);
513     query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);";
514     R=S.executeQuery(query);
515     int clidMaxIdxCount=0;
516     int maxIdxCount=0;
517     if (R.next()) {
518     clidMaxIdxCount=R.getInt(1);
519     maxIdxCount=R.getInt(2);
520     }
521     query="DROP TABLE tmp_allcs;";
522     S.executeUpdate(query);
523    
524     // STEP 4 -- join all c_<client_id> tables into a table with a serial column, and c_<client_id> columns each of them with the indices for each client_id
525     //query="SELECT c_34.serial,c_34.asu_id AS c_34,c_32.asu_id AS c_32,c_36.asu_id AS c_36 FROM c_34 LEFT JOIN c_32 ON (c_34.serial=c_32.serial) LEFT JOIN c_36 ON (c_34.serial=c_36.serial);";
526 duarte 72 String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+key+" AS c_"+clidMaxIdxCount;
527 duarte 21 String fromStr="c_"+clidMaxIdxCount;
528     for (i=0;i<clids.length;i++) {
529     if (clids[i]!=clidMaxIdxCount){
530 duarte 72 selectStr+=", c_"+clids[i]+"."+key+" AS c_"+clids[i];
531 duarte 21 fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)";
532     }
533     }
534     query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";";
535     S.executeUpdate(query);
536    
537     // STEP 5 -- put the table into a 2-dimensional array and return it
538     indMatrix = new int[maxIdxCount][clids.length];
539     query="SELECT * FROM indices_matrix";
540     R=S.executeQuery(query);
541     i=0;
542     while (R.next()) {
543     for (int j=0;j<clids.length;j++){
544 duarte 22 indMatrix[i][j]=R.getInt(j+2);
545 duarte 21 }
546     i++;
547     }
548     R.close();
549     S.close();
550     }
551     catch (SQLException e){
552 duarte 33 System.err.println("SQLException: " + e.getMessage());
553     System.err.println("SQLState: " + e.getSQLState());
554 duarte 72 System.err.println("Couldn't get the indices set from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
555 duarte 21 System.exit(2);
556     }
557     return indMatrix;
558     }
559    
560    
561 duarte 15 }

Properties

Name Value
svn:executable *