From 53d88e656dde889ff01d5c36c92473a05514b9b3 Mon Sep 17 00:00:00 2001 From: Tony Mack Date: Fri, 10 Oct 2008 19:04:19 +0000 Subject: [PATCH] starting new development to support slice conf files --- LICENSE | 31 ++ Makefile | 143 ++++++ Server.py | 105 ++++ Test.py | 281 ++++++++++ planetlab4.sql | 1153 ++++++++++++++++++++++++++++++++++++++++++ psycopg2/AUTHORS | 8 + psycopg2/LICENSE | 60 +++ psycopg2/MANIFEST.in | 12 + refresh-peer.py | 43 ++ 9 files changed, 1836 insertions(+) create mode 100644 LICENSE create mode 100644 Makefile create mode 100755 Server.py create mode 100755 Test.py create mode 100644 planetlab4.sql create mode 100644 psycopg2/AUTHORS create mode 100644 psycopg2/LICENSE create mode 100644 psycopg2/MANIFEST.in create mode 100644 refresh-peer.py diff --git a/LICENSE b/LICENSE new file mode 100644 index 00000000..4a80642d --- /dev/null +++ b/LICENSE @@ -0,0 +1,31 @@ +Copyright 2008 Princeton University + +Redistribution and use in source and binary forms, with or without +modification, are permitted provided that the following conditions are +met: + +* Redistributions of source code must retain the above + copyright notice, this list of conditions and the + following disclaimer. + +* Redistributions in binary form must reproduce the above + copyright notice, this list of conditions and the + following disclaimer in the documentation and/or other + materials provided with the distribution. + +* Neither the name of the copyright holder nor the names of + its contributors may be used to endorse or promote + products derived from this software without specific + prior written permission. + +THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS +IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED +TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A +PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL PRINCETON +UNIVERSITY OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, +INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT +NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF +USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON +ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF +THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. diff --git a/Makefile b/Makefile new file mode 100644 index 00000000..bc060982 --- /dev/null +++ b/Makefile @@ -0,0 +1,143 @@ +# +# (Re)builds Python metafiles (__init__.py) and documentation +# +# Mark Huang +# Copyright (C) 2005 The Trustees of Princeton University +# +# $Id: Makefile 7100 2007-11-26 12:48:14Z thierry $ +# + +# Metafiles +init := PLC/__init__.py PLC/Methods/__init__.py + +# Python modules +# see PLCAPI.spec for the settings of modules +# default is : no extra module get built + +## Temporarily until we can kill the Fedora Core 2 build +#curl_vernum := $(shell printf %d 0x$(shell curl-config --vernum)) +#pycurl_vernum := $(shell printf %d 0x070d01) # 7.13.1 +#pycurl_incompatnum := $(shell printf %d 0x071000) # 7.16.0 +#ifeq ($(shell test $(curl_vernum) -ge $(pycurl_vernum) && echo 1),1) +#ifeq ($(shell test $(curl_vernum) -ge $(pycurl_incompatnum) && echo 0),1) +#modules += pycurl +#endif +#endif + +modules-install := $(foreach module, $(modules), $(module)-install) +modules-clean := $(foreach module, $(modules), $(module)-clean) + +# Other stuff +subdirs := doc php php/xmlrpc + +# autoconf compatible variables +DESTDIR := /plc/root +datadir := /usr/share +bindir := /usr/bin + +PWD := $(shell pwd) + +all: $(init) $(subdirs) $(modules) + python setup.py build + +install: $(modules-install) + python setup.py install \ + --install-purelib=$(DESTDIR)/$(datadir)/plc_api \ + --install-scripts=$(DESTDIR)/$(datadir)/plc_api \ + --install-data=$(DESTDIR)/$(datadir)/plc_api + install -D -m 755 php/xmlrpc/xmlrpc.so $(DESTDIR)/$(shell php-config --extension-dir)/xmlrpc.so + install -D -m 755 refresh-peer.py $(DESTDIR)/$(bindir)/refresh-peer.py + +$(subdirs): $(init) $(modules) + +$(subdirs): %: + $(MAKE) -C $@ + +$(modules): + # Install in the current directory so that we can import it while developing + cd $@ && \ + python setup.py build && \ + python setup.py install_lib --install-dir=$(PWD) + +$(modules-install): %-install: + cd $* && \ + python setup.py install_lib --install-dir=$(DESTDIR)/$(datadir)/plc_api + +$(modules-clean): %-clean: + cd $* && python setup.py clean && rm -rf build + +clean: $(modules-clean) + find . -name '*.pyc' | xargs rm -f + rm -f $(INIT) + for dir in $(SUBDIRS) ; do $(MAKE) -C $$dir clean ; done + python setup.py clean && rm -rf build + +index: $(init) + +index-clean: + rm $(init) + +tags: + find . '(' -name '*.py' -o -name '*.sql' -o -name '*.php' -o -name Makefile ')' | xargs etags + +########## make sync PLCHOST=hostname +ifdef PLCHOST +PLCSSH:=root@$(PLCHOST) +endif + +LOCAL_RSYNC_EXCLUDES := --exclude '*.pyc' +RSYNC_EXCLUDES := --exclude .svn --exclude CVS --exclude '*~' --exclude TAGS $(LOCAL_RSYNC_EXCLUDES) +RSYNC_COND_DRY_RUN := $(if $(findstring n,$(MAKEFLAGS)),--dry-run,) +RSYNC := rsync -a -v $(RSYNC_COND_DRY_RUN) $(RSYNC_EXCLUDES) + +sync: +ifeq (,$(PLCSSH)) + echo "sync: You must define target host as PLCHOST on the command line" + echo " e.g. make sync PLCHOST=private.one-lab.org" ; exit 1 +else + +$(RSYNC) PLC planetlab4.sql migrations $(PLCSSH):/plc/root/usr/share/plc_api/ + ssh $(PLCSSH) chroot /plc/root apachectl graceful +endif + +#################### +# All .py files in PLC/ + +# the current content of __init__.py +PLC_now := $(sort $(shell fgrep -v '"' PLC/__init__.py 2>/dev/null)) +# what should be declared +PLC_paths := $(filter-out %/__init__.py, $(wildcard PLC/*.py)) +PLC_files := $(sort $(notdir $(PLC_paths:.py=))) + +ifneq ($(PLC_now),$(PLC_files)) +PLC/__init__.py: force +endif +PLC/__init__.py: + (echo 'all = """' ; cd PLC; ls -1 *.py | grep -v __init__ | sed -e 's,.py$$,,' ; echo '""".split()') > $@ + + +# the current content of __init__.py +METHODS_now := $(sort $(shell fgrep -v '"' PLC/Methods/__init__.py 2>/dev/null)) +# what should be declared +METHODS_paths := $(filter-out %/__init__.py, $(wildcard PLC/Methods/*.py PLC/Methods/system/*.py)) +METHODS_files := $(sort $(notdir $(subst system/, system., $(METHODS_paths:.py=)))) + +ifneq ($(METHODS_now),$(METHODS_files)) +PLC/Methods/__init__.py: force +endif +PLC/Methods/__init__.py: + (echo 'methods = """' ; cd PLC/Methods; ls -1 *.py system/*.py | grep -v __init__ | sed -e 's,.py$$,,' -e 's,system/,system.,' ; echo '""".split()') > $@ + +force: + +.PHONY: all install force clean index tags $(subdirs) $(modules) + +#################### convenience, for debugging only +# make +foo : prints the value of $(foo) +# make ++foo : idem but verbose, i.e. foo=$(foo) +++%: varname=$(subst +,,$@) +++%: + @echo "$(varname)=$($(varname))" ++%: varname=$(subst +,,$@) ++%: + @echo "$($(varname))" + diff --git a/Server.py b/Server.py new file mode 100755 index 00000000..e64996a1 --- /dev/null +++ b/Server.py @@ -0,0 +1,105 @@ +#!/usr/bin/python +# +# Simple standalone HTTP server for testing PLCAPI +# +# Mark Huang +# Copyright (C) 2006 The Trustees of Princeton University +# +# $Id: Server.py 5574 2007-10-25 20:33:17Z thierry $ +# + +import os +import sys +import getopt +import traceback +import BaseHTTPServer + +# Append PLC to the system path +sys.path.append(os.path.dirname(os.path.realpath(sys.argv[0]))) + +from PLC.API import PLCAPI + +class PLCAPIRequestHandler(BaseHTTPServer.BaseHTTPRequestHandler): + """ + Simple standalone HTTP request handler for testing PLCAPI. + """ + + def do_POST(self): + try: + # Read request + request = self.rfile.read(int(self.headers["Content-length"])) + + # Handle request + response = self.server.api.handle(self.client_address, request) + + # Write response + self.send_response(200) + self.send_header("Content-type", "text/xml") + self.send_header("Content-length", str(len(response))) + self.end_headers() + self.wfile.write(response) + + self.wfile.flush() + self.connection.shutdown(1) + + except Exception, e: + # Log error + sys.stderr.write(traceback.format_exc()) + sys.stderr.flush() + + def do_GET(self): + self.send_response(200) + self.send_header("Content-type", 'text/html') + self.end_headers() + self.wfile.write(""" + +PLCAPI XML-RPC/SOAP Interface + +

PLCAPI XML-RPC/SOAP Interface

+

Please use XML-RPC or SOAP to access the PLCAPI.

+ +""") + +class PLCAPIServer(BaseHTTPServer.HTTPServer): + """ + Simple standalone HTTP server for testing PLCAPI. + """ + + def __init__(self, addr, config): + self.api = PLCAPI(config) + self.allow_reuse_address = 1 + BaseHTTPServer.HTTPServer.__init__(self, addr, PLCAPIRequestHandler) + +# Defaults +addr = "0.0.0.0" +port = 8000 +config = "/etc/planetlab/plc_config" + +def usage(): + print "Usage: %s [OPTION]..." % sys.argv[0] + print "Options:" + print " -p PORT, --port=PORT TCP port number to listen on (default: %d)" % port + print " -f FILE, --config=FILE PLC configuration file (default: %s)" % config + print " -h, --help This message" + sys.exit(1) + +# Get options +try: + (opts, argv) = getopt.getopt(sys.argv[1:], "p:f:h", ["port=", "config=", "help"]) +except getopt.GetoptError, err: + print "Error: " + err.msg + usage() + +for (opt, optval) in opts: + if opt == "-p" or opt == "--port": + try: + port = int(optval) + except ValueError: + usage() + elif opt == "-f" or opt == "--config": + config = optval + elif opt == "-h" or opt == "--help": + usage() + +# Start server +PLCAPIServer((addr, port), config).serve_forever() diff --git a/Test.py b/Test.py new file mode 100755 index 00000000..abcc72b3 --- /dev/null +++ b/Test.py @@ -0,0 +1,281 @@ +#!/usr/bin/python +# +# Test script for peer caching +# +# Mark Huang +# Copyright (C) 2006 The Trustees of Princeton University +# +# $Id: Test.py 5574 2007-10-25 20:33:17Z thierry $ +# + +""" +Test script for peer caching. Intended for testing multiple PLCs +running on the same machine in different chroots. Here is how I set +things up after installing and configuring MyPLC: + +# Shut down MyPLC +service plc stop + +# Copy to /plc2 +cp -ra /plc /plc2 +ln -sf plc /etc/init.d/plc2 +echo 'PLC_ROOT=/plc2/root' > /etc/sysconfig/plc2 +echo 'PLC_DATA=/plc2/data' >> /etc/sysconfig/plc2 + +# Edit /plc2/data/etc/planetlab/plc_config.xml and change at least the +# following so that they do not conflict with the defaults: +# +# PLC_NAME (e.g., PlanetLab Two) +# PLC_SLICE_PREFIX (e.g., two) +# PLC_ROOT_USER (e.g., root@planetlab.two) +# PLC_API_MAINTENANCE_USER (e.g., maint@planetlab.two) +# PLC_DB_PORT (e.g., 5433) +# PLC_WWW_PORT (e.g., 81) +# PLC_WWW_SSL_PORT (e.g., 444) +# PLC_API_PORT (must be the same as PLC_WWW_SSL_PORT, e.g., 444) +# PLC_BOOT_SSL_PORT (must be the same as PLC_WWW_SSL_PORT, e.g., 444) +# PLC_BOOT_PORT (may be the same as PLC_WWW_PORT, e.g., 81) + +# Start up both MyPLC instances +service plc start +service plc2 start + +# Run test +./Test.py -f /etc/planetlab/plc_config -f /plc2/data/etc/planetlab/plc_config + +# If the test fails and your databases are corrupt and/or you want to +# start over, you can always just blow the databases away. +service plc stop +rm -rf /plc/data/var/lib/pgsql/data +service plc start + +service plc2 stop +rm -rf /plc2/data/var/lib/pgsql/data +service plc2 start +""" + +import re +from optparse import OptionParser + +from PLC.Config import Config +from PLC.GPG import gpg_export +from PLC.Shell import Shell +from PLC.Test import Test + +def todict(list_of_dicts, key): + """ + Turn a list of dicts into a dict keyed on key. + """ + + return dict([(d[key], d) for d in list_of_dicts]) + +def RefreshPeers(plcs): + """ + Refresh each peer with each other. + """ + + for plc in plcs: + for peer in plcs: + if peer == plc: + continue + + print plc.config.PLC_NAME, "refreshing", peer.config.PLC_NAME + plc.RefreshPeer(peer.config.PLC_NAME) + + peer_id = plc.GetPeers([peer.config.PLC_NAME])[0]['peer_id'] + + peer_sites = todict(plc.GetSites({'peer_id': peer_id}), 'site_id') + sites_at_peer = todict(peer.GetSites(), 'site_id') + + peer_keys = todict(plc.GetKeys({'peer_id': peer_id}), 'key_id') + keys_at_peer = todict(peer.GetKeys(), 'key_id') + + peer_persons = todict(plc.GetPersons({'peer_id': peer_id}), 'person_id') + persons_at_peer = todict(peer.GetPersons(), 'person_id') + + peer_nodes = todict(plc.GetNodes({'peer_id': peer_id}), 'node_id') + nodes_at_peer = todict(peer.GetNodes(), 'node_id') + + our_nodes = todict(plc.GetNodes({'peer_id': None}), 'node_id') + our_peer_id_at_peer = peer.GetPeers([plc.config.PLC_NAME])[0]['peer_id'] + our_nodes_at_peer = todict(peer.GetNodes({'peer_id': our_peer_id_at_peer, + 'peer_node_id': our_nodes.keys()}), 'peer_node_id') + + peer_slices = todict(plc.GetSlices({'peer_id': peer_id}), 'peer_slice_id') + slices_at_peer = todict(peer.GetSlices(), 'slice_id') + + for site_id, site in peer_sites.iteritems(): + # Verify that this site exists at the peer + peer_site_id = site['peer_site_id'] + assert peer_site_id in sites_at_peer + peer_site = sites_at_peer[peer_site_id] + + # And is the same + for field in ['name', 'abbreviated_name', 'login_base', 'is_public', + 'latitude', 'longitude', 'url', + 'max_slices', 'max_slivers',]: + assert site[field] == peer_site[field] + + for key_id, key in peer_keys.iteritems(): + # Verify that this key exists at the peer + peer_key_id = key['peer_key_id'] + assert peer_key_id in keys_at_peer + peer_key = keys_at_peer[peer_key_id] + + # And is the same + for field in ['key_type', 'key']: + assert key[field] == peer_key[field] + + for person_id, person in peer_persons.iteritems(): + # Verify that this user exists at the peer + peer_person_id = person['peer_person_id'] + assert peer_person_id in persons_at_peer + peer_person = persons_at_peer[peer_person_id] + + # And is the same + for field in ['first_name', 'last_name', 'title', 'email', 'phone', + 'url', 'bio', 'enabled']: + assert person[field] == peer_person[field] + + for key_id in person['key_ids']: + # Verify that the user is not associated with any local keys + assert key_id in peer_keys + key = peer_keys[key_id] + peer_key_id = key['peer_key_id'] + + # Verify that this key exists at the peer + assert peer_key_id in keys_at_peer + peer_key = keys_at_peer[peer_key_id] + + # And is related to the same user at the peer + assert peer_key['key_id'] in peer_person['key_ids'] + + for node_id, node in peer_nodes.iteritems(): + # Verify that this node exists at the peer + peer_node_id = node['peer_node_id'] + assert peer_node_id in nodes_at_peer + peer_node = nodes_at_peer[peer_node_id] + + # And is the same + for field in ['boot_state', 'ssh_rsa_key', 'hostname', + 'version', 'model']: + assert node[field] == peer_node[field] + + # Verify that the node is not associated with any local sites + assert node['site_id'] in peer_sites + site = peer_sites[node['site_id']] + + # Verify that this site exists at the peer + peer_site_id = site['peer_site_id'] + assert peer_site_id in sites_at_peer + peer_site = sites_at_peer[peer_site_id] + + # And is related to the same node at the peer + assert peer_site['site_id'] == peer_node['site_id'] + + for slice_id, slice in peer_slices.iteritems(): + # Verify that this slice exists at the peer + peer_slice_id = slice['peer_slice_id'] + assert peer_slice_id in slices_at_peer + peer_slice = slices_at_peer[peer_slice_id] + + # And is the same + for field in ['name', 'instantiation', 'url', 'description', + 'max_nodes', 'expires']: + assert slice[field] == peer_slice[field] + + for node_id in slice['node_ids']: + # Verify that the slice is associated only with + # the peer's own nodes, or with our nodes as + # last cached by the peer. + assert node_id in peer_nodes or node_id in our_nodes_at_peer + if node_id in peer_nodes: + node = peer_nodes[node_id] + peer_node_id = node['peer_node_id'] + elif node_id in our_nodes_at_peer: + peer_node = our_nodes_at_peer[node_id] + peer_node_id = peer_node['node_id'] + + # Verify that this node exists at the peer + assert peer_node_id in nodes_at_peer + + # And is related to the same slice at the peer + assert peer_node_id in peer_slice['node_ids'] + +def TestPeers(plcs, check = True, verbose = True, tiny = False): + # Register each peer with each other + for plc in plcs: + for peer in plcs: + if peer == plc: + continue + + key = gpg_export(peer.chroot + peer.config.PLC_ROOT_GPG_KEY_PUB) + cacert = file(peer.chroot + peer.config.PLC_API_CA_SSL_CRT).read() + + if plc.GetPeers([peer.config.PLC_NAME]): + print plc.config.PLC_NAME, "updating peer", peer.config.PLC_NAME + plc.UpdatePeer(peer.config.PLC_NAME, + {'peer_url': peer.url, 'key': key, 'cacert': cacert}) + else: + print plc.config.PLC_NAME, "adding peer", peer.config.PLC_NAME + plc.AddPeer({'peername': peer.config.PLC_NAME, + 'peer_url': peer.url, 'key': key, 'cacert': cacert}) + + # Populate the DB + plc.test = Test(api = plc, check = check, verbose = verbose) + + if tiny: + params = Test.tiny + else: + params = Test.default + + print "Populating", plc.config.PLC_NAME + plc.test.Add(**params) + + # Refresh each other + RefreshPeers(plcs) + + # Change some things + for plc in plcs: + print "Updating", plc.config.PLC_NAME + plc.test.Update() + + # Refresh each other again + RefreshPeers(plcs) + +def main(): + parser = OptionParser() + parser.add_option("-f", "--config", dest = "configs", action = "append", default = [], help = "Configuration file (default: %default)") + parser.add_option("-c", "--check", action = "store_true", default = False, help = "Verify actions (default: %default)") + parser.add_option("-q", "--quiet", action = "store_true", default = False, help = "Be quiet (default: %default)") + parser.add_option("-t", "--tiny", action = "store_true", default = False, help = "Run a tiny test (default: %default)") + (options, args) = parser.parse_args() + + # Test single peer by default + if not options.configs: + options.configs = ["/etc/planetlab/plc_config"] + + plcs = [] + for path in options.configs: + # Load configuration file + config = Config(path) + + # Determine path to chroot + m = re.match(r'(.*)/etc/planetlab', path) + if m is not None: + chroot = m.group(1) + else: + chroot = "" + + # Fix up path to SSL certificate + cacert = chroot + config.PLC_API_CA_SSL_CRT + + # Always connect with XML-RPC + plc = Shell(config = path, cacert = cacert, xmlrpc = True) + plc.chroot = chroot + plcs.append(plc) + + TestPeers(plcs, check = options.check, verbose = not options.quiet, tiny = options.tiny) + +if __name__ == "__main__": + main() diff --git a/planetlab4.sql b/planetlab4.sql new file mode 100644 index 00000000..b5ffb1b3 --- /dev/null +++ b/planetlab4.sql @@ -0,0 +1,1153 @@ +-- +-- PlanetLab Central database schema +-- Version 4, PostgreSQL +-- +-- Aaron Klingaman +-- Reid Moran +-- Mark Huang +-- Tony Mack +-- +-- Copyright (C) 2006 The Trustees of Princeton University +-- +-- $Id: planetlab4.sql 8984 2008-04-09 17:48:33Z rmoran $ +-- + +SET client_encoding = 'UNICODE'; + +-------------------------------------------------------------------------------- +-- Aggregates and store procedures +-------------------------------------------------------------------------------- + +-- Like MySQL GROUP_CONCAT(), this function aggregates values into a +-- PostgreSQL array. +CREATE AGGREGATE array_accum ( + sfunc = array_append, + basetype = anyelement, + stype = anyarray, + initcond = '{}' +); + +-------------------------------------------------------------------------------- +-- Version +-------------------------------------------------------------------------------- + +-- Database version +CREATE TABLE plc_db_version ( + version integer NOT NULL, + subversion integer NOT NULL DEFAULT 0 +) WITH OIDS; + +INSERT INTO plc_db_version (version, subversion) VALUES (4, 9); + +-------------------------------------------------------------------------------- +-- Accounts +-------------------------------------------------------------------------------- + +-- Accounts +CREATE TABLE persons ( + -- Mandatory + person_id serial PRIMARY KEY, -- Account identifier + email text NOT NULL, -- E-mail address + first_name text NOT NULL, -- First name + last_name text NOT NULL, -- Last name + deleted boolean NOT NULL DEFAULT false, -- Has been deleted + enabled boolean NOT NULL DEFAULT false, -- Has been disabled + + -- Password + password text NOT NULL DEFAULT 'nopass', -- Password (md5crypted) + verification_key text, -- Reset password key + verification_expires timestamp without time zone, + + -- Optional + title text, -- Honorific + phone text, -- Telephone number + url text, -- Home page + bio text, -- Biography + + -- Timestamps + date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP +) WITH OIDS; +CREATE INDEX persons_email_idx ON persons (email); + +-------------------------------------------------------------------------------- +-- Sites +-------------------------------------------------------------------------------- + +-- Sites +CREATE TABLE sites ( + -- Mandatory + site_id serial PRIMARY KEY, -- Site identifier + login_base text NOT NULL, -- Site slice prefix + name text NOT NULL, -- Site name + abbreviated_name text NOT NULL, -- Site abbreviated name + enabled boolean NOT NULL Default true, -- Is this site enabled + deleted boolean NOT NULL DEFAULT false, -- Has been deleted + is_public boolean NOT NULL DEFAULT true, -- Shows up in public lists + max_slices integer NOT NULL DEFAULT 0, -- Maximum number of slices + max_slivers integer NOT NULL DEFAULT 1000, -- Maximum number of instantiated slivers + + -- Optional + latitude real, + longitude real, + url text, + ext_consortium_id integer, -- external consortium id + + -- Timestamps + date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP +) WITH OIDS; +CREATE INDEX sites_login_base_idx ON sites (login_base); + +-- Account site membership +CREATE TABLE person_site ( + person_id integer REFERENCES persons NOT NULL, -- Account identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + is_primary boolean NOT NULL DEFAULT false, -- Is the primary site for this account + PRIMARY KEY (person_id, site_id) +); +CREATE INDEX person_site_person_id_idx ON person_site (person_id); +CREATE INDEX person_site_site_id_idx ON person_site (site_id); + +-- Ordered by primary site first +CREATE VIEW person_site_ordered AS +SELECT person_id, site_id +FROM person_site +ORDER BY is_primary DESC; + +-- Sites that each person is a member of +CREATE VIEW person_sites AS +SELECT person_id, +array_accum(site_id) AS site_ids +FROM person_site_ordered +GROUP BY person_id; + +-- Accounts at each site +CREATE VIEW site_persons AS +SELECT site_id, +array_accum(person_id) AS person_ids +FROM person_site +GROUP BY site_id; + +-------------------------------------------------------------------------------- +-- Mailing Addresses +-------------------------------------------------------------------------------- + +CREATE TABLE address_types ( + address_type_id serial PRIMARY KEY, -- Address type identifier + name text UNIQUE NOT NULL, -- Address type + description text -- Address type description +) WITH OIDS; + +INSERT INTO address_types (name) VALUES ('Personal'); +INSERT INTO address_types (name) VALUES ('Shipping'); +-- XXX Used to be Site +INSERT INTO address_types (name) VALUES ('Billing'); + +-- Mailing addresses +CREATE TABLE addresses ( + address_id serial PRIMARY KEY, -- Address identifier + line1 text NOT NULL, -- Address line 1 + line2 text, -- Address line 2 + line3 text, -- Address line 3 + city text NOT NULL, -- City + state text NOT NULL, -- State or province + postalcode text NOT NULL, -- Postal code + country text NOT NULL -- Country +) WITH OIDS; + +-- Each mailing address can be one of several types +CREATE TABLE address_address_type ( + address_id integer REFERENCES addresses NOT NULL, -- Address identifier + address_type_id integer REFERENCES address_types NOT NULL, -- Address type + PRIMARY KEY (address_id, address_type_id) +) WITH OIDS; +CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id); +CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id); + +CREATE VIEW address_address_types AS +SELECT address_id, +array_accum(address_type_id) AS address_type_ids, +array_accum(address_types.name) AS address_types +FROM address_address_type +LEFT JOIN address_types USING (address_type_id) +GROUP BY address_id; + +CREATE TABLE site_address ( + site_id integer REFERENCES sites NOT NULL, -- Site identifier + address_id integer REFERENCES addresses NOT NULL, -- Address identifier + PRIMARY KEY (site_id, address_id) +) WITH OIDS; +CREATE INDEX site_address_site_id_idx ON site_address (site_id); +CREATE INDEX site_address_address_id_idx ON site_address (address_id); + +CREATE VIEW site_addresses AS +SELECT site_id, +array_accum(address_id) AS address_ids +FROM site_address +GROUP BY site_id; + +-------------------------------------------------------------------------------- +-- Authentication Keys +-------------------------------------------------------------------------------- + +-- Valid key types +CREATE TABLE key_types ( + key_type text PRIMARY KEY -- Key type +) WITH OIDS; +INSERT INTO key_types (key_type) VALUES ('ssh'); + +-- Authentication keys +CREATE TABLE keys ( + key_id serial PRIMARY KEY, -- Key identifier + key_type text REFERENCES key_types NOT NULL, -- Key type + key text NOT NULL, -- Key material + is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted +) WITH OIDS; + +-- Account authentication key(s) +CREATE TABLE person_key ( + key_id integer REFERENCES keys PRIMARY KEY, -- Key identifier + person_id integer REFERENCES persons NOT NULL -- Account identifier +) WITH OIDS; +CREATE INDEX person_key_person_id_idx ON person_key (person_id); + +CREATE VIEW person_keys AS +SELECT person_id, +array_accum(key_id) AS key_ids +FROM person_key +GROUP BY person_id; + +-------------------------------------------------------------------------------- +-- Account roles +-------------------------------------------------------------------------------- + +-- Valid account roles +CREATE TABLE roles ( + role_id integer PRIMARY KEY, -- Role identifier + name text UNIQUE NOT NULL -- Role symbolic name +) WITH OIDS; +INSERT INTO roles (role_id, name) VALUES (10, 'admin'); +INSERT INTO roles (role_id, name) VALUES (20, 'pi'); +INSERT INTO roles (role_id, name) VALUES (30, 'user'); +INSERT INTO roles (role_id, name) VALUES (40, 'tech'); + +CREATE TABLE person_role ( + person_id integer REFERENCES persons NOT NULL, -- Account identifier + role_id integer REFERENCES roles NOT NULL, -- Role identifier + PRIMARY KEY (person_id, role_id) +) WITH OIDS; +CREATE INDEX person_role_person_id_idx ON person_role (person_id); + +-- Account roles +CREATE VIEW person_roles AS +SELECT person_id, +array_accum(role_id) AS role_ids, +array_accum(roles.name) AS roles +FROM person_role +LEFT JOIN roles USING (role_id) +GROUP BY person_id; + +-------------------------------------------------------------------------------- +-- Nodes +-------------------------------------------------------------------------------- + +-- Valid node boot states +CREATE TABLE boot_states ( + boot_state text PRIMARY KEY +) WITH OIDS; +INSERT INTO boot_states (boot_state) VALUES ('boot'); +INSERT INTO boot_states (boot_state) VALUES ('dbg'); +INSERT INTO boot_states (boot_state) VALUES ('diag'); +INSERT INTO boot_states (boot_state) VALUES ('disable'); +INSERT INTO boot_states (boot_state) VALUES ('inst'); +INSERT INTO boot_states (boot_state) VALUES ('rins'); +INSERT INTO boot_states (boot_state) VALUES ('new'); + +-- Nodes +CREATE TABLE nodes ( + -- Mandatory + node_id serial PRIMARY KEY, -- Node identifier + hostname text NOT NULL, -- Node hostname + site_id integer REFERENCES sites NOT NULL, -- At which site + + boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state + deleted boolean NOT NULL DEFAULT false, -- Is deleted + + -- Optional + 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 + ssh_rsa_key text, -- SSH host key updated by Boot Manager + key text, -- Node key generated by API when configuration file is downloaded + + -- Timestamps + date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_contact timestamp without time zone +) WITH OIDS; +CREATE INDEX nodes_hostname_idx ON nodes (hostname); +CREATE INDEX nodes_site_id_idx ON nodes (site_id); + +-- Nodes at each site +CREATE VIEW site_nodes AS +SELECT site_id, +array_accum(node_id) AS node_ids +FROM nodes +WHERE deleted IS false +GROUP BY site_id; + +-------------------------------------------------------------------------------- +-- Node groups +-------------------------------------------------------------------------------- + +-- Node groups +CREATE TABLE nodegroups ( + nodegroup_id serial PRIMARY KEY, -- Group identifier + name text UNIQUE NOT NULL, -- Group name + description text -- Group description +) WITH OIDS; + +-- Node group membership +CREATE TABLE nodegroup_node ( + nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Group identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + PRIMARY KEY (nodegroup_id, node_id) +) WITH OIDS; +CREATE INDEX nodegroup_node_nodegroup_id_idx ON nodegroup_node (nodegroup_id); +CREATE INDEX nodegroup_node_node_id_idx ON nodegroup_node (node_id); + +-- Nodes in each node group +CREATE VIEW nodegroup_nodes AS +SELECT nodegroup_id, +array_accum(node_id) AS node_ids +FROM nodegroup_node +GROUP BY nodegroup_id; + +-- Node groups that each node is a member of +CREATE VIEW node_nodegroups AS +SELECT node_id, +array_accum(nodegroup_id) AS nodegroup_ids +FROM nodegroup_node +GROUP BY node_id; + +-------------------------------------------------------------------------------- +-- Node configuration files +-------------------------------------------------------------------------------- + +CREATE TABLE conf_files ( + conf_file_id serial PRIMARY KEY, -- Configuration file identifier + enabled bool NOT NULL DEFAULT true, -- Configuration file is active + source text NOT NULL, -- Relative path on the boot server where file can be downloaded + dest text NOT NULL, -- Absolute path where file should be installed + file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions + file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner + file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner + preinstall_cmd text, -- Shell command to execute prior to installing + postinstall_cmd text, -- Shell command to execute after installing + error_cmd text, -- Shell command to execute if any error occurs + ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs + always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged +) WITH OIDS; + +CREATE TABLE conf_file_node ( + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + PRIMARY KEY (conf_file_id, node_id) +); +CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id); +CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id); + +-- Nodes linked to each configuration file +CREATE VIEW conf_file_nodes AS +SELECT conf_file_id, +array_accum(node_id) AS node_ids +FROM conf_file_node +GROUP BY conf_file_id; + +-- Configuration files linked to each node +CREATE VIEW node_conf_files AS +SELECT node_id, +array_accum(conf_file_id) AS conf_file_ids +FROM conf_file_node +GROUP BY node_id; + +CREATE TABLE conf_file_nodegroup ( + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier + PRIMARY KEY (conf_file_id, nodegroup_id) +); +CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id); +CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id); + +-- Node groups linked to each configuration file +CREATE VIEW conf_file_nodegroups AS +SELECT conf_file_id, +array_accum(nodegroup_id) AS nodegroup_ids +FROM conf_file_nodegroup +GROUP BY conf_file_id; + +-- Configuration files linked to each node group +CREATE VIEW nodegroup_conf_files AS +SELECT nodegroup_id, +array_accum(conf_file_id) AS conf_file_ids +FROM conf_file_nodegroup +GROUP BY nodegroup_id; + +-------------------------------------------------------------------------------- +-- Node network interfaces +-------------------------------------------------------------------------------- + +-- Valid network addressing schemes +CREATE TABLE network_types ( + type text PRIMARY KEY -- Addressing scheme +) WITH OIDS; +INSERT INTO network_types (type) VALUES ('ipv4'); + +-- Valid network configuration methods +CREATE TABLE network_methods ( + method text PRIMARY KEY -- Configuration method +) WITH OIDS; +INSERT INTO network_methods (method) VALUES ('static'); +INSERT INTO network_methods (method) VALUES ('dhcp'); +INSERT INTO network_methods (method) VALUES ('proxy'); +INSERT INTO network_methods (method) VALUES ('tap'); +INSERT INTO network_methods (method) VALUES ('ipmi'); +INSERT INTO network_methods (method) VALUES ('unknown'); + +-- Node network interfaces +CREATE TABLE nodenetworks ( + -- Mandatory + nodenetwork_id serial PRIMARY KEY, -- Network interface identifier + node_id integer REFERENCES nodes NOT NULL, -- Which node + is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node + type text REFERENCES network_types NOT NULL, -- Addressing scheme + method text REFERENCES network_methods NOT NULL, -- Configuration method + + -- Optional, depending on type and method + ip text, -- IP address + mac text, -- MAC address + gateway text, -- Default gateway address + network text, -- Network address + broadcast text, -- Network broadcast address + netmask text, -- Network mask + dns1 text, -- Primary DNS server + dns2 text, -- Secondary DNS server + bwlimit integer, -- Bandwidth limit in bps + hostname text -- Hostname of this interface +) WITH OIDS; +CREATE INDEX nodenetworks_node_id_idx ON nodenetworks (node_id); + +-- Ordered by primary interface first +CREATE VIEW nodenetworks_ordered AS +SELECT node_id, nodenetwork_id +FROM nodenetworks +ORDER BY is_primary DESC; + +-- Network interfaces on each node +CREATE VIEW node_nodenetworks AS +SELECT node_id, +array_accum(nodenetwork_id) AS nodenetwork_ids +FROM nodenetworks_ordered +GROUP BY node_id; + +-------------------------------------------------------------------------------- +-- Nodenetwork setting types and nodenetworks settings +-------------------------------------------------------------------------------- + +CREATE TABLE nodenetwork_setting_types ( + nodenetwork_setting_type_id serial PRIMARY KEY, + -- Setting Type Identifier + name text UNIQUE NOT NULL, -- Setting Name + description text, -- Optional Description + category text NOT NULL, -- Category, e.g. Wifi, or whatever + min_role_id integer references roles -- If set, minimal role required +) WITH OIDS; + +CREATE TABLE nodenetwork_setting ( + nodenetwork_setting_id serial PRIMARY KEY, -- Nodenetwork Setting Identifier + nodenetwork_id integer REFERENCES nodenetworks NOT NULL, + -- the nodenetwork this applies to + nodenetwork_setting_type_id integer REFERENCES nodenetwork_setting_types NOT NULL, + -- the setting type + value text +) WITH OIDS; + +CREATE OR REPLACE VIEW nodenetwork_settings AS +SELECT nodenetwork_id, +array_accum(nodenetwork_setting_id) AS nodenetwork_setting_ids +FROM nodenetwork_setting +GROUP BY nodenetwork_id; + +CREATE OR REPLACE VIEW view_nodenetwork_settings AS +SELECT +nodenetwork_setting.nodenetwork_setting_id, +nodenetwork_setting.nodenetwork_id, +nodenetwork_setting_types.nodenetwork_setting_type_id, +nodenetwork_setting_types.name, +nodenetwork_setting_types.description, +nodenetwork_setting_types.category, +nodenetwork_setting_types.min_role_id, +nodenetwork_setting.value +FROM nodenetwork_setting +INNER JOIN nodenetwork_setting_types USING (nodenetwork_setting_type_id); + +CREATE OR REPLACE VIEW view_nodenetworks AS +SELECT +nodenetworks.nodenetwork_id, +nodenetworks.node_id, +nodenetworks.is_primary, +nodenetworks.type, +nodenetworks.method, +nodenetworks.ip, +nodenetworks.mac, +nodenetworks.gateway, +nodenetworks.network, +nodenetworks.broadcast, +nodenetworks.netmask, +nodenetworks.dns1, +nodenetworks.dns2, +nodenetworks.bwlimit, +nodenetworks.hostname, +COALESCE((SELECT nodenetwork_setting_ids FROM nodenetwork_settings WHERE nodenetwork_settings.nodenetwork_id = nodenetworks.nodenetwork_id), '{}') AS nodenetwork_setting_ids +FROM nodenetworks; + +-------------------------------------------------------------------------------- +-- Power control units (PCUs) +-------------------------------------------------------------------------------- + +CREATE TABLE pcus ( + -- Mandatory + pcu_id serial PRIMARY KEY, -- PCU identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + hostname text, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU) + ip text NOT NULL, -- IP, not necessarily unique + + -- Optional + protocol text, -- Protocol, e.g. ssh or https or telnet + username text, -- Username, if applicable + "password" text, -- Password, if applicable + model text, -- Model, e.g. BayTech or iPal + notes text -- Random notes +) WITH OIDS; +CREATE INDEX pcus_site_id_idx ON pcus (site_id); + +CREATE VIEW site_pcus AS +SELECT site_id, +array_accum(pcu_id) AS pcu_ids +FROM pcus +GROUP BY site_id; + +CREATE TABLE pcu_node ( + pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + port integer NOT NULL, -- Port number + PRIMARY KEY (pcu_id, node_id), -- The same node cannot be controlled by different ports + UNIQUE (pcu_id, port) -- The same port cannot control multiple nodes +); +CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id); +CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id); + +CREATE VIEW node_pcus AS +SELECT node_id, +array_accum(pcu_id) AS pcu_ids, +array_accum(port) AS ports +FROM pcu_node +GROUP BY node_id; + +CREATE VIEW pcu_nodes AS +SELECT pcu_id, +array_accum(node_id) AS node_ids, +array_accum(port) AS ports +FROM pcu_node +GROUP BY pcu_id; + +-------------------------------------------------------------------------------- +-- Slices +-------------------------------------------------------------------------------- + +CREATE TABLE slice_instantiations ( + instantiation text PRIMARY KEY +) WITH OIDS; +INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice +INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager +INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated +INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller'); -- NM Controller + +-- Slices +CREATE TABLE slices ( + slice_id serial PRIMARY KEY, -- Slice identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier + + name text NOT NULL, -- Slice name + instantiation text REFERENCES slice_instantiations NOT NULL DEFAULT 'plc-instantiated', -- Slice state, e.g. plc-instantiated + url text, -- Project URL + description text, -- Project description + + max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice + + creator_person_id integer REFERENCES persons, -- Creator + created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Creation date + expires timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 weeks', -- Expiration date + + is_deleted boolean NOT NULL DEFAULT false +) WITH OIDS; +CREATE INDEX slices_site_id_idx ON slices (site_id); +CREATE INDEX slices_name_idx ON slices (name); + +-- Slivers +CREATE TABLE slice_node ( + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + PRIMARY KEY (slice_id, node_id) +) WITH OIDS; +CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id); +CREATE INDEX slice_node_node_id_idx ON slice_node (node_id); + +-- Synonym for slice_node +CREATE VIEW slivers AS +SELECT * FROM slice_node; + +-- Nodes in each slice +CREATE VIEW slice_nodes AS +SELECT slice_id, +array_accum(node_id) AS node_ids +FROM slice_node +GROUP BY slice_id; + +-- Slices on each node +CREATE VIEW node_slices AS +SELECT node_id, +array_accum(slice_id) AS slice_ids +FROM slice_node +GROUP BY node_id; + +-- Slices at each site +CREATE VIEW site_slices AS +SELECT site_id, +array_accum(slice_id) AS slice_ids +FROM slices +WHERE is_deleted is false +GROUP BY site_id; + +-- Slice membership +CREATE TABLE slice_person ( + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + person_id integer REFERENCES persons NOT NULL, -- Account identifier + PRIMARY KEY (slice_id, person_id) +) WITH OIDS; +CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id); +CREATE INDEX slice_person_person_id_idx ON slice_person (person_id); + +-- Members of the slice +CREATE VIEW slice_persons AS +SELECT slice_id, +array_accum(person_id) AS person_ids +FROM slice_person +GROUP BY slice_id; + +-- Slices of which each person is a member +CREATE VIEW person_slices AS +SELECT person_id, +array_accum(slice_id) AS slice_ids +FROM slice_person +GROUP BY person_id; + +-------------------------------------------------------------------------------- +-- Slice whitelist +-------------------------------------------------------------------------------- +-- slice whitelist on nodes +CREATE TABLE node_slice_whitelist ( + node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist + slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node + PRIMARY KEY (node_id, slice_id) +) WITH OIDS; +CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id); +CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id); + +-- Slices on each node +CREATE VIEW node_slices_whitelist AS +SELECT node_id, +array_accum(slice_id) AS slice_ids_whitelist +FROM node_slice_whitelist +GROUP BY node_id; + +-------------------------------------------------------------------------------- +-- Slice attributes +-------------------------------------------------------------------------------- + +-- Slice attribute types +CREATE TABLE slice_attribute_types ( + attribute_type_id serial PRIMARY KEY, -- Attribute type identifier + name text UNIQUE NOT NULL, -- Attribute name + description text, -- Attribute description + min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can set or change this attribute +) WITH OIDS; + +-- Slice/sliver attributes +CREATE TABLE slice_attribute ( + slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier + slice_id integer REFERENCES slices NOT NULL, -- Slice identifier + node_id integer REFERENCES nodes, -- Sliver attribute if set + nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set + attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier + value text +) WITH OIDS; +CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id); +CREATE INDEX slice_attribute_node_id_idx ON slice_attribute (node_id); +CREATE INDEX slice_attribute_nodegroup_id_idx ON slice_attribute (nodegroup_id); + +CREATE VIEW slice_attributes AS +SELECT slice_id, +array_accum(slice_attribute_id) AS slice_attribute_ids +FROM slice_attribute +GROUP BY slice_id; + +-------------------------------------------------------------------------------- +-- Initscripts +-------------------------------------------------------------------------------- + +-- Initscripts +CREATE TABLE initscripts ( + initscript_id serial PRIMARY KEY, -- Initscript identifier + name text NOT NULL, -- Initscript name + enabled bool NOT NULL DEFAULT true, -- Initscript is active + script text NOT NULL, -- Initscript + UNIQUE (name) +) WITH OIDS; +CREATE INDEX initscripts_name_idx ON initscripts (name); + + +-------------------------------------------------------------------------------- +-- Peers +-------------------------------------------------------------------------------- + +-- Peers +CREATE TABLE peers ( + peer_id serial PRIMARY KEY, -- Peer identifier + peername text NOT NULL, -- Peer name + peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface + cacert text, -- (SSL) Public certificate of peer API server + key text, -- (GPG) Public key used for authentication + deleted boolean NOT NULL DEFAULT false +) WITH OIDS; +CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false; + +-- Objects at each peer +CREATE TABLE peer_site ( + site_id integer REFERENCES sites PRIMARY KEY, -- Local site identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_site_id integer NOT NULL, -- Foreign site identifier at peer + UNIQUE (peer_id, peer_site_id) -- The same foreign site should not be cached twice +) WITH OIDS; +CREATE INDEX peer_site_peer_id_idx ON peers (peer_id); + +CREATE VIEW peer_sites AS +SELECT peer_id, +array_accum(site_id) AS site_ids, +array_accum(peer_site_id) AS peer_site_ids +FROM peer_site +GROUP BY peer_id; + +CREATE TABLE peer_person ( + person_id integer REFERENCES persons PRIMARY KEY, -- Local user identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_person_id integer NOT NULL, -- Foreign user identifier at peer + UNIQUE (peer_id, peer_person_id) -- The same foreign user should not be cached twice +) WITH OIDS; +CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id); + +CREATE VIEW peer_persons AS +SELECT peer_id, +array_accum(person_id) AS person_ids, +array_accum(peer_person_id) AS peer_person_ids +FROM peer_person +GROUP BY peer_id; + +CREATE TABLE peer_key ( + key_id integer REFERENCES keys PRIMARY KEY, -- Local key identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_key_id integer NOT NULL, -- Foreign key identifier at peer + UNIQUE (peer_id, peer_key_id) -- The same foreign key should not be cached twice +) WITH OIDS; +CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id); + +CREATE VIEW peer_keys AS +SELECT peer_id, +array_accum(key_id) AS key_ids, +array_accum(peer_key_id) AS peer_key_ids +FROM peer_key +GROUP BY peer_id; + +CREATE TABLE peer_node ( + node_id integer REFERENCES nodes PRIMARY KEY, -- Local node identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_node_id integer NOT NULL, -- Foreign node identifier + UNIQUE (peer_id, peer_node_id) -- The same foreign node should not be cached twice +) WITH OIDS; +CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id); + +CREATE VIEW peer_nodes AS +SELECT peer_id, +array_accum(node_id) AS node_ids, +array_accum(peer_node_id) AS peer_node_ids +FROM peer_node +GROUP BY peer_id; + +CREATE TABLE peer_slice ( + slice_id integer REFERENCES slices PRIMARY KEY, -- Local slice identifier + peer_id integer REFERENCES peers NOT NULL, -- Peer identifier + peer_slice_id integer NOT NULL, -- Slice identifier at peer + UNIQUE (peer_id, peer_slice_id) -- The same foreign slice should not be cached twice +) WITH OIDS; +CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id); + +CREATE VIEW peer_slices AS +SELECT peer_id, +array_accum(slice_id) AS slice_ids, +array_accum(peer_slice_id) AS peer_slice_ids +FROM peer_slice +GROUP BY peer_id; + +-------------------------------------------------------------------------------- +-- Authenticated sessions +-------------------------------------------------------------------------------- + +-- Authenticated sessions +CREATE TABLE sessions ( + session_id text PRIMARY KEY, -- Session identifier + expires timestamp without time zone +) WITH OIDS; + +-- People can have multiple sessions +CREATE TABLE person_session ( + person_id integer REFERENCES persons NOT NULL, -- Account identifier + session_id text REFERENCES sessions NOT NULL, -- Session identifier + PRIMARY KEY (person_id, session_id), + UNIQUE (session_id) -- Sessions are unique +) WITH OIDS; +CREATE INDEX person_session_person_id_idx ON person_session (person_id); + +-- Nodes can have only one session +CREATE TABLE node_session ( + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + session_id text REFERENCES sessions NOT NULL, -- Session identifier + UNIQUE (node_id), -- Nodes can have only one session + UNIQUE (session_id) -- Sessions are unique +) WITH OIDS; + + + +------------------------------------------------------------------------------- +-- PCU Types +------------------------------------------------------------------------------ +CREATE TABLE pcu_types ( + pcu_type_id serial PRIMARY KEY, + model text NOT NULL , -- PCU model name + name text -- Full PCU model name +) WITH OIDS; +CREATE INDEX pcu_types_model_idx ON pcu_types (model); + +CREATE TABLE pcu_protocol_type ( + pcu_protocol_type_id serial PRIMARY KEY, + pcu_type_id integer REFERENCES pcu_types NOT NULL, -- PCU type identifier + port integer NOT NULL, -- PCU port + protocol text NOT NULL, -- Protocol + supported boolean NOT NULL DEFAULT True -- Does PLC support +) WITH OIDS; +CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id); + + +CREATE OR REPLACE VIEW pcu_protocol_types AS +SELECT pcu_type_id, +array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids +FROM pcu_protocol_type +GROUP BY pcu_type_id; + +-------------------------------------------------------------------------------- +-- Message templates +-------------------------------------------------------------------------------- + +CREATE TABLE messages ( + message_id text PRIMARY KEY, -- Message name + subject text, -- Message summary + template text, -- Message template + enabled bool NOT NULL DEFAULT true -- Whether message is enabled +) WITH OIDS; + +-------------------------------------------------------------------------------- +-- Events +-------------------------------------------------------------------------------- + +-- Events +CREATE TABLE events ( + event_id serial PRIMARY KEY, -- Event identifier + person_id integer REFERENCES persons, -- Person responsible for event, if any + node_id integer REFERENCES nodes, -- Node responsible for event, if any + auth_type text, -- Type of auth used. i.e. AuthMethod + fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error + call_name text NOT NULL, -- Call responsible for this event + call text NOT NULL, -- Call responsible for this event, including parameters + message text, -- High level description of this event + runtime float DEFAULT 0, -- Event run time + time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp +) WITH OIDS; + +-- Database object(s) that may have been affected by a particular event +CREATE TABLE event_object ( + event_id integer REFERENCES events NOT NULL, -- Event identifier + object_id integer NOT NULL, -- Object identifier + object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting +) WITH OIDS; +CREATE INDEX event_object_event_id_idx ON event_object (event_id); +CREATE INDEX event_object_object_id_idx ON event_object (object_id); +CREATE INDEX event_object_object_type_idx ON event_object (object_type); + +CREATE OR REPLACE VIEW event_objects AS +SELECT event_id, +array_accum(object_id) AS object_ids, +array_accum(object_type) AS object_types +FROM event_object +GROUP BY event_id; + +-------------------------------------------------------------------------------- +-- Useful views +-------------------------------------------------------------------------------- +CREATE OR REPLACE VIEW view_pcu_types AS +SELECT +pcu_types.pcu_type_id, +pcu_types.model, +pcu_types.name, +COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids +FROM pcu_types; + +CREATE OR REPLACE VIEW view_events AS +SELECT +events.event_id, +events.person_id, +events.node_id, +events.auth_type, +events.fault_code, +events.call_name, +events.call, +events.message, +events.runtime, +CAST(date_part('epoch', events.time) AS bigint) AS time, +COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_ids, +COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types +FROM events; + +CREATE OR REPLACE VIEW view_event_objects AS +SELECT +events.event_id, +events.person_id, +events.node_id, +events.fault_code, +events.call_name, +events.call, +events.message, +events.runtime, +CAST(date_part('epoch', events.time) AS bigint) AS time, +event_object.object_id, +event_object.object_type +FROM events LEFT JOIN event_object USING (event_id); + +CREATE OR REPLACE VIEW view_persons AS +SELECT +persons.person_id, +persons.email, +persons.first_name, +persons.last_name, +persons.deleted, +persons.enabled, +persons.password, +persons.verification_key, +CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires, +persons.title, +persons.phone, +persons.url, +persons.bio, +CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated, +peer_person.peer_id, +peer_person.peer_person_id, +COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids, +COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles, +COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids, +COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids, +COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids +FROM persons +LEFT JOIN peer_person USING (person_id); + +CREATE OR REPLACE VIEW view_peers AS +SELECT +peers.*, +COALESCE((SELECT site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS site_ids, +COALESCE((SELECT peer_site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS peer_site_ids, +COALESCE((SELECT person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS person_ids, +COALESCE((SELECT peer_person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS peer_person_ids, +COALESCE((SELECT key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS key_ids, +COALESCE((SELECT peer_key_ids FROM peer_keys WHERE peer_keys.peer_id = peers.peer_id), '{}') AS peer_key_ids, +COALESCE((SELECT node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS node_ids, +COALESCE((SELECT peer_node_ids FROM peer_nodes WHERE peer_nodes.peer_id = peers.peer_id), '{}') AS peer_node_ids, +COALESCE((SELECT slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS slice_ids, +COALESCE((SELECT peer_slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS peer_slice_ids +FROM peers; + +CREATE OR REPLACE VIEW view_nodes AS +SELECT +nodes.node_id, +nodes.hostname, +nodes.site_id, +nodes.boot_state, +nodes.deleted, +nodes.model, +nodes.boot_nonce, +nodes.version, +nodes.ssh_rsa_key, +nodes.key, +CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, +CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, +peer_node.peer_id, +peer_node.peer_node_id, +COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids, +COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids, +COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids, +COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') AS slice_ids_whitelist, +COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids, +COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports, +COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids, +node_session.session_id AS session +FROM nodes +LEFT JOIN peer_node USING (node_id) +LEFT JOIN node_session USING (node_id); + +CREATE OR REPLACE VIEW view_nodegroups AS +SELECT +nodegroups.*, +COALESCE((SELECT node_ids FROM nodegroup_nodes WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') AS node_ids, +COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids +FROM nodegroups; + +CREATE OR REPLACE VIEW view_conf_files AS +SELECT +conf_files.*, +COALESCE((SELECT node_ids FROM conf_file_nodes WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids +FROM conf_files; + +CREATE OR REPLACE VIEW view_pcus AS +SELECT +pcus.*, +COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids, +COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports +FROM pcus; + +CREATE OR REPLACE VIEW view_sites AS +SELECT +sites.site_id, +sites.login_base, +sites.name, +sites.abbreviated_name, +sites.deleted, +sites.enabled, +sites.is_public, +sites.max_slices, +sites.max_slivers, +sites.latitude, +sites.longitude, +sites.url, +sites.ext_consortium_id, +CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated, +peer_site.peer_id, +peer_site.peer_site_id, +COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids, +COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids, +COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids, +COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids, +COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids +FROM sites +LEFT JOIN peer_site USING (site_id); + +CREATE OR REPLACE VIEW view_addresses AS +SELECT +addresses.*, +COALESCE((SELECT address_type_ids FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_type_ids, +COALESCE((SELECT address_types FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_types +FROM addresses; + +CREATE OR REPLACE VIEW view_keys AS +SELECT +keys.*, +person_key.person_id, +peer_key.peer_id, +peer_key.peer_key_id +FROM keys +LEFT JOIN person_key USING (key_id) +LEFT JOIN peer_key USING (key_id); + +CREATE OR REPLACE VIEW view_slices AS +SELECT +slices.slice_id, +slices.site_id, +slices.name, +slices.instantiation, +slices.url, +slices.description, +slices.max_nodes, +slices.creator_person_id, +slices.is_deleted, +CAST(date_part('epoch', slices.created) AS bigint) AS created, +CAST(date_part('epoch', slices.expires) AS bigint) AS expires, +peer_slice.peer_id, +peer_slice.peer_slice_id, +COALESCE((SELECT node_ids FROM slice_nodes WHERE slice_nodes.slice_id = slices.slice_id), '{}') AS node_ids, +COALESCE((SELECT person_ids FROM slice_persons WHERE slice_persons.slice_id = slices.slice_id), '{}') AS person_ids, +COALESCE((SELECT slice_attribute_ids FROM slice_attributes WHERE slice_attributes.slice_id = slices.slice_id), '{}') AS slice_attribute_ids +FROM slices +LEFT JOIN peer_slice USING (slice_id); + +CREATE OR REPLACE VIEW view_slice_attributes AS +SELECT +slice_attribute.slice_attribute_id, +slice_attribute.slice_id, +slice_attribute.node_id, +slice_attribute.nodegroup_id, +slice_attribute_types.attribute_type_id, +slice_attribute_types.name, +slice_attribute_types.description, +slice_attribute_types.min_role_id, +slice_attribute.value +FROM slice_attribute +INNER JOIN slice_attribute_types USING (attribute_type_id); + +CREATE OR REPLACE VIEW view_sessions AS +SELECT +sessions.session_id, +CAST(date_part('epoch', sessions.expires) AS bigint) AS expires, +person_session.person_id, +node_session.node_id +FROM sessions +LEFT JOIN person_session USING (session_id) +LEFT JOIN node_session USING (session_id); + +-------------------------------------------------------------------------------- +-- Built-in maintenance account and default site +-------------------------------------------------------------------------------- + +INSERT INTO persons +(first_name, last_name, email, password, enabled) +VALUES +('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true); + +INSERT INTO person_role (person_id, role_id) VALUES (1, 10); +INSERT INTO person_role (person_id, role_id) VALUES (1, 20); +INSERT INTO person_role (person_id, role_id) VALUES (1, 30); +INSERT INTO person_role (person_id, role_id) VALUES (1, 40); + +INSERT INTO sites +(login_base, name, abbreviated_name, max_slices) +VALUES +('pl', 'PlanetLab Central', 'PLC', 100); diff --git a/psycopg2/AUTHORS b/psycopg2/AUTHORS new file mode 100644 index 00000000..44c77fc8 --- /dev/null +++ b/psycopg2/AUTHORS @@ -0,0 +1,8 @@ +Main authors: + Federico Di Gregorio + +For the win32 port: + Jason Erickson (most of his changes are still in 2.0) + +Additional Help: + diff --git a/psycopg2/LICENSE b/psycopg2/LICENSE new file mode 100644 index 00000000..b20b2825 --- /dev/null +++ b/psycopg2/LICENSE @@ -0,0 +1,60 @@ +psycopg and the GPL +=================== + +psycopg is free software; you can redistribute it and/or modify +it under the terms of the GNU General Public License as published by +the Free Software Foundation; either version 2 of the License, or +(at your option) any later version. See file COPYING for details. + +As a special exception, specific permission is granted for the GPLed +code in this distribition to be linked to OpenSSL and PostgreSQL libpq +without invoking GPL clause 2(b). + +Note that the GPL was chosen to avoid proprietary adapters based on +psycopg code. Using psycopg in a proprietary product (even bundling +psycopg with the proprietary product) is fine as long as: + + 1. psycopg is called from Python only using only the provided API + (i.e., no linking with C code and no C modules based on it); and + + 2. all the other points of the GPL are respected (you offer a copy + of psycopg's source code, and so on.) + +Alternative licenses +==================== + +If you prefer you can use the Zope Database Adapter ZPsycopgDA (i.e., +every file inside the ZPsycopgDA directory) user the ZPL license as +published on the Zope web site, http://www.zope.org/Resources/ZPL. + +Also, the following BSD-like license applies (at your option) to the +files following the pattern psycopg/adapter*.{h,c} and +psycopg/microprotocol*.{h,c}: + + Permission is granted to anyone to use this software for any purpose, + including commercial applications, and to alter it and redistribute it + freely, subject to the following restrictions: + + 1. The origin of this software must not be misrepresented; you must not + claim that you wrote the original software. If you use this + software in a product, an acknowledgment in the product documentation + would be appreciated but is not required. + + 2. Altered source versions must be plainly marked as such, and must not + be misrepresented as being the original software. + + 3. This notice may not be removed or altered from any source distribution. + +psycopg is distributed in the hope that it will be useful, +but WITHOUT ANY WARRANTY; without even the implied warranty of +MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +GNU General Public License for more details. + +Proprietary licenses +==================== + +A non-exclusive license is available for companies that want to include +psycopg in their proprietary products without respecting the spirit of the +GPL. The price of the license is one day of development done by the author, +at the consulting fee he applies to his usual customers at the day of the +request. diff --git a/psycopg2/MANIFEST.in b/psycopg2/MANIFEST.in new file mode 100644 index 00000000..457004c5 --- /dev/null +++ b/psycopg2/MANIFEST.in @@ -0,0 +1,12 @@ +recursive-include psycopg *.c *.h +recursive-include lib *.py +recursive-include tests *.py +recursive-include ZPsycopgDA *.py *.gif *.dtml +recursive-include examples *.py somehackers.jpg whereareyou.jpg +recursive-include debian * +recursive-include doc TODO HACKING SUCCESS ChangeLog-1.x async.txt +recursive-include scripts *.py *.sh +include scripts/maketypes.sh scripts/buildtypes.py +include AUTHORS README INSTALL LICENSE ChangeLog +include PKG-INFO MANIFEST.in MANIFEST setup.py setup.cfg +recursive-include doc *.rst *.css *.html diff --git a/refresh-peer.py b/refresh-peer.py new file mode 100644 index 00000000..b340fca9 --- /dev/null +++ b/refresh-peer.py @@ -0,0 +1,43 @@ +#!/usr/bin/env plcsh +# $Id: refresh-peer.py 10030 2008-07-28 15:14:08Z thierry $ + +import sys,time,os,os.path + +logdir="/var/log/peers" + +def Run (peername): + timestring=time.strftime("%Y-%m-%d-%H-%M-%S") + print 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',peername + print 'RefreshPeer on %s - starting on %s'%(peername,timestring) + print 'xxxxxxxxxx' + sys.stdout.flush() + start=time.time() + result=RefreshPeer(peername) + finish=time.time() + + print 'Total duration',finish-start + print 'xxxxxxxxxx timers:' + keys=result.keys() + keys.sort() + for key in keys: + print key,result[key] + sys.stdout.flush() + sys.stderr.flush() + +def RunInLog (peername): + monthstring=time.strftime("%Y-%m") + if not os.path.isdir(logdir): + os.mkdir(logdir) + logname="%s/refresh-peer-%s-%s.log"%(logdir,peername,monthstring) + sys.stdout=open(logname,'a') + sys.stderr=sys.stdout + Run(peername) + sys.stderr.close() + sys.stdout.close() + +if __name__ == "__main__": + + for peername in sys.argv[1:]: + RunInLog (peername) + + -- 2.47.0