56 lines
2.2 KiB
SQL
56 lines
2.2 KiB
SQL
-- +goose Up
|
|
CREATE TABLE IF NOT EXISTS medicine_groups (
|
|
id BINARY(16) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
sortkey INT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
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,
|
|
deleted_at DATETIME(6) NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
PRIMARY KEY (id),
|
|
KEY idx_medicine_groups_is_active (is_active),
|
|
KEY idx_medicine_groups_sortkey (sortkey)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS motor_reactions (
|
|
id BINARY(16) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
score BIGINT NULL,
|
|
sortkey INT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
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,
|
|
deleted_at DATETIME(6) NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
PRIMARY KEY (id),
|
|
KEY idx_motor_reactions_is_active (is_active),
|
|
KEY idx_motor_reactions_sortkey (sortkey)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE medicines
|
|
ADD COLUMN weight BIGINT NULL AFTER dose,
|
|
ADD COLUMN medicine_group_id BINARY(16) NULL AFTER `column`,
|
|
ADD COLUMN motor_reaction_id BINARY(16) NULL AFTER medicine_group_id,
|
|
ADD KEY idx_medicines_medicine_group_id (medicine_group_id),
|
|
ADD KEY idx_medicines_motor_reaction_id (motor_reaction_id),
|
|
ADD CONSTRAINT fk_medicines_medicine_group FOREIGN KEY (medicine_group_id) REFERENCES medicine_groups(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
|
ADD CONSTRAINT fk_medicines_motor_reaction FOREIGN KEY (motor_reaction_id) REFERENCES motor_reactions(id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
-- +goose Down
|
|
ALTER TABLE medicines
|
|
DROP FOREIGN KEY fk_medicines_medicine_group,
|
|
DROP FOREIGN KEY fk_medicines_motor_reaction,
|
|
DROP KEY idx_medicines_medicine_group_id,
|
|
DROP KEY idx_medicines_motor_reaction_id,
|
|
DROP COLUMN medicine_group_id,
|
|
DROP COLUMN motor_reaction_id,
|
|
DROP COLUMN weight;
|
|
|
|
DROP TABLE IF EXISTS motor_reactions;
|
|
DROP TABLE IF EXISTS medicine_groups;
|