From 2aec7de70b2d512f20105a64255237c0119f2b1c Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Sun, 31 Jan 2021 13:23:41 +0200 Subject: [PATCH] add two indices, corresponding to foreign keys to help Room --- .../net.ktnx.mobileledger.db.DB/51.json | 20 +- .../net.ktnx.mobileledger.db.DB/52.json | 287 ++++++++++++++++++ .../java/net/ktnx/mobileledger/db/DB.java | 24 +- .../ktnx/mobileledger/db/PatternAccount.java | 32 +- .../utils/MobileLedgerDatabase.java | 2 +- app/src/main/res/raw/create_db.sql | 4 +- app/src/main/res/raw/sql_52.sql | 17 ++ 7 files changed, 375 insertions(+), 11 deletions(-) create mode 100644 app/schemas/net.ktnx.mobileledger.db.DB/52.json create mode 100644 app/src/main/res/raw/sql_52.sql diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/51.json b/app/schemas/net.ktnx.mobileledger.db.DB/51.json index e9fbfab7..f755a4bd 100644 --- a/app/schemas/net.ktnx.mobileledger.db.DB/51.json +++ b/app/schemas/net.ktnx.mobileledger.db.DB/51.json @@ -2,7 +2,7 @@ "formatVersion": 1, "database": { "version": 51, - "identityHash": "ed0a974c5dddf4db12e6ecd71552b550", + "identityHash": "d80c63258c511ee305dc5f0062a8af2a", "entities": [ { "tableName": "patterns", @@ -196,6 +196,22 @@ "id" ], "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_pattern_accounts` ON `${TABLE_NAME}` (`id`)" + }, + { + "name": "fk_pattern_accounts_pattern", + "unique": false, + "columnNames": [ + "pattern_id" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_pattern_accounts_pattern` ON `${TABLE_NAME}` (`pattern_id`)" + }, + { + "name": "fk_pattern_accounts_currency", + "unique": false, + "columnNames": [ + "currency" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_pattern_accounts_currency` ON `${TABLE_NAME}` (`currency`)" } ], "foreignKeys": [ @@ -265,7 +281,7 @@ "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, 'ed0a974c5dddf4db12e6ecd71552b550')" + "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, 'd80c63258c511ee305dc5f0062a8af2a')" ] } } \ No newline at end of file diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/52.json b/app/schemas/net.ktnx.mobileledger.db.DB/52.json new file mode 100644 index 00000000..883df1fe --- /dev/null +++ b/app/schemas/net.ktnx.mobileledger.db.DB/52.json @@ -0,0 +1,287 @@ +{ + "formatVersion": 1, + "database": { + "version": 52, + "identityHash": "d80c63258c511ee305dc5f0062a8af2a", + "entities": [ + { + "tableName": "patterns", + "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": [ + { + "name": "un_patterns_id", + "unique": true, + "columnNames": [ + "id" + ], + "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_patterns_id` ON `${TABLE_NAME}` (`id`)" + } + ], + "foreignKeys": [] + }, + { + "tableName": "pattern_accounts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`pattern_id` INTEGER NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT 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, FOREIGN KEY(`pattern_id`) REFERENCES `patterns`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`currency`) REFERENCES `currencies`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION )", + "fields": [ + { + "fieldPath": "patternId", + "columnName": "pattern_id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "id", + "columnName": "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 + } + ], + "primaryKey": { + "columnNames": [ + "id" + ], + "autoGenerate": true + }, + "indices": [ + { + "name": "un_pattern_accounts", + "unique": true, + "columnNames": [ + "id" + ], + "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_pattern_accounts` ON `${TABLE_NAME}` (`id`)" + }, + { + "name": "fk_pattern_accounts_pattern", + "unique": false, + "columnNames": [ + "pattern_id" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_pattern_accounts_pattern` ON `${TABLE_NAME}` (`pattern_id`)" + }, + { + "name": "fk_pattern_accounts_currency", + "unique": false, + "columnNames": [ + "currency" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_pattern_accounts_currency` ON `${TABLE_NAME}` (`currency`)" + } + ], + "foreignKeys": [ + { + "table": "patterns", + "onDelete": "NO ACTION", + "onUpdate": "NO ACTION", + "columns": [ + "pattern_id" + ], + "referencedColumns": [ + "id" + ] + }, + { + "table": "currencies", + "onDelete": "NO ACTION", + "onUpdate": "NO ACTION", + "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, 'd80c63258c511ee305dc5f0062a8af2a')" + ] + } +} \ 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 2dbc7c24..f68fccd5 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java @@ -17,9 +17,12 @@ package net.ktnx.mobileledger.db; +import androidx.annotation.NonNull; import androidx.room.Database; import androidx.room.Room; import androidx.room.RoomDatabase; +import androidx.room.migration.Migration; +import androidx.sqlite.db.SupportSQLiteDatabase; import net.ktnx.mobileledger.App; import net.ktnx.mobileledger.dao.CurrencyDAO; @@ -27,7 +30,7 @@ import net.ktnx.mobileledger.dao.PatternAccountDAO; import net.ktnx.mobileledger.dao.PatternHeaderDAO; import net.ktnx.mobileledger.utils.MobileLedgerDatabase; -@Database(version = 51, entities = {PatternHeader.class, PatternAccount.class, Currency.class}) +@Database(version = 52, entities = {PatternHeader.class, PatternAccount.class, Currency.class}) abstract public class DB extends RoomDatabase { private static DB instance; public static DB get() { @@ -39,10 +42,29 @@ abstract public class DB extends RoomDatabase { return instance = Room.databaseBuilder(App.instance, DB.class, MobileLedgerDatabase.DB_NAME) + .addMigrations(new Migration[]{new Migration(51, 52) { + @Override + public void migrate(@NonNull SupportSQLiteDatabase db) { + db.beginTransaction(); + try { + db.execSQL("create index fk_pattern_accounts_pattern on " + + "pattern_accounts(pattern_id);"); + db.execSQL("create index fk_pattern_accounts_currency on " + + "pattern_accounts(currency);"); + db.setTransactionSuccessful(); + } + finally { + db.endTransaction(); + } + } + } + }) .build(); } } public abstract PatternHeaderDAO getPatternDAO(); + public abstract PatternAccountDAO getPatternAccountDAO(); + public abstract CurrencyDAO getCurrencyDAO(); } diff --git a/app/src/main/java/net/ktnx/mobileledger/db/PatternAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/PatternAccount.java index 2b91c5d1..061379d0 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/PatternAccount.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/PatternAccount.java @@ -1,3 +1,20 @@ +/* + * Copyright © 2021 Damyan Ivanov. + * This file is part of MoLe. + * MoLe is free software: you can distribute it and/or modify it + * under the term of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your opinion), any later version. + * + * MoLe is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License terms for details. + * + * You should have received a copy of the GNU General Public License + * along with MoLe. If not, see . + */ + package net.ktnx.mobileledger.db; import androidx.annotation.NonNull; @@ -10,12 +27,15 @@ import androidx.room.PrimaryKey; import org.jetbrains.annotations.NotNull; @Entity(tableName = "pattern_accounts", - indices = {@Index(name = "un_pattern_accounts", unique = true, value = "id")}, - foreignKeys = {@ForeignKey(childColumns = "pattern_id", parentColumns = "id", - entity = PatternHeader.class), - @ForeignKey(childColumns = "currency", parentColumns = "id", - entity = Currency.class) - }) + indices = {@Index(name = "un_pattern_accounts", unique = true, value = "id"), + @Index(name = "fk_pattern_accounts_pattern", unique = false, + value = "pattern_id"), + @Index(name = "fk_pattern_accounts_currency", unique = false, value = "currency") + }, foreignKeys = {@ForeignKey(childColumns = "pattern_id", parentColumns = "id", + entity = PatternHeader.class), + @ForeignKey(childColumns = "currency", parentColumns = "id", + entity = Currency.class) +}) public class PatternAccount extends PatternBase { @NonNull @ColumnInfo(name = "pattern_id") 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 97f2b75b..eab47902 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 = 51; + private static final int LATEST_REVISION = 52; private static final String CREATE_DB_SQL = "create_db"; private final Application mContext; diff --git a/app/src/main/res/raw/create_db.sql b/app/src/main/res/raw/create_db.sql index 31cd5bb7..5b1cd52c 100644 --- a/app/src/main/res/raw/create_db.sql +++ b/app/src/main/res/raw/create_db.sql @@ -33,4 +33,6 @@ create table patterns(id INTEGER not null primary key, name TEXT not null, regul create unique index un_patterns_id on patterns(id); create table pattern_accounts(id INTEGER not null primary key, pattern_id INTEGER not null, position INTEGER not null, acc TEXT, acc_match_group INTEGER, currency INTEGER, currency_match_group INTEGER, amount REAL, amount_match_group INTEGER, comment TEXT, comment_match_group INTEGER, constraint fk_pattern_account_pattern foreign key(pattern_id) references patterns(id), constraint fk_pattern_account_currency foreign key(currency) references currencies(id)); create unique index un_pattern_accounts on pattern_accounts(id); --- updated to revision 48 \ No newline at end of file +create index fk_pattern_account_pattern on pattern_accounts(pattern_id); +create index fk_pattern_account_currency on pattern_accounts(currency); +-- updated to revision 52 \ No newline at end of file diff --git a/app/src/main/res/raw/sql_52.sql b/app/src/main/res/raw/sql_52.sql new file mode 100644 index 00000000..69fb8035 --- /dev/null +++ b/app/src/main/res/raw/sql_52.sql @@ -0,0 +1,17 @@ +-- Copyright © 2020 Damyan Ivanov. +-- This file is part of MoLe. +-- MoLe is free software: you can distribute it and/or modify it +-- under the term of the GNU General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your opinion), any later version. +-- +-- MoLe is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License terms for details. +-- +-- You should have received a copy of the GNU General Public License +-- along with MoLe. If not, see . + +create index if not exists fk_pattern_accounts_pattern on pattern_accounts(pattern_id); +create index if not exists fk_pattern_accounts_currency on pattern_accounts(currency); \ No newline at end of file -- 2.39.5