189 lines
4.5 KiB
SQL
189 lines
4.5 KiB
SQL
drop index weightDateIdx;
|
|
drop index weightDailyIdx;
|
|
drop index weightWeeklyIdx;
|
|
drop index weightMonthlyIdx;
|
|
drop index weightYearlyIdx;
|
|
|
|
create table weight2 (
|
|
id integer primary key,
|
|
date datetime default (datetime('now')),
|
|
weight real not null
|
|
);
|
|
insert into weight2 select * from weight;
|
|
drop table weight;
|
|
|
|
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;
|
|
|
|
alter table weight2 rename to weight;
|
|
|
|
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));
|
|
|
|
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 food2 (
|
|
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
|
|
);
|
|
|
|
drop index dailyIdx;
|
|
drop index weeklyIdx;
|
|
drop index monthlyIdx;
|
|
drop index yearlyIdx;
|
|
drop index dateIdx;
|
|
drop index foodIdx;
|
|
|
|
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;
|
|
|
|
insert into food2(date, food, description, amount) select date, food, description, amount from food;
|
|
drop table food;
|
|
alter table food2 rename to food;
|
|
|
|
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);
|
|
|
|
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 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;
|
|
end;
|
|
|
|
delete from foodfix;
|
|
insert into foodfix (word, rank)
|
|
select food as word,
|
|
count(*) as rank
|
|
from food
|
|
group by food; |