Repository: finos/legend-studio (ts)

Issue:
Bug: Missing support for local properties in relational mappings
### Similar issues

- [X] I have searched and found no existing similar issues

### How are you using Studio?

Legend Studio

### Current and expected behavior

Legend Engine allows users to define local properties in context of mapping. These properties are specifically useful in context of cross store relationships where adding the property in model is not ideal for modelling the business concept.

Engine supports local properties in context of relational mapping but studio does not support it yet.

Note : Studio does support local properties in context of M2M mapping

Below code currently throws an error - ```Can't build graph: Local mapping property is not supported```
Ideally studio should be able to compile below code

### Steps to reproduce

_No response_

### Model data

```text
###Relational
Database demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb
(
  Table productTable
  (
    productId VARCHAR(100) PRIMARY KEY,
    productName VARCHAR(100),
    description VARCHAR(1000)
  )
  Table synonymTable
  (
    synonymId INTEGER PRIMARY KEY,
    productId VARCHAR(100),
    type VARCHAR(200),
    name VARCHAR(200)
  )

  Join productTable_synonymTable(productTable.productId = synonymTable.productId)
)

###Pure

Enum demo::XStore::graphFetch::inMemoryAndRelational::models::SynonymType
{
  CUSIP,
  ISIN
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::T_Trade
{
  prodId: String[1];
  tradeId: Integer[1];
  traderKerb: String[0..1];
  quantity: Integer[1];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::S_TradesWrapper
{
  s_trades: demo::XStore::graphFetch::inMemoryAndRelational::models::S_Trade[*];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::Trade
{
  tradeId: Integer[1];
  traderKerb: String[0..1];
  quantity: Integer[1];
  productName() {$this.product.productName}: String[0..1];
  justProduct() {$this.product}: demo::XStore::graphFetch::inMemoryAndRelational::models::Product[0..1];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::Product
{
  productId: String[1];
  productName: String[1];
  description: String[1];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::TradeEvent
{
  eventId: String[1];
  description: String[1];
  timestamp: DateTime[1];
}


Class demo::XStore::graphFetch::inMemoryAndRelational::models::S_Trade
{
  s_tradeId: Integer[1];
  s_traderDetails: String[1];
  s_tradeDetails: String[1];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::Synonym
{
  name: String[1];
  type: demo::XStore::graphFetch::inMemoryAndRelational::models::SynonymType[1];
}

Class demo::XStore::graphFetch::inMemoryAndRelational::models::Trader
{
  kerberos: String[1];
  firstName: String[1];
  lastName: String[1];
}

Association demo::XStore::graphFetch::inMemoryAndRelational::models::Trade_Trader
{
  trader: demo::XStore::graphFetch::inMemoryAndRelational::models::Trader[1];
  trades: demo::XStore::graphFetch::inMemoryAndRelational::models::Trade[*];
}
Association demo::XStore::graphFetch::inMemoryAndRelational::models::T_Trade_Product
{
  product: demo::XStore::graphFetch::inMemoryAndRelational::models::Product[1];
  t_trades: demo::XStore::graphFetch::inMemoryAndRelational::models::T_Trade[*];
}

Association demo::XStore::graphFetch::inMemoryAndRelational::models::Product_Synonym
{
  product: demo::XStore::graphFetch::inMemoryAndRelational::models::Product[1];
  synonyms: demo::XStore::graphFetch::inMemoryAndRelational::models::Synonym[*];
}

Association demo::XStore::graphFetch::inMemoryAndRelational::models::Trade_Product
{
  product: demo::XStore::graphFetch::inMemoryAndRelational::models::Product[1];
  trades: demo::XStore::graphFetch::inMemoryAndRelational::models::Trade[*];
}

Association demo::XStore::graphFetch::inMemoryAndRelational::models::Trade_TradeEvent
{
  tradeEvents: demo::XStore::graphFetch::inMemoryAndRelational::models::TradeEvent[*];
  trade: demo::XStore::graphFetch::inMemoryAndRelational::models::Trade[1];
}
Association demo::XStore::graphFetch::inMemoryAndRelational::models::T_Trade_Trader
{
  trader: demo::XStore::graphFetch::inMemoryAndRelational::models::Trader[1];
  t_trades: demo::XStore::graphFetch::inMemoryAndRelational::models::T_Trade[*];
}


###Mapping
Mapping demo::XStore::graphFetch::inMemoryAndRelational::mappings::InMemoryAndRelationalCrossStoreMapping1
(
  demo::XStore::graphFetch::inMemoryAndRelational::models::Trade[trade_set]: Pure
  {
    ~src demo::XStore::graphFetch::inMemoryAndRelational::models::S_Trade
    tradeId: $src.s_tradeId,
    +prodId: String[1]: $src.s_tradeDetails->split(':')->at(0),
    quantity: $src.s_tradeDetails->split(':')->at(1)->parseInteger()
  }
  demo::XStore::graphFetch::inMemoryAndRelational::models::Product[prod_set]: Relational
  {
    ~primaryKey
    (
      [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable.productId
    )
    ~mainTable [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable

    +local : String[1] : [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable.productName,
    productId: [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable.productId,
    productName: [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable.productName,
    description: [demo::XStore::graphFetch::inMemoryAndRelational::database::inMemoryAndRelationalDb]productTable.description
  }

  demo::XStore::graphFetch::inMemoryAndRelational::models::Trade_Product: XStore
  {
    product[trade_set, prod_set]: $this.prodId == $that.productId,
    trades[prod_set, trade_set]: $this.productId == $that.prodId
  }
)
```


### Environment

```text
(no information provided)
```


### Possible solution and workaround

_No response_

### Contribution

- [ ] I would like to work on the fix for this issue

Pull request:
Support processing of local properties in relational mappings
<!--
  Thanks for submitting a pull request!
  We appreciate you spending the time to work on these changes. Please provide enough information so that others can review your pull request.

  Before submitting a pull request, please make sure the following is done:
  1. Fork [the repository](https://github.com/finos/legend-studio) and create your branch from `master`.
  2. Run `yarn` in the repository root.
  3. If you've fixed a bug or added code that should be tested, add tests!
  4. Add a changeset to summarize your change in the changelogs.
  5. If you haven't already, complete the CLA.

  Learn more about contributing: https://github.com/finos/legend-studio/blob/master/CONTRIBUTING.md
-->

## Summary

Closes #773 
<!--
  Explain the **motivation** for making this change. What existing problem(s) does this PR solve?
  Also, link the issue(s) to this PR: e.g. Fixes #1, Resolves #2
  If you also added documentation, link the PR from https://github.com/finos/legend
-->

## How did you test this change?

- [x] Test(s) added
- [ ] Manual testing (please provide screenshots/recordings)
- [ ] No testing (please provide an explanation)

<!--
  Demonstrate the code is solid. Screenshots/videos if the pull request changes the user interface.
  How exactly did you verify that your PR solves the issue you wanted to solve?
  If you leave this empty, your PR will very likely be closed.
-->

<!--
  More in-depth docs below:
  - Git workflow: https://github.com/finos/legend-studio/blob/master/docs/workflow/working-with-github.md
  - Test: https://github.com/finos/legend-studio/blob/master/docs/technical/test-strategy.md
  - Changeset: https://github.com/finos/legend-studio/blob/master/CONTRIBUTING.md#changeset
  - Dependency: https://github.com/finos/legend-studio/blob/master/docs/workflow/dependencies.md
  - UX/UI: https://github.com/finos/legend-studio/tree/master/docs/ux
-->


Patch:
diff --git a/.changeset/eleven-dolls-give.md b/.changeset/eleven-dolls-give.md
new file mode 100644
index 000000000..68eebe111
--- /dev/null
+++ b/.changeset/eleven-dolls-give.md
@@ -0,0 +1,5 @@
+---
+"@finos/legend-graph": patch
+---
+
+Support processing of local properties in relational mappings
diff --git a/packages/legend-graph/src/models/protocols/pure/v1/transformation/pureGraph/to/V1_ProtocolToMetaModelPropertyMappingBuilder.ts b/packages/legend-graph/src/models/protocols/pure/v1/transformation/pureGraph/to/V1_ProtocolToMetaModelPropertyMappingBuilder.ts
index 87ab210d0..c8bfbd42e 100644
--- a/packages/legend-graph/src/models/protocols/pure/v1/transformation/pureGraph/to/V1_ProtocolToMetaModelPropertyMappingBuilder.ts
+++ b/packages/legend-graph/src/models/protocols/pure/v1/transformation/pureGraph/to/V1_ProtocolToMetaModelPropertyMappingBuilder.ts
@@ -16,7 +16,6 @@
 
 import {
   LogEvent,
-  UnsupportedOperationError,
   assertNonNullable,
   assertNonEmptyString,
   guaranteeType,
@@ -86,6 +85,7 @@ import {
   getClassMappingsByClass,
 } from '../../../../../../../helpers/MappingHelper';
 import { GraphBuilderError } from '../../../../../../../graphManager/GraphManagerUtils';
+import type { AbstractProperty } from '../../../../../../metamodels/pure/packageableElements/domain/AbstractProperty';
 
 const resolveRelationalPropertyMappingSource = (
   immediateParent: PropertyMappingsImplementation,
@@ -414,11 +414,6 @@ export class V1_ProtocolToMetaModelPropertyMappingBuilder
       protocol.relationalOperation,
       `Relational property mapping 'relationalOperation' field is missing`,
     );
-    if (protocol.localMappingProperty) {
-      throw new UnsupportedOperationError(
-        'Local mapping property is not supported',
-      );
-    }
     // NOTE: mapping for derived property is not supported
     let propertyOwner: Class | Association;
     if (this.immediateParent instanceof AssociationImplementation) {
@@ -435,8 +430,40 @@ export class V1_ProtocolToMetaModelPropertyMappingBuilder
         `Can't find property owner class for property '${protocol.property.property}'`,
       );
     }
+    let property: AbstractProperty;
+    let localMapping: LocalMappingPropertyInfo | undefined;
+    if (protocol.localMappingProperty) {
+      const localMappingProperty = protocol.localMappingProperty;
+      const mappingClass = new MappingClass(
+        `${this.topParent?.parent.path}_${this.topParent?.id}${protocol.property.property}`,
+      );
+      const _multiplicity = this.context.graph.getMultiplicity(
+        localMappingProperty.multiplicity.lowerBound,
+        localMappingProperty.multiplicity.upperBound,
+      );
+      const _property = new Property(
+        protocol.property.property,
+        _multiplicity,
+        this.context.resolveGenericType(localMappingProperty.type),
+        mappingClass,
+      );
+      property = PropertyImplicitReference.create(
+        PackageableElementImplicitReference.create(
+          mappingClass,
+          protocol.property.class,
+        ),
+        _property,
+      ).value;
+      localMapping = new LocalMappingPropertyInfo();
+      localMapping.localMappingProperty = true;
+      localMapping.localMappingPropertyMultiplicity = _multiplicity;
+      localMapping.localMappingPropertyType = this.context.resolveType(
+        localMappingProperty.type,
+      ).value;
+    } else {
+      property = propertyOwner.getProperty(protocol.property.property);
+    }
     // NOTE: mapping for derived property is not supported
-    const property = propertyOwner.getProperty(protocol.property.property);
     // since we are not doing embedded property mappings yet, the target must have already been added to the mapping
     const propertyType = property.genericType.value.rawType;
     let targetSetImplementation: SetImplementation | undefined;
@@ -526,6 +553,7 @@ export class V1_ProtocolToMetaModelPropertyMappingBuilder
       }
       relationalPropertyMapping.transformer = enumerationMapping;
     }
+    relationalPropertyMapping.localMappingProperty = localMapping;
     return relationalPropertyMapping;
   }
 


Test patch:
diff --git a/packages/legend-graph/src/__tests__/ImportResolutionRoundtrip.test.ts b/packages/legend-graph/src/__tests__/ImportResolutionRoundtrip.test.ts
index 923b823b2..b6f259dda 100644
--- a/packages/legend-graph/src/__tests__/ImportResolutionRoundtrip.test.ts
+++ b/packages/legend-graph/src/__tests__/ImportResolutionRoundtrip.test.ts
@@ -58,6 +58,7 @@ import {
 import { TEST_DATA__ServiceRoundtrip } from './roundtripTestData/TEST_DATA__ServiceRoundtrip';
 import {
   TEST_DATA__AggregationAwareMappingRoundtrip,
+  TEST_DATA__Relational_LocalPropertyMappingRoundtrip,
   TEST_DATA__MappingRoundtrip,
   TEST_DATA__LocalPropertyMapping,
 } from './roundtripTestData/TEST_DATA__MappingRoundtrip';
@@ -119,6 +120,10 @@ describe(unitTest('Mapping import resolution roundtrip'), () => {
     ['M2M mapping', TEST_DATA__MappingRoundtrip],
     // TODO? association mapping
     ['Aggregation-aware mapping', TEST_DATA__AggregationAwareMappingRoundtrip],
+    [
+      'Relational mapping with local property',
+      TEST_DATA__Relational_LocalPropertyMappingRoundtrip,
+    ],
   ])('%s', async (testName, entities) => {
     await TEST__checkBuildingElementsRoundtrip(entities);
   });
diff --git a/packages/legend-graph/src/__tests__/roundtripTestData/TEST_DATA__MappingRoundtrip.ts b/packages/legend-graph/src/__tests__/roundtripTestData/TEST_DATA__MappingRoundtrip.ts
index 07059af4b..181eba3a6 100644
--- a/packages/legend-graph/src/__tests__/roundtripTestData/TEST_DATA__MappingRoundtrip.ts
+++ b/packages/legend-graph/src/__tests__/roundtripTestData/TEST_DATA__MappingRoundtrip.ts
@@ -1083,3 +1083,218 @@ export const TEST_DATA__AggregationAwareMappingRoundtrip = [
     classifierPath: 'meta::pure::metamodel::section::SectionIndex',
   },
 ];
+
+export const TEST_DATA__Relational_LocalPropertyMappingRoundtrip = [
+  {
+    path: 'my::models::Product',
+    content: {
+      _type: 'class',
+      name: 'Product',
+      package: 'my::models',
+      properties: [
+        {
+          multiplicity: {
+            lowerBound: 1,
+            upperBound: 1,
+          },
+          name: 'productId',
+          type: 'String',
+        },
+        {
+          multiplicity: {
+            lowerBound: 1,
+            upperBound: 1,
+          },
+          name: 'productName',
+          type: 'String',
+        },
+        {
+          multiplicity: {
+            lowerBound: 1,
+            upperBound: 1,
+          },
+          name: 'description',
+          type: 'String',
+        },
+      ],
+    },
+    classifierPath: 'meta::pure::metamodel::type::Class',
+  },
+  {
+    path: 'my::database::inMemoryAndRelationalDb',
+    content: {
+      _type: 'relational',
+      filters: [],
+      includedStores: [],
+      joins: [],
+      name: 'inMemoryAndRelationalDb',
+      package: 'my::database',
+      schemas: [
+        {
+          name: 'default',
+          tables: [
+            {
+              columns: [
+                {
+                  name: 'productId',
+                  nullable: false,
+                  type: {
+                    _type: 'Varchar',
+                    size: 100,
+                  },
+                },
+                {
+                  name: 'productName',
+                  nullable: true,
+                  type: {
+                    _type: 'Varchar',
+                    size: 100,
+                  },
+                },
+                {
+                  name: 'description',
+                  nullable: true,
+                  type: {
+                    _type: 'Varchar',
+                    size: 1000,
+                  },
+                },
+              ],
+              name: 'productTable',
+              primaryKey: ['productId'],
+            },
+          ],
+          views: [],
+        },
+      ],
+    },
+    classifierPath: 'meta::relational::metamodel::Database',
+  },
+  {
+    path: 'my::mappings::InMemoryAndRelationalCrossStoreMapping1',
+    content: {
+      _type: 'mapping',
+      classMappings: [
+        {
+          _type: 'relational',
+          class: 'my::models::Product',
+          distinct: false,
+          id: 'prod_set',
+          mainTable: {
+            _type: 'Table',
+            database: 'my::database::inMemoryAndRelationalDb',
+            mainTableDb: 'my::database::inMemoryAndRelationalDb',
+            schema: 'default',
+            table: 'productTable',
+          },
+          primaryKey: [
+            {
+              _type: 'column',
+              column: 'productId',
+              table: {
+                _type: 'Table',
+                database: 'my::database::inMemoryAndRelationalDb',
+                mainTableDb: 'my::database::inMemoryAndRelationalDb',
+                schema: 'default',
+                table: 'productTable',
+              },
+              tableAlias: 'productTable',
+            },
+          ],
+          propertyMappings: [
+            {
+              _type: 'relationalPropertyMapping',
+              localMappingProperty: {
+                multiplicity: {
+                  lowerBound: 1,
+                  upperBound: 1,
+                },
+                type: 'String',
+              },
+              property: {
+                class: 'my::models::Product',
+                property: 'local',
+              },
+              relationalOperation: {
+                _type: 'column',
+                column: 'productName',
+                table: {
+                  _type: 'Table',
+                  database: 'my::database::inMemoryAndRelationalDb',
+                  mainTableDb: 'my::database::inMemoryAndRelationalDb',
+                  schema: 'default',
+                  table: 'productTable',
+                },
+                tableAlias: 'productTable',
+              },
+            },
+            {
+              _type: 'relationalPropertyMapping',
+              property: {
+                class: 'my::models::Product',
+                property: 'productId',
+              },
+              relationalOperation: {
+                _type: 'column',
+                column: 'productId',
+                table: {
+                  _type: 'Table',
+                  database: 'my::database::inMemoryAndRelationalDb',
+                  mainTableDb: 'my::database::inMemoryAndRelationalDb',
+                  schema: 'default',
+                  table: 'productTable',
+                },
+                tableAlias: 'productTable',
+              },
+            },
+            {
+              _type: 'relationalPropertyMapping',
+              property: {
+                class: 'my::models::Product',
+                property: 'productName',
+              },
+              relationalOperation: {
+                _type: 'column',
+                column: 'productName',
+                table: {
+                  _type: 'Table',
+                  database: 'my::database::inMemoryAndRelationalDb',
+                  mainTableDb: 'my::database::inMemoryAndRelationalDb',
+                  schema: 'default',
+                  table: 'productTable',
+                },
+                tableAlias: 'productTable',
+              },
+            },
+            {
+              _type: 'relationalPropertyMapping',
+              property: {
+                class: 'my::models::Product',
+                property: 'description',
+              },
+              relationalOperation: {
+                _type: 'column',
+                column: 'description',
+                table: {
+                  _type: 'Table',
+                  database: 'my::database::inMemoryAndRelationalDb',
+                  mainTableDb: 'my::database::inMemoryAndRelationalDb',
+                  schema: 'default',
+                  table: 'productTable',
+                },
+                tableAlias: 'productTable',
+              },
+            },
+          ],
+          root: false,
+        },
+      ],
+      enumerationMappings: [],
+      includedMappings: [],
+      name: 'InMemoryAndRelationalCrossStoreMapping1',
+      package: 'my::mappings',
+      tests: [],
+    },
+    classifierPath: 'meta::pure::mapping::Mapping',
+  },
+];