From 390a653a4424bd146f73bca3e421a0be252ee443 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Thu, 11 Feb 2021 22:12:51 +0000 Subject: [PATCH] let SQL revision files control the transaction because the foreign_keys pragma can't be used inside a transaction --- .../net/ktnx/mobileledger/utils/MobileLedgerDatabase.java | 5 ----- app/src/main/res/raw/create_db.sql | 4 ++++ app/src/main/res/raw/sql_0.sql | 4 ++++ app/src/main/res/raw/sql_1.sql | 4 ++++ app/src/main/res/raw/sql_10.sql | 4 ++++ app/src/main/res/raw/sql_11.sql | 4 ++++ app/src/main/res/raw/sql_12.sql | 4 ++++ app/src/main/res/raw/sql_13.sql | 4 ++++ app/src/main/res/raw/sql_14.sql | 4 ++++ app/src/main/res/raw/sql_15.sql | 4 ++++ app/src/main/res/raw/sql_16.sql | 3 +++ app/src/main/res/raw/sql_17.sql | 4 ++++ app/src/main/res/raw/sql_18.sql | 4 ++++ app/src/main/res/raw/sql_19.sql | 4 ++++ app/src/main/res/raw/sql_2.sql | 4 ++++ app/src/main/res/raw/sql_20.sql | 4 ++++ app/src/main/res/raw/sql_21.sql | 4 ++++ app/src/main/res/raw/sql_22.sql | 4 ++++ app/src/main/res/raw/sql_23.sql | 4 ++++ app/src/main/res/raw/sql_24.sql | 4 ++++ app/src/main/res/raw/sql_25.sql | 4 ++++ app/src/main/res/raw/sql_26.sql | 4 ++++ app/src/main/res/raw/sql_27.sql | 4 ++++ app/src/main/res/raw/sql_28.sql | 3 +++ app/src/main/res/raw/sql_29.sql | 5 +++++ app/src/main/res/raw/sql_3.sql | 4 ++++ app/src/main/res/raw/sql_30.sql | 5 +++++ app/src/main/res/raw/sql_31.sql | 5 +++++ app/src/main/res/raw/sql_32.sql | 5 +++++ app/src/main/res/raw/sql_33.sql | 5 +++++ app/src/main/res/raw/sql_34.sql | 5 +++++ app/src/main/res/raw/sql_35.sql | 5 +++++ app/src/main/res/raw/sql_36.sql | 5 +++++ app/src/main/res/raw/sql_37.sql | 5 +++++ app/src/main/res/raw/sql_38.sql | 5 +++++ app/src/main/res/raw/sql_39.sql | 5 +++++ app/src/main/res/raw/sql_4.sql | 4 ++++ app/src/main/res/raw/sql_40.sql | 4 ++++ app/src/main/res/raw/sql_41.sql | 5 +++++ app/src/main/res/raw/sql_42.sql | 4 ++++ app/src/main/res/raw/sql_43.sql | 3 +++ app/src/main/res/raw/sql_44.sql | 4 ++++ app/src/main/res/raw/sql_45.sql | 4 ++++ app/src/main/res/raw/sql_46.sql | 4 ++++ app/src/main/res/raw/sql_47.sql | 4 ++++ app/src/main/res/raw/sql_48.sql | 4 ++++ app/src/main/res/raw/sql_49.sql | 4 ++++ app/src/main/res/raw/sql_5.sql | 4 ++++ app/src/main/res/raw/sql_50.sql | 3 +++ app/src/main/res/raw/sql_51.sql | 7 +++++++ app/src/main/res/raw/sql_52.sql | 4 ++++ app/src/main/res/raw/sql_53.sql | 3 +++ app/src/main/res/raw/sql_54.sql | 4 ++++ app/src/main/res/raw/sql_55.sql | 3 +++ app/src/main/res/raw/sql_6.sql | 4 ++++ app/src/main/res/raw/sql_7.sql | 4 ++++ app/src/main/res/raw/sql_8.sql | 4 ++++ app/src/main/res/raw/sql_9.sql | 4 ++++ 58 files changed, 237 insertions(+), 5 deletions(-) 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 32269fea..be31ebdf 100644 --- a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java +++ b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java @@ -83,7 +83,6 @@ public class MobileLedgerDatabase extends SQLiteOpenHelper { int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName()); if (res_id == 0) throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file)); - db.beginTransaction(); try (InputStream res = rm.openRawResource(res_id)) { debug("db", "Applying " + rev_file); InputStreamReader isr = new InputStreamReader(res); @@ -129,14 +128,10 @@ public class MobileLedgerDatabase extends SQLiteOpenHelper { throw new RuntimeException( String.format("Error applying %s: EOF after continuation", rev_file)); - db.setTransactionSuccessful(); } catch (IOException e) { Log.e("db", String.format("Error opening raw resource for %s", rev_file)); e.printStackTrace(); } - finally { - db.endTransaction(); - } } } diff --git a/app/src/main/res/raw/create_db.sql b/app/src/main/res/raw/create_db.sql index 6b56953f..e77ac570 100644 --- a/app/src/main/res/raw/create_db.sql +++ b/app/src/main/res/raw/create_db.sql @@ -12,6 +12,9 @@ -- -- 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, 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); @@ -34,4 +37,5 @@ CREATE TABLE template_accounts(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, te 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 diff --git a/app/src/main/res/raw/sql_0.sql b/app/src/main/res/raw/sql_0.sql index 3e4feca2..cf9cca97 100644 --- a/app/src/main/res/raw/sql_0.sql +++ b/app/src/main/res/raw/sql_0.sql @@ -13,6 +13,8 @@ -- 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); @@ -20,3 +22,5 @@ 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 index b5d79f9a..23243c31 100644 --- a/app/src/main/res/raw/sql_1.sql +++ b/app/src/main/res/raw/sql_1.sql @@ -13,5 +13,9 @@ -- 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 index 30962d41..e6ceb4e3 100644 --- a/app/src/main/res/raw/sql_10.sql +++ b/app/src/main/res/raw/sql_10.sql @@ -13,5 +13,9 @@ -- 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 index eec8c3f5..0bf366b4 100644 --- a/app/src/main/res/raw/sql_11.sql +++ b/app/src/main/res/raw/sql_11.sql @@ -13,5 +13,9 @@ -- 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 index 4ddd98dc..a9baf115 100644 --- a/app/src/main/res/raw/sql_12.sql +++ b/app/src/main/res/raw/sql_12.sql @@ -13,4 +13,8 @@ -- 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 index 9b08d717..522eb8d9 100644 --- a/app/src/main/res/raw/sql_13.sql +++ b/app/src/main/res/raw/sql_13.sql @@ -13,6 +13,8 @@ -- 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; @@ -35,3 +37,5 @@ 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 index 4f5ec4c8..ac47837e 100644 --- a/app/src/main/res/raw/sql_14.sql +++ b/app/src/main/res/raw/sql_14.sql @@ -13,6 +13,8 @@ -- 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; -- @@ -21,3 +23,5 @@ 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 index 8ce967c5..d134676c 100644 --- a/app/src/main/res/raw/sql_15.sql +++ b/app/src/main/res/raw/sql_15.sql @@ -13,6 +13,8 @@ -- 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); @@ -23,3 +25,5 @@ create table options(profile varchar not null, name varchar not null, value varc 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 index 0c71dd88..3bf8cc96 100644 --- a/app/src/main/res/raw/sql_16.sql +++ b/app/src/main/res/raw/sql_16.sql @@ -13,5 +13,8 @@ -- 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_17.sql b/app/src/main/res/raw/sql_17.sql index cfe1bb22..2da2ae32 100644 --- a/app/src/main/res/raw/sql_17.sql +++ b/app/src/main/res/raw/sql_17.sql @@ -13,5 +13,9 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + alter table profiles add permit_posting boolean default 0; update profiles set permit_posting = 1; + +COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_18.sql b/app/src/main/res/raw/sql_18.sql index e74471ea..abe720bd 100644 --- a/app/src/main/res/raw/sql_18.sql +++ b/app/src/main/res/raw/sql_18.sql @@ -13,5 +13,9 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + alter table profiles add theme integer default -1; update profiles set theme = -1; + +COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_19.sql b/app/src/main/res/raw/sql_19.sql index 7221d57f..d278070d 100644 --- a/app/src/main/res/raw/sql_19.sql +++ b/app/src/main/res/raw/sql_19.sql @@ -13,5 +13,9 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + alter table accounts add expanded default 1; update accounts set expanded = 1; + +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 index e7f8041c..27307216 100644 --- a/app/src/main/res/raw/sql_2.sql +++ b/app/src/main/res/raw/sql_2.sql @@ -13,4 +13,8 @@ -- 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_20.sql b/app/src/main/res/raw/sql_20.sql index 8e2c1eca..7510b7dc 100644 --- a/app/src/main/res/raw/sql_20.sql +++ b/app/src/main/res/raw/sql_20.sql @@ -13,7 +13,11 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + delete from accounts where not exists (select 1 from profiles where uuid = profile); delete from account_values where not exists (select 1 from profiles where uuid = profile); delete from transactions where not exists (select 1 from profiles where uuid = profile); delete from transaction_accounts where not exists (select 1 from profiles where uuid = profile); + +COMMIT TRANSACTION; \ No newline at end of file diff --git a/app/src/main/res/raw/sql_21.sql b/app/src/main/res/raw/sql_21.sql index 70602319..d7c08fdd 100644 --- a/app/src/main/res/raw/sql_21.sql +++ b/app/src/main/res/raw/sql_21.sql @@ -13,4 +13,8 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + alter table accounts add amounts_expanded boolean default 0; + +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 index 7b32f531..a1780f02 100644 --- a/app/src/main/res/raw/sql_22.sql +++ b/app/src/main/res/raw/sql_22.sql @@ -13,4 +13,8 @@ -- 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 index 5ceef0d5..ea3a9e85 100644 --- a/app/src/main/res/raw/sql_23.sql +++ b/app/src/main/res/raw/sql_23.sql @@ -13,4 +13,8 @@ -- 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 index 2ee78172..268922d7 100644 --- a/app/src/main/res/raw/sql_24.sql +++ b/app/src/main/res/raw/sql_24.sql @@ -13,4 +13,8 @@ -- 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_25.sql b/app/src/main/res/raw/sql_25.sql index ae9bb47f..6a02fcbe 100644 --- a/app/src/main/res/raw/sql_25.sql +++ b/app/src/main/res/raw/sql_25.sql @@ -13,4 +13,8 @@ -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . +BEGIN TRANSACTION; + 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 diff --git a/app/src/main/res/raw/sql_26.sql b/app/src/main/res/raw/sql_26.sql index 76749836..e44ec03b 100644 --- a/app/src/main/res/raw/sql_26.sql +++ b/app/src/main/res/raw/sql_26.sql @@ -13,4 +13,8 @@ -- 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 index 86d993f8..de643e48 100644 --- a/app/src/main/res/raw/sql_27.sql +++ b/app/src/main/res/raw/sql_27.sql @@ -13,4 +13,8 @@ -- 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 index 8ec5fb6b..f392fda1 100644 --- a/app/src/main/res/raw/sql_28.sql +++ b/app/src/main/res/raw/sql_28.sql @@ -13,5 +13,8 @@ -- 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 index 16164b87..a134a225 100644 --- a/app/src/main/res/raw/sql_29.sql +++ b/app/src/main/res/raw/sql_29.sql @@ -12,4 +12,9 @@ -- -- 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 index 17ed3dbd..7994a7aa 100644 --- a/app/src/main/res/raw/sql_3.sql +++ b/app/src/main/res/raw/sql_3.sql @@ -13,7 +13,11 @@ -- 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 index a559673e..79e142bc 100644 --- a/app/src/main/res/raw/sql_30.sql +++ b/app/src/main/res/raw/sql_30.sql @@ -12,4 +12,9 @@ -- -- 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_31.sql b/app/src/main/res/raw/sql_31.sql index 49b98ba4..d17eeea4 100644 --- a/app/src/main/res/raw/sql_31.sql +++ b/app/src/main/res/raw/sql_31.sql @@ -12,4 +12,9 @@ -- -- 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_comments_by_default boolean default 0; + +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 index fecf2ad0..860b0dd4 100644 --- a/app/src/main/res/raw/sql_32.sql +++ b/app/src/main/res/raw/sql_32.sql @@ -12,4 +12,9 @@ -- -- 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 index da442f8a..c915ce2d 100644 --- a/app/src/main/res/raw/sql_33.sql +++ b/app/src/main/res/raw/sql_33.sql @@ -12,4 +12,9 @@ -- -- 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_34.sql b/app/src/main/res/raw/sql_34.sql index e03541d4..041e3cf5 100644 --- a/app/src/main/res/raw/sql_34.sql +++ b/app/src/main/res/raw/sql_34.sql @@ -12,6 +12,9 @@ -- -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . + +BEGIN TRANSACTION; + 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; @@ -25,3 +28,5 @@ create table transactions_2(profile varchar not null, id integer not null, data_ 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; drop table transactions; alter table transactions_2 rename to transactions; + +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 index 0a77c3eb..b8dc44d8 100644 --- a/app/src/main/res/raw/sql_35.sql +++ b/app/src/main/res/raw/sql_35.sql @@ -12,7 +12,12 @@ -- -- 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 index 378eddc7..a7defcb7 100644 --- a/app/src/main/res/raw/sql_36.sql +++ b/app/src/main/res/raw/sql_36.sql @@ -12,7 +12,12 @@ -- -- 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 index 60b6e930..5f57308d 100644 --- a/app/src/main/res/raw/sql_37.sql +++ b/app/src/main/res/raw/sql_37.sql @@ -12,6 +12,11 @@ -- -- 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 index 299907f9..19a18bde 100644 --- a/app/src/main/res/raw/sql_38.sql +++ b/app/src/main/res/raw/sql_38.sql @@ -12,8 +12,13 @@ -- -- 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 index 92582fac..4f5db2ac 100644 --- a/app/src/main/res/raw/sql_39.sql +++ b/app/src/main/res/raw/sql_39.sql @@ -12,8 +12,13 @@ -- -- 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 index e2fce110..5ae73aad 100644 --- a/app/src/main/res/raw/sql_4.sql +++ b/app/src/main/res/raw/sql_4.sql @@ -13,5 +13,9 @@ -- 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 index b2168179..0d6d5da2 100644 --- a/app/src/main/res/raw/sql_40.sql +++ b/app/src/main/res/raw/sql_40.sql @@ -13,5 +13,9 @@ -- 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_41.sql b/app/src/main/res/raw/sql_41.sql index dea27a77..1c9936d3 100644 --- a/app/src/main/res/raw/sql_41.sql +++ b/app/src/main/res/raw/sql_41.sql @@ -12,6 +12,11 @@ -- -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . + +BEGIN TRANSACTION; + alter table profiles add detected_version_pre_1_19 boolean; alter table profiles add detected_version_major integer; alter table profiles add detected_version_minor integer; + +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 index 72959503..a678599c 100644 --- a/app/src/main/res/raw/sql_42.sql +++ b/app/src/main/res/raw/sql_42.sql @@ -13,7 +13,11 @@ -- 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 index 60e3147b..fc1db99e 100644 --- a/app/src/main/res/raw/sql_43.sql +++ b/app/src/main/res/raw/sql_43.sql @@ -13,5 +13,8 @@ -- 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 index b6d1cc08..15d54bd4 100644 --- a/app/src/main/res/raw/sql_44.sql +++ b/app/src/main/res/raw/sql_44.sql @@ -13,8 +13,12 @@ -- 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 index 3220e4ea..ffdf3467 100644 --- a/app/src/main/res/raw/sql_45.sql +++ b/app/src/main/res/raw/sql_45.sql @@ -13,6 +13,8 @@ -- 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; @@ -20,3 +22,5 @@ create table patterns(id integer not null primary key, name varchar not null, po 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 index c4f04597..031d201d 100644 --- a/app/src/main/res/raw/sql_46.sql +++ b/app/src/main/res/raw/sql_46.sql @@ -13,6 +13,8 @@ -- 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; @@ -20,3 +22,5 @@ create table patterns(id integer not null primary key, name text not null, posit 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 index e754a54f..0fb4ea9a 100644 --- a/app/src/main/res/raw/sql_47.sql +++ b/app/src/main/res/raw/sql_47.sql @@ -13,6 +13,8 @@ -- 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; @@ -20,3 +22,5 @@ create table patterns(id INTEGER not null primary key, name TEXT not null, posit 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 index fe36bb4a..99aa27d1 100644 --- a/app/src/main/res/raw/sql_48.sql +++ b/app/src/main/res/raw/sql_48.sql @@ -13,7 +13,11 @@ -- 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 index 2852a8b8..549c4a48 100644 --- a/app/src/main/res/raw/sql_49.sql +++ b/app/src/main/res/raw/sql_49.sql @@ -15,9 +15,13 @@ 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 index 84db5529..b0bb044a 100644 --- a/app/src/main/res/raw/sql_5.sql +++ b/app/src/main/res/raw/sql_5.sql @@ -13,5 +13,9 @@ -- 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 index 33395f2f..5dce5c7f 100644 --- a/app/src/main/res/raw/sql_50.sql +++ b/app/src/main/res/raw/sql_50.sql @@ -13,5 +13,8 @@ -- 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 index 1e84c56b..9536e684 100644 --- a/app/src/main/res/raw/sql_51.sql +++ b/app/src/main/res/raw/sql_51.sql @@ -12,8 +12,11 @@ -- -- 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; @@ -22,3 +25,7 @@ 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 index 86e3cb2f..0d85d687 100644 --- a/app/src/main/res/raw/sql_52.sql +++ b/app/src/main/res/raw/sql_52.sql @@ -13,5 +13,9 @@ -- 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 index 572156d3..a541b852 100644 --- a/app/src/main/res/raw/sql_53.sql +++ b/app/src/main/res/raw/sql_53.sql @@ -13,5 +13,8 @@ -- 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 index df6b44fc..f8e13ba9 100644 --- a/app/src/main/res/raw/sql_54.sql +++ b/app/src/main/res/raw/sql_54.sql @@ -15,6 +15,8 @@ -- 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; @@ -23,3 +25,5 @@ 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 index fb77862a..780b451c 100644 --- a/app/src/main/res/raw/sql_55.sql +++ b/app/src/main/res/raw/sql_55.sql @@ -14,6 +14,7 @@ -- 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; @@ -22,3 +23,5 @@ 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_6.sql b/app/src/main/res/raw/sql_6.sql index 4d2a54cd..dd4ff227 100644 --- a/app/src/main/res/raw/sql_6.sql +++ b/app/src/main/res/raw/sql_6.sql @@ -13,6 +13,8 @@ -- 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; @@ -20,3 +22,5 @@ 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 index 780e4d4d..299a9e7f 100644 --- a/app/src/main/res/raw/sql_7.sql +++ b/app/src/main/res/raw/sql_7.sql @@ -13,5 +13,9 @@ -- 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 index b4a85e52..6e9ae6c5 100644 --- a/app/src/main/res/raw/sql_8.sql +++ b/app/src/main/res/raw/sql_8.sql @@ -13,5 +13,9 @@ -- 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 index 16a78a83..6f290d76 100644 --- a/app/src/main/res/raw/sql_9.sql +++ b/app/src/main/res/raw/sql_9.sql @@ -13,6 +13,8 @@ -- 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); @@ -22,3 +24,5 @@ create table transactions(id integer primary key, date varchar, description varc 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.5