-- This script upgrades the Sqitch registry for MySQL 5.5.0 and higher. It
-- creates the checkit() function and sets up triggers in the registry to use
-- it to emulate CHECK constraints. It will then also be available for use in
-- verify scripts, as described in sqitchtutorial-mysql. If you have an
-- existing Sqitch registry that was upgraded from an earlier version of MySQL
-- to 5.5.0 or highher, you'll need to run this script to update it, like so:
-- mysql -u root sqitch --execute "source `sqitch --etc`/tools/upgrade-registry-to-mysql-5.5.0.sql'
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 ;