From caf9db3f39e12a8050f1d26bf196857bd3d36f2e Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Fri, 12 Feb 2021 08:09:53 +0200 Subject: [PATCH] add accounts to Room --- .../net.ktnx.mobileledger.db.DB/56.json | 342 ++++++++++++++++++ .../net/ktnx/mobileledger/dao/AccountDAO.java | 53 +++ .../net/ktnx/mobileledger/dao/BaseDAO.java | 54 +++ .../net/ktnx/mobileledger/db/Account.java | 96 +++++ .../java/net/ktnx/mobileledger/db/DB.java | 4 +- .../utils/MobileLedgerDatabase.java | 3 +- app/src/main/res/raw/create_db.sql | 5 +- app/src/main/res/raw/sql_56.sql | 34 ++ 8 files changed, 585 insertions(+), 6 deletions(-) create mode 100644 app/schemas/net.ktnx.mobileledger.db.DB/56.json create mode 100644 app/src/main/java/net/ktnx/mobileledger/dao/AccountDAO.java create mode 100644 app/src/main/java/net/ktnx/mobileledger/dao/BaseDAO.java create mode 100644 app/src/main/java/net/ktnx/mobileledger/db/Account.java create mode 100644 app/src/main/res/raw/sql_56.sql diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/56.json b/app/schemas/net.ktnx.mobileledger.db.DB/56.json new file mode 100644 index 00000000..f60708ce --- /dev/null +++ b/app/schemas/net.ktnx.mobileledger.db.DB/56.json @@ -0,0 +1,342 @@ +{ + "formatVersion": 1, + "database": { + "version": 56, + "identityHash": "00c7b4a529107e23cd5925d75867f6d9", + "entities": [ + { + "tableName": "templates", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`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)", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "name", + "columnName": "name", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "regularExpression", + "columnName": "regular_expression", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "testText", + "columnName": "test_text", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "transactionDescription", + "columnName": "transaction_description", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "transactionDescriptionMatchGroup", + "columnName": "transaction_description_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "transactionComment", + "columnName": "transaction_comment", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "transactionCommentMatchGroup", + "columnName": "transaction_comment_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateYear", + "columnName": "date_year", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateYearMatchGroup", + "columnName": "date_year_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateMonth", + "columnName": "date_month", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateMonthMatchGroup", + "columnName": "date_month_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateDay", + "columnName": "date_day", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "dateDayMatchGroup", + "columnName": "date_day_match_group", + "affinity": "INTEGER", + "notNull": false + } + ], + "primaryKey": { + "columnNames": [ + "id" + ], + "autoGenerate": true + }, + "indices": [], + "foreignKeys": [] + }, + { + "tableName": "template_accounts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`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 )", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "templateId", + "columnName": "template_id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "accountName", + "columnName": "acc", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "position", + "columnName": "position", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "accountNameMatchGroup", + "columnName": "acc_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "currency", + "columnName": "currency", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "currencyMatchGroup", + "columnName": "currency_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "amount", + "columnName": "amount", + "affinity": "REAL", + "notNull": false + }, + { + "fieldPath": "amountMatchGroup", + "columnName": "amount_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "accountComment", + "columnName": "comment", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "accountCommentMatchGroup", + "columnName": "comment_match_group", + "affinity": "INTEGER", + "notNull": false + }, + { + "fieldPath": "negateAmount", + "columnName": "negate_amount", + "affinity": "INTEGER", + "notNull": false + } + ], + "primaryKey": { + "columnNames": [ + "id" + ], + "autoGenerate": true + }, + "indices": [ + { + "name": "fk_template_accounts_template", + "unique": false, + "columnNames": [ + "template_id" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_template` ON `${TABLE_NAME}` (`template_id`)" + }, + { + "name": "fk_template_accounts_currency", + "unique": false, + "columnNames": [ + "currency" + ], + "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_currency` ON `${TABLE_NAME}` (`currency`)" + } + ], + "foreignKeys": [ + { + "table": "templates", + "onDelete": "CASCADE", + "onUpdate": "RESTRICT", + "columns": [ + "template_id" + ], + "referencedColumns": [ + "id" + ] + }, + { + "table": "currencies", + "onDelete": "RESTRICT", + "onUpdate": "RESTRICT", + "columns": [ + "currency" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "currencies", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `position` TEXT NOT NULL, `has_gap` INTEGER NOT NULL)", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "name", + "columnName": "name", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "position", + "columnName": "position", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "hasGap", + "columnName": "has_gap", + "affinity": "INTEGER", + "notNull": true + } + ], + "primaryKey": { + "columnNames": [ + "id" + ], + "autoGenerate": true + }, + "indices": [], + "foreignKeys": [] + }, + { + "tableName": "accounts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`level` INTEGER NOT NULL, `profile` TEXT NOT NULL, `name` TEXT NOT NULL, `name_upper` TEXT NOT NULL, `parent_name` TEXT, `expanded` INTEGER NOT NULL DEFAULT 1, `amounts_expanded` INTEGER NOT NULL DEFAULT 0, `generation` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`profile`, `name`))", + "fields": [ + { + "fieldPath": "level", + "columnName": "level", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "profile", + "columnName": "profile", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "name", + "columnName": "name", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "nameUpper", + "columnName": "name_upper", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "parentName", + "columnName": "parent_name", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "expanded", + "columnName": "expanded", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "1" + }, + { + "fieldPath": "amountsExpanded", + "columnName": "amounts_expanded", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + }, + { + "fieldPath": "generation", + "columnName": "generation", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "columnNames": [ + "profile", + "name" + ], + "autoGenerate": false + }, + "indices": [], + "foreignKeys": [] + } + ], + "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, '00c7b4a529107e23cd5925d75867f6d9')" + ] + } +} \ No newline at end of file diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/AccountDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/AccountDAO.java new file mode 100644 index 00000000..a4ed06e4 --- /dev/null +++ b/app/src/main/java/net/ktnx/mobileledger/dao/AccountDAO.java @@ -0,0 +1,53 @@ +/* + * 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 . + */ + +package net.ktnx.mobileledger.dao; + +import androidx.annotation.NonNull; +import androidx.lifecycle.LiveData; +import androidx.room.Dao; +import androidx.room.Delete; +import androidx.room.Insert; +import androidx.room.Query; +import androidx.room.Update; + +import net.ktnx.mobileledger.db.Account; + +import java.util.List; + +@Dao +public abstract class AccountDAO extends BaseDAO { + @Insert + abstract void insertSync(Account item); + + @Update + abstract void updateSync(Account item); + + @Delete + abstract void deleteSync(Account item); + + @Query("SELECT * FROM accounts") + abstract LiveData> getAll(); + + @Query("SELECT * FROM accounts WHERE profile = :profileUUID AND name = :accountName") + abstract LiveData getByName(@NonNull String profileUUID, @NonNull String accountName); + +// not useful for now +// @Transaction +// @Query("SELECT * FROM patterns") +// List getPatternsWithAccounts(); +} diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/BaseDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/BaseDAO.java new file mode 100644 index 00000000..0bcb3e48 --- /dev/null +++ b/app/src/main/java/net/ktnx/mobileledger/dao/BaseDAO.java @@ -0,0 +1,54 @@ +/* + * 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 . + */ + +package net.ktnx.mobileledger.dao; + +import android.os.AsyncTask; +import android.os.Handler; +import android.os.Looper; + +import androidx.annotation.Nullable; + +abstract class BaseDAO { + abstract void insertSync(T item); + public void insert(T item, @Nullable Runnable onDone) { + AsyncTask.execute(() -> { + insertSync(item); + if (onDone != null) + new Handler(Looper.getMainLooper()).post(onDone); + }); + } + + abstract void updateSync(T item); + public void update(T item, @Nullable Runnable onDone) { + AsyncTask.execute(() -> { + updateSync(item); + if (onDone != null) + new Handler(Looper.getMainLooper()).post(onDone); + }); + } + abstract void deleteSync(T item); + public void delete(T item, @Nullable Runnable onDone) { + AsyncTask.execute(() -> { + deleteSync(item); + if (onDone != null) + new Handler(Looper.getMainLooper()).post(onDone); + }); + } + + +} diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Account.java b/app/src/main/java/net/ktnx/mobileledger/db/Account.java new file mode 100644 index 00000000..7d88a030 --- /dev/null +++ b/app/src/main/java/net/ktnx/mobileledger/db/Account.java @@ -0,0 +1,96 @@ +/* + * 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 . + */ + +package net.ktnx.mobileledger.db; + +import androidx.annotation.NonNull; +import androidx.room.ColumnInfo; +import androidx.room.Entity; + +@Entity(tableName = "accounts", primaryKeys = {"profile", "name"}) +public class Account { + @ColumnInfo + int level; + @ColumnInfo + @NonNull + private String profile; + @ColumnInfo + @NonNull + private String name; + @NonNull + @ColumnInfo(name = "name_upper") + private String nameUpper; + @ColumnInfo(name = "parent_name") + private String parentName; + @ColumnInfo(defaultValue = "1") + private boolean expanded = true; + @ColumnInfo(name = "amounts_expanded", defaultValue = "0") + private boolean amountsExpanded = false; + @ColumnInfo(defaultValue = "0") + private int generation; + @NonNull + public String getProfile() { + return profile; + } + public void setProfile(@NonNull String profile) { + this.profile = profile; + } + @NonNull + public String getName() { + return name; + } + public void setName(@NonNull String name) { + this.name = name; + } + @NonNull + public String getNameUpper() { + return nameUpper; + } + public void setNameUpper(@NonNull String nameUpper) { + this.nameUpper = nameUpper; + } + public int getLevel() { + return level; + } + public void setLevel(int level) { + this.level = level; + } + public String getParentName() { + return parentName; + } + public void setParentName(String parentName) { + this.parentName = parentName; + } + public boolean isExpanded() { + return expanded; + } + public void setExpanded(boolean expanded) { + this.expanded = expanded; + } + public boolean isAmountsExpanded() { + return amountsExpanded; + } + public void setAmountsExpanded(boolean amountsExpanded) { + this.amountsExpanded = amountsExpanded; + } + public int getGeneration() { + return generation; + } + public void setGeneration(int generation) { + this.generation = generation; + } +} 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 1e0a380f..13d0818c 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java @@ -30,7 +30,9 @@ import net.ktnx.mobileledger.dao.TemplateAccountDAO; import net.ktnx.mobileledger.dao.TemplateHeaderDAO; import net.ktnx.mobileledger.utils.MobileLedgerDatabase; -@Database(version = 55, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class}) +@Database(version = 56, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class, + net.ktnx.mobileledger.db.Account.class +}) abstract public class DB extends RoomDatabase { private static DB instance; public static DB get() { 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 e0a0a421..b3c719e9 100644 --- a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java +++ b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java @@ -22,7 +22,6 @@ import android.content.res.Resources; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; -import android.util.Log; import androidx.lifecycle.MutableLiveData; @@ -41,7 +40,7 @@ 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 = 55; + private static final int LATEST_REVISION = 56; private static final String CREATE_DB_SQL = "create_db"; private final Application mContext; diff --git a/app/src/main/res/raw/create_db.sql b/app/src/main/res/raw/create_db.sql index e77ac570..3d206991 100644 --- a/app/src/main/res/raw/create_db.sql +++ b/app/src/main/res/raw/create_db.sql @@ -16,8 +16,7 @@ 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, order_no integer, permit_posting boolean default 0, theme integer default -1, preferred_accounts_filter varchar, future_dates integer, api_version integer, show_commodity_by_default boolean default 0, default_commodity varchar, show_comments_by_default boolean default 1, detected_version_pre_1_19 boolean, detected_version_major integer, detected_version_minor integer); -create table accounts(profile varchar not null, name varchar not null, name_upper varchar not null, level integer not null, parent_name varchar, expanded default 1, amounts_expanded boolean default 0, generation integer default 0); -create unique index un_accounts on accounts(profile, name); +create table accounts(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)); create table options(profile varchar not null, name varchar not null, value varchar); create unique index un_options on options(profile,name); create table account_values(profile varchar not null, account varchar not null, currency varchar not null default '', value decimal not null, generation integer default 0 ); @@ -38,4 +37,4 @@ create index fk_template_accounts_template on template_accounts(template_id); create index fk_template_accounts_currency on template_accounts(currency); COMMIT TRANSACTION; --- updated to revision 55 \ No newline at end of file +-- updated to revision 56 \ 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 new file mode 100644 index 00000000..8ca36cfc --- /dev/null +++ b/app/src/main/res/raw/sql_56.sql @@ -0,0 +1,34 @@ +-- 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 default 1, amounts_expanded boolean 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 -- 2.39.2