Java tutorial
/* Copyright 2013 The MITRE Corporation, All Rights Reserved. Licensed under the Apache License, Version 2.0 (the "License"); you may not use this work except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ package org.mitre.svmp.common; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteQueryBuilder; import android.util.Log; import org.json.JSONException; import org.json.JSONObject; import org.mitre.svmp.performance.MeasurementInfo; import org.mitre.svmp.performance.PointPerformanceData; import org.mitre.svmp.performance.SpanPerformanceData; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author Joe Portner */ public class DatabaseHandler extends SQLiteOpenHelper { private static final String TAG = DatabaseHandler.class.getName(); public static final String DB_NAME = "org.mitre.svmp.db"; public static final int DB_VERSION = 12; public static final int TABLE_CONNECTIONS = 0; public static final int TABLE_MEASUREMENT_INFO = 1; // groups together performance data public static final int TABLE_PERFORMANCE_DATA = 2; // raw performance data public static final int TABLE_APPS = 3; // app data for each connection public static final int TABLE_SEARCH_HISTORY = 4; // search for each connection public static final String[] Tables = new String[] { "Connections", "MeasurementInfo", "PerformanceData", "Apps", "SearchHistory" }; // this is used to generate queries to create new tables with appropriate constraints // foreign keys constraints are automatically added for matching table names // see SQLite Datatypes: http://www.sqlite.org/datatype3.html public static final String[][][] TableColumns = new String[][][] { // column, type, constraints { { "ConnectionID", "INTEGER", "PRIMARY KEY" }, { "Description", "TEXT" }, { "Username", "TEXT" }, { "Host", "TEXT" }, { "Port", "INTEGER" }, { "EncryptionType", "INTEGER" }, { "Domain", "TEXT" }, { "AuthType", "INTEGER DEFAULT 1" }, { "SessionToken", "TEXT DEFAULT ''" }, { "CertificateAlias", "TEXT DEFAULT ''" }, { "SessionExpires", "INTEGER DEFAULT 0" }, { "SessionGracePeriod", "INTEGER DEFAULT 0" }, // UNUSED/OBSOLETE { "LastDisconnected", "INTEGER DEFAULT 0" }, // UNUSED/OBSOLETE { "SessionHost", "TEXT DEFAULT ''" }, { "SessionPort", "TEXT DEFAULT ''" }, { "SessionWebrtc", "TEXT DEFAULT ''" }, { "Status", "INTEGER DEFAULT 0" } }, { { "StartDate", "INTEGER", "PRIMARY KEY" }, { "ConnectionID", "INTEGER" }, // foreign key { "MeasureInterval", "INTEGER" }, { "PingInterval", "INTEGER" } }, { { "MeasureDate", "INTEGER", "PRIMARY KEY" }, { "StartDate", "INTEGER" }, // foreign key { "FrameCount", "INTEGER" }, // count since last measurement { "SensorUpdates", "INTEGER" }, // count since last measurement { "TouchUpdates", "INTEGER" }, // count since last measurement { "CPUUsage", "REAL" }, // percentage (0.0 to 1.0) { "MemoryUsage", "INTEGER" }, // measured in kB { "WifiStrength", "REAL" }, // percentage (0.0 to 1.0) { "BatteryLevel", "REAL" }, // percentage (0.0 to 1.0) { "CellNetwork", "INTEGER" }, // what network the device is on (see TelephonyManager.NETWORK_* constants) { "CellValues", "TEXT" }, // a variety of cell values (depends on network type; LTE, GSM, CDMA/EVDO...) { "Ping", "INTEGER" } // last ping response in ms }, { { "ConnectionID", "INTEGER", "PRIMARY KEY" }, { "PackageName", "TEXT", "PRIMARY KEY" }, // this might exist on multiple connections { "AppName", "TEXT" }, { "Favorite", "BOOLEAN" }, { "Icon", "BLOB" }, { "IconHash", "BLOB" }, { "isInstalled", "INTEGER DEFAULT 1" } }, { { "SearchString", "TEXT", "PRIMARY KEY" } } }; private SQLiteDatabase db; private Context context; public DatabaseHandler(Context context) { super(context, DB_NAME, null, DB_VERSION); this.context = context; } private SQLiteDatabase getDb() { if (db == null) db = this.getWritableDatabase(); return db; } public void close() { // cleanup if (db != null && db.isOpen()) db.close(); } @Override public void onCreate(SQLiteDatabase db) { // loop through the tables and create them from the TableColumns jagged array for (int i = 0; i < Tables.length; i++) createTable(i, db); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { switch (oldVersion) { case 1: // changed Connections table, recreate it recreateTable(TABLE_CONNECTIONS, db); case 2: addTableColumn(TABLE_CONNECTIONS, 6, "''", db); addTableColumn(TABLE_CONNECTIONS, 7, "0", db); case 3: // changed auth types, now the IDs begin with 1, not 0 db.execSQL("UPDATE Connections SET AuthType=1 WHERE AuthType=0;"); case 4: // added measurement info and performance data tables, no need to change existing info createTable(TABLE_MEASUREMENT_INFO, db); createTable(TABLE_PERFORMANCE_DATA, db); case 5: addTableColumn(TABLE_CONNECTIONS, 8, "''", db); // SessionToken column added case 6: addTableColumn(TABLE_CONNECTIONS, 9, "''", db); // CertificateAlias column added case 7: // changed encryption types, removed SSL/untrusted, now we just have SSL db.execSQL("UPDATE Connections SET EncryptionType=1 WHERE EncryptionType=2;"); case 8: // changed session handling, now the client is aware when a session token is not valid db.execSQL("UPDATE Connections SET SessionToken='';"); // clear out all existing session tokens addTableColumn(TABLE_CONNECTIONS, 10, "0", db); // SessionExpires column added addTableColumn(TABLE_CONNECTIONS, 11, "0", db); // SessionGracePeriod column added addTableColumn(TABLE_CONNECTIONS, 12, "0", db); // LastDisconnected column added case 9: // added Apps table, no need to change existing data createTable(TABLE_APPS, db); case 10: // we don't use the Connections table's SessionGracePeriod column anymore, but there's no way to drop it case 11: // we don't use the Connections table's LastDisconnected column anymore, but there's no way to drop it // added session info columns addTableColumn(TABLE_CONNECTIONS, 13, "''", db); // SessionHost column added addTableColumn(TABLE_CONNECTIONS, 14, "''", db); // SessionPort column added addTableColumn(TABLE_CONNECTIONS, 15, "''", db); // SessionWebrtc column added // clear any existing session info ContentValues values = new ContentValues(); values.put("SessionToken", ""); // attempt update try { db.update(Tables[TABLE_CONNECTIONS], values, null, null); } catch (Exception e) { e.printStackTrace(); } updateRecord(TABLE_CONNECTIONS, values, null); default: break; } } // This is needed to enable cascade operations for foreign keys (delete and update) @Override public void onOpen(SQLiteDatabase db) { super.onOpen(db); if (!db.isReadOnly()) { // Enable foreign key constraints db.execSQL("PRAGMA foreign_keys=ON;"); } } // only used during onUpgrade private void addTableColumn(int tableID, int colNum, String defaultVal, SQLiteDatabase db) { String query = String.format("ALTER TABLE %s ADD COLUMN %s %s DEFAULT %s", Tables[tableID], // table name TableColumns[tableID][colNum][0], // column name TableColumns[tableID][colNum][1], // column type defaultVal); // try to create the table with the constructed query try { db.execSQL(query); } catch (Exception e) { e.printStackTrace(); } } // if the table exists, drop it; then, create the table again private void recreateTable(int tableID, SQLiteDatabase db) { // try to drop the table try { db.execSQL(String.format("DROP TABLE IF EXISTS %s", Tables[tableID])); } catch (Exception e) { e.printStackTrace(); } // create the table again createTable(tableID, db); } // creates a table, along with constraints, based on the TableColumns jagged array private void createTable(int tableID, SQLiteDatabase db) { StringBuilder query = new StringBuilder(); StringBuilder primaryKeys = new StringBuilder(); StringBuilder foreignKeys = new StringBuilder(); query.append(String.format("CREATE TABLE %s (", Tables[tableID])); for (int i = 0; i < TableColumns[tableID].length; i++) { if (i > 0) query.append(", "); // append column name, type, and constraints for (int j = 0; j < TableColumns[tableID][i].length; j++) { // if this is a primary key option, add it to the string and save it for the end if (j == 2 && TableColumns[tableID][i][j].equals("PRIMARY KEY")) { if (primaryKeys.length() > 0) primaryKeys.append(", "); primaryKeys.append(TableColumns[tableID][i][0]); } // if this is another option (UNIQUE, NOT NULL, etc) add it now else { if (j > 0) query.append(" "); query.append(TableColumns[tableID][i][j]); } } // loop through tables to construct foreign key constraints (looks at 1st column/primary key of each table) String foreignTable = ""; for (int k = 0; k < Tables.length; k++) { if (k < tableID // skip the same table, skip tables that haven't been added yet && TableColumns[k][0][0].equals(TableColumns[tableID][i][0]) && TableColumns[k][0].length > 2 && TableColumns[k][0][2].equals("PRIMARY KEY")) { foreignTable = Tables[k]; break; } } if (foreignTable.length() > 0) { String constraint = String.format( ", FOREIGN KEY (%s) REFERENCES %s (%s) ON DELETE CASCADE ON UPDATE CASCADE", TableColumns[tableID][i][0], foreignTable, TableColumns[tableID][i][0]); foreignKeys.append(constraint); } } // append primary key constraint(s) if (primaryKeys.length() > 0) { query.append(String.format(", PRIMARY KEY (%s)", primaryKeys.toString())); } query.append(foreignKeys.toString()); query.append(");"); Log.d(TAG, String.format("Creating table: %s", query.toString())); // try to create the table with the constructed query try { db.execSQL(query.toString()); } catch (Exception e) { e.printStackTrace(); } } public List<ConnectionInfo> getConnectionInfoList() { // run the query Cursor cursor = _getConnectionInfoCursor(null); // try to get results and add ConnectionInfo objects to the list List<ConnectionInfo> connectionInfoList = new ArrayList<ConnectionInfo>(); while (cursor.moveToNext()) { // construct a new ConnectionInfo from the cursor ConnectionInfo connectionInfo = makeConnectionInfo(cursor); // add the ConnectionInfo to the list if (connectionInfo != null) connectionInfoList.add(connectionInfo); } // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return connectionInfoList; } // returns a ConnectionInfo that matches the given ConnectionID (null if none found) public ConnectionInfo getConnectionInfo(int id) { return _getConnectionInfo("ConnectionID=?", String.valueOf(id)); } // returns a ConnectionInfo that does NOT match the given ConnectionID, but matches the given description (null if none found) public ConnectionInfo getConnectionInfo(int id, String description) { return _getConnectionInfo("ConnectionID!=? AND LOWER(Description)=TRIM(LOWER(?))", String.valueOf(id), description); } private ConnectionInfo _getConnectionInfo(String selection, String... selectionArgs) { // run the query Cursor cursor = _getConnectionInfoCursor(selection, selectionArgs); // try to get results and make a ConnectionInfo object to return ConnectionInfo connectionInfo = null; if (cursor.moveToFirst()) connectionInfo = makeConnectionInfo(cursor); // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return connectionInfo; } // selection and selectionArgs are optional private Cursor _getConnectionInfoCursor(String selection, String... selectionArgs) { SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder(); queryBuilder .setTables(String.format("%s NATURAL LEFT JOIN %s", Tables[TABLE_CONNECTIONS], Tables[TABLE_APPS])); // prepared statement for speed and security return queryBuilder.query(getDb(), new String[] { "ConnectionID", "Description", "Username", "Host", "Port", "EncryptionType", "AuthType", "CertificateAlias", "COUNT(PackageName)", "Status" }, // columns (null == "*") selection, // selection ('where' clause) selectionArgs, // selection args "ConnectionID", // group by null, // having "Description" // order by ); } // returns null if the connection doesn't have a valid session token public SessionInfo getSessionInfo(ConnectionInfo connectionInfo) { // run the query Cursor cursor = getDb().query(Tables[TABLE_CONNECTIONS], // table new String[] { "SessionToken", "SessionExpires", "SessionHost", "SessionPort", "SessionWebrtc" }, // columns (null == "*") "ConnectionID=?", // selection ('where' clause) new String[] { String.valueOf(connectionInfo.getConnectionID()) }, // selection args null, // group by null, // having null // order by ); // try to get results and find a Session Token to return SessionInfo value = null; if (cursor.moveToFirst()) { String token = cursor.getString(0); if (token != null && token.length() > 0) { // we have a token, check to see if it's valid long expires = cursor.getLong(1); // the longest the session is valid before it expires String host = cursor.getString(2); String port = cursor.getString(3); String webrtc = cursor.getString(4); Date expireDate = new Date(expires); SimpleDateFormat sdf = new SimpleDateFormat("h:mm:ss a"); Log.v(TAG, String.format("Found session info, [token: '%s', expires: '%s', host: '%s', port: '%s']", token, sdf.format(expireDate), host, port)); if (expireDate.after(new Date())) { // the session hasn't expired yet, try to make a webrtc JSON object try { JSONObject jsonObject = new JSONObject(webrtc); value = new SessionInfo(token, expires, host, port, jsonObject); } catch (JSONException e) { Log.e(TAG, "Session info contained invalid webrtc JSON string:", e); } } else { // we have session info, but it's expired; clear it clearSessionInfo(connectionInfo); } } } return value; } public List<String> getAllSearchHistory() { Cursor cursor = getDb().query(Tables[TABLE_SEARCH_HISTORY], // table null, // columns (null == "*") null, // selection ('where' clause) null, // selection args null, // group by null, // having null // order by ); // try to get results and add MeasurementInfo objects to the list List<String> historyInfoList = new ArrayList<String>(); while (cursor.moveToNext()) { // construct a new MeasurementInfo from the cursor String history = cursor.getString(0); // add the MeasurementInfo to the list if (history != null) historyInfoList.add(history); } // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return historyInfoList; } public List<MeasurementInfo> getAllMeasurementInfo() { Cursor cursor = getDb().query(Tables[TABLE_MEASUREMENT_INFO], // table null, // columns (null == "*") null, // selection ('where' clause) null, // selection args null, // group by null, // having null // order by ); // try to get results and add MeasurementInfo objects to the list List<MeasurementInfo> measurementInfoList = new ArrayList<MeasurementInfo>(); while (cursor.moveToNext()) { // construct a new MeasurementInfo from the cursor MeasurementInfo measurementInfo = makeMeasurementInfo(cursor); // add the MeasurementInfo to the list if (measurementInfo != null) measurementInfoList.add(measurementInfo); } // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return measurementInfoList; } public List<String> getAllPerformanceData(MeasurementInfo measurementInfo) { Cursor cursor = getDb().query(Tables[TABLE_PERFORMANCE_DATA], // table null, // columns (null == "*") "StartDate=?", // selection ('where' clause) new String[] { String.valueOf(measurementInfo.getStartDate().getTime()) }, // selection args null, // group by null, // having null // order by ); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ"); // try to get results and add String objects to the list List<String> performanceDataList = new ArrayList<String>(); while (cursor.moveToNext()) { // construct a new String from the cursor String performanceData = makePerformanceData(cursor, measurementInfo, dateFormat); // add the String to the list if (performanceData != null) performanceDataList.add(performanceData); } return performanceDataList; } public List<AppInfo> getAppInfoList_All(int connectionID) { return getAppInfoList("ConnectionID=?", String.valueOf(connectionID)); } public List<AppInfo> getAppInfoList_Favorites(int connectionID) { return getAppInfoList("ConnectionID=? AND Favorite=1", String.valueOf(connectionID)); } // returns a list of apps for a given ConnectionID, ordered by AppName private List<AppInfo> getAppInfoList(String selection, String... selectionArgs) { // prepared statement for speed and security Cursor cursor = getDb().query(Tables[TABLE_APPS], // table null, // columns (null == "*") selection, // selection ('where' clause) selectionArgs, // selection args null, // group by null, // having "AppName" // order by ); // try to get results and add AppInfo objects to the list List<AppInfo> appInfoList = new ArrayList<AppInfo>(); while (cursor.moveToNext()) { // construct a new AppInfo from the cursor AppInfo appInfo = makeAppInfo(cursor); // add the AppInfo to the list if (appInfo != null) appInfoList.add(appInfo); } // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return appInfoList; } // returns an AppInfo that matches the given ConnectionID and PackageName (null if none found) public AppInfo getAppInfo(int connectionID, String packageName) { return _getAppInfo("ConnectionID=? AND PackageName=?", String.valueOf(connectionID), packageName); } public AppInfo getNotInstalledAppInfo(int connectionID) { return _getAppInfo("ConnectionID=? AND isInstalled=?", String.valueOf(connectionID), "0"); } private AppInfo _getAppInfo(String selection, String... selectionArgs) { // prepared statement for speed and security Cursor cursor = getDb().query(Tables[TABLE_APPS], // table null, // columns (null == "*") selection, // selection ('where' clause) selectionArgs, // selection args null, // group by null, // having null // order by ); // try to get results and make an AppInfo object to return AppInfo appInfo = null; if (cursor.moveToFirst()) appInfo = makeAppInfo(cursor); // cleanup try { cursor.close(); } catch (Exception e) { // don't care } return appInfo; } private ConnectionInfo makeConnectionInfo(Cursor cursor) { try { // get values from query int connectionID = cursor.getInt(0); String description = cursor.getString(1); String username = cursor.getString(2); String host = cursor.getString(3); int port = cursor.getInt(4); int encryptionType = cursor.getInt(5); int authType = cursor.getInt(6); String certificateAlias = cursor.getString(7); int appCount = cursor.getInt(8); int status = cursor.getInt(9); return new ConnectionInfo(connectionID, description, username, host, port, encryptionType, authType, certificateAlias, appCount, status); } catch (Exception e) { e.printStackTrace(); return null; } } private MeasurementInfo makeMeasurementInfo(Cursor cursor) { try { // get values from query long startDate = cursor.getLong(0); int connectionID = cursor.getInt(1); int measureInterval = cursor.getInt(2); int pingInterval = cursor.getInt(3); return new MeasurementInfo(new Date(startDate), connectionID, measureInterval, pingInterval); } catch (Exception e) { e.printStackTrace(); return null; } } private String makePerformanceData(Cursor cursor, MeasurementInfo measurementInfo, SimpleDateFormat dateFormat) { try { // get values from query long measureDate = cursor.getLong(0); //long startDate = cursor.getLong(1); // don't need this, it's just a foreign key int frameCount = cursor.getInt(2); int sensorUpdates = cursor.getInt(3); int touchUpdates = cursor.getInt(4); double cpuUsage = cursor.getDouble(5); int memoryUsage = cursor.getInt(6); double wifiStrength = cursor.getDouble(7); double batteryLevel = cursor.getDouble(8); int cellNetwork = cursor.getInt(9); String cellValues = cursor.getString(10); int ping = cursor.getInt(11); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.append(dateFormat.format(new Date(measureDate))); stringBuilder.append(","); stringBuilder.append(numberPerSecond(frameCount, measurementInfo.getMeasureInterval())); stringBuilder.append(","); stringBuilder.append(numberPerSecond(sensorUpdates, measurementInfo.getMeasureInterval())); stringBuilder.append(","); stringBuilder.append(numberPerSecond(touchUpdates, measurementInfo.getMeasureInterval())); stringBuilder.append(","); stringBuilder.append(cpuUsage); stringBuilder.append(","); stringBuilder.append(memoryUsage); stringBuilder.append(","); stringBuilder.append(wifiStrength); stringBuilder.append(","); stringBuilder.append(batteryLevel); stringBuilder.append(","); stringBuilder.append(Utility.cellNetwork(cellNetwork)); stringBuilder.append(","); stringBuilder.append(cellValues); stringBuilder.append(","); stringBuilder.append(ping); return stringBuilder.toString(); } catch (Exception e) { e.printStackTrace(); return null; } } private double numberPerSecond(int input, int interval) { if (interval > 0) // sanity return ((double) input) / (interval / 1000); return input; } private AppInfo makeAppInfo(Cursor cursor) { try { // get values from query int connectionID = cursor.getInt(0); String packageName = cursor.getString(1); String appName = cursor.getString(2); int favoriteInt = cursor.getInt(3); // sqlite can't store booleans, only numbers boolean favorite = favoriteInt == 1; // if the AppInfo has an icon, get it byte[] icon = null; if (!cursor.isNull(4)) icon = cursor.getBlob(4); byte[] iconHash = null; if (!cursor.isNull(5)) iconHash = cursor.getBlob(5); int isInstalled = cursor.getInt(6); return new AppInfo(connectionID, packageName, appName, favorite, icon, iconHash, isInstalled); } catch (Exception e) { e.printStackTrace(); return null; } } public long insertConnectionInfo(ConnectionInfo connectionInfo) { // attempt insert return insertRecord(TABLE_CONNECTIONS, makeContentValues(connectionInfo)); } public long insertMeasurementInfo(MeasurementInfo measurementInfo) { ContentValues contentValues = new ContentValues(); contentValues.put("StartDate", measurementInfo.getStartDate().getTime()); contentValues.put("ConnectionID", measurementInfo.getConnectionID()); contentValues.put("MeasureInterval", measurementInfo.getMeasureInterval()); contentValues.put("PingInterval", measurementInfo.getPingInterval()); return insertRecord(TABLE_MEASUREMENT_INFO, contentValues); } public long insertSearchHistory(String searchHistory) { ContentValues contentValues = new ContentValues(); contentValues.put("SearchString", searchHistory); return insertRecord(TABLE_SEARCH_HISTORY, contentValues); } public long insertPerformanceData(long startDate, SpanPerformanceData spanMeasurements, PointPerformanceData pointMeasurements) { ContentValues contentValues = new ContentValues(); contentValues.put("MeasureDate", System.currentTimeMillis()); contentValues.put("StartDate", startDate); contentValues.put("FrameCount", spanMeasurements.getFrameCount()); contentValues.put("SensorUpdates", spanMeasurements.getSensorUpdates()); contentValues.put("TouchUpdates", spanMeasurements.getTouchUpdates()); contentValues.put("CpuUsage", pointMeasurements.getCpuUsage()); contentValues.put("MemoryUsage", pointMeasurements.getMemoryUsage()); contentValues.put("WifiStrength", pointMeasurements.getWifiStrength()); contentValues.put("BatteryLevel", pointMeasurements.getBatteryLevel()); contentValues.put("CellNetwork", pointMeasurements.getCellNetwork()); contentValues.put("CellValues", pointMeasurements.getCellValues()); contentValues.put("Ping", pointMeasurements.getPing()); return insertRecord(TABLE_PERFORMANCE_DATA, contentValues); } public long insertAppInfo(AppInfo appInfo) { // attempt insert return insertRecord(TABLE_APPS, makeContentValues(appInfo)); } private long insertRecord(int tableID, ContentValues contentValues) { long result = -1; // attempt insert try { result = getDb().insert(Tables[tableID], null, contentValues); } catch (Exception e) { e.printStackTrace(); } // return result return result; } public long updateConnectionInfo(ConnectionInfo connectionInfo) { // attempt insert return updateRecord(TABLE_CONNECTIONS, makeContentValues(connectionInfo), "ConnectionID=?", String.valueOf(connectionInfo.getConnectionID())); } public long updateSessionInfo(ConnectionInfo connectionInfo, SessionInfo sessionInfo) { // create content values ContentValues contentValues = new ContentValues(); contentValues.put("SessionToken", sessionInfo.getToken()); contentValues.put("SessionExpires", sessionInfo.getExpires()); contentValues.put("SessionHost", sessionInfo.getHost()); contentValues.put("SessionPort", sessionInfo.getPort()); contentValues.put("SessionWebrtc", sessionInfo.getWebrtc().toString()); // attempt update return updateRecord(TABLE_CONNECTIONS, contentValues, "ConnectionID=?", String.valueOf(connectionInfo.getConnectionID())); } public long clearSessionInfo(ConnectionInfo connectionInfo) { // create content values ContentValues contentValues = new ContentValues(); contentValues.put("SessionToken", ""); // attempt update return updateRecord(TABLE_CONNECTIONS, contentValues, "ConnectionID=?", String.valueOf(connectionInfo.getConnectionID())); } public long updateAppInfo(AppInfo appInfo) { ContentValues contentValues = makeContentValues(appInfo); // attempt insert return updateRecord(TABLE_APPS, contentValues, "ConnectionID=? AND PackageName=?", String.valueOf(appInfo.getConnectionID()), appInfo.getPackageName()); } public long updateAppInfo_Favorite(AppInfo appInfo, boolean favorite) { ContentValues contentValues = new ContentValues(); contentValues.put("Favorite", favorite); // attempt insert return updateRecord(TABLE_APPS, contentValues, "ConnectionID=? AND PackageName=?", String.valueOf(appInfo.getConnectionID()), appInfo.getPackageName()); } private long updateRecord(int tableID, ContentValues contentValues, String whereClause, String... whereArgs) { long result = -1; // attempt update try { result = getDb().update(Tables[tableID], contentValues, whereClause, whereArgs); } catch (Exception e) { e.printStackTrace(); } // return result return result; } public long wipeAllPerformanceData() { // delete all measurement info (will cascade delete all performance data) return getDb().delete(Tables[TABLE_MEASUREMENT_INFO], null, null); } private ContentValues makeContentValues(ConnectionInfo connectionInfo) { ContentValues contentValues = new ContentValues(); if (connectionInfo != null) { contentValues.put("Description", connectionInfo.getDescription()); contentValues.put("Username", connectionInfo.getUsername()); contentValues.put("Host", connectionInfo.getHost()); contentValues.put("Port", connectionInfo.getPort()); contentValues.put("EncryptionType", connectionInfo.getEncryptionType()); contentValues.put("Domain", ""); contentValues.put("AuthType", connectionInfo.getAuthType()); contentValues.put("CertificateAlias", connectionInfo.getCertificateAlias()); contentValues.put("Status", connectionInfo.getStatus()); } return contentValues; } private ContentValues makeContentValues(AppInfo appInfo) { ContentValues contentValues = new ContentValues(); if (appInfo != null) { contentValues.put("ConnectionID", appInfo.getConnectionID()); contentValues.put("PackageName", appInfo.getPackageName()); contentValues.put("AppName", appInfo.getAppName()); contentValues.put("Favorite", appInfo.isFavorite()); contentValues.put("isInstalled", appInfo.getIsInstalled()); byte[] icon = appInfo.getIcon(); if (icon != null) contentValues.put("Icon", icon); byte[] iconHash = appInfo.getIconHash(); if (iconHash != null) contentValues.put("IconHash", iconHash); } return contentValues; } public long deleteConnectionInfo(int connectionID) { // attempt delete return deleteRecord(TABLE_CONNECTIONS, "ConnectionID=?", String.valueOf(connectionID)); } public long deleteAppInfo(AppInfo appInfo) { // delete any shortcuts that may exist for this AppInfo Utility.removeShortcut(context, appInfo); // attempt delete return deleteRecord(TABLE_APPS, "ConnectionID=? AND PackageName=?", String.valueOf(appInfo.getConnectionID()), appInfo.getPackageName()); } // this is used when a user initiates a Full Refresh of the app list (wipes all apps and gets the whole list again) public long deleteAllAppInfos(int connectionID) { // first we have to delete all of the shortcuts for these apps deleteAllAppInfoShortcuts(connectionID); // attempt to delete all AppInfos for this connection return deleteRecord(TABLE_APPS, "ConnectionID=?", String.valueOf(connectionID)); } private void deleteAllAppInfoShortcuts(int connectionID) { // TODO: this is somewhat messy, the system creates a toast for each shortcut removed... is there a better way? // loop through the AppInfos for this connection and delete any shortcuts that may exist for them List<AppInfo> appInfoList = getAppInfoList_All(connectionID); for (AppInfo appInfo : appInfoList) Utility.removeShortcut(context, appInfo); } private long deleteRecord(int tableID, String whereClause, String... whereArgs) { long result = -1; // attempt delete try { result = getDb().delete(Tables[tableID], whereClause, whereArgs); } catch (Exception e) { e.printStackTrace(); } // return result return result; } }