-------------------------------------------------------- -- -- WOHTU DATABASE CREATION SCRIPT -- -- Created by Petteri Kamppuri -- Last modified 4.3.2003 -- -------------------------------------------------------- DROP TABLE W_group CASCADE; DROP TABLE W_role CASCADE; DROP TABLE W_user CASCADE; DROP TABLE W_task CASCADE; DROP TABLE W_task_type CASCADE; DROP TABLE W_spent_time CASCADE; DROP TABLE W_defect_type CASCADE; DROP TABLE W_defect CASCADE; DROP TABLE W_test_report CASCADE; -- Group is one Ohtu project. CREATE TABLE W_group ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, description TEXT NOT NULL, start_date BIGINT NOT NULL, end_date BIGINT NOT NULL, mailing_list_address VARCHAR(50) NOT NULL, -- mailing list address, for example wohtu-list@cs.helsinki.fi linux_group VARCHAR(8) UNIQUE NOT NULL -- linux group name assigned for the project ); -- Each project member has a role: project manager, support manager etc. CREATE TABLE W_role ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, description TEXT NOT NULL ); -- User in Wohtu system. CREATE TABLE W_user ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, -- mandatory userid VARCHAR(8) UNIQUE NOT NULL, -- used for login to Wohtu system, mandatory password VARCHAR(8) NOT NULL, -- saved as md5 hash, mandatory email VARCHAR(50) NOT NULL, -- mandatory information phone VARCHAR(15) NOT NULL, description TEXT NOT NULL, role_id INTEGER NOT NULL DEFAULT -1, group_id INTEGER NOT NULL DEFAULT -1, user_level INTEGER NOT NULL, -- guest, member, project manager, admin FOREIGN KEY (role_id) REFERENCES W_role ON DELETE SET DEFAULT, FOREIGN KEY (group_id) REFERENCES W_group ON DELETE SET DEFAULT ); -- Each task is of certain type: coding, planning, desiging, testing etc. -- Not definable by groups, but given as standard CREATE TABLE W_task_type ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, description TEXT NOT NULL ); -- Task is one thing a Group has to do, project consists of tasks. -- Tasks lay out the schedule and so on. CREATE TABLE W_task ( id SERIAL PRIMARY KEY, group_id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, description TEXT NOT NULL, supertask INTEGER NOT NULL, -- possible parent task start_date BIGINT NOT NULL, end_date BIGINT NOT NULL, est_start_date BIGINT NOT NULL, est_end_date BIGINT NOT NULL, type_id INTEGER NOT NULL DEFAULT -1, time_estimate INTEGER NOT NULL, -- estimated time length in minutes size_estimate INTEGER NOT NULL, -- estimated size in some units allocated_to INTEGER NOT NULL DEFAULT -1, -- user to whom this task is allocated FOREIGN KEY (group_id) REFERENCES W_group ON DELETE CASCADE, FOREIGN KEY (supertask) REFERENCES W_task ON DELETE CASCADE, FOREIGN KEY (allocated_to) REFERENCES W_user ON DELETE SET DEFAULT, FOREIGN KEY (type_id) REFERENCES W_task_type ON DELETE SET DEFAULT ); -- The time project members spent on the project are targeted on one task. CREATE TABLE W_spent_time ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, spent_time INTEGER NOT NULL, -- time spent on the task, in minutes done_date INTEGER NOT NULL, -- date stamp for when the time was spent description TEXT NOT NULL, task_id INTEGER NOT NULL DEFAULT -1, -- possible to enter time without a specific task type_id INTEGER NOT NULL DEFAULT -1, size INTEGER NOT NULL DEFAULT -1, -- size of the product done during spent time (in any unit) FOREIGN KEY (user_id) REFERENCES W_user ON DELETE CASCADE, FOREIGN KEY (task_id) REFERENCES W_task ON DELETE SET DEFAULT, FOREIGN KEY (type_id) REFERENCES W_task_type ON DELETE SET DEFAULT ); -- There are different kinds of defects: code bugs, design document errors and so on. CREATE TABLE W_defect_type ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, description TEXT NOT NULL ); -- Each defect found during (and after) the project are reported and tracked. CREATE TABLE W_defect ( id SERIAL PRIMARY KEY, reporter_id INTEGER NOT NULL DEFAULT, when_noticed BIGINT NOT NULL, type_id INTEGER NOT NULL DEFAULT -1, when_fixed BIGINT NOT NULL DEFAULT -1, description TEXT NOT NULL, fix_time INTEGER NOT NULL DEFAULT -1, -- time it took to fix the defect in minutes injected_task INTEGER NOT NULL DEFAULT -1, -- the task during which this defect was created fixed_task INTEGER NOT NULL DEFAULT -1, -- the task during which this defect was fixed FOREIGN KEY (reporter_id) REFERENCES W_user ON DELETE CASCADE, FOREIGN KEY (type_id) REFERENCES W_defect_type ON DELETE SET DEFAULT, FOREIGN KEY (injected_task) REFERENCES W_task ON DELETE SET DEFAULT, FOREIGN KEY (fixed_task) REFERENCES W_task ON DELETE SET DEFAULT ); -- Each project has to do with a computer program, which consists of features. -- Each feature is something the program should be able to do. CREATE TABLE W_feature ( id SERIAL PRIMARY KEY, group_id INTEGER NOT NULL, name VARCHAR(100) UNIQUE NOT NULL, description TEXT NOT NULL, who_added INTERGER NOT NULL DEFAULT -1, when_added BIGINT NOT NULL, priority INTEGER NOT NULL, FOREIGN KEY (who_added) REFERENCES W_user ON DELETE SET DEFAULT FOREIGN KEY (group_id) REFERENCES W_group ON DELETE CASCADE ); -- Testers need to write test reports on how their tests have gone. CREATE TABLE W_test_report ( id SERIAL PRIMARY KEY, group_id INTEGER NOT NULL, reporter_id INTEGER NOT NULL DEFAULT, name VARCHAR(50) NOT NULL, objective TEXT NOT NULL, description TEXT NOT NULL, -- description of the test event conditions TEXT NOT NULL, expected_results TEXT NOT NULL, actual_results TEXT NOT NULL, FOREIGN KEY (reporter_id) REFERENCES W_user ON DELETE CASCADE, FOREIGN KEY (group_id) REFERENCES W_group ON DELETE CASCADE );