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

# SQLite llil import script.
# Usage: bash llil_sql_imp1 [-reset] hash1.db big1.txt big2.txt big3.t
+xt

# 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

-- Create the kv_store table.
--
CREATE TABLE IF NOT EXISTS "kv_store" (
  "name"   TEXT NOT NULL UNIQUE,
  "value"  INTEGER DEFAULT 0,
  PRIMARY KEY("name")
);

-- Create a view for the kv_store table.
--
CREATE VIEW IF NOT EXISTS properties("name", "value")
  AS SELECT "name", "value" FROM "kv_store";

-- Create a trigger for inserting into properties.
--
CREATE TRIGGER IF NOT EXISTS insert_trg
  INSTEAD OF INSERT ON properties
BEGIN

  --------------------------------------------------------------------
+-----
  -- This form (ON DUPLICATE KEY) is not supported in SQLite.
  --
  -- INSERT INTO "kv_store" ("name", "value") VALUES (NEW.name, NEW.va
+lue)
  --   ON DUPLICATE KEY UPDATE "value" = kv_store.value + NEW.value;

  --------------------------------------------------------------------
+-----
  -- Try this instead; supported since SQLite version 3.24.0 (2018-06-
+04).
  -- https://sqlite.org/lang_UPSERT.html
  --
     INSERT INTO "kv_store" ("name", "value") VALUES (NEW.name, NEW.va
+lue)
       ON CONFLICT("name") DO UPDATE SET "value" = kv_store.value + NE
+W.value;

  --------------------------------------------------------------------
+-----
  -- For SQLite before version 3.24.0 (2018-06-04) or Berkeley DB (dbs
+ql).
  --
  -- -- Try updating an existing record.
  -- UPDATE "kv_store"
  --   SET "value" = kv_store.value + NEW.value WHERE "name" = NEW.nam
+e;
  -- -- Insert a row unless the update occurred.
  -- INSERT INTO "kv_store" ("name", "value")
  --   SELECT NEW.name, NEW.value WHERE (SELECT changes() = 0);

END;

-- Drop the query index to maximize import performance.
--
DROP INDEX IF EXISTS "query_idx";

-- Import CSV/TXT file(s).
--
.mode csv
.headers off
.separator ROW "\n"
.separator "\t"
EOF

  echo "$var"
}

# RUN

if [[ "$1" == "-reset" ]]; then
  DB="$2"; shift; shift
  rm -fr "$DB" "$DB"-journal "$DB"-shm "$DB"-wal
else
  DB="$1"; shift
fi

if [[ ! "$DB" =~ .db$ ]]; then
  echo "usage: bash $0 [ -reset ] hash1.db [ in1.txt ... ]" >&2
  exit 1
fi

SQLCMND=$( command -v sqlite3 )

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

SQLSCRIPT="$( make_script )"

NL=$'\n'

for infile in "$@"; do
  if [[ ! -r "$infile" ]]; then
    echo "$infile: cannot open '$infile', skipping..." >&2
    continue
  fi
  SQLSCRIPT+="${NL}.print Importing ${infile}"
  SQLSCRIPT+="${NL}.import '${infile}' properties"
done

exec "$SQLCMND" "$DB" < <(echo "$SQLSCRIPT")
[download]

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

# SQLite llil query script.
# Usage: bash llil_sql_qry1 hash1.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

-- A query index is not needed for one-time use.
--
-- CREATE INDEX IF NOT EXISTS "query_idx" ON "kv_store" (
--   "value" DESC, "name"
-- );

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

SELECT "name", "value" FROM "kv_store"
  ORDER BY "value" DESC, "name";

EOF

  echo "$var"
}

# RUN

DB="$1"; shift

if [[ ! "$DB" =~ .db$ ]]; then
  echo "usage: bash $0 hash1.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")
[download]