Megatest

--  Copyright 2006-2017, Matthew Welland.
-- 
-- This file is part of Megatest.
-- 
--     Megatest 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 3 of the License, or
--     (at your option) any later version.
-- 
--     Megatest 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.
-- 
--     You should have received a copy of the GNU General Public License
--     along with Megatest.  If not, see <http://www.gnu.org/licenses/>.

-- CREATE TABLE IF NOT EXISTS keys (
--        id SERIAL PRIMARY KEY,
--        fieldname TEXT,
--        fieldtype TEXT,
--        CONSTRAINT keyconstraint UNIQUE (fieldname));
DROP VIEW IF EXISTS area_tag_view;
DROP TABLE IF EXISTS areas;
DROP TABLE IF EXISTS ttype;
DROP TABLE IF EXISTS runs;
DROP TABLE IF EXISTS run_stats;
DROP TABLE IF EXISTS test_meta;
DROP TABLE IF EXISTS tasks_queue;
DROP TABLE IF EXISTS archive_disks;
DROP TABLE IF EXISTS archive_blocks;
DROP TABLE IF EXISTS archive_allocations;
DROP TABLE IF EXISTS extradat;
DROP TABLE IF EXISTS metadat;
DROP TABLE IF EXISTS access_log;
DROP TABLE IF EXISTS tests;
DROP TABLE IF EXISTS test_steps;
DROP TABLE IF EXISTS test_data;
DROP TABLE IF EXISTS test_rundat;
DROP TABLE IF EXISTS archives;
DROP TABLE IF EXISTS session_vars;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS users; 
DROP TABLE IF EXISTS webviews;
DROP TABLE IF EXISTS area_tags;
DROP TABLE IF EXISTS run_tags;
DROP TABLE IF EXISTS users_webviews;
DROP TABLE IF EXISTS base_paths;
DROP TABLE IF EXISTS area_owners;
DROP TABLE IF EXISTS shared_user_views;
DROP TABLE IF EXISTS cctrl_info;
DROP TABLE IF EXISTS cctrl_config;
DROP TABLE IF EXISTS platforms;


CREATE TABLE IF NOT EXISTS session_vars (
       id SERIAL PRIMARY KEY,
       session_id INTEGER,
       page TEXT,
       key TEXT,
       value TEXT);

CREATE TABLE IF NOT EXISTS sessions (
       id SERIAL PRIMARY KEY,
       session_key TEXT NOT NULL,
       last_used TIMESTAMP WITHOUT TIME ZONE DEFAULT now());

CREATE TABLE IF NOT EXISTS areas (
       id SERIAL PRIMARY KEY,
       area_name TEXT NOT NULL,
       area_path TEXT NOT NULL,
       last_sync INTEGER DEFAULT 0,
       CONSTRAINT areaconstraint UNIQUE (area_name));

CREATE TABLE IF NOT EXISTS tags (
       id SERIAL PRIMARY KEY,
       tag_name TEXT NOT NULL,
       CONSTRAINT tagconstraint UNIQUE (tag_name));

CREATE TABLE IF NOT EXISTS area_tags (
       id SERIAL PRIMARY KEY,
       tag_id   INTEGER DEFAULT 0,
       area_id  INTEGER DEFAULT 0,
       CONSTRAINT areatagconstraint UNIQUE (tag_id, area_id));

CREATE TABLE IF NOT EXISTS run_tags (
       id SERIAL PRIMARY KEY,
       tag_id   INTEGER DEFAULT 0,
       run_id  INTEGER DEFAULT 0,
       CONSTRAINT areatagconstraint UNIQUE (tag_id, run_id));


CREATE VIEW area_tag_view as 
select a.id as aid, t.id as tid,area_name,tag_name,area_path from areas as a inner join area_tags as at on at.area_id = a.id
inner join tags as t on t.id = at.tag_id  ;

CREATE VIEW run_tag_view as 
select r.id as rid, t.id as tid,tag_name from runs as r inner join run_tags as rt on rt.run_id = r.id
inner join tags as t on t.id = rt.tag_id  ;


INSERT INTO areas (id,area_name,area_path) VALUES (0,'local','.');

CREATE TABLE IF NOT EXISTS ttype (
       id SERIAL PRIMARY KEY,
       target_spec TEXT DEFAULT '');
       

       owner      TEXT DEFAULT '',
       event_time INTEGER DEFAULT extract(epoch from now()),
       comment    TEXT DEFAULT '',
       fail_count INTEGER DEFAULT 0,
       pass_count INTEGER DEFAULT 0,
       last_update INTEGER DEFAULT extract(epoch from now()),
       area_id     INTEGER DEFAULT 0,
       publish_time INTEGER default date_part('epoch'::text, now()),
       CONSTRAINT runsconstraint UNIQUE (target,ttype_id,run_name, area_id));

create Table if not exists change_triggers (
       id SERIAL           PRIMARY KEY,
       target              TEXT NOT NULL,
       area                TEXT NOT NULL,
       iteration           INTEGER NOT NULL,
       iteration_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),  
       reason              TEXT Not null);

CREATE TABLE IF NOT EXISTS run_stats (
       id     SERIAL PRIMARY KEY,
       run_id INTEGER,
       state  TEXT,
       status TEXT,
       count  INTEGER,

       test_id      INTEGER,
       state        TEXT DEFAULT 'new',
       status       TEXT DEFAULT 'n/a',
       archive_type TEXT DEFAULT 'bup',
       du           INTEGER,
       archive_path TEXT);
 
CREATE TABLE IF NOT EXISTS users(
   id SERIAL  PRIMARY KEY   ,
   username           TEXT    NOT NULL,
   fullname          TEXT    NOT NULL, 
   email             TEXT    NOT NULL, 
   default_view      TEXT    default '',
   is_admin          boolean default 'f',
   deleted           INTEGER     default 0
);

CREATE TABLE IF NOT EXISTS base_paths(
   id SERIAL  PRIMARY KEY   ,
   path           TEXT    NOT NULL,
   deleted           INTEGER     default 0
);


CREATE TABLE IF NOT EXISTS area_owners(
   id SERIAL  PRIMARY KEY   ,
   user_id INTEGER,
   base_path_id INTEGER,
	 deleted           INTEGER     default 0
);

CREATE TABLE IF NOT EXISTS shared_user_views(
   id SERIAL  PRIMARY KEY   ,
   user_id INTEGER,
   view_id INTEGER,
	 deleted           INTEGER     default 0
);

 
CREATE TABLE IF NOT EXISTS webviews(
   id SERIAL  PRIMARY KEY   ,
   owner_id          INTEGER NOT NULL,
   name              TEXT    NOT NULL, 
   ttype_id          INTEGER DEFAULT 0,
   view_specifics    TEXT   ,
   col               TEXT    NOT NULL,
   row               TEXT    NOT NULL,
   public            INTEGER DEFAULT 0,
   search_patt      TEXT    default '.*',
   deleted           INTEGER     default 0
);

CREATE TABLE IF NOT EXISTS flexviews(
   id SERIAL  PRIMARY KEY   ,
   public            INTEGER DEFAULT 0,
   attributes         TEXT    NOT NULL,
   name 						 TEXT    NOT NULL,
   deleted           INTEGER default 0
);


CREATE TABLE IF NOT EXISTS users_webviews(
 id      SERIAL  PRIMARY KEY   ,
 user_id         INTEGER NOT NULL,
 webview_id  		 INTEGER NOT NULL,
 deleted     		 INTEGER default 0,
 searchpattern 	 TEXT Default '',
 web_page   		 TEXT Default '',
 is_default      boolean default 'f',
 other_search_data TEXT Default ''
);

CREATE TABLE IF NOT EXISTS cctrl_info(
 id      SERIAL  PRIMARY KEY   ,
 user_id   INTEGER NOT NULL,
 input TEXT Default '',
 result_file TEXT Default NULL,
 chksum TEXT
);

CREATE TABLE IF NOT EXISTS cctrl_config(
	id      SERIAL  PRIMARY KEY   ,
  area_type Text,
  metadata text default '',
  cmd TEXT 
);

CREATE TABLE IF NOT EXISTS platforms(
	id      SERIAL  PRIMARY KEY   ,
  name Text
);


-- TRUNCATE archive_blocks, archive_allocations, extradat, metadat,
-- access_log, tests, test_steps, test_data, test_rundat, archives, runs,
-- run_stats, test_meta, tasks_queue, archive_disks;