#923 fix room fuckup

2 files changed, 62 insertions, 1 deletions

diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java
index 6f7ba45e0..34a0515e3 100644
--- a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java
+++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java
@@ -95,6 +95,7 @@ import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migra
 import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_27_28;
 import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_28_29;
 import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_29_30;
+import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_30_31;
 import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_8_9;
 import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migration_9_10;
 
@@ -133,7 +134,7 @@ import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration.Migra
                 FilterWidgetSort.class,
         },
         exportSchema = false,
-        version = 30
+        version = 31
 )
 @TypeConverters({DateTypeConverter.class, EnumConverter.class})
 public abstract class DeckDatabase extends RoomDatabase {
@@ -184,6 +185,7 @@ public abstract class DeckDatabase extends RoomDatabase {
                 .addMigrations(new Migration_27_28())
                 .addMigrations(new Migration_28_29())
                 .addMigrations(new Migration_29_30(context))
+                .addMigrations(new Migration_30_31())
                 .fallbackToDestructiveMigration()
                 .addCallback(ON_CREATE_CALLBACK)
                 .build();
diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/migration/Migration_30_31.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/migration/Migration_30_31.java
new file mode 100644
index 000000000..d15c38daf
--- /dev/null
+++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/migration/Migration_30_31.java
@@ -0,0 +1,59 @@
+package it.niedermann.nextcloud.deck.persistence.sync.adapters.db.migration;
+
+import android.database.Cursor;
+
+import androidx.annotation.NonNull;
+import androidx.room.migration.Migration;
+import androidx.sqlite.db.SupportSQLiteDatabase;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * <a href="https://github.com/stefan-niedermann/nextcloud-deck/issues/923">Foreign keys don't cascade (Cards stay in the database after deleting an Account)</a>
+ */
+public class Migration_30_31 extends Migration {
+
+    private static String[] TABLES = new String[] {"AccessControl", "Activity", "Attachment", "Board", "Card", "DeckComment", "Label", "OcsProject", "OcsProjectResource", "Stack", "User"};
+
+    public Migration_30_31() {
+        super(30, 31);
+    }
+
+    @Override
+    public void migrate(@NonNull SupportSQLiteDatabase database) {
+        for (String table : TABLES) {
+            Cursor cursor = database.query("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", new String[]{table});
+            cursor.moveToNext();
+            String originalCreateStatement = cursor.getString(0);
+            List<String> indexCreates = new ArrayList<>();
+            cursor = database.query("SELECT sql FROM SQLite_master WHERE type = 'index' AND tbl_name=?", new String[]{table});
+            while (cursor.moveToNext()) {
+                indexCreates.add(cursor.getString(0));
+            }
+            String newTableName = "`" + table + "_tmp`";
+            String create = originalCreateStatement.replace("CREATE TABLE `" + table + "`", "CREATE TABLE " + newTableName);
+            create = create.substring(0, create.lastIndexOf(')'));
+            create += ", FOREIGN KEY(`accountId`) REFERENCES `Account`(`id`) ON DELETE CASCADE )";
+            if ("Board".equals(table)) {
+                create = create.replace("SET NULL", "CASCADE");
+            }
+
+            // create copied table
+            database.execSQL(create);
+            // delete obsolete data from original one
+            database.execSQL("DELETE FROM `"+table+"` where accountId not in (select id from `Account`)");
+            // copy remaining rows
+            database.execSQL("INSERT INTO "+newTableName+" select * from `"+table+"`");
+            // remove old table
+            database.execSQL("DROP TABLE `"+table+"`");
+            // let copied table take place of original one
+            database.execSQL("ALTER TABLE "+newTableName+" RENAME TO `"+table+"`");
+            // recreate indices
+            for (String indexCreate : indexCreates) {
+                database.execSQL(indexCreate);
+            }
+        }
+
+    }
+}