| """SQLite storage: one row per focus session (epoch seconds, local time is derived on display)."""
|
| import os
|
| import sqlite3
|
| from pathlib import Path
|
|
|
| DATA_DIR = Path(os.environ.get("APPDATA", Path.home())) / "FocusTracker"
|
| DB_PATH = DATA_DIR / "focus.db"
|
| MIN_SESSION_S = 10
|
|
|
| SCHEMA = """
|
| CREATE TABLE IF NOT EXISTS sessions (
|
| id INTEGER PRIMARY KEY AUTOINCREMENT,
|
| project TEXT NOT NULL,
|
| start_ts INTEGER NOT NULL,
|
| end_ts INTEGER,
|
| last_ts INTEGER,
|
| plan_min INTEGER
|
| );
|
| CREATE INDEX IF NOT EXISTS idx_sessions_start ON sessions(start_ts);
|
| CREATE TABLE IF NOT EXISTS projects (name TEXT PRIMARY KEY);
|
| CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);
|
| """
|
|
|
|
|
| class DB:
|
| def __init__(self, path=DB_PATH):
|
| path = Path(path)
|
| path.parent.mkdir(parents=True, exist_ok=True)
|
| self.con = sqlite3.connect(path)
|
| self.con.executescript(SCHEMA)
|
| cols = [r[1] for r in self.con.execute("PRAGMA table_info(sessions)")]
|
| if "plan_min" not in cols:
|
| self.con.execute("ALTER TABLE sessions ADD COLUMN plan_min INTEGER")
|
| self._recover()
|
|
|
| def _recover(self):
|
|
|
| self.con.execute(
|
| "UPDATE sessions SET end_ts = COALESCE(last_ts, start_ts) WHERE end_ts IS NULL")
|
| self.con.execute("DELETE FROM sessions WHERE end_ts - start_ts < ?", (MIN_SESSION_S,))
|
| self.con.commit()
|
|
|
| def get(self, key, default=None):
|
| row = self.con.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
|
| return row[0] if row else default
|
|
|
| def set(self, key, value):
|
| self.con.execute("INSERT OR REPLACE INTO settings VALUES (?, ?)", (key, str(value)))
|
| self.con.commit()
|
|
|
|
|
| def start(self, project, ts, plan_min):
|
| self.add_project(project)
|
| cur = self.con.execute(
|
| "INSERT INTO sessions (project, start_ts, last_ts, plan_min) VALUES (?, ?, ?, ?)",
|
| (project, ts, ts, plan_min))
|
| self.con.commit()
|
| return cur.lastrowid
|
|
|
| def heartbeat(self, sid, ts):
|
| self.con.execute("UPDATE sessions SET last_ts=? WHERE id=?", (ts, sid))
|
| self.con.commit()
|
|
|
| def set_plan(self, sid, plan_min):
|
| self.con.execute("UPDATE sessions SET plan_min=? WHERE id=?", (plan_min, sid))
|
| self.con.commit()
|
|
|
| def stop(self, sid, ts):
|
| start = self.con.execute("SELECT start_ts FROM sessions WHERE id=?", (sid,)).fetchone()[0]
|
| if ts - start < MIN_SESSION_S:
|
| self.con.execute("DELETE FROM sessions WHERE id=?", (sid,))
|
| else:
|
| self.con.execute("UPDATE sessions SET end_ts=?, last_ts=? WHERE id=?", (ts, ts, sid))
|
| self.con.commit()
|
|
|
| def add_session(self, project, start, end, plan_min):
|
| self.add_project(project)
|
| cur = self.con.execute(
|
| "INSERT INTO sessions (project, start_ts, end_ts, last_ts, plan_min) VALUES (?, ?, ?, ?, ?)",
|
| (project, start, end, end, plan_min))
|
| self.con.commit()
|
| return cur.lastrowid
|
|
|
| def update_session(self, sid, project, start, end, plan_min):
|
| self.add_project(project)
|
| self.con.execute(
|
| "UPDATE sessions SET project=?, start_ts=?, end_ts=?, last_ts=?, plan_min=? WHERE id=?",
|
| (project, start, end, end, plan_min, sid))
|
| self.con.commit()
|
|
|
| def has_session(self, project, start, end):
|
| return self.con.execute(
|
| "SELECT 1 FROM sessions WHERE project=? AND start_ts=? AND end_ts=?",
|
| (project, start, end)).fetchone() is not None
|
|
|
| def delete_session(self, sid):
|
| self.con.execute("DELETE FROM sessions WHERE id=?", (sid,))
|
| self.con.commit()
|
|
|
| def sessions(self, now):
|
| """All sessions as (id, project, start, end, plan_min); a running session ends at now."""
|
| return self.con.execute(
|
| "SELECT id, project, start_ts, COALESCE(end_ts, ?), plan_min FROM sessions "
|
| "ORDER BY start_ts", (now,)).fetchall()
|
|
|
| def seconds_since(self, since, now):
|
| row = self.con.execute(
|
| "SELECT SUM(MIN(COALESCE(end_ts, ?), ?) - MAX(start_ts, ?)) FROM sessions "
|
| "WHERE COALESCE(end_ts, ?) > ?", (now, now, since, now, since)).fetchone()
|
| return row[0] or 0
|
|
|
|
|
| def add_project(self, name):
|
| self.con.execute("INSERT OR IGNORE INTO projects VALUES (?)", (name,))
|
| self.con.commit()
|
|
|
| def projects(self):
|
| """Project names, most recently used first, then projects never used."""
|
| used = [r[0] for r in self.con.execute(
|
| "SELECT project FROM sessions GROUP BY project ORDER BY MAX(start_ts) DESC")]
|
| extra = [r[0] for r in self.con.execute("SELECT name FROM projects ORDER BY name")
|
| if r[0] not in used]
|
| return used + extra
|
|
|
| def rename_project(self, old, new):
|
| self.add_project(new)
|
| self.con.execute("UPDATE sessions SET project=? WHERE project=?", (new, old))
|
| self.con.execute("DELETE FROM projects WHERE name=?", (old,))
|
| self.con.commit()
|
|
|
| def delete_project(self, name):
|
| self.con.execute("DELETE FROM sessions WHERE project=?", (name,))
|
| self.con.execute("DELETE FROM projects WHERE name=?", (name,))
|
| self.con.commit()
|
|
|
