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
 */
//TODO Maybe we should always pass the key for CreateStatement and the constructor and drop the 2 methods without key.
//TODO I think that should make things easier. To have to remember to be switching keys is not ideal, I'd rather pass it always as a parameter in CreateStatement
//TODO The only problem with this approach is when constructing the object the key must be passed. That shouldn't be a major problem anyway. Revise all this

public class ClusterConnection {

        private final String url= "jdbc:mysql://";
        private final 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. Then we need to call createNConStatement only passing the idx as argument.
        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();
                setMasterTable(db);
        }

        // create a cluster connection with an empty key field. In this case we need to call createNConStatement passing the key as argument 
        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+", exiting.");
                        System.exit(2);                                         
                }
        }
        
        public Statement createStatement(int idx) { // to use when the field "key" is already set
                setMasterTable();
                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;
        }
        
        public Statement createStatement(String key,int idx) {
                setKey(key);
                return createStatement(idx);
        }
        
        public void setMasterTable(String db) { // to set masterTable field in constructor (i.e. first time)
                this.keyTable=db+"_"+this.key+"_list_master";
        }
        
        public void setMasterTable() { // to set masterTable field when db is already set 
                this.keyTable=this.db+"_"+this.key+"_list_master";
        }
        
        public String getMasterTable() {
                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;
                setMasterTable();
                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 client_id for a certain idx and key
        //TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
        public int getHostId4Idx (String key,int idx) { 
                int hostId=0;
                this.setKey(key);
                Statement S;
                ResultSet R;
                String query;
                int countCids=0;
                try {
                        S=mCon.createStatement();
                        query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
                        R=S.executeQuery(query);
                        if (R.next()){
                                countCids=R.getInt(1);
                        }
                        if (countCids!=1){
                                System.err.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. 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.getMasterTable()+". 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:	33
Committed:	Wed Mar 15 11:58:24 2006 UTC (18 years, 6 months ago) by duarte
File size:	13600 byte(s)
Log Message:	Changed all error printing in catches to STDERR instead of STDOUT
Line	User	Rev	File contents
1	duarte	16	package tools;
2	duarte	15	import java.sql.*;
3
4			/**
5			* ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs
6			* @author Jose Duarte
7			*/
8			//TODO Maybe we should always pass the key for CreateStatement and the constructor and drop the 2 methods without key.
9			//TODO I think that should make things easier. To have to remember to be switching keys is not ideal, I'd rather pass it always as a parameter in CreateStatement
10			//TODO The only problem with this approach is when constructing the object the key must be passed. That shouldn't be a major problem anyway. Revise all this
11
12			public class ClusterConnection {
13
14			private final String url= "jdbc:mysql://";
15			private final String masterDb="key_master";
16			private final String masterHost="white";
17			private Connection nCon;
18			private Connection mCon;
19			public String keyTable;
20			public String key;
21			public String idxColumn;
22			public String host;
23			public String db;
24			private String user;
25			private String password;
26
27			//create a ClusterConnection passing a key. Then we need to call createNConStatement only passing the idx as argument.
28			public ClusterConnection (String db,String key, String user,String password) {
29	duarte	17	new ClusterConnection(db,user,password);
30			this.key=key; //can't use the setKey method here before we've got the db field initialized
31	duarte	15	setIdxColumn();
32			setMasterTable(db);
33			}
34
35			// create a cluster connection with an empty key field. In this case we need to call createNConStatement passing the key as argument
36			public ClusterConnection (String db, String user,String password) {
37	duarte	17	loadMySQLDriver();
38	duarte	15	setDb(db);
39			setUser(user);
40			setPassword(password);
41			try {
42			// For nCon we create a connection to the master too.
43			// This is just a place holder because the actual node connection is not created until we create the statement
44			// 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
45			this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
46			this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
47			}
48			catch(SQLException e){
49	duarte	33	System.err.println("SQLException: " + e.getMessage());
50			System.err.println("SQLState: " + e.getSQLState());
51			System.err.println("VendorError: " + e.getErrorCode());
52			System.err.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting.");
53	duarte	15	System.exit(2);
54			}
55			}
56
57
58			public void loadMySQLDriver() {
59			try {
60			Class.forName("com.mysql.jdbc.Driver");
61			}
62			catch(Exception e) {
63	duarte	33	System.err.println(e.getMessage());
64			System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
65	duarte	15	System.exit(1);
66			}
67			}
68			public void close() {
69			try {
70			this.nCon.close();
71			this.mCon.close();
72			}
73			catch(SQLException e) {
74	duarte	33	System.err.println("Couldn't close database connections for master: "+masterHost+" and node: "+this.host+", exiting.");
75			System.err.println("SQLException: " + e.getMessage());
76			System.err.println("SQLState: " + e.getSQLState());
77	duarte	15	System.exit(3);
78			}
79			}
80
81			public String getHost4Idx (int idx) {
82			String host="";
83			Statement S;
84			ResultSet R;
85			try {
86			S=mCon.createStatement();
87			String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
88			"ON (m.client_id=c.client_id) WHERE "+idxColumn+"="+idx+";";
89			R=S.executeQuery(query);
90			if (R.next()){
91			host=R.getString(1);
92			}
93			S.close();
94			R.close();
95			}
96			catch(SQLException e) {
97	duarte	33	System.err.println("Couldn't get the host name for idx "+idx+", exiting");
98			System.err.println("SQLException: " + e.getMessage());
99			System.err.println("SQLState: " + e.getSQLState());
100	duarte	15	System.exit(3);
101			}
102			return host;
103			}
104
105			public void setHostFromIdx(int idx){
106			setHost(getHost4Idx(idx));
107			}
108
109			public void setHost(String host) {
110			this.host=host;
111			try {
112	duarte	23	//Closing previous connection is essential
113			//If we don't close it a lot of connections stay open after using a ClusterConnection object for a while
114			this.nCon.close();
115	duarte	15	this.nCon=DriverManager.getConnection(url+host+"/"+db,user,password);
116			}
117			catch (SQLException e){
118	duarte	33	System.err.println("SQLException: " + e.getMessage());
119			System.err.println("SQLState: " + e.getSQLState());
120			System.err.println("Couldn't get connection to node "+host+", exiting.");
121	duarte	15	System.exit(2);
122			}
123			}
124
125			public Statement createStatement(int idx) { // to use when the field "key" is already set
126			setMasterTable();
127			setIdxColumn();
128			Statement S=null;
129			this.setHostFromIdx(idx);
130			try {
131			S=this.nCon.createStatement();
132			}
133			catch (SQLException e){
134	duarte	33	System.err.println("SQLException: " + e.getMessage());
135			System.err.println("SQLState: " + e.getSQLState());
136			System.err.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
137	duarte	15	System.exit(2);
138			}
139			return S;
140			}
141
142			public Statement createStatement(String key,int idx) {
143			setKey(key);
144			return createStatement(idx);
145			}
146
147			public void setMasterTable(String db) { // to set masterTable field in constructor (i.e. first time)
148			this.keyTable=db+"_"+this.key+"_list_master";
149			}
150
151			public void setMasterTable() { // to set masterTable field when db is already set
152			this.keyTable=this.db+"_"+this.key+"_list_master";
153			}
154
155			public String getMasterTable() {
156			return this.keyTable;
157			}
158
159			public void setIdxColumn() {
160			this.idxColumn=this.key+"_id";
161			}
162
163			public void setUser(String user) {
164			this.user=user;
165			}
166
167			public void setPassword(String password) {
168			this.password=password;
169			}
170
171			public void setDb(String db){
172			this.db=db;
173			}
174
175			public void setKey(String key){
176			this.key=key;
177			setMasterTable();
178			setIdxColumn();
179			}
180
181			public Statement createMasterStatement() {
182			Statement S=null;
183			try {
184			S=this.mCon.createStatement();
185			}
186			catch (SQLException e){
187	duarte	33	System.err.println("SQLException: " + e.getMessage());
188			System.err.println("SQLState: " + e.getSQLState());
189			System.err.println("Couldn't create statement for the master connection, exiting.");
190	duarte	15	System.exit(2);
191			}
192			return S;
193			}
194
195			public ResultSet getAllIdxFromMaster(String key) {
196			this.setKey(key);
197			String query;
198			Statement S;
199			ResultSet R=null;
200			try {
201			query="SELECT "+idxColumn+" FROM "+keyTable+";";
202			S=this.mCon.createStatement();
203			R=S.executeQuery(query);
204			//S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
205			}
206			catch (SQLException e){
207	duarte	33	System.err.println("SQLException: " + e.getMessage());
208			System.err.println("SQLState: " + e.getSQLState());
209			System.err.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting.");
210	duarte	15	System.exit(2);
211			}
212			return R;
213			}
214
215			// to get client_id for a certain idx and key
216			//TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
217			public int getHostId4Idx (String key,int idx) {
218			int hostId=0;
219			this.setKey(key);
220			Statement S;
221			ResultSet R;
222			String query;
223			int countCids=0;
224			try {
225			S=mCon.createStatement();
226			query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
227			R=S.executeQuery(query);
228			if (R.next()){
229			countCids=R.getInt(1);
230			}
231			if (countCids!=1){
232	duarte	33	System.err.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. Check what's wrong! Exiting now.");
233	duarte	15	System.exit(2);
234			}
235			else {
236			query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
237			R=S.executeQuery(query);
238			if (R.next()){
239			hostId=R.getInt(1);
240			}
241			}
242			S.close();
243			R.close();
244			}
245			catch(SQLException e) {
246	duarte	33	System.err.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting");
247			System.err.println("SQLException: " + e.getMessage());
248			System.err.println("SQLState: " + e.getSQLState());
249	duarte	15	System.exit(3);
250			}
251			return hostId;
252			}
253
254			public void insertIdxInMaster(String key, int clientId) {
255			Statement S;
256			String query;
257			this.setKey(key);
258			try {
259			S=this.mCon.createStatement();
260			query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
261			S.executeUpdate(query);
262			S.close();
263			}
264			catch (SQLException E) {
265	duarte	33	System.err.println("SQLException: " + E.getMessage());
266			System.err.println("SQLState: " + E.getSQLState());
267			System.err.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getMasterTable()+". The client_id for it was "+clientId+". Exiting.");
268	duarte	15	System.exit(2);
269			}
270			}
271
272			public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
273			this.setKey(keySrc);
274			int clientId=0;
275			clientId=this.getHostId4Idx(keySrc,idxSrc);
276			insertIdxInMaster(keyDest,clientId);
277			}
278
279			public int getLastInsertId(String key) {
280			int lastIdx=0;
281			this.setKey(key);
282			Statement S;
283			ResultSet R;
284			String query = "";
285			try {
286			S = this.mCon.createStatement();
287			query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
288			R = S.executeQuery(query);
289			if (R.next()) {
290			lastIdx=R.getInt(1);
291			}
292			R.close();
293			S.close();
294			}
295			catch (SQLException E) {
296	duarte	33	System.err.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting");
297			System.err.println("SQLException: " + E.getMessage());
298			System.err.println("SQLState: " + E.getSQLState());
299	duarte	15	System.exit(3);
300			} // end try/catch connection
301			return lastIdx;
302			} // end getGraphId
303	duarte	21
304			public int[][] getIdxSet(String key) {
305			int[][] indMatrix=null;
306			this.setKey(key);
307			String query;
308			Statement S;
309			ResultSet R;
310			try {
311			// STEP 1 -- getting set of all client_ids
312			S=this.mCon.createStatement();
313			query="SELECT count(distinct client_id) FROM "+keyTable+";";
314			int count=0;
315			R=S.executeQuery(query);
316			if (R.next()){
317			count=R.getInt(1);
318			}
319			query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;";
320			//R.close();
321			//S.close();
322			R=S.executeQuery(query);
323
324			// STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field
325			int[] clids=new int[count]; //array to store all client_ids. To be used in loops later
326			int i=0;
327			while (R.next()){
328			Statement Sloop=this.mCon.createStatement();
329			int clid=R.getInt(1);
330			query="CREATE TEMPORARY TABLE c_"+clid+" (serial int(11) NOT NULL AUTO_INCREMENT,"+idxColumn+" int(11),client_id int(11), PRIMARY KEY(serial));";
331			Sloop.executeUpdate(query);
332			query="INSERT INTO c_"+clid+" ("+idxColumn+",client_id) SELECT "+idxColumn+",client_id FROM "+keyTable+" WHERE client_id="+clid+";";
333			Sloop.executeUpdate(query);
334			clids[i]=clid;
335			i++;
336			Sloop.close();
337			}
338
339			// STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count
340			//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;";
341			query="DROP TABLE IF EXISTS tmp_allcs;";
342			S.executeUpdate(query);
343			//this table must be permanent! otherwise cannot do the select max(c) later
344			query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;";
345			S.executeUpdate(query);
346			String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id";
347			for (i=1;i<clids.length;i++) {
348			unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id";
349			}
350			query="INSERT INTO tmp_allcs "+unionStr+";";
351			S.executeUpdate(query);
352			query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);";
353			R=S.executeQuery(query);
354			int clidMaxIdxCount=0;
355			int maxIdxCount=0;
356			if (R.next()) {
357			clidMaxIdxCount=R.getInt(1);
358			maxIdxCount=R.getInt(2);
359			}
360			query="DROP TABLE tmp_allcs;";
361			S.executeUpdate(query);
362
363			// 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
364			//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);";
365			String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+idxColumn+" AS c_"+clidMaxIdxCount;
366			String fromStr="c_"+clidMaxIdxCount;
367			for (i=0;i<clids.length;i++) {
368			if (clids[i]!=clidMaxIdxCount){
369			selectStr+=", c_"+clids[i]+"."+idxColumn+" AS c_"+clids[i];
370			fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)";
371			}
372			}
373			query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";";
374			S.executeUpdate(query);
375
376			// STEP 5 -- put the table into a 2-dimensional array and return it
377			indMatrix = new int[maxIdxCount][clids.length];
378			query="SELECT * FROM indices_matrix";
379			R=S.executeQuery(query);
380			i=0;
381			while (R.next()) {
382			for (int j=0;j<clids.length;j++){
383	duarte	22	indMatrix[i][j]=R.getInt(j+2);
384	duarte	21	}
385			i++;
386			}
387			R.close();
388			S.close();
389			}
390			catch (SQLException e){
391	duarte	33	System.err.println("SQLException: " + e.getMessage());
392			System.err.println("SQLState: " + e.getSQLState());
393			System.err.println("Couldn't get the indices set from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting.");
394	duarte	21	System.exit(2);
395			}
396			return indMatrix;
397			}
398
399
400	duarte	15	}