49 lines
2.0 KiB
SQL
49 lines
2.0 KiB
SQL
-- +goose Up
|
|
-- Remove BMD module artifacts: role-permission links, permissions, and table.
|
|
|
|
DELETE rp
|
|
FROM role_permissions rp
|
|
INNER JOIN permissions p ON p.id = rp.permission_id
|
|
WHERE p.`key` IN ('bmd.create', 'bmd.read', 'bmd.update', 'bmd.delete');
|
|
|
|
DELETE FROM permissions
|
|
WHERE `key` IN ('bmd.create', 'bmd.read', 'bmd.update', 'bmd.delete');
|
|
|
|
DROP TABLE IF EXISTS bmds;
|
|
|
|
-- +goose Down
|
|
-- Recreate BMD table and baseline permissions (without restoring historical data/links).
|
|
|
|
CREATE TABLE IF NOT EXISTS bmds (
|
|
id BINARY(16) PRIMARY KEY,
|
|
country_name VARCHAR(128) NOT NULL,
|
|
note VARCHAR(255) NULL,
|
|
sortkey INT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
eu_accession_code SMALLINT NOT NULL,
|
|
created_at DATETIME(3) NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME(3) NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME(3) NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
KEY idx_bmds_sortkey (sortkey),
|
|
KEY idx_bmds_is_active (is_active)
|
|
);
|
|
|
|
INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
|
|
SELECT UUID_TO_BIN(UUID()), 'Create BMD', 'bmd.create', 'Create BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
|
|
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.create');
|
|
|
|
INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
|
|
SELECT UUID_TO_BIN(UUID()), 'Read BMD', 'bmd.read', 'Read BMD', 0, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
|
|
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.read');
|
|
|
|
INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
|
|
SELECT UUID_TO_BIN(UUID()), 'Update BMD', 'bmd.update', 'Update BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
|
|
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.update');
|
|
|
|
INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
|
|
SELECT UUID_TO_BIN(UUID()), 'Delete BMD', 'bmd.delete', 'Delete BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
|
|
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.delete');
|