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