#!/usr/bin/env bash
# https://perlmonks.org/?node_id=11150232

# SQLite llil query script.
# Usage: bash llil_sql_qry2 hash2.db > out.txt

# SQL

function make_script () {

  read -r -d '' var << 'EOF'

-- Set SQLite pragmas.
--
.output /dev/null
PRAGMA cache_size   = 10000;
PRAGMA page_size    = 4096;
PRAGMA journal_mode = OFF;
PRAGMA synchronous  = OFF;
PRAGMA temp_store   = MEMORY;
.output

-- Output records.
--
.mode csv
.headers off
.separator ROW "\n"
.separator "\t"

SELECT "name", sum("value") FROM "kv_store"
  GROUP BY "name"
  ORDER BY sum("value") DESC, "name";

EOF

  echo "$var"
}

# RUN

DB="$1"; shift

if [[ ! "$DB" =~ .db$ ]]; then
  echo "usage: bash $0 hash2.db" >&2
  exit 1
elif [[ ! -r "$DB" ]]; then
  echo "$DB: cannot open '$DB' (No such file)" >&2
  exit 1
fi

SQLCMND=$( command -v sqlite3 )

if [[ -z "$SQLCMND" ]]; then
  echo "sqlite3: command not found" >&2
  exit 1
fi

SQLSCRIPT="$( make_script )"

exec "$SQLCMND" "$DB" < <(echo "$SQLSCRIPT")