--
-- Copyright (C) 2006 The Trustees of Princeton University
--
--- $Id: planetlab4.sql,v 1.51 2006/11/28 22:00:14 tmack Exp $
+-- $Id: planetlab4.sql,v 1.52 2006/11/29 17:57:27 tmack Exp $
--
--------------------------------------------------------------------------------
peer_url text NOT NULL, -- the url of that peer's API
-- oops, looks like we have a dependency loop here
--person_id integer REFERENCES persons NOT NULL, -- the account we use for logging in
- person_id integer NOT NULL, -- the account we use for logging in
+ auth_person_id integer NOT NULL, -- the account we use for logging in
deleted boolean NOT NULL DEFAULT false
) WITH OIDS;
model text, -- Hardware make and model
boot_nonce text, -- Random nonce updated by Boot Manager
version text, -- Boot CD version string updated by Boot Manager
- -- XXX Should be key_id integer REFERENCES keys
ssh_rsa_key text, -- SSH host key updated by Boot Manager
key text, -- Node key generated by API when configuration file is downloaded
LEFT JOIN person_keys USING (person_id)
LEFT JOIN person_slices USING (person_id);
--- Nodes at each peer
+-- Objects at each peer
+CREATE VIEW peer_sites AS
+SELECT peer_id,
+array_accum(site_id) AS site_ids
+FROM sites
+GROUP BY peer_id;
+
+CREATE VIEW peer_persons AS
+SELECT peer_id,
+array_accum(person_id) AS person_ids
+FROM persons
+GROUP BY peer_id;
+
CREATE VIEW peer_nodes AS
SELECT peer_id,
array_accum(node_id) AS node_ids
CREATE VIEW view_peers AS
SELECT
peers.*,
+peer_sites.site_ids,
+peer_persons.person_ids,
peer_nodes.node_ids,
peer_slices.slice_ids
FROM peers
+LEFT JOIN peer_sites USING (peer_id)
+LEFT JOIN peer_persons USING (peer_id)
LEFT JOIN peer_nodes USING (peer_id)
LEFT JOIN peer_slices USING (peer_id);