229 lines
5.2 KiB
Plaintext
229 lines
5.2 KiB
Plaintext
begin;
|
|
|
|
create table weight (
|
|
id integer primary key,
|
|
date datetime default (datetime('now')),
|
|
weight real not null
|
|
);
|
|
|
|
create index weightDateIdx on weight(date);
|
|
create index weightDailyIdx on weight(strftime('%Y-%m-%d', date));
|
|
create index weightWeeklyIdx on weight(strftime('%Y-%W', date));
|
|
create index weightMonthlyIdx on weight(strftime('%Y-%m', date));
|
|
create index weightYearlyIdx on weight(strftime('%Y', date));
|
|
|
|
drop view if exists weightView;
|
|
drop view if exists weightDaily;
|
|
drop view if exists weightWeekly;
|
|
drop view if exists weightMonthly;
|
|
drop view if exists weightYearly;
|
|
|
|
create view weightView as
|
|
select id,
|
|
date,
|
|
round(weight, 2) as weight
|
|
from weight
|
|
order by date desc;
|
|
|
|
create view weightDaily as
|
|
select strftime('%Y-%m-%d', date) as period,
|
|
round(avg(weight), 2) as amount
|
|
from weight
|
|
group by strftime('%Y-%m-%d', date)
|
|
order by date desc;
|
|
|
|
create view weightWeekly as
|
|
select strftime('%Y-%W', date) as period,
|
|
round(avg(weight), 2) as amount
|
|
from weight
|
|
group by strftime('%Y-%W', date)
|
|
order by date desc;
|
|
|
|
create view weightMonthly as
|
|
select strftime('%Y-%m', date) as period,
|
|
round(avg(weight), 2) as amount
|
|
from weight
|
|
group by strftime('%Y-%m', date)
|
|
order by date desc;
|
|
|
|
create view weightYearly as
|
|
select strftime('%Y', date) as period,
|
|
round(avg(weight), 2) as amount
|
|
from weight
|
|
group by strftime('%Y', date)
|
|
order by date desc;
|
|
|
|
create table food(
|
|
id integer primary key,
|
|
date datetime default (datetime('now')),
|
|
food varchar not null,
|
|
description varchar,
|
|
amount real not null,
|
|
per100 real default 0,
|
|
energy generated always as (coalesce(amount, 0) * coalesce(per100, 0) / 100) stored
|
|
);
|
|
|
|
create virtual table foodfix using spellfix1;
|
|
insert into foodfix (word, rank)
|
|
select food as word,
|
|
count(*) as rank
|
|
from food
|
|
group by food;
|
|
|
|
drop trigger if exists food_foodfix_insert;
|
|
create trigger food_foodfix_insert AFTER
|
|
insert on food for EACH row
|
|
begin
|
|
update foodfix
|
|
set rank = rank + 1
|
|
where word = new.food;
|
|
insert into foodfix (word, rank)
|
|
select new.food,
|
|
1
|
|
where not exists (
|
|
select 1
|
|
from foodfix
|
|
where word = new.food
|
|
);
|
|
end;
|
|
|
|
drop trigger if exists food_foodfix_delete;
|
|
create trigger food_foodfix_delete AFTER
|
|
delete on food for EACH row
|
|
begin
|
|
update foodfix
|
|
set rank = rank - 1
|
|
where word = old.food;
|
|
|
|
delete from foodfix
|
|
where word = old.food
|
|
and rank <= 0;
|
|
end;
|
|
|
|
drop trigger if exists food_foodfix_update;
|
|
create trigger food_foodfix_update AFTER
|
|
update on food for EACH row
|
|
begin
|
|
update foodfix
|
|
set rank = rank - 1
|
|
where word = old.food;
|
|
|
|
delete from foodfix
|
|
where word = old.food
|
|
and rank <= 0;
|
|
update foodfix
|
|
set rank = rank + 1
|
|
where word = new.food;
|
|
|
|
insert into foodfix (word, rank)
|
|
select new.food,
|
|
1
|
|
where not exists (
|
|
select 1
|
|
from foodfix
|
|
where word = new.food
|
|
);
|
|
end;
|
|
|
|
with search_results as (
|
|
select word, score, f.rowid, f.*
|
|
from foodfix
|
|
inner join food f on f.food == word
|
|
where word match 'B'
|
|
)
|
|
select rowid, food, score, date, description, amount, per100, energy
|
|
from search_results
|
|
group by food
|
|
order by score asc, date desc;
|
|
|
|
create index dailyIdx on food(strftime('%Y-%m-%d', date));
|
|
create index weeklyIdx on food(strftime('%Y-%W', date));
|
|
create index monthlyIdx on food(strftime('%Y-%m', date));
|
|
create index yearlyIdx on food(strftime('%Y', date));
|
|
create index dateIdx on food(date);
|
|
create index foodIdx on food(food);
|
|
|
|
drop view if exists foodView;
|
|
drop view if exists foodDaily;
|
|
drop view if exists foodWeekly;
|
|
drop view if exists foodMonthly;
|
|
drop view if exists foodYearly;
|
|
drop view if exists foodRecent;
|
|
|
|
create view foodView as
|
|
select id,
|
|
date,
|
|
food,
|
|
description,
|
|
round(amount, 2) as amount,
|
|
round(per100, 2) as per100,
|
|
round(energy, 2) as energy
|
|
from food;
|
|
|
|
create view foodDaily as
|
|
select strftime('%Y-%m-%d', date) as period,
|
|
round(sum(amount), 2) as amount,
|
|
round(avg(per100), 2) as avgPer100,
|
|
round(sum(energy), 2) as energy
|
|
from food
|
|
group by strftime('%Y-%m-%d', date)
|
|
order by date desc;
|
|
|
|
create view foodWeekly as
|
|
select strftime('%Y-%W', date) as period,
|
|
round(sum(amount), 2) as amount,
|
|
round(avg(per100), 2) as avgPer100,
|
|
round(sum(energy), 2) as energy
|
|
from food
|
|
group by strftime('%Y-%W', date)
|
|
order by date desc;
|
|
|
|
create view foodMonthly as
|
|
select strftime('%Y-%m', date) as period,
|
|
round(sum(amount), 2) as amount,
|
|
round(avg(per100), 2) as avgPer100,
|
|
round(sum(energy), 2) as energy
|
|
from food
|
|
group by strftime('%Y-%m', date)
|
|
order by date desc;
|
|
|
|
create view foodYearly as
|
|
select strftime('%Y', date) as period,
|
|
round(sum(amount), 2) as amount,
|
|
round(avg(per100), 2) as avgPer100,
|
|
round(sum(energy), 2) as energy
|
|
from food
|
|
group by strftime('%Y', date)
|
|
order by date desc;
|
|
|
|
create view foodRecent as
|
|
select *
|
|
from food
|
|
order by date desc
|
|
limit 10;
|
|
|
|
drop trigger if exists getper100;
|
|
create trigger getper100 after
|
|
insert on food
|
|
begin
|
|
update food
|
|
set per100 = coalesce(
|
|
new.per100,
|
|
(
|
|
select per100
|
|
from food
|
|
where food = new.food
|
|
and per100 is not null
|
|
order by date desc
|
|
limit 1
|
|
)
|
|
)
|
|
where id = new.id;
|
|
end;
|
|
|
|
create table settings(
|
|
key text not null primary key,
|
|
value text not null
|
|
);
|
|
|
|
commit; |