search

xnuinside / simple-ddl-parser

Simple DDL Parser to parse SQL (HQL, TSQL, AWS Redshift, BigQuery, Snowflake and other dialects) ddl files to json/python dict with full information about columns: types, defaults, primary keys, etc. & table properties, types, domains, etc.
MIT License
175 stars 40 forks source link

Composite primary key, key & index in CREATE TABLE #260

Open hubg398 opened 2 months ago

hubg398 commented 2 months ago

Found another one while testing out the lib, logging it here to keep track for now.

Issue is with parsing composite primary keys with multiple columns

            CREATE TABLE `TABLE_2` (
                `column_1` int(11),
                `column_2` int(11),
                `column_3` int(11),
                `column_4` int(11),
                `column_5` int(11),
                `column_6` int(11),
                `column_7` int(11),
                `column_8` int(11),
                PRIMARY KEY `indexColumn1Column2` (`column_1`, `column_2`), /* This doesnt show in output */
                PRIMARY KEY `indexColumn1Column3Column8` (`column_1`, `column_3`, `column_8`),
                /* This doesnt show in output */
                PRIMARY KEY `indexColumn5` (`column_5`), /* This shows correctly in tables['primary_key'] */
            );
hubg398 commented 2 months ago

I think related to this, same issue parsing KEY/INDEX of the same syntax

CREATE TABLE `TABLE_2` (
                `column_1` int(11),
                `column_2` int(11),
                `column_3` int(11),
                `column_5` int(11),
                `column_8` int(11),
                KEY `indexColumn1Column2` (`column_1`, `column_2`), /* Doesnt crash, but incorrect. Only shows `column_1` as the column of the index
                KEY `indexColumn1Column3Column8` (`column_1`, `column_3`, `column_8`), /* Crashes when there's more than 2 columns here. Table fails to parse.
                KEY `indexColumn5` (`column_5`), /* Parses correctly */
            );

And also the UNIQUE variants

            CREATE TABLE `TABLE_2` (
                `column_1` int(11),
                `column_2` int(11),
                `column_3` int(11),
                `column_5` int(11),
                `column_8` int(11),
                UNIQUE `indexColumn1Column2` (`column_1`, `column_2`), /* Fails, lex error */
                UNIQUE `indexColumn1Column3Column8` (`column_1`, `column_3`, `column_8`), /* Fails, lex error */
                UNIQUE `indexColumn5` (`column_5`), /* Fails, lex error */
            );

Interestingly, it works if we swap 'UNIQUE' -> 'UNIQUE KEY'