Data Model
Transactions

Transactions

Introduction

To Code provides by default a comprehensive overview of the CRUD operations for each entity within our Data Model. CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing data.

Also To Code provides the ability to define a Transaction based the defined entities and relations by definining the inputs, statements and output.

A Transaction is a prepared definition that you can save and reuse. In other words, a Transaction is a set of command statements that can be executed as a single unit. Transaction help in performing operations such as data manipulation, and complex calculations while providing a way to encapsulate logic within the Code Model.

Syntax

Implement write, delete and update operations with ease. Transactions are exposed as functions in identity repository, ensuring data integrity and simplifying complex data integration.

LogicalId

{
  "LogicalId": "UpdateAuthorStatus",
}
  • Description: the LogicalId is unique, represent the name of defined Transactions.

Description

{
  "Description": "UpdateAuthorStatus",
}
  • Description: the field serve as descriptors for the transaction, useful for referencing and understanding the purpose of the transaction.

Statements

This section includes one or more operations to be executed. In this case, there's a single Put action for updating an entity described by:

{
    "Statements": [
    {
      "Action": "Put",
      "EntityLogicalId": "Author",
      "Filter": [
        {
            "ConditionExpressions": [
              {
                "Parameters": [
                  {
                    "Value": "@TagId"
                  }
                ],
                "Aggregate": "None",
                "PropertyLogicalId": "Id",
                "ConditionOperator": "In",
                "Dynamic": true,
                "Alias": null,
                "AllowNull": false,
                "LogicalOperator": "And"
              }
          ],
          "FilterExpressions": null,
          "LogicalOperator": "And",
        }
      ],
      "Parameters": [
        {
          "Type": "Parameter",
          "PropertyLogicalId": "Name",
          "Value": {
            "Type": "InputValue",
            "Value": "@Name",
            "IsDynamic": true
          }
        },
        {
          "Type": "Parameter",
          "PropertyLogicalId": "Active",
          "Value": {
            "Type": "InputValue",
            "Value": "@Active",
            "IsDynamic": true
          }
        }
      ]
    }
  ]
}
  • Statements:
    • Action: The action to be performed, can be the following values:
      • Put (Update)
      • Post (Insert)
      • Delete
    • EntityLogicalId: Specifies the entity being acted upon.
    • Filter: indicate the defined filter on excution operation as below:
      • ConditionExpressions:
        • Parameters: to be define the values compared with selected PropertyLogicalId.
        • Aggregate: indicate the defined function in SQL, can be:
          • Count: Calculates the number of elements in a dataset or list.
          • Sum: Adds up all the numerical values in a dataset or list.
          • Sub: Subtracts one numerical value from another or subtracts multiple values from an initial value.
          • Mlp: Typically refers to multiplication, multiplying two or more values.
          • Div: Divides one numerical value by another.
          • Avg: Computes the average (mean) of a set of numbers.
          • Min: Finds the minimum value in a dataset or list.
          • Max: Finds the maximum value in a dataset or list.
          • Day: Extracts or works with the day from a date.
          • Month: Extracts or works with the month from a date.
          • Year: Extracts or works with the year from a date.
          • Concat: Concatenates elements, typically strings, end-to-end.
          • ConcatWithComma: Joins elements using a comma as the separator.
          • ConcatWithSpace: Joins elements using a space as the separator.
          • ConcatWithDashe: Joins elements using a dash (hyphen) as the separator.
          • ConcatWithSlashe: Joins elements using a slash as the separator.
        • PropertyLogicalId: defined the selected Property of Entity to be compared.
        • ConditionOperator: indicate the defined condition operator in SQL, can be:
          • In: Checks if a value exists within a set of values.
          • BeginsWith: Checks if a string starts with a specified substring.
          • Between: Checks if a value falls within a certain range (inclusive).
          • Contains: Checks if a string contains a specific substring.
          • DoesNotBeginWith: Checks if a string does not start with a specified substring.
          • DoesNotContain: Checks if a string does not contain a specific substring.
          • DoesNotEndWith: Checks if a string does not end with a specified substring.
          • EndsWith: Checks if a string ends with a specified substring.
          • Equal: Checks if two values are equal.
          • GreaterEqual: Checks if a value is greater than or equal to another value.
          • GreaterThan: Checks if a value is greater than another value.
          • LessEqual: Checks if a value is less than or equal to another value.
          • LessThan: Checks if a value is less than another value.
          • Like: Checks if a string matches a specified pattern (often using wildcards).
          • NotBetween: Checks if a value is not within a certain range.
          • NotEqual: Checks if two values are not equal.
          • NotIn: Checks if a value does not exist within a set of values.
          • NotLike: Checks if a string does not match a specified pattern.
          • NotNull: Checks if a value is not null.
          • Null: Checks if a value is null.
        • Dynamic: define a boolean related to the parameter to be static value or variable.
        • AllowNull: a boolean value that checking to include the nullable values in comparison operation.
        • LogicalOperator: define the logical operator between the parameter (And, Or).
        • FilterExpressions: nested conditions (ConditionExpressions, FilterExpressions, LogicalOperator).
    • Parameters:: indicate to an Array on parameter defined as below:
      • Type: indicate the type of parameter, value can be:
        • Parameter
        • ParametersGroup
      • PropertyLogicalId: Specifies the particular property of the entity being updated,
      • Value:
        • Type: indicate the type of value, can be:
          • InputValue
          • ForeignValue
        • Value: define the variable name and must be unique,
        • IsDynamic: define a boolean related to the parameter to be static value or variable.
  • Description: This criterion specifies the command statements that the transaction should executed.

Output

{
  Output:
  {
      EntityLogicalId = "Author",
      PropertyLogicalId = "Id",
  }
}

  • EntityLogicalId: indicate the target entity

  • PropertyLogicalId: indicate the target property at the selected entity

  • Description: The operation is expected to return.

Example

This document describes the setup and functionality of the transaction UpdateAuthorStatus configuration used for update from an entity identified as Post.

{
  "LogicalId": "UpdateAuthorStatus",
  "Description": "UpdateAuthorStatus",
  "Statements": [
    {
      "Action": "Put",
      "EntityLogicalId": "Author",
      "Filter": null,
      "Parameters": [
        {
          "Type": "Parameter",
          "PropertyLogicalId": "Name",
          "Value": {
            "Type": "InputValue",
            "Value": "@Name",
            "IsDynamic": true
          }
        },
        {
          "Type": "Parameter",
          "PropertyLogicalId": "Active",
          "Value": {
            "Type": "InputValue",
            "Value": "@Active",
            "IsDynamic": true
          }
        }
      ]
    }
  ],
  "Output": {
    "EntityLogicalId": "Author",
    "PropertyLogicalId": "Id"
  },
  "ExtensionsData": null
}

The JSON structure describes an update transaction that target Author.

Output

To Code convert the above json to be SQL stored procedure.

  CREATE PROCEDURE [data].[usp_UpdateAuthorStatus]
    
    @Name NVARCHAR (250),
    @Active BIT
  AS
  BEGIN TRY
    BEGIN TRANSACTION
 
    UPDATE
      [data].[Author]
    SET
      [Name] = @Name,[Active] = @Active
    SELECT SCOPE_IDENTITY()
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
QueriesObjects