-- Database: testsuite

-- DROP DATABASE IF EXISTS testsuite;

CREATE DATABASE testsuite
    WITH
    OWNER = fpc
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_GB.UTF-8'
    LC_CTYPE = 'en_GB.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE SEQUENCE SEQ_TESTCATEGORY as INT start with 1;

CREATE TABLE TESTCATEGORY (
  TA_ID INT NOT NULL DEFAULT nextval('SEQ_TESTCATEGORY'),
  TA_NAME VARCHAR(20),
  constraint PK_TESTCATEGORY PRIMARY KEY (TA_ID)
);

CREATE UNIQUE INDEX UDX_TESTCATEGORYNAME ON TESTCATEGORY(TA_NAME);

CREATE SEQUENCE SEQ_TESTCPU as INT start with 1;

CREATE TABLE TESTCPU (
  TC_ID INT NOT NULL DEFAULT nextval('SEQ_TESTCPU'),
  TC_NAME varchar(10),
  constraint PK_TESTCPU PRIMARY KEY (TC_ID)
);

CREATE UNIQUE INDEX UDX_TESTCPU ON TESTCPU(TC_NAME);

CREATE SEQUENCE SEQ_TESTOS as INT start with 1;

CREATE TABLE TESTOS (
  TO_ID INT DEFAULT nextval('SEQ_TESTOS'),
  TO_NAME varchar(10),
  constraint PK_TESTOS PRIMARY KEY (TO_ID)
);

CREATE UNIQUE INDEX UDX_TESTOS ON TESTOS(TO_NAME);

CREATE SEQUENCE SEQ_TESTVERSION as INT start with 1;

CREATE TABLE TESTVERSION (
  TV_ID int NOT NULL default nextval('SEQ_TESTVERSION'),
  TV_VERSION varchar(10),
  TV_RELEASEDATE date DEFAULT CURRENT_TIMESTAMP,
  constraint PK_TESTVERSION PRIMARY KEY (TV_ID)
);

CREATE UNIQUE INDEX UDX_TESTVERSION ON TESTVERSION(TV_VERSION);

CREATE SEQUENCE SEQ_TESTS as INT start with 1;

CREATE TABLE TESTS (
  T_ID int NOT NULL default nextval('SEQ_TESTS'),
  T_NAME varchar(40),
  T_CPU varchar(20),
  T_OS varchar(30),
  T_VERSION varchar(10),
  T_ADDDATE date NOT NULL,
  T_GRAPH boolean NOT NULL default 'f',
  T_INTERACTIVE boolean NOT NULL default 'f',
  T_RESULT int NOT NULL default '0',
  T_FAIL boolean NOT NULL default 'f',
  T_RECOMPILE boolean NOT NULL default 'f',
  T_NORUN boolean NOT NULL default 'f',
  T_NEEDLIBRARY boolean NOT NULL default 'f',
  T_KNOWNRUNERROR INT NOT NULL default 0,
  T_KNOWN boolean NOT NULL default 'f',
  T_NOTE varchar(255) default NULL,
  T_DESCRIPTION text,
  T_SOURCE text,
  T_OPTS varchar(255) default NULL,
  constraint PK_TESTS PRIMARY KEY  (T_ID)
);

CREATE UNIQUE INDEX UDX_TESTS ON TESTS(T_NAME);

CREATE SEQUENCE SEQ_TESTPLATFORM as INT start with 1;

CREATE TABLE TESTPLATFORM (
  TP_ID INT NOT NULL default nextval('SEQ_TESTPLATFORM'),
  TP_CPU_FK INT NOT NULL,
  TP_OS_FK INT NOT NULL,
  TP_VERSION_FK INT NOT NULL,
  TP_CATEGORY_FK int NOT NULL,
  TP_CONFIG VARCHAR(255) NOT NULL,
  constraint PK_TESTPLATFORM PRIMARY KEY (TP_ID)
);

CREATE UNIQUE INDEX UDX_TESTPLATFORM ON TESTPLATFORM(TP_CPU_FK,TP_OS_FK,TP_VERSION_FK,TP_CATEGORY_FK,TP_CONFIG);
CREATE INDEX IDX_TESTPLATFORMRELATIONS ON TESTPLATFORM(TP_CPU_FK,TP_OS_FK,TP_VERSION_FK,TP_CATEGORY_FK);

CREATE SEQUENCE SEQ_TESTRUN as BIGINT start with 1;

CREATE TABLE TESTRUN (
  TU_ID bigint NOT NULL default nextval('SEQ_TESTRUN'),
  TU_DATE DATE NOT NULL default CURRENT_DATE,
  TU_PLATFORM_FK int NOT NULL,
  TU_MACHINE VARCHAR(127) NOT NULL,
  TU_COMPILERDATE VARCHAR(15),
  TU_COMPILERFULLVERSION VARCHAR(50),
  TU_COMPILERREVISION VARCHAR(50),
  TU_TESTSREVISION VARCHAR(50),
  TU_RTLREVISION VARCHAR(50),
  TU_PACKAGESREVISION VARCHAR(50),
  TU_SUBMITTER varchar(128) NOT NULL default '',
  TU_FAILEDTOCOMPILE int NOT NULL default 0,
  TU_SUCCESSFULLYFAILED int NOT NULL default 0,
  TU_FAILEDTOFAIL int NOT NULL default 0,
  TU_SUCCESFULLYCOMPILED int NOT NULL default 0,
  TU_FAILEDTORUN int NOT NULL default 0,
  TU_KNOWNPROBLEM int NOT NULL default 0,
  TU_SUCCESSFULLYRUN int NOT NULL default 0,
  TU_SKIPPEDGRAPHTEST int NOT NULL default 0,
  TU_SKIPPEDINTERACTIVETEST int NOT NULL default 0,
  TU_KNOWNBUG int NOT NULL default 0,
  TU_COMPILERVERIONTOOLOW int NOT NULL default 0,
  TU_COMPILERVERIONTOOHIGH int NOT NULL default 0,
  TU_OTHERCPU int NOT NULL default 0,
  TU_OTHERTARGET int NOT NULL default 0,
  TU_UNIT int NOT NULL default 0,
  TU_SKIPPINGRUNTEST int NOT NULL default 0,
  CONSTRAINT PK_TESTRUN PRIMARY KEY (TU_ID)
);

CREATE UNIQUE INDEX UDX_TESTRUN ON TESTRUN(TU_DATE,TU_PLATFORM_FK);
CREATE INDEX IDX_TESTRUNPLATFORM ON TESTRUN(TU_PLATFORM_FK,TU_DATE);

CREATE SEQUENCE SEQ_TESTRESULTS as BIGINT start with 1;

CREATE TABLE TESTRESULTS (
  TR_ID bigint NOT NULL default nextval('SEQ_TESTRESULTS'),
  TR_TESTRUN_FK bigint NOT NULL default 0,
  TR_TEST_FK int not NULL,
  TR_OK Boolean NOT NULL default 'f',
  TR_SKIP boolean NOT NULL default 'f',
  TR_RESULT int NOT NULL default 0,
  TR_LOG text,
  constraint PK_TESTRESULTS PRIMARY KEY (TR_ID)
);

CREATE UNIQUE INDEX UDX_TESTRESULTS ON TESTRESULTS (TR_TESTRUN_FK,TR_TEST_FK);

CREATE TABLE TESTLASTRESULTS (
  TL_TEST_FK int not NULL,
  TL_PLATFORM_FK int not NULL,
  TL_TESTRESULTS_FK bigint NOT NULL
);

CREATE UNIQUE INDEX UDX_TESTLASTRESULTS ON TESTLASTRESULTS(TL_PLATFORM_FK,TL_TEST_FK);

CREATE TABLE TESTPREVIOUSRESULTS (
  TPR_TEST_FK int not NULL,
  TPR_PLATFORM_FK int not NULL,
  TPR_TESTRESULTS_FK bigint NOT NULL
);

CREATE UNIQUE INDEX UDX_TESTPREVIOUSRESULTS ON TESTPREVIOUSRESULTS(TPR_PLATFORM_FK,TPR_TEST_FK);