JSON-RPC : QueryVideoDatabase

DKreeK · 2011-05-24 10:25

Hello,

With the deprecated HTTP API, we could use QueryVideoDatabase that provides a SQL interface to the XBMC Video Database. There are an equivalent in JSON-RPC ?

Thank's

**spiff** · 2011-05-24 10:31

no and not happening.

DKreeK · 2011-05-24 10:38

Ha ok, shame ... I will have to find otherwise.

**Montellese** · 2011-05-24 10:57

What exactly are your use cases? If we don't know what you guys are missing we don't know what to add to solve it. Obviously it may be that we don't want to provide certain functionality (like a direct SQL access to the database) but it can't get worse than a "no" Wink

You can already retrieve all kinds of video information over jsonrpc using the methods in the VideoLibrary namespace. Furthermore I have already started working on functionality to update certain values of movies/tvshows/episodes/musicvideos and to remove certain videos from the database.

DKreeK · 2011-05-24 11:38

I use iViewer to drive my home theater and I want to include the piloting of XBMC in this one. Today, I develop a man in the middle with a web server that directly accessing the SQLite database and do some request. I am about to explore the posibility of using JSON-RPC instead of the webserver.

I have a query that returns me a list of recent series where an unread episode was added. This query is different from the last episode because I've only one entry by series TV. In addition to retrieve informations of the first episode not seen, I retrieve informations about the file, the path (fo episode and show) to recover the series for fanart, clearlogo and thumb. I retrieve also the number of unread episodes of the series.

That is my request :

Code:

SELECT  show.idShow "idShow"

      , show.nbUnread "nbUnread"

      , infoFirstEpisode.strTitle "showTitle"

      , infoFirstEpisode.idEpisode "idEpisode"

       , infoFirstEpisode.title "episodeTitle"

       , infoFirstEpisode.season "season"

       , infoFirstEpisode.episode "episode"

       , infoFirstEpisode.Rate "Rate"

       , infoFirstEpisode.Descr "Descr"

       , infoFirstEpisode.strPath "strPath"

       , infoFirstEpisode.strFileName "strFileName"

       , infoFirstEpisode.view "View"

       , path.strPath "showPath"

FROM   (

          SELECT listepisodes.idShow

               , max(idEpisode) "lastEpisodes"

               , sum(listepisodes.unread) "nbUnread"

               , CASE WHEN sum(listepisodes.unread) = 0 THEN 0 ELSE 1 END "unread"

          FROM    (

                    SELECT episodeview.idShow

                         , episodeview.idEpisode

                         , CASE WHEN playCount IS NULL THEN 1 ELSE 0 END "unread"

                      FROM episodeview

                  ) listepisodes

          GROUP BY listepisodes.idShow

        ) show

LEFT JOIN (

          SELECT infoshow.idShow

               , infoshow.strTitle

               , infoshow.c12 "season"

               , infoshow.c13 "episode"

               , infoshow.c00 "title"

               , infoshow.idEpisode

               , infoshow.c03 "Rate"

               , infoshow.c01 "Descr"

               , infoshow.strPath "strPath"

               , infoshow.strFileName "strFileName"

               , CASE WHEN infoshow.playCount IS NULL THEN 0 ELSE 1 END "view"

          FROM     (

                    SELECT unreadEpisode.idShow

                         , min(unreadEpisode.Classif) "firstClassif"

                      FROM (

                                SELECT episodeview.idShow

                                     , episodeview.idEpisode

                                     , (episodeview.c12 * 100000 + episodeview.c13 * 1) * CASE WHEN playCount IS NULL THEN 1 ELSE -1 END + CASE WHEN playCount IS NULL THEN 0 ELSE 9999999 END "Classif"

                                  FROM episodeview

                           ) unreadEpisode

                  GROUP BY unreadEpisode.idShow

                 ) firstUnread

          LEFT JOIN episodeview "infoshow" ON infoshow.idShow = firstUnread.idShow

                          AND (infoshow.c12 * 100000 + infoshow.c13 * 1) * CASE WHEN infoshow.playCount IS NULL THEN 1 ELSE -1 END + CASE WHEN playCount IS NULL THEN 0 ELSE 9999999 END = firstUnread.firstClassif

          ) infoFirstEpisode ON infoFirstEpisode.idShow = show.idShow

LEFT JOIN tvshowlinkpath ON tvshowlinkpath.idShow = show.idShow

LEFT JOIN path ON path.idPath = tvshowlinkpath.idPath

ORDER BY show.unread DESC, show.lastEpisodes DESC

LIMIT 0, 7

**ronie** · 2011-05-24 14:06

Montellese Wrote:What exactly are your use cases? If we don't know what you guys are missing we don't know what to add to solve it.

i've been looking into re-coding the randomitems script, used in many skins, to make use of json instead of http-api.

in the process of doing so, i ran into a few things that made me wonder why certain things are implemented the way they are...

- VideoLibrary.GetEpisodes : it's required to provide the tvshowid.
would it be possible to get all the episodes in my library in one go?
similar to AudioLibrary.GetSongs, where it's optional to specify the artistid.

- AudioLibrary.GetArtistDetails : not implemented?
would be nice to be able to fetch data for a single artist,
similar to VideoLibrary.GetTVShowDetails.

- VideoLibrary.GetMusicVideos : the runtime value is returned in minutes.
imo, it would be more accurate to return it in seconds (if possible).
since these are all short videos, seeing a runtime value of just "3" or "4"
is not really useful to me.
(it might make sense to do the same for movies/episodes)

i'm willing to create feature requests on trac if you think any of this makes sense. ;-)

**Montellese** · 2011-05-24 14:41

ronie Wrote:- VideoLibrary.GetEpisodes : it's required to provide the tvshowid.
would it be possible to get all the episodes in my library in one go?
similar to AudioLibrary.GetSongs, where it's optional to specify the artistid.

I'd say it should be possible but I'll have to look at the code. Feature request ticket is welcome Smile

ronie Wrote:- AudioLibrary.GetArtistDetails : not implemented?
would be nice to be able to fetch data for a single artist,
similar to VideoLibrary.GetTVShowDetails.

My guess is that at the beginning (in Dharma that is) there was so little information per artist that a GetArtistDetails didn't make sense. But with my jsonrpc re-write I added quite a few extra information for artists so this would certainly make sense (also to be more consistent with the rest). Feature request ticket also welcome Wink

ronie Wrote:- VideoLibrary.GetMusicVideos : the runtime value is returned in minutes.
imo, it would be more accurate to return it in seconds (if possible).
since these are all short videos, seeing a runtime value of just "3" or "4"
is not really useful to me.
(it might make sense to do the same for movies/episodes)

I am wondering about the runtime information for videos ever since I started working with jsonrpc. I can understand that it might make sense to allow users to store a string for their runtime so that everyone can display it the way they like but it's a PITA for jsonrpc as the returned value is basically useless apart from just printing it out somehwere. What you see in the "runtime" field value is simply what I am able to retrieve from the video database and AFAIK there is no more accurate information stored in the video database right now. This would probably require some changes independant of jsonrpc before this could be mapped to jsonrpc.

**ronie** · 2011-05-24 15:08

Montellese Wrote:I am wondering about the runtime information for videos ever since I started working with jsonrpc. I can understand that it might make sense to allow users to store a string for their runtime so that everyone can display it the way they like but it's a PITA for jsonrpc as the returned value is basically useless apart from just printing it out somehwere. What you see in the "runtime" field value is simply what I am able to retrieve from the video database and AFAIK there is no more accurate information stored in the video database right now. This would probably require some changes independant of jsonrpc before this could be mapped to jsonrpc.

you're probably right.
i'll look into fetching the duration value from the streamdetails instead,
that one does returns the duration-in-seconds and is exactly what i'm looking for.

i'll create those tickets in a bit.

cheers!

DKreeK Junior Member Posts: 21 Joined: Jan 2010 Reputation: 0	2011-05-24 10:25 Post: #1 Hello, With the deprecated HTTP API, we could use QueryVideoDatabase that provides a SQL interface to the XBMC Video Database. There are an equivalent in JSON-RPC ? Thank's
	find quote

spiff Grumpy Bastard Developer Posts: 12,179 Joined: Nov 2003 Reputation: 82	2011-05-24 10:31 Post: #2 no and not happening. Always read the XBMC online-manual, FAQ and search the forum before posting. Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules. For troubleshooting and bug reporting please make sure you read this first.
	find quote

DKreeK Junior Member Posts: 21 Joined: Jan 2010 Reputation: 0	2011-05-24 10:38 Post: #3 Ha ok, shame ... I will have to find otherwise.
	find quote