[slash]

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.

[CapnBry]

This will reduce the Movie DB size by 10% to 20% and will prevent issues with large movie collections.

A) What issues with large movie collections?
B) Where are you getting these numbers from? To say I am incredibly skeptical of those numbers would be a massive understatement.

sqlite stores data in what is called a sparse format, meaning that for a typical subtitle row, it stores (rowid, int, int, text). Changing it to the way you're suggesting would store (rowid, int, int) and text would now be shared. Text is 3 letters. To save 10% of my 1.3MB, 230 movie database, at 3 letters per row, I would have to have 40,106 subtitles in the database or 174 subtitles per movie. Of course, this is somewhat of an overestimation because of length and null byte but I hope it makes the point that I believe your assertion to be fallacy.

In addition, this requires loading streamdetails to also do an additional 2 table join lookup per item. If you think loading a large movie collection is a problem now, you're going to really hate that.

[slash]

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.

[jmarshall]

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

[CapnBry]

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.

[slash]

I did the test and you guys are right

I also had a quick look at the code and at first sight I doubt I could make it faster, but if I find something I'll let you know.
All this started because I wanted to integrate the flagging to my web media management interface.

I should have digged more into this before posting an "alarmist" thread (I apologize for that)

Thanks for the explanation.

[CapnBry]

It's no problem. Always good to have extra eyes on the code, especially when I wrote it