codey/api
1
0
Fork 0
Code Issues 1 Pull Requests Activity

Utilize LRCLib SQLite Dumps for Lyric Retrieval #34

New Issue
Open
opened 2025-11-21 07:34:45 -05:00 by codey · 1 comment
codey commented 2025-11-21 07:34:45 -05:00
Owner
Copy Link

One of the currently used lyric search sources, LRCLib, provides periodic database dumps, in the form of gzip'd SQLite DBs.

Current goal: Create independent API endpoint(s) for cached lyric retrieval against the LRCLib database. Fetch and import new database dumps provided from LRCLib (after validating database structure between files). Current gzip filesize is at 17.2GB.

Database Structure (2 relevant tables: tracks, lyrics)

tracks

Query used to create this table

CREATE TABLE tracks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
name_lower TEXT,
artist_name TEXT,
artist_name_lower TEXT,
album_name TEXT,
album_name_lower TEXT,
duration FLOAT,
last_lyrics_id INTEGER,
created_at DATETIME,
updated_at DATETIME,
FOREIGN KEY (last_lyrics_id) REFERENCES lyrics (id),
UNIQUE(name_lower, artist_name_lower, album_name_lower, duration)
);
;
CREATE INDEX idx_tracks_name_lower ON tracks (name_lower);
CREATE INDEX idx_tracks_artist_name_lower ON tracks (artist_name_lower);
CREATE INDEX idx_tracks_album_name_lower ON tracks (album_name_lower);
CREATE INDEX idx_tracks_duration ON tracks (duration);
CREATE INDEX idx_tracks_last_lyrics_id ON tracks (last_lyrics_id);
CREATE TRIGGER tracks_ai AFTER INSERT ON tracks
BEGIN
INSERT INTO tracks_fts (rowid, name_lower, album_name_lower, artist_name_lower)
VALUES (new.id, new.name_lower, new.album_name_lower, new.artist_name_lower);
END;
CREATE TRIGGER tracks_au AFTER UPDATE ON tracks
BEGIN
INSERT INTO tracks_fts(tracks_fts, rowid, name_lower, album_name_lower, artist_name_lower)
VALUES('delete', old.id, old.name_lower, old.album_name_lower, old.artist_name_lower);
INSERT INTO tracks_fts (rowid, name_lower, album_name_lower, artist_name_lower)
VALUES (new.id, new.name_lower, new.album_name_lower, new.artist_name_lower);
END;
CREATE TRIGGER tracks_ad AFTER DELETE ON tracks
BEGIN
INSERT INTO tracks_fts(tracks_fts, rowid, name_lower, album_name_lower, artist_name_lower)
VALUES('delete', old.id, old.name_lower, old.album_name_lower, old.artist_name_lower);
END;

lyrics

Query used to create this table

CREATE TABLE lyrics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
plain_lyrics TEXT,
synced_lyrics TEXT,
track_id INTEGER,
has_plain_lyrics BOOLEAN,
has_synced_lyrics BOOLEAN,
instrumental BOOLEAN,
source TEXT,
created_at DATETIME,
updated_at DATETIME,
FOREIGN KEY (track_id) REFERENCES tracks (id)
);
CREATE INDEX idx_lyrics_track_id ON lyrics (track_id);
CREATE INDEX idx_lyrics_has_plain_lyrics ON lyrics (has_plain_lyrics);
CREATE INDEX idx_lyrics_has_synced_lyrics ON lyrics (has_synced_lyrics);
CREATE INDEX idx_lyrics_source ON lyrics (source);
CREATE INDEX idx_lyrics_created_at ON lyrics (created_at);
CREATE TRIGGER set_tracks_last_lyrics_id
AFTER INSERT ON lyrics
BEGIN
UPDATE tracks SET last_lyrics_id = NEW.id WHERE tracks.id = NEW.track_id;
END;

Example Query

SELECT tracks.artist_name, tracks.name, lyrics.plain_lyrics, lyrics.synced_lyrics FROM tracks INNER JOIN lyrics ON lyrics.id = tracks.last_lyrics_id WHERE artist_name LIKE "AFI" and name LIKE "17 Crimes" LIMIT 1

image.png

One of the currently used lyric search sources, LRCLib, provides periodic database dumps, in the form of [gzip'd SQLite DBs.](https://lrclib.net/db-dumps) Current goal: Create **independent** API endpoint(s) for cached lyric retrieval against the LRCLib database. Fetch and import new database dumps provided from LRCLib (after validating database structure between files). Current gzip filesize is at `17.2GB`. # Database Structure (2 relevant tables: `tracks`, `lyrics`) ## `tracks` Query used to create this table ```sql CREATE TABLE tracks ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, name_lower TEXT, artist_name TEXT, artist_name_lower TEXT, album_name TEXT, album_name_lower TEXT, duration FLOAT, last_lyrics_id INTEGER, created_at DATETIME, updated_at DATETIME, FOREIGN KEY (last_lyrics_id) REFERENCES lyrics (id), UNIQUE(name_lower, artist_name_lower, album_name_lower, duration) ); ; CREATE INDEX idx_tracks_name_lower ON tracks (name_lower); CREATE INDEX idx_tracks_artist_name_lower ON tracks (artist_name_lower); CREATE INDEX idx_tracks_album_name_lower ON tracks (album_name_lower); CREATE INDEX idx_tracks_duration ON tracks (duration); CREATE INDEX idx_tracks_last_lyrics_id ON tracks (last_lyrics_id); CREATE TRIGGER tracks_ai AFTER INSERT ON tracks BEGIN INSERT INTO tracks_fts (rowid, name_lower, album_name_lower, artist_name_lower) VALUES (new.id, new.name_lower, new.album_name_lower, new.artist_name_lower); END; CREATE TRIGGER tracks_au AFTER UPDATE ON tracks BEGIN INSERT INTO tracks_fts(tracks_fts, rowid, name_lower, album_name_lower, artist_name_lower) VALUES('delete', old.id, old.name_lower, old.album_name_lower, old.artist_name_lower); INSERT INTO tracks_fts (rowid, name_lower, album_name_lower, artist_name_lower) VALUES (new.id, new.name_lower, new.album_name_lower, new.artist_name_lower); END; CREATE TRIGGER tracks_ad AFTER DELETE ON tracks BEGIN INSERT INTO tracks_fts(tracks_fts, rowid, name_lower, album_name_lower, artist_name_lower) VALUES('delete', old.id, old.name_lower, old.album_name_lower, old.artist_name_lower); END; ``` ## `lyrics` Query used to create this table ```sql CREATE TABLE lyrics ( id INTEGER PRIMARY KEY AUTOINCREMENT, plain_lyrics TEXT, synced_lyrics TEXT, track_id INTEGER, has_plain_lyrics BOOLEAN, has_synced_lyrics BOOLEAN, instrumental BOOLEAN, source TEXT, created_at DATETIME, updated_at DATETIME, FOREIGN KEY (track_id) REFERENCES tracks (id) ); CREATE INDEX idx_lyrics_track_id ON lyrics (track_id); CREATE INDEX idx_lyrics_has_plain_lyrics ON lyrics (has_plain_lyrics); CREATE INDEX idx_lyrics_has_synced_lyrics ON lyrics (has_synced_lyrics); CREATE INDEX idx_lyrics_source ON lyrics (source); CREATE INDEX idx_lyrics_created_at ON lyrics (created_at); CREATE TRIGGER set_tracks_last_lyrics_id AFTER INSERT ON lyrics BEGIN UPDATE tracks SET last_lyrics_id = NEW.id WHERE tracks.id = NEW.track_id; END; ``` # Example Query ```sql SELECT tracks.artist_name, tracks.name, lyrics.plain_lyrics, lyrics.synced_lyrics FROM tracks INNER JOIN lyrics ON lyrics.id = tracks.last_lyrics_id WHERE artist_name LIKE "AFI" and name LIKE "17 Crimes" LIMIT 1 ``` ![image.png](/attachments/6e999367-8a4c-4314-adbc-c0861ff1399c)
image.png
716 KiB
codey self-assigned this 2025-11-21 07:37:30 -05:00
codey added the due date 2025-12-05 2025-11-21 07:37:48 -05:00
codey added the Task label 2025-11-21 07:37:56 -05:00
codey referenced this issue from a commit 2025-11-21 12:29:21 -05:00
Begin #34
codey referenced this issue from a commit 2025-11-22 13:13:10 -05:00
More progress re: #34
codey commented 2025-11-23 07:35:49 -05:00
Author
Owner
Copy Link

Change of direction: The LRCLib source for /lyric/search will be replaced with local cache fetches (from LRCLib DB dump)

LRCLib SQLite data has been imported to a Postgres database for performance reasons.

TODO

  • Check for new database dumps at https://lrclib.net/db-dumps at regular intervals, TBD
  • Import only new/missing rows to the Postgres database (consider updating existing rows in the event of changes to already imported records in the new dataset)
**Change of direction**: The LRCLib source for /lyric/search will be replaced with local cache fetches (from LRCLib DB dump) LRCLib SQLite data has been imported to a Postgres database for performance reasons. # TODO - Check for new database dumps at https://lrclib.net/db-dumps at regular intervals, TBD - Import only new/missing rows to the Postgres database (consider updating existing rows in the event of changes to already imported records in the new dataset)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Potential For Improvement
Task
No Label Task
Milestone
No items
No Milestone
Assignees
Clear assignees
codey
No Assignees codey
1 Participants
Notifications
Due Date
2025-12-05
Dependencies

No dependencies set.

Reference: codey/api#34
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?