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.1 2006/09/25 14:40:16 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 UNIQUE 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 );
  57
  58 --------------------------------------------------------------------------------
  59 -- Sites
  60 --------------------------------------------------------------------------------
  61
  62 -- Sites
  63 CREATE TABLE sites (
  64     -- Mandatory
  65     site_id serial PRIMARY KEY, -- Site identifier
  66     login_base text UNIQUE NOT NULL, -- Site slice prefix
  67     name text NOT NULL, -- Site name
  68     abbreviated_name text NOT NULL, -- Site abbreviated name
  69     deleted boolean NOT NULL DEFAULT false, -- Has been deleted
  70     is_public boolean NOT NULL DEFAULT true, -- Shows up in public lists
  71     max_slices integer NOT NULL DEFAULT 0, -- Maximum number of slices
  72
  73     -- XXX Sites should have an address
  74     -- address_id REFERENCES addresses,
  75
  76     -- Optional
  77     latitude real,
  78     longitude real,
  79     url text,
  80
  81     -- Timestamps
  82     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  83     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
  84 );
  85
  86 -- Account site membership
  87 CREATE TABLE person_site (
  88     person_id integer REFERENCES persons, -- Account identifier
  89     site_id integer REFERENCES sites, -- 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
  94 -- Ordered by primary site first
  95 CREATE VIEW person_site_ordered AS
  96 SELECT person_id, site_id
  97 FROM person_site
  98 ORDER BY is_primary DESC;
  99
 100 -- Sites that each person is a member of
 101 CREATE VIEW person_sites AS
 102 SELECT person_id,
 103 array_to_string(array_accum(site_id), ',') AS site_ids
 104 FROM person_site_ordered
 105 GROUP BY person_id;
 106
 107 -- Accounts at each site
 108 CREATE VIEW site_persons AS
 109 SELECT site_id,
 110 array_to_string(array_accum(person_id), ',') AS person_ids
 111 FROM person_site
 112 GROUP BY site_id;
 113
 114 --------------------------------------------------------------------------------
 115 -- Mailing Addresses
 116 --------------------------------------------------------------------------------
 117
 118 -- Valid mailing address types
 119 CREATE TABLE address_types (
 120     address_type_id serial PRIMARY KEY, -- Address type identifier
 121     address_type text UNIQUE NOT NULL -- Address type
 122 );
 123 INSERT INTO address_types (address_type) VALUES ('Personal');
 124 INSERT INTO address_types (address_type) VALUES ('Shipping');
 125 INSERT INTO address_types (address_type) VALUES ('Site');
 126
 127 -- Mailing addresses
 128 CREATE TABLE addresses (
 129     address_id serial PRIMARY KEY, -- Address identifier
 130     line1 text NOT NULL, -- Address line 1
 131     line2 text, -- Address line 2
 132     line3 text, -- Address line 3
 133     city text NOT NULL, -- City
 134     state text NOT NULL, -- State or province
 135     postalcode text NOT NULL, -- Postal code
 136     country text NOT NULL -- Country
 137 );
 138
 139 -- Each address can be multiple types
 140 CREATE TABLE address_address_type (
 141     address_id integer REFERENCES addresses,
 142     address_type_id integer REFERENCES address_types,
 143     PRIMARY KEY (address_id, address_type_id)
 144 );
 145
 146 -- Types of each address
 147 CREATE VIEW address_address_types AS
 148 SELECT address_id,
 149 array_to_string(array_accum(address_type_id), ',') AS address_type_ids,
 150 array_to_string(array_accum(address_type), ',') AS address_types
 151 FROM address_address_type
 152 LEFT JOIN address_types USING (address_type_id)
 153 GROUP BY address_id;
 154
 155 CREATE TABLE person_address (
 156     person_id integer REFERENCES persons, -- Account identifier
 157     address_id integer REFERENCES addresses, -- Address identifier
 158     PRIMARY KEY (person_id, address_id)
 159 );
 160
 161 -- Account mailing addresses
 162 CREATE VIEW person_addresses AS
 163 SELECT person_id,
 164 array_to_string(array_accum(address_id), ',') AS address_ids
 165 FROM person_address
 166 GROUP BY person_id;
 167
 168 --------------------------------------------------------------------------------
 169 -- Authentication Keys
 170 --------------------------------------------------------------------------------
 171
 172 -- Valid key types
 173 CREATE TABLE key_types (
 174     key_type text PRIMARY KEY -- Key type
 175 );
 176 INSERT INTO key_types (key_type) VALUES ('ssh');
 177
 178 -- Authentication keys
 179 CREATE TABLE keys (
 180     key_id serial PRIMARY KEY, -- Key identifier
 181     key_type text REFERENCES key_types, -- Key type
 182     key text NOT NULL, -- Key material
 183     is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted
 184 );
 185
 186 -- Account authentication key(s)
 187 CREATE TABLE person_key (
 188     person_id integer REFERENCES persons, -- Account identifier
 189     key_id integer REFERENCES keys, -- Key identifier
 190     is_primary boolean NOT NULL DEFAULT false, -- Is the primary key for this account
 191     PRIMARY KEY (person_id, key_id)
 192 );
 193
 194 CREATE VIEW person_keys AS
 195 SELECT person_id,
 196 array_to_string(array_accum(key_id), ',') AS key_ids
 197 FROM person_key
 198 GROUP BY person_id;
 199
 200 --------------------------------------------------------------------------------
 201 -- Account roles
 202 --------------------------------------------------------------------------------
 203
 204 -- Valid account roles
 205 CREATE TABLE roles (
 206     role_id integer PRIMARY KEY, -- Role identifier
 207     name text UNIQUE NOT NULL -- Role symbolic name
 208 );
 209 INSERT INTO roles (role_id, name) VALUES (10, 'admin');
 210 INSERT INTO roles (role_id, name) VALUES (20, 'pi');
 211 INSERT INTO roles (role_id, name) VALUES (30, 'user');
 212 INSERT INTO roles (role_id, name) VALUES (40, 'tech');
 213 INSERT INTO roles (role_id, name) VALUES (1000, 'node');
 214 INSERT INTO roles (role_id, name) VALUES (2000, 'anonymous');
 215
 216 CREATE TABLE person_role (
 217     person_id integer REFERENCES persons, -- Account identifier
 218     role_id integer REFERENCES roles, -- Role identifier
 219     PRIMARY KEY (person_id, role_id)
 220 );
 221
 222 -- Account roles
 223 CREATE VIEW person_roles AS
 224 SELECT person_id,
 225 array_to_string(array_accum(role_id), ',') AS role_ids,
 226 array_to_string(array_accum(roles.name), ',') AS roles
 227 FROM person_role
 228 LEFT JOIN roles USING (role_id)
 229 GROUP BY person_id;
 230
 231 --------------------------------------------------------------------------------
 232 -- Nodes
 233 --------------------------------------------------------------------------------
 234
 235 -- Valid node boot states
 236 CREATE TABLE boot_states (
 237     boot_state text PRIMARY KEY
 238 );
 239 INSERT INTO boot_states (boot_state) VALUES ('boot');
 240 INSERT INTO boot_states (boot_state) VALUES ('dbg');
 241 INSERT INTO boot_states (boot_state) VALUES ('inst');
 242 INSERT INTO boot_states (boot_state) VALUES ('rins');
 243 INSERT INTO boot_states (boot_state) VALUES ('rcnf');
 244 INSERT INTO boot_states (boot_state) VALUES ('new');
 245
 246 -- Nodes
 247 CREATE TABLE nodes (
 248     -- Mandatory
 249     node_id serial PRIMARY KEY, -- Node identifier
 250     hostname text UNIQUE NOT NULL, -- Node hostname
 251     site_id integer REFERENCES sites, -- At which site
 252     boot_state text REFERENCES boot_states, -- Node boot state
 253     deleted boolean NOT NULL DEFAULT false, -- Is deleted
 254
 255     -- Optional
 256     model text, -- Hardware make and model
 257     boot_nonce text, -- Random nonce updated by Boot Manager
 258     version text, -- Boot CD version string updated by Boot Manager
 259     -- XXX Should be key_id integer REFERENCES keys
 260     ssh_rsa_key text, -- SSH host key updated by Boot Manager
 261     key text, -- Node key generated by API when configuration file is downloaded
 262     session text, -- Session key generated by PLC when Boot Manager authenticates
 263
 264     -- Timestamps
 265     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
 266     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 267 );
 268
 269 -- Nodes at each site
 270 CREATE VIEW site_nodes AS
 271 SELECT site_id,
 272 array_to_string(array_accum(node_id), ',') AS node_ids
 273 FROM nodes
 274 GROUP BY site_id;
 275
 276 --------------------------------------------------------------------------------
 277 -- Node groups
 278 --------------------------------------------------------------------------------
 279
 280 -- Node groups
 281 CREATE TABLE nodegroups (
 282     nodegroup_id serial PRIMARY KEY, -- Group identifier
 283     name text UNIQUE NOT NULL, -- Group name
 284     description text -- Group description
 285 );
 286
 287 -- Node group membership
 288 CREATE TABLE nodegroup_node (
 289     nodegroup_id integer REFERENCES nodegroups, -- Group identifier
 290     node_id integer REFERENCES nodes, -- Node identifier
 291     PRIMARY KEY (nodegroup_id, node_id)
 292 );
 293
 294 -- Nodes in each node gruop
 295 CREATE VIEW nodegroup_nodes AS
 296 SELECT nodegroup_id,
 297 array_to_string(array_accum(node_id), ',') AS node_ids
 298 FROM nodegroup_node
 299 GROUP BY nodegroup_id;
 300
 301 -- Node groups that each node is a member of
 302 CREATE VIEW node_nodegroups AS
 303 SELECT node_id,
 304 array_to_string(array_accum(nodegroup_id), ',') AS nodegroup_ids
 305 FROM nodegroup_node
 306 GROUP BY node_id;
 307
 308 --------------------------------------------------------------------------------
 309 -- Node network interfaces
 310 --------------------------------------------------------------------------------
 311
 312 -- Valid network addressing schemes
 313 CREATE TABLE nodenetwork_types (
 314     type text PRIMARY KEY -- Addressing scheme
 315 );
 316 INSERT INTO nodenetwork_types (type) VALUES ('ipv4');
 317 INSERT INTO nodenetwork_types (type) VALUES ('ipv6');
 318
 319 -- Valid network configuration methods
 320 CREATE TABLE nodenetwork_methods (
 321     method text PRIMARY KEY -- Configuration method
 322 );
 323 INSERT INTO nodenetwork_methods (method) VALUES ('static');
 324 INSERT INTO nodenetwork_methods (method) VALUES ('dhcp');
 325 INSERT INTO nodenetwork_methods (method) VALUES ('proxy');
 326 INSERT INTO nodenetwork_methods (method) VALUES ('tap');
 327 INSERT INTO nodenetwork_methods (method) VALUES ('ipmi');
 328 INSERT INTO nodenetwork_methods (method) VALUES ('unknown');
 329
 330 -- Node network interfaces
 331 CREATE TABLE nodenetworks (
 332     -- Mandatory
 333     nodenetwork_id serial PRIMARY KEY, -- Network interface identifier
 334     node_id integer REFERENCES nodes, -- Which node
 335     is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node
 336     type text REFERENCES nodenetwork_types, -- Addressing scheme
 337     method text REFERENCES nodenetwork_methods, -- Configuration method
 338
 339     -- Optional, depending on type and method
 340     ip text, -- IP address
 341     mac text, -- MAC address
 342     gateway text, -- Default gateway address
 343     network text, -- Network address
 344     broadcast text, -- Network broadcast address
 345     netmask text, -- Network mask
 346     dns1 text, -- Primary DNS server
 347     dns2 text, -- Secondary DNS server
 348     bwlimit integer, -- Bandwidth limit in bps
 349     hostname text -- Hostname of this interface
 350 );
 351
 352 -- Network interfaces on each node
 353 CREATE VIEW node_nodenetworks AS
 354 SELECT node_id,
 355 array_to_string(array_accum(nodenetwork_id), ',') AS nodenetwork_ids
 356 FROM nodenetworks
 357 GROUP BY node_id;
 358
 359 --------------------------------------------------------------------------------
 360 -- Useful views
 361 --------------------------------------------------------------------------------
 362
 363 CREATE VIEW view_persons AS
 364 SELECT persons.*,
 365 person_roles.role_ids,
 366 person_roles.roles,
 367 person_sites.site_ids,
 368 person_addresses.address_ids,
 369 person_keys.key_ids
 370 FROM persons
 371 LEFT JOIN person_roles USING (person_id)
 372 LEFT JOIN person_sites USING (person_id)
 373 LEFT JOIN person_addresses USING (person_id)
 374 LEFT JOIN person_keys USING (person_id);
 375
 376 CREATE VIEW view_addresses AS
 377 SELECT addresses.*,
 378 address_address_types.address_type_ids,
 379 address_address_types.address_types
 380 FROM addresses
 381 LEFT JOIN address_address_types USING (address_id);
 382
 383 CREATE VIEW view_nodes AS
 384 SELECT nodes.*,
 385 node_nodenetworks.nodenetwork_ids,
 386 node_nodegroups.nodegroup_ids
 387 FROM nodes
 388 LEFT JOIN node_nodenetworks USING (node_id)
 389 LEFT JOIN node_nodegroups USING (node_id);
 390
 391 CREATE VIEW view_nodegroups AS
 392 SELECT nodegroups.*,
 393 nodegroup_nodes.node_ids
 394 FROM nodegroups
 395 LEFT JOIN nodegroup_nodes USING (nodegroup_id);
 396
 397 CREATE VIEW view_sites AS
 398 SELECT sites.*,
 399 site_persons.person_ids,
 400 site_nodes.node_ids
 401 FROM sites
 402 LEFT JOIN site_persons USING (site_id)
 403 LEFT JOIN site_nodes USING (site_id);
 404
 405 --------------------------------------------------------------------------------
 406 -- Built-in maintenance account and default site
 407 --------------------------------------------------------------------------------
 408
 409 INSERT INTO persons
 410 (first_name, last_name, email, password, enabled)
 411 VALUES
 412 ('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true);
 413
 414 INSERT INTO person_role (person_id, role_id) VALUES (1, 10);
 415 INSERT INTO person_role (person_id, role_id) VALUES (1, 20);
 416 INSERT INTO person_role (person_id, role_id) VALUES (1, 30);
 417 INSERT INTO person_role (person_id, role_id) VALUES (1, 40);
 418
 419 INSERT INTO sites
 420 (login_base, name, abbreviated_name, max_slices)
 421 VALUES
 422 ('pl', 'PlanetLab Central', 'PLC', 100);