Additional Data types on SQL DDL generation

[COF-RNorman]

Jut a general question:

I was looking to generate a SQL database and noticed I could not select the GUID data type (UNIQUEIDENTIFIER) with any option I could find. Is there a way to do this? Anybody open to adding the feature so that ObjectID or some other datatype could be set to be a custom supplied type?

[mcurland]

The last two commits (Jan 29 and a reference mode migration fix on Feb 25) dealt with this issue. I just haven't done a Marketplace push yet. I have other users who've been using it in the Pro version of the tool, so I'm comfortable with the change now. I haven't done the readme writeup for it yet.

There were a surprising number of changes that were needed to make this work. NORMA did not support auto-generation on any datatype other than AutoCounterNumeric, so the notion of auto-generation in NORMA needed to be expanded. The SQL generated for this is also much different (default function instead of a sequence, etc.) So, along with adding UUID there were several other additions.

1. Auto-generated data type can now be indicated on 2/4/8 byte signed integer in addition to AutoCounter, which is equivalent to auto-generated i8. This was the set of types supported by most database targets. SQL Server supported few more, but auto increment on a byte is kind of silly, and auto increment on a decimal is a bit much as well, so I picked a reasonable set.
2. The only database targets that natively support UUID are SQL Server and Postgres. Other targets have an auto generator function (like Oracle) and the datatype produced by NORMA matches the generator functions.
3. Name generation did not properly replace casing on an acronym (like UUID). This has been modified. The replacement phrases are now case sensitive and anything recognized as a fixed case (basically reference mode units and acronyms) can be replaced with an exact match. If you care about casing and want to avoid this, use the Uuid reference mode instead of UUID.
4. The sample population editor has an item for auto datatypes.
5. Enable a default data type on a non-intrinsic reference mode.
6. SQL Server importer is much cleaner with the data type import, including UNIQUEIDENTIFIER support. I haven't finished other database targets yet (there was a lot more messed up here than just UUID).

Anyway, it won't help you until I get it posted, so I'll try to get to that in the next couple of days.

You can read more details at:
2effaa6 and
1d734b9

[COF-RNorman]

Ok, cool. I will keep an eye out for the update. I appreciate all the work everyone is doing on this. It has become one of my favorite design tools so I recommend it all the time.

[mcurland]

This is ready to push, but my internet has been glitching out all night, so I'll try again tomorrow (likely early afternoon MDT when the kids are in preschool.)

I'm curious on how you're using the tool, would like to chat privately as well if you're willing. I'm glad you're enjoying the tool.

[mcurland]

This has been posted now. If you have automatic updates enabled you can restart Visual Studio to update the version, which should move from 1.3047.5 to 1.3048.0.

[COF-RNorman]

Awesome thank you sir. I appreciate all your hard work. And sure I can give more insight on how I use the tool.

For me I started using Object Role Modeling more after they demoed it on The .NET Show way back when... ( see: https://www.youtube.com/watch?v=ztyGtbJhlGU )

After the Visual Studio Visio tool was basically retired, I ran across NORMA as a plugin. Really liked the way we were able to create conceptual models and confirm with stake holders that is what they intended. Really helpful to explain things. Also when questions were asked about database design choices we could relate that back to the conceptual model we created.

Now where I work they have not used it before but the reports that come out of this tool help me explain why certain design choices were made and make edits to the design to better fit what they intend.

I will admit that currently the tools don't lend themselves to a "code-first" model like Entity Framework promotes, but for me it is a small price to pay for the clarity and universality of the output for now. Maybe in the future this tool can generate the classes that Entity Framework can use to generate the DB schema. But right now I just take the generated DDL and link that into a DB project which is easier for our SQL developers to read anyway.

[mcurland]

Cool, so you've probably been in the ORM camp longer than I have. My education started when I was recruited to write what is now NORMA in the 2004 timeframe, but it was a year after that before we started the project. I might have you beat on working with object/relational mapping systems (which I always call O/RM since the original ORM predated O/RM systems by 3 decades) after working on the unreleased ObjectSpaces project for Microsoft. ObjectSpaces was a relational mapping system that predated Entity Framework by several years (around 5). In fact, the 'virtual tree grid' control used for some large sets in VS (like code coverage results) and a ton of controls in NORMA was written as a skunk works project while I was waiting for an implementable spec for the ObjectSpaces front end. AFAIK it is the only part of the project that left Redmond, and it is still in use 2 decades later and 10 versions of VS later.

From an ORM modeler's perspective, going "code-first" is possibly missing the mark even more than "database-first". At least with a database there is a reasonable logical representation of the system. With code you're already at a physical representation, then marking it up to get a logical representation. But none of this is close to the conceptual model of the domain because you're trying to model concepts with a fixed structure.

There is a big chunk of NORMA that isn't open source. I'm working on getting the Pro version more publicly available but don't really have the bandwidth for broad release at this point. One of the main things I've been concentrating on is a framework that not only covers the database, but also a client-side definition of the model with a strong rules engine. The client framework reads data defined in terms of the model and produces json change sets that can be incrementally applied on the server. The backend is a generated database, O/RM layer, and I/O layer that communicates with the O/RM layer and produces/consumes json from the client. Basically, my apps use one write method than can write any set of legal model changes to the database (I don't care if the request changes 1 row or 10,000), then a few lines of code to interface with the web app and call the generated code.

Basically, there is zero chance you can touch any of this with a code-centric approach because you simply don't have sufficient information about your domain. I have a data-bound client layer and generated interfaces (for TypeScript), a generated model (sufficient for all reading, writing, rules and change management), generated JSON production, generated I/O layer, generated O/RM and generated database. We're also working on automatically reading/writing XML (with a schema produced from the same model, of course) into the system. Basically, everything behind the UI is generated directly off the ORM model, and it all works seamlessly together because it uses the model as the primary development artifact. There is no way you coordinate half a dozen (and growing) representations of the same data with code markup.

Basically, I think in a lot of ways the ORM community and tools were heavily distracted by being able to create the perfect database. The ultimate database is obviously a great thing to have, but in reality DDL is only a fraction of the code in any real development project (well under 5% in my experience). The goal is to read and write data in a form that is easily recognized to the conceptual model. If you can accomplish that then how the data is stored becomes less and less central to the system. I want 95% generated, not 5%.

Do I have an EF stack I can offer you at this point? Unfortunately, no. The first project I actually built this stack for wanted PHP, so the current implementation is built on Doctrine and PHP. A .NET Core implementation is moving up the list though. I'd like to be able to simply plug in the backend language/framework, database choice (including relational or not) and front-end binding layer (Angular, React, etc.). Right now though the stack is PHP/Doctrine for the data access with an Angular application (pick your web server). The Doctrine markup can produce the databases (and covers things NORMA doesn't, like sqlite), but the database isn't as constrained as the NORMA generators, which add a number of checks that can't be mapped in Doctrine.

Another issue is that that SQL Server still doesn't do deferred constraints. This is a major problem with O/RM implementations (not just mine) because it is very easy to make object changes that are 100% correct at the end of a transaction but violate row constraints. I'm usually on Postgres, but have done Oracle also, and prototypes on sqlite and sql server.

mcurland at live dot com if you're interested in talking more.

[EladiaPaviolitis4oe]

I would love to see the feature added to allow for custom supplied data types, such as ObjectID, to be set to a specific type. This would make the process much more flexible and allow us to better tailor the database to our specific needs.

[mcurland]

I'll take the liberty of surmising that there is a very specific requirement behind that general request, but I won't try to guess what it is, so I'll give you a broad answer which includes mea culpas along with where I'd like to be.

I'll be the first to say that data types could use some work. The current set was based directly on Visio Modeler when the tool was first put together in 2005 and have been only lightly extended since that time. Frankly, I never intended this very limited approach to last as long as it did, but changing out the data typing system is far from trivial at this point due to the amount of code that relies on it. Much of this is external file format dependencies. The NORMA file format was never meant to be quite as stable as it has become (there was much quicker turnover in the earlier days of the tool), or the primary way to read the model, but it has become that some that a lot of people know how to read, so breaking changes become a political issue as much as a technical one.

(Skip down if you want to know how to force this in the current tool.)

The challenge with data types is that really are on the boundary between the conceptual and physical layers. NORMA always makes the assumption that there are multiple physical implements produced by the model, even of the same kind like a database. The conceptual model doesn't really care how you store a date value, but a given database implementation might, and you'll see a lot of variance across the db world here. It really isn't feasible to collect precision physical implementation data for all physical targets with a fixed set of conceptual types and 2 placeholder aspects (scale and length, which are displayed with different names for different data types). So, basically, the system needs to be reworked so that we can collect data for other aspects of the datatype as well as actually define data types instead of just consuming a fixed list. We also need to expand aspects (beyond scale and length) so things like regex would be really useful, but it is only meaningful for implementation targets if you can include different flavors of the same expression for the different targets.

Currently there are two parts of the system that are 'outside' the object type/fact type/role paradigm. One is data types, the other is functions. Functions are used with formal derivation rules, which are defined and verbalization in NORMA but only have editing tools in NORMA Pro. There are several problems with this separation that are blocking work in this area, so it will have to change soon. Basically, 1+2=3 is a fact and should be treated as such, but you have to be very careful how this is done to keep your system finite. It is fundamental to data systems that declared fact types are object types have a finite population, either through assertion or derivation from a finite population. Adding arithmetic operations in the same mixing bowl means that you now have infinite sets. Obviously, this is done in the real world and very possible, you just have to make sure all role players for the infinite fact types are sourced from finite sets.

Where this is going is that data types need to be first-class object types, not a completely separate class. Functions operating on the data types also need to be fact types, which we'll call scalar fact types to distinguish from the normal ones. The set of data types (with some standard set of names for synchronization) then needs to be defined in a reference model along with other things (reference mode patterns, a good collection of arithmetic functions, etc.). These external types could then be copied when used into the local model (the user would just see a global list, merging local/reference sets would be up to the tool). Data types could also be composed, annotated, renamed, and stored so you could define things like complex numbers and reason about them as a single value as well as defining things like 'medium length string' to reference in your model.

The trick then comes with the physical mapping, where you would allow different physical targets to define and store their own annotations on a data type, thus freeing up the generators to use more target-specific data. Compound data types would also need to be expandable for systems that don't natively support them as raw data would still be able to consume the data and be generated from the model.

Anyway, this is obviously a long list and not currently scheduled, but I need it relatively soon to finish things like aggregation rules and queries--you need to be able to get a query result without needing a value type in the model to hold the data (calculations, count(), etc.). I can't really do this without promoting data types to first-class object types.

(Skip to here)

This is easiest with a per-user install because the files are easier to find and aren't admin-locked. You'll find the files below the %localappdata% directory file (system environment variable) in Microsoft\VSVersion\Extensions\ORM Solutions\Natural ORM Architect\NORMAVersion\Xml\Generators\DIL\Transforms. You'll see XSLT files different database targets here. They all import the DDILtoSQLStandard.xslt transform. The trick is to hijack another type. I usually use a string type with an odd. So, for example, matching ddt:characterString[@Length=1001] in a template and use a variable length string at length 1001 to trigger the code.

These transforms are reloaded automatically when they change, so if you 'Run custom tool' in NORMA the outputs will immediately use the modified files. The downside is that they'll also be overwritten when NORMA updates--so keep a copy.

[COF-RNorman]

I will close this discussion with the latest release fixing the issue of GUID data types (UUID). Thanks for all your work on this before I even brought it up. Will be willing to continue to provide feedback as I find things or issues. Appreciate you and anyone else that contributed to this really cool tool.