def test_simple_cte_extraction(): sql = 'WITH a AS (SELECT abc FROM xxx) SELECT * FROM a' start_pos = len('WITH a AS ') stop_pos = len('WITH a AS (SELECT abc FROM xxx)') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == (('a', ('abc', ), start_pos, stop_pos), ) assert remainder.strip() == 'SELECT * FROM a'
def test_simple_cte_extraction(): sql = 'WITH a AS (SELECT abc FROM xxx) SELECT * FROM a' start_pos = len('WITH a AS ') stop_pos = len('WITH a AS (SELECT abc FROM xxx)') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == (('a', ('abc',), start_pos, stop_pos),) assert remainder.strip() == 'SELECT * FROM a'
def test_cte_extraction_around_comments(): sql = '''--blah blah blah WITH a AS (SELECT abc def FROM x) SELECT * FROM a''' start_pos = len('''--blah blah blah WITH a AS ''') stop_pos = len('''--blah blah blah WITH a AS (SELECT abc def FROM x)''') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == (('a', ('def', ), start_pos, stop_pos), ) assert remainder.strip() == 'SELECT * FROM a'
def test_cte_extraction_around_comments(): sql = '''--blah blah blah WITH a AS (SELECT abc def FROM x) SELECT * FROM a''' start_pos = len('''--blah blah blah WITH a AS ''') stop_pos = len('''--blah blah blah WITH a AS (SELECT abc def FROM x)''') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == (('a', ('def',), start_pos, stop_pos),) assert remainder.strip() == 'SELECT * FROM a'
def test_multiple_cte_extraction(): sql = '''WITH x AS (SELECT abc, def FROM x), y AS (SELECT ghi, jkl FROM y) SELECT * FROM a, b''' start1 = len('''WITH x AS ''') stop1 = len('''WITH x AS (SELECT abc, def FROM x)''') start2 = len('''WITH x AS (SELECT abc, def FROM x), y AS ''') stop2 = len('''WITH x AS (SELECT abc, def FROM x), y AS (SELECT ghi, jkl FROM y)''') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == (('x', ('abc', 'def'), start1, stop1), ('y', ('ghi', 'jkl'), start2, stop2))
def test_multiple_cte_extraction(): sql = '''WITH x AS (SELECT abc, def FROM x), y AS (SELECT ghi, jkl FROM y) SELECT * FROM a, b''' start1 = len('''WITH x AS ''') stop1 = len('''WITH x AS (SELECT abc, def FROM x)''') start2 = len('''WITH x AS (SELECT abc, def FROM x), y AS ''') stop2 = len('''WITH x AS (SELECT abc, def FROM x), y AS (SELECT ghi, jkl FROM y)''') ctes, remainder = extract_ctes(sql) assert tuple(ctes) == ( ('x', ('abc', 'def'), start1, stop1), ('y', ('ghi', 'jkl'), start2, stop2))