Posts: 578
Joined: Sep 2007
Reputation:
6
Hi,
The Media Flagging is a very nice addition to XBMC and the work done on this is impressive, however I noticed that the new table streamdetails has a lot of empty fields (NULL) mostly to support multiple subtitles for the same movie.
I would suggest creating two new tables (like for movie genre):
subtitle: contain a list of existing subtitles
movielinksubtitle: link between movie and subtitle
This will reduce the Movie DB size by 10% to 20% and will prevent issues with large movie collections.
Of course this will require a bit of code modification.
I'm willing to contribute but I don't want to break anything as this feature is still work in progress.
Posts: 578
Joined: Sep 2007
Reputation:
6
In the DB schema I see for streamdetails :
At least 10 int fields in addition to the text field for the subtitle
so for the same movie you have a minimum of 3 rows:
rowid, idFile, codecinfo, then a bunch of NULL (for the video infos)
rowid, idFile, NULL, NULL, fields for audio infos, then few NULL (for the audio infos)
rowid, idFile, many NULL fields then text field for subtitle (one row per subtitle)
So either I dont have the latest DB / code and I'm completely wrong and in that case I apologize.
Or for I have the right schema and each movie XBMC will create at least 3 rows in the streamdetails table (I have a movie it created 6 rows).
Anyway, I'm just trying to understand how the new flagging feature is inserted into DB. I also realize that SQLite is limited with join and hopefully will be moving to something more scalable.
Posts: 26,215
Joined: Oct 2003
Reputation:
187
Are you not just shifting the problem from multiple streamdetails rows to multiple link table rows for subs, in addition to multiple streamdetails rows for a/v details? I don't think the number of rows is going to reduce, right?
Don't let that stop you experimenting though! If you can come up with an alternate scheme that is better than the current one then we're all for it. Note that "better" here has to be both in terms of performance AND ease of implementation, ofcourse.
Cheers,
Jonathan
Posts: 418
Joined: Oct 2004
Reputation:
0
You're seeing the schema properly, you're just not getting the storage mechanism of the database. NULLs aren't even stored in sqlite, only fields which have values are written to the disk. Try this:
make a copy of MyVideos34.db
open it up in sqlite3
vacuum it
check the file size
delete from streamdetails where iStreamType = 2;
vacuum again
check the file size
You'll see that storage of the subtitle information is very very small even with a large library, and adding a detail resolver like you're suggesting would might save like 100KB in a 4MB database at the cost of slowing down the myvideos view load.
But I'm with jmarshall, XBMC is fun to experiment with. If you think you've found a better stronger faster way of doing something, don't hesitate to bring it up! We like new ideas.
For troubleshooting and bug reporting please make sure you read this first.