1 |
package tools; |
2 |
import java.sql.*; |
3 |
|
4 |
/** |
5 |
* ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs |
6 |
* @author Jose Duarte |
7 |
*/ |
8 |
//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 |
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 |
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 |
loadMySQLDriver(); |
38 |
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 |
System.out.println("SQLException: " + e.getMessage()); |
50 |
System.out.println("SQLState: " + e.getSQLState()); |
51 |
System.out.println("VendorError: " + e.getErrorCode()); |
52 |
System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting."); |
53 |
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 |
System.out.println(e.getMessage()); |
64 |
System.out.println("An exception occurred while loading the mysql jdbc driver, exiting."); |
65 |
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 |
System.out.println("Couldn't close database connections for master: "+masterHost+" and node: "+this.host+", exiting."); |
75 |
System.out.println("SQLException: " + e.getMessage()); |
76 |
System.out.println("SQLState: " + e.getSQLState()); |
77 |
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 |
System.out.println("Couldn't get the host name for idx "+idx+", exiting"); |
98 |
System.out.println("SQLException: " + e.getMessage()); |
99 |
System.out.println("SQLState: " + e.getSQLState()); |
100 |
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 |
//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 |
this.nCon=DriverManager.getConnection(url+host+"/"+db,user,password); |
116 |
} |
117 |
catch (SQLException e){ |
118 |
System.out.println("SQLException: " + e.getMessage()); |
119 |
System.out.println("SQLState: " + e.getSQLState()); |
120 |
System.out.println("Couldn't get connection to node "+host+", exiting."); |
121 |
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 |
System.out.println("SQLException: " + e.getMessage()); |
135 |
System.out.println("SQLState: " + e.getSQLState()); |
136 |
System.out.println("Couldn't create statement for the node connection, idx= "+idx+", exiting."); |
137 |
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 |
System.out.println("SQLException: " + e.getMessage()); |
188 |
System.out.println("SQLState: " + e.getSQLState()); |
189 |
System.out.println("Couldn't create statement for the master connection, exiting."); |
190 |
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 |
System.out.println("SQLException: " + e.getMessage()); |
208 |
System.out.println("SQLState: " + e.getSQLState()); |
209 |
System.out.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting."); |
210 |
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 |
System.out.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. Check what's wrong! Exiting now."); |
233 |
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 |
System.out.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting"); |
247 |
System.out.println("SQLException: " + e.getMessage()); |
248 |
System.out.println("SQLState: " + e.getSQLState()); |
249 |
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 |
System.out.println("SQLException: " + E.getMessage()); |
266 |
System.out.println("SQLState: " + E.getSQLState()); |
267 |
System.out.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getMasterTable()+". The client_id for it was "+clientId+". Exiting."); |
268 |
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 |
System.out.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting"); |
297 |
System.out.println("SQLException: " + E.getMessage()); |
298 |
System.out.println("SQLState: " + E.getSQLState()); |
299 |
System.exit(3); |
300 |
} // end try/catch connection |
301 |
return lastIdx; |
302 |
} // end getGraphId |
303 |
|
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 |
indMatrix[i][j]=R.getInt(j+2); |
384 |
} |
385 |
i++; |
386 |
} |
387 |
R.close(); |
388 |
S.close(); |
389 |
} |
390 |
catch (SQLException e){ |
391 |
System.out.println("SQLException: " + e.getMessage()); |
392 |
System.out.println("SQLState: " + e.getSQLState()); |
393 |
System.out.println("Couldn't get the indices set from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting."); |
394 |
System.exit(2); |
395 |
} |
396 |
return indMatrix; |
397 |
} |
398 |
|
399 |
|
400 |
} |