[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)

megacrypto · 2010-01-04 15:37

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 · 2010-01-04 16:50

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 Smile

megacrypto · 2010-01-04 20:21

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 · 2010-01-05 01:34

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

Cheers,
Robbie

megacrypto · 2010-01-05 03:09

unfortunately i do not know how to write a patch Sad

**firnsy** · 2010-01-05 05:17

megacrypto Wrote: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 Big Grin

megacrypto · 2010-01-05 15:15

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 · 2010-01-05 19:33

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** · 2010-01-06 01:31

megacrypto Wrote: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** · 2010-01-06 13:37

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

crash123 Senior Member Posts: 258 Joined: Oct 2008 Reputation: 0 Location: New Plymouth, New Zealand	2010-01-05 01:34 Post: #64 Add a patch to the trac ticket dude or it may get passed over. Cheers, Robbie
	find quote

megacrypto Senior Member Posts: 219 Joined: Apr 2008 Reputation: 0	2010-01-05 03:09 Post: #65 unfortunately i do not know how to write a patch
	find quote

firnsy Team-XBMC Developer Posts: 104 Joined: Jul 2009 Reputation: 0 Location: Australia	2010-01-05 05:17 Post: #66 megacrypto Wrote: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
	find quote

megacrypto Senior Member Posts: 219 Joined: Apr 2008 Reputation: 0	2010-01-05 19:33 Post: #68 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
	find quote

firnsy Team-XBMC Developer Posts: 104 Joined: Jul 2009 Reputation: 0 Location: Australia	2010-01-06 13:37 Post: #70 Ticket #8169 has been updated with all issues described hitherto.
	find quote