package com.samknows.measurement.storage; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Locale; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import com.samknows.libcore.SKPorting; import com.samknows.measurement.SKApplication; import com.samknows.measurement.SKApplication.eNetworkTypeResults; import com.samknows.measurement.activity.components.SKGraphForResults.DATERANGE_1w1m3m1y; import com.samknows.measurement.storage.StorageTestResult.*; //Helper class for accessing the data stored in the SQLite DB //It Exposes only the methods to populate the Interface //and to insert new data in the db public class DBHelper { // grapdata JSONObject keys public static final String GRAPHDATA_TYPE = "type"; public static final String GRAPHDATA_YLABEL = "y_label"; public static final String GRAPHDATA_STARTDATE = "start_date"; public static final String GRAPHDATA_ENDDATE = "end_date"; public static final String GRAPHDATA_RESULTS = "results"; public static final String GRAPHDATA_RESULTS_DATETIME = "datetime"; public static final String GRAPHDATA_RESULTS_VALUE = "value"; public static final String[] GRAPHDATA_JSON_KEYS = { GRAPHDATA_TYPE, GRAPHDATA_YLABEL, GRAPHDATA_STARTDATE, GRAPHDATA_ENDDATE, GRAPHDATA_RESULTS, GRAPHDATA_RESULTS_DATETIME, GRAPHDATA_RESULTS_VALUE }; // gridtata JSONObject keys public static final String GRIDDATA_TYPE = "type"; public static final String GRIDDATA_RESULTS = "results"; public static final String GRIDDATA_RESULTS_ARCHIVEINDEX = "archiveindex"; public static final String GRIDDATA_RESULTS_DTIME = "dtime"; public static final String GRIDDATA_RESULTS_DATETIME = "datetime"; public static final String GRIDDATA_RESULTS_LOCATION = "location"; public static final String GRIDDATA_RESULTS_RESULT = "result"; public static final String GRIDDATA_RESULTS_SUCCESS = "success"; public static final String GRIDDATA_RESULTS_HRRESULT = "hrresult"; public static final String GRIDDATA_RESULTS_NETWORK_TYPE = "network_type"; public static final String[] GRIDDATA_JSON_KEYS = { GRIDDATA_TYPE, GRIDDATA_RESULTS }; // averagedata JSONObject keys public static final String AVERAGEDATA_TYPE = "type"; public static final String AVERAGEDATA_VALUE = "value"; public static final String[] AVERAGEDATA_JSON_KEYS = { AVERAGEDATA_TYPE, AVERAGEDATA_VALUE }; // archivedata JSONObject keys public static final String ARCHIVEDATA_INDEX = "index"; public static final String ARCHIVEDATA_DTIME = "dtime"; public static final String ARCHIVEDATA_DATETIME = "datetime"; public static final String ARCHIVEDATA_ACTIVEMETRICS = "activemetrics"; public static final String ARCHIVEDATA_ACTIVEMETRICS_TEST = "test"; public static final String ARCHIVEDATA_ACTIVEMETRICS_DTIME = "dtime"; public static final String ARCHIVEDATA_ACTIVEMETRICS_DATETIME = "datetime"; public static final String ARCHIVEDATA_ACTIVEMETRICS_LOCATION = "location"; public static final String ARCHIVEDATA_ACTIVEMETRICS_RESULT = "result"; public static final String ARCHIVEDATA_ACTIVEMETRICS_SUCCESS = "success"; public static final String ARCHIVEDATA_ACTIVEMETRICS_HRRESULT = "hrresult"; public static final String ARCHIVEDATA_PASSIVEMETRICS_METRIC = "metric"; public static final String ARCHIVEDATA_PASSIVEMETRICS_TYPE = "type"; public static final String ARCHIVEDATA_PASSIVEMETRICS_VALUE = "value"; public static final String ARCHIVEDATA_PASSIVEMETRICS = "passivemetrics"; public static final String[] ARCHIVEDATA_JSON_KEYS = { ARCHIVEDATA_INDEX, ARCHIVEDATA_DTIME, ARCHIVEDATA_DATETIME, ARCHIVEDATA_ACTIVEMETRICS, ARCHIVEDATA_PASSIVEMETRICS }; // archivedatasummary JSONObject keys public static final String ARCHIVEDATASUMMARY_COUNTER = "counter"; public static final String ARCHIVEDATASUMMARY_STARTDATE = "startdate"; public static final String ARCHIVEDATASUMMARY_ENDDATE = "enddate"; public static final String ARCHIVEDATASUMMARY_TESTCOUNTER = "test_counter"; public static final String[] ARCHIVEDATASUMMARY_JSON_KEYS = { ARCHIVEDATASUMMARY_COUNTER, ARCHIVEDATASUMMARY_STARTDATE, ARCHIVEDATASUMMARY_ENDDATE }; // members private SQLiteDatabase database; private final SKSQLiteHelper dbhelper; private final static Object sync = new Object(); // Constructor used to set the context public DBHelper(Context context) { dbhelper = new SKSQLiteHelper(context); } private boolean open() { boolean ret = false; try { database = dbhelper.getWritableDatabase(); ret = true; } catch (SQLException sqle) { SKPorting.sAssertE(this, "Error in opening the database.", sqle); } return ret; } private void close() { database.close(); } public synchronized boolean isEmpty() { synchronized (sync) { boolean ret = false; if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM " + SKSQLiteHelper.TABLE_TESTRESULT, null); cursor.moveToFirst(); ret = cursor.getInt(0) == 0; cursor.close(); close(); return ret; } } // converter private static String testValueToGraph(DETAIL_TEST_ID test_type_id, double value) { String ret = value + ""; switch (test_type_id) { case UPLOAD_TEST_ID: case DOWNLOAD_TEST_ID: ret = (value / 1000000) + ""; break; case LATENCY_TEST_ID: case JITTER_TEST_ID: ret = ((long) value / 1000) + ""; break; case PACKETLOSS_TEST_ID: ret = String.format("%.2f", value); break; default: SKPorting.sAssert(false); break; } return ret; } // Translate an entry in the test result table results entry if a JSONObject // for archivedata private static JSONObject testResultToArchiveData(JSONObject tr) { JSONObject ret = new JSONObject(); try { String test_type = tr.getString(SKSQLiteHelper.TR_COLUMN_TYPE); DETAIL_TEST_ID test_type_id = StorageTestResult.testStringToId(test_type); long dtime = tr.getLong(SKSQLiteHelper.TR_COLUMN_DTIME); String location = tr.getString(SKSQLiteHelper.TR_COLUMN_LOCATION); int success = tr.getInt(SKSQLiteHelper.TR_COLUMN_SUCCESS); double result = tr.getDouble(SKSQLiteHelper.TR_COLUMN_RESULT); String hrresult = StorageTestResult.hrResult(test_type_id, result); ret.put(ARCHIVEDATA_ACTIVEMETRICS_TEST, test_type_id.getValueAsInt()); ret.put(ARCHIVEDATA_ACTIVEMETRICS_DTIME, dtime); ret.put(ARCHIVEDATA_ACTIVEMETRICS_SUCCESS, success + ""); ret.put(ARCHIVEDATA_ACTIVEMETRICS_LOCATION, location); ret.put(ARCHIVEDATA_ACTIVEMETRICS_RESULT, result); ret.put(ARCHIVEDATA_ACTIVEMETRICS_HRRESULT, hrresult); } catch (JSONException je) { SKPorting.sAssert(false); } return ret; } // Translate an entry in the test result table results entry if a JSONObject // for griddata private static JSONObject testResultToGridData(JSONObject tr) { JSONObject ret = new JSONObject(); try { String test_type = tr.getString(SKSQLiteHelper.TR_COLUMN_TYPE); DETAIL_TEST_ID test_type_id = StorageTestResult.testStringToId(test_type); long dtime = tr.getLong(SKSQLiteHelper.TR_COLUMN_DTIME); String location = tr.getString(SKSQLiteHelper.TR_COLUMN_LOCATION); int success = tr.getInt(SKSQLiteHelper.TR_COLUMN_SUCCESS); double result = tr.getDouble(SKSQLiteHelper.TR_COLUMN_RESULT); String hrresult = StorageTestResult.hrResult(test_type_id, result); ret.put(GRIDDATA_RESULTS_DTIME, dtime); ret.put(GRIDDATA_RESULTS_SUCCESS, success + ""); ret.put(GRIDDATA_RESULTS_LOCATION, location); ret.put(GRIDDATA_RESULTS_RESULT, result); ret.put(GRIDDATA_RESULTS_HRRESULT, hrresult); } catch (JSONException je) { SKPorting.sAssert(false); } return ret; } // Translate an entry in the test result table results entry if a JSONObject // for graphdata private static JSONObject testResultToGraphData(DETAIL_TEST_ID test_type_id, JSONObject tr) { JSONObject ret = new JSONObject(); try { String value = testValueToGraph(test_type_id, tr.getDouble(SKSQLiteHelper.TR_COLUMN_RESULT)); //value = "0.00499"; // TODO - this is for DEBUG/TESTING only! long dtime = tr.getLong(SKSQLiteHelper.TR_COLUMN_DTIME); ret.put(GRAPHDATA_RESULTS_DATETIME, "" + dtime); ret.put(GRAPHDATA_RESULTS_VALUE, value); } catch (JSONException je) { SKPorting.sAssert(false); } return ret; } private static JSONObject passiveMetricToArchiveData(JSONObject pm) { JSONObject ret = new JSONObject(); try { String metric = pm.getString(SKSQLiteHelper.PM_COLUMN_METRIC); String type = pm.getString(SKSQLiteHelper.PM_COLUMN_TYPE); String value = pm.getString(SKSQLiteHelper.PM_COLUMN_VALUE); ret.put(ARCHIVEDATA_PASSIVEMETRICS_METRIC, metric); ret.put(ARCHIVEDATA_PASSIVEMETRICS_TYPE, type); //value = "0.00499"; // TODO - this is for DEBUG/TESTING only! ret.put(ARCHIVEDATA_PASSIVEMETRICS_VALUE, value); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "error creating json object", je); } return ret; } // Translatror private static String testIdToGraphLabel(DETAIL_TEST_ID test_type_id) { String ret = ""; switch (test_type_id) { case UPLOAD_TEST_ID: case DOWNLOAD_TEST_ID: ret = "Mbps"; break; case LATENCY_TEST_ID: case JITTER_TEST_ID: ret = "ms"; break; case PACKETLOSS_TEST_ID: ret = "%"; break; default: SKPorting.sAssert(false); break; } return ret; } // Returns the JSONObject containing the data to draw a graph for one test // of type test_type_id between startdtime and enddtime // Whereas on iOS, the equivalent search would average all data by day; // on Android, this returns all the point data in the specified period. public JSONObject fetchGraphData(DETAIL_TEST_ID test_type_id, long startdtime, long enddtime, DATERANGE_1w1m3m1y dateRange) { JSONObject ret = new JSONObject(); String test_type = StorageTestResult.testIdToString(test_type_id); try { ret.put(GRAPHDATA_TYPE, test_type_id); ret.put(GRAPHDATA_YLABEL, testIdToGraphLabel(test_type_id)); ret.put(GRAPHDATA_STARTDATE, startdtime + ""); ret.put(GRAPHDATA_ENDDATE, enddtime + ""); List<JSONObject> entries = getTestResultByTypeAndInterval( test_type, startdtime, enddtime, " AND success <> 0"); JSONArray results = new JSONArray(); for (JSONObject jo : entries) { results.put(testResultToGraphData(test_type_id, jo)); } ret.put(GRAPHDATA_RESULTS, results); // if (dateRange == DATERANGE_1w1m3m1y.DATERANGE_1w1m3m1y_ONE_DAY) { // // No need to specifically extract "24hours" data, c.f. iOS; // // as the data returned is single point data on Android // // (whereas on iOS, it is returned averaged by day). // } } catch (JSONException je) { SKPorting.sAssert(false); } return ret; } // Returns the JSONObject containing the data to populate a grid for a // specific test // with id test_type_id, returns offset entry starting from index public JSONObject getGridData(DETAIL_TEST_ID test_type_id, int index, int offset, long startdtime, long enddtime) { JSONObject ret = new JSONObject(); String test_type = StorageTestResult.testIdToString(test_type_id); List<JSONObject> entries = getFilteredTestResultsInDateRange(test_type, index, offset, startdtime, enddtime); try { ret.put(GRIDDATA_TYPE, test_type_id); JSONArray results = new JSONArray(); for (JSONObject jo : entries) { long testId = jo.getLong(SKSQLiteHelper.TR_COLUMN_BATCH_ID); String networkType = ""; List<JSONObject> passive_metrics = getPassiveMetrics(testId); for (JSONObject pm : passive_metrics) { String type = pm.getString(SKSQLiteHelper.PM_COLUMN_METRIC); if (type.equals("activenetworktype")) { String value = pm.getString(SKSQLiteHelper.PM_COLUMN_VALUE); networkType = value; break; } } JSONObject theGridData = testResultToGridData(jo); if (networkType != null) { theGridData.put(GRIDDATA_RESULTS_NETWORK_TYPE, networkType); } results.put(theGridData); } ret.put(GRIDDATA_RESULTS, results); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "Error in creating data for the grid"); } return ret; } // Return archived data from the database. // This can return just one, indexed item - or all items. // The result(s) is/are returned via a JSONArray. // // Parameters: // index is the position of the archive data in the database // index = -1 - return ALL archive items, via in the JSONArray. // index = 0...N - Just one (or zero!) object in the JSONArray, at position "index". public JSONArray getArchiveData(int index) { synchronized (sync) { JSONArray arrayRet = new JSONArray(); if (open() == false) { SKPorting.sAssert(getClass(), false); return arrayRet; } // A consequence of the system "collecting" metrics both when we start *and* stop a test, is that // this leads to multiple rows in the passive_metric table, with the same batch_id and metric...! // So, our query needs to cater for this... String metricValue = ""; if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Any) { metricValue = "'mobile', 'WiFi'"; } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Mobile) { metricValue = "'mobile'"; } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_WiFi) { metricValue = "'WiFi'"; } /* SELECT _id, dtime, manual FROM test_batch AS tb WHERE tb._id IN (select tb2._id from test_batch as tb2, passive_metric as pm2 where pm2.batch_id = tb2._id and pm2.metric = 'activenetworktype' and pm2.value = 'mobile') ORDER BY dtime DESC; */ StringBuilder MY_QUERY = new StringBuilder(); MY_QUERY.append("SELECT _id, dtime, manual "); MY_QUERY.append("FROM test_batch AS tb "); MY_QUERY.append("WHERE tb._id IN "); MY_QUERY.append("(select tb2._id "); MY_QUERY.append(" from test_batch as tb2,"); MY_QUERY.append(" passive_metric as pm2 "); MY_QUERY.append(" where pm2.batch_id = tb2._id "); MY_QUERY.append(" and pm2.metric = 'activenetworktype' "); MY_QUERY.append(" and pm2.value in (").append(metricValue).append(")) "); MY_QUERY.append("ORDER BY dtime DESC "); //Log.d("!!", MY_QUERY.toString()); Cursor cursor1 = database.rawQuery(MY_QUERY.toString(), new String[]{}); if (cursor1 == null) { SKPorting.sAssert(getClass(), false); close(); return null; } if (cursor1.moveToFirst() == false) { // Nothing to return! cursor1.close(); close(); return arrayRet; } for (int thisIndex = 0; ; thisIndex++) { if (cursor1.isAfterLast()) { // We've reached the end! break; } // Find the indexed item - or, if index is -1, find ALL items. if (index == -1) { // We're looking at ALL items. } else { // We just want ONE item. if (thisIndex != index) { // This current item is NOT of interest - keep looking! cursor1.moveToNext(); continue; } } // To reach here, this is an item of interest. // Either because we're returning all items, or this is the specific item of interest. long test_batch_id = cursor1.getLong(0); long test_batch_time = cursor1.getLong(1); String selection = SKSQLiteHelper.TR_COLUMN_BATCH_ID + " = " + test_batch_id; List<JSONObject> tests = getTestResults(selection); List<JSONObject> passive_metrics = getPassiveMetrics(test_batch_id); JSONArray j_tests = new JSONArray(); JSONArray j_pm = new JSONArray(); try { JSONObject objRet = new JSONObject(); objRet.put(ARCHIVEDATA_INDEX, index + ""); objRet.put(ARCHIVEDATA_DTIME, test_batch_time + ""); for (JSONObject jo : tests) { j_tests.put(testResultToArchiveData(jo)); } for (JSONObject jo : passive_metrics) { j_pm.put(passiveMetricToArchiveData(jo)); } objRet.put(ARCHIVEDATA_ACTIVEMETRICS, j_tests); objRet.put(ARCHIVEDATA_PASSIVEMETRICS, j_pm); arrayRet.put(objRet); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "Error in converting tests and passive metrics for archive data" + je.getMessage()); } if (thisIndex == index) { // We've found the sole item of interest! break; } cursor1.moveToNext(); } cursor1.close(); close(); if (index == -1) { // We wanted all items. It is possible that there were no items! } else { // We wanted just ONE item. // Did we find it? // It would usually be an error if we didn't find it! SKPorting.sAssert(getClass(), arrayRet.length() == 1); } return arrayRet; } } public JSONObject getSingleArchiveDataItemAtIndex(int index) { JSONArray arrayRet = getArchiveData(index); if (arrayRet.length() == 0) { SKPorting.sAssert(getClass(), false); return null; } SKPorting.sAssert(getClass(), arrayRet.length() == 1); try { return (JSONObject)arrayRet.get(0); } catch (JSONException e) { SKPorting.sAssert(getClass(), false); return null; } } // Return a summary of the archive data public JSONObject getArchiveDataSummary() { synchronized (sync) { //Trace.beginSection("getArchiveDataSummary"); List<Integer> batches = getTestBatchesByPassiveMetric(getPassiveMetricsFilter()); JSONObject ret = new JSONObject(); if (open() == false) { //Trace.endSection(); SKPorting.sAssert(getClass(), false); return ret; } // test batch counter // A consequence of the system "collecting" metrics both when we start *and* stop a test, is that // this leads to multiple rows in the passive_metric table, with the same batch_id and metric...! // So, our query needs to cater for this... String metricValue = ""; if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Any) { // Nothing to append! metricValue = "'mobile', 'WiFi'"; } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Mobile) { metricValue = "'mobile'"; } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_WiFi) { metricValue = "'WiFi'"; } // Query the number of test batches (only of the required network type!), together with // min/max test dates. /* SELECT COUNT(*), MIN(tb.dtime), MAX(tb.dtime) FROM test_batch AS tb WHERE tb._id in (select tb2._id from test_batch as tb2, passive_metric as pm2 where pm2.batch_id = tb2._id and pm2.metric = 'activenetworktype' and pm2.value = 'mobile'); */ StringBuilder MY_QUERY = new StringBuilder(); MY_QUERY.append("SELECT COUNT(*), MIN(tb.dtime), MAX(tb.dtime) "); MY_QUERY.append("FROM test_batch AS tb "); MY_QUERY.append("WHERE tb._id in "); MY_QUERY.append("(select tb2._id "); MY_QUERY.append(" from test_batch as tb2,"); MY_QUERY.append(" passive_metric as pm2 "); MY_QUERY.append(" where pm2.batch_id = tb2._id "); MY_QUERY.append(" and pm2.metric = 'activenetworktype' "); MY_QUERY.append(" and pm2.value in (").append(metricValue).append(")) "); //Log.d("!!", MY_QUERY.toString()); Cursor cursor1 = database.rawQuery(MY_QUERY.toString(), new String[]{}); String counter = "0"; String min = "0"; String max = "0"; if (cursor1.moveToFirst() == true) { // Got something! counter = cursor1.getLong(0) + ""; min = cursor1.getLong(1) + ""; max = cursor1.getLong(2) + ""; } else { // The first time this query is run, there might be zero rows returned; // do not treat this as a failure. } cursor1.close(); /* SELECT tr.type, COUNT(*) FROM test_result AS tr WHERE tr.batch_id IN (select pm.batch_id FROM passive_metric AS pm WHERE pm.metric = 'activenetworktype' AND pm.value = 'mobile') GROUP BY tr.type; */ // test results counter MY_QUERY = new StringBuilder(); MY_QUERY.append("SELECT tr.type, COUNT(*) "); MY_QUERY.append("FROM test_result AS tr "); MY_QUERY.append("WHERE tr.batch_id IN "); MY_QUERY.append("(SELECT pm.batch_id "); MY_QUERY.append("FROM passive_metric AS pm "); MY_QUERY.append("WHERE pm.metric = 'activenetworktype' AND pm.value in (").append(metricValue).append(")) "); MY_QUERY.append("GROUP BY tr.type "); // Log.d("!!", MY_QUERY.toString()); Cursor cursor2 = database.rawQuery(MY_QUERY.toString(), new String[]{}); cursor2.moveToFirst(); JSONObject test_counter = new JSONObject(); while (!cursor2.isAfterLast()) { try { test_counter.put(StorageTestResult.testStringToId(cursor2.getString(0)) + "", cursor2.getInt(1) + ""); } catch (JSONException je) { SKPorting.sAssert(getClass(), false); } cursor2.moveToNext(); } cursor2.close(); try { ret.put(ARCHIVEDATASUMMARY_COUNTER, counter); ret.put(ARCHIVEDATASUMMARY_STARTDATE, min); ret.put(ARCHIVEDATASUMMARY_ENDDATE, max); ret.put(ARCHIVEDATASUMMARY_TESTCOUNTER, test_counter); } catch (JSONException je) { SKPorting.sAssert(getClass(), false); } close(); //Trace.endSection(); return ret; } } public void emptyTheDatabase() { synchronized (sync) { if (open() == false) { SKPorting.sAssert(getClass(), false); return; } database.delete(SKSQLiteHelper.TABLE_TESTRESULT, null, null); database.delete(SKSQLiteHelper.TABLE_PASSIVEMETRIC, null, null); database.delete(SKSQLiteHelper.TABLE_TESTBATCH, null, null); close(); } } public long insertTestBatch(JSONObject test_batch, JSONArray tests, JSONArray passive_metrics) { long test_batch_id; test_batch_id = insertTestBatch(test_batch); insertTestResult(tests, test_batch_id); insertPassiveMetric(passive_metrics, test_batch_id); return test_batch_id; } public long insertTestBatch(JSONObject test_batch, List<JSONObject> tests, List<JSONObject> passive_metrics) { long test_batch_id = insertTestBatch(test_batch); insertTestResult(tests, test_batch_id); insertPassiveMetric(passive_metrics, test_batch_id); return test_batch_id; } public long insertTestBatch(JSONObject test_batch) { long start_time; int run_manually; long ret = -1; try { start_time = test_batch.getLong(TestBatch.JSON_DTIME); run_manually = Integer.parseInt(test_batch .getString(TestBatch.JSON_RUNMANUALLY)); ret = insertTestBatch(start_time, run_manually); } catch (JSONException je) { SKPorting.sAssertE(this, "Error in creating json object.", je); } return ret; } public long insertTestBatch(long start_time, int run_manually) { synchronized (sync) { if (open() == false) { SKPorting.sAssert(getClass(), false); return -1; } ContentValues values = new ContentValues(); values.put(SKSQLiteHelper.TB_COLUMN_DTIME, start_time); values.put(SKSQLiteHelper.TB_COLUMN_MANUAL, run_manually); long insertId = database.insert(SKSQLiteHelper.TABLE_TESTBATCH, null, values); close(); return insertId; } } public void insertTestResult(List<JSONObject> tests, long test_batch_id) { for (JSONObject t : tests) { insertTestResult(t, test_batch_id); } } public void insertTestResult(JSONArray tests, long test_batch_id) { for (int i = 0; i < tests.length(); i++) { try { insertTestResult(tests.getJSONObject(i), test_batch_id); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "Error in converting JSONArray.", je); } } } public void insertTestResult(JSONObject test, long test_batch_id) { try { String type_name = test.getString(StorageTestResult.JSON_TYPE_NAME); long dtime = test.getLong(StorageTestResult.JSON_DTIME); long success = test.getLong(StorageTestResult.JSON_SUCCESS); double result = test.getDouble(StorageTestResult.JSON_RESULT); String location = test.getString(StorageTestResult.JSON_LOCATION); insertTestResult(type_name, dtime, success, result, location, test_batch_id); } catch (JSONException je) { SKPorting.sAssertE( DBHelper.class, "Error in converting TestResult JSONObject in database entry.", je); } } private void insertTestResult(String type_name, long dtime, long success, double result, String location, long test_batch_id) { synchronized (sync) { if (open() == false) { SKPorting.sAssert(getClass(), false); return; } ContentValues values = new ContentValues(); values.put(SKSQLiteHelper.TR_COLUMN_DTIME, dtime); values.put(SKSQLiteHelper.TR_COLUMN_TYPE, type_name); values.put(SKSQLiteHelper.TR_COLUMN_LOCATION, location); values.put(SKSQLiteHelper.TR_COLUMN_SUCCESS, success); values.put(SKSQLiteHelper.TR_COLUMN_RESULT, result); values.put(SKSQLiteHelper.TR_COLUMN_BATCH_ID, test_batch_id); long id = database.insert(SKSQLiteHelper.TABLE_TESTRESULT, null, values); close(); } } public void insertPassiveMetric(JSONArray metrics, long test_batch_id) { for (int i = 0; i < metrics.length(); i++) { try { insertPassiveMetric(metrics.getJSONObject(i), test_batch_id); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "Error in converting JSONArray: " + je.getMessage()); } } } public void insertPassiveMetric(List<JSONObject> metrics, long test_batch_id) { for (JSONObject pm : metrics) { insertPassiveMetric(pm, test_batch_id); } } private void insertPassiveMetric(JSONObject metric, long test_batch_id) { String metric_type; long dtime; String value; String type; try { metric_type = metric.getString(PassiveMetric.JSON_METRIC_NAME); dtime = metric.getLong(PassiveMetric.JSON_DTIME); value = metric.getString(PassiveMetric.JSON_VALUE); type = metric.getString(PassiveMetric.JSON_TYPE); insertPassiveMetric(metric_type, type, dtime, value, test_batch_id); } catch (JSONException je) { SKPorting.sAssertE( DBHelper.class, "Error in converting JSONObject ot passive metric: " + je.getMessage()); } } private void insertPassiveMetric(String metric_type, String type, long dtime, String value, long test_batch_id) { synchronized (sync) { ContentValues values = new ContentValues(); if (open() == false) { SKPorting.sAssert(getClass(), false); return; } // A consequence of the system "collecting" metrics both when we start *and* stop a test, is that // this leads to multiple rows in the passive_metric table, with the same batch_id and metric...! if (metric_type.equals("activenetworktype")) { Log.d("activenetworktype", "value=" + value); //Log.d("activenetworktype", Thread.currentThread().getStackTrace().toString()); } // if (metric_type.equals("activenetworktype")) { // if (OtherUtils.isThisDeviceAnEmulator() == true) { // if (value.equals(DCSConvertorUtil.convertConnectivityType(ConnectivityManager.TYPE_MOBILE))) { // // Can force to save as particular network type, to assist in debugging! // value = DCSConvertorUtil.convertConnectivityType(ConnectivityManager.TYPE_WIFI); // } // } // } values.put(SKSQLiteHelper.PM_COLUMN_METRIC, metric_type); values.put(SKSQLiteHelper.PM_COLUMN_TYPE, type); values.put(SKSQLiteHelper.PM_COLUMN_DTIME, dtime); values.put(SKSQLiteHelper.PM_COLUMN_VALUE, value); values.put(SKSQLiteHelper.PM_COLUMN_BATCH_ID, test_batch_id); long id = database.insert(SKSQLiteHelper.TABLE_PASSIVEMETRIC, null, values); close(); } } // Returns all the TestResult stored in the db public List<JSONObject> getAllTestResults() { return getTestResults(); } // Returns all the TestResult stored in the db for a given test type public List<JSONObject> getAllTestResultsByType(String type) { String selection = String.format(Locale.US, "%s = '%s'", SKSQLiteHelper.TR_COLUMN_TYPE, type); return getTestResults(selection); } public List<JSONObject> getTestResultByTypeAndInterval(String type, long starttime, long endtime, String extraFilter) { String selection = String.format(Locale.US, "%s = '%s' AND %s BETWEEN %d AND %d %s", SKSQLiteHelper.TR_COLUMN_TYPE, type, SKSQLiteHelper.TR_COLUMN_DTIME, starttime, endtime, extraFilter); List<Integer> batches = getTestBatchesByPassiveMetric(getPassiveMetricsFilter()); if (batches == null || batches.size() == 0) { return new ArrayList<>(); } selection += " AND " + getInClause(SKSQLiteHelper.TR_COLUMN_BATCH_ID, batches); return getTestResults(selection); } // Returns all the TestResult stored in the db run in an interval public List<JSONObject> getAllTestResultsInterval(long starttime, long endtime) { String selection = String.format(Locale.US, "%s BETWEEN %d AND %d", SKSQLiteHelper.TR_COLUMN_DTIME, starttime, endtime); return getTestResults(selection); } // Returns n TestResult from the database for a given type after a specific // time /* * public List<JSONObject> getTestResults(String type, long starttime, int * n) { String selection = String.format(Locale.US, "%s = '%s' AND %s >= %d", * SKSQLiteHelper.TR_COLUMN_TYPE, type, SKSQLiteHelper.TR_COLUMN_DTIME, * starttime); return getTestResults(selection, n + ""); } */ // Returns n TestResult the i-th result for a given type, irrespective of date range. public List<JSONObject> getFilteredTestResults(String type, int startindex, int n) { String selection = String.format(Locale.US, "%s = '%s'", SKSQLiteHelper.TR_COLUMN_TYPE, type); String limit = String.format(Locale.US, "%d,%d", startindex, n); List<Integer> batches = getTestBatchesByPassiveMetric(getPassiveMetricsFilter()); if (batches == null || batches.size() == 0) { return new ArrayList<>(); } selection += " AND " + getInClause(SKSQLiteHelper.TR_COLUMN_BATCH_ID, batches); return getTestResults(selection, limit); } // Returns n TestResult the i-th result for a given type, given a date range. public List<JSONObject> getFilteredTestResultsInDateRange(String type, int startindex, int n, long starttime, long endtime) { String selection = String.format(Locale.US, "%s = '%s'", SKSQLiteHelper.TR_COLUMN_TYPE, type); String limit = String.format(Locale.US, "%d,%d", startindex, n); List<Integer> batches = getTestBatchesByPassiveMetric(getPassiveMetricsFilter()); if (batches == null || batches.size() == 0) { return new ArrayList<>(); } selection += " AND " + getInClause(SKSQLiteHelper.TR_COLUMN_BATCH_ID, batches); String selection2 = String.format(Locale.US, "AND dtime BETWEEN %d AND %d AND success <> 0", starttime, endtime); selection += selection2; return getTestResults(selection, limit); } // Returns a JSONArray with the averages for the tests in the interval // between starttime and endtime // the average is computed only on successful tests public JSONArray getAverageResults(long starttime, long endtime, List<Integer> test_batches) { synchronized (sync) { JSONArray ret = new JSONArray(); if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } String selection = String.format(Locale.US, "dtime BETWEEN %d AND %d AND success <> 0", starttime, endtime); if (test_batches != null && test_batches.size() == 0) { return ret; } if (test_batches != null) { selection += " AND " + getInClause(SKSQLiteHelper.TR_COLUMN_BATCH_ID, test_batches); } String averageColumn = String.format(Locale.US, "AVG(%s)", SKSQLiteHelper.TR_COLUMN_RESULT); String[] columns = { SKSQLiteHelper.TR_COLUMN_TYPE, averageColumn, "COUNT(*)" }; String groupBy = SKSQLiteHelper.TR_COLUMN_TYPE; Cursor cursor = database.query(SKSQLiteHelper.TABLE_TESTRESULT, columns, selection, null, groupBy, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { JSONObject curr = new JSONObject(); try { DETAIL_TEST_ID test_type_id = StorageTestResult.testStringToId(cursor .getString(0)); curr.put(AVERAGEDATA_TYPE, test_type_id.getValueAsInt() + ""); String value = StorageTestResult.hrResult(test_type_id, cursor.getDouble(1)); //value = "0.00499"; // TODO - this is for DEBUG/TESTING only! curr.put( AVERAGEDATA_VALUE, value); } catch (JSONException je) { SKPorting.sAssert(false); } ret.put(curr); cursor.moveToNext(); } cursor.close(); close(); return ret; } } // public JSONArray getAverageResults(long starttime, long endtime) { List<Integer> batches = getTestBatchesByPassiveMetric(starttime, endtime); return getAverageResults(starttime, endtime, batches); } private String getInClause(String field, List<Integer> values) { StringBuilder sb = new StringBuilder(); sb.append(field).append(" IN ("); for (Iterator<Integer> it = values.iterator(); it.hasNext();) { sb.append(it.next()); if (it.hasNext()) { sb.append(", "); } } sb.append(" )"); return sb.toString(); } // When retrieving averages, graph and grid data we have to filter by // Passive metric private String getPassiveMetricsFilter() { StringBuilder sb = new StringBuilder(); sb.append(" metric = 'activenetworktype' AND value in("); if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Any) { sb.append ("'mobile', 'WiFi'"); } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_Mobile) { sb.append("'mobile'"); } else if (SKApplication.getNetworkTypeResults() == SKApplication.eNetworkTypeResults.eNetworkTypeResults_WiFi) { sb.append("'WiFi'"); } sb.append(")"); return sb.toString(); } // Return a list of test batch ids with a passive metric value equal to // value in the specified period public List<Integer> getTestBatchesByPassiveMetric(String selection) { synchronized (sync) { List<Integer> ret = new ArrayList<>(); if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } String[] columns = { SKSQLiteHelper.PM_COLUMN_BATCH_ID }; Cursor cursor = database.query(SKSQLiteHelper.TABLE_PASSIVEMETRIC, columns, selection, null, SKSQLiteHelper.PM_COLUMN_BATCH_ID, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { ret.add(cursor.getInt(0)); cursor.moveToNext(); } cursor.close(); close(); return ret; } } public List<JSONObject> getTestBatches() { synchronized (sync) { List<JSONObject> ret = new ArrayList<>(); if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } String[] columns = { "_id", SKSQLiteHelper.PM_COLUMN_DTIME }; Cursor cursor = database.query(SKSQLiteHelper.TABLE_TESTBATCH, columns, null, null, null, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { JSONObject ret2 = new JSONObject(); try { ret2.put(columns[0], cursor.getLong(0)); ret2.put(columns[1], cursor.getLong(1)); } catch (JSONException je) { SKPorting.sAssert(false); } ret.add(ret2); cursor.moveToNext(); } cursor.close(); close(); return ret; } } public List<Integer> getTestBatchesByPassiveMetric(long start_time, long end_time) { String selection = String.format(Locale.US, SKSQLiteHelper.PM_COLUMN_DTIME + " BETWEEN %d AND %d", start_time, end_time); selection += " AND " + getPassiveMetricsFilter(); return getTestBatchesByPassiveMetric(selection); } private List<JSONObject> getTestResults() { return getTestResults(null, null); } private List<JSONObject> getTestResults(String selection) { return getTestResults(selection, null); } private List<JSONObject> getTestResults(String selection, String limit) { synchronized (sync) { List<JSONObject> ret = new ArrayList<>(); if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } Cursor cursor = database.query(SKSQLiteHelper.TABLE_TESTRESULT, SKSQLiteHelper.TABLE_TESTRESULT_ALLCOLUMNS, selection, null, null, null, SKSQLiteHelper.TEST_RESULT_ORDER, limit); cursor.moveToFirst(); while (!cursor.isAfterLast()) { ret.add(cursorTestResultToJSONObject(cursor)); cursor.moveToNext(); } cursor.close(); close(); return ret; } } private List<JSONObject> getPassiveMetrics(long test_batch_id) { synchronized (sync) { List<JSONObject> ret = new ArrayList<>(); if (open() == false) { SKPorting.sAssert(getClass(), false); return ret; } String selection = SKSQLiteHelper.PM_COLUMN_BATCH_ID + " = " + test_batch_id; Cursor cursor = database.query(SKSQLiteHelper.TABLE_PASSIVEMETRIC, SKSQLiteHelper.TABLE_PASSIVEMETRIC_ALLCOLUMNS, selection, null, null, null, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { ret.add(cursorPassiveMetricToJSONObject(cursor)); cursor.moveToNext(); } cursor.close(); close(); return ret; } } // Translate a cursor to a TestResult JSONObject ready to be sent to the UI private JSONObject cursorTestResultToJSONObject(Cursor c) { JSONObject ret = new JSONObject(); try { ret.put(SKSQLiteHelper.TR_COLUMN_ID, c.getLong(0)); ret.put(SKSQLiteHelper.TR_COLUMN_TYPE, c.getString(1)); ret.put(SKSQLiteHelper.TR_COLUMN_DTIME, c.getLong(2)); ret.put(SKSQLiteHelper.TR_COLUMN_LOCATION, c.getString(3)); ret.put(SKSQLiteHelper.TR_COLUMN_SUCCESS, c.getInt(4)); ret.put(SKSQLiteHelper.TR_COLUMN_RESULT, c.getDouble(5)); ret.put(SKSQLiteHelper.TR_COLUMN_BATCH_ID, c.getLong(6)); } catch (JSONException je) { SKPorting.sAssert(false); } return ret; } // Translate a cursor to a PassiveMetric JSONObject ready to be sent to the // UI private JSONObject cursorPassiveMetricToJSONObject(Cursor c) { JSONObject ret = new JSONObject(); // PM_COLUMN_ID, PM_COLUMN_METRIC, PM_COLUMN_DTIME, PM_COLUMN_VALUE, // PM_COLUMN_TYPE, PM_COLUMN_BATCH_ID try { ret.put(SKSQLiteHelper.PM_COLUMN_ID, c.getLong(0)); ret.put(SKSQLiteHelper.PM_COLUMN_METRIC, c.getString(1)); ret.put(SKSQLiteHelper.PM_COLUMN_DTIME, c.getLong(2)); ret.put(SKSQLiteHelper.PM_COLUMN_VALUE, c.getString(3)); ret.put(SKSQLiteHelper.PM_COLUMN_TYPE, c.getString(4)); ret.put(SKSQLiteHelper.PM_COLUMN_BATCH_ID, c.getLong(5)); } catch (JSONException je) { SKPorting.sAssertE(DBHelper.class, "Error in converting passive metric entry into JSONObject" + je.getMessage()); } return ret; } // PABLO'S ADDITION // This method is used to retrieve information for the summary screen results about average and best results. public ArrayList<SummaryResult> getSummaryValues(eNetworkTypeResults pNetworkType, long pTimePeriodStart) { synchronized (sync) { ArrayList<SummaryResult> summaryResults = new ArrayList<>(); String whereClause; // Depending on the network type, we use different where clauses. switch (pNetworkType) { case eNetworkTypeResults_Any: whereClause = " WHERE " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_DTIME + " > " + pTimePeriodStart; break; case eNetworkTypeResults_WiFi: whereClause = " WHERE " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + "." + SKSQLiteHelper.PM_COLUMN_METRIC + "= \"activenetworktype\" AND " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + "." + SKSQLiteHelper.PM_COLUMN_VALUE + " = " + "\"WiFi\"" + " AND " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_DTIME + " > " + pTimePeriodStart; break; case eNetworkTypeResults_Mobile: whereClause = " WHERE " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + "." + SKSQLiteHelper.PM_COLUMN_METRIC + "= \"activenetworktype\" AND " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + "." + SKSQLiteHelper.PM_COLUMN_VALUE + " = " + "\"mobile\"" + " AND " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_DTIME + " > " + pTimePeriodStart; break; default: SKPorting.sAssert(getClass(), false); whereClause = " WHERE " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_DTIME + " > " + pTimePeriodStart; SKPorting.sAssert(getClass(), false); break; } // Defining the query String query = "SELECT " + SKSQLiteHelper.TABLE_TESTRESULT + "." + SKSQLiteHelper.TR_COLUMN_TYPE+ " ,AVG(" + SKSQLiteHelper.TR_COLUMN_RESULT + "), MAX(" + SKSQLiteHelper.TR_COLUMN_RESULT + "), MIN(" + SKSQLiteHelper.TR_COLUMN_RESULT + ")" + " FROM " + SKSQLiteHelper.TABLE_TESTBATCH + " JOIN " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + " ON " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_ID + " = " + SKSQLiteHelper.TABLE_PASSIVEMETRIC + "." + SKSQLiteHelper.PM_COLUMN_BATCH_ID + " JOIN " + SKSQLiteHelper.TABLE_TESTRESULT + " ON " + SKSQLiteHelper.TABLE_TESTBATCH + "." + SKSQLiteHelper.TB_COLUMN_ID + " = " + SKSQLiteHelper.TABLE_TESTRESULT + "." + SKSQLiteHelper.TR_COLUMN_BATCH_ID + whereClause + " GROUP BY " + SKSQLiteHelper.TABLE_TESTRESULT + "." + SKSQLiteHelper.TR_COLUMN_TYPE; if (open() == false) { SKPorting.sAssert(getClass(), false); } else { Cursor cursor = database.rawQuery(query, null); DETAIL_TEST_ID testType = DETAIL_TEST_ID.DOWNLOAD_TEST_ID; float max = 0; float min = 0; float average = 0; if (cursor.moveToFirst() == false) { // No results! //SKLogger.sAssert(getClass(), false); } else { do { if (cursor.getString(0).equals("download")) { testType = DETAIL_TEST_ID.DOWNLOAD_TEST_ID; average = cursor.getFloat(1) / 1000000; max = cursor.getFloat(2) / 1000000; min = cursor.getFloat(3) / 1000000; } else if (cursor.getString(0).equals("upload")) { testType = DETAIL_TEST_ID.UPLOAD_TEST_ID; average = cursor.getFloat(1) / 1000000; max = cursor.getFloat(2) / 1000000; min = cursor.getFloat(3) / 1000000; } else if (cursor.getString(0).equals("latency")) { testType = DETAIL_TEST_ID.LATENCY_TEST_ID; average = cursor.getFloat(1) / 1000; max = cursor.getFloat(2) / 1000; min = cursor.getFloat(3) / 1000; } else if (cursor.getString(0).equals("packetloss")) { testType = DETAIL_TEST_ID.PACKETLOSS_TEST_ID; average = cursor.getFloat(1); max = cursor.getFloat(2); min = cursor.getFloat(3); } else if (cursor.getString(0).endsWith("jitter")) { testType = DETAIL_TEST_ID.JITTER_TEST_ID; average = cursor.getFloat(1) / 1000; max = cursor.getFloat(2) / 1000; min = cursor.getFloat(3) / 1000; } else { SKPorting.sAssert(getClass(), false); } summaryResults.add(new SummaryResult(testType, average, max, min)); } while (cursor.moveToNext()); } cursor.close(); close(); } return summaryResults; } } }