trunk/tools/ClusterConnection.java

package tools;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

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

public class ClusterConnection {

        private String MASTERDB=DataDistribution.KEYMASTERDB; 
        private final String MASTERHOST=DataDistribution.MASTER;
        private MySQLConnection nCon;
        private MySQLConnection mCon;
        public String keyTable;
        public String key;
        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: e.g. asu_id
         * @param user the user name for connection to both master and nodes
         * @param password the password for connection to both master and nodes
         * @throws SQLException 
         */
        public ClusterConnection (String db,String key, String user,String password) throws SQLException {
                setDb(db);
                setUser(user);
                setPassword(password);
                // 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 = new MySQLConnection(MASTERHOST,user,password,MASTERDB); 
                this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
                this.key=key; //can't use the setKey method here before we've got the db field initialized
                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
         * @throws SQLException 
         */ 
        public ClusterConnection (String db, String user,String password) throws SQLException { 
                setDb(db);
                setUser(user);
                setPassword(password);          
                // 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 = new MySQLConnection(MASTERHOST,user,password,MASTERDB); 
                this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
        }
        
        public void close() {
                this.nCon.close();
                this.mCon.close();
        }
        
        public <T> String getHost4Idx (T idx) {
                String host="";
                String query = "SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
                                           "ON (m.client_id=c.client_id) WHERE "+key+"='"+idx+"';";
                host=this.mCon.getStringFromDb(query);
                return host;
        }
        
        public <T> void setHostFromIdx(T idx) throws SQLException{
                setHost(getHost4Idx(idx));
        }
        
        public void setHost(String host) throws SQLException {
                this.host=host;
                //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 = new MySQLConnection(host,user,password,db);
        }
        
        /**
         * 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
         * @throws SQLException 
         */
        private <T> Statement createStatement(T idx) throws SQLException { // to use when the field "key" is already set
                setKeyTable();
                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
         * @throws SQLException 
         */
        public <T> Statement createStatement(String key,T idx) throws SQLException {
                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 <T> void executeSql(String query,String key, T idx) throws SQLException {
                Statement stmt;
                stmt = this.createStatement(key,idx);   
                stmt.execute(query);
                stmt.close();               
        }
        
        /**
         * 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
         * @throws SQLException 
         */
        public void setKeyDb(String db) throws SQLException { 
                this.MASTERDB=db;
                //Closing previous connection is essential
                this.mCon.close(); 
                this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
        }
        
        /**
         * To set keyTable field in constructor (i.e. first time). Only to be used in constructor.
         * @param db the database name
         */
        public void setKeyTable(String db) { 
                String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+db+"\' AND key_name=\'"+this.key+"\';";
                this.keyTable=this.mCon.getStringFromDb(query);
        }
        
        /**
         * To set the keyTable field when db is already set
         * The value of keyTable is taken from the dbs_keys table in the database given the db and key.
         */
        public void setKeyTable() {  
                String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+this.db+"\' AND key_name=\'"+this.key+"\';";
                this.keyTable=this.mCon.getStringFromDb(query);
        }
        
        public String getKeyTable() {
                return this.keyTable;
        }
        
        /**
         * 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
         * @return
         */
        public String getTableOnNode(){
                String table="";
                if (this.keyTable.contains("__")) {
                        String[] tokens=this.keyTable.split("__");
                        table=tokens[1];
                }
                else {
                        System.err.println("Error! The keyTable field is not set in this ClusterConnection object.");
                }
                return table;
        }
        
        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();
        }

        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 Object[] getAllIdxFromMaster(String key) { 
                this.setKey(key);
                Object[] ids=null;
                ArrayList<Object> idsAL=new ArrayList<Object>();
                try {                   
                        String query="SELECT "+key+" FROM "+keyTable+";";
                        Statement S=this.mCon.createStatement();
                        ResultSet R=S.executeQuery(query);
                        while (R.next()){
                                idsAL.add(R.getInt(1));
                        }
                        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 all indices from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                ids=new Object[idsAL.size()];
                for (int i=0;i<idsAL.size();i++){
                        ids[i]=idsAL.get(i);
                }
                return ids;
        }
        
        /**
         * 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 HashMap with keys = indices, and values = node names where the corresponding index is stored
         */
        public HashMap<Object,String> getAllIdxAndClients (String key){
                this.setKey(key);
                HashMap<Object,String> idsAndClients=new HashMap<Object,String>();
                try {
                        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);";
                        Statement S=this.mCon.createStatement();
                        ResultSet R=S.executeQuery(query);
                        while (R.next()){
                                idsAndClients.put(R.getString(1),R.getString(2));
                        }
                        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 all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                return idsAndClients;
        }
        
        /**
         * To get client_id for a certain idx and key
         * @param key the key name
         * @param idx the id value for that key (either a String or an int)
         * @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 <T> int getHostId4Idx (String key,T idx) { 
                int hostId=0;
                this.setKey(key);
                String query;
                int countCids=0;
                query="SELECT count(client_id) FROM "+keyTable+" WHERE "+key+"='"+idx+"';";
                countCids=this.mCon.getIntFromDb(query);
                if (countCids!=1){
                        System.err.println("the query was: "+query);
                        System.err.println("Error! the count of client_ids for idx "+key+"= "+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 "+key+"='"+idx+"';";
                        hostId=this.mCon.getIntFromDb(query);
                }
                return hostId;
        }
        
        // next 4 methods here refer purely to numeric keys, won't change them to be general text/numeric
        public void insertIdxInMaster(String key, int clientId) {
                String query;
                this.setKey(key);
                try {
                        query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
                        this.mCon.executeSql(query);
                } 
                catch (SQLException E) {
                        System.err.println("SQLException: " + E.getMessage());
                        System.err.println("SQLState:     " + E.getSQLState());
                        System.err.println("Couldn't insert new "+this.key+" 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);
                String query = "";
                query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
                lastIdx=this.mCon.getIntFromDb(query);
                return lastIdx;
        }
        
        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                      
                        query="SELECT count(distinct client_id) FROM "+keyTable+";";
                        int count=0;
                        count=this.mCon.getIntFromDb(query);
                        S=this.mCon.createStatement();
                        query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
                        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,"+key+" int(11),client_id int(11), PRIMARY KEY(serial));";                             
                                Sloop.executeUpdate(query);
                                query="INSERT INTO c_"+clid+" ("+key+",client_id) SELECT "+key+",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+"."+key+" AS c_"+clidMaxIdxCount;
                        String fromStr="c_"+clidMaxIdxCount;
                        for (i=0;i<clids.length;i++) {
                                if (clids[i]!=clidMaxIdxCount){
                                        selectStr+=", c_"+clids[i]+"."+key+" 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 "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
                        System.exit(2);                                                                 
                }
                return indMatrix;
        }

        
}
Revision:	202
Committed:	Thu Jun 21 17:18:11 2007 UTC (17 years, 4 months ago) by duarte
File size:	15419 byte(s)
Log Message:	MySQLConnection now throwing SQLException on connect Many files changed following this: all calling classes now re-throwing or catching the SQLException
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	92	private String MASTERDB=DataDistribution.KEYMASTERDB;
14			private final String MASTERHOST=DataDistribution.MASTER;
15	duarte	81	private MySQLConnection nCon;
16			private MySQLConnection mCon;
17	duarte	15	public String keyTable;
18			public String key;
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	duarte	72	* @param key the key name: e.g. asu_id
28	duarte	35	* @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	202	* @throws SQLException
31	duarte	34	*/
32	duarte	202	public ClusterConnection (String db,String key, String user,String password) throws SQLException {
33	duarte	72	setDb(db);
34			setUser(user);
35			setPassword(password);
36	duarte	81	// For nCon we create a connection to the master too.
37			// This is just a place holder because the actual node connection is not created until we create the statement
38			// 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
39			this.nCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
40			this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
41	duarte	17	this.key=key; //can't use the setKey method here before we've got the db field initialized
42	duarte	34	setKeyTable(db);
43	duarte	15	}
44
45	duarte	34	/**
46			* Create a ClusterConnection without passing a key. The key will be set later when we call createStatement(key,idx)
47	duarte	35	* @param db the database name
48			* @param user the user name for connection to both master and nodes
49			* @param password the password for connection to both master and nodes
50	duarte	202	* @throws SQLException
51	duarte	34	*/
52	duarte	202	public ClusterConnection (String db, String user,String password) throws SQLException {
53	duarte	15	setDb(db);
54			setUser(user);
55	duarte	81	setPassword(password);
56			// For nCon we create a connection to the master too.
57			// This is just a place holder because the actual node connection is not created until we create the statement
58			// 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
59			this.nCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
60			this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
61	duarte	15	}
62
63			public void close() {
64	duarte	81	this.nCon.close();
65			this.mCon.close();
66	duarte	15	}
67
68	duarte	95	public <T> String getHost4Idx (T idx) {
69	duarte	15	String host="";
70	duarte	81	String query = "SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
71	duarte	95	"ON (m.client_id=c.client_id) WHERE "+key+"='"+idx+"';";
72	duarte	81	host=this.mCon.getStringFromDb(query);
73	duarte	15	return host;
74			}
75
76	duarte	202	public <T> void setHostFromIdx(T idx) throws SQLException{
77	duarte	15	setHost(getHost4Idx(idx));
78			}
79
80	duarte	202	public void setHost(String host) throws SQLException {
81	duarte	15	this.host=host;
82	duarte	81	//Closing previous connection is essential
83			//If we don't close it a lot of connections stay open after using a ClusterConnection object for a while
84			this.nCon.close();
85			this.nCon = new MySQLConnection(host,user,password,db);
86	duarte	15	}
87	duarte	35
88	duarte	34	/**
89			* This method is strictly private. We shouldn't call this from another class as a key might not be set when we call it
90			* and thus we can't get the client_id from the master key table. Only to be called from createStatement(key,idx)
91	duarte	35	* @param idx the value of the id for a certain key already set
92			* @return a Stament object with a connection to the node that contains idx for key
93	duarte	202	* @throws SQLException
94	duarte	34	*/
95	duarte	202	private <T> Statement createStatement(T idx) throws SQLException { // to use when the field "key" is already set
96	duarte	34	setKeyTable();
97	duarte	15	Statement S=null;
98			this.setHostFromIdx(idx);
99			try {
100			S=this.nCon.createStatement();
101			}
102			catch (SQLException e){
103	duarte	33	System.err.println("SQLException: " + e.getMessage());
104			System.err.println("SQLState: " + e.getSQLState());
105			System.err.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
106	duarte	15	System.exit(2);
107			}
108			return S;
109			}
110	duarte	35
111	duarte	34	/**
112	duarte	35	* This method is used to create a statement passing the key and idx. It will create a connection the the right node
113			* and return a Statement for that connection
114			* @param idx the key name
115			* @param idx the id value for that key
116			* @return a Statement object with a connection to the node that contains idx for key
117	duarte	202	* @throws SQLException
118	duarte	34	*/
119	duarte	202	public <T> Statement createStatement(String key,T idx) throws SQLException {
120	duarte	15	setKey(key);
121			return createStatement(idx);
122			}
123
124	duarte	35	/**
125	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
126			* @param query the SQL query
127			* @param key the name of the key
128			* @param idx the id value for that key
129			*/
130	duarte	95	public <T> void executeSql(String query,String key, T idx) throws SQLException {
131	duarte	43	Statement stmt;
132	duarte	81	stmt = this.createStatement(key,idx);
133			stmt.execute(query);
134			stmt.close();
135	duarte	43	}
136	duarte	81
137	duarte	51	/**
138			* To change the MASTERDB String, i.e. the name of the key master database. To be used in testing.
139			* @param db the name of the key master db we want to use instead of the default defined in the MASTERDB field
140	duarte	202	* @throws SQLException
141	duarte	51	*/
142	duarte	202	public void setKeyDb(String db) throws SQLException {
143	duarte	51	this.MASTERDB=db;
144	duarte	81	//Closing previous connection is essential
145			this.mCon.close();
146			this.mCon = new MySQLConnection(MASTERHOST,user,password,MASTERDB);
147	duarte	51	}
148	duarte	43
149			/**
150	duarte	72	* To set keyTable field in constructor (i.e. first time). Only to be used in constructor.
151	duarte	35	* @param db the database name
152			*/
153			public void setKeyTable(String db) {
154	duarte	72	String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+db+"\' AND key_name=\'"+this.key+"\';";
155	duarte	81	this.keyTable=this.mCon.getStringFromDb(query);
156	duarte	15	}
157
158	duarte	35	/**
159	duarte	43	* To set the keyTable field when db is already set
160	duarte	72	* The value of keyTable is taken from the dbs_keys table in the database given the db and key.
161	duarte	35	*/
162			public void setKeyTable() {
163	duarte	72	String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+this.db+"\' AND key_name=\'"+this.key+"\';";
164	duarte	81	this.keyTable=this.mCon.getStringFromDb(query);
165	duarte	15	}
166
167	duarte	34	public String getKeyTable() {
168	duarte	15	return this.keyTable;
169			}
170
171	duarte	72	/**
172			* 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
173			* @return
174			*/
175			public String getTableOnNode(){
176			String table="";
177			if (this.keyTable.contains("__")) {
178			String[] tokens=this.keyTable.split("__");
179			table=tokens[1];
180			}
181			else {
182			System.err.println("Error! The keyTable field is not set in this ClusterConnection object.");
183			}
184			return table;
185	duarte	15	}
186	duarte	72
187	duarte	15	public void setUser(String user) {
188			this.user=user;
189			}
190
191			public void setPassword(String password) {
192			this.password=password;
193			}
194
195			public void setDb(String db){
196			this.db=db;
197			}
198
199			public void setKey(String key){
200			this.key=key;
201	duarte	34	setKeyTable();
202	duarte	15	}
203
204			public Statement createMasterStatement() {
205			Statement S=null;
206			try {
207			S=this.mCon.createStatement();
208			}
209			catch (SQLException e){
210	duarte	33	System.err.println("SQLException: " + e.getMessage());
211			System.err.println("SQLState: " + e.getSQLState());
212			System.err.println("Couldn't create statement for the master connection, exiting.");
213	duarte	15	System.exit(2);
214			}
215			return S;
216			}
217	duarte	74
218	duarte	95	public Object[] getAllIdxFromMaster(String key) {
219	duarte	15	this.setKey(key);
220	duarte	95	Object[] ids=null;
221			ArrayList<Object> idsAL=new ArrayList<Object>();
222	duarte	67	try {
223	duarte	72	String query="SELECT "+key+" FROM "+keyTable+";";
224	duarte	67	Statement S=this.mCon.createStatement();
225			ResultSet R=S.executeQuery(query);
226			while (R.next()){
227			idsAL.add(R.getInt(1));
228			}
229			R.close();
230			S.close();
231	duarte	15	}
232			catch (SQLException e){
233	duarte	33	System.err.println("SQLException: " + e.getMessage());
234			System.err.println("SQLState: " + e.getSQLState());
235	duarte	72	System.err.println("Couldn't get all indices from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
236	duarte	15	System.exit(2);
237			}
238	duarte	95	ids=new Object[idsAL.size()];
239	duarte	67	for (int i=0;i<idsAL.size();i++){
240			ids[i]=idsAL.get(i);
241			}
242			return ids;
243	duarte	15	}
244	duarte	95
245	duarte	35	/**
246	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
247			* @param key the name of the key
248	duarte	67	* @return HashMap with keys = indices, and values = node names where the corresponding index is stored
249	duarte	38	*/
250	duarte	95	public HashMap<Object,String> getAllIdxAndClients (String key){
251	duarte	38	this.setKey(key);
252	duarte	95	HashMap<Object,String> idsAndClients=new HashMap<Object,String>();
253	duarte	38	try {
254	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);";
255	duarte	67	Statement S=this.mCon.createStatement();
256			ResultSet R=S.executeQuery(query);
257			while (R.next()){
258	duarte	95	idsAndClients.put(R.getString(1),R.getString(2));
259	duarte	67	}
260			R.close();
261			S.close();
262	duarte	38	}
263			catch (SQLException e){
264			System.err.println("SQLException: " + e.getMessage());
265			System.err.println("SQLState: " + e.getSQLState());
266			System.err.println("Couldn't get all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
267			System.exit(2);
268			}
269	duarte	67	return idsAndClients;
270	duarte	38	}
271
272			/**
273	duarte	35	* To get client_id for a certain idx and key
274			* @param key the key name
275	duarte	95	* @param idx the id value for that key (either a String or an int)
276	duarte	35	* @return the client_id for node that has the data for the idx for that key
277			*/
278	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
279	duarte	95	public <T> int getHostId4Idx (String key,T idx) {
280	duarte	15	int hostId=0;
281			this.setKey(key);
282			String query;
283			int countCids=0;
284	duarte	95	query="SELECT count(client_id) FROM "+keyTable+" WHERE "+key+"='"+idx+"';";
285	duarte	81	countCids=this.mCon.getIntFromDb(query);
286			if (countCids!=1){
287			System.err.println("the query was: "+query);
288			System.err.println("Error! the count of client_ids for idx "+key+"= "+idx+" is " +countCids+
289			". It must be 1! The values were taken from host: "+MASTERHOST+", database: "+MASTERDB+", table: "+keyTable+". Check what's wrong! Exiting now.");
290			System.exit(2);
291	duarte	15	}
292	duarte	81	else {
293	duarte	95	query="SELECT client_id FROM "+keyTable+" WHERE "+key+"='"+idx+"';";
294	duarte	81	hostId=this.mCon.getIntFromDb(query);
295	duarte	15	}
296			return hostId;
297			}
298
299	duarte	95	// next 4 methods here refer purely to numeric keys, won't change them to be general text/numeric
300	duarte	15	public void insertIdxInMaster(String key, int clientId) {
301			String query;
302			this.setKey(key);
303			try {
304			query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
305	duarte	81	this.mCon.executeSql(query);
306	duarte	15	}
307			catch (SQLException E) {
308	duarte	33	System.err.println("SQLException: " + E.getMessage());
309			System.err.println("SQLState: " + E.getSQLState());
310	duarte	72	System.err.println("Couldn't insert new "+this.key+" in master table "+this.getKeyTable()+". The client_id for it was "+clientId+". Exiting.");
311	duarte	15	System.exit(2);
312			}
313			}
314
315			public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
316			this.setKey(keySrc);
317			int clientId=0;
318			clientId=this.getHostId4Idx(keySrc,idxSrc);
319			insertIdxInMaster(keyDest,clientId);
320			}
321
322			public int getLastInsertId(String key) {
323			int lastIdx=0;
324			this.setKey(key);
325			String query = "";
326	duarte	81	query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
327			lastIdx=this.mCon.getIntFromDb(query);
328	duarte	15	return lastIdx;
329	duarte	81	}
330	duarte	21
331			public int[][] getIdxSet(String key) {
332			int[][] indMatrix=null;
333			this.setKey(key);
334			String query;
335			Statement S;
336			ResultSet R;
337			try {
338	duarte	81	// STEP 1 -- getting set of all client_ids
339	duarte	21	query="SELECT count(distinct client_id) FROM "+keyTable+";";
340			int count=0;
341	duarte	81	count=this.mCon.getIntFromDb(query);
342			S=this.mCon.createStatement();
343	duarte	21	query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
344			R=S.executeQuery(query);
345
346			// STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field
347			int[] clids=new int[count]; //array to store all client_ids. To be used in loops later
348			int i=0;
349			while (R.next()){
350			Statement Sloop=this.mCon.createStatement();
351			int clid=R.getInt(1);
352	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));";
353	duarte	21	Sloop.executeUpdate(query);
354	duarte	72	query="INSERT INTO c_"+clid+" ("+key+",client_id) SELECT "+key+",client_id FROM "+keyTable+" WHERE client_id="+clid+";";
355	duarte	21	Sloop.executeUpdate(query);
356			clids[i]=clid;
357			i++;
358			Sloop.close();
359			}
360
361			// STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count
362			//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;";
363			query="DROP TABLE IF EXISTS tmp_allcs;";
364			S.executeUpdate(query);
365			//this table must be permanent! otherwise cannot do the select max(c) later
366			query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;";
367			S.executeUpdate(query);
368			String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id";
369			for (i=1;i<clids.length;i++) {
370			unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id";
371			}
372			query="INSERT INTO tmp_allcs "+unionStr+";";
373			S.executeUpdate(query);
374			query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);";
375			R=S.executeQuery(query);
376			int clidMaxIdxCount=0;
377			int maxIdxCount=0;
378			if (R.next()) {
379			clidMaxIdxCount=R.getInt(1);
380			maxIdxCount=R.getInt(2);
381			}
382			query="DROP TABLE tmp_allcs;";
383			S.executeUpdate(query);
384
385			// 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
386			//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);";
387	duarte	72	String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+key+" AS c_"+clidMaxIdxCount;
388	duarte	21	String fromStr="c_"+clidMaxIdxCount;
389			for (i=0;i<clids.length;i++) {
390			if (clids[i]!=clidMaxIdxCount){
391	duarte	72	selectStr+=", c_"+clids[i]+"."+key+" AS c_"+clids[i];
392	duarte	21	fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)";
393			}
394			}
395			query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";";
396			S.executeUpdate(query);
397
398			// STEP 5 -- put the table into a 2-dimensional array and return it
399			indMatrix = new int[maxIdxCount][clids.length];
400			query="SELECT * FROM indices_matrix";
401			R=S.executeQuery(query);
402			i=0;
403			while (R.next()) {
404			for (int j=0;j<clids.length;j++){
405	duarte	22	indMatrix[i][j]=R.getInt(j+2);
406	duarte	21	}
407			i++;
408			}
409			R.close();
410			S.close();
411			}
412			catch (SQLException e){
413	duarte	33	System.err.println("SQLException: " + e.getMessage());
414			System.err.println("SQLState: " + e.getSQLState());
415	duarte	72	System.err.println("Couldn't get the indices set from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting.");
416	duarte	21	System.exit(2);
417			}
418			return indMatrix;
419			}
420
421
422	duarte	15	}