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, coalesce(future_dates,-1),
47 coalesce(api_version,0),
48 show_commodity_by_default, default_commodity, show_comments_by_default,
49 coalesce(detected_version_pre_1_19,0), coalesce(detected_version_major,0),
50 coalesce(detected_version_minor,0)
54 create table options_new(profile varchar not null, name varchar not null, value varchar, primary key(profile, name));
56 insert into options_new(profile, name, value)
57 select profile, name, value from options;
60 create table accounts_new(
61 profile varchar not null,
62 name varchar not null,
63 name_upper varchar not null,
64 level integer not null,
66 expanded integer not null default 1,
67 amounts_expanded integer not null default 0,
68 generation integer not null default 0,
69 primary key(profile, name));
71 insert into accounts_new(profile, name, name_upper, level, parent_name,
72 expanded, amounts_expanded, generation)
73 select profile, name, name_upper, level, parent_name, expanded,
74 amounts_expanded, generation from accounts;
77 create table account_values_new(
78 profile varchar not null,
79 account varchar not null,
80 currency varchar not null default '',
82 generation integer not null default 0,
83 primary key(profile, account, currency));
85 insert into account_values_new(
86 profile, account, currency, value, generation)
87 select profile, account, currency, value, generation
90 -- description_history
91 create table description_history_new(
92 description varchar collate NOCASE not null,
93 description_upper varchar not null,
94 generation integer not null default 0,
95 primary key(description));
97 insert into description_history_new(description, description_upper, generation)
98 select description, description_upper, generation from description_history;
101 create table transactions_new(
102 profile varchar not null,
104 data_hash varchar not null,
105 year integer not null,
106 month integer not null,
107 day integer not null,
108 description varchar collate NOCASE not null,
110 generation integer not null default 0,
111 primary key(profile,id));
113 insert into transactions_new(profile, id, data_hash, year, month, day, description,
115 select profile, id, data_hash, year, month, day, description,
119 -- transaction_accounts
120 create table transaction_accounts_new(
121 profile varchar not null,
122 transaction_id integer not null,
123 order_no integer not null,
124 account_name varchar not null,
125 currency varchar not null default '',
126 amount real not null,
128 generation integer not null default 0,
129 primary key(profile, transaction_id, order_no),
130 foreign key (profile,account_name) references accounts(profile,name)
131 on delete cascade on update restrict,
132 foreign key(profile, transaction_id) references transactions(profile,id)
133 on delete cascade on update restrict);
135 insert into transaction_accounts_new(profile, transaction_id, order_no, account_name,
136 currency, amount, comment, generation)
137 select profile, transaction_id, order_no, account_name,
138 currency, amount, comment, generation
139 from transaction_accounts;
142 create table currencies_new(id integer not null primary key, name varchar not null,
143 position varchar not null, has_gap integer not null);
145 insert into currencies_new(id, name, position, has_gap)
146 select id, name, position, has_gap
151 drop table transaction_accounts;
152 drop table transactions;
153 drop table account_values;
155 drop table description_history;
158 drop table currencies;
161 alter table options_new rename to options;
162 alter table profiles_new rename to profiles;
163 alter table accounts_new rename to accounts;
164 alter table account_values_new rename to account_values;
165 alter table description_history_new rename to description_history;
166 alter table transactions_new rename to transactions;
167 alter table transaction_accounts_new rename to transaction_accounts;
168 alter table currencies_new rename to currencies;
171 create index fk_tran_acc_prof_acc on transaction_accounts(profile, account_name);
172 create unique index un_transactions_data_hash on transactions(profile,data_hash);
173 create index idx_transaction_description on transactions(description);
177 CREATE TABLE templates (
178 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
180 regular_expression TEXT NOT NULL,
182 transaction_description TEXT,
183 transaction_description_match_group INTEGER,
184 transaction_comment TEXT,
185 transaction_comment_match_group INTEGER,
187 date_year_match_group INTEGER,
189 date_month_match_group INTEGER,
191 date_day_match_group INTEGER,
192 is_fallback INTEGER NOT NULL DEFAULT 0);
193 CREATE TABLE template_accounts(
194 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
195 template_id INTEGER NOT NULL,
197 position INTEGER NOT NULL,
198 acc_match_group INTEGER,
200 currency_match_group INTEGER,
202 amount_match_group INTEGER,
204 comment_match_group INTEGER,
205 negate_amount INTEGER,
206 FOREIGN KEY(template_id) REFERENCES templates(id) ON UPDATE RESTRICT ON DELETE CASCADE,
207 FOREIGN KEY(currency) REFERENCES currencies(id) ON UPDATE RESTRICT ON DELETE RESTRICT);
208 create index fk_template_accounts_template on template_accounts(template_id);
209 create index fk_template_accounts_currency on template_accounts(currency);