[Enhancement]: Remove unnecessary columns from playbackSessions table in database

[nichwall]

Type of Enhancement

Server Backend

Describe the Feature/Enhancement

The playbackSessions table has several columns which should not be needed anymore. For example, if a book is updated or the authors are cleaned up, the displayTitle and displayAuthor can get out of sync on older listening sessions. I also have a number of /tmp related cover paths from old version of the iOS client, which doesn't really mean anything and is probably the local path on the device.

The following columns could be removed and instead fetched from the corresponding podcastEpisode or book entry in the other table, with an index to speed up fetches:

• displayTitle
• displayAuthor
• coverPath
• mediaMetadata

Why would this be helpful?

Reduce required amount of data for transfer and simplify the database for listening sessions.
Removes duplicate and out-of-date information.
Related to #3690 and #3689

Future Implementation (Screenshot)

Book ERD. Same sort of thing for podcastEpisode using the mediaId

Audiobookshelf Server Version

v2.17.4

Current Implementation (Screenshot)

No response

[advplyr]

The main reason I did that is in case the media gets deleted there is still a record for calculating stats. For example, some users are regularly removing old podcast episodes so if the episode doesn't exist anymore we don't have a displayTitle.

mediaMetadata stores more data than we use for stats. I wasn't sure what stats we would want at the time so stored the full object there.

One thing we should do for sure is not require that data when doing local progress syncs

[Vito0912]

@nichwall I considered that approach, but I decided against making it a relation because media can be deleted. Even if the media is removed, authors and other related details should still remain accessible in the playbacksession. That’s why I chose this method. I understand it involves some "redundant" data, and perhaps what we actually need is another table to store all that information persistently, even if an item gets deleted. However, with your proposal, the title and other details wouldn’t be retrievable after deleting the associated items. Thats why I didn't do it. (Maybe I overlook something)

Basically what @advplyr said

[nichwall]

Ah, that makes sense, thanks for clarifying. I forget that people delete old podcast episodes.

I remember there have been requests for keeping old episode metadata, so that probably falls under having a separate table to track everything including deleted media.

[advplyr]

I didn't see your PR until now. That PR isn't taking into account podcasts

[Vito0912]

@advplyr Oh, what exactly is needed for podcasts? I don’t use them often and noticed that they use author instead of authors. It seemed to work fine in my tests. Could you please point me to where I can see what needs to be changed? Thanks for the heads-up! I am a bit lost :)

[advplyr]

I'll have to spend some time to go through it which I may not be able to do today

[xairoo]

@advplyr Oh, what exactly is needed for podcasts? I don’t use them often and noticed that they use author instead of authors. It seemed to work fine in my tests. Could you please point me to where I can see what needs to be changed? Thanks for the heads-up! I am a bit lost :)

I guess it's podcastTitle (name of the postcast) and title is the name of the episode.