Check Entitiy's property "Index to Foreign Key in Child Entity"

Toad Data Modeler
1

Hi,

I'm developing a macro for Toad DataModeler 5.3 and I need to check "Index to Foreign Key in Child Entity" in the relationships. I'm developing this MACRO:

var Key, Rel, a, i, fkName, Attr, Constraint;

Log.Information("- Taula " + Entity.Name + ", " + Entity.Keys.Count + " claus, " + Entity.Relations.Count + " relacions." );

Entity.Lock();

var prefix;
var appPrefix;
prefix = GetEntityPrefix(Entity)
appPrefix = GetAppPrefix(Entity);

for (a=0; a<Entity.Relations.Count; a++)
{
Rel = Entity.Relations.GetObject(a);
fkName = GetFKName(Entity, Rel, Log);

if (fkName!=null) {
Rel.Name = fkName;
Rel.Caption = fkName.substring(fkName.indexOf("") + 1, fkName.length - fkName.indexOf(""));
}
if (Rel.SupportsProperty("CreateIndexToFK")) {
Rel.CreateIndexToFK = true;
}
if (Rel.SupportsProperty("FkIndexName")) {
Rel.FkIndexName = Rel.Name + "_I";
}
}

for (a=0; a<Entity.Attributes.Count; a++)
{
Attr = Entity.Attributes.GetObject(a);
if (Attr.SupportsProperty("ConstraintNotNullName")) {
if (Attr.NotNull) {
Attr.ConstraintNotNullName = appPrefix + "_" + Attr.Name + "_NN";
} else {
Attr.ConstraintNotNullName = "";
}
}
}

if (Entity.SupportsProperty("CheckConstraints")) {
for (a=0; a<Entity.CheckConstraints.Count; a++)
{
Constraint = Entity.CheckConstraints.GetObject(a);
var chkOldCaption= Constraint.Caption;
var chkOldPrefix= "";
var chkAttr = Constraint.Caption;
if (chkOldCaption.indexOf("")==3) {
chkOldPrefix = chkOldCaption.substring(0,4);
chkOldCaption = chkOldCaption.substring(4);
}
Constraint.Caption = prefix + "" + chkOldCaption;
Constraint.Name = appPrefix + "_" + Constraint.Caption + "_CK";
Constraint.Name = Constraint.Name.toUpperCase();

if (Constraint.SQL!="") {
if (chkAttr!=Constraint.Caption) {
Constraint.SQL = ReplaceAll(Constraint.SQL, chkAttr, Constraint.Caption);
}
if ((chkOldPrefix!="") && (chkOldPrefix!=(prefix + ""))) {
Constraint.SQL = ReplaceAll(Constraint.SQL, chkOldPrefix, prefix + "");
}
} else {
Constraint.SQL = Constraint.Caption + " in ('S', 'N')";
}

}

Model.jpeg
Model.jpeg370×627 38.6 KB

This MACRO works fine on DataModeler 5.2, but not on 5.3.

Can anyone help me with the issue?

Thank you

2

Hello Gabriel,

The change is related to the Foreign Index in Child Entity system change. If you remember, we discussed the change in this topic (the differences between the systems in TDM 5.2 and 5.3 is explained here).

I’m mentioning this because this change has affected macros as well. Some of the properties/methods you use in your macro have become obsolete in TDM 5.3.

You can either go through your code and try to refactor the obsolete code, or you can send your code to us and we will try to fix it. If you would like us to refactor your code, please:

  • Clearly specify what is your macro supposed to do.
  • Send us the entire macro with all related functions (ideally in a custom TDM package) to modeling@quest.com.
    Regards,

Lukas

3

Hi Lucas,

I would like make the changes myself. Can you get me some macro code examples to check “Foreign Index in Child Entity”?

Thank you,

Regards

4

Look at the following examples:

Creating a Foreign Index in Child Entity:

var Relation = Model.Relations.GetObjectByName('Relationship1');
Relation.CreateForeignIndex();

Deleting a Foreign Index in Child Entity:

if (Relation.ForeignIndex != null)
{
Relation.ForeignIndex.Delete();
}

Note that you are basically doing the same action as if you would have checked the Foreign Index in Child Entity option in Relationship Properties. Once you create the index, the option will be checked as well. And if you delete the index, the option will become unchecked accordingly.

Also make sure to not accidentally run the CreateForeignIndex method multiple times. It would create multiple indexes, but only the newest one is linked to the Relationship and can be deleted via ForeignIndex.Delete method. You would have to delete the rest manually.

Regards,

Lukas