LoR database builder

rundeck_db_build.sh

#!/bin/sh

# hackish, unpolished shellscript that downloads LoR jsons and images, compresses
# pngs to verry lossy webps and pack that to single file sqlite database.
# It is practical usage example of sunzip-stout
# installation:
# - make sure you have curl, sqlite3 and cwebp in your system
# - build sunzip-stout and place it in your path: https://github.com/gizlu/sunzip-stout
#
# I have never played Legends of Runeterra and don't have any real usage for this
# This was done due to boredom and that I once done something similar as "assignment"
# during workshops organised by local telcom, in kinda overcomplicated way. 
# This is second aproach
#
# I know that invoking sqlite directly from shellcript sounds suspiciously, 
# but I think I managed to do it without SQL injections

retain=0

while [ $# -gt 0 ]; do
  case $1 in
    -r|--retain)
      retain=1
      shift # past argument
      ;;
    -h|--help)
      # todo
      shift # past argument
      ;;
    *)
      ver="$1" # save positional arg
      shift # past argument
      ;;
  esac
done

[ -z "$ver" ] && ver="latest"

out_dir=$PWD
tmp_dir=$(mktemp -d -t rundeck-XXXXXX)
printf "rundeck_db_build: using %s as tempdir\n" "$tmp_dir" 1>&2 
cd "$tmp_dir" || exit 1

err_handler() {
    printf "rundeck_db_build: cleaning up.\n" 1>&2
    [ $retain -ne 1 ] && rm -r "$tmp_dir"
}
trap err_handler EXIT INT HUP QUIT ABRT

lang=en_us
mkdir data/ img/

# unpack imgaes from set zip suplied to stdin and convert them to webp
unpack_set_to_webp() {
    sunzip -c cwebp -quiet -resize 205 310 -q 40 -alpha_q 2 -mt -m 6 -o - -- - \; -j 4 -qq
}
# unpack imgaes from core zip suplied to stdin and convert them to webp
unpack_core_to_webp() {
    sunzip -c cwebp -quiet -resize 64 64 -q 25 -alpha_q 0 -mt -m 6 -o - -- - \; -j 4 -qq
}

curl --parallel -s https://dd.b.pvp.net/latest/core/"$lang"/data/globals-"$lang".json -o data/globals.json \
"https://dd.b.pvp.net/latest/core-$lang.zip" | unpack_core_to_webp

fetch_set() {
 setid=$1
 curl -s --parallel https://dd.b.pvp.net/"$ver"/set"$setid"/en_us/data/set"$setid"-"$lang".json -o data/set"$setid".json \
 https://dd.b.pvp.net/"$ver"/set"$setid"-lite-"$lang".zip | unpack_set_to_webp
}
# fetch all sets
sqlite3 -bail <<'EOF' | while read -r setid; do fetch_set "$setid"; done
 SELECT substr(json_extract(value, '$.nameRef'), 4)      
 FROM json_each(readfile('data/globals.json'), '$.sets')
 -- filter out not numbers (for path safety and ingoring 'event' junkset)
 WHERE NOT substr(json_extract(value, '$.nameRef'), 4) GLOB '*[^0-9]*'
EOF

mv $lang/img/* img/
rm -r "${lang:?}" README.md metadata.json COPYRIGHT # remove junkfiles

# extract absolute version string (such as '3_4_0' rather than 'latest')
# yeah, this is redarded
# Riot doesn't give any simple way for getting version number of latest assets
# As a kludge we extract random url like "http://dd.b.pvp.net/3_3_0/core/en_us/img/regions/icon-targon.png"
# from globals.json and parse it with sed magic
ver=$(sqlite3 -bail <<EOF | sed 's:.*/\([0-9_]*\)/.*:\1:'
SELECT json_extract(value, '$.iconAbsolutePath')
FROM json_each(readfile('data/globals.json'), '$.regions')
LIMIT 1
EOF
)

# import data/images to db
sqlite3 "$out_dir"/rundeck-"$lang"-"$ver".db <<'EOF'
BEGIN TRANSACTION;
CREATE TABLE "sets"(
  "id" INTEGER,
  "name" TEXT,
  "iconImg" BLOB,
  PRIMARY KEY("id")
);
INSERT INTO sets(id, name, iconImg) SELECT
	substr(json_extract(value, '$.nameRef'), 4),
	json_extract(value, '$.name'),
	readfile('img/sets/set' || substr(json_extract(value, '$.nameRef'), 4) || '.png')
FROM json_each(readfile('data/globals.json'), '$.sets')
WHERE NOT substr(json_extract(value, '$.nameRef'), 4) GLOB '*[^0-9]'; -- filter out not-numbers (for ensuring path safety)

CREATE TABLE "regions"(
  "id" TEXT,
  "name" TEXT,
  "iconImg" BLOB,
  PRIMARY KEY("id")
);
INSERT INTO regions(id, name, iconImg) SELECT
	json_extract(value, '$.abbreviation'),
	json_extract(value, '$.name'),
	readfile('img/regions/icon-' || lower(json_extract(value, '$.nameRef')) || '.png')
FROM json_each(readfile('data/globals.json'), '$.regions')
WHERE NOT json_extract(value, '$.nameRef') GLOB '*[^a-zA-Z0-9]*'; -- filter out not-alphanumeric (for ensuring path safety)

CREATE TABLE "rarities"(
  "id" TEXT,
  "name" TEXT,
  PRIMARY KEY("id")
);
INSERT INTO rarities(id, name) SELECT
	json_extract(value, '$.nameRef'),
	json_extract(value, '$.name')
FROM json_each(readfile('data/globals.json'), '$.rarities');

CREATE TABLE "spellSpeeds"(
  "id" TEXT,
  "name" TEXT, -- localized
  PRIMARY KEY("id")
);
INSERT INTO spellSpeeds(id, name) SELECT
	json_extract(value, '$.nameRef'),
	json_extract(value, '$.name')
FROM json_each(readfile('data/globals.json'), '$.spellSpeeds');

CREATE TABLE IF NOT EXISTS "cards"(
  "cardCode" TEXT GENERATED ALWAYS AS (printf('%02d%s%03d', setId, regionId, cardId)) VIRTUAL,
  "setId" INTEGER,
  "regionId" TEXT,
  "cardId" INTEGER, -- multiple cards can have same ids but must not belong to same set&region
  "name" TEXT,
  "rarityId" TEXT,
  "spellSpeedId" TEXT,
  "attack" INTEGER,
  "cost" INTEGER,
  "health" INTEGER,
  "cardImg" BLOB,
  FOREIGN KEY("spellSpeedId") REFERENCES "spellSpeeds"("id"),
  PRIMARY KEY("cardId", "setId", "regionId"),
  FOREIGN KEY("setId") REFERENCES "sets"("id"),
  FOREIGN KEY("rarityId") REFERENCES "rarities"("id"),
  FOREIGN KEY("regionId") REFERENCES "regions"("id")
);

INSERT INTO cards(setId, regionId, cardId, name, rarityId, attack, cost, health, cardImg, spellSpeedId) SELECT
    substr(json_extract(value, '$.cardCode'), 1, 2),
    substr(json_extract(value, '$.cardCode'), 3, 2),
    substr(json_extract(value, '$.cardCode'), 5, 3),
    json_extract(value, '$.name'),
    json_extract(value, '$.rarityRef'),
    json_extract(value, '$.attack'),
    json_extract(value, '$.cost'),
    json_extract(value, '$.health'),
	readfile('img/cards/' || json_extract(value, '$.cardCode') || '.png'),
    json_extract(value, '$.spellSpeedRef')
FROM sets
JOIN json_each(readfile('data/set' || sets.id || '.json'))
WHERE json_extract(value, '$.collectible') = 1 -- we dont need non collectibles in deck builder
AND NOT json_extract(value, '$.cardCode') GLOB '*[^a-zA-Z0-9]*'; -- filter out not-alphanumeric (for ensuring path safety)

COMMIT TRANSACTION;
EOF