sense-bases
A
file is YAML: filters selecting notes, formulas computing values, and views ordering
and grouping them. Obsidian evaluates it against its own metadata cache; sense holds the same
data in SQL tables. Every Bases construct that selects or computes rows has a SQL equivalent.
What has none is presentation (
, card layout), those change pixels, not rows, so
a translation loses nothing by ignoring them.
Translate one view to one query: base-level
AND the view's
, the view's
/
/
as the SELECT list, ORDER BY, and LIMIT. A base with four views becomes
four queries; saving them under the base's name in
's
block keeps
them runnable as
. The general query surface is the
skill; this one only
maps Bases constructs onto it.
Properties
| Bases | sense | notes |
|---|
| / bare | | frontmatter column, same name |
| | the Name column: no directory, no extension |
| a name with extension | | the suffix arg is Unix basename's: present, stripped |
| | for |
| | sense indexes only markdown |
| | bytes |
| datetime(f._mtime / 1000, 'unixepoch')
| stored as ms |
| datetime(f._ctime / 1000, 'unixepoch')
| see the caveat below |
| the table | frontmatter + inline , deduplicated, Obsidian's grain |
| | one row per distinct written target |
| | |
| | |
| a bound | see below |
is the filesystem's creation time. A
or a copy resets it, so on a
tree that arrived as a checkout, every ctime is the checkout date and age formulas are wrong
about authorship without being wrong about the filesystem.
warns when modification
times have this shape; creation times share it.
Predicates
| Bases | sense |
|---|
| | same |
| | |
| / / filter blocks | parenthesized / / |
| (f.field IS NULL OR f.field IN ('', '[]', '[null]'))
|
| EXISTS (SELECT 1 FROM tags WHERE tags.path = f.path AND (tag = 'book' OR tag LIKE 'book/%'))
|
| EXISTS (SELECT 1 FROM links WHERE src = f.path AND dst = 'Note.md')
|
| EXISTS (SELECT 1 FROM json_each(f.list) WHERE value = x)
|
| |
| the EXISTS with |
| no SQLite regex; / cover anchored and wildcard shapes |
| 's own column: ... FROM json_each(f.field) j WHERE j.type = 'object'
|
A field no note in the tree declares has no column at all, so a filter naming it errors with
instead of treating every row as empty. Obsidian's evaluator returns empty
for unknown properties; SQL does not. Dropping the clause states the same thing the error
did, and
SELECT name FROM pragma_table_info('frontmatter')
lists what exists.
has four true cases because empty is stored three ways: NULL (key absent),
(empty string value),
(a list written
), and
(a list key above a bare
). Obsidian's
is true for all of them;
alone finds only the first.
The IN list is the whole test.
is not: it reads
as length
1 and throws on plain strings. The same trap inside a list:
hands a string member
to
as plain text, so
throws
on it; the scan's own
column is the discriminator.
compares against a link value. In frontmatter, a list of links
holds the written text, so the
comparison value is the literal
.
compares resolved paths:
is the resolved target (
for dead
links), so the comparison value is the target's path, not its display name.
sense's own
is looser than both
variants: exact membership on JSON
arrays but substring on strings, so
also matches
. It
reads shorter when the field is known to be a list; the
form is the exact
translation.
Formulas
Formulas are SELECT expressions. The Bases functions map onto SQLite's:
| Bases | sense |
|---|
| or |
| / | / |
| |
| julianday(date1) - julianday(date2)
|
(now() - acquired).months
| (julianday('now') - julianday(f.acquired)) / 30.44
|
| / | / |
| |
| |
| json_array_length(f.list)
|
list(a).filter(list(b).containsAny(value)).unique()
| an EXISTS-joined subquery; worked example in EXAMPLES.md |
Bases durations are typed; SQL date arithmetic is julianday day-fractions. A formula chaining
duration fields (
) flattens to arithmetic on the julianday difference.
A formula referencing another formula becomes a CTE layer: SQL cannot read a SELECT alias in
the same SELECT list, so each dependency level computes its formulas as columns and the next
level reads them (
WITH t AS (SELECT ..., <level-1 formulas> FROM frontmatter) SELECT ..., <level-2 formulas> FROM t
). A five-formula chain is however many
levels it has, not five
CTEs. Formulas that only read base columns share one layer.
Views
-
(multi-key, each with direction) ->
.
->
.
-
does not collapse rows. Obsidian shows every row bucketed under headers with
per-group summaries. The SQL producing the same rows and numbers is ordering plus window
functions, not GROUP BY:
sql
SELECT f.path, f.status, f.days,
AVG(f.days) OVER (PARTITION BY f.status) AS group_avg,
COUNT(*) OVER (PARTITION BY f.status) AS group_n
FROM frontmatter f WHERE f.days IS NOT NULL
ORDER BY f.status, f.days DESC
A collapsed one-row-per-group report is plain
. That is a different result than
the Bases view shows.
-
View
(Sum, Average, Median via ordering, Unique, Filled, Checked) are the
matching aggregates, windowed as above to keep the rows, or a separate aggregate query.
is the note the base is evaluated against: the embedding note, or Obsidian's active
pane. sense has no pane, so the caller supplies the path as a bound parameter:
, or a saved query run as
. A CTE keeps it single-bind:
sql
WITH me AS (SELECT ? AS p)
and every
expression joins
. The full Related.base translation in
EXAMPLES.md is the worked case: link overlap, mutual-link filters, and the shared-target list
via
, all from one bound path.
Not translatable, and why it costs nothing
- , card/table/list/map chrome: pixel layout over the same rows.
- Obsidian choosing from the active pane: there is no pane; the path parameter is the
resolution.
- Live re-evaluation as files change: every sense query reconciles against the filesystem
first, so the freshness is already there; nothing re-renders on its own.