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 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 btslist = new LinkedList(); // 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 list = new LinkedList(); 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 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 btslist = new LinkedList(); // 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 btslist = new LinkedList(); 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 tempGPS = new ArrayList( 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 tempBTS = new ArrayList(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; } }