Column comments not assiciated to column names

[erwin-frohsinn]

Describe the bug A DDL containing COMMENT ON COLUMN does not associate the comment to the column

To Reproduce run this:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from simple_ddl_parser import DDLParser
from pprint import pprint

parse_results = DDLParser("""-- something.t_spiel definition

-- Drop table

-- DROP TABLE something.t_spiel;

CREATE TABLE something.t_spiel (
    id varchar(5) NOT NULL,
    refprodid varchar(10) NULL,
    titel varchar(100) NOT NULL,
    refsaalid varchar(10) NULL, -- Verweis auf den Saal
    freieplatzwahl bool NOT NULL DEFAULT true, -- Ja/Nein
    CONSTRAINT idx_t_spiel_primary PRIMARY KEY (id)
);

-- Column comments

-- COMMENT ON COLUMN something.t_spiel.refsaalid IS 'Verweis auf den Saal';
-- COMMENT ON COLUMN something.t_spiel.freieplatzwahl IS 'Ja/Nein';

-- something.t_spiel foreign keys

ALTER TABLE something.t_spiel ADD CONSTRAINT "foreign_key_Ref_Prod_Id" FOREIGN KEY (refprodid) REFERENCES something.t_produktion(id);
ALTER TABLE something.t_spiel ADD CONSTRAINT "foreign_key_Ref_Saal_Id" FOREIGN KEY (refsaalid) REFERENCES something.t_saal(id);
""",silent=True).run(output_mode='sql')

pprint(parse_results)

This code produces ... {'comments': [' Verweis auf den Saal', ' Ja/Nein']}...

which is just a collection of comments w/o reference to the fields they belong to.

Expected behavior something like {'comments': {'something.t_spiel.refsaalid': ' Verweis auf den Saal', 'something.t_spiel.freieplatzwahl': ' Ja/Nein'}}... should be generated, or even better for postprocessing, the comment should be added to the field description: {'check': None, 'default': None, 'name': 'refsaalid', ... 'comment': 'Verweis auf den Saal' },

Desktop (please complete the following information):

• Database: Postgresql
• DDL created with DBeaver

[xnuinside]

@erwin-frohsinn hi! Thanks for opening the issue, can you provide the link to the doc for such type of comments? Normally in sql doble ‘-‘ mean start of the comments that are ignored in SQL interpretation and it is just a comment in the document without any associations

[erwin-frohsinn]

Thanks for this fast reply! https://www.postgresql.org/docs/current/sql-comment.html describes COMMENT. I saw that the comments are written without leading "--", therefore I modified my code accordingly to

-- Column comments

COMMENT ON COLUMN something.t_spiel.refsaalid IS 'Verweis auf den Saal'; COMMENT ON COLUMN something.t_spiel.freieplatzwahl IS 'Ja/Nein';

but also in this case, DDLParser produces

{'comments': [' Verweis auf den Saal', ' Ja/Nein']}

Thanks for providing this great program! Kind regards.

On 21.11.22 07:15, Iuliia Volkova @.***> wrote:

@erwin-frohsinn https://github.com/erwin-frohsinn hi! Thanks for opening the issue, can you provide the link to the doc for such type of comments? Normally in sql doble ‘-‘ mean start of the comments that are ignored in SQL interpretation and it is just a comment in the document without any associations

— Reply to this email directly, view it on GitHub https://github.com/xnuinside/simple-ddl-parser/issues/173#issuecomment-1321511618, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARU4XPRL2HHMJPX4GYQIE2LWJMHPRANCNFSM6AAAAAASGI3RVM. You are receiving this because you were mentioned.Message ID: @.***>

[ahaessly]

Hi. I have also run into the same problem. Just to clarify, these are not comments within the ddl that start with "--". These are SQL statements that add comments to tables, columns, etc. Also, I'm not seeing any 'comments' entry in my json.

I would also like to see the comments added to the appropriate item. For example, if the ddl has a COMMENT ON TABLE, the comment should be added under the table json node. And when there is a COMMENT ON COLUMN, the comment should be added under the column json node.

For example, this ddl:

--
-- PostgreSQL database dump
--

CREATE TABLE public."Diagnosis" (
    id text NOT NULL,
);

COMMENT ON TABLE public."Diagnosis" IS 'A collection of characteristics that describe an abnormal condition of the body as assessed at a point in time. May be used to capture information about neoplastic and non-neoplastic conditions.';

produced this json (no comment section):

[
 {
  "columns": [
   {
    "name": "id",
    "type": "text",
    "size": null,
    "references": null,
    "unique": false,
    "nullable": false,
    "default": null,
    "check": null
   }
  ],
  "primary_key": [],
  "alter": {},
  "checks": [],
  "index": [],
  "partitioned_by": [],
  "tablespace": null,
  "schema": "public",
  "table_name": "\"Diagnosis\""
 }
]

[tduval-unifylogic]

greetings! any update on this?