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