1 files changed, 105 insertions, 0 deletions
diff --git a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
index 836ec3d..52f07eb 100644
--- a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
+++ b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
@@ -698,4 +698,109 @@ class MigrationTest {
             assertEquals("the crown jewel must survive", 141421L, it.getLong(0))
         }
     }
+
+    /**
+     * The transcript tables arrive empty and accept a cue. Nothing else moves: words are new
+     * evidence about an episode, not a change to anything already stored.
+     *
+     * One row per cue rather than a JSON blob on the episode. Android reads every Room row
+     * through a CursorWindow that refuses a row over 2MB on the way *out*, which is the bug
+     * that made intro detection crash the app the moment a show's second episode was
+     * fingerprinted -- an insert that succeeds and a read that throws. A long episode's
+     * transcript runs to thousands of cues, and a column holding all of them is the same shape
+     * of mistake waiting to be made again.
+     */
+    @Test
+    fun migrate16To17AddsTranscriptsAndLeavesEverythingElseAlone() {
+        helper.createDatabase(name, 16).apply {
+            execSQL(
+                """INSERT INTO subscriptions
+                   (id, feedUrl, kind, title, addedAtMs, skipIntroMs, skipOutroMs, dripEnabled,
+                    dripIntervalMs, dripCount, dripOldestFirst, skipAdChapters, bedtime)
+                   VALUES ('s1', 'https://example.com/feed.xml', 'PODCAST', 'A Show', 1000, 0, 0,
+                           0, 86400000, 1, 1, 1, 0)"""
+            )
+            execSQL(
+                """INSERT INTO episodes
+                   (id, subscriptionId, guid, title, mediaUrl, mediaType, discoveredAtMs,
+                    positionMs, downloadState)
+                   VALUES ('e1', 's1', 'g1', 'One', 'https://cdn/1.mp3', 'audio/mpeg', 1000,
+                           271828, 'NONE')"""
+            )
+            close()
+        }
+
+        val db = helper.runMigrationsAndValidate(name, 17, true, AppDatabase.MIGRATION_16_17)
+
+        db.query("SELECT COUNT(*) FROM transcript_cues").use {
+            it.moveToFirst()
+            assertEquals(0, it.getInt(0))
+        }
+        db.execSQL(
+            "INSERT INTO episode_transcripts (episodeId, source, cues, note, fetchedAtMs) " +
+                "VALUES ('e1', 'published', 2, '', 99)"
+        )
+        db.execSQL(
+            "INSERT INTO transcript_cues (episodeId, startMs, endMs, text) " +
+                "VALUES ('e1', 0, 3120, 'Welcome back.')"
+        )
+        db.query("SELECT text FROM transcript_cues WHERE episodeId = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("Welcome back.", it.getString(0))
+        }
+        db.query("SELECT source FROM episode_transcripts WHERE episodeId = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("published", it.getString(0))
+        }
+        db.query("SELECT positionMs FROM episodes WHERE id = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("the crown jewel must survive", 271828L, it.getLong(0))
+        }
+    }
+
+    /**
+     * An episode's words go when the episode does. A transcript of a row nobody has any more is
+     * dead weight, and the cue table is the biggest thing in this database by row count.
+     */
+    @Test
+    fun deletingAnEpisodeTakesItsTranscriptWithIt() {
+        helper.createDatabase(name, 16).apply {
+            execSQL(
+                """INSERT INTO subscriptions
+                   (id, feedUrl, kind, title, addedAtMs, skipIntroMs, skipOutroMs, dripEnabled,
+                    dripIntervalMs, dripCount, dripOldestFirst, skipAdChapters, bedtime)
+                   VALUES ('s1', 'https://example.com/feed.xml', 'PODCAST', 'A Show', 1000, 0, 0,
+                           0, 86400000, 1, 1, 1, 0)"""
+            )
+            execSQL(
+                """INSERT INTO episodes
+                   (id, subscriptionId, guid, title, mediaUrl, mediaType, discoveredAtMs,
+                    positionMs, downloadState)
+                   VALUES ('e1', 's1', 'g1', 'One', 'https://cdn/1.mp3', 'audio/mpeg', 1000, 0,
+                           'NONE')"""
+            )
+            close()
+        }
+
+        val db = helper.runMigrationsAndValidate(name, 17, true, AppDatabase.MIGRATION_16_17)
+        db.execSQL("PRAGMA foreign_keys = ON")
+        db.execSQL(
+            "INSERT INTO episode_transcripts (episodeId, source, cues, note, fetchedAtMs) " +
+                "VALUES ('e1', 'published', 1, '', 99)"
+        )
+        db.execSQL(
+            "INSERT INTO transcript_cues (episodeId, startMs, endMs, text) " +
+                "VALUES ('e1', 0, 1000, 'Hello.')"
+        )
+        db.execSQL("DELETE FROM episodes WHERE id = 'e1'")
+
+        db.query("SELECT COUNT(*) FROM transcript_cues").use {
+            it.moveToFirst()
+            assertEquals("cues outlived their episode", 0, it.getInt(0))
+        }
+        db.query("SELECT COUNT(*) FROM episode_transcripts").use {
+            it.moveToFirst()
+            assertEquals("the transcript row outlived its episode", 0, it.getInt(0))
+        }
+    }
 }