Skip to content

Instantly share code, notes, and snippets.

@dmgerman
Created May 21, 2024 07:08
Show Gist options
  • Save dmgerman/f8679f7a11bbdd748f648dd3f30151c5 to your computer and use it in GitHub Desktop.
Save dmgerman/f8679f7a11bbdd748f648dd3f30151c5 to your computer and use it in GitHub Desktop.
Test org-roam db
#!/bin/bash
time sqlite3 /tmp/org-roam.db | wc <<EOF
SELECT
id,
file,
filetitle,
"level",
todo,
pos,
priority ,
scheduled ,
deadline ,
title,
properties ,
olp,
atime,
mtime,
'(' || group_concat(tags, ' ') || ')' as tags,
aliases,
refs
FROM
(
SELECT
id,
file,
filetitle,
"level",
todo,
pos,
priority ,
scheduled ,
deadline ,
title,
properties ,
olp,
atime,
mtime,
tags,
'(' || group_concat(aliases, ' ') || ')' as aliases,
refs
FROM
(
SELECT
nodes.id as id,
nodes.file as file,
nodes."level" as "level",
nodes.todo as todo,
nodes.pos as pos,
nodes.priority as priority,
nodes.scheduled as scheduled,
nodes.deadline as deadline,
nodes.title as title,
nodes.properties as properties,
nodes.olp as olp,
files.atime as atime,
files.mtime as mtime,
files.title as filetitle,
tags.tag as tags,
aliases.alias as aliases,
'(' || group_concat(RTRIM (refs."type", '"') || ':' || LTRIM(refs.ref, '"'), ' ') || ')' as refs
FROM nodes
LEFT JOIN files ON files.file = nodes.file
LEFT JOIN tags ON tags.node_id = nodes.id
LEFT JOIN aliases ON aliases.node_id = nodes.id
LEFT JOIN refs ON refs.node_id = nodes.id
GROUP BY nodes.id, tags.tag, aliases.alias )
GROUP BY id, tags )
GROUP BY id
EOF
@akashpal-21
Copy link

time sqlite3 /tmp/org-roam.db <<EOF | wc -l

EOF should be before wc or else we are piping the sqlite string into wc and the sqlite query never does its job.
Please rectify

Best

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment