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

[Simon Hobson]

"Michael T. Dean" <mtdean at thirdcontact.com> 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, and rebuild it into a loop with all the selection criteria. 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 ?



>> 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".

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.