2013-01-14, 18:49
I propose that we should merge all video data and associated file caches into a single structure which supports multiple profiles.
The current structure for sharing video data across multiple profiles is inefficient on a number of fronts.
The big code changes that I have identifed (there is more I'm sure);
Right now I am looking for feedback on 3 fronts:
Database - Tables:
Add a table to hold the profile information.
Table name = "profile"
PK = "idProfile"
Other fields = TBD
Add a table to hold the set of paths available to each profile.
Table name = "profilelinkpath"
PK = "idProfile"+"idPath"
FKs to profile and path tables
Other fields = none
Add a table to store the play count and last played data on a per profile basis
Table name = "profilelinkfile"
PK = "idProfile"+"idFile"
FKs to profile and file tables
Other fields = "playCount" (integer) and "lastPlayed" (text)
Remove the play count and last played data from the "file" table
There may also be some fields on the settings table that may need to move to the "profilelinkfile" table. --NEED SOME INPUT HERE--
Database - Views:
Update the following views such that they only return datasets accessable to the currently active profile. This would be achieved by referencing the profilelinkpath table outlined above.
episodeview
movieview
musicvideoview
tvshowview
Database - Other Queries (Stored Procedures?):
Any queries used to retrieve metadata, actors, genres, years, etc. should also be updated to only return datasets accessable to the currently active profile, again by referencing the profilelinkpath table. --NEED SOME INPUT HERE--
The current structure for sharing video data across multiple profiles is inefficient on a number of fronts.
- Data duplication: For all videos that are shared between profiles, there are multiple copies of the image cache stored. Also there are multiple copies of the data records stored in multiple databases.
- Effort duplication: When a new file is added, multiple libraries need to be updated, if that file is shared across profiles.
- Effort duplication: When metadata or image files are updated, multiple libraries and cached need to be updated, if that file is hared across profiles.
The big code changes that I have identifed (there is more I'm sure);
- Creation of a common file cache for all profiles.
- How the file cache is accessed.
- How the database is accessed.
- Procedure to write profile data to the video database.
- Procedure to write play counts, last played etc. to the new table structure.
Right now I am looking for feedback on 3 fronts:
- The level of interest in this proposal.
- Any major objections to the proposal, from either a philosophical or technical standpoint.
- Any interest in assisting on the non-DB portions of the change (or for that matter anyone interested in cooperating on the DB portion)
Database - Tables:
Add a table to hold the profile information.
Table name = "profile"
PK = "idProfile"
Other fields = TBD
Add a table to hold the set of paths available to each profile.
Table name = "profilelinkpath"
PK = "idProfile"+"idPath"
FKs to profile and path tables
Other fields = none
Add a table to store the play count and last played data on a per profile basis
Table name = "profilelinkfile"
PK = "idProfile"+"idFile"
FKs to profile and file tables
Other fields = "playCount" (integer) and "lastPlayed" (text)
Remove the play count and last played data from the "file" table
There may also be some fields on the settings table that may need to move to the "profilelinkfile" table. --NEED SOME INPUT HERE--
Database - Views:
Update the following views such that they only return datasets accessable to the currently active profile. This would be achieved by referencing the profilelinkpath table outlined above.
episodeview
movieview
musicvideoview
tvshowview
Database - Other Queries (Stored Procedures?):
Any queries used to retrieve metadata, actors, genres, years, etc. should also be updated to only return datasets accessable to the currently active profile, again by referencing the profilelinkpath table. --NEED SOME INPUT HERE--