Files

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']))