Table names, quoted and delimited

[nurih]

DDL syntax allows for declaring a table name delimited by square brackets:
CREATE TABLE [T1] (id: int);

When parsing this, what should the table name be?
Should it be [T1] or T1?
If a foreign key constraint on table T2 refers to the field id of T1, should it use T1.id or [T1].id?

[xnuinside]

@nurih yeah... it DDL in different dialects (at least that I saw for now) names can be in [], ``(bigquery), '', and "".
I torn between two options: parse names as is in DDL with [] or clean names before parser output. I decided to choose by default first variant, because in second option you cannot reconstruct DDL from parser output as it was before parsing. But it's not needed in each case of parser usage - I understand.

Maybe make sense to add flag in parser like 'normalize_name' or smth like this and if this flag passed - remove [] from names.

[nurih]

Makes sense, trying to balance the 2 requirements:

• That a forward-generated DDL from the AST will produce the identical DDL
• That field content represents the correct syntactical element

How about this approach:

1. SEMVER major change: clean name, store delimiters separate
   1. table_name stores parsed name part, without surrounding delimiters
   2. table_name_delimiters stores a 2-element tuple/ list of outer delimiters
2. SEMVER minor change: store bare name in original field
   1. Allow round trip parse-generate
      1. table_name stores name part, including surrounding delimiter
      2. table_identifier stores parsed name part, without surrounding delimiters
   2. Behavioral: Syntactically correct round trip parse-generate
      1. Setting normalize_names enum for tablespace, table name, schema, etc. defaults to None. marshals what goes into respective fields
      2. Discard delimiters where indicated
      3. Add quoted_name(name, delimiters=('"','"')) to allow DDL generation from AST. This will use delimiters supplied, " by default, but not match exactly the syntax it is parsed from. Will retain grammatically correct DDL though.
   3. Behavioral: Identical round trip parse-generate
      • Same as syntactically correct, but keep original delimiters in __delimiters: {namespace?: , table?: schema?:} or a {field}_delimiters counterpart field as necessary

My vote tends toward 2.ii or 2.iii. Maybe even 2.iii and 2.ii.c together to allow granular control over forward-generation from AST.

Realizing this project is named simple ddl parser, I totally understand not needing to cover every niche use case though.

[xnuinside]

First of all, thanks for suggestions. All reasonable, but I'm not sure about this "table_name_delimiters stores a 2-element tuple/ list of outer delimiters". You mean store something like ('[', ']')?

[nurih]

Yes, that's what I meant.

[xnuinside]

I started to add table_name_delimiters output, but recognized that it will be not correct to use this field for whole table, because for example, this is valid statement:

create table foo.[index] (col1 int);

delimiters [ and ] used only in one name, but other without it
also this will be valid too:
create table foo.[index] ("col1" int);

so I just added 'normalize_names" flag - https://github.com/xnuinside/simple-ddl-parser/pull/114/files

[nurih]

Very cool.
Tested with mixed notation, works great.

def test_flag_normalize_names_mixed_usage():

    ddl = ddl = """
    CREATE TABLE [dbo].T1(ID int NOT NULL PRIMARY KEY)    
    CREATE TABLE dbo.[T2](T2_TO_T1_ID int FOREIGN KEY REFERENCES dbo.[T1](ID))
    CREATE TABLE dbo.T3(T3_TO_T1_ID int FOREIGN KEY REFERENCES [dbo].[T1](ID))
    """


    result = DDLParser(ddl, silent=False, normalize_names=True).run(group_by_type=True)
    expected = {
        'tables': [
            {
                'columns': [
                    {
                        'name': 'ID',
                        'type': 'int',
                        'size': None,
                        'references': None,
                        'unique': False,
                        'nullable': False,
                        'default': None,
                        'check': None
                    }
                ],
                'primary_key': ['ID'],
                'alter': {},
                'checks': [],
                'index': [],
                'partitioned_by': [],
                'tablespace': None,
                'schema': 'dbo',
                'table_name': 'T1'
            },
            {
                'columns': [
                    {
                        'name': 'T2_TO_T1_ID',
                        'type': 'int',
                        'size': None,
                        'references':
                        {
                            'table': 'T1',
                            'schema': 'dbo',
                            'on_delete': None,
                            'on_update': None,
                            'deferrable_initially': None,
                            'column': 'ID'
                        },
                        'unique': False,
                        'nullable': True,
                        'default': None,
                        'check': None
                    }
                ],
                'primary_key': [],
                'alter': {},
                'checks': [],
                'index': [],
                'partitioned_by': [],
                'tablespace': None,
                'schema': 'dbo',
                'table_name': 'T2'
            },

            {
                'columns': [
                    {
                        'name': 'T3_TO_T1_ID',
                        'type': 'int',
                        'size': None,
                        'references':
                        {
                            'table': 'T1',
                            'schema': 'dbo',
                            'on_delete': None,
                            'on_update': None,
                            'deferrable_initially': None,
                            'column': 'ID'
                        },
                        'unique': False,
                        'nullable': True,
                        'default': None,
                        'check': None
                    }
                ],
                'primary_key': [],
                'alter': {},
                'checks': [],
                'index': [],
                'partitioned_by': [],
                'tablespace': None,
                'schema': 'dbo',
                'table_name': 'T3'
            },

        ],
        'types': [],
        'sequences': [],
        'domains': [],
        'schemas': [],
        'ddl_properties': []
    }

    assert expected == result