markdown-to-sqlite
markdown-to-sqlite copied to clipboard
simonw
how to use YAML for extra fields?
In the readme you mention something about embedded yaml and extra fields, but I cannot see anything documented about it. How does it work? What do I need? I would love to have more data in my database instead of having to alter the generated database directly.
I love this tool, thank you!
Hi, if I may, as I've just discovered this tool, do you mean something like described in pandoc documentation and reported in this short example?
file : a.md, containing embedded YAML for title, author, etc.
---
title: Test title
date: 2021-08-23
author:
- Bob
- Alice
- And and some other authors
some_key: |
and some very
long multiline
value.
tree_value:
sub_tree :
- foo
- bar
- baz
---
# title
## subtitle 1
para 1.
## subtitle 2
para 2.
then
> markdown-to-sqlite.exe md.sqlite markdown_table a.md
.schema
CREATE TABLE [markdown_table] (
[_id] TEXT PRIMARY KEY,
[_path] TEXT,
[text] TEXT,
[html] TEXT,
[title] TEXT,
[date] TEXT,
[author] TEXT,
[some_key] TEXT,
[tree_value] TEXT
);
.mode line
select * from markdown_table;
_id = 5d48a79a3e66a0b67d03c582501493d256004f1f
_path = a.md
text = # title
## subtitle 1
para 1.
## subtitle 2
para 2.
html = <h1>title</h1>
<h2>subtitle 1</h2>
<p>para 1.</p>
<h2>subtitle 2</h2>
<p>para 2.</p>
title = Test title
date = 2021-08-23
author = ["Bob", "Alice", "And and some other authors"]
some_key = and some very
long multiline
value.
tree_value = {"sub_tree": ["foo", "bar", "baz"]}
yaml metadata are serialized as JSON.
HTH
Yeah, pretty much this is what I was not getting, many thanks!
It wasn't clear to me how I could: First, embed YAML into the markdown, and second, what would the result of this would be. It wouldn't have occurred to me to look at the Pandoc documentation!
This all makes a lot more sense now.
Happy I could help you.
AFAIK, metadata block should be terminated by --- but not ... as pandoc allows.
By the way, have you come across this post or SQLite documentation for automatically extracting data from json which is pretty relevant here. I changed the previous SQLite schema:
SQLite> alter table markdown_table add column extra_info generated always as (json_extract(tree_value, '$.extra_info')) virtual;
I created another markdown (b.md):
---
title: Test 2
date: 2021-08-23
author:
- me
- etc
some_key: |
and some very
long multiline
value.
tree_value:
extra_info:
"I am an extra info!"
---
# title b
## subtitle b
para b.
> markdown-to-sqlite.exe md.sqlite markdown_table b.md
then sqlite3 md.sqlite (note the extra_info column):
sqlite>.mode line
sqlite> select * from markdown_table where title = 'Test 2';
_id = bb801cb4e571ec0bc7a6ad6d316dacf27c7b107a
_path = b.md
text = # title b
## subtitle b
para b.
html = <h1>title b</h1>
<h2>subtitle b</h2>
<p>para b.</p>
title = Test 2
date = 2021-08-23
author = ["me", "etc"]
some_key = and some very
long multiline
value.
tree_value = {"extra_info": "I am an extra info!"}
extra_info = I am an extra info!
And all the thanks to Simon Willison!