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