Dissection and Manipulation of the Database Files

[prpr]

The actual channel.db file didn't get any smaller though, despite deleting a significant number of records

It won't unless you issue a "vacuum" command.

 

[Black Hole]

I have not yet explored the "execute SQL" section of DB Browser for SQLite.

 

[af123]

I just tried deleting a service from the database and then adding it back in via a copy from the old database. Worked fine.

humax# cd /var/lib/humaxtv
humax# cp channel.db channel.db~
humax# /etc/init.d/S90settop shut
humax# sqlite3 channel.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> delete from TBL_SVC where uslcn = 108;
sqlite>
humax# sync
humax# reboot

Verified that LCN 108 was no longer available on the box.

humax# /etc/init.d/S90settop shut
humax# cd /var/lib/humaxtv
umax# sqlite3 channel.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> select svcidx from TBL_SVC where uslcn = 108;
... proves 108 is not in the current database ...
sqlite> attach '/var/lib/humaxtv/channel.db~' as old;
sqlite> insert into TBL_SVC select * from old.TBL_SVC where uslcn = 108;
sqlite> select svcidx from TBL_SVC where uslcn = 108;
127
... 108 is back ...
sqlite>
humax# sync
humax# reboot

Channel back and working fine.

 

[Black Hole]

OK, so what do we learn from that? What I conclude is that a record pulled in from an existing database file is somehow more complete than a record reconstructed by hand. Is there a way to look at the record before it gets inserted?

 

[af123]

What I did should be no different to manually inserting the deleted record by any other means, I just saved myself some effort.
The channel ended up back with the same svcIdx of course, but that sounds like the same experiment you did in #9
There isn't going to be anything hidden in the data.

There are some interesting queries in the humax application but no guarantee that they are used for Freeview (some of them are definitely satellite specific). Here's one that fixes up the svcIdx and hSvc values in the table.

UPDATE TBL_SVC SET svcIdx=ROWID,hSvc=((tsIdx<<16)|(ROWID&65535))

ROWID is an internal value maintained by SQLite.. I wonder if you have ended up with a rowid that doesn't match the service ID, not that I can see why it should matter. For my patched database, it looks good.

sqlite> select count(*) from TBL_SVC where rowid != svcIdx;
0

 

[Black Hole]

Ah, so your inserted record didn't get added as a new row in the database?

 

[af123]

It did, but it assumed its old row ID because I kept the svcidx value the same. I can try changing that (although not tonight)

 

[Black Hole]

It won't unless you issue a "vacuum" command.

In "Edit Pragmas" I found an "Auto Vacuum" setting at "None" and tried switching it to "Full", but it didn't do anything and when I looked at the setting again it had gone back to "None".

I would like it if somebody who knows something about this stuff would install DB Browser for SQLite and have a look around at the facilities - they might spot something helpful that to me is just noise.

 

[prpr]

I would like it if somebody who knows something about this stuff would install DB Browser for SQLite and have a look around at the facilities - they might spot something helpful that to me is just noise.

It just gives "cannot VACUUM from within a transaction: vacuum" when I tried it in the Execute SQL tab. This is why I hate these things as you're never quite sure what's going to happen or what commands it's issuing. Obviously it appears to be wrapping what you enter into a transaction, which is not always what you want.
I'm afraid you'll just have to get down and dirty and use a proper Sqlite command line shell if you want to do this. Not that I know what you are trying to achieve by shrinking the file anyway?

 

[prpr]

"Compact Database" on the File menu appears to be what you want.

 

[Black Hole]

Thanks, but I wasn't just thinking of that - anything else you see that may be of any use?

 

[Black Hole]

Not that I know what you are trying to achieve by shrinking the file anyway?

I agree, the unvacuumed file seems to work perfectly well, it's just part of the exploration of what can be done outside the box.