36 lines
1.2 KiB
SQL
36 lines
1.2 KiB
SQL
-- +goose Up
|
|
CREATE TABLE IF NOT EXISTS user_roles (
|
|
id BINARY(16) NOT NULL,
|
|
user_id BINARY(16) NOT NULL,
|
|
role_id BINARY(16) NOT NULL,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
updated_by BINARY(16) NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY ux_user_role (user_id, role_id),
|
|
KEY idx_user_roles_user_id (user_id),
|
|
KEY idx_user_roles_role_id (role_id),
|
|
CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO user_roles (id, user_id, role_id, created_at, updated_at)
|
|
SELECT
|
|
UNHEX(REPLACE(UUID(), '-', '')),
|
|
users.id,
|
|
users.role_id,
|
|
CURRENT_TIMESTAMP(6),
|
|
CURRENT_TIMESTAMP(6)
|
|
FROM users
|
|
WHERE users.role_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM user_roles
|
|
WHERE user_roles.user_id = users.id
|
|
AND user_roles.role_id = users.role_id
|
|
);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS user_roles;
|