Repository: tobymao/sqlglot
Pull request: Fix sqlite primary key order constraint
Fixes #742 

Problem statement:
[sqlite dialect] Parser doesn't recognize optional order parameter for PKEY column-constraint
sqlite's `CREATE TABLE` statement accepts an optional order parameter for `PRIMARY KEY` column-constraints. `sqlglot` does not yet handle this parameter:

```python
create_table_sql = "CREATE TABLE foo (id INTEGER PRIMARY KEY ASC);"
sqlglot.parse_one(create_table_sql, read="sqlite")
```

```shell
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/sqlglot/sqlglot/__init__.py", line 82, in parse_one
    result = dialect.parse(sql, **opts)
  File "/sqlglot/sqlglot/dialects/dialect.py", line 150, in parse
    return self.parser(**opts).parse(self.tokenizer.tokenize(sql), sql)
  File "/sqlglot/sqlglot/parser.py", line 604, in parse
    return self._parse(
  File "/sqlglot/sqlglot/parser.py", line 637, in _parse
    expressions.append(parse_method(self))
  File "/sqlglot/sqlglot/parser.py", line 728, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
  File "/sqlglot/sqlglot/parser.py", line 391, in <lambda>
    TokenType.CREATE: lambda self: self._parse_create(),
  File "/sqlglot/sqlglot/parser.py", line 801, in _parse_create
    this = self._parse_table(schema=True)
  File "/sqlglot/sqlglot/parser.py", line 1417, in _parse_table
    return self._parse_schema(this=this)
  File "/sqlglot/sqlglot/parser.py", line 2140, in _parse_schema
    self._match_r_paren()
  File "/sqlglot/sqlglot/parser.py", line 2759, in _match_r_paren
    self.raise_error("Expecting )")
  File "/sqlglot/sqlglot/parser.py", line 665, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 42.
  CREATE TABLE foo (id INTEGER PRIMARY KEY ASC);
```

Hints from issue discussion:

Commit message: Fix sqlite primary key order constraint

Patch to review:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 71f0265677..1488f40c13 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -387,8 +387,9 @@ def generatedasidentitycolumnconstraint_sql(self, expression):
     def notnullcolumnconstraint_sql(self, _):
         return "NOT NULL"
 
-    def primarykeycolumnconstraint_sql(self, _):
-        return "PRIMARY KEY"
+    def primarykeycolumnconstraint_sql(self, expression):
+        order = f" {expression.name}" if expression.this else ""
+        return f"PRIMARY KEY{order}"
 
     def uniquecolumnconstraint_sql(self, _):
         return "UNIQUE"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 83bbd94ae8..8b97536c65 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2050,7 +2050,10 @@ def _parse_column_constraint(self):
         elif self._match(TokenType.SCHEMA_COMMENT):
             kind = self.expression(exp.CommentColumnConstraint, this=self._parse_string())
         elif self._match(TokenType.PRIMARY_KEY):
-            kind = exp.PrimaryKeyColumnConstraint()
+            order = None
+            if self._match(TokenType.ASC) or self._match(TokenType.DESC):
+                order = self.expression(exp.Var, this=self._prev.text)
+            kind = exp.PrimaryKeyColumnConstraint(this=order)
         elif self._match(TokenType.UNIQUE):
             kind = exp.UniqueColumnConstraint()
         elif self._match(TokenType.GENERATED):
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 3cc974cebc..e54a4bc5b8 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -5,6 +5,10 @@ class TestSQLite(Validator):
     dialect = "sqlite"
 
     def test_ddl(self):
+        self.validate_all(
+            "CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)",
+            write={"sqlite": "CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)"},
+        )
         self.validate_all(
             """
             CREATE TABLE "Track"
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index b0b632828c..9321980bda 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -441,6 +441,7 @@ SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(scores) WITH ORDINALITY AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(x.scores) AS t(score)
 SELECT student, score FROM tests CROSS JOIN UNNEST(ARRAY(x.scores)) AS t(score)
+CREATE TABLE foo (id INT PRIMARY KEY ASC)
 CREATE TABLE a.b AS SELECT 1
 CREATE TABLE a.b AS SELECT a FROM a.c
 CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d


Code review comments:
diff_hunk: @@ -2050,7 +2050,10 @@ def _parse_column_constraint(self):
         elif self._match(TokenType.SCHEMA_COMMENT):
             kind = self.expression(exp.CommentColumnConstraint, this=self._parse_string())
         elif self._match(TokenType.PRIMARY_KEY):
-            kind = exp.PrimaryKeyColumnConstraint()
+            order = None
+            if self._match(TokenType.ASC) or self._match(TokenType.DESC):
+                order = self.expression(exp.Var, this=self._prev.text)
+            kind = exp.PrimaryKeyColumnConstraint(this=order)
path: sqlglot/parser.py
text: @user1:
what is this? here? shouldn't we make a new flag on here that is like order?