def transform_table_identifiers(sql, callable):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(sql)
# Do some validation first
for ast in ast_list:
if type(ast) is str:
log_info("copyAsDrupalQuery", ast) # error
mforms.App.get().set_status_text("Cannot format invalid SQL: %s" %
ast)
return 1
#from sql_reformatter import dump_tree
#import sys
#myFile = open("C:\\temp\\log.txt", "w")
#dump_tree(myFile, ast)
#myFile.close()
offsets = []
for ast in ast_list:
get_table_ident_offsets(offsets, ast)
sql2 = ""
bb = 0
for b, e in offsets:
sql2 += sql[bb:b] + callable(sql[b:e])
bb = e
sql2 += sql[bb:]
return sql2
def transform_table_identifiers(sql,callable):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(sql)
# Do some validation first
for ast in ast_list:
if type(ast) is str:
log_info("copyAsDrupalQuery",ast) # error
mforms.App.get().set_status_text("Cannot format invalid SQL: %s" % ast)
return 1
#from sql_reformatter import dump_tree
#import sys
#myFile = open("C:\\temp\\log.txt", "w")
#dump_tree(myFile, ast)
#myFile.close()
offsets = []
for ast in ast_list:
get_table_ident_offsets(offsets, ast)
sql2 = ""
bb = 0
for b, e in offsets:
sql2 += sql[bb:b] + callable(sql[b:e])
bb = e
sql2 += sql[bb:]
return sql2
def reverseEngineer(cls, connection, catalog_name, schemata_list, context):
from grt.modules import MysqlSqlFacade
grt.send_progress(0, "Reverse engineering catalog information")
cls.check_interruption()
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
# calculate total workload 1st
grt.send_progress(0.1, 'Preparing...')
get_tables = context.get("reverseEngineerTables", True)
# 10% of the progress is for preparation
total = 1e-10 # total should not be zero to avoid DivisionByZero exceptions
accumulated_progress = 0.1
total += len(cls.getTableNames(connection, catalog_name,
'')) if get_tables else 0
grt.send_progress(0.1, "Gathered stats")
# Now the second pass for reverse engineering tables:
if get_tables:
idx = 0
for object_type, name, tbl_name, _, sql in cls.execute_query(
connection, "SELECT * FROM sqlite_master"):
if type in ('view', 'trigger'
) or not sql or tbl_name.startswith('sqlite_'):
continue
sql = sql.replace('[', '').replace(']', '')
grt.log_debug('SQLiteReverseEngineering',
'Procesing this sql:\n%s;' % sql)
MysqlSqlFacade.parseSqlScriptString(catalog, sql)
cls.check_interruption()
grt.send_progress(0.1 + idx / total,
'Object %s reverse engineered!' % name)
idx += 1
grt.send_progress(1.0, 'Reverse engineering completed!')
return catalog
def apply_to_keywords(editor, callable):
from grt.modules import MysqlSqlFacade
non_keywords = [
"ident", "ident_or_text", "TEXT_STRING", "text_string",
"TEXT_STRING_filesystem", "TEXT_STRING_literal", "TEXT_STRING_sys",
"part_name"
]
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.script
new_text = ""
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
bb = 0
for ast in ast_list:
if type(ast) is str:
# error
print ast
mforms.App.get().set_status_text("Cannot format invalid SQL: %s" %
ast)
return 1
else:
if 0: # debug
from sql_reformatter import dump_tree
import sys
dump_tree(sys.stdout, ast)
def get_keyword_offsets(offsets, script, node):
s, v, c, base, b, e = node
if v:
b += base
e += base
if s not in non_keywords:
offsets.append((b, e))
for i in c:
get_keyword_offsets(offsets, script, i)
offsets = []
get_keyword_offsets(offsets, text, ast)
for b, e in offsets:
new_text += text[bb:b] + callable(text[b:e])
bb = e
new_text += text[bb:]
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceContents(new_text)
mforms.App.get().set_status_text("SQL code reformatted.")
return 0
def apply_to_keywords(editor, callable):
from grt.modules import MysqlSqlFacade
non_keywords = ["ident", "ident_or_text", "TEXT_STRING", "text_string", "TEXT_STRING_filesystem", "TEXT_STRING_literal", "TEXT_STRING_sys",
"part_name"]
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.script
new_text = ""
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
bb = 0
for ast in ast_list:
if type(ast) is str:
# error
print ast
mforms.App.get().set_status_text("Cannot format invalid SQL: %s"%ast)
return 1
else:
if 0: # debug
from sql_reformatter import dump_tree
import sys
dump_tree(sys.stdout, ast)
def get_keyword_offsets(offsets, script, node):
s, v, c, base, b, e = node
if v:
b += base
e += base
if s not in non_keywords:
offsets.append((b, e))
for i in c:
get_keyword_offsets(offsets, script, i)
offsets = []
get_keyword_offsets(offsets, text, ast)
for b, e in offsets:
new_text += text[bb:b] + callable(text[b:e])
bb = e
new_text += text[bb:]
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceContents(new_text)
mforms.App.get().set_status_text("SQL code reformatted.")
return 0
def reverseEngineer(cls, connection, catalog_name, schemata_list, context):
from grt.modules import MysqlSqlFacade
grt.send_progress(0, "Reverse engineering catalog information")
cls.check_interruption()
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
# calculate total workload 1st
grt.send_progress(0.1, 'Preparing...')
get_tables = context.get("reverseEngineerTables", True)
# 10% of the progress is for preparation
total = 1e-10 # total should not be zero to avoid DivisionByZero exceptions
accumulated_progress = 0.1
total += len(cls.getTableNames(connection, catalog_name, '')) if get_tables else 0
grt.send_progress(0.1, "Gathered stats")
# Now the second pass for reverse engineering tables:
if get_tables:
idx = 0
for object_type, name, tbl_name, _, sql in cls.execute_query(connection, "SELECT * FROM sqlite_master"):
if type in ('view', 'trigger') or not sql or tbl_name.startswith('sqlite_'):
continue
sql = sql.replace('[', '').replace(']', '')
grt.log_debug('SQLiteReverseEngineering', 'Procesing this sql:\n%s;' % sql)
MysqlSqlFacade.parseSqlScriptString(catalog, sql)
cls.check_interruption()
grt.send_progress(0.1 + idx / total, 'Object %s reverse engineered!' % name)
idx += 1
grt.send_progress(1.0, 'Reverse engineering completed!')
return catalog
def _parse_column_name_list_from_query(query):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(query)
for ast in ast_list:
if type(ast) is str:
continue
else:
s, v, c, _base, _begin, _end = ast
trimmed_ast = trim_ast(ast)
select_item_list = find_child_node(trimmed_ast, "select_item_list")
if select_item_list:
columns = []
variables = []
index = 0
for node in node_children(select_item_list):
if node_symbol(node) == "select_item":
index += 1
ident = find_child_node(find_child_node(node, "expr"), "ident")
alias = find_child_node(find_child_node(node, "select_alias"), "ident")
if not alias:
if ident:
name = node_value(ident)
else:
name = "field%i" % index
else:
name = node_value(alias)
columns.append(name)
helper = ASTHelper(query)
begin, end = helper.get_ast_range(ast)
# dump_tree(sys.stdout, ast)
query = query[begin:end]
offset = begin
vars = find_child_nodes(ast, "variable")
for var in reversed(vars):
begin, end = helper.get_ast_range(var)
begin -= offset
end -= offset
name = query[begin:end]
query = query[:begin] + "?" + query[end:]
variables.insert(0, name)
return query, columns, variables
def _parse_column_name_list_from_query(query):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(query)
for ast in ast_list:
if type(ast) is str:
continue
else:
s, v, c, _base, _begin, _end = ast
trimmed_ast = trim_ast(ast)
select_item_list = find_child_node(trimmed_ast, "select_item_list")
if select_item_list:
columns = []
variables = []
index = 0
for node in node_children(select_item_list):
if node_symbol(node) == "select_item":
index += 1
ident = find_child_node(find_child_node(node, "expr"), "ident")
alias = find_child_node(find_child_node(node, "select_alias"), "ident")
if not alias:
if ident:
name = node_value(ident)
else:
name = "field%i"%index
else:
name = node_value(alias)
columns.append(name)
helper = ASTHelper(query)
begin, end = helper.get_ast_range(ast)
#dump_tree(sys.stdout, ast)
query = query[begin:end]
offset = begin
vars = find_child_nodes(ast, "variable")
for var in reversed(vars):
begin, end = helper.get_ast_range(var)
begin -= offset
end -= offset
name = query[begin:end]
query = query[:begin] + "?" + query[end:]
variables.insert(0, name)
return query, columns, variables
def doReformatSQLStatement(text, return_none_if_unsupported):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
if len(ast_list) != 1:
raise Exception("Error parsing statement")
if type(ast_list[0]) is str:
raise Exception("Error parsing statement: %s" % ast_list[0])
ast = ast_list[0]
def trim_ast_fix_bq(text, node, add_rollup):
s = node[0]
v = node[1]
c = node[2]
# put back backquotes to identifiers, if there's any
if s in ("ident", "ident_or_text"):
begin = node[3] + node[4]
end = node[3] + node[5]
if begin > 0 and text[begin - 1] == '`' and text[end] == '`':
v = "`%s`" % v.replace("`", "``")
l = []
for i, nc in enumerate(c):
l.append(trim_ast_fix_bq(text, nc, add_rollup))
if add_rollup and nc[0] == "olap_opt" and nc[1].upper(
) == "WITH" and (i == len(c) - 1
or c[i + 1][1].upper() != "ROLLUP"):
l.append(("olap_opt", "ROLLUP", []))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
# workaround a bug in parser where WITH ROLLUP is turned into WITH
add_rollup = "WITH ROLLUP" in text.upper()
p = formatter(trim_ast_fix_bq(text, ast, add_rollup))
return p.run()
else:
if return_none_if_unsupported:
return None
return text
def doReformatSQLStatement(text, return_none_if_unsupported):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
if len(ast_list) != 1:
raise Exception("Error parsing statement")
if type(ast_list[0]) is str:
raise Exception("Error parsing statement: %s" % ast_list[0])
ast = ast_list[0]
def trim_ast_fix_bq(text, node, add_rollup):
s = node[0]
v = node[1]
c = node[2]
# put back backquotes to identifiers, if there's any
if s in ("ident", "ident_or_text"):
begin = node[3] + node[4]
end = node[3] + node[5]
if begin > 0 and text[begin-1] == '`' and text[end] == '`':
v = "`%s`" % v.replace("`", "``")
l = []
for i, nc in enumerate(c):
l.append(trim_ast_fix_bq(text, nc, add_rollup))
if add_rollup and nc[0] == "olap_opt" and nc[1].upper() == "WITH" and (i == len(c)-1 or c[i+1][1].upper() != "ROLLUP"):
l.append(("olap_opt", "ROLLUP", []))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
# workaround a bug in parser where WITH ROLLUP is turned into WITH
add_rollup = "WITH ROLLUP" in text.upper()
p = formatter(trim_ast_fix_bq(text, ast, add_rollup))
return p.run()
else:
if return_none_if_unsupported:
return None
return text
def doReformatSQLStatement(text, return_none_if_unsupported):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
if len(ast_list) != 1:
raise Exception("Error parsing statement")
if type(ast_list[0]) is str:
raise Exception("Error parsing statement: %s" % ast_list[0])
helper = ASTHelper(text)
curpos = 0
new_text = ""
ast = ast_list[0]
def trim_ast_fix_bq(text, node):
s = node[0]
v = node[1]
c = node[2]
# put back backquotes to identifiers, if there's any
if s in ("ident", "ident_or_text"):
begin = node[3] + node[4]
end = node[3] + node[5]
if begin > 0 and text[begin-1] == '`' and text[end] == '`':
v = "`%s`" % v.replace("`", "``")
l = []
for i in c:
l.append(trim_ast_fix_bq(text, i))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
p = formatter(trim_ast_fix_bq(text, ast))
return p.run()
else:
if return_none_if_unsupported:
return None
return text
def doReformatSQLStatement(text, return_none_if_unsupported):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
if len(ast_list) != 1:
raise Exception("Error parsing statement")
if type(ast_list[0]) is str:
raise Exception("Error parsing statement: %s" % ast_list[0])
helper = ASTHelper(text)
curpos = 0
new_text = ""
ast = ast_list[0]
def trim_ast_fix_bq(text, node):
s = node[0]
v = node[1]
c = node[2]
# put back backquotes to identifiers, if there's any
if s in ("ident", "ident_or_text"):
begin = node[3] + node[4]
end = node[3] + node[5]
if begin > 0 and text[begin-1] == '`' and text[end] == '`':
v = "`%s`" % v.replace("`", "``")
l = []
for i in c:
l.append(trim_ast_fix_bq(text, i))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
p = formatter(trim_ast_fix_bq(text, ast))
return p.run()
else:
if return_none_if_unsupported:
return None
return text
def enbeautificate(editor):
"""Reformat the selected SQL statements or the one under the cursor."""
from grt.modules import MysqlSqlFacade
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.currentStatement
ok_count = 0
bad_count = 0
prev_end = 0
new_text = []
ranges = MysqlSqlFacade.getSqlStatementRanges(text)
for begin, end in ranges:
end = begin + end
if begin > prev_end:
new_text.append(text[prev_end:begin])
statement = text[begin:end]
#
stripped = statement.lstrip(" \t\r\n")
leading = statement[:len(statement) - len(stripped)]
statement = stripped
stripped = statement.rstrip(" \t\r\n")
if stripped != statement:
trailing = statement[-(len(statement) - len(stripped)):]
else:
trailing = ""
statement = stripped
# if there's a comment at the start, then skip the comment until its end
while True:
if statement.startswith("-- "):
comment, _, rest = statement.partition("\n")
leading += comment+"\n"
statement = rest
elif statement.startswith("/*"):
pos = statement.find("*/")
if pos >= 0:
leading += statement[:pos+2]
statement = statement[pos+2:]
else:
break
else:
break
stripped = statement.lstrip(" \t\r\n")
leading += statement[:len(statement) - len(stripped)]
statement = stripped
stripped = statement.rstrip(" \t\r\n")
if stripped != statement:
trailing += statement[-(len(statement) - len(stripped)):]
statement = stripped
try:
result = doReformatSQLStatement(statement, True)
except:
import traceback
log_error("Error reformating SQL: %s\n%s\n" % (statement, traceback.format_exc()))
result = None
if result:
ok_count += 1
if leading:
new_text.append(leading.strip(" "))
new_text.append(result)
if trailing:
new_text.append(trailing.strip(" "))
else:
bad_count += 1
new_text.append(text[begin:end])
prev_end = end
new_text.append(text[prev_end:])
new_text = "".join(new_text)
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceCurrentStatement(new_text)
if bad_count > 0:
mforms.App.get().set_status_text("Formatted %i statements, %i unsupported statement types skipped."%(ok_count, bad_count))
else:
mforms.App.get().set_status_text("Formatted %i statements."%ok_count)
return 0
def _parse_column_name_list_from_query(query):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(query)
for ast in ast_list:
if type(ast) is str:
continue
else:
s, v, c, _base, _begin, _end = ast
trimmed_ast = trim_ast(ast)
select_item_list = find_child_node(trimmed_ast, "select_item_list")
if select_item_list:
columns = []
variables = []
index = 0
for node in node_children(select_item_list):
if node_symbol(node) == "select_item":
alias = find_child_node(find_child_node(node, "select_alias"), "ident")
if not alias:
ident = find_child_node(node, "simple_ident_q")
if ident and len(node_children(ident)) == 3:
ident = node_children(ident)[-1]
else:
ident = find_child_node(find_child_node(node, "expr"), "ident")
if ident:
name = node_value(ident)
else:
name = "field"
field = flatten_node(node)
if field:
import re
m = re.match("([a-zA-Z0-9_]*)", field)
if m:
name = m.groups()[0]
else:
name = node_value(alias)
columns.append(name)
helper = ASTHelper(query)
begin, end = helper.get_ast_range(ast)
#dump_tree(sys.stdout, ast)
query = query[begin:end]
offset = begin
vars = find_child_nodes(ast, "variable")
for var in reversed(vars):
begin, end = helper.get_ast_range(var)
begin -= offset
end -= offset
name = query[begin:end]
query = query[:begin] + "?" + query[end:]
variables.insert(0, name)
duplicates = {}
for i, c in enumerate(columns):
if duplicates.has_key(c):
columns[i] = "%s%i" % (c, duplicates[c])
duplicates[c] += 1
duplicates[c] = duplicates.get(c, 0)+1
return query, columns, variables
def _parse_column_name_list_from_query(query):
from grt.modules import MysqlSqlFacade
ast_list = MysqlSqlFacade.parseAstFromSqlScript(query)
for ast in ast_list:
if type(ast) is str:
continue
else:
s, v, c, _base, _begin, _end = ast
trimmed_ast = trim_ast(ast)
select_item_list = find_child_node(trimmed_ast, "select_item_list")
if select_item_list:
columns = []
variables = []
index = 0
for node in node_children(select_item_list):
if node_symbol(node) == "select_item":
alias = find_child_node(find_child_node(node, "select_alias"), "ident")
if not alias:
ident = find_child_node(node, "simple_ident_q")
if ident and len(node_children(ident)) == 3:
ident = node_children(ident)[-1]
else:
ident = find_child_node(find_child_node(node, "expr"), "ident")
if ident:
name = node_value(ident)
else:
name = "field"
field = flatten_node(node)
if field:
import re
m = re.match("([a-zA-Z0-9_]*)", field)
if m:
name = m.groups()[0]
else:
name = node_value(alias)
columns.append(name)
helper = ASTHelper(query)
begin, end = helper.get_ast_range(ast)
#dump_tree(sys.stdout, ast)
query = query[begin:end]
offset = begin
vars = find_child_nodes(ast, "variable")
for var in reversed(vars):
begin, end = helper.get_ast_range(var)
begin -= offset
end -= offset
name = query[begin:end]
query = query[:begin] + "?" + query[end:]
variables.insert(0, name)
duplicates = {}
for i, c in enumerate(columns):
if duplicates.has_key(c):
columns[i] = "%s%i" % (c, duplicates[c])
duplicates[c] += 1
duplicates[c] = duplicates.get(c, 0)+1
return query, columns, variables
def enbeautificate(editor):
from grt.modules import MysqlSqlFacade
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.script
helper = ASTHelper(text)
ok_count = 0
bad_count = 0
curpos = 0
new_text = ""
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
for ast in ast_list:
if type(ast) is str:
# error
print ast
mforms.App.get().set_status_text("Cannot format invalid SQL: %s"%ast)
return 1
else:
if 0: # debug
from sql_reformatter import dump_tree
import sys
dump_tree(sys.stdout, ast)
s, v, c, _base, _begin, _end = ast
begin, end = helper.get_ast_range(ast)
new_text += text[curpos:begin].rstrip(" ") # strip spaces that would come before statement
# The token range does not include the quotation char if the token is quoted.
# So extend the range by one to avoid adding part of the original token to the output.
if end < len(text):
possible_quote_char = text[end]
else:
possible_quote_char = None
if possible_quote_char == '\'' or possible_quote_char == '"' or possible_quote_char == '`':
curpos = end + 1
else:
curpos = end
def trim_ast(node):
s = node[0]
v = node[1]
c = node[2]
l = []
for i in c:
l.append(trim_ast(i))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
ok_count += 1
p = formatter(trim_ast(ast))
fmted = p.run()
else:
bad_count += 1
fmted = text[begin:end]
new_text += fmted
new_text += text[curpos:]
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceContents(new_text)
if bad_count > 0:
mforms.App.get().set_status_text("Formatted %i statements, %i unsupported statement types skipped."%(ok_count, bad_count))
else:
mforms.App.get().set_status_text("Formatted %i statements."%ok_count)
return 0
def enbeautificate_old(editor):
from grt.modules import MysqlSqlFacade
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.script
helper = ASTHelper(text)
ok_count = 0
bad_count = 0
curpos = 0
new_text = ""
ast_list = MysqlSqlFacade.parseAstFromSqlScript(text)
for ast in ast_list:
if type(ast) is str:
# error
print ast
mforms.App.get().set_status_text("Cannot format invalid SQL: %s"%ast)
return 1
else:
if 0: # debug
from sql_reformatter import dump_tree
import sys
dump_tree(sys.stdout, ast)
s, v, c, _base, _begin, _end = ast
begin, end = helper.get_ast_range(ast)
new_text += text[curpos:begin].rstrip(" ") # strip spaces that would come before statement
# The token range does not include the quotation char if the token is quoted.
# So extend the range by one to avoid adding part of the original token to the output.
if end < len(text):
possible_quote_char = text[end]
else:
possible_quote_char = None
if possible_quote_char == '\'' or possible_quote_char == '"' or possible_quote_char == '`':
curpos = end + 1
else:
curpos = end
def trim_ast_fix_bq(text, node):
s = node[0]
v = node[1]
c = node[2]
# put back backquotes to identifiers, if there's any
if s in ("ident", "ident_or_text"):
begin = node[3] + node[4]
end = node[3] + node[5]
if begin > 0 and end < len(text) and text[begin-1] == '`' and text[end] == '`':
v = "`%s`" % v.replace("`", "``")
l = []
for i in c:
l.append(trim_ast_fix_bq(text, i))
return (s, v, l)
formatter = formatter_for_statement_ast(ast)
if formatter:
ok_count += 1
p = formatter(trim_ast_fix_bq(text, ast))
fmted = p.run()
else:
bad_count += 1
fmted = text[begin:end]
new_text += fmted
new_text += text[curpos:]
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceContents(new_text)
if bad_count > 0:
mforms.App.get().set_status_text("Formatted %i statements, %i unsupported statement types skipped."%(ok_count, bad_count))
else:
mforms.App.get().set_status_text("Formatted %i statements."%ok_count)
return 0
