58 lines
2.1 KiB
SQL
58 lines
2.1 KiB
SQL
create schema profile;
|
|
|
|
create table profile.t_login (
|
|
lastchange timestamp without time zone default now(),
|
|
pk integer not null primary key generated always as identity,
|
|
login text not null unique,
|
|
forename text,
|
|
surname text,
|
|
duedate timestamp without time zone not null,
|
|
password text not null
|
|
);
|
|
|
|
create table profile.t_profile (
|
|
lastchange timestamp without time zone default now(),
|
|
pk_profile integer not null primary key generated always as identity,
|
|
username text not null unique,
|
|
theme text default 'light'::text not null,
|
|
column dynamic_css text
|
|
);
|
|
|
|
create table profile.t_role (
|
|
lastchange timestamp without time zone default now(),
|
|
pk integer not null primary key generated always as identity,
|
|
name text not null unique
|
|
);
|
|
|
|
create table profile.t_loginrole (
|
|
lastchange timestamp without time zone default now(),
|
|
pk integer not null primary key generated always as identity,
|
|
fk_login integer not null references profile.t_login(pk),
|
|
fk_role integer not null references profile.t_role(pk),
|
|
unique (fk_login, fk_role)
|
|
);
|
|
|
|
create view profile.v_loginrole as
|
|
select l.login, l.forename, l.surname, l.duedate, r.name as role_name
|
|
from profile.t_loginrole
|
|
left join profile.t_login l on l.pk = t_loginrole.fk_login
|
|
left join profile.t_role r on r.pk = t_loginrole.fk_role;
|
|
|
|
copy profile.t_role (lastchange, pk, name) from stdin;
|
|
2019-01-08 10:16:19.959185 1 read_done
|
|
2019-01-08 10:16:19.959185 2 write_done
|
|
2019-01-08 10:16:19.959185 3 write_job
|
|
2019-01-08 10:16:19.959185 5 write_project
|
|
2019-01-08 10:16:19.959185 4 write_module
|
|
2019-09-24 16:00:49.811943 6 write_note
|
|
2019-09-24 16:00:49.811943 7 write_contact
|
|
\.
|
|
|
|
grant usage on schema profile to timetrack;
|
|
|
|
grant select,insert,delete,update on table profile.t_login to timetrack;
|
|
grant select,insert,delete,update on table profile.t_loginrole to timetrack;
|
|
grant select,insert,update on table profile.t_profile to timetrack;
|
|
grant select,insert,delete,update on table profile.t_role to timetrack;
|
|
grant select,insert,delete,update on table profile.v_loginrole to timetrack;
|