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