From 27a9a0c135cbdc2044dfb71bdba33cd279ad6bcc Mon Sep 17 00:00:00 2001 From: Tony Mack Date: Fri, 10 Oct 2008 19:19:05 +0000 Subject: [PATCH] starting new development to support slice conf files --- migrations/001-down-subversion.sql | 3 + migrations/001-up-subversion.sql | 5 + migrations/002-up-slices.sql | 6 + migrations/003-down-network-settings.sql | 24 +++ migrations/003-test.py | 60 +++++++ migrations/003-up-network-settings.sql | 86 ++++++++++ migrations/004-up-fix-site-nodes.sql | 16 ++ migrations/005-down-import-apr-2007.sql | 112 +++++++++++++ migrations/005-up-import-apr-2007.sql | 154 ++++++++++++++++++ .../006-down-slice-attribute-nodegroup.sql | 25 +++ .../006-up-slice-attribute-nodegroup.sql | 29 ++++ migrations/007-down-event-objects-view.sql | 11 ++ migrations/007-up-event-objects-view.sql | 26 +++ migrations/008-down-import-aug-2007.sql | 42 +++++ migrations/008-up-import-aug-2007.sql | 64 ++++++++ migrations/009-down-pcu-types.sql | 13 ++ migrations/009-up-pcu-types.sql | 41 +++++ migrations/README.txt | 8 + 18 files changed, 725 insertions(+) create mode 100644 migrations/001-down-subversion.sql create mode 100644 migrations/001-up-subversion.sql create mode 100644 migrations/002-up-slices.sql create mode 100644 migrations/003-down-network-settings.sql create mode 100755 migrations/003-test.py create mode 100644 migrations/003-up-network-settings.sql create mode 100644 migrations/004-up-fix-site-nodes.sql create mode 100644 migrations/005-down-import-apr-2007.sql create mode 100644 migrations/005-up-import-apr-2007.sql create mode 100644 migrations/006-down-slice-attribute-nodegroup.sql create mode 100644 migrations/006-up-slice-attribute-nodegroup.sql create mode 100644 migrations/007-down-event-objects-view.sql create mode 100644 migrations/007-up-event-objects-view.sql create mode 100644 migrations/008-down-import-aug-2007.sql create mode 100644 migrations/008-up-import-aug-2007.sql create mode 100644 migrations/009-down-pcu-types.sql create mode 100644 migrations/009-up-pcu-types.sql create mode 100644 migrations/README.txt diff --git a/migrations/001-down-subversion.sql b/migrations/001-down-subversion.sql new file mode 100644 index 00000000..5e3255c5 --- /dev/null +++ b/migrations/001-down-subversion.sql @@ -0,0 +1,3 @@ +-- you may also write downgrader scripts, though they are not - yet - supported + +ALTER TABLE plc_db_version DROP subversion; diff --git a/migrations/001-up-subversion.sql b/migrations/001-up-subversion.sql new file mode 100644 index 00000000..01046a5e --- /dev/null +++ b/migrations/001-up-subversion.sql @@ -0,0 +1,5 @@ +-- Add plc_db_version.subversion field +ALTER TABLE plc_db_version ADD subversion integer NOT NULL DEFAULT 0; + +-- Bump subversion +UPDATE plc_db_version SET subversion = 1; diff --git a/migrations/002-up-slices.sql b/migrations/002-up-slices.sql new file mode 100644 index 00000000..4bf656db --- /dev/null +++ b/migrations/002-up-slices.sql @@ -0,0 +1,6 @@ +-- Remove NOT NULL constraint from creator_person_id in case the +-- creator is deleted. +ALTER TABLE slices ALTER creator_person_id DROP NOT NULL; + +-- Bump subversion +UPDATE plc_db_version SET subversion = 2; diff --git a/migrations/003-down-network-settings.sql b/migrations/003-down-network-settings.sql new file mode 100644 index 00000000..796e4b4c --- /dev/null +++ b/migrations/003-down-network-settings.sql @@ -0,0 +1,24 @@ +-- IMPORTANT NOTICE +-- +-- this down script is provided for convenience only +-- DO NOT USE on an operational site +-- the change in migration 003 involves creating +-- the new view view_nodenetworks for fetching instances +-- of NodeNetworks +-- AND to alter NodeNetworks.py accordingly +-- so this change cannot be easily undone +-- unless you also revert the API itself + +DROP VIEW view_nodenetworks; + +DROP VIEW view_nodenetwork_settings; + +DROP VIEW nodenetwork_settings; + +DROP TABLE nodenetwork_setting; + +DROP TABLE nodenetwork_setting_types; + +-- deflate subversion +UPDATE plc_db_version SET subversion = 2; +SELECT subversion from plc_db_version; diff --git a/migrations/003-test.py b/migrations/003-test.py new file mode 100755 index 00000000..aed650b1 --- /dev/null +++ b/migrations/003-test.py @@ -0,0 +1,60 @@ +#!/usr/bin/env plcsh + +nnst = GetNodeNetworkSettingTypes(['interface_name']) +if nnst: + print 'NodeNetworkSettingType interface_name already defined' +else: + AddNodeNetworkSettingType({ + 'category':'general', + 'min_role_id':30, + 'name':'interface_name', + 'description':'allows to specify a custom interface name' + }) +nnst_ifname_id = GetNodeNetworkSettingTypes(['interface_name'])[0]['nodenetwork_setting_type_id'] + + +nnst = GetNodeNetworkSettingTypes(['ssid']) +if nnst: + print 'NodeNetworkSettingType ssid already defined' +else: + AddNodeNetworkSettingType({ + 'category':'wifi', + 'min_role_id':30, + 'name':'ssid', + 'description':'allows to set ESSID' + }) +nnst_ssid_id = GetNodeNetworkSettingTypes(['ssid'])[0]['nodenetwork_setting_type_id'] + +nodename = 'onelab2.inria.fr' + +nodenetwork_id=GetNodes(nodename)[0]['nodenetwork_ids'][0] + +####### +nns_ifname = GetNodeNetworkSettings ({'nodenetwork_id':nodenetwork_id, + 'nodenetwork_setting_type_id':nnst_ifname_id}) +if nns_ifname: + print "interface name for %s already set (got %s - cat=%s)" %\ + (nodename,nns_ifname[0]['value'],nns_ifname[0]['category']) +else: + AddNodeNetworkSetting(nodenetwork_id, 'interface_name', 'custom-eth0') + +nns_ifname_id = GetNodeNetworkSettings ({'nodenetwork_id':nodenetwork_id, + 'nodenetwork_setting_type_id':nnst_ifname_id})[0]['nodenetwork_setting_id'] +####### +nns_ssid = GetNodeNetworkSettings ({'nodenetwork_id':nodenetwork_id, + 'nodenetwork_setting_type_id':nnst_ssid_id}) +if nns_ssid: + print "ssid for %s already set (got %s - cat=%s)" %\ + (nodename,nns_ifname[0]['value'],nns_ifname[0]['category']) +else: + AddNodeNetworkSetting(nodenetwork_id, 'ssid', 'init-onelab-g') + +nns_ssid_id = GetNodeNetworkSettings ({'nodenetwork_id':nodenetwork_id, + 'nodenetwork_setting_type_id':nnst_ssid_id})[0]['nodenetwork_setting_id'] + +####### + +UpdateNodeNetworkSetting (nns_ssid_id,'onelab-g') + +DeleteNodeNetworkSetting (nns_ifname_id) + diff --git a/migrations/003-up-network-settings.sql b/migrations/003-up-network-settings.sql new file mode 100644 index 00000000..64b0ff6c --- /dev/null +++ b/migrations/003-up-network-settings.sql @@ -0,0 +1,86 @@ +-- +-- Thierry Parmentelat -- INRIA +-- +-- migration 003 +-- +-- purpose : provide a generic mechanism for assigning +-- nodenetworks (read, network interfaces) with +-- custom settings +-- +-- design +-- mimicks the way slice attributes are being handled, +-- not that this design is particularly attractive +-- but let's not add confusion here +-- i.e: +-- (*) nodenetwork_setting_types (see slice_attribute_types) +-- allows to define a new setting +-- e.g, define one such object for storing wifi SSID +-- +-- (*) nodenetwork_setting (see slice_attribute) +-- allow to associate a nodenetwork, a nodenetwork_setting_type, and a value +-- +-- NOTE. with slice_attributes there is a trick that allows to define +-- the attribute either on the slice globally or on a particular node only. +-- of course we do not need such a trick + +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 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; + +-- Bump subversion +UPDATE plc_db_version SET subversion = 3; +SELECT subversion from plc_db_version; diff --git a/migrations/004-up-fix-site-nodes.sql b/migrations/004-up-fix-site-nodes.sql new file mode 100644 index 00000000..8bc9c58f --- /dev/null +++ b/migrations/004-up-fix-site-nodes.sql @@ -0,0 +1,16 @@ +-- +-- bugfix +-- the site_nodes should restrict to nodes where deleted is false +-- + +CREATE OR REPLACE VIEW site_nodes AS +SELECT site_id, +array_accum(node_id) AS node_ids +FROM nodes +WHERE deleted is false +GROUP BY site_id; + +-- Bump subversion +UPDATE plc_db_version SET subversion = 4; +SELECT subversion from plc_db_version; + diff --git a/migrations/005-down-import-apr-2007.sql b/migrations/005-down-import-apr-2007.sql new file mode 100644 index 00000000..fa16b125 --- /dev/null +++ b/migrations/005-down-import-apr-2007.sql @@ -0,0 +1,112 @@ +-- revert migration 005 +-- +-- this is a rather complex example, so for next times, make sure that you +-- * first restore old columns or tables +-- * then create or replace views +-- * and only finally drop new columns and tables +-- otherwise the columns may refuse to get dropped if they are still used by views +-- + +---------- creations + +ALTER TABLE events ADD object_type text NOT NULL Default 'Unknown'; + +---------- view changes + +-- for some reason these views require to be dropped first +DROP VIEW view_events; +DROP VIEW event_objects; +DROP VIEW view_nodes; +DROP VIEW view_sites; + +CREATE OR REPLACE VIEW event_objects AS +SELECT event_id, +array_accum(object_id) AS object_ids +FROM event_object +GROUP BY event_id; + +CREATE OR REPLACE VIEW view_events AS +SELECT +events.event_id, +events.person_id, +events.node_id, +events.fault_code, +events.call_name, +events.call, +events.object_type, +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 +FROM events; + +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, +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 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_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, +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); + +---------- deletions + +ALTER TABLE sites DROP COLUMN ext_consortium_id; + +ALTER TABLE nodes DROP COLUMN last_contact; + +DROP INDEX initscripts_name_idx; +DROP TABLE initscripts; + +ALTER TABLE events DROP COLUMN auth_type; + +ALTER TABLE event_object DROP COLUMN object_type; + +---------- revert subversion + +UPDATE plc_db_version SET subversion = 4; +SELECT subversion from plc_db_version; diff --git a/migrations/005-up-import-apr-2007.sql b/migrations/005-up-import-apr-2007.sql new file mode 100644 index 00000000..3a34f25c --- /dev/null +++ b/migrations/005-up-import-apr-2007.sql @@ -0,0 +1,154 @@ +-- +-- to apply changes from import done in april 2007 from the +-- planetlab-4_0-branch tag +-- +-- this is a rather complex example, so for next times, make sure that you +-- * first add new columns and new tables +-- * then create or replace views +-- * and only finally drop columns +-- otherwise the columns may refuse to get dropped if they are still used by views +-- + +---------- creations + +ALTER TABLE sites ADD ext_consortium_id integer; + +ALTER TABLE nodes ADD last_contact timestamp without time zone; + +-- 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); + +-- rather drop the tables altogether, +-- ALTER TABLE events ADD auth_type text; +-- ALTER TABLE event_object ADD COLUMN object_type text NOT NULL Default 'Unknown'; +-- CREATE INDEX event_object_object_type_idx ON event_object (object_type); + +-- for some reason these views require to be dropped first +DROP VIEW view_events; +DROP VIEW event_objects; +DROP VIEW view_nodes; +DROP VIEW view_sites; + +----dropping tables must be preceded by dropping views using those tables +----otherwise dependency problems +DROP TABLE event_object; +DROP TABLE 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); + +---------- view changes + +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; + +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_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 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_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); + +---------- deletions +--dont need to drop this colum it doesn't exit anymore +-----ALTER TABLE events DROP COLUMN object_type; + +---------- bump subversion + +UPDATE plc_db_version SET subversion = 5; +SELECT subversion from plc_db_version; diff --git a/migrations/006-down-slice-attribute-nodegroup.sql b/migrations/006-down-slice-attribute-nodegroup.sql new file mode 100644 index 00000000..0dc4baeb --- /dev/null +++ b/migrations/006-down-slice-attribute-nodegroup.sql @@ -0,0 +1,25 @@ +---------- view changes + +DROP VIEW view_slice_attributes; + +CREATE OR REPLACE VIEW view_slice_attributes AS +SELECT +slice_attribute.slice_attribute_id, +slice_attribute.slice_id, +slice_attribute.node_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); + +---------- deletions +DROP INDEX slice_attribute_nodegroup_id_idx; +ALTER TABLE slice_attribute DROP COLUMN nodegroup_id; + +---------- revert subversion + +UPDATE plc_db_version SET subversion = 5; +SELECT subversion from plc_db_version; diff --git a/migrations/006-up-slice-attribute-nodegroup.sql b/migrations/006-up-slice-attribute-nodegroup.sql new file mode 100644 index 00000000..801f2a00 --- /dev/null +++ b/migrations/006-up-slice-attribute-nodegroup.sql @@ -0,0 +1,29 @@ +---------- creations + +ALTER TABLE slice_attribute ADD nodegroup_id integer REFERENCES nodegroups; + +CREATE INDEX slice_attribute_nodegroup_id_idx ON slice_attribute (nodegroup_id); + +---------- view changes + +DROP VIEW view_slice_attributes; + +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); + + +---------- bump subversion + +UPDATE plc_db_version SET subversion = 6; +SELECT subversion from plc_db_version; diff --git a/migrations/007-down-event-objects-view.sql b/migrations/007-down-event-objects-view.sql new file mode 100644 index 00000000..09b86f38 --- /dev/null +++ b/migrations/007-down-event-objects-view.sql @@ -0,0 +1,11 @@ +-- +-- migration 007 - revert +-- + +DROP VIEW view_event_objects; + +---------- revert subversion + +UPDATE plc_db_version SET subversion = 6; +SELECT subversion from plc_db_version; + diff --git a/migrations/007-up-event-objects-view.sql b/migrations/007-up-event-objects-view.sql new file mode 100644 index 00000000..2ed47ee9 --- /dev/null +++ b/migrations/007-up-event-objects-view.sql @@ -0,0 +1,26 @@ +-- +-- migration 007 +-- change the way event objects are fetched, use a view for that purpose +-- + + +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); + + +---------- bump subversion + +UPDATE plc_db_version SET subversion = 7; +SELECT subversion from plc_db_version; diff --git a/migrations/008-down-import-aug-2007.sql b/migrations/008-down-import-aug-2007.sql new file mode 100644 index 00000000..9a3eaab4 --- /dev/null +++ b/migrations/008-down-import-aug-2007.sql @@ -0,0 +1,42 @@ + +DELETE from slice_instantiations WHERE instantiation='nm-controller'; + + + +DROP VIEW view_nodes; +DROP VIEW node_slices_whitelist; +DROP TABLE node_slice_whitelist; + +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 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); + + +---------- revert subversion + +UPDATE plc_db_version SET subversion = 7; +SELECT subversion from plc_db_version; diff --git a/migrations/008-up-import-aug-2007.sql b/migrations/008-up-import-aug-2007.sql new file mode 100644 index 00000000..2220f8b2 --- /dev/null +++ b/migrations/008-up-import-aug-2007.sql @@ -0,0 +1,64 @@ +-- +-- migration 008 +-- import from Princeton codebase on august 2007 28 +-- slice instantiation 'nm-controller' +-- * white lists +-- + +INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller'); -- NM Controller + +-------------------------------------------------------------------------------- +-- 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; + +DROP VIEW view_nodes; + + +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); + +---------- bump subversion + +UPDATE plc_db_version SET subversion = 8; +SELECT subversion from plc_db_version; diff --git a/migrations/009-down-pcu-types.sql b/migrations/009-down-pcu-types.sql new file mode 100644 index 00000000..1dbe9d2e --- /dev/null +++ b/migrations/009-down-pcu-types.sql @@ -0,0 +1,13 @@ +-- +-- 009 revert +-- + +DROP VIEW view_pcu_types; + +DROP VIEW pcu_type_ports; + +DROP TABLE pcu_type_port; + +DROP TABLE pcu_types; + +UPDATE plc_db_version SET subversion = 7; diff --git a/migrations/009-up-pcu-types.sql b/migrations/009-up-pcu-types.sql new file mode 100644 index 00000000..c7dd7b61 --- /dev/null +++ b/migrations/009-up-pcu-types.sql @@ -0,0 +1,41 @@ +-- +-- Tony Mack - PlanetLab +-- +-- migration 009 +-- +-- purpose: provide a means for storing details about pcu models +-- +-- + +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; + +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; + +UPDATE plc_db_version SET subversion = 9; diff --git a/migrations/README.txt b/migrations/README.txt new file mode 100644 index 00000000..e8570bfc --- /dev/null +++ b/migrations/README.txt @@ -0,0 +1,8 @@ +Store here migration scripts, named +-up-any-text. +with contiguous indices starting with 001 + +if the extension is .sql it is ran on the planetlab4 database +otherwise it is run as is + +See myplc/plc.d/db for how this is used -- 2.47.0