From d5218337e111a377a3661f0ae4a5049edc055705 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Fri, 19 Feb 2021 16:37:56 +0000 Subject: [PATCH] describe transaction_accounts to Room --- .../net.ktnx.mobileledger.db.DB/58.json | 97 +++++++++++++++- .../java/net/ktnx/mobileledger/db/DB.java | 2 +- .../mobileledger/db/TransactionAccount.java | 104 ++++++++++++++++++ app/src/main/res/raw/create_db.sql | 17 ++- app/src/main/res/raw/sql_58.sql | 25 +++++ 5 files changed, 240 insertions(+), 5 deletions(-) create mode 100644 app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/58.json b/app/schemas/net.ktnx.mobileledger.db.DB/58.json index c4e61313..fd3239de 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": "00d9b541473347c65a90e47ce9072436", + "identityHash": "249d4507f837a17defe32f1bc9dc2de6", "entities": [ { "tableName": "templates", @@ -662,12 +662,105 @@ } ], "foreignKeys": [] + }, + { + "tableName": "transaction_accounts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`profile` TEXT NOT NULL, `transaction_id` INTEGER NOT NULL, `order_no` INTEGER NOT NULL, `account_name` TEXT NOT NULL, `currency` TEXT NOT NULL DEFAULT '', `amount` REAL NOT NULL, `comment` TEXT, `generation` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`profile`, `transaction_id`, `order_no`), FOREIGN KEY(`profile`, `transaction_id`) REFERENCES `transactions`(`profile`, `id`) ON UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY(`profile`, `account_name`) REFERENCES `accounts`(`profile`, `name`) ON UPDATE RESTRICT ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "profile", + "columnName": "profile", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "transactionId", + "columnName": "transaction_id", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "orderNo", + "columnName": "order_no", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "accountName", + "columnName": "account_name", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "currency", + "columnName": "currency", + "affinity": "TEXT", + "notNull": true, + "defaultValue": "''" + }, + { + "fieldPath": "amount", + "columnName": "amount", + "affinity": "REAL", + "notNull": true + }, + { + "fieldPath": "comment", + "columnName": "comment", + "affinity": "TEXT", + "notNull": false + }, + { + "fieldPath": "generation", + "columnName": "generation", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "columnNames": [ + "profile", + "transaction_id", + "order_no" + ], + "autoGenerate": false + }, + "indices": [], + "foreignKeys": [ + { + "table": "transactions", + "onDelete": "CASCADE", + "onUpdate": "RESTRICT", + "columns": [ + "profile", + "transaction_id" + ], + "referencedColumns": [ + "profile", + "id" + ] + }, + { + "table": "accounts", + "onDelete": "CASCADE", + "onUpdate": "RESTRICT", + "columns": [ + "profile", + "account_name" + ], + "referencedColumns": [ + "profile", + "name" + ] + } + ] } ], "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, '00d9b541473347c65a90e47ce9072436')" + "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '249d4507f837a17defe32f1bc9dc2de6')" ] } } \ No newline at end of file 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 630c5b27..e5cfb5d4 100644 --- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java +++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java @@ -34,7 +34,7 @@ import net.ktnx.mobileledger.utils.MobileLedgerDatabase; @Database(version = 58, entities = {TemplateHeader.class, TemplateAccount.class, Currency.class, Account.class, Profile.class, Option.class, AccountValue.class, DescriptionHistory.class, - Transaction.class + Transaction.class, TransactionAccount.class }) abstract public class DB extends RoomDatabase { private static DB instance; diff --git a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java new file mode 100644 index 00000000..671e5d01 --- /dev/null +++ b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java @@ -0,0 +1,104 @@ +/* + * 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; +import androidx.room.ForeignKey; + +@Entity(tableName = "transaction_accounts", primaryKeys = {"profile", "transaction_id", "order_no"}, + foreignKeys = {@ForeignKey(entity = Transaction.class, parentColumns = {"profile", "id"}, + childColumns = {"profile", "transaction_id"}, + onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.RESTRICT), + @ForeignKey(entity = Account.class, parentColumns = {"profile", "name"}, + childColumns = {"profile", "account_name"}, + onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.RESTRICT) + }) +public class TransactionAccount { + @ColumnInfo + @NonNull + private String profile; + @ColumnInfo(name = "transaction_id") + private int transactionId; + @ColumnInfo(name = "order_no") + private int orderNo; + @ColumnInfo(name = "account_name") + @NonNull + private String accountName; + @ColumnInfo(defaultValue = "") + @NonNull + private String currency = ""; + @ColumnInfo + private float amount; + @ColumnInfo + private String comment; + @ColumnInfo(defaultValue = "0") + private int generation = 0; + @NonNull + public String getProfile() { + return profile; + } + public void setProfile(@NonNull String profile) { + this.profile = profile; + } + public int getTransactionId() { + return transactionId; + } + public void setTransactionId(int transactionId) { + this.transactionId = transactionId; + } + public int getOrderNo() { + return orderNo; + } + public void setOrderNo(int orderNo) { + this.orderNo = orderNo; + } + @NonNull + public String getAccountName() { + return accountName; + } + public void setAccountName(@NonNull String accountName) { + this.accountName = accountName; + } + @NonNull + public String getCurrency() { + return currency; + } + public void setCurrency(@NonNull String currency) { + this.currency = currency; + } + public float getAmount() { + return amount; + } + public void setAmount(float amount) { + this.amount = amount; + } + public String getComment() { + return comment; + } + public void setComment(String comment) { + this.comment = comment; + } + public int getGeneration() { + return generation; + } + public void setGeneration(int generation) { + this.generation = generation; + } +} diff --git a/app/src/main/res/raw/create_db.sql b/app/src/main/res/raw/create_db.sql index 62582672..5df18e0b 100644 --- a/app/src/main/res/raw/create_db.sql +++ b/app/src/main/res/raw/create_db.sql @@ -66,8 +66,21 @@ create table transactions( create unique index un_transactions_data_hash on transactions(profile,data_hash); create index idx_transaction_description on transactions(description); -create table transaction_accounts(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 decimal not null, comment varchar, generation integer 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)); -create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); +create table transaction_accounts( + 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); + create table currencies(id integer not null primary key, name varchar not null, position varchar not null, has_gap integer not null); 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); diff --git a/app/src/main/res/raw/sql_58.sql b/app/src/main/res/raw/sql_58.sql index b2b687c5..ad28d1a5 100644 --- a/app/src/main/res/raw/sql_58.sql +++ b/app/src/main/res/raw/sql_58.sql @@ -119,6 +119,31 @@ 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; -- 2.39.2