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 results = new ArrayList(); public int polltime = 3000; public ArrayList phones; private Long[] IMSIs; // private boolean interrupt = false; /** * @param args * @throws InterruptedException */ public static void main(String[] args) throws InterruptedException { ArrayList blalist = new ArrayList(); blalist.add(1l); blalist.add(1l); blalist.add(1l); blalist.add(3l); blalist.add(4l); ArrayList result = new ArrayList(new HashSet(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 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 rsBTS = getUsedBTS(rsMR); ArrayList 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 mergePhones(ArrayList phones, Long[] IMSIs) { ArrayList result = new ArrayList(); 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 averagePhones(ArrayList phones) { ArrayList result = new ArrayList(); 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 mergeMrBts(ResultSet rsMR, ArrayList rsBTS) throws SQLException { ArrayList phones = new ArrayList(); 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 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 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 result = new ArrayList(); 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 IMSIs = new ArrayList(); rs.beforeFirst(); while (rs.next()) { IMSIs.add(rs.getLong("IMSI")); } // make Hashset ArrayList result = new ArrayList(new HashSet(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(); } } }