57 lines
1.8 KiB
SQL
57 lines
1.8 KiB
SQL
create table exercise (
|
|
id smallserial primary key,
|
|
name varchar not null
|
|
);
|
|
create table shorthand (
|
|
exercise smallint not null,
|
|
name varchar unique not null,
|
|
constraint fk_exercise foreign key (exercise) references exercise(id)
|
|
);
|
|
create table account (
|
|
id serial primary key,
|
|
name varchar not null,
|
|
login varchar not null,
|
|
birthdate date not null,
|
|
bodymass integer,
|
|
constraint fk_bodymass foreign key (bodymass) references bodymass(id)
|
|
);
|
|
create table bodymass (
|
|
id serial primary key,
|
|
account integer not null,
|
|
time timestamptz not null,
|
|
kilos numeric(4,1) not null,
|
|
constraint fk_account foreign key (account) references account(id),
|
|
constraint unique_account_time unique (account, time),
|
|
constraint time_not_infty check (time <> 'infinity'::timestamptz)
|
|
);
|
|
create table training (
|
|
id serial primary key,
|
|
account integer not null,
|
|
time timestamptz not null default timezone('utc', now()),
|
|
exercise smallint not null,
|
|
runs smallint not null,
|
|
reps smallint not null,
|
|
kilos numeric(4,1) not null,
|
|
constraint fk_exercise foreign key (exercise) references exercise(id),
|
|
constraint fk_account foreign key (account) references account(id)
|
|
);
|
|
create table message (
|
|
id serial primary key,
|
|
account integer not null,
|
|
time timestamptz not null default timezone('utc', now()),
|
|
message varchar not null,
|
|
constraint fk_account foreign key (account) references account(id)
|
|
);
|
|
create view dailylift as select
|
|
date(time) as time, account, exercise, sum(runs * reps * kilos) as lift
|
|
from training group by 1, 2, 3;
|
|
|
|
create role training_user;
|
|
grant select on account to training_user;
|
|
grant select on exercise to training_user;
|
|
grant select on shorthand to training_user;
|
|
grant insert on training to training_user;
|
|
grant select on training to training_user;
|
|
grant insert on message to training_user;
|
|
grant usage on training_id_seq to training_user;
|