Interfaces first migration (#4635)
* Added (hopefully) working goose up section
* finished 'down' section, added 'ON UPDATE/DELETE' clauses
* Fixed check to handle IPv6, trim existing subnets from gateways
* Fixed typo
* Added type aliases for ease of array selection
diff --git a/traffic_ops/app/db/migrations/20200414000000_interfaces.sql b/traffic_ops/app/db/migrations/20200414000000_interfaces.sql
new file mode 100644
index 0000000..85e9c39e
--- /dev/null
+++ b/traffic_ops/app/db/migrations/20200414000000_interfaces.sql
@@ -0,0 +1,162 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+CREATE TABLE interface (
+ max_bandwidth bigint DEFAULT NULL CHECK (max_bandwidth IS NULL OR max_bandwidth >= 0),
+ monitor boolean NOT NULL,
+ mtu bigint DEFAULT 1500 CHECK (mtu IS NULL OR mtu > 1280),
+ name text NOT NULL CHECK (name != ''),
+ server bigint NOT NULL,
+ PRIMARY KEY (name, server),
+ FOREIGN KEY (server) REFERENCES server(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+CREATE TABLE ip_address (
+ address inet NOT NULL,
+ gateway inet CHECK (
+ gateway IS NULL OR (
+ family(gateway) = 4 AND
+ masklen(gateway) = 32
+ ) OR (
+ family(gateway) = 6 AND
+ masklen(gateway) = 128
+ )
+ ),
+ interface text NOT NULL,
+ server bigint NOT NULL,
+ service_address boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY (address, interface, server),
+ FOREIGN KEY (server) REFERENCES server(id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ FOREIGN KEY (interface, server) REFERENCES interface(name, server) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+CREATE TYPE server_ip_address AS (address inet, gateway inet, service_address boolean);
+CREATE TYPE server_interface AS (ip_addresses server_ip_address ARRAY, max_bandwidth bigint, monitor boolean, mtu bigint, name text);
+
+INSERT INTO interface(
+ max_bandwidth,
+ monitor,
+ mtu,
+ name,
+ server
+)
+SELECT NULL, FALSE, NULL, 'mgmt', id
+FROM server
+WHERE server.mgmt_ip_address IS NOT NULL
+AND server.mgmt_ip_address != '';
+
+INSERT INTO interface(
+ max_bandwidth,
+ monitor,
+ mtu,
+ name,
+ server
+)
+SELECT NULL, TRUE, server.interface_mtu::bigint, server.interface_name, id
+FROM server;
+
+INSERT INTO ip_address(
+ address,
+ gateway,
+ interface,
+ server,
+ service_address
+)
+SELECT
+ set_masklen(
+ server.mgmt_ip_address::inet,
+ CASE
+ WHEN server.mgmt_ip_netmask IS NULL THEN 32
+ WHEN server.mgmt_ip_netmask = '' THEN 32
+ ELSE
+ (SELECT SUM(
+ get_bit(digit, 0) +
+ get_bit(digit, 1) +
+ get_bit(digit, 2) +
+ get_bit(digit, 3) +
+ get_bit(digit, 4) +
+ get_bit(digit, 5) +
+ get_bit(digit, 6) +
+ get_bit(digit, 7)
+ )::int FROM (
+ SELECT regexp_split_to_table::int::bit(8) AS digit
+ FROM regexp_split_to_table(server.mgmt_ip_netmask, '\.')
+ ) AS digits)
+ END
+ ),
+ NULLIF(regexp_replace(server.mgmt_ip_gateway, '/\d+$', ''), '')::inet,
+ 'mgmt',
+ server.id,
+ FALSE
+FROM server
+WHERE server.mgmt_ip_address IS NOT NULL
+AND server.mgmt_ip_address != '';
+
+INSERT INTO ip_address(
+ address,
+ gateway,
+ interface,
+ server,
+ service_address
+)
+SELECT
+ set_masklen(
+ server.ip_address::inet,
+ CASE
+ WHEN server.ip_netmask IS NULL THEN 32
+ WHEN server.ip_netmask = '' THEN 32
+ ELSE
+ (SELECT SUM(
+ get_bit(digit, 0) +
+ get_bit(digit, 1) +
+ get_bit(digit, 2) +
+ get_bit(digit, 3) +
+ get_bit(digit, 4) +
+ get_bit(digit, 5) +
+ get_bit(digit, 6) +
+ get_bit(digit, 7)
+ )::int FROM (
+ SELECT regexp_split_to_table::int::bit(8) AS digit
+ FROM regexp_split_to_table(server.ip_netmask, '\.')
+ ) AS digits)
+ END
+ ),
+ NULLIF(regexp_replace(server.ip_gateway, '/\d+$', ''), '')::inet,
+ server.interface_name,
+ server.id,
+ server.ip_address_is_service
+FROM server
+WHERE server.ip_address IS NOT NULL
+AND server.ip_address != '';
+
+INSERT INTO ip_address(
+ address,
+ gateway,
+ interface,
+ server,
+ service_address
+)
+SELECT
+ trim(BOTH '[]' FROM server.ip6_address)::inet,
+ NULLIF(regexp_replace(server.ip6_gateway, '/\d+$', ''), '')::inet,
+ server.interface_name,
+ server.id,
+ server.ip6_address_is_service
+FROM server
+WHERE server.ip6_address IS NOT NULL
+AND server.ip6_address != '';
+
+-- +goose Down
+DROP TABLE IF EXISTS ip_address;
+DROP TABLE IF EXISTS interface;
