planetlab5.sql

   1 --
   2 -- PlanetLab Central database schema
   3 -- Version 5, PostgreSQL
   4 --
   5 -- Aaron Klingaman <alk@cs.princeton.edu>
   6 -- Reid Moran <rmoran@cs.princeton.edu>
   7 -- Mark Huang <mlhuang@cs.princeton.edu>
   8 -- Tony Mack <tmack@cs.princeton.edu>
   9 -- Thierry Parmentelat <thierry.parmentelat@sophia.inria.fr>
  10 --
  11 -- Copyright (C) 2006 The Trustees of Princeton University
  12 --
  13 -- NOTE: this file was first created for version 4.3, the filename might be confusing
  14 --
  15 -- $Id$
  16 -- $URL$
  17 --
  18
  19 SET client_encoding = 'UNICODE';
  20
  21 --------------------------------------------------------------------------------
  22 -- Version
  23 --------------------------------------------------------------------------------
  24
  25 -- Database version
  26 CREATE TABLE plc_db_version (
  27     version integer NOT NULL,
  28     subversion integer NOT NULL DEFAULT 0
  29 ) WITH OIDS;
  30
  31 INSERT INTO plc_db_version (version, subversion) VALUES (5, 0);
  32
  33 --------------------------------------------------------------------------------
  34 -- Aggregates and store procedures
  35 --------------------------------------------------------------------------------
  36
  37 -- Like MySQL GROUP_CONCAT(), this function aggregates values into a
  38 -- PostgreSQL array.
  39 CREATE AGGREGATE array_accum (
  40     sfunc = array_append,
  41     basetype = anyelement,
  42     stype = anyarray,
  43     initcond = '{}'
  44 );
  45
  46 --------------------------------------------------------------------------------
  47 -- Accounts
  48 --------------------------------------------------------------------------------
  49
  50 -- Accounts
  51 CREATE TABLE persons (
  52     -- Mandatory
  53     person_id serial PRIMARY KEY,                       -- Account identifier
  54     email text NOT NULL,                                -- E-mail address
  55     first_name text NOT NULL,                           -- First name
  56     last_name text NOT NULL,                            -- Last name
  57     deleted boolean NOT NULL DEFAULT false,             -- Has been deleted
  58     enabled boolean NOT NULL DEFAULT false,             -- Has been disabled
  59
  60     password text NOT NULL DEFAULT 'nopass',            -- Password (md5crypted)
  61     verification_key text,                              -- Reset password key
  62     verification_expires timestamp without time zone,
  63
  64     -- Optional
  65     title text,                                         -- Honorific
  66     phone text,                                         -- Telephone number
  67     url text,                                           -- Home page
  68     bio text,                                           -- Biography
  69
  70     -- Timestamps
  71     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  72     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
  73 ) WITH OIDS;
  74 CREATE INDEX persons_email_idx ON persons (email);
  75
  76 --------------------------------------------------------------------------------
  77 -- Sites
  78 --------------------------------------------------------------------------------
  79
  80 -- Sites
  81 CREATE TABLE sites (
  82     -- Mandatory
  83     site_id serial PRIMARY KEY,                         -- Site identifier
  84     login_base text NOT NULL,                           -- Site slice prefix
  85     name text NOT NULL,                                 -- Site name
  86     abbreviated_name text NOT NULL,                     -- Site abbreviated name
  87     enabled boolean NOT NULL Default true,              -- Is this site enabled
  88     deleted boolean NOT NULL DEFAULT false,             -- Has been deleted
  89     is_public boolean NOT NULL DEFAULT true,            -- Shows up in public lists
  90     max_slices integer NOT NULL DEFAULT 0,              -- Maximum number of slices
  91     max_slivers integer NOT NULL DEFAULT 1000,          -- Maximum number of instantiated slivers
  92
  93     -- Optional
  94     latitude real,
  95     longitude real,
  96     url text,
  97     ext_consortium_id integer,                          -- external consortium id
  98
  99     -- Timestamps
 100     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
 101     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 102 ) WITH OIDS;
 103 CREATE INDEX sites_login_base_idx ON sites (login_base);
 104
 105 -- Account site membership
 106 CREATE TABLE person_site (
 107     person_id integer REFERENCES persons NOT NULL,      -- Account identifier
 108     site_id integer REFERENCES sites NOT NULL,          -- Site identifier
 109     is_primary boolean NOT NULL DEFAULT false,          -- Is the primary site for this account
 110     PRIMARY KEY (person_id, site_id)
 111 );
 112 CREATE INDEX person_site_person_id_idx ON person_site (person_id);
 113 CREATE INDEX person_site_site_id_idx ON person_site (site_id);
 114
 115 -- Ordered by primary site first
 116 CREATE OR REPLACE VIEW person_site_ordered AS
 117 SELECT person_id, site_id
 118 FROM person_site
 119 ORDER BY is_primary DESC;
 120
 121 -- Sites that each person is a member of
 122 CREATE OR REPLACE VIEW person_sites AS
 123 SELECT person_id,
 124 array_accum(site_id) AS site_ids
 125 FROM person_site_ordered
 126 GROUP BY person_id;
 127
 128 -- Accounts at each site
 129 CREATE OR REPLACE VIEW site_persons AS
 130 SELECT site_id,
 131 array_accum(person_id) AS person_ids
 132 FROM person_site
 133 GROUP BY site_id;
 134
 135 --------------------------------------------------------------------------------
 136 -- Mailing Addresses
 137 --------------------------------------------------------------------------------
 138
 139 CREATE TABLE address_types (
 140     address_type_id serial PRIMARY KEY,                 -- Address type identifier
 141     name text UNIQUE NOT NULL,                          -- Address type
 142     description text                                    -- Address type description
 143 ) WITH OIDS;
 144
 145 -- Multi-rows insertion "insert .. values (row1), (row2)" is not supported by pgsql-8.1
 146 -- 'Billing' Used to be 'Site'
 147 INSERT INTO address_types (name) VALUES ('Personal');
 148 INSERT INTO address_types (name) VALUES ('Shipping');
 149 INSERT INTO address_types (name) VALUES ('Billing');
 150
 151 -- Mailing addresses
 152 CREATE TABLE addresses (
 153     address_id serial PRIMARY KEY,                      -- Address identifier
 154     line1 text NOT NULL,                                -- Address line 1
 155     line2 text,                                         -- Address line 2
 156     line3 text,                                         -- Address line 3
 157     city text NOT NULL,                                 -- City
 158     state text NOT NULL,                                -- State or province
 159     postalcode text NOT NULL,                           -- Postal code
 160     country text NOT NULL                               -- Country
 161 ) WITH OIDS;
 162
 163 -- Each mailing address can be one of several types
 164 CREATE TABLE address_address_type (
 165     address_id integer REFERENCES addresses NOT NULL,           -- Address identifier
 166     address_type_id integer REFERENCES address_types NOT NULL,  -- Address type
 167     PRIMARY KEY (address_id, address_type_id)
 168 ) WITH OIDS;
 169 CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
 170 CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
 171
 172 CREATE OR REPLACE VIEW address_address_types AS
 173 SELECT address_id,
 174 array_accum(address_type_id) AS address_type_ids,
 175 array_accum(address_types.name) AS address_types
 176 FROM address_address_type
 177 LEFT JOIN address_types USING (address_type_id)
 178 GROUP BY address_id;
 179
 180 CREATE TABLE site_address (
 181     site_id integer REFERENCES sites NOT NULL,          -- Site identifier
 182     address_id integer REFERENCES addresses NOT NULL,   -- Address identifier
 183     PRIMARY KEY (site_id, address_id)
 184 ) WITH OIDS;
 185 CREATE INDEX site_address_site_id_idx ON site_address (site_id);
 186 CREATE INDEX site_address_address_id_idx ON site_address (address_id);
 187
 188 CREATE OR REPLACE VIEW site_addresses AS
 189 SELECT site_id,
 190 array_accum(address_id) AS address_ids
 191 FROM site_address
 192 GROUP BY site_id;
 193
 194 --------------------------------------------------------------------------------
 195 -- Authentication Keys
 196 --------------------------------------------------------------------------------
 197
 198 -- Valid key types
 199 CREATE TABLE key_types (
 200     key_type text PRIMARY KEY                           -- Key type
 201 ) WITH OIDS;
 202 INSERT INTO key_types (key_type) VALUES ('ssh');
 203
 204 -- Authentication keys
 205 CREATE TABLE keys (
 206     key_id serial PRIMARY KEY,                          -- Key identifier
 207     key_type text REFERENCES key_types NOT NULL,        -- Key type
 208     key text NOT NULL, -- Key material
 209     is_blacklisted boolean NOT NULL DEFAULT false       -- Has been blacklisted
 210 ) WITH OIDS;
 211
 212 -- Account authentication key(s)
 213 CREATE TABLE person_key (
 214     key_id integer REFERENCES keys PRIMARY KEY,         -- Key identifier
 215     person_id integer REFERENCES persons NOT NULL       -- Account identifier
 216 ) WITH OIDS;
 217 CREATE INDEX person_key_person_id_idx ON person_key (person_id);
 218
 219 CREATE OR REPLACE VIEW person_keys AS
 220 SELECT person_id,
 221 array_accum(key_id) AS key_ids
 222 FROM person_key
 223 GROUP BY person_id;
 224
 225 --------------------------------------------------------------------------------
 226 -- Account roles
 227 --------------------------------------------------------------------------------
 228
 229 -- Valid account roles
 230 CREATE TABLE roles (
 231     role_id integer PRIMARY KEY,                        -- Role identifier
 232     name text UNIQUE NOT NULL                           -- Role symbolic name
 233 ) WITH OIDS;
 234 INSERT INTO roles (role_id, name) VALUES (10, 'admin');
 235 INSERT INTO roles (role_id, name) VALUES (20, 'pi');
 236 INSERT INTO roles (role_id, name) VALUES (30, 'user');
 237 INSERT INTO roles (role_id, name) VALUES (40, 'tech');
 238
 239 CREATE TABLE person_role (
 240     person_id integer REFERENCES persons NOT NULL,      -- Account identifier
 241     role_id integer REFERENCES roles NOT NULL,          -- Role identifier
 242     PRIMARY KEY (person_id, role_id)
 243 ) WITH OIDS;
 244 CREATE INDEX person_role_person_id_idx ON person_role (person_id);
 245
 246 -- Account roles
 247 CREATE OR REPLACE VIEW person_roles AS
 248 SELECT person_id,
 249 array_accum(role_id) AS role_ids,
 250 array_accum(roles.name) AS roles
 251 FROM person_role
 252 LEFT JOIN roles USING (role_id)
 253 GROUP BY person_id;
 254
 255 --------------------------------------------------------------------------------
 256 -- Nodes
 257 --------------------------------------------------------------------------------
 258
 259 -- Valid node boot states (Nodes.py expect max length to be 20)
 260 CREATE TABLE boot_states (
 261     boot_state text PRIMARY KEY
 262 ) WITH OIDS;
 263 INSERT INTO boot_states (boot_state) VALUES ('boot');
 264 INSERT INTO boot_states (boot_state) VALUES ('safeboot');
 265 INSERT INTO boot_states (boot_state) VALUES ('reinstall');
 266 INSERT INTO boot_states (boot_state) VALUES ('disabled');
 267
 268 CREATE TABLE run_levels  (
 269     run_level text PRIMARY KEY
 270 ) WITH OIDS;
 271 INSERT INTO run_levels  (run_level) VALUES ('boot');
 272 INSERT INTO run_levels  (run_level) VALUES ('safeboot');
 273 INSERT INTO run_levels  (run_level) VALUES ('failboot');
 274 INSERT INTO run_levels  (run_level) VALUES ('reinstall');
 275
 276 -- Known node types (Nodes.py expect max length to be 20)
 277 CREATE TABLE node_types (
 278     node_type text PRIMARY KEY
 279 ) WITH OIDS;
 280 INSERT INTO node_types (node_type) VALUES ('regular');
 281 -- old dummynet stuff, to be removed
 282 INSERT INTO node_types (node_type) VALUES ('dummynet');
 283
 284 -- Nodes
 285 CREATE TABLE nodes (
 286     -- Mandatory
 287     node_id serial PRIMARY KEY,                         -- Node identifier
 288     node_type text REFERENCES node_types                -- node type
 289                DEFAULT 'regular',
 290
 291     hostname text NOT NULL,                             -- Node hostname
 292     site_id integer REFERENCES sites NOT NULL,          -- At which site
 293     boot_state text REFERENCES boot_states NOT NULL     -- Node boot state
 294                DEFAULT 'reinstall',
 295     run_level  text REFERENCES run_levels DEFAULT NULL, -- Node Run Level
 296     deleted boolean NOT NULL DEFAULT false,             -- Is deleted
 297
 298     -- Optional
 299     model text,                                         -- Hardware make and model
 300     boot_nonce text,                                    -- Random nonce updated by Boot Manager
 301     version text,                                       -- Boot CD version string updated by Boot Manager
 302     ssh_rsa_key text,                                   -- SSH host key updated by Boot Manager
 303     key text,                                           -- Node key generated when boot file is downloaded
 304         verified boolean NOT NULL DEFAULT false,                -- whether or not the node & pcu are verified
 305
 306     -- Timestamps
 307     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
 308     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
 309     last_contact timestamp without time zone
 310 ) WITH OIDS;
 311 CREATE INDEX nodes_hostname_idx ON nodes (hostname);
 312 CREATE INDEX nodes_site_id_idx ON nodes (site_id);
 313
 314 -- Nodes at each site
 315 CREATE OR REPLACE VIEW site_nodes AS
 316 SELECT site_id,
 317 array_accum(node_id) AS node_ids
 318 FROM nodes
 319 WHERE deleted IS false
 320 GROUP BY site_id;
 321
 322 --------------------------------------------------------------------------------
 323 -- node tags
 324 --------------------------------------------------------------------------------
 325 CREATE TABLE tag_types (
 326
 327     tag_type_id serial PRIMARY KEY,                     -- ID
 328     tagname text UNIQUE NOT NULL,                       -- Tag Name
 329     description text,                                   -- Optional Description
 330     min_role_id integer REFERENCES roles DEFAULT 10,    -- set minimal role required
 331     category text NOT NULL DEFAULT 'general'            -- Free text for grouping tags together
 332 ) WITH OIDS;
 333
 334 CREATE TABLE node_tag (
 335     node_tag_id serial PRIMARY KEY,                     -- ID
 336     node_id integer REFERENCES nodes NOT NULL,          -- node id
 337     tag_type_id integer REFERENCES tag_types,           -- tag type id
 338     value text                                          -- value attached
 339 ) WITH OIDS;
 340
 341 --------------------------------------------------------------------------------
 342 -- (network) interfaces
 343 --------------------------------------------------------------------------------
 344
 345 -- Valid network addressing schemes
 346 CREATE TABLE network_types (
 347     type text PRIMARY KEY -- Addressing scheme
 348 ) WITH OIDS;
 349 INSERT INTO network_types (type) VALUES ('ipv4');
 350
 351 -- Valid network configuration methods
 352 CREATE TABLE network_methods (
 353     method text PRIMARY KEY -- Configuration method
 354 ) WITH OIDS;
 355
 356 INSERT INTO network_methods (method) VALUES ('static');
 357 INSERT INTO network_methods (method) VALUES ('dhcp');
 358 INSERT INTO network_methods (method) VALUES ('proxy');
 359 INSERT INTO network_methods (method) VALUES ('tap');
 360 INSERT INTO network_methods (method) VALUES ('ipmi');
 361 INSERT INTO network_methods (method) VALUES ('unknown');
 362
 363 -- Network interfaces
 364 CREATE TABLE interfaces (
 365     -- Mandatory
 366     interface_id serial PRIMARY KEY,                    -- Network interface identifier
 367     node_id integer REFERENCES nodes NOT NULL,          -- Which node
 368     is_primary boolean NOT NULL DEFAULT false,          -- Is the primary interface for this node
 369     type text REFERENCES network_types NOT NULL,        -- Addressing scheme
 370     method text REFERENCES network_methods NOT NULL,    -- Configuration method
 371
 372     -- Optional, depending on type and method
 373     ip text,                                            -- IP address
 374     mac text,                                           -- MAC address
 375     gateway text,                                       -- Default gateway address
 376     network text,                                       -- Network address
 377     broadcast text,                                     -- Network broadcast address
 378     netmask text,                                       -- Network mask
 379     dns1 text,                                          -- Primary DNS server
 380     dns2 text,                                          -- Secondary DNS server
 381     bwlimit integer,                                    -- Bandwidth limit in bps
 382     hostname text                                       -- Hostname of this interface
 383 ) WITH OIDS;
 384 CREATE INDEX interfaces_node_id_idx ON interfaces (node_id);
 385
 386 -- Ordered by primary interface first
 387 CREATE OR REPLACE VIEW interfaces_ordered AS
 388 SELECT node_id, interface_id
 389 FROM interfaces
 390 ORDER BY is_primary DESC;
 391
 392 -- Network interfaces on each node
 393 CREATE OR REPLACE VIEW node_interfaces AS
 394 SELECT node_id,
 395 array_accum(interface_id) AS interface_ids
 396 FROM interfaces_ordered
 397 GROUP BY node_id;
 398
 399 --------------------------------------------------------------------------------
 400 -- Interface tags (formerly known as interface settings)
 401 --------------------------------------------------------------------------------
 402
 403 CREATE TABLE interface_tag (
 404     interface_tag_id serial PRIMARY KEY,                -- Interface Setting Identifier
 405     interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to
 406     tag_type_id integer REFERENCES tag_types NOT NULL,  -- the setting type
 407     value text                                          -- value attached
 408 ) WITH OIDS;
 409
 410 CREATE OR REPLACE VIEW interface_tags AS
 411 SELECT interface_id,
 412 array_accum(interface_tag_id) AS interface_tag_ids
 413 FROM interface_tag
 414 GROUP BY interface_id;
 415
 416 CREATE OR REPLACE VIEW view_interface_tags AS
 417 SELECT
 418 interface_tag.interface_tag_id,
 419 interface_tag.interface_id,
 420 interfaces.ip,
 421 tag_types.tag_type_id,
 422 tag_types.tagname,
 423 tag_types.description,
 424 tag_types.category,
 425 tag_types.min_role_id,
 426 interface_tag.value
 427 FROM interface_tag
 428 INNER JOIN tag_types USING (tag_type_id)
 429 INNER JOIN interfaces USING (interface_id);
 430
 431 CREATE OR REPLACE VIEW view_interfaces AS
 432 SELECT
 433 interfaces.interface_id,
 434 interfaces.node_id,
 435 interfaces.is_primary,
 436 interfaces.type,
 437 interfaces.method,
 438 interfaces.ip,
 439 interfaces.mac,
 440 interfaces.gateway,
 441 interfaces.network,
 442 interfaces.broadcast,
 443 interfaces.netmask,
 444 interfaces.dns1,
 445 interfaces.dns2,
 446 interfaces.bwlimit,
 447 interfaces.hostname,
 448 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
 449 FROM interfaces;
 450
 451 --------------------------------------------------------------------------------
 452 -- ilinks : links between interfaces
 453 --------------------------------------------------------------------------------
 454 CREATE TABLE ilink (
 455        ilink_id serial PRIMARY KEY,                             -- id
 456        tag_type_id integer REFERENCES tag_types,                -- id of the tag type
 457        src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
 458        dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
 459        value text                                               -- optional value on the link
 460 ) WITH OIDS;
 461
 462 CREATE OR REPLACE VIEW view_ilinks AS
 463 SELECT * FROM tag_types
 464 INNER JOIN ilink USING (tag_type_id);
 465
 466 -- xxx TODO : expose to view_interfaces the set of ilinks a given interface is part of
 467 -- this is needed for properly deleting these ilinks when an interface gets deleted
 468 -- as this is not done yet, it prevents DeleteInterface, thus DeleteNode, thus DeleteSite
 469 -- from working correctly when an iLink is set
 470
 471 --------------------------------------------------------------------------------
 472 -- Node groups
 473 --------------------------------------------------------------------------------
 474
 475 -- Node groups
 476 CREATE TABLE nodegroups (
 477     nodegroup_id serial PRIMARY KEY,            -- Group identifier
 478     groupname text UNIQUE NOT NULL,             -- Group name
 479     tag_type_id integer REFERENCES tag_types,   -- node is in nodegroup if it has this tag defined
 480     -- can be null, make management faster & easier
 481     value text                                  -- with this value attached
 482 ) WITH OIDS;
 483
 484 -- xxx - first rough implem. similar to former semantics but might be slow
 485 CREATE OR REPLACE VIEW nodegroup_node AS
 486 SELECT nodegroup_id, node_id
 487 FROM tag_types
 488 JOIN node_tag
 489 USING (tag_type_id)
 490 JOIN nodegroups
 491 USING (tag_type_id,value);
 492
 493 CREATE OR REPLACE VIEW nodegroup_nodes AS
 494 SELECT nodegroup_id,
 495 array_accum(node_id) AS node_ids
 496 FROM nodegroup_node
 497 GROUP BY nodegroup_id;
 498
 499 -- Node groups that each node is a member of
 500 CREATE OR REPLACE VIEW node_nodegroups AS
 501 SELECT node_id,
 502 array_accum(nodegroup_id) AS nodegroup_ids
 503 FROM nodegroup_node
 504 GROUP BY node_id;
 505
 506 --------------------------------------------------------------------------------
 507 -- Node configuration files
 508 --------------------------------------------------------------------------------
 509
 510 CREATE TABLE conf_files (
 511     conf_file_id serial PRIMARY KEY,                    -- Configuration file identifier
 512     enabled bool NOT NULL DEFAULT true,                 -- Configuration file is active
 513     source text NOT NULL,                               -- Relative path on the boot server
 514                                                         -- where file can be downloaded
 515     dest text NOT NULL,                                 -- Absolute path where file should be installed
 516     file_permissions text NOT NULL DEFAULT '0644',      -- chmod(1) permissions
 517     file_owner text NOT NULL DEFAULT 'root',            -- chown(1) owner
 518     file_group text NOT NULL DEFAULT 'root',            -- chgrp(1) owner
 519     preinstall_cmd text,                                -- Shell command to execute prior to installing
 520     postinstall_cmd text,                               -- Shell command to execute after installing
 521     error_cmd text,                                     -- Shell command to execute if any error occurs
 522     ignore_cmd_errors bool NOT NULL DEFAULT false,      -- Install file anyway even if an error occurs
 523     always_update bool NOT NULL DEFAULT false           -- Always attempt to install file even if unchanged
 524 ) WITH OIDS;
 525
 526 CREATE TABLE conf_file_node (
 527     conf_file_id integer REFERENCES conf_files NOT NULL,        -- Configuration file identifier
 528     node_id integer REFERENCES nodes NOT NULL,                  -- Node identifier
 529     PRIMARY KEY (conf_file_id, node_id)
 530 );
 531 CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id);
 532 CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
 533
 534 -- Nodes linked to each configuration file
 535 CREATE OR REPLACE VIEW conf_file_nodes AS
 536 SELECT conf_file_id,
 537 array_accum(node_id) AS node_ids
 538 FROM conf_file_node
 539 GROUP BY conf_file_id;
 540
 541 -- Configuration files linked to each node
 542 CREATE OR REPLACE VIEW node_conf_files AS
 543 SELECT node_id,
 544 array_accum(conf_file_id) AS conf_file_ids
 545 FROM conf_file_node
 546 GROUP BY node_id;
 547
 548 CREATE TABLE conf_file_nodegroup (
 549     conf_file_id integer REFERENCES conf_files NOT NULL,        -- Configuration file identifier
 550     nodegroup_id integer REFERENCES nodegroups NOT NULL,        -- Node group identifier
 551     PRIMARY KEY (conf_file_id, nodegroup_id)
 552 );
 553 CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id);
 554 CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
 555
 556 -- Node groups linked to each configuration file
 557 CREATE OR REPLACE VIEW conf_file_nodegroups AS
 558 SELECT conf_file_id,
 559 array_accum(nodegroup_id) AS nodegroup_ids
 560 FROM conf_file_nodegroup
 561 GROUP BY conf_file_id;
 562
 563 -- Configuration files linked to each node group
 564 CREATE OR REPLACE VIEW nodegroup_conf_files AS
 565 SELECT nodegroup_id,
 566 array_accum(conf_file_id) AS conf_file_ids
 567 FROM conf_file_nodegroup
 568 GROUP BY nodegroup_id;
 569
 570 --------------------------------------------------------------------------------
 571 -- Power control units (PCUs)
 572 --------------------------------------------------------------------------------
 573
 574 CREATE TABLE pcus (
 575     -- Mandatory
 576     pcu_id serial PRIMARY KEY,                          -- PCU identifier
 577     site_id integer REFERENCES sites NOT NULL,          -- Site identifier
 578     hostname text,                                      -- Hostname, not necessarily unique
 579                                                         -- (multiple logical sites could use the same PCU)
 580     ip text NOT NULL,                                   -- IP, not necessarily unique
 581
 582     -- Optional
 583     protocol text,                                      -- Protocol, e.g. ssh or https or telnet
 584     username text,                                      -- Username, if applicable
 585     "password" text,                                    -- Password, if applicable
 586     model text,                                         -- Model, e.g. BayTech or iPal
 587     notes text                                          -- Random notes
 588 ) WITH OIDS;
 589 CREATE INDEX pcus_site_id_idx ON pcus (site_id);
 590
 591 CREATE OR REPLACE VIEW site_pcus AS
 592 SELECT site_id,
 593 array_accum(pcu_id) AS pcu_ids
 594 FROM pcus
 595 GROUP BY site_id;
 596
 597 CREATE TABLE pcu_node (
 598     pcu_id integer REFERENCES pcus NOT NULL,            -- PCU identifier
 599     node_id integer REFERENCES nodes NOT NULL,          -- Node identifier
 600     port integer NOT NULL,                              -- Port number
 601     PRIMARY KEY (pcu_id, node_id),                      -- The same node cannot be controlled by different ports
 602     UNIQUE (pcu_id, port)                               -- The same port cannot control multiple nodes
 603 );
 604 CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id);
 605 CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id);
 606
 607 CREATE OR REPLACE VIEW node_pcus AS
 608 SELECT node_id,
 609 array_accum(pcu_id) AS pcu_ids,
 610 array_accum(port) AS ports
 611 FROM pcu_node
 612 GROUP BY node_id;
 613
 614 CREATE OR REPLACE VIEW pcu_nodes AS
 615 SELECT pcu_id,
 616 array_accum(node_id) AS node_ids,
 617 array_accum(port) AS ports
 618 FROM pcu_node
 619 GROUP BY pcu_id;
 620
 621 --------------------------------------------------------------------------------
 622 -- Slices
 623 --------------------------------------------------------------------------------
 624
 625 CREATE TABLE slice_instantiations (
 626     instantiation text PRIMARY KEY
 627 ) WITH OIDS;
 628 INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated');   -- Placeholder slice
 629 INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated');   -- Instantiated by Node Manager
 630 INSERT INTO slice_instantiations (instantiation) VALUES ('delegated');          -- Manually instantiated
 631 INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller');      -- NM Controller
 632
 633 -- Slices
 634 CREATE TABLE slices (
 635     slice_id serial PRIMARY KEY,                        -- Slice identifier
 636     site_id integer REFERENCES sites NOT NULL,          -- Site identifier
 637
 638     name text NOT NULL,                                 -- Slice name
 639     instantiation text REFERENCES slice_instantiations  -- Slice state, e.g. plc-instantiated
 640                   NOT NULL DEFAULT 'plc-instantiated',
 641     url text,                                           -- Project URL
 642     description text,                                   -- Project description
 643
 644     max_nodes integer NOT NULL DEFAULT 100,             -- Maximum number of nodes that can be assigned to this slice
 645
 646     creator_person_id integer REFERENCES persons,       -- Creator
 647     created timestamp without time zone NOT NULL        -- Creation date
 648         DEFAULT CURRENT_TIMESTAMP,
 649     expires timestamp without time zone NOT NULL        -- Expiration date
 650         DEFAULT CURRENT_TIMESTAMP + '2 weeks',
 651
 652     is_deleted boolean NOT NULL DEFAULT false
 653 ) WITH OIDS;
 654 CREATE INDEX slices_site_id_idx ON slices (site_id);
 655 CREATE INDEX slices_name_idx ON slices (name);
 656
 657 -- Slivers
 658 CREATE TABLE slice_node (
 659     slice_id integer REFERENCES slices NOT NULL,        -- Slice identifier
 660     node_id integer REFERENCES nodes NOT NULL,          -- Node identifier
 661     PRIMARY KEY (slice_id, node_id)
 662 ) WITH OIDS;
 663 CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
 664 CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
 665
 666 -- Synonym for slice_node
 667 CREATE OR REPLACE VIEW slivers AS
 668 SELECT * FROM slice_node;
 669
 670 -- Nodes in each slice
 671 CREATE OR REPLACE VIEW slice_nodes AS
 672 SELECT slice_id,
 673 array_accum(node_id) AS node_ids
 674 FROM slice_node
 675 GROUP BY slice_id;
 676
 677 -- Slices on each node
 678 CREATE OR REPLACE VIEW node_slices AS
 679 SELECT node_id,
 680 array_accum(slice_id) AS slice_ids
 681 FROM slice_node
 682 GROUP BY node_id;
 683
 684 -- Slices at each site
 685 CREATE OR REPLACE VIEW site_slices AS
 686 SELECT site_id,
 687 array_accum(slice_id) AS slice_ids
 688 FROM slices
 689 WHERE is_deleted is false
 690 GROUP BY site_id;
 691
 692 -- Slice membership
 693 CREATE TABLE slice_person (
 694     slice_id integer REFERENCES slices NOT NULL,        -- Slice identifier
 695     person_id integer REFERENCES persons NOT NULL,      -- Account identifier
 696     PRIMARY KEY (slice_id, person_id)
 697 ) WITH OIDS;
 698 CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id);
 699 CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
 700
 701 -- Members of the slice
 702 CREATE OR REPLACE VIEW slice_persons AS
 703 SELECT slice_id,
 704 array_accum(person_id) AS person_ids
 705 FROM slice_person
 706 GROUP BY slice_id;
 707
 708 -- Slices of which each person is a member
 709 CREATE OR REPLACE VIEW person_slices AS
 710 SELECT person_id,
 711 array_accum(slice_id) AS slice_ids
 712 FROM slice_person
 713 GROUP BY person_id;
 714
 715 --------------------------------------------------------------------------------
 716 -- Slice whitelist
 717 --------------------------------------------------------------------------------
 718 -- slice whitelist on nodes
 719 CREATE TABLE node_slice_whitelist (
 720     node_id integer REFERENCES nodes NOT NULL,          -- Node id of whitelist
 721     slice_id integer REFERENCES slices NOT NULL,        -- Slice id thats allowd on this node
 722     PRIMARY KEY (node_id, slice_id)
 723 ) WITH OIDS;
 724 CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id);
 725 CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
 726
 727 -- Slices on each node
 728 CREATE OR REPLACE VIEW node_slices_whitelist AS
 729 SELECT node_id,
 730 array_accum(slice_id) AS slice_ids_whitelist
 731 FROM node_slice_whitelist
 732 GROUP BY node_id;
 733
 734 --------------------------------------------------------------------------------
 735 -- Slice tags (formerly known as slice attributes)
 736 --------------------------------------------------------------------------------
 737
 738 -- Slice/sliver attributes
 739 CREATE TABLE slice_tag (
 740     slice_tag_id serial PRIMARY KEY,            -- Slice attribute identifier
 741     slice_id integer REFERENCES slices NOT NULL,        -- Slice identifier
 742     node_id integer REFERENCES nodes,                   -- Sliver attribute if set
 743     nodegroup_id integer REFERENCES nodegroups,         -- Node group attribute if set
 744     tag_type_id integer REFERENCES tag_types NOT NULL,  -- Attribute type identifier
 745     value text
 746 ) WITH OIDS;
 747 CREATE INDEX slice_tag_slice_id_idx ON slice_tag (slice_id);
 748 CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id);
 749 CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id);
 750
 751 --------------------------------------------------------------------------------
 752 -- Initscripts
 753 --------------------------------------------------------------------------------
 754
 755 -- Initscripts
 756 CREATE TABLE initscripts (
 757     initscript_id serial PRIMARY KEY,                   -- Initscript identifier
 758     name text NOT NULL,                                 -- Initscript name
 759     enabled bool NOT NULL DEFAULT true,                 -- Initscript is active
 760     script text NOT NULL,                               -- Initscript body
 761     UNIQUE (name)
 762 ) WITH OIDS;
 763 CREATE INDEX initscripts_name_idx ON initscripts (name);
 764
 765
 766 --------------------------------------------------------------------------------
 767 -- Peers
 768 --------------------------------------------------------------------------------
 769
 770 -- Peers
 771 CREATE TABLE peers (
 772     peer_id serial PRIMARY KEY,                         -- Peer identifier
 773     peername text NOT NULL,                             -- Peer name
 774     peer_url text NOT NULL,                             -- (HTTPS) URL of the peer PLCAPI interface
 775     cacert text,                                        -- (SSL) Public certificate of peer API server
 776     key text,                                           -- (GPG) Public key used for authentication
 777     shortname text,                                     -- abbreviated name for displaying foreign objects
 778     hrn_root text,                                              -- root for this peer domain
 779     deleted boolean NOT NULL DEFAULT false
 780 ) WITH OIDS;
 781 CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
 782 CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false;
 783
 784 -- Objects at each peer
 785 CREATE TABLE peer_site (
 786     site_id integer REFERENCES sites PRIMARY KEY,       -- Local site identifier
 787     peer_id integer REFERENCES peers NOT NULL,          -- Peer identifier
 788     peer_site_id integer NOT NULL,                      -- Foreign site identifier at peer
 789     UNIQUE (peer_id, peer_site_id)                      -- The same foreign site should not be cached twice
 790 ) WITH OIDS;
 791 CREATE INDEX peer_site_peer_id_idx ON peers (peer_id);
 792
 793 CREATE OR REPLACE VIEW peer_sites AS
 794 SELECT peer_id,
 795 array_accum(site_id) AS site_ids,
 796 array_accum(peer_site_id) AS peer_site_ids
 797 FROM peer_site
 798 GROUP BY peer_id;
 799
 800 CREATE TABLE peer_person (
 801     person_id integer REFERENCES persons PRIMARY KEY,   -- Local user identifier
 802     peer_id integer REFERENCES peers NOT NULL,          -- Peer identifier
 803     peer_person_id integer NOT NULL,                    -- Foreign user identifier at peer
 804     UNIQUE (peer_id, peer_person_id)                    -- The same foreign user should not be cached twice
 805 ) WITH OIDS;
 806 CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id);
 807
 808 CREATE OR REPLACE VIEW peer_persons AS
 809 SELECT peer_id,
 810 array_accum(person_id) AS person_ids,
 811 array_accum(peer_person_id) AS peer_person_ids
 812 FROM peer_person
 813 GROUP BY peer_id;
 814
 815 CREATE TABLE peer_key (
 816     key_id integer REFERENCES keys PRIMARY KEY,         -- Local key identifier
 817     peer_id integer REFERENCES peers NOT NULL,          -- Peer identifier
 818     peer_key_id integer NOT NULL,                       -- Foreign key identifier at peer
 819     UNIQUE (peer_id, peer_key_id)                       -- The same foreign key should not be cached twice
 820 ) WITH OIDS;
 821 CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id);
 822
 823 CREATE OR REPLACE VIEW peer_keys AS
 824 SELECT peer_id,
 825 array_accum(key_id) AS key_ids,
 826 array_accum(peer_key_id) AS peer_key_ids
 827 FROM peer_key
 828 GROUP BY peer_id;
 829
 830 CREATE TABLE peer_node (
 831     node_id integer REFERENCES nodes PRIMARY KEY,       -- Local node identifier
 832     peer_id integer REFERENCES peers NOT NULL,          -- Peer identifier
 833     peer_node_id integer NOT NULL,                      -- Foreign node identifier
 834     UNIQUE (peer_id, peer_node_id)                      -- The same foreign node should not be cached twice
 835 ) WITH OIDS;
 836 CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id);
 837
 838 CREATE OR REPLACE VIEW peer_nodes AS
 839 SELECT peer_id,
 840 array_accum(node_id) AS node_ids,
 841 array_accum(peer_node_id) AS peer_node_ids
 842 FROM peer_node
 843 GROUP BY peer_id;
 844
 845 CREATE TABLE peer_slice (
 846     slice_id integer REFERENCES slices PRIMARY KEY,     -- Local slice identifier
 847     peer_id integer REFERENCES peers NOT NULL,          -- Peer identifier
 848     peer_slice_id integer NOT NULL,                     -- Slice identifier at peer
 849     UNIQUE (peer_id, peer_slice_id)                     -- The same foreign slice should not be cached twice
 850 ) WITH OIDS;
 851 CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id);
 852
 853 CREATE OR REPLACE VIEW peer_slices AS
 854 SELECT peer_id,
 855 array_accum(slice_id) AS slice_ids,
 856 array_accum(peer_slice_id) AS peer_slice_ids
 857 FROM peer_slice
 858 GROUP BY peer_id;
 859
 860 --------------------------------------------------------------------------------
 861 -- Authenticated sessions
 862 --------------------------------------------------------------------------------
 863
 864 -- Authenticated sessions
 865 CREATE TABLE sessions (
 866     session_id text PRIMARY KEY,                        -- Session identifier
 867     expires timestamp without time zone
 868 ) WITH OIDS;
 869
 870 -- People can have multiple sessions
 871 CREATE TABLE person_session (
 872     person_id integer REFERENCES persons NOT NULL,      -- Account identifier
 873     session_id text REFERENCES sessions NOT NULL,       -- Session identifier
 874     PRIMARY KEY (person_id, session_id),
 875     UNIQUE (session_id)                                 -- Sessions are unique
 876 ) WITH OIDS;
 877 CREATE INDEX person_session_person_id_idx ON person_session (person_id);
 878
 879 -- Nodes can have only one session
 880 CREATE TABLE node_session (
 881     node_id integer REFERENCES nodes NOT NULL,          -- Node identifier
 882     session_id text REFERENCES sessions NOT NULL,       -- Session identifier
 883     UNIQUE (node_id),                                   -- Nodes can have only one session
 884     UNIQUE (session_id)                                 -- Sessions are unique
 885 ) WITH OIDS;
 886
 887 -------------------------------------------------------------------------------
 888 -- PCU Types
 889 ------------------------------------------------------------------------------
 890 CREATE TABLE pcu_types (
 891     pcu_type_id serial PRIMARY KEY,
 892     model text NOT NULL ,                               -- PCU model name
 893     name text                                           -- Full PCU model name
 894 ) WITH OIDS;
 895 CREATE INDEX pcu_types_model_idx ON pcu_types (model);
 896
 897 CREATE TABLE pcu_protocol_type (
 898     pcu_protocol_type_id serial PRIMARY KEY,
 899     pcu_type_id integer REFERENCES pcu_types NOT NULL,  -- PCU type identifier
 900     port integer NOT NULL,                              -- PCU port
 901     protocol text NOT NULL,                             -- Protocol
 902     supported boolean NOT NULL DEFAULT True             -- Does PLC support
 903 ) WITH OIDS;
 904 CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id);
 905
 906
 907 CREATE OR REPLACE VIEW pcu_protocol_types AS
 908 SELECT pcu_type_id,
 909 array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids
 910 FROM pcu_protocol_type
 911 GROUP BY pcu_type_id;
 912
 913 --------------------------------------------------------------------------------
 914 -- Message templates
 915 --------------------------------------------------------------------------------
 916
 917 CREATE TABLE messages (
 918     message_id text PRIMARY KEY,                        -- Message name
 919     subject text,                                       -- Message summary
 920     template text,                                      -- Message template
 921     enabled bool NOT NULL DEFAULT true                  -- Whether message is enabled
 922 ) WITH OIDS;
 923
 924 --------------------------------------------------------------------------------
 925 -- Events
 926 --------------------------------------------------------------------------------
 927
 928 -- Events
 929 CREATE TABLE events (
 930     event_id serial PRIMARY KEY,                        -- Event identifier
 931     person_id integer REFERENCES persons,               -- Person responsible for event, if any
 932     node_id integer REFERENCES nodes,                   -- Node responsible for event, if any
 933     auth_type text,                                     -- Type of auth used. i.e. AuthMethod
 934     fault_code integer NOT NULL DEFAULT 0,              -- Did this event result in error
 935     call_name text NOT NULL,                            -- Call responsible for this event
 936     call text NOT NULL,                                 -- Call responsible for this event, including parameters
 937     message text,                                       -- High level description of this event
 938     runtime float DEFAULT 0,                            -- Event run time
 939     time timestamp without time zone NOT NULL           -- Event timestamp
 940         DEFAULT CURRENT_TIMESTAMP
 941 ) WITH OIDS;
 942
 943 -- Database object(s) that may have been affected by a particular event
 944 CREATE TABLE event_object (
 945     event_id integer REFERENCES events NOT NULL,        -- Event identifier
 946     object_id integer NOT NULL,                         -- Object identifier
 947     object_type text NOT NULL Default 'Unknown'         -- What type of object is this event affecting
 948 ) WITH OIDS;
 949 CREATE INDEX event_object_event_id_idx ON event_object (event_id);
 950 CREATE INDEX event_object_object_id_idx ON event_object (object_id);
 951 CREATE INDEX event_object_object_type_idx ON event_object (object_type);
 952
 953 CREATE OR REPLACE VIEW event_objects AS
 954 SELECT event_id,
 955 array_accum(object_id) AS object_ids,
 956 array_accum(object_type) AS object_types
 957 FROM event_object
 958 GROUP BY event_id;
 959
 960 --------------------------------------------------------------------------------
 961 -- Useful views
 962 --------------------------------------------------------------------------------
 963 CREATE OR REPLACE VIEW view_pcu_types AS
 964 SELECT
 965 pcu_types.pcu_type_id,
 966 pcu_types.model,
 967 pcu_types.name,
 968 COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types
 969                  WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}')
 970 AS pcu_protocol_type_ids
 971 FROM pcu_types;
 972
 973 --------------------------------------------------------------------------------
 974 CREATE OR REPLACE VIEW view_events AS
 975 SELECT
 976 events.event_id,
 977 events.person_id,
 978 events.node_id,
 979 events.auth_type,
 980 events.fault_code,
 981 events.call_name,
 982 events.call,
 983 events.message,
 984 events.runtime,
 985 CAST(date_part('epoch', events.time) AS bigint) AS time,
 986 COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_ids,
 987 COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types
 988 FROM events;
 989
 990 CREATE OR REPLACE VIEW view_event_objects AS
 991 SELECT
 992 events.event_id,
 993 events.person_id,
 994 events.node_id,
 995 events.fault_code,
 996 events.call_name,
 997 events.call,
 998 events.message,
 999 events.runtime,
1000 CAST(date_part('epoch', events.time) AS bigint) AS time,
1001 event_object.object_id,
1002 event_object.object_type
1003 FROM events LEFT JOIN event_object USING (event_id);
1004
1005 --------------------------------------------------------------------------------
1006 CREATE OR REPLACE VIEW view_persons AS
1007 SELECT
1008 persons.person_id,
1009 persons.email,
1010 persons.first_name,
1011 persons.last_name,
1012 persons.deleted,
1013 persons.enabled,
1014 persons.password,
1015 persons.verification_key,
1016 CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires,
1017 persons.title,
1018 persons.phone,
1019 persons.url,
1020 persons.bio,
1021 CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created,
1022 CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated,
1023 peer_person.peer_id,
1024 peer_person.peer_person_id,
1025 COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids,
1026 COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles,
1027 COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids,
1028 COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids,
1029 COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids
1030 FROM persons
1031 LEFT JOIN peer_person USING (person_id);
1032
1033 --------------------------------------------------------------------------------
1034 CREATE OR REPLACE VIEW view_peers AS
1035 SELECT
1036 peers.*,
1037 COALESCE((SELECT site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS site_ids,
1038 COALESCE((SELECT peer_site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS peer_site_ids,
1039 COALESCE((SELECT person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS person_ids,
1040 COALESCE((SELECT peer_person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS peer_person_ids,
1041 COALESCE((SELECT key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS key_ids,
1042 COALESCE((SELECT peer_key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS peer_key_ids,
1043 COALESCE((SELECT node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS node_ids,
1044 COALESCE((SELECT peer_node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS peer_node_ids,
1045 COALESCE((SELECT slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS slice_ids,
1046 COALESCE((SELECT peer_slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS peer_slice_ids
1047 FROM peers;
1048
1049 --------------------------------------------------------------------------------
1050 CREATE OR REPLACE VIEW node_tags AS
1051 SELECT node_id,
1052 array_accum(node_tag_id) AS node_tag_ids
1053 FROM node_tag
1054 GROUP BY node_id;
1055
1056 CREATE OR REPLACE VIEW view_node_tags AS
1057 SELECT
1058 node_tag.node_tag_id,
1059 node_tag.node_id,
1060 nodes.hostname,
1061 tag_types.tag_type_id,
1062 tag_types.tagname,
1063 tag_types.description,
1064 tag_types.category,
1065 tag_types.min_role_id,
1066 node_tag.value
1067 FROM node_tag
1068 INNER JOIN tag_types USING (tag_type_id)
1069 INNER JOIN nodes USING (node_id);
1070
1071 CREATE OR REPLACE VIEW view_nodes AS
1072 SELECT
1073 nodes.node_id,
1074 nodes.node_type,
1075 nodes.hostname,
1076 nodes.site_id,
1077 nodes.boot_state,
1078 nodes.run_level,
1079 nodes.deleted,
1080 nodes.model,
1081 nodes.boot_nonce,
1082 nodes.version,
1083 nodes.verified,
1084 nodes.ssh_rsa_key,
1085 nodes.key,
1086 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
1087 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
1088 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
1089 peer_node.peer_id,
1090 peer_node.peer_node_id,
1091 COALESCE((SELECT interface_ids FROM node_interfaces
1092                  WHERE node_interfaces.node_id = nodes.node_id), '{}')
1093 AS interface_ids,
1094 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
1095                  WHERE node_nodegroups.node_id = nodes.node_id), '{}')
1096 AS nodegroup_ids,
1097 COALESCE((SELECT slice_ids FROM node_slices
1098                  WHERE node_slices.node_id = nodes.node_id), '{}')
1099 AS slice_ids,
1100 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
1101                  WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
1102 AS slice_ids_whitelist,
1103 COALESCE((SELECT pcu_ids FROM node_pcus
1104                  WHERE node_pcus.node_id = nodes.node_id), '{}')
1105 AS pcu_ids,
1106 COALESCE((SELECT ports FROM node_pcus
1107                  WHERE node_pcus.node_id = nodes.node_id), '{}')
1108 AS ports,
1109 COALESCE((SELECT conf_file_ids FROM node_conf_files
1110                  WHERE node_conf_files.node_id = nodes.node_id), '{}')
1111 AS conf_file_ids,
1112 COALESCE((SELECT node_tag_ids FROM node_tags
1113                  WHERE node_tags.node_id = nodes.node_id), '{}')
1114 AS node_tag_ids,
1115 node_session.session_id AS session
1116 FROM nodes
1117 LEFT JOIN peer_node USING (node_id)
1118 LEFT JOIN node_session USING (node_id);
1119
1120 --------------------------------------------------------------------------------
1121 CREATE OR REPLACE VIEW view_nodegroups AS
1122 SELECT
1123 nodegroups.*,
1124 tag_types.tagname,
1125 COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files
1126                  WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}')
1127 AS conf_file_ids,
1128 COALESCE((SELECT node_ids FROM nodegroup_nodes
1129                  WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}')
1130 AS node_ids
1131 FROM nodegroups INNER JOIN tag_types USING (tag_type_id);
1132
1133 --------------------------------------------------------------------------------
1134 CREATE OR REPLACE VIEW view_conf_files AS
1135 SELECT
1136 conf_files.*,
1137 COALESCE((SELECT node_ids FROM conf_file_nodes
1138                  WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}')
1139 AS node_ids,
1140 COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups
1141                  WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}')
1142 AS nodegroup_ids
1143 FROM conf_files;
1144
1145 --------------------------------------------------------------------------------
1146 CREATE OR REPLACE VIEW view_pcus AS
1147 SELECT
1148 pcus.*,
1149 COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
1150 COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
1151 FROM pcus;
1152
1153 --------------------------------------------------------------------------------
1154 CREATE OR REPLACE VIEW view_sites AS
1155 SELECT
1156 sites.site_id,
1157 sites.login_base,
1158 sites.name,
1159 sites.abbreviated_name,
1160 sites.deleted,
1161 sites.enabled,
1162 sites.is_public,
1163 sites.max_slices,
1164 sites.max_slivers,
1165 sites.latitude,
1166 sites.longitude,
1167 sites.url,
1168 sites.ext_consortium_id,
1169 CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created,
1170 CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
1171 peer_site.peer_id,
1172 peer_site.peer_site_id,
1173 COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids,
1174 COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
1175 COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
1176 COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
1177 COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids
1178 FROM sites
1179 LEFT JOIN peer_site USING (site_id);
1180
1181 --------------------------------------------------------------------------------
1182 CREATE OR REPLACE VIEW view_addresses AS
1183 SELECT
1184 addresses.*,
1185 COALESCE((SELECT address_type_ids FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_type_ids,
1186 COALESCE((SELECT address_types FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_types
1187 FROM addresses;
1188
1189 --------------------------------------------------------------------------------
1190 CREATE OR REPLACE VIEW view_keys AS
1191 SELECT
1192 keys.*,
1193 person_key.person_id,
1194 peer_key.peer_id,
1195 peer_key.peer_key_id
1196 FROM keys
1197 LEFT JOIN person_key USING (key_id)
1198 LEFT JOIN peer_key USING (key_id);
1199
1200 --------------------------------------------------------------------------------
1201 CREATE OR REPLACE VIEW slice_tags AS
1202 SELECT slice_id,
1203 array_accum(slice_tag_id) AS slice_tag_ids
1204 FROM slice_tag
1205 GROUP BY slice_id;
1206
1207 CREATE OR REPLACE VIEW view_slices AS
1208 SELECT
1209 slices.slice_id,
1210 slices.site_id,
1211 slices.name,
1212 slices.instantiation,
1213 slices.url,
1214 slices.description,
1215 slices.max_nodes,
1216 slices.creator_person_id,
1217 slices.is_deleted,
1218 CAST(date_part('epoch', slices.created) AS bigint) AS created,
1219 CAST(date_part('epoch', slices.expires) AS bigint) AS expires,
1220 peer_slice.peer_id,
1221 peer_slice.peer_slice_id,
1222 COALESCE((SELECT node_ids FROM slice_nodes WHERE slice_nodes.slice_id = slices.slice_id), '{}') AS node_ids,
1223 COALESCE((SELECT person_ids FROM slice_persons WHERE slice_persons.slice_id = slices.slice_id), '{}') AS person_ids,
1224 COALESCE((SELECT slice_tag_ids FROM slice_tags WHERE slice_tags.slice_id = slices.slice_id), '{}') AS slice_tag_ids
1225 FROM slices
1226 LEFT JOIN peer_slice USING (slice_id);
1227
1228 CREATE OR REPLACE VIEW view_slice_tags AS
1229 SELECT
1230 slice_tag.slice_tag_id,
1231 slice_tag.slice_id,
1232 slice_tag.node_id,
1233 slice_tag.nodegroup_id,
1234 tag_types.tag_type_id,
1235 tag_types.tagname,
1236 tag_types.description,
1237 tag_types.category,
1238 tag_types.min_role_id,
1239 slice_tag.value,
1240 slices.name
1241 FROM slice_tag
1242 INNER JOIN tag_types USING (tag_type_id)
1243 INNER JOIN slices USING (slice_id);
1244
1245 --------------------------------------------------------------------------------
1246 CREATE OR REPLACE VIEW view_sessions AS
1247 SELECT
1248 sessions.session_id,
1249 CAST(date_part('epoch', sessions.expires) AS bigint) AS expires,
1250 person_session.person_id,
1251 node_session.node_id
1252 FROM sessions
1253 LEFT JOIN person_session USING (session_id)
1254 LEFT JOIN node_session USING (session_id);
1255
1256 --------------------------------------------------------------------------------
1257 -- Built-in maintenance account and default site
1258 --------------------------------------------------------------------------------
1259
1260 INSERT INTO persons (first_name, last_name, email, password, enabled)
1261 VALUES              ('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true);
1262
1263 INSERT INTO person_role (person_id, role_id) VALUES (1, 10);
1264 INSERT INTO person_role (person_id, role_id) VALUES (1, 20);
1265 INSERT INTO person_role (person_id, role_id) VALUES (1, 30);
1266 INSERT INTO person_role (person_id, role_id) VALUES (1, 40);
1267
1268 INSERT INTO sites (login_base, name, abbreviated_name, max_slices)
1269 VALUES ('pl', 'PlanetLab Central', 'PLC', 100);