0663afb335
Currently, uniqueness for the following tables is handled by a unique index in access_tag: * firewall * load_balancer * postgres_resource * private_subnet * vm These tables previously used the unique index in access_tag to enforce a unique name per project. When we stop using access_tag for these, we still need the unique name per project/location enforced, so this adds a unique index per table to enforce uniqueness. Note that load_balancer does not have a location column, so for load balancer, the unique index is for the private_subnet_id and name. This also adds unique indexes for project_id and name for dns_zone and usage_alert, since they will be needed in the future. There are 3 additional tables that have project_id and name columns, but I don't believe they are created by users, so I'm not sure if a (project_id, name) unique index is appropriate (maybe only unique name index is appropriate, or something else): * github_installation * inference_endpoint * minio_cluster I noticed what I think is a bug, in that postgres_resource.name was unique by itself. This means that two separate projects/users cannot create PostgreSQL databases with the same name. I expect that was not desired, and stems from when postgres_resource was originally named postgres_server, so this drops the unique constraint on name when adding the unique index on (project_id, name). Additionally, the migration to add the project_id columns did not make each column a foreign key, so this makes each project_id column a foreign key referencing project.
50 lines
1.8 KiB
Ruby
50 lines
1.8 KiB
Ruby
# frozen_string_literal: true
|
|
|
|
Sequel.migration do
|
|
no_transaction
|
|
|
|
up do
|
|
%i[postgres_resource firewall private_subnet vm minio_cluster].each do |table|
|
|
add_index table, [:project_id, :location, :name], name: :"#{table}_project_id_location_name_uidx", unique: true, concurrently: true
|
|
end
|
|
|
|
%i[dns_zone usage_alert].each do |table|
|
|
add_index table, [:project_id, :name], name: :"#{table}_project_id_name_uidx", unique: true, concurrently: true
|
|
end
|
|
|
|
add_index :load_balancer, [:private_subnet_id, :name], name: :load_balancer_private_subnet_id_name_uidx, unique: true, concurrently: true
|
|
|
|
alter_table(:postgres_resource) do
|
|
drop_constraint :postgres_server_server_name_key
|
|
end
|
|
|
|
%i[api_key firewall load_balancer minio_cluster private_subnet vm].each do |table|
|
|
alter_table(table) do
|
|
add_foreign_key [:project_id], :project, name: :"#{table}_project_id_fkey"
|
|
end
|
|
end
|
|
end
|
|
|
|
down do
|
|
%i[api_key firewall load_balancer minio_cluster private_subnet vm].reverse_each do |table|
|
|
alter_table(table) do
|
|
drop_constraint :"#{table}_project_id_fkey"
|
|
end
|
|
end
|
|
|
|
alter_table(:postgres_resource) do
|
|
add_unique_constraint :name, name: :postgres_server_server_name_key
|
|
end
|
|
|
|
drop_index :load_balancer, [:private_subnet_id, :name], name: :load_balancer_private_subnet_id_name_uidx, unique: true, concurrently: true
|
|
|
|
%i[dns_zone usage_alert].reverse_each do |table|
|
|
drop_index table, [:project_id, :name], name: :"#{table}_project_id_name_uidx", unique: true, concurrently: true
|
|
end
|
|
|
|
%i[postgres_resource firewall private_subnet vm minio_cluster].reverse_each do |table|
|
|
drop_index table, [:project_id, :location, :name], name: :"#{table}_project_id_location_name_uidx", unique: true, concurrently: true
|
|
end
|
|
end
|
|
end
|