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