Suggestions for VS NORMA Development

[gitmikebrown]

I've been working with NORMA for a little while now and have compiled a few notes along the way. It's possible that some of the items below may already be implemented within NORMA, but I may not be aware of them. I share all of these respectfully and not as a work order.

1. Date Columns

   • Currently, I need to manually add "Date Modified" and "Date Updated" columns/values to every ID-related entity type. Could an option be added to enable these columns for all tables by default? Alternatively, could this functionality be added to the properties window so that I can toggle them on or off for specific tables? Adding "Date Last Accessed" as a third option would also be useful.

2. Additional Data Types

   • Enum: (.code) feels similar to an Enum type but isn't explicitly identified as such within ORM verbalization. Could this be clarified or explicitly defined as Enum?
   • JSON: To support PostgreSQL compatibility, incorporating JSON (e.g., jsonb) as a data type would be beneficial.

3. Indexed Columns

   • It does not appear the NORMA extension provides any way to designate a column as indexed. Could this functionality be introduced? For example, adding an option within the properties tab could allow users to specify indexed columns. This would be particularly useful for frequently indexed fields, such as ID entity types with auto counters or columns like email addresses that require indexing in certain projects.

4. Improved Contextual Descriptions

   • Could "Informal Description" be changed to "Informal Description for [Entity Name]"? This adjustment would help AI better understand the relationship between the description and the entity when verbalizations are plugged in.
   • Similarly, the "Notes" section in the properties tab could also benefit from this type of change.

5. Enhanced Table Display in Relational View

   • In the relational view, where tables and their relationships are displayed, would it be possible to adjust the table format to a three-column layout (for example: PK | age | int)? This could potentially make the presentation more visually clear and organized, especially when dealing with complex table relationships.

[mcurland]

That's quite a list Mike. Let me see where I can add some insights.

1. Date Columns In years past I had an 'add audit columns' example that modified the output .dcil file (XML table/column representation) to produce another .dcil with additional columns on every table. So, the tool supports this, but it is a little harder to do after the setup changes (since 2017). NORMA used to have a fixed location and global registry entries. Now it doesn't, so anything plugged in needs to have an associated .vsix installer file. The exact audit columns to add depends on the user and are really a physical mapping issue more than a conceptual one. I totally concur that this can add a ton of complexity to a model. There is also a good chance your in-model audit columns will change the relational mapping, which really isn't what you want. Let me poke around old drives and see if I still have this transform. A sample transform/.vsix should be fairly easy to produce. However, the exact modifications made to the output tables will be completely up to you.
2. Additional Data Types Enum types are interesting. This is more closely tied to a value constraint than an actual data type. Basically, a constraint with distinct values (not ranges) could give names to those values to produce an enum. The JFE mapping (see the @ormsolutions channel on youtube) allows this on the client (the database stores and transports the strings as strings, but the JavaScript reduces them to numbers (const enum in typescript)). There are other related mappings here. For example, a bunch of unaries on one object type can be reduced to a bitfield enum. The main advantage here is the ability to store a number instead of a string. However, strings are interned in modern database, meaning that a repeated string is not extra data, so the win is minimal. You also have much easier maintenance on the db. Other mappings, like lookup tables (enabling name changes) are also possible. However, it really isn't just a datatype.
   New datatypes are something I would like to get to sooner than later. I've really only held off because it is a breaking file format change to do these correctly. However, to do this right, we would also want to schematize the json (same for xml) instead of just having what is essentially a blob. Changes would elevate data types to be object types, which allows things like per-target datatype specialization, scalar functions defined as fact types, data queries and aggregation results without defining a value type just to hold the data, and some level of user-defined data types. This is a ton of work though, especially typing the json (I haven't checked if postgres supports that).
3. Indexed Columns NORMA generates primary and foreign keys, which are backed by indexes. Also, if any of these indexes have multiple columns, then the first column will be used as an index (this can make the role order on your uniqueness constraints more significant). However, if you're trying to index both directions across a many-to-many join then you'll need to add additional indexes. I know it isn't ideal, but I generally have a '...ByHand.sql' file that does this (and occasionally redefines constraints as deferred) to add these extra lookups. It is rarely more than a page or two and easy enough to execute with the generated file. The main issue here is relating this back to something conceptual (like 'navigagle') without cluttering the model. Another related one is 'key total' (create a table). This is triggered by IsIndependent=true (among other things) right now, but this isn't totally correct either because this means all rows (referenced and independent) need to be listed in the table. Given how easy this is to add by hand it will likely be well down the relational list. I need to tackle things like delete propagation, multiple database in a model (multiples is probably NORMA Pro), etc. before I tackle this.
4. Improved Contextual Descriptions This isn't likely to change since the indentation implies the context of the statement. Having said that, I know that copying out the html verbalization loses the verbalization indentation. Do you have different results if you reapply the indents or otherwise group the statements? Note that I just (like an hour ago) posted verbalization capabilities at https://ormsolutions.com/tools/verbalize (like the ...orm page, just with a verbalization pane). This is a full verbalization rewrite (new engine, new outputs, new object model) that supports a few things NORMA does not. The biggest of these is plain-text verbalization. I just haven't put the button on yet to trigger this, but being able to verbalize the selection to the clipboard as plain text (with indentation) would make this a lot easier. The options (things like how object type names are rendered, so 'object_type' could be 'object type' or 'ObjectType' or 'objectType' etc.) might also affect AI interpretation.
5. Enhanced Table Display Wow, its been years (probably over a decade) since I cracked the table rendering code. I have considered adding vertical representations in the orm uniqueness style as a smaller notation. The default relational view is nightmare fuel once you get above ~30 tables though (Pro lets you construct multiple relational views). Feel free to make suggestions: the code is AlternateViews\RelationalView\ShapeModel\ColumnElementListCompartment.cs. The biggest issue is likely determining the minimum shape width (friendly note: Do not try running dev and setup NORMA on the same Visual Studio installation).

Feel free to push back on these. I'm especially curious on the AI results. The web verbalizer is an active project, so things like a 'copy text' button and options UI are not far out.

[gitmikebrown]

1. Date Columns

Thank you for offering a solution. Keep in mind I don't know all that much about VS and working with code extensions. Learning to work with those files may turn into more than what it would take for me to just add the value types where I need them.

I re-read my number one and can see that I forgot to add that the biggest help of all would be having those audit values (date added/modified) show up in the ORM verbalization by way of a dropdown option of some kind. Visually seeing them in the relational view would be nice too, but the ORM verbalization is where this would be most helpful.

[gitmikebrown]

2. Additional Data Types

I can see how enums and JSON types would function more as value constraints, and I completely agree with that perspective. My initial reason for bringing these up was to suggest reflecting them explicitly within the NORMA ORM verbalization. Even if they are treated as value types conceptually, it would be incredibly helpful if the verbalization specified when a value is an Enum or JSON. This clarity would make it easier for me to use the verbalization effectively, especially when collaborating with AI tools like Copilot.

For example, if the ORM verbalization labeled a value as "JSON" and I copied it into Copilot while working with a PostgreSQL database, Copilot would know to create a column with the appropriate JSON or JSONB data type. Similarly, marking a value as "Enum" would help AI tools interpret the constraints and generate SQL with an ENUM type or its equivalent. Having these designations directly in the verbalization would bridge the gap between conceptual design and practical implementation, saving time, and reducing any inaccuracy.

[gitmikebrown]

3. Indexed Columns

Thank you for the reply on this. It sounds like it would be easier for me to make a note and handle this manually as needed. I appreciate the offer to work with a config file, but I don’t have all that much experience with Visual Studio config files.

I'm most likely going to give the ORM verbalization to AI and ask it to create what is needed. With that said, that's where it would be helpful if these indexed columns were reflected within the ORM verbalization. This would provide additional clarity when working with AI.

[gitmikebrown]

4. Improved Contextual Descriptions

That tool is AWESOME! I use Copilot in Edge. Within Edge, and without having to export anything, Copilot can see everything relating to my project. It can look through all of the tabs too. It sees everything because of that tool. Thank you for this.

The one issue I’ve noticed, and this is more of a me problem, is that Copilot in the browser doesn’t carry over what it sees to the desktop version. I like working with the desktop app more so than the browser. Being that I have everything broken up into different tabs, is there a way to do a "copy all" for the complete verbalization of my project? That way I can paste it into a text file and upload it to AI. I know you said you were working on a button. Is this what you are referring to?

[gitmikebrown]

5. Enhanced Table Display in Relational View - I don't know much about that syntax/language. If someone sees this and feels inspired, below is what AI had to say about it:

Modifications Required:

1. Update the GetItemDrawInfo Method:

   • This method is responsible for formatting and displaying each column's information in the compartment.
   • Currently, it appends constraints (e.g., PK, FK), column names, and optionally the data type to a single string.
   • The logic can be updated to construct a three-part representation separated by clear delimiters (like tabs or spaces).

2. Logic for PK and FK Indicators:

   • The section that appends PK (PrimaryKeyString) and FK (ForeignKeyString) constraints already exists.
   • This would need to be adjusted so that the FK/PK indicator appears as a distinct part in the first column.

3. Column Name Placement:

   • The column name (e.g., currentColumn.Name) is already handled in this method. It will now need to appear explicitly in the second column.

4. Data Type Rendering:

   • The data type is generated by the helper method GetDataType.
   • This would need to be consistently formatted as the third column.

5. Adjustments to String Formatting:

   • Instead of combining all elements into a single string, you would separate them into three distinct sections and control their alignment to visually create a three-column layout.

---

Code Suggestion:

Here's an idea of what the updated GetItemDrawInfo method might look like with these changes:

public override void GetItemDrawInfo(ListField listField, int row, ItemDrawInfo itemDrawInfo)
{
    base.GetItemDrawInfo(listField, row, itemDrawInfo);
    Column currentColumn = this.Items[row] as Column;
    Debug.Assert(currentColumn != null, "An item in the ColumnElementListCompartment is not a column.");

    // Determine FK/PK
    StringBuilder fkPkIndicator = new StringBuilder();
    foreach (UniquenessConstraint constraint in UniquenessConstraintIncludesColumn.GetUniquenessConstraints(currentColumn))
    {
        if (constraint.IsPrimary)
        {
            fkPkIndicator.Append(PrimaryKeyString);
        }
        else
        {
            fkPkIndicator.AppendFormat(AlternateKeyString, /* constraint number logic here */);
        }
    }
    foreach (ColumnReference columnReference in ColumnReference.GetLinksToTargetColumnCollection(currentColumn))
    {
        fkPkIndicator.AppendFormat(ForeignKeyString, /* FK numbering logic here */);
    }

    // Get column name
    string columnName = currentColumn.Name;

    // Get data type
    string dataType = GetDataType(currentColumn.AssociatedValueType);

    // Combine into a three-column layout
    itemDrawInfo.Text = $"{fkPkIndicator.ToString().PadRight(10)} | {columnName.PadRight(20)} | {dataType}";
}


### Display Adjustments:
- You might also need to modify the layout settings in `InitializeShapeFields` to ensure that these columns are aligned properly in the visual representation.

---

### Next Steps:
1. Implement and test these changes in a development environment.
2. Ensure that the layout works well even for wider data types and longer column names.
3. Handle edge cases, such as columns with no FK/PK or undefined data types.