Talk:Databases

TO-DO (what needs to be added to this database article)

• The layout, etc. will be explained in more detail here later.
• Need to add music database information

These don't exist in MyMusic32 or MyVideos75, but will need confirmation as to whether or not they will be in the future: MyMusic32:

• exartistablum - Links artists to albums
• exartistsong - Links artists to songs
• exgenrealbum - Links genres to albums
• exgenresong - Links genres to songs
• thumb

MyVideos75

• tvshowlinkepisode - This table links TV shows (series) to episodes.

Example for update on Watched

• TiloWiki 20:25, 31 March 2013 (EDT)
• Here a simple example to update the watched flag based on a folder path of the movie/TV show
  • I couldn’t update with the movieview (DB view) therefore I used the "where .. in (Select ..."
• With slight modification you can use this to set watched for movies added before date X.

--Check with this SQL
select * from files where idFile in (select idFile From movieview where playcount is null and strPath like '%A_alreadyViewed%')

--Update with this SQL
update files set playcount = '1', lastplayed = '2013-03-30 17:55:55'  where idFile in (select idFile From movieview where playcount is null and strPath like '%A_alreadyViewed%')

What to do if views are missing?

I've added my music files and scanned them using content scrapers. I'm using MySQL 5.6 to synchronize my libraries, and I've checked that the database does contain the information I would expect after scraping from my files. However, XBMC does not display my music library, only my files. The movie and TV show libraries work absolutely fine. Looking at the log, I see these error that could explain this problem:

02:44:00 T:3132   ERROR: SQL: The table does not exist
                                            Query: SELECT albumview.* FROM albumview  WHERE albumview.strAlbum <> ''
02:44:00 T:3132   ERROR: CMusicDatabase::GetAlbumsByWhere () failed
02:44:00 T:3132   ERROR: SQL: The table does not exist
                                            Query: SELECT artistview.* FROM artistview  WHERE (artistview.idArtist IN (SELECT song_artist.idArtist FROM song_artist) OR artistview.idArtist IN (SELECT album_artist.idArtist FROM album_artist)) and artistview.strArtist != '' and artistview.idArtist <> 1
02:44:00 T:3132   ERROR: CMusicDatabase::GetArtistsByWhere failed
02:44:00 T:3132   ERROR: SQL: The table does not exist
                                            Query: SELECT songview.* FROM songview  WHERE ((CAST(songview.iTimesPlayed as DECIMAL(5,1)) < 1))
02:44:00 T:3132   ERROR: CMusicDatabase::GetSongsByWhere() failed

Looking at the database, it shows that there are no views at all in the mymusic32 database. It seems XBMC has failed to create them. The myvideos75 database does have views.

Should I expect XBMC to notice these views are missing and try to create them on startup? If so, what errors might I look for in my log to explain why this fails - I can't see anything that might be related? If not, is there a way to force it to do so, so I can try to interpret the errors in the log?

If all else fails, can I create these views from a command line manually? What sql commands would do the trick?

SkyLined 05:28, 29 May 2013 (EDT)

I believe it's a known bug in XBMC in regards to MySQL 5.6. Until it's fixed, we've advised users to just use MySQL 5.5. -- Ned Scott 19:16, 29 May 2013 (EDT)