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