Hello,
I’m working with TDM 5.3.4.13, modelling for a postgresql 9.2 database.
my entities have some (7 for precision) “standard” attributes that alway appear at top.
In order to ease my job in preparing new entities, I’ve followed the advice I’ve seen somewhere in this site of using gallery…
So:
-
I’ve prepared 5 domains for the standard attributes (and not 7 because some attributes are duplicated, with different meaning); the domains have inside also check constraints defined
-
I’ve prepared a “base” entity with only the 7 duplicated attributes, that uses the domains for their definition
-
I’ve saved the base entity inside a gallery
So far so good…
Then when I have to generate a new entity, I pick up the base entity from gallery and drop it in my model. Fantastic! Problem is, that every time I drop an entity from gallery, the check constraints inside domains defined at point 1) get duplicated!!! So, after generating 5 entities, I find inside my domains (and by consequence inside my entities) 5 times the same constraint defined… My feeling is that when I drop the entity from gallery, the software check for existence of domains, and in case are missing generates them too (useful for cross-model use of gallery btw)… but only if they exists… probably the same check (generate only if non-existant) has been forgotten for check constraints inside domains (probably for defaults also, but this is something I don’t use)
I’ve tried workarounding adding the constraint directly inside attribute of base entity, but there I have another weird (and if possible even more showstopper) problem. As I always use “named” constraints, I’ve added the constraint at attribute level with name and capion: ckc<%ColumnName%>, and sql (for example) <%ColumnName%> >= 0. But while with a similar constraint inside domain I correctly see a generated SQL like “constraint “ckc_my_columname” check (my_columname >= 0)”, if I add the domain directly at attribute level the generated SQL is a disappointing “constraint “ckc<%ColumnName%>” check (my_columname >= 0)”… that means that application variables for constraint name are NOT resolved when constraint is applied directly inside attribute, but only for constraint SQL!!! I’ve tried to use different application variables (Name, FullName, etc) but with no luck… !!!
Hello Roberto,
First, thank you for your description. I'll break your issue into two parts:
Check Constrains duplication in DomainsThis is a bug, unfortunately. I have already reported it. Knowing our diligent devs, I'm sure they'll fix it soon.
Workaround using Check Constraints in Attributes
Under normal circumstances, Application Variables in Attributes are not resolved (you can see which Application Variables are resolved in Supported Properties section of their window, see picture below)
This section is different for every form (Attribute Properties, Entity Properties...).
There is a solution though. By using it, Application Variables in Attributes will be resolved, but I only recommend using it until a bug fix for the issue comes out.
- With Expert Mode enabled, go to Expert Mode Menu | Customization | Package Explorer.
- In Package Explorer look for Generations for PostgreSQL 9.2 | Scripts, right-click the PERCodeGeneratorPG92 and choose Properties.
- In Properties dialog click the Unlock Package button.
-
Close the dialog and double-click the PERCodeGeneratorPG92 script so it opens in Scripts window.
-
Find the following string:
** * text += "CONSTRAINT "+Check.QuotedName(Instance)+" ";***
it should be on line 525, change it to:
text += "CONSTRAINT "+Instance.Quote(Check.Resolve(Check.Name))+" ";
- Save the changes by clicking Save button.
Your Application Variables should be now resolved within Attributes. See the following pictures for difference between vs. after.
Bare in mind that you this applies for PG 9.2 models only and this configuration will be lost once you upgrade. It really is only a workaround until the original issue is fixed.
Regards,
Lukas
great answer! I am pretty sure this bug will be fixed in next rel… no doubt on this…
thanks for the workaround in the meantime 
Dear Lukas, one more thing:
while I really appreciate your effort in giving me a good workaround for the issue, thinking in long term (ie when the bug on domains will be fixed), I think is wiser to behave like the bug is already fixed, instead of applying the second workaround. This because, when bug will be fixed, I will not have to do any other maintenance job on my existing model. In the other case, i’ll need to pass each and every table to remove the constraint at attribute level (as I still prefer to keep constraints in domains as much as I can).
So I am asking a courtesy, if possible and does not require too much time: I need an help to code a “cleanup” macro that does a massive deletion of all constraints after the first one in my domains. I always have maximum one constraint per domain so I am not risking nothing with this routine… I will try to code it myself, but I am still at “dummy” level in macro creating, so a little help will be much much much appreciated … Thanks in advance!!!
Here you go, this will delete every Check Constraint in your Domains except for the first one:
function main()
{
var d, ch, Domain, CheckConstraint;
Model.Lock();
for (d = 0; d<Model.Domains.Count; d++)
{
Domain = Model.Domains.GetObject(d);
for (ch = Domain.CheckConstraints.Count - 1; ch > 0; ch–)
**{ **
CheckConstraint = Domain.CheckConstraints.GetObject(ch);
Log.Information("Deleting Check Constraint: " + CheckConstraint.Name);
CheckConstraint.Delete();
}
}
Model.UnLock();
}
Don’t forget to choose your model in Scripting Window:
Regards,
Lukas
That is!!!
great great great… now I have all I need
thank you for your help, much appreciated, really!!!
