app/src/main/java/net/ktnx/mobileledger/db/DB.java

   1 /*
   2  * Copyright © 2021 Damyan Ivanov.
   3  * This file is part of MoLe.
   4  * MoLe is free software: you can distribute it and/or modify it
   5  * under the term of the GNU General Public License as published by
   6  * the Free Software Foundation, either version 3 of the License, or
   7  * (at your opinion), any later version.
   8  *
   9  * MoLe is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12  * GNU General Public License terms for details.
  13  *
  14  * You should have received a copy of the GNU General Public License
  15  * along with MoLe. If not, see <https://www.gnu.org/licenses/>.
  16  */
  17
  18 package net.ktnx.mobileledger.db;
  19
  20 import androidx.annotation.NonNull;
  21 import androidx.room.Database;
  22 import androidx.room.Room;
  23 import androidx.room.RoomDatabase;
  24 import androidx.room.migration.Migration;
  25 import androidx.sqlite.db.SupportSQLiteDatabase;
  26
  27 import net.ktnx.mobileledger.App;
  28 import net.ktnx.mobileledger.dao.AccountDAO;
  29 import net.ktnx.mobileledger.dao.CurrencyDAO;
  30 import net.ktnx.mobileledger.dao.TemplateAccountDAO;
  31 import net.ktnx.mobileledger.dao.TemplateHeaderDAO;
  32 import net.ktnx.mobileledger.utils.MobileLedgerDatabase;
  33
  34 @Database(version = 58,
  35           entities = {TemplateHeader.class, TemplateAccount.class, Currency.class, Account.class,
  36                       Profile.class, Option.class, AccountValue.class, DescriptionHistory.class,
  37                       Transaction.class
  38           })
  39 abstract public class DB extends RoomDatabase {
  40     private static DB instance;
  41     public static DB get() {
  42         if (instance != null)
  43             return instance;
  44         synchronized (DB.class) {
  45             if (instance != null)
  46                 return instance;
  47
  48             return instance =
  49                     Room.databaseBuilder(App.instance, DB.class, MobileLedgerDatabase.DB_NAME)
  50                         .addMigrations(new Migration[]{new Migration(51, 52) {
  51                             @Override
  52                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  53                                 db.beginTransaction();
  54                                 try {
  55                                     db.execSQL("create index fk_pattern_accounts_pattern on " +
  56                                                "pattern_accounts(pattern_id);");
  57                                     db.execSQL("create index fk_pattern_accounts_currency on " +
  58                                                "pattern_accounts(currency);");
  59                                     db.setTransactionSuccessful();
  60                                 }
  61                                 finally {
  62                                     db.endTransaction();
  63                                 }
  64                             }
  65                         }, new Migration(52, 53) {
  66                             @Override
  67                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  68                                 db.execSQL(
  69                                         "alter table pattern_accounts add negate_amount boolean;");
  70                             }
  71                         }, new Migration(53, 54) {
  72                             @Override
  73                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  74                                 db.execSQL("CREATE TABLE templates (id INTEGER PRIMARY KEY " +
  75                                            "AUTOINCREMENT NOT NULL, name TEXT NOT NULL, " +
  76                                            "regular_expression TEXT NOT NULL, test_text TEXT, " +
  77                                            "transaction_description TEXT, " +
  78                                            "transaction_description_match_group INTEGER, " +
  79                                            "transaction_comment TEXT, " +
  80                                            "transaction_comment_match_group INTEGER, date_year " +
  81                                            "INTEGER, date_year_match_group INTEGER, date_month " +
  82                                            "INTEGER, date_month_match_group INTEGER, date_day " +
  83                                            "INTEGER, date_day_match_group INTEGER)");
  84                                 db.execSQL(
  85                                         "CREATE TABLE template_accounts (id INTEGER PRIMARY KEY " +
  86                                         "AUTOINCREMENT NOT NULL, template_id INTEGER NOT NULL, " +
  87                                         "acc TEXT, position INTEGER NOT NULL, acc_match_group " +
  88                                         "INTEGER, currency INTEGER, currency_match_group INTEGER," +
  89                                         " amount REAL, amount_match_group INTEGER, comment TEXT, " +
  90                                         "comment_match_group INTEGER, negate_amount INTEGER, " +
  91                                         "FOREIGN KEY(template_id) REFERENCES templates(id) ON " +
  92                                         "UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY" +
  93                                         "(currency) REFERENCES currencies(id) ON UPDATE NO ACTION" +
  94                                         " ON DELETE NO ACTION )");
  95                                 db.execSQL("insert into templates(id, name, regular_expression, " +
  96                                            "test_text, transaction_description, " +
  97                                            "transaction_description_match_group, " +
  98                                            "transaction_comment, transaction_comment_match_group," +
  99                                            " date_year, date_year_match_group, date_month, " +
 100                                            "date_month_match_group, date_day, " +
 101                                            "date_day_match_group)" +
 102                                            " select id, name, regular_expression, test_text, " +
 103                                            "transaction_description, " +
 104                                            "transaction_description_match_group, " +
 105                                            "transaction_comment, transaction_comment_match_group," +
 106                                            " date_year, date_year_match_group, date_month, " +
 107                                            "date_month_match_group, date_day, " +
 108                                            "date_day_match_group from patterns");
 109                                 db.execSQL("insert into template_accounts(id, template_id, acc, " +
 110                                            "position, acc_match_group, currency, " +
 111                                            "currency_match_group, amount, amount_match_group, " +
 112                                            "amount, amount_match_group, comment, " +
 113                                            "comment_match_group, negate_amount) select id, " +
 114                                            "pattern_id, acc, position, acc_match_group, " +
 115                                            "currency, " +
 116                                            "currency_match_group, amount, amount_match_group, " +
 117                                            "amount, amount_match_group, comment, " +
 118                                            "comment_match_group, negate_amount from " +
 119                                            "pattern_accounts");
 120                                 db.execSQL("create index fk_template_accounts_template on " +
 121                                            "template_accounts(template_id)");
 122                                 db.execSQL("create index fk_template_accounts_currency on " +
 123                                            "template_accounts(currency)");
 124                                 db.execSQL("drop table pattern_accounts");
 125                                 db.execSQL("drop table patterns");
 126                             }
 127                         }, new Migration(54, 55) {
 128                             @Override
 129                             public void migrate(@NonNull SupportSQLiteDatabase db) {
 130                                 db.execSQL(
 131                                         "CREATE TABLE template_accounts_new (id INTEGER PRIMARY " +
 132                                         "KEY " +
 133                                         "AUTOINCREMENT NOT NULL, template_id INTEGER NOT NULL, " +
 134                                         "acc TEXT, position INTEGER NOT NULL, acc_match_group " +
 135                                         "INTEGER, currency INTEGER, currency_match_group INTEGER," +
 136                                         " amount REAL, amount_match_group INTEGER, comment TEXT, " +
 137                                         "comment_match_group INTEGER, negate_amount INTEGER, " +
 138                                         "FOREIGN KEY(template_id) REFERENCES templates(id) ON " +
 139                                         "UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY" +
 140                                         "(currency) REFERENCES currencies(id) ON UPDATE RESTRICT" +
 141                                         " ON DELETE RESTRICT)");
 142                                 db.execSQL(
 143                                         "insert into template_accounts_new(id, template_id, acc, " +
 144                                         "position, acc_match_group, currency, " +
 145                                         "currency_match_group, amount, amount_match_group, " +
 146                                         "amount, amount_match_group, comment, " +
 147                                         "comment_match_group, negate_amount) select id, " +
 148                                         "template_id, acc, position, acc_match_group, " +
 149                                         "currency, " +
 150                                         "currency_match_group, amount, amount_match_group, " +
 151                                         "amount, amount_match_group, comment, " +
 152                                         "comment_match_group, negate_amount from " +
 153                                         "template_accounts");
 154                                 db.execSQL("drop table template_accounts");
 155                                 db.execSQL("alter table template_accounts_new rename to " +
 156                                            "template_accounts");
 157                                 db.execSQL("create index fk_template_accounts_template on " +
 158                                            "template_accounts(template_id)");
 159                                 db.execSQL("create index fk_template_accounts_currency on " +
 160                                            "template_accounts(currency)");
 161                             }
 162                         }
 163                         })
 164                         .addCallback(new Callback() {
 165                             @Override
 166                             public void onOpen(@NonNull SupportSQLiteDatabase db) {
 167                                 super.onOpen(db);
 168                                 db.execSQL("PRAGMA foreign_keys = ON");
 169                             }
 170                         })
 171                         .build();
 172         }
 173     }
 174     public abstract TemplateHeaderDAO getTemplateDAO();
 175
 176     public abstract TemplateAccountDAO getTemplateAccountDAO();
 177
 178     public abstract CurrencyDAO getCurrencyDAO();
 179
 180     public abstract AccountDAO getAccountDAO();
 181 }