todos

SELECT
  (SELECT COUNT() FROM todos WHERE completed = 0) AS activeCount,
  (SELECT EXISTS (SELECT 1 FROM todos WHERE completed = 1)) as hasCompleted,
  (SELECT EXISTS (SELECT 1 FROM todos)) as hasAny

SELECT rowid, title, completed FROM todos

SELECT
  (SELECT COUNT() FROM todos WHERE completed = 0) AS activeCount,
  (SELECT EXISTS (SELECT 1 FROM todos WHERE completed = 1)) as hasCompleted,
  (SELECT EXISTS (SELECT 1 FROM todos)) as hasAny

COMMIT

INSERT INTO todos (title, completed) VALUES ('Write notes on fluid dynamics.', 0)

INSERT INTO todos (title, completed) VALUES ('Experiment with new painting techniques.', 0)

INSERT INTO todos (title, completed) VALUES ('Research the mechanics of bird flight.', 1)

INSERT INTO todos (title, completed) VALUES ('Finish sketch of the Last Supper.', 1)

INSERT INTO todos (title, completed) VALUES ('Design a new flying machine concept.', 1)

BEGIN

SELECT EXISTS (SELECT 1 FROM todos)

CREATE TEMPORARY TRIGGER update_completed_trigger
  AFTER UPDATE OF completed ON todos
  WHEN old.completed <> new.completed
  BEGIN SELECT updated_completed_fn(new.rowid, new.completed); END

CREATE TEMPORARY TRIGGER update_title_trigger
  AFTER UPDATE OF title ON todos
  WHEN old.title <> new.title
  BEGIN SELECT updated_title_fn(new.rowid, new.title); END

CREATE TEMPORARY TRIGGER delete_trigger AFTER DELETE ON todos
  BEGIN SELECT deleted_item_fn(old.rowid); END

CREATE TEMPORARY TRIGGER insert_trigger AFTER INSERT ON todos
  BEGIN SELECT inserted_item_fn(new.rowid, new.title, new.completed); END

CREATE INDEX IF NOT EXISTS completed_index ON todos (completed);

CREATE TABLE IF NOT EXISTS todos (
	title TEXT NOT NULL,
	completed INTEGER NOT NULL DEFAULT 0,
	CHECK (title <> ''),
	CHECK (completed IN (0, 1))
);