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")