58 lines
1.6 KiB
Bash
Executable File
58 lines
1.6 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
#
|
|
# Build the Hammy reference bundle from the numbered SQL sources.
|
|
#
|
|
# The plain `for f in *.sql; do sqlite3 db < $f; done` loop keeps going after a
|
|
# failure, so one bad file leaves a half-populated database that looks fine
|
|
# until something queries the missing rows. -bail plus set -e stops at the first
|
|
# error instead.
|
|
|
|
set -euo pipefail
|
|
|
|
DB="${1:-hammy-ref.sqlite}"
|
|
|
|
# -batch ignores ~/.sqliterc, so output formatting does not depend on whatever
|
|
# .mode the developer has configured.
|
|
SQLITE=(sqlite3 -bail -batch)
|
|
|
|
# Order matters: 05-dxcc.sql must load before 06-eng-beacons.sql, because
|
|
# ncdxf_beacons.dxcc_id has a foreign key into dxcc_entities.
|
|
SOURCES=(*.sql)
|
|
|
|
if [ -e "$DB" ]; then
|
|
echo "removing existing $DB"
|
|
rm -f "$DB"
|
|
fi
|
|
|
|
for f in "${SOURCES[@]}"; do
|
|
printf ' %-24s' "$f"
|
|
"${SQLITE[@]}" "$DB" < "$f"
|
|
echo "ok"
|
|
done
|
|
|
|
echo
|
|
echo "integrity"
|
|
fk=$("${SQLITE[@]}" "$DB" 'PRAGMA foreign_key_check;')
|
|
if [ -n "$fk" ]; then
|
|
echo " FOREIGN KEY VIOLATIONS:"
|
|
echo "$fk" | sed 's/^/ /'
|
|
exit 1
|
|
fi
|
|
echo " foreign keys ok"
|
|
echo " integrity $("${SQLITE[@]}" "$DB" 'PRAGMA integrity_check;')"
|
|
|
|
echo
|
|
echo "row counts"
|
|
# pragma_table_info returns one row per COLUMN, so counting it gives column
|
|
# counts. Real row counts need a query per table; generate them and pipe back in.
|
|
"${SQLITE[@]}" -noheader "$DB" "
|
|
SELECT 'SELECT '' ' || name || ''' , COUNT(*) FROM ' || name || ';'
|
|
FROM sqlite_master WHERE type='table' ORDER BY name;
|
|
" | "${SQLITE[@]}" -noheader -separator ' ' "$DB" | awk '{printf " %-34s %8s\n", $1, $2}'
|
|
|
|
echo
|
|
echo "compacting"
|
|
"${SQLITE[@]}" "$DB" 'VACUUM;'
|
|
|
|
ls -lh "$DB"
|