sfa/storage/migrations/002-up-keys-roles.sql

   1 ----
   2 -- the purpose of this migration is to enrich the proper SFA table
   3 -- so that the registry can perform reasonably in standalone mode,
   4 -- i.e. without any underlying myplc
   5 -- this is desirable in the perspective of providing a generic framework
   6 -- for non myplc-based testbeds
   7 -- prior to this change, the registry needed to inspect the myplc db in order
   8 -- to retrieve keys and roles, so as to be able to make the right decisions in
   9 -- terms of delivering credentials
  10 ----
  11
  12 --------------------------------------------------------------------------------
  13 -- Authentication Keys
  14 --------------------------------------------------------------------------------
  15 -- Valid key types
  16 CREATE TABLE key_types (
  17     key_type text PRIMARY KEY
  18 ) WITH OIDS;
  19 INSERT INTO key_types (key_type) VALUES ('ssh');
  20
  21 -- Authentication keys
  22 CREATE TABLE keys (
  23     key_id serial PRIMARY KEY,
  24     key_type text REFERENCES key_types NOT NULL,
  25     key text NOT NULL
  26 ) WITH OIDS;
  27
  28 -- attaching keys to records
  29 CREATE TABLE record_key (
  30     record_id integer REFERENCES records NOT NULL,
  31     key_id integer REFERENCES keys PRIMARY KEY
  32 ) WITH OIDS;
  33 CREATE INDEX record_key_record_id_idx ON record_key (record_id);
  34
  35 -- get all keys attached to one record
  36 CREATE OR REPLACE VIEW record_keys AS
  37 SELECT record_id,
  38 array_accum(key_id) AS key_ids
  39 FROM record_key
  40 GROUP BY record_id;
  41
  42 -- a synthesis view for records
  43 CREATE OR REPLACE VIEW view_records AS
  44 SELECT
  45 records.record_id,
  46 records.hrn,
  47 records.authority,
  48 records.peer_authority,
  49 records.gid,
  50 records.type,
  51 records.pointer,
  52 records.date_created,
  53 records.last_updated,
  54 COALESCE((SELECT key_ids FROM record_keys WHERE record_keys.record_id = records.record_id), '{}') AS key_ids
  55 FROM records;
  56
  57 ------------------------------------------------------------
  58 UPDATE sfa_db_version SET subversion = 2;