X-Git-Url: https://git.ktnx.net/?p=mobile-ledger.git;a=blobdiff_plain;f=app%2Fsrc%2Fmain%2Fjava%2Fnet%2Fktnx%2Fmobileledger%2Fdb%2FDB.java;h=482628888b1b021c9b78d53eb159ab974c479d2e;hp=136f947d5c115d062a6a2d587510ba17765e742e;hb=833544eb24cb630dc1ce221e4aa3dedb3f6341e3;hpb=60e72ad85b09f4e6954fa794249400902439d42c 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 136f947d..48262888 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java @@ -17,7 +17,12 @@ package net.ktnx.mobileledger.db; +import android.content.res.Resources; +import android.database.Cursor; +import android.database.SQLException; + import androidx.annotation.NonNull; +import androidx.lifecycle.MutableLiveData; import androidx.room.Database; import androidx.room.Room; import androidx.room.RoomDatabase; @@ -25,13 +30,39 @@ import androidx.room.migration.Migration; import androidx.sqlite.db.SupportSQLiteDatabase; import net.ktnx.mobileledger.App; +import net.ktnx.mobileledger.dao.AccountDAO; +import net.ktnx.mobileledger.dao.AccountValueDAO; import net.ktnx.mobileledger.dao.CurrencyDAO; +import net.ktnx.mobileledger.dao.OptionDAO; +import net.ktnx.mobileledger.dao.ProfileDAO; import net.ktnx.mobileledger.dao.TemplateAccountDAO; import net.ktnx.mobileledger.dao.TemplateHeaderDAO; -import net.ktnx.mobileledger.utils.MobileLedgerDatabase; +import net.ktnx.mobileledger.dao.TransactionAccountDAO; +import net.ktnx.mobileledger.dao.TransactionDAO; +import net.ktnx.mobileledger.utils.Logger; + +import org.jetbrains.annotations.NotNull; + +import java.io.BufferedReader; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.util.Locale; +import java.util.UUID; +import java.util.regex.Matcher; +import java.util.regex.Pattern; -@Database(version = 55, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class}) +import static net.ktnx.mobileledger.utils.Logger.debug; + +@Database(version = DB.REVISION, + entities = {TemplateHeader.class, TemplateAccount.class, Currency.class, Account.class, + Profile.class, Option.class, AccountValue.class, Transaction.class, + TransactionAccount.class + }) abstract public class DB extends RoomDatabase { + public static final int REVISION = 65; + public static final String DB_NAME = "MoLe.db"; + public static final MutableLiveData initComplete = new MutableLiveData<>(false); private static DB instance; public static DB get() { if (instance != null) @@ -40,135 +71,190 @@ abstract public class DB extends RoomDatabase { if (instance != null) return instance; - 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(); + RoomDatabase.Builder builder = + Room.databaseBuilder(App.instance, DB.class, DB_NAME); + builder.addMigrations( + new Migration[]{singleVersionMigration(17), singleVersionMigration(18), + singleVersionMigration(19), singleVersionMigration(20), + multiVersionMigration(20, 22), multiVersionMigration(22, 30), + multiVersionMigration(30, 32), multiVersionMigration(32, 34), + multiVersionMigration(34, 40), singleVersionMigration(41), + multiVersionMigration(41, 58), singleVersionMigration(59), + singleVersionMigration(60), singleVersionMigration(61), + singleVersionMigration(62), singleVersionMigration(63), + singleVersionMigration(64), new Migration(64, 65) { + @Override + public void migrate(@NonNull @NotNull SupportSQLiteDatabase database) { + try (Cursor c = database.query( + "SELECT id, description FROM transactions")) + { + while (c.moveToNext()) { + final long id = c.getLong(0); + final String description = c.getString(1); + database.execSQL( + "UPDATE transactions SET description_uc=? WHERE id=?", + new Object[]{description.toUpperCase(), id + }); } } - }, new Migration(52, 53) { - @Override - public void migrate(@NonNull SupportSQLiteDatabase db) { - db.execSQL( - "alter table pattern_accounts add negate_amount boolean;"); - } - }, new Migration(53, 54) { - @Override - public void migrate(@NonNull SupportSQLiteDatabase db) { - db.execSQL("CREATE TABLE templates (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)"); - db.execSQL( - "CREATE TABLE template_accounts (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 NO ACTION ON DELETE NO ACTION , FOREIGN KEY" + - "(currency) REFERENCES currencies(id) ON UPDATE NO ACTION" + - " ON DELETE NO ACTION )"); - db.execSQL("insert into templates(id, name, regular_expression, " + - "test_text, transaction_description, " + - "transaction_description_match_group, " + - "transaction_comment, transaction_comment_match_group," + - " date_year, date_year_match_group, date_month, " + - "date_month_match_group, date_day, " + - "date_day_match_group)" + - " select id, name, regular_expression, test_text, " + - "transaction_description, " + - "transaction_description_match_group, " + - "transaction_comment, transaction_comment_match_group," + - " date_year, date_year_match_group, date_month, " + - "date_month_match_group, date_day, " + - "date_day_match_group from patterns"); - db.execSQL("insert into template_accounts(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, " + - "pattern_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 " + - "pattern_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)"); - 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() { - @Override - public void onOpen(@NonNull SupportSQLiteDatabase db) { - super.onOpen(db); - db.execSQL("PRAGMA foreign_keys = ON"); + } + }) + .addCallback(new Callback() { + @Override + public void onOpen(@NonNull SupportSQLiteDatabase db) { + super.onOpen(db); + db.execSQL("PRAGMA foreign_keys = ON"); + db.execSQL("pragma case_sensitive_like" + "=ON;"); + + } + }); + +// if (BuildConfig.DEBUG) +// builder.setQueryCallback(((sqlQuery, bindArgs) -> Logger.debug("room", sqlQuery)), +// Executors.newSingleThreadExecutor()); + + return instance = builder.build(); + } + } + private static Migration singleVersionMigration(int toVersion) { + return new Migration(toVersion - 1, toVersion) { + @Override + public void migrate(@NonNull SupportSQLiteDatabase db) { + String fileName = String.format(Locale.US, "db_%d", toVersion); + + applyRevisionFile(db, fileName); + + // when migrating to version 59, migrate profile/theme options to the + // SharedPreferences + if (toVersion == 59) { + try (Cursor c = db.query( + "SELECT p.id, p.theme FROM profiles p WHERE p.id=(SELECT o.value " + + "FROM options o WHERE o.profile_id=0 AND o.name=?)", + new Object[]{"profile_id"})) + { + if (c.moveToFirst()) { + long currentProfileId = c.getLong(0); + int currentTheme = c.getInt(1); + + if (currentProfileId >= 0 && currentTheme >= 0) { + App.storeStartupProfileAndTheme(currentProfileId, currentTheme); } - }) - .build(); + } + } + } + if (toVersion == 63) { + try (Cursor c = db.query("SELECT id FROM templates")) { + while (c.moveToNext()) { + db.execSQL("UPDATE templates SET uuid=? WHERE id=?", + new Object[]{UUID.randomUUID().toString(), c.getLong(0)}); + } + } + } + } + }; + } + private static Migration dummyVersionMigration(int toVersion) { + return new Migration(toVersion - 1, toVersion) { + @Override + public void migrate(@NonNull SupportSQLiteDatabase db) { + Logger.debug("db", + String.format(Locale.ROOT, "Dummy DB migration to version %d", toVersion)); + } + }; + } + private static Migration multiVersionMigration(int fromVersion, int toVersion) { + return new Migration(fromVersion, toVersion) { + @Override + public void migrate(@NonNull SupportSQLiteDatabase db) { + String fileName = String.format(Locale.US, "db_%d_%d", fromVersion, toVersion); + + applyRevisionFile(db, fileName); + } + }; + } + public static void applyRevisionFile(@NonNull SupportSQLiteDatabase db, String fileName) { + final Resources rm = App.instance.getResources(); + int res_id = rm.getIdentifier(fileName, "raw", App.instance.getPackageName()); + if (res_id == 0) + throw new SQLException(String.format(Locale.US, "No resource for %s", fileName)); + + try (InputStream res = rm.openRawResource(res_id)) { + debug("db", "Applying " + fileName); + InputStreamReader isr = new InputStreamReader(res); + BufferedReader reader = new BufferedReader(isr); + + Pattern endOfStatement = Pattern.compile(";\\s*(?:--.*)?$"); + + String line; + String sqlStatement = null; + int lineNo = 0; + while ((line = reader.readLine()) != null) { + lineNo++; + if (line.startsWith("--")) + continue; + if (line.isEmpty()) + continue; + + if (sqlStatement == null) + sqlStatement = line; + else + sqlStatement = sqlStatement.concat(" " + line); + + Matcher m = endOfStatement.matcher(line); + if (!m.find()) + continue; + + try { + db.execSQL(sqlStatement); + sqlStatement = null; + } + catch (Exception e) { + throw new RuntimeException( + String.format("Error applying %s, line %d, statement: %s", fileName, + lineNo, sqlStatement), e); + } + } + + if (sqlStatement != null) + throw new RuntimeException(String.format( + "Error applying %s: EOF after continuation. Line %s, Incomplete " + + "statement: %s", fileName, lineNo, sqlStatement)); + + } + catch (IOException e) { + throw new RuntimeException(String.format("Error opening raw resource for %s", fileName), + e); } } public abstract TemplateHeaderDAO getTemplateDAO(); - public abstract TemplateAccountDAO getPatternAccountDAO(); + public abstract TemplateAccountDAO getTemplateAccountDAO(); public abstract CurrencyDAO getCurrencyDAO(); + + public abstract AccountDAO getAccountDAO(); + + public abstract AccountValueDAO getAccountValueDAO(); + + public abstract TransactionDAO getTransactionDAO(); + + public abstract TransactionAccountDAO getTransactionAccountDAO(); + + public abstract OptionDAO getOptionDAO(); + + public abstract ProfileDAO getProfileDAO(); + + @androidx.room.Transaction + public void deleteAllSync() { + getTransactionAccountDAO().deleteAllSync(); + getTransactionDAO().deleteAllSync(); + getAccountValueDAO().deleteAllSync(); + getAccountDAO().deleteAllSync(); + getTemplateAccountDAO().deleteAllSync(); + getTemplateDAO().deleteAllSync(); + getCurrencyDAO().deleteAllSync(); + getOptionDAO().deleteAllSync(); + getProfileDAO().deleteAllSync(); + } }