Re: [Planner] Database work plan
- From: "lincoln phipps openmutual net" <lincoln phipps openmutual net>
- To: Planner Project Manager <planner lists imendio com>
- Subject: Re: [Planner] Database work plan
- Date: Sun, 27 Jun 2004 12:44:14 +0100
David Goodenough wrote:
I recently came across Planner, and installed it on my system (Debian
unstable with KDE as my desktop). I tried to use the database feature
from the File->Save to Database menu entry. But it then complained that
I did not have a relation Project did not exist. The Debian project does
not seem to ship any documentation(at least that I can open, I guess
I do not have enough of Gnome installed to read the Help) and I can
not find anything telling me what I need to set up in the DB. There is
no SQL to create tables as far as I can tell.
Where do I find this documentation?
The SQL commands to create planner tables are not yet shipped
as part of the main release but if you get a CVS copy then
it has the files under the docs\sql directory.
What we'd like to do is to not have people worry about this
and make the database operations like create etc have a GUI.
FYI: Attached are the current SQL and DTD files and the README
from CVS.
Rgds,
Lincoln.
Thanks in advance
David
_______________________________________________
Planner mailing list
Planner lists imendio com
http://lists.imendio.com/mailman/listinfo/planner
<?xml version ='1.0' encoding='UTF-8'?>
<!ELEMENT project (properties*,phases?,calendars?,tasks?,resource-groups?,resources?,allocations?)>
<!ATTLIST project mrproject-version CDATA #REQUIRED
name CDATA #REQUIRED
company CDATA #IMPLIED
manager CDATA #IMPLIED
project-start CDATA #REQUIRED
calendar CDATA #IMPLIED
phase CDATA #IMPLIED>
<!ELEMENT properties (property*)>
<!--
Note: A mistake in the format design led to this suboptimality. A
"property" tag can be both a property declaration and a property
value. When it's a declaration, all attributes except "value"
are required and allowed, and only "name" and "value" are
required and allowed. We'll fix this for the new GSF based xml format.
-->
<!ELEMENT property (list-item*)>
<!ATTLIST property name CDATA #REQUIRED
type (date|duration|float|int|text|text-list|cost) #IMPLIED
owner (project|task|resource) #IMPLIED
label CDATA #IMPLIED
description CDATA #IMPLIED
value CDATA #IMPLIED>
<!ELEMENT list-item EMPTY>
<!ATTLIST list-item value CDATA #REQUIRED>
<!ELEMENT phases (phase*)>
<!ELEMENT phase EMPTY>
<!ATTLIST phase name CDATA #REQUIRED>
<!ELEMENT predecessors (predecessor*)>
<!ELEMENT constraint EMPTY>
<!ATTLIST constraint type CDATA #REQUIRED
time CDATA #REQUIRED>
<!ELEMENT predecessor EMPTY>
<!ATTLIST predecessor id CDATA #REQUIRED
predecessor-id CDATA #REQUIRED
type (FS|FF|SS|SF) "FS"
lag CDATA #IMPLIED>
<!ELEMENT tasks (task*)>
<!ELEMENT task (properties?,constraint?,predecessors?,task*)>
<!ATTLIST task id CDATA #REQUIRED
name CDATA #REQUIRED
note CDATA #IMPLIED
effort CDATA #IMPLIED
start CDATA #REQUIRED
end CDATA #REQUIRED
duration CDATA #IMPLIED
work CDATA #IMPLIED
percent-complete CDATA #IMPLIED
priority CDATA #IMPLIED
type (normal|milestone) "normal"
scheduling (fixed-work|
fixed-duration) "fixed-work">
<!ELEMENT resource-groups (group*)>
<!ATTLIST resource-groups default_group CDATA #IMPLIED>
<!ELEMENT group EMPTY>
<!ATTLIST group id CDATA #REQUIRED
name CDATA #REQUIRED
admin-name CDATA #IMPLIED
admin-email CDATA #IMPLIED
admin-phone CDATA #IMPLIED>
<!ELEMENT resources (resource*)>
<!ELEMENT resource (properties?)>
<!ATTLIST resource id CDATA #REQUIRED
name CDATA #REQUIRED
short-name CDATA #IMPLIED
email CDATA #IMPLIED
type (1|2) #REQUIRED
group CDATA #IMPLIED
units CDATA #REQUIRED
note CDATA #IMPLIED
std-rate CDATA #IMPLIED
ovt-rate CDATA #IMPLIED
calendar CDATA #IMPLIED>
<!ELEMENT allocations (allocation*)>
<!ELEMENT allocation EMPTY>
<!ATTLIST allocation task-id CDATA #REQUIRED
resource-id CDATA #REQUIRED
units CDATA #IMPLIED>
<!ELEMENT calendars (day-types,calendar*)>
<!ELEMENT day-types (day-type*)>
<!ELEMENT day-type (interval*)>
<!ATTLIST day-type id CDATA #REQUIRED
name CDATA #REQUIRED
description CDATA #REQUIRED>
<!ELEMENT interval EMPTY>
<!ATTLIST interval start CDATA #REQUIRED
end CDATA #REQUIRED>
<!ELEMENT calendar (default-week,overridden-day-types?,days?,calendar*)>
<!ATTLIST calendar name CDATA #REQUIRED
id CDATA #REQUIRED>
<!ELEMENT default-week EMPTY>
<!ATTLIST default-week mon CDATA #IMPLIED
tue CDATA #IMPLIED
wed CDATA #IMPLIED
thu CDATA #IMPLIED
fri CDATA #IMPLIED
sat CDATA #IMPLIED
sun CDATA #IMPLIED>
<!ELEMENT overridden-day-types (overridden-day-type*)>
<!ELEMENT overridden-day-type (interval*)>
<!ATTLIST overridden-day-type id CDATA #REQUIRED>
<!ELEMENT days (day*)>
<!ELEMENT day (interval*)>
<!ATTLIST day date CDATA #REQUIRED
type CDATA #REQUIRED
id CDATA #IMPLIED>
-- $Id: database-0.11.sql,v 1.2 2004/04/06 20:38:23 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,
priority 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),
CHECK (priority > -1 AND priority < 10000),
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,
short_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;
This is a plugin that implements a backend for keeping project data in a
postgres SQL database.
A very brief getting started howto (change username etc to use...):
* Init database (WARNING: This is usually already done on a working system):
initdb -D /tmp/test-db
* Start server (WARNING: This is usually already done on a working system):
postmaster -D /tmp/test-db
* Enable access to another user than the default:
echo 'CREATE USER rhult CREATEDB;' | psql -e
* Create a UNICODE database:
createdb -E UNICODE -U rhult plannerdb
* Create database group:
echo 'CREATE GROUP planner WITH USER rhult;' | psql -e -U rhult -d plannerdb
* Create tables from new. This depends upon the schema that you are up to
in your code. Use the highest versioned SQL file.
e.g.
cat database.sql | psql -e -U rhult -d plannerdb
or e.g.
cat database-0.11.sql | psql -e -U rhult -d plannerdb
* Update Tables (in existing database). If you already have a database then
just update this to the new schema. Use the appropriate upgrade file based
on your old and new schema,
e.g.
cat upgrade-0.6.x-0.11.sql | psql -e -U rhult -d plannerdb
* Drop database and group to start over:
echo 'DROP GROUP planner;' | psql -e -U rhult -d plannerdb
dropdb plannerdb
-- Planner Database Schema Upgrade from 0.6.x to 0.11
-- Lincoln Phipps <lincoln phipps openmutual net>
-- Copyright 2004 Lincoln Phipps
--
-- Resource Schema Changes from 0.6.x to 0.11
--
ALTER TABLE resource ADD COLUMN short_name text;
ALTER TABLE task ADD COLUMN priority integer;
ALTER TABLE task ALTER COLUMN priority SET DEFAULT 0;
ALTER TABLE task ADD CHECK (priority > -1 AND priority < 10000);
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
