1P0P6r2b

1import argparse
2import os
3import re
4
5
6def main(sql_file):
7    with open(sql_file, 'r') as f:
8        sql = f.read()
9
10    sql = sql.upper().replace('WITH', ',')
11    opens, closes = parse_sql_into_chunks(sql)
12    subquery_data = find_subquery_data(sql, opens, closes)
13    temp_table_queries = create_temp_tables(sql, subquery_data)
14    last_subquery_idx = find_select_query_start(sql, opens, closes)
15
16    print(('{}\n'
17           '{}').format(''.join(temp_table_queries),
18                        sql[last_subquery_idx:]))
19
20
21def find_outer_parentheses(sql):
22    opening_parenthesis = re.search('\(', sql)
23    if opening_parenthesis:
24        opening_parenthesis_idx = opening_parenthesis.start()
25    else:
26        raise KeyError
27
28    with_subqueries = 0
29    counter = -1
30    for char in sql[opening_parenthesis_idx:]:
31        counter += 1
32        if char == '(':
33            with_subqueries += 1
34        elif char == ')':
35            with_subqueries -= 1
36
37        if with_subqueries == 0:
38            break
39
40    return opening_parenthesis_idx, opening_parenthesis_idx + counter
41
42def parse_sql_into_chunks(sql):
43    opens_idx = []
44    closes_idx = []
45    cursor = 0
46
47    sql_copy = sql
48
49    while len(sql_copy) > 0:
50        try:
51            o, c = find_outer_parentheses(sql_copy)
52        except KeyError:
53            break
54        opens_idx.append(o + cursor)
55        closes_idx.append(c + cursor)
56
57        cursor += c
58        sql_copy = sql[cursor:]
59
60    return opens_idx, closes_idx
61
62def find_subquery_data(sql, opens, closes):
63    """
64    Give SQL and the start and close of outer-level parentheses, return the
65    name of the subquery and the start and close index of each, discarding
66    the last piece that isn't a subquery.
67    """
68    for o, c1, c in zip(opens, [0] + closes[:-2], closes):
69        w = re.search(r'(\w+) AS \(', sql[c1:(o + 1)])
70        if w:
71            w = w.group(1)
72            yield w, o, c
73
74def find_select_query_start(sql, opens, closes):
75    for o, c1, c in zip(opens, [0] + closes[:-2], closes):
76        w = re.search(r'(\w+) AS \(', sql[c1:(o + 1)], flags=re.IGNORECASE)
77        if not w:
78            return c
79
80def create_temp_tables(sql, subquery_data):
81    for s in subquery_data:
82        q = ('DROP TABLE IF EXISTS {subquery_name} CASCADE;\n'
83             'CREATE LOCAL TEMPORARY TABLE {subquery_name} ON COMMIT PRESERVE ROWS AS\n'
84             '{subquery}\n'
85             ';\n'.format(subquery_name=s[0],
86                        subquery=sql[(s[1] + 1):s[2]]))
87        yield q
88
89
90if __name__ == '__main__':
91    parser = argparse.ArgumentParser()
92    parser.add_argument('file', type=str, help='Path to the SQL file')
93
94    cmd_args = parser.parse_args()
95
96    main(cmd_args.file)