[Regss]

Hi. My database have ~1400 movies and ~15000 actors

I run simple script:

Code:

import xbmc
import time

json = '{"jsonrpc": "2.0", "method": "VideoLibrary.GetMovies", "params": {"properties": ["cast", "title", "plot", "rating", "year", "art", "runtime", "genre", "director", "originaltitle", "country", "set", "imdbnumber", "studio", "trailer", "playcount", "lastplayed", "dateadded", "streamdetails", "file"]}, "id": "1"}'

start = time.time()
xbmc.executeJSONRPC(json)
print 'TIME: ' + str(time.time() - start)

And in kodi.log i have:

Code:

15:03:33 T:4048   DEBUG: JSONRPC: Incoming request: {"jsonrpc": "2.0", "method": "VideoLibrary.GetMovies", "params": {"properties": ["cast", "title", "plot", "rating", "year", "art", "runtime", "genre", "director", "originaltitle", "country", "set", "imdbnumber", "studio", "trailer", "playcount", "lastplayed", "dateadded", "streamdetails", "file"]}, "id": "1"}
15:03:33 T:4048   DEBUG: CVideoDatabase::RunQuery took 219 ms for 1462 items query: select * from movie_view
15:03:48 T:4048  NOTICE: TIME: 14.893999815

The mysql query took 219ms but in script took 14 sec it is correct?

[Montellese]

First of all that's only the first query. If you request information like "streamdetails" or "cast" that will result in an extra SQL query for every property (in this case "streamdetails" and "cast") for every result of the previous query so you end up with an additional 2 * 1462 = 2924 SQL queries.

Furthermore serialization of the resulting data takes rather long. This is a known problem. I'm evaluating a different JSON library but not sure if it will make much of a difference.

[Regss]

Now I get it. I create md5 hash of result this query and detect changes in database. Maybe exist simplest way to detect changes in database like timestamp?

[Tolriq]

Update timestamp would be cool but does not exist yet (too bad :p)

Currently it takes 2 queries to detect changes due to how Kodi works, it's a bad for a good but when you rescrape a movie it is deleted and added again so the id and the date added are updated.

But this is not true for updates of the playcount (watched status).

So you need to order by dateadded to detect updated movies (> your last sync) and lastplayed (> your last sync) to detect watched status.

This does not covers the update of the stream details when the movie is first played but there's no proper way to achieve that until a correct database implementation is made to allow proper synchronisation.

[Razze]

Update timestamp would be cool but does not exist yet (too bad :p)

Currently it takes 2 queries to detect changes due to how Kodi works, it's a bad for a good but when you rescrape a movie it is deleted and added again so the id and the date added are updated.

But this is not true for updates of the playcount (watched status).

So you need to order by dateadded to detect updated movies (> your last sync) and lastplayed (> your last sync) to detect watched status.

This does not covers the update of the stream details when the movie is first played but there's no proper way to achieve that until a correct database implementation is made to allow proper synchronisation.

I actually have this on my list.
But I also want to refactor the database and split userdata and metadata (scraped info etc) but this will probably need some discussion before I can start it off.

[Tolriq]

Timestamps or fixed id ?

(Both would be cool, to allow syncs, and to allow remote playlist without risks of id change on any scrape)

[Razze]

Timestamps or fixed id ?

(Both would be cool, to allow syncs, and to allow remote playlist without risks of id change on any scrape)

Not sure what your asking here

Modified dates have to be date/time stamps
And all our tables should have an id, haven't checked for that, but when I see some table missing this I will add it.

[Tolriq]

It's a scraping problem

When you rescrape something from nfo or internet the media is removed then added again, meaning it's ID change.

IMO for a fixed media the ID should not changed on meta data update.

This typically forbid remotes to create playlists based on ids outside of Kodi (Mandatory since no API to manage playlist in Kodi except the now playing one).
We can use filename to create the queues but when creating the now playing playlist from files and not id their metadata is not loaded until the media is played, leading to not good behavior for end users.

Another problem (But much more rare) is a user watch a movie offline on a remote, if the media was scraped again I can't sync the watched status back due to this. (And there's no API / ways to set watched status for a file, only works from ids).

So in fact there's 2 things :
- Timestamp for any row update to allow proper syncing of changes from remotes
- Stables ids to allow remotes to keep playlists / refresh working on rescrape.

[Razze]

It's a scraping problem

When you rescrape something from nfo or internet the media is removed then added again, meaning it's ID change.

IMO for a fixed media the ID should not changed on meta data update.

This typically forbid remotes to create playlists based on ids outside of Kodi (Mandatory since no API to manage playlist in Kodi except the now playing one).
We can use filename to create the queues but when creating the now playing playlist from files and not id their metadata is not loaded until the media is played, leading to not good behavior for end users.

Another problem (But much more rare) is a user watch a movie offline on a remote, if the media was scraped again I can't sync the watched status back due to this. (And there's no API / ways to set watched status for a file, only works from ids).

So in fact there's 2 things :
- Timestamp for any row update to allow proper syncing of changes from remotes
- Stables ids to allow remotes to keep playlists / refresh working on rescrape.

I've seen that id problem in the music database, but wasn't aware video also did that.

Thats something we have to tackle ASAP, as that is a major design problem. It might also speed up, scanning.

[alanchow]

i recommend that rather than getting the whole library in one hit get them in chunks by using the limit functionality. That way you can get it in bite size chunks when you need it. Eg i get 50 movies at a time and use the limit's total to determine how many movies there are to display a scroll bar of that size.

[Regss]

Has anything changed in this regard?