Subject Re: [Firebird-Architect] Domains and NOT NULL in PSQL
Author Dmitry Yemanov

> While NOT NULL is a constraint, I see it as a complement of the data type.
> But seems to be only me. :-)

I well understand your point, but prefer to follow the standard here :-)

> If we not apply NOT NULL I also prefer TYPE OF.


>> Per SQL-2003, CAST(<value> AS <domain name>) is perfectly legal, so I
>> here.
> Yes, but if we have syntax that apply and that not apply constraints we
> need two syntax for cast too.

Why? CAST knows nothing about constraints and it shouldn't. Casting some
value to domain D should not mean that the value will acquire any constraint
definition from D. It's about datatypes only.

> We may require assignment in the declaration of NOT NULL variables

I'd vote for that.

> But default assignment of NOT NULL output parameters seems not logical.
> If we support constraints we need to do something about it.

Agree, and this is a problem, as I see no good solution here.

> We can easy do one of the two things:
> 1) Use the dependencies and don't allow such type of incompatible
> change. That will prevent some changes that make procedures continuing
> work correctly.
> 2) Allow the change and let procedure fail at runtime.

We cannot say whether the change is compatible or not. For tables, it's
pretty straightforward - you can convert numerics to strings and to increase
field length, and the mover guarantees that such a conversion is possible.
But for PSQL, this rule doesn't work (see my previous example with a format
length). So only BLR parsing can be a validation.

I see more choices here:

1) Any domain change marks all dependent BLRs as invalid. Any parsing or
execution of such BLR is rejected with an appropriate error. This "invalid"
flag is cleared as soon as the BLR gets successfully regenerated and parsed.
The flag is persistent, so it should be stored in the schema. This is the
most reliable approach, as it should catch all possible errors, but it's
also quite hard in use (it requires manual processing and perhaps also SQL
source to be available).

2) A domain change forces all dependent BLR to load and parse. Those failed
to parse are marked as invalid in the schema until its BLR is regenerated
and/or parsed successfully. It's the same as above, but not all objects are
invalidated but only those affected by an incompatible change.

3) A domain change doesn't enforce any BLR validation, instead it's defered
until BLR is loaded to the metadata cache again. If the parsing fails, the
flag is set in the schema. Mostly same as above, but user will see problems
only upon the first usage.

The goal of the "invalid" flag is to mark the objects that require user's
attention before being executed properly. It's done via a special error
message in runtime and via a schema flag in design-time.

4) We allow any changes without BLR invalidation. All possible issues will
throw an error at compile-time or runtime. It requires no clever logic on
our side, but I don't really like this approach. It reminds me MSSQL where
you can have a valid stored procedure that has no chances to either compile
or execute. You you may find it on production only.

> What you mean by non-SQL procedures?

Java ones. But I'm afraid we cannot answer it for sure until we have the
code in our CVS :-)

> We may better defer constraints.
> People want primary domain data type and we can have both working in the
> future with different syntax.

I tend to agree. Let's offer datatype only domain usage for now.