From 9ea5a330029c99e0eecf55aaa94d8689fa64fc92 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Sat, 20 Feb 2021 08:49:45 +0200 Subject: [PATCH] Room takes over DB migrations --- .../net.ktnx.mobileledger.db.DB/58.json | 16 +- .../main/java/net/ktnx/mobileledger/App.java | 7 +- .../java/net/ktnx/mobileledger/db/DB.java | 239 +++++++++--------- .../mobileledger/db/TransactionAccount.java | 3 +- .../utils/MobileLedgerDatabase.java | 107 +------- .../main/res/raw/{sql_17.sql => db_17.sql} | 0 .../main/res/raw/{sql_18.sql => db_18.sql} | 0 .../main/res/raw/{sql_19.sql => db_19.sql} | 0 .../main/res/raw/{sql_20.sql => db_20.sql} | 0 .../main/res/raw/{sql_21.sql => db_20_22.sql} | 7 +- .../main/res/raw/{sql_25.sql => db_22_30.sql} | 23 +- .../main/res/raw/{sql_31.sql => db_30_32.sql} | 8 +- .../main/res/raw/{sql_34.sql => db_32_34.sql} | 26 +- app/src/main/res/raw/db_34_40.sql | 70 +++++ .../main/res/raw/{sql_41.sql => db_41.sql} | 0 app/src/main/res/raw/db_41_58.sql | 207 +++++++++++++++ app/src/main/res/raw/sql_0.sql | 26 -- app/src/main/res/raw/sql_1.sql | 21 -- app/src/main/res/raw/sql_10.sql | 21 -- app/src/main/res/raw/sql_11.sql | 21 -- app/src/main/res/raw/sql_12.sql | 20 -- app/src/main/res/raw/sql_13.sql | 41 --- app/src/main/res/raw/sql_14.sql | 27 -- app/src/main/res/raw/sql_15.sql | 29 --- app/src/main/res/raw/sql_16.sql | 20 -- app/src/main/res/raw/sql_2.sql | 20 -- app/src/main/res/raw/sql_22.sql | 20 -- app/src/main/res/raw/sql_23.sql | 20 -- app/src/main/res/raw/sql_24.sql | 20 -- app/src/main/res/raw/sql_26.sql | 20 -- app/src/main/res/raw/sql_27.sql | 20 -- app/src/main/res/raw/sql_28.sql | 20 -- app/src/main/res/raw/sql_29.sql | 20 -- app/src/main/res/raw/sql_3.sql | 23 -- app/src/main/res/raw/sql_30.sql | 20 -- app/src/main/res/raw/sql_32.sql | 20 -- app/src/main/res/raw/sql_33.sql | 20 -- app/src/main/res/raw/sql_35.sql | 23 -- app/src/main/res/raw/sql_36.sql | 23 -- app/src/main/res/raw/sql_37.sql | 22 -- app/src/main/res/raw/sql_38.sql | 24 -- app/src/main/res/raw/sql_39.sql | 24 -- app/src/main/res/raw/sql_4.sql | 21 -- app/src/main/res/raw/sql_40.sql | 21 -- app/src/main/res/raw/sql_42.sql | 23 -- app/src/main/res/raw/sql_43.sql | 20 -- app/src/main/res/raw/sql_44.sql | 24 -- app/src/main/res/raw/sql_45.sql | 26 -- app/src/main/res/raw/sql_46.sql | 26 -- app/src/main/res/raw/sql_47.sql | 26 -- app/src/main/res/raw/sql_48.sql | 23 -- app/src/main/res/raw/sql_49.sql | 27 -- app/src/main/res/raw/sql_5.sql | 21 -- app/src/main/res/raw/sql_50.sql | 20 -- app/src/main/res/raw/sql_51.sql | 31 --- app/src/main/res/raw/sql_52.sql | 21 -- app/src/main/res/raw/sql_53.sql | 20 -- app/src/main/res/raw/sql_54.sql | 29 --- app/src/main/res/raw/sql_55.sql | 27 -- app/src/main/res/raw/sql_56.sql | 34 --- app/src/main/res/raw/sql_57.sql | 23 -- app/src/main/res/raw/sql_58.sql | 150 ----------- app/src/main/res/raw/sql_6.sql | 26 -- app/src/main/res/raw/sql_7.sql | 21 -- app/src/main/res/raw/sql_8.sql | 21 -- app/src/main/res/raw/sql_9.sql | 28 -- 66 files changed, 469 insertions(+), 1538 deletions(-) rename app/src/main/res/raw/{sql_17.sql => db_17.sql} (100%) rename app/src/main/res/raw/{sql_18.sql => db_18.sql} (100%) rename app/src/main/res/raw/{sql_19.sql => db_19.sql} (100%) rename app/src/main/res/raw/{sql_20.sql => db_20.sql} (100%) rename app/src/main/res/raw/{sql_21.sql => db_20_22.sql} (83%) rename app/src/main/res/raw/{sql_25.sql => db_22_30.sql} (60%) rename app/src/main/res/raw/{sql_31.sql => db_30_32.sql} (83%) rename app/src/main/res/raw/{sql_34.sql => db_32_34.sql} (70%) create mode 100644 app/src/main/res/raw/db_34_40.sql rename app/src/main/res/raw/{sql_41.sql => db_41.sql} (100%) create mode 100644 app/src/main/res/raw/db_41_58.sql delete mode 100644 app/src/main/res/raw/sql_0.sql delete mode 100644 app/src/main/res/raw/sql_1.sql delete mode 100644 app/src/main/res/raw/sql_10.sql delete mode 100644 app/src/main/res/raw/sql_11.sql delete mode 100644 app/src/main/res/raw/sql_12.sql delete mode 100644 app/src/main/res/raw/sql_13.sql delete mode 100644 app/src/main/res/raw/sql_14.sql delete mode 100644 app/src/main/res/raw/sql_15.sql delete mode 100644 app/src/main/res/raw/sql_16.sql delete mode 100644 app/src/main/res/raw/sql_2.sql delete mode 100644 app/src/main/res/raw/sql_22.sql delete mode 100644 app/src/main/res/raw/sql_23.sql delete mode 100644 app/src/main/res/raw/sql_24.sql delete mode 100644 app/src/main/res/raw/sql_26.sql delete mode 100644 app/src/main/res/raw/sql_27.sql delete mode 100644 app/src/main/res/raw/sql_28.sql delete mode 100644 app/src/main/res/raw/sql_29.sql delete mode 100644 app/src/main/res/raw/sql_3.sql delete mode 100644 app/src/main/res/raw/sql_30.sql delete mode 100644 app/src/main/res/raw/sql_32.sql delete mode 100644 app/src/main/res/raw/sql_33.sql delete mode 100644 app/src/main/res/raw/sql_35.sql delete mode 100644 app/src/main/res/raw/sql_36.sql delete mode 100644 app/src/main/res/raw/sql_37.sql delete mode 100644 app/src/main/res/raw/sql_38.sql delete mode 100644 app/src/main/res/raw/sql_39.sql delete mode 100644 app/src/main/res/raw/sql_4.sql delete mode 100644 app/src/main/res/raw/sql_40.sql delete mode 100644 app/src/main/res/raw/sql_42.sql delete mode 100644 app/src/main/res/raw/sql_43.sql delete mode 100644 app/src/main/res/raw/sql_44.sql delete mode 100644 app/src/main/res/raw/sql_45.sql delete mode 100644 app/src/main/res/raw/sql_46.sql delete mode 100644 app/src/main/res/raw/sql_47.sql delete mode 100644 app/src/main/res/raw/sql_48.sql delete mode 100644 app/src/main/res/raw/sql_49.sql delete mode 100644 app/src/main/res/raw/sql_5.sql delete mode 100644 app/src/main/res/raw/sql_50.sql delete mode 100644 app/src/main/res/raw/sql_51.sql delete mode 100644 app/src/main/res/raw/sql_52.sql delete mode 100644 app/src/main/res/raw/sql_53.sql delete mode 100644 app/src/main/res/raw/sql_54.sql delete mode 100644 app/src/main/res/raw/sql_55.sql delete mode 100644 app/src/main/res/raw/sql_56.sql delete mode 100644 app/src/main/res/raw/sql_57.sql delete mode 100644 app/src/main/res/raw/sql_58.sql delete mode 100644 app/src/main/res/raw/sql_6.sql delete mode 100644 app/src/main/res/raw/sql_7.sql delete mode 100644 app/src/main/res/raw/sql_8.sql delete mode 100644 app/src/main/res/raw/sql_9.sql diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/58.json b/app/schemas/net.ktnx.mobileledger.db.DB/58.json index fd3239de..c4dea2e7 100644 --- a/app/schemas/net.ktnx.mobileledger.db.DB/58.json +++ b/app/schemas/net.ktnx.mobileledger.db.DB/58.json @@ -2,7 +2,7 @@ "formatVersion": 1, "database": { "version": 58, - "identityHash": "249d4507f837a17defe32f1bc9dc2de6", + "identityHash": "0f584c8b143be77895cc315ffbc41f3e", "entities": [ { "tableName": "templates", @@ -726,7 +726,17 @@ ], "autoGenerate": false }, - "indices": [], + "indices": [ + { + "name": "fk_tran_acc_prof_acc", + "unique": false, + "columnNames": [ + "profile", + "account_name" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_tran_acc_prof_acc` ON `${TABLE_NAME}` (`profile`, `account_name`)" + } + ], "foreignKeys": [ { "table": "transactions", @@ -760,7 +770,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, '249d4507f837a17defe32f1bc9dc2de6')" + "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '0f584c8b143be77895cc315ffbc41f3e')" ] } } \ No newline at end of file diff --git a/app/src/main/java/net/ktnx/mobileledger/App.java b/app/src/main/java/net/ktnx/mobileledger/App.java index a492e8a4..2fc6d65c 100644 --- a/app/src/main/java/net/ktnx/mobileledger/App.java +++ b/app/src/main/java/net/ktnx/mobileledger/App.java @@ -1,5 +1,5 @@ /* - * Copyright © 2020 Damyan Ivanov. + * 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 @@ -23,6 +23,7 @@ import android.content.res.Resources; import android.database.sqlite.SQLiteDatabase; import android.util.Log; +import net.ktnx.mobileledger.db.DB; import net.ktnx.mobileledger.model.Data; import net.ktnx.mobileledger.ui.profiles.ProfileDetailModel; import net.ktnx.mobileledger.utils.Globals; @@ -143,6 +144,10 @@ public class App extends Application { if (dbHelper != null) return; + // Let Room do any possible migrations + // this method may be removed when all DB access is made via Room + DB.get() + .compileStatement("select count(*) from profiles"); dbHelper = new MobileLedgerDatabase(this); } } 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 e5cfb5d4..173d730e 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java @@ -17,6 +17,9 @@ package net.ktnx.mobileledger.db; +import android.content.res.Resources; +import android.database.SQLException; + import androidx.annotation.NonNull; import androidx.room.Database; import androidx.room.Room; @@ -29,14 +32,25 @@ import net.ktnx.mobileledger.dao.AccountDAO; import net.ktnx.mobileledger.dao.CurrencyDAO; import net.ktnx.mobileledger.dao.TemplateAccountDAO; import net.ktnx.mobileledger.dao.TemplateHeaderDAO; -import net.ktnx.mobileledger.utils.MobileLedgerDatabase; -@Database(version = 58, +import java.io.BufferedReader; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.util.Locale; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +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, DescriptionHistory.class, Transaction.class, TransactionAccount.class }) abstract public class DB extends RoomDatabase { + public static final int REVISION = 58; + public static final String DB_NAME = "MoLe.db"; private static DB instance; public static DB get() { if (instance != null) @@ -45,130 +59,103 @@ 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(); - } - } - }, 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"); - } - }) - .build(); + return instance = Room.databaseBuilder(App.instance, DB.class, DB_NAME) + .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), + }) + .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;"); + + } + }) + .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); + } + }; + } + 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(); diff --git a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java index 671e5d01..6d51fec9 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java @@ -21,6 +21,7 @@ import androidx.annotation.NonNull; import androidx.room.ColumnInfo; import androidx.room.Entity; import androidx.room.ForeignKey; +import androidx.room.Index; @Entity(tableName = "transaction_accounts", primaryKeys = {"profile", "transaction_id", "order_no"}, foreignKeys = {@ForeignKey(entity = Transaction.class, parentColumns = {"profile", "id"}, @@ -29,7 +30,7 @@ import androidx.room.ForeignKey; @ForeignKey(entity = Account.class, parentColumns = {"profile", "name"}, childColumns = {"profile", "account_name"}, onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.RESTRICT) - }) + }, indices = {@Index(name = "fk_tran_acc_prof_acc", value = {"profile", "account_name"})}) public class TransactionAccount { @ColumnInfo @NonNull 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 78c325e1..911c2034 100644 --- a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java +++ b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java @@ -18,125 +18,38 @@ package net.ktnx.mobileledger.utils; import android.app.Application; -import android.content.res.Resources; -import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import androidx.lifecycle.MutableLiveData; -import net.ktnx.mobileledger.BuildConfig; import net.ktnx.mobileledger.db.DB; -import java.io.BufferedReader; -import java.io.IOException; -import java.io.InputStream; -import java.io.InputStreamReader; -import java.util.Locale; -import java.util.regex.Matcher; -import java.util.regex.Pattern; - 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 = 58; - private static final String CREATE_DB_SQL = "create_db"; - private final Application mContext; - @Override - public void onOpen(SQLiteDatabase db) { - super.onOpen(db); - // force a check by Room to ensure everything is OK - // TODO: remove when all DB structure manipulation is via Room - DB.get() - .compileStatement("SELECT COUNT(*) FROM profiles"); - } public MobileLedgerDatabase(Application context) { - super(context, DB_NAME, null, LATEST_REVISION); + super(context, DB.DB_NAME, null, DB.REVISION); debug("db", "creating helper instance"); - mContext = context; super.setWriteAheadLoggingEnabled(true); } @Override public void onCreate(SQLiteDatabase db) { - debug("db", "onCreate called"); - applyRevisionFile(db, CREATE_DB_SQL); - } - - @Override - public void onConfigure(SQLiteDatabase db) { - super.onConfigure(db); - db.execSQL("pragma case_sensitive_like=ON;"); - if (BuildConfig.DEBUG) - db.execSQL("PRAGMA foreign_keys=ON"); + throw new IllegalStateException("Should not happen. Where's Room!?"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { - debug("db", - String.format(Locale.US, "needs upgrade from version %d to version %d", oldVersion, - newVersion)); - for (int i = oldVersion + 1; i <= newVersion; i++) - applyRevision(db, i); - } - private void applyRevision(SQLiteDatabase db, int rev_no) { - String rev_file = String.format(Locale.US, "sql_%d", rev_no); - - applyRevisionFile(db, rev_file); + throw new IllegalStateException("Should not happen. Where's Room!?"); } - private void applyRevisionFile(SQLiteDatabase db, String revFile) { - final Resources rm = mContext.getResources(); - int res_id = rm.getIdentifier(revFile, "raw", mContext.getPackageName()); - if (res_id == 0) - throw new SQLException(String.format(Locale.US, "No resource for %s", revFile)); - try (InputStream res = rm.openRawResource(res_id)) { - debug("db", "Applying " + revFile); - 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", revFile, - lineNo, sqlStatement), e); - } - } - - if (sqlStatement != null) - throw new RuntimeException(String.format( - "Error applying %s: EOF after continuation. Line %s, Incomplete " + - "statement: %s", revFile, lineNo, sqlStatement)); - - } - catch (IOException e) { - throw new RuntimeException(String.format("Error opening raw resource for %s", revFile), - e); - } + @Override + public void onConfigure(SQLiteDatabase db) { + super.onConfigure(db); + // force a check by Room to ensure everything is OK + // TODO: remove when all DB access is via Room + DB.get() + .compileStatement("SELECT COUNT(*) FROM profiles"); } } diff --git a/app/src/main/res/raw/sql_17.sql b/app/src/main/res/raw/db_17.sql similarity index 100% rename from app/src/main/res/raw/sql_17.sql rename to app/src/main/res/raw/db_17.sql diff --git a/app/src/main/res/raw/sql_18.sql b/app/src/main/res/raw/db_18.sql similarity index 100% rename from app/src/main/res/raw/sql_18.sql rename to app/src/main/res/raw/db_18.sql diff --git a/app/src/main/res/raw/sql_19.sql b/app/src/main/res/raw/db_19.sql similarity index 100% rename from app/src/main/res/raw/sql_19.sql rename to app/src/main/res/raw/db_19.sql diff --git a/app/src/main/res/raw/sql_20.sql b/app/src/main/res/raw/db_20.sql similarity index 100% rename from app/src/main/res/raw/sql_20.sql rename to app/src/main/res/raw/db_20.sql diff --git a/app/src/main/res/raw/sql_21.sql b/app/src/main/res/raw/db_20_22.sql similarity index 83% rename from app/src/main/res/raw/sql_21.sql rename to app/src/main/res/raw/db_20_22.sql index d7c08fdd..72301de4 100644 --- a/app/src/main/res/raw/sql_21.sql +++ b/app/src/main/res/raw/db_20_22.sql @@ -1,4 +1,4 @@ --- Copyright © 2019 Damyan Ivanov. +-- 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 @@ -13,8 +13,7 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . -BEGIN TRANSACTION; +-- migrate from revision 20 to revision 22 alter table accounts add amounts_expanded boolean default 0; - -COMMIT TRANSACTION; \ No newline at end of file +alter table profiles add preferred_accounts_filter varchar; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_25.sql b/app/src/main/res/raw/db_22_30.sql similarity index 60% rename from app/src/main/res/raw/sql_25.sql rename to app/src/main/res/raw/db_22_30.sql index 6a02fcbe..e1760cc2 100644 --- a/app/src/main/res/raw/sql_25.sql +++ b/app/src/main/res/raw/db_22_30.sql @@ -1,4 +1,4 @@ --- Copyright © 2020 Damyan Ivanov. +-- 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 @@ -13,8 +13,25 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . -BEGIN TRANSACTION; +-- migrate from revision 22 to revision 30 +-- 23, 24, 27, 28 +alter table profiles +add future_dates integer, +add api_version integer, +add show_commodity_by_default boolean default 0, +add default_commodity varchar; + +-- 25 create table currencies(id integer not null primary key, name varchar not null, position varchar not null, has_gap boolean not null); -COMMIT TRANSACTION; \ No newline at end of file +-- 26 +alter table transaction_accounts add comment varchar; + +-- 29 +create index idx_transaction_description on transactions(description); + +-- 30 +delete from options +where profile <> '-' + and not exists (select 1 from profiles p where p.uuid=options.profile); \ No newline at end of file diff --git a/app/src/main/res/raw/sql_31.sql b/app/src/main/res/raw/db_30_32.sql similarity index 83% rename from app/src/main/res/raw/sql_31.sql rename to app/src/main/res/raw/db_30_32.sql index d17eeea4..61ae734c 100644 --- a/app/src/main/res/raw/sql_31.sql +++ b/app/src/main/res/raw/db_30_32.sql @@ -1,4 +1,4 @@ --- Copyright © 2020 Damyan Ivanov. +-- 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 @@ -13,8 +13,10 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . -BEGIN TRANSACTION; +-- migrate from revision 30 to revision 32 +-- 31 alter table profiles add show_comments_by_default boolean default 0; -COMMIT TRANSACTION; \ No newline at end of file +-- 32 +update profiles set show_comments_by_default = 1; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_34.sql b/app/src/main/res/raw/db_32_34.sql similarity index 70% rename from app/src/main/res/raw/sql_34.sql rename to app/src/main/res/raw/db_32_34.sql index 041e3cf5..baa1ab14 100644 --- a/app/src/main/res/raw/sql_34.sql +++ b/app/src/main/res/raw/db_32_34.sql @@ -1,4 +1,4 @@ --- Copyright © 2020 Damyan Ivanov. +-- 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 @@ -13,8 +13,12 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . -BEGIN TRANSACTION; +-- migrate from revision 32 to revision 34 +-- 33 (merged below) +-- alter table transactions add comment varchar; + +-- 34 alter table transactions add year integer not null default 0; alter table transactions add month integer not null default 0; alter table transactions add day integer not null default 0; @@ -24,9 +28,19 @@ update transactions set tmp_md= substr(date, instr(date, '/')+1); update transactions set month=cast(substr(tmp_md,1,instr(tmp_md,'/')-1) as integer); update transactions set day= cast(substr(tmp_md, instr(tmp_md,'/')+1) as integer); -- alter table transactions drop date -create table transactions_2(profile varchar not null, id integer not null, data_hash varchar not null, year integer not null, month integer not null, day integer not null, description varchar not null, comment varchar, keep boolean not null default 0); -insert into transactions_2(profile, id, data_hash, year, month, day, description, comment, keep) select profile, id, data_hash, year, month, day, description, comment, keep from transactions; +create table transactions_2( + profile varchar not null, + id integer not null, + data_hash varchar not null, + year integer not null, + month integer not null, + day integer not null, + description varchar not null, + comment varchar, + keep boolean not null default 0); +insert into transactions_2(profile, id, data_hash, year, month, day, description, comment, keep) +select profile, id, data_hash, year, month, day, description, null, keep from transactions; + drop table transactions; -alter table transactions_2 rename to transactions; -COMMIT TRANSACTION; \ No newline at end of file +alter table transactions_2 rename to transactions; diff --git a/app/src/main/res/raw/db_34_40.sql b/app/src/main/res/raw/db_34_40.sql new file mode 100644 index 00000000..c3867490 --- /dev/null +++ b/app/src/main/res/raw/db_34_40.sql @@ -0,0 +1,70 @@ +-- 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 . + +-- migrate from revision 34 to revision 40 + +-- 35 +create table accounts_new( + profile varchar not null, + name varchar not null, + name_upper varchar not null, + keep boolean not null default 0, + level integer not null, + parent_name varchar, + expanded default 1, + amounts_expanded boolean default 0, + generation integer default 0); + +insert into accounts_new( + profile, name, name_upper, keep, level, parent_name, expanded, amounts_expanded) +select profile, name, name_upper, keep, level, parent_name, expanded, amounts_expanded +from accounts; + +drop table accounts; + +alter table accounts_new rename to accounts; + +-- 36 +-- merged in 35 --alter table accounts add generation integer default 0; + +alter table account_values add generation integer default 0; + +alter table transactions add generation integer default 0; + +alter table transaction_accounts +add generation integer default 0, +add order_no integer not null default 0; + +-- 37 +update transaction_accounts set order_no = rowid; + +-- 40 +delete from transaction_accounts where not exists (select 1 from accounts a where a.profile=transaction_accounts.profile and a.name=transaction_accounts.account_name); +delete from transaction_accounts where not exists (select 1 from transactions t where t.profile=transaction_accounts.profile and t.id=transaction_accounts.transaction_id); + +-- 38 +CREATE TABLE transaction_accounts_new(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, comment varchar, generation integer default 0, order_no integer not null default 0, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id)); +insert into transaction_accounts_new(profile, transaction_id, account_name, currency, amount, comment, generation, order_no) select profile, transaction_id, account_name, currency, amount, comment, generation, order_no from transaction_accounts; +drop table transaction_accounts; +alter table transaction_accounts_new rename to transaction_accounts; +create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); + +-- 39 +create table description_history_new(description varchar not null primary key, description_upper varchar, generation integer default 0); +insert into description_history_new(description, description_upper) select description, description_upper from description_history; +drop table description_history; +alter table description_history_new rename to description_history; +create unique index un_description_history on description_history(description_upper); + diff --git a/app/src/main/res/raw/sql_41.sql b/app/src/main/res/raw/db_41.sql similarity index 100% rename from app/src/main/res/raw/sql_41.sql rename to app/src/main/res/raw/db_41.sql diff --git a/app/src/main/res/raw/db_41_58.sql b/app/src/main/res/raw/db_41_58.sql new file mode 100644 index 00000000..0a4394a6 --- /dev/null +++ b/app/src/main/res/raw/db_41_58.sql @@ -0,0 +1,207 @@ +-- 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 . + +-- migrate from revision 41 to revision 58 + +-- profiles +create table profiles_new( + uuid text not null, + name text not null, + url text not null, + use_authentication integer not null, + auth_user text, + auth_password text, + order_no integer not null, + permit_posting integer not null default 0, + theme integer not null default -1, + preferred_accounts_filter varchar, + future_dates integer not null, + api_version integer not null, + show_commodity_by_default integer not null default 0, + default_commodity text, + show_comments_by_default integer not null default 1, + detected_version_pre_1_19 integer not null, + detected_version_major integer not null, + detected_version_minor integer not null, + primary key(uuid)); + +insert into profiles_new( + uuid, name, url, use_authentication, auth_user, auth_password, order_no, + permit_posting, theme, preferred_accounts_filter, future_dates, api_version, + show_commodity_by_default, default_commodity, show_comments_by_default, + detected_version_pre_1_19, detected_version_major, detected_version_minor) +select uuid, name, url, use_authentication, auth_user, auth_password, order_no, + permit_posting, theme, preferred_accounts_filter, future_dates, api_version, + show_commodity_by_default, default_commodity, show_comments_by_default, + detected_version_pre_1_19, detected_version_major, detected_version_minor +from profiles; + +-- options +create table options_new(profile varchar not null, name varchar not null, value varchar, primary key(profile, name)); + +insert into options_new(profile, name, value) +select profile, name, value from options; + +-- accounts +create table accounts_new( + profile varchar not null, + name varchar not null, + name_upper varchar not null, + level integer not null, + parent_name varchar, + expanded integer not null default 1, + amounts_expanded integer not null default 0, + generation integer not null default 0, + primary key(profile, name)); + +insert into accounts_new(profile, name, name_upper, level, parent_name, + expanded, amounts_expanded, generation) +select profile, name, name_upper, level, parent_name, expanded, + amounts_expanded, generation from accounts; + +-- account_values +create table account_values_new( + profile varchar not null, + account varchar not null, + currency varchar not null default '', + value real not null, + generation integer not null default 0, + primary key(profile, account, currency)); + +insert into account_values_new( + profile, account, currency, value, generation) +select profile, account, currency, value, generation +from account_values; + +-- description_history +create table description_history_new( + description varchar collate NOCASE not null, + description_upper varchar not null, + generation integer not null default 0, + primary key(description)); + +insert into description_history_new(description, description_upper, generation) +select description, description_upper, generation from description_history; + +-- transactions +create table transactions_new( + profile varchar not null, + id integer not null, + data_hash varchar not null, + year integer not null, + month integer not null, + day integer not null, + description varchar collate NOCASE not null, + comment varchar, + generation integer not null default 0, + primary key(profile,id)); + +insert into transactions_new(profile, id, data_hash, year, month, day, description, + comment, generation) +select profile, id, data_hash, year, month, day, description, + comment, generation +from transactions; + +-- transaction_accounts +create table transaction_accounts_new( + profile varchar not null, + transaction_id integer not null, + order_no integer not null, + account_name varchar not null, + currency varchar not null default '', + amount real not null, + comment varchar, + generation integer not null default 0, + primary key(profile, transaction_id, order_no), + foreign key (profile,account_name) references accounts(profile,name) + on delete cascade on update restrict, + foreign key(profile, transaction_id) references transactions(profile,id) + on delete cascade on update restrict); + +insert into transaction_accounts_new(profile, transaction_id, order_no, account_name, + currency, amount, comment, generation) +select profile, transaction_id, order_no, account_name, + currency, amount, comment, generation +from transaction_accounts; + +--currencies +create table currencies_new(id integer not null primary key, name varchar not null, + position varchar not null, has_gap integer not null); + +insert into currencies_new(id, name, position, has_gap) +select id, name, position, has_gap +from currencies; + + +-- drop originals +drop table transaction_accounts; +drop table transactions; +drop table account_values; +drop table accounts; +drop table description_history; +drop table profiles; +drop table options; +drop table currencies; + +-- rename new +alter table options_new rename to options; +alter table profiles_new rename to profiles; +alter table accounts_new rename to accounts; +alter table account_values_new rename to account_values; +alter table description_history_new rename to description_history; +alter table transactions_new rename to transactions; +alter table transaction_accounts_new rename to transaction_accounts; +alter table currencies_new rename to currencies; + +-- indices +create index fk_tran_acc_prof_acc on transaction_accounts(profile, account_name); +create unique index un_transactions_data_hash on transactions(profile,data_hash); +create index idx_transaction_description on transactions(description); + + +-- new tables +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, + is_fallback INTEGER NOT NULL DEFAULT 0); +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 RESTRICT ON DELETE CASCADE, + FOREIGN KEY(currency) REFERENCES currencies(id) ON UPDATE RESTRICT ON DELETE RESTRICT); +create index fk_template_accounts_template on template_accounts(template_id); +create index fk_template_accounts_currency on template_accounts(currency); diff --git a/app/src/main/res/raw/sql_0.sql b/app/src/main/res/raw/sql_0.sql deleted file mode 100644 index cf9cca97..00000000 --- a/app/src/main/res/raw/sql_0.sql +++ /dev/null @@ -1,26 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -create table if not exists accounts(name varchar); -create index if not exists idx_accounts_name on accounts(name); -create table if not exists options(name varchar, value varchar); -create unique index if not exists idx_options_name on options(name); -create table if not exists account_values(account varchar not null, currency varchar not null, value decimal(18,2) not null); -create index if not exists idx_account_values_account on account_values(account); -create unique index if not exists un_account_values on account_values(account,currency); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_1.sql b/app/src/main/res/raw/sql_1.sql deleted file mode 100644 index 23243c31..00000000 --- a/app/src/main/res/raw/sql_1.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table accounts add keep boolean; -alter table account_values add keep boolean; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_10.sql b/app/src/main/res/raw/sql_10.sql deleted file mode 100644 index e6ceb4e3..00000000 --- a/app/src/main/res/raw/sql_10.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -delete from transaction_accounts; -delete from transactions; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_11.sql b/app/src/main/res/raw/sql_11.sql deleted file mode 100644 index 0bf366b4..00000000 --- a/app/src/main/res/raw/sql_11.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -create table profiles(uuid varchar not null primary key, name not null, url not null, use_authentication boolean not null, auth_user varchar, auth_password varchar); -create unique index un_profile_name on profiles(name); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_12.sql b/app/src/main/res/raw/sql_12.sql deleted file mode 100644 index a9baf115..00000000 --- a/app/src/main/res/raw/sql_12.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -drop index un_profile_name; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_13.sql b/app/src/main/res/raw/sql_13.sql deleted file mode 100644 index 522eb8d9..00000000 --- a/app/src/main/res/raw/sql_13.sql +++ /dev/null @@ -1,41 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -delete from options where name='transaction_list_last_update'; -delete from options where name='last_refresh'; -alter table options add profile varchar; -drop index idx_options_name; -create unique index un_options on options(profile,name); --- -drop table account_values; -create table account_values(profile varchar not null, account varchar not null, currency varchar not null default '', keep boolean, value decimal not null ); -create unique index un_account_values on account_values(profile,account,currency); --- -drop table accounts; -create table accounts(profile varchar not null, name varchar not null, name_upper varchar not null, hidden boolean not null default 0, keep boolean not null default 0, level integer not null, parent_name varchar); -create unique index un_accounts on accounts(profile, name); --- -drop table transaction_accounts; -drop table transactions; --- -create table transactions(id integer not null, data_hash varchar not null, date varchar not null, description varchar not null, keep boolean not null default 0); -create unique index un_transactions_id on transactions(id); -create unique index un_transactions_data_hash on transactions(data_hash); --- -create table transaction_accounts(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,account_name), constraint fk_transaction_accounts_trn foreign key(transaction_id) references transactions(id)); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_14.sql b/app/src/main/res/raw/sql_14.sql deleted file mode 100644 index ac47837e..00000000 --- a/app/src/main/res/raw/sql_14.sql +++ /dev/null @@ -1,27 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -drop table transaction_accounts; -drop table transactions; --- -create table transactions(profile varchar not null, id integer not null, data_hash varchar not null, date varchar not null, description varchar not null, keep boolean not null default 0); -create unique index un_transactions_id on transactions(profile,id); -create unique index un_transactions_data_hash on transactions(profile,data_hash); --- -create table transaction_accounts(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,account_name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id)); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_15.sql b/app/src/main/res/raw/sql_15.sql deleted file mode 100644 index d134676c..00000000 --- a/app/src/main/res/raw/sql_15.sql +++ /dev/null @@ -1,29 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -delete from options where profile is null and name='last_scrape'; -create table new_options(profile varchar not null, name varchar not null, value varchar); - -insert into new_options(profile, name, value) select distinct '-', o.name, (select o2.value from options o2 where o2.name=o.name and o2.profile is null) from options o where o.profile is null; -insert into new_options(profile, name, value) select distinct o.profile, o.name, (select o2.value from options o2 where o2.name=o.name and o2.profile=o.profile) from options o where o.profile is not null; -drop table options; -create table options(profile varchar not null, name varchar not null, value varchar); -create unique index un_options on options(profile,name); -insert into options(profile,name,value) select profile,name,value from new_options; -drop table new_options; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_16.sql b/app/src/main/res/raw/sql_16.sql deleted file mode 100644 index 3bf8cc96..00000000 --- a/app/src/main/res/raw/sql_16.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -alter table profiles add order_no integer; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_2.sql b/app/src/main/res/raw/sql_2.sql deleted file mode 100644 index 27307216..00000000 --- a/app/src/main/res/raw/sql_2.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -create table description_history(description varchar not null primary key, keep boolean); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_22.sql b/app/src/main/res/raw/sql_22.sql deleted file mode 100644 index a1780f02..00000000 --- a/app/src/main/res/raw/sql_22.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -alter table profiles add preferred_accounts_filter varchar; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_23.sql b/app/src/main/res/raw/sql_23.sql deleted file mode 100644 index ea3a9e85..00000000 --- a/app/src/main/res/raw/sql_23.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -alter table profiles add future_dates integer; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_24.sql b/app/src/main/res/raw/sql_24.sql deleted file mode 100644 index 268922d7..00000000 --- a/app/src/main/res/raw/sql_24.sql +++ /dev/null @@ -1,20 +0,0 @@ --- Copyright © 2019 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 . - -BEGIN TRANSACTION; - -alter table profiles add api_version integer; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_26.sql b/app/src/main/res/raw/sql_26.sql deleted file mode 100644 index e44ec03b..00000000 --- a/app/src/main/res/raw/sql_26.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table transaction_accounts add comment varchar; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_27.sql b/app/src/main/res/raw/sql_27.sql deleted file mode 100644 index de643e48..00000000 --- a/app/src/main/res/raw/sql_27.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table profiles add show_commodity_by_default boolean default 0; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_28.sql b/app/src/main/res/raw/sql_28.sql deleted file mode 100644 index f392fda1..00000000 --- a/app/src/main/res/raw/sql_28.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table profiles add default_commodity varchar; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_29.sql b/app/src/main/res/raw/sql_29.sql deleted file mode 100644 index a134a225..00000000 --- a/app/src/main/res/raw/sql_29.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -create index idx_transaction_description on transactions(description); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_3.sql b/app/src/main/res/raw/sql_3.sql deleted file mode 100644 index 7994a7aa..00000000 --- a/app/src/main/res/raw/sql_3.sql +++ /dev/null @@ -1,23 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table description_history add description_upper varchar; -update description_history set description_upper = upper(description); -alter table accounts add name_upper varchar; -update accounts set name_upper = upper(name); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_30.sql b/app/src/main/res/raw/sql_30.sql deleted file mode 100644 index 79e142bc..00000000 --- a/app/src/main/res/raw/sql_30.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -delete from options where profile <> '-' and not exists (select 1 from profiles p where p.uuid=options.profile); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_32.sql b/app/src/main/res/raw/sql_32.sql deleted file mode 100644 index 860b0dd4..00000000 --- a/app/src/main/res/raw/sql_32.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -update profiles set show_comments_by_default = 1; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_33.sql b/app/src/main/res/raw/sql_33.sql deleted file mode 100644 index c915ce2d..00000000 --- a/app/src/main/res/raw/sql_33.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table transactions add comment varchar; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_35.sql b/app/src/main/res/raw/sql_35.sql deleted file mode 100644 index b8dc44d8..00000000 --- a/app/src/main/res/raw/sql_35.sql +++ /dev/null @@ -1,23 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -create table accounts_new(profile varchar not null, name varchar not null, name_upper varchar not null, keep boolean not null default 0, level integer not null, parent_name varchar, expanded default 1, amounts_expanded boolean default 0); -insert into accounts_new(profile, name, name_upper, keep, level, parent_name, expanded, amounts_expanded) select profile, name, name_upper, keep, level, parent_name, expanded, amounts_expanded from accounts; -drop table accounts; -alter table accounts_new rename to accounts; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_36.sql b/app/src/main/res/raw/sql_36.sql deleted file mode 100644 index a7defcb7..00000000 --- a/app/src/main/res/raw/sql_36.sql +++ /dev/null @@ -1,23 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table accounts add generation integer default 0; -alter table account_values add generation integer default 0; -alter table transactions add generation integer default 0; -alter table transaction_accounts add generation integer default 0; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_37.sql b/app/src/main/res/raw/sql_37.sql deleted file mode 100644 index 5f57308d..00000000 --- a/app/src/main/res/raw/sql_37.sql +++ /dev/null @@ -1,22 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table transaction_accounts add order_no integer not null default 0; -update transaction_accounts set order_no = rowid; -create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_38.sql b/app/src/main/res/raw/sql_38.sql deleted file mode 100644 index 19a18bde..00000000 --- a/app/src/main/res/raw/sql_38.sql +++ /dev/null @@ -1,24 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -CREATE TABLE transaction_accounts_new(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, comment varchar, generation integer default 0, order_no integer not null default 0, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id)); -insert into transaction_accounts_new(profile, transaction_id, account_name, currency, amount, comment, generation, order_no) select profile, transaction_id, account_name, currency, amount, comment, generation, order_no from transaction_accounts; -drop table transaction_accounts; -alter table transaction_accounts_new rename to transaction_accounts; -create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_39.sql b/app/src/main/res/raw/sql_39.sql deleted file mode 100644 index 4f5db2ac..00000000 --- a/app/src/main/res/raw/sql_39.sql +++ /dev/null @@ -1,24 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -create table description_history_new(description varchar not null primary key, description_upper varchar, generation integer default 0); -insert into description_history_new(description, description_upper) select description, description_upper from description_history; -drop table description_history; -alter table description_history_new rename to description_history; -create unique index un_description_history on description_history(description_upper); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_4.sql b/app/src/main/res/raw/sql_4.sql deleted file mode 100644 index 5ae73aad..00000000 --- a/app/src/main/res/raw/sql_4.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table accounts add hidden boolean default 0; -update accounts set hidden = 0; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_40.sql b/app/src/main/res/raw/sql_40.sql deleted file mode 100644 index 0d6d5da2..00000000 --- a/app/src/main/res/raw/sql_40.sql +++ /dev/null @@ -1,21 +0,0 @@ --- 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 . -pragma foreign_keys=off; -BEGIN TRANSACTION; - -delete from transaction_accounts where not exists (select 1 from accounts a where a.profile=transaction_accounts.profile and a.name=transaction_accounts.account_name); -delete from transaction_accounts where not exists (select 1 from transactions t where t.profile=transaction_accounts.profile and t.id=transaction_accounts.transaction_id); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_42.sql b/app/src/main/res/raw/sql_42.sql deleted file mode 100644 index a678599c..00000000 --- a/app/src/main/res/raw/sql_42.sql +++ /dev/null @@ -1,23 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -create table patterns(id integer not null primary key, name varchar not null, position integer not null, regular_expression varchar not null, transaction_description varchar, transaction_comment varchar, date_year_match_group integer, date_month_match_group integer, date_day_match_group integer); -create unique index un_patterns_id on patterns(id); -create table pattern_accounts(id integer not null primary key, pattern_id integer not null, acc varchar, acc_match_group integer, currency integer, currency_match_group integer, amount decimal, amount_match_group integer, comment varchar, 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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_43.sql b/app/src/main/res/raw/sql_43.sql deleted file mode 100644 index fc1db99e..00000000 --- a/app/src/main/res/raw/sql_43.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table pattern_accounts add position integer not null default 0; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_44.sql b/app/src/main/res/raw/sql_44.sql deleted file mode 100644 index 15d54bd4..00000000 --- a/app/src/main/res/raw/sql_44.sql +++ /dev/null @@ -1,24 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table patterns add transaction_description_match_group short; -alter table patterns add transaction_comment_match_group short; -alter table patterns add date_year short; -alter table patterns add date_month short; -alter table patterns add date_day short; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_45.sql b/app/src/main/res/raw/sql_45.sql deleted file mode 100644 index ffdf3467..00000000 --- a/app/src/main/res/raw/sql_45.sql +++ /dev/null @@ -1,26 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -drop table pattern_accounts; -drop table patterns; - -create table patterns(id integer not null primary key, name varchar not null, position integer not null, regular_expression varchar not null, transaction_description varchar, transaction_description_match_group integer, transaction_comment varchar, 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); -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 varchar, acc_match_group integer, currency integer, currency_match_group integer, amount decimal, amount_match_group integer, comment varchar, 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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_46.sql b/app/src/main/res/raw/sql_46.sql deleted file mode 100644 index 031d201d..00000000 --- a/app/src/main/res/raw/sql_46.sql +++ /dev/null @@ -1,26 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -drop table pattern_accounts; -drop table patterns; - -create table patterns(id integer not null primary key, name text not null, position integer not null, regular_expression text not null, 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); -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 decimal, 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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_47.sql b/app/src/main/res/raw/sql_47.sql deleted file mode 100644 index 0fb4ea9a..00000000 --- a/app/src/main/res/raw/sql_47.sql +++ /dev/null @@ -1,26 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -drop table pattern_accounts; -drop table patterns; - -create table patterns(id INTEGER not null primary key, name TEXT not null, position INTEGER not null, regular_expression TEXT not null, 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); -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 decimal, 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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_48.sql b/app/src/main/res/raw/sql_48.sql deleted file mode 100644 index 99aa27d1..00000000 --- a/app/src/main/res/raw/sql_48.sql +++ /dev/null @@ -1,23 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -drop table pattern_accounts; - -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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_49.sql b/app/src/main/res/raw/sql_49.sql deleted file mode 100644 index 549c4a48..00000000 --- a/app/src/main/res/raw/sql_49.sql +++ /dev/null @@ -1,27 +0,0 @@ --- 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 . - -pragma foreign_keys=off; - -BEGIN TRANSACTION; - -create table currencies_new(id integer not null primary key, name varchar not null, position varchar not null, has_gap integer not null); -insert into currencies_new(id, name, position, has_gap) select id, name, position, has_gap from currencies; - -drop table currencies; - -alter table currencies_new rename to currencies; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_5.sql b/app/src/main/res/raw/sql_5.sql deleted file mode 100644 index b0bb044a..00000000 --- a/app/src/main/res/raw/sql_5.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table accounts add level integer; -alter table accounts add parent varchar; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_50.sql b/app/src/main/res/raw/sql_50.sql deleted file mode 100644 index 5dce5c7f..00000000 --- a/app/src/main/res/raw/sql_50.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table patterns add test_text text; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_51.sql b/app/src/main/res/raw/sql_51.sql deleted file mode 100644 index 9536e684..00000000 --- a/app/src/main/res/raw/sql_51.sql +++ /dev/null @@ -1,31 +0,0 @@ --- 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 . - -pragma foreign_keys=off; - -BEGIN TRANSACTION; - -create table patterns_new(id INTEGER not null primary key, name TEXT not null, regular_expression TEXT not null, 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); - -insert into patterns_new(id, name, regular_expression, 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, 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; - -drop table patterns; - -alter table patterns_new rename to patterns; -create unique index un_patterns_id on patterns(id); - -COMMIT TRANSACTION; - -pragma foreign_keys = ON; \ 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 deleted file mode 100644 index 0d85d687..00000000 --- a/app/src/main/res/raw/sql_52.sql +++ /dev/null @@ -1,21 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -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); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_53.sql b/app/src/main/res/raw/sql_53.sql deleted file mode 100644 index a541b852..00000000 --- a/app/src/main/res/raw/sql_53.sql +++ /dev/null @@ -1,20 +0,0 @@ --- 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 . - -BEGIN TRANSACTION; - -alter table pattern_accounts add negate_amount INTEGER; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_54.sql b/app/src/main/res/raw/sql_54.sql deleted file mode 100644 index f8e13ba9..00000000 --- a/app/src/main/res/raw/sql_54.sql +++ /dev/null @@ -1,29 +0,0 @@ --- 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 . - --- copied from the Room migration - -BEGIN TRANSACTION; - -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); -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); -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; -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; -create index fk_template_accounts_template on template_accounts(template_id); -create index fk_template_accounts_currency on template_accounts(currency); -drop table pattern_accounts; -drop table patterns; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_55.sql b/app/src/main/res/raw/sql_55.sql deleted file mode 100644 index 780b451c..00000000 --- a/app/src/main/res/raw/sql_55.sql +++ /dev/null @@ -1,27 +0,0 @@ --- 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 . - --- copied from the Room migration -BEGIN TRANSACTION; - -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); -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; -drop table template_accounts; -alter table template_accounts_new rename to template_accounts; - -create index fk_template_accounts_template on template_accounts(template_id); -create index fk_template_accounts_currency on template_accounts(currency); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_56.sql b/app/src/main/res/raw/sql_56.sql deleted file mode 100644 index ba393936..00000000 --- a/app/src/main/res/raw/sql_56.sql +++ /dev/null @@ -1,34 +0,0 @@ --- 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 . - --- copied from the Room migration - -PRAGMA foreign_keys = OFF; -BEGIN TRANSACTION; - -create table accounts_new(profile varchar not null, name varchar not null, \ - name_upper varchar not null, level integer not null, parent_name varchar, \ - expanded integer default 1, amounts_expanded integer default 0, \ - generation integer default 0, primary key(profile, name)); -insert into accounts_new(profile, name, name_upper, level, parent_name, expanded, \ - amounts_expanded, generation) \ - select profile, name, name_upper, level, parent_name, expanded, \ - amounts_expanded, generation \ - from accounts; -drop table accounts; -alter table accounts_new rename to accounts; - -COMMIT TRANSACTION; -PRAGMA foreign_keys = ON; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_57.sql b/app/src/main/res/raw/sql_57.sql deleted file mode 100644 index 45812bc5..00000000 --- a/app/src/main/res/raw/sql_57.sql +++ /dev/null @@ -1,23 +0,0 @@ --- 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 . - --- add templates.is_fallback - -BEGIN TRANSACTION; - -alter table templates \ -add is_fallback integer default 0; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_58.sql b/app/src/main/res/raw/sql_58.sql deleted file mode 100644 index ad28d1a5..00000000 --- a/app/src/main/res/raw/sql_58.sql +++ /dev/null @@ -1,150 +0,0 @@ --- 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 . - --- profiles ground for Room - -PRAGMA foreign_keys = OFF; -BEGIN TRANSACTION; - -create table profiles( - uuid text not null, - name text not null, - url text not null, - use_authentication integer not null, - auth_user text, - auth_password text, - order_no integer not null, - permit_posting integer not null default 0, - theme integer not null default -1, - preferred_accounts_filter varchar, - future_dates integer not null, - api_version integer not null, - show_commodity_by_default integer not null default 0, - default_commodity text, - show_comments_by_default integer not null default 1, - detected_version_pre_1_19 integer not null, - detected_version_major integer not null, - detected_version_minor integer not null, - primary key(uuid)); - -insert into profiles_new( - uuid, name, url, use_authentication, auth_user, auth_password, order_no, - permit_posting, theme, preferred_accounts_filter, future_dates, api_version, - show_commodity_by_default, default_commodity, show_comments_by_default, - detected_version_pre_1_19, detected_version_major, detected_version_minor) -select uuid, name, url, use_authentication, auth_user, auth_password, order_no, - permit_posting, theme, preferred_accounts_filter, future_dates, api_version, - show_commodity_by_default, default_commodity, show_comments_by_default, - detected_version_pre_1_19, detected_version_major, detected_version_minor -from profiles; - -drop table profiles; - -alter table profiles_new -rename to profiles; - -create table options_new(profile varchar not null, name varchar not null, value varchar, primary key(profile, name)); - -insert into options_new(profile, name, value) -select profile, name, value from options; - -drop table options; - -alter table options_new -rename to options; - -create table account_values_new( - profile varchar not null, - account varchar not null, - currency varchar not null default '', - value real not null, - generation integer not null default 0, - primary key(profile, account, currency)); - -insert into account_values_new( - profile, account, currency, value, generation) -select profile, account, currency, value, generation -from account_values; - -drop table account_values; -alter table account_values_new rename to account_values; - -create table description_history_new( - description varchar collate NOCASE not null primary key, - description_upper varchar not null, - generation integer not null default 0, - primary key(description)); - -insert into description_history_new(description, description_upper, generation) -select description, description_upper, generation from description_history; - -drop table description_history; -alter table description_history_new rename to description_history; - --- transactions - -create table transactions_new( - profile varchar not null, - id integer not null, - data_hash varchar not null, - year integer not null, - month integer not null, - day integer not null, - description varchar collate NOCASE not null, - comment varchar, - generation integer not null default 0, - primary key(profile,id)); - -insert into transactions_new(profile, id, data_hash, year, month, day, description, - comment, generation) -select profile, id, data_hash, year, month, day, description, - comment, generation -from transactions; - -drop table transactions; -alter table transactions_new rename to transactions; - -create unique index un_transactions_data_hash on transactions(profile,data_hash); -create index idx_transaction_description on transactions(description); - --- transaction_accounts - -create table transaction_accounts_new( - profile varchar not null, - transaction_id integer not null, - order_no integer not null, - account_name varchar not null, - currency varchar not null default '', - amount real not null, - comment varchar, - generation integer not null default 0, - primary key(profile, transaction_id, order_no), - foreign key (profile,account_name) references accounts(profile,name) - on delete cascade on update restrict, - foreign key(profile, transaction_id) references transactions(profile,id) - on delete cascade on update restrict); - -insert into transaction_accounts_new(profile, transaction_id, order_no, account_name, - currency, amount, comment, generation) -select profile, transaction_id, order_no, account_name, - currency, amount, comment, generation -from transaction_accounts; - -drop table transaction_accounts; -alter table transaction_accounts_new rename to transaction_accounts; - -COMMIT TRANSACTION; - -PRAGMA foreign_keys = ON; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_6.sql b/app/src/main/res/raw/sql_6.sql deleted file mode 100644 index dd4ff227..00000000 --- a/app/src/main/res/raw/sql_6.sql +++ /dev/null @@ -1,26 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -drop index idx_accounts_name; -create table accounts_tmp(name varchar not null, name_upper varchar not null primary key, hidden boolean not null default 0, level integer not null default 0, parent_name varchar); -insert or replace into accounts_tmp(name, name_upper, hidden, level, parent_name) select name, name_upper, hidden, level, parent from accounts; -drop table accounts; -create table accounts(name varchar not null, name_upper varchar not null primary key, hidden boolean not null default 0, level integer not null default 0, parent_name varchar, keep boolean default 1); -insert into accounts(name, name_upper, hidden, level, parent_name) select name, name_upper, hidden, level, parent_name from accounts_tmp; -drop table accounts_tmp; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_7.sql b/app/src/main/res/raw/sql_7.sql deleted file mode 100644 index 299a9e7f..00000000 --- a/app/src/main/res/raw/sql_7.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -create table transactions(id varchar primary key, date varchar, description varchar); -create table transaction_accounts(transaction_id integer not null, account_name varchar not null, amount float, currency varchar, foreign key (transaction_id) references transactions(id), foreign key(account_name) references accounts(name)); - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_8.sql b/app/src/main/res/raw/sql_8.sql deleted file mode 100644 index 6e9ae6c5..00000000 --- a/app/src/main/res/raw/sql_8.sql +++ /dev/null @@ -1,21 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table transactions add data_hash varchar; -delete from transactions; - -COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_9.sql b/app/src/main/res/raw/sql_9.sql deleted file mode 100644 index 6f290d76..00000000 --- a/app/src/main/res/raw/sql_9.sql +++ /dev/null @@ -1,28 +0,0 @@ --- Copyright © 2018 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 . - -BEGIN TRANSACTION; - -alter table transactions add keep boolean default 1 not null; -update transactions set keep = 1; -create table transactions_new(id integer, date varchar, description varchar, data_hash varchar, keep boolean); -insert into transactions_new(id, date, description, data_hash, keep) select cast(id as integer), date, description, data_hash, keep from transactions; -drop table transactions; -create table transactions(id integer primary key, date varchar, description varchar, data_hash varchar, keep boolean); -create unique index un_transactions_data_hash on transactions(data_hash); -insert into transactions(id, date, description, data_hash, keep) select id, date, description, data_hash, keep from transactions_new; -drop table transactions_new; - -COMMIT TRANSACTION; \ No newline at end of file -- 2.39.2