BEGIN;
CREATE TABLE releases (
version FLOAT PRIMARY KEY,
installed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
installer_name TEXT NOT NULL,
installer_email TEXT NOT NULL
);
-- Create a new changes table with script_hash.
CREATE TABLE new_changes (
change_id TEXT PRIMARY KEY,
script_hash TEXT NULL UNIQUE,
change TEXT NOT NULL,
project TEXT NOT NULL REFERENCES projects(project) ON UPDATE CASCADE,
note TEXT NOT NULL DEFAULT '',
committed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
committer_name TEXT NOT NULL,
committer_email TEXT NOT NULL,
planned_at DATETIME NOT NULL,
planner_name TEXT NOT NULL,
planner_email TEXT NOT NULL
);
-- Copy all the data to the new table and move it into place.
INSERT INTO new_changes
SELECT change_id, change_id, change, project, note,
committed_at, committer_name, committer_email,
planned_at, planner_name, planner_email
FROM changes;
PRAGMA foreign_keys = OFF;
DROP TABLE changes;
ALTER TABLE new_changes RENAME TO changes;
PRAGMA foreign_keys = ON;
-- Create a new events table with support for "merge" events.
CREATE TABLE new_events (
event TEXT NOT NULL CHECK (event IN ('deploy', 'revert', 'fail', 'merge')),
change_id TEXT NOT NULL,
change TEXT NOT NULL,
project TEXT NOT NULL REFERENCES projects(project) ON UPDATE CASCADE,
note TEXT NOT NULL DEFAULT '',
requires TEXT NOT NULL DEFAULT '',
conflicts TEXT NOT NULL DEFAULT '',
tags TEXT NOT NULL DEFAULT '',
committed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
committer_name TEXT NOT NULL,
committer_email TEXT NOT NULL,
planned_at DATETIME NOT NULL,
planner_name TEXT NOT NULL,
planner_email TEXT NOT NULL,
PRIMARY KEY (change_id, committed_at)
);
INSERT INTO new_events
SELECT * FROM events;
PRAGMA foreign_keys = OFF;
DROP TABLE events;
ALTER TABLE new_events RENAME TO events;
PRAGMA foreign_keys = ON;
COMMIT;