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

[Michael T. Dean]

On 11/03/2014 11:59 AM, Simon Hobson wrote:
> "Michael T. Dean" wrote:
>>> 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 users!!!).  Yes, this is simply unworkable.  I want my microseconds back!
> Was missing the point deliberate ?
>
> I don't care about the seconds for a script that's run occasionally. It's about the effort required to take a nice simple statement that works on sets,

such that the looping is done by the (admittedly, well-optimized for 
this type of work) database engine, instead of the command-level 
interface (SQL, though we'd prefer to see future use of Python or whatever)

>   and rebuild it into a loop with all the selection criteria.

You're still specifying selection criteria in SQL--the WHERE clause.

>   I'm guessing I need to write something like :
> for a in (list of values*)
>    get record for channel a
>      test a against my criteria
>        if it matches
>          compute new value
>          write it back
> * Can I do that directly, or do I have to fetch a list of values first ?

So just specify the selection criteria when you get the list of channels 
to update.

>>> Then each statement of the form :
>>>> update channel set visible=1,channum=1,xmltvid="north-west.bbc1.bbc.co.uk" where callsign="BBC ONE N West"
>>> alos becomes several statements - in practice I'll need to loop round, but bear in mind I have different settings for different channels, eg :
>>>> update channel set visible=1,channum=27,useonairguide=1,xmltvid="freeview.quest.discoveryeurope.com" where callsign="QUEST"
>>> SO a very simple list of statements becomes a significant coding exercise.
>> All that is simply a user-interface issue.  Make a script better suited to exactly what you want and then it will be even easier than your SQL--and will survive changes over time.
> What user interface did you have in mind ? I cannot think of anything easier or simpler that "update these values in this record".

So make that user interface.  Just do it with some language other than 
SQL (i.e. Python with Python bindings--possibly eventually Python 
bindings that use Services API--or whatever).  Your interface could 
actually be a delimited text file with just the values (rather than 
repetitive SQL commands), such that the first field(s) specify the 
selection criterion(a) and other fields specify the values.  So instead of:

update channel set 
visible=1,channum=27,useonairguide=1,xmltvid="freeview.quest.discoveryeurope.com" 
where callsign="QUEST";
update channel set 
visible=1,channum=28,useonairguide=1,xmltvid="freeview.something.whatever.com" 
where callsign="SOMETHING";
...

You could have:

callsign, visible, channum, useonairguide, xmltvid
QUEST, 1, 27, 1, freeview.quest.discoveryeurope.com
SOMETHING, 1, 28, 1, freeview.something.whatever.com
...

or:
-----
callsign, sourceid
channum, visible, useonairguide, xmltvid

QUEST, 1
27, 1, 1, freeview.quest.discoveryeurope.com

SOMETHING, 2
28, 1, 1, freeview.something.whatever.com
-----

where the criteria and values are separated with line feeds (and in both 
cases the "header" line is used to specify the fields involved).

Though, personally, I think an even easier UI would be one that allows 
graphical selection of channels (i.e. in the backend web page for 
channel setup or in mythtv-setup's channel editor) and then selecting a 
"group action" type button (i.e. "Mark selected as not visible").  As a 
matter of fact, I think at least a preliminary version of something like 
this is already in the backend web setup.

> There are things that could be simplified. One option that comes to mind, using shell syntax(ish)
>
> do_update (
>    perform update of field $1 with value $2
> )
>
> for a in (list of values*)
>    do_update channel_id $( $Channel_id + 1000 )
>    do_update visible 0
>
> for a in (list of values*)
>    case
>      callsign="BBC1")) do_update channel_id 1 ; do_update xmltv_id bbc1.something or other ;;
>      ....
>    esac
>
> The point is that SQL excels at doing set operations - which is what most of the examples have been about. From what I've seen, and I stand to be corrected, the API is lacking :
> - select a set of records based on an arbitrary comparison - which means stepping through them one at a time
> - do relative updates - so read, compute, write instead
> - update arbitrary fields in one query
>
> None of this stops the API achieving the job, it just means that a "simple" "program" becomes a significantly bigger programming project.

What "API" are you talking about?  The Services API allows selection of 
data with specified criteria and updating multiple arbitrary fields in 
one operation (and pretty sure it already does so for the channel 
information--but if not it will).  It may not allow "SET chanid = chanid 
+ 1000" type relative updates (I think that's what you're talking 
about), but I don't think that's really critical--for those few times 
when doing such an update (a "simple computation" update) a simple 
for-each type construct with the computations in the program is likely fine.

That said, the Services API (and Python bindings and web interfaces to 
MythTV and ...) aren't finished.  Eventually--especially if they become 
the only way to access data and, so, encourage people to help add 
features--they'll get much better.

Mike