Author: jure Date: Fri Jan 18 12:54:44 2013 New Revision: 1435121 URL: http://svn.apache.org/viewvc?rev=1435121&view=rev Log: #288, workaround for sqlparse underline (_) bug, added translated SQL test cases
Modified: incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py Modified: incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py URL: http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py?rev=1435121&r1=1435120&r2=1435121&view=diff ============================================================================== --- incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py (original) +++ incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py Fri Jan 18 12:54:44 2013 @@ -87,6 +87,45 @@ class BloodhoundProductSQLTranslate(obje self._product_column = product_column self._product_prefix = product_prefix + def _sqlparse_underline_hack(self, token): + underline_token = lambda token: token.ttype == Tokens.Token.Error and token.value == '_' + identifier_token = lambda token: isinstance(token, Types.Identifier) or isinstance(token, Types.Token) + def prefix_token(token, prefix): + if identifier_token(token): + if isinstance(token, Types.IdentifierList): + token = token.tokens[0] + token.value = prefix + token.value + token.normalized = token.value.upper() if token.ttype in Tokens.Keyword \ + else token.value + if hasattr(token, 'tokens'): + if len(token.tokens) != 1: + raise Exception("Internal error, invalid token list") + token.tokens[0].value, token.tokens[0].normalized = token.value, token.normalized + return + + if hasattr(token, 'tokens') and token.tokens and len(token.tokens): + current = self._token_first(token) + while current: + leftover = None + if underline_token(current): + prefix = '' + while underline_token(current): + prefix += current.value + prev = current + current = self._token_next(token, current) + self._token_delete(token, prev) + # expression ends with _ ... push the token to parent + if not current: + return prev + prefix_token(current, prefix) + else: + leftover = self._sqlparse_underline_hack(current) + if leftover: + leftover.parent = token + self._token_insert_after(token, current, leftover) + current = leftover if leftover else self._token_next(token, current) + return None + def _select_table_name_alias(self, tokens): return filter(lambda t: t.upper() != 'AS', [t.value for t in tokens if t.value.strip()]) def _column_expression_name_alias(self, tokens): @@ -210,6 +249,8 @@ class BloodhoundProductSQLTranslate(obje alias = name parent.tokens[self._token_idx(parent, token)] = sqlparse.parse(self._prefixed_table_view_sql(name, alias))[0] + if table_name_callback: + table_name_callback(name) def inject_table_alias(token, alias): parent.tokens[self._token_idx(parent, token)] = sqlparse.parse(self._select_alias_sql(alias))[0] @@ -301,7 +342,7 @@ class BloodhoundProductSQLTranslate(obje fields_token = self._token_next(parent, token) if token.match(Tokens.Keyword, ['ALL', 'DISTINCT']) else token current_token, field_lists = self._select_expression_tokens(parent, fields_token, ['FROM'] + self._from_end_words) def handle_insert_table(table_name): - if table_name == insert_table: + if insert_table and insert_table in self._translate_tables: for keyword in [self._product_column, ',', ' ']: self._token_insert_before(parent, fields_token, Types.Token(Tokens.Keyword, keyword)) return @@ -581,14 +622,23 @@ class BloodhoundProductSQLTranslate(obje 'DROP': self._drop, } try: + format_sql = True + formatted_sql = lambda sql: sqlparse.format(sql.to_unicode(), reindent=True) \ + if format_sql \ + else sql.to_unicode() sql_statement = sqlparse.parse(sql)[0] + if '_' in sql: + self._sqlparse_underline_hack(sql_statement) +# format_sql = False t = sql_statement.token_first() if t.match(Tokens.DML, dml_handlers.keys()): dml_handlers[t.value](sql_statement, t) - sql = sqlparse.format(sql_statement.to_unicode(), reindent=True) + sql = formatted_sql(sql_statement) elif t.match(Tokens.DDL, ddl_handlers.keys()): ddl_handlers[t.value](sql_statement, t) - sql = sqlparse.format(sql_statement.to_unicode(), reindent=True) - except Exception: - raise Exception("Failed to translate SQL '%s'" % sql) + sql = formatted_sql(sql_statement) + else: + pass + except Exception, ex: + raise Exception("Failed to translate SQL '%s', exception '%s'" % (sql, ex.message)) return sql Modified: incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py URL: http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py?rev=1435121&r1=1435120&r2=1435121&view=diff ============================================================================== --- incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py (original) +++ incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py Fri Jan 18 12:54:44 2013 @@ -573,6 +573,110 @@ WHERE w1.version = w2.ver OR w1.author LIKE %s ESCAPE '/' OR w1.text LIKE %s ESCAPE '/')""" ), + ( +"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority, + owner, reporter, cc, version, milestone, status, resolution, + summary, description, keywords) + SELECT id, 'defect', time, changetime, component, severity, priority, owner, + reporter, cc, version, milestone, status, resolution, summary, + description, keywords FROM ticket_old + WHERE COALESCE(severity,'') <> 'enhancement'""", +"""INSERT INTO ticket(product, id, type, time, changetime, component, severity, priority, owner, reporter, cc, version, milestone, status, resolution, summary, description, keywords) +SELECT product, id, 'defect', time, changetime, component, severity, priority, owner, reporter, cc, version, milestone, + status, + resolution, + summary, + description, + keywords +FROM + (SELECT * + FROM PRODUCT_ticket_old) AS ticket_old +WHERE COALESCE(severity,'') <> 'enhancement'""" + ), + ( +"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority, + owner, reporter, cc, version, milestone, status, resolution, + summary, description, keywords) + SELECT id, 'enhancement', time, changetime, component, 'normal', priority, + owner, reporter, cc, version, milestone, status, resolution, summary, + description, keywords FROM ticket_old + WHERE severity = 'enhancement'""", +"""INSERT INTO ticket(product, id, type, time, changetime, component, severity, priority, owner, reporter, cc, version, milestone, status, resolution, summary, description, keywords) +SELECT product, id, 'enhancement', time, changetime, component, 'normal', priority, owner, reporter, cc, version, milestone, + status, + resolution, + summary, + description, + keywords +FROM + (SELECT * + FROM PRODUCT_ticket_old) AS ticket_old +WHERE severity = 'enhancement'""", + ), + ( +"""SELECT COUNT(*) FROM ( + SELECT __color__, __group, + (CASE + WHEN __group = 1 THEN 'Accepted' + WHEN __group = 2 THEN 'Owned' + WHEN __group = 3 THEN 'Reported' + ELSE 'Commented' END) AS __group__, + ticket, summary, component, version, milestone, + type, priority, created, _changetime, _description, + _reporter + FROM ( + SELECT DISTINCT CAST(p.value AS integer) AS __color__, + (CASE + WHEN owner = %s AND status = 'accepted' THEN 1 + WHEN owner = %s THEN 2 + WHEN reporter = %s THEN 3 + ELSE 4 END) AS __group, + t.id AS ticket, summary, component, version, milestone, + t.type AS type, priority, t.time AS created, + t.changetime AS _changetime, description AS _description, + reporter AS _reporter + FROM ticket t + LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' + LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = %s + AND tc.field = 'comment' + WHERE t.status <> 'closed' + AND (owner = %s OR reporter = %s OR author = %s) + ) AS sub + ORDER BY __group, __color__, milestone, type, created + + ) AS tab""", +"""SELECT COUNT(*) FROM ( + SELECT __color__, __group, + (CASE + WHEN __group = 1 THEN 'Accepted' + WHEN __group = 2 THEN 'Owned' + WHEN __group = 3 THEN 'Reported' + ELSE 'Commented' END) AS __group__, + ticket, summary, component, version, milestone, + type, priority, created, _changetime, _description, + _reporter + FROM ( + SELECT DISTINCT CAST(p.value AS integer) AS __color__, + (CASE + WHEN owner = %s AND status = 'accepted' THEN 1 + WHEN owner = %s THEN 2 + WHEN reporter = %s THEN 3 + ELSE 4 END) AS __group, + t.id AS ticket, summary, component, version, milestone, + t.type AS type, priority, t.time AS created, + t.changetime AS _changetime, description AS _description, + reporter AS _reporter + FROM (SELECT * FROM ticket WHERE product="PRODUCT") AS t + LEFT JOIN (SELECT * FROM enum WHERE product="PRODUCT") AS p ON p.name = t.priority AND p.type = 'priority' + LEFT JOIN ticket_change ON tc.ticket = t.id AND tc.author = %s + AND tc.field = 'comment' + WHERE t.status <> 'closed' + AND (owner = %s OR reporter = %s OR author = %s) + ) AS sub + ORDER BY __group, __color__, milestone, type, created + + ) AS tab""" + ), ], # custom table SELECTs