09. The database¶

The design of the VeeJay database, from the meaning of the data down to the tests that prove it. It is written as layers, and each layer is produced from the one above it, so a fact is written once and everything else follows from it.

Two rules hold the design together:

1. One place for every fact. The data dictionary is the only place a field is defined, and it lives as data in the database's own blueprint tables. The tables, the checks and the field pages of this site are produced from it and never typed again.
2. The database refuses bad data. Every rule is enforced by the database file itself (types, lists, formats, keys, links and cross-field checks), so it holds whether a value arrives from a person at the screen, from a script or from an import. A rule that exists only in a script or on a screen is not a rule.

9.1 The data model¶

The data model names the things VeeJay knows about and how they relate. It has no columns and no storage detail. If someone who has never seen the database cannot follow this section, the model is wrong.

The things¶

How they relate¶

erDiagram
    Albums }o--|| Artists : "Artist"
    Albums }o--|| Collections : "Collection"
    Artist_Relations }o--|| Artists : "Artist"
    Artist_Relations }o--|| Artists : "Related_Artist"
    Data_Points }o--|| Artists : "Artist"
    Data_Points }o--|| Songs : "Song"
    Files }o--|| Videos : "Video"
    Review }o--|| Artists : "Artist"
    Review }o--|| Songs : "Song"
    Review }o--|| Videos : "Video"
    Song_Platform_IDs }o--|| Platforms : "Platform"
    Song_Platform_IDs }o--|| Songs : "Song"
    Songs }o--o{ Artists : "Additional_Artists"
    Songs }o--|| Albums : "Album"
    Songs }o--|| Artists : "Track_Artist"
    Videos }o--|| Collections : "Collection"
    Videos }o--|| Songs : "Song"

Principles of the model¶

1. One fact, one place. A fact about an artist is stored on the artist and nowhere else. A video points to its song, the song to its album, the album to its artist, so a correction made once is right everywhere.
2. A video is the unit of the library. A row exists because a video exists. A video that is deleted, private or blocked has no row.
3. Audio belongs to its video. Each video has at most one audio file that goes with it. A video with no audio yet is a to-do, not an error. Audio-only files are not part of the library.
4. Names are derived, never stored. The file name and the file tags of a video are built from its row and the rows it points to. There is no stored copy to drift.
5. Identity before facts. Before any fact is written about an artist, the artist's identity is established by evidence and scored. Only verified facts count toward the score and may fill a field.
6. Outside identifiers are first-class. Each outside system (MusicBrainz, Wikipedia, Wikidata, YouTube, Spotify, Apple Music, IMVDb and others) is described once in the vendor list. A stored identifier is checked against its vendor's format, and its link is built from the identifier, never typed.
7. Provenance for every researched value. The source of each researched value is recorded, so bad data can be traced to where it came from.
8. Files can be synchronized. Each file carries its size, content hash and modified time, so that copies on different devices can be compared exactly.

What the model leaves out¶

• Lyric text beyond what is stored for a verified song. Searches are used for identity and spelling only.
• Thumbnails, codecs, frame rates and match scores.
• Which device holds which file. It is added when the synchronization feature is built.

9.2 The data dictionary¶

The data dictionary defines every field of every table once. It is the single source from which the physical schema, the checks, the reference lists and the field pages are produced. Nothing in a lower layer may add, rename or retype a field. A change starts here.

Where it lives. The dictionary is data in the blueprint tables of the database file, not text in a document. Every row has a name, a definition and a purpose, and a row cannot be saved without them. A field row also holds its type, whether it is required, its MP4 and MP3 tag (or None when it is not a file tag), its score weight and a permanent key that never changes when the field is renamed.

The field reference has one page for each table, with every field and its rules. Those pages are generated from the blueprint.

Conventions¶

Names.

• Every table and field is written Title_Case_With_Underscores. An outside vendor keeps its own capitals: YouTube, MusicBrainz, IMDb, IMVDb, TikTok, SoundCloud. SQLite treats names without regard to case, so the dictionary name is used as written, with no second spelling.
• An identifier from an outside system is Vendor_Entity_ID and is stored (Spotify_Artist_ID, MusicBrainz_Release_Group_ID). A link to it is Vendor_Entity_URL and is derived from the identifier. A link is typed only when no identifier exists (Official_Website_URL, Wikipedia_Artist_URL).
• A field that links to another table is named for that table in the singular (Song, Album), with a role in front when a table links twice (Track_Artist).
• Endings carry the unit: _Date (a day), _Year, _Count, _Time (a date and time, always UTC), _Seconds, _Bytes, _Kbps, _Hz, and _Hash_SHA256 for a hash. A yes or no field starts with Is_ or Has_. A choice field is a singular noun for its category (Role, Origin).
• There is no general link field and no general platform field anywhere.

Types.

The rule vocabulary. Every field may carry rules. Each rule has a short kind name, so the schema generator and the tests can treat all fields alike.

Defaults.

• A field is optional unless the dictionary marks it Required.
• A Yes or no field is never empty; it has a default.
• A choice field with a natural starting value has that default, so it is never blank.
• A derived field is read-only everywhere.

The blueprint tables¶

The blueprint is the one part of the database that is written by hand (the first migration). It has seven tables and two views, and every other table is generated from its rows.

• Keys. A _Key never changes, even when the name does. The generator, the scripts and the tests find a field by its key. A name is a label and may be corrected.
• Definition and purpose. The definition says what the thing is; the purpose says why the database has it. Both are required on every row, and neither may be empty or only spaces.
• Tags. MP4_Tag and MP3_Tag name the tag the field is written to in each file format, or hold the word None when the field is not a file tag. A tag is never left blank, so "not decided" cannot be mistaken for "no tag".
• Retirement. A row is retired (Is_Retired), never deleted, once anything has used it. A retired field keeps its key forever, and its key is never reused.

Two views watch the blueprint: blueprint_Drift lists every difference between the blueprint and SQLite's own list of real tables and columns (a real column with no blueprint row, a blueprint row with no real column, a type that does not match), and is empty when the database is true to its blueprint. blueprint_Incomplete lists every row that is not yet complete enough to generate from.

Rules of the blueprint itself. The blueprint is protected by the same machinery as the data.

Rules that apply to specific fields¶

These rules are enforced by the database. Each has a number that the rule catalog in 9.6 and the tests use.

9.3 Reference data¶

Reference data is every list of allowed values that does not change from row to row. A field that must hold one of those values does not store free text. It links to a row of the list, so a value that is not on the list cannot be stored.

How a list is stored¶

Every reference table has a permanent key that never changes, a human-readable label that may be edited, and a flag that retires a value instead of deleting it. A retired value stays on old rows and cannot be chosen for new ones. There is never an "Other" value: a value that fits no list is a missing value, and the right fix is a new value on the list.

Lists to be created¶

Current contents¶

Identifier formats¶

Choice lists¶

Score settings¶

Rules for changing a list¶

1. A list changes only through a migration, never by editing a row in the application's screens.
2. A value is never reused for a different meaning. A value that turns out wrong is retired and a new one added.
3. A value must not overlap another value of the same list or be ambiguous. When two values could both describe a case, the list is revised so they cannot.
4. The reference tables are loaded before any data and are the same in every copy of the database.
5. The country table is loaded from the ISO standard by a migration. When the standard changes, a new migration changes the table.

9.4 The physical schema¶

The physical schema is the real SQLite structure: tables, keys, types and constraints. Nobody writes it by hand, apart from the first migration that creates the blueprint tables themselves. A generator reads the blueprint and produces it, so the schema can never disagree with its definition.

Where it runs¶

The database is one SQLite file inside the VeeJay program. It needs nothing installed and a backup is a copy of the file. The program is the only writer: the web interface, the API and the background worker all reach the file through one storage layer. That layer is the only code that opens the database, and it opens it the same way every time:

Because a program that opens the file in some other way would skip the foreign-key setting, every rule that can be written as a check, a unique key or a trigger is written that way, so it travels inside the file. Only the links between tables depend on the setting. A test (see 9.6) opens the file the wrong way and records exactly what still holds.

Five areas¶

SQLite has no schemas, so the area is the first part of a table's name.

A row moves from staging to core only through a promotion step. It checks the rules a row must meet to be a fact (for example, that its song is identified) and refuses the move if any fail. This is what lets the raw playlist load in one batch while the core tables stay strict.

The tables are named for the area and the dictionary name, such as core_Artists and ref_Countries.

Keys¶

• Every table has a surrogate key, ID INTEGER PRIMARY KEY. It is meaningless and never changes, and all links use it, so renaming or correcting a real-world identifier never breaks a link.
• Real-world identifiers are separate columns with unique keys (MusicBrainz IDs, the YouTube video ID, the vendor key). A blank identifier is NULL, never an empty string, and NULLs do not collide.
• The YouTube video ID is unique across videos and MTV pieces together, enforced by a shared registry table that both write to.

How each rule becomes a constraint¶

Every table is declared STRICT, so a column holds only its declared type.

Patterns without regular expressions. SQLite has no regular-expression operator of its own. A program can register one, but then every other tool that opens the file would either fail or skip the check. So the schema uses only functions that are built into SQLite. A UUID is length(x) = 36 plus a GLOB of the exact shape. "Exactly 22 letters and digits" is length(x) = 22 AND x NOT GLOB '*[^A-Za-z0-9]*'. This is enough for every identifier format in the vendor list.

Constraint names follow one pattern, <table>_<column>_<kind>, so an error message names the exact rule that refused the value.

Audit trail¶

Every table in the core and research areas has Created_Time and Updated_Time, filled by triggers. A trigger copies the old row into an audit table on every update and delete, with the time and the program part that made the change. Nothing is changed or removed without a record that can undo it.

Access¶

SQLite has no database logins. What a person or program may do is decided by the application, which is the only writer. The database still refuses a wrong value however it arrives, because the rules are inside the file.

Derived fields¶

Fields the dictionary calls derived (a link built from an identifier, the expected file name, the album artist, the identity score and level) are not stored by hand. A value computed from the same row is a generated column. A value that needs other rows is a view.

What the generator produces¶

1. The CREATE TABLE, constraint, index and trigger statements, written into migration files.
2. A comment on every table and column carrying its dictionary name, definition and purpose.
3. The reference-table loads, taken from 9.3.
4. A constraint list that the tests use to check that every rule has a test.

The generator is deterministic: the same blueprint always produces the same files.

9.5 Migrations¶

A migration is one numbered script that moves the database from one known state to the next. The database is always the result of running every migration, in order, from empty. The structure is changed in no other way.

VeeJay uses Alembic, the migration tool that goes with SQLAlchemy. Because SQLite cannot alter most constraints in place, Alembic's batch mode rebuilds a table by creating the new one, copying the rows and swapping it in, all inside one transaction.

Rules¶

1. Every change is a migration. Adding a table, a column, a constraint, a reference value or a trigger is a new script. Changing the structure by hand is not allowed.
2. Migrations are ordered. Each one names the one before it, so there is exactly one history.
3. A migration never changes after it has been applied. A mistake is fixed by a new migration. This keeps the history honest and the same everywhere.
4. Each migration runs in one transaction. It either completes or leaves nothing behind.
5. The database records what ran. Alembic keeps the current revision in the file. A migration whose text has changed since it ran stops the run.
6. Forward only, with a copy first. A migration has no automatic undo. Before any migration runs against data, a full copy of the file is made with SQLite's backup function, and the run refuses to start without it. Rolling back means restoring that copy.
7. The database can be rebuilt from nothing. Running every migration on an empty file must produce the current schema, with every reference table loaded. This is tested every time a migration is added.

The first migration is the only one written by hand¶

The first migration creates the blueprint tables, which must exist before anything can be generated from them. Every later migration is produced from the blueprint's rows. The blueprint_Drift view compares the blueprint with SQLite's own list of real columns, and a test fails if they disagree.

Where the files live¶

The migrations live in the veejay-app-code repository beside the generator and the tests, in version control, so a reader can see which blueprint change caused which migration.

How a change flows¶

1. The change is made in the blueprint rows. Each change is audited.
2. The generator compares the blueprint with the schema that the migrations have built so far, and writes the new migration for the difference. A file export of the blueprint is written beside it.
3. A person reads the generated migration. Generated structure is reviewed like code.
4. The copy is made, the migration is applied, and the tests run.
5. The decision and its reason are added to the decision log.

Data migrations¶

A change that must move existing values (for example, splitting a field in two) is written inside one migration as a sequence: first the new structure is added, then the values are copied and verified, and only a later migration removes the old structure. Nothing is dropped in the same step that replaces it.

Two copies of the database¶

There are two: the working database, and a throwaway one that the tests rebuild from empty. Both are produced by the same migrations. A migration is applied to the throwaway one first.

9.6 Rules and tests¶

A rule is finished only when a test proves the database refuses what the rule forbids. This section holds the catalog of every rule and describes the tests that try to break each one.

The principle¶

For every rule there are at least three kinds of test:

A rule also has a fourth test when it can be broken by a change and not only by an insert: the same bad value is tried as an update, and as a bulk import. The wrong value must be refused by every route.

The tests are written with pytest and run against a throwaway file rebuilt from empty by the migrations.

The rule catalog¶

Each rule has a number, the field it protects, its kind, a statement, the tempting wrong value the test uses, and the name of its test. The number never changes. A rule that is withdrawn is marked Retired and is never reused.

The catalog grows with the blueprint. The generator writes the list of constraints it created, and a check compares that list with this catalog and fails if a constraint has no rule number or a rule has no test.

Coverage check¶

A coverage test runs after every migration and fails when:

1. a constraint exists in the database that no rule in the catalog claims,
2. a rule in the catalog has no test,
3. a test is not wired to a rule, or
4. a field that the dictionary marks Required or Unique has no constraint.

Tests of the process, not only the data¶

When a test fails¶

A failing test blocks the change. The fix is to repair the rule or the blueprint and regenerate. The test is never loosened to make it pass.

9.7 The database and the files¶

The database holds every fact about the library. VeeJay writes the file names, the tags in the files and the NFO files from it, so they cannot disagree with each other. A fact exists in one place, and the files are made from it.

When the database and a file disagree¶

The database wins. A file is made from the database, and nothing reads a tag back from a file into it.

VeeJay acts only when a database value changes. It then rewrites that one value in the file and leaves every other tag alone. It never scans the files by itself and never asks you anything about them. A tag you fix by hand in another tool therefore stays until the database changes that same value, and then the database's value replaces it.

Check files¶

The app has a Check files button. When you press it, VeeJay compares every file with the database and lists what differs. You can restore the differences in one click. Nothing happens unless you press the button.

Edits¶

An edit made in the database changes the files, the names and the NFO files that come from it. When a name changes, VeeJay renames the file and updates its path in the database. Changing an album's title or an artist's name renames the files of every song that depends on it.