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