Update notif database

7 files changed, 190 insertions, 92 deletions

diff --git a/app/src/androidTest/kotlin/com/pitchedapps/frost/db/NotificationDbTest.kt b/app/src/androidTest/kotlin/com/pitchedapps/frost/db/NotificationDbTest.kt
index 12092bf6..2e9f1875 100644
--- a/app/src/androidTest/kotlin/com/pitchedapps/frost/db/NotificationDbTest.kt
+++ b/app/src/androidTest/kotlin/com/pitchedapps/frost/db/NotificationDbTest.kt
@@ -1,20 +1,93 @@
 package com.pitchedapps.frost.db
 
-import com.pitchedapps.frost.facebook.FbItem
-import com.pitchedapps.frost.facebook.defaultTabs
+import android.database.sqlite.SQLiteConstraintException
+import com.pitchedapps.frost.services.NOTIF_CHANNEL_GENERAL
+import com.pitchedapps.frost.services.NotificationContent
 import kotlinx.coroutines.runBlocking
 import kotlin.test.Test
 import kotlin.test.assertEquals
+import kotlin.test.assertFailsWith
+import kotlin.test.assertTrue
 
 class NotificationDbTest : BaseDbTest() {
 
     private val dao get() = db.notifDao()
 
+    private fun cookie(id: Long) = CookieEntity(id, "name$id", "cookie$id")
+
+    private fun notifContent(id: Long, cookie: CookieEntity, time: Long = id) = NotificationContent(
+        data = cookie,
+        id = id,
+        href = "",
+        title = null,
+        text = "",
+        timestamp = time,
+        profileUrl = null
+    )
+
+    @Test
+    fun saveAndRetrieve() {
+        val cookie = cookie(12345L)
+        // Unique unsorted ids
+        val notifs = listOf(0L, 4L, 2L, 6L, 99L, 3L).map { notifContent(it, cookie) }
+        runBlocking {
+            db.cookieDao().insertCookie(cookie)
+            dao.saveNotifications(NOTIF_CHANNEL_GENERAL, notifs)
+            val dbNotifs = dao.selectNotifications(cookie.id, NOTIF_CHANNEL_GENERAL)
+            assertEquals(notifs.sortedByDescending { it.timestamp }, dbNotifs, "Incorrect notification list received")
+        }
+    }
+
     /**
-     * Note that order is also preserved here
+     * Primary key is both id and userId, in the event that the same notification to multiple users has the same id
      */
     @Test
-    fun save() {
+    fun primaryKeyCheck() {
+        runBlocking {
+            val cookie1 = cookie(12345L)
+            val cookie2 = cookie(12L)
+            val notifs1 = (0L..2L).map { notifContent(it, cookie1) }
+            val notifs2 = notifs1.map { it.copy(data = cookie2) }
+            db.cookieDao().insertCookie(cookie1)
+            db.cookieDao().insertCookie(cookie2)
+            dao.saveNotifications(NOTIF_CHANNEL_GENERAL, notifs1)
+            dao.saveNotifications(NOTIF_CHANNEL_GENERAL, notifs2)
+        }
+    }
 
+    @Test
+    fun cascadeDeletion() {
+        val cookie = cookie(12345L)
+        // Unique unsorted ids
+        val notifs = listOf(0L, 4L, 2L, 6L, 99L, 3L).map { notifContent(it, cookie) }
+        runBlocking {
+            db.cookieDao().insertCookie(cookie)
+            dao.saveNotifications(NOTIF_CHANNEL_GENERAL, notifs)
+            db.cookieDao().deleteById(cookie.id)
+            val dbNotifs = dao.selectNotifications(cookie.id, NOTIF_CHANNEL_GENERAL)
+            assertTrue(dbNotifs.isEmpty(), "Cascade deletion failed")
+        }
+    }
+
+    @Test
+    fun latestEpoch() {
+        val cookie = cookie(12345L)
+        // Unique unsorted ids
+        val notifs = listOf(0L, 4L, 2L, 6L, 99L, 3L).map { notifContent(it, cookie) }
+        runBlocking {
+            assertEquals(-1L, dao.latestEpoch(cookie.id, NOTIF_CHANNEL_GENERAL), "Default epoch failed")
+            db.cookieDao().insertCookie(cookie)
+            dao.saveNotifications(NOTIF_CHANNEL_GENERAL, notifs)
+            assertEquals(99L, dao.latestEpoch(cookie.id, NOTIF_CHANNEL_GENERAL), "Latest epoch failed")
+        }
+    }
+
+    @Test
+    fun insertionWithInvalidCookies() {
+        assertFailsWith(SQLiteConstraintException::class) {
+            runBlocking {
+                dao.saveNotifications(NOTIF_CHANNEL_GENERAL, listOf(notifContent(1L, cookie(2L))))
+            }
+        }
     }
 }
\ No newline at end of file
diff --git a/app/src/main/kotlin/com/pitchedapps/frost/db/CookiesDb.kt b/app/src/main/kotlin/com/pitchedapps/frost/db/CookiesDb.kt
index 34a88011..d5347f18 100644
--- a/app/src/main/kotlin/com/pitchedapps/frost/db/CookiesDb.kt
+++ b/app/src/main/kotlin/com/pitchedapps/frost/db/CookiesDb.kt
@@ -17,6 +17,7 @@
 package com.pitchedapps.frost.db
 
 import android.os.Parcelable
+import androidx.room.ColumnInfo
 import androidx.room.Dao
 import androidx.room.Entity
 import androidx.room.Insert
@@ -38,6 +39,7 @@ import kotlinx.android.parcel.Parcelize
 @Parcelize
 data class CookieEntity(
     @androidx.room.PrimaryKey
+    @ColumnInfo(name = "cookie_id")
     val id: Long,
     val name: String?,
     val cookie: String?
@@ -53,7 +55,7 @@ interface CookieDao {
     @Query("SELECT * FROM cookies")
     suspend fun selectAll(): List<CookieEntity>
 
-    @Query("SELECT * FROM cookies WHERE id = :id")
+    @Query("SELECT * FROM cookies WHERE cookie_id = :id")
     suspend fun selectById(id: Long): CookieEntity?
 
     @Insert(onConflict = OnConflictStrategy.REPLACE)
@@ -62,7 +64,7 @@ interface CookieDao {
     @Insert(onConflict = OnConflictStrategy.REPLACE)
     suspend fun insertCookies(cookies: List<CookieEntity>)
 
-    @Query("DELETE FROM cookies WHERE id = :id")
+    @Query("DELETE FROM cookies WHERE cookie_id = :id")
     suspend fun deleteById(id: Long)
 }
 
diff --git a/app/src/main/kotlin/com/pitchedapps/frost/db/Database.kt b/app/src/main/kotlin/com/pitchedapps/frost/db/Database.kt
index a37c2ee9..1ce9e7c2 100644
--- a/app/src/main/kotlin/com/pitchedapps/frost/db/Database.kt
+++ b/app/src/main/kotlin/com/pitchedapps/frost/db/Database.kt
@@ -14,7 +14,7 @@ interface FrostPrivateDao {
 }
 
 @Database(
-    entities = [CookieEntity::class, NotificationInfoEntity::class, NotificationEntity::class],
+    entities = [CookieEntity::class, NotificationEntity::class],
     version = 1,
     exportSchema = true
 )
diff --git a/app/src/main/kotlin/com/pitchedapps/frost/db/NotificationDb.kt b/app/src/main/kotlin/com/pitchedapps/frost/db/NotificationDb.kt
index 56c3c5ac..9622ec47 100644
--- a/app/src/main/kotlin/com/pitchedapps/frost/db/NotificationDb.kt
+++ b/app/src/main/kotlin/com/pitchedapps/frost/db/NotificationDb.kt
@@ -16,16 +16,17 @@
  */
 package com.pitchedapps.frost.db
 
+import androidx.room.ColumnInfo
 import androidx.room.Dao
 import androidx.room.Embedded
 import androidx.room.Entity
 import androidx.room.ForeignKey
-import androidx.room.Ignore
 import androidx.room.Index
 import androidx.room.Insert
+import androidx.room.OnConflictStrategy
 import androidx.room.Query
-import androidx.room.Relation
 import androidx.room.Transaction
+import com.pitchedapps.frost.services.NotificationContent
 import com.pitchedapps.frost.utils.L
 import com.raizlabs.android.dbflow.annotation.ConflictAction
 import com.raizlabs.android.dbflow.annotation.Database
@@ -41,59 +42,108 @@ import com.raizlabs.android.dbflow.kotlinextensions.where
 import com.raizlabs.android.dbflow.sql.SQLiteType
 import com.raizlabs.android.dbflow.sql.migration.AlterTableMigration
 import com.raizlabs.android.dbflow.structure.BaseModel
-
-@Entity(
-    tableName = "notification_info",
-    foreignKeys = [ForeignKey(
-        entity = CookieEntity::class,
-        parentColumns = ["id"], childColumns = ["id"], onDelete = ForeignKey.CASCADE
-    )]
-)
-data class NotificationInfoEntity(
-    @androidx.room.PrimaryKey val id: Long,
-    val epoch: Long,
-    val epochIm: Long
-)
+import kotlinx.coroutines.Dispatchers
+import kotlinx.coroutines.withContext
 
 @Entity(
     tableName = "notifications",
+    primaryKeys = ["notif_id", "userId"],
     foreignKeys = [ForeignKey(
-        entity = NotificationInfoEntity::class,
-        parentColumns = ["id"],
+        entity = CookieEntity::class,
+        parentColumns = ["cookie_id"],
         childColumns = ["userId"],
         onDelete = ForeignKey.CASCADE
     )],
-    indices = [Index("userId")]
+    indices = [Index("notif_id"), Index("userId")]
 )
 data class NotificationEntity(
-    @androidx.room.PrimaryKey var id: Long,
+    @ColumnInfo(name = "notif_id")
+    val id: Long,
     val userId: Long,
     val href: String,
     val title: String?,
     val text: String,
     val timestamp: Long,
-    val profileUrl: String?
+    val profileUrl: String?,
+    // Type essentially refers to channel
+    val type: String
 ) {
-    @Ignore
-    val notifId = Math.abs(id.toInt())
+    constructor(
+        type: String,
+        content: NotificationContent
+    ) : this(
+        content.id,
+        content.data.id,
+        content.href,
+        content.title,
+        content.text,
+        content.timestamp,
+        content.profileUrl,
+        type
+    )
 }
 
-data class NotificationInfo(
+data class NotificationContentEntity(
     @Embedded
-    val info: NotificationInfoEntity,
-    @Relation(parentColumn = "id", entityColumn = "userId")
-    val notifications: List<NotificationEntity> = emptyList()
-)
+    val cookie: CookieEntity,
+    @Embedded
+    val notif: NotificationEntity
+) {
+    fun toNotifContent() = NotificationContent(
+        data = cookie,
+        id = notif.id,
+        href = notif.href,
+        title = notif.title,
+        text = notif.text,
+        timestamp = notif.timestamp,
+        profileUrl = notif.profileUrl
+    )
+}
 
 @Dao
 interface NotificationDao {
 
-    @Query("SELECT * FROM notification_info WHERE id = :id")
-    fun selectById(id: Long): NotificationInfo?
+    /**
+     * Note that notifications are guaranteed to be ordered by descending timestamp
+     */
+    @Transaction
+    @Query("SELECT * FROM cookies INNER JOIN notifications ON cookie_id = userId WHERE userId = :userId  AND type = :type ORDER BY timestamp DESC")
+    fun _selectNotifications(userId: Long, type: String): List<NotificationContentEntity>
 
+    @Query("SELECT timestamp FROM notifications WHERE userId = :userId AND type = :type ORDER BY timestamp DESC LIMIT 1")
+    fun _selectEpoch(userId: Long, type: String): Long?
+
+    @Insert(onConflict = OnConflictStrategy.REPLACE)
+    fun _insertNotifications(notifs: List<NotificationEntity>)
+
+    @Query("DELETE FROM notifications WHERE userId = :userId AND type = :type")
+    fun _deleteNotifications(userId: Long, type: String)
+
+    /**
+     * It is assumed that the notification batch comes from the same user
+     */
     @Transaction
-    @Insert
-    fun insertInfo(info: NotificationInfoEntity)
+    fun _saveNotifications(type: String, notifs: List<NotificationContent>) {
+        val userId = notifs.firstOrNull()?.data?.id ?: return
+        val entities = notifs.map { NotificationEntity(type, it) }
+        _deleteNotifications(userId, type)
+        _insertNotifications(entities)
+    }
+}
+
+suspend fun NotificationDao.selectNotifications(userId: Long, type: String): List<NotificationContent> =
+    withContext(Dispatchers.IO) {
+        _selectNotifications(userId, type).map { it.toNotifContent() }
+    }
+
+suspend fun NotificationDao.saveNotifications(type: String, notifs: List<NotificationContent>) {
+    withContext(Dispatchers.IO) {
+        _saveNotifications(type, notifs)
+    }
+}
+
+suspend fun NotificationDao.latestEpoch(userId: Long, type: String): Long = withContext(Dispatchers.IO) {
+    _selectEpoch(userId, type) ?: -1
 }
 
 /**
diff --git a/app/src/main/kotlin/com/pitchedapps/frost/services/FrostNotifications.kt b/app/src/main/kotlin/com/pitchedapps/frost/services/FrostNotifications.kt
index abd871b3..7da3c128 100644
--- a/app/src/main/kotlin/com/pitchedapps/frost/services/FrostNotifications.kt
+++ b/app/src/main/kotlin/com/pitchedapps/frost/services/FrostNotifications.kt
@@ -248,6 +248,7 @@ enum class NotificationType(
  * Notification data holder
  */
 data class NotificationContent(
+    // TODO replace data with userId?
     val data: CookieEntity,
     val id: Long,
     val href: String,
diff --git a/app/src/schemas/com.pitchedapps.frost.db.FrostPrivateDatabase/1.json b/app/src/schemas/com.pitchedapps.frost.db.FrostPrivateDatabase/1.json
index f0909a17..c382bce7 100644
--- a/app/src/schemas/com.pitchedapps.frost.db.FrostPrivateDatabase/1.json
+++ b/app/src/schemas/com.pitchedapps.frost.db.FrostPrivateDatabase/1.json
@@ -2,15 +2,15 @@
   "formatVersion": 1,
   "database": {
     "version": 1,
-    "identityHash": "d0911840f629ef359aaf8ac8635dc571",
+    "identityHash": "77eff76407f59b690b8877cc22fac42f",
     "entities": [
       {
         "tableName": "cookies",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER NOT NULL, `name` TEXT, `cookie` TEXT, PRIMARY KEY(`id`))",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`cookie_id` INTEGER NOT NULL, `name` TEXT, `cookie` TEXT, PRIMARY KEY(`cookie_id`))",
         "fields": [
           {
             "fieldPath": "id",
-            "columnName": "id",
+            "columnName": "cookie_id",
             "affinity": "INTEGER",
             "notNull": true
           },
@@ -29,7 +29,7 @@
         ],
         "primaryKey": {
           "columnNames": [
-            "id"
+            "cookie_id"
           ],
           "autoGenerate": false
         },
@@ -37,56 +37,12 @@
         "foreignKeys": []
       },
       {
-        "tableName": "notification_info",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER NOT NULL, `epoch` INTEGER NOT NULL, `epochIm` INTEGER NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY(`id`) REFERENCES `cookies`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
-        "fields": [
-          {
-            "fieldPath": "id",
-            "columnName": "id",
-            "affinity": "INTEGER",
-            "notNull": true
-          },
-          {
-            "fieldPath": "epoch",
-            "columnName": "epoch",
-            "affinity": "INTEGER",
-            "notNull": true
-          },
-          {
-            "fieldPath": "epochIm",
-            "columnName": "epochIm",
-            "affinity": "INTEGER",
-            "notNull": true
-          }
-        ],
-        "primaryKey": {
-          "columnNames": [
-            "id"
-          ],
-          "autoGenerate": false
-        },
-        "indices": [],
-        "foreignKeys": [
-          {
-            "table": "cookies",
-            "onDelete": "CASCADE",
-            "onUpdate": "NO ACTION",
-            "columns": [
-              "id"
-            ],
-            "referencedColumns": [
-              "id"
-            ]
-          }
-        ]
-      },
-      {
         "tableName": "notifications",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER NOT NULL, `userId` INTEGER NOT NULL, `href` TEXT NOT NULL, `title` TEXT, `text` TEXT NOT NULL, `timestamp` INTEGER NOT NULL, `profileUrl` TEXT, PRIMARY KEY(`id`), FOREIGN KEY(`userId`) REFERENCES `notification_info`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`notif_id` INTEGER NOT NULL, `userId` INTEGER NOT NULL, `href` TEXT NOT NULL, `title` TEXT, `text` TEXT NOT NULL, `timestamp` INTEGER NOT NULL, `profileUrl` TEXT, `type` TEXT NOT NULL, PRIMARY KEY(`notif_id`, `userId`), FOREIGN KEY(`userId`) REFERENCES `cookies`(`cookie_id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
         "fields": [
           {
             "fieldPath": "id",
-            "columnName": "id",
+            "columnName": "notif_id",
             "affinity": "INTEGER",
             "notNull": true
           },
@@ -125,16 +81,31 @@
             "columnName": "profileUrl",
             "affinity": "TEXT",
             "notNull": false
+          },
+          {
+            "fieldPath": "type",
+            "columnName": "type",
+            "affinity": "TEXT",
+            "notNull": true
           }
         ],
         "primaryKey": {
           "columnNames": [
-            "id"
+            "notif_id",
+            "userId"
           ],
           "autoGenerate": false
         },
         "indices": [
           {
+            "name": "index_notifications_notif_id",
+            "unique": false,
+            "columnNames": [
+              "notif_id"
+            ],
+            "createSql": "CREATE  INDEX `index_notifications_notif_id` ON `${TABLE_NAME}` (`notif_id`)"
+          },
+          {
             "name": "index_notifications_userId",
             "unique": false,
             "columnNames": [
@@ -145,14 +116,14 @@
         ],
         "foreignKeys": [
           {
-            "table": "notification_info",
+            "table": "cookies",
             "onDelete": "CASCADE",
             "onUpdate": "NO ACTION",
             "columns": [
               "userId"
             ],
             "referencedColumns": [
-              "id"
+              "cookie_id"
             ]
           }
         ]
@@ -161,7 +132,7 @@
     "views": [],
     "setupQueries": [
       "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
-      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"d0911840f629ef359aaf8ac8635dc571\")"
+      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"77eff76407f59b690b8877cc22fac42f\")"
     ]
   }
 }
\ No newline at end of file
diff --git a/app/src/schemas/com.pitchedapps.frost.db.FrostPublicDatabase/1.json b/app/src/schemas/com.pitchedapps.frost.db.FrostPublicDatabase/1.json
index 687d0bc1..fe2aa83e 100644
--- a/app/src/schemas/com.pitchedapps.frost.db.FrostPublicDatabase/1.json
+++ b/app/src/schemas/com.pitchedapps.frost.db.FrostPublicDatabase/1.json
@@ -31,6 +31,7 @@
         "foreignKeys": []
       }
     ],
+    "views": [],
     "setupQueries": [
       "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
       "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"fde868470836ff9230f1d406922d7563\")"