forked from antekov/Adwizard
439 lines
13 KiB
SQL
--
| |||
-- @version 1.05
| |||
--
| |||
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,
| |||
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;
|