-
-
Save ovace/62ad266b3ec4d59d8b0ae8965a8195de to your computer and use it in GitHub Desktop.
#jarchi #ArchiSQLGenerator
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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