162 lines
7.9 KiB
Python
162 lines
7.9 KiB
Python
from contextlib import contextmanager
|
|
from datetime import datetime, timezone
|
|
import json
|
|
from pathlib import Path
|
|
import sqlite3
|
|
|
|
|
|
def now_iso():
|
|
return datetime.now(timezone.utc).isoformat()
|
|
|
|
|
|
class Store:
|
|
def __init__(self, directory: Path):
|
|
directory.mkdir(parents=True, exist_ok=True)
|
|
self.path = directory / "mt5.sqlite3"
|
|
with self.connect() as db:
|
|
db.executescript("""
|
|
PRAGMA journal_mode=WAL;
|
|
CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
|
|
CREATE TABLE IF NOT EXISTS bars (
|
|
symbol TEXT, day TEXT, open REAL, high REAL, low REAL, close REAL,
|
|
volume REAL, tick_volume REAL, PRIMARY KEY(symbol,day));
|
|
CREATE TABLE IF NOT EXISTS decisions (
|
|
symbol TEXT, signal_day TEXT, PRIMARY KEY(symbol,signal_day));
|
|
CREATE TABLE IF NOT EXISTS intents (
|
|
id TEXT PRIMARY KEY, account TEXT NOT NULL, created REAL NOT NULL,
|
|
status TEXT NOT NULL, request TEXT NOT NULL, result TEXT);
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY, time TEXT NOT NULL, kind TEXT NOT NULL, payload TEXT NOT NULL);
|
|
CREATE TABLE IF NOT EXISTS executions (
|
|
day TEXT, symbol TEXT, side TEXT, status TEXT, intent TEXT,
|
|
updated_at TEXT, detail TEXT, PRIMARY KEY(day,symbol));
|
|
""")
|
|
|
|
@contextmanager
|
|
def connect(self):
|
|
db = sqlite3.connect(self.path, timeout=15)
|
|
db.row_factory = sqlite3.Row
|
|
try:
|
|
with db:
|
|
yield db
|
|
finally:
|
|
db.close()
|
|
|
|
def get(self, key, default=None):
|
|
with self.connect() as db:
|
|
row = db.execute("SELECT value FROM meta WHERE key=?", (key,)).fetchone()
|
|
return json.loads(row[0]) if row else default
|
|
|
|
def put(self, key, value):
|
|
with self.connect() as db:
|
|
db.execute("INSERT OR REPLACE INTO meta VALUES (?,?)", (key, json.dumps(value, allow_nan=False)))
|
|
|
|
def event(self, kind, payload):
|
|
with self.connect() as db:
|
|
db.execute("INSERT INTO events(time,kind,payload) VALUES (?,?,?)",
|
|
(now_iso(), kind, json.dumps(payload, allow_nan=False)))
|
|
|
|
def claim_execution(self, day, symbol, side):
|
|
# Reserve BEFORE any preparation or send. A crash consumes the daily
|
|
# attempt too. Neither restart nor a missing broker response resends it.
|
|
with self.connect() as db:
|
|
return bool(db.execute(
|
|
"INSERT OR IGNORE INTO executions VALUES (?,?,?,'preparing',NULL,?,NULL)",
|
|
(day, symbol, side, now_iso())).rowcount)
|
|
|
|
def finish_execution(self, day, symbol, status, detail, intent=None):
|
|
with self.connect() as db:
|
|
db.execute('UPDATE executions SET status=?,detail=?,intent=?,updated_at=? WHERE day=? AND symbol=?',
|
|
(status, json.dumps(detail), intent, now_iso(), day, symbol))
|
|
|
|
def executions(self, limit=100):
|
|
with self.connect() as db:
|
|
return [dict(r) for r in db.execute(
|
|
'SELECT * FROM executions ORDER BY updated_at DESC LIMIT ?', (limit,))]
|
|
|
|
def save_bars(self, rows):
|
|
with self.connect() as db:
|
|
db.executemany("INSERT OR REPLACE INTO bars VALUES (?,?,?,?,?,?,?,?)", rows)
|
|
|
|
def bars(self, start, end):
|
|
with self.connect() as db:
|
|
return [dict(row) for row in db.execute(
|
|
"SELECT * FROM bars WHERE day>=? AND day<=? ORDER BY day,symbol", (start, end))]
|
|
|
|
def bar_summary(self):
|
|
with self.connect() as db:
|
|
return dict(db.execute("SELECT count(*) AS rows, count(DISTINCT symbol) AS symbols, "
|
|
"min(day) AS first_day,max(day) AS last_day FROM bars").fetchone())
|
|
|
|
def last_bar_day(self, symbol, through):
|
|
with self.connect() as db:
|
|
return db.execute('SELECT max(day) FROM bars WHERE symbol=? AND day<=?',
|
|
(symbol, through)).fetchone()[0]
|
|
|
|
def skipped(self, day):
|
|
with self.connect() as db:
|
|
return {r[0] for r in db.execute("SELECT symbol FROM decisions WHERE signal_day=?", (day,))}
|
|
|
|
def skip(self, symbol, day, skip=True):
|
|
with self.connect() as db:
|
|
if skip:
|
|
db.execute("INSERT OR IGNORE INTO decisions VALUES (?,?)", (symbol, day))
|
|
else:
|
|
db.execute("DELETE FROM decisions WHERE symbol=? AND signal_day=?", (symbol, day))
|
|
|
|
def create_intent(self, ident, account, created, request):
|
|
with self.connect() as db:
|
|
db.execute("INSERT INTO intents(id,account,created,status,request) VALUES (?,?,?,'preview',?)",
|
|
(ident, str(account), created, json.dumps(request)))
|
|
|
|
def claim_intent(self, ident, account, cutoff):
|
|
with self.connect() as db:
|
|
db.execute("BEGIN IMMEDIATE")
|
|
row = db.execute("SELECT * FROM intents WHERE id=?", (ident,)).fetchone()
|
|
if not row or row['status'] != 'preview' or row['account'] != str(account) or row['created'] < cutoff:
|
|
raise ValueError("คำสั่งหมดอายุ ถูกใช้ไปแล้ว หรือบัญชีเปลี่ยน กรุณาสร้างตัวอย่างใหม่")
|
|
db.execute("UPDATE intents SET status='sending' WHERE id=?", (ident,))
|
|
return json.loads(row['request'])
|
|
|
|
def finish_intent(self, ident, status, result):
|
|
with self.connect() as db:
|
|
db.execute("UPDATE intents SET status=?,result=? WHERE id=?",
|
|
(status, json.dumps(result), ident))
|
|
|
|
def intents(self):
|
|
with self.connect() as db:
|
|
return [dict(r) for r in db.execute("SELECT id,created,status,request,result FROM intents ORDER BY created DESC LIMIT 30")]
|
|
|
|
def unresolved(self):
|
|
with self.connect() as db:
|
|
return [dict(r) for r in db.execute("SELECT * FROM intents WHERE status IN ('sending','unknown')")]
|
|
|
|
def reconcile(self, snapshot, magic):
|
|
"""Resolve send uncertainty from broker tickets/deals, never by resending."""
|
|
with self.connect() as db:
|
|
intents = db.execute("SELECT * FROM intents WHERE status IN ('sending','unknown','accepted','partial')").fetchall()
|
|
for intent in intents:
|
|
request = json.loads(intent['request'])
|
|
comment = request.get('comment')
|
|
if not comment:
|
|
continue
|
|
deals = [d for d in snapshot.get('deals', []) if d.get('magic') == magic and d.get('comment') == comment]
|
|
pending = [o for o in snapshot.get('orders', []) if o.get('magic') == magic and o.get('comment') == comment]
|
|
history = [o for o in snapshot.get('history_orders', []) if o.get('magic') == magic and o.get('comment') == comment]
|
|
volume = sum(float(d.get('volume', 0)) for d in deals if d.get('type') in (0, 1))
|
|
status = None
|
|
if volume >= float(request.get('volume', float('inf'))) - 1e-8:
|
|
status = 'filled'
|
|
elif volume > 0:
|
|
status = 'partial' if pending else 'partial_closed'
|
|
elif pending:
|
|
status = 'accepted'
|
|
elif history and history[-1].get('state') in (2, 5, 6):
|
|
# MT5 ORDER_STATE_CANCELED, REJECTED, EXPIRED.
|
|
status = {2: 'cancelled', 5: 'rejected', 6: 'expired'}[history[-1]['state']]
|
|
if status:
|
|
prior = json.loads(intent['result'] or '{}')
|
|
prior['broker_reconciliation'] = {'status': status, 'filled_volume': volume, 'at': now_iso()}
|
|
db.execute("UPDATE intents SET status=?,result=? WHERE id=?",
|
|
(status, json.dumps(prior), intent['id']))
|