mysql.sql

BEGIN;

SET SESSION sql_mode = ansi;

CREATE TABLE projects (
    project         VARCHAR(255) PRIMARY KEY
                    COMMENT 'Unique Name of a project.',
    uri             VARCHAR(255) NULL UNIQUE
                    COMMENT 'Optional project URI',
    created_at      DATETIME(6)  NOT NULL
                    COMMENT 'Date the project was added to the database.',
    creator_name    VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who added the project.',
    creator_email   VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who added the project.'
) ENGINE  InnoDB,
  CHARACTER SET 'utf8',
  COMMENT 'Sqitch projects deployed to this database.'
;

CREATE TABLE changes (
    change_id       VARCHAR(40)  PRIMARY KEY
                    COMMENT 'Change primary key.',
    "change"        VARCHAR(255) NOT NULL
                    COMMENT 'Name of a deployed change.',
    project         VARCHAR(255) NOT NULL
                    COMMENT 'Name of the Sqitch project to which the change belongs.'
                    REFERENCES projects(project) ON UPDATE CASCADE,
    note            TEXT         NOT NULL
                    COMMENT 'Description of the change.',
    committed_at    DATETIME(6)  NOT NULL
                    COMMENT 'Date the change was deployed.',
    committer_name  VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who deployed the change.',
    committer_email VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who deployed the change.',
    planned_at      DATETIME(6)  NOT NULL
                    COMMENT 'Date the change was added to the plan.',
    planner_name    VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who planed the change.',
    planner_email   VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who planned the change.'
) ENGINE  InnoDB,
  CHARACTER SET 'utf8',
  COMMENT 'Tracks the changes currently deployed to the database.'
;

CREATE TABLE tags (
    tag_id          VARCHAR(40)  PRIMARY KEY
                    COMMENT 'Tag primary key.',
    tag             VARCHAR(255) NOT NULL
                    COMMENT 'Project-unique tag name.',
    project         VARCHAR(255) NOT NULL
                    COMMENT 'Name of the Sqitch project to which the tag belongs.'
                    REFERENCES projects(project) ON UPDATE CASCADE,
    change_id       VARCHAR(40)  NOT NULL
                    COMMENT 'ID of last change deployed before the tag was applied.'
                    REFERENCES changes(change_id) ON UPDATE CASCADE,
    note            VARCHAR(255) NOT NULL
                    COMMENT 'Description of the tag.',
    committed_at    DATETIME(6)  NOT NULL
                    COMMENT 'Date the tag was applied to the database.',
    committer_name  VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who applied the tag.',
    committer_email VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who applied the tag.',
    planned_at      DATETIME(6)  NOT NULL
                    COMMENT 'Date the tag was added to the plan.',
    planner_name    VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who planed the tag.',
    planner_email   VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who planned the tag.',
    UNIQUE(project, tag)
) ENGINE  InnoDB,
  CHARACTER SET 'utf8',
  COMMENT 'Tracks the tags currently applied to the database.'
;

CREATE TABLE dependencies (
    change_id       VARCHAR(40)  NOT NULL
                    COMMENT 'ID of the depending change.'
                    REFERENCES changes(change_id) ON UPDATE CASCADE ON DELETE CASCADE,
    type            VARCHAR(8)   NOT NULL
                    COMMENT 'Type of dependency.',
    dependency      VARCHAR(255) NOT NULL
                    COMMENT 'Dependency name.',
    dependency_id   VARCHAR(40)      NULL
                    COMMENT 'Change ID the dependency resolves to.'
                    REFERENCES changes(change_id) ON UPDATE CASCADE,
    PRIMARY KEY (change_id, dependency)
) ENGINE  InnoDB,
  CHARACTER SET 'utf8',
  COMMENT 'Tracks the currently satisfied dependencies.'
;

CREATE TABLE events (
    event           ENUM ('deploy', 'fail', 'revert') NOT NULL
                    COMMENT 'Type of event.',
    change_id       VARCHAR(40)  NOT NULL
                    COMMENT 'Change ID.',
    "change"        VARCHAR(255) NOT NULL
                    COMMENT 'Change name.',
    project         VARCHAR(255) NOT NULL
                    COMMENT 'Name of the Sqitch project to which the change belongs.'
                    REFERENCES projects(project) ON UPDATE CASCADE,
    note            TEXT         NOT NULL
                    COMMENT 'Description of the change.',
    requires        TEXT         NOT NULL
                    COMMENT 'List of the names of required changes.',
    conflicts       TEXT         NOT NULL
                    COMMENT 'List of the names of conflicting changes.',
    tags            TEXT         NOT NULL
                    COMMENT 'List of tags associated with the change.',
    committed_at    DATETIME(6)  NOT NULL
                    COMMENT 'Date the event was committed.',
    committer_name  VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who committed the event.',
    committer_email VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who committed the event.',
    planned_at      DATETIME(6)  NOT NULL
                    COMMENT 'Date the event was added to the plan.',
    planner_name    VARCHAR(255) NOT NULL
                    COMMENT 'Name of the user who planed the change.',
    planner_email   VARCHAR(255) NOT NULL
                    COMMENT 'Email address of the user who plan planned the change.',
    PRIMARY KEY (change_id, committed_at)
) ENGINE  InnoDB,
  CHARACTER SET 'utf8',
  COMMENT 'Contains full history of all deployment events.'
;

-- MySQL does not support checks, so we kind of create our own. The checkit()
-- function works sort of like a CHECK: if the first argument is 0 or NULL, it
-- throws the second argument as an exception. Conveniently, verify scripts
-- can also use it to ensure an error is thrown when a change cannot be
-- verified.

DELIMITER |

CREATE FUNCTION checkit(doit INTEGER, message VARCHAR(256)) RETURNS INTEGER DETERMINISTIC
BEGIN
    IF doit IS NULL OR doit = 0 THEN
        SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = message;
    END IF;
    RETURN doit;
END;
|

CREATE TRIGGER ck_insert_dependency BEFORE INSERT ON dependencies
FOR EACH ROW BEGIN
    -- DO does not work. http://bugs.mysql.com/bug.php?id=69647
    SET @dummy := checkit(
            (NEW.type = 'require'  AND NEW.dependency_id IS NOT NULL)
         OR (NEW.type = 'conflict' AND NEW.dependency_id IS NULL),
        'Type must be "require" with dependency_id set or "conflict" with dependency_id not set'
    );
END;
|

CREATE TRIGGER ck_update_dependency BEFORE UPDATE ON dependencies
FOR EACH ROW BEGIN
    -- DO does not work. http://bugs.mysql.com/bug.php?id=69647
    SET @dummy := checkit(
            (NEW.type = 'require'  AND NEW.dependency_id IS NOT NULL)
         OR (NEW.type = 'conflict' AND NEW.dependency_id IS NULL),
        'Type must be "require" with dependency_id set or "conflict" with dependency_id not set'
    );
END;
|

DELIMITER ;

COMMIT;