Here we have a small program (stripped < 10k) that enables SQLite3 SQL to be run from shell scripts, without the heft of sqlite3.
I wrote
The sqlite3 package provides the very capable
This tool allows databases to be manipulated at boot time and so this xinit script (named to run before
Notes
I wrote
Rather than wait for the rsvsync utility to be rebuilt, I considered a scripting solution.
The sqlite3 package provides the very capable
/mod/bin/sqlite3 query tool. However at boot (xinit) time when this utility runs, the /mod filesystem is not available; but even after UPX compression this program is too big to leave on one of the flash filesystems permanently. Slightly surprisingly, I couldn't see any suitable alternative online (we have the dbupdate package but its documentation seems to amount to the one usage I could find in /mod/webif/html/settings/modules/network/save.jim; also /sbin/sqlexec which only accepts a query on the command line and doesn't seem to produce any select output). So I investigated the SQLite API and came up with this (a small C program), based on the example code in TFM:
Code:
# /mod/boot/usqlite --help
Usage: /mod/boot/usqlite ["SQL command;" ...] [< SQL_script]
# /mod/boot/usqlite --version <<EOM
> attach '/var/lib/humaxtv/setup.db' as s;
> select itemText from s.TBL_MENUCONFIG where itemName like 'FAV%' and itemValue <> 0;
> EOM
usqlite SQLite exec wrapper version 0.1
FavesHD
Faves
# /mod/boot/usqlite "attach '/var/lib/humaxtv/setup.db' as s; select itemText from s.TBL_MENUCONFIG where itemName like 'FAV%' and itemValue <> 0;" <&-
FavesHD
Faves/mod/boot/xinit.d/rsvsync fixes the issue I reported:
Code:
#!/bin/sh
PATH=${0%/*}/..:$PATH
dbs=/var/lib/humaxtv
attach() { # db_path db_alias
printf "attach '%s' as %s" "$1" "$2"
}
tables() { # db_alias
printf "select name from %s.sqlite_master where type='table'" "$1"
}
# check for favnames table
usqlite "$(attach "$dbs/rsvp.db" x); $(tables x);" <&- | grep -q 'favnames' || exit 0
# process the pending/restored favnames
usqlite <<EOM
$(attach "$dbs/rsvp.db" rsvp);
$(attach "$dbs/setup.db" setup);
begin transaction;
update setup.TBL_MENUCONFIG set itemValue=0 where itemName like 'FAV_CUSTOM_STR0%';
update setup.TBL_MENUCONFIG set (itemValue, itemText) =
(select 1, f.name from rsvp.favnames f
where itemName = 'FAV_CUSTOM_STR0'||f.idx)
where (select idx from rsvp.favnames f
where itemName = 'FAV_CUSTOM_STR0'||f.idx) not null;
drop table rsvp.favnames;
commit;
EOM- This thread was invaluable in preventing the program from hanging at
sql3_open, which must be some issue with the setup of the sqlite3 package since the rest of the world doesn't appear to have the problem. - usqlite only handles actual SQL commands and doesn't know about the . commands in the query tool; so instead of
.tables dbnamein the script above, we have to look in the internal tabledbname.sqlite_masterthat contains its schema; similarlyattachis used instead of.open. - 'u' is an abbreviation for "micro" popular in the typewriter era before it was easy to write the Greek letter μ (mu), or it could stand for "unplasticised" which would be equally valid as I avoided the
-fplasticiseflag togcc. - It seems that there are a quite a few database tweak programs running at boot time and this program might make it easier to maintain the database-related side of such tweaks without needing to rebuild each affected program.
- If there is interest I could package it up with the addition of appropriate notices, etc, and perhaps a test suite.
Last edited:
