[Jandalf]

Hello I'm Jandalf a very new Member, I would like to suggest a change to the JSON API because the actual SetMovieDetails request has very much overhead if you only want to update a single field in the movie table which has no relation to other tables (e.g. Rating). I can't code C++ and I know nearly nothing about Git and I don't know how you handle development here at XBMC but I'm now how to code in general and I would like to contribute to this great project.

I looked a bit into the json\videolibrary and the video\videodatabase class.
videolibrary handles the JSON requests and executes functions of videodatabase.
videodatabase executes direct SQL commands.

So the goal is to update single values the database tables without deleting anything.
So we may not update any linked fields, only fields with no relation to other tables

My suggestions:

• add new UpdateMovieDetail to videolibrary
  
• change SetMovieDetails to update only single fields when useful
  

for both a whitelist with fields that are not linked (and so do not the normal update process) is necessary.

Useful functions:

• videodatabase.SetDetail (updates only one field)
  
• videodatabase.GetValueString (converts parameters to SQL)
  
• videodatabase.SetDetailsForMovie (last block)
  

If everything is done all has to get adopted for tvshows and music.
And eventually do the same thing for other tables (e.g. watched flag in file table)

What do you think about it, is this idea useful and for which solution should I go?

Reference:
http://forum.xbmc.org/showthread.php?tid=166567
http://forum.xbmc.org/showthread.php?tid=162880

[Montellese]

IMO the problem basically is that neither CVideoLibrary::SetMovieDetails() nor CVideoDatabase::SetDetailsForMovie() know which values in CVideoInfoTag have changed. Changing that for CVideoLibrary::SetMovieDetails() shouldn't be a big problem but it's almost useless there because CVideoDatabase::SetDetailsForMovie() simply updates everything. Maybe it would make sense to extend it to take a array/list/vector/set of properties which should be updated and then e.g. skips the re-creation of all genres etc if the "genre" property hasn't changed.

Most ideally this could be achieved with some fancy logic in the properties of CVideoInfoTag but that's probably a bit far fetched right now. As described above IMO a viable temporary solution would be to extend CVideoDatabase::SetDetailsForMovie() and to change the logic in CVideoLibrary to be able to provide that list of changed properties.

[Jandalf]

CVideoLibrary::SetMovieDetails has the parameterObject. Aren't this the parameters I passed over with my request? Only later after UpdateVideoTag nobody knows what should be changed. My plan is to change only CVideoDatabase::SetDetailsForMovie:
1. look into the parameters if it only contains objects from the whitelist
2. if not go on as usual
3. if it only contains non linked fields:
4. use videodatabase.SetDetail to update a single field
5. repeat 4. until all fields are done

Do I understand anything wrong?

[Tolriq]

There's also things to change at CVideoLibrary::SetMovieDetails

Like the getArt or removeTags if not needed and the double set of Playcount for announcement that should only be changed the second time.

[Jandalf]

There's also things to change at CVideoLibrary::SetMovieDetails

Like the getArt or removeTags if not needed and the double set of Playcount for announcement that should only be changed the second time.

sorry, but I know nothing about tags and artwork...
But I could have a look into the Playcount double set.

[Montellese]

CVideoLibrary::SetMovieDetails has the parameterObject. Aren't this the parameters I passed over with my request? Only later after UpdateVideoTag nobody knows what should be changed. My plan is to change only CVideoDatabase::SetDetailsForMovie:
1. look into the parameters if it only contains objects from the whitelist
2. if not go on as usual
3. if it only contains non linked fields:
4. use videodatabase.SetDetail to update a single field
5. repeat 4. until all fields are done

Do I understand anything wrong?

The problem with this approach is that you will issue 20 indepentent queries if CVideoDatabase::SetDetailsForMovie() is called with a CVideoInfoTag object that had 20 non-linked properties changed. That will again add unnecessary additional SQL queries which could be handled with in a single one.

[Jandalf]

The problem with this approach is that you will issue 20 indepentent queries if CVideoDatabase::SetDetailsForMovie() is called with a CVideoInfoTag object that had 20 non-linked properties changed. That will again add unnecessary additional SQL queries which could be handled with in a single one.

Ok that's a problem.

new Solutions:
1. update single Fields if not more than e.g. 3-5 (Quick and dirty, should be ok for the actual problems, who wants more often also updates also linked fields...)
2. completely rewrite CVideoDatabase::SetDetailsForMovie() to know what will be update and update only these fields (will be the best I think, but the hardest)
3. what exactly do you mean with the CVideoInfoTag, why should it get logic?

Do we need a long term solution? Will the GSOC project be successful and how long would it take to implement the changes?
If we only want to speed up a few add-ons and wait till the whole DBS changes we could to the quick and dirty solution...

[Milhouse]

A total re-write probably carries with it too much risk, and with the longer term GSOC projects, is probably not worth the investment of time.

A more pragmatic, quick and dirty solution that knocks off the real performance killers, would seem the best compromise for what may be a relatively short lived development, assuming a new database (and new database update methods) is incoming in Gotham+1 or Gotham+2, or whenever. The important thing with any of the GSOC re-design projects would be to avoid the same inefficient database update methods that we have now.

Perhaps identifying the most common database performance killers would be a good place to start - we've already identified the addition of new artwork (eg. Artwork Downloader addon), but that's seemingly already out of scope which is most unfortunate. Can other add-on developers/users suggest more fields that are also a problem? Or does anyone have this kind of information to hand?

Or should we just assume that all movie database updates incur the same performance overhead, in which case just target those fields that are most likely to be updated outside of a library scrape, eg. playcount, lastplayed, streamdetails, resume, tag, rating, votes, etc.

[Jandalf]

I totally agree with you. Maybe we could send eddu1123 a link to this thread...

The performance killer is the total deletion and reinsert of a movie for every update.

I know about add-on updating the following fields:
Votes, Rating, MPAA, watched status, artwork and maybe set name
the first 3 are the easy ones, watched status will also work and for artwork I need to dig deeper into it, for the set name I don't know if there is a need...

more suggestions?

[olivaar]

and i want to set, change, delete the tags of a movie, tvshow, etc.

[Jandalf]

and i want to set, change, delete the tags of a movie, tvshow, etc.

tags are not that simple cause its not one single field but a linked field to another table. I'm not that deep into the whole think to manage update single fields of liked tables for a single movie...