summaryrefslogtreecommitdiffstats
path: root/Parse/sqlreader.java
blob: 82688103f159b9554b16d0a2243defa353a4f8c6 (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
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
package Parse;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedList;

import DataStructure.GPScoordinate;
import DataStructure.SingleBTS;

public class sqlreader {
	private LinkedList<GPScoordinate> gpslist;
	public SingleBTS[] btsarray;
	public GPScoordinate[] gpsarray;
	private ResultSet MR_rs;
	private ResultSet usedBTS_rs;
	private Statement st;
	private Date start;
	private Date end;
	public long IMSI;
	public SingleBTS[] BtsNames;

	/**
	 * Builds a list of MR and Coordinates. Entry x of the first list reflects
	 * element x of the second list
	 * 
	 * @param nmea
	 *            NMEAParse Object
	 * @param IMSI
	 *            which phone
	 * @param matchtime
	 *            Time in milliseconds a coordinate may be away from a
	 *            measurement result
	 * @throws ClassNotFoundException
	 *             when no sql driver is found
	 * @throws SQLException
	 *             when no connection to databse is possible
	 */
	// Constructor
	public sqlreader(NMEAParse nmea, long IMSI, long matchtime)
			throws ClassNotFoundException, SQLException {
		this.IMSI = IMSI;
		gpslist = nmea.getGPSList();
		// if a call was done before a gps fix, it would crash while searching
		// for the usedBTS. So, minus 1 minute or so
		this.start = new Date(gpslist.getFirst().time.getTime() - 60000);
		this.end = new Date(gpslist.getLast().time.getTime() + 60000);

		openSQL();
		// ask sql for all known BTS in database (table arfcn)
		BtsNames = getBTSnames();
		// get (starting)timestamps when BTS got used (during a call). Nothing
		// else
		SingleBTS[] usedBTStimestamps = getBTSlist();
		// get all Measurement Reports
		SingleBTS[] BTSarray = parseMR(usedBTStimestamps, BtsNames);
		System.out.println("Valid SQL-Entries: " + BTSarray.length);
		searchCorrespondingCoords(gpslist, BTSarray, matchtime);

		// now, get corresponding gps-coordinates

		// Date[] mrDate = openSQL(gpslist.getFirst().time,
		// gpslist.getLast().time, IMSI);
		// match usedBTS to MRlist (max. valid arfcn = 1023)
		// SingleBTS[] usedBTS = getBTSlist();
	}

	// fills the MR_rs ResultSet
	public void openSQL() throws ClassNotFoundException, SQLException {
		// select driver
		Class.forName("com.mysql.jdbc.Driver");
		// open connection
		Connection cn = DriverManager.getConnection(
				"jdbc:mysql://132.230.4.13:3306/logging", "richard",
				"uh237Aug.ad7");
		cn.setReadOnly(true);
		System.out.println("Connected to SQL");
		st = cn.createStatement();
		// query Timeout
		st.setQueryTimeout(20);// ---------------------------

	}

	// queries the Database and fills MR_rs
	private void fillMR_rs() throws SQLException {
		// System.out.println("fillMR_rs called");
		String query = queryMR();
		try {
			MR_rs = st.executeQuery(query);
		} catch (SQLException e) {
			System.out
					.println("Timeout. SQL query hasn't finished within time");
			e.printStackTrace();
		}

	}

	/**
	 * reads the MR from SQL database. Also takes Information from
	 * btsTimestamps(usedBTS table) and the btsNames(arfcn table) to get the
	 * name and arfcn of each SingleBTS
	 * 
	 * @param btsTimestamp
	 *            Array: SQL table usedBTS
	 * @param btsNames
	 *            Array: SQL table arfcn. Information about location and name
	 */
	private SingleBTS[] parseMR(SingleBTS[] btsTimestamp, SingleBTS[] btsNames)
			throws SQLException {
		// System.out.println("parseMR called with btsTimestamp of size "
		// + btsTimestamp.length + " and btsNames of size "
		// + btsNames.length);

		// fill SQL buffer first -> query
		fillMR_rs();
		MR_rs.beforeFirst();

		// prepare variables
		SingleBTS current;
		LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
		// traverse sql output
		int result = 0;
		while (MR_rs.next()) {
			// System.out.println("getting SQL row number " + result);
			result++;
			// search for timestamp when this MR was created
			Date timestamp = MR_rs.getTimestamp("cur_timestamp");

			// to which BTS was the phone connected when this MR got created
			int hit = searchForUsedBTS(btsTimestamp, timestamp);
			if (hit < 0) {
				System.out.println("corresponding BTS not found for " + IMSI
						+ " !");
				hit = 0;
			}
			// parse SQL input for FullBTS
			int ARFCN = btsTimestamp[hit].ARFCN;
			int RXul = MR_rs.getInt("RXL_FULL_ul");
			int RXdl = MR_rs.getInt("RXL_FULL_dl");
			boolean interpolated = false;
			String name = btsTimestamp[hit].name;
			int TA = MR_rs.getInt("L1_TA");
			int BS_POWER = MR_rs.getInt("BS_POWER");
			int MS_TO = MR_rs.getInt("MS_TO");
			int MS_PWR = MR_rs.getInt("L1_MS_PWR");
			int reason = MR_rs.getInt("actionID");
			int rxQualsubDL = MR_rs.getInt("RXQ_FULL_dl");
			// int rxQualfullDL = MR_rs.getInt("RXQ_SUB_dl");
			int rxQualsubUL = MR_rs.getInt("RXQ_SUB_ul");
			// int rxQualfullUL = MR_rs.getInt("RXQ_FULL_ul");
			// only add if source was a phone call!
			if (reason == 2) {
				// store the FullBTS details
				current = new SingleBTS(ARFCN, RXul, RXdl, interpolated,
						timestamp, name, TA, BS_POWER, MS_TO, MS_PWR);
				current.fullBTS = true;
				current.reason = reason;
				// current.dlQfull.add((double) rxQualfullDL);
				// current.dlQsub.add((double) rxQualsubDL);
				// current.ulQfull.add((double) rxQualfullUL);
				current.ulQsub.add((double) rxQualsubUL);
				current.dlQsub.add((double) rxQualsubDL);
				btslist.add(current);

				// parse other BTS from the measurement report
				// parse SQL input for SingleBTS. Number of neighbors: neigh
				int neigh = MR_rs.getInt("NUM_NEIGH");
				if (neigh == 7) {
					neigh = 0;
					// no more BTS available. Actually, the MR is not valid!
					// Maybe
					// check for this before adding the FullBTS?
				} else {
					for (int i = 0; i < neigh; i++) {
						RXdl = MR_rs.getInt("rxlev_" + i);
						// throw out BTS that are in neighbor list but not
						// received
						if (RXdl > -116) {
							ARFCN = MR_rs.getInt("ARFCN_" + i);
							// get The Name (String) of this ARFCN
							name = searchBTSName(btsNames, ARFCN);
							current = new SingleBTS(ARFCN, 0, RXdl, false,
									timestamp, name);
							current.reason = reason;
							btslist.add(current);
						}

					}
				}
			}

		}
		SingleBTS[] btsarray = new SingleBTS[btslist.size()];
		btsarray = btslist.toArray(btsarray);
		return btsarray;

	}

	/**
	 * Gets the table usedBTS. It contains Timestamps when which BTS got used.
	 * This is needed to when a MS was connected to which BTS
	 * 
	 * @return Array of SingleBTS with Timestamp. Timestamp indicates since when
	 *         this BTS was used by the MS
	 * @throws SQLException
	 *             when SQL connection is unavailable or some problem occurs
	 */
	private SingleBTS[] getBTSlist() throws SQLException {
		// System.out.println("getBtslist called");
		// LinkedList<SingleBTS> list = new LinkedList<SingleBTS>();
		usedBTS_rs = st.executeQuery(queryusedBTS());
		int y = getRowCount(usedBTS_rs);
		int n = 0;
		SingleBTS[] BTSarray = new SingleBTS[y];
		// array mit usedBTS erstellen
		while (usedBTS_rs.next()) {
			String name = usedBTS_rs.getString("location");
			Date time = usedBTS_rs.getTimestamp("cur_timestamp");
			int arfcn = usedBTS_rs.getInt("arfcn");
			// use SingleBTS to represent usedBTS
			BTSarray[n] = new SingleBTS(arfcn, 0, 0, false, time, name);
			n++;
		}
		return BTSarray;
		// für jeden MR einen Binary Search machen. Das Ergebnis ist die
		// benutzte Zelle (weil binary search den insert point findet)

	}

	// queries for all measurementResults for given IMSI and time
	private String queryMR() {
		String beginning = dateToString(start);
		String ending = dateToString(end);
		String query = "SELECT MR.cur_timestamp, 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, RXQ_FULL_dl, RXQ_SUB_dl, RXQ_FULL_ul, RXQ_SUB_ul "
				+ "FROM measurementResults MR "
				+ "JOIN action ON MR.cur_timestamp BETWEEN "
				+ "\""
				+ beginning
				+ "\""
				+ " AND "
				+ "\""
				+ ending
				+ "\""
				+ " AND action.IMSI = "
				+ IMSI + " AND MR.actionRefID = action.ID";
		System.out.println("Query for MR is: " + query);
		return query;
	}

	// queries usedBTS
	private String queryusedBTS() {
		String beginning = dateToString(start);
		String ending = dateToString(end);
		String query = "select bts.cur_timestamp, " + "bts.bts, "
				+ "arfcn.arfcn, " + "arfcn.location " + "from usedBTS bts "
				+ "join action on " + "imsi = " + IMSI + " and "
				+ "bts.cur_timestamp BETWEEN \"" + beginning + "\"" + "AND "
				+ "\"" + ending + "\" " + "AND bts.actionRefID = action.ID "
				+ "JOIN arfcn ON bts.bts = arfcn.bts " + "ORDER BY bts.ID";
		System.out.println("BTSQuery is: " + query);
		return query;
	}

	// returns date string in mySQL-fashion
	public static String dateToString(Date time) {
		return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S").format(time);
		// 2011-01-13 19:51:55.0
	}

	// parses mySQL date to java date
	public static Date stringToDate(String s) {
		try {
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S").parse(s);
		} catch (ParseException e) {
			System.out.println("Cannot parse SQL timestamp");
			e.printStackTrace();
			System.exit(-1);
			return null;
		}
	}

	// finds usedBTS index for a measured BTS
	// btsnames: SQL table "usedBTS"
	private int searchForUsedBTS(SingleBTS[] btsnames, Date timestamp) {

		// System.out.print("searchInBetween called...");
		SingleBTS searchelement = new SingleBTS(0, 0, 0, false, timestamp,
				"name");
		int between = Arrays.binarySearch(btsnames, searchelement);
		if (between < 0) {
			between = Math.abs(between);
			between = between - 2;
		}
		// if between < 0 it means that the call would be invalid because there
		// is no usedBTS assignable. A call was before a BTS got assigned
		// through OpenBSC. Check if there is a x second margin!

		// System.out.println("ended!");

		return between;
	}

	// not as speedy as it could be
	/**
	 * gets name of bts for given arfcn. If name is not found this might
	 * indicate that the logging databse is out of date or this is a foreign
	 * BTS, for example from E-Plus
	 */
	private String searchBTSName(SingleBTS[] btsnames, int arfcn) {
		// System.out.println("searchBTSName called");
		for (int i = 0; i < btsnames.length; i++) {
			if (btsnames[i].ARFCN == arfcn)
				return btsnames[i].name;
		}
		// System.out.println("arfcn Database is out of Date!");
		return "unknown - possible foreign BTS";

	}

	// would be faster with a "sweep" approach! For example: get bts counter.
	// start searching from specific point

	private void searchCorrespondingCoords(LinkedList<GPScoordinate> gpslist,
			SingleBTS[] btsarray, long matchtime) {
		// create gpsarray out of gpslist to search efficiently
		GPScoordinate[] gpsarray = new GPScoordinate[1];
		gpsarray = gpslist.toArray(gpsarray);
		// create BTS list
		LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
		// recycle gpslist. Clear it
		gpslist.clear();
		boolean insert = false;
		GPScoordinate bestfit = new GPScoordinate(null, 0, 'N', 0, 'W', false);
		int discarded = 0;
		// traverse every bts, find best gps coordinate
		for (SingleBTS singleBTS : btsarray) {
			// check for FullBTS. After a FullBTS, other BTS follow from the
			// Neighbor list!
			if (singleBTS.fullBTS) {
				bestfit = getBestCoord(gpsarray, singleBTS);
				// check for matchtime
				if (Math.abs(bestfit.time.getTime() - singleBTS.time.getTime()) < matchtime) {
					insert = true;
				} else
					insert = false;
			}
			if (insert) {
				// if insert is true, bestfit is securely initlialized!

				gpslist.add(bestfit);
				btslist.add(singleBTS);

			} else
				// do not insert any following SingleBTS. GPS coordinat and BTS
				// time are too far away!
				discarded++;

		}
		System.out.println("Measurements without GPS point: " + discarded);
		// save lists to arrays
		this.btsarray = btslist.toArray(new SingleBTS[0]);
		this.gpsarray = gpslist.toArray(new GPScoordinate[0]);
		if (this.btsarray.length != this.gpsarray.length)
			System.out.println("Array sizes not correct: btsarray"
					+ btsarray.length + "Elements, gpsarray: "
					+ gpsarray.length + " Elements");

	}

	private GPScoordinate getBestCoord(GPScoordinate[] gpsarray,
			SingleBTS singleBTS) {
		int hit = Arrays.binarySearch(gpsarray, singleBTS.time);
		// search for best fit!
		if (hit < 0) {
			hit++;
			hit = Math.abs(hit);
		}
		// if hit is at start of the array, there's nothing more to do
		if (hit == 0) {
			return gpsarray[hit];
		}
		// hit is the last element. Take it
		else if (hit == gpsarray.length) {
			return gpsarray[hit - 1];
		}
		// hit is in between the array bounds. Check hit and hit-1 for best fit
		else {
			long left = gpsarray[hit - 1].time.getTime();
			long right = gpsarray[hit].time.getTime();
			long element = singleBTS.time.getTime();
			if (Math.abs(left - element) < Math.abs(right - element)) {
				return gpsarray[hit - 1];
			} else
				return gpsarray[hit];

		}

	}

	private int getRowCount(ResultSet rs) throws SQLException {
		rs.last();
		int i = rs.getRow();
		rs.beforeFirst();
		return i;

	}

	/**
	 * 
	 * @return Names and Coordinates of BTS stored in the sql database
	 * @throws SQLException
	 */
	private SingleBTS[] getBTSnames() throws SQLException {
		// System.out.println("getBTSNames called");
		ResultSet btsNames = st.executeQuery("Select * from arfcn");
		LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
		SingleBTS currentBTS = null;
		while (btsNames.next()) {
			int ARFCN = btsNames.getInt("arfcn");
			String name = btsNames.getString("location");
			currentBTS = new SingleBTS(ARFCN, 0, 0, false, null, name);
			// btslist.add(new SingleBTS(ARFCN, 0, 0, false, null, name));
			double coord1 = btsNames.getDouble("CoordNS");
			double coord2 = btsNames.getDouble("CoordEW");
			currentBTS.coordinate = new GPScoordinate(null, coord1, 'N',
					coord2, 'E', true);
			btslist.add(currentBTS);
		}
		// random part, just to initiate a SingleBTS array
		SingleBTS[] instancearray = new SingleBTS[1];
		instancearray[0] = new SingleBTS(123, 0, 0, false, null, "unknown");
		return btslist.toArray(instancearray);

	}

	/**
	 * Adds sql entries from another sqlreader to this one. BTS names stored in
	 * SQLreader must be the same!
	 * 
	 * @param item
	 */
	public void merge(sqlreader item) {
		// only merge arrays!
		int totalsizeGPS = item.gpsarray.length + this.gpsarray.length + 10;
		int totalsizeBTS = item.btsarray.length + this.btsarray.length + 10;
		ArrayList<GPScoordinate> tempGPS = new ArrayList<GPScoordinate>(
				totalsizeGPS);
		for (int i = 0; i < gpsarray.length; i++) {
			tempGPS.add(gpsarray[i]);
		}
		for (int i = 0; i < item.gpsarray.length; i++) {
			tempGPS.add(item.gpsarray[i]);
		}

		ArrayList<SingleBTS> tempBTS = new ArrayList<SingleBTS>(totalsizeBTS);
		for (int i = 0; i < btsarray.length; i++) {
			tempBTS.add(btsarray[i]);
		}
		for (int i = 0; i < item.btsarray.length; i++) {
			tempBTS.add(item.btsarray[i]);
		}

		// back to array again
		gpsarray = tempGPS.toArray(gpsarray);
		btsarray = tempBTS.toArray(btsarray);
		IMSI = 0;
	}
}