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 41 to revision 58
19 create table profiles_new(
23 use_authentication integer not null,
26 order_no integer not null,
27 permit_posting integer not null default 0,
28 theme integer not null default -1,
29 preferred_accounts_filter varchar,
30 future_dates integer not null,
31 api_version integer not null,
32 show_commodity_by_default integer not null default 0,
33 default_commodity text,
34 show_comments_by_default integer not null default 1,
35 detected_version_pre_1_19 integer not null,
36 detected_version_major integer not null,
37 detected_version_minor integer not null,
40 insert into profiles_new(
41 uuid, name, url, use_authentication, auth_user, auth_password, order_no,
42 permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
43 show_commodity_by_default, default_commodity, show_comments_by_default,
44 detected_version_pre_1_19, detected_version_major, detected_version_minor)
45 select uuid, name, url, use_authentication, auth_user, auth_password, order_no,
46 permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
47 show_commodity_by_default, default_commodity, show_comments_by_default,
48 detected_version_pre_1_19, detected_version_major, detected_version_minor
52 create table options_new(profile varchar not null, name varchar not null, value varchar, primary key(profile, name));
54 insert into options_new(profile, name, value)
55 select profile, name, value from options;
58 create table accounts_new(
59 profile varchar not null,
60 name varchar not null,
61 name_upper varchar not null,
62 level integer not null,
64 expanded integer not null default 1,
65 amounts_expanded integer not null default 0,
66 generation integer not null default 0,
67 primary key(profile, name));
69 insert into accounts_new(profile, name, name_upper, level, parent_name,
70 expanded, amounts_expanded, generation)
71 select profile, name, name_upper, level, parent_name, expanded,
72 amounts_expanded, generation from accounts;
75 create table account_values_new(
76 profile varchar not null,
77 account varchar not null,
78 currency varchar not null default '',
80 generation integer not null default 0,
81 primary key(profile, account, currency));
83 insert into account_values_new(
84 profile, account, currency, value, generation)
85 select profile, account, currency, value, generation
88 -- description_history
89 create table description_history_new(
90 description varchar collate NOCASE not null,
91 description_upper varchar not null,
92 generation integer not null default 0,
93 primary key(description));
95 insert into description_history_new(description, description_upper, generation)
96 select description, description_upper, generation from description_history;
99 create table transactions_new(
100 profile varchar not null,
102 data_hash varchar not null,
103 year integer not null,
104 month integer not null,
105 day integer not null,
106 description varchar collate NOCASE not null,
108 generation integer not null default 0,
109 primary key(profile,id));
111 insert into transactions_new(profile, id, data_hash, year, month, day, description,
113 select profile, id, data_hash, year, month, day, description,
117 -- transaction_accounts
118 create table transaction_accounts_new(
119 profile varchar not null,
120 transaction_id integer not null,
121 order_no integer not null,
122 account_name varchar not null,
123 currency varchar not null default '',
124 amount real not null,
126 generation integer not null default 0,
127 primary key(profile, transaction_id, order_no),
128 foreign key (profile,account_name) references accounts(profile,name)
129 on delete cascade on update restrict,
130 foreign key(profile, transaction_id) references transactions(profile,id)
131 on delete cascade on update restrict);
133 insert into transaction_accounts_new(profile, transaction_id, order_no, account_name,
134 currency, amount, comment, generation)
135 select profile, transaction_id, order_no, account_name,
136 currency, amount, comment, generation
137 from transaction_accounts;
140 create table currencies_new(id integer not null primary key, name varchar not null,
141 position varchar not null, has_gap integer not null);
143 insert into currencies_new(id, name, position, has_gap)
144 select id, name, position, has_gap
149 drop table transaction_accounts;
150 drop table transactions;
151 drop table account_values;
153 drop table description_history;
156 drop table currencies;
159 alter table options_new rename to options;
160 alter table profiles_new rename to profiles;
161 alter table accounts_new rename to accounts;
162 alter table account_values_new rename to account_values;
163 alter table description_history_new rename to description_history;
164 alter table transactions_new rename to transactions;
165 alter table transaction_accounts_new rename to transaction_accounts;
166 alter table currencies_new rename to currencies;
169 create index fk_tran_acc_prof_acc on transaction_accounts(profile, account_name);
170 create unique index un_transactions_data_hash on transactions(profile,data_hash);
171 create index idx_transaction_description on transactions(description);
175 CREATE TABLE templates (
176 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
178 regular_expression TEXT NOT NULL,
180 transaction_description TEXT,
181 transaction_description_match_group INTEGER,
182 transaction_comment TEXT,
183 transaction_comment_match_group INTEGER,
185 date_year_match_group INTEGER,
187 date_month_match_group INTEGER,
189 date_day_match_group INTEGER,
190 is_fallback INTEGER NOT NULL DEFAULT 0);
191 CREATE TABLE template_accounts(
192 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
193 template_id INTEGER NOT NULL,
195 position INTEGER NOT NULL,
196 acc_match_group INTEGER,
198 currency_match_group INTEGER,
200 amount_match_group INTEGER,
202 comment_match_group INTEGER,
203 negate_amount INTEGER,
204 FOREIGN KEY(template_id) REFERENCES templates(id) ON UPDATE RESTRICT ON DELETE CASCADE,
205 FOREIGN KEY(currency) REFERENCES currencies(id) ON UPDATE RESTRICT ON DELETE RESTRICT);
206 create index fk_template_accounts_template on template_accounts(template_id);
207 create index fk_template_accounts_currency on template_accounts(currency);