[megacrypto]

actually this entire query is not right (the tv episodes one), the joining of the tables is done a wrong order (besides the case sensitivity issue)

the current query is :

Code:

select episode.c12, path.strPath, tvshow.c00, tvshow.c08, count(1), count(files.playCount)
from episode
join tvshow on tvshow.idShow = tvshowlinkepisode.idShow
join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode
join files on files.idFile = episode.idFile
join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow
join path on path.idPath = tvshowlinkpath.idPath
where tvshow.idShow = 16  group by episode.c12

note that the 1st join joins tvshow and tvshowlinkepisode before joining tvshowlinkepisode with episode, so i have changed it to (also after upper-casing the lower-cased fields):

Code:

select episode.c12, path.strPath, tvshow.c00, tvshow.c08, 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 = 16  group by episode.c12

all i did is that i moved the:

Code:

join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode

before

Code:

join tvshow on tvshow.idShow = tvshowlinkepisode.idShow

this actually returned something back (of course this is all in the query browser)

[megacrypto]

the problem with the movie info screens is the following:

Code:

16:40:11 T:139910946486336 M:538206208   ERROR: SQLite: Undefined MySQL error: Code (1054)
                                            Query: select actors.strActor,actorlinkmovie.strRole,actors.strThumb from  actorlinkmovie,actors where actorlinkmovie.idMovie=3 and actorlinkmovie.idActor=actors.idActor order by actorlinkmovie.ROWID
16:40:11 T:139910946486336 M:538206208   ERROR: GetMovieInfo (/storage/drive3/Movies/MyMovies/xxxxxxxxxxxx.2008.Cocain.avi) failed

the query has:

Code:

order by actorlinkmovie.ROWID

while the table actorlinkmovie has no ROWID .. as far as i could read until now, mysql does not have rowid column, unlike some other dbms.

when i remove the order by from the query, it works fine.

The ROWID appears twice in the VideoDatabase.cpp:
at line 2812

Code:

CStdString strSQL = FormatSQL("select actors.strActor,actorlinkmovie.strRole,actors.strThumb from  actorlinkmovie,actors where actorlinkmovie.idMovie=%i and actorlinkmovie.idActor=actors.idActor order by actorlinkmovie.ROWID",idMovie);

and at line 2826:

Code:

strSQL = FormatSQL("select sets.strSet from sets,setlinkmovie where setlinkmovie.idMovie=%i and setlinkmovie.idSet=sets.idSet order by setlinkmovie.ROWID",idMovie);

i manually edited VideoDatabase.cpp and removed the two order by ROWID's and recompiled xbmc, and now my move info screen works and shows all details correctly

[megacrypto]

another error when scanning new tv episodes:

Code:

20:10:12 T:139844867377488 M:609370112   ERROR: SQLite: Undefined MySQL error: Code (1052)
                                            Query: 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 idShow=1
20:10:12 T:139844867377488 M:609370112   ERROR: GetTvShowInfo (/storage/drive1/TVSeries/MySeries/Warehouse.13/) failed

the error is in the where clause

Code:

where idShow=1

it should be

Code:

where tvshow.idShow=1

this is on line 1605 in VideoDatabase.cpp:

Code:

CStdString sql = FormatSQL(VIDEO_DATABASE_VIEW_TVSHOW " where idShow=%i", idTvShow);

[crash123]

Add a patch to the trac ticket dude or it may get passed over.

Cheers,
Robbie

[megacrypto]

unfortunately i do not know how to write a patch

[firnsy]

unfortunately i do not know how to write a patch

Thanks for the intimate testing megacrypto. I'll add those updates to the SQL portability patch some time today.

Yes the ROWID is specific to SQLite3.

Keep the feedback coming. The devs like to know patches are tried and tested

[megacrypto]

there is one other query that is showing errors in my log:

Code:

select distinct path.idPath from path,tvshowlinkepisode join episode on tvshowlinkepisode.idEpisode=episode.idEpisode join files on files.idPath=path.idPath where episode.idFile = files.idFile and tvshowlinkepisode.idShow=48

im trying to test some more, but theTVDB website is down and its kind of not helping in scrapping for info. im sure it will be back online very soon, and then i will test some more.

[megacrypto]

quick update: i tested the music db and so far all seems to be working fine, but some tables are not documented and would like to understand what they are for like:
- albuminfo and albuminfosong
- the ex tables (exartistalbum, exartistsong, etc.)

but so far the tables are filling with no problems when scanning albums into the library

[firnsy]

there is one other query that is showing errors in my log:

Code:

select distinct path.idPath from path,tvshowlinkepisode join episode on tvshowlinkepisode.idEpisode=episode.idEpisode join files on files.idPath=path.idPath where episode.idFile = files.idFile and tvshowlinkepisode.idShow=48

im trying to test some more, but theTVDB website is down and its kind of not helping in scrapping for info. im sure it will be back online very soon, and then i will test some more.

I'm working this issue as well, the mysql error is "ERROR 1054 (42S22): Unknown column 'path.idPath' in 'on clause'" and I'm pretty sure it's an join ordering issue. I too am waiting for the TVDB website to come back up so I can populate the SQLite3 database and compare.

[firnsy]

Ticket #8169 has been updated with all issues described hitherto.

[megacrypto]

i dont know if this is relevant to this thread here or not, but when i try to delete / remove a TV Series from my library and delete its files, whenever the library is updated again, the deleted is series is added again. As far as i could trace the problem, i found out that when a library update is executed it looks up the strHash in the path table to check if its changed or not. Now i went looking at the path table and found out that the series i've deleted is still listed there, and therefore it is added again with every library update?!

I will try to figure out more, but thought i'd put it out here maybe someone else could shed some light on the matter.

Update: this seems to be an XBMC issue, i removed the adv.settings to fall back the sqlite db and the same thing happened, so its not a db issue it seems.

Update: No, it is db related, and im guessing it has something with deleting from the path table. I tried, in more details, to switch back to the sqlite db and do the same steps, and the files (series) did not show up again in the update. will investigate some more and see.

Update: after i manually deleted the corresponding entries for the series i removed from the path table, only then the series did not show up in the library update. actually in sqlite, when a series (i only tested on series) is removed, its entry is deleted from the path table. i could not find anywhere in the log where this deleted occurs, maybe someone could figure out what is missing there.

[rcoops]

@megacrypto deleting both entries running a clean DB from the settings menu and then rescanning that should do the trick (it so far always worked for me)

As for the development it is looking very good but I am wondering why so far the poor little pictures and program databases have not been moved to anything other then SQLite3? Is that a time thing where getting the concept out of the door is more important then repeating the same trick for all databases or is there a deeper thought that I have been missing so far?

[firnsy]

As for the development it is looking very good but I am wondering why so far the poor little pictures and program databases have not been moved to anything other then SQLite3? Is that a time thing where getting the concept out of the door is more important then repeating the same trick for all databases or is there a deeper thought that I have been missing so far?

They haven't been forgotten, simply music and video pose the greatest challenge. And yes the same trick can be rolled out for all databases.

[megacrypto]

@rcoops: it did not work for me, when deleting a series and then running the clean DB, the entries for that series remained still in the path table (of course we are talking about the mySQL DB here not the sqlite). So, i have to manually delete the entries in the path table manually, then do a clean db, then a scan, and only then, they don't show up again.

[firnsy]

@rcoops: it did not work for me, when deleting a series and then running the clean DB, the entries for that series remained still in the path table (of course we are talking about the mySQL DB here not the sqlite). So, i have to manually delete the entries in the path table manually, then do a clean db, then a scan, and only then, they don't show up again.

I'll look into this and report back. Are there any SQLite errors in your log when running the clean DB (when not deleting entries from the path table manually)?