From a7f0a8c621447d357b9f2b42cfa2513211aeb751 Mon Sep 17 00:00:00 2001 From: Thierry Parmentelat Date: Fri, 23 May 2008 15:17:29 +0000 Subject: [PATCH] work in progress - passes simple tests --- PLC/InterfaceSettingTypes.py | 7 +- PLC/Keys.py | 5 - PLC/Methods/AddNodeTag.py | 9 +- PLC/Methods/AddNodeTagType.py | 2 +- PLC/Methods/DeleteNodeTagType.py | 2 +- PLC/Methods/GetNodeTagTypes.py | 2 +- PLC/Methods/UpdateNodeTag.py | 4 +- PLC/Methods/UpdateNodeTagType.py | 4 +- PLC/NodeGroups.py | 9 +- PLC/NodeTagTypes.py | 13 +- PLC/NodeTags.py | 10 +- PLC/Nodes.py | 10 - PLC/Persons.py | 19 +- PLC/Sites.py | 9 - PLC/SliceAttributeTypes.py | 5 - PLC/Slices.py | 12 - planetlab5.sql | 503 ++++++++++++++++--------------- 17 files changed, 296 insertions(+), 329 deletions(-) diff --git a/PLC/InterfaceSettingTypes.py b/PLC/InterfaceSettingTypes.py index 25c59d3..27f5987 100644 --- a/PLC/InterfaceSettingTypes.py +++ b/PLC/InterfaceSettingTypes.py @@ -24,15 +24,10 @@ class InterfaceSettingType (Row): 'interface_setting_type_id': Parameter(int, "Interface setting type identifier"), 'name': Parameter(str, "Interface setting type name", max = 100), 'description': Parameter(str, "Interface setting type description", max = 254), - 'category' : Parameter (str, "Interface setting category", max=64), + 'category' : Parameter (str, "Interface setting category", max=64, optional=True), 'min_role_id': Parameter(int, "Minimum (least powerful) role that can set or change this attribute"), } - # for Cache - class_key = 'name' - foreign_fields = ['category','description','min_role_id'] - foreign_xrefs = [] - def validate_name(self, name): if not len(name): raise PLCInvalidArgument, "interface setting type name must be set" diff --git a/PLC/Keys.py b/PLC/Keys.py index 8d22dc2..91ce92a 100644 --- a/PLC/Keys.py +++ b/PLC/Keys.py @@ -26,11 +26,6 @@ class Key(Row): 'peer_key_id': Parameter(int, "Foreign key identifier at peer", nullok = True), } - # for Cache - class_key= 'key' - foreign_fields = ['key_type'] - foreign_xrefs = [] - def validate_key_type(self, key_type): key_types = [row['key_type'] for row in KeyTypes(self.api)] if key_type not in key_types: diff --git a/PLC/Methods/AddNodeTag.py b/PLC/Methods/AddNodeTag.py index e6db8ed..04dae7f 100644 --- a/PLC/Methods/AddNodeTag.py +++ b/PLC/Methods/AddNodeTag.py @@ -32,10 +32,11 @@ class AddNodeTag(Method): accepts = [ Auth(), # no other way to refer to a node - NodeTag.fields['node_id'], + Mixed(Node.fields['node_id'], + Node.fields['hostname']), Mixed(NodeTagType.fields['node_tag_type_id'], - NodeTagType.fields['name']), - NodeTag.fields['value'], + NodeTagType.fields['tagname']), + NodeTag.fields['tagvalue'], ] returns = Parameter(int, 'New node_tag_id (> 0) if successful') @@ -81,7 +82,7 @@ class AddNodeTag(Method): node_tag = NodeTag(self.api) node_tag['node_id'] = node['node_id'] node_tag['node_tag_type_id'] = node_tag_type['node_tag_type_id'] - node_tag['value'] = value + node_tag['tagvalue'] = value node_tag.sync() self.object_ids = [node_tag['node_tag_id']] diff --git a/PLC/Methods/AddNodeTagType.py b/PLC/Methods/AddNodeTagType.py index c95f520..a542bd1 100644 --- a/PLC/Methods/AddNodeTagType.py +++ b/PLC/Methods/AddNodeTagType.py @@ -12,7 +12,7 @@ from PLC.NodeTagTypes import NodeTagType, NodeTagTypes from PLC.Auth import Auth can_update = lambda (field, value): field in \ - ['name', 'description', 'category', 'min_role_id'] + ['tagname', 'description', 'category', 'min_role_id'] class AddNodeTagType(Method): """ diff --git a/PLC/Methods/DeleteNodeTagType.py b/PLC/Methods/DeleteNodeTagType.py index 16b750d..3e6694c 100644 --- a/PLC/Methods/DeleteNodeTagType.py +++ b/PLC/Methods/DeleteNodeTagType.py @@ -21,7 +21,7 @@ class DeleteNodeTagType(Method): accepts = [ Auth(), Mixed(NodeTagType.fields['node_tag_type_id'], - NodeTagType.fields['name']), + NodeTagType.fields['tagname']), ] returns = Parameter(int, '1 if successful') diff --git a/PLC/Methods/GetNodeTagTypes.py b/PLC/Methods/GetNodeTagTypes.py index 32a6787..137e721 100644 --- a/PLC/Methods/GetNodeTagTypes.py +++ b/PLC/Methods/GetNodeTagTypes.py @@ -22,7 +22,7 @@ class GetNodeTagTypes(Method): accepts = [ Auth(), Mixed([Mixed(NodeTagType.fields['node_tag_type_id'], - NodeTagType.fields['name'])], + NodeTagType.fields['tagname'])], Filter(NodeTagType.fields)), Parameter([str], "List of fields to return", nullok = True) ] diff --git a/PLC/Methods/UpdateNodeTag.py b/PLC/Methods/UpdateNodeTag.py index 55857b2..563e62b 100644 --- a/PLC/Methods/UpdateNodeTag.py +++ b/PLC/Methods/UpdateNodeTag.py @@ -29,7 +29,7 @@ class UpdateNodeTag(Method): accepts = [ Auth(), NodeTag.fields['node_tag_id'], - NodeTag.fields['value'] + NodeTag.fields['tagvalue'] ] returns = Parameter(int, '1 if successful') @@ -65,7 +65,7 @@ class UpdateNodeTag(Method): min(self.caller['role_ids']) > required_min_role: raise PLCPermissionDenied, "Not allowed to modify the specified node tag, requires role %d",required_min_role - node_tag['value'] = value + node_tag['tagvalue'] = value node_tag.sync() self.object_ids = [node_tag['node_tag_id']] diff --git a/PLC/Methods/UpdateNodeTagType.py b/PLC/Methods/UpdateNodeTagType.py index 952b3ff..b0831ba 100644 --- a/PLC/Methods/UpdateNodeTagType.py +++ b/PLC/Methods/UpdateNodeTagType.py @@ -10,7 +10,7 @@ from PLC.NodeTagTypes import NodeTagType, NodeTagTypes from PLC.Auth import Auth can_update = lambda (field, value): field in \ - ['name', 'description', 'category', 'min_role_id'] + ['tagname', 'description', 'category', 'min_role_id'] class UpdateNodeTagType(Method): """ @@ -27,7 +27,7 @@ class UpdateNodeTagType(Method): accepts = [ Auth(), Mixed(NodeTagType.fields['node_tag_type_id'], - NodeTagType.fields['name']), + NodeTagType.fields['tagname']), node_tag_type_fields ] diff --git a/PLC/NodeGroups.py b/PLC/NodeGroups.py index 6ec7a4f..bf624ef 100644 --- a/PLC/NodeGroups.py +++ b/PLC/NodeGroups.py @@ -31,12 +31,13 @@ class NodeGroup(Row): 'nodegroup_id': Parameter(int, "Node group identifier"), 'groupname': Parameter(str, "Node group name", max = 50), 'node_tag_type_id': Parameter (int, "Node tag type id"), - 'value' : Parameter(str, "value that the nodegroup definition is based upon"), - } - related_fields = { - 'name' : Parameter(str, "Tag name that the nodegroup definition is based upon"), + 'tagvalue' : Parameter(str, "value that the nodegroup definition is based upon"), + 'tagname' : Parameter(str, "Tag name that the nodegroup definition is based upon"), 'conf_file_ids': Parameter([int], "List of configuration files specific to this node group"), + 'node_ids' : Parameter([int], "List of node_ids that belong to this nodegroup"), } + related_fields = { + } def validate_name(self, name): # Make sure name is not blank diff --git a/PLC/NodeTagTypes.py b/PLC/NodeTagTypes.py index 0c05bc8..92d96d2 100644 --- a/PLC/NodeTagTypes.py +++ b/PLC/NodeTagTypes.py @@ -22,17 +22,12 @@ class NodeTagType (Row): join_tables = ['node_tag'] fields = { 'node_tag_type_id': Parameter(int, "Node tag type identifier"), - 'name': Parameter(str, "Node tag type name", max = 100), + 'tagname': Parameter(str, "Node tag type name", max = 100), 'description': Parameter(str, "Node tag type description", max = 254), - 'category' : Parameter (str, "Node tag category", max=64), + 'category' : Parameter (str, "Node tag category", max=64, optional=True), 'min_role_id': Parameter(int, "Minimum (least powerful) role that can set or change this attribute"), } - # for Cache - class_key = 'name' - foreign_fields = ['category','description','min_role_id'] - foreign_xrefs = [] - def validate_name(self, name): if not len(name): raise PLCInvalidArgument, "node tag type name must be set" @@ -69,13 +64,13 @@ class NodeTagTypes(Table): # Separate the list into integers and strings ints = filter(lambda x: isinstance(x, (int, long)), node_tag_type_filter) strs = filter(lambda x: isinstance(x, StringTypes), node_tag_type_filter) - node_tag_type_filter = Filter(NodeTagType.fields, {'node_tag_type_id': ints, 'name': strs}) + node_tag_type_filter = Filter(NodeTagType.fields, {'node_tag_type_id': ints, 'tagname': strs}) sql += " AND (%s) %s" % node_tag_type_filter.sql(api, "OR") elif isinstance(node_tag_type_filter, dict): node_tag_type_filter = Filter(NodeTagType.fields, node_tag_type_filter) sql += " AND (%s) %s" % node_tag_type_filter.sql(api, "AND") elif isinstance (node_tag_type_filter, StringTypes): - node_tag_type_filter = Filter(NodeTagType.fields, {'name':[node_tag_type_filter]}) + node_tag_type_filter = Filter(NodeTagType.fields, {'tagname':[node_tag_type_filter]}) sql += " AND (%s) %s" % node_tag_type_filter.sql(api, "AND") else: raise PLCInvalidArgument, "Wrong node tag type filter %r"%node_tag_type_filter diff --git a/PLC/NodeTags.py b/PLC/NodeTags.py index 6577c91..8690a8a 100644 --- a/PLC/NodeTags.py +++ b/PLC/NodeTags.py @@ -7,6 +7,7 @@ from PLC.Faults import * from PLC.Parameter import Parameter from PLC.Filter import Filter from PLC.Table import Row, Table +from PLC.Nodes import Node, Nodes from PLC.NodeTagTypes import NodeTagType, NodeTagTypes class NodeTag(Row): @@ -19,15 +20,14 @@ class NodeTag(Row): primary_key = 'node_tag_id' fields = { 'node_tag_id': Parameter(int, "Node tag identifier"), - 'node_id': Parameter(int, "Node identifier"), + 'node_id': Node.fields['node_id'], + 'hostname' : Node.fields['hostname'], 'node_tag_type_id': NodeTagType.fields['node_tag_type_id'], - 'name': NodeTagType.fields['name'], + 'tagvalue': Parameter(str, "Node tag value"), + 'tagname': NodeTagType.fields['tagname'], 'description': NodeTagType.fields['description'], 'category': NodeTagType.fields['category'], 'min_role_id': NodeTagType.fields['min_role_id'], - 'value': Parameter(str, "Node tag value"), - ### relations - } class NodeTags(Table): diff --git a/PLC/Nodes.py b/PLC/Nodes.py index 22c6860..a7973a4 100644 --- a/PLC/Nodes.py +++ b/PLC/Nodes.py @@ -79,16 +79,6 @@ class Node(Row): 'slices_whitelist': [Mixed(Parameter(int, "Slice identifier"), Parameter(str, "Slice name"))] } - # for Cache - class_key = 'hostname' - foreign_fields = ['boot_state','model','version'] - # forget about these ones, they are read-only anyway - # handling them causes Cache to re-sync all over again - # 'date_created','last_updated' - foreign_xrefs = [ - # in this case, we dont need the 'table' but Cache will look it up, so... - {'field' : 'site_id' , 'class' : 'Site' , 'table' : 'unused-on-direct-refs' } , - ] def validate_hostname(self, hostname): if not valid_hostname(hostname): diff --git a/PLC/Persons.py b/PLC/Persons.py index 8eb882c..887f780 100644 --- a/PLC/Persons.py +++ b/PLC/Persons.py @@ -68,22 +68,6 @@ class Person(Row): Parameter(str, "Slice name"))] } - - - # for Cache - class_key = 'email' - foreign_fields = ['first_name', 'last_name', 'title', 'email', 'phone', 'url', - 'bio', 'enabled', 'password', ] - # forget about these ones, they are read-only anyway - # handling them causes Cache to re-sync all over again - # 'last_updated', 'date_created' - foreign_xrefs = [ - {'field' : 'key_ids', 'class': 'Key', 'table' : 'person_key' } , - {'field' : 'site_ids', 'class': 'Site', 'table' : 'person_site'}, -# xxx this is not handled by Cache yet -# 'role_ids': Parameter([int], "List of role identifiers"), -] - def validate_email(self, email): """ Validate email address. Stolen from Mailman. @@ -394,8 +378,7 @@ class Persons(Table): def __init__(self, api, person_filter = None, columns = None): Table.__init__(self, api, Person, columns) - #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \ - # ", ".join(self.columns) + foreign_fields = {'role_ids': ('role_id', 'person_role'), 'roles': ('name', 'roles'), 'site_ids': ('site_id', 'person_site'), diff --git a/PLC/Sites.py b/PLC/Sites.py index 6035bb7..ab8854d 100644 --- a/PLC/Sites.py +++ b/PLC/Sites.py @@ -51,15 +51,6 @@ class Site(Row): 'addresses': [Mixed(Parameter(int, "Address identifer"), Filter(Address.fields))] } - # for Cache - class_key = 'login_base' - foreign_fields = ['abbreviated_name', 'name', 'is_public', 'latitude', 'longitude', - 'url', 'max_slices', 'max_slivers', - ] - # forget about these ones, they are read-only anyway - # handling them causes Cache to re-sync all over again - # 'last_updated', 'date_created' - foreign_xrefs = [] def validate_name(self, name): if not len(name): diff --git a/PLC/SliceAttributeTypes.py b/PLC/SliceAttributeTypes.py index 5884fe0..030bcde 100644 --- a/PLC/SliceAttributeTypes.py +++ b/PLC/SliceAttributeTypes.py @@ -22,11 +22,6 @@ class SliceAttributeType(Row): 'min_role_id': Parameter(int, "Minimum (least powerful) role that can set or change this attribute"), } - # for Cache - class_key = 'name' - foreign_fields = ['description','min_role_id'] - foreign_xrefs = [] - def validate_name(self, name): if not len(name): raise PLCInvalidArgument, "Slice attribute type name must be set" diff --git a/PLC/Slices.py b/PLC/Slices.py index 1a1786c..5c670b7 100644 --- a/PLC/Slices.py +++ b/PLC/Slices.py @@ -46,18 +46,6 @@ class Slice(Row): 'nodes': [Mixed(Parameter(int, "Node identifier"), Parameter(str, "Fully qualified hostname"))] } - # for Cache - class_key = 'name' - foreign_fields = ['instantiation', 'url', 'description', 'max_nodes', 'expires'] - foreign_xrefs = [ - {'field': 'node_ids' , 'class': 'Node', 'table': 'slice_node' }, - {'field': 'person_ids', 'class': 'Person', 'table': 'slice_person'}, - {'field': 'creator_person_id', 'class': 'Person', 'table': 'unused-on-direct-refs'}, - {'field': 'site_id', 'class': 'Site', 'table': 'unused-on-direct-refs'}, - ] - # forget about this one, it is read-only anyway - # handling it causes Cache to re-sync all over again - # 'created' def validate_name(self, name): # N.B.: Responsibility of the caller to ensure that login_base diff --git a/planetlab5.sql b/planetlab5.sql index 8062324..382589f 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -46,23 +46,22 @@ CREATE AGGREGATE array_accum ( -- Accounts CREATE TABLE persons ( -- Mandatory - person_id serial PRIMARY KEY, -- Account identifier - email text NOT NULL, -- E-mail address - first_name text NOT NULL, -- First name - last_name text NOT NULL, -- Last name - deleted boolean NOT NULL DEFAULT false, -- Has been deleted - enabled boolean NOT NULL DEFAULT false, -- Has been disabled - - -- Password - password text NOT NULL DEFAULT 'nopass', -- Password (md5crypted) - verification_key text, -- Reset password key + person_id serial PRIMARY KEY, -- Account identifier + email text NOT NULL, -- E-mail address + first_name text NOT NULL, -- First name + last_name text NOT NULL, -- Last name + deleted boolean NOT NULL DEFAULT false, -- Has been deleted + enabled boolean NOT NULL DEFAULT false, -- Has been disabled + + password text NOT NULL DEFAULT 'nopass', -- Password (md5crypted) + verification_key text, -- Reset password key verification_expires timestamp without time zone, -- Optional - title text, -- Honorific - phone text, -- Telephone number - url text, -- Home page - bio text, -- Biography + title text, -- Honorific + phone text, -- Telephone number + url text, -- Home page + bio text, -- Biography -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -77,21 +76,21 @@ CREATE INDEX persons_email_idx ON persons (email); -- Sites CREATE TABLE sites ( -- Mandatory - site_id serial PRIMARY KEY, -- Site identifier - login_base text NOT NULL, -- Site slice prefix - name text NOT NULL, -- Site name - abbreviated_name text NOT NULL, -- Site abbreviated name - enabled boolean NOT NULL Default true, -- Is this site enabled - deleted boolean NOT NULL DEFAULT false, -- Has been deleted - is_public boolean NOT NULL DEFAULT true, -- Shows up in public lists - max_slices integer NOT NULL DEFAULT 0, -- Maximum number of slices - max_slivers integer NOT NULL DEFAULT 1000, -- Maximum number of instantiated slivers + site_id serial PRIMARY KEY, -- Site identifier + login_base text NOT NULL, -- Site slice prefix + name text NOT NULL, -- Site name + abbreviated_name text NOT NULL, -- Site abbreviated name + enabled boolean NOT NULL Default true, -- Is this site enabled + deleted boolean NOT NULL DEFAULT false, -- Has been deleted + is_public boolean NOT NULL DEFAULT true, -- Shows up in public lists + max_slices integer NOT NULL DEFAULT 0, -- Maximum number of slices + max_slivers integer NOT NULL DEFAULT 1000, -- Maximum number of instantiated slivers -- Optional latitude real, longitude real, url text, - ext_consortium_id integer, -- external consortium id + ext_consortium_id integer, -- external consortium id -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -101,9 +100,9 @@ CREATE INDEX sites_login_base_idx ON sites (login_base); -- Account site membership CREATE TABLE person_site ( - person_id integer REFERENCES persons NOT NULL, -- Account identifier - site_id integer REFERENCES sites NOT NULL, -- Site identifier - is_primary boolean NOT NULL DEFAULT false, -- Is the primary site for this account + person_id integer REFERENCES persons NOT NULL, -- Account identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + is_primary boolean NOT NULL DEFAULT false, -- Is the primary site for this account PRIMARY KEY (person_id, site_id) ); CREATE INDEX person_site_person_id_idx ON person_site (person_id); @@ -134,9 +133,9 @@ GROUP BY site_id; -------------------------------------------------------------------------------- CREATE TABLE address_types ( - address_type_id serial PRIMARY KEY, -- Address type identifier - name text UNIQUE NOT NULL, -- Address type - description text -- Address type description + address_type_id serial PRIMARY KEY, -- Address type identifier + name text UNIQUE NOT NULL, -- Address type + description text -- Address type description ) WITH OIDS; -- 'Billing' Used to be 'Site' @@ -144,20 +143,20 @@ INSERT INTO address_types (name) VALUES ('Personal'), ('Shipping'), ('Billing'); -- Mailing addresses CREATE TABLE addresses ( - address_id serial PRIMARY KEY, -- Address identifier - line1 text NOT NULL, -- Address line 1 - line2 text, -- Address line 2 - line3 text, -- Address line 3 - city text NOT NULL, -- City - state text NOT NULL, -- State or province - postalcode text NOT NULL, -- Postal code - country text NOT NULL -- Country + address_id serial PRIMARY KEY, -- Address identifier + line1 text NOT NULL, -- Address line 1 + line2 text, -- Address line 2 + line3 text, -- Address line 3 + city text NOT NULL, -- City + state text NOT NULL, -- State or province + postalcode text NOT NULL, -- Postal code + country text NOT NULL -- Country ) WITH OIDS; -- Each mailing address can be one of several types CREATE TABLE address_address_type ( - address_id integer REFERENCES addresses NOT NULL, -- Address identifier - address_type_id integer REFERENCES address_types NOT NULL, -- Address type + address_id integer REFERENCES addresses NOT NULL, -- Address identifier + address_type_id integer REFERENCES address_types NOT NULL, -- Address type PRIMARY KEY (address_id, address_type_id) ) WITH OIDS; CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id); @@ -172,8 +171,8 @@ LEFT JOIN address_types USING (address_type_id) GROUP BY address_id; CREATE TABLE site_address ( - site_id integer REFERENCES sites NOT NULL, -- Site identifier - address_id integer REFERENCES addresses NOT NULL, -- Address identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + address_id integer REFERENCES addresses NOT NULL, -- Address identifier PRIMARY KEY (site_id, address_id) ) WITH OIDS; CREATE INDEX site_address_site_id_idx ON site_address (site_id); @@ -191,22 +190,22 @@ GROUP BY site_id; -- Valid key types CREATE TABLE key_types ( - key_type text PRIMARY KEY -- Key type + key_type text PRIMARY KEY -- Key type ) WITH OIDS; INSERT INTO key_types (key_type) VALUES ('ssh'); -- Authentication keys CREATE TABLE keys ( - key_id serial PRIMARY KEY, -- Key identifier - key_type text REFERENCES key_types NOT NULL, -- Key type + key_id serial PRIMARY KEY, -- Key identifier + key_type text REFERENCES key_types NOT NULL, -- Key type key text NOT NULL, -- Key material - is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted + is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted ) WITH OIDS; -- Account authentication key(s) CREATE TABLE person_key ( - key_id integer REFERENCES keys PRIMARY KEY, -- Key identifier - person_id integer REFERENCES persons NOT NULL -- Account identifier + key_id integer REFERENCES keys PRIMARY KEY, -- Key identifier + person_id integer REFERENCES persons NOT NULL -- Account identifier ) WITH OIDS; CREATE INDEX person_key_person_id_idx ON person_key (person_id); @@ -222,14 +221,14 @@ GROUP BY person_id; -- Valid account roles CREATE TABLE roles ( - role_id integer PRIMARY KEY, -- Role identifier - name text UNIQUE NOT NULL -- Role symbolic name + role_id integer PRIMARY KEY, -- Role identifier + name text UNIQUE NOT NULL -- Role symbolic name ) WITH OIDS; INSERT INTO roles (role_id, name) VALUES (10, 'admin'), (20, 'pi'), (30, 'user'), (40, 'tech'); CREATE TABLE person_role ( - person_id integer REFERENCES persons NOT NULL, -- Account identifier - role_id integer REFERENCES roles NOT NULL, -- Role identifier + person_id integer REFERENCES persons NOT NULL, -- Account identifier + role_id integer REFERENCES roles NOT NULL, -- Role identifier PRIMARY KEY (person_id, role_id) ) WITH OIDS; CREATE INDEX person_role_person_id_idx ON person_role (person_id); @@ -257,19 +256,20 @@ INSERT INTO boot_states (boot_state) -- Nodes CREATE TABLE nodes ( -- Mandatory - node_id serial PRIMARY KEY, -- Node identifier - hostname text NOT NULL, -- Node hostname - site_id integer REFERENCES sites NOT NULL, -- At which site + node_id serial PRIMARY KEY, -- Node identifier + hostname text NOT NULL, -- Node hostname + site_id integer REFERENCES sites NOT NULL, -- At which site - boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state - deleted boolean NOT NULL DEFAULT false, -- Is deleted + boot_state text REFERENCES boot_states NOT NULL -- Node boot state + DEFAULT 'inst', + deleted boolean NOT NULL DEFAULT false, -- Is deleted -- Optional - model text, -- Hardware make and model - boot_nonce text, -- Random nonce updated by Boot Manager - version text, -- Boot CD version string updated by Boot Manager - ssh_rsa_key text, -- SSH host key updated by Boot Manager - key text, -- Node key generated by API when configuration file is downloaded + model text, -- Hardware make and model + boot_nonce text, -- Random nonce updated by Boot Manager + version text, -- Boot CD version string updated by Boot Manager + ssh_rsa_key text, -- SSH host key updated by Boot Manager + key text, -- Node key generated when boot file is downloaded -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -292,19 +292,18 @@ GROUP BY site_id; -------------------------------------------------------------------------------- CREATE TABLE node_tag_types ( - node_tag_type_id serial PRIMARY KEY, -- ID - name text UNIQUE NOT NULL, -- Tag Name - description text, -- Optional Description - category text NOT NULL, -- Free text for grouping tags together - min_role_id integer REFERENCES roles -- set minimal role required + node_tag_type_id serial PRIMARY KEY, -- ID + tagname text UNIQUE NOT NULL, -- Tag Name + description text, -- Optional Description + category text NOT NULL DEFAULT 'general', -- Free text for grouping tags together + min_role_id integer REFERENCES roles DEFAULT 10 -- set minimal role required ) WITH OIDS; CREATE TABLE node_tag ( - node_tag_id serial PRIMARY KEY, -- ID - node_id integer REFERENCES nodes NOT NULL, -- node id - node_tag_type_id integer REFERENCES node_tag_types, - -- tag type id - value text -- value attached + node_tag_id serial PRIMARY KEY, -- ID + node_id integer REFERENCES nodes NOT NULL, -- node id + node_tag_type_id integer REFERENCES node_tag_types, -- tag type id + tagvalue text -- value attached ) WITH OIDS; CREATE OR REPLACE VIEW node_tags AS @@ -317,14 +316,16 @@ CREATE OR REPLACE VIEW view_node_tags AS SELECT node_tag.node_tag_id, node_tag.node_id, +nodes.hostname, node_tag_types.node_tag_type_id, -node_tag_types.name, +node_tag_types.tagname, node_tag_types.description, node_tag_types.category, node_tag_types.min_role_id, -node_tag.value +node_tag.tagvalue FROM node_tag -INNER JOIN node_tag_types USING (node_tag_type_id); +INNER JOIN node_tag_types USING (node_tag_type_id) +INNER JOIN nodes USING (node_id); -------------------------------------------------------------------------------- -- Node groups @@ -333,9 +334,9 @@ INNER JOIN node_tag_types USING (node_tag_type_id); -- Node groups CREATE TABLE nodegroups ( nodegroup_id serial PRIMARY KEY, -- Group identifier - groupname text UNIQUE NOT NULL, -- Group name (name & value will refer to the tag) + groupname text UNIQUE NOT NULL, -- Group name node_tag_type_id integer REFERENCES node_tag_types, -- node is in nodegroup if it has this tag defined - value text NOT NULL -- with value 'value' + tagvalue text NOT NULL -- with this value attached ) WITH OIDS; -- xxx - first rough implem @@ -345,7 +346,7 @@ FROM node_tag_types JOIN node_tag USING (node_tag_type_id) JOIN nodegroups -USING (node_tag_type_id,value); +USING (node_tag_type_id,tagvalue); CREATE OR REPLACE VIEW nodegroup_nodes AS SELECT nodegroup_id, @@ -365,23 +366,24 @@ GROUP BY node_id; -------------------------------------------------------------------------------- CREATE TABLE conf_files ( - conf_file_id serial PRIMARY KEY, -- Configuration file identifier - enabled bool NOT NULL DEFAULT true, -- Configuration file is active - source text NOT NULL, -- Relative path on the boot server where file can be downloaded - dest text NOT NULL, -- Absolute path where file should be installed - file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions - file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner - file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner - preinstall_cmd text, -- Shell command to execute prior to installing - postinstall_cmd text, -- Shell command to execute after installing - error_cmd text, -- Shell command to execute if any error occurs - ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs - always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged + conf_file_id serial PRIMARY KEY, -- Configuration file identifier + enabled bool NOT NULL DEFAULT true, -- Configuration file is active + source text NOT NULL, -- Relative path on the boot server + -- where file can be downloaded + dest text NOT NULL, -- Absolute path where file should be installed + file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions + file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner + file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner + preinstall_cmd text, -- Shell command to execute prior to installing + postinstall_cmd text, -- Shell command to execute after installing + error_cmd text, -- Shell command to execute if any error occurs + ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs + always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged ) WITH OIDS; CREATE TABLE conf_file_node ( - conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier - node_id integer REFERENCES nodes NOT NULL, -- Node identifier + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier PRIMARY KEY (conf_file_id, node_id) ); CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id); @@ -402,8 +404,8 @@ FROM conf_file_node GROUP BY node_id; CREATE TABLE conf_file_nodegroup ( - conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier - nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier PRIMARY KEY (conf_file_id, nodegroup_id) ); CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id); @@ -443,23 +445,23 @@ INSERT INTO network_methods (method) VALUES -- Node network interfaces CREATE TABLE interfaces ( -- Mandatory - interface_id serial PRIMARY KEY, -- Network interface identifier - node_id integer REFERENCES nodes NOT NULL, -- Which node - is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node - type text REFERENCES network_types NOT NULL, -- Addressing scheme - method text REFERENCES network_methods NOT NULL, -- Configuration method + interface_id serial PRIMARY KEY, -- Network interface identifier + node_id integer REFERENCES nodes NOT NULL, -- Which node + is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node + type text REFERENCES network_types NOT NULL, -- Addressing scheme + method text REFERENCES network_methods NOT NULL, -- Configuration method -- Optional, depending on type and method - ip text, -- IP address - mac text, -- MAC address - gateway text, -- Default gateway address - network text, -- Network address - broadcast text, -- Network broadcast address - netmask text, -- Network mask - dns1 text, -- Primary DNS server - dns2 text, -- Secondary DNS server - bwlimit integer, -- Bandwidth limit in bps - hostname text -- Hostname of this interface + ip text, -- IP address + mac text, -- MAC address + gateway text, -- Default gateway address + network text, -- Network address + broadcast text, -- Network broadcast address + netmask text, -- Network mask + dns1 text, -- Primary DNS server + dns2 text, -- Secondary DNS server + bwlimit integer, -- Bandwidth limit in bps + hostname text -- Hostname of this interface ) WITH OIDS; CREATE INDEX interfaces_node_id_idx ON interfaces (node_id); @@ -481,21 +483,19 @@ GROUP BY node_id; -------------------------------------------------------------------------------- CREATE TABLE interface_setting_types ( - interface_setting_type_id serial PRIMARY KEY, - -- Setting Type Identifier - name text UNIQUE NOT NULL, -- Setting Name - description text, -- Optional Description - category text NOT NULL, -- Category, e.g. Wifi, or whatever - min_role_id integer REFERENCES roles -- If set, minimal role required + interface_setting_type_id serial PRIMARY KEY, -- Setting Type Identifier + name text UNIQUE NOT NULL, -- Setting Name + description text, -- Optional Description + category text NOT NULL DEFAULT 'general', -- Free text for grouping, e.g. Wifi, or whatever + min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimal role required ) WITH OIDS; CREATE TABLE interface_setting ( - interface_setting_id serial PRIMARY KEY, -- Interface Setting Identifier - interface_id integer REFERENCES interfaces NOT NULL, - -- the interface this applies to - interface_setting_type_id integer REFERENCES interface_setting_types NOT NULL, - -- the setting type - value text -- value attached + interface_setting_id serial PRIMARY KEY, -- Interface Setting Identifier + interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to + interface_setting_type_id integer + REFERENCES interface_setting_types NOT NULL, -- the setting type + value text -- value attached ) WITH OIDS; CREATE OR REPLACE VIEW interface_settings AS @@ -543,17 +543,18 @@ FROM interfaces; CREATE TABLE pcus ( -- Mandatory - pcu_id serial PRIMARY KEY, -- PCU identifier - site_id integer REFERENCES sites NOT NULL, -- Site identifier - hostname text, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU) - ip text NOT NULL, -- IP, not necessarily unique + pcu_id serial PRIMARY KEY, -- PCU identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + hostname text, -- Hostname, not necessarily unique + -- (multiple logical sites could use the same PCU) + ip text NOT NULL, -- IP, not necessarily unique -- Optional - protocol text, -- Protocol, e.g. ssh or https or telnet - username text, -- Username, if applicable - "password" text, -- Password, if applicable - model text, -- Model, e.g. BayTech or iPal - notes text -- Random notes + protocol text, -- Protocol, e.g. ssh or https or telnet + username text, -- Username, if applicable + "password" text, -- Password, if applicable + model text, -- Model, e.g. BayTech or iPal + notes text -- Random notes ) WITH OIDS; CREATE INDEX pcus_site_id_idx ON pcus (site_id); @@ -564,11 +565,11 @@ FROM pcus GROUP BY site_id; CREATE TABLE pcu_node ( - pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier - node_id integer REFERENCES nodes NOT NULL, -- Node identifier - port integer NOT NULL, -- Port number - PRIMARY KEY (pcu_id, node_id), -- The same node cannot be controlled by different ports - UNIQUE (pcu_id, port) -- The same port cannot control multiple nodes + pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + port integer NOT NULL, -- Port number + PRIMARY KEY (pcu_id, node_id), -- The same node cannot be controlled by different ports + UNIQUE (pcu_id, port) -- The same port cannot control multiple nodes ); CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id); CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id); @@ -595,26 +596,29 @@ CREATE TABLE slice_instantiations ( instantiation text PRIMARY KEY ) WITH OIDS; INSERT INTO slice_instantiations (instantiation) VALUES - ('not-instantiated'), -- Placeholder slice - ('plc-instantiated'), -- Instantiated by Node Manager - ('delegated'), -- Manually instantiated - ('nm-controller'); -- NM Controller + ('not-instantiated'), -- Placeholder slice + ('plc-instantiated'), -- Instantiated by Node Manager + ('delegated'), -- Manually instantiated + ('nm-controller'); -- NM Controller -- Slices CREATE TABLE slices ( - slice_id serial PRIMARY KEY, -- Slice identifier - site_id integer REFERENCES sites NOT NULL, -- Site identifier + slice_id serial PRIMARY KEY, -- Slice identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier - name text NOT NULL, -- Slice name - instantiation text REFERENCES slice_instantiations NOT NULL DEFAULT 'plc-instantiated', -- Slice state, e.g. plc-instantiated - url text, -- Project URL - description text, -- Project description + name text NOT NULL, -- Slice name + instantiation text REFERENCES slice_instantiations -- Slice state, e.g. plc-instantiated + NOT NULL DEFAULT 'plc-instantiated', + url text, -- Project URL + description text, -- Project description - max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice + max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice - creator_person_id integer REFERENCES persons, -- Creator - created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Creation date - expires timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 weeks', -- Expiration date + creator_person_id integer REFERENCES persons, -- Creator + created timestamp without time zone NOT NULL -- Creation date + DEFAULT CURRENT_TIMESTAMP, + expires timestamp without time zone NOT NULL -- Expiration date + DEFAULT CURRENT_TIMESTAMP + '2 weeks', is_deleted boolean NOT NULL DEFAULT false ) WITH OIDS; @@ -623,8 +627,8 @@ CREATE INDEX slices_name_idx ON slices (name); -- Slivers CREATE TABLE slice_node ( - slice_id integer REFERENCES slices NOT NULL, -- Slice identifier - node_id integer REFERENCES nodes NOT NULL, -- Node identifier + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier PRIMARY KEY (slice_id, node_id) ) WITH OIDS; CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id); @@ -658,8 +662,8 @@ GROUP BY site_id; -- Slice membership CREATE TABLE slice_person ( - slice_id integer REFERENCES slices NOT NULL, -- Slice identifier - person_id integer REFERENCES persons NOT NULL, -- Account identifier + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + person_id integer REFERENCES persons NOT NULL, -- Account identifier PRIMARY KEY (slice_id, person_id) ) WITH OIDS; CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id); @@ -684,8 +688,8 @@ GROUP BY person_id; -------------------------------------------------------------------------------- -- slice whitelist on nodes CREATE TABLE node_slice_whitelist ( - node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist - slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node + node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist + slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node PRIMARY KEY (node_id, slice_id) ) WITH OIDS; CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id); @@ -704,19 +708,21 @@ GROUP BY node_id; -- Slice attribute types CREATE TABLE slice_attribute_types ( - attribute_type_id serial PRIMARY KEY, -- Attribute type identifier - name text UNIQUE NOT NULL, -- Attribute name - description text, -- Attribute description - min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can set or change this attribute + attribute_type_id serial PRIMARY KEY, -- Attribute type identifier + name text UNIQUE NOT NULL, -- Attribute name + description text, -- Attribute description + min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can + -- set or change this attribute ) WITH OIDS; -- Slice/sliver attributes CREATE TABLE slice_attribute ( - slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier - slice_id integer REFERENCES slices NOT NULL, -- Slice identifier - node_id integer REFERENCES nodes, -- Sliver attribute if set - nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set - attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier + slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + node_id integer REFERENCES nodes, -- Sliver attribute if set + nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set + attribute_type_id integer -- Attribute type identifier + REFERENCES slice_attribute_types NOT NULL, value text ) WITH OIDS; CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id); @@ -735,10 +741,10 @@ GROUP BY slice_id; -- Initscripts CREATE TABLE initscripts ( - initscript_id serial PRIMARY KEY, -- Initscript identifier - name text NOT NULL, -- Initscript name - enabled bool NOT NULL DEFAULT true, -- Initscript is active - script text NOT NULL, -- Initscript + initscript_id serial PRIMARY KEY, -- Initscript identifier + name text NOT NULL, -- Initscript name + enabled bool NOT NULL DEFAULT true, -- Initscript is active + script text NOT NULL, -- Initscript body UNIQUE (name) ) WITH OIDS; CREATE INDEX initscripts_name_idx ON initscripts (name); @@ -750,21 +756,21 @@ CREATE INDEX initscripts_name_idx ON initscripts (name); -- Peers CREATE TABLE peers ( - peer_id serial PRIMARY KEY, -- Peer identifier - peername text NOT NULL, -- Peer name - peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface - cacert text, -- (SSL) Public certificate of peer API server - key text, -- (GPG) Public key used for authentication + peer_id serial PRIMARY KEY, -- Peer identifier + peername text NOT NULL, -- Peer name + peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface + cacert text, -- (SSL) Public certificate of peer API server + key text, -- (GPG) Public key used for authentication deleted boolean NOT NULL DEFAULT false ) WITH OIDS; CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false; -- Objects at each peer CREATE TABLE peer_site ( - site_id integer REFERENCES sites PRIMARY KEY, -- Local site identifier - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - peer_site_id integer NOT NULL, -- Foreign site identifier at peer - UNIQUE (peer_id, peer_site_id) -- The same foreign site should not be cached twice + site_id integer REFERENCES sites PRIMARY KEY, -- Local site identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_site_id integer NOT NULL, -- Foreign site identifier at peer + UNIQUE (peer_id, peer_site_id) -- The same foreign site should not be cached twice ) WITH OIDS; CREATE INDEX peer_site_peer_id_idx ON peers (peer_id); @@ -776,10 +782,10 @@ FROM peer_site GROUP BY peer_id; CREATE TABLE peer_person ( - person_id integer REFERENCES persons PRIMARY KEY, -- Local user identifier - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - peer_person_id integer NOT NULL, -- Foreign user identifier at peer - UNIQUE (peer_id, peer_person_id) -- The same foreign user should not be cached twice + person_id integer REFERENCES persons PRIMARY KEY, -- Local user identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_person_id integer NOT NULL, -- Foreign user identifier at peer + UNIQUE (peer_id, peer_person_id) -- The same foreign user should not be cached twice ) WITH OIDS; CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id); @@ -791,10 +797,10 @@ FROM peer_person GROUP BY peer_id; CREATE TABLE peer_key ( - key_id integer REFERENCES keys PRIMARY KEY, -- Local key identifier - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - peer_key_id integer NOT NULL, -- Foreign key identifier at peer - UNIQUE (peer_id, peer_key_id) -- The same foreign key should not be cached twice + key_id integer REFERENCES keys PRIMARY KEY, -- Local key identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_key_id integer NOT NULL, -- Foreign key identifier at peer + UNIQUE (peer_id, peer_key_id) -- The same foreign key should not be cached twice ) WITH OIDS; CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id); @@ -806,10 +812,10 @@ FROM peer_key GROUP BY peer_id; CREATE TABLE peer_node ( - node_id integer REFERENCES nodes PRIMARY KEY, -- Local node identifier - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - peer_node_id integer NOT NULL, -- Foreign node identifier - UNIQUE (peer_id, peer_node_id) -- The same foreign node should not be cached twice + node_id integer REFERENCES nodes PRIMARY KEY, -- Local node identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_node_id integer NOT NULL, -- Foreign node identifier + UNIQUE (peer_id, peer_node_id) -- The same foreign node should not be cached twice ) WITH OIDS; CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id); @@ -821,10 +827,10 @@ FROM peer_node GROUP BY peer_id; CREATE TABLE peer_slice ( - slice_id integer REFERENCES slices PRIMARY KEY, -- Local slice identifier - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - peer_slice_id integer NOT NULL, -- Slice identifier at peer - UNIQUE (peer_id, peer_slice_id) -- The same foreign slice should not be cached twice + slice_id integer REFERENCES slices PRIMARY KEY, -- Local slice identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_slice_id integer NOT NULL, -- Slice identifier at peer + UNIQUE (peer_id, peer_slice_id) -- The same foreign slice should not be cached twice ) WITH OIDS; CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id); @@ -841,36 +847,34 @@ GROUP BY peer_id; -- Authenticated sessions CREATE TABLE sessions ( - session_id text PRIMARY KEY, -- Session identifier + session_id text PRIMARY KEY, -- Session identifier expires timestamp without time zone ) WITH OIDS; -- People can have multiple sessions CREATE TABLE person_session ( - person_id integer REFERENCES persons NOT NULL, -- Account identifier - session_id text REFERENCES sessions NOT NULL, -- Session identifier + person_id integer REFERENCES persons NOT NULL, -- Account identifier + session_id text REFERENCES sessions NOT NULL, -- Session identifier PRIMARY KEY (person_id, session_id), - UNIQUE (session_id) -- Sessions are unique + UNIQUE (session_id) -- Sessions are unique ) WITH OIDS; CREATE INDEX person_session_person_id_idx ON person_session (person_id); -- Nodes can have only one session CREATE TABLE node_session ( - node_id integer REFERENCES nodes NOT NULL, -- Node identifier - session_id text REFERENCES sessions NOT NULL, -- Session identifier - UNIQUE (node_id), -- Nodes can have only one session - UNIQUE (session_id) -- Sessions are unique + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + session_id text REFERENCES sessions NOT NULL, -- Session identifier + UNIQUE (node_id), -- Nodes can have only one session + UNIQUE (session_id) -- Sessions are unique ) WITH OIDS; - - ------------------------------------------------------------------------------- -- PCU Types ------------------------------------------------------------------------------ CREATE TABLE pcu_types ( pcu_type_id serial PRIMARY KEY, - model text NOT NULL , -- PCU model name - name text -- Full PCU model name + model text NOT NULL , -- PCU model name + name text -- Full PCU model name ) WITH OIDS; CREATE INDEX pcu_types_model_idx ON pcu_types (model); @@ -895,10 +899,10 @@ GROUP BY pcu_type_id; -------------------------------------------------------------------------------- CREATE TABLE messages ( - message_id text PRIMARY KEY, -- Message name - subject text, -- Message summary - template text, -- Message template - enabled bool NOT NULL DEFAULT true -- Whether message is enabled + message_id text PRIMARY KEY, -- Message name + subject text, -- Message summary + template text, -- Message template + enabled bool NOT NULL DEFAULT true -- Whether message is enabled ) WITH OIDS; -------------------------------------------------------------------------------- @@ -907,23 +911,24 @@ CREATE TABLE messages ( -- Events CREATE TABLE events ( - event_id serial PRIMARY KEY, -- Event identifier - person_id integer REFERENCES persons, -- Person responsible for event, if any - node_id integer REFERENCES nodes, -- Node responsible for event, if any - auth_type text, -- Type of auth used. i.e. AuthMethod - fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error - call_name text NOT NULL, -- Call responsible for this event - call text NOT NULL, -- Call responsible for this event, including parameters - message text, -- High level description of this event - runtime float DEFAULT 0, -- Event run time - time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp + event_id serial PRIMARY KEY, -- Event identifier + person_id integer REFERENCES persons, -- Person responsible for event, if any + node_id integer REFERENCES nodes, -- Node responsible for event, if any + auth_type text, -- Type of auth used. i.e. AuthMethod + fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error + call_name text NOT NULL, -- Call responsible for this event + call text NOT NULL, -- Call responsible for this event, including parameters + message text, -- High level description of this event + runtime float DEFAULT 0, -- Event run time + time timestamp without time zone NOT NULL -- Event timestamp + DEFAULT CURRENT_TIMESTAMP ) WITH OIDS; -- Database object(s) that may have been affected by a particular event CREATE TABLE event_object ( - event_id integer REFERENCES events NOT NULL, -- Event identifier - object_id integer NOT NULL, -- Object identifier - object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting + event_id integer REFERENCES events NOT NULL, -- Event identifier + object_id integer NOT NULL, -- Object identifier + object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting ) WITH OIDS; CREATE INDEX event_object_event_id_idx ON event_object (event_id); CREATE INDEX event_object_object_id_idx ON event_object (object_id); @@ -944,7 +949,9 @@ SELECT pcu_types.pcu_type_id, pcu_types.model, pcu_types.name, -COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids +COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types + WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') +AS pcu_protocol_type_ids FROM pcu_types; CREATE OR REPLACE VIEW view_events AS @@ -1037,14 +1044,30 @@ CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, 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 tag_ids FROM node_tags WHERE node_tags.node_id = nodes.node_id), '{}') AS tag_ids, +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 tag_ids FROM node_tags + WHERE node_tags.node_id = nodes.node_id), '{}') +AS tag_ids, node_session.session_id AS session FROM nodes LEFT JOIN peer_node USING (node_id) @@ -1053,14 +1076,24 @@ LEFT JOIN node_session USING (node_id); CREATE OR REPLACE VIEW view_nodegroups AS SELECT nodegroups.*, -COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids -FROM nodegroups; +node_tag_types.tagname, +COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files + WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') +AS conf_file_ids, +COALESCE((SELECT node_ids FROM nodegroup_nodes + WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') +AS node_ids +FROM nodegroups INNER JOIN node_tag_types USING (node_tag_type_id); CREATE OR REPLACE VIEW view_conf_files AS SELECT conf_files.*, -COALESCE((SELECT node_ids FROM conf_file_nodes WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, -COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids +COALESCE((SELECT node_ids FROM conf_file_nodes + WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') +AS node_ids, +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups + WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') +AS nodegroup_ids FROM conf_files; CREATE OR REPLACE VIEW view_pcus AS -- 2.43.0