# Database Script for MS Project™ plans
# V2; 2022-Jan-05, Gabriel Rüeck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>
# Delete existing databases
REVOKE ALL ON projects.* FROM 'gabriel';
DROP DATABASE projects;

# Create a new database
CREATE DATABASE projects;
GRANT ALL ON projects.* TO 'gabriel';
USE projects;

CREATE TABLE projects         (proj_uid               SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,\
                               proj_name              VARCHAR(80)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE tasks            (proj_uid               SMALLINT UNSIGNED NOT NULL,\
                               task_uid               SMALLINT UNSIGNED NOT NULL,\
                               task_name              VARCHAR(150),\
                               is_summary_task        BOOLEAN           DEFAULT FALSE NOT NULL,\
                               is_milestone           BOOLEAN           DEFAULT FALSE NOT NULL,\
                               is_critical            BOOLEAN           DEFAULT FALSE NOT NULL,\
                               task_start             DATETIME          NOT NULL,\
                               task_finish            DATETIME          NOT NULL,\
                               task_duration          INT UNSIGNED      DEFAULT 0,\
                               task_work              INT UNSIGNED      DEFAULT 0,\
                               free_slack             INT               DEFAULT 0,\
                               total_slack            INT               DEFAULT 0,\
                               finish_slack           INT               DEFAULT 0,\
                               rem_duration           INT UNSIGNED      DEFAULT 0,\
                               rem_work               INT UNSIGNED      DEFAULT 0,\
                               `%_complete`           SMALLINT UNSIGNED DEFAULT 0,\
                                                      PRIMARY KEY (proj_uid, task_uid)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE task_links       (proj_uid               SMALLINT UNSIGNED NOT NULL,\
                               predecessor            SMALLINT UNSIGNED NOT NULL,\
                               successor              SMALLINT UNSIGNED NOT NULL,
                               link_lag               INT               DEFAULT 0) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE resources        (proj_uid               SMALLINT UNSIGNED NOT NULL,\
                               resource_uid           SMALLINT UNSIGNED NOT NULL,\
                               calendar_uid           SMALLINT UNSIGNED NOT NULL,\
                               resource_units         DECIMAL(3,2)      DEFAULT 1.00,\
                               resource_name          VARCHAR(100),\
                                                      PRIMARY KEY (proj_uid, resource_uid)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE assignments      (proj_uid               SMALLINT UNSIGNED NOT NULL,\
                               task_uid               SMALLINT UNSIGNED NOT NULL,\
                               resource_uid           SMALLINT UNSIGNED NOT NULL,\
                               assignment_uid         SMALLINT UNSIGNED NOT NULL,\
                               assignment_start       DATETIME          NOT NULL,\
                               assignment_finish      DATETIME          NOT NULL,\
                               assignment_work        INT UNSIGNED      DEFAULT 0,\
                                                      PRIMARY KEY (proj_uid, task_uid, resource_uid)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE assignment_data  (proj_uid               SMALLINT UNSIGNED NOT NULL,\
                               assignment_uid         SMALLINT UNSIGNED NOT NULL,\
                               assignment_start       DATETIME          NOT NULL,\
                               assignment_finish      DATETIME          NOT NULL,\
                               assignment_work        INT UNSIGNED      DEFAULT 0) ENGINE=MyISAM DEFAULT CHARSET=utf8;
