bein-0.3.1: sql/core_tables.sql

\set ON_ERROR_STOP

-- Bein's authentication is quite simple: every object belongs
-- permanently to the user who created it (and it cannot be
-- transferred), and to a group of users.  Each object is always
-- readable and writable by its owner, and the permissions to read and
-- write are set for group and world a la unix.

-- Anyone who is an administrator can access the configuration table
-- of the database, start and stop the daemon, check everyone's jobs,
-- etc.  The read/write options don't apply to him.  He's root,
-- essentially.  An administrator will probably want a separate
-- account for his own work so it's not continually clogged by
-- everyone else's data.

create table groups (
       gid serial primary key,
       name varchar unique not null
);

-- on insert:
-- on update:
-- on delete:
-- * fail if trying to delete group 'nobody'
-- * fail if trying to delete group which is default for a user

create type authentication_enum as
       enum ('password','host','nologin');

create table users (
       uid integer primary key,
       name varchar unique not null,
       default_gr boolean not null,
       default_gw boolean not null,
       default_wr boolean not null,
       default_ww boolean not null,
       default_group integer references groups(gid),
       administrator boolean not null default false,
       auth_type authentication_enum not null default 'password',
       auth_secret text
);

-- on insert:
-- * create a group of the same name as the user, and add user to it.
-- on update:
-- on delete:
-- * fail if trying to delete user 'nobody'
-- * delete the group corresponding to this user.

insert into groups (gid,name) values (0,'nobody');

insert into users (uid,name,default_gr,default_gw,default_wr,default_ww,default_group,auth_type)
values (0,'nobody',true,true,true,true,0,'nologin');

create table group_members (
       uid integer references users(uid)
                   on delete cascade
		   on update cascade
                   deferrable,
       gid integer references groups(gid)
                   on delete cascade
                   on update cascade,
       primary key (uid,gid)
);

insert into group_members (uid,gid) values (0,0); -- nobody is part of group nobody

-- on insert:
-- on update:
-- on delete:
-- * set all objects owned by user with group gid to be owned by user's group
-- * if no more users in group, delete group

-- Every object has a header, which is independent of its type.  The
-- header stores descriptions, permissions, and timestamps, and a
-- pointer to the object's type.
create type object_type_enum as 
       enum ('program', 'execution', 'file', 'sequence');

create table headers (
       id serial primary key, -- which creates a sequence header_id_seq
       label varchar not null default '',
       notes varchar not null default '',
       uid integer default 0 
                   references users(uid)
                   on delete set default
		   on update cascade,
       gid integer references groups(gid)
                   on delete set default
                   on update cascade,
       gr boolean not null,
       gw boolean not null,
       wr boolean not null,
       ww boolean not null,
       created timestamp not null,
       last_modified timestamp not null,
       type object_type_enum not null,
       unique (id,type)
);

-- on insert:
-- * set created and last_modified time stamps
-- * if this is a copy, insert a row into copying_dependencies
-- on update:
-- * update last_modified time stamp
-- * fail if setting gid to a group the uid is not part of
-- on delete:
-- * fail if immutable


-- In this version, there is no implementation of sequence.  They
-- don't exist yet and won't until a later version.  The other three
-- types - program, execution, and file - follow:

-- Programs
create type script_language as enum ('perl','r');

create table programs (
       id integer primary key references headers(id)
                  on delete cascade on update cascade,
       language script_language default 'perl' not null,
       script varchar default ''
);

-- on insert:
-- * create empty resource request
-- on update:
-- * fail if id is immutable
-- * update last_modified in headers
-- on delete:
-- * fail if id is immutable

create type input_enum as enum ('sequence', 'number', 'string', 'file');

create table program_inputs (
       id integer references programs(id)
                  on delete cascade on update cascade,
       label varchar not null,
       type input_enum,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- * add corresponding row to any executions with value null
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- * alter rows on referencing execution rows
-- on delete:
-- * fail if immutable
-- * delete corresponding execution rows

create type program_output as enum ('sequence', 'file');

create table program_outputs (
       id integer references headers(id)
                  on delete cascade on update cascade,
       label varchar not null,
       type program_output,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- * insert additional execution output on corresponding executions
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- * update execution outputs as necessary
-- on delete:
-- * fail if immutable
-- * delete execution output

-- Executions
create type execution_status as enum ('waiting','running','complete','failed','dependency_failed');

create table executions (
       id integer references headers(id) on delete cascade on update cascade primary key,
       program integer references programs(id) on delete set null on update cascade,
       status execution_status default 'waiting',
       failed_dependency integer references executions(id)
);

-- on insert:
-- * create empty resource request
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- * update all inputs and outputs if program changed
-- on delete:
-- * fail if immutable

create table execution_inputs (
       id integer references executions(id) on delete cascade on update cascade,
       label varchar not null,
       type input_enum not null,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- * insert row into appropriate type input table
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- * delete row from appropriate type, create in new type
-- on delete:
-- * fail if immutable
-- * delete row from appropriate type table

create table execution_string_inputs (
       id integer,
       label varchar,
       value varchar,
       foreign key (id,label) references execution_inputs (id,label)
       on delete cascade on update cascade,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- on update:
-- * update last_modified in headers
-- * fail if immutable
-- on delete:
-- * fail if immutable

create table execution_number_inputs (
       id integer,
       label varchar,
       value float,
       foreign key (id,label) references execution_inputs (id,label)
       on delete cascade on update cascade,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- on update:
-- * update last_modified in headers
-- * fail if immutable
-- on delete:
-- * fail if immutable

create table execution_object_inputs (
       id integer,
       label varchar,
       value integer,
       value_type object_type_enum,
       foreign key (id,label) references execution_inputs (id,label)
       on delete cascade on update cascade,
       foreign key (value,value_type) references headers (id,type)
       on delete set null on update cascade,
       primary key (id,label)
);

-- on insert:
-- * fail if immutable
-- on update:
-- * update last_modified in headers
-- * fail if immutable
-- on delete:
-- * fail if immutable

create table execution_outputs (
       id integer references executions(id) on delete cascade on update cascade,
       label varchar,
       target integer, 
       type object_type_enum,
       primary key (id,label),
       foreign key (target,type) references headers(id,type)
       match simple
       on update no action on delete no action
       deferrable 
);

-- on insert:
-- * fail if immutable
-- * fail if target,type is not a future
-- * create future of appropriate type and put it in target,type pair
-- on update:
-- * update last_modified in headers
-- * fail if immutable
-- * fail if target,type is not a future
-- * delete previous future if changing target,type
-- on delete:
-- * fail if immutable
-- * delete output object

create table execution_logs (
       id integer references executions(id) 
                  on delete cascade on update cascade,
       log_time timestamp not null,
       log_message varchar not null,
       primary key (id,log_time)
);

-- on insert:
-- * fail if immutable
-- on update:
-- * update last_modified in headers
-- * fail if immutable
-- on delete:
-- * fail if immutable

-- Both programs and executions have the same resource specification
-- fields, so they are extracted into their own table.
create table resource_specification (
       id integer primary key,
       type object_type_enum,
       -- See the LSF User's Manual for the meaning of the resreq
       -- string.
       resreq varchar default '',
       -- In the following fields, null signifies infinity, i.e., no
       -- limit.
       maxcpu integer default null,
       maxfilesize integer default null,
       maxram integer default null,
       maxswap integer default null,
       maxprocs integer default null,
       foreign key (id,type) references headers(id,type)
       on delete cascade on update cascade
);

-- on insert:
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- on delete:
-- * fail if immutable


-- Files

-- There is a decision to be made when defining files.  If we want to
-- have lazy copies of files, we need to divide files into a table
-- serving as the body of the Bein object, which contains a pointer to
-- a reference-counted second table that actually holds the file
-- information.

-- Lazy copying is probably important for sequences, but for files it
-- really isn't.  Files are black boxes, and the only time you would
-- want to copy one is when it is immutable and you want to replace it
-- with another file.  In this case the lazy copy is simply not
-- helpful.  Therefore I am going with the simpler form of a table
-- pointing to a file.

-- Files are stored externally.  PostgreSQL's large binary object
-- store is limited to blobs of under 2GB, and Bein will face much
-- larger files.  Instead I set a directory in the configuration where
-- the files are to be stored, and keep their file name there in the
-- database instead.  Since the database never needs to manipulate
-- files, this is just fine.
create table files (
       id integer references headers(id)
                  on delete cascade on update cascade
                  primary key,
       user_filename varchar not null,
       content_type varchar not null default 'application/octet-stream',
       stored_as varchar unique not null
);

-- on insert:
-- * fail if stored_as does not refer to a filename that exists
-- on update:
-- * fail if immutable
-- * update last_modified in headers
-- * if changing stored_as, move file to new name, failing if location already occupied
-- on delete:
-- * fail if immutable
-- * delete file from disk


-- Configuration

-- All of Bein's configuration, what of it there is, is stored as key
-- value pairs in a table called configuration.
create type configuration_key as enum (
       'file_repository',
       'scratch_directory',
       'static_content_directory',
       'perl_executable',
       'r_executable',
       'max_executions',
       'minion_command',
       'daemon_port',
       'minion_port',
       'http_port',
       'http_base_url',
       'http_base_path',
       'template_path',
       'authentication');

create table configuration (
       key configuration_key primary key,
       value varchar
);

insert into configuration (key,value) values ('file_repository','/tmp/bein_files');
insert into configuration (key,value) values ('scratch_directory','/tmp/bein_scratch');
insert into configuration (key,value) values ('static_content_directory','/home/ross/bein/static');
insert into configuration (key,value) values ('perl_executable','/usr/bin/perl');
insert into configuration (key,value) values ('r_executable','/usr/bin/R');
insert into configuration (key,value) values ('max_executions',1000);
insert into configuration (key,value) values ('minion_command','beinminion');
insert into configuration (key,value) values ('daemon_port','/tmp/.s.BEIND');
insert into configuration (key,value) values ('minion_port','.s.MINION');
insert into configuration (key,value) values ('http_port',8082);
insert into configuration (key,value) values ('http_base_url','');
insert into configuration (key,value) values ('http_base_path','');
insert into configuration (key,value) values ('authentication','None');
insert into configuration (key,value) values ('template_path','/home/ross/bein/templates');

create or replace function delete_fails() returns trigger as $$
begin
     raise exception 'Cannot delete configuration keys, only update them.';
end;
$$ language plpgsql;

create trigger delete_fails before delete on configuration for each statement execute procedure delete_fails();

-- Dependencies

-- Objects are used as templates for other objects, inputs for other
-- objects, are created byother objects, etc.  These relations
-- determine the mutability of a given object, so we have to store it.
-- Both program and input dependencies can be queried from the
-- execution table, but template and created by must be stored here.
-- In the end, they should be seen uniformly as a unioned view,
-- implemented as 'dependencies'.

create type dependency as enum ('template', 'program', 'input', 'created_by');

create table copying_dependencies (
       object integer references headers(id) on delete cascade on update cascade primary key,
       depends_on integer references headers(id) on delete cascade on update cascade
);

create or replace view dependencies (object,depends_on,dependency_type) as
select object,depends_on,'template' from copying_dependencies union all
select id,value,'input' from execution_object_inputs where value is not null union all
select id,program,'program' from executions where program is not null union all
select target,id,'created_by' from execution_outputs;

create type job_status as enum ('waiting','pending','running','dependency_failed');

create table current_jobs (
       id integer primary key references executions(id) on delete cascade on update cascade,
       status job_status default 'waiting',
       running_as_pid integer,
       scratch_dir text
);

create table jobs_awaiting (
       id integer references current_jobs(id) on delete cascade on update cascade,
       awaited_by integer references current_jobs(id) on delete cascade on update cascade,
       primary key (id,awaited_by)
);

create table sessions (
       key char(20) primary key,
       uid integer references users(uid),
       last_activity timestamp not null
);