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