"""Make the topic tree say one thing once, and match the ABP outline. The tree was grown from a model's per-question tags rather than written, so it reads like transcription rather than a taxonomy. Three faults, all visible in the question counts: *Same idea, two names.* "Nocturnal Enuresis" beside "Enuresis"; "Adhd" beside "Attention-Deficit/Hyperactivity Disorder"; "Down Syndrome" in Neonatology and "Trisomy 21" in Genetics; and twenty-odd "Pediatric Cardiology"-style topics sitting directly under a root of the same name, on a site that is entirely paediatric. A learner filtering on either half sees half the questions, which is the complaint that started this. *Filed where it does not belong.* Immunisation content under Allergy, dentistry and medication error under "Professional Topics", child abuse under Primary Care, a nasal fracture under Critical Care. *Blueprint holes.* Two ABP domains had no topic of their own at all — Ethics and Patient Safety/QI/Research both claimed "Professional Topics", Nephrology and Genitourinary both claimed "Nephrology & Urology" — so six per cent of the paper rested on two rows and could not be split by arithmetic. Psychosocial Issues and Child Abuse had scaffolding with nothing in it: zero questions against a 3% weight each. What this does NOT do is guess. Every merge below is an explicit (survivor, loser) pair with both ids and both names written out, and the script refuses the pair if a name no longer matches its id. No normaliser, no fuzzy key: a normaliser that folds punctuation and digits turns Trisomy 18 into Trisomy 21 and Type 1 diabetes into Type 2, and that is not a merge, it is a loss. Pairs that are arguably one topic and arguably two — GER against GERD, Respiratory Distress against Respiratory Distress Syndrome, Varicella against Varicella Zoster — are deliberately absent, and listed for the owner instead. Nothing is thrown away. A merge moves the questions first and only deletes an empty row; where the losing copy hung under a different branch, the questions gain an extra `question_category_links` row to that branch, so the association that put it there survives as a cross-filing. A rehome does the same: the old parent becomes a link, so the old branch still finds its questions. Questions are also cross-filed into the new professional and psychosocial topics from the `subject` tags that already mark them — read-only on the tag tables, which are not ours to edit. Idempotent, and a dry run by default: docker compose exec backend python -m scripts.reorganize_topics docker compose exec backend python -m scripts.reorganize_topics --apply """ import sys from sqlalchemy import text as sa_text from app.database import SessionLocal #: Tables that point a row at one category and would be orphaned by a delete. REFERRING = [("articles", "category_id"), ("flashcard_decks", "category_id"), ("media_assets", "category_id"), ("category_grants", "category_id"), ("draft_batches", "category_id"), ("draft_questions", "category_id")] #: Topics to create if absent: (name, parent id, parent name, system tag). #: The three ABP professional domains had nowhere of their own to live. NEW_TOPICS = [ ("Ethics", 14896, "Professional Topics", "Social Sciences & Ethics"), ("Patient Safety & Quality Improvement", 14896, "Professional Topics", "Social Sciences & Ethics"), ("Research & Biostatistics", 14896, "Professional Topics", "Biostatistics & Epidemiology"), ] #: (survivor id, survivor name, [(loser id, loser name)], why). #: Both names are checked against the database before anything moves. MERGES = [ # --- "Pediatric X" under a root already called X ------------------------- (1, "Infectious Disease", [(14903, "Pediatric Infectious Disease")], "the root is already the paediatric one"), (14874, "Allergy/Immunology", [(14914, "Pediatric Allergy And Immunology")], "the root is already the paediatric one"), (14875, "Cardiology", [(14901, "Pediatric Cardiology")], "the root is already the paediatric one"), (14876, "Critical Care", [(14904, "Pediatric Critical Care")], "the root is already the paediatric one"), (14877, "Dermatology", [(14923, "Pediatric Dermatology")], "the root is already the paediatric one"), (14879, "Emergency Medicine", [(14910, "Pediatric Emergency Medicine")], "the root is already the paediatric one"), (14880, "Endocrinology", [(14906, "Pediatric Endocrinology")], "the root is already the paediatric one"), (14881, "Gastroenterology & Nutrition", [(14907, "Pediatric Gastroenterology")], "the root is already the paediatric one"), (14883, "Hematology-Oncology", [(14922, "Pediatric Hematology-Oncology"), (14920, "Pediatric Hematology"), (14912, "Pediatric Oncology")], "the root is already the paediatric one, and names both halves"), (14886, "Neurology", [(14900, "Pediatric Neurology"), (14921, "Pediatric Neurosurgery")], "the root is already the paediatric one"), (14888, "Orthopedics & Sports", [(14917, "Pediatric Orthopedics")], "the root is already the paediatric one"), (14889, "Otolaryngology", [(14908, "Pediatric Otolaryngology")], "the root is already the paediatric one"), (14890, "Psychiatry & Psychology", [(14899, "Pediatric Psychiatry")], "the root is already the paediatric one"), (14891, "Pulmonology", [(14916, "Pediatric Pulmonology")], "the root is already the paediatric one"), (14892, "Rheumatology", [(14915, "Pediatric Rheumatology")], "the root is already the paediatric one"), (14893, "Surgery", [(14898, "Pediatric Surgery")], "the root is already the paediatric one"), (16002, "Inborn Errors of Metabolism", [(14926, "Pediatric Metabolic Disorders")], "the bucket is the same subject under a tidier name"), (15971, "Nutrition & Growth", [(14918, "Pediatric Nutrition")], "the bucket is the same subject under a tidier name"), (15969, "Liver Disease", [(14924, "Pediatric Hepatology")], "hepatology is liver disease"), (15897, "Trauma", [(14913, "Pediatric Trauma"), (14919, "Pediatric Trauma Surgery")], "one-question copies of a topic that already exists"), (16004, "Sexual & Reproductive Health", [(14925, "Pediatric Gynecology")], "a two-question sliver inside its own sibling's scope"), # --- the same condition written twice ------------------------------------ (15338, "Upper Respiratory Infection", [(15656, "Upper Respiratory Tract Infection")], "the same words with 'Tract' added"), (15378, "Pharyngitis", [(15666, "Acute Pharyngitis")], "paediatric pharyngitis is acute pharyngitis"), (15095, "Hepatitis C", [(14948, "Hepatitis C Virus Infection")], "one virus, two names"), (15267, "Chlamydia Infection", [(15916, "Chlamydia Trachomatis Infection"), (15357, "Chlamydial Infection")], "one organism filed three times, once per discipline that mentioned it"), (15118, "Group B Streptococcal Infection", [(15676, "Group B Streptococcus"), (15389, "Group B Streptococcus Infection")], "one organism, three spellings"), (14953, "Sexually Transmitted Infection", [(15570, "Sexually Transmitted Infections")], "singular and plural"), (15761, "Influenza", [(15765, "Influenza A")], "there is no Influenza B topic to tell it apart from"), (15623, "Hiv Infection", [(15944, "Hiv/Aids")], "one disease, two names"), (15958, "Bacterial Infections", [(15303, "Bacterial Infection")], "a generic leaf beside the bucket that means the same thing"), (15959, "Viral Infections", [(15793, "Viral Infection")], "a generic leaf beside the bucket that means the same thing"), (15993, "Mood Disorders", [(15241, "Mood Disorder")], "a generic leaf beside the bucket that means the same thing"), (15994, "Anxiety Disorders", [(15041, "Anxiety Disorder"), (15717, "Anxiety")], "a generic leaf beside the bucket that means the same thing"), (15997, "Eating Disorders", [(15705, "Eating Disorder")], "a generic leaf beside the bucket that means the same thing"), (15949, "Arrhythmias", [(15693, "Arrhythmia"), (15136, "Cardiac Arrhythmia")], "a generic leaf beside the bucket that means the same thing"), (15975, "Growth Disorders", [(15156, "Growth Disorder")], "a generic leaf beside the bucket that means the same thing"), (16003, "Connective Tissue Disorders", [(15838, "Connective Tissue Disorder")], "a generic leaf beside the bucket that means the same thing"), (15979, "Anaemia", [(15134, "Anemia")], "the bucket and the leaf are the same word, spelled two ways"), (14988, "Hemolytic Anemia", [(14990, "Autoimmune Hemolytic Anemia")], "a one-question subtype of its own sibling"), (15433, "Acute Lymphoblastic Leukemia", [(15133, "Acute Lymphocytic Leukemia")], "two names for ALL"), (15942, "Respiratory Infection", [(15837, "Respiratory Tract Infection"), (15875, "Viral Respiratory Infection")], "two question-less generics inside a generic"), (15524, "Respiratory Syncytial Virus Infection", [(15688, "Respiratory Syncytial Virus (Rsv) Infection")], "the same name with the acronym"), (15597, "Phenylketonuria", [(15229, "Phenylketonuria (Pku)")], "the same name with the acronym"), (15415, "Enuresis", [(15716, "Nocturnal Enuresis")], "paediatric enuresis is nocturnal enuresis"), (15769, "Proteinuria", [(15083, "Asymptomatic Proteinuria")], "a three-question restatement of its sibling"), (15084, "Glomerulonephritis", [(15291, "Acute Glomerulonephritis")], "a question-less restatement of its sibling"), (15289, "Post-Streptococcal Glomerulonephritis", [(15817, "Post-Infectious Glomerulonephritis")], "in paediatrics these are the same entity"), (15443, "Cryptorchidism", [(15594, "Undescended Testis")], "one condition, two names"), (15695, "Migraine", [(15037, "Migraine Headache")], "one condition, two names"), (14979, "Seizure Disorder", [(15681, "Seizures")], "one condition, two names"), (15555, "Respiratory Failure", [(15926, "Acute Respiratory Failure")], "a one-question restatement of its sibling"), (15151, "Asthma Exacerbation", [(15497, "Acute Asthma Exacerbation")], "an exacerbation is acute"), (15885, "Exercise-Induced Asthma", [(15568, "Exercise-Induced Bronchoconstriction")], "the old and the new name for one thing"), (15364, "Pyloric Stenosis", [(15784, "Infantile Hypertrophic Pyloric Stenosis")], "the short and the long name for one thing"), (15902, "Dysmenorrhea", [(15826, "Primary Dysmenorrhea")], "there is no secondary dysmenorrhea topic to tell it apart from"), (15935, "Abnormal Uterine Bleeding", [(15333, "Dysfunctional Uterine Bleeding")], "DUB is the retired name for AUB"), (15453, "Amenorrhea", [(15002, "Primary Amenorrhea")], "keeping the general name so secondary cases are not mislabelled"), (15646, "Hemangioma", [(15752, "Infantile Hemangioma")], "paediatric haemangiomas are infantile haemangiomas"), (15447, "Melanoma", [(15449, "Skin Cancer")], "a one-question generic beside the only skin cancer in the bank"), (15194, "Speech Delay", [(15101, "Language Delay"), (15390, "Speech And Language Delay")], "one presentation under three names; survivor is renamed below"), (15016, "Normal Child Development", [(14942, "Normal Development")], "the same topic with one word dropped"), (15795, "Learning Disability", [(15285, "Learning Disorder")], "one idea, two names"), (15108, "Attention-Deficit/Hyperactivity Disorder", [(14968, "Adhd"), (15620, "Attention Deficit")], "the name, the acronym and half the name"), (15042, "Depression", [(15069, "Major Depressive Disorder"), (15549, "Adolescent Depression")], "in a paediatric bank all depression is child and adolescent depression"), (15275, "Sepsis", [(15948, "Hyperinflammatory Sepsis")], "a one-question orphan with no links of its own"), (15841, "Drowning", [(15348, "Near-Drowning")], "'near-drowning' was retired from the drowning definition"), (14952, "Dehydration", [(15869, "Severe Dehydration")], "a severity grade is not a separate topic"), (14932, "Acute Gastroenteritis", [(15057, "Gastroenteritis")], "one topic either side of two roots"), (15631, "Trisomy 21", [(15258, "Down Syndrome")], "one syndrome under both its names; survivor is renamed below"), (15496, "Milk Protein Allergy", [(15938, "Cow Milk Protein Sensitivity")], "one condition, two names"), (15060, "Atopic Dermatitis", [(15092, "Eczema")], "one condition, two names"), (15868, "Cervical Lymphadenitis", [(15208, "Lymphadenitis")], "a one-question generic; paediatric lymphadenitis is cervical"), (15355, "Neonatal Conjunctivitis", [(15362, "Ophthalmia Neonatorum")], "one condition, two names"), (15269, "Idiopathic Intracranial Hypertension", [(15268, "Intracranial Hypertension")], "a question-less generic over its only instance"), (15220, "Obesity", [(15729, "Childhood Obesity")], "in a paediatric bank all obesity is childhood obesity"), (15279, "Type 1 Diabetes Mellitus", [(15405, "Type 1 Diabetes")], "with and without 'Mellitus'; type 2 is kept strictly apart"), (14991, "Type 2 Diabetes Mellitus", [(15300, "Type 2 Diabetes")], "with and without 'Mellitus'; type 1 is kept strictly apart"), (15065, "Intestinal Obstruction", [(15404, "Bowel Obstruction")], "one condition, two names"), # --- abuse content moved onto the blueprint's own scaffolding ------------- (16015, "Sexual Abuse", [(15104, "Child Sexual Abuse")], "the ABP's own heading, which had been left empty"), (16013, "Child Abuse and Neglect", [(15640, "Child Abuse")], "the ABP's own heading, which had been left empty"), (15995, "Neurodevelopmental", [(15852, "Neurodevelopmental Disorder")], "a generic leaf beside the bucket that means the same thing"), ] #: (id, name, new parent id, new parent name, why). The old parent is kept as a #: cross-filing so the branch it left still reaches the questions. REHOME = [ (15691, "Vaccine-Preventable Diseases", 14895, "Primary Care & Prevention", "immunisation is ABP 1.C, not a disorder of immune function"), (14905, "Pediatric Dentistry", 14895, "Primary Care & Prevention", "a clinical subject filed under 'professional' because nothing else fitted"), (15263, "Dental Fluorosis", 14895, "Primary Care & Prevention", "fluoride is anticipatory guidance, ABP 1.E"), (15733, "Medication Error", None, "Patient Safety & Quality Improvement", "an adverse event, ABP 24.A"), (15467, "Intimate Partner Violence", 16008, "Family Circumstances", "family violence is ABP 6.A"), (15867, "Grief Reaction", 16008, "Family Circumstances", "death in the family is ABP 6.A"), (15440, "Metabolic Syndrome", 15973, "Diabetes", "ABP names it in 18.E beside type 2 diabetes"), (15753, "Nasal Fracture", 14889, "Otolaryngology", "a nose is not critical care"), (15510, "Inguinal Hernia", 15986, "Genital & Scrotal", "a groin, not a loose end of Surgery"), (15108, "Attention-Deficit/Hyperactivity Disorder", 15995, "Neurodevelopmental", "the bucket that was built for it"), ] #: (id, old name, new name). House spelling, capitalised acronyms, and the two #: survivors whose merge widened their scope. RENAMES = [ (15979, "Anaemia", "Anemia"), (15981, "Leukaemia & Lymphoma", "Leukemia & Lymphoma"), (15982, "Solid Tumours", "Solid Tumors"), (15967, "Diarrhoea & Gastroenteritis", "Diarrhea & Gastroenteritis"), (15996, "Behaviour Disorders", "Behavior Disorders"), (15623, "Hiv Infection", "HIV Infection"), (15877, "G6Pd Deficiency", "G6PD Deficiency"), (15515, "Iga Nephropathy", "IgA Nephropathy"), (15586, "Iga Vasculitis", "IgA Vasculitis"), (15799, "Long Qt Syndrome", "Long QT Syndrome"), (15329, "Digeorge Syndrome", "DiGeorge Syndrome"), (15697, "Nursemaid'S Elbow", "Nursemaid's Elbow"), (15194, "Speech Delay", "Speech & Language Delay"), (15631, "Trisomy 21", "Down Syndrome (Trisomy 21)"), (15995, "Neurodevelopmental", "Neurodevelopmental Disorders"), ] #: (id, name, system tag name). Topics whose organ-system axis was plainly #: wrong, plus the dozen that never got one. SYSTEMS = [ (15248, "Vesicoureteral Reflux", "Renal & Urinary System"), (15933, "Osteosarcoma", "Musculoskeletal System"), (15269, "Idiopathic Intracranial Hypertension", "Nervous System & Special Senses"), (15719, "Breastfeeding Difficulty", "Gastrointestinal System"), (14893, "Surgery", "Multisystem Processes & Disorders"), (14895, "Primary Care & Prevention", "Social Sciences & Ethics"), (14897, "General Pediatrics", "Multisystem Processes & Disorders"), (15440, "Metabolic Syndrome", "Endocrine System"), (15467, "Intimate Partner Violence", "Social Sciences & Ethics"), (15510, "Inguinal Hernia", "Gastrointestinal System"), (15528, "Lead Poisoning", "Multisystem Processes & Disorders"), ] #: Tag name -> topic to cross-file its questions into. Links only: the tag rows #: themselves are another axis and are not edited. This is what gives the three #: new professional topics and the two empty ABP scaffolds their content. TAG_FILE = { "Ethics": ["Ethics", "Medical Ethics", "Research Ethics"], "Patient Safety & Quality Improvement": ["Patient Safety", "Quality Improvement", "Safety"], "Research & Biostatistics": ["Biostatistics", "Clinical Research", "Research Methods", "Epidemiology", "Clinical Epidemiology", "Evidence-Based Medicine"], "Primary Care & Prevention": ["Preventive Medicine", "Immunization", "Injury Prevention", "Newborn Screening"], "Child Abuse and Neglect": ["Child Abuse And Neglect", "Child Abuse Medicine", "Child Abuse", "Child Abuse & Neglect"], "Family Circumstances": ["Parental Divorce Impact On Children", "Parental Divorce Adjustment", "Parental Divorce Stress", "Parental Divorce Effects", "Adoption Medicine", "Sibling Conflict", "Grief/Bereavement", "Grief Reaction", "Family Medicine"], "Impact of Chronic Illness and Disability": ["Palliative Care", "Care Coordination"], "Social Determinants of Health": ["Food Insecurity", "Social Medicine", "School Violence"], "Equity and Racism in Medicine": ["Health Equity", "Health Disparities"], "Social Media and Wellbeing": ["Bullying"], } #: ABP domain code -> the topics it is about. Replaces whatever is mapped. #: Every domain gets a branch nothing else claims: the two roots that had been #: claimed twice, Professional Topics and Nephrology & Urology, are now mapped #: through their children instead of themselves. BLUEPRINT = { "1": [14895], "2": [14884], "3": [14873], "4": [1], "5": [14878, 14890], "6": [16007], "7": [14876, 14879, 14894], "8": [16013], "9": [14888], "10": [14887, 14889], "11": [14875], "12": [14891], "13": [14881], "14": [14886], "15": [14877], "16": [14883], "17": [14874], "18": [14880], "19": [14911, 15012, 15250, 15415, 15455, 15983, 15984], "20": [14909, 15471, 15565, 15985, 15986], "21": [14882], "22": [14892], "23": ["Ethics"], "24": ["Patient Safety & Quality Improvement", "Research & Biostatistics"], } def scalar(db, sql, **args): return db.execute(sa_text(sql), args).scalar() def row(db, sql, **args): return db.execute(sa_text(sql), args).mappings().first() def cat(db, cid): return row(db, "SELECT id, name, parent_id, system_id FROM question_categories WHERE id = :i", i=cid) #: A survivor renamed by this script answers to both names on a rerun, so the #: id/name guard below does not mistake its own work for a mismatch. ALIASES: dict[int, set[str]] = {} for _cid, _old, _new in RENAMES: ALIASES.setdefault(_cid, set()).update({_old, _new}) RENAMED_TO = {old: new for _cid, old, new in RENAMES} def is_named(node, expected: str) -> bool: return node["name"] == expected or expected in ALIASES.get(node["id"], set()) def by_name(db, name): found = row(db, "SELECT id, name, parent_id, system_id FROM question_categories " "WHERE name = :n ORDER BY id LIMIT 1", n=name) if found is None and name in RENAMED_TO: found = row(db, "SELECT id, name, parent_id, system_id FROM question_categories " "WHERE name = :n ORDER BY id LIMIT 1", n=RENAMED_TO[name]) return found def ancestors(db, cid): """The chain above a category, so a cross-file link is never redundant.""" seen, node = set(), cat(db, cid) while node and node["parent_id"] and node["parent_id"] not in seen: seen.add(node["parent_id"]) node = cat(db, node["parent_id"]) return seen def counts(db, cid): direct = scalar(db, "SELECT count(*) FROM questions WHERE question_category_id = :i " "AND deleted_at IS NULL", i=cid) linked = scalar(db, "SELECT count(*) FROM question_category_links WHERE category_id = :i", i=cid) kids = scalar(db, "SELECT count(*) FROM question_categories WHERE parent_id = :i", i=cid) return direct, linked, kids def cross_file(db, category_id, target, write): """Keep the association a move would otherwise drop, as an extra link.""" if not target: return 0 sql = """ SELECT count(*) FROM ( SELECT q.id FROM questions q WHERE q.question_category_id = :src AND q.deleted_at IS NULL UNION SELECT k.question_id FROM question_category_links k WHERE k.category_id = :src ) s WHERE NOT EXISTS (SELECT 1 FROM question_category_links e WHERE e.question_id = s.id AND e.category_id = :dst) """ n = scalar(db, sql, src=category_id, dst=target) if write and n: db.execute(sa_text(""" INSERT INTO question_category_links (question_id, category_id) SELECT s.id, :dst FROM ( SELECT q.id FROM questions q WHERE q.question_category_id = :src AND q.deleted_at IS NULL UNION SELECT k.question_id FROM question_category_links k WHERE k.category_id = :src ) s ON CONFLICT DO NOTHING """), {"src": category_id, "dst": target}) return n def absorb(db, keep_id, loser_id, write): """Move everything that points at the loser onto the survivor.""" if not write: return # A link to the survivor may already exist; the unique pair would refuse the # move, so the row that would collide goes first. db.execute(sa_text(""" DELETE FROM question_category_links a WHERE a.category_id = :loser AND EXISTS ( SELECT 1 FROM question_category_links b WHERE b.question_id = a.question_id AND b.category_id = :keep) """), {"loser": loser_id, "keep": keep_id}) db.execute(sa_text("UPDATE question_category_links SET category_id = :keep " "WHERE category_id = :loser"), {"keep": keep_id, "loser": loser_id}) db.execute(sa_text("UPDATE questions SET question_category_id = :keep " "WHERE question_category_id = :loser"), {"keep": keep_id, "loser": loser_id}) # A question whose primary category became the survivor does not also need # an extra link to it. db.execute(sa_text(""" DELETE FROM question_category_links a USING questions q WHERE a.question_id = q.id AND a.category_id = :keep AND q.question_category_id = :keep """), {"keep": keep_id}) for table, column in REFERRING: db.execute(sa_text(f"UPDATE {table} SET {column} = :keep WHERE {column} = :loser"), {"keep": keep_id, "loser": loser_id}) db.execute(sa_text(""" DELETE FROM blueprint_category_links a WHERE a.category_id = :loser AND EXISTS ( SELECT 1 FROM blueprint_category_links b WHERE b.blueprint_id = a.blueprint_id AND b.category_id = :keep) """), {"loser": loser_id, "keep": keep_id}) db.execute(sa_text("UPDATE blueprint_category_links SET category_id = :keep " "WHERE category_id = :loser"), {"keep": keep_id, "loser": loser_id}) def step(title): print(f"\n{title}\n{'-' * len(title)}") def main(apply: bool) -> int: db = SessionLocal() problems: list[str] = [] # Everything below runs for real inside one transaction and is rolled back # unless --apply, so a dry run reports the numbers it would actually leave # behind rather than a guess at them. write = True try: before_topics = scalar(db, "SELECT count(*) FROM question_categories") before_roots = scalar(db, "SELECT count(*) FROM question_categories WHERE parent_id IS NULL") before_questions = scalar(db, "SELECT count(*) FROM questions WHERE deleted_at IS NULL") before_primary = scalar(db, "SELECT count(*) FROM questions " "WHERE question_category_id IS NOT NULL AND deleted_at IS NULL") owner = scalar(db, "SELECT id FROM users ORDER BY id LIMIT 1") if not owner: print("! no users, so nothing can own a category") return 1 step("topics to create") made = 0 for name, parent_id, parent_name, system in NEW_TOPICS: if by_name(db, name): print(f" {name:<38} already there") continue parent = cat(db, parent_id) if not parent or parent["name"] != parent_name: problems.append(f"parent {parent_id} is not '{parent_name}'") print(f" ! {name:<36} parent {parent_id} is not '{parent_name}', skipped") continue print(f" {name:<38} under {parent_name}") made += 1 if write: sid = scalar(db, "SELECT id FROM question_tags WHERE name = :n AND type = 'system'", n=system) db.execute(sa_text( "INSERT INTO question_categories (name, parent_id, user_id, system_id, created_at) " "VALUES (:n, :p, :u, :s, NOW())"), {"n": name, "p": parent_id, "u": owner, "s": sid}) if write: db.flush() step("merges") moved = cross = gone = 0 for keep_id, keep_name, losers, why in MERGES: keep = cat(db, keep_id) if not keep: print(f" {keep_name:<40} already merged or missing") continue if not is_named(keep, keep_name): problems.append(f"#{keep_id} is '{keep['name']}', not '{keep_name}'") print(f" ! #{keep_id} is '{keep['name']}', expected '{keep_name}' — skipped") continue keep_up = ancestors(db, keep_id) | {keep_id} live = [] for loser_id, loser_name in losers: loser = cat(db, loser_id) if not loser: continue if not is_named(loser, loser_name): problems.append(f"#{loser_id} is '{loser['name']}', not '{loser_name}'") print(f" ! #{loser_id} is '{loser['name']}', expected '{loser_name}' — skipped") continue live.append(loser) if not live: continue print(f" keep #{keep_id} {keep_name} — {why}") for loser in live: direct, linked, kids = counts(db, loser["id"]) if kids: problems.append(f"#{loser['id']} {loser['name']} still has {kids} children") print(f" ! #{loser['id']} {loser['name']} has {kids} children — skipped") continue target = loser["parent_id"] if loser["parent_id"] not in keep_up else None n = cross_file(db, loser["id"], target, write) absorb(db, keep_id, loser["id"], write) if write: left = counts(db, loser["id"]) if left[0] or left[2]: raise RuntimeError(f"#{loser['id']} still holds {left}; refusing to delete") db.execute(sa_text("DELETE FROM question_categories WHERE id = :i"), {"i": loser["id"]}) if not keep["system_id"] and loser["system_id"]: db.execute(sa_text("UPDATE question_categories SET system_id = :s " "WHERE id = :i"), {"s": loser["system_id"], "i": keep_id}) keep = cat(db, keep_id) where = f" (kept a link to #{target})" if target else "" print(f" drop #{loser['id']:<6} {loser['name']:<44} " f"{direct} primary, {linked} linked{where}") moved += direct cross += n gone += 1 if write: db.flush() step("rehomes") for cid, name, parent_id, parent_name, why in REHOME: node = cat(db, cid) if not node or not is_named(node, name): print(f" {name:<38} not found under id {cid} — skipped") continue target = cat(db, parent_id) if parent_id else by_name(db, parent_name) if not target or not is_named(target, parent_name): problems.append(f"no parent '{parent_name}' for {name}") print(f" ! {name:<36} no parent '{parent_name}' — skipped") continue if node["parent_id"] == target["id"]: print(f" {name:<38} already under {parent_name}") continue old = node["parent_id"] keep_link = old if old and old not in (ancestors(db, target["id"]) | {target["id"]}) else None n = cross_file(db, cid, keep_link, write) print(f" {name:<38} → {parent_name} — {why}" + (f" (kept a link to #{keep_link}, {n} questions)" if keep_link else "")) if write: db.execute(sa_text("UPDATE question_categories SET parent_id = :p WHERE id = :i"), {"p": target["id"], "i": cid}) cross += n if write: db.flush() step("renames") for cid, old, new in RENAMES: node = cat(db, cid) if not node: continue if node["name"] == new: print(f" {new:<38} already renamed") continue if node["name"] != old: print(f" ! #{cid} is '{node['name']}', expected '{old}' — skipped") continue print(f" {old:<38} → {new}") if write: db.execute(sa_text("UPDATE question_categories SET name = :n WHERE id = :i"), {"n": new, "i": cid}) step("organ system corrections") for cid, name, system in SYSTEMS: node = cat(db, cid) if not node or not is_named(node, name): print(f" {name:<38} not found under id {cid} — skipped") continue sid = scalar(db, "SELECT id FROM question_tags WHERE name = :n AND type = 'system'", n=system) if not sid: problems.append(f"no system tag '{system}'") continue if node["system_id"] == sid: print(f" {name:<38} already {system}") continue print(f" {name:<38} → {system}") if write: db.execute(sa_text("UPDATE question_categories SET system_id = :s WHERE id = :i"), {"s": sid, "i": cid}) step("cross-filing by subject tag") for target_name, tags in TAG_FILE.items(): target = by_name(db, target_name) if not target: problems.append(f"no topic '{target_name}' to cross-file into") print(f" ! no topic '{target_name}'") continue n = scalar(db, """ SELECT count(DISTINCT l.question_id) FROM question_tag_links l JOIN question_tags t ON t.id = l.tag_id JOIN questions q ON q.id = l.question_id AND q.deleted_at IS NULL WHERE t.name = ANY(:names) AND NOT EXISTS (SELECT 1 FROM question_category_links e WHERE e.question_id = l.question_id AND e.category_id = :dst) AND q.question_category_id IS DISTINCT FROM :dst """, names=tags, dst=target["id"]) print(f" {target_name:<44} + {n} questions from {len(tags)} tags") if write and n: db.execute(sa_text(""" INSERT INTO question_category_links (question_id, category_id) SELECT DISTINCT l.question_id, :dst FROM question_tag_links l JOIN question_tags t ON t.id = l.tag_id JOIN questions q ON q.id = l.question_id AND q.deleted_at IS NULL WHERE t.name = ANY(:names) AND q.question_category_id IS DISTINCT FROM :dst ON CONFLICT DO NOTHING """), {"names": tags, "dst": target["id"]}) cross += n if write: db.flush() step("blueprint") resolved: dict[str, set[int]] = {} for code, wanted in BLUEPRINT.items(): line = row(db, "SELECT id, title FROM exam_blueprints " "WHERE exam_id = 1 AND code = :c AND parent_id IS NULL", c=code) if not line: problems.append(f"no ABP domain {code}") print(f" ! no domain {code}") continue ids = [] for want in wanted: node = cat(db, want) if isinstance(want, int) else by_name(db, want) if not node: problems.append(f"domain {code} wants a topic that does not exist: {want}") print(f" ! domain {code} wants {want!r}, which does not exist") continue ids.append(node["id"]) resolved[code] = set(ids) names = ", ".join(cat(db, i)["name"] for i in ids) print(f" {code:>2} {line['title'][:40]:<42} → {names}") if write: db.execute(sa_text("DELETE FROM blueprint_category_links WHERE blueprint_id = :b"), {"b": line["id"]}) for cid in ids: db.execute(sa_text( "INSERT INTO blueprint_category_links (blueprint_id, category_id) " "VALUES (:b, :c) ON CONFLICT DO NOTHING"), {"b": line["id"], "c": cid}) if write: db.flush() step("check") # Two domains sharing one branch is the fault this is here to end, so # the subtrees are expanded and compared, not just the mapped rows. spread: dict[str, set[int]] = {} for code, ids in resolved.items(): found, frontier = set(ids), list(ids) while frontier: kids = [r[0] for r in db.execute(sa_text( "SELECT id FROM question_categories WHERE parent_id = ANY(:p)"), {"p": frontier}).all()] new = [k for k in kids if k not in found] found.update(new) frontier = new spread[code] = found codes = sorted(spread, key=lambda c: int(c)) for i, a in enumerate(codes): for b in codes[i + 1:]: shared = spread[a] & spread[b] if shared: problems.append(f"domains {a} and {b} share {len(shared)} categories") print(f" ! domains {a} and {b} share: " + ", ".join(cat(db, c)["name"] for c in sorted(shared)[:4])) if not any(p.startswith("domains ") for p in problems): print(" no two domains share a category") after_topics = scalar(db, "SELECT count(*) FROM question_categories") after_roots = scalar(db, "SELECT count(*) FROM question_categories WHERE parent_id IS NULL") after_questions = scalar(db, "SELECT count(*) FROM questions WHERE deleted_at IS NULL") after_primary = scalar(db, "SELECT count(*) FROM questions " "WHERE question_category_id IS NOT NULL AND deleted_at IS NULL") empty = db.execute(sa_text(""" SELECT c.id, c.name FROM question_categories c WHERE NOT EXISTS (SELECT 1 FROM question_categories k WHERE k.parent_id = c.id) AND NOT EXISTS (SELECT 1 FROM questions q WHERE q.question_category_id = c.id AND q.deleted_at IS NULL) AND NOT EXISTS (SELECT 1 FROM question_category_links l WHERE l.category_id = c.id) ORDER BY c.name """)).mappings().all() print(f"\n topics {before_topics} → {after_topics}" f" ({gone} merged away, {made} created)") print(f" roots {before_roots} → {after_roots}") print(f" questions {before_questions} → {after_questions}" f" ({before_primary} → {after_primary} with a primary topic)") print(f" {moved} questions change primary topic, {cross} cross-filings added") print(f" empty leaf topics: {len(empty)}" + ("" if not empty else " — " + ", ".join(e["name"] for e in empty))) if problems: print("\n! " + "\n! ".join(problems)) if not apply: print("\ndry run. Pass --apply to write.") db.rollback() return 0 db.commit() print("\nwritten.") return 0 except Exception: db.rollback() raise finally: db.close() if __name__ == "__main__": sys.exit(main("--apply" in sys.argv))