trunk/tools/ClusterConnection.java

package tools;
import java.sql.*;

/**
 * ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs
 * @author Jose Duarte
 */

public class ClusterConnection {

        private final String URL= "jdbc:mysql://";
        private String MASTERDB="key_master"; 
        private final String MASTERHOST="white";
        private Connection nCon;
        private Connection mCon;
        public String keyTable;
        public String key;
        public String idxColumn;
        public String host;
        public String db;
        private String user;
        private String password;

        /**
         * Create a ClusterConnection passing a key. 
         * @param db the database name
         * @param key the key name: if asu_id is the ids from which my key is based on, then the key name is "asu"
         * @param user the user name for connection to both master and nodes
         * @param password the password for connection to both master and nodes
         */
        public ClusterConnection (String db,String key, String user,String password) {
                new ClusterConnection(db,user,password);
                this.key=key; //can't use the setKey method here before we've got the db field initialized
                setIdxColumn();
                setKeyTable(db);
        }

        /**
         * Create a ClusterConnection without passing a key. The key will be set later when we call createStatement(key,idx) 
         * @param db the database name
         * @param user the user name for connection to both master and nodes
         * @param password the password for connection to both master and nodes
         */ 
        public ClusterConnection (String db, String user,String password) { 
                loadMySQLDriver();
                setDb(db);
                setUser(user);
                setPassword(password);
                try {
                        // For nCon we create a connection to the master too. 
                        // This is just a place holder because the actual node connection is not created until we create the statement
                        // 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
                        this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
                        this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
                }
                catch(SQLException e){
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
                        System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting.");
                        System.exit(2);                 
                }
        }
        
        public void loadMySQLDriver() {
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                }
                catch(Exception e) {
                        System.err.println(e.getMessage());
                        System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
                        System.exit(1);
                }
        }
        public void close() {
                try {
                        this.nCon.close();
                        this.mCon.close();
                }
                catch(SQLException e) {
                        System.err.println("Couldn't close database connections for master: "+MASTERHOST+" and node: "+this.host+", exiting.");
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                         
                }
        }
        
        public String getHost4Idx (int idx) {
                String host="";
                Statement S;
                ResultSet R;
                try {
                        S=mCon.createStatement();
                        String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
                                                "ON (m.client_id=c.client_id) WHERE "+idxColumn+"="+idx+";";
                        R=S.executeQuery(query);
                        if (R.next()){
                                host=R.getString(1);
                        }
                        S.close();
                        R.close();
                }
                catch(SQLException e) {
                        System.err.println("Couldn't get the host name for idx "+idx+", exiting");
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                                                 
                }
                return host;
        }
        
        public void setHostFromIdx(int idx){
                setHost(getHost4Idx(idx));
        }
        
        public void setHost(String host) {
                this.host=host;
                try {
                        //Closing previous connection is essential
                        //If we don't close it a lot of connections stay open after using a ClusterConnection object for a while
                        this.nCon.close(); 
                        this.nCon=DriverManager.getConnection(URL+host+"/"+db,user,password);
                }
                catch (SQLException e){
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't get connection to node "+host+", database "+db+", exiting.");
                        System.exit(2);                                         
                }
        }
        
        /**
         * This method is strictly private. We shouldn't call this from another class as a key might not be set when we call it
         * and thus we can't get the client_id from the master key table. Only to be called from createStatement(key,idx)
         * @param idx the value of the id for a certain key already set
         * @return a Stament object with a connection to the node that contains idx for key
         */
        private Statement createStatement(int idx) { // to use when the field "key" is already set
                setKeyTable();
                setIdxColumn();
                Statement S=null;
                this.setHostFromIdx(idx);
                try {
                        S=this.nCon.createStatement();
                }
                catch (SQLException e){
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
                        System.exit(2);                                                                 
                }
                return S;
        }
        
        /**
         * This method is used to create a statement passing the key and idx. It will create a connection the the right node 
         * and return a Statement for that connection
         * @param idx the key name
         * @param idx the id value for that key
         * @return a Statement object with a connection to the node that contains idx for key
         */
        public Statement createStatement(String key,int idx) {
                setKey(key);
                return createStatement(idx);
        }
        
        /**
         * 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
         * @param query the SQL query
         * @param key the name of the key
         * @param idx the id value for that key
         */
        public void executeSql(String query,String key, int idx) {
                Statement stmt;
                try {
                    stmt = this.createStatement(key,idx);       
                    stmt.execute(query);
                        stmt.close();               
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } 
        }
        /**
         * To change the MASTERDB String, i.e. the name of the key master database. To be used in testing.
         * @param db the name of the key master db we want to use instead of the default defined in the MASTERDB field
         */
        public void setKeyDb(String db) { 
                this.MASTERDB=db;
        }
        
        /**
         * To set keyTable field in constructor (i.e. first time)
         * @param db the database name
         */
        public void setKeyTable(String db) { 
                this.keyTable=db+"_"+this.key+"_list_master";
        }
        
        /**
         * To set the keyTable field when db is already set
         *
         */
        public void setKeyTable() {  
                this.keyTable=this.db+"_"+this.key+"_list_master";
        }
        
        public String getKeyTable() {
                return this.keyTable;
        }
        
        public void setIdxColumn() {
                this.idxColumn=this.key+"_id";
        }

        public void setUser(String user) {
                this.user=user;
        }

        public void setPassword(String password) {
                this.password=password;
        }
        
        public void setDb(String db){
                this.db=db;
        }
        
        public void setKey(String key){
                this.key=key;
                setKeyTable();
                setIdxColumn();
        }

        public Statement createMasterStatement() { 
                Statement S=null;
                try {
                        S=this.mCon.createStatement();
                }
                catch (SQLException e){
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't create statement for the master connection, exiting.");
                        System.exit(2);                                                                 
                }
                return S;
        }

        public ResultSet getAllIdxFromMaster(String key) { 
                this.setKey(key);
                String query;
                Statement S;
                ResultSet R=null;
                try {
                        query="SELECT "+idxColumn+" FROM "+keyTable+";";
                        S=this.mCon.createStatement();
                        R=S.executeQuery(query);
                        //S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
                }
                catch (SQLException e){
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                return R;
        }
        /**
         * To get all ids and clients_names pairs for a certain key. Useful when need to submit to all hosts using qsub -q
         * @param key the name of the key
         * @return ResultSet with the pairs: id, client_name
         */
        public ResultSet getAllIdxAndClients (String key){
                this.setKey(key);
                String query;
                Statement S;
                ResultSet R = null;
                try {
                        query="SELECT a."+idxColumn+",c.client_name FROM "+keyTable+" AS a INNER JOIN clients_names AS c ON (a.client_id=c.client_id);";
                        S=this.mCon.createStatement();
                        R=S.executeQuery(query);
                        //S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
                }
                catch (SQLException e){
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't get all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                return R;
        }
        
        /**
         * To get client_id for a certain idx and key
         * @param key the key name
         * @param idx the id value for that key
         * @return the client_id for node that has the data for the idx for that key
         */
        //TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
        public int getHostId4Idx (String key,int idx) { 
                int hostId=0;
                this.setKey(key);
                Statement S;
                ResultSet R;
                String query;
                int countCids=0;
                try {
                        S=mCon.createStatement();
                        query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
                        R=S.executeQuery(query);
                        if (R.next()){
                                countCids=R.getInt(1);
                        }
                        if (countCids!=1){
                                System.err.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is 0 or bigger than 1." +
                                                " The values where taken from host: "+MASTERHOST+", database: "+MASTERDB+", table: "+keyTable+". Check what's wrong! Exiting now.");
                                System.exit(2);
                        }
                        else {
                                query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
                                R=S.executeQuery(query);
                                if (R.next()){
                                        hostId=R.getInt(1);
                                }
                        }
                        S.close();
                        R.close();
                }
                catch(SQLException e) {
                        System.err.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting");
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                                                 
                }
                return hostId;
        }
        
        public void insertIdxInMaster(String key, int clientId) {
                Statement S;
                String query;
                this.setKey(key);
                try {
                        S=this.mCon.createStatement();
                        query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
                        S.executeUpdate(query);
                        S.close();
                } 
                catch (SQLException E) {
                        System.err.println("SQLException: " + E.getMessage());
                        System.err.println("SQLState:     " + E.getSQLState());
                        System.err.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getKeyTable()+". The client_id for it was "+clientId+". Exiting.");
                        System.exit(2);
                } 
        }

        public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
                this.setKey(keySrc);
                int clientId=0;
                clientId=this.getHostId4Idx(keySrc,idxSrc);
                insertIdxInMaster(keyDest,clientId);
        }

        public int getLastInsertId(String key) {
                int lastIdx=0;
                this.setKey(key);
                Statement S;
                ResultSet R;
                String query = "";
                try {
                        S = this.mCon.createStatement();
                        query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
                        R = S.executeQuery(query);
                        if (R.next()) {
                                lastIdx=R.getInt(1);
                        }
                        R.close();
                        S.close();
                } 
                catch (SQLException E) {
                        System.err.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting");
                        System.err.println("SQLException: " + E.getMessage());
                        System.err.println("SQLState:     " + E.getSQLState());
                        System.exit(3);
                } // end try/catch connection 
                return lastIdx;
        } // end getGraphId
        
        public int[][] getIdxSet(String key) { 
                int[][] indMatrix=null;
                this.setKey(key);
                String query;
                Statement S;
                ResultSet R;
                try {
                        // STEP 1 -- getting set of all client_ids
                        S=this.mCon.createStatement();
                        query="SELECT count(distinct client_id) FROM "+keyTable+";";
                        int count=0;
                        R=S.executeQuery(query);
                        if (R.next()){
                                count=R.getInt(1);
                        }
                        query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
                        //R.close();
                        //S.close();
                        R=S.executeQuery(query);
                        
                        // STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field
                        int[] clids=new int[count]; //array to store all client_ids. To be used in loops later
                        int i=0;
                        while (R.next()){
                                Statement Sloop=this.mCon.createStatement();
                                int clid=R.getInt(1);
                                query="CREATE TEMPORARY TABLE c_"+clid+" (serial int(11) NOT NULL AUTO_INCREMENT,"+idxColumn+" int(11),client_id int(11), PRIMARY KEY(serial));";                               
                                Sloop.executeUpdate(query);
                                query="INSERT INTO c_"+clid+" ("+idxColumn+",client_id) SELECT "+idxColumn+",client_id FROM "+keyTable+" WHERE client_id="+clid+";";
                                Sloop.executeUpdate(query);
                                clids[i]=clid;
                                i++;
                                Sloop.close();
                        }
                        
                        // STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count
                        //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;";
                        query="DROP TABLE IF EXISTS tmp_allcs;"; 
                        S.executeUpdate(query);
                        //this table must be permanent! otherwise cannot do the select max(c) later
                        query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;";
                        S.executeUpdate(query);
                        String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id";
                        for (i=1;i<clids.length;i++) {
                                unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id";
                        }
                        query="INSERT INTO tmp_allcs "+unionStr+";";
                        S.executeUpdate(query);
                        query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);";
                        R=S.executeQuery(query);
                        int clidMaxIdxCount=0;
                        int maxIdxCount=0;
                        if (R.next()) {
                                clidMaxIdxCount=R.getInt(1);
                                maxIdxCount=R.getInt(2);
                        }
                        query="DROP TABLE tmp_allcs;";
                        S.executeUpdate(query);
                        
                        // 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 
                        //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);";
                        String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+idxColumn+" AS c_"+clidMaxIdxCount;
                        String fromStr="c_"+clidMaxIdxCount;
                        for (i=0;i<clids.length;i++) {
                                if (clids[i]!=clidMaxIdxCount){
                                        selectStr+=", c_"+clids[i]+"."+idxColumn+" AS c_"+clids[i];
                                        fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)";
                                }
                        } 
                        query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";";
                        S.executeUpdate(query);
                        
                        // STEP 5 -- put the table into a 2-dimensional array and return it
                        indMatrix = new int[maxIdxCount][clids.length];
                        query="SELECT * FROM indices_matrix";
                        R=S.executeQuery(query);
                        i=0;
                        while (R.next()) {
                                for (int j=0;j<clids.length;j++){
                                        indMatrix[i][j]=R.getInt(j+2);
                                }
                                i++;
                        }
                        R.close();
                        S.close();
                }
                catch (SQLException e){
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("Couldn't get the indices set from columnn "+idxColumn+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                return indMatrix;
        }

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