Files
calorie-counter/food.ddl
PhatPhuckDave 8fd8d53cc3 Rework search to return a list
Like a proper search would!"
2024-08-13 18:51:50 +02:00

230 lines
5.2 KiB
Plaintext

begin transaction;
create table weight (
date datetime default (datetime('now', '+2 hours')),
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 rowid,
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(
date datetime default (datetime('now', '+2 hours')),
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;
-- Spellfix search example
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 rowid,
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 rowid,
*
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 rowid = new.rowid;
end;
create table settings(
key text not null primary key,
value text not null
);
commit;