planetlab4.sql

   1 --
   2 -- PlanetLab Central database schema
   3 -- Version 4, 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 --
  10 -- Copyright (C) 2006 The Trustees of Princeton University
  11 --
  12 -- $Id: planetlab4.sql,v 1.68 2007/01/30 11:27:12 thierry Exp $
  13 --
  14
  15 --------------------------------------------------------------------------------
  16 -- Aggregates and store procedures
  17 --------------------------------------------------------------------------------
  18
  19 -- Like MySQL GROUP_CONCAT(), this function aggregates values into a
  20 -- PostgreSQL array.
  21 CREATE AGGREGATE array_accum (
  22     sfunc = array_append,
  23     basetype = anyelement,
  24     stype = anyarray,
  25     initcond = '{}'
  26 );
  27
  28 --------------------------------------------------------------------------------
  29 -- Version
  30 --------------------------------------------------------------------------------
  31
  32 -- Database version
  33 CREATE TABLE plc_db_version (
  34         version integer NOT NULL
  35 -- subversion created on demand by plc.d/db
  36 ) WITH OIDS;
  37
  38 INSERT INTO plc_db_version (version) VALUES (4);
  39
  40 --------------------------------------------------------------------------------
  41 -- Accounts
  42 --------------------------------------------------------------------------------
  43
  44 -- Accounts
  45 CREATE TABLE persons (
  46     -- Mandatory
  47     person_id serial PRIMARY KEY, -- Account identifier
  48     email text NOT NULL, -- E-mail address
  49     first_name text NOT NULL, -- First name
  50     last_name text NOT NULL, -- Last name
  51     deleted boolean NOT NULL DEFAULT false, -- Has been deleted
  52     enabled boolean NOT NULL DEFAULT false, -- Has been disabled
  53
  54     -- Password
  55     password text NOT NULL DEFAULT 'nopass', -- Password (md5crypted)
  56     verification_key text, -- Reset password key
  57     verification_expires timestamp without time zone,
  58
  59     -- Optional
  60     title text, -- Honorific
  61     phone text, -- Telephone number
  62     url text, -- Home page
  63     bio text, -- Biography
  64
  65     -- Timestamps
  66     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  67     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
  68 ) WITH OIDS;
  69 CREATE INDEX persons_email_idx ON persons (email) WHERE deleted IS false;
  70
  71 --------------------------------------------------------------------------------
  72 -- Sites
  73 --------------------------------------------------------------------------------
  74
  75 -- Sites
  76 CREATE TABLE sites (
  77     -- Mandatory
  78     site_id serial PRIMARY KEY, -- Site identifier
  79     login_base text NOT NULL, -- Site slice prefix
  80     name text NOT NULL, -- Site name
  81     abbreviated_name text NOT NULL, -- Site abbreviated name
  82     enabled boolean NOT NULL Default true, -- Is this site enabled
  83     deleted boolean NOT NULL DEFAULT false, -- Has been deleted
  84     is_public boolean NOT NULL DEFAULT true, -- Shows up in public lists
  85     max_slices integer NOT NULL DEFAULT 0, -- Maximum number of slices
  86     max_slivers integer NOT NULL DEFAULT 1000, -- Maximum number of instantiated slivers
  87
  88     -- Optional
  89     latitude real,
  90     longitude real,
  91     url text,
  92
  93     -- Timestamps
  94     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  95     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
  96 ) WITH OIDS;
  97 CREATE INDEX sites_login_base_idx ON sites (login_base) WHERE deleted IS false;
  98
  99 -- Account site membership
 100 CREATE TABLE person_site (
 101     person_id integer REFERENCES persons NOT NULL, -- Account identifier
 102     site_id integer REFERENCES sites NOT NULL, -- Site identifier
 103     is_primary boolean NOT NULL DEFAULT false, -- Is the primary site for this account
 104     PRIMARY KEY (person_id, site_id)
 105 );
 106 CREATE INDEX person_site_person_id_idx ON person_site (person_id);
 107 CREATE INDEX person_site_site_id_idx ON person_site (site_id);
 108
 109 -- Ordered by primary site first
 110 CREATE VIEW person_site_ordered AS
 111 SELECT person_id, site_id
 112 FROM person_site
 113 ORDER BY is_primary DESC;
 114
 115 -- Sites that each person is a member of
 116 CREATE VIEW person_sites AS
 117 SELECT person_id,
 118 array_accum(site_id) AS site_ids
 119 FROM person_site_ordered
 120 GROUP BY person_id;
 121
 122 -- Accounts at each site
 123 CREATE VIEW site_persons AS
 124 SELECT site_id,
 125 array_accum(person_id) AS person_ids
 126 FROM person_site
 127 GROUP BY site_id;
 128
 129 --------------------------------------------------------------------------------
 130 -- Mailing Addresses
 131 --------------------------------------------------------------------------------
 132
 133 CREATE TABLE address_types (
 134     address_type_id serial PRIMARY KEY, -- Address type identifier
 135     name text UNIQUE NOT NULL, -- Address type
 136     description text -- Address type description
 137 ) WITH OIDS;
 138
 139 INSERT INTO address_types (name) VALUES ('Personal');
 140 INSERT INTO address_types (name) VALUES ('Shipping');
 141 -- XXX Used to be Site
 142 INSERT INTO address_types (name) VALUES ('Billing');
 143
 144 -- Mailing addresses
 145 CREATE TABLE addresses (
 146     address_id serial PRIMARY KEY, -- Address identifier
 147     line1 text NOT NULL, -- Address line 1
 148     line2 text, -- Address line 2
 149     line3 text, -- Address line 3
 150     city text NOT NULL, -- City
 151     state text NOT NULL, -- State or province
 152     postalcode text NOT NULL, -- Postal code
 153     country text NOT NULL -- Country
 154 ) WITH OIDS;
 155
 156 -- Each mailing address can be one of several types
 157 CREATE TABLE address_address_type (
 158     address_id integer REFERENCES addresses NOT NULL, -- Address identifier
 159     address_type_id integer REFERENCES address_types NOT NULL, -- Address type
 160     PRIMARY KEY (address_id, address_type_id)
 161 ) WITH OIDS;
 162 CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
 163 CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
 164
 165 CREATE VIEW address_address_types AS
 166 SELECT address_id,
 167 array_accum(address_type_id) AS address_type_ids,
 168 array_accum(address_types.name) AS address_types
 169 FROM address_address_type
 170 LEFT JOIN address_types USING (address_type_id)
 171 GROUP BY address_id;
 172
 173 CREATE TABLE site_address (
 174     site_id integer REFERENCES sites NOT NULL, -- Site identifier
 175     address_id integer REFERENCES addresses NOT NULL, -- Address identifier
 176     PRIMARY KEY (site_id, address_id)
 177 ) WITH OIDS;
 178 CREATE INDEX site_address_site_id_idx ON site_address (site_id);
 179 CREATE INDEX site_address_address_id_idx ON site_address (address_id);
 180
 181 CREATE VIEW site_addresses AS
 182 SELECT site_id,
 183 array_accum(address_id) AS address_ids
 184 FROM site_address
 185 GROUP BY site_id;
 186
 187 --------------------------------------------------------------------------------
 188 -- Authentication Keys
 189 --------------------------------------------------------------------------------
 190
 191 -- Valid key types
 192 CREATE TABLE key_types (
 193     key_type text PRIMARY KEY -- Key type
 194 ) WITH OIDS;
 195 INSERT INTO key_types (key_type) VALUES ('ssh');
 196
 197 -- Authentication keys
 198 CREATE TABLE keys (
 199     key_id serial PRIMARY KEY, -- Key identifier
 200     key_type text REFERENCES key_types NOT NULL, -- Key type
 201     key text NOT NULL, -- Key material
 202     is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted
 203 ) WITH OIDS;
 204
 205 -- Account authentication key(s)
 206 CREATE TABLE person_key (
 207     key_id integer REFERENCES keys PRIMARY KEY, -- Key identifier
 208     person_id integer REFERENCES persons NOT NULL -- Account identifier
 209 ) WITH OIDS;
 210 CREATE INDEX person_key_person_id_idx ON person_key (person_id);
 211
 212 CREATE VIEW person_keys AS
 213 SELECT person_id,
 214 array_accum(key_id) AS key_ids
 215 FROM person_key
 216 GROUP BY person_id;
 217
 218 --------------------------------------------------------------------------------
 219 -- Account roles
 220 --------------------------------------------------------------------------------
 221
 222 -- Valid account roles
 223 CREATE TABLE roles (
 224     role_id integer PRIMARY KEY, -- Role identifier
 225     name text UNIQUE NOT NULL -- Role symbolic name
 226 ) WITH OIDS;
 227 INSERT INTO roles (role_id, name) VALUES (10, 'admin');
 228 INSERT INTO roles (role_id, name) VALUES (20, 'pi');
 229 INSERT INTO roles (role_id, name) VALUES (30, 'user');
 230 INSERT INTO roles (role_id, name) VALUES (40, 'tech');
 231
 232 CREATE TABLE person_role (
 233     person_id integer REFERENCES persons NOT NULL, -- Account identifier
 234     role_id integer REFERENCES roles NOT NULL, -- Role identifier
 235     PRIMARY KEY (person_id, role_id)
 236 ) WITH OIDS;
 237 CREATE INDEX person_role_person_id_idx ON person_role (person_id);
 238
 239 -- Account roles
 240 CREATE VIEW person_roles AS
 241 SELECT person_id,
 242 array_accum(role_id) AS role_ids,
 243 array_accum(roles.name) AS roles
 244 FROM person_role
 245 LEFT JOIN roles USING (role_id)
 246 GROUP BY person_id;
 247
 248 --------------------------------------------------------------------------------
 249 -- Nodes
 250 --------------------------------------------------------------------------------
 251
 252 -- Valid node boot states
 253 CREATE TABLE boot_states (
 254     boot_state text PRIMARY KEY
 255 ) WITH OIDS;
 256 INSERT INTO boot_states (boot_state) VALUES ('boot');
 257 INSERT INTO boot_states (boot_state) VALUES ('dbg');
 258 INSERT INTO boot_states (boot_state) VALUES ('inst');
 259 INSERT INTO boot_states (boot_state) VALUES ('rins');
 260 INSERT INTO boot_states (boot_state) VALUES ('rcnf');
 261 INSERT INTO boot_states (boot_state) VALUES ('new');
 262
 263 -- Nodes
 264 CREATE TABLE nodes (
 265     -- Mandatory
 266     node_id serial PRIMARY KEY, -- Node identifier
 267     hostname text NOT NULL, -- Node hostname
 268     site_id integer REFERENCES sites NOT NULL, -- At which site
 269
 270     boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state
 271     deleted boolean NOT NULL DEFAULT false, -- Is deleted
 272
 273     -- Optional
 274     model text, -- Hardware make and model
 275     boot_nonce text, -- Random nonce updated by Boot Manager
 276     version text, -- Boot CD version string updated by Boot Manager
 277     ssh_rsa_key text, -- SSH host key updated by Boot Manager
 278     key text, -- Node key generated by API when configuration file is downloaded
 279
 280     -- Timestamps
 281     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
 282     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 283 ) WITH OIDS;
 284 CREATE INDEX nodes_hostname_idx ON nodes (hostname) WHERE deleted IS false;
 285 CREATE INDEX nodes_site_id_idx ON nodes (site_id) WHERE deleted IS false;
 286
 287 -- Nodes at each site
 288 CREATE VIEW site_nodes AS
 289 SELECT site_id,
 290 array_accum(node_id) AS node_ids
 291 FROM nodes
 292 GROUP BY site_id;
 293
 294 --------------------------------------------------------------------------------
 295 -- Node groups
 296 --------------------------------------------------------------------------------
 297
 298 -- Node groups
 299 CREATE TABLE nodegroups (
 300     nodegroup_id serial PRIMARY KEY, -- Group identifier
 301     name text UNIQUE NOT NULL, -- Group name
 302     description text -- Group description
 303 ) WITH OIDS;
 304
 305 -- Node group membership
 306 CREATE TABLE nodegroup_node (
 307     nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Group identifier
 308     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
 309     PRIMARY KEY (nodegroup_id, node_id)
 310 ) WITH OIDS;
 311 CREATE INDEX nodegroup_node_nodegroup_id_idx ON nodegroup_node (nodegroup_id);
 312 CREATE INDEX nodegroup_node_node_id_idx ON nodegroup_node (node_id);
 313
 314 -- Nodes in each node group
 315 CREATE VIEW nodegroup_nodes AS
 316 SELECT nodegroup_id,
 317 array_accum(node_id) AS node_ids
 318 FROM nodegroup_node
 319 GROUP BY nodegroup_id;
 320
 321 -- Node groups that each node is a member of
 322 CREATE VIEW node_nodegroups AS
 323 SELECT node_id,
 324 array_accum(nodegroup_id) AS nodegroup_ids
 325 FROM nodegroup_node
 326 GROUP BY node_id;
 327
 328 --------------------------------------------------------------------------------
 329 -- Node configuration files
 330 --------------------------------------------------------------------------------
 331
 332 CREATE TABLE conf_files (
 333     conf_file_id serial PRIMARY KEY, -- Configuration file identifier
 334     enabled bool NOT NULL DEFAULT true, -- Configuration file is active
 335     source text NOT NULL, -- Relative path on the boot server where file can be downloaded
 336     dest text NOT NULL, -- Absolute path where file should be installed
 337     file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions
 338     file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner
 339     file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner
 340     preinstall_cmd text, -- Shell command to execute prior to installing
 341     postinstall_cmd text, -- Shell command to execute after installing
 342     error_cmd text, -- Shell command to execute if any error occurs
 343     ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs
 344     always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged
 345 );
 346
 347 CREATE TABLE conf_file_node (
 348     conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
 349     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
 350     PRIMARY KEY (conf_file_id, node_id)
 351 );
 352 CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id);
 353 CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
 354
 355 -- Nodes linked to each configuration file
 356 CREATE VIEW conf_file_nodes AS
 357 SELECT conf_file_id,
 358 array_accum(node_id) AS node_ids
 359 FROM conf_file_node
 360 GROUP BY conf_file_id;
 361
 362 -- Configuration files linked to each node
 363 CREATE VIEW node_conf_files AS
 364 SELECT node_id,
 365 array_accum(conf_file_id) AS conf_file_ids
 366 FROM conf_file_node
 367 GROUP BY node_id;
 368
 369 CREATE TABLE conf_file_nodegroup (
 370     conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
 371     nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier
 372     PRIMARY KEY (conf_file_id, nodegroup_id)
 373 );
 374 CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id);
 375 CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
 376
 377 -- Node groups linked to each configuration file
 378 CREATE VIEW conf_file_nodegroups AS
 379 SELECT conf_file_id,
 380 array_accum(nodegroup_id) AS nodegroup_ids
 381 FROM conf_file_nodegroup
 382 GROUP BY conf_file_id;
 383
 384 -- Configuration files linked to each node group
 385 CREATE VIEW nodegroup_conf_files AS
 386 SELECT nodegroup_id,
 387 array_accum(conf_file_id) AS conf_file_ids
 388 FROM conf_file_nodegroup
 389 GROUP BY nodegroup_id;
 390
 391 --------------------------------------------------------------------------------
 392 -- Node network interfaces
 393 --------------------------------------------------------------------------------
 394
 395 -- Valid network addressing schemes
 396 CREATE TABLE network_types (
 397     type text PRIMARY KEY -- Addressing scheme
 398 ) WITH OIDS;
 399 INSERT INTO network_types (type) VALUES ('ipv4');
 400
 401 -- Valid network configuration methods
 402 CREATE TABLE network_methods (
 403     method text PRIMARY KEY -- Configuration method
 404 ) WITH OIDS;
 405 INSERT INTO network_methods (method) VALUES ('static');
 406 INSERT INTO network_methods (method) VALUES ('dhcp');
 407 INSERT INTO network_methods (method) VALUES ('proxy');
 408 INSERT INTO network_methods (method) VALUES ('tap');
 409 INSERT INTO network_methods (method) VALUES ('ipmi');
 410 INSERT INTO network_methods (method) VALUES ('unknown');
 411
 412 -- Node network interfaces
 413 CREATE TABLE nodenetworks (
 414     -- Mandatory
 415     nodenetwork_id serial PRIMARY KEY, -- Network interface identifier
 416     node_id integer REFERENCES nodes NOT NULL, -- Which node
 417     is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node
 418     type text REFERENCES network_types NOT NULL, -- Addressing scheme
 419     method text REFERENCES network_methods NOT NULL, -- Configuration method
 420
 421     -- Optional, depending on type and method
 422     ip text, -- IP address
 423     mac text, -- MAC address
 424     gateway text, -- Default gateway address
 425     network text, -- Network address
 426     broadcast text, -- Network broadcast address
 427     netmask text, -- Network mask
 428     dns1 text, -- Primary DNS server
 429     dns2 text, -- Secondary DNS server
 430     bwlimit integer, -- Bandwidth limit in bps
 431     hostname text -- Hostname of this interface
 432 ) WITH OIDS;
 433 CREATE INDEX nodenetworks_node_id_idx ON nodenetworks (node_id);
 434
 435 -- Ordered by primary interface first
 436 CREATE VIEW nodenetworks_ordered AS
 437 SELECT node_id, nodenetwork_id
 438 FROM nodenetworks
 439 ORDER BY is_primary DESC;
 440
 441 -- Network interfaces on each node
 442 CREATE VIEW node_nodenetworks AS
 443 SELECT node_id,
 444 array_accum(nodenetwork_id) AS nodenetwork_ids
 445 FROM nodenetworks_ordered
 446 GROUP BY node_id;
 447
 448 --------------------------------------------------------------------------------
 449 -- Power control units (PCUs)
 450 --------------------------------------------------------------------------------
 451
 452 CREATE TABLE pcus (
 453     -- Mandatory
 454     pcu_id serial PRIMARY KEY, -- PCU identifier
 455     site_id integer REFERENCES sites NOT NULL, -- Site identifier
 456     hostname text, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU)
 457     ip text NOT NULL, -- IP, not necessarily unique
 458
 459     -- Optional
 460     protocol text, -- Protocol, e.g. ssh or https or telnet
 461     username text, -- Username, if applicable
 462     "password" text, -- Password, if applicable
 463     model text, -- Model, e.g. BayTech or iPal
 464     notes text -- Random notes
 465 ) WITH OIDS;
 466 CREATE INDEX pcus_site_id_idx ON pcus (site_id);
 467
 468 CREATE VIEW site_pcus AS
 469 SELECT site_id,
 470 array_accum(pcu_id) AS pcu_ids
 471 FROM pcus
 472 GROUP BY site_id;
 473
 474 CREATE TABLE pcu_node (
 475     pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier
 476     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
 477     port integer NOT NULL, -- Port number
 478     PRIMARY KEY (pcu_id, node_id), -- The same node cannot be controlled by different ports
 479     UNIQUE (pcu_id, port) -- The same port cannot control multiple nodes
 480 );
 481 CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id);
 482 CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id);
 483
 484 CREATE VIEW node_pcus AS
 485 SELECT node_id,
 486 array_accum(pcu_id) AS pcu_ids,
 487 array_accum(port) AS ports
 488 FROM pcu_node
 489 GROUP BY node_id;
 490
 491 CREATE VIEW pcu_nodes AS
 492 SELECT pcu_id,
 493 array_accum(node_id) AS node_ids,
 494 array_accum(port) AS ports
 495 FROM pcu_node
 496 GROUP BY pcu_id;
 497
 498 --------------------------------------------------------------------------------
 499 -- Slices
 500 --------------------------------------------------------------------------------
 501
 502 CREATE TABLE slice_instantiations (
 503     instantiation text PRIMARY KEY
 504 ) WITH OIDS;
 505 INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice
 506 INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager
 507 INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated
 508
 509 -- Slices
 510 CREATE TABLE slices (
 511     slice_id serial PRIMARY KEY, -- Slice identifier
 512     site_id integer REFERENCES sites NOT NULL, -- Site identifier
 513
 514     name text NOT NULL, -- Slice name
 515     instantiation text REFERENCES slice_instantiations NOT NULL DEFAULT 'plc-instantiated', -- Slice state, e.g. plc-instantiated
 516     url text, -- Project URL
 517     description text, -- Project description
 518
 519     max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice
 520
 521     creator_person_id integer REFERENCES persons NOT NULL, -- Creator
 522     created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Creation date
 523     expires timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 weeks', -- Expiration date
 524
 525     is_deleted boolean NOT NULL DEFAULT false
 526 ) WITH OIDS;
 527 CREATE INDEX slices_site_id_idx ON slices (site_id) WHERE is_deleted IS false;
 528 CREATE INDEX slices_name_idx ON slices (name) WHERE is_deleted IS false;
 529
 530 -- Slivers
 531 CREATE TABLE slice_node (
 532     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
 533     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
 534     PRIMARY KEY (slice_id, node_id)
 535 ) WITH OIDS;
 536 CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
 537 CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
 538
 539 -- Synonym for slice_node
 540 CREATE VIEW slivers AS
 541 SELECT * FROM slice_node;
 542
 543 -- Nodes in each slice
 544 CREATE VIEW slice_nodes AS
 545 SELECT slice_id,
 546 array_accum(node_id) AS node_ids
 547 FROM slice_node
 548 GROUP BY slice_id;
 549
 550 -- Slices on each node
 551 CREATE VIEW node_slices AS
 552 SELECT node_id,
 553 array_accum(slice_id) AS slice_ids
 554 FROM slice_node
 555 GROUP BY node_id;
 556
 557 -- Slices at each site
 558 CREATE VIEW site_slices AS
 559 SELECT site_id,
 560 array_accum(slice_id) AS slice_ids
 561 FROM slices
 562 WHERE is_deleted is false
 563 GROUP BY site_id;
 564
 565 -- Slice membership
 566 CREATE TABLE slice_person (
 567     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
 568     person_id integer REFERENCES persons NOT NULL, -- Account identifier
 569     PRIMARY KEY (slice_id, person_id)
 570 ) WITH OIDS;
 571 CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id);
 572 CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
 573
 574 -- Members of the slice
 575 CREATE VIEW slice_persons AS
 576 SELECT slice_id,
 577 array_accum(person_id) AS person_ids
 578 FROM slice_person
 579 GROUP BY slice_id;
 580
 581 -- Slices of which each person is a member
 582 CREATE VIEW person_slices AS
 583 SELECT person_id,
 584 array_accum(slice_id) AS slice_ids
 585 FROM slice_person
 586 GROUP BY person_id;
 587
 588 --------------------------------------------------------------------------------
 589 -- Slice attributes
 590 --------------------------------------------------------------------------------
 591
 592 -- Slice attribute types
 593 CREATE TABLE slice_attribute_types (
 594     attribute_type_id serial PRIMARY KEY, -- Attribute type identifier
 595     name text UNIQUE NOT NULL, -- Attribute name
 596     description text, -- Attribute description
 597     min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can set or change this attribute
 598 ) WITH OIDS;
 599
 600 -- Slice/sliver attributes
 601 CREATE TABLE slice_attribute (
 602     slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier
 603     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
 604     node_id integer REFERENCES nodes, -- Sliver attribute if set
 605     attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier
 606     value text
 607 ) WITH OIDS;
 608 CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id);
 609 CREATE INDEX slice_attribute_node_id_idx ON slice_attribute (node_id);
 610
 611 CREATE VIEW slice_attributes AS
 612 SELECT slice_id,
 613 array_accum(slice_attribute_id) AS slice_attribute_ids
 614 FROM slice_attribute
 615 GROUP BY slice_id;
 616
 617 --------------------------------------------------------------------------------
 618 -- Peers
 619 --------------------------------------------------------------------------------
 620
 621 -- Peers
 622 CREATE TABLE peers (
 623     peer_id serial PRIMARY KEY, -- Peer identifier
 624     peername text NOT NULL, -- Peer name
 625     peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface
 626     cacert text, -- (SSL) Public certificate of peer API server
 627     key text, -- (GPG) Public key used for authentication
 628     deleted boolean NOT NULL DEFAULT false
 629 ) WITH OIDS;
 630 CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
 631
 632 -- Objects at each peer
 633 CREATE TABLE peer_site (
 634     site_id integer REFERENCES sites PRIMARY KEY, -- Local site identifier
 635     peer_id integer REFERENCES peers NOT NULL, -- Peer identifier
 636     peer_site_id integer NOT NULL, -- Foreign site identifier at peer
 637     UNIQUE (peer_id, peer_site_id) -- The same foreign site should not be cached twice
 638 ) WITH OIDS;
 639 CREATE INDEX peer_site_peer_id_idx ON peers (peer_id);
 640
 641 CREATE VIEW peer_sites AS
 642 SELECT peer_id,
 643 array_accum(site_id) AS site_ids,
 644 array_accum(peer_site_id) AS peer_site_ids
 645 FROM peer_site
 646 GROUP BY peer_id;
 647
 648 CREATE TABLE peer_person (
 649     person_id integer REFERENCES persons PRIMARY KEY, -- Local user identifier
 650     peer_id integer REFERENCES peers NOT NULL, -- Peer identifier
 651     peer_person_id integer NOT NULL, -- Foreign user identifier at peer
 652     UNIQUE (peer_id, peer_person_id) -- The same foreign user should not be cached twice
 653 ) WITH OIDS;
 654 CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id);
 655
 656 CREATE VIEW peer_persons AS
 657 SELECT peer_id,
 658 array_accum(person_id) AS person_ids,
 659 array_accum(peer_person_id) AS peer_person_ids
 660 FROM peer_person
 661 GROUP BY peer_id;
 662
 663 CREATE TABLE peer_key (
 664     key_id integer REFERENCES keys PRIMARY KEY, -- Local key identifier
 665     peer_id integer REFERENCES peers NOT NULL, -- Peer identifier
 666     peer_key_id integer NOT NULL, -- Foreign key identifier at peer
 667     UNIQUE (peer_id, peer_key_id) -- The same foreign key should not be cached twice
 668 ) WITH OIDS;
 669 CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id);
 670
 671 CREATE VIEW peer_keys AS
 672 SELECT peer_id,
 673 array_accum(key_id) AS key_ids,
 674 array_accum(peer_key_id) AS peer_key_ids
 675 FROM peer_key
 676 GROUP BY peer_id;
 677
 678 CREATE TABLE peer_node (
 679     node_id integer REFERENCES nodes PRIMARY KEY, -- Local node identifier
 680     peer_id integer REFERENCES peers NOT NULL, -- Peer identifier
 681     peer_node_id integer NOT NULL, -- Foreign node identifier
 682     UNIQUE (peer_id, peer_node_id) -- The same foreign node should not be cached twice
 683 ) WITH OIDS;
 684 CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id);
 685
 686 CREATE VIEW peer_nodes AS
 687 SELECT peer_id,
 688 array_accum(node_id) AS node_ids,
 689 array_accum(peer_node_id) AS peer_node_ids
 690 FROM peer_node
 691 GROUP BY peer_id;
 692
 693 CREATE TABLE peer_slice (
 694     slice_id integer REFERENCES slices PRIMARY KEY, -- Local slice identifier
 695     peer_id integer REFERENCES peers NOT NULL, -- Peer identifier
 696     peer_slice_id integer NOT NULL, -- Slice identifier at peer
 697     UNIQUE (peer_id, peer_slice_id) -- The same foreign slice should not be cached twice
 698 ) WITH OIDS;
 699 CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id);
 700
 701 CREATE VIEW peer_slices AS
 702 SELECT peer_id,
 703 array_accum(slice_id) AS slice_ids,
 704 array_accum(peer_slice_id) AS peer_slice_ids
 705 FROM peer_slice
 706 GROUP BY peer_id;
 707
 708 --------------------------------------------------------------------------------
 709 -- Authenticated sessions
 710 --------------------------------------------------------------------------------
 711
 712 -- Authenticated sessions
 713 CREATE TABLE sessions (
 714     session_id text PRIMARY KEY, -- Session identifier
 715     expires timestamp without time zone
 716 ) WITH OIDS;
 717
 718 -- People can have multiple sessions
 719 CREATE TABLE person_session (
 720     person_id integer REFERENCES persons NOT NULL, -- Account identifier
 721     session_id text REFERENCES sessions NOT NULL, -- Session identifier
 722     PRIMARY KEY (person_id, session_id),
 723     UNIQUE (session_id) -- Sessions are unique
 724 ) WITH OIDS;
 725 CREATE INDEX person_session_person_id_idx ON person_session (person_id);
 726
 727 -- Nodes can have only one session
 728 CREATE TABLE node_session (
 729     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
 730     session_id text REFERENCES sessions NOT NULL, -- Session identifier
 731     UNIQUE (node_id), -- Nodes can have only one session
 732     UNIQUE (session_id) -- Sessions are unique
 733 ) WITH OIDS;
 734
 735 --------------------------------------------------------------------------------
 736 -- Message templates
 737 --------------------------------------------------------------------------------
 738
 739 CREATE TABLE messages (
 740     message_id text PRIMARY KEY, -- Message name
 741     subject text, -- Message summary
 742     template text, -- Message template
 743     enabled bool NOT NULL DEFAULT true -- Whether message is enabled
 744 ) WITH OIDS;
 745
 746 --------------------------------------------------------------------------------
 747 -- Events
 748 --------------------------------------------------------------------------------
 749
 750 -- Events
 751 CREATE TABLE events (
 752     event_id serial PRIMARY KEY,  -- Event identifier
 753     person_id integer REFERENCES persons, -- Person responsible for event, if any
 754     node_id integer REFERENCES nodes, -- Node responsible for event, if any
 755     fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error
 756     call_name text NOT NULL, -- Call responsible for this event
 757     call text NOT NULL, -- Call responsible for this event, including parameters
 758     object_type text, -- What type of object is this event affecting
 759     message text, -- High level description of this event
 760     runtime float DEFAULT 0, -- Event run time
 761     time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp
 762 ) WITH OIDS;
 763
 764 -- Database object(s) that may have been affected by a particular event
 765 CREATE TABLE event_object (
 766     event_id integer REFERENCES events NOT NULL, -- Event identifier
 767     object_id integer NOT NULL -- Object identifier
 768 ) WITH OIDS;
 769 CREATE INDEX event_object_event_id_idx ON event_object (event_id);
 770 CREATE INDEX event_object_object_id_idx ON event_object (object_id);
 771
 772 CREATE VIEW event_objects AS
 773 SELECT event_id,
 774 array_accum(object_id) AS object_ids
 775 FROM event_object
 776 GROUP BY event_id;
 777
 778 --------------------------------------------------------------------------------
 779 -- Useful views
 780 --------------------------------------------------------------------------------
 781
 782 CREATE OR REPLACE VIEW view_events AS
 783 SELECT
 784 events.event_id,
 785 events.person_id,
 786 events.node_id,
 787 events.fault_code,
 788 events.call_name,
 789 events.call,
 790 events.object_type,
 791 events.message,
 792 events.runtime,
 793 CAST(date_part('epoch', events.time) AS bigint) AS time,
 794 COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_ids
 795 FROM events;
 796
 797 CREATE OR REPLACE VIEW view_persons AS
 798 SELECT
 799 persons.person_id,
 800 persons.email,
 801 persons.first_name,
 802 persons.last_name,
 803 persons.deleted,
 804 persons.enabled,
 805 persons.password,
 806 persons.verification_key,
 807 CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires,
 808 persons.title,
 809 persons.phone,
 810 persons.url,
 811 persons.bio,
 812 CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created,
 813 CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated,
 814 peer_person.peer_id,
 815 peer_person.peer_person_id,
 816 COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids,
 817 COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles,
 818 COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids,
 819 COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids,
 820 COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids
 821 FROM persons
 822 LEFT JOIN peer_person USING (person_id);
 823
 824 CREATE OR REPLACE VIEW view_peers AS
 825 SELECT
 826 peers.*,
 827 COALESCE((SELECT site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS site_ids,
 828 COALESCE((SELECT peer_site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS peer_site_ids,
 829 COALESCE((SELECT person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS person_ids,
 830 COALESCE((SELECT peer_person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS peer_person_ids,
 831 COALESCE((SELECT key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS key_ids,
 832 COALESCE((SELECT peer_key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS peer_key_ids,
 833 COALESCE((SELECT node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS node_ids,
 834 COALESCE((SELECT peer_node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS peer_node_ids,
 835 COALESCE((SELECT slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS slice_ids,
 836 COALESCE((SELECT peer_slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS peer_slice_ids
 837 FROM peers;
 838
 839 CREATE OR REPLACE VIEW view_nodes AS
 840 SELECT
 841 nodes.node_id,
 842 nodes.hostname,
 843 nodes.site_id,
 844 nodes.boot_state,
 845 nodes.deleted,
 846 nodes.model,
 847 nodes.boot_nonce,
 848 nodes.version,
 849 nodes.ssh_rsa_key,
 850 nodes.key,
 851 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
 852 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
 853 peer_node.peer_id,
 854 peer_node.peer_node_id,
 855 COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids,
 856 COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids,
 857 COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids,
 858 COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids,
 859 COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports,
 860 COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids,
 861 node_session.session_id AS session
 862 FROM nodes
 863 LEFT JOIN peer_node USING (node_id)
 864 LEFT JOIN node_session USING (node_id);
 865
 866 CREATE OR REPLACE VIEW view_nodegroups AS
 867 SELECT
 868 nodegroups.*,
 869 COALESCE((SELECT node_ids FROM nodegroup_nodes WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') AS node_ids,
 870 COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids
 871 FROM nodegroups;
 872
 873 CREATE OR REPLACE VIEW view_conf_files AS
 874 SELECT
 875 conf_files.*,
 876 COALESCE((SELECT node_ids FROM conf_file_nodes WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids,
 877 COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids
 878 FROM conf_files;
 879
 880 CREATE OR REPLACE VIEW view_pcus AS
 881 SELECT
 882 pcus.*,
 883 COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
 884 COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
 885 FROM pcus;
 886
 887 CREATE OR REPLACE VIEW view_sites AS
 888 SELECT
 889 sites.site_id,
 890 sites.login_base,
 891 sites.name,
 892 sites.abbreviated_name,
 893 sites.deleted,
 894 sites.enabled,
 895 sites.is_public,
 896 sites.max_slices,
 897 sites.max_slivers,
 898 sites.latitude,
 899 sites.longitude,
 900 sites.url,
 901 CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created,
 902 CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
 903 peer_site.peer_id,
 904 peer_site.peer_site_id,
 905 COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids,
 906 COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
 907 COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
 908 COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
 909 COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids
 910 FROM sites
 911 LEFT JOIN peer_site USING (site_id);
 912
 913 CREATE OR REPLACE VIEW view_addresses AS
 914 SELECT
 915 addresses.*,
 916 COALESCE((SELECT address_type_ids FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_type_ids,
 917 COALESCE((SELECT address_types FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_types
 918 FROM addresses;
 919
 920 CREATE OR REPLACE VIEW view_keys AS
 921 SELECT
 922 keys.*,
 923 person_key.person_id,
 924 peer_key.peer_id,
 925 peer_key.peer_key_id
 926 FROM keys
 927 LEFT JOIN person_key USING (key_id)
 928 LEFT JOIN peer_key USING (key_id);
 929
 930 CREATE OR REPLACE VIEW view_slices AS
 931 SELECT
 932 slices.slice_id,
 933 slices.site_id,
 934 slices.name,
 935 slices.instantiation,
 936 slices.url,
 937 slices.description,
 938 slices.max_nodes,
 939 slices.creator_person_id,
 940 slices.is_deleted,
 941 CAST(date_part('epoch', slices.created) AS bigint) AS created,
 942 CAST(date_part('epoch', slices.expires) AS bigint) AS expires,
 943 peer_slice.peer_id,
 944 peer_slice.peer_slice_id,
 945 COALESCE((SELECT node_ids FROM slice_nodes WHERE slice_nodes.slice_id = slices.slice_id), '{}') AS node_ids,
 946 COALESCE((SELECT person_ids FROM slice_persons WHERE slice_persons.slice_id = slices.slice_id), '{}') AS person_ids,
 947 COALESCE((SELECT slice_attribute_ids FROM slice_attributes WHERE slice_attributes.slice_id = slices.slice_id), '{}') AS slice_attribute_ids
 948 FROM slices
 949 LEFT JOIN peer_slice USING (slice_id);
 950
 951 CREATE OR REPLACE VIEW view_slice_attributes AS
 952 SELECT
 953 slice_attribute.slice_attribute_id,
 954 slice_attribute.slice_id,
 955 slice_attribute.node_id,
 956 slice_attribute_types.attribute_type_id,
 957 slice_attribute_types.name,
 958 slice_attribute_types.description,
 959 slice_attribute_types.min_role_id,
 960 slice_attribute.value
 961 FROM slice_attribute
 962 INNER JOIN slice_attribute_types USING (attribute_type_id);
 963
 964 CREATE OR REPLACE VIEW view_sessions AS
 965 SELECT
 966 sessions.session_id,
 967 CAST(date_part('epoch', sessions.expires) AS bigint) AS expires,
 968 person_session.person_id,
 969 node_session.node_id
 970 FROM sessions
 971 LEFT JOIN person_session USING (session_id)
 972 LEFT JOIN node_session USING (session_id);
 973
 974 --------------------------------------------------------------------------------
 975 -- Built-in maintenance account and default site
 976 --------------------------------------------------------------------------------
 977
 978 INSERT INTO persons
 979 (first_name, last_name, email, password, enabled)
 980 VALUES
 981 ('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true);
 982
 983 INSERT INTO person_role (person_id, role_id) VALUES (1, 10);
 984 INSERT INTO person_role (person_id, role_id) VALUES (1, 20);
 985 INSERT INTO person_role (person_id, role_id) VALUES (1, 30);
 986 INSERT INTO person_role (person_id, role_id) VALUES (1, 40);
 987
 988 INSERT INTO sites
 989 (login_base, name, abbreviated_name, max_slices)
 990 VALUES
 991 ('pl', 'PlanetLab Central', 'PLC', 100);