migrations/106-up-newinterface.sql

   1 INSERT INTO network_types (type) VALUES ('ipv6');
   2
   3 CREATE TABLE ip_addresses (
   4     -- Mandatory
   5     ip_address_id serial PRIMARY KEY,                   -- Network interface identifier
   6     interface_id integer REFERENCES interfaces NOT NULL,                -- Which interface
   7     type text REFERENCES network_types NOT NULL,        -- ipv4 or ipv6
   8     ip_addr text NOT NULL,                              -- IP address
   9     netmask text NOT NULL,                              -- Netmask
  10     last_updated timestamp without time zone -- When the interface was last updated
  11 ) WITH OIDS;
  12 CREATE INDEX ip_addresses_interface_id_idx ON ip_addresses (interface_id);
  13
  14 CREATE OR REPLACE VIEW interface_ip_addresses AS
  15 SELECT interface_id,
  16 array_accum(ip_address_id) AS ip_address_ids
  17 FROM ip_addresses
  18 GROUP BY interface_id;
  19
  20 CREATE OR REPLACE VIEW view_ip_addresses AS
  21 SELECT
  22 ip_addresses.ip_address_id,
  23 ip_addresses.interface_id,
  24 ip_addresses.type,
  25 ip_addresses.ip_addr,
  26 ip_addresses.netmask,
  27 CAST(date_part('epoch', ip_addresses.last_updated) AS bigint) AS last_updated
  28 FROM ip_addresses;
  29
  30 DROP VIEW view_interfaces;
  31 DROP VIEW IF EXISTS tagvalue_view_interface_id_ifname;
  32 DROP VIEW view_interface_tags;
  33
  34 INSERT INTO ip_addresses (interface_id, type, ip_addr, netmask)
  35 SELECT interface_id, type, ip as ip_addr, netmask FROM interfaces;
  36
  37 ALTER TABLE interfaces DROP COLUMN type;
  38 ALTER TABLE interfaces DROP COLUMN ip;
  39 ALTER TABLE interfaces DROP COLUMN network;
  40 ALTER TABLE interfaces DROP COLUMN broadcast;
  41 ALTER TABLE interfaces DROP COLUMN netmask;
  42 ALTER TABLE interfaces ADD COLUMN if_name text;
  43
  44 CREATE OR REPLACE VIEW view_interfaces AS
  45 SELECT
  46 interfaces.interface_id,
  47 interfaces.node_id,
  48 interfaces.is_primary,
  49 interfaces.method,
  50 interfaces.mac,
  51 interfaces.bwlimit,
  52 interfaces.hostname,
  53 interfaces.if_name,
  54 CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
  55 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids,
  56 COALESCE((SELECT ip_address_ids FROM interface_ip_addresses WHERE interface_ip_addresses.interface_id = interfaces.interface_id), '{}') AS ip_address_ids
  57 FROM interfaces;
  58
  59 CREATE OR REPLACE VIEW view_interface_tags AS
  60 SELECT
  61 interface_tag.interface_tag_id,
  62 interface_tag.interface_id,
  63 tag_types.tag_type_id,
  64 tag_types.tagname,
  65 tag_types.description,
  66 tag_types.category,
  67 interface_tag.value
  68 FROM interface_tag
  69 INNER JOIN tag_types USING (tag_type_id)
  70 INNER JOIN interfaces USING (interface_id);
  71
  72 CREATE TABLE routes (
  73     -- Mandatory
  74     route_id serial PRIMARY KEY,                        -- Network interface identifier
  75     node_id integer REFERENCES nodes NOT NULL,          -- Which node
  76     subnet text NOT NULL,                               -- Subnet route applies to
  77     next_hop text NOT NULL,                             -- Next hop IP address
  78     interface_id integer REFERENCES interfaces NOT NULL, -- Outgoing interface
  79     last_updated timestamp without time zone -- When the interface was last updated
  80 ) WITH OIDS;
  81 CREATE INDEX routes_node_id_idx ON routes (node_id);
  82
  83 CREATE OR REPLACE VIEW view_routes AS
  84 SELECT
  85 routes.route_id,
  86 routes.node_id,
  87 routes.subnet,
  88 routes.next_hop,
  89 routes.interface_id,
  90 CAST(date_part('epoch', routes.last_updated) AS bigint) AS last_updated
  91 FROM routes;
  92
  93 CREATE OR REPLACE VIEW node_routes AS
  94 SELECT node_id,
  95 array_accum(route_id) AS route_ids
  96 FROM routes
  97 GROUP BY node_id;
  98
  99 ALTER TABLE nodes ADD COLUMN dns text;
 100 ALTER TABLE nodes ADD COLUMN ntp text;
 101
 102 -- Insert the default gateways from the primary interfaces on each node as
 103 -- a route with subnet=0.0.0.0/0 and next_hop=gateway
 104 INSERT INTO routes (subnet, node_id, interface_id, next_hop)
 105 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;
 106
 107 -- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
 108 -- for primary interfaces with one dns server
 109 UPDATE nodes SET dns = dns1 FROM interfaces
 110 WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is NULL;
 111
 112 -- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
 113 -- for primary interfaces with two dns servers
 114 UPDATE nodes SET dns = dns1 || ', ' || dns2 FROM interfaces
 115 WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is not NULL;
 116
 117 -- now we can drop the gateway and dns columns from interface
 118 ALTER TABLE interfaces DROP COLUMN gateway;
 119 ALTER TABLE interfaces DROP COLUMN dns1;
 120 ALTER TABLE interfaces DROP COLUMN dns2;
 121
 122 DROP VIEW view_nodes;
 123 CREATE OR REPLACE VIEW view_nodes AS
 124 SELECT
 125 nodes.node_id,
 126 nodes.node_type,
 127 nodes.hostname,
 128 nodes.site_id,
 129 nodes.boot_state,
 130 nodes.run_level,
 131 nodes.deleted,
 132 nodes.model,
 133 nodes.boot_nonce,
 134 nodes.version,
 135 nodes.verified,
 136 nodes.ssh_rsa_key,
 137 nodes.dns,
 138 nodes.ntp,
 139 nodes.key,
 140 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
 141 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
 142 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
 143 CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
 144 CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
 145 CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
 146 CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
 147 nodes.last_time_spent_online,
 148 nodes.last_time_spent_offline,
 149 peer_node.peer_id,
 150 peer_node.peer_node_id,
 151 COALESCE((SELECT interface_ids FROM node_interfaces
 152                  WHERE node_interfaces.node_id = nodes.node_id), '{}')
 153 AS interface_ids,
 154 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
 155                  WHERE node_nodegroups.node_id = nodes.node_id), '{}')
 156 AS nodegroup_ids,
 157 COALESCE((SELECT slice_ids FROM node_slices
 158                  WHERE node_slices.node_id = nodes.node_id), '{}')
 159 AS slice_ids,
 160 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
 161                  WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
 162 AS slice_ids_whitelist,
 163 COALESCE((SELECT pcu_ids FROM node_pcus
 164                  WHERE node_pcus.node_id = nodes.node_id), '{}')
 165 AS pcu_ids,
 166 COALESCE((SELECT ports FROM node_pcus
 167                  WHERE node_pcus.node_id = nodes.node_id), '{}')
 168 AS ports,
 169 COALESCE((SELECT conf_file_ids FROM node_conf_files
 170                  WHERE node_conf_files.node_id = nodes.node_id), '{}')
 171 AS conf_file_ids,
 172 COALESCE((SELECT node_tag_ids FROM node_tags
 173                  WHERE node_tags.node_id = nodes.node_id), '{}')
 174 AS node_tag_ids,
 175 COALESCE((SELECT route_ids FROM node_routes
 176                  WHERE node_routes.node_id = nodes.node_id), '{}')
 177 AS route_ids,
 178 node_session.session_id AS session
 179 FROM nodes
 180 LEFT JOIN peer_node USING (node_id)
 181 LEFT JOIN node_session USING (node_id);
 182
 183 UPDATE plc_db_version SET subversion = 106;