Skip to content

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

text  longtext  html  url  email  number  duration  date  bool  choice  list  image

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.