v9: batch of feed URL fixes from July 31 triage; dedupe rename-and-reassign

Live triage pass over every subscription showing "Last fetch failed."
Host-substring LIKE matching, not exact-string like v6-v8, since the
exact previously-stored URL for most of these wasn't known ahead of
time. Also extracts the repeated "rename a feed's id, reassign its
articles, don't let a failure abort the migration transaction" dance
(duplicated inline in v6/v7) into one renameFeedSafely helper used by
both those and the new v9 migration.

1 files changed, 112 insertions, 8 deletions

diff --git a/app/src/main/java/org/terst/feedreader/data/AppDatabase.kt b/app/src/main/java/org/terst/feedreader/data/AppDatabase.kt
index 745148c..7f33439 100644
--- a/app/src/main/java/org/terst/feedreader/data/AppDatabase.kt
+++ b/app/src/main/java/org/terst/feedreader/data/AppDatabase.kt
@@ -1,15 +1,18 @@
 package org.terst.feedreader.data
 
 import android.content.Context
+import android.util.Log
 import androidx.room.Database
 import androidx.room.Room
 import androidx.room.RoomDatabase
 import androidx.sqlite.db.SupportSQLiteDatabase
 import androidx.room.migration.Migration
 
+private const val TAG = "FeedMigration"
+
 @Database(
     entities = [FeedEntity::class, ArticleEntity::class, FilterRuleEntity::class],
-    version = 8,
+    version = 9,
     exportSchema = false
 )
 abstract class AppDatabase : RoomDatabase() {
@@ -20,6 +23,33 @@ abstract class AppDatabase : RoomDatabase() {
     companion object {
         @Volatile private var instance: AppDatabase? = null
 
+        // Renames a feed's id (its primary key) to newUrl, reassigning its articles to
+        // match, safely and without duplicating this dance in every migration that needs
+        // it (v6-v9 all do). Two things make this safe where a plain pair of UPDATEs
+        // wasn't:
+        // - The feeds rename happens FIRST, not last. feeds.id is a PRIMARY KEY; if a row
+        //   at newUrl already exists (e.g. the user manually re-subscribed at the fixed
+        //   URL before this migration got a chance to run -- confirmed as the likely
+        //   cause of one v9 entry needing a second fix), that UPDATE throws before
+        //   anything touches articles at all. articles.feedId has no FK constraint (Room
+        //   doesn't enforce one here), so it essentially can't fail on its own -- putting
+        //   the one statement that CAN fail first means a failure leaves nothing
+        //   reassigned rather than leaving articles pointing at a feedId that doesn't
+        //   correspond to any real feeds row.
+        // - The whole thing is wrapped in try/catch: a failure here (or a fix based on
+        //   research done outside this device, not the actual on-disk rows) must never
+        //   abort Room's migration transaction, which would crash the app on every future
+        //   launch -- Room retries the same failing migration every time the DB is opened.
+        private fun renameFeedSafely(db: SupportSQLiteDatabase, oldUrl: String, newUrl: String) {
+            if (oldUrl == newUrl) return
+            try {
+                db.execSQL("UPDATE feeds SET id = ? WHERE id = ?", arrayOf(newUrl, oldUrl))
+                db.execSQL("UPDATE articles SET feedId = ? WHERE feedId = ?", arrayOf(newUrl, oldUrl))
+            } catch (e: Exception) {
+                Log.e(TAG, "Failed to rename feed '$oldUrl' -> '$newUrl'", e)
+            }
+        }
+
         // v2: added ArticleEntity.imageUrl (thumbnail support). Real migrations,
         // not fallbackToDestructiveMigration, since this app already has real
         // subscriptions/read-state on-device during active development --
@@ -129,8 +159,7 @@ abstract class AppDatabase : RoomDatabase() {
                         "https://feeds.feedburner.com/blogspot/sjomU"
                 )
                 for ((oldUrl, newUrl) in httpToHttps) {
-                    db.execSQL("UPDATE articles SET feedId = ? WHERE feedId = ?", arrayOf(newUrl, oldUrl))
-                    db.execSQL("UPDATE feeds SET id = ? WHERE id = ?", arrayOf(newUrl, oldUrl))
+                    renameFeedSafely(db, oldUrl, newUrl)
                 }
             }
         }
@@ -147,10 +176,85 @@ abstract class AppDatabase : RoomDatabase() {
         // /rss, /atom.xml, and the homepage's own <link rel="alternate"> -- nothing).
         private val MIGRATION_7_8 = object : Migration(7, 8) {
             override fun migrate(db: SupportSQLiteDatabase) {
-                val oldUrl = "http://blog.securitymouse.com/feeds/posts/default"
-                val newUrl = "https://www.blogger.com/feeds/5803873077727212399/posts/default"
-                db.execSQL("UPDATE articles SET feedId = ? WHERE feedId = ?", arrayOf(newUrl, oldUrl))
-                db.execSQL("UPDATE feeds SET id = ? WHERE id = ?", arrayOf(newUrl, oldUrl))
+                renameFeedSafely(
+                    db,
+                    "http://blog.securitymouse.com/feeds/posts/default",
+                    "https://www.blogger.com/feeds/5803873077727212399/posts/default"
+                )
+            }
+        }
+
+        // v9: batch of feed fixes from a live triage pass over every subscription showing
+        // "Last fetch failed" (2026-07-31). Unlike v6-v8's exact-string matches, this uses a
+        // host-substring LIKE match for the URL rewrites: the exact previously-stored URL for
+        // most of these wasn't known ahead of time (only the feed's title and, for some, the
+        // failing host from its error message), so matching on host is what's actually
+        // verifiable here. Worst case for a wrong guess is a silent no-op (the row just stays
+        // whatever it already was) -- there's no destructive failure mode. Every replacement
+        // URL below was fetched live and confirmed to return real RSS/Atom XML before being
+        // written into this migration.
+        private val MIGRATION_8_9 = object : Migration(8, 9) {
+            override fun migrate(db: SupportSQLiteDatabase) {
+                // host/old-scheme fragment (LIKE-matched against feeds.id) -> confirmed-working URL.
+                val fixes = listOf(
+                    "%sailingworld.com%" to "https://www.sailingworld.com/feed/",
+                    "%cruisingworld.com%" to "https://www.cruisingworld.com/feed/",
+                    "%shapeof.com%" to "https://shapeof.com/rss.xml",
+                    "%furbo.org%" to "https://furbo.org/feed/",
+                    "%netnewswire.blog%" to "https://netnewswire.blog/feed.xml",
+                    "%isdonaldtrumpalive.com%" to "https://isdonaldtrumpalive.com/latest/rss/",
+                    "%amateurgourmet.com%" to "https://amateurgourmet.substack.com/feed",
+                    // Scoped to the http:// + exact feedburner slug so this can't ever touch
+                    // the OTHER feedburner-hosted feed (divingforpearlsblog, migrated in v7)
+                    // that already lives at a different path on the same feedburner.com host.
+                    "http://feeds.feedburner.com/GoogleOnlineSecurityBlog%" to
+                        "https://feeds.feedburner.com/GoogleOnlineSecurityBlog",
+                    "http://www.darknet.org.uk%" to "https://www.darknet.org.uk/feed/",
+                    // Re-fixes cryptographyengineering.com: v7 already migrated ONE exact old
+                    // URL for this blog, but it's showing the same cleartext failure again --
+                    // most likely re-subscribed since with a plain http:// URL that missed that
+                    // exact-match migration. Scoped to http:// specifically so this can't touch
+                    // v7's already-https row even if that one's somehow still present verbatim.
+                    "http://blog.cryptographyengineering.com%" to
+                        "https://blog.cryptographyengineering.com/feed/"
+                )
+                // Looked up per pattern, then renamed one row at a time via renameFeedSafely
+                // (not one big per-pattern try/catch): this migration is built entirely from
+                // live research done outside this device, not from reading its actual on-disk
+                // rows first, so a LIKE pattern matching more than one existing row is a real
+                // possibility (e.g. two slightly different subscriptions to the same site).
+                // Isolating failures per row means one bad row can't stop a pattern's OTHER
+                // matches from getting fixed too.
+                for ((oldUrlLike, newUrl) in fixes) {
+                    val oldUrls = mutableListOf<String>()
+                    try {
+                        db.query("SELECT id FROM feeds WHERE id LIKE ?", arrayOf(oldUrlLike)).use { cursor ->
+                            while (cursor.moveToNext()) oldUrls.add(cursor.getString(0))
+                        }
+                    } catch (e: Exception) {
+                        Log.e(TAG, "v9 lookup failed for pattern '$oldUrlLike'", e)
+                    }
+                    for (oldUrl in oldUrls) {
+                        renameFeedSafely(db, oldUrl, newUrl)
+                    }
+                }
+
+                // Two feeds from the same triage pass whose failing error (a real XML parse
+                // failure, e.g. "Parameter entity references are not supported") confirmed a
+                // feed genuinely exists, but neither title resolved to a specific identifiable
+                // blog -- dropped outright rather than guessing at a replacement URL.
+                val titlesToDrop = listOf("%Knowing and Doing%", "%Malware Reversing%")
+                for (titleLike in titlesToDrop) {
+                    try {
+                        db.execSQL(
+                            "DELETE FROM articles WHERE feedId IN (SELECT id FROM feeds WHERE title LIKE ?)",
+                            arrayOf(titleLike)
+                        )
+                        db.execSQL("DELETE FROM feeds WHERE title LIKE ?", arrayOf(titleLike))
+                    } catch (e: Exception) {
+                        Log.e(TAG, "v9 drop failed for title pattern '$titleLike'", e)
+                    }
+                }
             }
         }
 
@@ -161,7 +265,7 @@ abstract class AppDatabase : RoomDatabase() {
                 "feedreader.db"
             ).addMigrations(
                 MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5,
-                MIGRATION_5_6, MIGRATION_6_7, MIGRATION_7_8
+                MIGRATION_5_6, MIGRATION_6_7, MIGRATION_7_8, MIGRATION_8_9
             ).build().also { instance = it }
         }
     }