cascade delete of template_accounts from templates

diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/55.json b/app/schemas/net.ktnx.mobileledger.db.DB/55.json
new file mode 100644 (file)
index 0000000..8c790a3
--- /dev/null
+++ b/app/schemas/net.ktnx.mobileledger.db.DB/55.json
@@ -0,0 +1,276 @@
+{
+  "formatVersion": 1,
+  "database": {
+    "version": 55,
+    "identityHash": "ed75412e9453605c9829ad7f3269f62e",
+    "entities": [
+      {
+        "tableName": "templates",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `regular_expression` TEXT NOT NULL, `test_text` TEXT, `transaction_description` TEXT, `transaction_description_match_group` INTEGER, `transaction_comment` TEXT, `transaction_comment_match_group` INTEGER, `date_year` INTEGER, `date_year_match_group` INTEGER, `date_month` INTEGER, `date_month_match_group` INTEGER, `date_day` INTEGER, `date_day_match_group` INTEGER)",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "regularExpression",
+            "columnName": "regular_expression",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "testText",
+            "columnName": "test_text",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionDescription",
+            "columnName": "transaction_description",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionDescriptionMatchGroup",
+            "columnName": "transaction_description_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionComment",
+            "columnName": "transaction_comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionCommentMatchGroup",
+            "columnName": "transaction_comment_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateYear",
+            "columnName": "date_year",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateYearMatchGroup",
+            "columnName": "date_year_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateMonth",
+            "columnName": "date_month",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateMonthMatchGroup",
+            "columnName": "date_month_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateDay",
+            "columnName": "date_day",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateDayMatchGroup",
+            "columnName": "date_day_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [],
+        "foreignKeys": []
+      },
+      {
+        "tableName": "template_accounts",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `template_id` INTEGER NOT NULL, `acc` TEXT, `position` INTEGER NOT NULL, `acc_match_group` INTEGER, `currency` INTEGER, `currency_match_group` INTEGER, `amount` REAL, `amount_match_group` INTEGER, `comment` TEXT, `comment_match_group` INTEGER, `negate_amount` INTEGER, FOREIGN KEY(`template_id`) REFERENCES `templates`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY(`currency`) REFERENCES `currencies`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "templateId",
+            "columnName": "template_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountName",
+            "columnName": "acc",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "position",
+            "columnName": "position",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountNameMatchGroup",
+            "columnName": "acc_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "currency",
+            "columnName": "currency",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "currencyMatchGroup",
+            "columnName": "currency_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "amount",
+            "columnName": "amount",
+            "affinity": "REAL",
+            "notNull": false
+          },
+          {
+            "fieldPath": "amountMatchGroup",
+            "columnName": "amount_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "accountComment",
+            "columnName": "comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "accountCommentMatchGroup",
+            "columnName": "comment_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "negateAmount",
+            "columnName": "negate_amount",
+            "affinity": "INTEGER",
+            "notNull": false
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "fk_template_accounts_template",
+            "unique": false,
+            "columnNames": [
+              "template_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_template` ON `${TABLE_NAME}` (`template_id`)"
+          },
+          {
+            "name": "fk_template_accounts_currency",
+            "unique": false,
+            "columnNames": [
+              "currency"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_currency` ON `${TABLE_NAME}` (`currency`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "templates",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "template_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          },
+          {
+            "table": "currencies",
+            "onDelete": "RESTRICT",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "currency"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      },
+      {
+        "tableName": "currencies",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `position` TEXT NOT NULL, `has_gap` INTEGER NOT NULL)",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "position",
+            "columnName": "position",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "hasGap",
+            "columnName": "has_gap",
+            "affinity": "INTEGER",
+            "notNull": true
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [],
+        "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, 'ed75412e9453605c9829ad7f3269f62e')"
+    ]
+  }
+}
\ No newline at end of file

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/DB.java b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
index 9a73f4d45601bae8be56287325d474637e917ff0..a404ed963c821529be268d49868b09a04deb54c3 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
@@ -30,7 +30,7 @@ import net.ktnx.mobileledger.dao.TemplateAccountDAO;
 import net.ktnx.mobileledger.dao.TemplateHeaderDAO;
 import net.ktnx.mobileledger.utils.MobileLedgerDatabase;
 
-@Database(version = 54, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class})
+@Database(version = 55, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class})
 abstract public class DB extends RoomDatabase {
     private static DB instance;
     public static DB get() {
@@ -119,6 +119,41 @@ abstract public class DB extends RoomDatabase {
                                 db.execSQL("drop table pattern_accounts");
                                 db.execSQL("drop table patterns");
                             }
+                        }, new Migration(54, 55) {
+                            @Override
+                            public void migrate(@NonNull SupportSQLiteDatabase db) {
+                                db.execSQL(
+                                        "CREATE TABLE template_accounts_new (id INTEGER PRIMARY " +
+                                        "KEY " +
+                                        "AUTOINCREMENT NOT NULL, template_id INTEGER NOT NULL, " +
+                                        "acc TEXT, position INTEGER NOT NULL, acc_match_group " +
+                                        "INTEGER, currency INTEGER, currency_match_group INTEGER," +
+                                        " amount REAL, amount_match_group INTEGER, comment TEXT, " +
+                                        "comment_match_group INTEGER, negate_amount INTEGER, " +
+                                        "FOREIGN KEY(template_id) REFERENCES templates(id) ON " +
+                                        "UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY" +
+                                        "(currency) REFERENCES currencies(id) ON UPDATE RESTRICT" +
+                                        " ON DELETE RESTRICT)");
+                                db.execSQL(
+                                        "insert into template_accounts_new(id, template_id, acc, " +
+                                        "position, acc_match_group, currency, " +
+                                        "currency_match_group, amount, amount_match_group, " +
+                                        "amount, amount_match_group, comment, " +
+                                        "comment_match_group, negate_amount) select id, " +
+                                        "template_id, acc, position, acc_match_group, " +
+                                        "currency, " +
+                                        "currency_match_group, amount, amount_match_group, " +
+                                        "amount, amount_match_group, comment, " +
+                                        "comment_match_group, negate_amount from " +
+                                        "template_accounts");
+                                db.execSQL("drop table template_accounts");
+                                db.execSQL("alter table template_accounts_new rename to " +
+                                           "template_accounts");
+                                db.execSQL("create index fk_template_accounts_template on " +
+                                           "template_accounts(template_id)");
+                                db.execSQL("create index fk_template_accounts_currency on " +
+                                           "template_accounts(currency)");
+                            }
                         }
                         })
                         .addCallback(new Callback() {

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/TemplateAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/TemplateAccount.java
index c662ff1c18092121ebf6f6b74bbffe1ec98be595..8651e3fee09ba66ac857788e30a62e0e04894e84 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/TemplateAccount.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/TemplateAccount.java
@@ -30,9 +30,11 @@ import org.jetbrains.annotations.NotNull;
         indices = {@Index(name = "fk_template_accounts_template", value = "template_id"),
                    @Index(name = "fk_template_accounts_currency", value = "currency")
         }, foreignKeys = {@ForeignKey(childColumns = "template_id", parentColumns = "id",
-                                      entity = TemplateHeader.class),
+                                      entity = TemplateHeader.class, onDelete = ForeignKey.CASCADE,
+                                      onUpdate = ForeignKey.RESTRICT),
                           @ForeignKey(childColumns = "currency", parentColumns = "id",
-                                      entity = Currency.class)
+                                      entity = Currency.class, onDelete = ForeignKey.RESTRICT,
+                                      onUpdate = ForeignKey.RESTRICT)
 })
 public class TemplateAccount extends TemplateBase {
     @PrimaryKey(autoGenerate = true)

diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java
index 3785ddd3324ad57c9aa3d9b2d1eac3494c03b3de..0a9a509ba6556b012fdd9810d2a9ed8aa162f27a 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java
+++ b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java
@@ -39,7 +39,7 @@ import static net.ktnx.mobileledger.utils.Logger.debug;
 public class MobileLedgerDatabase extends SQLiteOpenHelper {
     public static final MutableLiveData<Boolean> initComplete = new MutableLiveData<>(false);
     public static final String DB_NAME = "MoLe.db";
-    private static final int LATEST_REVISION = 54;
+    private static final int LATEST_REVISION = 55;
     private static final String CREATE_DB_SQL = "create_db";
     private final Application mContext;
 
@@ -72,6 +72,9 @@ public class MobileLedgerDatabase extends SQLiteOpenHelper {
             applyRevision(db, i);
     }
     private void applyRevision(SQLiteDatabase db, int rev_no) {
+        if (rev_no == 55)
+            return;
+
         String rev_file = String.format(Locale.US, "sql_%d", rev_no);
 
         applyRevisionFile(db, rev_file);