2011-02-17, 07:48
zepfan Wrote:Night does in some view types.
Here's the answer:
Whenever you go to TV Shows in XBMC it makes a SQL query to get the tvshows data. The studio information resides in the tvshow table so it is available in the returned data to be displayed in the Night skin when you are viewing TV Shows. Here is the SQL that gets executed by XBMC to pull the tvshows for the tvshow window.
GetTvShowsByWhere query:
Code:
SELECT tvshow.*,path.strPath AS strPath,counts.totalcount AS totalCount,counts.watchedcount AS watchedCount,counts.totalcount=counts.watchedcount AS watched FROM tvshow JOIN tvshowlinkpath ON tvshow.idShow=tvshowlinkpath.idShow JOIN path ON path.idpath=tvshowlinkpath.idPath LEFT OUTER join ( SELECT tvshow.idShow AS idShow,count(1) AS totalcount,count(files.playCount) AS watchedcount FROM tvshow JOIN tvshowlinkepisode ON tvshow.idShow=tvshowlinkepisode.idShow JOIN episode ON episode.idEpisode=tvshowlinkepisode.idEpisode JOIN files ON files.idFile=episode.idFile GROUP BY tvshow.idShow) counts ON tvshow.idShow=counts.idShow
The next queries retrieve the seasons data for the tvshow selected.
GetStackedTvShowList query:
Code:
select idShow from tvshow where c00 like (select c00 from tvshow where idShow=67) order by idShow
TV studio data is stored in tvshow.c14 field. Based on the sql query below, this data is returned for the season view so it could be shown on this skin window.
GetSeasonsNav query:
Code:
select episode.c12,path.strPath,tvshow.c00,tvshow.c08,tvshow.c14,tvshow.c13,count(1),count(files.playCount) from episode join tvshowlinkepisode on tvshowlinkepisode.idEpisode=episode.idEpisode join tvshow on tvshow.idShow=tvshowlinkepisode.idShow join files on files.idFile=episode.idFile join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow join path on path.idPath = tvshowlinkpath.idPath where tvshow.idShow = 67 group by episode.c12
Next query run gets whether the tv show has an associated movie linked to it. No tv studio available from this query.
GetMoviesByWhere query:
Code:
select * from movieview join movielinktvshow on movielinktvshow.idMovie=movieview.idMovie where movielinktvshow.idShow = 67
Next query doesn't return studio data.
GetStackedTvShowList query:
Code:
select idShow from tvshow where c00 like (select c00 from tvshow where idShow=67) order by idShow
Finally, we get to episodes query. The episodeview does return the studio data as strStudio so it is available to be displayed in the tv episode window of a skin.
GetEpisodesByWhere query:
Code:
select * from episodeview where idShow = 67 and (c12=1 or (c12=0 and (c15=0 or c15=1)))
So at this point we can see where a skin has access to the data via the standard video tvshow windows.
Now let's look at how XBMC queries the data when using playlists.
TVShow playlist filter by studio = cbs executes the following SQL. Since we already know studio data is stored in tvshow table, it would be available, but tvshow playlists don't return episodes to watch, just a list of the tv shows.
GetTvShowsByWhere query:
Code:
SELECT tvshow.*,path.strPath AS strPath,counts.totalcount AS totalCount,counts.watchedcount AS watchedCount,counts.totalcount=counts.watchedcount AS watched FROM tvshow JOIN tvshowlinkpath ON tvshow.idShow=tvshowlinkpath.idShow JOIN path ON path.idpath=tvshowlinkpath.idPath LEFT OUTER join ( SELECT tvshow.idShow AS idShow,count(1) AS totalcount,count(files.playCount) AS watchedcount FROM tvshow JOIN tvshowlinkepisode ON tvshow.idShow=tvshowlinkepisode.idShow JOIN episode ON episode.idEpisode=tvshowlinkepisode.idEpisode JOIN files ON files.idFile=episode.idFile GROUP BY tvshow.idShow) counts ON tvshow.idShow=counts.idShow WHERE ('1')
So next lets look at what is returned for an episode playlist and how XBMC queries the database for it.
Episode playlist filter by genre = comedy limit 50. This playlist queries the episodeview which also contains the studio data in the strStudio field returned. Looks good right?
GetEpisodesByWhere query:
Code:
select * from episodeview WHERE (idShow in (select idShow from genrelinktvshow join genre on genre.idGenre=genrelinktvshow.idGenre where genre.strGenre LIKE 'Comedy')) ORDER BY RANDOM() LIMIT 50
Let's try to create a playlist to query for cbs as the studio. The data is in episodesview so it could be queried against to return episodes where strStudio=cbs
Here is the playlist xml.
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<smartplaylist type="episodes">
<name>test</name>
<match>all</match>
<rule field="studio" operator="is">cbs</rule>
<limit>50</limit>
<order direction="ascending">random</order>
</smartplaylist>
which executes the following sql in XBMC
Code:
GetEpisodesByWhere query: select * from episodeview WHERE ('1') ORDER BY RANDOM() LIMIT 50
See what happened? The XBMC code does not know how to handle rule field=studio so it just creates a random episode playlist. This would be the same as not passing a rule at all.
This is why we cannot create a playlist by studio so we could have a CBS channel, HBO channel, etc.
I know this was long but hopefully it can put to rest what the capabilities are for playlists and how XBMC converts them to SQL database queries.