37 lines
1.0 KiB
MySQL
37 lines
1.0 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS `tab_db_version` (
|
||
|
|
`db_version_id` INT NOT NULL AUTO_INCREMENT,
|
||
|
|
`db_version` VARCHAR(100) NOT NULL,
|
||
|
|
`db_version_create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`db_version_update_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`db_version_id`),
|
||
|
|
UNIQUE INDEX `uq_tab_db_version_db_version` (`db_version` ASC));
|
||
|
|
|
||
|
|
DELIMITER $$
|
||
|
|
|
||
|
|
DROP PROCEDURE IF EXISTS `sp_set_db_version`$$
|
||
|
|
|
||
|
|
CREATE PROCEDURE `sp_set_db_version`(
|
||
|
|
IN `script_name` VARCHAR(100)
|
||
|
|
)
|
||
|
|
LANGUAGE SQL
|
||
|
|
DETERMINISTIC
|
||
|
|
MODIFIES SQL DATA
|
||
|
|
SQL SECURITY INVOKER
|
||
|
|
COMMENT 'Sets a DB version'
|
||
|
|
BEGIN
|
||
|
|
IF (script_name NOT LIKE '%.sql') THEN
|
||
|
|
SET script_name = CONCAT(script_name, '.sql');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
INSERT INTO `tab_db_version`(`db_version`)
|
||
|
|
VALUES(script_name)
|
||
|
|
ON DUPLICATE KEY UPDATE
|
||
|
|
`db_version` = VALUES(`db_version`);
|
||
|
|
END$$
|
||
|
|
|
||
|
|
DELIMITER ;
|
||
|
|
|
||
|
|
call `sp_set_db_version`('0000-initial-db-structure');
|
||
|
|
call `sp_set_db_version`('0001-initial-db-load');
|
||
|
|
call `sp_set_db_version`('0002-db-version');
|