summaryrefslogtreecommitdiffstats
path: root/Parse/sqlreader.java
diff options
context:
space:
mode:
Diffstat (limited to 'Parse/sqlreader.java')
-rw-r--r--Parse/sqlreader.java483
1 files changed, 483 insertions, 0 deletions
diff --git a/Parse/sqlreader.java b/Parse/sqlreader.java
new file mode 100644
index 0000000..8268810
--- /dev/null
+++ b/Parse/sqlreader.java
@@ -0,0 +1,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;
+ }
+}