Add order criterion to identifier in ORDER BY clause (fixes andialbrecht#89).

andialbrecht · andialbrecht · commit 341143e294c8 · 2013-04-05T05:46:41.000+02:00
diff --git a/CHANGES b/CHANGES
@@ -9,9 +9,10 @@ Bug Fixes
    invalid SQL statements.
 
 Enhancements
- * Improve parsing speed when SQL contains CLOBs or BLOBs (issue86).
  * Top-level API functions now accept encoding keyword to parse
    statements in certain encodings more reliable (issue20).
+ * Improve parsing speed when SQL contains CLOBs or BLOBs (issue86).
+ * Improve formatting of ORDER BY clauses (issue89).
 
 Other
  * Documentation updates.
diff --git a/sqlparse/engine/grouping.py b/sqlparse/engine/grouping.py
@@ -332,6 +332,20 @@ def group_functions(tlist):
         token = tlist.token_next_by_type(idx, T.Name)
 
 
+def group_order(tlist):
+    idx = 0
+    token = tlist.token_next_by_type(idx, T.Keyword.Order)
+    while token:
+        prev = tlist.token_prev(token)
+        if isinstance(prev, sql.Identifier):
+            ido = tlist.group_tokens(sql.Identifier,
+                                     tlist.tokens_between(prev, token))
+            idx = tlist.token_index(ido) + 1
+        else:
+            idx = tlist.token_index(token) + 1
+        token = tlist.token_next_by_type(idx, T.Keyword.Order)
+
+
 def group(tlist):
     for func in [
             group_comments,
@@ -340,6 +354,7 @@ def group(tlist):
             group_where,
             group_case,
             group_identifier,
+            group_order,
             group_typecasts,
             group_as,
             group_aliased,
diff --git a/sqlparse/keywords.py b/sqlparse/keywords.py
@@ -17,7 +17,7 @@
     'ANALYZE': tokens.Keyword,
     'ANY': tokens.Keyword,
     'ARE': tokens.Keyword,
-    'ASC': tokens.Keyword,
+    'ASC': tokens.Keyword.Order,
     'ASENSITIVE': tokens.Keyword,
     'ASSERTION': tokens.Keyword,
     'ASSIGNMENT': tokens.Keyword,
@@ -124,7 +124,7 @@
     'DELIMITER': tokens.Keyword,
     'DELIMITERS': tokens.Keyword,
     'DEREF': tokens.Keyword,
-    'DESC': tokens.Keyword,
+    'DESC': tokens.Keyword.Order,
     'DESCRIBE': tokens.Keyword,
     'DESCRIPTOR': tokens.Keyword,
     'DESTROY': tokens.Keyword,
diff --git a/sqlparse/sql.py b/sqlparse/sql.py
@@ -495,6 +495,13 @@ def get_typecast(self):
             return None
         return unicode(next_)
 
+    def get_ordering(self):
+        """Returns the ordering or ``None`` as uppercase string."""
+        ordering = self.token_next_by_type(0, T.Keyword.Order)
+        if ordering is None:
+            return None
+        return ordering.value.upper()
+
 
 class IdentifierList(TokenList):
     """A list of :class:`~sqlparse.sql.Identifier`\'s."""
diff --git a/tests/test_format.py b/tests/test_format.py
@@ -267,3 +267,14 @@ def test_sql(self):  # "sql" is an allowed option but has no effect
         sql = 'select * from foo;'
         f = lambda sql: sqlparse.format(sql, output_format='sql')
         self.ndiffAssertEqual(f(sql), 'select * from foo;')
+
+
+def test_format_column_ordering():  # issue89
+    sql = 'select * from foo order by c1 desc, c2, c3;'
+    formatted = sqlparse.format(sql, reindent=True)
+    expected = '\n'.join(['select *',
+                          'from foo',
+                          'order by c1 desc,',
+                          '         c2,',
+                          '         c3;'])
+    assert formatted == expected
diff --git a/tests/test_grouping.py b/tests/test_grouping.py
@@ -231,3 +231,14 @@ def test_identifier_string_concat():
     p = sqlparse.parse('\'foo\' || bar')[0]
     assert len(p.tokens) == 1
     assert isinstance(p.tokens[0], sql.Identifier)
+
+
+def test_identifier_consumes_ordering():  # issue89
+    p = sqlparse.parse('select * from foo order by c1 desc, c2, c3')[0]
+    assert isinstance(p.tokens[-1], sql.IdentifierList)
+    ids = list(p.tokens[-1].get_identifiers())
+    assert len(ids) == 3
+    assert ids[0].get_name() == 'c1'
+    assert ids[0].get_ordering() == 'DESC'
+    assert ids[1].get_name() == 'c2'
+    assert ids[1].get_ordering() is None
