Repository: tobymao/sqlglot

Issue:
Online DDL in Mysql
Could support for Mysql's online DDL please be added to Sqlglot? That is, DDL operations should support `ALGORITHM` and `LOCK` clauses.

See, e.g. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

Here's an example of what I tried to do, the last step is what I'd like to see improved.

```
>>> import sys; sys.version_info
sys.version_info(major=3, minor=11, micro=7, releaselevel='final', serial=0)

>>> import sqlglot; sqlglot._version.version_tuple
(21, 1, 2)

>>> sqlglot.parse_one("ALTER TABLE t1 ADD COLUMN x INT;", dialect="mysql")
AlterTable(
  this=Table(
    this=Identifier(this=t1, quoted=False)),
  actions=[
    ColumnDef(
      this=Identifier(this=x, quoted=False),
      kind=DataType(this=Type.INT, nested=False))])

>>> sqlglot.parse_one("ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE;", dialect="mysql")
'ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE' contains unsupported syntax. Falling back to parsing as a 'Command'.
Command(this=ALTER, expression=TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE)
```

I'll take a look at the code myself but I suspect it will take me some time to understand how Sqlglot's modules are interrelated and the authors can probably do it faster/better.

Thanks!

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 13f8b858..7a160c1d 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -391,6 +391,11 @@ class MySQL(Dialect):
             "WARNINGS": _show_parser("WARNINGS"),
         }
 
+        PROPERTY_PARSERS = {
+            **parser.Parser.PROPERTY_PARSERS,
+            "LOCK": lambda self: self._parse_property_assignment(exp.LockProperty),
+        }
+
         SET_PARSERS = {
             **parser.Parser.SET_PARSERS,
             "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 4fb3679a..3bec25ee 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2322,6 +2322,10 @@ class LocationProperty(Property):
     arg_types = {"this": True}
 
 
+class LockProperty(Property):
+    arg_types = {"this": True}
+
+
 class LockingProperty(Property):
     arg_types = {
         "this": False,
@@ -2505,6 +2509,7 @@ class Properties(Expression):
         "FORMAT": FileFormatProperty,
         "LANGUAGE": LanguageProperty,
         "LOCATION": LocationProperty,
+        "LOCK": LockProperty,
         "PARTITIONED_BY": PartitionedByProperty,
         "RETURNS": ReturnsProperty,
         "ROW_FORMAT": RowFormatProperty,
@@ -3923,7 +3928,13 @@ class Rollback(Expression):
 
 
 class AlterTable(Expression):
-    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
+    arg_types = {
+        "this": True,
+        "actions": True,
+        "exists": False,
+        "only": False,
+        "options": False,
+    }
 
 
 class AddConstraint(Expression):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index d8b6290d..66466734 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -388,6 +388,7 @@ class Generator(metaclass=_Generator):
         exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
         exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
         exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
+        exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
         exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
         exp.LogProperty: exp.Properties.Location.POST_NAME,
         exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
@@ -2833,7 +2834,9 @@ class Generator(metaclass=_Generator):
 
         exists = " IF EXISTS" if expression.args.get("exists") else ""
         only = " ONLY" if expression.args.get("only") else ""
-        return f"ALTER TABLE{exists}{only} {self.sql(expression, 'this')} {actions}"
+        options = self.expressions(expression, key="options")
+        options = f", {options}" if options else ""
+        return f"ALTER TABLE{exists}{only} {self.sql(expression, 'this')} {actions}{options}"
 
     def add_column_sql(self, expression: exp.AlterTable) -> str:
         if self.ALTER_TABLE_INCLUDE_COLUMN_KEYWORD:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 322fa1ca..3465c56d 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -5281,6 +5281,9 @@ class Parser(metaclass=_Parser):
     def _parse_var_or_string(self) -> t.Optional[exp.Expression]:
         return self._parse_var() or self._parse_string()
 
+    def _parse_primary_or_var(self) -> t.Optional[exp.Expression]:
+        return self._parse_primary() or self._parse_var(any_token=True)
+
     def _parse_null(self) -> t.Optional[exp.Expression]:
         if self._match_set(self.NULL_TOKENS):
             return self.PRIMARY_PARSERS[TokenType.NULL](self, self._prev)
@@ -5299,16 +5302,12 @@ class Parser(metaclass=_Parser):
         return self._parse_placeholder()
 
     def _parse_parameter(self) -> exp.Parameter:
-        def _parse_parameter_part() -> t.Optional[exp.Expression]:
-            return (
-                self._parse_identifier() or self._parse_primary() or self._parse_var(any_token=True)
-            )
-
         self._match(TokenType.L_BRACE)
-        this = _parse_parameter_part()
-        expression = self._match(TokenType.COLON) and _parse_parameter_part()
+        this = self._parse_identifier() or self._parse_primary_or_var()
+        expression = self._match(TokenType.COLON) and (
+            self._parse_identifier() or self._parse_primary_or_var()
+        )
         self._match(TokenType.R_BRACE)
-
         return self.expression(exp.Parameter, this=this, expression=expression)
 
     def _parse_placeholder(self) -> t.Optional[exp.Expression]:
@@ -5551,6 +5550,7 @@ class Parser(metaclass=_Parser):
         parser = self.ALTER_PARSERS.get(self._prev.text.upper()) if self._prev else None
         if parser:
             actions = ensure_list(parser(self))
+            options = self._parse_csv(self._parse_property)
 
             if not self._curr and actions:
                 return self.expression(
@@ -5559,6 +5559,7 @@ class Parser(metaclass=_Parser):
                     exists=exists,
                     actions=actions,
                     only=only,
+                    options=options,
                 )
 
         return self._parse_as_command(start)
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index eab61052..004b2882 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -1187,7 +1187,7 @@ class Tokenizer(metaclass=_Tokenizer):
                 self._advance()
             elif self._peek == "." and not decimal:
                 after = self.peek(1)
-                if after.isdigit() or not after.isalpha():
+                if after.isdigit() or not (after.isalpha() or after == "_"):
                     decimal = True
                     self._advance()
                 else:
diff --git a/sqlglotrs/src/tokenizer.rs b/sqlglotrs/src/tokenizer.rs
index 94a8b084..927f3d80 100644
--- a/sqlglotrs/src/tokenizer.rs
+++ b/sqlglotrs/src/tokenizer.rs
@@ -470,7 +470,7 @@ impl<'a> TokenizerState<'a> {
                 self.advance(1)?;
             } else if self.peek_char == '.' && !decimal {
                 let after = self.peek(1)?;
-                if after.is_digit(10) || !after.is_alphabetic() {
+                if after.is_digit(10) || !(after.is_alphabetic() || after == '_') {
                     decimal = true;
                     self.advance(1)?;
                 } else {


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 38c19a77..db8da304 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -7,6 +7,7 @@ from sqlglot import (
     UnsupportedError,
     exp,
     parse,
+    parse_one,
     transpile,
 )
 from sqlglot.helper import logger as helper_logger
@@ -40,6 +41,11 @@ class TestBigQuery(Validator):
             },
         )
 
+        table = parse_one("x-0._y.z", dialect="bigquery", into=exp.Table)
+        self.assertEqual(table.catalog, "x-0")
+        self.assertEqual(table.db, "_y")
+        self.assertEqual(table.name, "z")
+
         self.validate_identity("SELECT * FROM x-0.y")
         self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql(), '"x"."y"."z"')
         self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index fd27a1ee..5f23c440 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -29,6 +29,7 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE foo (a BIGINT, INDEX USING BTREE (b))")
         self.validate_identity("CREATE TABLE foo (a BIGINT, FULLTEXT INDEX (b))")
         self.validate_identity("CREATE TABLE foo (a BIGINT, SPATIAL INDEX (b))")
+        self.validate_identity("ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE")
         self.validate_identity(
             "CREATE TABLE `oauth_consumer` (`key` VARCHAR(32) NOT NULL, UNIQUE `OAUTH_CONSUMER_KEY` (`key`))"
         )
@@ -68,6 +69,26 @@ class TestMySQL(Validator):
         self.validate_identity(
             "CREATE OR REPLACE VIEW my_view AS SELECT column1 AS `boo`, column2 AS `foo` FROM my_table WHERE column3 = 'some_value' UNION SELECT q.* FROM fruits_table, JSON_TABLE(Fruits, '$[*]' COLUMNS(id VARCHAR(255) PATH '$.$id', value VARCHAR(255) PATH '$.value')) AS q",
         )
+        self.validate_identity(
+            "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
+            "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
+        )
+        self.validate_identity(
+            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE KEY d (b), KEY e (b))",
+            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE d (b), INDEX e (b))",
+        )
+        self.validate_identity(
+            "CREATE TABLE test (ts TIMESTAMP, ts_tz TIMESTAMPTZ, ts_ltz TIMESTAMPLTZ)",
+            "CREATE TABLE test (ts DATETIME, ts_tz TIMESTAMP, ts_ltz TIMESTAMP)",
+        )
+        self.validate_identity(
+            "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT",
+            "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
+        )
+        self.validate_identity(
+            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
+            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
+        )
 
         self.validate_all(
             "CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
@@ -78,12 +99,6 @@ class TestMySQL(Validator):
                 "sqlite": "CREATE TABLE z (a INTEGER)",
             },
         )
-        self.validate_all(
-            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
-            write={
-                "mysql": "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
-            },
-        )
         self.validate_all(
             "CREATE TABLE x (id int not null auto_increment, primary key (id))",
             write={
@@ -96,33 +111,9 @@ class TestMySQL(Validator):
                 "sqlite": "CREATE TABLE x (id INTEGER NOT NULL)",
             },
         )
-        self.validate_all(
-            "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
-            write={
-                "mysql": "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
-            },
-        )
-        self.validate_all(
-            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE KEY d (b), KEY e (b))",
-            write={
-                "mysql": "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE d (b), INDEX e (b))",
-            },
-        )
-        self.validate_all(
-            "CREATE TABLE test (ts TIMESTAMP, ts_tz TIMESTAMPTZ, ts_ltz TIMESTAMPLTZ)",
-            write={
-                "mysql": "CREATE TABLE test (ts DATETIME, ts_tz TIMESTAMP, ts_ltz TIMESTAMP)",
-            },
-        )
-        self.validate_all(
-            "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT",
-            write={
-                "mysql": "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
-            },
-        )
-        self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
 
     def test_identity(self):
+        self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
         self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')")
         self.validate_identity("SELECT @var1 := 1, @var2")
         self.validate_identity("UNLOCK TABLES")