🗒 Sqlish

Metadata

Key	Value
👨‍👩‍👧‍👦 Parents	Project Management Blog (432137cb)
🏷 Tags
👤 Author	hexylena
Created	2024-07-31 13:28:00
Modified	2024-08-12 14:04:10

Contents

Running SQL(ish) Queries on the CLI

see, insane ideas.

$ ./pm sqlish 'select created, short_id, title, type, icon from asdf group by 'type' order by created limit 3'        
time=2024-07-31T15:27:43.032+02:00 level=INFO msg=LoggerInitialized
time=2024-07-31T15:27:43.038+02:00 level=INFO msg="Starting pm" pm.mod=main
time=2024-07-31T15:27:43.038+02:00 level=INFO msg=LoadNotes pm.mod=adapter
time=2024-07-31T15:27:43.043+02:00 level=INFO msg="Executing Command" pm.mod=main
LIMIT 3
Select=[created, short_id, title, type, icon] From=[asdf] Where=[] GroupBy=[type] OrderBy=[created ASC] Limit=[3]
[created short_id title type icon]
log
┌──────────────────┬──────────────────┬──────────────────┬──────────────────┬──────────────────┐
│     created      │     short_id     │      title       │       type       │       icon       │
├──────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┤
│ 1722338778       │ ed8ea678         │ Working on the   │ log              │ ⏰               │
│                  │                  │ project manager  │                  │                  │
│ 1722415301       │ 25c22c88         │ Improving PM     │ log              │ ⏰               │
└──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┘
project
┌──────────────────┬──────────────────┬──────────────────┬──────────────────┬──────────────────┐
│     created      │     short_id     │      title       │       type       │       icon       │
├──────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┤
│ 1706202061       │ 883892c6         │ Personal Project │ project          │ 📁               │
│                  │                  │ Manager          │                  │                  │
└──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┘
time=2024-07-31T15:27:43.045+02:00 level=INFO msg="Shutting Down and Saving Notes" pm.mod=main
time=2024-07-31T15:27:43.045+02:00 level=INFO msg=SaveNotes pm.mod=adapter

Which can be run in the web, too

CREATED	SHORT_ID	TITLE	TYPE	ICON
Project
2024-01-25 17:01:01	883892c6	📁 Personal Project Manager	project	📁
Person
2024-02-01 17:46:48	13bcdf42	hexylena	person	https://github.com/hexylena.png
Note
2024-06-05 19:18:30	842736ea	🗒 Markdown Testing	note	🗒

it has some notable bugs/oddities like:

• (NOW FIXED) non-deterministic sorting.
• we do nothing with the table name? should we?

More Fun Things

see 🗒 Alternative Views

From Table

perhaps we could use FROM TABLE as an implicit

where parent = 'TABLE'

or maybe

where ancestors includes 'TABLE'

Limits of the -Ish

this would be a totally valid thing to want to query but currently impossible

./pm sqlish "SELECT title, pretty_duration(end_time - start_time) FROM tasks

is there an argument for loading the data into an (in memory?) sql database and use the real query engine to make these queries and calculate these fields?

SELECT end_time - start_time AS duration

doesn’t seem to be extractable. So, maybe it is worth it to temporarily load + query there?