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