Skip to content

Instantly share code, notes, and snippets.

@gevaertw

gevaertw/ArchiSQLGenerator.ajs

Last active August 25, 2019 09:44
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gevaertw/626fc6c954b841d1c88b53876e4699b4 to your computer and use it in GitHub Desktop.
Save gevaertw/626fc6c954b841d1c88b53876e4699b4 to your computer and use it in GitHub Desktop.
Download ZIP
#jarchi #ArchiSQLGenerator
Raw
ArchiSQLGenerator.ajs
/*
The goal is that a user creates a pattern on a view. Based on that pattern a SQL query is created to analyse the entire model for that view.
The model must be in a database created with herve database plugin (Link) This is very powerfull to analyse the model using SQL and by further extention BI tools like PowerBI
Check https://gevaertw.wordpress.com/generating-sql-queries-for-the-archi-database for more info
*/
//
//-------------------------- Script Settings --------------------------------------------------------------
// to set language comment or uncomment
dbLanguage = "HerveDB"; //dbLanguage = "HerveDB" for regular archi-herve DB's if you want queries for sparx: dbLanguage = "Sparx";
//----------------------------------------------------------------------------------------------------------------------------------------------------
/*Debug Functions--------------------------------------------------------------------------------------------------------------------------------------*/
function showScriptStartHeader ()
{
//this function displays a start header with start time
console.clear();
var scripStartDate = new Date();
console.log("/*Script start " + scripStartDate + "\n" + "----------------------------------------------------------------*/");
}
function showScriptEndHeader ()
{
//this function displays a end header with end time
var scriptStopDate = new Date();
console.log("/*----------------------------------------------------------------" + "\n Script done on " + scriptStopDate + "\nIf you value and use Archi please consider making a donation! https://www.archimatetool.com/donate */" );
}
function showJarchiCollection(JarchiCollection_Parameter)
{
l = JarchiCollection_Parameter.length;
for (i = 0; i < l; i++)
{
console.log(
i
+ " | " + JarchiCollection_Parameter[i].name
+ " | " + JarchiCollection_Parameter[i].type
+ " | " + JarchiCollection_Parameter[i].id
+ " | " + JarchiCollection_Parameter[i].documentation
);
}
}
/*Archi Functions---------------------------------------------------------------------------------------------------------------------------------------*/
function showSequencedArray (sequence_Parameter)
{
for (i=0; i<sequence_Parameter.length; i++)
{
console.log (
"sequence_Parameter.SeqID: " + sequence_Parameter[i].SeqID+ "\n"
+"sequence_Parameter.ArchiID: " + sequence_Parameter[i].ArchiID+ "\n"
+"sequence_Parameter.Name: " + sequence_Parameter[i].Name+ "\n"
+"sequence_Parameter.Type: " + sequence_Parameter[i].Type+ "\n"
+"sequence_Parameter.Direction: " + sequence_Parameter[i].Direction+ "\n"
);
}
return null
}
function isRelation (archiObject_Parameter)
{
// this function returns true if the given object is a relation, false if not
var relationList = ["composition-relationship", "aggregation-relationship", "assignment-relationship", "realization-relationship", "serving-relationship", "access-relationship", "influence-relationship", "triggering-relationship" , "flow-relationship", "specialization-relationship", "association-relationship"];
var i = relationList.indexOf(archiObject_Parameter);
if (i <0)
{
r = false;
}
else
{
r = true;
}
//console.log (i, r);
return r;
}
function getRelationDirection (ArchiElementID_Parameter, ArchiRelationID_Parameter)
{
//given the realtion check the sourceEnds and targetEnds. Return source, target or null (in case no relation)
//create a collection containing the relation
testedRelationColllection = jArchi("#" + ArchiRelationID_Parameter);
//console.log("testedRelationColllection", testedRelationColllection);
testedRelationSources = testedRelationColllection.sourceEnds();
//console.log("testedRelationSources", testedRelationSources);
testedRelationTargets = testedRelationColllection.targetEnds();
direction = null;
if (testedRelationSources[0].id == ArchiElementID_Parameter)
{
//console.log ("Source found. ");
direction = "Source";
}
else
{
if (testedRelationTargets[0].id == ArchiElementID_Parameter)
{
//console.log ("Target found. ");
direction = "Target";
}
else
{
//console.log("No direction found. ");
direction = null;
}
}
//console.log("Direction: " + direction);
return direction;
}
function reverseDirection (Direction_Parameter)
{
//function returns the other direction
r = null;
switch(Direction_Parameter)
{
case "Source":
r = "Target";
break;
case "Target":
r = "Source";
break;
default:
r = null;
}
return r;
}
function getArchiElementsFromView (view_Parameter)
{
//this function gets all Child ID on a view (from parameter) ->>>> these are not the ID's of the elements
// if the view contains notes an error is thrown, TODO
allChildObjectOnView = view_Parameter.children();
allElementsOnViewArr = [];
for (i=0; i< allChildObjectOnView.length; i++)
{
//is the current ittaration an element or a relation?
if (isRelation(allChildObjectOnView[i].concept.type) == false)
{
//console.log ("An element " + allChildObjectOnView[i].concept.name);
boundsFromView = allChildObjectOnView[i].bounds;
//console.log (boundsFromView);
allElementsOnViewArr [i] = {
ID:allChildObjectOnView[i].concept.id
,Name:allChildObjectOnView[i].concept.name
,Type:allChildObjectOnView[i].concept.type
,xBounds:boundsFromView.x
,yBounds:boundsFromView.y
,widthBounds:boundsFromView.width
,heightBounds:boundsFromView.height
};
}
else
{
//console.log ("A relation " + allChildObjectOnView[i].concept.name);
allElementsOnViewArr [i] = {
ID:allChildObjectOnView[i].concept.id
,Name:allChildObjectOnView[i].concept.name
,Type:allChildObjectOnView[i].concept.type
,xBounds:2147483647 // can I remove this? its nasty
};
}
}
return allElementsOnViewArr;
}
function getSortedElementListForQuery(allElementsOnViewArr_Parameter)
{
//based on the X position on the view we can now sort the array. The pattern is element-relation-element-relation-element.... this means taht in the aray all elements ae in the even position
//
allElementsOnViewArr_Parameter.sort(function (a,b) {return a.xBounds - b.xBounds});
//console.log("allElementsOnViewArr_Parameter entry: ")
//console.log (allElementsOnViewArr_Parameter);
var sortedElementListForQuery = [];
// first, put allelements in order
for (i=0; i <= allElementsOnViewArr_Parameter.length; i+=2)
{
sortedElementListForQuery [(i)] = {
ID:allElementsOnViewArr_Parameter[(i/2)].ID
,Name:allElementsOnViewArr_Parameter[(i/2)].Name
,Type:allElementsOnViewArr_Parameter[(i/2)].Type
};
}
//console.log("sortedElementListForQuery after element sort:")
//console.log(sortedElementListForQuery);
// now put the relations in between if a relation matches both source and target element it fits in between...
//console.log ("allElementsOnViewArr_Parameter.length: " + allElementsOnViewArr_Parameter.length);
for (i=0 ;i < ((allElementsOnViewArr_Parameter.length-1)/2); i++)
{
//define the elements to check
firstElementID = sortedElementListForQuery[i*2].ID;
nextElementID = sortedElementListForQuery[((i*2)+2)].ID;
//console.log ("Elements checked: "+ firstElementID + ","+nextElementID)
firstElementArchiCol = jArchi("#" + firstElementID);
nextElementArchiCol = jArchi("#" + nextElementID);
//test the relations
firstElementArchiColRelations = firstElementArchiCol.rels();
nextElementArchiColRelations = nextElementArchiCol.rels();
//console.log("firstElementArchiColRelations \n", firstElementArchiColRelations);
matchedRelation = false;
matchingRelationID = "";
matchingRelationDirection = "";
for (ii = 0; ii <firstElementArchiColRelations.length && matchedRelation == false ; ii++)
{
firstTestRelID = firstElementArchiColRelations[ii].id;
for (iii = 0; iii < nextElementArchiColRelations.length && matchedRelation == false ; iii++)
{
//console.log ("Start iii | "+ "i="+i+ " ii=" + ii+ " iii="+ iii);
//console.log( " nextElementArchiColRelations.length "+ nextElementArchiColRelations.length);
nextTestRelID = nextElementArchiColRelations[iii].id;
if (firstTestRelID == nextTestRelID)
{
//console.log ("in if...");
matchingRelationID = firstTestRelID;
matchedRelation = true;
//console.log("Match found on relation ID: " + matchingRelationID );
//now we have a match, we need the direction as well
matchingRelationDirection = getRelationDirection (firstElementID, matchingRelationID);
//console.log ("matchingRelationDirection: ",matchingRelationDirection);
}
}
}
//a match is found and we have the ID, now put in the right place
matchedRelationCol = jArchi("#" + matchingRelationID);
matchedRelationCol[0].id
sortedElementListForQuery [((i*2)+1)] = {
ID:matchedRelationCol[0].id
,Name:matchedRelationCol[0].name
,Type:matchedRelationCol[0].type
,Direction:matchingRelationDirection
};
}
//console.log("sortedElementListForQuery after relation sort:" ,sortedElementListForQuery);
return sortedElementListForQuery;
}
function getArchiTranslationObjects()
{
return [
{Archi:"access-relationship",HerveDB:"AccessRelationship",Sparx:"ArchiMate_Access"},
{Archi:"aggregation-relationship",HerveDB:"AggregationRelationship",Sparx:"ArchiMate_Aggregation"},
{Archi:"application-collaboration",HerveDB:"ApplicationCollaboration",Sparx:"ArchiMate_ApplicationCollaboration"},
{Archi:"application-component",HerveDB:"ApplicationComponent",Sparx:"ArchiMate_ApplicationComponent"},
{Archi:"application-event",HerveDB:"ApplicationEvent",Sparx:"ArchiMate_ApplicationEvent"},
{Archi:"application-function",HerveDB:"ApplicationFunction",Sparx:"ArchiMate_ApplicationFunction"},
{Archi:"application-interaction",HerveDB:"ApplicationInteraction",Sparx:"ArchiMate_ApplicationInteraction"},
{Archi:"application-interface",HerveDB:"ApplicationInterface",Sparx:"ArchiMate_ApplicationInterface"},
{Archi:"application-process",HerveDB:"ApplicationProcess",Sparx:"ArchiMate_ApplicationProcess"},
{Archi:"application-service",HerveDB:"ApplicationService",Sparx:"ArchiMate_ApplicationService"},
{Archi:"archimate-diagram-model",HerveDB:"na",Sparx:"na"},
{Archi:"artifact",HerveDB:"Artifact",Sparx:"ArchiMate_Artifact"},
{Archi:"assessment",HerveDB:"Assessment",Sparx:"ArchiMate_Assessment"},
{Archi:"assignment-relationship",HerveDB:"AssignmentRelationship",Sparx:"ArchiMate_Assignment"},
{Archi:"association-relationship",HerveDB:"AssociationRelationship",Sparx:"ArchiMate_Association"},
{Archi:"business-actor",HerveDB:"BusinessActor",Sparx:"ArchiMate_BusinessActor"},
{Archi:"business-collaboration",HerveDB:"BusinessCollaboration",Sparx:"ArchiMate_BusinessCollaboration"},
{Archi:"business-event",HerveDB:"BusinessEvent",Sparx:"ArchiMate_BusinessEvent"},
{Archi:"business-function",HerveDB:"BusinessFunction",Sparx:"ArchiMate_BusinessFunction"},
{Archi:"business-interaction",HerveDB:"BusinessInteraction",Sparx:"ArchiMate_BusinessInteraction"},
{Archi:"business-interface",HerveDB:"BusinessInterface",Sparx:"ArchiMate_BusinessInterface"},
{Archi:"business-object",HerveDB:"BusinessObject",Sparx:"ArchiMate_BusinessObject"},
{Archi:"business-process",HerveDB:"BusinessProcess",Sparx:"ArchiMate_BusinessProcess"},
{Archi:"business-role",HerveDB:"BusinessRole",Sparx:"ArchiMate_BusinessRole"},
{Archi:"business-service",HerveDB:"BusinessService",Sparx:"ArchiMate_BusinessService"},
{Archi:"canvas-model",HerveDB:"na",Sparx:"na"},
{Archi:"canvas-model-block",HerveDB:"na",Sparx:"na"},
{Archi:"canvas-model-image",HerveDB:"na",Sparx:"na"},
{Archi:"canvas-model-sticky",HerveDB:"na",Sparx:"na"},
{Archi:"capability",HerveDB:"Capability",Sparx:"ArchiMate_Capability"},
{Archi:"communication-network",HerveDB:"CommunicationNetwork",Sparx:"ArchiMate_CommunicationNetwork"},
{Archi:"composition-relationship",HerveDB:"CompositionRelationship",Sparx:"ArchiMate_Composition"},
{Archi:"constraint",HerveDB:"Constraint",Sparx:"ArchiMate_Constraint"},
{Archi:"contract",HerveDB:"Contract",Sparx:"ArchiMate_Contract"},
{Archi:"course-of-action",HerveDB:"CourseOfAction",Sparx:"ArchiMate_CourseOfAction"},
{Archi:"data-object",HerveDB:"DataObject",Sparx:"ArchiMate_DataObject"},
{Archi:"deliverable",HerveDB:"Deliverable",Sparx:"ArchiMate_Deliverable"},
{Archi:"device",HerveDB:"Device",Sparx:"ArchiMate_Device"},
{Archi:"diagram-model-connection",HerveDB:"",Sparx:"na"},
{Archi:"diagram-model-group",HerveDB:"",Sparx:"na"},
{Archi:"diagram-model-image",HerveDB:"",Sparx:"na"},
{Archi:"diagram-model-note",HerveDB:"",Sparx:"na"},
{Archi:"diagram-model-reference",HerveDB:"",Sparx:"na"},
{Archi:"distribution-network",HerveDB:"DistributionNetwork",Sparx:"TODO"},
{Archi:"driver",HerveDB:"Driver",Sparx:"ArchiMate_Driver"},
{Archi:"equipment",HerveDB:"Equipment",Sparx:"TODO"},
{Archi:"facility",HerveDB:"Facility",Sparx:"TODO"},
{Archi:"flow-relationship",HerveDB:"FlowRelationship",Sparx:"ArchiMate_Flow"},
{Archi:"gap",HerveDB:"Gap",Sparx:"ArchiMate_Gap"},
{Archi:"goal",HerveDB:"Goal",Sparx:"ArchiMate_Goal"},
{Archi:"grouping",HerveDB:"na",Sparx:"na"},
{Archi:"implementation-event",HerveDB:"ImplementationEvent",Sparx:"ArchiMate_ImplementationEvent"},
{Archi:"influence-relationship",HerveDB:"InfluenceRelationship",Sparx:"ArchiMate_Influence"},
{Archi:"junction",HerveDB:"na",Sparx:"na"},
{Archi:"location",HerveDB:"Location",Sparx:"ArchiMate_Location"},
{Archi:"material",HerveDB:"Material",Sparx:""},
{Archi:"meaning",HerveDB:"Meaning",Sparx:"ArchiMate_Meaning"},
{Archi:"node",HerveDB:"Node",Sparx:"ArchiMate_Node"},
{Archi:"outcome",HerveDB:"Outcome",Sparx:"ArchiMate_Outcome"},
{Archi:"path",HerveDB:"Path",Sparx:"ArchiMate_Path"},
{Archi:"plateau",HerveDB:"Plateau",Sparx:"ArchiMate_Plateau"},
{Archi:"principle",HerveDB:"Principle",Sparx:"ArchiMate_Principle"},
{Archi:"product",HerveDB:"Product",Sparx:"ArchiMate_Product"},
{Archi:"realization-relationship",HerveDB:"RealizationRelationship",Sparx:"ArchiMate_Realization"},
{Archi:"representation",HerveDB:"Representation",Sparx:"ArchiMate_Representation"},
{Archi:"requirement",HerveDB:"Requirement",Sparx:"ArchiMate_Requirement"},
{Archi:"resource",HerveDB:"Resource",Sparx:"ArchiMate_Resource"},
{Archi:"serving-relationship",HerveDB:"ServingRelationship",Sparx:"ArchiMate_Serving"},
{Archi:"sketch-model",HerveDB:"na",Sparx:"na"},
{Archi:"sketch-model-actor",HerveDB:"na",Sparx:"na"},
{Archi:"sketch-model-sticky",HerveDB:"na",Sparx:"na"},
{Archi:"specialization-relationship",HerveDB:"SpecializationRelationship",Sparx:"ArchiMate_Specialization"},
{Archi:"stakeholder",HerveDB:"Stakeholder",Sparx:"ArchiMate_Stakeholder"},
{Archi:"system-software",HerveDB:"SystemSoftware",Sparx:"ArchiMate_SystemSoftware"},
{Archi:"technology-collaboration",HerveDB:"TechnologyCollaboration",Sparx:"ArchiMate_TechnologyCollaboration"},
{Archi:"technology-event",HerveDB:"TechnologyEvent",Sparx:"ArchiMate_TechnologyEvent"},
{Archi:"technology-function",HerveDB:"TechnologyFunction",Sparx:"ArchiMate_TechnologyFunction"},
{Archi:"technology-interaction",HerveDB:"TechnologyInteraction",Sparx:"ArchiMate_TechnologyInteraction"},
{Archi:"technology-interface",HerveDB:"TechnologyInterface",Sparx:"ArchiMate_TechnologyInterface"},
{Archi:"technology-process",HerveDB:"TechnologyProcess",Sparx:"ArchiMate_TechnologyProcess"},
{Archi:"technology-service",HerveDB:"TechnologyService",Sparx:"ArchiMate_TechnologyService"},
{Archi:"triggering-relationship",HerveDB:"TriggeringRelationship",Sparx:"ArchiMate_Triggering"},
{Archi:"value",HerveDB:"Value",Sparx:"ArchiMate_Value"},
{Archi:"work-package",HerveDB:"WorkPackage",Sparx:"ArchiMate_WorkPackage"}
];
}
function archiTranslate (toTranslate_Parameter, language_parameter)
{
// this function translates notations from archi to HerveDB,
languageDictionary = getArchiTranslationObjects();
found =false;
r = null;
switch(language_parameter)
{
case "HerveDB":
for (ii=0;ii<languageDictionary.length && found==false;ii++)
{
//console.log ("language i: " + i + "languageDictionary[i].Archi" + languageDictionary[i].Archi);
if(languageDictionary[ii].Archi == toTranslate_Parameter)
{
found = true;
r = languageDictionary[ii].HerveDB;
//console.log("found: " + r);
}
}
break;
case "Sparx":
for (ii=0;ii<languageDictionary.length && found==false;ii++)
{
//console.log ("language i: " + i + "languageDictionary[i].Archi" + languageDictionary[i].Archi);
if(languageDictionary[ii].Archi == toTranslate_Parameter)
{
found = true;
r = languageDictionary[ii].Sparx;
//console.log("found: " + r);
}
}
break;
default:
r = null;
}
return r;
}
function archiQueryBuilder (sortedArray_Parameter, dbLanguage_Parameter)
{
//console.log ("Building the query....");
SQLQuery = "SQL query for Archimodel in "+ dbLanguage_Parameter + "\n\nSELECT "; //this string will contain the SQL query. It will be huge:)
SQLElementCount =(sortedArray_Parameter.length +1);
SQLLevelCount = (sortedArray_Parameter.length * 2 - 1);
//console.log (SQLElementCount);
//PART 1 the SELECT
//the first one uses the real table names from the vieuws
SQLQuery += "VW_Elements_Latest_Model.Element_Name as Element_Name_1 \n";
SQLQuery += "--,VW_Elements_Latest_Model.Element_ID as Element_ID_1 \n";
SQLQuery += ",VW_Elements_Latest_Model.Element_Class as Element_Class_1 \n\n";
//the subsequent ones uses the "result" names from the subqueries
for (i = 2 ;i < SQLElementCount; i+=2)
{
SQLQuery += ",SQ_" + i + ".Relation_Class as Relation_Class_" + i + "\n";
SQLQuery += "--,SQ_" + i + ".Target_ID as Element_Target_ID_" + i + "\n";
SQLQuery += "--,SQ_" + i + ".Relation_ID as Relation_ID_" + i + "\n";
SQLQuery += "--,SQ_" + i + ".Source_ID as Element_Source_ID_" + i + "\n\n";
SQLQuery += ",SQ_" + (i+1) + ".Element_Name as Element_Name_" + (i+1) + "\n";
SQLQuery += "--,SQ_" + (i+1) + ".Element_ID as Element_ID_" + (i+1) + "\n";
SQLQuery += ",SQ_" + (i+1) + ".Element_Class as Element_Class_" + (i+1) + "\n\n";
}
SQLQuery += "FROM VW_Elements_Latest_Model \n\n";
//part 2 the joins
//First set
i = 2
SQLQuery += "INNER JOIN(\n";
SQLQuery += "--Pre Loop: Looking for the next relation--\n"; // comment nog opkuisen
SQLQuery += "SELECT\n";
SQLQuery += "VW_Relations_Latest_Model.Relation_Class AS Relation_Class\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_Target_ID AS Target_ID\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_Source_ID AS Source_ID\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_ID AS Relation_ID\n";
rC = sortedArray[(i-1)].Type;
//relationClass = rC.replace('-','');
relationClass = archiTranslate(rC, dbLanguage_Parameter);
//console.log (rC + " " + relationClass);
SQLQuery += "FROM VW_Relations_Latest_Model\n";
SQLQuery += "WHERE VW_Relations_Latest_Model.Relation_Class LIKE '" + relationClass +"'\n"; /// -----'RealizationRelationship' --- is a var that needs to be added
direction = sortedArray[(i-1)].Direction;
SQLQuery += ") AS SQ_" + (i) +"\n";
//SQLQuery += "On VW_Elements_Latest_Model.Element_ID LIKE SQ_" + i + "." + direction + "_ID\n\n"; // Direction test (out = Source) only the first!!
SQLQuery += "ON VW_Elements_Latest_Model.Element_ID LIKE SQ_" + i + "." + direction + "_ID\n\n"; // Direction test (out = Source)
//------------
SQLQuery += "INNER JOIN (\n";
SQLQuery += "--Looking for the next element-- \n";
/*STEP 3 Find Application component Data*/
SQLQuery += "SELECT \n"
SQLQuery += "VW_Elements_Latest_Model.Element_Class AS Element_Class\n";
SQLQuery += ",VW_Elements_Latest_Model.Element_ID AS Element_ID\n";
SQLQuery += ",VW_Elements_Latest_Model.Element_Name AS Element_Name\n";
eC = sortedArray[(i)].Type;
//elementClass = eC.replace('-','');
elementClass = archiTranslate(eC, dbLanguage_Parameter);
SQLQuery += "FROM VW_Elements_Latest_Model\n";
SQLQuery += "WHERE VW_Elements_Latest_Model.Element_Class LIKE '" + elementClass +"'\n"; //'ApplicationComponent' s a var that needs to beadded
SQLQuery += ") AS SQ_" + (i+1) + "\n";
//SQLQuery += "ON SQ_" + (i) +".Source_ID LIKE SQ_" + (i+1) + ".Element_ID\n\n";
rDirection = reverseDirection(direction);
SQLQuery += "ON SQ_" + (i) +"." + rDirection +"_ID LIKE SQ_" + (i+1) + ".Element_ID\n\n"; //working here!!!
// subsequent sets
for (i = 4; i < SQLElementCount; i+=2 )
{
SQLQuery += "INNER JOIN(\n";
SQLQuery += "--Looping Looking for the next relation--\n"; //Comment nog opkuisen
SQLQuery += "SELECT\n";
SQLQuery += "VW_Relations_Latest_Model.Relation_Class AS Relation_Class\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_Target_ID AS Target_ID\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_Source_ID AS Source_ID\n";
SQLQuery += ",VW_Relations_Latest_Model.Relation_ID AS Relation_ID\n";
rC = sortedArray[(i-1)].Type;
//relationClass = rC.replace('-','');
relationClass = archiTranslate(rC,dbLanguage_Parameter);
//console.log (rC + " " + relationClass);
SQLQuery += "FROM VW_Relations_Latest_Model\n";
SQLQuery += "WHERE VW_Relations_Latest_Model.Relation_Class LIKE '" + relationClass +"'\n"; /// -----'RealizationRelationship' --- is a var that needs to be added
direction = sortedArray[(i-1)].Direction;
SQLQuery += ") AS SQ_" + (i) +"\n";
//SQLQuery += "On VW_Elements_Latest_Model.Element_ID LIKE SQ_" + i + "." + direction + "_ID\n\n"; // Direction test (out = Source) only the first!!
SQLQuery += "ON SQ_" + (i-1) + ".Element_ID LIKE SQ_" + i + "." + direction + "_ID\n\n"; // Direction test (out = Source)
//------------
SQLQuery += "INNER JOIN (\n";
SQLQuery += "--Looking for the next element-- \n";
/*STEP 3 Find Application component Data*/
SQLQuery += "SELECT \n"
SQLQuery += "VW_Elements_Latest_Model.Element_Class AS Element_Class\n";
SQLQuery += ",VW_Elements_Latest_Model.Element_ID AS Element_ID\n";
SQLQuery += ",VW_Elements_Latest_Model.Element_Name AS Element_Name\n";
eC = sortedArray[(i)].Type;
//elementClass = eC.replace('-','');
elementClass = archiTranslate(eC,dbLanguage_Parameter);
SQLQuery += "FROM VW_Elements_Latest_Model\n";
SQLQuery += "WHERE VW_Elements_Latest_Model.Element_Class LIKE '" + elementClass +"'\n"; //'ApplicationComponent' s a var that needs to beadded
SQLQuery += ") AS SQ_" + (i+1) + "\n";
rDirection = reverseDirection(direction);
SQLQuery += "ON SQ_" + (i) +"." + rDirection +"_ID LIKE SQ_" + (i+1) + ".Element_ID\n\n"; //working here!!!
}
//part 3 the where that filters out the start element
eC = sortedArray[0].Type;
//elementClass = eC.replace('-','');
elementClass = archiTranslate(eC,dbLanguage_Parameter);
SQLQuery += "where VW_Elements_Latest_Model.Element_Class LIKE '" + elementClass + "' \n";
SQLQuery += "Order by VW_Elements_Latest_Model.Element_Name"
return SQLQuery;
}
function drawResultNotes (viewName_Parameter, archiSQL_Parameter)
{
var arrchiNote = viewName_Parameter.createObject("note", 10, 200, 400, 600);
arrchiNote.setText("--SQL Query for Archi \n\n" + archiSQL_Parameter + "\n --EOF");
return null;
}
/*Other Functions--------------------------------------------------------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------------------------------------------------------------------
******************************* MAIN *************************************************************************************************************
--------------------------------------------------------------------------------------------------------------------------------------------------*/
showScriptStartHeader()
/*------------------------------------------------------------------------------------------------------------------------------------------------------*/
// what model are we looking at on screen, this is teh model we be working on
var currentView = selection.filter("archimate-diagram-model").first();
var SQLViewName = currentView.name;
SQLView = jArchi("." + SQLViewName); //a collection of elements on the view
// what is in that view
SQLViewCollectionObj = getArchiElementsFromView (SQLView); // an object with elements
// create an array that has everything in the right sequence, from start to stop
sortedArray = getSortedElementListForQuery (SQLViewCollectionObj);
//Build the query in the right language
archiQuery = archiQueryBuilder(sortedArray, dbLanguage);
//Write query in the documentation
currentView.documentation = archiQuery;
/*------------------------------------------------------------------------------------------------------------------------------------------------------*/
showScriptEndHeader()
/*------------------------------------------------------------------------------------------------------------------------------------------------------*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment