Docs/Skills and code/src/

src/viewer-database-registry.js

Code93 lines4.4 KBsrc/

import fs from "node:fs";
import path from "node:path";
import Database from "better-sqlite3";
import { config } from "../config.js";

const qid = value => `"${String(value).replaceAll('"', '""')}"`;
const tableExists = (db, name) => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type IN ('table','view') AND name=?").get(name));
function tableColumns(db, name) {
  if (!tableExists(db, name)) return new Set();
  return new Set(db.prepare(`PRAGMA table_info(${qid(name)})`).all().map(row => row.name));
}

/**
 * Finds local SQLite databases that look like this project's ecosystem corpus.
 * Only schema inspection and LIMIT 1 probes are used; no full integrity scan.
 */
export function createViewerDatabaseRegistry(defaultDb, configuredPath = config.dbPath) {
  const dataDir = path.dirname(path.resolve(configuredPath));
  const defaultName = path.basename(path.resolve(configuredPath));
  const entries = new Map();
  const rejected = [];
  entries.set(defaultName, { name: defaultName, path: path.resolve(configuredPath), db: defaultDb, isDefault: true, shape: inspectShape(defaultDb) });

  function inspectShape(db) {
    try {
      const channelCols = tableColumns(db, "channels");
      const videoCols = tableColumns(db, "videos");
      const mapCols = tableColumns(db, "channel_videos");
      const problems = [];
      if (!channelCols.size) problems.push("missing channels table");
      if (!videoCols.size) problems.push("missing videos table");
      if (!channelCols.has("channel_url")) problems.push("channels.channel_url is required");
      if (!videoCols.has("video_id")) problems.push("videos.video_id is required");
      const videoLink = videoCols.has("channel_url") || (mapCols.has("channel_url") && mapCols.has("video_id"));
      if (!videoLink) problems.push("needs videos.channel_url or channel_videos(channel_url, video_id)");
      if (videoCols.size && !videoCols.has("title") && !videoCols.has("name")) problems.push("videos needs a title or name field");
      if (problems.length) return { ok: false, problems, channelColumns: [...channelCols], videoColumns: [...videoCols] };
      // Quick probes catch malformed/misidentified tables without scanning the corpus.
      db.prepare("SELECT 1 FROM channels LIMIT 1").get();
      db.prepare("SELECT 1 FROM videos LIMIT 1").get();
      return { ok: true, problems: [], channelColumns: [...channelCols], videoColumns: [...videoCols] };
    } catch (error) {
      return { ok: false, problems: [`schema probe failed: ${error.message}`], channelColumns: [], videoColumns: [] };
    }
  }

  let files = [];
  try {
    files = fs.readdirSync(dataDir, { withFileTypes: true })
      .filter(item => item.isFile() && /\.(?:db|sqlite|sqlite3)$/i.test(item.name))
      .map(item => item.name)
      .sort((a, b) => a.localeCompare(b));
  } catch (error) {
    console.warn(`[viewer:db] Could not scan data folder ${dataDir}: ${error.message}`);
  }

  for (const name of files) {
    if (entries.has(name)) continue;
    const filePath = path.join(dataDir, name);
    let candidate;
    try {
      candidate = new Database(filePath, { readonly: true, fileMustExist: true, timeout: 2000 });
      candidate.pragma("query_only = ON");
      const shape = inspectShape(candidate);
      if (!shape.ok) {
        rejected.push({ name, problems: shape.problems });
        candidate.close();
        console.log(`[viewer:db] Skipping ${name}: ${shape.problems.join("; ")}`);
        continue;
      }
      entries.set(name, { name, path: filePath, db: candidate, isDefault: false, shape });
    } catch (error) {
      try { candidate?.close(); } catch {}
      rejected.push({ name, problems: [`could not open: ${error.message}`] });
      console.log(`[viewer:db] Skipping ${name}: could not open (${error.message})`);
    }
  }

  const defaultEntry = entries.get(defaultName);
  if (!defaultEntry.shape.ok) {
    console.warn(`[viewer:db] Configured database has explorer compatibility warnings: ${defaultEntry.shape.problems.join("; ")}`);
  }
  console.log(`[viewer:db] ${entries.size} compatible database(s) discovered in ${dataDir}; ${rejected.length} skipped.`);

  return {
    dataDir,
    defaultName,
    list() { return [...entries.values()].map(({ name, path: filePath, isDefault, shape }) => ({ name, path: filePath, isDefault, shape })); },
    get(name) { return entries.get(name) || null; },
    getRejected() { return rejected.map(item => ({ ...item, problems: [...item.problems] })); }
  };
}

Shown as it is in the repository at build time (2026-11-03); the home directory appears as <home>. If this page and the file differ, the file is right. Back to the catalogue.