Padreug
7362592880
m001 was leaving partial state on first boot: CREATE TABLE succeeded but `CREATE UNIQUE INDEX ... ON tasks.tasks (...)` failed silently in sqlite (schema prefix on the target table, not the index name, is not what sqlite expects for ATTACHed databases). Because LNbits never recorded the migration version, every restart retried m001 and crashed on `CREATE TABLE tasks already exists`. Two fixes: - Use IF NOT EXISTS on every CREATE so a partial run is safe to re-run. - Schema-prefix the index *name*, not the target table: `CREATE INDEX tasks.idx ON tbl (...)` instead of `CREATE INDEX idx ON tasks.tbl (...)`. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
78 lines
2.6 KiB
Python
78 lines
2.6 KiB
Python
async def m001_initial(db):
|
|
"""
|
|
Tasks table (NIP-52 kind 31922 cache) and completions table (kind 31925
|
|
cache). The (pubkey, d_tag) pair is the parameterized-replaceable key.
|
|
Uses IF NOT EXISTS throughout so a partial first-run leaves the
|
|
migration safely re-runnable.
|
|
"""
|
|
await db.execute(
|
|
f"""
|
|
CREATE TABLE IF NOT EXISTS tasks.tasks (
|
|
id TEXT PRIMARY KEY,
|
|
wallet TEXT NOT NULL,
|
|
pubkey TEXT NOT NULL,
|
|
d_tag TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
start_date TEXT NOT NULL,
|
|
end_date TEXT,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
location TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
event_type TEXT NOT NULL DEFAULT 'task',
|
|
participants TEXT,
|
|
categories TEXT,
|
|
recurrence TEXT,
|
|
nostr_event_id TEXT,
|
|
nostr_event_created_at INTEGER,
|
|
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
|
);
|
|
"""
|
|
)
|
|
# Schema prefix goes on the INDEX NAME (not the target table) in sqlite
|
|
# when the schema is an ATTACHed database — `CREATE INDEX schema.idx`,
|
|
# not `CREATE INDEX idx ON schema.tbl`.
|
|
await db.execute(
|
|
"CREATE UNIQUE INDEX IF NOT EXISTS tasks.tasks_pubkey_dtag_idx "
|
|
"ON tasks (pubkey, d_tag)"
|
|
)
|
|
|
|
await db.execute(
|
|
f"""
|
|
CREATE TABLE IF NOT EXISTS tasks.completions (
|
|
id TEXT PRIMARY KEY,
|
|
task_address TEXT NOT NULL,
|
|
pubkey TEXT NOT NULL,
|
|
occurrence TEXT,
|
|
task_status TEXT NOT NULL DEFAULT 'claimed',
|
|
completed_at INTEGER,
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
nostr_created_at INTEGER NOT NULL,
|
|
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
|
);
|
|
"""
|
|
)
|
|
await db.execute(
|
|
"CREATE INDEX IF NOT EXISTS tasks.tasks_completions_address_idx "
|
|
"ON completions (task_address)"
|
|
)
|
|
await db.execute(
|
|
"CREATE INDEX IF NOT EXISTS tasks.tasks_completions_pubkey_idx "
|
|
"ON completions (pubkey)"
|
|
)
|
|
|
|
|
|
async def m002_settings(db):
|
|
"""Singleton settings row for the admin UI."""
|
|
await db.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS tasks.settings (
|
|
id INTEGER PRIMARY KEY DEFAULT 1,
|
|
public_listing BOOLEAN NOT NULL DEFAULT FALSE
|
|
)
|
|
"""
|
|
)
|
|
await db.execute(
|
|
"INSERT INTO tasks.settings (id, public_listing) "
|
|
"SELECT 1, FALSE WHERE NOT EXISTS "
|
|
"(SELECT 1 FROM tasks.settings WHERE id = 1)"
|
|
)
|