1 -- Copyright © 2021 Damyan Ivanov.
2 -- This file is part of MoLe.
3 -- MoLe is free software: you can distribute it and/or modify it
4 -- under the term of the GNU General Public License as published by
5 -- the Free Software Foundation, either version 3 of the License, or
6 -- (at your opinion), any later version.
8 -- MoLe is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License terms for details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with MoLe. If not, see <https://www.gnu.org/licenses/>.
16 -- migrate from revision 58 to revision 59
18 -- pragmas need to be outside of transaction control
19 -- foreign_keys is needed so that foreign key constraints are redirected
22 pragma foreign_keys = on;
27 CREATE TABLE profiles_new (
28 id INTEGER NOT NULL PRIMARY KEY,
32 use_authentication INTEGER NOT NULL,
35 order_no INTEGER NOT NULL,
36 permit_posting INTEGER NOT NULL,
37 theme INTEGER NOT NULL DEFAULT -1,
38 preferred_accounts_filter TEXT,
39 future_dates INTEGER NOT NULL,
40 api_version INTEGER NOT NULL,
41 show_commodity_by_default INTEGER NOT NULL,
42 default_commodity TEXT,
43 show_comments_by_default INTEGER NOT NULL DEFAULT 1,
44 detected_version_pre_1_19 INTEGER NOT NULL,
45 detected_version_major INTEGER NOT NULL,
46 detected_version_minor INTEGER NOT NULL);
48 insert into profiles_new(
49 deprecated_uuid, name, url, use_authentication, auth_user, auth_password,
50 order_no, permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
51 show_commodity_by_default, default_commodity, show_comments_by_default, detected_version_pre_1_19,
52 detected_version_major, detected_version_minor)
53 select uuid, name, url, use_authentication, auth_user, auth_password,
54 order_no, permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
55 show_commodity_by_default, default_commodity, show_comments_by_default, detected_version_pre_1_19,
56 detected_version_major, detected_version_minor
60 create table accounts_new(
61 id integer primary key not null,
62 profile_id integer not null references profiles_new(id) on delete cascade on update restrict,
63 level INTEGER NOT NULL,
65 name_upper TEXT NOT NULL,
67 expanded INTEGER NOT NULL DEFAULT 1,
68 amounts_expanded INTEGER NOT NULL DEFAULT 0,
69 generation INTEGER NOT NULL DEFAULT 0);
71 insert into accounts_new(profile_id, level, name, name_upper, parent_name, expanded, amounts_expanded, generation)
72 select p.id, a.level, a.name, a.name_upper, a.parent_name, a.expanded, a.amounts_expanded, a.generation
74 join accounts a on a.profile=p.deprecated_uuid;
77 alter table accounts_new rename to accounts;
80 alter table profiles_new rename to profiles;
82 create index fk_account_profile on accounts(profile_id);
83 create unique index un_account_name on accounts(profile_id, name);
86 create table options_new(
88 profile_id integer not null,
90 primary key(profile_id,name));
92 insert into options_new(name, profile_id, value)
93 select o.name, p.id, o.value
95 join profiles p on p.deprecated_uuid = o.profile;
98 alter table options_new rename to options;
101 set name='profile_id'
102 , value=(select p.id from profiles p where p.deprecated_uuid=options.value)
103 where name='profile_uuid';
106 create table account_values_new(
107 id integer not null primary key,
108 account_id integer not null references accounts(id) on delete cascade on update restrict,
109 currency text not null default '',
111 generation integer not null default 0);
113 insert into account_values_new(account_id, currency, value, generation)
114 select a.id, av.currency, av.value, av.generation
115 from account_values av
116 join profiles p on p.deprecated_uuid=av.profile
117 join accounts a on a.profile_id = p.id and a.name = av.account;
119 drop table account_values;
120 alter table account_values_new rename to account_values;
122 create index fk_account_value_acc on account_values(account_id);
123 create unique index un_account_values on account_values(account_id, currency);
126 create table transactions_new(
127 id integer not null primary key,
128 profile_id integer not null references profiles(id) on delete cascade on update restrict,
129 ledger_id integer not null,
130 description text not null,
131 year integer not null,
132 month integer not null,
133 day integer not null,
135 data_hash text not null,
136 generation integer not null);
138 insert into transactions_new(profile_id, ledger_id, description, year, month, day, comment, data_hash, generation)
139 select p.id, t.id, t.description, t.year, t.month, t.day, t.comment, t.data_hash, t.generation
141 join profiles p on p.deprecated_uuid = t.profile;
143 -- transaction_accounts
144 create table transaction_accounts_new(
145 id integer not null primary key,
146 transaction_id integer not null references transactions_new(id) on delete cascade on update restrict,
147 order_no integer not null,
148 account_name text not null,
149 currency text not null default '',
150 amount real not null,
152 generation integer not null default 0);
154 insert into transaction_accounts_new(transaction_id, order_no, account_name,
155 currency, amount, comment, generation)
156 select ta.transaction_id, ta.order_no, ta.account_name, ta.currency, ta.amount, ta.comment, ta.generation
157 from transaction_accounts ta;
159 drop table transaction_accounts;
160 alter table transaction_accounts_new rename to transaction_accounts;
162 drop table transactions;
163 alter table transactions_new rename to transactions;
165 create index idx_transaction_description on transactions(description);
166 create unique index un_transactions_ledger_id on transactions(profile_id, ledger_id);
167 create index fk_transaction_profile on transactions(profile_id);
169 create unique index un_transaction_accounts on transaction_accounts(transaction_id, order_no);
170 create index fk_trans_acc_trans on transaction_accounts(transaction_id);