starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

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

New Issue
Open
opened 2026-04-25 00:06:47 +02:00 by adam · 7 comments
adam commented 2026-04-25 00:06:47 +02:00
Owner
Copy Link
Copy Source

Originally created by @nichwall on GitHub (Dec 7, 2024).

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 https://github.com/advplyr/audiobookshelf/pull/3690 and https://github.com/advplyr/audiobookshelf/pull/3689

Future Implementation (Screenshot)

Book ERD. Same sort of thing for podcastEpisode using the mediaId
Screenshot from 2024-12-07 09-07-46

Audiobookshelf Server Version

v2.17.4

Current Implementation (Screenshot)

No response

Originally created by @nichwall on GitHub (Dec 7, 2024). ### 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 https://github.com/advplyr/audiobookshelf/pull/3690 and https://github.com/advplyr/audiobookshelf/pull/3689 ### Future Implementation (Screenshot) Book ERD. Same sort of thing for `podcastEpisode` using the `mediaId` ![Screenshot from 2024-12-07 09-07-46](https://github.com/user-attachments/assets/bc13a134-9df5-41f0-8700-5300629e6626) ### Audiobookshelf Server Version v2.17.4 ### Current Implementation (Screenshot) _No response_
adam added the enhancement label 2026-04-25 00:06:47 +02:00
adam commented 2026-04-25 00:06:47 +02:00
Author
Owner
Copy Link
Copy Source

@advplyr commented on GitHub (Dec 7, 2024):

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

@advplyr commented on GitHub (Dec 7, 2024): 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
adam commented 2026-04-25 00:06:47 +02:00
Author
Owner
Copy Link
Copy Source

@Vito0912 commented on GitHub (Dec 7, 2024):

@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

@Vito0912 commented on GitHub (Dec 7, 2024): @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
adam commented 2026-04-25 00:06:47 +02:00
Author
Owner
Copy Link
Copy Source

@nichwall commented on GitHub (Dec 7, 2024):

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.

@nichwall commented on GitHub (Dec 7, 2024): 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.
adam commented 2026-04-25 00:06:47 +02:00
Author
Owner
Copy Link
Copy Source

@advplyr commented on GitHub (Dec 7, 2024):

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

@advplyr commented on GitHub (Dec 7, 2024): I didn't see your PR until now. That PR isn't taking into account podcasts
adam commented 2026-04-25 00:06:48 +02:00
Author
Owner
Copy Link
Copy Source

@Vito0912 commented on GitHub (Dec 7, 2024):

@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 :)

@Vito0912 commented on GitHub (Dec 7, 2024): @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 :)
adam commented 2026-04-25 00:06:48 +02:00
Author
Owner
Copy Link
Copy Source

@advplyr commented on GitHub (Dec 7, 2024):

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

@advplyr commented on GitHub (Dec 7, 2024): I'll have to spend some time to go through it which I may not be able to do today
adam commented 2026-04-25 00:06:48 +02:00
Author
Owner
Copy Link
Copy Source

@xairoo commented on GitHub (Dec 17, 2024):

@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.

@xairoo commented on GitHub (Dec 17, 2024): > @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.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label enhancement
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#2403
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?