Print this page
Tuesday, 13 July 2021 11:51

EAPostgresImportfromNativeXML: Enterprise Architect Native XML import to PostgreSQL solution

Written by
Rate this item
(0 votes)

EAPostgresImportfromNativeXML Enterprise Architect Native XML import to PostgreSQL solution

 

About Enterprise Architect Native XML Project Transfer

Native XML Project Transfer is a feature introduced in Enterprise Architect 15.1. Until then a full EA project could be transferred via EA Project Transfer between databases (from and to a local EAP file and/or a DBMS).

Enterprise Architect Native XML involves a two-step process:

  1. Export the source EA Project: generated in the selected target folder, XML files match each EA DB table (e.g. t_attribute, t_object) and contain data in the XML format.
  2. Import the XML files and overwrite the target EA Project.

Important: Native XML is different from the XMI import/export since it supports the entire project data, whereas the XMI only exports the model information (e.g. packages, elements, and diagrams) to be exchanged between EA projects or with other modelling tools.

Native XML advantages:

  • Compared with EA Project Transfer, a connection to the source and/or target database is no longer needed.
    • This is convenient to load a project on a Cloud-hosted EA project (e.g. in AWS or Ms Azure) by transferring files and run an import.
  • Available on an http/https “Cloud Connection” via the Pro Cloud Server.
  • Provides a convenient full backup function.
  • Unnecessary data can be easily removed by deleting the XML file(s) matching a table (e.g. t_document.xml).

Description

EAPostgresImportfromNativeXML is a specific third-party solution for Sparx Systems Enterprise Architect modelling tool.

Goals:

  • Import a Native XML into a PostgreSQL EA database from a command-line prompt or via a CMD batch file, instead of opening Enterprise Architect (note: the Native XML Import is not supported by EA API).
  • Deliver better performances with a reduced execution time, especially relevant for very large EA model repositories.

This solution can be useful for a publication process involving transferring the work model repository to a publication database, dedicated for end users via Prolaborate; a batch file can be configured in this case with Windows Task Scheduler to run the transfer on a nightly basis.

You can contact me via email (guillaume [at] umlchannel.com) to obtain a copy of EAPostgresImportfromNativeXML.

Notes:

  • Requires .NET Core 3.1 or greater.
    • If it is not installed yet, you need to download Microsoft NET Core 3.1 Runtime Windows x64 Installer (or greater) from the following link: dotnet.microsoft.com/download
  • This project uses Npgsql C# library (www.npgsql.org).
  • The target PostgreSQL database must have an existing EA Project i.e. the SQL scripts available from Sparx Systems must have been run and an initial project loaded.

Initial Setup Instructions

  • Extract the zip file e.g. in C:\EAProjectTransfer.

  • Open the source EA project and run the Native XML Export (Configure > Transfer menu) to a target folder.
  • Example with C:\EAProjectTransfer\nativeXML:

EAPostgresImportfromNativeXML/02 Sparx Enterprise Architect Native XML export

  • Result for Sparx Systems EA Example project:

EAPostgresImportfromNativeXML/03 Sparx Enterprise Architect Native XML export folder content

  • Create a text file with details of the target Postgres database.
    • An EA repository must be running on this Postgres database.
    • WARNING: ALL THE DATA WILL BE DELETED ON THE TARGET DATABASE BEFORE RUNNING THE TRANSFER.
    • The text file must contain the following details, separated by semi-colons:
      • Database server IP address or name;
      • Postgres Port number;
      • Database name;
      • Username;
      • Password;
    • Example
      • Create dbdetails.txt text file in C:\EAProjectTransfer folder with the following content: localhost;5432;test_db;postgres;admin

EAPostgresImportfromNativeXML/04 EAPostgresImportfromNativeXML db details

Running the Native XML to Postgres EA database full project transfer

  • Open the command line prompt (cmd).
  • Go to the active folder e.g. cd C:\EAProjectTransfer.
  • Run dotnet –version to check if .NET Core is installed.

  • Running dotnet EAPostgresImportfromNativeXML.dll will display the following instructions.

EAPostgresImportfromNativeXML/06 EAPostgresImportfromNativeXML instructions

  • EAPostgresImportfromNativeXML tool takes 2 or 3 parameters:
    • Parameter 1: full path to the text file that contains the database details (see previous instructions).
      • e.g. C:\EAProjectTransfer\dbdetails.txt.
    • Paramètre 2: path to the folder with the Native XML files generated by Enterprise Architect.
      • e.g. "C:\EAProjectTransfer\nativeXML"
    • Optional parameter 3: when the value skipwarning is provided, it skips the warning prompt before clearing data from the target Postgres EA database.
  • Example to run the import to a target database as per the dbdetails.txt file and using XML files from C:\EAProjectTransfer\nativeXML:
    • dotnet EAPostgresImportfromNativeXML.dll C:\EAProjectTransfer\dbdetails.txt C:\EAProjectTransfer\nativeXML
    • An executable file is also available:
      • EAPostgresImportfromNativeXML.exe C:\EAProjectTransfer\dbdetails.txt "C:\EAProjectTransfer\nativeXML"
    • Here is an illustration of the main steps executed by EAPostgresImportfromNativeXML.
      • Parameters and the db details file content are checked.
      • A connection with the Postgres database is established.
      • If the database is missing one or several EA tables such as t_object, an error message displays all the missing tables.

EAPostgresImportfromNativeXML/07 EAPostgresImportfromNativeXML error missing EA tables

  • When all the native XML files are processed, the number of resulting INSERT queries is shown (no query has yet been executed on the target DB).

EAPostgresImportfromNativeXML/08 EAPostgresImportfromNativeXML insert queries count

  • Press "Enter" to confirm DELETING ALL DATA from the target DB EA tables and load the data from the XML files.
    • Note this step is skipped when skipwarning parameter is provided.

EAPostgresImportfromNativeXML/09 EAPostgresImportfromNativeXML warning prompt

  • Data is deleted from each EA table (e.g. DELETE from t_object, etc.).
  • The INSERT queries are executed to populate the database.
  • Finally, the elapsed time in milliseconds is displayed and the EA Project can be opened using the target database.
    • Transferring the EA Example project to a local Postgres DB took 40 seconds (note: it includes the time to confirm the prompt message).

EAPostgresImportfromNativeXML/10 EAPostgresImportfromNativeXML completed

 Contact: guillaume [at] umlchannel.com.