ETL transformation between Tables

Hi,

I try to implement ETL tool and one part should be detail transformation between source tables and destiny table.
Design should contains 3 columns, where:

  • first has all source tables with fields,
  • second has all operations, direct links and constants,
  • last has one target table.

In my current solution, I use TableLayout where I put 3 groups with set width and height and GridLayout.
Problem that I’m facing is related to center elements in second column and move it to correct place.
You can see screen and code bellow.

Do you have any suggestion, how should I change my implementation?
Thx,
Artur

public genDiagram() {
    if (this.diagram) {
      this.diagram.div = null;
    }

    const me = this;
    const $ = GraphObject.make;

    this.diagram = $(go.Diagram, "TransformationDesignArea", {
      isEnabled: !me.isReadOnly(),
      scrollMode: go.Diagram.DocumentScroll,
      initialContentAlignment: go.Spot.TopCenter,
      validCycle: Diagram.CycleNotDirected, // don't allow loops
      allowHorizontalScroll: false,
      allowVerticalScroll: false,
    });

    this.diagram.groupTemplate = $(
      go.Group,
      "Vertical",
      {
        movable: false,
        copyable: false,
        deletable: false,
        selectable: false,
        computesBoundsAfterDrag: true,
        handlesDragDropForMembers: false, // don't need to define handlers on member Nodes and Links
        layout: $(go.GridLayout, {
          wrappingColumn: 1,
          alignment: GridLayout.Forward,
          cellSize: new go.Size(this.diagram.viewportBounds.width / 3, NaN),
        }),
      },
      new go.Binding("column", "column"),
      new go.Binding("row", "row"),
      $(
        go.Shape,
        {
          name: "SHAPE",
          desiredSize: new go.Size(this.WIDTH, this.groupHeight),
          strokeWidth: 0,
        },

        new go.Binding("fill", "key", (key) =>
          key === Const.SOURCE || key === Const.TARGET ? "#f5f5f5" : "white"
        )
      )
    );

    const cardtemplate = $(
      go.Node,
      "Auto",
      {
        deletable: true,
        copyable: false,
        movable: true,
        resizable: true,
        reshapable: false,
        alignment: go.Spot.Center
      },
      $(
        go.Shape,
        "RoundedRectangle",
        {
          portId: "",
          cursor: "pointer",
          fromLinkable: true,
          toLinkable: true,
          fromSpot: go.Spot.Right,
          toSpot: go.Spot.Left,
          width: 150,
          height: 30,
          fill: "grey",
          stroke: null,
        },
        new go.Binding("fill", "group", (group) =>
          group === Const.JOIN ? "rgba(255,218,47,0.67)" : "#9dd7ff"
        )
      ),
      $(
        go.TextBlock,
        {
          font: "bold 14px sans-serif",
        },
        new go.Binding("text", "name")
      )
    );

    const tabletemplate = $(
      go.Node,
      "Auto",
      {
        movable: false,
        copyable: false,
        deletable: false,
        selectable: false,
      },
      $(go.Shape, { fill: "white" }),
      $(
        go.Panel,
        "Table",
        {
          defaultRowSeparatorStroke: "lightgray",
        },
        new go.Binding("desiredSize", "size"),
        new go.Binding("itemArray", "items"),
        {
          defaultAlignment: go.Spot.Left,
          itemTemplate: $(
            go.Panel,
            "TableRow",
            {
              fromSpot: go.Spot.Right,
              toSpot: go.Spot.Left,
            },
            new go.Binding("portId", "name"),
            new go.Binding(
              "fromLinkable",
              "group",
              (group) => group === Const.SOURCE
            ),
            new go.Binding(
              "toLinkable",
              "group",
              (group) => group === Const.TARGET
            ),
            $(go.TextBlock, new go.Binding("text", "name"), {
              column: 0,
              margin: new go.Margin(6, 4, 6, 8),
              height: 20,
              font: "12pt Allianz Neo",
            }),
            $(go.TextBlock, new go.Binding("text", "value"), {
              column: 1,
              margin: new go.Margin(2, 8, 0, 4),
              height: 20,
              opacity: 0.9,
              font: "12pt Allianz Neo",
              background: "lightblue",
              alignment: go.Spot.Right,
            })
          ),
        },
        $(
          go.Panel,
          "TableRow",
          { isPanelMain: true }, // needed to keep this element when itemArray gets an Array
          $(go.TextBlock, new go.Binding("text", "name"), {
            margin: new go.Margin(0, 2, 0, 8),
            height: 32,
            font: "bold 16pt Allianz Neo",
          })
        ),
        $(
          go.RowColumnDefinition,
          { row: 0 },
          new go.Binding("background", "group", (group) =>
            group === Const.SOURCE ? "#7beeeb" : "#CCDD61"
          )
        ),
        $(go.RowColumnDefinition, { row: 1, separatorStroke: "black" })
      )
    );

    const templmap = new go.Map<string, go.Part>();
    templmap.add(Const.CARDS, cardtemplate);
    templmap.add(Const.TABLES, tabletemplate);

    templmap.add("", this.diagram.nodeTemplate);
    this.diagram.nodeTemplateMap = templmap;

    this.diagram.linkTemplate = $(
      "Link",
      {
        copyable: false,
        movable: true,
        resizable: false,
        reshapable: true,
        deletable: true,
        relinkableFrom: true,
        relinkableTo: true,
      },
      { cursor: "pointer" },
      $(
        "Shape",
        { strokeWidth: 2 },
        new go.Binding("stroke", "hasOperation", (hasOperation) =>
          hasOperation ? "#00B5CB" : "#b9b9b9"
        )
      ),

      $(
        "Shape",
        { stroke: null, toArrow: "Standard" },
        new go.Binding("fill", "hasOperation", (hasOperation) =>
          hasOperation ? "#00B5CB" : "#b9b9b9"
        )
      )
    );

    // @ts-ignore
    this.diagram.layout = $(this.loadTableLayout());

    this.diagram.model = $(go.GraphLinksModel, {
      linkFromPortIdProperty: "fromPort",
      linkToPortIdProperty: "toPort",
      nodeDataArray: this.dataAndDefinitions,
      linkDataArray: this.linkArray,
    });
  }

I’m wondering if you shouldn’t have a group in the middle.

I’m also wondering if you could then set Diagram.layout to an instance of LayeredDigraphLayout and then position the left and right groups programmatically yourself (i.e. ignore where the LayeredDigraphLayout positions those groups).

EDIT: I think this could work. I made a copy of the Record Mapper sample, https://gojs.net/latest/samples/records.html. I added these lines:

        $(go.Diagram, "myDiagramDiv",
          {
            layout: $(go.LayeredDigraphLayout, { columnSpacing: 10 }),
            "InitialLayoutCompleted": function(e) {
              var left = e.diagram.findNodeForKey("Record1");
              if (left) left.position = new go.Point(0, 0);
              var right = e.diagram.findNodeForKey("Record2");
              if (right) right.position = new go.Point(400, 0);
            },
            . . .

I changed the node template for the records to be its own category/template:

      // This template represents a whole "record".
      myDiagram.nodeTemplateMap.add("Record",
        $(go.Node, "Auto",
            . . .
        ));  // end Node

and I made the corresponding changes to both the model node data objects.
Then I could add some nodes and links representing the operations that occur in the mapping.

            nodeDataArray: [
              {
                category: "Record",
                key: "Record1",
                fields: [
                  { name: "field1", info: "", color: "#F7B84B", figure: "Ellipse" },
                  { name: "field2", info: "the second one", color: "#F25022", figure: "Ellipse" },
                  { name: "fieldThree", info: "3rd", color: "#00BCF2" }
                ]
              },
              {
                category: "Record",
                key: "Record2",
                fields: [
                  { name: "fieldA", info: "", color: "#FFB900", figure: "Diamond" },
                  { name: "fieldB", info: "", color: "#F25022", figure: "Rectangle" },
                  { name: "fieldC", info: "", color: "#7FBA00", figure: "Diamond" },
                  { name: "fieldD", info: "fourth", color: "#00BCF2", figure: "Rectangle" }
                ]
              },
              { key: 1, text: "Round" },
              { key: 2, text: "Multiply" }
            ],
            linkDataArray: [
              { from: "Record1", fromPort: "field1", to: 1 },
              { from: 1, to: "Record2", toPort: "fieldA" },
              { from: "Record1", fromPort: "field2", to: 2 },
              { from: "Record1", fromPort: "fieldThree", to: 2 },
              { from: 2, to: "Record2", toPort: "fieldB" }
            ]

Note that I didn’t bother defining a node template. You already have one defined, but I didn’t bother using it – it’s not relevant to your question. Just as the definition of the “Record” template doesn’t really matter either.

With only those changes, here’s the result:

Hi @walter!

User can change mapping dynamically, and record positions are changing after this. So initial positioning doesn’t fit our needs.

Now can we improve this sample to stick them at least horizontally? So if mapping is changed, records could move up or down, but not left or right. I tried to add ‘position’ to node’s and panel’s configs, but it doesn’t seem to be working.

I am unclear what it is that you want. In the sample above, the “InitialLayoutCompleted” DiagramEvent listener could be a “LayoutCompleted” DiagramEvent listener instead, if you want to execute that code after each layout. Normally, a layout will happen after a node or a link has been added or removed.

You can also modify that listener code to position those record nodes where you want them to be.

@walter this answers my question, thanks!