To offload my RaspBerry Pi with RaspBMC, I move away from the internal SQLite database to my MySQL server (as described on the XBMC Wiki).
After adding new sources and scraping the content... nothing showed up in the Movies overview... while the tables were populated...
In the xbmc.log file I found the following clue:
21:06:59 T:2928669760 ERROR: SQL: The table does not exist
Query: select * from movieview
21:06:59 T:2928669760 ERROR: GetMoviesByWhere failed
And after investigation, I noticed that there were no views defined in the schema.
To do a quick analysis on the sqllite database, I installed the SQLite Manager plugin for Firefox and found 4 views... and there I found the create statement for these views... so I simply copy-and-paste it into mysql... and it worked...
Unfortunately I was not able to insert the triggers that way... still have to find it out..
According to Bug ticket #13959 on the XBMC-Trac site, this was caused because I was a MySQL version < 5.16.... which was true... it was 5.0.something, so I upgraded my MySQL to version 5.1.67, and guess what... that did not work either...
So something goes "weird", but looking into the source code didn't gave me a clue so far... so up for further investigation...
The code to create the views:
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, tvshow.c16 AS strShowPath,
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;
CREATE VIEW
movieview AS SELECT movie.*, sets.strSet AS strSet, files.strFileName
AS strFileName, path.strPath AS strPath, files.playCount AS
playCount, files.lastPlayed AS lastPlayed, files.dateAdded AS
dateAdded, bookmark.timeInSeconds AS resumeTimeInSeconds,
bookmark.totalTimeInSeconds AS totalTimeInSeconds FROM movie LEFT JOIN
sets ON sets.idSet = movie.idSet JOIN files ON
files.idFile=movie.idFile JOIN path ON path.idPath=files.idPath
LEFT JOIN bookmark ON bookmark.idFile=movie.idFile AND
bookmark.type=1;
CREATE VIEW movieview AS SELECT movie.*,
sets.strSet AS strSet, files.strFileName AS strFileName, path.strPath
AS strPath, files.playCount AS playCount, files.lastPlayed AS
lastPlayed, files.dateAdded AS dateAdded, bookmark.timeInSeconds AS
resumeTimeInSeconds, bookmark.totalTimeInSeconds AS totalTimeInSeconds
FROM movie LEFT JOIN sets ON sets.idSet = movie.idSet JOIN files
ON files.idFile=movie.idFile JOIN path ON
path.idPath=files.idPath LEFT JOIN bookmark ON
bookmark.idFile=movie.idFile AND bookmark.type=1;
tvshowview AS SELECT tvshow.*, path.strPath AS strPath,
path.dateAdded AS dateAdded, 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
FROM tvshow LEFT JOIN tvshowlinkpath ON
tvshowlinkpath.idShow=tvshow.idShow LEFT JOIN path ON
path.idPath=tvshowlinkpath.idPath LEFT JOIN episode ON
episode.idShow=tvshow.idShow LEFT JOIN files ON
files.idFile=episode.idFile GROUP BY tvshow.idShow;
