Update DDL Generation Scripts

[darnjo]

String Enumerations

We want to use String enumerations and the Lookup Resource for the RESO Reference server.

The Lookup Resource and corresponding insert statements for it already exist, but individual records can't currently store values for multi-enumerations due to the table row size constraints (too wide).

Single enumerations are fine at the moment, and are already generating strings in the DDL, it's multiple-enumerations that need to change (since they're currently only VARCHAR(64)).

There are a few potential options for storing multi-valued enumerations:

• TEXT field
• Join Table
• JSON Data Type

The JSON Data Type in MySQL/MariaDB seems to be sufficient for storing multiple enumerations.

Questions (Samee)

• How does the JSON type calculate into the table size (since we're already constrained)?
  • Searching: https://dev.mysql.com/doc/refman/8.0/en/json.html
    • any() and all() queries, with the ability to filter by substring match of any value in the JSON array of values
    • compound conditions (or, in, and)
    • things can be a little inefficient on the reference server since it's not expected to perform in a production environment

See: Web API Core 2.0.0 Specification for queries that need to be supported.

Related Issue: RESOStandards/reso-web-api-reference-server#24

Once we answer these questions and are confident we want to proceed in this direction, we need to update the appropriate DDL generator for multiple enumerations. (Samee)

• Field creation markup for multi-enumeration JSON values.

Note: For the DDL generator, the DD 2.0 branch should be used in the Commander repository. The multiple enumeration generator can be found here. This will be back-ported into the #100 branch and merged into main.

Field Resource Insert Statements

We want to generate the Field Resource as well. For the initial version of this, we only need to generate the non-deprecated fields from the DD 1.7 spec. https://ddwiki.reso.org/display/DDW17/Field+Resource

In the future, we will need to support everything outlined here, but we just have to be aware it's coming and don't have to build it now.

There are two deprecated fields we'll want to handle now (@darnjo).

• Deprecated fields in the Field Resource

• ResourceName becomes ModelKey (the key can just the resource name)
• DisplayName becomes DisplayNames, which will be a complex type with an array of locale/value pairs representing each display name.