[mythtv-users] Custom Priority question

[Michael T. Dean]

On 09/16/2013 03:36 PM, David Engel wrote:
> FYI2.  The channel table is already included in the bigger query, so
> you can simplify this to
>
>    lower(program.callsign) like 'bbc%' and hour(program.starttime)<  5

And, really, since the schema uses*** the utf8_general_ci collation 
(meaning UTF-8 general case-insensitive), the LOWER() function call is 
unnecessary.  As a matter of fact, all UTF-8 character collations in 
MySQL are case-insensitive, even if MySQL chooses to use a 
different-from-the-column collation due to specific string literals in a 
query that make a different locale-specific collation make more sense, 
it would still be case insensitive matching.

(I just checked and made sure the "Match related callsigns" example in 
the custom/power recording rule editor doesn't use LOWER(), so thanks 
for this though exercise, which gave me a reason check to make sure our 
examples make sense, too. :)

Mike

*** There are just a few columns that use utf8_bin (i.e. do binary 
comparison of the UTF-8-encoded data) to get case-sensitive-like 
behavior (though, really, this gives us locale-unaware behavior for 
those columns, meaning they don't sort properly, etc.), but most won't 
ever come up in a custom recording/priority rule--with the exception of 
people.name and oldprogram.oldtitle (which is forced case-insensitive as 
a workaround to broken data from back when people were running with 
varying charsets specified in their databases, before we switched to 
UTF-8), not because it should be.  Eventually, I'll change all those 
that should be changed (meaning, not ones like storagegroup.dirname, 
which needs to be case-sensitive and differentiate between different 
characters, even if they're considered equivalent/mostly-equivalent 
according to the locale--such as the accented and non-accented version 
of certain characters).  Once that's done, you're very unlikely to ever 
run into a column that uses utf8_bin collation.