Unsupported "DEFAULT NEXT VALUE FOR" & ON <DESTINATIONCLAUSE>

[fredcwbr]

Unsupported parse of NEXT VALUE FOR and ON on CREATE TABLE

When CREATE TABLE uses DEFAULT NEXT VALUE or script does user storage options (ex.: ON PRIMARY) , the parser does not output any results.,

To Reproduce

from simple_ddl_parser import DDLParser

ddl_Orig = '''
CREATE TABLE [dbo].[SLIPEVENTO] (
[cdSLIP] [bigint] NOT NULL
DEFAULT NEXT VALUE FOR [dbo].[sqCdSLIPEvt] ,
-- Referencia da origem do evento
[hashOrigem] VARBINARY NOT NULL,
-- HASH pela tabela de onde vem o lancamento
-- SIC, DARE21, SF, Manual, ...
[tbOrigem] [int] NOT NULL,
[tbVrtDstDflt] [int] NULL,
CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ),
-- chave de referencia para multiplos lancamentos distribuidos
-- ??? Identifica um documento origem via HASH na tbOrigem
CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem])
-- A FK precisa vir por trigger, pois sao diferentes origens.,
--
) ON [PRIMARY]
GO

'''

ddl1 = '''
CREATE TABLE [dbo].[SLIPEVENTO] (
[cdSLIP] [bigint] NOT NULL,
-- Referencia da origem do evento
[hashOrigem] VARBINARY NOT NULL,
-- HASH pela tabela de onde vem o lancamento
-- SIC, DARE21, SF, Manual, ...
[tbOrigem] [int] NOT NULL,
[tbVrtDstDflt] [int] NULL,
CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ),
-- chave de referencia para multiplos lancamentos distribuidos
-- ??? Identifica um documento origem via HASH na tbOrigem
CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem])
-- A FK precisa vir por trigger, pois sao diferentes origens.,
--
) ON [PRIMARY]
'''

ddl2 = '''
CREATE TABLE [dbo].[SLIPEVENTO] (
[cdSLIP] [bigint] NOT NULL,
-- Referencia da origem do evento
[hashOrigem] VARBINARY NOT NULL,
-- HASH pela tabela de onde vem o lancamento
-- SIC, DARE21, SF, Manual, ...
[tbOrigem] [int] NOT NULL,
[tbVrtDstDflt] [int] NULL,
CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ),
-- chave de referencia para multiplos lancamentos distribuidos
-- ??? Identifica um documento origem via HASH na tbOrigem
CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem])
-- A FK precisa vir por trigger, pois sao diferentes origens.,
--
);
'''

result = DDLParser(ddl_Orig).run(dump=True)
print(result)

result = DDLParser(ddl1).run(dump=True)
print(result)

result = DDLParser(ddl2).run(dump=True)
print(result)

Expected behavior
Interpretation as proposed on docs.

Desktop (please complete the following information):

• SQL Dialect - MSSQL - Autogenerated scripts - V12
• OS: Debian - Python3.9
• Idle3 ; Eric6, QtCreator (all of them )
• Version
• fred@alpha:~/Documentos/GoiasFomento/QtPy/src$ pip3 install simple-ddl-parser
  Collecting simple-ddl-parser
  Downloading simple_ddl_parser-0.20.0-py3-none-any.whl (31 kB)
  Requirement already satisfied: ply<4.0,>=3.11 in /usr/lib/python3/dist-packages (from simple-ddl-parser) (3.11)
  Installing collected packages: simple-ddl-parser
  Successfully installed simple-ddl-parser-0.20.0

Additional context
Add any other context about the problem here.

CREATE TYPE does not parse too.

DROP TYPE dbo.T_LCT_SLIPS ;
GO
CREATE TYPE dbo.T_LCT_SLIPS AS TABLE (
hashKY varbinary(48),
numContratoGF bigint,
FATORGERADOR date ,
dtREFSIC date,
cdTbOrigem int,
cdTbVrtDstDflt int,
Sq int,
vl DECIMAL(32,0),
descr varchar(254),
tpEvento varchar(254),
tpLcto char
);
GO

[xnuinside]

@fredcwbr hi, thanks for reporting the issue. Will work on it.

[fredcwbr]

@fredcwbr hi, thanks for reporting the issue. Will work on it.
Thanks in advance., .. .will wait for it.,
BTW , have a nice day.,.. and a good mug of ko.fi..

Is there any interest on a full script ? If it of any use, the attachment has a partial DB implementation with almost everything.
SLIPV1.3.txt

My goal is to use your ddl-parser to extract table/view/procedures/tables of TABLE TYPE and generate a "factory like" class ENUM based to adress PySide2/6 MVC

[xnuinside]

@fredcwbr , thanks for samples, will work on them

[xnuinside]

@fredcwbr I released 0.21.0 version - https://pypi.org/project/simple-ddl-parser/. It correct parses ddl samples that you write directly in the issue. About Types - problem was in 'AS TABLE' - previously was added only AS ENUM, AS OBJECT and several more. Now AS TABLE also supported.

I checked the script that you attached - it contains a lot of statements ) and also includes VIEW, Procedures & etc I planned to support them some time ago, but bumped of thoughts that they contains DML like INSERT, SELECT & etc. and it little bit contrivers to idea of DDL parser. I'm really not sure that to do with them. Maybe parse like 'flat' object only name & columns for view and 'raw' query in result and something similar for procedure. Because another way if parse it correctly statement-by-statement - it will be again AST tree as sqlparse

PS: thanks for ko-fi mug :) really appreciate

[fredcwbr]

@fredcwbr I released 0.21.0 version - https://pypi.org/project/simple-ddl-parser/. It correct parses ddl samples that you write directly in the issue. About Types - problem was in 'AS TABLE' - previously was added only AS ENUM, AS OBJECT and several more. Now AS TABLE also supported.

Thank you very much for your work ., it's a saver.,

I checked the script that you attached - it contains a lot of statements ) and also includes VIEW, Procedures & etc I planned to support them some time ago, but bumped of thoughts that they contains DML like INSERT, SELECT & etc. and it little bit contrivers to idea of DDL parser.

DML on DB generating scripts are, in general, helpers to pre-load; They don't affect the DB structure "per-se" ; Considering the goal of DDL , considering it as DMLCOMMENT or just COMMENT would be fine. After all they do not contribute do DB Structure.
If some one does argue about tricks ant trinkets used on such things as dynamic DDL, thats just it: they are tricks and trinkets.;
See, use them myself, for a descendant hierarquichal SELECT from a WITH ., and thats the point , .. the WITH is a DB INFORMATION abstraction .., the SELECT is not,.
In conclusion, if one is looking for DDL, it is not looking for DML . Two complimentary universes.

I'm really not sure that to do with them. Maybe parse like 'flat' object only name & columns for view

Although my idea of Views (and Materialized Views for this matter) is a debateble, I look as View (and MVs) as table equivalents; The reason is that in whatever level the normalization of the database is TRIGGER [ INSTEAD OF ] .... is used in these DB Objects to abstract information from/to the normalized structure.
Adding support to mimic TABLE-LIKE structure of these objects (name,columname,type,NULLABLE), would help tremendously in allowing an interface to "recompile/interpret" on the fly a ENUM-LIKE MVC structure that can be used from an abstract point of view on QT (pySide2/5) ., and other frameworks .

and 'raw' query in result and something similar for procedure. Because another way if parse it correctly statement-by-statement - it will be again AST tree as sqlparse

I agree with you, it does not matter what is inside procedure or functions as far as DB INTERFACE is concerned, having the raw code would be a plus, ... but the important issue would be the TABLE-LIKE object interface that can be derived (should really stop repeating myself) , either as a scalar, vector or dict .,

PS: thanks for ko-fi mug :) really appreciate
It's my pleasure.,

Have a very nice day.

Best Regards from Brasil.,