From 47d0f971fcf64d1bb036f0081d2ff209ec5007ee Mon Sep 17 00:00:00 2001
From: Jari Vetoniemi <jari.vetoniemi@indooratlas.com>
Date: Thu, 17 Feb 2022 05:31:06 +0900
Subject: initial commit

---
 scripts/boostrap-steam-db.bash           | 22 ++++++++
 scripts/jq/transform-creators.jq         |  1 +
 scripts/jq/transform-product-creators.jq |  6 ++
 scripts/jq/transform-products.jq         | 11 ++++
 scripts/update-schema-models.bash        | 95 ++++++++++++++++++++++++++++++++
 5 files changed, 135 insertions(+)
 create mode 100644 scripts/boostrap-steam-db.bash
 create mode 100644 scripts/jq/transform-creators.jq
 create mode 100644 scripts/jq/transform-product-creators.jq
 create mode 100644 scripts/jq/transform-products.jq
 create mode 100644 scripts/update-schema-models.bash

(limited to 'scripts')

diff --git a/scripts/boostrap-steam-db.bash b/scripts/boostrap-steam-db.bash
new file mode 100644
index 0000000..ca06dd2
--- /dev/null
+++ b/scripts/boostrap-steam-db.bash
@@ -0,0 +1,22 @@
+#!/bin/bash
+set -euo pipefail
+tmp="$(mktemp -d)"
+trap 'rm -rf -- "$tmp"' EXIT
+
+# deps
+hash sqlite3 rq
+
+# first generate clean db
+bash update-schema-models.bash
+
+for db in kanta; do
+   rm -f "$db".sqlite{,-shm,-wal}
+   sqlite3 "$db".sqlite < db/"$db".sql
+   sqlite3 "$db".sqlite <<'EOF'
+insert into engines (id, name) values (0, "generic");
+insert into roles (id, name) values (0, "developer");
+EOF
+   jq -r -f scripts/jq/transform-creators.jq steam-dataset-messy/steam.json | sqlite3 "$db".sqlite
+   jq -r -f scripts/jq/transform-products.jq steam-dataset-messy/steam.json | sqlite3 "$db".sqlite
+   jq -r -f scripts/jq/transform-product-creators.jq steam-dataset-messy/steam.json | sqlite3 "$db".sqlite
+done
diff --git a/scripts/jq/transform-creators.jq b/scripts/jq/transform-creators.jq
new file mode 100644
index 0000000..90b47d2
--- /dev/null
+++ b/scripts/jq/transform-creators.jq
@@ -0,0 +1 @@
+[.[].developers] | flatten | unique | .[] | "insert into creators (name) values ('\(. | gsub("'"; "''"))');"
diff --git a/scripts/jq/transform-product-creators.jq b/scripts/jq/transform-product-creators.jq
new file mode 100644
index 0000000..1a1bef7
--- /dev/null
+++ b/scripts/jq/transform-product-creators.jq
@@ -0,0 +1,6 @@
+.[] | select(has("steam_appid") and .type=="game") |
+    "insert into product_creators (creator, product, role) values (
+        (select id from creators where name = '\(.developers[0]? // "" | gsub("'"; "''"))'),
+        \(.steam_appid),
+        0
+    );"
diff --git a/scripts/jq/transform-products.jq b/scripts/jq/transform-products.jq
new file mode 100644
index 0000000..48f51e7
--- /dev/null
+++ b/scripts/jq/transform-products.jq
@@ -0,0 +1,11 @@
+.[] | select(has("steam_appid") and .type=="game") |
+    "insert into products (id, engine, platforms, price, title, body, short, header) values (
+        \(.steam_appid),
+        0,
+        0,
+        \(.price_overview.final? // 0),
+        '\(.name? // "no-name" | gsub("'"; "''"))',
+        '\(.detailed_description? // "no-desc" | gsub("'"; "''"))',
+        '\(.short_description? // "no-short-desc" | gsub("'"; "''"))',
+        '\(.header_image? // "https://waifu.cloudef.pw/loading.png" | gsub("'"; "''"))'
+    );"
diff --git a/scripts/update-schema-models.bash b/scripts/update-schema-models.bash
new file mode 100644
index 0000000..f91f043
--- /dev/null
+++ b/scripts/update-schema-models.bash
@@ -0,0 +1,95 @@
+#!/bin/bash
+set -euo pipefail
+tmp="$(mktemp -d)"
+trap 'rm -rf -- "$tmp"' EXIT
+
+# deps
+hash sqlite3 diesel diesel_ext rustfmt
+
+gen_route() {
+   local upper="$1"
+   local lower="$(sed 's/[[:upper:]]/_&/g;s/^_//' <<<"$upper" | tr '[:upper:]' '[:lower:]')"
+cat <<EOF
+
+#[put("/$lower", data = "<input>")]
+async fn put_$lower(db: Db, input: InOut<$upper>) -> Result<Created<InOut<$upper>>> {
+   let data = input.clone();
+   db.run(move |conn| {
+        diesel::insert_into(${lower}s::table)
+            .values(&data)
+            .execute(conn)
+    })
+    .await?;
+    Ok(Created::new("/").body(input))
+}
+
+#[get("/$lower/<id>")]
+async fn get_$lower(db: Db, id: i64) -> Option<InOut<$upper>> {
+    db.run(move |conn| ${lower}s::table.filter(${lower}s::id.eq(id)).first(conn))
+        .await
+        .map(InOut)
+        .ok()
+}
+EOF
+}
+
+gen_routes() {
+   local db="$1"
+cat <<EOF
+use crate::diesel::ExpressionMethods;
+use crate::diesel::QueryDsl;
+use crate::diesel::RunQueryDsl;
+use rocket::Rocket;
+use rocket::response::{status::Created, Debug};
+use rocket::serde::json::Json as InOut;
+// use rocket::serde::msgpack::MsgPack as InOut;
+use rocket_sync_db_pools::database;
+
+#[database("$db")]
+pub struct Db(diesel::SqliteConnection);
+
+type Result<T, E = Debug<diesel::result::Error>> = std::result::Result<T, E>;
+EOF
+
+   grep 'pub struct' "$tmp"/model.rs | grep -v 'User' | while read -r _ _ name _; do
+      gen_route "$name"
+   done
+
+cat <<EOF
+
+pub fn mount_at(rocket: Rocket<rocket::Build>, path: &str) -> Rocket<rocket::Build> {
+   rocket.attach(Db::fairing()).mount(path, routes![
+EOF
+
+   grep 'pub struct' "$tmp"/model.rs | grep -v 'User' | while read -r _ _ name _; do
+      local lower="$(sed 's/[[:upper:]]/_&/g;s/^_//' <<<"$name" | tr '[:upper:]' '[:lower:]')"
+      printf '      %s,\n' "put_$lower"
+      printf '      %s,\n' "get_$lower"
+   done
+
+cat <<EOF
+   ])
+}
+EOF
+}
+
+for db in kanta; do
+   printf 'import: %s\n' db/"$db".sql
+   rm -f "$tmp"/tmp.sqlite
+   sqlite3 "$tmp"/tmp.sqlite < db/"$db".sql
+   diesel print-schema --database-url "$tmp"/tmp.sqlite |\
+      sed 's/Integer/BigInt/g' |\
+      sed 's/created_at -> Timestamp/created_at -> Nullable<Timestamp>/g' |\
+      sed 's/id -> BigInt/id -> Nullable<BigInt>/g' |\
+      sed 's/meta -> Text/meta -> Nullable<Text>/g' \
+      > "$tmp"/schema.rs
+   diesel_ext -s "$tmp"/schema.rs -m \
+      -d 'Serialize, Deserialize, Queryable, Insertable, Clone, Debug' \
+      -I 'rocket::serde::{Serialize, Deserialize}' |\
+      awk '/#\[derive/{print;print "#[serde(crate = \"rocket::serde\")]";next}1' \
+      > "$tmp"/model.rs
+   gen_routes "$db" | cat "$tmp"/schema.rs "$tmp"/model.rs - |\
+      grep -v '^// Generated by diesel_ext' |\
+      grep -v '^#!' > src/"$db".rs
+   rustfmt --edition 2021 src/"$db".rs
+done
-- 
cgit v1.2.3-70-g09d2