todo.sql
is a SQL-y language to select todos. You can use it to create new
views for tudor-tui
/tudor-cli
or to perform analytics on your
todo.txt
/done.txt
/someday.txt
.
Examples
Note, these queries work, but if you want full control over contexts and projects, see
Sets in todo.sql
-- NOTE: field names:
-- ccontext = canonical ctx (alphabetically first)
-- cproject = canonical prj (alphabetically first)
-- NOTE: table names:
-- - todo_txt is the name of the "table" holding all the todos
-- - done_txt
-- - someday_txt
-- 📮 inbox
select *
from todo_txt
where ccontext is null
-- 🚀 active tasks `active_tasks`
select *
from todo_txt
where
ccontext not in ('@@delegated', '@@blocked', '@@maybe')
and (
threshold_date is null
or threshold_date <= date('today')
)
-- 👓 selecting from other views
select *
from active_tasks
where project in ('+proj1', '+proj2')
-- active tasks by @context
select *
from active_tasks
-- NOTE: only single grouping supported;
-- NOTE: group behaves differently than SQL, it determines the sections in the
-- main view;
group by ccontext
-- the todos are sorted by within section
order by priority
-- active tasks by +project
select *
from active_tasks
group by project
order by priority
-- 🔮 future tasks
select * from todo_txt
-- NOTE: this works because @@blocked would be the canonical ctx for something
-- tagged with a normal tag (e.g., @home)
where ccontext in ('@@blocked', '@@maybe')
or threshold_date > date('today')
-- 🛑 blocked tasks
select *
from todo_txt
where ccontext in ('@@blocked')
or is_blocked = true
-- ✋ blocking tasks
select *
from todo_txt
where is_blocking_for is not null
order by length(is_blocking_for) desc
-- list all projects
select distinct project
from todo_txt
-- active projects
select distinct project
from todo_txt
where ccontext not in ('@@blocked')
-- 👻 hidden todos
select *
from todo_txt
where is_hidden = true
-- **`TODO:`** ??? blocked projects (i.e. projects where all tasks are stuck)
-- selecting from other files
select *
from done_txt -- it just looks for filenames where s/_/./g
-- accessible fields
-- ???: maybe we just return everything all the time for now?
select
( full_text -- full, raw todo string
, is_completed
, is_blocking_for -- set: `id`s of tasks that depend on this
, is_blocked -- does not depend on incomplete todo(s)
, creation_date
, completion_date
, priority
, ccontext -- canonical ctx (alphabetically first)
, contexts -- set: all ctxs
, cproject -- canonical prj
, projects -- set: all prjs
, due_date
, threshold_date
, is_hidden
)
from todo_txt
Constraints
- Only one due date per task
- Only one threshold date per task
- if multiple prj/ctx in a given task, we take first one alphabetically as
canonical one to match with Simpletask. We call this the canonical
ctx/proj (
ccontext
andcproject
)
🧱 Building aids
- Implement a PostgreSQL Foreign Data Wrapper for
todo.txt
- look at multicorn to implement
- consider using a full blown SQL parser