[mythtv-users] Converting scripts to not use the database directly

[Michael T. Dean]

On 11/04/2014 03:12 AM, Simon Hobson wrote:
> jedi wrote:
>
>>>> Yes it's a substitute, and probably workable, but from what I'm seeing it looks like a very inferior substitute.
>>>>
>>>> It looks like a simple statement :
>>>>> update channel set channum=channum+10000 where channum<   1000 and not channum between 700 and 799
>>>> becomes a loop where I've got to work through all the channels, perform a test, and update some of them.
>>> And we all know just how long a modern computer takes to loop
>>> through hundreds of things (or even a couple thousand for some
>>     You're trying to be sarcastic but this is actually the truth.
>>
>>     Doing things in the least efficient way that you can possibly think
>> of will in fact bog down a modern computer system. You can easily see
>> this for yourself and it doesn't take a huge dataset to do it either.
> For my use case I don't care about the time - it's a script I only run once or twice after a channel scan. But yes, I've seen "suboptimal" techniques bring systems to a halt - my favourite one from a previous job was a report that took 40 hours to run (and could only be run at weekends as it ground the system to an almost halt) due to inefficient non-use of indexes on a large table. Re-written with a different tool, and taking care of index use, it got down to 90 seconds and could be run any time without users noticing.
> But I digress ...
>
> My observation was that we are taking a step back from being able to use a tool where all the looping is nicely handled for us. Instead, everyone who writes a script to operate with the API would, it appears, have to re-invent the wheel every time.

So it's that much harder to do:

chanids = [1031, 1081, 1161, 1281, 1282, 1283, 1384]
for channel in Channel.GetChannelList(chanids)
     channel.SetVisible(false)

than

UPDATE channel SET visible = 0 WHERE chanid in (1031, 1081, 1161, 1281, 
1282, 1283, 1384);

?  Even though the update basically translates to:

for each channel in the list of channels with the specified chanids, set 
visible to 0, which is exactly what the Python would translate to--well, 
except that the Python actually allows specifying the meaning for the 
value of visible (false) rather than the integer value that we're using 
to represent that meaning (which, here, isn't too hard to understand, 
but is much different when you talk about values for things like 
recstatus and many others where values are by definition and are 
described only in code).

> I understand the desire to remove direct access ot the DB, but to remove a "good" tool (from the user's perspective) and replace it with what appears at the moment to be very inferior does seem a step backwards. IMO to be a "good" replacement the API needs to support set operations, arbitrary joins, arbitrary selections, and relative updates - which makes it sound rather like SQL ! It looks very much like at least 1 or 2 of those are missing.

You make an interesting point.  I'll work on removing some of the 
inferior tools we have right away.

In Watch Recordings, we currently have MENU|Playlist Options|Storage 
Options|Change Recording Group, which simply changes the recgroup.  I 
don't know why someone put that in there when it's so easy to just:

UPDATE recorded SET recgroup = 'Cancelled' WHERE chanid = '1071' AND 
starttime = '20141103200000';

and similarly, I'll get rid of the Change Playback Group (playgroup), 
Disable/Enable Auto Expire (autoexpire), Mark as Watched/Unwatched 
(watched), and Allow Re-record (duplicate), and Preserve/Do not preserve 
this episode from the Playlist and non-Playlist Storage Options.***

Oh, and Watch Recording's MENU|Recording Options|Change Recording 
Metadata, which would be much easier with:

UPDATE recorded set title = 'DVR', subtitle = 'Interfaces', description 
= 'This show explores the early history of DVRs when user interfaces 
eschewed SQL and how the MythTV challenged the status quo and made it 
clear that the best approach for controlling a DVR is through direct 
database access.' WHERE chanid = '1028' and starttime = '20171101200000';

And there are so many more places where we have those non-SQL approaches 
that should be removed.

All this time I thought it was important to embed the MySQL database 
server in MythTV, when really we just need to embed the mysql 
command-line client.

Finally we'll be leading the way and then the XBMC developers will be 
playing catch up--since TTBOMK nothing in their 
lauded-by-everyone-as-the-pinnacle-of-perfection user interface is SQL 
based.  I can't wait to start seeing the "Why don't we just drop the 
XBMC UI and start using mythfrontend since it uses SQL for everything?" 
posts on the XBMC list.

Mike

*** BTW, for those reading the archives, don't try to extrapolate too 
much from the SQL example shown and the list of similar "easy to do with 
SQL" options since there's more required for some of those changes than 
is apparent (meaning you really need to know the whole MythTV schema and 
data interpretation to successfully make some of those changes with SQL).