program.category_type is either 'movie', 'series', 'sports', or 'tvshow'.
But table recorded doesn't have this.
So once a show is recorded, and after a while the upcoming schedule has "rolled off" the data in table program, you can't join with it any more. Is that right? (My system hasn't been running long enough to see anything roll off the schedule.)
But, recorded.programid starts with two characters that correspond to the category_type. So you could do this:
Code: Select all
case when programid like 'MV%' then 'movie' when programid like 'EP%' then 'series' when programid like 'SP%' then 'sports' else 'tvshow' end
So why do I care?
I'd like to set up a job that automatically changes the recording group for recordings, to make it easier to browse in "Watch Recordings". Hit m and select group filter.
My job will look at not just category_type, but also category and originalairdate, so it can do something like (pseudocode:)
Code: Select all
# don't auto-change anything except 'Default' so as to not interfere # with manually-assigned recording groups, and to not resurrect deleted recordings if (current) recording group is 'Default' if category is 'Documentary' move to documentaries recording group # irrespective whether is is a movie, series, etc. else if category_type is 'movie' move to movies recording group else if category_type is 'sports' move to sports recording group else if category_type is 'series' if originalairdate < current date - 6 month # whatever separates latest season from previous seasons move to oldseries recording group else move to newseries recording group fi else # tvshow move to other recording group fi fi
I'm thinking "Recording started". I've already used it to echo some parameters into a log, and it seems all the data I need is already present when that event fires.
Next thing is to find an efficient way to run the queries. I know it can be scripted, just have to look at the myth database perl scripts, I just don't know if creating and discarding a database connection each time the script is run will cause problems. But it should only run in small clusters around the hour or half-hour mark when recordings usually start.
If anybody is familiar with scripting database jobs, or a way to share and reuse a connection, or maybe make it a stored procedure (I work with Oracle but am new to MySQL,) I could use some pointers.
Actually, if this job were run at the start of a recording, I could join with program to get category_type, but the case statement above could be useful to re-group existing older recordings where program.category_type no longer exists.