mirror of https://github.com/coder/coder.git
38 lines
1.1 KiB
SQL
38 lines
1.1 KiB
SQL
-- Add back the storage_source column. This must be nullable temporarily.
|
|
ALTER TABLE provisioner_jobs ADD COLUMN storage_source text;
|
|
|
|
-- Set the storage_source to the hash of the files.id reference.
|
|
UPDATE
|
|
provisioner_jobs
|
|
SET
|
|
storage_source=files.hash
|
|
FROM
|
|
files
|
|
WHERE
|
|
provisioner_jobs.file_id = files.id;
|
|
|
|
-- Now that we've populated storage_source drop the file_id column.
|
|
ALTER TABLE provisioner_jobs DROP COLUMN file_id;
|
|
-- We can set the storage_source column as NOT NULL now.
|
|
ALTER TABLE provisioner_jobs ALTER COLUMN storage_source SET NOT NULL;
|
|
|
|
-- Delete all the duplicate rows where hashes collide.
|
|
-- We filter on 'id' to ensure only 1 unique row.
|
|
DELETE FROM
|
|
files a
|
|
USING
|
|
files b
|
|
WHERE
|
|
a.created_by < b.created_by
|
|
AND
|
|
a.hash = b.hash;
|
|
|
|
-- Drop the primary key on files.id.
|
|
ALTER TABLE files DROP CONSTRAINT files_pkey;
|
|
-- Drop the id column.
|
|
ALTER TABLE files DROP COLUMN id;
|
|
-- Drop the unique constraint on hash + owner.
|
|
ALTER TABLE files DROP CONSTRAINT files_hash_created_by_key;
|
|
-- Set the primary key back to hash.
|
|
ALTER TABLE files ADD PRIMARY KEY (hash);
|