summaryrefslogtreecommitdiffstats
path: root/Parse/SqlPollerUnThreaded.java
blob: e5136c625fb02527ba55709a77ec7b67f3603e38 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
package Parse;

import helper.ListBTS;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;

import DataStructure.MobilePhone;
import DataStructure.SingleBTS;

/**
 * Man könnte auch das Datum des aktuellsten Eintrags nehmen und x Sekunden
 * davon abziehen! Methoden: gefundene IMSI ausgeben. Zeitspanne angeben, in der
 * man suchen will. Mitteln immer über eine (x) Sekunde?
 * 
 * @author richy
 * 
 */
public class SqlPollerUnThreaded {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	public Connection cn;
	public long last_id;
	public long last_ActionRef;
	public ArrayList<MobilePhone> results = new ArrayList<MobilePhone>();
	public int polltime = 3000;
	public ArrayList<MobilePhone> phones;
	private Long[] IMSIs;

	// private boolean interrupt = false;

	/**
	 * @param args
	 * @throws InterruptedException
	 */
	public static void main(String[] args) throws InterruptedException {

		ArrayList<Long> blalist = new ArrayList<Long>();
		blalist.add(1l);
		blalist.add(1l);
		blalist.add(1l);
		blalist.add(3l);
		blalist.add(4l);
		ArrayList<Long> result = new ArrayList<Long>(new HashSet<Long>(blalist));
		System.out.println(result);
		SqlPollerUnThreaded bla = new SqlPollerUnThreaded();
		System.out.println("Init... Wait");
		Thread.sleep(1300);
		bla.getMRs();
		Thread.sleep(1300);
		bla.getMRs();
		System.out.println("Done");

	}

	/**
	 * Between creation and use of SqlPoller, some time must pass so that the
	 * SQL table can grow!
	 */
	public SqlPollerUnThreaded() {

		// mit LIMIT(0,30) die aktuellesten n Einträge anzeigen?
		// Oder auf cur_timestamp achten? Z.B.: die letzten drei Sekunden?
		initSQL();

	}

	// public void Interrupt() {
	// interrupt = true;
	// }

	// public boolean isInterrupted() {
	// return interrupt;
	// }

	public ArrayList<MobilePhone> getMRs() {
		// formerly known as run()
		if (cn == null || last_id < 0) {
			initSQL();
		}

		// TODO Get Elements here and store it in a public variable

		try {
			Statement st = cn.createStatement();
			st.setQueryTimeout(polltime); // -------------------
			ResultSet rsMR = getAllMR();
			ArrayList<ResultSet> rsBTS = getUsedBTS(rsMR);
			ArrayList<MobilePhone> phones = mergeMrBts(rsMR, rsBTS);
			phones = mergePhones(phones, IMSIs);
			this.phones = phones;
			System.out.println(phones);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Cannot get a Statement from SQL in SqlPoller");
			e.printStackTrace();
		}
		/*
		 * try { Thread.sleep(polltime); } catch (InterruptedException e) {
		 * System.out.println("sqlPoller closed"); // e.printStackTrace(); try {
		 * System.out.println("Closing Connection to SQL Server...");
		 * cn.close(); System.out.println("Closed successfully"); } catch
		 * (SQLException e1) { System.out.println(""); // e1.printStackTrace();
		 * } }
		 */

		// at the end, get new last_id: prepare for next run
		try {
			last_id = getLastID();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return phones;
	}

	/**
	 * Traverses IMSIs and takes all phones from phones that match each IMSI.
	 * All MRs are joined and averaged
	 * 
	 * @param phones
	 * @param IMSIs
	 * @return
	 */
	private ArrayList<MobilePhone> mergePhones(ArrayList<MobilePhone> phones,
			Long[] IMSIs) {
		ArrayList<MobilePhone> result = new ArrayList<MobilePhone>();
		for (long imsi : IMSIs) {
			MobilePhone current = new MobilePhone();
			current.IMSI = imsi;
			for (int i = 0; i < phones.size(); i++) {
				if (phones.get(i).IMSI == imsi) {
					current.MR.addAll(phones.get(i).MR);
					phones.remove(i);
					i--;
				}
			}
			result.add(current);

		}
		result = averagePhones(result);
		return result;
	}

	private ArrayList<MobilePhone> averagePhones(ArrayList<MobilePhone> phones) {
		ArrayList<MobilePhone> result = new ArrayList<MobilePhone>();
		for (int i = 0; i < phones.size(); i++) {
			MobilePhone current = new MobilePhone();
			current.IMSI = phones.get(i).IMSI;
			SingleBTS[] content = ListBTS.content(phones.get(i).MR);
			current.MR = ListBTS
					.generateAveragedList(phones.get(i).MR, content);
			result.add(current);
		}
		return result;
	}

	private ArrayList<MobilePhone> mergeMrBts(ResultSet rsMR,
			ArrayList<ResultSet> rsBTS) throws SQLException {
		ArrayList<MobilePhone> phones = new ArrayList<MobilePhone>();
		rsMR.beforeFirst();
		while (rsMR.next()) {
			MobilePhone current = new MobilePhone();
			current.IMSI = rsMR.getLong("IMSI");
			int neigh = rsMR.getInt("NUM_NEIGH");
			if (neigh != 7) {
				for (int i = 0; i < neigh; i++) {
					int ARFCN = rsMR.getInt("ARFCN_" + i);
					int rxdl = rsMR.getInt("rxlev_" + i);
					int reason = rsMR.getInt("actionID");
					SingleBTS new_elem = new SingleBTS(ARFCN, 0, rxdl, false,
							new Date(), "Lookup Record");
					new_elem.reason = reason;
					current.MR.add(new_elem);
				}
			}
			// now, search currently connected BTS for this phone's IMSI
			int ARFCN = getARFCNforIMSI(current.IMSI, rsBTS);
			int reason = rsMR.getInt("actionID");
			int rxul = rsMR.getInt("RXL_SUB_ul");
			int rxdl = rsMR.getInt("RXL_SUB_dl");
			SingleBTS new_elem = new SingleBTS(ARFCN, rxul, rxdl, false,
					new Date(), "Lookup Record");
			new_elem.reason = reason;
			current.MR.add(new_elem);
			phones.add(current);

		}

		// put phones together
		return phones;
	}

	private int getARFCNforIMSI(long IMSI, ArrayList<ResultSet> rsBTS)
			throws SQLException {
		for (ResultSet rs : rsBTS) {
			rs.beforeFirst();
			while (rs.next()) {
				if (rs.getLong("IMSI") == IMSI) {
					return rs.getInt("arfcn");
				}
			}
		}
		return 0;
	}

	/**
	 * Matches entries from rs with usedBTS table
	 * 
	 * @param rs
	 * @throws SQLException
	 */
	private ArrayList<ResultSet> getUsedBTS(ResultSet rs) throws SQLException {
		// get lowest actionRefID
		long actionRefID = getLowestRefID(rs);
		rs.beforeFirst();
		Long[] IMSIs = getDistinctIMSIs(rs);
		this.IMSIs = IMSIs;
		// now, get the usedBTS part for each IMSI that is found in rs
		ArrayList<ResultSet> result = new ArrayList<ResultSet>();
		for (long IMSI : IMSIs) {
			Statement st = cn.createStatement();
			ResultSet rs2 = st.executeQuery(usedBTSQuery(IMSI, actionRefID));
			result.add(rs2);

			// merge this stuff
		}
		return result;

	}

	private String usedBTSQuery(long IMSI, long RefID) {
		String query = "SELECT * FROM usedBTS"
				+ " JOIN action ON usedBTS.actionRefID >= " + RefID
				+ " AND action.IMSI=" + IMSI
				+ " AND usedBTS.actionRefID = action.id"
				+ " JOIN arfcn ON arfcn.bts = usedBTS.bts"
				+ " ORDER BY usedBTS.cur_timestamp desc";
		System.out.println("Query for usedBTS: " + query);
		return query;
	}

	/**
	 * Traverses ResultSet from getAllMR(). Outputs all IMSIs that are inside
	 * this Set
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private Long[] getDistinctIMSIs(ResultSet rs) throws SQLException {
		// make ArrayList with all IMSIs inside
		Collection<Long> IMSIs = new ArrayList<Long>();
		rs.beforeFirst();
		while (rs.next()) {
			IMSIs.add(rs.getLong("IMSI"));
		}
		// make Hashset
		ArrayList<Long> result = new ArrayList<Long>(new HashSet<Long>(IMSIs));
		return result.toArray(new Long[1]);

	}

	/**
	 * Takes a ResultSet with MRs. Gets the lowest actionRefID
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private long getLowestRefID(ResultSet rs) throws SQLException {
		long actionRefID = Long.MAX_VALUE;
		rs.beforeFirst();
		while (rs.next()) {
			if (actionRefID > rs.getLong("MR.actionRefID")) {
				actionRefID = rs.getLong("actionRefID");
			}
		}
		return actionRefID;
	}

	private void initSQL() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			cn = DriverManager.getConnection(
					"jdbc:mysql://132.230.4.13:3306/logging", "richard",
					"uh237Aug.ad7");
			cn.setReadOnly(true);
			// Connection is ready
			// get current ID
			// Statement getCurrID = cn.createStatement();
			last_id = getLastID();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Gets a resultset that contains all MeasurementReports since last_id.
	 * MIN(actionRefID is at the end of this ResultSet)
	 * 
	 * @return
	 * @throws SQLException
	 *             when last_id <= 0 or Connection is broken
	 */
	private ResultSet getAllMR() throws SQLException {
		// e.g. last id = 1702898
		if (last_id <= 0) {
			throw new SQLException(
					"seems it is not inilialized. Last_id points to 0");
		}
		String query = "SELECT * FROM measurementResults MR "
				+ "JOIN action ON " + "MR.id >= " + last_id + " AND "
				+ "MR.actionRefID = action.ID "
				// + "ORDER BY MR.cur_timestamp DESC";
				+ "ORDER BY MR.actionRefID";
		System.out.println("Query for all MR is: " + query);
		Statement st = cn.createStatement();
		ResultSet result = st.executeQuery(query);
		result.first();
		// last_ActionRef = result.getLong("ActionRefID");
		result.beforeFirst();
		return result;

		/*
		 * SELECT MR.id, MR.cur_timestamp, action.IMSI, MR.RXL_FULL_ul,
		 * MR.RXL_FULL_dl, BS_POWER, MS_TO, L1_MS_PWR, L1_TA,NUM_NEIGH, IDX_0,
		 * ARFCN_0,BSIC_0, rxlev_0, IDX_1, ARFCN_1,BSIC_1, rxlev_1, IDX_2,
		 * ARFCN_2,BSIC_2, rxlev_2, IDX_3, ARFCN_3,BSIC_3, rxlev_3, IDX_4,
		 * ARFCN_4,BSIC_4, rxlev_4, IDX_5, ARFCN_5,BSIC_5, rxlev_5, actionID
		 * FROM measurementResults MR JOIN action ON MR.id >= last_id AND
		 * MR.actionRefID = action.ID;
		 */

	}

	private int getLastID() throws SQLException {
		String query = "SELECT MAX( id ) FROM measurementResults ";
		Statement st = cn.createStatement();
		ResultSet result = st.executeQuery(query);
		if (result.first()) {
			return result.getInt(1);
		} else {
			return -1;
		}
	}

	public void close() {
		try {
			cn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}