Order channels in Kodi’s TV29.db

order_channels.sql

with
   favs(pat) as (
        values ("3sat%"), ("ARD%Alpha%"), ("arte%"), ("Das Erste%"), ("PHOENIX%")),
   channels_weighted(id, weighted_name) as (
        select idChannel, (
            lower(
                -- prefix channel name with weight (1 is highest).
                case when (select count(*) from favs where channels.sChannelName like favs.pat) > 0 then 1
            else 2
            end ||" "||sChannelName))
        from channels),
   channelgroups_with_name(chanid, groupid, weighted_name) as (
        select cg.idChannel,
               cg.idGroup,
               cw.weighted_name
        from map_channelgroups_channels cg
        inner join channels_weighted cw on cg.idChannel = cw.id),
   channels_numbered_by_group(chanid, groupid, num) as (select a.chanid, a.groupid, (
        -- channel number is count of all lower-sorting (i.e. preceding) channels plus one.
        (select count(*) from channelgroups_with_name b
            where b.weighted_name < a.weighted_name
            and   b.groupid = a.groupid) +1)
        from channelgroups_with_name a)
update map_channelgroups_channels set iChannelNumber =
    (select num from channels_numbered_by_group
        where chanid = idChannel
        and   groupid = idGroup);