Edit Columns of EtlTable programmatically?

HI every body
Is it possible to edit an Etl table in a business process diagram programmatically using open api ?
for example add a custom column to that etl table to show data of a array[] ?
thanks in advance

I think so, but at the time of writing I donā€™t have any hands on experience with this (yet). The reason I believe this to be possible is the existence of the IETLTableDiagramUIModel interface (see the link).

The other reason why I theorize in this direction is because of IDiagramTypeConstants, in specific: DIAGRAM_TYPE_ETL_TABLE_DIAGRAM.

Iā€™ll see if I can come up with a proof of concept later today :slight_smile:

1 Like

As promised above Iā€™ve done some testing and right now I can confirm that this should definitely be possible. Further studies have also pointed my attention to classes such as IETLTableDiagramColumn which definitely indicate that this should be possible.

The bad news though is that I havenā€™t yet found the best method to access the diagram. That isā€¦ to get the right element:

    @Override
    public void performAction(VPAction arg0) {
        ApplicationManager vpam = ApplicationManager.instance();
        DiagramManager vpdm = vpam.getDiagramManager();
        ViewManager vpvm = vpam.getViewManager();

        String dianame = null;
        IETLTableDiagramUIModel etldiagram = null;
        IDiagramUIModel diagram = vpdm.getActiveDiagram();

        // Discover ETL table
        for (IDiagramElement ide : diagram.toDiagramElementArray()) {
            if (ide.getShapeType().equals("ETLTable")) {
                vpvm.showMessage("Discovered ETL table.");
                dianame = ide.getModelElement().getName();

                if (ide instanceof IShapeUIModel) {
                    IETLTableUIModel itlm = (IETLTableUIModel) ide;
                    vpvm.showMessage("Is table collapsed: " + itlm.isCollapsed());
                }
            }
        }

        // Locate ETL diagram
        for (IDiagramUIModel idm : vpdm.getOpenedDiagrams()) {
            if (idm.getName().equals(dianame)) {
                etldiagram = (IETLTableDiagramUIModel) idm;
            }
        }
    }

Situation: A BPMN diagram which contains an ETL table. An ETL table manifests itself in 2 ways: as part of your current diagram, but also as a separate diagram (check the project browser to see this for yourself).

Here Iā€™m checking the current diagram to get the ETL table element and once I get the name Iā€™m then using that to find the actual diagram entry. So far, so good, but this still doesnā€™t give me access to what we need: the IETLTable
class.

Iā€™m pretty sure that itā€™s something small Iā€™m overlooking here, and I havenā€™t given up yet. As soon as I know more Iā€™ll post again.

But to answer your initial question: yes, this is definitely possible.

1 Like

Figured Iā€™d add an update. Iā€™m getting really close but Iā€™m not quite there yet.

The problem (what I think it to be) is that you need to gain access to the IETLTableDiagram class. This gives us access to methods such as createETLDiagramColumn().

But the best I can accomplish right now is the IETLTableDiagramUIModel:

    @Override
    public void performAction(VPAction arg0) {
        ApplicationManager vpam = ApplicationManager.instance();
        DiagramManager vpdm = vpam.getDiagramManager();
        ViewManager vpvm = vpam.getViewManager();

        IDiagramUIModel diagram = vpdm.getActiveDiagram();
        
        IETLTable ietable = null;
        IETLTableDiagram iediagram = null;
        IETLTableDiagramContainer iecontainer = null;
        IETLTableDiagramColumn iecolumn = null;
        IETLTableDiagramUIModel iemodel = null;

        // Discover ETL table
        for (IDiagramElement ide : diagram.toDiagramElementArray()) {
            if (ide.getShapeType().equals("ETLTable")) {
                vpvm.showMessage("Discovered ETL table.");

                ietable = (IETLTable) ide.getModelElement();
                vpvm.showMessage("Diagram ID: " + ietable.getDiagramId());
                vpvm.showMessage("Diagram name: " + ietable.getName());
                vpvm.showMessage("Column amount: " + ietable.excludedColumnCount());
                vpvm.showMessage("Item model count: " + ietable.analysisItemModelCount());
                vpvm.showMessage("Sub diagram count: " + ietable.subDiagramCount());

                try {
                    if (ietable.subDiagramCount() > 0) {
                        IDiagramUIModel dia = ietable.getSubDiagramAt(0);
                        iemodel = (IETLTableDiagramUIModel) dia;
                        vpvm.showMessage("Sub dia name: " + iemodel.getName());
                        vpvm.showMessage("Sub dia ID: " + iemodel.getId());
                        vpvm.showMessage("Diagram address: " + iemodel.getEtlTableDiagramAddress());

                        vpvm.showMessage("Listing elements:");
                        for (IDiagramElement ide2 : iemodel.toDiagramElementArray()) {
                            vpvm.showMessage(ide2.getModelElement().getName());
                        }

                        vpvm.showMessage("Opening diagram:");
                        vpdm.openDiagram(iemodel);
                    }
                } catch (Exception e) {
                    vpvm.showMessage("Caught sub dia exception: " + e.getMessage());
                }

                if (ide instanceof IShapeUIModel) {
                    IETLTableUIModel itlm = (IETLTableUIModel) ide;
                    vpvm.showMessage("Is table collapsed: " + itlm.isCollapsed());
                }
            }
        }
    }

The somewhat frustrating part is that IETLTableDiagram extends IModelElement. Which is also a super interface for IETLTable.

However, while writing this (which is why I share these updates: they also help me to re-evaluate the whole thing while trying to explain the things I did)ā€¦ So while writing I couldnā€™t help notice that IETable extends IHasChildrenBaseModelElement. That class has some interesting methods: setTransitFrom() and setTransitTo(). Both are deprecated by ITransitProperty which provides methods which allow us to create an array of IModelElements (getValueAsModelArray()).

Right now I believe this to be the key.

You can see that Iā€™m doing something right here because if you apply my plugin with the scenario as you described (BPMN diagram with an ETLTable) then it will successfully identify the ETL diagram but more so: in this last example the ETL diagram also get successfully opened. Making me believe that Iā€™m definitely on the right track.

Iā€™m calling it quits for today but Iā€™ll keep you posted. Hoping for a breakthrough tomorrow.

1 Like

well ,
but IETLTableDiagramUIModel class has no method for editing or even accessing columns.

Methods for accessing and updating Columns of an etl Table are in IETLTableDiagram class.methods like toColumnArray() , addColumn(), and ā€¦

when i try to cast dia object as a IETLTableDiagram object, i encounter to an exception because you save the sub diagram inside etlTable as a IDiagramUIModel and there is no way to cast sub diagram of a an IETlTable as an IETLTableDiagram object.

i think we must find a way to save iemodel as a IETLTableDiagram object to can access columns and edit them.

if you find a solution for this problem please share it with me.

Thank you

Hi, Mamad,

We just enhanced OpenAPI to support edit columns of a ETL Table.
Please update to latest patch to try the enhancement.
FYI, How to update to patch.

The following sample show you how to:

  1. add a Property column, and a Custom-Property column.

  2. sort by Name column.

  3. finally, close & reopen the ETL Table diagram to show the changes.

     	IETLTableDiagramUIModel lEtlDiagram = ...; // the ETL Table Diagram.
     	IETLTableDiagram lEtlModel = lEtlDiagram.getEtlTableDiagram();
     	{
     		// add a property column: taskType
     		IETLTableDiagramColumn lColumn = lEtlModel.createETLTableDiagramColumn();
     		lEtlModel.addColumn(lColumn);
     		
     		lColumn.setColumnType(IETLTableDiagramColumn.COLUMN_TYPE_PROPERTY);
     		lColumn.setColumnValue(IBPTask.PROP_TASK_TYPE); // property-name
     	}
     	{
     		// add a custom property: startDate
     		IETLTableDiagramColumn lColumn = lEtlModel.createETLTableDiagramColumn();
     		lEtlModel.addColumn(lColumn);
     		
     		lColumn.setColumnType(IETLTableDiagramColumn.COLUMN_TYPE_TAGGED_VALUE); // custom property
     		lColumn.setColumnValue("startDate"); // property-name
     		lColumn.setName("Start Date"); // caption
     		lColumn.setColumnWidth(200); // in pixel
     		
     		// data type of the custom property: Date 
     		lColumn.setTaggedValueType(ITaggedValue.TYPE_DATE);
     	}
     	
     	{
     		// sort by name
     		IETLTableDiagramColumn lNameColumn = null;
     		for (IETLTableDiagramColumn lColumn : lEtlModel.toColumnArray()) {
     			if (
     					lColumn.getColumnType() == IETLTableDiagramColumn.COLUMN_TYPE_PROPERTY && 
     					IModelElement.PROP_NAME.equals(lColumn.getColumnValue())
     			) {
     				lNameColumn = lColumn;
     				break;
     			}
     		}
     		
     		if (lNameColumn != null) {
     			ISortColumnInfo lSortColumn = lEtlModel.createSortColumnInfo();
     			lEtlModel.addSortColumnInfoInProperty(lSortColumn);
     			
     			lSortColumn.setColumn(lNameColumn);
     			lSortColumn.setAscending(true);
     		}
     	}
     	
     	// reopen the ETL diagram to show the new columns.
     	DiagramManager lDiagramManager = ApplicationManager.instance().getDiagramManager();
     	lEtlDiagram.realCloseDiagram();
     	lDiagramManager.openDiagram(lEtlDiagram);
    

and I prepared a simple class diagram to show you a part of the model structure.


PS:
There are 2 relationships between the ETLTableDiagram & its Column (Parent/Child, and ā€˜columnsā€™ Association)
So, in plugin:
i. the createETLTableDiagramColumn() function create the column in Parent/Child.
ii. the addColumn(...) function add the column in columns association.

2 Likes

Hi peter,
currently im using Version 15.0 so i have this feature in my vp .

I tried to test you code but in the second line i faced with an error

Method with name: getEtlTableDiagram() doesnt exist !!!

but another method with name: getEtlTableDiagramAddress() exist !

why ? does this problem relate with the version of openapi library that i have added in my project build path ?

No we donā€™t, trust me on this. Before I started hacking into the API I updated my version to the latest update and trust me when I say that a method called getEtlTableDiagram() doesnā€™t exist, just as you noticed yourself:

Thatā€™s because you need to update to the latest patch. As I mentioned above I fully based my conclusion about this to be possible on the existence of the ā€œColumn classā€ but as you noticed I wasnā€™t able to gain access to the required method. Well, now we know why: because it wasnā€™t possible at that time.

Itā€™s the only small caveat with Visual Paradigmā€™s OpenAPI: sometimes it looks as if things should be possible but theyā€™re not. But thatā€™s when the team steps in as shown above.

So just follow @peter.wong 's example and upgrade your version to the latest patch, then itā€™ll work.

Wellā€¦ I can confirm that this fully works! :sunglasses:

First Iā€™ll share my code:

    @Override
    public void performAction(VPAction arg0) {
        ApplicationManager vpam = ApplicationManager.instance();
        DiagramManager vpdm = vpam.getDiagramManager();
        ViewManager vpvm = vpam.getViewManager();

        IDiagramUIModel diagram = vpdm.getActiveDiagram();
        IETLTableDiagramUIModel etlmodel = null;

        IETLTableDiagram etldiagram = null;
        IETLTable etltable = null;
        IETLTableDiagramColumn etlcol = null;

        for (IDiagramElement ide : diagram.toDiagramElementArray()) {
            // Get ETL table
            if (ide.getShapeType().equals("ETLTable")) {
                vpvm.showMessage("Discovered ETL table.");

                try {
                    vpvm.showMessage("Processing table element.");
                    etltable = (IETLTable) ide.getModelElement();

                    if (etltable.subDiagramCount() > 0) {
                        etlmodel = (IETLTableDiagramUIModel) etltable.getSubDiagramAt(0);
                    }

                    // Get related diagram
                    vpvm.showMessage("Get related ETL table diagram");
                    etldiagram = etlmodel.getEtlTableDiagram();

                    // Create & add new column
                    vpvm.showMessage("Creating new column");
                    etlcol = etldiagram.createETLTableDiagramColumn();
                    vpvm.showMessage("Adding new column");
                    etldiagram.addColumn(etlcol);

                    etlcol.setColumnType(IETLTableDiagramColumn.COLUMN_TYPE_PROPERTY);
                    etlcol.setColumnValue("Auto added column");
                } catch (Exception e) {
                    vpvm.showMessage("Caught ETL exception: " + e.getMessage());
                }
            }
        }
        etlmodel.realCloseDiagram();
        vpdm.openDiagram(etlmodel);
    }

And this is the result:

I figured Iā€™d share because Peterā€™s code didnā€™t show how to get the actual class. But then again, my previous message showed that already.

But yeah, this works!

1 Like

Hi, Mamad,

Sorry, your current build in 15.0 didnā€™t contain our fixes for this post.

Our engineer did the enhancement on yesterday (after you found & tell us this problem, thanks).
After build: 20180330af, the OpenAPI is able to handle ETL Table.

So, now, you can update to latest patch. You will find the library bundled/openapi.jar is updated, and com.vp.plugin.diagram.IETLTableDiagramUIModel supported
public IETLTableDiagram getEtlTableDiagram() function

If you are not sure how to update to latest patch, you may reference to following know-how page:

and, thanks ShelLuser.

2 Likes

Hi peter.wong and ShelLuser ,
thank you so much for your time and contribution.
Unfortunately iā€™m on vacation and cant test your codes right now but i will update vp and try your codes in the next week .
i will share the result of my tests here.
thank you guys

Hello again,

Ok guys, your codes are fine for adding a custom column to a ETL Table.

But the second part of my general question in this topic still remains unanswered.

How to change a column to show the values of an array[] programmatically ?

i.e i have an Etl Table and a column that i made programmatically .how to show data of an String array[] in this column at Etl Table ?

Is it possible at all ?

Sorry, String[] is not supported.

Custom Property is a TaggedValue,
TaggedValue supports the following data types:
String(singleline/multiline), int, float, boolean, date, time, elementRef(reference to an element).
but String[] is not supported.

about String, the difference between single/multi-line is:

  • singleline will be edited in a single-line text field.
  • multline will be edited in a multiline text box dialog.

Actually I do have an idea. An enhanced for ā€¦ next loop should solve that. Iā€™ll set up a test case tomorrow, at most upcoming weekend, and Iā€™ll get back to you.