/*
* Sqitch database deployment metadata v1.0.;
*/
/*
* Required PAGE SIZE = 16384 to avoid error: "key size exceeds
* implementation restriction for index..."
*/
-- Table: releases
CREATE TABLE releases (
version FLOAT NOT NULL PRIMARY KEY,
installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
installer_name VARCHAR(255) NOT NULL,
installer_email VARCHAR(255) NOT NULL
);
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Sqitch registry releases.'
WHERE RDB$RELATION_NAME = 'RELEASES';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Version of the Sqitch registry.'
WHERE RDB$RELATION_NAME = 'RELEASES' AND RDB$FIELD_NAME = 'VERSION';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the registry release was installed.'
WHERE RDB$RELATION_NAME = 'VERSIONS' AND RDB$FIELD_NAME = 'INSTALLED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who installed the registry release.'
WHERE RDB$RELATION_NAME = 'VERSIONS' AND RDB$FIELD_NAME = 'INSTALLER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who installed the registry release.'
WHERE RDB$RELATION_NAME = 'VERSIONS' AND RDB$FIELD_NAME = 'INSTALLER_EMAIL';
-- Table: projects
CREATE TABLE projects (
project VARCHAR(255) NOT NULL PRIMARY KEY,
uri VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
creator_name VARCHAR(255) NOT NULL,
creator_email VARCHAR(255) NOT NULL
);
-- Description (comments)
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Sqitch projects deployed to this database.'
WHERE RDB$RELATION_NAME = 'PROJECTS';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Unique Name of a project.'
WHERE RDB$RELATION_NAME = 'PROJECTS' AND RDB$FIELD_NAME = 'PROJECT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Optional project URI.'
WHERE RDB$RELATION_NAME = 'PROJECTS' AND RDB$FIELD_NAME = 'URI';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the project was added to the database.'
WHERE RDB$RELATION_NAME = 'PROJECTS' AND RDB$FIELD_NAME = 'CREATED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who added the project.'
WHERE RDB$RELATION_NAME = 'PROJECTS' AND RDB$FIELD_NAME = 'CREATOR_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who added the project.'
WHERE RDB$RELATION_NAME = 'PROJECTS' AND RDB$FIELD_NAME = 'CREATOR_EMAIL';
-- Table: changes
CREATE TABLE changes (
change_id VARCHAR(40) NOT NULL PRIMARY KEY,
change VARCHAR(255) NOT NULL,
project VARCHAR(255) NOT NULL REFERENCES projects(project)
ON UPDATE CASCADE,
note BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
committed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
committer_name VARCHAR(255) NOT NULL,
committer_email VARCHAR(255) NOT NULL,
planned_at TIMESTAMP NOT NULL,
planner_name VARCHAR(255) NOT NULL,
planner_email VARCHAR(255) NOT NULL
);
-- Description (comments)
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Tracks the changes currently deployed to the database.'
WHERE RDB$RELATION_NAME = 'CHANGES';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Change primary key.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'CHANGE_ID';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of a deployed change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'CHANGE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the Sqitch project to which the change belongs.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'PROJECT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Description of the change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'NOTE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the change was deployed.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'COMMITTED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who deployed the change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'COMMITTER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who deployed the change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'COMMITTER_EMAIL';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the change was added to the plan.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'PLANNED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who planed the change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'PLANNER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who planned the change.'
WHERE RDB$RELATION_NAME = 'CHANGES' AND RDB$FIELD_NAME = 'PLANNER_EMAIL';
-- Table: tags
CREATE TABLE tags (
tag_id CHAR(40) NOT NULL PRIMARY KEY,
tag VARCHAR(250) NOT NULL,
project VARCHAR(255) NOT NULL REFERENCES projects(project)
ON UPDATE CASCADE,
change_id CHAR(40) NOT NULL REFERENCES changes(change_id)
ON UPDATE CASCADE,
note BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
committed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
committer_name VARCHAR(512) NOT NULL,
committer_email VARCHAR(512) NOT NULL,
planned_at TIMESTAMP NOT NULL,
planner_name VARCHAR(512) NOT NULL,
planner_email VARCHAR(512) NOT NULL,
UNIQUE(project, tag)
);
-- Description (comments)
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Tracks the tags currently applied to the database.'
WHERE RDB$RELATION_NAME = 'TAGS';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Tag primary key.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'TAG_ID';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Project-unique tag name.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'TAG';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the Sqitch project to which the tag belongs.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'PROJECT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'ID of last change deployed before the tag was applied.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'CHANGE_ID';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Description of the tag.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'NOTE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the tag was applied to the database.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'COMMITTED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who applied the tag.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'COMMITTER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who applied the tag.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'COMMITTER_EMAIL';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the tag was added to the plan.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'PLANNED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who planed the tag.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'PLANNER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who planned the tag.'
WHERE RDB$RELATION_NAME = 'TAGS' AND RDB$FIELD_NAME = 'PLANNER_EMAIL';
-- Table: dependencies
CREATE TABLE dependencies (
change_id CHAR(40) NOT NULL REFERENCES changes(change_id)
ON UPDATE CASCADE ON DELETE CASCADE,
type VARCHAR(8) NOT NULL,
dependency VARCHAR(512) NOT NULL,
dependency_id CHAR(40) REFERENCES changes(change_id)
ON UPDATE CASCADE CHECK (
(type = 'require' AND dependency_id IS NOT NULL)
OR (type = 'conflict' AND dependency_id IS NULL)
),
PRIMARY KEY (change_id, dependency)
);
-- Description (comments)
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Tracks the currently satisfied dependencies.'
WHERE RDB$RELATION_NAME = 'DEPENDENCIES';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'ID of the depending change.'
WHERE RDB$RELATION_NAME = 'DEPENDENCIES' AND RDB$FIELD_NAME = 'CHANGE_ID';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Type of dependency.'
WHERE RDB$RELATION_NAME = 'DEPENDENCIES' AND RDB$FIELD_NAME = 'TYPE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Dependency name.'
WHERE RDB$RELATION_NAME = 'DEPENDENCIES' AND RDB$FIELD_NAME = 'DEPENDENCY';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Change ID the dependency resolves to.'
WHERE RDB$RELATION_NAME = 'DEPENDENCIES' AND RDB$FIELD_NAME = 'DEPENDENCY_ID';
-- Table: events
CREATE TABLE events (
event VARCHAR(6) NOT NULL
CHECK (event IN ('deploy', 'revert', 'fail')),
change_id CHAR(40) NOT NULL,
change VARCHAR(512) NOT NULL,
project VARCHAR(255) NOT NULL REFERENCES projects(project)
ON UPDATE CASCADE,
note BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
requires BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
conflicts BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
tags BLOB SUB_TYPE TEXT DEFAULT '' NOT NULL,
committed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
committer_name VARCHAR(512) NOT NULL,
committer_email VARCHAR(512) NOT NULL,
planned_at TIMESTAMP NOT NULL,
planner_name VARCHAR(512) NOT NULL,
planner_email VARCHAR(512) NOT NULL,
PRIMARY KEY (change_id, committed_at)
);
-- Description (comments)
UPDATE RDB$RELATIONS SET
RDB$DESCRIPTION = 'Contains full history of all deployment events.'
WHERE RDB$RELATION_NAME = 'EVENTS';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Type of event.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'EVENT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Change ID.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'CHANGE_ID';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Change name.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'CHANGE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the Sqitch project to which the change belongs.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'PROJECT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Description of the change.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'NOTE';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Array of the names of required changes.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'REQUIRES';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Array of the names of conflicting changes.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'CONFLICTS';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Tags associated with the change.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'TAGS';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the event was committed.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'COMMITTED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who committed the event.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'COMMITTER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who committed the event.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'COMMITTER_EMAIL';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Date the event was added to the plan.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'PLANNED_AT';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Name of the user who planed the change.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'PLANNER_NAME';
UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = 'Email address of the user who plan planned the change.'
WHERE RDB$RELATION_NAME = 'EVENTS' AND RDB$FIELD_NAME = 'PLANNER_EMAIL';
COMMIT;