`scripts/diagnostics/parse-ddr-cascade-map.mjs`

141 lines
#!/usr/bin/env node
// parse-ddr-cascade-map.mjs — turn a FileMaker Database Design Report (DDR, XML) into
// the CASCADE-DELETE IMPACT MAP that TK-10922's DTD verdict made a precondition.
//
// Cody's objection to "just repair the graph" was that nobody had produced this map:
// you cannot safely repair a cascade relationship without first knowing what it deletes.
// This script answers three questions from a read-only DDR export:
//
//   1. Which table occurrences are DANGLING (no resolvable base table / missing source)?
//   2. Which relationships have CASCADE DELETE enabled, and what would they delete?
//   3. Which of those two sets INTERSECT -> the culprit behind FileMaker error [110].
//
// Read-only. Parses a local file. Touches no database. Zero dependencies.
//
// Usage:
//   node scripts/diagnostics/parse-ddr-cascade-map.mjs ~/Desktop/ddr/WALLPAPER.xml
//   node scripts/diagnostics/parse-ddr-cascade-map.mjs <ddr.xml> --base WALLPAPER

import { readFileSync, existsSync } from 'node:fs';

const file = process.argv[2];
const baseArg = (() => { const i = process.argv.indexOf('--base'); return i >= 0 ? process.argv[i + 1] : null; })();

if (!file || !existsSync(file)) {
  console.error('usage: node parse-ddr-cascade-map.mjs <DDR.xml> [--base <BaseTableName>]');
  console.error('\nProduce the DDR first (read-only, changes nothing):');
  console.error('  FileMaker Pro > open WALLPAPER > Tools > Database Design Report...');
  console.error('  format XML, select only the WALLPAPER file, save to ~/Desktop/ddr/');
  process.exit(2);
}
const xml = readFileSync(file, 'utf8');

// ---- tolerant attribute reader (DDR attribute spelling shifts across FM versions) ----
const attr = (tag, ...names) => {
  for (const n of names) {
    const m = tag.match(new RegExp(`\\b${n}\\s*=\\s*"([^"]*)"`, 'i'));
    if (m) return m[1];
  }
  return '';
};
const truthy = (v) => /^(true|yes|1)$/i.test(String(v || '').trim());
// a base table that is blank, or literally "<Table Missing>", is dangling
const MISSING = (v) => {
  const s = String(v || '').trim().replace(/&lt;/g, '<').replace(/&gt;/g, '>');
  return !s || /^<.*missing.*>$/i.test(s) || /missing/i.test(s);
};

// ---- 1. table occurrences ----
const tos = [];
for (const m of xml.matchAll(/<TableOccurrence\b([^>]*)\/?>/gi)) {
  const t = m[1];
  const name = attr(t, 'name');
  if (!name) continue;
  tos.push({
    name,
    baseTable: attr(t, 'baseTable', 'baseTableName', 'table'),
    source: attr(t, 'dataSource', 'source', 'fileName'),
  });
}
const toByName = new Map(tos.map((t) => [t.name, t]));
const dangling = tos.filter((t) => MISSING(t.baseTable));

// ---- 2. relationships + their cascade-delete flags ----
// DDR encodes cascade delete per SIDE: deleting a record in the OTHER side's table
// deletes matching records in THIS side's table.
const rels = [];
for (const m of xml.matchAll(/<Relationship\b[^>]*>([\s\S]*?)<\/Relationship>/gi)) {
  const body = m[1];
  const sides = [];
  for (const s of body.matchAll(/<(LeftTable|RightTable)\b([^>]*)\/?>/gi)) {
    sides.push({
      side: s[1],
      name: attr(s[2], 'name', 'table'),
      cascadeDelete: truthy(attr(s[2], 'allowDelete', 'delete', 'cascadeDelete', 'deleteRelated')),
      allowCreate: truthy(attr(s[2], 'allowCreate', 'create')),
    });
  }
  if (sides.length === 2) rels.push({ sides });
}

// ---- report ----
const P = (s = '') => console.log(s);
P('');
P('=== FileMaker DDR cascade-delete impact map ===');
P(`source: ${file}`);
P(`table occurrences: ${tos.length}   relationships: ${rels.length}`);
if (!tos.length || !rels.length) {
  P('');
  P('!! Parsed 0 table occurrences or 0 relationships. The DDR may be HTML rather than XML,');
  P('!! or a newer schema. Re-export as XML; if it still parses empty, say so and I will');
  P('!! adapt the parser to the actual tag names in your export.');
}

P('');
P('--- 1. DANGLING table occurrences (unresolvable base table) ---');
if (!dangling.length) P('  none found');
for (const d of dangling) P(`  ⚠ "${d.name}"   baseTable="${d.baseTable}"   source="${d.source}"`);

P('');
P('--- 2. CASCADE-DELETE relationships (what a delete would take with it) ---');
const cascades = [];
for (const r of rels) {
  const [a, b] = r.sides;
  // side X cascadeDelete => deleting in the OTHER side deletes matching records in X
  if (a.cascadeDelete) cascades.push({ trigger: b.name, deletes: a.name });
  if (b.cascadeDelete) cascades.push({ trigger: a.name, deletes: b.name });
}
if (!cascades.length) P('  none — no relationship in this file cascades deletes');
for (const c of cascades) {
  const dead = MISSING(toByName.get(c.deletes)?.baseTable ?? 'unknown-TO');
  P(`  deleting a record in "${c.trigger}"  ->  ALSO DELETES matching in "${c.deletes}"${dead ? '   <== TARGET IS DANGLING' : ''}`);
}

P('');
P('--- 3. CULPRIT CANDIDATES for FileMaker error [110] ---');
const culprits = cascades.filter((c) => MISSING(toByName.get(c.deletes)?.baseTable ?? 'unknown'));
if (!culprits.length) {
  P('  No cascade-delete relationship points at a dangling TO.');
  P('  If deletes still fail with [110], the break is a NON-cascade relationship evaluated');
  P('  at delete time, or a dangling entry in File > Manage > External Data Sources.');
  if (dangling.length) {
    P('  Start with the dangling TOs listed in section 1 — they are still the prime suspects.');
  }
} else {
  for (const c of culprits) {
    P(`  ★ "${c.trigger}" --cascade--> "${c.deletes}"  (base table unresolvable)`);
  }
  P('');
  P('  DISARM, DO NOT REPOINT (per the DTD verdict):');
  P('    • Unticking the cascade box, or deleting the dangling TO, DISARMS an inert cascade.');
  P('    • Repointing it at a real table ARMS it across every future delete in this file.');
  P('    These are opposite outcomes. Section 2 above is what would become live if you repoint.');
}

if (baseArg) {
  P('');
  P(`--- TOs on base table "${baseArg}" (the delete walks outward from these) ---`);
  for (const t of tos.filter((t) => t.baseTable === baseArg)) P(`  ${t.name}`);
}
P('');