Re: [Planner] SQL schema for planner

[Richard Hult <richard imendio com>]

tis 2004-01-06 klockan 17.29 skrev Mário Filipe:
> Hi
> 
> Is the sql schema for planner available anywhere?

It's distributed with the source code. In a coming release we should
install it as well, and more importantly, add database setup code to the
GUI so you don't need to do that manually. 

Attaching the schema for now,
Richard

-- 
Richard Hult                    richard imendio com
Imendio                         http://www.imendio.com

-- $Id: database.sql,v 1.1.1.1 2003/12/01 17:36:26 rhult Exp $

-- Planner Database Schema

-- Daniel Lundin <daniel codefactory se>
-- Richard Hult <richard imendio com>
-- Copyright 2003 CodeFactory AB

--
-- Project
--
CREATE TABLE project (
       	proj_id	         serial,
       	name           	 text NOT NULL,
	company		 text,
	manager		 text,
	proj_start	 date NOT NULL DEFAULT CURRENT_TIMESTAMP,
	cal_id	         integer,
	phase	         text,
	default_group_id integer,
	revision         integer,
	last_user        text NOT NULL DEFAULT (user),
	PRIMARY KEY (proj_id)
);
GRANT select,insert,update,delete ON project TO GROUP planner;
GRANT select,update ON project_proj_id_seq TO GROUP planner;

--
-- Phases
--
CREATE TABLE phase (
        phase_id        serial,
        proj_id         integer,
        name            text NOT NULL,
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (phase_id)
);
GRANT select,insert,update,delete ON phase TO GROUP planner;
GRANT select,update ON phase_phase_id_seq TO GROUP planner;

--
-- Day Types
--
CREATE TABLE daytype (
	dtype_id	serial,
       	proj_id		integer,
       	name           	text,
       	descr          	text,
	is_work	        boolean NOT NULL DEFAULT FALSE,
	is_nonwork      boolean NOT NULL DEFAULT FALSE,
	UNIQUE (proj_id, name),
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (dtype_id)
);
GRANT select,insert,update,delete ON daytype TO GROUP planner;
GRANT select,update ON daytype_dtype_id_seq TO GROUP planner;


--
-- Calendar
--
CREATE TABLE calendar (
	cal_id		serial,
       	proj_id		integer,
	parent_cid	integer,
       	name           	text,
	day_mon		integer DEFAULT NULL,
	day_tue		integer DEFAULT NULL,
	day_wed		integer DEFAULT NULL,
	day_thu		integer DEFAULT NULL,
	day_fri		integer DEFAULT NULL,
	day_sat		integer DEFAULT NULL,
	day_sun		integer DEFAULT NULL,
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (day_mon) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_tue) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_wed) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_thu) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_fri) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_sat) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (day_sun) REFERENCES daytype (dtype_id) 
		ON DELETE SET DEFAULT ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	FOREIGN KEY (parent_cid) REFERENCES calendar (cal_id) 
		ON DELETE SET NULL ON UPDATE CASCADE,
	PRIMARY KEY (cal_id)
);
GRANT select,insert,update,delete ON calendar TO GROUP planner;
GRANT select,update ON calendar_cal_id_seq TO GROUP planner;
ALTER TABLE project ADD CONSTRAINT project_cal_id 
	FOREIGN KEY (cal_id) REFERENCES calendar (cal_id) 
	ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;


--
-- Day
--
CREATE TABLE day (
	day_id		serial,
       	cal_id		integer,
	dtype_id	integer,
	date		date,	
	FOREIGN KEY (dtype_id) REFERENCES daytype (dtype_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (cal_id) REFERENCES calendar (cal_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (day_id)
);
GRANT select,insert,update,delete ON day TO GROUP planner;


--
-- Day (working) Interval
--
CREATE TABLE day_interval (
       	cal_id		integer,
	dtype_id	integer,
       	start_time      time with time zone,
       	end_time       	time with time zone,
	FOREIGN KEY (dtype_id) REFERENCES daytype (dtype_id) 
		ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
	FOREIGN KEY (cal_id) REFERENCES calendar (cal_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (dtype_id, cal_id, start_time, end_time)
);
GRANT select,insert,update,delete ON day_interval TO GROUP planner;


--
-- Task
--
CREATE TABLE task (
       	task_id           serial,
	parent_id	  integer,
	proj_id	          integer,
       	name              text NOT NULL,
	note		  text,
	start	          timestamp with time zone,
	finish	          timestamp with time zone,
	work	 	  integer DEFAULT 0,
	duration	  integer DEFAULT 0,
	percent_complete  integer DEFAULT 0,
	is_milestone	  boolean NOT NULL DEFAULT FALSE,
	is_fixed_work     boolean NOT NULL DEFAULT TRUE,
	constraint_type   text NOT NULL DEFAULT 'ASAP',
        constraint_time   timestamp with time zone,
	CHECK (constraint_type = 'ASAP' OR constraint_type = 'MSO' OR constraint_type = 'FNLT' OR constraint_type = 'SNET'),
 	CHECK (percent_complete > -1 AND percent_complete < 101),
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (parent_id) REFERENCES task (task_id) 
		ON DELETE SET NULL ON UPDATE CASCADE,
	PRIMARY KEY (task_id)
);
GRANT select,insert,update,delete ON task TO GROUP planner;
GRANT select,update ON task_task_id_seq TO GROUP planner;

-- FIXME: Add triggers to handle different types of tasks/milestones


--
-- Predecessor (tasks)
--
CREATE TABLE predecessor (
	task_id	 	 integer NOT NULL,
	pred_task_id	 integer NOT NULL,
       	pred_id          serial,
       	type             text NOT NULL DEFAULT 'FS',
        lag              integer DEFAULT 0,
	CHECK (type = 'FS' OR type = 'FF' OR type = 'SS' OR type = 'SF'),
	UNIQUE (pred_id),
	FOREIGN KEY (task_id) REFERENCES task (task_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (pred_task_id) REFERENCES task (task_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (task_id, pred_task_id)
);
GRANT select,insert,update,delete ON predecessor TO GROUP planner;
GRANT select,update ON predecessor_pred_id_seq TO GROUP planner;


--
-- Property types
--
CREATE TABLE property_type (
       	proptype_id    	  serial,
       	proj_id           integer,
       	name           	  text NOT NULL,
	label		  text NOT NULL,
	type		  text NOT NULL DEFAULT 'text',
	owner		  text NOT NULL DEFAULT 'project',
	descr		  text,
	CHECK (type = 'date' OR type = 'duration' OR type = 'float' 
	       OR type = 'int' OR type = 'text' OR type = 'text-list'
	       OR type = 'cost'),
	CHECK (owner = 'project' OR owner = 'task' OR owner = 'resource'),
	UNIQUE (proj_id, name),
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (proptype_id)
);
GRANT select,insert,update,delete ON property_type TO GROUP planner;
GRANT select,update ON property_type_proptype_id_seq TO GROUP planner;


--
-- Properties
--
CREATE TABLE property (
       	prop_id    	  serial,
	proptype_id	  integer NOT NULL,
	value		  text,
	FOREIGN KEY (proptype_id) REFERENCES property_type (proptype_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (prop_id)
);
GRANT select,insert,update,delete ON property TO GROUP planner;
GRANT select,update ON property_prop_id_seq TO GROUP planner;


--
-- Project properties
--
CREATE TABLE project_to_property (
       	proj_id         integer,
	prop_id	        integer,
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (prop_id) REFERENCES property (prop_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (proj_id, prop_id)
);
GRANT select,insert,update,delete ON project_to_property TO GROUP planner;


--
-- Task properties
--
CREATE TABLE task_to_property (
	prop_id	        integer,
       	task_id         integer,
	FOREIGN KEY (task_id) REFERENCES task (task_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (prop_id) REFERENCES property (prop_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (task_id, prop_id)
);
GRANT select,insert,update,delete ON task_to_property TO GROUP planner;


--
-- Resource Group
--
CREATE TABLE resource_group (
       	group_id         serial,
	proj_id	        integer,
       	name            text NOT NULL,
	admin_name	text,
	admin_phone	text,
	admin_email	text,
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (group_id)
);
GRANT select,insert,update,delete ON resource_group TO GROUP planner;
GRANT select,update ON resource_group_group_id_seq TO GROUP planner;


--
-- Resource
--
CREATE TABLE resource (
       	res_id          serial,
	proj_id	        integer,
	group_id	integer,
       	name            text,
	email		text,
	note		text,
	is_worker	boolean NOT NULL DEFAULT TRUE,
	units		real NOT NULL DEFAULT 1.0,
	std_rate	real NOT NULL DEFAULT 0.0,	
	ovt_rate	real NOT NULL DEFAULT 0.0,
	cal_id		integer,
	FOREIGN KEY (proj_id) REFERENCES project (proj_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (group_id) REFERENCES resource_group (group_id) 
		ON DELETE SET NULL ON UPDATE CASCADE,
	FOREIGN KEY (cal_id) REFERENCES calendar (cal_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (res_id)
);
GRANT select,insert,update,delete ON resource TO GROUP planner;
GRANT select,update ON resource_res_id_seq TO GROUP planner;


--
-- Resource properties
--
CREATE TABLE resource_to_property (
	prop_id	        integer,
       	res_id          integer,
	FOREIGN KEY (res_id) REFERENCES resource (res_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (prop_id) REFERENCES property (prop_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (res_id, prop_id)
);
GRANT select,insert,update,delete ON resource_to_property TO GROUP planner;


--
-- Allocations (of resources)
--
CREATE TABLE allocation (
	task_id	        integer,
       	res_id          integer,
	units		real NOT NULL DEFAULT 1.0,
	FOREIGN KEY (res_id) REFERENCES resource (res_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (task_id) REFERENCES task (task_id) 
		ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (res_id, task_id)
);
GRANT select,insert,update,delete ON allocation TO GROUP planner;