diff options
Diffstat (limited to 'trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt')
| -rw-r--r-- | trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt | 461 |
1 files changed, 461 insertions, 0 deletions
diff --git a/trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt b/trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt new file mode 100644 index 0000000..6aa76cf --- /dev/null +++ b/trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt @@ -0,0 +1,461 @@ +/* + * Copyright (C) 2023 MURENA SAS + * Copyright (C) 2022 E FOUNDATION + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see <https://www.gnu.org/licenses/>. + */ + +package foundation.e.advancedprivacy.trackers.data + +import android.content.ContentValues +import android.content.Context +import android.database.Cursor +import android.database.sqlite.SQLiteDatabase +import android.database.sqlite.SQLiteOpenHelper +import android.provider.BaseColumns +import androidx.core.database.getStringOrNull +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_APPID +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_NUMBER_BLOCKED +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_NUMBER_CONTACTED +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_TIMESTAMP +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_TRACKER +import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.TABLE_NAME +import foundation.e.advancedprivacy.trackers.domain.entities.Tracker +import timber.log.Timber +import java.time.ZonedDateTime +import java.time.format.DateTimeFormatter +import java.time.temporal.ChronoUnit +import java.time.temporal.TemporalUnit +import java.util.concurrent.TimeUnit + +class StatsDatabase( + context: Context, + private val trackersRepository: TrackersRepository +) : + SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) { + + companion object { + const val DATABASE_VERSION = 2 + const val DATABASE_NAME = "TrackerFilterStats.db" + private const val SQL_CREATE_TABLE = "CREATE TABLE $TABLE_NAME (" + + "${BaseColumns._ID} INTEGER PRIMARY KEY," + + "$COLUMN_NAME_TIMESTAMP INTEGER," + + "$COLUMN_NAME_TRACKER TEXT," + + "$COLUMN_NAME_NUMBER_CONTACTED INTEGER," + + "$COLUMN_NAME_NUMBER_BLOCKED INTEGER," + + "$COLUMN_NAME_APPID TEXT)" + + private const val PROJECTION_NAME_PERIOD = "period" + private const val PROJECTION_NAME_CONTACTED_SUM = "contactedsum" + private const val PROJECTION_NAME_BLOCKED_SUM = "blockedsum" + private const val PROJECTION_NAME_LEAKED_SUM = "leakedsum" + private const val PROJECTION_NAME_TRACKERS_COUNT = "trackerscount" + + private val MIGRATE_1_2 = listOf( + "ALTER TABLE $TABLE_NAME ADD COLUMN $COLUMN_NAME_APPID TEXT" + // "ALTER TABLE $TABLE_NAME DROP COLUMN app_uid" + // DROP COLUMN is available since sqlite 3.35.0, and sdk29 as 3.22.0, sdk32 as 3.32.2 + ) + } + + object AppTrackerEntry : BaseColumns { + const val TABLE_NAME = "tracker_filter_stats" + const val COLUMN_NAME_TIMESTAMP = "timestamp" + const val COLUMN_NAME_TRACKER = "tracker" + const val COLUMN_NAME_NUMBER_CONTACTED = "sum_contacted" + const val COLUMN_NAME_NUMBER_BLOCKED = "sum_blocked" + const val COLUMN_NAME_APPID = "app_apid" + } + + private var projection = arrayOf( + COLUMN_NAME_TIMESTAMP, + COLUMN_NAME_TRACKER, + COLUMN_NAME_NUMBER_CONTACTED, + COLUMN_NAME_NUMBER_BLOCKED, + COLUMN_NAME_APPID + ) + + private val lock = Any() + + override fun onCreate(db: SQLiteDatabase) { + db.execSQL(SQL_CREATE_TABLE) + } + + override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { + if (oldVersion == 1 && newVersion == 2) { + MIGRATE_1_2.forEach(db::execSQL) + } else { + Timber.e( + "Unexpected database versions: oldVersion: $oldVersion ; newVersion: $newVersion" + ) + } + } + + private fun getCallsByPeriod( + periodsCount: Int, + periodUnit: TemporalUnit, + sqlitePeriodFormat: String + ): Map<String, Pair<Int, Int>> { + synchronized(lock) { + val minTimestamp = getPeriodStartTs(periodsCount, periodUnit) + val db = readableDatabase + val selection = "$COLUMN_NAME_TIMESTAMP >= ?" + val selectionArg = arrayOf("" + minTimestamp) + + val projection = ( + "$COLUMN_NAME_TIMESTAMP, " + + "STRFTIME('$sqlitePeriodFormat', DATETIME($COLUMN_NAME_TIMESTAMP, 'unixepoch', 'localtime')) $PROJECTION_NAME_PERIOD," + + "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM, " + + "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM" + ) + + val cursor = db.rawQuery( + "SELECT $projection FROM $TABLE_NAME WHERE $selection" + + " GROUP BY $PROJECTION_NAME_PERIOD" + + " ORDER BY $COLUMN_NAME_TIMESTAMP DESC LIMIT $periodsCount", + selectionArg + ) + val callsByPeriod = HashMap<String, Pair<Int, Int>>() + while (cursor.moveToNext()) { + val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM) + val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM) + callsByPeriod[cursor.getString(PROJECTION_NAME_PERIOD)] = blocked to contacted - blocked + } + cursor.close() + db.close() + return callsByPeriod + } + } + + private fun callsByPeriodToPeriodsList( + callsByPeriod: Map<String, Pair<Int, Int>>, + periodsCount: Int, + periodUnit: TemporalUnit, + javaPeriodFormat: String + ): List<Pair<Int, Int>> { + var currentDate = ZonedDateTime.now().minus(periodsCount.toLong(), periodUnit) + val formatter = DateTimeFormatter.ofPattern(javaPeriodFormat) + val calls = mutableListOf<Pair<Int, Int>>() + for (i in 0 until periodsCount) { + currentDate = currentDate.plus(1, periodUnit) + val currentPeriod = formatter.format(currentDate) + calls.add(callsByPeriod.getOrDefault(currentPeriod, 0 to 0)) + } + return calls + } + + fun getTrackersCallsOnPeriod( + periodsCount: Int, + periodUnit: TemporalUnit + ): List<Pair<Int, Int>> { + var sqlitePeriodFormat = "%Y%m" + var javaPeriodFormat = "yyyyMM" + if (periodUnit === ChronoUnit.MONTHS) { + sqlitePeriodFormat = "%Y%m" + javaPeriodFormat = "yyyyMM" + } else if (periodUnit === ChronoUnit.DAYS) { + sqlitePeriodFormat = "%Y%m%d" + javaPeriodFormat = "yyyyMMdd" + } else if (periodUnit === ChronoUnit.HOURS) { + sqlitePeriodFormat = "%Y%m%d%H" + javaPeriodFormat = "yyyyMMddHH" + } + val callsByPeriod = getCallsByPeriod(periodsCount, periodUnit, sqlitePeriodFormat) + return callsByPeriodToPeriodsList(callsByPeriod, periodsCount, periodUnit, javaPeriodFormat) + } + + fun getActiveTrackersByPeriod(periodsCount: Int, periodUnit: TemporalUnit): Int { + synchronized(lock) { + val minTimestamp = getPeriodStartTs(periodsCount, periodUnit) + val db = writableDatabase + val selection = "$COLUMN_NAME_TIMESTAMP >= ? AND " + + "$COLUMN_NAME_NUMBER_CONTACTED > $COLUMN_NAME_NUMBER_BLOCKED" + val selectionArg = arrayOf("" + minTimestamp) + val projection = + "COUNT(DISTINCT $COLUMN_NAME_TRACKER) $PROJECTION_NAME_TRACKERS_COUNT" + + val cursor = db.rawQuery( + "SELECT $projection FROM $TABLE_NAME WHERE $selection", + selectionArg + ) + var count = 0 + if (cursor.moveToNext()) { + count = cursor.getInt(0) + } + cursor.close() + db.close() + return count + } + } + + fun getContactedTrackersCount(): Int { + synchronized(lock) { + val db = readableDatabase + var query = "SELECT DISTINCT $COLUMN_NAME_TRACKER FROM $TABLE_NAME" + + val cursor = db.rawQuery(query, arrayOf()) + var count = 0 + while (cursor.moveToNext()) { + trackersRepository.getTracker(cursor.getString(COLUMN_NAME_TRACKER))?.let { + count++ + } + } + cursor.close() + db.close() + return count + } + } + + fun getContactedTrackersCountByAppId(): Map<String, Int> { + synchronized(lock) { + val db = readableDatabase + val projection = "$COLUMN_NAME_APPID, $COLUMN_NAME_TRACKER" + val cursor = db.rawQuery( + "SELECT DISTINCT $projection FROM $TABLE_NAME", // + + arrayOf() + ) + val countByApp = mutableMapOf<String, Int>() + while (cursor.moveToNext()) { + trackersRepository.getTracker(cursor.getString(COLUMN_NAME_TRACKER))?.let { + val appId = cursor.getString(COLUMN_NAME_APPID) + countByApp[appId] = countByApp.getOrDefault(appId, 0) + 1 + } + } + cursor.close() + db.close() + return countByApp + } + } + + fun getCallsByAppIds(periodCount: Int, periodUnit: TemporalUnit): Map<String, Pair<Int, Int>> { + synchronized(lock) { + val minTimestamp = getPeriodStartTs(periodCount, periodUnit) + val db = readableDatabase + val selection = "$COLUMN_NAME_TIMESTAMP >= ?" + val selectionArg = arrayOf("" + minTimestamp) + val projection = "$COLUMN_NAME_APPID, " + + "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM," + + "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM" + val cursor = db.rawQuery( + "SELECT $projection FROM $TABLE_NAME" + + " WHERE $selection" + + " GROUP BY $COLUMN_NAME_APPID", + selectionArg + ) + val callsByApp = HashMap<String, Pair<Int, Int>>() + while (cursor.moveToNext()) { + val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM) + val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM) + callsByApp[cursor.getString(COLUMN_NAME_APPID)] = blocked to contacted - blocked + } + cursor.close() + db.close() + return callsByApp + } + } + + fun getCalls(appId: String, periodCount: Int, periodUnit: TemporalUnit): Pair<Int, Int> { + synchronized(lock) { + val minTimestamp = getPeriodStartTs(periodCount, periodUnit) + val db = readableDatabase + val selection = "$COLUMN_NAME_APPID = ? AND " + + "$COLUMN_NAME_TIMESTAMP >= ?" + val selectionArg = arrayOf("" + appId, "" + minTimestamp) + val projection = + "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM," + + "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM" + val cursor = db.rawQuery( + "SELECT $projection FROM $TABLE_NAME WHERE $selection", + selectionArg + ) + var calls: Pair<Int, Int> = 0 to 0 + if (cursor.moveToNext()) { + val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM) + val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM) + calls = blocked to contacted - blocked + } + cursor.close() + db.close() + return calls + } + } + + fun getMostLeakedAppId(periodCount: Int, periodUnit: TemporalUnit): String { + synchronized(lock) { + val minTimestamp = getPeriodStartTs(periodCount, periodUnit) + val db = readableDatabase + val selection = "$COLUMN_NAME_TIMESTAMP >= ?" + val selectionArg = arrayOf("" + minTimestamp) + val projection = "$COLUMN_NAME_APPID, " + + "SUM($COLUMN_NAME_NUMBER_CONTACTED - $COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_LEAKED_SUM" + val cursor = db.rawQuery( + "SELECT $projection FROM $TABLE_NAME" + + " WHERE $selection" + + " GROUP BY $COLUMN_NAME_APPID" + + " ORDER BY $PROJECTION_NAME_LEAKED_SUM DESC LIMIT 1", + selectionArg + ) + var appId = "" + if (cursor.moveToNext()) { + appId = cursor.getString(COLUMN_NAME_APPID) + } + cursor.close() + db.close() + return appId + } + } + + fun logAccess(trackerId: String?, appId: String, blocked: Boolean) { + synchronized(lock) { + val currentHour = getCurrentHourTs() + val db = writableDatabase + val values = ContentValues() + values.put(COLUMN_NAME_APPID, appId) + values.put(COLUMN_NAME_TRACKER, trackerId) + values.put(COLUMN_NAME_TIMESTAMP, currentHour) + + /*String query = "UPDATE product SET "+COLUMN_NAME_NUMBER_CONTACTED+" = "+COLUMN_NAME_NUMBER_CONTACTED+" + 1 "; + if(blocked) + query+=COLUMN_NAME_NUMBER_BLOCKED+" = "+COLUMN_NAME_NUMBER_BLOCKED+" + 1 "; +*/ + val selection = "$COLUMN_NAME_TIMESTAMP = ? AND " + + "$COLUMN_NAME_APPID = ? AND " + + "$COLUMN_NAME_TRACKER = ? " + val selectionArg = arrayOf("" + currentHour, "" + appId, trackerId) + val cursor = db.query( + TABLE_NAME, + projection, + selection, + selectionArg, + null, + null, + null + ) + if (cursor.count > 0) { + cursor.moveToFirst() + val entry = cursorToEntry(cursor) + if (blocked) values.put( + COLUMN_NAME_NUMBER_BLOCKED, + entry.sum_blocked + 1 + ) else values.put(COLUMN_NAME_NUMBER_BLOCKED, entry.sum_blocked) + values.put(COLUMN_NAME_NUMBER_CONTACTED, entry.sum_contacted + 1) + db.update(TABLE_NAME, values, selection, selectionArg) + + // db.execSQL(query, new String[]{""+hour, ""+day, ""+month, ""+year, ""+appUid, ""+trackerId}); + } else { + if (blocked) values.put( + COLUMN_NAME_NUMBER_BLOCKED, + 1 + ) else values.put(COLUMN_NAME_NUMBER_BLOCKED, 0) + values.put(COLUMN_NAME_NUMBER_CONTACTED, 1) + db.insert(TABLE_NAME, null, values) + } + cursor.close() + db.close() + } + } + + private fun cursorToEntry(cursor: Cursor): StatEntry { + val entry = StatEntry() + entry.timestamp = cursor.getLong(COLUMN_NAME_TIMESTAMP) + entry.appId = cursor.getString(COLUMN_NAME_APPID) + entry.sum_blocked = cursor.getInt(COLUMN_NAME_NUMBER_BLOCKED) + entry.sum_contacted = cursor.getInt(COLUMN_NAME_NUMBER_CONTACTED) + entry.tracker = cursor.getInt(COLUMN_NAME_TRACKER) + return entry + } + + fun getTrackers(appIds: List<String>?): List<Tracker> { + synchronized(lock) { + val columns = arrayOf(COLUMN_NAME_TRACKER, COLUMN_NAME_APPID) + var selection: String? = null + + var selectionArg: Array<String>? = null + appIds?.let { appIds -> + selection = "$COLUMN_NAME_APPID IN (${appIds.joinToString(", ") { "'$it'" }})" + selectionArg = arrayOf() + } + + val db = readableDatabase + val cursor = db.query( + true, + TABLE_NAME, + columns, + selection, + selectionArg, + null, + null, + null, + null + ) + val trackers: MutableList<Tracker> = ArrayList() + while (cursor.moveToNext()) { + val trackerId = cursor.getString(COLUMN_NAME_TRACKER) + val tracker = trackersRepository.getTracker(trackerId) + if (tracker != null) { + trackers.add(tracker) + } + } + cursor.close() + db.close() + return trackers + } + } + + class StatEntry { + var appId = "" + var sum_contacted = 0 + var sum_blocked = 0 + var timestamp: Long = 0 + var tracker = 0 + } + + private fun getCurrentHourTs(): Long { + val hourInMs = TimeUnit.HOURS.toMillis(1L) + val hourInS = TimeUnit.HOURS.toSeconds(1L) + return System.currentTimeMillis() / hourInMs * hourInS + } + + private fun getPeriodStartTs( + periodsCount: Int, + periodUnit: TemporalUnit + ): Long { + var start = ZonedDateTime.now() + .minus(periodsCount.toLong(), periodUnit) + .plus(1, periodUnit) + var truncatePeriodUnit = periodUnit + if (periodUnit === ChronoUnit.MONTHS) { + start = start.withDayOfMonth(1) + truncatePeriodUnit = ChronoUnit.DAYS + } + return start.truncatedTo(truncatePeriodUnit).toEpochSecond() + } + + private fun Cursor.getInt(columnName: String): Int { + val columnIndex = getColumnIndex(columnName) + return if (columnIndex >= 0) getInt(columnIndex) else 0 + } + + private fun Cursor.getLong(columnName: String): Long { + val columnIndex = getColumnIndex(columnName) + return if (columnIndex >= 0) getLong(columnIndex) else 0 + } + + private fun Cursor.getString(columnName: String): String { + val columnIndex = getColumnIndex(columnName) + return if (columnIndex >= 0) { + getStringOrNull(columnIndex) ?: "" + } else "" + } +} |