bring over migration scripts from verivue

diff --git a/migrations/106-down-newinterface.sql b/migrations/106-down-newinterface.sql
new file mode 100644 (file)
index 0000000..2296cde
--- /dev/null
+++ b/migrations/106-down-newinterface.sql
@@ -0,0 +1,119 @@
+DELETE FROM network_types WHERE type='ipv6';
+
+ALTER TABLE interfaces ADD COLUMN gateway text;
+ALTER TABLE interfaces ADD COLUMN dns1 text;
+ALTER TABLE interfaces ADD COLUMN dns2 text;
+ALTER TABLE interfaces ADD COLUMN type text;
+ALTER TABLE interfaces ADD COLUMN ip text;
+ALTER TABLE interfaces ADD COLUMN network text;
+ALTER TABLE interfaces ADD COLUMN broadcast text;
+ALTER TABLE interfaces ADD COLUMN netmask text;
+
+DROP VIEW view_interfaces;
+CREATE OR REPLACE VIEW view_interfaces AS
+SELECT
+interfaces.interface_id,
+interfaces.node_id,
+interfaces.is_primary,
+interfaces.type,
+interfaces.method,
+interfaces.ip,
+interfaces.mac,
+interfaces.gateway,
+interfaces.network,
+interfaces.broadcast,
+interfaces.netmask,
+interfaces.dns1,
+interfaces.dns2,
+interfaces.bwlimit,
+interfaces.hostname,
+CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
+COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
+FROM interfaces;
+
+DROP VIEW view_interface_tags;
+CREATE OR REPLACE VIEW view_interface_tags AS
+SELECT
+interface_tag.interface_tag_id,
+interface_tag.interface_id,
+interfaces.ip,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+interface_tag.value
+FROM interface_tag
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN interfaces USING (interface_id);
+
+
+DROP VIEW view_nodes;
+CREATE OR REPLACE VIEW view_nodes AS
+SELECT
+nodes.node_id,
+nodes.node_type,
+nodes.hostname,
+nodes.site_id,
+nodes.boot_state,
+nodes.run_level,
+nodes.deleted,
+nodes.model,
+nodes.boot_nonce,
+nodes.version,
+nodes.verified,
+nodes.ssh_rsa_key,
+nodes.key,
+CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
+CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
+CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,  
+CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,  
+CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,  
+CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,  
+CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,  
+nodes.last_time_spent_online,
+nodes.last_time_spent_offline,
+peer_node.peer_id,
+peer_node.peer_node_id,
+COALESCE((SELECT interface_ids FROM node_interfaces 
+                WHERE node_interfaces.node_id = nodes.node_id), '{}') 
+AS interface_ids,
+COALESCE((SELECT nodegroup_ids FROM node_nodegroups 
+                WHERE node_nodegroups.node_id = nodes.node_id), '{}') 
+AS nodegroup_ids,
+COALESCE((SELECT slice_ids FROM node_slices 
+                WHERE node_slices.node_id = nodes.node_id), '{}') 
+AS slice_ids,
+COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist 
+                WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') 
+AS slice_ids_whitelist,
+COALESCE((SELECT pcu_ids FROM node_pcus 
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS pcu_ids,
+COALESCE((SELECT ports FROM node_pcus
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS ports,
+COALESCE((SELECT conf_file_ids FROM node_conf_files
+                WHERE node_conf_files.node_id = nodes.node_id), '{}') 
+AS conf_file_ids,
+COALESCE((SELECT node_tag_ids FROM node_tags 
+                WHERE node_tags.node_id = nodes.node_id), '{}') 
+AS node_tag_ids,
+node_session.session_id AS session
+FROM nodes
+LEFT JOIN peer_node USING (node_id)
+LEFT JOIN node_session USING (node_id);
+
+ALTER TABLE interfaces DROP COLUMN if_name;
+ALTER TABLE nodes DROP COLUMN dns;
+ALTER TABLE nodes DROP COLUMN ntp;
+
+DROP VIEW node_routes;
+DROP VIEW view_routes;
+DROP TABLE routes;
+
+DROP VIEW interface_ip_addresses;
+DROP VIEW view_ip_addresses;
+DROP INDEX ip_addresses_interface_id_idx;
+DROP TABLE ip_addresses;
+
+UPDATE plc_db_version SET subversion = 105;

diff --git a/migrations/106-up-newinterface.sql b/migrations/106-up-newinterface.sql
new file mode 100644 (file)
index 0000000..394502d
--- /dev/null
+++ b/migrations/106-up-newinterface.sql
@@ -0,0 +1,184 @@
+INSERT INTO network_types (type) VALUES ('ipv6');
+
+CREATE TABLE ip_addresses (
+    -- Mandatory
+    ip_address_id serial PRIMARY KEY,                  -- Network interface identifier
+    interface_id integer REFERENCES interfaces NOT NULL,               -- Which interface
+    type text REFERENCES network_types NOT NULL,       -- ipv4 or ipv6
+    ip_addr text NOT NULL,                             -- IP address
+    netmask text NOT NULL,                              -- Netmask
+    last_updated timestamp without time zone -- When the interface was last updated
+) WITH OIDS;
+CREATE INDEX ip_addresses_interface_id_idx ON ip_addresses (interface_id);
+
+CREATE OR REPLACE VIEW interface_ip_addresses AS
+SELECT interface_id,
+array_accum(ip_address_id) AS ip_address_ids
+FROM ip_addresses
+GROUP BY interface_id;
+
+CREATE OR REPLACE VIEW view_ip_addresses AS
+SELECT
+ip_addresses.ip_address_id,
+ip_addresses.interface_id,
+ip_addresses.type,
+ip_addresses.ip_addr,
+ip_addresses.netmask,
+CAST(date_part('epoch', ip_addresses.last_updated) AS bigint) AS last_updated
+FROM ip_addresses;
+
+DROP VIEW view_interfaces;
+-- FIXME: Why was this needed?
+-- DROP VIEW tagvalue_view_interface_id_ifname;
+DROP VIEW view_interface_tags;
+
+INSERT INTO ip_addresses (interface_id, type, ip_addr, netmask)
+SELECT interface_id, type, ip as ip_addr, netmask FROM interfaces;
+
+ALTER TABLE interfaces DROP COLUMN type;
+ALTER TABLE interfaces DROP COLUMN ip;
+ALTER TABLE interfaces DROP COLUMN network;
+ALTER TABLE interfaces DROP COLUMN broadcast;
+ALTER TABLE interfaces DROP COLUMN netmask;
+ALTER TABLE interfaces ADD COLUMN if_name text;
+
+CREATE OR REPLACE VIEW view_interfaces AS
+SELECT
+interfaces.interface_id,
+interfaces.node_id,
+interfaces.is_primary,
+interfaces.method,
+interfaces.mac,
+interfaces.bwlimit,
+interfaces.hostname,
+interfaces.if_name,
+CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
+COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids,
+COALESCE((SELECT ip_address_ids FROM interface_ip_addresses WHERE interface_ip_addresses.interface_id = interfaces.interface_id), '{}') AS ip_address_ids
+FROM interfaces;
+
+CREATE OR REPLACE VIEW view_interface_tags AS
+SELECT
+interface_tag.interface_tag_id,
+interface_tag.interface_id,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+interface_tag.value
+FROM interface_tag
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN interfaces USING (interface_id);
+
+CREATE TABLE routes (
+    -- Mandatory
+    route_id serial PRIMARY KEY,                       -- Network interface identifier
+    node_id integer REFERENCES nodes NOT NULL,          -- Which node
+    subnet text NOT NULL,                              -- Subnet route applies to
+    next_hop text NOT NULL,                            -- Next hop IP address
+    interface_id integer REFERENCES interfaces NOT NULL, -- Outgoing interface
+    last_updated timestamp without time zone -- When the interface was last updated
+) WITH OIDS;
+CREATE INDEX routes_node_id_idx ON routes (node_id);
+
+CREATE OR REPLACE VIEW view_routes AS
+SELECT
+routes.route_id,
+routes.node_id,
+routes.subnet,
+routes.next_hop,
+routes.interface_id,
+CAST(date_part('epoch', routes.last_updated) AS bigint) AS last_updated
+FROM routes;
+
+CREATE OR REPLACE VIEW node_routes AS
+SELECT node_id,
+array_accum(route_id) AS route_ids
+FROM routes
+GROUP BY node_id;
+
+ALTER TABLE nodes ADD COLUMN dns text;
+ALTER TABLE nodes ADD COLUMN ntp text;
+
+-- Insert the default gateways from the primary interfaces on each node as
+-- a route with subnet=0.0.0.0/0 and next_hop=gateway
+INSERT INTO routes (subnet, node_id, interface_id, next_hop)
+SELECT '0.0.0.0/0' as subnet, node_id, interface_id, gateway as next_hop FROM interfaces WHERE gateway is not NULL and is_primary is true;
+
+-- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
+-- for primary interfaces with one dns server
+UPDATE nodes SET dns = dns1 FROM interfaces
+WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is NULL;
+
+-- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
+-- for primary interfaces with two dns servers
+UPDATE nodes SET dns = dns1 || ', ' || dns2 FROM interfaces
+WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is not NULL;
+
+-- now we can drop the gateway and dns columns from interface
+ALTER TABLE interfaces DROP COLUMN gateway;
+ALTER TABLE interfaces DROP COLUMN dns1;
+ALTER TABLE interfaces DROP COLUMN dns2;
+
+DROP VIEW view_nodes;
+CREATE OR REPLACE VIEW view_nodes AS
+SELECT
+nodes.node_id,
+nodes.node_type,
+nodes.hostname,
+nodes.site_id,
+nodes.boot_state,
+nodes.run_level,
+nodes.deleted,
+nodes.model,
+nodes.boot_nonce,
+nodes.version,
+nodes.verified,
+nodes.ssh_rsa_key,
+nodes.dns,
+nodes.ntp,
+nodes.key,
+CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
+CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
+CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
+CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
+CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
+CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
+CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
+nodes.last_time_spent_online,
+nodes.last_time_spent_offline,
+peer_node.peer_id,
+peer_node.peer_node_id,
+COALESCE((SELECT interface_ids FROM node_interfaces
+                WHERE node_interfaces.node_id = nodes.node_id), '{}')
+AS interface_ids,
+COALESCE((SELECT nodegroup_ids FROM node_nodegroups
+                WHERE node_nodegroups.node_id = nodes.node_id), '{}')
+AS nodegroup_ids,
+COALESCE((SELECT slice_ids FROM node_slices
+                WHERE node_slices.node_id = nodes.node_id), '{}')
+AS slice_ids,
+COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
+                WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
+AS slice_ids_whitelist,
+COALESCE((SELECT pcu_ids FROM node_pcus
+                WHERE node_pcus.node_id = nodes.node_id), '{}')
+AS pcu_ids,
+COALESCE((SELECT ports FROM node_pcus
+                WHERE node_pcus.node_id = nodes.node_id), '{}')
+AS ports,
+COALESCE((SELECT conf_file_ids FROM node_conf_files
+                WHERE node_conf_files.node_id = nodes.node_id), '{}')
+AS conf_file_ids,
+COALESCE((SELECT node_tag_ids FROM node_tags
+                WHERE node_tags.node_id = nodes.node_id), '{}')
+AS node_tag_ids,
+COALESCE((SELECT route_ids FROM node_routes
+                WHERE node_routes.node_id = nodes.node_id), '{}')
+AS route_ids,
+node_session.session_id AS session
+FROM nodes
+LEFT JOIN peer_node USING (node_id)
+LEFT JOIN node_session USING (node_id);
+
+UPDATE plc_db_version SET subversion = 106;