Spaces:

lucharo
/

etymology

Running

App Files Files Community

etymology / backend /sql /queries /traverse_etymology.sql

lucharo

Luis Chaves Rodriguez

Super-squash branch 'main' using huggingface_hub

13812dc 9 months ago

Raw History Blame Contribute Delete

1.54 kB

WITH RECURSIVE
-- Resolve negative targets through sequences table
resolved_links AS (
    -- Simple links (positive target = direct word reference)
    SELECT source, target AS parent_ix, FALSE AS is_compound, type
    FROM links
    WHERE target > 0
    UNION ALL
    -- Compound links (negative target = sequence, resolve to parents)
    SELECT l.source, s.parent_ix, TRUE AS is_compound, l.type
    FROM links l
    JOIN sequences s ON s.seq_ix = l.target
    WHERE l.target < 0
),
traversal(child_ix, parent_ix, is_compound, type, lvl) AS (
    SELECT source, parent_ix, is_compound, type, 1
    FROM resolved_links
    WHERE source = ?
    UNION ALL
    -- Only follow FROM parents that have valid sense (non-NULL for English)
    -- This keeps sense=NULL entries as nodes but doesn't traverse their garbage links
    SELECT rl.source, rl.parent_ix, rl.is_compound, rl.type, lvl + 1
    FROM traversal t
    JOIN resolved_links rl ON rl.source = t.parent_ix
    JOIN words parent_word ON parent_word.word_ix = t.parent_ix
    WHERE lvl < ?
      AND (parent_word.lang != 'en' OR parent_word.sense IS NOT NULL)
)
SELECT
    child.word_ix AS child_ix,
    child.lexeme AS child_lexeme,
    child.lang AS child_lang,
    child.sense AS child_sense,
    parent.word_ix AS parent_ix,
    parent.lexeme AS parent_lexeme,
    parent.lang AS parent_lang,
    parent.sense AS parent_sense,
    tr.is_compound,
    tr.type
FROM traversal tr
JOIN words child ON child.word_ix = tr.child_ix
JOIN words parent ON parent.word_ix = tr.parent_ix

	WITH RECURSIVE
	-- Resolve negative targets through sequences table
	resolved_links AS (
	-- Simple links (positive target = direct word reference)
	SELECT source, target AS parent_ix, FALSE AS is_compound, type
	FROM links
	WHERE target > 0
	UNION ALL
	-- Compound links (negative target = sequence, resolve to parents)
	SELECT l.source, s.parent_ix, TRUE AS is_compound, l.type
	FROM links l
	JOIN sequences s ON s.seq_ix = l.target
	WHERE l.target < 0
	),
	traversal(child_ix, parent_ix, is_compound, type, lvl) AS (
	SELECT source, parent_ix, is_compound, type, 1
	FROM resolved_links
	WHERE source = ?
	UNION ALL
	-- Only follow FROM parents that have valid sense (non-NULL for English)
	-- This keeps sense=NULL entries as nodes but doesn't traverse their garbage links
	SELECT rl.source, rl.parent_ix, rl.is_compound, rl.type, lvl + 1
	FROM traversal t
	JOIN resolved_links rl ON rl.source = t.parent_ix
	JOIN words parent_word ON parent_word.word_ix = t.parent_ix
	WHERE lvl < ?
	AND (parent_word.lang != 'en' OR parent_word.sense IS NOT NULL)
	)
	SELECT
	child.word_ix AS child_ix,
	child.lexeme AS child_lexeme,
	child.lang AS child_lang,
	child.sense AS child_sense,
	parent.word_ix AS parent_ix,
	parent.lexeme AS parent_lexeme,
	parent.lang AS parent_lang,
	parent.sense AS parent_sense,
	tr.is_compound,
	tr.type
	FROM traversal tr
	JOIN words child ON child.word_ix = tr.child_ix
	JOIN words parent ON parent.word_ix = tr.parent_ix