The Relook database file stored in /var/db.dat or /var/data/db.dat on hard disk machines. It is an SQL database file and sqlite3 (http://www.sqlite.org) is used to maintain this file. PCEditor by Jon Doe is a great utility for manipulating this file and normally will be used to make any changes required. This article attempts to give some of the underlying basics of SQL.

Sqlite can be used either on the Windows PC or directly on the Relook to inspect and update a database file.

On the Windows PC: 1) Find the file sqlite3.exe which is in the bin folder of PCEditor and copy this file to a new folder for testing. 2) Copy a db.dat file into this folder. 3) Run cmd from the start bar and cd to the folder. 4) To start Sqlite the command is: sqlite3 db.dat

SQLite version 3.2.8 Enter ”.help” for instructions sqlite>

On the Relook: (by the way – if you are going to play with your database on the Relook, back it up first !) 1) Telnet to the Relook as: telnet 192.168.1.10 (use your ip address) and login as user=root and password = relook (usually) 2) cd /var/data 3) To start Sqlite, the command is the same: sqlite3 db.dat

SQLite version 3.3.8 Enter ”.help” for instructions sqlite>

Entering .help will list the meta commands available. These are commands which control the sqlite processor and not the database commands. These meta commands all start with ”.” There are three important meta commands. .exit will quit the sqlite3 processor and save the database and any changes made. .tables will show the database tables.

sqlite> .tables CamInfo HistoryInfo OptionInfo UserOption ChannelInfo LNBInfo SATInfo bookmark_info EpgMap MasterCode SDTInfo deleted_media_info FAVGRPInfo NITEpgInfo ScheduleInfo media_info FAVListInfo NITInfo ScrambleInfo options

.schema will show the database layout of all the tables in the loaded database.

sqlite> .schema CREATE TABLE CamInfo (

              CAM_ID                  int,
CAM_Name                char(32),
CAM_Type                int
);

CREATE TABLE ChannelInfo (

              CH_ID                   int primary key,
ch_seq                  int,
Name                    varchar(16),
NIT_ID                  int,
PG_ID                   int,
SAT_ID                  int,
CAS_Type                int,
Media_Mode              int,
Video_PID               int,
Audio_PID               int,
PCR_PID                 int,
PMT_PID                 int,
pch_id                  int,
tuner_id                int,
audio_mode              int,
Lock                    int,
Hide                    int,
Day                     int,
ch_order                int,
SDT_ID                  int,
epg_mode                int
);

(this is the first two table definitions) . . .

Probably the most important SQL command is SELECT. This command is used to interrogate the table (or tables) in the database.

The .tables (and also the .schema) command showed that there is a table called options. We can dump this entire table by:

sqlite> select * from options; schema_revision|$Revision: 1.18 $ platform_name|relook400s radiobg|default local_time_mode|manual slide_delay_time|3 12/24 hours|24 how_view_channel_list|channel_list ttx_charset|0

This table contains the database revision as well as other information. In SQL the commands and the table names are not case sensitive. Most SQL books write the SQL as SELECT * FROM options to highlight the SQL keywords so I will continue this practice here.

A SQL table contains columns and rows, so in the above example name is the first column and schema_revision is the first row, platform_name is the second row, etc. The vertical bar | is used to separate columns. The * after the SELECT command is a wildcard that matches any column name. The ; is important. Every SQL command must be terminated by ;. This allows an SQL command to be multiple lines.

In the table options, we can see from .schema that there are two columns called name and “value”, so if the database revision is all we want to see, rather than the whole table we can select just the row we want as:

sqlite> SELECT value FROM options WHERE name = “schema_revision”; $Revision: 1.18 $

The tuning information uses three tables. ChannelInfo, NITInfo, and SATInfo contain these details. A new database file contains no ChannelInfo, but does contain NITInfo and SATInfo. These two tables form the transponders to use in a search. From .schemas, we see that the definitions are:

CREATE TABLE ChannelInfo (

              CH_ID                   int primary key,
ch_seq                  int,
Name                    varchar(16),
NIT_ID                  int,
PG_ID                   int,
SAT_ID                  int,
CAS_Type                int,
Media_Mode              int,
Video_PID               int,
Audio_PID               int,
PCR_PID                 int,
PMT_PID                 int,
pch_id                  int,
tuner_id                int,
audio_mode              int,
Lock                    int,
Hide                    int,
Day                     int,
ch_order                int,
SDT_ID                  int,
epg_mode                int
);

The first column is “CH_ID”. This is an SQL index (the PRIMARY key) and is NOT the channel number. The 19th column “ch_order” is the channel number that is displayed in the channel map. We can dump a part of the ChannelInfo table to look at its structure.

sqlite> SELECT * FROM ChannelInfo WHERE ch_order=1; 1|0|W9|232|1701|14|1280|0|120|131|120|100|0|1|3|0|0|1198833147|1|0|0 15|0|Radio Berbere|232|1731|14|0|1|8191|1131|1131|1101|0|1|3|0|0|1198833149|1|1|

We see that there are two channels with ch_order=1. One is a radio channel and one is TV. This information is contained in “Media_Mode”, so to select TV only:

sqlite> SELECT * FROM ChannelInfo WHERE ch_order=1 AND Media_Mode=0; 1|0|W9|232|1701|14|1280|0|120|131|120|100|0|1|3|0|0|1198833147|1|0|0

We see that the ChannelInfo table contains name=“W9”, and all the PID data, but no frequency data. This is contained in the NITInfo. The NITInfo definition from ”.schema” is:

CREATE TABLE NITInfo (

              NIT_ID                  INTEGER PRIMARY KEY     AUTOINCREMENT,
SAT_ID                  int,
Symbol_Rate             int,
Frequency               int,
FEC                     int,
POL                     int,
Drift                   int,
TS_ID                   int,
Network_ID              int,
Package_ID              int
);

In ChannelInfo, the NIT_ID is 232.

sqlite> SELECT * FROM NITInfo WHERE NIT_ID=232; 232|14|27500|11034000|3|0|0|12600|318|0

so we can see that the SAT_ID = 14 , symbol rate =27500, frequency = 11034 etc.

Looking at the SATInfo table from ”.schema”:

CREATE TABLE SATInfo (

              SAT_ID                  int primary key,
Name                    char(16),
posi_index              int,
posi_index_org          int,
SAT_seq                 int,
position                int,
position_org            int,
added                   int,
lnb_type                int,
unfold                  int
);

sqlite> SELECT * FROM SATInfo WHERE SAT_ID=14; 14|HOTBIRD 1,2,(13.0E)|0|0|13|130|130|0|2|0

So we see that satellite 14 is Hotbird and its position is 130 (degrees * 10, E = positive).

It is possible to perform more complex queries with SQL. Since there are three tables for the tuning information, it could be that you want to know certain values from all three with one query. Here is an example:

sqlite> SELECT ChannelInfo.name, NITInfo.frequency, SatInfo.name FROM ChannelInfo, NITInfo, SATInfo WHERE ChannelInfo.ch_order=1 AND ChannelInfo.NIT_ID = NITInfo.NIT_ID AND ChannelInfo.SAT_ID = SatInfo.SAT_ID;

W9|11034000|HOTBIRD 1,2,(13.0E)

To add data to the database, there are basically two SQL commands. “UPDATE” can change the data for an existing column and “INSERT” can add new data. Some tables have the PRIMARY Key set to “AUTOINCREMENT”. This means that Sqllite will generate it automatically and “null” should be passed for this value. NITInfo is one such table. Here is an example showing the largest NIT_ID and then adding a column. (with unrealistic values)

sqlite> SELECT MAX(NIT_ID) FROM NITInfo; 4598 sqlite> INSERT INTO NITInfo VALUES (NULL,1,2,3,4,5,6,7,8,9); sqlite> SELECT MAX(NIT_ID) from NITInfo; 4599 sqlite> SELECT * FROM NITInfo WHERE NIT_ID=4599; 4599|1|2|3|4|5|6|7|8|9

(When dealing with databases which are subject to change – a better way would be: INSERT INTO NITInfo(NIT_ID, SAT_ID, Symbol_Rate, Frequency, FEC, POL, Drift, TS_ID, Network_ID, Package_ID) VALUES (NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9); This ensures that the data would be added to the correct columns if the definitions changed)

Compacting and improving the database

If you modify the database at /var, then this is the one used for factory defaults. Since I have no C Band equipment, I deleted all entries with a frequency greater than 12750000 and less than 10700000. Delete is very permanent, so you should check with SELECT before you delete. Also, I deleted all satellites < 60E and greater than 60W. (Of course, you can use PCEditor to make these changes on a database and then FTP it to /var.)

sqlite3 /var/db.dat sqlite> SELECT * FROM NITInfo WHERE frequency > 12750000; 2212|89|20000|13361000|3|1|0|0|0|0 (and lots more like this)

sqlite> DELETE FROM NITInfo WHERE frequency > 12750000;

sqlite> SELECT * FROM NITInfo WHERE frequency < 10700000; 573|19|27500|3722000|3|1|0|0|0|0 (and lots more like this) sqlite> DELETE FROM NITInfo WHERE frequency < 10700000;

sqlite> SELECT * FROM SATInfo WHERE position > 600; 37|INTELSAT 902(62.0E)|0|0|38|620|620|0|2|0 (and lots more)

sqlite> DELETE from SATInfo WHERE position > 600;

sqlite> SELECT * from SATInfo WHERE position < -600; 88|NSS 5(177.0W)|0|0|106|-1770|-1770|0|2|0

sqlite> DELETE from SATInfo where position < -600;

One final area on the database always bothered me. In the search menu, the satellite angle was not displayed. The satellite names are of the form: HOTBIRD 1,2,(13.0E) I re-arranged these names to 13.0E HOTBIRD 6,7,8 so that in the search menu I could see the satellite angle.

Some good pointers are on http://www.sqlite.orgA good starting SQL book is: MySQL, by Ben Forta ISBN 0-672-32712-0