From 32ad05360ecf167803ef0bf9c7938c52b348123e Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Fri, 5 Feb 2021 04:48:13 +0000 Subject: [PATCH] cascade delete of template_accounts from templates --- .../net.ktnx.mobileledger.db.DB/55.json | 276 ++++++++++++++++++ .../java/net/ktnx/mobileledger/db/DB.java | 37 ++- .../ktnx/mobileledger/db/TemplateAccount.java | 6 +- .../utils/MobileLedgerDatabase.java | 5 +- 4 files changed, 320 insertions(+), 4 deletions(-) create mode 100644 app/schemas/net.ktnx.mobileledger.db.DB/55.json 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 index 00000000..8c790a39 --- /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 9a73f4d4..a404ed96 100644 --- 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 c662ff1c..8651e3fe 100644 --- 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 3785ddd3..0a9a509b 100644 --- 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 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); -- 2.39.2