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