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
  37           })
  38 abstract public class DB extends RoomDatabase {
  39     private static DB instance;
  40     public static DB get() {
  41         if (instance != null)
  42             return instance;
  43         synchronized (DB.class) {
  44             if (instance != null)
  45                 return instance;
  46
  47             return instance =
  48                     Room.databaseBuilder(App.instance, DB.class, MobileLedgerDatabase.DB_NAME)
  49                         .addMigrations(new Migration[]{new Migration(51, 52) {
  50                             @Override
  51                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  52                                 db.beginTransaction();
  53                                 try {
  54                                     db.execSQL("create index fk_pattern_accounts_pattern on " +
  55                                                "pattern_accounts(pattern_id);");
  56                                     db.execSQL("create index fk_pattern_accounts_currency on " +
  57                                                "pattern_accounts(currency);");
  58                                     db.setTransactionSuccessful();
  59                                 }
  60                                 finally {
  61                                     db.endTransaction();
  62                                 }
  63                             }
  64                         }, new Migration(52, 53) {
  65                             @Override
  66                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  67                                 db.execSQL(
  68                                         "alter table pattern_accounts add negate_amount boolean;");
  69                             }
  70                         }, new Migration(53, 54) {
  71                             @Override
  72                             public void migrate(@NonNull SupportSQLiteDatabase db) {
  73                                 db.execSQL("CREATE TABLE templates (id INTEGER PRIMARY KEY " +
  74                                            "AUTOINCREMENT NOT NULL, name TEXT NOT NULL, " +
  75                                            "regular_expression TEXT NOT NULL, test_text TEXT, " +
  76                                            "transaction_description TEXT, " +
  77                                            "transaction_description_match_group INTEGER, " +
  78                                            "transaction_comment TEXT, " +
  79                                            "transaction_comment_match_group INTEGER, date_year " +
  80                                            "INTEGER, date_year_match_group INTEGER, date_month " +
  81                                            "INTEGER, date_month_match_group INTEGER, date_day " +
  82                                            "INTEGER, date_day_match_group INTEGER)");
  83                                 db.execSQL(
  84                                         "CREATE TABLE template_accounts (id INTEGER PRIMARY KEY " +
  85                                         "AUTOINCREMENT NOT NULL, template_id INTEGER NOT NULL, " +
  86                                         "acc TEXT, position INTEGER NOT NULL, acc_match_group " +
  87                                         "INTEGER, currency INTEGER, currency_match_group INTEGER," +
  88                                         " amount REAL, amount_match_group INTEGER, comment TEXT, " +
  89                                         "comment_match_group INTEGER, negate_amount INTEGER, " +
  90                                         "FOREIGN KEY(template_id) REFERENCES templates(id) ON " +
  91                                         "UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY" +
  92                                         "(currency) REFERENCES currencies(id) ON UPDATE NO ACTION" +
  93                                         " ON DELETE NO ACTION )");
  94                                 db.execSQL("insert into templates(id, name, regular_expression, " +
  95                                            "test_text, transaction_description, " +
  96                                            "transaction_description_match_group, " +
  97                                            "transaction_comment, transaction_comment_match_group," +
  98                                            " date_year, date_year_match_group, date_month, " +
  99                                            "date_month_match_group, date_day, " +
 100                                            "date_day_match_group)" +
 101                                            " select id, name, regular_expression, test_text, " +
 102                                            "transaction_description, " +
 103                                            "transaction_description_match_group, " +
 104                                            "transaction_comment, transaction_comment_match_group," +
 105                                            " date_year, date_year_match_group, date_month, " +
 106                                            "date_month_match_group, date_day, " +
 107                                            "date_day_match_group from patterns");
 108                                 db.execSQL("insert into template_accounts(id, template_id, acc, " +
 109                                            "position, acc_match_group, currency, " +
 110                                            "currency_match_group, amount, amount_match_group, " +
 111                                            "amount, amount_match_group, comment, " +
 112                                            "comment_match_group, negate_amount) select id, " +
 113                                            "pattern_id, acc, position, acc_match_group, " +
 114                                            "currency, " +
 115                                            "currency_match_group, amount, amount_match_group, " +
 116                                            "amount, amount_match_group, comment, " +
 117                                            "comment_match_group, negate_amount from " +
 118                                            "pattern_accounts");
 119                                 db.execSQL("create index fk_template_accounts_template on " +
 120                                            "template_accounts(template_id)");
 121                                 db.execSQL("create index fk_template_accounts_currency on " +
 122                                            "template_accounts(currency)");
 123                                 db.execSQL("drop table pattern_accounts");
 124                                 db.execSQL("drop table patterns");
 125                             }
 126                         }, new Migration(54, 55) {
 127                             @Override
 128                             public void migrate(@NonNull SupportSQLiteDatabase db) {
 129                                 db.execSQL(
 130                                         "CREATE TABLE template_accounts_new (id INTEGER PRIMARY " +
 131                                         "KEY " +
 132                                         "AUTOINCREMENT NOT NULL, template_id INTEGER NOT NULL, " +
 133                                         "acc TEXT, position INTEGER NOT NULL, acc_match_group " +
 134                                         "INTEGER, currency INTEGER, currency_match_group INTEGER," +
 135                                         " amount REAL, amount_match_group INTEGER, comment TEXT, " +
 136                                         "comment_match_group INTEGER, negate_amount INTEGER, " +
 137                                         "FOREIGN KEY(template_id) REFERENCES templates(id) ON " +
 138                                         "UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY" +
 139                                         "(currency) REFERENCES currencies(id) ON UPDATE RESTRICT" +
 140                                         " ON DELETE RESTRICT)");
 141                                 db.execSQL(
 142                                         "insert into template_accounts_new(id, template_id, acc, " +
 143                                         "position, acc_match_group, currency, " +
 144                                         "currency_match_group, amount, amount_match_group, " +
 145                                         "amount, amount_match_group, comment, " +
 146                                         "comment_match_group, negate_amount) select id, " +
 147                                         "template_id, acc, position, acc_match_group, " +
 148                                         "currency, " +
 149                                         "currency_match_group, amount, amount_match_group, " +
 150                                         "amount, amount_match_group, comment, " +
 151                                         "comment_match_group, negate_amount from " +
 152                                         "template_accounts");
 153                                 db.execSQL("drop table template_accounts");
 154                                 db.execSQL("alter table template_accounts_new rename to " +
 155                                            "template_accounts");
 156                                 db.execSQL("create index fk_template_accounts_template on " +
 157                                            "template_accounts(template_id)");
 158                                 db.execSQL("create index fk_template_accounts_currency on " +
 159                                            "template_accounts(currency)");
 160                             }
 161                         }
 162                         })
 163                         .addCallback(new Callback() {
 164                             @Override
 165                             public void onOpen(@NonNull SupportSQLiteDatabase db) {
 166                                 super.onOpen(db);
 167                                 db.execSQL("PRAGMA foreign_keys = ON");
 168                             }
 169                         })
 170                         .build();
 171         }
 172     }
 173     public abstract TemplateHeaderDAO getTemplateDAO();
 174
 175     public abstract TemplateAccountDAO getTemplateAccountDAO();
 176
 177     public abstract CurrencyDAO getCurrencyDAO();
 178
 179     public abstract AccountDAO getAccountDAO();
 180 }