fit.sql

--
-- 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;