Adwizard/Database/db.opt.schema.sql

--
-- @version 1.06
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: jobs
DROP TABLE IF EXISTS jobs;

CREATE TABLE jobs (
    id_job        INTEGER PRIMARY KEY AUTOINCREMENT,
    id_stage      INTEGER REFERENCES stages (id_stage) ON DELETE CASCADE
                                                       ON UPDATE CASCADE
                          NOT NULL,
    symbol        TEXT    DEFAULT EURGBP,
    period        TEXT    DEFAULT H1,
    tester_inputs TEXT,
    status        TEXT    CHECK (status IN ('Queued', 'Process', 'Done') ) 
                          NOT NULL
                          DEFAULT Done
);


-- Table: passes
DROP TABLE IF EXISTS passes;

CREATE TABLE passes (
    id_pass               INTEGER  PRIMARY KEY AUTOINCREMENT,
    id_task               INTEGER  REFERENCES tasks (id_task) ON DELETE CASCADE,
    pass                  INTEGER,
    is_optimization       INTEGER  CHECK (is_optimization IN (0, 1) ),
    is_forward            INTEGER  CHECK (is_forward IN (0, 1) ),
    initial_deposit       REAL,
    withdrawal            REAL,
    profit                REAL,
    gross_profit          REAL,
    gross_loss            REAL,
    max_profittrade       REAL,
    max_losstrade         REAL,
    conprofitmax          REAL,
    conprofitmax_trades   REAL,
    max_conwins           REAL,
    max_conprofit_trades  REAL,
    conlossmax            REAL,
    conlossmax_trades     REAL,
    max_conlosses         REAL,
    max_conloss_trades    REAL,
    balancemin            REAL,
    balance_dd            REAL,
    balancedd_percent     REAL,
    balance_ddrel_percent REAL,
    balance_dd_relative   REAL,
    equitymin             REAL,
    equity_dd             REAL,
    equitydd_percent      REAL,
    equity_ddrel_percent  REAL,
    equity_dd_relative    REAL,
    expected_payoff       REAL,
    profit_factor         REAL,
    recovery_factor       REAL,
    sharpe_ratio          REAL,
    min_marginlevel       REAL,
    deals                 REAL,
    trades                REAL,
    profit_trades         REAL,
    loss_trades           REAL,
    short_trades          REAL,
    long_trades           REAL,
    profit_shorttrades    REAL,
    profit_longtrades     REAL,
    profittrades_avgcon   REAL,
    losstrades_avgcon     REAL,
    complex_criterion     REAL,
    custom_ontester       REAL,
    params                TEXT,
    inputs                TEXT,
    pass_date             DATETIME
);


-- Table: passes_clusters
DROP TABLE IF EXISTS passes_clusters;

CREATE TABLE passes_clusters (
    id_task INTEGER,
    id_pass INTEGER,
    cluster INTEGER
);


-- Table: projects
DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
    id_project  INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL,
    version     TEXT    NOT NULL,
    description TEXT,
    params      TEXT,
    status      TEXT    CHECK (status IN ('Queued', 'Process', 'Done') ) 
                        NOT NULL
                        DEFAULT Done
);


-- Table: stages
DROP TABLE IF EXISTS stages;

CREATE TABLE stages (
    id_stage               INTEGER PRIMARY KEY AUTOINCREMENT,
    id_project             INTEGER REFERENCES projects (id_project) ON DELETE CASCADE
                                                                    ON UPDATE CASCADE,
    id_parent_stage        INTEGER REFERENCES stages (id_stage) ON DELETE CASCADE
                                                                ON UPDATE CASCADE,
    name                   TEXT    NOT NULL
                                   DEFAULT (1),
    expert                 TEXT,
    symbol                 TEXT    NOT NULL
                                   DEFAULT EURGBP,
    period                 TEXT    NOT NULL
                                   DEFAULT H1,
    optimization           INTEGER NOT NULL
                                   DEFAULT (2),
    model                  INTEGER NOT NULL
                                   DEFAULT (2),
    from_date              DATE    NOT NULL
                                   DEFAULT ('2022.01.01'),
    to_date                DATE    NOT NULL
                                   DEFAULT ('2022.06.01'),
    forward_mode           INTEGER NOT NULL
                                   DEFAULT (0),
    forward_date           DATE,
    deposit                INTEGER NOT NULL
                                   DEFAULT (10000),
    currency               TEXT    NOT NULL
                                   DEFAULT USD,
    profit_in_pips         INTEGER NOT NULL
                                   DEFAULT (0),
    leverage               INTEGER NOT NULL
                                   DEFAULT (200),
    execution_mode         INTEGER NOT NULL
                                   DEFAULT (0),
    optimization_criterion INTEGER NOT NULL
                                   DEFAULT (7),
    status                 TEXT    CHECK (status IN ('Queued', 'Process', 'Done') ) 
                                   NOT NULL
                                   DEFAULT Done
);


-- Table: strategy_groups
DROP TABLE IF EXISTS strategy_groups;

CREATE TABLE strategy_groups (
    id_pass INTEGER REFERENCES passes (id_pass) ON DELETE CASCADE
                                                ON UPDATE CASCADE
                    PRIMARY KEY,
    name    TEXT
);


-- Table: tasks
DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
    id_task                INTEGER  PRIMARY KEY AUTOINCREMENT,
    id_job                 INTEGER  NOT NULL
                                    REFERENCES jobs (id_job) ON DELETE CASCADE
                                                             ON UPDATE CASCADE,
    optimization_criterion INTEGER  DEFAULT (7) 
                                    NOT NULL,
    start_date             DATETIME,
    finish_date            DATETIME,
    max_duration           INTEGER  NOT NULL
                                    DEFAULT 0,
    status                 TEXT     NOT NULL
                                    DEFAULT Queued
                                    CHECK (status IN ('Queued', 'Process', 'Done') ) 
);


-- Trigger: insert_empty_job
DROP TRIGGER IF EXISTS insert_empty_job;
CREATE TRIGGER insert_empty_job
         AFTER INSERT
            ON stages
          WHEN NEW.name = 'Single tester pass'
BEGIN
    INSERT INTO jobs VALUES (
                         NULL,
                         NEW.id_stage,
                         NULL,
                         NULL,
                         NULL,
                         'Done'
                     );
    INSERT INTO tasks (
                          id_job,
                          optimization_criterion,
                          status
                      )
                      VALUES (
                          (
                              SELECT id_job
                                FROM jobs
                               WHERE id_stage = NEW.id_stage
                          ),
-                         1,
                          'Done'
                      );
END;


-- Trigger: insert_empty_stage
DROP TRIGGER IF EXISTS insert_empty_stage;
CREATE TRIGGER insert_empty_stage
         AFTER INSERT
            ON projects
BEGIN
    INSERT INTO stages (
                           id_project,
                           name,
                           optimization,
                           status
                       )
                       VALUES (
                           NEW.id_project,
                           'Single tester pass',
                           0,
                           'Done'
                       );
END;


-- Trigger: upd_job_status_done
DROP TRIGGER IF EXISTS upd_job_status_done;
CREATE TRIGGER upd_job_status_done
         AFTER UPDATE OF status
            ON jobs
          WHEN NEW.status = 'Done'
BEGIN
    UPDATE stages
       SET status = (
               SELECT CASE WHEN (
                                    SELECT COUNT( * ) 
                                      FROM jobs j
                                     WHERE (j.status = 'Queued' OR 
                                            j.status = 'Process') AND 
                                           j.id_stage = NEW.id_stage
                                )
=                         0 THEN 'Done' ELSE 'Process' END
           )
     WHERE id_stage = NEW.id_stage;
END;


-- Trigger: upd_job_status_process
DROP TRIGGER IF EXISTS upd_job_status_process;
CREATE TRIGGER upd_job_status_process
         AFTER UPDATE OF status
            ON jobs
          WHEN NEW.status = 'Process'
BEGIN
    UPDATE stages
       SET status = 'Process'
     WHERE id_stage = NEW.id_stage;
END;


-- Trigger: upd_job_status_queued
DROP TRIGGER IF EXISTS upd_job_status_queued;
CREATE TRIGGER upd_job_status_queued
         AFTER UPDATE OF status
            ON jobs
          WHEN NEW.status = 'Queued'
BEGIN
    UPDATE tasks
       SET status = 'Queued'
     WHERE id_job = NEW.id_job;
END;


-- Trigger: upd_pass_date
DROP TRIGGER IF EXISTS upd_pass_date;
CREATE TRIGGER upd_pass_date
         AFTER INSERT
            ON passes
BEGIN
    UPDATE passes
       SET pass_date = DATETIME('NOW') 
     WHERE id_pass = NEW.id_pass;
END;


-- Trigger: upd_project_status_done
DROP TRIGGER IF EXISTS upd_project_status_done;
CREATE TRIGGER upd_project_status_done
         AFTER UPDATE OF status
            ON projects
          WHEN NEW.status = 'Done'
BEGIN
    UPDATE tasks
       SET status = 'Done'
     WHERE id_task IN (
        SELECT t.id_task
          FROM tasks t
               JOIN
               jobs j ON j.id_job = t.id_job
               JOIN
               stages s ON s.id_stage = j.id_stage
               JOIN
               projects p ON p.id_project = s.id_project
         WHERE p.id_project = NEW.id_project AND 
               t.status <> 'Done'
    );
END;


-- Trigger: upd_project_status_queued
DROP TRIGGER IF EXISTS upd_project_status_queued;
CREATE TRIGGER upd_project_status_queued
         AFTER UPDATE OF status
            ON projects
          WHEN NEW.status = 'Queued'
BEGIN
    UPDATE stages
       SET status = 'Queued'
     WHERE id_project = NEW.id_project AND
           name <> 'Single tester pass';
END;


-- Trigger: upd_stage_status_done
DROP TRIGGER IF EXISTS upd_stage_status_done;
CREATE TRIGGER upd_stage_status_done
         AFTER UPDATE OF status
            ON stages
          WHEN NEW.status = 'Done'
BEGIN
    UPDATE projects
       SET status = (
               SELECT CASE WHEN (
                                    SELECT COUNT( * ) 
                                      FROM stages s
                                     WHERE (s.status = 'Queued' OR 
                                            s.status = 'Process') AND 
                                           s.name <> 'Single tester pass' AND 
                                           s.id_project = NEW.id_project
                                )
=                         0 THEN 'Done' ELSE 'Process' END
           )
     WHERE id_project = NEW.id_project;
END;


-- Trigger: upd_stage_status_process
DROP TRIGGER IF EXISTS upd_stage_status_process;
CREATE TRIGGER upd_stage_status_process
         AFTER UPDATE OF status
            ON stages
          WHEN NEW.status = 'Process'
BEGIN
    UPDATE projects
       SET status = 'Process'
     WHERE id_project = NEW.id_project;
END;


-- Trigger: upd_stage_status_queued
DROP TRIGGER IF EXISTS upd_stage_status_queued;
CREATE TRIGGER upd_stage_status_queued
         AFTER UPDATE
            ON stages
          WHEN NEW.status = 'Queued' AND 
               OLD.status <> NEW.status
BEGIN
    UPDATE jobs
       SET status = 'Queued'
     WHERE id_stage = NEW.id_stage;
END;


-- Trigger: upd_task_status_done
DROP TRIGGER IF EXISTS upd_task_status_done;
CREATE TRIGGER upd_task_status_done
         AFTER UPDATE OF status
            ON tasks
          WHEN NEW.status = 'Done'
BEGIN
    UPDATE tasks
       SET finish_date = DATETIME('NOW') 
     WHERE id_task = NEW.id_task;
    UPDATE jobs
       SET status = (
               SELECT CASE WHEN (
                                    SELECT COUNT( * ) 
                                      FROM tasks t
                                     WHERE (t.status = 'Queued' OR 
                                            t.status = 'Process') AND 
                                           t.id_job = NEW.id_job
                                )
=                         0 THEN 'Done' ELSE 'Process' END
           )
     WHERE id_job = NEW.id_job;
END;


-- Trigger: upd_task_status_process
DROP TRIGGER IF EXISTS upd_task_status_process;
CREATE TRIGGER upd_task_status_process
         AFTER UPDATE OF status
            ON tasks
          WHEN NEW.status = 'Process'
BEGIN
    UPDATE tasks
       SET start_date = DATETIME('NOW') 
     WHERE id_task = NEW.id_task;
    DELETE FROM passes
          WHERE id_task = NEW.id_task;
    UPDATE jobs
       SET status = 'Process'
     WHERE id_job = NEW.id_job;
END;


-- Trigger: upd_task_status_queued
DROP TRIGGER IF EXISTS upd_task_status_queued;
CREATE TRIGGER upd_task_status_queued
         AFTER UPDATE OF status
            ON tasks
          WHEN NEW.status = 'Queued'
BEGIN
    UPDATE tasks
       SET start_date = NULL,
           finish_date = NULL
     WHERE id_task = NEW.id_task;
END;


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;