Last active
October 17, 2019 07:07
-
-
Save blacklight/ac298120709416c8511d23538e4eaf17 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- tmp_fit_data table setup | |
-- | |
drop sequence if exists tmp_fit_data_seq cascade; | |
create sequence tmp_fit_data_seq; | |
drop table if exists tmp_fit_data cascade; | |
create table tmp_fit_data( | |
id integer not null default nextval('tmp_fit_data_seq'), | |
username varchar(255) not null default 'me', | |
data_source varchar(1024) not null, | |
orig_data_source varchar(1024), | |
data_type varchar(255) not null, | |
value float, | |
json_value jsonb, | |
start_time timestamp with time zone not null, | |
end_time timestamp with time zone not null, | |
primary key(id) | |
); | |
alter sequence tmp_fit_data_seq owned by tmp_fit_data.id; | |
-- | |
-- fit_user table setup | |
-- | |
drop sequence if exists fit_user_seq cascade; | |
create sequence fit_user_seq; | |
drop table if exists fit_user cascade; | |
create table fit_user( | |
id integer not null default nextval('fit_user_seq'), | |
name varchar(255) unique not null, | |
primary key(id) | |
); | |
alter sequence fit_user_seq owned by fit_user.id; | |
-- | |
-- fit_data_source table setup | |
-- | |
drop sequence if exists fit_data_source_seq cascade; | |
create sequence fit_data_source_seq; | |
drop table if exists fit_data_source cascade; | |
create table fit_data_source( | |
id integer not null default nextval('fit_data_source_seq'), | |
name varchar(255) unique not null, | |
primary key(id) | |
); | |
alter sequence fit_data_source_seq owned by fit_data_source.id; | |
-- | |
-- fit_data_type table setup | |
-- | |
drop sequence if exists fit_data_type_seq cascade; | |
create sequence fit_data_type_seq; | |
drop table if exists fit_data_type cascade; | |
create table fit_data_type( | |
id integer not null default nextval('fit_data_type_seq'), | |
name varchar(255) unique not null, | |
primary key(id) | |
); | |
alter sequence fit_data_type_seq owned by fit_data_type.id; | |
-- | |
-- fit_data table setup | |
-- | |
drop sequence if exists fit_data_seq cascade; | |
create sequence fit_data_seq; | |
drop table if exists fit_data cascade; | |
create table fit_data( | |
id integer not null default nextval('fit_data_seq'), | |
user_id integer not null, | |
data_source_id integer not null, | |
orig_data_source_id integer, | |
data_type_id integer not null, | |
value float, | |
json_value jsonb, | |
start_time timestamp with time zone not null, | |
end_time timestamp with time zone not null, | |
primary key(id), | |
foreign key(user_id) references fit_user(id), | |
foreign key(data_source_id) references fit_data_source(id), | |
foreign key(orig_data_source_id) references fit_data_source(id), | |
foreign key(data_type_id) references fit_data_type(id) | |
); | |
alter sequence fit_data_seq owned by fit_data.id; | |
-- | |
-- Sync fit_data table trigger setup | |
-- | |
create or replace function sync_fit_data() | |
returns trigger as | |
$$ | |
begin | |
insert into fit_user(name) values(new.username) | |
on conflict do nothing; | |
insert into fit_data_source(name) values(new.data_source) | |
on conflict do nothing; | |
insert into fit_data_source(name) values(new.orig_data_source) | |
on conflict do nothing; | |
insert into fit_data_type(name) values(new.data_type) | |
on conflict do nothing; | |
insert into fit_data(user_id, data_source_id, orig_data_source_id, data_type_id, value, json_value, start_time, end_time) values( | |
(select id from fit_user u where u.name = new.username), | |
(select id from fit_data_source ds where ds.name = new.data_source), | |
(select id from fit_data_source ds where ds.name = new.orig_data_source), | |
(select id from fit_data_type dt where dt.name = new.data_type), | |
new.value, new.json_value, new.start_time, new.end_time | |
); | |
delete from tmp_fit_data where id = new.id; | |
return new; | |
end; | |
$$ | |
language 'plpgsql'; | |
drop trigger if exists on_tmp_fit_data_insert on tmp_fit_data; | |
create trigger on_tmp_fit_data_insert | |
after insert on tmp_fit_data | |
for each row | |
execute procedure sync_fit_data(); | |
-- | |
-- vfit view definition | |
drop view if exists vfit; | |
create view vfit as | |
select d.id | |
, u.name as username | |
, ds.name as data_source | |
, ods.name as orig_data_source | |
, dt.name as data_type | |
, value | |
, json_value | |
, start_time | |
, end_time | |
from fit_data d | |
join fit_user u on d.user_id = u.id | |
join fit_data_source ds on d.data_source_id = ds.id | |
left join fit_data_source ods on d.orig_data_source_id = ods.id | |
join fit_data_type dt on d.data_type_id = dt.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment