#jArchi scripts to sync (some) model concepts from one or more CSV files

country-codes.csv

Sync Model from CSV.ajs

// Sync Model from CSV.ajs
//
// Requires jArchi - https://www.archimatetool.com/blog/2018/07/02/jarchi/
//
// This script allows easy integration of data coming from other systems through CSV files.
// Each CSV file is described through a configuration object that you can later load and sync.
// Most of the mapping informations (Id, Name, Documentation, Properties...) can be expressed through
// the name of a column from the CSV file or through a function (which allows more advanced computation).
//
// Dataset used as example comes from https://datahub.io/core/country-codes
//
// v0.5 - jb.sarrodie@accenture.com
//        Cast property's value to String to avoid ambiguity between prop(String, String) and prop(String, boolean)
//        propMapping's values can now be functions
//        Now prints a message at the end of the synchronisation
//        A target folder can be defined on data sources (if defined, elements will be moved to this folder)
//        Tag elements and relationships that used to be synced but are no more in source files
//        Check rowid for null (don't index)
// v0.4 - jb.sarrodie@accenture.com
//        'id' can now be a function and is result is stored
//        as a '_id' property of each 'row'
//        Now imports all datasources
// v0.3 - jb.sarrodie@accenture.com
//        Configuration is now done using JS object syntaxe
//        (easier to read/write for a Human)
// v0.2 - jb.sarrodie@accenture.com
//        Is now fully configuration (includes relationships) and 
//        updates an existing model instead of creating a new one
// v0.1 - jb.sarrodie@accenture.com
//        First version which is partly configurable and successfully
//        loads (some) CSV into an empty ArchiMate model
//
// Copyright (c) 2020  JB Sarrodie & La Poste (www.laposte.fr)
//
// Permission is hereby granted, free of charge, to any person
// obtaining a copy of this software and associated documentation
// files (the "Software"), to deal in the Software without
// restriction, including without limitation the rights to use,
// copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the
// Software is furnished to do so, subject to the following
// conditions:
//
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
// OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
// HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
// WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
// FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
// OTHER DEALINGS IN THE SOFTWARE.

// Configuration starts here ======================================================================================

// Each source of information (data source) has its own object which contains:
// - The label describing the datasource (label)
// - The path of the CSV file (csv)
// - The ArchiMate concept type used when importing (targetType)
// - The function which extracts "logical" id from an ArchiMate element, or null if
//   element should be skipped (getId)
// - The function which stores external id into an ArchiMate element (setId)
// - The column name (or function) used to get (or compute) a unique id (id)
// - The column name (or function) used to get (or compute) element's name (name)
// - The column name (or function) used to get (or compute) element's documentation (documentation)
// - A mapping of external attributes to elements' properties (propMapping)
// - A description of external relations to map to ArchiMate relationships (relations)

/* Template
var xxx = {
  'label': '',
  'csv': __DIR__+'xxx.csv',
  'targetType': '',
  'targetFolder': getFolder('layer', 'folderName'),
  'getId': function(element) {
    // Do something to extract id
  },
  'setId': function(element, row) {
    // Do something to store id
  },
  'id': '', // Can also be a function
  'name': '', // Can also be a function
  'documentation': '', // Can also be a function
  'propMapping': {
    'Property_name': 'Column_name',
    'Property_name': 'Column_name'
  },
  'relations': {
    'Relation_Label': {               // can be changed, will be used in logs
      'column': '',                   // column name
      'reference': dataSourceObject,  // reference of the referenced data dource object
      'targetType': '',               // ArchiMate relationship type
                                      //  see https://github.com/archimatetool/archi-scripting-plugin/wiki/jArchi-Collection#object-selector-type
      'accessType': '',               // sets access type if targetType='access-relationship'
                                      //  see https://github.com/archimatetool/archi-scripting-plugin/wiki/jArchi-Object#accesstype
      'isReversed': true|false        // sets direction of the relationship
    },
    // You can add other relations if needed
  }
};
*/

var regions = {
  'label': 'Regions',
  'csv': __DIR__+'country-codes.csv',
  'targetType': 'location',
  'targetFolder': getFolder('Other', 'Regions'),
  'getId': function(element) {
    return element.prop('Region Code')
  },
  'setId': function(element, row) {
    element.prop('Region Code', row['_id'])
  },
  'id': 'Region Code',
  'name': 'Region Name',
  'documentation': '',
  'propMapping': {},
  'relations': {}
};

var subregions = {
  'label': 'Sub-regions',
  'csv': __DIR__+'country-codes.csv',
  'targetType': 'location',
  'targetFolder': getFolder('Other', 'Sub-regions'),
  'getId': function(element) {
    return element.prop('Sub-region Code')
  },
  'setId': function(element, row) {
    element.prop('Sub-region Code', row['_id'])
  },
  'id': 'Sub-region Code',
  'name': 'Sub-region Name',
  'documentation': '',
  'propMapping': {},
  'relations': {
    'Region': {
      'column': 'Region Code',
      'reference': regions,
      'targetType': 'composition-relationship',
      'isReversed': true
    }
  }
};

var countries = {
  'label': 'Countries',
  'csv': __DIR__+'country-codes.csv',
  'targetType': 'location',
  'targetFolder': getFolder('Other', 'Countries'),
  'getId': function(element) {
    return element.prop('Country Code')
  },
  'setId': function(element, row) {
    element.prop('Country Code', row['_id'])
  },
  'id': 'ISO3166-1-Alpha-3',
  'name': 'official_name_en',
  'documentation': 'UNTERM English Formal',
  'propMapping': {
    'Capital': 'Capital',
    'Currency': function(row) {
      var curcode = '';
      if (row['ISO4217-currency_alphabetic_code']) {
        curcode = ' (' + row['ISO4217-currency_alphabetic_code'] + ')';
      }
      return row['ISO4217-currency_name'] + curcode;
    }
  },
  'relations': {
    'Region': {
      'column': 'Sub-region Code',
      'reference': subregions,
      'targetType': 'composition-relationship',
      'isReversed': true
    }
  }
};

// End of configuration =========================================================================================





// Don't change this unless you really know what you are doing ==================================================
var syncPropName = 'Latest Sync Date';
var deletedPropName = 'Deleted from CSV Datasource';
// Compute the date which will appear in every new or updated concepts - Has to be 
var currentDateTime = new Date();
currentDateTime = lpad(currentDateTime.getDate()) +'/'+ lpad(currentDateTime.getMonth()+1) +'/'+ currentDateTime.getFullYear() +' '+ lpad(currentDateTime.getHours()) +':'+ lpad(currentDateTime.getMinutes()) +':'+ lpad(currentDateTime.getSeconds());




// Things start here ============================================================================================
// Make sure console is opened and dependancies are loaded
console.show();
console.clear();
// In production I recommend dowloading papaparse.min.js and load it locally
//load(__DIR__+'papaparse.min.js');
load('https://unpkg.com/papaparse@latest/papaparse.min.js');

console.log("Start of synchronization. If script doesn't ends with an 'End of synchronization' message, then check Archi error log in 'Help > About Archi > Installation Details > Configuration > View Error Log'");
var startTime = new Date().getTime();

// Load data from CSV Files and sync model elements
loadAndSync(regions);

loadAndSync(subregions);
syncModelRelationships(subregions);

loadAndSync(countries);
syncModelRelationships(countries);

// Uncomment next line to enable tagging of concepts previously imported but no more present in current CSV files
//tagDeletedConcepts();

var endTime = new Date().getTime();
console.log("Elapse time: ", (endTime - startTime)/1000, " sec");
console.log("End of synchronization. Check previous messages for errors and undo in case of doubt.");









// Functions ====================================================================================================
function loadAndSync(dataSource) {
  loadData(dataSource);
  buildModelIndex(dataSource);
  syncModelElements(dataSource);
}


function lpad(text) {
  return ('0'+text).substr(-2);
}


function loadData(dataSource) {
  if (dataSource._loaded) {
    console.log('WARNING - Datasource "', dataSource.label, '" has already been loaded');
    return
  }
  
  console.log('INFO - Loading "', dataSource.label, '" from CSV...');
  
  var rows = Papa.parse(readFully(dataSource.csv, 'utf-8'), { header: true, encoding: 'utf-8', skipEmptyLines: true }).data;
  dataSource.rows = {};
  
  for (var i=0; i<rows.length; i++) {
    var id = (typeof dataSource.id === 'function')? dataSource.id(rows[i]) : rows[i][dataSource.id];
    if(id) {
      dataSource.rows[id] = rows[i];
      dataSource.rows[id]['_id'] = id;
    } else {
      console.log('ERROR - Row #', i, ' of ', dataSource.label, ' doesn\'t have a valid id');
    }
  }
  
  dataSource._loaded = true;
}


function buildModelIndex(dataSource) {
  if (dataSource._modelIndexed) {
    console.log('WARNING - Model elements associated with "', dataSource.label, '" have already been indexed');
    return
  }
  
  console.log('INFO - Indexing "', dataSource.label, '" from model...');
  
  dataSource.model = {};
  
  $(dataSource.targetType).each(function(element) {
    var id = dataSource.getId(element);
    if (id) {
      dataSource.model[id] = element;
    }
  });
  
  dataSource._modelIndexed = true;
}


function syncModelElements(dataSource) {
  if (dataSource._modelElementsSynced) {
    console.log('WARNING - Datasource "', dataSource.label, '" has already been synced');
    return
  }
  
  console.log('INFO - Syncing "', dataSource.label, '" elements from CSV with model...');
  var created = 0;
  var updated = 0;
  
  for (var id in dataSource.rows) {
    var row = dataSource.rows[id];
    var element = dataSource.model[id];
    var name = (typeof dataSource.name === 'function')? dataSource.name(row) : row[dataSource.name];
    var documentation = (typeof dataSource.documentation === 'function')? dataSource.documentation(row) : row[dataSource.documentation];
    
    if (element) {
      updated++;
    } else {
      element = model.createElement(dataSource.targetType, name);
      dataSource.setId(element, row);
      dataSource.model[id] = element;
      created++;
    }
    
    // Brute force update
    if(dataSource.targetFolder) {
      dataSource.targetFolder.add(element);
    }
    element.prop(syncPropName, currentDateTime);
    element.name = name;
    element.documentation = documentation;
    for (var propName in dataSource.propMapping) {
      var propValue = (typeof dataSource.propMapping[propName] === 'function')? dataSource.propMapping[propName](row) : row[dataSource.propMapping[propName]];
      // Cast propValue to String to avoid ambiguity between prop(String, String) and prop(String, boolean) in rare occasions
      element.prop(propName, String(propValue));
    }
  }
  
  dataSource._modelElementsSynced = true;
  console.log('INFO - Datasource "', dataSource.label, '" has been synced: ', created, ' element(s) created and ', updated, ' updated');
}


function syncModelRelationships(dataSource) {
  if (dataSource._modelRelationshipsSynced) {
    console.log('WARNING - Model relationships associated with "', dataSource.label, '" have already been synced');
    return
  }
  
  console.log('INFO - Syncing "', dataSource.label, '" relationships from CSV with model...');
  var createdOrUpdated = 0;
  
  for (var id in dataSource.rows) {
    var row = dataSource.rows[id];
    var element = dataSource.model[id];
    
    if (!element) {
      console.log('WARNING - No element found for "', dataSource.label, '" with id=', id, ' (maybe model elements haven\'t been synced before?)');
    } else {
      for (var relationName in dataSource.relations) {
        var relation = dataSource.relations[relationName];
        var otherEndsIds = row[relation.column];
        if (otherEndsIds) {
          otherEndsIds = otherEndsIds.split('\n');
        
          for (var i=0; i<otherEndsIds.length; i++) {
            var otherEnd = relation.reference.model[otherEndsIds[i]];
            if (!otherEnd) {
              console.log('WARNING - Element "', dataSource.label, '" with id=', id, ' references another element ', relation.reference.label, ' with id=', otherEndsIds[i], ' which doesn\'t exist (maybe model elements haven\'t been synced before?)');
            } else {
              if (relation.isReversed) {
                createOrUpdateRelationship(relation, otherEnd, relation.targetType, element);
              } else {
                createOrUpdateRelationship(relation, element, relation.targetType, otherEnd);
              }
              createdOrUpdated++;
            }
          }
        }
      }
    }
  }
  
  dataSource._modelRelationshipsSynced = true;
  console.log('INFO - ', createdOrUpdated, ' relationship(s) associated with datasource "', dataSource.label, '" have been created or updated');

}

function tagDeletedConcepts() {
  console.log('INFO - Looking for deleted elements or relationships and tagging them as deleted...');
  var deleted = 0;
  
  $('concept').filter(function(c){
    lastUpdateDate = c.prop(syncPropName);
    if(lastUpdateDate) {
      return lastUpdateDate != currentDateTime;
    } else {
      return false;
    }
  }).each(function(c){
    if(!c.prop(deletedPropName)) {
      c.name = '[DELETED] ' + c.name;
      c.prop(deletedPropName, currentDateTime);
      deleted++;
    }
  });
  
  console.log('INFO - ', deleted, ' elements or relationships have been tagged as deleted');
}


function createOrUpdateRelationship(config, source, type, target) {
  var relationship = $(source).outRels(type).filter(function(r) {return r.target.equals(target)}).filter(function(r) {return r.prop(syncPropName)}).first();
  
  if (!relationship) {
    relationship = model.createRelationship(type, '', source, target);
  }
  
  relationship.prop(syncPropName, currentDateTime);
  
  if (type == 'access-relationship') {
    relationship.accessType = config.accessType;
  }
  
  if (type == 'association-relationship') {
    relationship.associationDirected = config.associationDirected;
  }
}


function getFolder(layer, folderName) {
  layerFolder = $(model).children().filter('folder.'+layer).first();
  folder = $(layerFolder).children().filter('folder.'+folderName).first();
  
  if(!folder) {
    folder = layerFolder.createFolder(folderName);
  }
  
  return folder;
}


// Some Polyfills for Nashorn ====================================================================================
function readFully(url, charset) {
    var result = '';
    var imports = new JavaImporter(java.net, java.lang, java.io);

    with (imports) {

        var urlObj = null;

        try {
            urlObj = new URL(url);
        } catch (e) {
            // If the URL cannot be built, assume it is a file path.
            urlObj = new URL(new File(url).toURI().toURL());
        }

        var reader = new BufferedReader(new InputStreamReader(urlObj.openStream(), charset));

        var line = reader.readLine();
        while (line != null) {
            result += line + '\n';
            line = reader.readLine();
        }

        reader.close();
    }

    return result;
}

This script allows easy integration of data coming from other systems through CSV files.

It has been created for the architecture team of La Poste and they kindly accepted to share it with everyone under the MIT license:

Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following
conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.

Each CSV file is described through a configuration object that you can later load and sync. Most of the mapping informations (Id, Name, Documentation, Properties...) can be expressed through the name of a column from the CSV file or through a function (which allows more advanced computation).

Each source of information (data source) has its own object which contains:

• The label describing the datasource (label)
• The path of the CSV file (csv)
• The ArchiMate concept type used when importing (targetType)
• The function which extracts "logical" id from an ArchiMate element, or null if element should be skipped (getId)
• The function which stores external id into an ArchiMate element (setId)
• The column name (or function) used to get (or compute) a unique id (id)
• The column name (or function) used to get (or compute) element's name (name)
• The column name (or function) used to get (or compute) element's documentation (documentation)
• A mapping of external attributes to elements' properties (propMapping)
• A description of external relations to map to ArchiMate relationships (relations)

Template:

var xxx = {
  'label': '',
  'csv': __DIR__+'xxx.csv',
  'targetType': '',
  'targetFolder': getFolder('layer', 'folderName'),
  'getId': function(element) {
    // Do something to extract id
  },
  'setId': function(element, row) {
    // Do something to store id
  },
  'id': '', // Can also be a function
  'name': '', // Can also be a function
  'documentation': '', // Can also be a function
  'propMapping': {
    'Property_name': 'Column_name',
    'Property_name': 'Column_name'
  },
  'relations': {
    'Relation_Label': {               // can be changed, will be used in logs
      'column': '',                   // column name
      'reference': dataSourceObject,  // reference of the referenced data dource object
      'targetType': '',               // ArchiMate relationship type
                                      //  see https://github.com/archimatetool/archi-scripting-plugin/wiki/jArchi-Collection#object-selector-type
      'accessType': '',               // sets access type if targetType='access-relationship'
                                      //  see https://github.com/archimatetool/archi-scripting-plugin/wiki/jArchi-Object#accesstype
      'isReversed': true|false        // sets direction of the relationship
    },
    // You can add other relations if needed
  }
};

Once configured, you can load and sync elements with a simple call:

loadAndSync(datasource);
loadAndSync(anotherDatasource);

And when elements have been synced, you can sync relationships with a simple call:

syncModelRelationships(datasource);

The provided script is configured to load Regions, Sub-regions and Countries from the country-codes dataset. Simply download both script and dataset from this gist and try it on a new or existing model. This should provide enough information for you to start customizing it.

Hi, @jbsarrodie. I'm trying to change relationship to the other in the 176 string of your code ('targetType': 'composition-relationship',), but nothing exept serving, triggering, flow & association relationships doesn't work. What am I doing wrong? I use relationships from here. Can You help me?