bein-0.3: sql/triggers.sql
\set ON_ERROR_STOP
create or replace function add_execution_input() returns trigger as $$
declare
rec record;
begin
for rec in select id from executions where program = new.id loop
insert into execution_inputs(id,label,type)
values (rec.id,new.label,new.type);
end loop;
return new;
end;
$$ language plpgsql;
create trigger add_execution_input_on_program_input_insert_trigger before insert
on program_inputs for each row execute procedure add_execution_input();
-- insert into groups values (1,'boris');
-- insert into users values (1,'boris',true,true,true,true,1,false);
-- insert into headers(uid,gid,type,gr,gw,wr,ww,created,last_modified)
-- values (1,1,'program',false,false,false,false,now(),now());
-- insert into headers(uid,gid,type,gr,gw,wr,ww,created,last_modified)
-- values (1,1,'execution',false,false,false,false,now(),now());
-- insert into programs(id,language,script) values (7,'perl','');
-- insert into executions(id,program) values (8,7);
-- insert into program_inputs values (7,'hey','number');
-- create or replace function prevent_manual_setting_of_execution_objects() returns trigger as $$
-- begin
-- if new.target is not null then
-- raise exception 'Cannot manually set execution outputs!';
-- else
-- return new;
-- end if;
-- end;
-- $$ language plpgsql;
-- create trigger aa_prevent_manual_execution_output_setting before insert or update
-- on execution_outputs for each row execute
-- procedure prevent_manual_setting_of_execution_objects();
-- insert into execution_outputs (id,label,target,type) values (8,'meep',null,null);
create or replace function only_program_or_execution_into_resource_specification()
returns trigger as $$
begin
if new.type = 'program' or new.type = 'execution' then
return new;
else
raise exception 'Resource specifications are invalid for type %', new.type;
end if;
end;
$$ language plpgsql;
create trigger only_program_or_execution_into_resource_specification
before insert or update on resource_specification for each row execute
procedure only_program_or_execution_into_resource_specification();
-- insert into headers(uid,gid,type,gr,gw,wr,ww,created,last_modified)
-- values (1,1,'file',false,false,false,false,now(),now());
-- This succeeds:
-- insert into resource_specification(id,type) values (7,'program');
-- This fails:
-- insert into resource_specification(id,type) values (9,'file');
create or replace function update_last_modified() returns trigger as $$
begin
update headers set last_modified = now() where id = new.id;
return new;
end;
$$ language plpgsql;
create or replace function update_last_modified_on_headers() returns trigger as $$
begin
new.last_modified = now();
return new;
end;
$$ language plpgsql;
-- To avoid infinite recursion, we have to handle the headers table case separately
-- and not do an update on it (or the trigger just gets called again forever until
-- we run out of return stack). Instead, we modify the last_modified row in new
-- for headers, and the rest then refer to this trigger.
create trigger update_last_modified before insert or update on headers for each row execute procedure update_last_modified_on_headers();
create trigger update_last_modified before insert or update on programs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on program_inputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on program_outputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on executions for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_inputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_string_inputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_number_inputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_object_inputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_outputs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on execution_logs for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on resource_specification for each row execute procedure update_last_modified();
create trigger update_last_modified before insert or update on files for each row execute procedure update_last_modified();
create or replace function delete_group_without_members() returns trigger as $$
declare
num_users integer;
begin
select count(uid) into num_users from group_members where gid = old.gid;
if num_users < 1 then
delete from groups where gid = old.gid;
end if;
return old;
end;
$$ language plpgsql;
create trigger delete_empty_group after update or delete on group_members for each row execute procedure delete_group_without_members();
create or replace function set_new_default_group() returns trigger as $$
declare
newgid integer;
begin
select gid into newgid from group_members where uid = old.uid and gid != old.gid limit 1;
if newgid is null then
raise exception 'Cannot delete the last group a user is part of.';
else
update users set default_group = newgid where uid = old.uid;
return old;
end if;
end;
$$ language plpgsql;
create trigger new_new_default_group before delete on group_members for each row execute procedure set_new_default_group();
-- insert into groups values (2,'hilda');
-- insert into group_members values (1,2);
-- select * from group_members;
-- select * from groups;
-- delete from group_members where gid = 2;
create or replace function one_user_per_host() returns trigger as $$
declare
c integer;
begin
select count(uid) into c from users where auth_secret = new.auth_secret and uid != new.uid;
if new.auth_type = 'host' and c > 0 then
raise exception 'There is already a user associated with this host.';
end if;
return new;
end;
$$ language plpgsql;
create trigger one_user_per_host before insert or update on users for each row execute procedure one_user_per_host();
create or replace function create_group_for_each_user() returns trigger as $$
declare
groupid integer;
begin
set constraints group_members_uid_fkey deferred;
-- the other case is taken care of by foreign key constraints
if new.default_group is null then
groupid := nextval('groups_gid_seq');
insert into groups(gid,name) values (groupid,new.name);
insert into group_members (uid,gid) values (new.uid,groupid);
new.default_group := groupid;
else
insert into group_members (uid,gid) values (new.uid,new.default_group);
end if;
return new;
end;
$$ language plpgsql;
create trigger add_default_group before insert on users for each row execute procedure create_group_for_each_user();
-- insert into users values (13,'hilda',true,true,true,true,2,false);
create or replace function force_auth_secret() returns trigger as $$
begin
if (new.auth_type = 'host' or new.auth_type = 'password') and new.auth_secret is null then
raise exception 'Must specify value (even empty) for host or password based authentication.';
else
return new;
end if;
end;
$$ language plpgsql;
create trigger force_auth_secret before insert or update on users for each row execute procedure force_auth_secret();
create or replace function add_resource_specification() returns trigger as $$
declare
begin
insert into resource_specification (id,type) values (new.id,cast(tg_argv[0] as object_type_enum));
return new;
end;
$$ language plpgsql;
create trigger default_resource_spec_executions after insert on executions for each row execute procedure add_resource_specification('execution');
create trigger default_resource_spec_programs after insert on programs for each row execute procedure add_resource_specification('program');
-- insert into headers(uid,gid,type,gr,gw,wr,ww,created,last_modified)
-- values (1,1,'program',false,false,false,false,now(),now());
-- insert into programs (id) values (10);
create or replace function insert_default_future() returns trigger as $$
declare
new_target integer;
new_type object_type_enum;
begin
if new.type is null then
return old;
elsif tg_op = 'UPDATE' then
-- this gets touchy because the call to delete was
-- cascading an to remove or set null the row,
-- which called this trigger a second time.
set constraints execution_outputs_target_fkey deferred;
if new.type != old.type then
new_target := insert_future(new.id, new.type);
new_type := new.type;
delete from headers where id = old.target;
new.target := new_target;
new.type := new_type;
end if;
return new;
elsif tg_op = 'INSERT' then
new.target := insert_future(new.id, new.type);
end if;
return new;
end;
$$ language plpgsql;
create or replace function insert_future(creator_id integer, new_type object_type_enum)
returns integer as $$
declare
puid integer;
pgid integer;
pgr boolean;
pgw boolean;
pwr boolean;
pww boolean;
new_id integer;
begin
select uid,gid,gr,gw,wr,ww
into puid,pgid,pgr,pgw,pwr,pww
from headers where id = creator_id;
new_id := nextval('headers_id_seq');
insert into headers (id,uid,gid,gr,gw,wr,ww,type) values
(new_id,puid,pgid,pgr,pgw,pwr,pww,new_type);
return new_id;
end;
$$ language plpgsql;
create trigger create_default_future before insert or update on execution_outputs for each row execute procedure insert_default_future();
-- insert into execution_outputs (id,label,type) values (502,'meep','file');
-- update execution_outputs set type ='file';
-- select * from execution_outputs;
create or replace function set_created_timestamp() returns trigger as $$
begin
new.created = now();
return new;
end;
$$ language plpgsql;
create trigger set_created_timestamp before insert on headers for each row execute procedure set_created_timestamp();
create or replace function add_typed_row() returns trigger as $$
begin
if new.type = 'sequence' then
raise exception 'Sequences are not yet implemented.';
elsif new.type = 'number' then
insert into execution_number_inputs (id,label,value)
values (new.id,new.label,null);
elsif new.type = 'string' then
insert into execution_string_inputs (id,label,value)
values (new.id,new.label,null);
elsif new.type = 'file' then
insert into execution_object_inputs (id,label,value,value_type)
values (new.id,new.label,null,'file');
end if;
return new;
end;
$$ language plpgsql;
create trigger add_typed_row after insert on execution_inputs for each row execute procedure add_typed_row();
-- insert into execution_inputs (id,label,type) values (8,'meep','string');
create or replace function delete_output_future() returns trigger as $$
begin
delete from headers where id = old.target;
return old;
end;
$$ language plpgsql;
create trigger delete_output_future after delete on execution_outputs for each row execute procedure delete_output_future();
create or replace function refuse_to_delete_nobody() returns trigger as $$
begin
if old.name = 'nobody' then
raise exception 'Cannot delete fallback entry ''nobody''';
else
return old;
end if;
end;
$$ language plpgsql;
create trigger refuse_to_delete_nobody before delete on users for each row execute procedure refuse_to_delete_nobody();
create trigger refuse_to_delete_nobody before delete on groups for each row execute procedure refuse_to_delete_nobody();
create or replace function reset_group_on_owned_objects() returns trigger as $$
declare
defaultgid integer;
begin
select default_group into defaultgid from users where uid = old.uid;
update headers set gid = defaultgid where uid = old.uid;
return old;
end;
$$ language plpgsql;
create trigger reset_group_on_owned_objects after delete on group_members for each row execute procedure reset_group_on_owned_objects();
create or replace function prevent_deleting_default_group() returns trigger as $$
declare
num_as_default integer;
begin
select count(uid) into num_as_default from users where default_group = old.gid;
if num_as_default > 0 then
raise exception 'Group is default for at least one user; cannot delete.';
end if;
return old;
end;
$$ language plpgsql;
create trigger prevent_deleting_default_group before delete on groups for each row execute procedure prevent_deleting_default_group();
-- This turns out not to be the desired behavior:
-- create or replace function prevent_user_setting_foreign_group() returns trigger as $$
-- declare
-- res integer;
-- begin
-- select uid into res from group_members where uid = new.uid and gid = new.gid;
-- if res is null then
-- raise exception 'UID % is not part of GID %; cannot set GID on object.',
-- new.uid, new.gid;
-- else
-- return new;
-- end if;
-- end;
-- $$ language plpgsql;
-- create trigger prevent_user_setting_foreign_group before update or insert on headers for each row execute procedure prevent_user_setting_foreign_group();
create or replace function impose_default_permissions_and_group() returns trigger as $$
declare
user_record record;
begin
select default_gr,default_gw,default_wr,default_ww,default_group into user_record
from users where uid = new.uid;
if new.gr is null then
new.gr := user_record.default_gr;
end if;
if new.gw is null then
new.gw := user_record.default_gw;
end if;
if new.wr is null then
new.wr := user_record.default_wr;
end if;
if new.ww is null then
new.ww := user_record.default_ww;
end if;
if new.gid is null then
new.gid := user_record.default_group;
end if;
return new;
end;
$$ language plpgsql;
create trigger impose_default_permissions_and_group before insert on headers for each row execute procedure impose_default_permissions_and_group();
-- insert into headers (uid,type) values (11,'program');
create or replace function immutable(tid integer) returns boolean as $$
declare
dep_id integer;
num_nonwaiting integer;
obj_type object_type_enum;
begin
if tid is null then
return false;
end if;
for dep_id in select object from copying_dependencies where depends_on = tid loop
if immutable(dep_id) then
return true;
end if;
end loop;
select type into obj_type from headers where id = tid;
if obj_type::text = 'program' then
select count(id) into num_nonwaiting from executions
where program = tid and status != 'waiting';
if num_nonwaiting > 0 then
return true;
end if;
elsif obj_type::text = 'file' or obj_type::text = 'sequence' then
select count(a.id) into num_nonwaiting
from execution_object_inputs as a
join executions as b on a.id = b.id
where b.status != 'waiting' and a.value = tid;
if num_nonwaiting > 0 then
return true;
end if;
elsif obj_type::text = 'execution' then
for dep_id in select target from execution_outputs where id = tid loop
if immutable(dep_id) then
return true;
end if;
end loop;
end if;
return false;
end;
$$ language plpgsql;
create or replace function fail_if_immutable() returns trigger as $$
begin
if tg_op = 'DELETE' then
if immutable(old.id) then
raise exception 'Cannot alter immutable object.';
else
return old;
end if;
else
if immutable(new.id) then
raise exception 'Cannot alter immutable object.';
else
return new;
end if;
end if;
end;
$$ language plpgsql;
create trigger immutability before delete on headers
for each row execute procedure fail_if_immutable('old');
create trigger immutability_u before update or delete on programs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on program_inputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on program_outputs
for each row execute procedure fail_if_immutable();
create trigger immutability_u before update or delete on executions
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_inputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_string_inputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_number_inputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_object_inputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_outputs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before insert or update or delete on execution_logs
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before update or delete on resource_specification
for each row execute procedure fail_if_immutable();
create trigger immutability_iu before update or delete on files
for each row execute procedure fail_if_immutable();
-- Copy function
create or replace function copy(tid integer, new_owner integer) returns integer as $$
declare
r record;
new_id integer;
new_filename text;
begin
select label,notes,uid,gid,gr,gw,wr,ww,type into r
from headers where id = tid;
new_id := nextval('headers_id_seq');
if new_owner is null or r.uid = new_owner then
insert into headers (id,label,notes,uid,gid,gr,gw,wr,ww,type)
values (new_id, r.label, r.notes, r.uid, r.gid,
r.gr, r.gw, r.wr, r.ww, r.type);
else
insert into headers (id, label,notes,uid,type)
values (new_id, r.label, r.notes, new_owner, r.type);
end if;
if not is_future(tid) then
if r.type :: text = 'program' then
select language,script into r from programs where id = tid;
insert into programs (id,language,script)
values (new_id, r.language, r.script);
for r in select label,type from program_inputs where id = tid loop
insert into program_inputs (id,label,type) values (new_id,r.label,r.type);
end loop;
for r in select label,type from program_outputs where id = tid loop
insert into program_outputs (id,label,type)
values (new_id,r.label,r.type);
end loop;
elsif r.type :: text = 'execution' then
select program into r from executions where id = tid;
insert into executions (id,program) values (new_id,r.program);
for r in select label,type from execution_inputs where id = tid loop
insert into execution_inputs (id,label,type)
values (new_id,r.label,r.type);
end loop;
for r in select label,value from execution_string_inputs where id = tid loop
raise notice 'String input: %', r;
update execution_string_inputs set value = r.value
where id = new_id and label = r.label;
end loop;
for r in select label,value from execution_number_inputs where id = tid loop
update execution_number_inputs set value = r.value
where id = new_id and label = r.label;
end loop;
for r in select label,type from execution_outputs
where id = tid loop
insert into execution_outputs (id,label,type)
values (new_id, r.label, r.type);
end loop;
-- Logs are not copied.
elsif r.type :: text = 'sequence' then
raise warning 'Sequence type not implemented yet; no body to copy.';
elsif r.type :: text = 'file' then
new_filename := unique_name(in_repository(''),50);
select user_filename,stored_as into r from files where id = tid;
perform copy_file(in_repository(r.stored_as), in_repository(new_filename));
insert into files (id,user_filename,stored_as)
values (new_id,r.user_filename,new_filename);
end if;
end if;
insert into copying_dependencies (object,depends_on) values (new_id,tid);
return new_id;
end;
$$ language plpgsql;
create or replace function update_execution_inputs() returns trigger as $$
declare
r record;
begin
for r in select id from executions where program = new.id loop
if old.label != new.label then
update execution_inputs set label = new.label where id = r.id;
end if;
if old.type != new.type then
update execution_inputs set type = new.type where id = r.id;
end if;
end loop;
return new;
end;
$$ language plpgsql;
create trigger update_execution_inputs before update on program_inputs
for each row execute procedure update_execution_inputs();
create or replace function update_typed_row() returns trigger as $$
begin
if new.type != old.type then
if old.type :: text = 'number' then
delete from execution_number_inputs where id = old.id;
elsif old.type :: text = 'string' then
delete from execution_string_inputs where id = old.id;
else
delete from execution_object_inputs where id = old.id;
end if;
if new.type :: text = 'number' then
insert into execution_number_inputs (id,label,value)
values (new.id,new.label,null);
elsif new.type :: text = 'string' then
insert into execution_string_inputs (id,label,value)
values (new.id,new.label,null);
else
insert into execution_object_inputs (id,label,value,value_type)
values (new.id,new.label,null,cast(new.type :: text as object_type_enum));
end if;
end if;
return new;
end;
$$ language plpgsql;
create trigger updated_type_row before update on execution_inputs
for each row execute procedure update_typed_row();
create or replace function delete_typed_row() returns trigger as $$
begin
if old.type :: text = 'string' then
delete from execution_string_inputs where id = old.id and label = old.label;
elsif old.type :: text = 'number' then
delete from execution_number_inputs where id = old.id and label = old.label;
else
delete from execution_object_inputs where id = old.id and label = old.label;
end if;
return old;
end;
$$ language plpgsql;
create trigger delete_typed_row after delete on execution_inputs
for each row execute procedure delete_typed_row();
create or replace function insert_execution_output() returns trigger as $$
declare
r integer;
begin
for r in select id from executions where program = new.id loop
insert into execution_outputs (id,label,type)
values (r, new.label, cast(cast(new.type as text) as object_type_enum));
end loop;
return new;
end;
$$ language plpgsql;
create trigger insert_execution_output before insert on program_outputs
for each row execute procedure insert_execution_output();
create or replace function delete_execution_inputs() returns trigger as $$
declare
r integer;
begin
for r in select id from executions where program = old.id loop
delete from execution_inputs where id = r and label = old.label;
end loop;
return old;
end;
$$ language plpgsql;
create trigger delete_execution_inputs after delete on program_inputs
for each row execute procedure delete_execution_inputs();
create or replace function delete_execution_outputs() returns trigger as $$
declare
r integer;
begin
for r in select id from executions where program = old.id loop
delete from execution_outputs where id = r and label = old.label;
end loop;
return old;
end;
$$ language plpgsql;
create trigger delete_execution_outputs after delete on program_outputs
for each row execute procedure delete_execution_outputs();
create or replace function update_execution_outputs() returns trigger as $$
declare
r integer;
begin
for r in select id from executions where program = old.id loop
update execution_outputs set label = new.label,
type = cast(cast(new.type as text) as object_type_enum)
where id = r and label = old.label;
end loop;
return new;
end;
$$ language plpgsql;
create trigger update_execution_outputs after update on program_outputs
for each row execute procedure update_execution_outputs();
create or replace function fill_execution_io(ex integer) returns void as $$
declare
prog integer;
lbl text;
typev input_enum;
typeq object_type_enum;
begin
select program into prog from executions where id = ex;
delete from execution_inputs where id = ex;
delete from execution_outputs where id = ex;
for lbl,typev in select label,type from program_inputs where id = prog loop
insert into execution_inputs (id,label,type) values (ex,lbl,typev);
end loop;
for lbl,typeq in select label,type from program_outputs where id = prog loop
insert into execution_outputs (id,label,type)
values (ex, lbl, cast(cast(typeq as text) as object_type_enum));
end loop;
end;
$$ language plpgsql;
create or replace function fill_on_insert() returns trigger as $$
begin
if new.program is not null then
perform fill_execution_io(new.id);
end if;
return new;
end;
$$ language plpgsql;
create or replace function fill_on_update() returns trigger as $$
begin
if new.program != old.program and new.program is not null then
perform fill_execution_io(new.id);
end if;
return new;
end;
$$ language plpgsql;
create trigger update_io after update on executions for each row execute procedure fill_on_update();
create trigger insert_io after insert on executions for each row execute procedure fill_on_insert();
create or replace function no_default_foreign_group() returns trigger as $$
declare
r record;
group_name varchar;
begin
select uid into r from group_members where uid = new.uid and gid = new.default_group;
if r is null then
select name into group_name from groups where gid = new.default_group;
raise exception 'User % is not part of group %; cannot set it as default',
new.name, group_name;
else
return new;
end if;
end;
$$ language plpgsql;
create trigger no_default_foreign_group before insert or update on users
for each row execute procedure no_default_foreign_group();
create or replace function is_future(tid integer) returns boolean as $$
declare
r integer;
objtype object_type_enum;
begin
select type into objtype from headers where id = tid;
if objtype = 'sequence' then
raise exception 'Sequences are not implemented yet.';
elsif objtype = 'file' then
select id into r from files where id = tid;
elsif objtype = 'program' then
select id into r from programs where id = tid;
elsif objtype = 'execution' then
select id into r from executions where id = tid;
end if;
return r is null;
end;
$$ language plpgsql;
-- File functions
create or replace function in_repository(name text) returns text as $$
declare
filedir text;
begin
select value into filedir from configuration where key = 'file_repository';
return join_path(filedir,name);
end;
$$ language plpgsql;
create or replace function assert_file_exists() returns trigger as $$
begin
if simple_file_exists(in_repository(new.stored_as)) then
return new;
else
raise exception 'Target file % does not exist.', new.stored_as;
end if;
end;
$$ language plpgsql;
create trigger assert_file_exists before insert or update on files
for each row execute procedure assert_file_exists();
-- insert into files (id,user_filename,stored_as)
-- values (35,'boris','/tmp/hilda');
-- update files set stored_as = '/tmp/asdfasdf';
create or replace function move_stored_file() returns trigger as $$
begin
perform move_file(in_repository(old.stored_as), in_repository(new.stored_as));
return new;
end;
$$ language plpgsql;
create trigger aa_move_stored_file before update on files
for each row execute procedure move_stored_file();
create or replace function delete_repository_file() returns trigger as $$
begin
perform unlink_file(in_repository(old.stored_as));
return old;
end;
$$ language plpgsql;
create trigger delete_repository_file after delete on files
for each row execute procedure delete_repository_file();
create or replace function import(filepath text, targetobjid integer, targetowner integer, import_type char(4))
returns integer as $$
declare
objid integer;
new_filename text;
owner integer;
begin
if targetobjid is null then
objid := nextval('headers_id_seq');
else
objid := targetobjid;
end if;
if targetowner is null then
owner := 0;
else
owner := targetowner;
end if;
new_filename := unique_name(in_repository(''),50);
if import_type = 'copy' then
perform copy_file(filepath, in_repository(new_filename));
elsif import_type = 'move' then
perform move_file(filepath, in_repository(new_filename));
else
raise exception 'Invalid import mode % (must be ''copy'' or ''move'').', import_mode;
end if;
insert into headers (id,uid,type) values (objid,owner,'file');
insert into files (id,user_filename,stored_as)
values (objid,path_basename(filepath),new_filename);
return objid;
end;
$$ language plpgsql;
create or replace function export(objid integer, targetpath text, targetfile text, export_mode char(4))
returns void as $$
declare
target text;
r record;
begin
select id,user_filename,stored_as into r from files where id = objid;
if targetfile is null then
target := join_path(targetpath,r.stored_as);
else
target := join_path(targetpath,targetfile);
end if;
if export_mode = 'copy' then
perform copy_file(in_repository(r.stored_as),target);
elsif export_mode = 'link' then
perform link_file(in_repository(r.stored_as),target);
perform chmod_file(in_repository(r.stored_as), 7, 4, 4);
else
raise exception 'Invalid export mode % (must be ''copy'' or ''link'').',
export_mode;
end if;
end;
$$ language plpgsql;
create or replace function is_valid_auth(a text) returns boolean as $$
begin
if a = 'None' or a = 'SameUser' or a ~ E'User \\d+' then
return true;
else
return false;
end if;
end;
$$ language plpgsql;
create or replace function valid_authentication() returns trigger as $$
begin
if new.key = 'authentication' and not(is_valid_auth(new.value)) then
raise exception 'Invalid value for authentication.';
else
return new;
end if;
end;
$$ language plpgsql;
create trigger fva before update on configuration for each row execute procedure valid_authentication();
create or replace function complete_job(job integer) returns void as $$
declare
d integer;
begin
for d in select awaited_by from jobs_awaiting where id = job loop
update current_jobs set status = 'pending' where id = d;
end loop;
delete from jobs_awaiting where id = job;
delete from current_jobs where id = job;
end;
$$ language plpgsql;
create or replace function fail_job(job integer) returns void as $$
declare
d integer;
begin
for d in select awaited_by from jobs_awaiting where id = job LOOP
update executions set (status,failed_dependency) = ('dependency_failed',job) where id = d;
end loop;
delete from current_jobs where id = job;
end;
$$ language plpgsql;
create or replace function end_job_trigger() returns trigger as $$
begin
if new.program is null then
return old;
elsif new.status = 'complete' then
perform complete_job(new.id);
elsif new.status = 'failed' or new.status = 'dependency_failed' then
perform fail_job(new.id);
end if;
return new;
end;
$$ language plpgsql;
create trigger update_joblist before update on executions for each row execute procedure end_job_trigger();
create or replace view execution_dependencies as
select distinct n.id as id, o.id as depends_on
from execution_object_inputs as n
left join execution_outputs as o on n.value = o.target;
create or replace function run(ex integer) returns void as $$
declare
ndeps integer;
dex integer;
started integer;
begin
select count(depends_on) into ndeps from execution_dependencies where id = ex;
select count(id) into started from current_jobs where id = ex;
if ndeps > 0 then
if started = 0 then insert into current_jobs (id,status) values (ex,'waiting'); end if;
for dex in select depends_on from execution_dependencies where id = ex loop
perform run(dex);
insert into jobs_awaiting (id,awaited_by) values (dex,ex);
end loop;
else
if started = 0 then insert into current_jobs (id,status) values (ex,'pending'); end if;
end if;
end;
$$ language plpgsql;
create or replace function new_session(userid integer) returns char(20) as $$
declare
s char(20);
begin
loop
select unique_name(20) into s;
if (select count(key) from sessions where key=s) = 0 then
insert into sessions(key,uid,last_activity) values (s,userid,now());
return s;
end if;
end loop;
end;
$$ language plpgsql;
create or replace function touch_session(char(20)) returns void as $$
update sessions set last_activity = now() where key = $1;
$$ language sql;
create or replace function remove_expired_sessions() returns void as $$
delete from sessions where now() - last_activity > interval '30' minute;
$$ language sql;
create or replace function create_group(newname text,username integer) returns integer as $$
declare
newgid integer;
begin
if (select gid from groups where name = newname) is not null then
return -1;
else
newgid := nextval('groups_gid_seq');
insert into groups (gid,name) values (newgid,newname);
insert into group_members(uid,gid) values (username,newgid);
return newgid;
end if;
end;
$$ language plpgsql;
create or replace function new_object(type_to_create object_type_enum, new_owner_id integer) returns integer as $$
declare
newid integer;
begin
select nextval('headers_id_seq') into newid;
insert into headers(id,uid,type) values (newid,new_owner_id,type_to_create);
if type_to_create = 'program' then
insert into programs(id) values (newid);
elsif type_to_create = 'execution' then
insert into executions(id) values (newid);
elsif type_to_create = 'sequence' then
raise exception 'Sequences are not yet implemented; cannot create one.';
end if;
return newid;
end;
$$ language plpgsql;
create or replace view executions_forcing_immutability(file_id,execution_id,execution_label) as
select inp.value as file_id, inp.id as execution_id, hd.label as execution_label
from execution_object_inputs as inp
left join executions as ex on inp.id = ex.id
left join headers as hd on hd.id = inp.id
where ex.status != 'waiting' and inp.value is not null;