2 -- Thierry Parmentelat -- INRIA
6 -- purpose : provide a generic mechanism for assigning
7 -- nodenetworks (read, network interfaces) with
11 -- mimicks the way slice attributes are being handled,
12 -- not that this design is particularly attractive
13 -- but let's not add confusion here
15 -- (*) nodenetwork_setting_types (see slice_attribute_types)
16 -- allows to define a new setting
17 -- e.g, define one such object for storing wifi SSID
19 -- (*) nodenetwork_setting (see slice_attribute)
20 -- allow to associate a nodenetwork, a nodenetwork_setting_type, and a value
22 -- NOTE. with slice_attributes there is a trick that allows to define
23 -- the attribute either on the slice globally or on a particular node only.
24 -- of course we do not need such a trick
26 CREATE TABLE nodenetwork_setting_types (
27 nodenetwork_setting_type_id serial PRIMARY KEY,
28 -- Setting Type Identifier
29 name text UNIQUE NOT NULL, -- Setting Name
30 description text, -- Optional Description
31 category text NOT NULL, -- Category, e.g. Wifi, or whatever
32 min_role_id integer references roles -- If set, minimal role required
35 CREATE TABLE nodenetwork_setting (
36 nodenetwork_setting_id serial PRIMARY KEY, -- Nodenetwork Setting Identifier
37 nodenetwork_id integer REFERENCES nodenetworks NOT NULL,
38 -- the nodenetwork this applies to
39 nodenetwork_setting_type_id integer REFERENCES nodenetwork_setting_types NOT NULL,
45 CREATE OR REPLACE VIEW nodenetwork_settings AS
46 SELECT nodenetwork_id,
47 array_accum(nodenetwork_setting_id) AS nodenetwork_setting_ids
48 FROM nodenetwork_setting
49 GROUP BY nodenetwork_id;
51 CREATE OR REPLACE VIEW view_nodenetwork_settings AS
53 nodenetwork_setting.nodenetwork_setting_id,
54 nodenetwork_setting.nodenetwork_id,
55 nodenetwork_setting_types.nodenetwork_setting_type_id,
56 nodenetwork_setting_types.name,
57 nodenetwork_setting_types.description,
58 nodenetwork_setting_types.category,
59 nodenetwork_setting_types.min_role_id,
60 nodenetwork_setting.value
61 FROM nodenetwork_setting
62 INNER JOIN nodenetwork_setting_types USING (nodenetwork_setting_type_id);
64 CREATE VIEW view_nodenetworks AS
66 nodenetworks.nodenetwork_id,
68 nodenetworks.is_primary,
75 nodenetworks.broadcast,
80 nodenetworks.hostname,
81 COALESCE((SELECT nodenetwork_setting_ids FROM nodenetwork_settings WHERE nodenetwork_settings.nodenetwork_id = nodenetworks.nodenetwork_id), '{}') AS nodenetwork_setting_ids
85 UPDATE plc_db_version SET subversion = 3;
86 SELECT subversion from plc_db_version;