Dissection and Manipulation of the Database Files

[Black Hole]

For background, please refer to the associated topic: http://hummy.tv/forum/threads/copying-tuning-from-one-hd-hdr-fox-to-another.5975/. Note that this discussion does not require custom firmware installed on the HD/HDR-FOX. Note also that I have no experience with databases, so whatever I describe below anyone could do (well, almost).

I decided I would have a crack at adding the new services Classic FM and Magic to the tuning database manually, using data from primary sources only (ie not copied from another 'FOX). The first thing to do (I thought) was to dissect the channel.db file, so I downloaded the portable version of DB Browser for SQLite from sqlitebrowser.org and used it to open the channel.db file extracted from my recently-tuned HD-FOX using the hidden menu "Copy DB to USB" option. Then, for good measure, I exported the TBL_SVC table as CSV and opened that as a spreadsheet (saved to .xls and available for inspection as a zipped attachment to this post - different service types are identified by highlighting applied afterwards). Bear in mind that the tuning set has already been edited down in the 'FOX by deleting the services I am not interested in. Warning: DB Browser for SQLite does not have a "save as" option. Committing alterations to the database file over-writes the original file.

I must say - I am a little bemused as to what advantage a database brings to this as opposed to a straightforward data table (saving the overhead of running SQLite). Maybe it pays dividends when it comes to the reservation database rsv.db, and if SQLite is running for one, they might as well use it for the other.

Well, SQLite is not running as such, it's just being used as a library for reading the data file off disk. A ready-made parser making it easy to read, update, sort etc. It also allows the use of SQL to construct queries across the various tables as necessary and they can use triggers to handle cascading deletes...

Some of the fields are constant and therefore of little interest (although they might not have been constant if I had not deleted shopping/IPTV/gay/sex/chat.., and they might respond to some user settings - eg subtitles, I will edit in subsequent new information):

eCasType - always "1"
usTtxPid - always "8192"
prvIdx - always "1"
ucSearchFlag - always "0"
eOrgDeliType - always "2"
ucAudUserSetFlag - always "0"
ucSubttlUserFlag - always "0"
ucSubttlIdx - always "255"
ucLocked - always "0"
usAudioAuxPid - always "8192"
ucSatType - always "0"
ucSoundMode - always "0"
ucAudioAuxCodec - always "0"
usDolbyPid - always "8192"
ucChListPriority - always "0"
ucAntIdx - always "0"
usOnId - always "9018"
ucModifiedName - always "0"
ucDolbyFlag - always "0"
ucLcnFlag - always "1"
ucVisibleSvcFlag - always "1"
ucDolbyCodec - always "0"
ulRatingPassedEventId - always "4294967295"
eSection - always "16"
eSvcOpType - always "1"
eUserFlag1 - always "2"
usOrgLcn - always "0"
ucGuidanceType - always "255"
ucGuidanceMode - always "0"
szGuidanceStr - always ""
stRegionInfo - some kind of binary string, apparently constant even though from West and Wales.​

Thus, in my slightly naive analysis, I reckon it should be possible to add records to this table by duplicating an existing record and then inserting appropriate values for the fields which are not constant. The problem is: what are the appropriate values. Bear in mind the following are hypotheses based on observations from the attached file only.

svcIdx

This is a number unique for each record in the table, presumably used to reference that record in database queries. Is this number arbitrary, and allocated on a first-come-first-served basis? If I had retuned from factory reset and not deleted any services, would this field be an incrementing count?​

usLcn

This number corresponds with the LCN for the service.​

szSvcName

Text string corresponding with the display name of the service at that LCN.​

usSvcId

Different for each record, presumed to be the unique identifier for the service on the broadcast network. This is constant across the network for non-regional services, but (for example) BBC ONE has a different ID in the South region compared to the West. See post 2.​

eSvcType

"2" for radio, "1" for everything else (including the BBC Red Button data service, LCN 200).​

eVideoType

"0" for radio, "2" for HiDef, "1" for everything else (including data).​

ucVideoCodec

"0" for radio and data, "2" for HiDef, but then it gets complicated. StDef TV services are mostly "1" with the following exceptions: Film4+1 (transmitted on a HiDef mux) is "2"; BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1 are "0".

I wonder if this has something to do with the difficulty somebody is having with BBC THREE and FOUR (StDef)? Flagging them "0" seems to imply no video codec. I wonder what would happen if they were changed to "1"?​

ucAudioCodec

"0" for data, "7" for HiDef, "1" for radio, "1" for StDef TV services with the following exceptions: Film4+1 (transmitted on a HiDef mux) is "7"; BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1 are "0".

Flagging the exceptions "0" seems to imply no audio codec. I wonder what would happen if they were changed to "1"?​

usPmtPid
usVideoPid
usAudioPid
usPcrPid

These tend to have unique values for each record, with a few exceptions as noted below. My guess is that these are something to do with identifying specific data streams within the service TS.

usPmtPid shows some duplication between radio and TV services. usVideoPid is "8192" for radio, data, and BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1, 5*. usAudioPid is "8192" for data and BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1, 5*. usPcrPid is "8191" for data and BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1, 5*.

If these parameters are necessary for extracting the relevant data from the TS stream, why can some services get away with defaulting to 8192? See post 4.​

tsIdx

This number corresponds with the index number for the particular mux in the TBL_TS section of the database, so will be used to look up the tuning parameters (channel frequency etc) for this particular service and send them to the tuner front-end.

In my data, indexes 7-10 are missing because I have deleted any services from those channels - however, in the TBL_TS, 7-9 are missing. That there are only 9 records in TBL_TS is not a mystery: I only (manually) tuned 9 channels (and then deleted the contents of one of them, as a means to push BBC ONE Wales onto LCN 801). What is more of a mystery is why these 9 channels are not allocated IDs 1-9 instead of 1-12 (maybe a hang-over from an auto-tune without a factory reset in between?).​

hSvc

This value corresponds with svcIdx + tsIdx * 2^16.

hSvc = 65536 * tsIdx + svcIdx
so 131094 decodes to tsIdx=2, svcIdx=22

usTsId

This value is unique to each mux, presumably some kind of identification on the broadcast network. Difficult to see why it is in this table at all - it could be referenced from TBL_TS.​

eOrgSvcType

"2" for radio, "1" for everything else (including data).​

aucDefaultAuthority

A URL-like string identifying the broadcaster responsible for that service.​

ulFTAContentMgr

"50332417" for HiDef, "0" for everything else.​

So, in my naive expectations, adding records for new services (presuming they are on muxes currently included in TBL_TS) is a case of finding suitable values for usSvcId, usPmtPid, usVideoPid, usAudioPid, and usPcrPid. The other fields can then be patched according to the patterns observed, or left blank maybe (eg aucDefaultAuthority).

 

[Black Hole]

There is a post on the Toppy forum HERE (click) which lists services from Rowridge. Comparing their data with mine indicates that usSvcId is constant for non-regional services regardless of transmitter. usVideoPid also seems to correspond. The toppy AudioPid = Humax usAudioPid + 2^15.

I was amused to read they recommend having 40 services tuned max!

 

[Black Hole]

It doesn't seem to help my current quest, but this might be useful to somebody:

http://stakeholders.ofcom.org.uk/binaries/broadcast/guidance/tech-guidance/dttt_uk.pdf

 

[Black Hole]

Nobody got anything useful to contribute?

I've done a little reading on Transport Stream and it seems the PMTPID (Program Map Table Packet Identifier) identifies data packets which identify the PIDs for the streams which make up that service (the PCR stream is Program Clock Reference - so why is the sound track sync so hit and miss?? I had concluded there was no transmitted sync timebase). It sounds reasonable that the receiver only needs to pick up the PMT packets and then use them to find the video and audio packets (and alternative sound tracks etc), so there is a good chance the usPmtPid field is the only one that matters in the database table - the others may well get filled in "live".

If that's the case, then all I need are values for usSvcId and usPmtPid. Unfortunately I have been unable to find a source. If some kind person can supply these (just these) values for Classic FM and Magic, I can test my theory.

NB: Program - I am quoting the references, and aware that the spelling should be "programme"!

 

[MikeSh]

Nobody got anything useful to contribute?

Sorry. Beyond my ken

 

[Black Hole]

Timid, and shy, and scared are you?

 

[af123]

I must say - I am a little bemused as to what advantage a database brings to this as opposed to a straightforward data table (saving the overhead of running SQLite). Maybe it pays dividends when it comes to the reservation database rsv.db, and if SQLite is running for one, they might as well use it for the other.

Well, SQLite is not running as such, it's just being used as a library for reading the data file off disk. A ready-made parser making it easy to read, update, sort etc. It also allows the use of SQL to construct queries across the various tables as necessary and they can use triggers to handle cascading deletes. Here's an example that is in that database:

CREATE TRIGGER IF NOT EXISTS [TRG_DEL_FAV_SVC]
AFTER DELETE On [TBL_SVC] FOR EACH ROW
begin
    DELETE FROM TBL_FAV  WHERE hSvc=OLD.hSvc;
end;

So, delete a logical channel through any means (including the various custom firmware options) and the corresponding entries in the favourites table disappear too.

and an example query used by the Humax software:

SELECT S.hSvc FROM TBL_SVC S
INNER JOIN (SELECT tsIdx FROM TBL_TS
WHERE usOrgNetId=%d AND usTsId=%d) T
ON S.tsIdx=T.tsIdx WHERE S.usSvcId=%d
AND S.eOrgDeliType=%d

You can see that they're joining both the SVC and TS tables at once to retrieve the data they need.

prvIdx - always "1"

Actually an index into the provider table (TBL_PRV).. it probably contains only one entry for most if not all.

ucLocked

- always "0"

Becomes "1" when the channel is locked.

usOnId - always "9018"

Original network ID. 9018 means UK Digital Terrestrial Television.

usOrgLcn

- always "0"

For duplicate services that have been placed in the 700+ range, this will hold the original LCN.
Also used by the custom firmware channel move package to record the original location.

usSvcId

Different for each record, presumed to be the unique identifier for the service on the broadcast network. This is constant across the network for non-regional services, but (for example) BBC ONE has a different ID in the South region compared to the West. See post 2.​

Seems fair. I have access to tuning data from boxes subscribed to RS and if I look up the service ID used for BBC TWO I find that all but two boxes have service ID 4287, the other two being 4228 - presumably ones that have not retuned recently.

usPmtPid
usVideoPid
usAudioPid
usPcrPid

These tend to have unique values for each record, with a few exceptions as noted below. My guess is that these are something to do with identifying specific data streams within the service TS.
​

Yep - PID == packet ID.
PID 8192 is usually a dummy PID meaning the whole multiplex.

In my data, indexes 7-10 are missing because I have deleted any services from those channels - however, in the TBL_TS, 7-9 are missing. That there are only 9 records in TBL_TS is not a mystery: I only (manually) tuned 9 channels (and then deleted the contents of one of them, as a means to push BBC ONE Wales onto LCN 801). What is more of a mystery is why these 9 channels are not allocated IDs 1-9 instead of 1-12 (maybe a hang-over from an auto-tune without a factory reset in between?).

It's due to the way that auto-increment fields work in SQL databases. See also the contents of the sqlite_sequence table in that database file which records the last value used.

usTsId

This value is unique to each mux, presumably some kind of identification on the broadcast network. Difficult to see why it is in this table at all - it could be referenced from TBL_TS.​

Yep, it isn't normalised for some reason.

 

[Black Hole]

I'll feed those back in.

 

[Black Hole]

Ouch!

Lacking a source of data, I ran an auto-tune and grabbed the resulting channel.db (then reverted to the previous channel.db). I then tried using DB Browser for SQLite to add a single record to TBL_SVC for Classic FM, using the general pattern for a radio service plus the very few parameters from the received data that ought to be sufficient to define Classic FM's extraction from the stream on COM5. It didn't work. What I got was a "0" entry in the service list (accessed by selecting a working radio service then pressing the OK button), and a misbehaving EPG (weird wrap-arounds when scrolling through the radio service list).

So then I tried filling in the Classic FM record completely, right down to the binary data in the last field. No improvement.

There appears to be some property of this database that I have not so far discovered, which controls how the services are registered. Could it be that all the services from a particular mux are grouped in the table? If so, I don't know how I can create a record in the right place.

 

[af123]

So then I tried filling in the Classic FM record completely, right down to the binary data in the last field. No improvement.

Are you sure that the TS table was the same after the retune? It's possible that the ids in there moved around.

 

[Black Hole]

Granted, but I patched the tsIdx value to suit the old setup.

 

[Black Hole]

The information for service "0" ( OK >> info ) is unpopulated too.

 

[prpr]

I've already tried this stuff - I posted about it previously - using the correct values for all fields. It appears to add OK to the database (of course you have to shut the humaxtv app. first), but as soon as you reboot, the Humax software removes the entry again. I don't know how it knows or where it stores the info. - you'd have thought the database would be God, but it appears not to be.

 

[Black Hole]

My results are different. Having extracted the databases again, by patched line is still there and as far as I can see intact (not removed). I assume it is responsible for the "0" item in the displayed LCN list.

 

[dragon-it]

Are the video Codec and audio Codec entries 0 because those channels were off air at the time so had no video or audio associated with them, maybe different later - sounds like you removed most of the other part time channels?

 

[Black Hole]

For BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1, 5* you mean? Good point.

The hypothesis works for BBC THREE, BBC FOUR, ITV3+1, ITV4+1, truTV+1, maybe not for 5* (which tends to go teleshopping).

That would be at the time of tuning, because the data is the same even when snap-shotted when these services are on-air (which rather indicates that much of the content of the table is irrelevant).

Update: 5* doesn't have zero in the codec fields, so the hypothesis is holding water. It is grouped with the others because it has 8192 in the PID fields.

 

[Black Hole]

I've already tried this stuff - I posted about it previously - using the correct values for all fields. It appears to add OK to the database (of course you have to shut the humaxtv app. first), but as soon as you reboot, the Humax software removes the entry again. I don't know how it knows or where it stores the info. - you'd have thought the database would be God, but it appears not to be.

That isn't right. Switching another channel.db in from another unit (or saved from the same unit at a previous time with a different tuning) duplicates the tuning of that unit. Therefore channel.db must contain all the necessary info. If tweaking it by adding a line to TBL_SVC isn't sufficient, there has to be some other property of the file that is not being updated properly. The problem is to find it.

 

[prpr]

I don't disagree. I only report what I have seen in experiments so far...

 

[Black Hole]

New experimental results (mostly negative):

I used DB Browser for SQLite to delete records from TBL_SVC, and to alter LCNs, and that worked fine. The actual channel.db file didn't get any smaller though, despite deleting a significant number of records (which is not the case if the services are deleted using the SUI).

I then patched the details for Magic and Classic FM into existing records (instead of adding records), and that resulted in the same as before: zero LCN entries and a confused EPG listing. The result of patching records was that the tsIdx entries were out of order, so next I will try patching existing records with the same tsIdx.

 

[Black Hole]

The result of patching records was that the tsIdx entries were out of order, so next I will try patching existing records with the same tsIdx.

Nope, that didn't work either. Zero LCNs again. Clearly DB Browser for SQLite doesn't show or control everything that needs to be taken into account, and I don't (at the moment) know enough about the innards to hack it directly.

By the way, it's much easier to play with all this on an HD-FOX than an HDR-FOX - rebooting an HD-FOX is so easy!