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 |
34 |
private final String URL= "jdbc:mysql://"; |
14 |
|
|
private String MASTERDB="key_master"; |
15 |
|
|
private final String MASTERHOST="white"; |
16 |
duarte |
15 |
private Connection nCon; |
17 |
|
|
private Connection mCon; |
18 |
|
|
public String keyTable; |
19 |
|
|
public String key; |
20 |
|
|
public String host; |
21 |
|
|
public String db; |
22 |
|
|
private String user; |
23 |
|
|
private String password; |
24 |
duarte |
34 |
|
25 |
|
|
/** |
26 |
|
|
* Create a ClusterConnection passing a key. |
27 |
duarte |
35 |
* @param db the database name |
28 |
duarte |
72 |
* @param key the key name: e.g. asu_id |
29 |
duarte |
35 |
* @param user the user name for connection to both master and nodes |
30 |
|
|
* @param password the password for connection to both master and nodes |
31 |
duarte |
34 |
*/ |
32 |
duarte |
15 |
public ClusterConnection (String db,String key, String user,String password) { |
33 |
duarte |
72 |
loadMySQLDriver(); |
34 |
|
|
setDb(db); |
35 |
|
|
setUser(user); |
36 |
|
|
setPassword(password); |
37 |
|
|
try { |
38 |
|
|
// For nCon we create a connection to the master too. |
39 |
|
|
// This is just a place holder because the actual node connection is not created until we create the statement |
40 |
|
|
// 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 |
41 |
|
|
this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password); |
42 |
|
|
this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password); |
43 |
|
|
} |
44 |
|
|
catch(SQLException e){ |
45 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
46 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
47 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
48 |
|
|
System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting."); |
49 |
|
|
System.exit(2); |
50 |
|
|
} |
51 |
duarte |
17 |
this.key=key; //can't use the setKey method here before we've got the db field initialized |
52 |
duarte |
34 |
setKeyTable(db); |
53 |
duarte |
15 |
} |
54 |
|
|
|
55 |
duarte |
34 |
/** |
56 |
|
|
* Create a ClusterConnection without passing a key. The key will be set later when we call createStatement(key,idx) |
57 |
duarte |
35 |
* @param db the database name |
58 |
|
|
* @param user the user name for connection to both master and nodes |
59 |
|
|
* @param password the password for connection to both master and nodes |
60 |
duarte |
34 |
*/ |
61 |
duarte |
15 |
public ClusterConnection (String db, String user,String password) { |
62 |
duarte |
17 |
loadMySQLDriver(); |
63 |
duarte |
15 |
setDb(db); |
64 |
|
|
setUser(user); |
65 |
|
|
setPassword(password); |
66 |
|
|
try { |
67 |
|
|
// For nCon we create a connection to the master too. |
68 |
|
|
// This is just a place holder because the actual node connection is not created until we create the statement |
69 |
|
|
// 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 |
70 |
duarte |
34 |
this.nCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password); |
71 |
|
|
this.mCon = DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password); |
72 |
duarte |
15 |
} |
73 |
|
|
catch(SQLException e){ |
74 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
75 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
76 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
77 |
duarte |
34 |
System.err.println("Couldn't get connection to master host "+MASTERHOST+", db="+MASTERDB+", exiting."); |
78 |
duarte |
15 |
System.exit(2); |
79 |
|
|
} |
80 |
|
|
} |
81 |
|
|
|
82 |
|
|
public void loadMySQLDriver() { |
83 |
|
|
try { |
84 |
duarte |
72 |
Class.forName("com.mysql.jdbc.Driver").newInstance(); |
85 |
duarte |
15 |
} |
86 |
|
|
catch(Exception e) { |
87 |
duarte |
33 |
System.err.println(e.getMessage()); |
88 |
|
|
System.err.println("An exception occurred while loading the mysql jdbc driver, exiting."); |
89 |
duarte |
15 |
System.exit(1); |
90 |
|
|
} |
91 |
|
|
} |
92 |
|
|
public void close() { |
93 |
|
|
try { |
94 |
|
|
this.nCon.close(); |
95 |
|
|
this.mCon.close(); |
96 |
|
|
} |
97 |
|
|
catch(SQLException e) { |
98 |
duarte |
34 |
System.err.println("Couldn't close database connections for master: "+MASTERHOST+" and node: "+this.host+", exiting."); |
99 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
100 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
101 |
duarte |
15 |
System.exit(3); |
102 |
|
|
} |
103 |
|
|
} |
104 |
|
|
|
105 |
|
|
public String getHost4Idx (int idx) { |
106 |
|
|
String host=""; |
107 |
|
|
Statement S; |
108 |
|
|
ResultSet R; |
109 |
|
|
try { |
110 |
|
|
S=mCon.createStatement(); |
111 |
|
|
String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+ |
112 |
duarte |
72 |
"ON (m.client_id=c.client_id) WHERE "+key+"="+idx+";"; |
113 |
duarte |
15 |
R=S.executeQuery(query); |
114 |
|
|
if (R.next()){ |
115 |
|
|
host=R.getString(1); |
116 |
|
|
} |
117 |
|
|
S.close(); |
118 |
|
|
R.close(); |
119 |
|
|
} |
120 |
|
|
catch(SQLException e) { |
121 |
duarte |
33 |
System.err.println("Couldn't get the host name for idx "+idx+", exiting"); |
122 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
123 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
124 |
duarte |
15 |
System.exit(3); |
125 |
|
|
} |
126 |
|
|
return host; |
127 |
|
|
} |
128 |
|
|
|
129 |
|
|
public void setHostFromIdx(int idx){ |
130 |
|
|
setHost(getHost4Idx(idx)); |
131 |
|
|
} |
132 |
|
|
|
133 |
|
|
public void setHost(String host) { |
134 |
|
|
this.host=host; |
135 |
|
|
try { |
136 |
duarte |
23 |
//Closing previous connection is essential |
137 |
|
|
//If we don't close it a lot of connections stay open after using a ClusterConnection object for a while |
138 |
|
|
this.nCon.close(); |
139 |
duarte |
34 |
this.nCon=DriverManager.getConnection(URL+host+"/"+db,user,password); |
140 |
duarte |
15 |
} |
141 |
|
|
catch (SQLException e){ |
142 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
143 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
144 |
duarte |
37 |
System.err.println("Couldn't get connection to node "+host+", database "+db+", exiting."); |
145 |
duarte |
15 |
System.exit(2); |
146 |
|
|
} |
147 |
|
|
} |
148 |
duarte |
35 |
|
149 |
duarte |
34 |
/** |
150 |
|
|
* This method is strictly private. We shouldn't call this from another class as a key might not be set when we call it |
151 |
|
|
* and thus we can't get the client_id from the master key table. Only to be called from createStatement(key,idx) |
152 |
duarte |
35 |
* @param idx the value of the id for a certain key already set |
153 |
|
|
* @return a Stament object with a connection to the node that contains idx for key |
154 |
duarte |
34 |
*/ |
155 |
|
|
private Statement createStatement(int idx) { // to use when the field "key" is already set |
156 |
|
|
setKeyTable(); |
157 |
duarte |
15 |
Statement S=null; |
158 |
|
|
this.setHostFromIdx(idx); |
159 |
|
|
try { |
160 |
|
|
S=this.nCon.createStatement(); |
161 |
|
|
} |
162 |
|
|
catch (SQLException e){ |
163 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
164 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
165 |
|
|
System.err.println("Couldn't create statement for the node connection, idx= "+idx+", exiting."); |
166 |
duarte |
15 |
System.exit(2); |
167 |
|
|
} |
168 |
|
|
return S; |
169 |
|
|
} |
170 |
duarte |
35 |
|
171 |
duarte |
34 |
/** |
172 |
duarte |
35 |
* This method is used to create a statement passing the key and idx. It will create a connection the the right node |
173 |
|
|
* and return a Statement for that connection |
174 |
|
|
* @param idx the key name |
175 |
|
|
* @param idx the id value for that key |
176 |
|
|
* @return a Statement object with a connection to the node that contains idx for key |
177 |
duarte |
34 |
*/ |
178 |
duarte |
15 |
public Statement createStatement(String key,int idx) { |
179 |
|
|
setKey(key); |
180 |
|
|
return createStatement(idx); |
181 |
|
|
} |
182 |
|
|
|
183 |
duarte |
35 |
/** |
184 |
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 |
185 |
|
|
* @param query the SQL query |
186 |
|
|
* @param key the name of the key |
187 |
|
|
* @param idx the id value for that key |
188 |
|
|
*/ |
189 |
|
|
public void executeSql(String query,String key, int idx) { |
190 |
|
|
Statement stmt; |
191 |
|
|
try { |
192 |
|
|
stmt = this.createStatement(key,idx); |
193 |
|
|
stmt.execute(query); |
194 |
|
|
stmt.close(); |
195 |
|
|
} catch (SQLException e) { |
196 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
197 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
198 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
199 |
|
|
e.printStackTrace(); |
200 |
|
|
} |
201 |
|
|
} |
202 |
duarte |
51 |
/** |
203 |
|
|
* To change the MASTERDB String, i.e. the name of the key master database. To be used in testing. |
204 |
|
|
* @param db the name of the key master db we want to use instead of the default defined in the MASTERDB field |
205 |
|
|
*/ |
206 |
|
|
public void setKeyDb(String db) { |
207 |
|
|
this.MASTERDB=db; |
208 |
duarte |
53 |
try { |
209 |
|
|
//Closing previous connection is essential |
210 |
|
|
this.mCon.close(); |
211 |
|
|
this.mCon=DriverManager.getConnection(URL+MASTERHOST+"/"+MASTERDB,user,password); |
212 |
|
|
} |
213 |
|
|
catch (SQLException e){ |
214 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
215 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
216 |
|
|
System.err.println("Couldn't get connection to master host "+host+", database "+MASTERDB+", exiting."); |
217 |
|
|
System.exit(2); |
218 |
|
|
} |
219 |
duarte |
51 |
} |
220 |
duarte |
43 |
|
221 |
|
|
/** |
222 |
duarte |
72 |
* To set keyTable field in constructor (i.e. first time). Only to be used in constructor. |
223 |
duarte |
35 |
* @param db the database name |
224 |
|
|
*/ |
225 |
|
|
public void setKeyTable(String db) { |
226 |
duarte |
72 |
String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+db+"\' AND key_name=\'"+this.key+"\';"; |
227 |
|
|
try { |
228 |
|
|
Statement S = this.mCon.createStatement(); |
229 |
|
|
ResultSet R = S.executeQuery(query); |
230 |
|
|
if (R.next()){ |
231 |
|
|
this.keyTable=R.getString(1); |
232 |
|
|
} |
233 |
|
|
R.close(); |
234 |
|
|
S.close(); |
235 |
|
|
} catch (SQLException e) { |
236 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
237 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
238 |
|
|
System.err.println("Couldn't get the key_master_table from "+MASTERDB+", exiting."); |
239 |
|
|
System.exit(2); |
240 |
|
|
} |
241 |
duarte |
15 |
} |
242 |
|
|
|
243 |
duarte |
35 |
/** |
244 |
duarte |
43 |
* To set the keyTable field when db is already set |
245 |
duarte |
72 |
* The value of keyTable is taken from the dbs_keys table in the database given the db and key. |
246 |
duarte |
35 |
*/ |
247 |
|
|
public void setKeyTable() { |
248 |
duarte |
72 |
String query="SELECT key_master_table FROM dbs_keys WHERE db=\'"+this.db+"\' AND key_name=\'"+this.key+"\';"; |
249 |
|
|
try { |
250 |
|
|
Statement S = this.mCon.createStatement(); |
251 |
|
|
ResultSet R = S.executeQuery(query); |
252 |
|
|
if (R.next()){ |
253 |
|
|
this.keyTable=R.getString(1); |
254 |
|
|
} |
255 |
|
|
R.close(); |
256 |
|
|
S.close(); |
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 the key_master_table from "+MASTERDB+", exiting."); |
261 |
|
|
System.exit(2); |
262 |
|
|
} |
263 |
duarte |
15 |
} |
264 |
|
|
|
265 |
duarte |
34 |
public String getKeyTable() { |
266 |
duarte |
15 |
return this.keyTable; |
267 |
|
|
} |
268 |
|
|
|
269 |
duarte |
72 |
/** |
270 |
|
|
* 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 |
271 |
|
|
* @return |
272 |
|
|
*/ |
273 |
|
|
public String getTableOnNode(){ |
274 |
|
|
String table=""; |
275 |
|
|
if (this.keyTable.contains("__")) { |
276 |
|
|
String[] tokens=this.keyTable.split("__"); |
277 |
|
|
table=tokens[1]; |
278 |
|
|
} |
279 |
|
|
else { |
280 |
|
|
System.err.println("Error! The keyTable field is not set in this ClusterConnection object."); |
281 |
|
|
} |
282 |
|
|
return table; |
283 |
duarte |
15 |
} |
284 |
duarte |
72 |
|
285 |
duarte |
15 |
public void setUser(String user) { |
286 |
|
|
this.user=user; |
287 |
|
|
} |
288 |
|
|
|
289 |
|
|
public void setPassword(String password) { |
290 |
|
|
this.password=password; |
291 |
|
|
} |
292 |
|
|
|
293 |
|
|
public void setDb(String db){ |
294 |
|
|
this.db=db; |
295 |
|
|
} |
296 |
|
|
|
297 |
|
|
public void setKey(String key){ |
298 |
|
|
this.key=key; |
299 |
duarte |
34 |
setKeyTable(); |
300 |
duarte |
15 |
} |
301 |
|
|
|
302 |
|
|
public Statement createMasterStatement() { |
303 |
|
|
Statement S=null; |
304 |
|
|
try { |
305 |
|
|
S=this.mCon.createStatement(); |
306 |
|
|
} |
307 |
|
|
catch (SQLException e){ |
308 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
309 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
310 |
|
|
System.err.println("Couldn't create statement for the master connection, exiting."); |
311 |
duarte |
15 |
System.exit(2); |
312 |
|
|
} |
313 |
|
|
return S; |
314 |
|
|
} |
315 |
duarte |
74 |
|
316 |
|
|
public void createNewKeyMasterTbl(String table) { |
317 |
|
|
String keyMasterTbl=db+"__"+table; |
318 |
|
|
try { |
319 |
|
|
String query="CREATE TABLE IF NOT EXISTS "+keyMasterTbl+" ("+ |
320 |
|
|
key+" int(11) NOT NULL auto_increment, " + |
321 |
|
|
"client_id smallint(6) NOT NULL default '0', " + |
322 |
|
|
"PRIMARY KEY (`"+key+"`) " + |
323 |
|
|
") ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;"; |
324 |
|
|
Statement S=this.mCon.createStatement(); |
325 |
|
|
S.executeUpdate(query); |
326 |
|
|
S.close(); |
327 |
|
|
} catch (SQLException e) { |
328 |
|
|
System.err.println("Couldn't create table "+keyMasterTbl); |
329 |
|
|
e.printStackTrace(); |
330 |
|
|
} |
331 |
|
|
try { |
332 |
|
|
Statement S=this.mCon.createStatement(); |
333 |
|
|
// following query is exactly the same as query in key_master.sql for key_master database definition |
334 |
|
|
String query = "INSERT INTO dbs_keys "+ |
335 |
|
|
"SELECT i.COLUMN_NAME AS key_name, LEFT(i.TABLE_NAME,POSITION('__' IN i.TABLE_NAME)-1) AS db, i.TABLE_NAME AS key_master_table "+ |
336 |
|
|
"FROM INFORMATION_SCHEMA.STATISTICS AS i "+ |
337 |
|
|
"WHERE i.TABLE_SCHEMA='"+MASTERDB+"' AND i.TABLE_NAME!='clients_names' AND i.TABLE_NAME!='dbs_keys';"; |
338 |
|
|
S.executeUpdate(query); |
339 |
|
|
S.close(); |
340 |
|
|
} catch (SQLException e) { |
341 |
|
|
System.err.println("Didn't insert new record into table dbs_keys of database: "+MASTERDB+". The record for key: "+key+", table: "+table+" existed already. This is usually a harmless error!"); |
342 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
343 |
|
|
} |
344 |
|
|
setKeyTable(); |
345 |
|
|
} |
346 |
duarte |
15 |
|
347 |
duarte |
67 |
public int[] getAllIdxFromMaster(String key) { |
348 |
duarte |
15 |
this.setKey(key); |
349 |
duarte |
67 |
int[] ids=null; |
350 |
|
|
ArrayList<Integer> idsAL=new ArrayList<Integer>(); |
351 |
|
|
try { |
352 |
duarte |
72 |
String query="SELECT "+key+" FROM "+keyTable+";"; |
353 |
duarte |
67 |
Statement S=this.mCon.createStatement(); |
354 |
|
|
ResultSet R=S.executeQuery(query); |
355 |
|
|
while (R.next()){ |
356 |
|
|
idsAL.add(R.getInt(1)); |
357 |
|
|
} |
358 |
|
|
R.close(); |
359 |
|
|
S.close(); |
360 |
duarte |
15 |
} |
361 |
|
|
catch (SQLException e){ |
362 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
363 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
364 |
duarte |
72 |
System.err.println("Couldn't get all indices from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting."); |
365 |
duarte |
15 |
System.exit(2); |
366 |
|
|
} |
367 |
duarte |
67 |
ids=new int[idsAL.size()]; |
368 |
|
|
for (int i=0;i<idsAL.size();i++){ |
369 |
|
|
ids[i]=idsAL.get(i); |
370 |
|
|
} |
371 |
|
|
return ids; |
372 |
duarte |
15 |
} |
373 |
duarte |
35 |
/** |
374 |
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 |
375 |
|
|
* @param key the name of the key |
376 |
duarte |
67 |
* @return HashMap with keys = indices, and values = node names where the corresponding index is stored |
377 |
duarte |
38 |
*/ |
378 |
duarte |
67 |
public HashMap<Integer,String> getAllIdxAndClients (String key){ |
379 |
duarte |
38 |
this.setKey(key); |
380 |
duarte |
67 |
HashMap<Integer,String> idsAndClients=new HashMap<Integer,String>(); |
381 |
duarte |
38 |
try { |
382 |
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);"; |
383 |
duarte |
67 |
Statement S=this.mCon.createStatement(); |
384 |
|
|
ResultSet R=S.executeQuery(query); |
385 |
|
|
while (R.next()){ |
386 |
|
|
idsAndClients.put(R.getInt(1),R.getString(2)); |
387 |
|
|
} |
388 |
|
|
R.close(); |
389 |
|
|
S.close(); |
390 |
duarte |
38 |
} |
391 |
|
|
catch (SQLException e){ |
392 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
393 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
394 |
|
|
System.err.println("Couldn't get all indices/client_names pairs from table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting."); |
395 |
|
|
System.exit(2); |
396 |
|
|
} |
397 |
duarte |
67 |
return idsAndClients; |
398 |
duarte |
38 |
} |
399 |
|
|
|
400 |
|
|
/** |
401 |
duarte |
35 |
* To get client_id for a certain idx and key |
402 |
|
|
* @param key the key name |
403 |
|
|
* @param idx the id value for that key |
404 |
|
|
* @return the client_id for node that has the data for the idx for that key |
405 |
|
|
*/ |
406 |
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 |
407 |
|
|
public int getHostId4Idx (String key,int idx) { |
408 |
|
|
int hostId=0; |
409 |
|
|
this.setKey(key); |
410 |
|
|
Statement S; |
411 |
|
|
ResultSet R; |
412 |
|
|
String query; |
413 |
|
|
int countCids=0; |
414 |
|
|
try { |
415 |
|
|
S=mCon.createStatement(); |
416 |
duarte |
72 |
query="SELECT count(client_id) FROM "+keyTable+" WHERE "+key+"="+idx+";"; |
417 |
duarte |
15 |
R=S.executeQuery(query); |
418 |
|
|
if (R.next()){ |
419 |
|
|
countCids=R.getInt(1); |
420 |
|
|
} |
421 |
|
|
if (countCids!=1){ |
422 |
duarte |
76 |
System.err.println("the query was: "+query); |
423 |
duarte |
72 |
System.err.println("Error! the count of client_ids for idx "+key+"= "+idx+" is " +countCids+ |
424 |
duarte |
52 |
". It must be 1! The values were taken from host: "+MASTERHOST+", database: "+MASTERDB+", table: "+keyTable+". Check what's wrong! Exiting now."); |
425 |
duarte |
15 |
System.exit(2); |
426 |
|
|
} |
427 |
|
|
else { |
428 |
duarte |
72 |
query="SELECT client_id FROM "+keyTable+" WHERE "+key+"="+idx+";"; |
429 |
duarte |
15 |
R=S.executeQuery(query); |
430 |
|
|
if (R.next()){ |
431 |
|
|
hostId=R.getInt(1); |
432 |
|
|
} |
433 |
|
|
} |
434 |
|
|
S.close(); |
435 |
|
|
R.close(); |
436 |
|
|
} |
437 |
|
|
catch(SQLException e) { |
438 |
duarte |
72 |
System.err.println("Couldn't get the host id for idx "+key+"="+idx+", exiting"); |
439 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
440 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
441 |
duarte |
15 |
System.exit(3); |
442 |
|
|
} |
443 |
|
|
return hostId; |
444 |
|
|
} |
445 |
|
|
|
446 |
|
|
public void insertIdxInMaster(String key, int clientId) { |
447 |
|
|
Statement S; |
448 |
|
|
String query; |
449 |
|
|
this.setKey(key); |
450 |
|
|
try { |
451 |
|
|
S=this.mCon.createStatement(); |
452 |
|
|
query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");"; |
453 |
|
|
S.executeUpdate(query); |
454 |
|
|
S.close(); |
455 |
|
|
} |
456 |
|
|
catch (SQLException E) { |
457 |
duarte |
33 |
System.err.println("SQLException: " + E.getMessage()); |
458 |
|
|
System.err.println("SQLState: " + E.getSQLState()); |
459 |
duarte |
72 |
System.err.println("Couldn't insert new "+this.key+" in master table "+this.getKeyTable()+". The client_id for it was "+clientId+". Exiting."); |
460 |
duarte |
15 |
System.exit(2); |
461 |
|
|
} |
462 |
|
|
} |
463 |
|
|
|
464 |
|
|
public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) { |
465 |
|
|
this.setKey(keySrc); |
466 |
|
|
int clientId=0; |
467 |
|
|
clientId=this.getHostId4Idx(keySrc,idxSrc); |
468 |
|
|
insertIdxInMaster(keyDest,clientId); |
469 |
|
|
} |
470 |
|
|
|
471 |
|
|
public int getLastInsertId(String key) { |
472 |
|
|
int lastIdx=0; |
473 |
|
|
this.setKey(key); |
474 |
|
|
Statement S; |
475 |
|
|
ResultSet R; |
476 |
|
|
String query = ""; |
477 |
|
|
try { |
478 |
|
|
S = this.mCon.createStatement(); |
479 |
|
|
query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;"; |
480 |
|
|
R = S.executeQuery(query); |
481 |
|
|
if (R.next()) { |
482 |
|
|
lastIdx=R.getInt(1); |
483 |
|
|
} |
484 |
|
|
R.close(); |
485 |
|
|
S.close(); |
486 |
|
|
} |
487 |
|
|
catch (SQLException E) { |
488 |
duarte |
72 |
System.err.println("Couldn't get the last insert id for key type "+this.key+" from table "+this.keyTable+". Exiting"); |
489 |
duarte |
33 |
System.err.println("SQLException: " + E.getMessage()); |
490 |
|
|
System.err.println("SQLState: " + E.getSQLState()); |
491 |
duarte |
15 |
System.exit(3); |
492 |
|
|
} // end try/catch connection |
493 |
|
|
return lastIdx; |
494 |
|
|
} // end getGraphId |
495 |
duarte |
21 |
|
496 |
|
|
public int[][] getIdxSet(String key) { |
497 |
|
|
int[][] indMatrix=null; |
498 |
|
|
this.setKey(key); |
499 |
|
|
String query; |
500 |
|
|
Statement S; |
501 |
|
|
ResultSet R; |
502 |
|
|
try { |
503 |
|
|
// STEP 1 -- getting set of all client_ids |
504 |
|
|
S=this.mCon.createStatement(); |
505 |
|
|
query="SELECT count(distinct client_id) FROM "+keyTable+";"; |
506 |
|
|
int count=0; |
507 |
|
|
R=S.executeQuery(query); |
508 |
|
|
if (R.next()){ |
509 |
|
|
count=R.getInt(1); |
510 |
|
|
} |
511 |
|
|
query="SELECT DISTINCT client_id FROM "+keyTable+" ORDER BY client_id;"; |
512 |
|
|
//R.close(); |
513 |
|
|
//S.close(); |
514 |
|
|
R=S.executeQuery(query); |
515 |
|
|
|
516 |
|
|
// STEP 2 -- putting sets of indices counts into temp tables c_<client_id> with a serial auto_increment field |
517 |
|
|
int[] clids=new int[count]; //array to store all client_ids. To be used in loops later |
518 |
|
|
int i=0; |
519 |
|
|
while (R.next()){ |
520 |
|
|
Statement Sloop=this.mCon.createStatement(); |
521 |
|
|
int clid=R.getInt(1); |
522 |
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));"; |
523 |
duarte |
21 |
Sloop.executeUpdate(query); |
524 |
duarte |
72 |
query="INSERT INTO c_"+clid+" ("+key+",client_id) SELECT "+key+",client_id FROM "+keyTable+" WHERE client_id="+clid+";"; |
525 |
duarte |
21 |
Sloop.executeUpdate(query); |
526 |
|
|
clids[i]=clid; |
527 |
|
|
i++; |
528 |
|
|
Sloop.close(); |
529 |
|
|
} |
530 |
|
|
|
531 |
|
|
// STEP3 -- merging all c_<client_id> tables into a temp table tmp_allcs and selecting the client_id with the maximum count |
532 |
|
|
//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;"; |
533 |
|
|
query="DROP TABLE IF EXISTS tmp_allcs;"; |
534 |
|
|
S.executeUpdate(query); |
535 |
|
|
//this table must be permanent! otherwise cannot do the select max(c) later |
536 |
|
|
query="CREATE TABLE IF NOT EXISTS tmp_allcs (client_id int(11), c int(11)) ENGINE=MEMORY;"; |
537 |
|
|
S.executeUpdate(query); |
538 |
|
|
String unionStr="SELECT client_id,count(*) AS c FROM c_"+clids[0]+" GROUP BY client_id"; |
539 |
|
|
for (i=1;i<clids.length;i++) { |
540 |
|
|
unionStr+=" UNION SELECT client_id,count(*) AS c FROM c_"+clids[i]+" GROUP BY client_id"; |
541 |
|
|
} |
542 |
|
|
query="INSERT INTO tmp_allcs "+unionStr+";"; |
543 |
|
|
S.executeUpdate(query); |
544 |
|
|
query="SELECT client_id,c FROM tmp_allcs WHERE c=(SELECT max(c) FROM tmp_allcs);"; |
545 |
|
|
R=S.executeQuery(query); |
546 |
|
|
int clidMaxIdxCount=0; |
547 |
|
|
int maxIdxCount=0; |
548 |
|
|
if (R.next()) { |
549 |
|
|
clidMaxIdxCount=R.getInt(1); |
550 |
|
|
maxIdxCount=R.getInt(2); |
551 |
|
|
} |
552 |
|
|
query="DROP TABLE tmp_allcs;"; |
553 |
|
|
S.executeUpdate(query); |
554 |
|
|
|
555 |
|
|
// 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 |
556 |
|
|
//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);"; |
557 |
duarte |
72 |
String selectStr="c_"+clidMaxIdxCount+".serial, c_"+clidMaxIdxCount+"."+key+" AS c_"+clidMaxIdxCount; |
558 |
duarte |
21 |
String fromStr="c_"+clidMaxIdxCount; |
559 |
|
|
for (i=0;i<clids.length;i++) { |
560 |
|
|
if (clids[i]!=clidMaxIdxCount){ |
561 |
duarte |
72 |
selectStr+=", c_"+clids[i]+"."+key+" AS c_"+clids[i]; |
562 |
duarte |
21 |
fromStr+=" LEFT JOIN c_"+clids[i]+" ON (c_"+clidMaxIdxCount+".serial=c_"+clids[i]+".serial)"; |
563 |
|
|
} |
564 |
|
|
} |
565 |
|
|
query="CREATE TEMPORARY TABLE indices_matrix "+"SELECT "+selectStr+" FROM "+fromStr+";"; |
566 |
|
|
S.executeUpdate(query); |
567 |
|
|
|
568 |
|
|
// STEP 5 -- put the table into a 2-dimensional array and return it |
569 |
|
|
indMatrix = new int[maxIdxCount][clids.length]; |
570 |
|
|
query="SELECT * FROM indices_matrix"; |
571 |
|
|
R=S.executeQuery(query); |
572 |
|
|
i=0; |
573 |
|
|
while (R.next()) { |
574 |
|
|
for (int j=0;j<clids.length;j++){ |
575 |
duarte |
22 |
indMatrix[i][j]=R.getInt(j+2); |
576 |
duarte |
21 |
} |
577 |
|
|
i++; |
578 |
|
|
} |
579 |
|
|
R.close(); |
580 |
|
|
S.close(); |
581 |
|
|
} |
582 |
|
|
catch (SQLException e){ |
583 |
duarte |
33 |
System.err.println("SQLException: " + e.getMessage()); |
584 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
585 |
duarte |
72 |
System.err.println("Couldn't get the indices set from columnn "+key+" in table "+keyTable+" from "+MASTERDB+" database in "+MASTERHOST+", exiting."); |
586 |
duarte |
21 |
System.exit(2); |
587 |
|
|
} |
588 |
|
|
return indMatrix; |
589 |
|
|
} |
590 |
|
|
|
591 |
|
|
|
592 |
duarte |
15 |
} |