-- Copyright (c) 1998  Dustin Sallings
--
-- $Id: events.sql,v 1.2 1999/01/22 19:56:12 dustin Exp $
--
-- This is used to bootstrap your postgres database for event logging.

begin transaction;

-- add support for PL/pgsql

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
	'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
				
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
	HANDLER plpgsql_call_handler
	LANCOMPILER 'PL/pgSQL';

-- tag table

create table tags (
	tag_id serial,
	tag_name text
);

grant all on tags to nobody;
create unique index tags_byname on tags(tag_name);

create function gettag(text) returns integer as
'
declare
	id integer;
begin
	select tag_id into id from tags where tag_name = $1;
	if not found then
		insert into tags(tag_name) values($1);
		select tag_id into id from tags where tag_name = $1;
	end if;
	return(id);
end;
' language 'plpgsql';

-- filenames

create table files (
	file_id serial,
	file_name text
);

grant all on files to nobody;
create unique index files_byname on files(file_name);

create function getfile(text) returns integer as
'
declare
	id integer;
begin
	select file_id into id from files where file_name = $1;
	if not found then
		insert into files(file_name) values($1);
		select file_id into id from files where file_name = $1;
	end if;
	return(id);
end;
' language 'plpgsql';

-- usernames

create table usernames (
	user_id serial,
	user_name text
);

grant all on usernames to nobody;
create unique index usernames_byname on usernames(user_name);

create function getuser(text) returns integer as
'
declare
	id integer;
begin
	select user_id into id from usernames where user_name = $1;
	if not found then
		insert into usernames(user_name) values($1);
		select user_id into id from usernames where user_name = $1;
	end if;
	return(id);
end;
' language 'plpgsql';

-- message data

create table messages (
	message_id serial,
	message text
);

grant all on messages to nobody;
create unique index message_text on messages(message);

create function getmsg(text) returns integer as
'
declare
	id integer;
begin
	select message_id into id from messages where message = $1;
	if not found then
		insert into messages(message) values($1);
		select message_id into id from messages where message = $1;
	end if;
	return(id);
end;
' language 'plpgsql';

-- actual event log

create table events (
	event_id serial,
	tag_id integer not null,
	priority integer not null,
	hostdate datetime not null,
	message_id integer not null,
	script_id integer not null,
	file_id integer not null,
	linenum integer not null,
	user_id integer not null,
	wwwuser_id integer not null,
	stack text not null,
	ts datetime default(datetime(now()))
);

grant all on events to nobody;
create index events_tag on events(tag_id);

-- example insert:
-- insert into events(tag_id, priority, hostdate, message_id, script_id,
--	 			file_id, linenum, user_id, wwwuser_id, stack)
--  values(gettag('badfunc'), 5, 'date time', getmsg('error message'),
--         getfile('/.../scripts/scriptname.pl'), getfile('/.../libs/name.pl'),
--			843, getuser('nobody'), getwwwuser('dustin'), 'stack trace');

create view event_view as
	select events.event_id, events.hostdate, events.ts, events.priority,
		tags.tag_name, events.stack,
		messages.message,
		usernames.user_name as user_name,
		wwwusers.user_name as wwwuser,
		files.file_name as filename,
		scripts.file_name as scriptname,
		events.linenum
	from events, tags, messages, usernames, usernames as wwwusers,
		files, files as scripts
	where events.tag_id=tags.tag_id
		and events.message_id=messages.message_id
		and usernames.user_id = events.user_id
		and wwwusers.user_id = events.wwwuser_id
		and files.file_id = events.file_id
		and scripts.file_id = events.script_id
;

grant all on event_view to nobody;

commit;