--
-- glossary.sql
--
-- defined by philg@mit.edu on March 6, 1999
--
-- a system that lets a group of people collaboratively maintain
-- a glossary
--

-- we limit the definition to 4000 chars because we don't want to deal with CLOBs

create table glossary (
	term		varchar(200) primary key,
	definition	varchar(4000) not null,
	author			integer not null references users,
	approved_p		char(1) default 'f' check(approved_p in ('t','f')),
	creation_date		datetime not null,
	modification_date	datetime not null
);

create function trig_glossary_modified() returns opaque
AS '
declare
begin
  NEW.modification_date := current_timestamp;
  return NEW;
end;
' language 'plpgsql';


create trigger glossary_modified
before insert or update on glossary
for each row
execute procedure trig_glossary_modified();

-- the same thing as GLOSSARY but without a primary key constraint on TERM

create table glossary_audit (
	term		varchar(200),
	definition	varchar(4000),
	author			integer,
	modification_date	datetime
);

create function trig_glossary_audit_sql() returns opaque as '
declare
begin
  insert into glossary_audit (term, definition, author, modification_date)
  values
  (OLD.term, OLD.definition, OLD.author, OLD.modification_date);
  return NEW;
end;
' language 'plpgsql';

create trigger glossary_audit_sql
before update on glossary
for each row
execute procedure trig_glossary_audit_sql();