engine_3_0/training_scripts/make_domains_migration.py

"""Generate backend/migrations/012_domains_catalogue.sql for the UniHive `main` repo: seeds the recommender's 13 broad interests and 114 interest tags
into the existing `domains` table. Source of truth: backend/crud/taxonomy.py (recommender branch). Usage: python make_domains_migration.py <taxonomy.py> <out.sql>"""
import importlib.util, sys

src, out = sys.argv[1], sys.argv[2]
spec = importlib.util.spec_from_file_location("taxonomy", src); tx = importlib.util.module_from_spec(spec); spec.loader.exec_module(tx)
q = lambda s: "'" + str(s).replace("'", "''") + "'"
broad, child, names = [], [], set()
for bslug, bname, bdesc, children in tx.TAXONOMY:
    bslug = tx.slugify(bname)          # phrase slug ('software-development'), not the recommender's short one ('software'): short slugs are ordinary words that main's text matcher would find in everyday posts
    assert bname.lower() not in names; names.add(bname.lower()); broad.append((bname, bslug, bdesc))
    for c in children:
        assert c.lower() not in names, c; names.add(c.lower()); child.append((c, tx.slugify(c), bslug))
assert len(broad) == 13 and len(child) == 114 and len({c[1] for c in child}) == 114 and len({b[1] for b in broad} | {c[1] for c in child}) == 127
allslugs = [s for _, s, _ in broad] + [s for _, s, _ in child]
D = "$$"
sql = f"""-- Migration 012: seed the recommender's interest taxonomy into the existing `domains` table.
-- 13 broad interests (parent_id NULL, phrase slugs such as 'software-development') + 114 interest tags (parent = their broad interest). The tag slugs are the recommender's own
-- (backend/crud/taxonomy.py of the recommender branch), so engine 3.0, the tag suggester (/api/tags/suggest) and the onboarding screen share one set of ids.
-- Additive and safe to run twice: rows that already exist (same slug OR same name) are left untouched, and a NOTICE reports any slug that could not be inserted.
BEGIN;

INSERT INTO domains (name, slug, parent_id, description) VALUES
{(',' + chr(10)).join(f"  ({q(n)}, {q(s)}, NULL, {q(d)})" for n, s, d in broad)}
ON CONFLICT DO NOTHING;

INSERT INTO domains (name, slug, parent_id, description)
SELECT v.name, v.slug, p.id, NULL
FROM (VALUES
{(',' + chr(10)).join(f"  ({q(n)}, {q(s)}, {q(b)})" for n, s, b in child)}
) AS v(name, slug, parent_slug)
JOIN domains p ON p.slug = v.parent_slug
ON CONFLICT DO NOTHING;

DO {D}
DECLARE missing int;
BEGIN
  SELECT count(*) INTO missing FROM (VALUES {", ".join("(" + q(s) + ")" for s in allslugs)}) AS t(slug)
  WHERE NOT EXISTS (SELECT 1 FROM domains d WHERE d.slug = t.slug);
  IF missing > 0 THEN
    RAISE NOTICE '012_domains_catalogue: % taxonomy slugs could not be inserted (a different row already uses the same name or slug): %', missing,
      (SELECT string_agg(t.slug, ', ') FROM (VALUES {", ".join("(" + q(s) + ")" for s in allslugs)}) AS t(slug) WHERE NOT EXISTS (SELECT 1 FROM domains d WHERE d.slug = t.slug));
  END IF;
END
{D};

COMMIT;
"""
open(out, "w", encoding="utf8", newline="\n").write(sql)
print("written", out, "|", len(broad), "broad +", len(child), "child |", sql.count("\n"), "lines")