174 lines
6.0 KiB
PL/PgSQL
174 lines
6.0 KiB
PL/PgSQL
BEGIN;
|
|
|
|
INSERT INTO strategy_run (run_id, user_id, status)
|
|
SELECT 'default_' || id, id, 'STOPPED'
|
|
FROM app_user
|
|
ON CONFLICT (run_id) DO NOTHING;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_attribute
|
|
WHERE attrelid = 'engine_status'::regclass AND attname = 'id' AND attidentity = ''
|
|
) THEN
|
|
ALTER TABLE engine_status ALTER COLUMN id DROP DEFAULT;
|
|
END IF;
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_attribute
|
|
WHERE attrelid = 'engine_state'::regclass AND attname = 'id' AND attidentity = ''
|
|
) THEN
|
|
ALTER TABLE engine_state ALTER COLUMN id DROP DEFAULT;
|
|
END IF;
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_attribute
|
|
WHERE attrelid = 'engine_state_paper'::regclass AND attname = 'id' AND attidentity = ''
|
|
) THEN
|
|
ALTER TABLE engine_state_paper ALTER COLUMN id DROP DEFAULT;
|
|
END IF;
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_attribute
|
|
WHERE attrelid = 'paper_broker_account'::regclass AND attname = 'id' AND attidentity = ''
|
|
) THEN
|
|
ALTER TABLE paper_broker_account ALTER COLUMN id DROP DEFAULT;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_engine_status_user_run') THEN
|
|
ALTER TABLE engine_status
|
|
ADD CONSTRAINT uq_engine_status_user_run UNIQUE (user_id, run_id);
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_engine_state_user_run') THEN
|
|
ALTER TABLE engine_state
|
|
ADD CONSTRAINT uq_engine_state_user_run UNIQUE (user_id, run_id);
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_engine_state_paper_user_run') THEN
|
|
ALTER TABLE engine_state_paper
|
|
ADD CONSTRAINT uq_engine_state_paper_user_run UNIQUE (user_id, run_id);
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_paper_broker_account_user_run') THEN
|
|
ALTER TABLE paper_broker_account
|
|
ADD CONSTRAINT uq_paper_broker_account_user_run UNIQUE (user_id, run_id);
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE strategy_config ADD COLUMN IF NOT EXISTS user_id text;
|
|
ALTER TABLE strategy_config ADD COLUMN IF NOT EXISTS run_id text;
|
|
|
|
ALTER TABLE strategy_config DROP CONSTRAINT IF EXISTS strategy_config_pkey;
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_attribute
|
|
WHERE attrelid = 'strategy_config'::regclass AND attname = 'id' AND attidentity = ''
|
|
) THEN
|
|
ALTER TABLE strategy_config ALTER COLUMN id DROP DEFAULT;
|
|
END IF;
|
|
END $$;
|
|
|
|
INSERT INTO strategy_config (
|
|
id, strategy, sip_amount, sip_frequency_value, sip_frequency_unit,
|
|
mode, broker, active, frequency, frequency_days, unit, next_run,
|
|
user_id, run_id
|
|
)
|
|
SELECT
|
|
sc.id, sc.strategy, sc.sip_amount, sc.sip_frequency_value, sc.sip_frequency_unit,
|
|
sc.mode, sc.broker, sc.active, sc.frequency, sc.frequency_days, sc.unit, sc.next_run,
|
|
sr.user_id, sr.run_id
|
|
FROM strategy_config sc
|
|
CROSS JOIN strategy_run sr
|
|
WHERE sc.user_id IS NULL AND sc.run_id IS NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM strategy_config sc2
|
|
WHERE sc2.user_id = sr.user_id AND sc2.run_id = sr.run_id
|
|
);
|
|
|
|
DELETE FROM strategy_config
|
|
WHERE user_id IS NULL AND run_id IS NULL;
|
|
|
|
ALTER TABLE strategy_config
|
|
ALTER COLUMN user_id SET NOT NULL,
|
|
ALTER COLUMN run_id SET NOT NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_strategy_config_user_run') THEN
|
|
ALTER TABLE strategy_config
|
|
ADD CONSTRAINT uq_strategy_config_user_run UNIQUE (user_id, run_id);
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_config_user') THEN
|
|
ALTER TABLE strategy_config
|
|
ADD CONSTRAINT fk_strategy_config_user FOREIGN KEY (user_id)
|
|
REFERENCES app_user(id) ON DELETE CASCADE;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_config_run') THEN
|
|
ALTER TABLE strategy_config
|
|
ADD CONSTRAINT fk_strategy_config_run FOREIGN KEY (run_id)
|
|
REFERENCES strategy_run(run_id) ON DELETE CASCADE;
|
|
END IF;
|
|
END $$;
|
|
|
|
UPDATE strategy_log
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
|
|
ALTER TABLE engine_status DISABLE TRIGGER USER;
|
|
ALTER TABLE engine_state DISABLE TRIGGER USER;
|
|
ALTER TABLE engine_state_paper DISABLE TRIGGER USER;
|
|
ALTER TABLE paper_broker_account DISABLE TRIGGER USER;
|
|
ALTER TABLE paper_order DISABLE TRIGGER USER;
|
|
ALTER TABLE paper_trade DISABLE TRIGGER USER;
|
|
ALTER TABLE mtm_ledger DISABLE TRIGGER USER;
|
|
ALTER TABLE event_ledger DISABLE TRIGGER USER;
|
|
ALTER TABLE paper_position DISABLE TRIGGER USER;
|
|
ALTER TABLE paper_equity_curve DISABLE TRIGGER USER;
|
|
|
|
UPDATE engine_status
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE engine_event
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE paper_broker_account
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE paper_position
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE paper_order
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE paper_trade
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE paper_equity_curve
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE engine_state
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE engine_state_paper
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE mtm_ledger
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
UPDATE event_ledger
|
|
SET run_id = 'default_' || user_id
|
|
WHERE run_id IS NULL OR run_id = 'default_run';
|
|
|
|
ALTER TABLE engine_status ENABLE TRIGGER USER;
|
|
ALTER TABLE engine_state ENABLE TRIGGER USER;
|
|
ALTER TABLE engine_state_paper ENABLE TRIGGER USER;
|
|
ALTER TABLE paper_broker_account ENABLE TRIGGER USER;
|
|
ALTER TABLE paper_order ENABLE TRIGGER USER;
|
|
ALTER TABLE paper_trade ENABLE TRIGGER USER;
|
|
ALTER TABLE mtm_ledger ENABLE TRIGGER USER;
|
|
ALTER TABLE event_ledger ENABLE TRIGGER USER;
|
|
ALTER TABLE paper_position ENABLE TRIGGER USER;
|
|
ALTER TABLE paper_equity_curve ENABLE TRIGGER USER;
|
|
|
|
COMMIT;
|