import sql (postgres ddl) into datacontract.yml: numeric --> variant

[SabineGl]

HI,

I'm using datacontract cli version 0.10.10

and tried to import a sql ddl file (postgres db syntax) into a datacontract yml but numeric is translated into "variant".

Table table and column comments are ignored. They do not show up in the description part of fields or model.

also references for foreign key information is ignored.

datacontract import --format sql --source cfg_customer_location_comments.sql

CREATE TABLE my_schema.customer_location
(
   id                             numeric              NOT NULL,
   created_by                     varchar(30)          NOT NULL,
   create_date                    timestamp            NOT NULL,
   changed_by                     varchar(30)         ,
   change_date                    timestamp           ,
   name                           varchar(120)         NOT NULL,
   short_name                     varchar(60)         ,
   display_name                   varchar(120)         NOT NULL,
   code                           varchar(30)          NOT NULL,
   description                    varchar(4000)       ,
   language_id                    numeric              NOT NULL,
   status                         varchar(2)           NOT NULL,
   CONSTRAINT customer_location_code_key UNIQUE (code),
   CONSTRAINT customer_location_pkey PRIMARY KEY (id),
   CONSTRAINT customer_location_language_fk FOREIGN KEY (language_id) REFERENCES my_schema.language(id)
);

comment on table my_schema.customer_location is 'Table contains records of customer specific Location/address.'

comment on column my_schema.customer_location.change_date is 'Date when record is modified.'
comment on column my_schema.customer_location.changed_by is 'User who modified record.'
comment on column my_schema.customer_location.code is 'Customer location code.'
comment on column my_schema.customer_location.create_date is 'Date when record is created.'
comment on column my_schema.customer_location.created_by is 'User who created a record.'
comment on column my_schema.customer_location.description is 'Description if needed.'
comment on column my_schema.customer_location.display_name is 'Display name of the customer location.'
comment on column my_schema.customer_location.id is 'Unique identification ID for the record - created by sequence SEQ_CUSTOMER_LOCATION.'
comment on column my_schema.customer_location.language_id is 'Language ID. Reference to LANGUAGE table.'
comment on column my_schema.customer_location.name is 'Name of the customer location.'
comment on column my_schema.customer_location.short_name is 'Short name of the customer location.'
comment on column my_schema.customer_location.status is 'Status of the customer location.'

Result:

dataContractSpecification: 0.9.3
id: my-data-contract-id
info:
  title: My Data Contract
  version: 0.0.1
models:
  customer_location:
    type: table
    fields:
      id:
        type: variant
        required: true
        primary: true
        unique: true
      created_by:
        type: varchar
        required: true
        maxLength: 30
      create_date:
        type: timestamp
        required: true
      changed_by:
        type: varchar
        maxLength: 30
      change_date:
        type: timestamp
      name:
        type: varchar
        required: true
        maxLength: 120
      short_name:
        type: varchar
        maxLength: 60
      display_name:
        type: varchar
        required: true
        maxLength: 120
      code:
        type: varchar
        required: true
        maxLength: 30
      description:
        type: varchar
        maxLength: 4000
      language_id:
        type: variant
        required: true
      status:
        type: varchar
        required: true
        maxLength: 2

[roykoand]

I think a lot of stuff just not yet implemented. As of numeric type, it should be pretty easy to fix:

https://github.com/roykoand/datacontract-cli/blob/b56656a1d99fbfc7fb8a26da3df666a5a00ac438/datacontract/imports/sql_importer.py#L51-L82

You would need to add numeric into this function's logic, since there's no a proper if it goes to else section which is variant type

[jochenchrist]

For comments, we would need to wait for an upstream fix in simple-ddl-parser issue https://github.com/xnuinside/simple-ddl-parser/issues/173

[jochenchrist]

Mapping for numeric and decimal added: https://github.com/datacontract/datacontract-cli/commit/e0aa5acb5944fcb7a7588857f42c25b201cb6c96