Add support for custom column settings, and for table settings

[danlessa]

Hi there! I'm interested in using DBML as an metadata container for our Data Warehouse, and it would be interesting if we could add notes and settings for Tables, and custom settings for the Fields.

Something like that:

Table table_name [table_settings] { field1 field_type [column_settings] field1 field_type [column_settings] }

where table_settings and column_settings could be an list of an arbitrary key/values and/or flags, not only the ['note', 'default', 'pk', 'null', 'unique', 'increment'] ones.

Those arbitrary and non-specified keys would have an implementation-dependent interpretation then.

[gerry-hornbill]

I was looking at this and had the exact same requirement. We currently use XML as our DDL metadata, and I was thinking about defining something more succinct and easier to read using CSS-like syntax. DBML meets that requirement for the most part. The big thing is, the standard does not go far enough in terms of allowing implementation-specific custom settings, it would be nice to see that in by default and to then see tools that work with dbml to allow for these custom settings.

I suppose as dbml is made available under the apache licence, so I could always branch the spec and extend it, I would probably have to do that anyway for internal use in order to document the custom settings, but its a shame that the base spec does not already support this

[TeaNguyen]

Thank you for sharing your thoughts.

Indeed, we're considering letting users define & store custom metadata for DB objects with new Metadata concept.

Something like this:

// ----- Physical structure definitions -----
Table public.merchants {
    id int [pk]
    ...
}

// ----- Custom metadata & settings -----
Metadata <field> public.merchants.id {
    note: '''
        lorem ipsum
        ...
    '''
    color: #000000
    added_by: "TeaNguyen" // custom metadata
}

Metadata <table> public.merchants {
    owner: "TeaNguyen" // custom metadata
}

Metadata <schema> public {
    ...
}

Inner thoughts

Why are we introducing the new Metadata concept? Why don't we simply add custom metadata in column settings, as proposed above?

• Flexible: Allow users to decouple the code definition for (custom) metadata/settings from the physical structure. We believe it will be useful for users in working with DBML in the CI/CD pipeline.
• Clean and legible syntax: We do not want a table/column's code definition to become a muddle after users add a lot of custom metadata to its settings.

Feel free to share your thoughts about this.

[gerry-hornbill]

We currently use XML as metadata to describe our database schema's, this works well, its well structured and allows for easy extensibility. We have certain properties that are important to us and our application, and I am sure thats true for many requirements, this is why I asked the question.

One of the major benefits I can see with DBML is to be able to describe a schema in a more succinct and readable form. At first glance, the concept of Metadata is certainly flexible, but, for what its worth, in my opinion that will add a lot of verbosity to a DBML schema, you will start to erode the thing that makes DBML an attractive possibility.

One other oddity with the spec is the Ref: statement. Again, very nice idea and can simplify the description of relationships, this is made all the better when you have auto visualisation tools to show these. However, again, in our application, we describe relationships using XML, in our case we call these entities, and an entity essentially extends a table, and describes its relationships to other entities. In our use case then, the Ref: could also do with user definable properties too. Unlike tables which are described like so

Table the_table_name { .... }

The Ref: objects are described on a single line with a Ref: tag. It seems a little odd that it does not follow the same pattern

Ref the_ref_name { col1 > col2 [some options] } Or something like that anyway, just for more consistency and flexibility.

Appreciate these views may well not be shared by others, and its great that DBML even exists, but thought I would share my thoughts in the hope they may be of some use. As it stands at the moment, DBML cannot be used for what I would have liked, but it does look like we could generate some DBML from our existing metadata in order to generate some visual documentation easily - I am looking at it from that perspective at the moment.

[atkawa7]

@TeaNguyen We are also hitting a similar issue. We want to auto generate jpa entities from dbml. The challenge is aliasing some columns to different field names. Maybe something like this would do province_id integer [pk, alias: 'id']

Table Province {
  province_id integer [pk, alias: id]
  code varchar [unique, not null]
  name varchar [not null]
  local_name varchar
  is_active boolean [default: true]
  longitude decimal
  latitude decimal
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  updated_at timestamp [default: `CURRENT_TIMESTAMP`]
}

@Setter
@Getter
@Entity
@Table(
    name = "tms_province"
)
public class Province {
  @Column(
      name = "province_id"
  )
  @Id
  private Long id;

  @Column(
      name = "code"
  )
  private String code;

  @Column(
      name = "name"
  )
  private String name;

  @Column(
      name = "local_name"
  )
  private String localName;

  @Column(
      name = "is_active"
  )
  private Boolean isActive;

  @Column(
      name = "longitude"
  )
  private BigDecimal longitude;

  @Column(
      name = "latitude"
  )
  private BigDecimal latitude;

  @Column(
      name = "created_at"
  )
  private LocalDateTime createdAt;

  @Column(
      name = "updated_at"
  )
  private LocalDateTime updatedAt;
}