kodi 14.2 non existing views in sql server [problem and solved]
#1
I just upgraded my old xbmc installation to kodi. On a raspberry pi now to openelec 5.0.8 (latest stable at this time)
By the way I wanted to store the kodi database on a central mysql server to share the ressource among several clients.

All went fine after this howto:
http://www.htpcbeginner.com/kodi-mysql-s...e-library/
I prefer this guide, because it restricts the Kodi user to the databases which belong to him.

but I soon noticed, that I cannot sort tvshows and movies e.g. by title. All I got was a message, that the server is not accessible.
The kodi log mentioned that some SQL queries failed.
However the databases were created on the server. I the noticed, that sever views were missing.
According to similar discussions it ssems to be an incompatibility between sqlite and full mysql regarding views.
see here:

https://discourse.osmc.tv/t/closed-libra...views/3777
I tried to change my sql mode as discussed here:
http://forum.kodi.tv/showthread.php?tid=221716
but this was not the issue.

Actually the hints from the first thread helped further, some "features" were back after I manually added the views by using phpmyadmin.

Since nobody seems to be running my configuration, I extracted the queries to create the required views from the source code and post the here for reference:

tvshowview:
Code:
CREATE VIEW tvshowview AS SELECT
tvshow.*,
path.idParentPath AS idParentPath,
path.strPath AS strPath,
tvshowcounts.dateAdded AS dateAdded,
lastPlayed, totalCount, watchedcount, totalSeasons
FROM tvshow
LEFT JOIN tvshowlinkpath ON
tvshowlinkpath.idShow=tvshow.idShow
LEFT JOIN path ON
path.idPath=tvshowlinkpath.idPath
INNER JOIN tvshowcounts ON
tvshow.idShow = tvshowcounts.idShow
GROUP BY tvshow.idShow;

tvshowcounts:
Code:
CREATE VIEW tvshowcounts AS SELECT
tvshow.idShow AS idShow,
MAX(files.lastPlayed) AS lastPlayed,
NULLIF(COUNT(episode.c12), 0) AS totalCount,
COUNT(files.playCount) AS watchedcount,
NULLIF(COUNT(DISTINCT(episode.c12)), 0) AS totalSeasons,
MAX(files.dateAdded) as dateAdded
FROM tvshow
LEFT JOIN episode ON
episode.idShow=tvshow.idShow
LEFT JOIN files ON
files.idFile=episode.idFile
GROUP BY tvshow.idShow;

episodeview:
Code:
CREATE VIEW episodeview AS SELECT
  episode.*,
  files.strFileName AS strFileName,
  path.strPath AS strPath,
  files.playCount AS playCount,
  files.lastPlayed AS lastPlayed,
  files.dateAdded AS dateAdded,
  tvshow.c00 AS strTitle,
  tvshow.c14 AS strStudio,
  tvshow.c05 AS premiered,
  tvshow.c13 AS mpaa,
  bookmark.timeInSeconds AS resumeTimeInSeconds,
  bookmark.totalTimeInSeconds AS totalTimeInSeconds,
  seasons.idSeason AS idSeason
FROM episode
  JOIN files ON
    files.idFile=episode.idFile
  JOIN tvshow ON
    tvshow.idShow=episode.idShow
  LEFT JOIN seasons ON
    seasons.idShow=episode.idShow AND seasons.season=episode.c12
  JOIN path ON
    files.idPath=path.idPath
  LEFT JOIN bookmark ON
    bookmark.idFile=episode.idFile AND bookmark.type=1;

seasonview:
Code:
CREATE VIEW seasonview AS SELECT
  seasons.*,
  tvshowview.strPath AS strPath,
  tvshowview.c00 AS showTitle,
  tvshowview.c01 AS plot,
  tvshowview.c05 AS premiered,
  tvshowview.c08 AS genre,
  tvshowview.c14 AS strStudio,
  tvshowview.c13 AS mpaa,
  count(DISTINCT episodeview.idEpisode) AS episodes,
  count(files.playCount) AS playCount
FROM seasons
  JOIN tvshowview ON
    tvshowview.idShow = seasons.idShow
  JOIN episodeview ON
    episodeview.idShow = seasons.idShow AND episodeview.c12 = seasons.season
  JOIN files ON
    files.idFile = episodeview.idFile
GROUP BY seasons.idSeason;

with this done, I could use the movies and tvshows menus :-)
Hope this helps other users switching to a central database.

BTW: I am running gentoo and just upgraded to mysql 5.6.26. Now I access my nfs shared folder from Raspberry Pi and Windows clients.
May be this helps somebody. May be developers become aware of this issue and this is already solved in the next releases...

If somebody needs to extract the queries for himself. they are found in VideoDatabase.cpp and the constants from VideoDatabase.h
Reply

Logout Mark Read Team Forum Stats Members Help
kodi 14.2 non existing views in sql server [problem and solved]0