Continuing my TV recipe
So I had a text file for channels with over 400 entries, many I new were unrelated to my interests.
I could have manually removed any channels I did not like but then what about when I refreshed the data?
No I needed to keep all the channels and mark which ones were interesting.
There were many many ways to do this but for the minimal about of coding I opted for ...
First time; import all the channels into the database, then unselect the ones I was not interested in.
All further imports; import into channels into a comparison table, figure out any new or deleted channels then add/delete new/obsolete channels.
The key was how to unselect the channels ...
sqlite3 database/tv.db "select id,selected,name from channel;" | sed -e "s/|+|/|checked=\"checked\"|/gi" | sed -e "s/\(^[0-9]*\)|\(.*\)|\(.*\)/<input type=\"checkbox\" name=\"id\" value=\"\1\" \2 \/>\3<br \/>/gi" > c.html
I then topped and tailed the c.html file to include a FORM BODY HTML tags.
Ran it in a browser and had a looong list of checkbox and channel names.
What I knew from the Computer-O quiz
was that submitting the form using the GET method would put all the form data (i.e. selected channels) in the query string.
Its a bit mad but very easy, then it was a matter of taking that query string and running it by ...
echo QUERYSTRING | sed -e "s/\(^.*?id.\)/update channel set selected = '+' where id in (/" | sed -e "s/&id=/,/g" | sed -e "s/$/);/" | sqlite3 database/tv.db
echo QUERYSTRING | sed -e "s/\(^.*?id.\)/update channel set selected = '' where id not in (/" | sed -e "s/&id=/,/g" | sed -e "s/$/);/" | sqlite3 database/tv.db
Where QUERYSTRING is the query string from submitting my form to itself.
This sets the selected flag for included values and clears it (second command) where the are not included.
I love bash and sqlite
Next time importing the programmes and the tracker table...