-- structure
CREATE TABLE todo (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
priority INTEGER NOT NULL DEFAULT 2 CHECK(priority >= 1 AND priority <= 3),
created_on DATETIME NOT NULL,
due_on DATETIME NOT NULL,
last_modified_on DATETIME NOT NULL,
title TEXT NOT NULL,
description TEXT,
comment TEXT,
status TEXT NOT NULL CHECK(status IN('PENDING', 'DONE', 'VOIDED')),
deleted INTEGER NOT NULL DEFAULT 0 CHECK(deleted IN(0, 1))
);
CREATE INDEX idx_todo_priority ON todo(priority);
CREATE INDEX idx_todo_due_on ON todo(due_on);
CREATE INDEX idx_todo_status ON todo(status);
CREATE INDEX idx_todo_deleted ON todo(deleted);
-- data
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 2, '2011-10-20 11:00:00', '2015-10-20 00:00:00', '2099-10-20 11:00:00', 'Clean the house', 'Clean the whole house, ideally including garden.', NULL, 'PENDING', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 2, '2011-09-02 18:24:00', '2011-10-05 15:00:00', '2011-10-07 08:26:49', 'Cut the lawn', 'Cut the lawn around the house.', NULL, 'PENDING', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 3, '2011-09-15 09:30:00', '2012-01-01 00:00:00', '2011-10-19 10:25:00', 'Buy a car', 'Choose the best car to buy and simply buy it.', 'New BMW bought.', 'DONE', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 3, '2011-09-27 17:33:00', '2011-11-01 00:00:00', '2011-10-11 13:48:00', 'Open a new bank account', NULL, 'Not needed.', 'VOIDED', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 1, '2010-08-12 08:17:00', '2010-09-01 00:00:00', '2011-10-07 08:06:40', 'Finish all the exams', NULL, NULL, 'DONE', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 2, '2011-10-02 10:38:36', '2011-10-04 12:00:00', '2011-10-03 13:26:48', 'Send a letter to my sister', 'Send a letter to my sister with important information about what needs to be done.', 'Letter not needed, I called her.', 'VOIDED', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 1, '2010-04-07 17:28:52', '2010-07-01 00:00:00', '2010-05-12 11:47:00', 'Book air tickets', 'Book air tickets to Canary Islands, for 3 people.', '', 'PENDING', 0);
INSERT INTO todo (id, priority, created_on, last_modified_on, due_on, title, description, comment, status, deleted)
VALUES (NULL, 2, '2011-10-07 10:44:47', '2011-11-01 00:00:00', '2011-10-24 10:46:14', 'Pay electricity bills', 'Pay electricity bills for the house.', 'Paid.', 'DONE', 0);
|
Download
