CREATE TABLE plc_db_version (
version integer NOT NULL,
subversion integer NOT NULL DEFAULT 0
CREATE TABLE plc_db_version (
version integer NOT NULL,
subversion integer NOT NULL DEFAULT 0
CREATE TABLE roles (
role_id integer PRIMARY KEY, -- Role identifier
name text UNIQUE NOT NULL -- Role symbolic name
CREATE TABLE roles (
role_id integer PRIMARY KEY, -- Role identifier
name text UNIQUE NOT NULL -- Role symbolic name
INSERT INTO roles (role_id, name) VALUES (10, 'admin');
INSERT INTO roles (role_id, name) VALUES (20, 'pi');
INSERT INTO roles (role_id, name) VALUES (30, 'user');
INSERT INTO roles (role_id, name) VALUES (10, 'admin');
INSERT INTO roles (role_id, name) VALUES (20, 'pi');
INSERT INTO roles (role_id, name) VALUES (30, 'user');
tag_type_id serial PRIMARY KEY, -- ID
tagname text UNIQUE NOT NULL, -- Tag Name
description text, -- Optional Description
tag_type_id serial PRIMARY KEY, -- ID
tagname text UNIQUE NOT NULL, -- Tag Name
description text, -- Optional Description
min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required
category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together
min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required
category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
CREATE INDEX persons_email_idx ON persons (email);
--------------------------------------------------------------------------------
CREATE INDEX persons_email_idx ON persons (email);
--------------------------------------------------------------------------------
person_id integer REFERENCES persons NOT NULL, -- person id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
person_id integer REFERENCES persons NOT NULL, -- person id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
site_id integer REFERENCES sites NOT NULL, -- site id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
site_id integer REFERENCES sites NOT NULL, -- site id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
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
-- Multi-rows insertion "insert .. values (row1), (row2)" is not supported by pgsql-8.1
-- 'Billing' Used to be 'Site'
-- Multi-rows insertion "insert .. values (row1), (row2)" is not supported by pgsql-8.1
-- 'Billing' Used to be 'Site'
state text NOT NULL, -- State or province
postalcode text NOT NULL, -- Postal code
country text NOT NULL -- Country
state text NOT NULL, -- State or province
postalcode text NOT NULL, -- Postal code
country text NOT NULL -- Country
-- 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
PRIMARY KEY (address_id, address_type_id)
-- 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
PRIMARY KEY (address_id, address_type_id)
CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
site_id integer REFERENCES sites NOT NULL, -- Site identifier
address_id integer REFERENCES addresses NOT NULL, -- Address identifier
PRIMARY KEY (site_id, address_id)
site_id integer REFERENCES sites NOT NULL, -- Site identifier
address_id integer REFERENCES addresses NOT NULL, -- Address identifier
PRIMARY KEY (site_id, address_id)
CREATE INDEX site_address_site_id_idx ON site_address (site_id);
CREATE INDEX site_address_address_id_idx ON site_address (address_id);
CREATE INDEX site_address_site_id_idx ON site_address (site_id);
CREATE INDEX site_address_address_id_idx ON site_address (address_id);
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
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
-- 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
-- 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
CREATE INDEX person_key_person_id_idx ON person_key (person_id);
CREATE OR REPLACE VIEW person_keys AS
CREATE INDEX person_key_person_id_idx ON person_key (person_id);
CREATE OR REPLACE VIEW person_keys AS
person_id integer REFERENCES persons NOT NULL, -- Account identifier
role_id integer REFERENCES roles NOT NULL, -- Role identifier
PRIMARY KEY (person_id, role_id)
person_id integer REFERENCES persons NOT NULL, -- Account identifier
role_id integer REFERENCES roles NOT NULL, -- Role identifier
PRIMARY KEY (person_id, role_id)
-- Valid node boot states (Nodes.py expect max length to be 20)
CREATE TABLE boot_states (
boot_state text PRIMARY KEY
-- Valid node boot states (Nodes.py expect max length to be 20)
CREATE TABLE boot_states (
boot_state text PRIMARY KEY
INSERT INTO boot_states (boot_state) VALUES ('boot');
INSERT INTO boot_states (boot_state) VALUES ('safeboot');
INSERT INTO boot_states (boot_state) VALUES ('reinstall');
INSERT INTO boot_states (boot_state) VALUES ('boot');
INSERT INTO boot_states (boot_state) VALUES ('safeboot');
INSERT INTO boot_states (boot_state) VALUES ('reinstall');
INSERT INTO run_levels (run_level) VALUES ('boot');
INSERT INTO run_levels (run_level) VALUES ('safeboot');
INSERT INTO run_levels (run_level) VALUES ('failboot');
INSERT INTO run_levels (run_level) VALUES ('boot');
INSERT INTO run_levels (run_level) VALUES ('safeboot');
INSERT INTO run_levels (run_level) VALUES ('failboot');
-- Known node types (Nodes.py expect max length to be 20)
CREATE TABLE node_types (
node_type text PRIMARY KEY
-- Known node types (Nodes.py expect max length to be 20)
CREATE TABLE node_types (
node_type text PRIMARY KEY
INSERT INTO node_types (node_type) VALUES ('regular');
-- old dummynet stuff, to be removed
INSERT INTO node_types (node_type) VALUES ('dummynet');
INSERT INTO node_types (node_type) VALUES ('regular');
-- old dummynet stuff, to be removed
INSERT INTO node_types (node_type) VALUES ('dummynet');
last_pcu_reboot timestamp without time zone,
last_pcu_confirmation timestamp without time zone,
last_contact timestamp without time zone
last_pcu_reboot timestamp without time zone,
last_pcu_confirmation timestamp without time zone,
last_contact timestamp without time zone
CREATE INDEX nodes_hostname_idx ON nodes (hostname);
CREATE INDEX nodes_site_id_idx ON nodes (site_id);
CREATE INDEX nodes_hostname_idx ON nodes (hostname);
CREATE INDEX nodes_site_id_idx ON nodes (site_id);
node_id integer REFERENCES nodes NOT NULL, -- node id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
node_id integer REFERENCES nodes NOT NULL, -- node id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
-- Valid network addressing schemes
CREATE TABLE network_types (
type text PRIMARY KEY -- Addressing scheme
-- Valid network addressing schemes
CREATE TABLE network_types (
type text PRIMARY KEY -- Addressing scheme
INSERT INTO network_types (type) VALUES ('ipv4');
-- Valid network configuration methods
CREATE TABLE network_methods (
method text PRIMARY KEY -- Configuration method
INSERT INTO network_types (type) VALUES ('ipv4');
-- Valid network configuration methods
CREATE TABLE network_methods (
method text PRIMARY KEY -- Configuration method
INSERT INTO network_methods (method) VALUES ('static');
INSERT INTO network_methods (method) VALUES ('dhcp');
INSERT INTO network_methods (method) VALUES ('static');
INSERT INTO network_methods (method) VALUES ('dhcp');
bwlimit integer, -- Bandwidth limit in bps
hostname text, -- Hostname of this interface
last_updated timestamp without time zone -- When the interface was last updated
bwlimit integer, -- Bandwidth limit in bps
hostname text, -- Hostname of this interface
last_updated timestamp without time zone -- When the interface was last updated
interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to
tag_type_id integer REFERENCES tag_types NOT NULL, -- the setting type
value text -- value attached
interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to
tag_type_id integer REFERENCES tag_types NOT NULL, -- the setting type
value text -- value attached
src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
value text -- optional value on the link
src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
value text -- optional value on the link
tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined
-- can be null, make management faster & easier
value text -- with this value attached
tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined
-- can be null, make management faster & easier
value text -- with this value attached
-- xxx - first rough implem. similar to former semantics but might be slow
CREATE OR REPLACE VIEW nodegroup_node AS
-- xxx - first rough implem. similar to former semantics but might be slow
CREATE OR REPLACE VIEW nodegroup_node AS
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
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
CREATE TABLE conf_file_node (
conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
CREATE TABLE conf_file_node (
conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
model text, -- Model, e.g. BayTech or iPal
last_updated timestamp without time zone,
notes text -- Random notes
model text, -- Model, e.g. BayTech or iPal
last_updated timestamp without time zone,
notes text -- Random notes
INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice
INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager
INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated
INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice
INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager
INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated
DEFAULT CURRENT_TIMESTAMP + '2 weeks',
is_deleted boolean NOT NULL DEFAULT false
DEFAULT CURRENT_TIMESTAMP + '2 weeks',
is_deleted boolean NOT NULL DEFAULT false
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
node_id integer REFERENCES nodes NOT NULL, -- Node identifier
PRIMARY KEY (slice_id, node_id)
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
node_id integer REFERENCES nodes NOT NULL, -- Node identifier
PRIMARY KEY (slice_id, node_id)
CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
person_id integer REFERENCES persons NOT NULL, -- Account identifier
PRIMARY KEY (slice_id, person_id)
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
person_id integer REFERENCES persons NOT NULL, -- Account identifier
PRIMARY KEY (slice_id, person_id)
CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id);
CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id);
CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
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)
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)
CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id);
CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id);
CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set
tag_type_id integer REFERENCES tag_types NOT NULL, -- Attribute type identifier
value text
nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set
tag_type_id integer REFERENCES tag_types NOT NULL, -- Attribute type identifier
value text
CREATE INDEX slice_tag_slice_id_idx ON slice_tag (slice_id);
CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id);
CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id);
CREATE INDEX slice_tag_slice_id_idx ON slice_tag (slice_id);
CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id);
CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id);
initscript_id serial PRIMARY KEY, -- Initscript identifier
name text NOT NULL, -- Initscript name
enabled bool NOT NULL DEFAULT true, -- Initscript is active
initscript_id serial PRIMARY KEY, -- Initscript identifier
name text NOT NULL, -- Initscript name
enabled bool NOT NULL DEFAULT true, -- Initscript is active
shortname text, -- abbreviated name for displaying foreign objects
hrn_root text, -- root for this peer domain
deleted boolean NOT NULL DEFAULT false
shortname text, -- abbreviated name for displaying foreign objects
hrn_root text, -- root for this peer domain
deleted boolean NOT NULL DEFAULT false
CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false;
CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false;
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
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
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
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
CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id);
CREATE OR REPLACE VIEW peer_persons AS
CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id);
CREATE OR REPLACE VIEW peer_persons AS
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
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
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
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
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
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
CREATE TABLE sessions (
session_id text PRIMARY KEY, -- Session identifier
expires timestamp without time zone
CREATE TABLE sessions (
session_id text PRIMARY KEY, -- Session identifier
expires timestamp without time zone
session_id text REFERENCES sessions NOT NULL, -- Session identifier
PRIMARY KEY (person_id, session_id),
UNIQUE (session_id) -- Sessions are unique
session_id text REFERENCES sessions NOT NULL, -- Session identifier
PRIMARY KEY (person_id, session_id),
UNIQUE (session_id) -- Sessions are unique
CREATE INDEX person_session_person_id_idx ON person_session (person_id);
-- Nodes can have only one session
CREATE INDEX person_session_person_id_idx ON person_session (person_id);
-- Nodes can have only one session
session_id text REFERENCES sessions NOT NULL, -- Session identifier
UNIQUE (node_id), -- Nodes can have only one session
UNIQUE (session_id) -- Sessions are unique
session_id text REFERENCES sessions NOT NULL, -- Session identifier
UNIQUE (node_id), -- Nodes can have only one session
UNIQUE (session_id) -- Sessions are unique
pcu_type_id serial PRIMARY KEY,
model text NOT NULL , -- PCU model name
name text -- Full PCU model name
pcu_type_id serial PRIMARY KEY,
model text NOT NULL , -- PCU model name
name text -- Full PCU model name
port integer NOT NULL, -- PCU port
protocol text NOT NULL, -- Protocol
supported boolean NOT NULL DEFAULT True -- Does PLC support
port integer NOT NULL, -- PCU port
protocol text NOT NULL, -- Protocol
supported boolean NOT NULL DEFAULT True -- Does PLC support
subject text, -- Message summary
template text, -- Message template
enabled bool NOT NULL DEFAULT true -- Whether message is enabled
subject text, -- Message summary
template text, -- Message template
enabled bool NOT NULL DEFAULT true -- Whether message is enabled
runtime float DEFAULT 0, -- Event run time
time timestamp without time zone NOT NULL -- Event timestamp
DEFAULT CURRENT_TIMESTAMP
runtime float DEFAULT 0, -- Event run time
time timestamp without time zone NOT NULL -- Event timestamp
DEFAULT CURRENT_TIMESTAMP
-- 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
-- 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
CREATE INDEX event_object_event_id_idx ON event_object (event_id);
CREATE INDEX event_object_object_id_idx ON event_object (object_id);
CREATE INDEX event_object_object_type_idx ON event_object (object_type);
CREATE INDEX event_object_event_id_idx ON event_object (event_id);
CREATE INDEX event_object_object_id_idx ON event_object (object_id);
CREATE INDEX event_object_object_type_idx ON event_object (object_type);