31 lines
1.0 KiB
SQL
31 lines
1.0 KiB
SQL
-- +goose Up
|
|
ALTER TABLE file_files
|
|
ADD COLUMN lifecycle_status VARCHAR(32) NOT NULL DEFAULT 'temp' AFTER status,
|
|
ADD COLUMN attached_at DATETIME(6) NULL AFTER lifecycle_status,
|
|
ADD COLUMN orphaned_at DATETIME(6) NULL AFTER attached_at,
|
|
ADD COLUMN lifecycle_deleted_at DATETIME(6) NULL AFTER orphaned_at,
|
|
ADD KEY idx_file_files_lifecycle_status_orphaned (lifecycle_status, orphaned_at);
|
|
|
|
UPDATE file_files
|
|
SET lifecycle_status = CASE
|
|
WHEN deleted_at IS NOT NULL THEN 'deleted'
|
|
WHEN status = 'ready' AND name_slot = 'live' THEN 'active'
|
|
ELSE 'temp'
|
|
END,
|
|
attached_at = CASE
|
|
WHEN status = 'ready' AND name_slot = 'live' AND deleted_at IS NULL THEN COALESCE(attached_at, updated_at)
|
|
ELSE attached_at
|
|
END,
|
|
lifecycle_deleted_at = CASE
|
|
WHEN deleted_at IS NOT NULL THEN COALESCE(lifecycle_deleted_at, deleted_at)
|
|
ELSE lifecycle_deleted_at
|
|
END;
|
|
|
|
-- +goose Down
|
|
ALTER TABLE file_files
|
|
DROP KEY idx_file_files_lifecycle_status_orphaned,
|
|
DROP COLUMN lifecycle_deleted_at,
|
|
DROP COLUMN orphaned_at,
|
|
DROP COLUMN attached_at,
|
|
DROP COLUMN lifecycle_status;
|