~larry-e-works/uci-engine/write-exitcode-to-file

-- Non Functional Test Metrics Database Schema.

 

DROP TABLE IF EXISTS data;

DROP TABLE IF EXISTS test;

DROP TABLE IF EXISTS project;

 

CREATE SEQUENCE project_id_seq;

CREATE TABLE project (

    id      INTEGER PRIMARY KEY default nextval('project_id_seq'),

    name    varchar(128) NOT NULL,

    UNIQUE (name)    

);

ALTER SEQUENCE project_id_seq owned by project.id;

CREATE INDEX project_name_index ON project(name);

 

CREATE SEQUENCE test_id_seq;

CREATE TABLE test (

    id          INTEGER PRIMARY KEY default nextval('test_id_seq'),

    name        varchar(128) NOT NULL,

    project_id  integer,

    FOREIGN KEY (project_id) REFERENCES project(id),

    UNIQUE (project_id, name)

);

ALTER SEQUENCE test_id_seq owned by test.id;

CREATE INDEX test_name_index ON test(name);

 

-- The client table stores secret resource owner keys, as well as public

-- client and resource owner keys. We also store some information about

-- the client.

-- The 'valid' column defaults to true. We only accept connections from 

-- clients where this is set to 'true' and we set it to 'false' when 

-- invalidating client keys. We do this rather than deleting the key

-- because we still want to retain historical information about which

-- data points were inserted by which client.

CREATE SEQUENCE client_id_seq;

CREATE TABLE client (

    id                      INTEGER PRIMARY KEY default nextval('client_id_seq'),

    access_key              VARCHAR(30) NOT NULL,

    name                    TEXT,

    description             TEXT,

    point_of_contact        TEXT,

    resource_owner_key      VARCHAR(30) NOT NULL,

    resource_owner_secret   VARCHAR(30) NOT NULL,

    valid                   BOOLEAN default true,

    UNIQUE (access_key)

);

ALTER SEQUENCE client_id_seq owned by client.id;

-- we'll often want to look up a specific client access key, so let's index that:

CREATE INDEX client_access_key_index ON client(access_key);

-- We need to have a dummy client row added so the auth process is constant time:

-- Note that the resource owner key and secret need to be valid values, but can 

-- never be authenticated against.

INSERT INTO client (access_key, name, resource_owner_key, resource_owner_secret, valid) VALUES (

    'dummy-client-access-token', 

    'Dummy client', 

    'O9MFI1GUCrzBU6Uv1vshLX5wvhjDji', 

    '{wtgn,V>$#bn1fwqbe&,}/l1STF<@Y',

    false

);

-- We also add a client that's invalid (can never be authenticated) that's used 

-- for all the pre-populated data in the system.

INSERT INTO client (access_key, name, description, resource_owner_key, resource_owner_secret, valid) VALUES (

    'sys-prepopulated-data-client', 

    'System Client', 

    'This is not a real client - it is used to link to pre-populated data.',

    'Bad resource owner key', 

    'Bad resource owner secret',

    false

);

 

CREATE SEQUENCE data_id_seq;

CREATE TABLE data (

    id              INTEGER PRIMARY KEY default nextval('data_id_seq'),

    date_entered    timestamp with time zone NOT NULL,

    data            json NOT NULL,

    test_id         integer,

    client_id       integer,

    FOREIGN KEY (test_id) REFERENCES test(id),

    FOREIGN KEY (client_id) REFERENCES client(id)

);

ALTER SEQUENCE data_id_seq owned by data.id;

-- We will frequently be looking for data between two date ranges. Index the

-- date_entered column so this is efficient:

CREATE INDEX data_date_entered_index ON data(date_entered);

 

 

-- We need to store nonce values in the database. The nonce and timestamp are

-- stored with the access key and the resource owner key.

CREATE TABLE nonce (

    access_key          VARCHAR(30) NOT NULL,

    timestamp           timestamp without time zone NOT NULL,

    nonce               TEXT NOT NULL,

    resource_owner_key  TEXT

);

-- we'll always be searching for entries in all columns, so index that:

CREATE UNIQUE INDEX nonce_all_index ON nonce(access_key, timestamp, nonce, resource_owner_key);

-- We'll also be cleaning this table out periodically by sorting on the timestamp column:

CREATE INDEX nonce_timestamp_index ON nonce(timestamp);