# DataSource Contract Patterns

## Critical Rule

**ALWAYS use declarative XML structure with Attributes/Entities/QueryCondition**
**NEVER use external JavaScript approach unless absolutely necessary**

## Why Declarative XML?

-   **Cleaner:** No boilerplate JavaScript code
-   **Framework-generated:** SQL generation handled automatically
-   **Maintainable:** Schema changes reflected in XML, not code
-   **Type-safe:** Framework validates attributes against entities
-   **Standard:** Follows documented contract pattern

---

## Correct Pattern: Declarative XML

### Basic Structure

```xml
<DataSource name="DsBoOpportunity"
            backendSystem="sf"
            businessObjectClass="BoOpportunity"
            readOnly="true"
            external="false"           <!-- KEY: false for declarative -->
            editableEntity="Opportunity"
            schemaVersion="2.0">

  <!-- 1. Map BO properties to database columns -->
  <Attributes>
    <Attribute name="pKey" table="Opportunity" column="Id" />
    <Attribute name="opportunityName" table="Opportunity" column="Name" />
    <Attribute name="stageName" table="Opportunity" column="StageName" />
    <Attribute name="amount" table="Opportunity" column="Amount" />
    <Attribute name="accountName" table="Account" column="Name" />
    <Attribute name="ownerName" table="User" column="Name" />
  </Attributes>

  <!-- 2. Define tables and their relationships -->
  <Entities>
    <Entity name="Opportunity" alias="" idAttribute="Id" />

    <Entity name="User" alias="">
      <Join Type="inner">
        <SimpleJoin>
          <Condition leftSideValue="User.Id"
                     comparator="EQ"
                     rightSideType="Attribute"
                     rightSideValue="Opportunity.OwnerId" />
        </SimpleJoin>
      </Join>
    </Entity>

    <Entity name="Account" alias="">
      <Join Type="left">
        <SimpleJoin>
          <Condition leftSideValue="Account.Id"
                     comparator="EQ"
                     rightSideType="Attribute"
                     rightSideValue="Opportunity.AccountId" />
        </SimpleJoin>
      </Join>
    </Entity>
  </Entities>

  <!-- 3. Declare parameters -->
  <Parameters>
    <Parameter name="pKey" type="TEXT" />
  </Parameters>

  <!-- 4. WHERE clause with parameter macros -->
  <QueryCondition><![CDATA[
    Opportunity.Id = '#pKey#'
  ]]></QueryCondition>

  <!-- 5. Optional: Sort order -->
  <OrderCriteria>
    <OrderCriterion entity="Opportunity" attribute="CloseDate" direction="DESC" />
  </OrderCriteria>
</DataSource>
```

---

## Wrong Pattern: External JavaScript

```xml
<!-- DON'T DO THIS -->
<DataSource name="DsBoOpportunity"
            backendSystem="sf"
            external="true"             <!-- AVOID: true for JavaScript -->
            ...>
  <Database platform="SQLite">
    <Load><![CDATA[
      var pKey = "";
      if (Utils.isDefined(jsonQuery.pKey)) {
        pKey = jsonQuery.pKey;
      }
      var sqlParams = {pKey};

      var sqlStmt = "SELECT ";
      sqlStmt += "Opportunity.Id as pKey, ";
      sqlStmt += "Opportunity.Name as opportunityName, ";
      // ... lots of string concatenation

      var sqlResult = Utils.replaceMacrosParam(sqlStmt, sqlParams);
      return {sql: sqlResult.sql, params: sqlResult.params};
    ]]></Load>
    <Update><![CDATA[return undefined;]]></Update>
    <Insert><![CDATA[return undefined;]]></Insert>
    <Delete><![CDATA[return undefined;]]></Delete>
  </Database>
</DataSource>
```

**Problems:**

-   Verbose string concatenation
-   Manual parameter handling
-   Unnecessary empty sections for read-only DataSources
-   Harder to maintain and debug
-   Obscures the data model

---

## Key Elements

### 1. Attributes Section

Maps BO/LO properties to database columns:

```xml
<Attributes>
  <!-- Standard attribute -->
  <Attribute name="pKey" table="Opportunity" column="Id" />

  <!-- Derived attribute with SQL expression -->
  <DerivedAttribute name="fullName"
                    value="FirstName || ' ' || LastName" />

  <!-- DateTime split into date and time -->
  <DateTimeAttribute dateName="date"
                     timeName="time"
                     table="Opportunity"
                     column="CreatedDate" />
</Attributes>
```

### 2. Entities Section

Defines tables and relationships:

```xml
<Entities>
  <!-- Main table (first entity has no JOIN) -->
  <Entity name="MainTable" alias="" idAttribute="Id" />

  <!-- Joined tables -->
  <Entity name="RelatedTable" alias="">
    <Join Type="inner|left|left outer">
      <SimpleJoin>
        <Condition leftSideValue="RelatedTable.ForeignKey"
                   comparator="EQ"
                   rightSideType="Attribute"
                   rightSideValue="MainTable.Id" />
      </SimpleJoin>
    </Join>
  </Entity>
</Entities>
```

**Join Types:**

-   `inner` - Only matching records
-   `left` - All from left table + matching from right
-   `left outer` - Same as left

**Comparators:** EQ, NE, LT, LE, GT, GE

### 3. Parameters Section

Declare parameters for QueryCondition:

```xml
<Parameters>
  <Parameter name="pKey" type="TEXT" />
  <Parameter name="accountId" type="TEXT" />
  <Parameter name="validFrom" type="TEXT" baseType="Date" />
  <Parameter name="stageNames" type="LIST" />
</Parameters>
```

**Types:** TEXT, INTEGER, REAL, NULL, LIST

### 4. QueryCondition Section

WHERE clause with parameter macros:

```xml
<QueryCondition><![CDATA[
  Opportunity.AccountId = '#currentCustomerPKey#'
  AND Opportunity.IsDeleted <> 1
  AND Opportunity.StageName IN (#stageNames#)
]]></QueryCondition>
```

**Framework Macros:**

-   `#Language#` - User's language (e.g., "en")
-   `#SalesOrg#` - Sales organization code
-   `#Client#` - Client code
-   `#UserPKey#` - Current user PKey
-   `#Today#` - Current date (YYYY-MM-DD)
-   `#MaxDate#` - Maximum date (9999-12-31)
-   `#MinDate#` - Minimum date (1800-01-01)

**Parameter Macros:** `#parameterName#`

### 5. OrderCriteria Section (Optional)

```xml
<OrderCriteria>
  <OrderCriterion entity="Opportunity" attribute="CloseDate" direction="DESC" />
  <OrderCriterion entity="Opportunity" attribute="Probability" direction="DESC" />
  <OrderCriterion entity="Opportunity" attribute="Name" direction="ASC" />
</OrderCriteria>
```

### 6. ConditionalParameters (Optional)

Add conditions only when parameter is provided:

```xml
<ConditionalParameters>
  <ConditionalParameter name="stageName">
    <SimpleConditions>
      <Condition leftSideValue="Opportunity.StageName"
                 comparator="EQ"
                 rightSideType="Literal"
                 rightSideValue="'#stageName#'" />
    </SimpleConditions>
  </ConditionalParameter>
</ConditionalParameters>
```

---

## When External JavaScript IS Appropriate

Use `external="true"` **ONLY** for:

-   Complex dynamic SQL that cannot be expressed declaratively
-   Runtime-dependent query structure
-   Advanced aggregations or window functions
-   Complex CASE statements in SELECT clause

**Estimate:** 90% of DataSources should use declarative XML, only 10% need JavaScript.

---

## Read-Only DataSources

For `readOnly="true"` DataSources:

-   Do NOT include `<Database>` section at all
-   Framework automatically skips write operations
-   Much cleaner contract definition

---

## Checklist for New DataSources

-   [ ] Set `external="false"`
-   [ ] Define all `<Attributes>` with correct table.column mapping
-   [ ] Define `<Entities>` with proper JOIN conditions
-   [ ] Declare all `<Parameters>` used in QueryCondition
-   [ ] Write `<QueryCondition>` with parameter macros
-   [ ] Add `<OrderCriteria>` if sorting is needed
-   [ ] For read-only: set `readOnly="true"` and omit `<Database>` section
-   [ ] Test parameter macro replacement
-   [ ] Verify JOIN logic matches business requirements

---

_This documentation is maintained by the Modeler CLI plugin and refreshed on workspace upgrade._