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