2 -- PlanetLab Central database schema
3 -- Version 4, PostgreSQL
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>
10 -- Copyright (C) 2006 The Trustees of Princeton University
15 --------------------------------------------------------------------------------
16 -- Aggregates and store procedures
17 --------------------------------------------------------------------------------
19 -- Like MySQL GROUP_CONCAT(), this function aggregates values into a
21 CREATE AGGREGATE array_accum (
23 basetype = anyelement,
28 --------------------------------------------------------------------------------
30 --------------------------------------------------------------------------------
33 CREATE TABLE persons (
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
43 password text NOT NULL, -- Password (md5crypted)
44 verification_key text, -- Reset password key
45 verification_expires timestamp without time zone,
48 title text, -- Honorific
49 phone text, -- Telephone number
50 url text, -- Home page
51 bio text, -- Biography
54 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
55 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
58 --------------------------------------------------------------------------------
60 --------------------------------------------------------------------------------
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
73 -- XXX Sites should have an address
74 -- address_id REFERENCES addresses,
82 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
83 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
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)
94 -- Ordered by primary site first
95 CREATE VIEW person_site_ordered AS
96 SELECT person_id, site_id
98 ORDER BY is_primary DESC;
100 -- Sites that each person is a member of
101 CREATE VIEW person_sites AS
103 array_to_string(array_accum(site_id), ',') AS site_ids
104 FROM person_site_ordered
107 -- Accounts at each site
108 CREATE VIEW site_persons AS
110 array_to_string(array_accum(person_id), ',') AS person_ids
114 --------------------------------------------------------------------------------
116 --------------------------------------------------------------------------------
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
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');
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
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)
146 -- Types of each address
147 CREATE VIEW address_address_types AS
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)
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)
161 -- Account mailing addresses
162 CREATE VIEW person_addresses AS
164 array_to_string(array_accum(address_id), ',') AS address_ids
168 --------------------------------------------------------------------------------
169 -- Authentication Keys
170 --------------------------------------------------------------------------------
173 CREATE TABLE key_types (
174 key_type text PRIMARY KEY -- Key type
176 INSERT INTO key_types (key_type) VALUES ('ssh');
178 -- Authentication 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
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)
194 CREATE VIEW person_keys AS
196 array_to_string(array_accum(key_id), ',') AS key_ids
200 --------------------------------------------------------------------------------
202 --------------------------------------------------------------------------------
204 -- Valid account roles
206 role_id integer PRIMARY KEY, -- Role identifier
207 name text UNIQUE NOT NULL -- Role symbolic name
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');
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)
223 CREATE VIEW person_roles AS
225 array_to_string(array_accum(role_id), ',') AS role_ids,
226 array_to_string(array_accum(roles.name), ',') AS roles
228 LEFT JOIN roles USING (role_id)
231 --------------------------------------------------------------------------------
233 --------------------------------------------------------------------------------
235 -- Valid node boot states
236 CREATE TABLE boot_states (
237 boot_state text PRIMARY KEY
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');
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
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
265 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
266 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
269 -- Nodes at each site
270 CREATE VIEW site_nodes AS
272 array_to_string(array_accum(node_id), ',') AS node_ids
276 --------------------------------------------------------------------------------
278 --------------------------------------------------------------------------------
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
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)
294 -- Nodes in each node gruop
295 CREATE VIEW nodegroup_nodes AS
297 array_to_string(array_accum(node_id), ',') AS node_ids
299 GROUP BY nodegroup_id;
301 -- Node groups that each node is a member of
302 CREATE VIEW node_nodegroups AS
304 array_to_string(array_accum(nodegroup_id), ',') AS nodegroup_ids
308 --------------------------------------------------------------------------------
309 -- Node network interfaces
310 --------------------------------------------------------------------------------
312 -- Valid network addressing schemes
313 CREATE TABLE nodenetwork_types (
314 type text PRIMARY KEY -- Addressing scheme
316 INSERT INTO nodenetwork_types (type) VALUES ('ipv4');
317 INSERT INTO nodenetwork_types (type) VALUES ('ipv6');
319 -- Valid network configuration methods
320 CREATE TABLE nodenetwork_methods (
321 method text PRIMARY KEY -- Configuration method
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');
330 -- Node network interfaces
331 CREATE TABLE nodenetworks (
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
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
352 -- Network interfaces on each node
353 CREATE VIEW node_nodenetworks AS
355 array_to_string(array_accum(nodenetwork_id), ',') AS nodenetwork_ids
359 --------------------------------------------------------------------------------
361 --------------------------------------------------------------------------------
363 CREATE VIEW view_persons AS
365 person_roles.role_ids,
367 person_sites.site_ids,
368 person_addresses.address_ids,
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);
376 CREATE VIEW view_addresses AS
378 address_address_types.address_type_ids,
379 address_address_types.address_types
381 LEFT JOIN address_address_types USING (address_id);
383 CREATE VIEW view_nodes AS
385 node_nodenetworks.nodenetwork_ids,
386 node_nodegroups.nodegroup_ids
388 LEFT JOIN node_nodenetworks USING (node_id)
389 LEFT JOIN node_nodegroups USING (node_id);
391 CREATE VIEW view_nodegroups AS
393 nodegroup_nodes.node_ids
395 LEFT JOIN nodegroup_nodes USING (nodegroup_id);
397 CREATE VIEW view_sites AS
399 site_persons.person_ids,
402 LEFT JOIN site_persons USING (site_id)
403 LEFT JOIN site_nodes USING (site_id);
405 --------------------------------------------------------------------------------
406 -- Built-in maintenance account and default site
407 --------------------------------------------------------------------------------
410 (first_name, last_name, email, password, enabled)
412 ('Maintenance', 'Account', 'maint@planet-lab.org', 'nopass', true);
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);
420 (login_base, name, abbreviated_name, max_slices)
422 ('pl', 'PlanetLab Central', 'PLC', 100);