VFXdemo/stats-db.js

const fs = require("fs/promises");
const path = require("path");
const initSqlJs = require("sql.js");

const STATS_PATH = path.join(__dirname, "data", "stats.sqlite");

let db = null;

async function load() {
  const SQL = await initSqlJs();
  let buf;
  try {
    buf = await fs.readFile(STATS_PATH);
  } catch {
    buf = undefined;
  }
  db = buf ? new SQL.Database(buf) : new SQL.Database();
  db.run(`
    CREATE TABLE IF NOT EXISTS shader_stats (
      id TEXT PRIMARY KEY NOT NULL,
      views INTEGER NOT NULL DEFAULT 0,
      likes INTEGER NOT NULL DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS shader_likes (
      shader_id TEXT NOT NULL,
      visitor_id TEXT NOT NULL,
      PRIMARY KEY (shader_id, visitor_id)
    );
  `);
}

async function persist() {
  const data = db.export();
  await fs.writeFile(STATS_PATH, Buffer.from(data));
}

async function migrateIfEmpty(list) {
  const r = db.exec("SELECT COUNT(*) FROM shader_stats");
  const n = r.length && r[0].values.length ? r[0].values[0][0] : 0;
  if (n > 0) return;
  const stmt = db.prepare(
    "INSERT INTO shader_stats (id, views, likes) VALUES (?, ?, ?)"
  );
  for (const item of list) {
    stmt.run([
      item.id,
      Number(item.views) || 0,
      Number(item.likes) || 0,
    ]);
  }
  stmt.free();
  await persist();
}

function getStatsRow(id) {
  const stmt = db.prepare("SELECT views, likes FROM shader_stats WHERE id = ?");
  stmt.bind([id]);
  if (!stmt.step()) {
    stmt.free();
    return null;
  }
  const o = stmt.getAsObject();
  stmt.free();
  return { views: o.views, likes: o.likes };
}

function mergeItem(item, visitorId) {
  const row = getStatsRow(item.id);
  const views = row ? row.views : Number(item.views) || 0;
  const likes = row ? row.likes : Number(item.likes) || 0;
  let userLiked = false;
  if (visitorId) {
    const s = db.prepare(
      "SELECT 1 FROM shader_likes WHERE shader_id = ? AND visitor_id = ?"
    );
    s.bind([item.id, visitorId]);
    userLiked = s.step();
    s.free();
  }
  return { ...item, views, likes, userLiked };
}

async function incrementView(id, fallback) {
  const row = getStatsRow(id);
  if (!row) {
    db.run("INSERT INTO shader_stats (id, views, likes) VALUES (?, ?, ?)", [
      id,
      (Number(fallback.views) || 0) + 1,
      Number(fallback.likes) || 0,
    ]);
  } else {
    db.run("UPDATE shader_stats SET views = views + 1 WHERE id = ?", [id]);
  }
  await persist();
  return getStatsRow(id);
}

async function tryLike(id, visitorId, fallback) {
  const chk = db.prepare(
    "SELECT 1 FROM shader_likes WHERE shader_id = ? AND visitor_id = ?"
  );
  chk.bind([id, visitorId]);
  if (chk.step()) {
    chk.free();
    const r = getStatsRow(id);
    return {
      likes: r ? r.likes : Number(fallback.likes) || 0,
      liked: false,
    };
  }
  chk.free();

  db.run("INSERT INTO shader_likes (shader_id, visitor_id) VALUES (?, ?)", [
    id,
    visitorId,
  ]);

  const existing = getStatsRow(id);
  if (!existing) {
    db.run("INSERT INTO shader_stats (id, views, likes) VALUES (?, ?, ?)", [
      id,
      Number(fallback.views) || 0,
      (Number(fallback.likes) || 0) + 1,
    ]);
  } else {
    db.run("UPDATE shader_stats SET likes = likes + 1 WHERE id = ?", [id]);
  }
  await persist();
  return { likes: getStatsRow(id).likes, liked: true };
}

async function insertStats(id, views, likes) {
  db.run("INSERT OR REPLACE INTO shader_stats (id, views, likes) VALUES (?, ?, ?)", [
    id,
    views,
    likes,
  ]);
  await persist();
}

async function deleteStats(id) {
  db.run("DELETE FROM shader_likes WHERE shader_id = ?", [id]);
  db.run("DELETE FROM shader_stats WHERE id = ?", [id]);
  await persist();
}

module.exports = {
  load,
  migrateIfEmpty,
  mergeItem,
  incrementView,
  tryLike,
  insertStats,
  deleteStats,
};