goyban
39842c72d7
- Tasks get an optional plan date separate from the deadline. Unfinished plans roll over in Today; only missed deadlines count as overdue. - Postpone sheet on every task (tomorrow, weekend, next week, ...) that can move either the plan date or the deadline, with undo. - Backlog view for undated tasks, with suggestions in Today. - Projects with deadline, notes, progress, next step and "falling behind" warnings, shown in Today and in their own view. - Times and the calendar now follow the plan date (else the deadline). - Schema v3 migration (backup taken first); export format v3 includes projects and still imports older files. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
121 lines
4.1 KiB
Python
121 lines
4.1 KiB
Python
import os
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
DB_PATH = os.environ.get("DB_PATH", "/data/whattodo.db")
|
|
SCHEMA_VERSION = 3
|
|
|
|
DEFAULT_CATEGORIES = [
|
|
("Work", "#2563eb"),
|
|
("Life", "#16a34a"),
|
|
("Personal", "#9333ea"),
|
|
("YouTube", "#dc2626"),
|
|
]
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS meta (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
color TEXT NOT NULL DEFAULT '#6b7280',
|
|
position INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
due_date TEXT,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
done_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id INTEGER PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
due_date TEXT,
|
|
planned_date TEXT,
|
|
start_time TEXT,
|
|
end_time TEXT,
|
|
tags TEXT NOT NULL DEFAULT '[]',
|
|
recurrence TEXT NOT NULL DEFAULT 'none',
|
|
recur_interval INTEGER NOT NULL DEFAULT 1,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
done_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS subtasks (
|
|
id INTEGER PRIMARY KEY,
|
|
task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
position INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_subtasks_task ON subtasks(task_id);
|
|
"""
|
|
|
|
|
|
def connect() -> sqlite3.Connection:
|
|
# FastAPI may open a request's connection and run its endpoint on different threads;
|
|
# each connection is still used by only one request at a time.
|
|
conn = sqlite3.connect(DB_PATH, check_same_thread=False)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
|
return conn
|
|
|
|
|
|
def init() -> None:
|
|
Path(DB_PATH).parent.mkdir(parents=True, exist_ok=True)
|
|
conn = connect()
|
|
try:
|
|
conn.execute("PRAGMA journal_mode = WAL")
|
|
conn.executescript(SCHEMA)
|
|
row = conn.execute("SELECT value FROM meta WHERE key = 'schema_version'").fetchone()
|
|
if row is not None and int(row["value"]) < SCHEMA_VERSION:
|
|
migrate(conn, int(row["value"]))
|
|
elif row is None:
|
|
# Fresh database: seed default categories.
|
|
with conn:
|
|
for i, (name, color) in enumerate(DEFAULT_CATEGORIES):
|
|
conn.execute(
|
|
"INSERT INTO categories (name, color, position) VALUES (?, ?, ?)",
|
|
(name, color, i),
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO meta (key, value) VALUES ('schema_version', ?)",
|
|
(str(SCHEMA_VERSION),),
|
|
)
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def migrate(conn: sqlite3.Connection, version: int) -> None:
|
|
# Keep a copy of the database as it was before migrating, next to it.
|
|
backup = sqlite3.connect(f"{DB_PATH}.before-v{SCHEMA_VERSION}.bak")
|
|
try:
|
|
conn.backup(backup)
|
|
finally:
|
|
backup.close()
|
|
with conn:
|
|
if version < 2:
|
|
conn.execute("ALTER TABLE tasks ADD COLUMN start_time TEXT")
|
|
conn.execute("ALTER TABLE tasks ADD COLUMN end_time TEXT")
|
|
if version < 3:
|
|
# The projects table itself is created by SCHEMA before migrating.
|
|
conn.execute("ALTER TABLE tasks ADD COLUMN planned_date TEXT")
|
|
conn.execute(
|
|
"ALTER TABLE tasks ADD COLUMN project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL"
|
|
)
|
|
conn.execute(
|
|
"UPDATE meta SET value = ? WHERE key = 'schema_version'", (str(SCHEMA_VERSION),)
|
|
)
|