Music DB

Introduction

Just putting my music in a DB

Database Setup

• Create User
• Create DB
• Grant Privileges
• Create Schema

CREATE TABLE music_metadata (
    path TEXT PRIMARY KEY,
    extension TEXT,
    song TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    format TEXT,
    duration TEXT,
    size BIGINT,
    bitrate TEXT,
    writing_library TEXT,
    has_cover BOOLEAN,
    mtime BIGINT,
    ctime BIGINT,
    btime BIGINT,
    mediainfo_empty BOOLEAN
);

Injest Data

Here is the script to read the json

#!/usr/bin/env bash
set -euo pipefail

JSONL="/tmp/music_metadata.jsonl"
TSV="/tmp/music_metadata.tsv"

# Convert JSONL to TSV for COPY
jq -r '
    [
        .path,
        .extension,
        .song,
        .artist,
        .album,
        .year,
        .genre,
        .format,
        .duration,
        (.size | tonumber),
        .bitrate,
        .writing_library,
        (.has_cover == "true"),
        (.mtime | tonumber),
        (.ctime | tonumber),
        (if .btime == "" then null else (.btime | tonumber) end),
        (.mediainfo_empty == "true")
    ]
    | @tsv
' "$JSONL" > "$TSV"

# Load into Postgres with upsert
psql <<EOF
CREATE TABLE IF NOT EXISTS music_metadata (
    path TEXT PRIMARY KEY,
    extension TEXT,
    song TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    format TEXT,
    duration TEXT,
    size BIGINT,
    bitrate TEXT,
    writing_library TEXT,
    has_cover BOOLEAN,
    mtime BIGINT,
    ctime BIGINT,
    btime BIGINT,
    mediainfo_empty BOOLEAN
);

CREATE TEMP TABLE music_metadata_stage (LIKE music_metadata);

COPY music_metadata_stage FROM '$TSV' WITH (FORMAT text);

INSERT INTO music_metadata
SELECT * FROM music_metadata_stage
ON CONFLICT (path) DO UPDATE SET
    extension = EXCLUDED.extension,
    song = EXCLUDED.song,
    artist = EXCLUDED.artist,
    album = EXCLUDED.album,
    year = EXCLUDED.year,
    genre = EXCLUDED.genre,
    format = EXCLUDED.format,
    duration = EXCLUDED.duration,
    size = EXCLUDED.size,
    bitrate = EXCLUDED.bitrate,
    writing_library = EXCLUDED.writing_library,
    has_cover = EXCLUDED.has_cover,
    mtime = EXCLUDED.mtime,
    ctime = EXCLUDED.ctime,
    btime = EXCLUDED.btime,
    mediainfo_empty = EXCLUDED.mediainfo_empty;
EOF

echo "Database load complete."