CREATE TABLE host_network ( public_ipv4_cidr_block TEXT NOT NULL, network_name TEXT NOT NULL, host TEXT NOT NULL REFERENCES hosts(id) ON DELETE RESTRICT, CONSTRAINT host_network_pkey PRIMARY KEY (host, network_name) ); INSERT INTO host_network (host, network_name, public_ipv4_cidr_block) VALUES ('baikal', 'virbr1', '69.61.2.162/27'), ('baikal', 'virbr2', '69.61.2.194/26'), ('baikal', 'virbr3', '69.61.38.193/26'); ALTER TABLE vms RENAME COLUMN last_seen_ipv4 TO public_ipv4; ALTER TABLE vms RENAME COLUMN last_seen_ipv6 TO public_ipv6; ALTER TABLE vms ADD COLUMN network_name TEXT; UPDATE vms SET network_name = 'virbr1' WHERE public_ipv4 < '69.61.2.192' OR public_ipv4 is NULL; UPDATE vms SET network_name = 'virbr2' WHERE public_ipv4 >= '69.61.2.192'; ALTER TABLE vms ALTER COLUMN network_name SET NOT NULL; ALTER TABLE vms ADD CONSTRAINT vms_host_network_name_fkey FOREIGN KEY (host, network_name) REFERENCES host_network(host, network_name) ON DELETE RESTRICT; UPDATE schemaversion SET version = 16;