The data model¶
One podcast is one folder. Inside it, <slug>.podlibre is an ordinary SQLite
database and the audio sits beside it as files. Every path stored is relative to
that folder, so the whole thing can be copied, moved, backed up or put under
version control without anything inside it needing to be rewritten.
A field's value is one or the other: a path relative to the folder, or an
address somewhere else — transcript below holds a URL, and an imported
podcast's cover holds cover.png. podlibre.show.beside() is the one place
that rule is written down, and everything that reads a field back through it:
a value with a scheme in it is left alone, and anything else is joined to the
show's folder.
This page is about what is in the database, and in particular about the question it exists to answer: how one file holds every field of the iTunes DTD, every field of Podcasting 2.0, and whatever fields a plugin invents, without any of them knowing about the others.
The short answer¶
Fields are rows, not columns.
A column can only be added by changing the schema, which means a migration, which means Podlibre has to have heard of the field. A row can be added by anyone at any time. So Podlibre ships two tables — one saying which fields exist and one holding their values — and a plugin adds to the first exactly the way the shipped layers do.
field_def what a field is (key, target) -> type, rss_tag, owner, …
field_value what it holds here (key, target, target_id) -> value
There is no table of iTunes fields and no table of Podcasting 2.0 fields. There
is one table, and owner says where each definition came from.
One consequence is worth stating early, because it is the payoff: the metadata forms are generated from these definitions. A field a plugin declares is editable the moment it is declared, in a group under that plugin's name, with no interface written for it — see editing metadata.
The three layers¶
| Layer | owner |
Count | Added when |
|---|---|---|---|
| iTunes DTD | itunes |
27 | the show file is created, and every time it is opened |
| Podcasting 2.0 | podcast2 |
11 | the show file is created, and every time it is opened |
| A plugin's own | the plugin's id | any | the plugin is loaded |
They are the same kind of thing and they are stored the same way. A plugin's
field is not a lesser field: it has a type, it can have a translation key, it can
name an RSS tag and an ID3 frame, and it is read and written through the same
call as title.
Worked example¶
One episode of Ondes Courtes carrying all three at once, plus its title:
show.set_field(F.EPISODE, episode.id, "title", "Le son du silence")
show.set_field(F.EPISODE, episode.id, "season", 2)
show.set_field(F.EPISODE, episode.id, "transcript", "https://ondescourtes.fr/t.vtt")
show.set_field(F.EPISODE, episode.id, "mood", "calm")
Their definitions, in one table:
| key | target | type | owner | rss_tag | stored |
|---|---|---|---|---|---|
title |
episode | text | itunes | title |
episode.title |
season |
episode | number | itunes | itunes:season |
— |
transcript |
episode | url | podcast2 | podcast:transcript |
— |
mood |
episode | choice | tag_mood | — | — |
Their values, in another:
| key | target | target_id | value |
|---|---|---|---|
mood |
episode | 1 | calm |
season |
episode | 1 | 2 |
transcript |
episode | 1 | https://ondescourtes.fr/t.vtt |
title is deliberately absent from that second table — see stored fields
below. All four are read back the same way, and come back typed:
show.field(F.EPISODE, episode.id, "title") # 'Le son du silence'
show.field(F.EPISODE, episode.id, "season") # 2 -- an int, not '2'
show.field(F.EPISODE, episode.id, "transcript") # 'https://ondescourtes.fr/t.vtt'
show.field(F.EPISODE, episode.id, "mood") # 'calm'
Nothing in that code says which layer a field belongs to, and nothing needs to.
Why the key is (key, target)¶
target is show or episode. It is half the primary key of both tables,
because the same name means different things at the two levels: a show has a
title and so does an episode, a show has a description and so does an episode,
and they are not the same field — different types are allowed, different RSS
tags, different owners.
This is also why field_value carries target as well as target_id. Without
it, episode 1 and the show would collide, since the show is always id 1.
The columns¶
field_def — what a field is¶
| Column | Meaning |
|---|---|
key |
the name used in code. Half the primary key. |
target |
show or episode. The other half. |
type |
one of the twelve below. |
choices |
a JSON array: the permitted values of a choice, or the vocabulary a list is picked from. |
label_key |
a translation key. NULL means use the key itself. |
rss_tag |
where it goes on the way out, if anywhere. |
id3_frame |
likewise, for a tagged audio file. |
owner |
itunes, podcast2, or a plugin id. |
shared |
whether other plugins may read it. |
stored |
set when the value lives in a column instead. |
field_value — what it holds here¶
| Column | Meaning |
|---|---|
key, target |
which field. Together they reference field_def. |
target_id |
which show or episode. The show is always 1. |
value |
the value, as text. |
The foreign key is ON DELETE CASCADE, so removing a definition removes its
values: uninstalling a plugin does not leave values nothing can interpret.
Types¶
Values are stored as text and come back as the type says: number as an int,
bool as a bool, list as a list. The storage is uniform; the reading is not.
A field whose stored text cannot be read as its type comes back as the text,
rather than raising — a damaged value should not stop a show opening.
Stored fields¶
Two fields are an exception. show.title and episode.title are real columns,
because the application orders, lists and names things by them constantly and a
join for every row in a library would be absurd.
stored holds the column — 'episode.title' — and reading or writing that field
goes to the column instead of to field_value. The caller never knows:
show.field(F.EPISODE, id, "title") works regardless, which is the point. A
field is either stored or rowed, never both, so there is no second copy to drift.
A vocabulary behind a field¶
choices on a list field means "these are the values, pick from them". The
one that matters is category on a show: Apple publishes nineteen categories
and some hundred subcategories, reads the first three of them, and matches
nothing spelled any other way. The list ships as data — fields.APPLE_CATEGORIES
— so the metadata form offers it and the feed writer nests a subcategory inside
its parent, neither of them keeping a list of its own. A category is held as one
string, "Arts > Books", because a field holds values and not trees.
A value the vocabulary does not contain is kept rather than dropped. A show imported from somebody else's feed is not a show to edit quietly.
Anti-shadowing¶
A plugin may not redefine a field that another layer already owns. Declaring
title on episode is refused at load time rather than silently replacing the
iTunes definition. A plugin that wants its own meaning uses its own key.
This is checked when the plugin's fields are registered, which is the moment
its podlibre_fields hook is called. A step naming a field in requires that
nothing defines is not an error: that step is simply not ready, which is the
truth, and the workflow says which field is missing.
The other tables¶
The field model is for metadata. Everything with structure of its own gets a table, because rows of text would lose it.
| Table | Holds |
|---|---|
show |
the one show. id is checked to be 1. |
episode |
episodes. slug is also the folder name under episodes/. |
track, clip |
the timeline: a clip names a source file and where it sits. |
segment, speaker |
the transcript, and who is speaking. |
chapter |
chapters, with optional image and link. |
bookmark |
a place somebody wanted to come back to. |
dictionary_rule |
corrections applied to a transcript, in order. |
mark |
steps the user said were done. See below. |
workflow_state |
which workflow this show uses, and any it has of its own. |
field_def, field_value |
everything above. |
schema_version |
one row, one integer. |
Audio is never in the database. A clip names a file beside it.
Completion is derived, not stored¶
mark holds only what a person said by hand. Everything else about a step's
state is worked out by reading the data when the workflow is drawn: audio exists
or it does not, there are segments or there are not.
A stored flag can lie. Copy a show to another machine, delete the audio, and a flag would still say the audio was imported. Reading the data cannot be wrong about the data. It also means an automatic step and the screen are looking at the same thing, so they can never disagree.
See the workflow for what is made of that.
Migrations¶
schema_version holds one integer. Opening a file whose version is lower runs
the migrations between, in order, in one transaction. Opening a file from a
newer Podlibre is refused rather than guessed at.
Each migration is a function from one version to the next, and the test suite checks that a migrated file matches a freshly created one table for table — so a migration that forgets something fails the suite rather than shipping.
The vocabulary is migrated too¶
The shipped definitions are written into the show file when it is made, so a
show made by an older build knew only the fields that build had. Opening a show
therefore matches its two shipped layers against this build's: a field added
since arrives, a definition that has changed — Apple's category list, say, added
to category long after the first shows existed — is replaced, and a field
Podlibre no longer defines is taken out with its values, which is what the
cascade above is for.
Only the shipped layers. A plugin's fields are the plugin's, and a show created without the Podcasting 2.0 layer is not given one. A show already matching is not written to at all, so opening a podcast is not a change to it.