Toad World® Forums

What is the Best Jscript Method to find FK Relations by Child Table Name/Attribute Name Directly with out Repeat Scanning

Hi

I have been Trying to Decipher the Reference Guide to try to help me write a Jscript which would allow me to Generate a Report

I know how to Iterate Entities and within Entities to Iterate the Attributes

And I Can discover in my iteration of the Attributes that I can use the Field Attribute.CountParentAttributes and if it is > 0 then I know it is a Foreign.

However, I want to for a Entity/Attribute which is a Foreign key list the Parent Entity/Attriubute

So I can Iteration ALL Relationships in My Model using this in order to Produce a Report

function main()
{
var app = System.GetInterface(‘Application’);
var Model = app.Models.GetObject(0); // first model in Application View
var r,a, Relation,Foreignkey, Parent_Attributes, Child_Attributes;

for (r=0; r<Model.Relations.Count; r++)
{
Relation = Model.Relations.GetObject®;
Parent_Attributes="";
Child_Attributes="";

for (a=0; a<Relation.ForeignKeys.Count; a++)
{
Foreignkey=Relation.ForeignKeys.GetObject(a);

if (a == 0)
{
Parent_Attributes=Foreignkey.AttrParent.Name;
Child_Attributes=Foreignkey.AttrChild.Name;
}
else
{
Parent_Attributes=Parent_Attributes+","+Foreignkey.AttrParent.Name;
Child_Attributes=Child_Attributes+","+Foreignkey.AttrChild.Name;
}
}
Log.Information(“Relation = “+Relation.Name+” Parent= “+Relation.ParentEntity.Name+”(”+Parent_Attributes+")"+" Child= “+Relation.ChildEntity.name+”("+Child_Attributes+")");
}
Log.Information(“DONE”);
}

But I would like to know what is the Most Efficient way to Go from Entity/Attribute which I know is a Foreign key (Aka it is the Relation.ChildEntity.name and one of the Attributes of that relations Relation.ForeignKeys.AttrChild.Name in order to on the Same line as the Entity/Attribute produce the Parent Entity Name and its Attribute Name (Aka the Relation.ParentEntity.Name and the Foreignkeys.AttrParent.Name)

Is there a Method to do that (like Relations.GetSomething(parentname,childname) without Creating a Internal Array of All Parent/Child Attribute lists and Scanning it every time.

Please let me know.

Thanks

Anthony Vitale

Hi Anthony,

there are code that should help you

function main(){
var i, j, Ent, Attr, ParentAttr;
for(i=0;i<Model.Entities.Count;i++)
{
Ent = Model.Entities.GetObject(i);
for(j=0;j<Ent.Attributes.Count;j++)
{
Attr = Ent.Attributes.GetObject(j);
if (Attr.IsFK()) //This Method show you if Attribute is Foreign
{
//This loop very easy go through all Paren Attributes - One Attribute can have more parent attributes, but without relationship
for(k=0;k<Attr.CountParentAttributes;k++)
{
ParentAttr = Attr.ParentAttributes(k);
Log.Information(" Parent= “+ParentAttr.Owner.Name+”("+ParentAttr.Name+")"+" Child= “+Ent.Name+”("+Attr.Name+")");
}

     //Better waym with relationship
     for(k=0;k<Attr.**FKForeignKeys**.Count;k++)
     {
       FK = Attr.FKForeignKeys.GetObject(k);           
       Log.Information('FK Relation: '+FK.Owner.Name+'Parent: '+ FK.AttrParent.Owner.Name+'.'+FK.AttrParent.Name+' Child'+Attr.Owner.Name+'.'+Attr.Name);
     }   
   }  
 }

}
}

Regards

Daril

Thank you Daril.

That is Great.

Sorry to ask for what might sound Dumb for me to ask.

But Is there any form of Diagram of the DOM used (aside from the Reference Guide) which shows some inter-relationship of the Various Classes (like Entity to Attribute to Parentkeys and Foreignkeys

Do you know if there is anything that for a DataModeler of a Database Can be used by me to better understand the hierarchies involved in this Tool.

I assume the Answer is NO but I have to ask as I have been Diving Head long into Using TDM for our Company now and I truly love the Jscript ability here.

Please let me know.

Anthony Vitale

Unfortunately this diagram is not available.

You can a lot of information see from stored model file (txp file). It is xml document so you can see tree structure of existing objects. Each object has property ID with GUID and you can identify object by this property.

Daril

Sorry, I figured it out using this

function main() {
var App = System.GetInterface(‘Application’);
var Model = App.Models.GetObject(0); // first model in Application View
var Entity, entity_iterator, r, a, Relation, Foreignkey, Parent_Attributes, Child_Attributes;
var WorkSpaceMain = Model.Workspaces.GetObjectByName(“Subject2”);

for (entities_iterator = 0; entities_iterator < WorkSpaceMain.ShapeList.Count; entities_iterator++) // iterate entities
{

Entity = Model.Entities.GetObjectByName(WorkSpaceMain.ShapeList.GetObject(entities_iterator).Name);

for (r = 0; r < Entity.Relations.Count; r++) {
Relation = Entity.Relations.GetObject®;
if (Relation.ChildEntity.name == Entity.Name) {

Parent_Attributes = “”;
Child_Attributes = “”;

for (a = 0; a < Relation.ForeignKeys.Count; a++) {
Foreignkey = Relation.ForeignKeys.GetObject(a);

if (a == 0) {
Parent_Attributes = Foreignkey.AttrParent.Name;
Child_Attributes = Foreignkey.AttrChild.Name;
}
else {
Parent_Attributes = Parent_Attributes + “,” + Foreignkey.AttrParent.Name;
Child_Attributes = Child_Attributes + “,” + Foreignkey.AttrChild.Name;
}
}
Log.Information("ALTER TABLE " + Entity.Name + " Add Constraint " + Relation.Name + " FOREIGN KEY ( " + Child_Attributes + ") References " + Relation.ParentEntity.Name + “(” + Parent_Attributes + “);”);
}
}
}
Log.Information(“DONE”);
}