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;
                try {
                        //Closing previous connection is essential
                        this.mCon.close(); 
                        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("Couldn't get connection to master host "+host+", database "+MASTERDB+", exiting.");
                        System.exit(2);                                         
                }
        }
        
        /**
         * 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.out.println("the query was: "+query);
                                System.err.println("Error! the count of client_ids for idx "+idxColumn+"= "+idx+" is " +countCids+
                                                ". It must be 1! The values were 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:	53
Committed:	Wed Mar 29 13:01:40 2006 UTC (18 years, 6 months ago) by duarte
File size:	17066 byte(s)
Log Message:	Fixed a bug with setKeyDb method, was not actually working: as well as setting the MASTERDB string needed to change connection to master database to point to new db! From now on take care if setKeyDb is used in another class. It will open another master connection, thus master connection won't be always the same during the run of the program
Line	File contents
1	package tools;
2	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	private final String URL= "jdbc:mysql://";
12	private String MASTERDB="key_master";
13	private final String MASTERHOST="white";
14	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
24	/**
25	* Create a ClusterConnection passing a key.
26	* @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	*/
31	public ClusterConnection (String db,String key, String user,String password) {
32	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	setIdxColumn();
35	setKeyTable(db);
36	}
37
38	/**
39	* Create a ClusterConnection without passing a key. The key will be set later when we call createStatement(key,idx)
40	* @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	*/
44	public ClusterConnection (String db, String user,String password) {
45	loadMySQLDriver();
46	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	this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
54	this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
55	}
56	catch(SQLException e){
57	System.err.println("SQLException: " + e.getMessage());
58	System.err.println("SQLState: " + e.getSQLState());
59	System.err.println("VendorError: " + e.getErrorCode());
60	System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting.");
61	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	System.err.println(e.getMessage());
71	System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
72	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	System.err.println("Couldn't close database connections for master: "+MASTERHOST+" and node: "+this.host+", exiting.");
82	System.err.println("SQLException: " + e.getMessage());
83	System.err.println("SQLState: " + e.getSQLState());
84	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	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	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	//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	this.nCon=DriverManager.getConnection(URL+host+"/"+db,user,password);
123	}
124	catch (SQLException e){
125	System.err.println("SQLException: " + e.getMessage());
126	System.err.println("SQLState: " + e.getSQLState());
127	System.err.println("Couldn't get connection to node "+host+", database "+db+", exiting.");
128	System.exit(2);
129	}
130	}
131
132	/**
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	* @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	*/
138	private Statement createStatement(int idx) { // to use when the field "key" is already set
139	setKeyTable();
140	setIdxColumn();
141	Statement S=null;
142	this.setHostFromIdx(idx);
143	try {
144	S=this.nCon.createStatement();
145	}
146	catch (SQLException e){
147	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	System.exit(2);
151	}
152	return S;
153	}
154
155	/**
156	* 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	*/
162	public Statement createStatement(String key,int idx) {
163	setKey(key);
164	return createStatement(idx);
165	}
166
167	/**
168	* 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	/**
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	try {
193	//Closing previous connection is essential
194	this.mCon.close();
195	this.mCon=DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password);
196	}
197	catch (SQLException e){
198	System.err.println("SQLException: " + e.getMessage());
199	System.err.println("SQLState: " + e.getSQLState());
200	System.err.println("Couldn't get connection to master host "+host+", database "+MASTERDB+", exiting.");
201	System.exit(2);
202	}
203	}
204
205	/**
206	* To set keyTable field in constructor (i.e. first time)
207	* @param db the database name
208	*/
209	public void setKeyTable(String db) {
210	this.keyTable=db+"_"+this.key+"_list_master";
211	}
212
213	/**
214	* To set the keyTable field when db is already set
215	*
216	*/
217	public void setKeyTable() {
218	this.keyTable=this.db+"_"+this.key+"_list_master";
219	}
220
221	public String getKeyTable() {
222	return this.keyTable;
223	}
224
225	public void setIdxColumn() {
226	this.idxColumn=this.key+"_id";
227	}
228
229	public void setUser(String user) {
230	this.user=user;
231	}
232
233	public void setPassword(String password) {
234	this.password=password;
235	}
236
237	public void setDb(String db){
238	this.db=db;
239	}
240
241	public void setKey(String key){
242	this.key=key;
243	setKeyTable();
244	setIdxColumn();
245	}
246
247	public Statement createMasterStatement() {
248	Statement S=null;
249	try {
250	S=this.mCon.createStatement();
251	}
252	catch (SQLException e){
253	System.err.println("SQLException: " + e.getMessage());
254	System.err.println("SQLState: " + e.getSQLState());
255	System.err.println("Couldn't create statement for the master connection, exiting.");
256	System.exit(2);
257	}
258	return S;
259	}
260
261	public ResultSet getAllIdxFromMaster(String key) {
262	this.setKey(key);
263	String query;
264	Statement S;
265	ResultSet R=null;
266	try {
267	query="SELECT "+idxColumn+" FROM "+keyTable+";";
268	S=this.mCon.createStatement();
269	R=S.executeQuery(query);
270	//S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
271	}
272	catch (SQLException e){
273	System.err.println("SQLException: " + e.getMessage());
274	System.err.println("SQLState: " + e.getSQLState());
275	System.err.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
276	System.exit(2);
277	}
278	return R;
279	}
280	/**
281	* To get all ids and clients_names pairs for a certain key. Useful when need to submit to all hosts using qsub -q
282	* @param key the name of the key
283	* @return ResultSet with the pairs: id, client_name
284	*/
285	public ResultSet getAllIdxAndClients (String key){
286	this.setKey(key);
287	String query;
288	Statement S;
289	ResultSet R = null;
290	try {
291	query="SELECT a."+idxColumn+",c.client_name FROM "+keyTable+" AS a INNER JOIN clients_names AS c ON (a.client_id=c.client_id);";
292	S=this.mCon.createStatement();
293	R=S.executeQuery(query);
294	//S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
295	}
296	catch (SQLException e){
297	System.err.println("SQLException: " + e.getMessage());
298	System.err.println("SQLState: " + e.getSQLState());
299	System.err.println("Couldn't get all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
300	System.exit(2);
301	}
302	return R;
303	}
304
305	/**
306	* To get client_id for a certain idx and key
307	* @param key the key name
308	* @param idx the id value for that key
309	* @return the client_id for node that has the data for the idx for that key
310	*/
311	//TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
312	public int getHostId4Idx (String key,int idx) {
313	int hostId=0;
314	this.setKey(key);
315	Statement S;
316	ResultSet R;
317	String query;
318	int countCids=0;
319	try {
320	S=mCon.createStatement();
321	query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
322	R=S.executeQuery(query);
323	if (R.next()){
324	countCids=R.getInt(1);
325	}
326	if (countCids!=1){
327	System.out.println("the query was: "+query);
328	System.err.println("Error! the count of client_ids for idx "+idxColumn+"= "+idx+" is " +countCids+
329	". It must be 1! The values were taken from host: "+MASTERHOST+", database: "+MASTERDB+", table: "+keyTable+". Check what's wrong! Exiting now.");
330	System.exit(2);
331	}
332	else {
333	query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
334	R=S.executeQuery(query);
335	if (R.next()){
336	hostId=R.getInt(1);
337	}
338	}
339	S.close();
340	R.close();
341	}
342	catch(SQLException e) {
343	System.err.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting");
344	System.err.println("SQLException: " + e.getMessage());
345	System.err.println("SQLState: " + e.getSQLState());
346	System.exit(3);
347	}
348	return hostId;
349	}
350
351	public void insertIdxInMaster(String key, int clientId) {
352	Statement S;
353	String query;
354	this.setKey(key);
355	try {
356	S=this.mCon.createStatement();
357	query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
358	S.executeUpdate(query);
359	S.close();
360	}
361	catch (SQLException E) {
362	System.err.println("SQLException: " + E.getMessage());
363	System.err.println("SQLState: " + E.getSQLState());
364	System.err.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getKeyTable()+". The client_id for it was "+clientId+". Exiting.");
365	System.exit(2);
366	}
367	}
368
369	public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
370	this.setKey(keySrc);
371	int clientId=0;
372	clientId=this.getHostId4Idx(keySrc,idxSrc);
373	insertIdxInMaster(keyDest,clientId);
374	}
375
376	public int getLastInsertId(String key) {
377	int lastIdx=0;
378	this.setKey(key);
379	Statement S;
380	ResultSet R;
381	String query = "";
382	try {
383	S = this.mCon.createStatement();
384	query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
385	R = S.executeQuery(query);
386	if (R.next()) {
387	lastIdx=R.getInt(1);
388	}
389	R.close();
390	S.close();
391	}
392	catch (SQLException E) {
393	System.err.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting");
394	System.err.println("SQLException: " + E.getMessage());
395	System.err.println("SQLState: " + E.getSQLState());
396	System.exit(3);
397	} // end try/catch connection
398	return lastIdx;
399	} // end getGraphId
400
401	public int[][] getIdxSet(String key) {
402	int[][] indMatrix=null;
403	this.setKey(key);
404	String query;
405	Statement S;
406	ResultSet R;
407	try {
408	// STEP 1 -- getting set of all client_ids
409	S=this.mCon.createStatement();
410	query="SELECT count(distinct client_id) FROM "+keyTable+";";
411	int count=0;
412	R=S.executeQuery(query);
413	if (R.next()){
414	count=R.getInt(1);
415	}
416	query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
417	//R.close();
418	//S.close();
419	R=S.executeQuery(query);
420
421	// STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field
422	int[] clids=new int[count]; //array to store all client_ids. To be used in loops later
423	int i=0;
424	while (R.next()){
425	Statement Sloop=this.mCon.createStatement();
426	int clid=R.getInt(1);
427	query="CREATE TEMPORARY TABLE c_"+clid+" (serial int(11) NOT NULL AUTO_INCREMENT,"+idxColumn+" int(11),client_id int(11), PRIMARY KEY(serial));";
428	Sloop.executeUpdate(query);
429	query="INSERT INTO c_"+clid+" ("+idxColumn+",client_id) SELECT "+idxColumn+",client_id FROM "+keyTable+" WHERE client_id="+clid+";";
430	Sloop.executeUpdate(query);
431	clids[i]=clid;
432	i++;
433	Sloop.close();
434	}
435
436	// STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count
437	//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;";
438	query="DROP TABLE IF EXISTS tmp_allcs;";
439	S.executeUpdate(query);
440	//this table must be permanent! otherwise cannot do the select max(c) later
441	query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;";
442	S.executeUpdate(query);
443	String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id";
444	for (i=1;i<clids.length;i++) {
445	unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id";
446	}
447	query="INSERT INTO tmp_allcs "+unionStr+";";
448	S.executeUpdate(query);
449	query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);";
450	R=S.executeQuery(query);
451	int clidMaxIdxCount=0;
452	int maxIdxCount=0;
453	if (R.next()) {
454	clidMaxIdxCount=R.getInt(1);
455	maxIdxCount=R.getInt(2);
456	}
457	query="DROP TABLE tmp_allcs;";
458	S.executeUpdate(query);
459
460	// 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
461	//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);";
462	String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+idxColumn+" AS c_"+clidMaxIdxCount;
463	String fromStr="c_"+clidMaxIdxCount;
464	for (i=0;i<clids.length;i++) {
465	if (clids[i]!=clidMaxIdxCount){
466	selectStr+=", c_"+clids[i]+"."+idxColumn+" AS c_"+clids[i];
467	fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)";
468	}
469	}
470	query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";";
471	S.executeUpdate(query);
472
473	// STEP 5 -- put the table into a 2-dimensional array and return it
474	indMatrix = new int[maxIdxCount][clids.length];
475	query="SELECT * FROM indices_matrix";
476	R=S.executeQuery(query);
477	i=0;
478	while (R.next()) {
479	for (int j=0;j<clids.length;j++){
480	indMatrix[i][j]=R.getInt(j+2);
481	}
482	i++;
483	}
484	R.close();
485	S.close();
486	}
487	catch (SQLException e){
488	System.err.println("SQLException: " + e.getMessage());
489	System.err.println("SQLState: " + e.getSQLState());
490	System.err.println("Couldn't get the indices set from columnn "+idxColumn+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
491	System.exit(2);
492	}
493	return indMatrix;
494	}
495
496
497	}