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