Nat Knight

#release #sqlite3

I wrote a spec for a SQLite-based todo database and just release version 0.1.

todo.txt is an obvious inspiration, but I wanted to put my data in SQLite for a couple of reasons:

• SQLite is very stable, nearly as stable as plain text.
• Having your todos in a database opens up possibilities for querying, viewing, and extending that data much more easily than if it's in plain text.
• Having the data in SQLite in particular means multiple apps can interact with the database simultaneously, so I can have a CLI, a web app, and automation scripts touching the same todo list without risking corruption or data loss.

Now that the database schema is ready I just have to build the rest of the app...

The schema (not counting a tagging extension) in full:

-- todo-sqlite3
-- https://github.com/nathanielknight/todo-sqlite3

-- Foreign Key constraints are required.
PRAGMA foreign_keys = ON;


-- Core Items table
CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    -- Every item has a title (not necessarily unique)
    title TEXT NOT NULL,
    -- Body can be markdown, plaintext, asciidoc, etc.
    body TEXT,
    -- Items can be archived to support soft-deletion
    is_archived BOOLEAN NOT NULL DEFAULT 0,
    archived_status_changed_at TIMESTAMP,
    -- Items have automatically updating created_at and changed_at timestamps
    created_at TIMESTAMP NOT NULL DEFAULT (unixepoch('now', 'subsec')),
    changed_at TIMESTAMP NOT NULL DEFAULT (unixepoch('now', 'subsec'))
);

-- The changed_at and archived_status_changed_at fields are automatically updated.
CREATE TRIGGER update_items_changed_at 
AFTER UPDATE ON items
BEGIN
    UPDATE items SET changed_at = unixepoch('now', 'subsec')
    WHERE id = NEW.id;
END;

CREATE TRIGGER update_archived_status_timestamp
AFTER UPDATE OF is_archived ON items
WHEN NEW.is_archived != OLD.is_archived
BEGIN
    UPDATE items 
    SET archived_status_changed_at = unixepoch('now', 'subsec')
    WHERE id = NEW.id;
END;