mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
8e684c8195
Updates coder/customers#365 This PR updates our migration framework to run all migrations in a single transaction. This is the same behavior we had in v1 and ensures that failed migrations don't bring the whole deployment down. If a migration fails now, it will automatically be rolled back to the previous version, allowing the deployment to continue functioning.
39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
-- This migration updates the files table to move the unique
|
|
-- constraint to be hash + created_by. This is necessary to
|
|
-- allow regular users who have been granted admin to a specific
|
|
-- template to be able to push and read files used for template
|
|
-- versions they create.
|
|
-- Prior to this collisions on file.hash were not an issue
|
|
-- since users who could push files could also read all files.
|
|
--
|
|
-- This migration also adds a 'files.id' column as the primary
|
|
-- key. As a side effect the provisioner_jobs must now reference
|
|
-- the files.id column since the 'hash' column is now ambiguous.
|
|
-- Drop the primary key on hash.
|
|
ALTER TABLE files DROP CONSTRAINT files_pkey;
|
|
|
|
-- Add an 'id' column and designate it the primary key.
|
|
ALTER TABLE files ADD COLUMN
|
|
id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid ();
|
|
|
|
-- Update the constraint to include the user who created it.
|
|
ALTER TABLE files ADD UNIQUE(hash, created_by);
|
|
|
|
-- Update provisioner_jobs to include a file_id column.
|
|
-- This must be temporarily nullable.
|
|
ALTER TABLE provisioner_jobs ADD COLUMN file_id uuid;
|
|
|
|
-- Update all the rows to point to key in the files table.
|
|
UPDATE provisioner_jobs
|
|
SET
|
|
file_id = files.id
|
|
FROM
|
|
files
|
|
WHERE
|
|
provisioner_jobs.storage_source = files.hash;
|
|
|
|
-- Enforce NOT NULL on file_id now.
|
|
ALTER TABLE provisioner_jobs ALTER COLUMN file_id SET NOT NULL;
|
|
-- Drop storage_source since it is no longer useful for anything.
|
|
ALTER TABLE provisioner_jobs DROP COLUMN storage_source;
|