3 -- this is the script to migrate from 4.2 to 5.0
6 ----------------------------------------
7 -- rename nodenetwork into interface
8 ----------------------------------------
10 ALTER TABLE nodenetworks RENAME TO interfaces;
11 ALTER TABLE interfaces RENAME COLUMN nodenetwork_id TO interface_id;
13 ALTER INDEX nodenetworks_node_id_idx RENAME TO interfaces_node_id_idx;
15 ALTER TABLE nodenetwork_setting_types RENAME TO interface_setting_types;
16 ALTER TABLE interface_setting_types RENAME COLUMN nodenetwork_setting_type_id TO interface_setting_type_id;
18 ALTER TABLE nodenetwork_setting RENAME TO interface_setting;
21 ALTER TABLE nodenetworks_ordered RENAME TO interfaces_ordered;
22 ALTER TABLE interfaces_ordered RENAME COLUMN nodenetwork_id TO interface_id;
24 ALTER TABLE node_nodenetworks RENAME TO node_interfaces;
25 ALTER TABLE node_interfaces RENAME COLUMN nodenetwork_ids TO interface_ids;
27 ALTER TABLE nodenetwork_settings RENAME TO interface_settings;
28 ALTER TABLE interface_settings RENAME COLUMN nodenetwork_id TO interface_id;
29 ALTER TABLE interface_settings RENAME COLUMN nodenetwork_setting_ids TO interface_setting_ids;
31 ALTER TABLE view_nodenetwork_settings RENAME TO view_interface_settings;
32 ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_setting_id TO interface_setting_id;
33 ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_id TO interface_id;
34 ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_setting_type_id TO interface_setting_type_id;
36 ALTER TABLE view_nodenetworks RENAME TO view_interfaces;
37 ALTER TABLE view_interfaces RENAME COLUMN nodenetwork_id TO interface_id;
38 ALTER TABLE view_interfaces RENAME COLUMN nodenetwork_setting_ids TO interface_setting_ids;
40 ALTER TABLE view_nodes RENAME COLUMN nodenetwork_ids TO interface_ids;
42 ----------------------------------------
44 ----------------------------------------
45 CREATE TABLE node_tag_types (
47 node_tag_type_id serial PRIMARY KEY, -- ID
48 name text UNIQUE NOT NULL, -- Tag Name
49 description text, -- Optional Description
50 category text NOT NULL, -- Free text for grouping tags together
51 min_role_id integer REFERENCES roles -- set minimal role required
54 CREATE TABLE node_tag (
55 node_tag_id serial PRIMARY KEY, -- ID
56 node_id integer REFERENCES nodes NOT NULL, -- node id
57 node_tag_type_id integer REFERENCES node_tag_types,
59 value text -- value attached
62 ---------- related views
63 CREATE OR REPLACE VIEW node_tags AS
65 array_accum(node_tag_id) AS tag_ids
69 CREATE OR REPLACE VIEW view_node_tags AS
73 node_tag_types.node_tag_type_id,
75 node_tag_types.description,
76 node_tag_types.category,
77 node_tag_types.min_role_id,
80 INNER JOIN node_tag_types USING (node_tag_type_id);
82 ----------------------------------------
85 -- a more usable migration script would need to capture more data
86 ----------------------------------------
87 DROP TABLE IF EXISTS nodegroups CASCADE;
90 CREATE TABLE nodegroups (
91 nodegroup_id serial PRIMARY KEY, -- Group identifier
92 groupname text UNIQUE NOT NULL, -- Group name
93 node_tag_type_id integer REFERENCES node_tag_types, -- node is in nodegroup if it has this tag defined
94 value text -- with value 'value'
97 CREATE OR REPLACE VIEW nodegroup_node AS
98 SELECT nodegroup_id, node_id
101 USING (node_tag_type_id)
103 USING (node_tag_type_id,value);
105 CREATE OR REPLACE VIEW nodegroup_nodes AS
107 array_accum(node_id) AS node_ids
109 GROUP BY nodegroup_id;
111 -- Node groups that each node is a member of
112 CREATE OR REPLACE VIEW node_nodegroups AS
114 array_accum(nodegroup_id) AS nodegroup_ids
118 CREATE OR REPLACE VIEW view_nodegroups AS
121 COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids
123 ----------------------------------------
125 ----------------------------------------
126 DROP VIEW view_nodes;
127 CREATE OR REPLACE VIEW view_nodes AS
139 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
140 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
141 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
143 peer_node.peer_node_id,
144 COALESCE((SELECT interface_ids FROM node_interfaces WHERE node_interfaces.node_id = nodes.node_id), '{}') AS interface_ids,
145 COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids,
146 COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids,
147 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') AS slice_ids_whitelist,
148 COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids,
149 COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports,
150 COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids,
151 COALESCE((SELECT tag_ids FROM node_tags WHERE node_tags.node_id = nodes.node_id), '{}') AS tag_ids,
152 node_session.session_id AS session
154 LEFT JOIN peer_node USING (node_id)
155 LEFT JOIN node_session USING (node_id);
157 ----------------------------------------
159 ----------------------------------------
160 UPDATE plc_db_version SET version=5, subversion=0;