search

tobymao / sqlglot

Python SQL Parser and Transpiler
https://sqlglot.com/
MIT License
6.79k stars 714 forks source link

Online DDL in Mysql #3020

Closed samotarnik closed 9 months ago

samotarnik commented 9 months ago

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!

georgesittas commented 9 months ago

Hey, I'll take a look into this soon. May need to do a refactor while I'm at it because it seems like MySQL uses these "table options" in other clauses as well..