-- Start by using the correct search path
drop schema lm cascade;
create schema lm;
set search_path TO lm;


-- --------------------------------------------------------------------------
--                         CREATE THE TABLES
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
-- Create the permissions table
-- --------------------------------------------------------------------------
create table perm (
	name		text not null,			-- username/groupname 
	command		text not null,			-- command to allow/deny
	rule		text not null,			-- ALLOW or DENY
	check(rule = 'ALLOW' or rule = 'DENY')
);



-- --------------------------------------------------------------------------
-- Create the hosts table
-- --------------------------------------------------------------------------
-- This table defines all of the monitored hosts, and thier general info
--
-- serial		hostid			Internal host ID
-- text			host			hostname (fqdn)
-- int			boardid			internal motherboard ID for this host
-- int			osid			internal os ID for this host
-- int			statusid		internal status ID for this host
-- int			incarnation		not null if host was retired
--
-- unique values: (host, incarnation), hostid

create table hosts (
	host 		text 		not null, 
        board           text            not null,
        os              text            not null,
        status          text            not null,
	unique(host)
);
 

-- --------------------------------------------------------------------------
-- Create the hosts_config table
-- --------------------------------------------------------------------------
-- Contains 'collection of attributes' style configuration of hosts
--

create table hosts_config (
	host 		text not null,
	attrib		text not null,
	value		text not null
);

create index hosts_config_index_host on hosts_config (host);
create index hosts_config_index_attrib on hosts_config (attrib);


-- --------------------------------------------------------------------------
-- Create the exec_queue table
-- --------------------------------------------------------------------------
-- This table is used to issue/schedule/execute commands for the lm-srv process
--
--
-- serial		commandid		Unique ID for each command to be processed
-- timestamp	scheduled_time	Time at which the command was queue'ed
-- timestamp	start_time		Time at which the command should be started,
--								if this is NULL then it should be started
--								immediately.
-- text			command			command text, to be processed by lm-srv
-- text			issuer			username of user that issued the command
-- boolean		completed		true if the command has been completed
-- timestamp	exec_time		Time at which the command execution started
--								null if the command was never started
-- timestamp	finish_time		Time at which the command was finished
--								null if the command was never finished
-- text			result			This is the result text from the lm-srv
--								process... error messages/etc
--
-- unique values: commandid

create or replace function trigger_exec_queue_insert()
RETURNS opaque
AS 'BEGIN
	If new.issuer != current_user
	THEN RAISE EXCEPTION ''Issuer of command must match current db user.'';
	END IF;

	RETURN new;
	END;'
LANGUAGE 'plpgsql';

create table exec_queue (
	commandid 		serial, 
	scheduled_time 	timestamp		not null default now(), 
	start_time 		timestamp, 
	command 		text			not null, 
	arguments		text,
	issuer 			text			not null default current_user, 
	completed 		boolean			not null default 'f',
	exec_time		timestamp,
	finish_time		timestamp,
	result			text
);

create TRIGGER trigger_exec_queue
BEFORE INSERT
ON exec_queue
FOR EACH ROW
EXECUTE PROCEDURE trigger_exec_queue_insert();


-- --------------------------------------------------------------------------
-- Create sensors table
-- --------------------------------------------------------------------------
-- This is where the definition for each sensor get stored
--
-- serial		sensorid		Internal sensor ID
-- int			hostid			internal host id
-- text			sensor			Name of the sensor
-- text			type			Type of sensor (float/boolean/string)
-- text			label			Text label for the sensor (ex. CPU)
--
-- unique values (sensorid), (hostid, sensor)

create table sensors (
        host                    text            not null,
	sensor			text		not null,
	type			text		not null,
	label			text,

	unique(host, sensor)
);





-- --------------------------------------------------------------------------
-- Create sensor_config table
-- --------------------------------------------------------------------------
-- This table contains a 'collection of attributes' that define the
-- configuration for each sensor
--

create table sensors_config (
        host                    text not null,
        sensor                  text not null,
	attrib			text not null,
	value			text not null
);

create index sensors_config_index_attrib on sensors_config (attrib);


-- --------------------------------------------------------------------------
-- Table to hold board sensor-sensor dependencies
-- --------------------------------------------------------------------------
-- board/os     what board/os we are dealing with
-- sensor       sensor NAME that has a dependency
-- target       sensor NAME that it depends on
-- hand_tuned   a bit that says that this row is done by hand, and shouldn't
--              be changed automagically
--
-- Example:  board X/os Y has sensor temp0 (sensor) dependant on cpu0 (target)
create table board_sensordep (
    board           text not null,
    os              text not null,
    sensor          text not null,  
    target          text not null,
    hand_tuned      boolean not null default 'f' 
);

-- --------------------------------------------------------------------------
-- Grant permisions on all of the tables
-- --------------------------------------------------------------------------
-- Grant all for strivia and labstaff

grant all on exec_queue, exec_queue_commandid_seq, hosts, hosts_config, sensors, sensors_config, board_sensordep to strivia, group staff;
grant select on exec_queue, exec_queue_commandid_seq, hosts, hosts_config, sensors, sensors_config, board_sensordep to "www";


-- the permisions table should only be staff for write
grant all on perm to group staff;
grant select on perm to strivia;





