Manually rebuilding playlists using data in strawberry.db

[DrBrightside]

All of my playlists stored in Strawberry got wiped. I enabled EBU R 128 analysis for about .5TB of songs and killed Strawberry improperly, leading to the database corrupting and needing to rescan my collection of songs. Every starred/'saved' playlist in Strawberry is now just a collection of rows of empty/null entries (not just missing entries with greyed out titles). I say 'saved' because that was the feature I was using to 'save' playlists; I add songs and update my playlists a lot, so having to manually save individual playlists to disk whenever I add a single song or trying to make sure the two copies are always synched was tedious. My collection has since been rescanned but the issue with the playlists remains. Even if I try to save the playlists as a .xspf file, when I open it in with gedit it's just empty xml tags.

I made a copy of strawberry.db right after the incident and cracked it open with a browser. I'm trying to make sense of how the database works in an attempt to find the data and manually rebuild the lost playlists. Despite some missing information, I figure there has to be something somewhere. Does anyone know where I can find in the db information about what songs are in the playlists saved in the db?

[silver-sun]

If you are using Linux, the database can be viewed with DB Browser for SQlite, which should be in the repository (It is on Linux Mint.). It's pretty daunting, so edit with caution.

What I would try first is to use the database backup file, 'strawberry.db.bak', found in ~/.local/share/strawberry/strawberry. If that was made before your debacle, you might have a chance of restoring your playlists.

Rename your corrupted db file to strawberry.db_old. Make a copy of the .bak file and rename it to strawberry.db, and give it a go. Good luck.

[DrBrightside]

@silver-sun Done, done, and partially done.

I'm on Ubuntu and have already cracked it open with DB Browser. 'It' being the .db file, I didn't know the .db.bak was a backup I could also check.

I know some rudimentary SQL and how databases work, but I still don't know how to interpret the data. I've found the playlists table and it's full of the plaintext names of the playlists, but not the songs or any data about the playlist contents. There's also playlist_items with columns like playlist, collection_id, and playlist_url, which I assume has to mean something but all the columns are integers bereft of context (playlist_url is entirely full of NULL cells). Does anyone know how to interpret the data in the database? Is there any documentation on how the database is meant to work?

Update: Reading through the rest of the columns in playlist_url it seems like there are supposed to be plaintext cells with song titles, artists, albums, etc. They're all empty. At first I figured this meant the data was destroyed, but I copied the .db file I've been using with new playlists so I could see what the data was supposed to look like. Just like in the old database, every column which should hold the titles and such was empty. The only data that differed consistently (to a point) row to row was collection_id. Does collection_id represent a unique identifier to a song? How does Strawberry actually interpret the playlist_items to get data about the items in playlists out?

[silver-sun]

@DrBrightside Sorry -- that I couldn't tell you. Your pay grade is obviously higher than mine. I have used DB Browser a few times to make simple changes, but you're really in the weeds. I hope the backup database works.

[DrBrightside]

Your pay grade is obviously higher than mine.

God I hope not I'm unemployed