Project

General

Profile

Database schema

Initial diagram by Igor is below:

Latest Schema diagram is available here:

Minimal changes made in existing schema from previous version:

  • Index "LarCI_statistic_name_idx" btree (name) added (Sped up queries for test details in ~5 times)
  • All foreign keys verified/put in place

Addition to scheme from previous version:

  • Tables for logfiles/datafiles and images and data storage table.

The goal is to provide the way to store related documents (like logfiles) and images in the database.

CREATE TABLE "LarCI_doc" (
    "id" serial NOT NULL PRIMARY KEY,
    "resultset_id" integer NOT NULL REFERENCES "LarCI_resultset" ("id") DEFERRABLE INITIALLY DEFERRED,
    "phase_id" integer NOT NULL REFERENCES "LarCI_phase" ("id") DEFERRABLE INITIALLY DEFERRED,
    "tag" varchar(255) NOT NULL,
    "testname" varchar(255) NOT NULL,
    "fid" varchar(100) NOT NULL,
    "when" timestamp with time zone NOT NULL
);

CREATE TABLE "LarCI_image" (
    "id" serial NOT NULL PRIMARY KEY,
    "resultset_id" integer NOT NULL REFERENCES "LarCI_resultset" ("id") DEFERRABLE INITIALLY DEFERRED,
    "phase_id" integer NOT NULL REFERENCES "LarCI_phase" ("id") DEFERRABLE INITIALLY DEFERRED,
    "tag" varchar(255) NOT NULL,
    "testname" varchar(255) NOT NULL,
    "fid" varchar(100) NOT NULL,
    "fid_m" varchar(100) NOT NULL,
    "fid_s" varchar(100) NOT NULL,
    "when" timestamp with time zone NOT NULL
);

CREATE TABLE "LarCI_filestorage" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL UNIQUE,
    "data" text NOT NULL,
    "mimetype" varchar(50) NOT NULL
);