Postgres constraint generation

[cbility]

Hi,

I'm not sure if this is an issue, a feature request or a request for documentation. I'm quite new to NORMA and I'm liking the tool but I'm struggling to understand the full functionality and feel like I'm lacking some docs to review.

I'm generating PostgreSQL from an ORM model and recently noticed that a number of the constraints expressed in the model do not have a counterpart in the SQL and so can be violated in the database. I'm wondering whether I am doing something wrong that is causing the constraints to not be expressed in the SQL, or if not all constraints are supported by the code generation tool. If there are constraints that can be expressed in the model that cannot be translated into SQL, it would be good to understand what type(s) of constraint these are, so that I can implement it in the database or avoid relying on it.

As an example, consider this model:

Generating Postgres code from the above model gives the following:

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

CREATE SCHEMA IF NOT EXISTS ORMModel1;

SET search_path TO ORMMODEL1,"$user",public;

CREATE TABLE ORMModel1.ObjectType
(
	objectTypeId SERIAL NOT NULL,
	fact1 CHARACTER VARYING,
	fact2 CHARACTER VARYING,
	CONSTRAINT ObjectType_PK PRIMARY KEY(objectTypeId)
);

COMMIT WORK;

Note that the exor constraint is not enforced. I would have expected the generated code to look something like this (code amended by me):

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

CREATE SCHEMA IF NOT EXISTS ORMModel1;

SET search_path TO ORMMODEL1,"$user",public;

CREATE TABLE ORMModel1.ObjectType (
    objectTypeId SERIAL NOT NULL,
    fact1 CHARACTER VARYING,
    fact2 CHARACTER VARYING,
    CONSTRAINT ObjectType_PK PRIMARY KEY (objectTypeId),
    CONSTRAINT ExclusiveOrConstraint1 CHECK (
        (fact1 IS NOT NULL AND fact2 IS NULL) OR (fact1 IS NULL AND fact2 IS NOT NULL)
    )
);

COMMIT WORK;

Is there a way to get NORMA to generate code like this?

Documentation for NORMA that I've found and have been using is mostly the powerpoints found on orm.net under the Resources tab and some stack exchange questions. Are there other documentation resources? Information on the Pro tool is at youtube.com/@ORMSolutions. ORM Foundation seems to have been replaced with a scam site.

[mcurland]

Hi Adam,

You're correct in observing that external constraints are not currently mapped.

This case, where the constraint can be mapped to a check clause in a single row, is on the lower branches of the fruit tree, but hasn't been picked yet. The only extra constraints we really do (meaning beyond the basic internal uniqueness and mandatory) are mandatory group constraints for absorbed types (subtypes, objectified unary or functional binary, etc.). Basically, these just group the columns that would have been 'non null' in a separated subtype table and make them all set/not set together.

The pragmatic issue here is that a full implementation of these constraints at the row level can make the database almost impossible to populate. For example, consider the simple example of a supertype A with an auto-generated id and two xor subtypes B and C, where B has a mandatory reference to a C (possibly chained, meaning C has a mandatory reference to a D). This can easily be satisfied in one transaction (not one insert or update) without row-level row enforcement (create initial rows with just the supertype to get the ids, then fill in subtypes and references), but becomes increasingly difficult to stage, especially in an automated fashion, as the constraints become stricter. In this case, you would never be able to get an id for the supertype without specifying a subtype, and could never populate a D without first doing a C. This is an extraordinary amount of delicate staging needed for a simple and predictable example.

Basically, you can't solve this in a general fashion with row-level enforcement. This means deferred constraints (for systems that support it, like Postgres, although AFAIK no RDBMS supports deferred check clauses) and/or triggers to run at the end of a transaction. The way the DDL is generated is via overrides to a 'SQL standard' transform that operates on a generic XML representation of the DDL. We have never used the trigger portions of these targeted renderings. I always assume that code that hasn't run doesn't work, so it would likely need a lot of work just to get to the stage where we can get triggers generating at all.

The bottom line is that I would like to have this as well, but it needs to be done very carefully. Also, I'd rather have code generators and frameworks to enable ORM models as primary development artifacts that can generate a full stack application, which is what I'm concentrating on for the foreseeable future. This environment would have constraints enforced at different levels in the stack, but also require a database that will not be fully constraint compliant until transactions end. Again, I'm not discounting this request or the generator holes that haven't been closed. I just have to prioritize the work.

On the other topics:

1. ORM Foundation was run by Ken Evans, who succumbed last year after a long (maybe 15 year?) cancer battle (and inevitable old age, which always wins eventually). Unfortunately, he said the site had been hacked a year or so before that and he had never gotten it running again. He also let the domain expire. He was very tight with his code access. I had occasional access and had fixed a few site bugs for him, but he never left that access open long and I was never anywhere near the database. It is unfortunate as there was a ton of content there from years of ORM posts, but it is no longer.
2. NORMA Pro is a 'contact Matt' at this point. It is easy enough to install (just point VS to a private gallery), but currently needs a per-machine license file to load. The overview video on the youtube channel gives a good feel for what is in there.

-Matt

[cbility]

Hi Matt,

Thank you for your detailed response, and for all of your work on what is an excellent tool. That external constraint generation will probably not be supported soon is what I was afraid to hear. Your work on generating full applications from ORM sounds very interesting. If you will tolerate my thoughts:

• I do worry that moving the scope of ORM generation out of the database space will restrict users to a particular type of application, development environment or set of features.
• Could the issue of some constraints making the database unduly difficult/impossible to populate be navigated by a user compromising by marking the constraint in question as deontic instead of alethic?
• Could external constraint DDL generation be enabled only for an RDBMS that supports deferred constraints?

My use for NORMA as a powerful but quick to use database design and communication tool is restricted by the lack of external constraint generation. I am not sure if I will have a use for a full stack application generated from ORM, but I am interested in this project and will continue to follow it.

I will keep this issue open for now as I expect it will make it easier for others with the same question as me.