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