Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Order channels in Kodi’s TV29.db
favs(pat) as (
values ("3sat%"), ("ARD%Alpha%"), ("arte%"), ("Das Erste%"), ("PHOENIX%")),
channels_weighted(id, weighted_name) as (
select idChannel, (
-- 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,
from map_channelgroups_channels cg
inner join channels_weighted cw on cg.idChannel =,
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment