Object

Creating an Object

Use the db.object method to construct the Object object:

import * as db from 'db';

@useObject(['student__cst'])
class Demo {
    test(): void {
        let student = db.object('student__cst');
    }
}

All operation object objects need to be decorated with the useObject function. The parameter must be a constant, which is used to analyze the objects on which the script depends during app packaging.

Single operation

Insert a single record.

let s = db.object('Student__CST');

let record = {
    "name": "hello",
    "age__CST": "demotest"
};


let id = s.insert(record);
console.log("insert result = ", id);
let r = s.query(id);
console.log("insert record = ", r);

Querying Records by ID

let s = db.object('Student__CST');

let record = s.query(id);
console.log("query", record)

Update records by ID.

let s = db.object('Student__CST');

let record = {name: "hello1"};
let count = s.update(id, record);
console.log("update result = ", count);
let r = s.query(id);
console.log("after update = ", r)

Delete records by ID.

let s = db.object('Student__CST');

let count = s.delete(id);
console.log("delete result = ", count);
let r = s.query(id);
console.log("after delete = ", r);

Batch operation

Inserting records in batches

let s = db.object('Student__CST');

let records = [];

for (let i = 0; i < 100; i++) {

    let name = "hello" + <string><any>(i);
    let age = "age" + <string><any>(i)
    let record = {
        "name": name,
        "age__CST": age
    }

    records.push(record);
}

let ids = s.batchInsert(records);
console.log("id list = ", ids);

count = s.count();
console.log("record count = ", count);

Creating Parent and Child Object Records in Batches

Records created in batches are all successful or all failed in a complete transaction. The ID list of parent and child object records is returned upon success.

let s = db.object('Customer__CST');

//The parent and child records need to be associated with each other through fields. For example, the following records are associated with each other through Contacts to form a combined record.
let records = [ {
    "name": "hello",
    "count__CST": 123,
    "Contacts": {
        "records": [
            {
                "name": "hello_contact1"
            },
            {
                "name": "hello_contact2"
            }
        ]
    }
}];

let ids = s.compositeInsert(records);
console.log("id list = ", ids);

count = s.count();
console.log("record count = ", count);

Conditional operation

condition

Single condition

{
    conjunction: db.Conjunction.AND,
    conditions: [
        {
            field: "f1",
            operator: db.Operator.eq,
            value: "123"
        },
        {
            field: "f2",
            operator: db.Operator.eq,
            value: "456"
        },
        {
            field: "f3",
            operator: db.Operator.in,
            value: [
                "a",
                "b",
                "c"
            ]
        }
    ]
}

Combined condition

{
  conjunction: db.Conjunction.AND,
  conditions: [
    {
      field: "f1",
      operator: db.Operator.eq,
      value: "123"
    },
    {
      field: "f2",
      operator: "eq",
      value: "456"
    },
    {
      field: "f3",
      operator: db.Operator.in,
      value: [
        "a",
        "b",
        "c"
      ]
    },
    {
      condition: {
        conjunction: db.Conjunction.OR,
        conditions: [
          {
            field: "f4",
            operator: db.Operator.eq,
            value: "789"
          },
          {
            field: "f5",
            operator: db.Operator.eq,
            value: "111"
          }
        ]
      }
    }
  ]
}

operator

The following table lists the operators supported by each data type.

Data Type	Supported Operators	Remarks
AutoNumber	eq, ne, le, ge, lt, gt, in, contains, startwith, endwith, isnull, isnotnull
CheckBox	eq, ne, isnull, isnotnull
Currency	eq, ne, le, ge, lt, gt, in, isnull, isnotnull
Date	eq, ne, le, ge, lt, gt, in, isnull, isnotnull
DateTime	eq, ne, le, ge, lt, gt, in, isnull, isnotnull
Email	eq, ne, le, ge, lt, gt, in, contains, startwith, endwith, isnull, isnotnull
Formula		Determine the supported operators based on the formula type.
ID	eq, ne, in, isnull, isnotnull
Lookup	eq, ne, in, isnull, isnotnull
MasterDetail	eq, ne, in, isnull, isnotnull
Name	eq, ne, le, ge, lt, gt, in, contains, startwith, endwith, isnull, isnotnull
Hierarchy	eq, ne, in, isnull, isnotnull
Number	eq, ne, le, ge, lt, gt, in, isnull, isnotnull
Percent	eq, ne, le, ge, lt, gt, in
Phone	eq, ne, le, ge, lt, gt, in, contains, startwith, endwith, isnull, isnotnull
SingleSelect	eq, ne, isnull, isnotnull	The contains, startwith, and endwith functions are not supported.
MultiSelect	eq, ne, includes, excludes, isnull, isnotnull	The contains, startwith, and endwith functions are not supported.
RollupSummary	sum, min, max, count, isnull, isnotnull	Determine the supported operators based on the formula type.
Text	eq, ne, le, ge, lt, gt, contains, startwith, endwith, isnull, isnotnull, in
TextArea	isnull, isnotnull	No operation is supported.
URL	eq, ne, le, ge, lt, gt, in, contains, startwith, endwith, isnull, isnotnull
PostalCode	eq, ne, le, ge, lt, gt, in, contains, startwith, endwit, isnull, isnotnullh

Special Instructions

The in operation needs to pass the value through an array. For example:

{
    value: ["hello", "hi"]
}

Querying records with conditions

let s = db.object('Student__CST');

let records = s.queryByCondition({
    conjunction: db.Conjunction.AND,
    conditions: [{
        field: "name",
        operator: db.Operator.eq,
        value: "hello1"
    }, {
        field: "age__CST",
        operator: db.Operator.eq,
        value: "age1"
    }]
});

console.log("name = ", records[0].name);
console.log("age__CST = ", records[0].age__CST);

Update records with conditions

let s = db.object('Student__CST');

let record = {
    "name": "helloupdate",
    "age__CST": "ageupdate"
};

let count = s.updateByCondition({
    conjunction: db.Conjunction.AND,
    conditions: [{
        field: "name",
        operator: db.Operator.eq,
        value: "hello1"
    },
    {
        field: "age__CST",
        operator: db.Operator.eq,
        value: "age1"
    }
    ]
}, record);

Deleting records with conditions

let s = db.object('Student__CST');

let count = s.deleteByCondition({
    conjunction: db.Conjunction.AND,
    conditions: [{
        field: "name",
        operator: db.Operator.eq,
        value: "test123"
    }]
});

console.log("delete count = ", count);
console.log("left count = ", s.count());

Advanced Query

Specify Query Field Set

let s = db.object('student__cst');

let records = s.queryByCondition(
    {
        conjunction: db.Conjunction.AND,
        conditions: [{
            field: "name",
            operator: db.Operator.ne,
            value: "hello1"
        }]
    },

    {
        options: {
           fields: ["f1", "f2", "f3"],
        }
    });

Result Sorting

Query result sets can be sorted in ascending (asc) or descending (desc) order by the value of a specified field.

let s = db.object('student__cst');

let records = s.queryByCondition(
    {
        conjunction: db.Conjunction.AND,
        conditions: [{
            field: "name",
            operator: db.Operator.ne,
            value: "hello1"
        }]
    },

    {
        options: {
            orderby: [
                {
                    field: "name",
                    order: db.Order.desc
                }
            ],
        }
    });

Querying a group

Query result sets can be grouped by specified fields.

let s = db.object('student__cst');

let records = s.queryByCondition(
    {
        conjunction: db.Conjunction.AND,
        conditions: [{
            field: "name",
            operator: db.Operator.ne,
            value: "hello1"
        }]
    },

    {
        options: {
            groupby: [
                {
                    field: "name",
                }
            ],
        }
    });

Query deduplication

let s = db.object('student__cst');

let records = s.queryByCondition(
    {
        conjunction: db.Conjunction.AND,
        conditions: [{
            field: "name",
            operator: db.Operator.ne,
            value: "hello1"
        }]
    },

    {
        options: {
            distinct: true,
        }
    });

Query by page

The number of returned records can be limited. The skip value is used to specify the number of records from which the query starts, and the limit value is used to specify the number of returned records.

If skip and limit are not specified, the default values are skip=0 and limit=5000, indicating that the first 5000 records in the query result set are returned.

The maximum limit can be set to 10000, indicating that the query interface can return only the first 10000 records of the query result set.

let s = db.object('Student__CST');

let records = s.queryByCondition(
    {
        conjunction: db.Conjunction.AND,
        conditions: [{
            field: "name",
            operator: db.Operator.ne,
            value: "hello1"
        }]
    },

    {
        options: {
            skip: 100,
            limit: 100
        }
    });

Aggregate Query

Common aggregation functions: sum, max, min, and avg

{
    aggregate: [
      {
        alias:   "total",
        field:    "f3",
        function: db.Function.sum
      },
      {
        field:    "f3",
        function: db.Function.max
      },
      {
        field:    "f3",
        function: db.Function.min
      },
      {
        field:    "f3",
        function: db.Function.avg
      }
    ]
}

Associated query

{  
    //Upward query (optional)
    parents: [  //Level-1 parent object
      {
        relatedField: "CarHolder__CST",  //Field of the current object (Lookup/MasterDetail type) based on which the parent object is determined.
        options: {
          fields: ["id", "name", "Age__CST"] //Field to be queried for the parent object.
        },
        parents: [ //(Optional) Second-level parent object
          {
            relatedField: "cust__CST", //Field of the parent object (Lookup/MasterDetail type) based on which the grandfather object is determined.
            options: {
              fields: ["id", "name", "Email__CST"] //To-be-queried field of a grandfather object
            }
          }
        ]
      }
    ],

    //(Optional) Downward query.
    childs: [   //Level-1 child object
      {
        relationName: "Contacts", //Name of the sub-object relationship, which is specified when the lookup/masterDetail is defined for the sub-object.
        options: {
          fields: ["id", "name", "Age__CST", "cust__CST"] //Field to be queried for a subobject.
      },
      childs: [  //(Optional) Second-level subobjects
        {
            relationName: "ContactCars", //Relationship name of a grandchild object.
            options: {
              fields: ["id", "name", "Price__CST", "CarHolder__CST"]  //To-be-queried fields of the grandchild object
            }
        }
      ]
      }
    ]
  }

Transaction operation

In the following script triggering modes:

Script for triggering HTTP requests.
The HTTP request triggers the flow, and the flow invokes the script.
Trigger of the HTTP request trigger object, and then the trigger invokes the script.
Script for triggering a scheduled task.
Script for triggering an event message.
The HTTP request triggers the workflow, and the workflow invokes the script.
...

In these cases, the script is executed in a common external transaction context and after the request process is executed:

If the request is successfully executed, the transaction is committed. If the request fails to be executed, the transaction is rolled back.

To control transactions in the script, you can invoke the following APIs of the db module:

Encapsulates an independent transaction.

transactionA series of database operations can be encapsulated in a single transaction, either all successful or all failed.

The success and failure do not affect external operations, and the success or failure of external operations do not affect external operations.transactionof the results.

import { object, transaction } from 'db';

@useObject(['student__cst'])
class Demo {
    test(): void {
        let student = object('student__cst');

        //Perform some database operations.
        //...

        let op = function(): void {
            //Perform other database operations.
        };

        //The database operations inside the op are independent transactions and do not affect the transaction operations outside the op.
        transaction(op)
    }
}

Commit: Commit the current transaction.

import { object, commit } from 'db';

@useObject(['student__cst'])
class Demo {
    test(): void {
        let student = object('student__cst');

        //...do some database operations

        //Commit the current transaction
        commit();
    }
}

rollback Rolls back the current transaction.t

import { object, rollback } from 'db';

@useObject(['student__cst'])
class Demo {
    test(): void {
        let student = object('student__cst');

        //...do some database operations

        //Rollback the current transaction.
        rollback();
    }
}

commit,rollbackThe transaction for the operation can be a common external transaction that triggers the script, or atransactionIndependent transactions that are triggered, depending on the transaction context in which they are executed.

System Object Operations

Use db.setup to support CRUD operations on system objects.

setupFor system objects,objectCustomize an object based on the standard object.

The core difference between the two is as follows: For performance, most system objects are cached in the private process cache, while standard objects and customized objects are not cached.

If a record is queried by ID, the setup process directly queries the result from the cache, and the object process needs to query the result from the database. Therefore, the setup process is much faster.

The performance of the setup object is poor during batch deletion and update. When the setup object is deleted or the record is updated, many special processing is required. Therefore, the setup object cannot be deleted in batches from the database.

setup.updateByConditionIt's different from thesetup.deleteByConditionThe implementation manner of is as follows:

Call firstobject.queryByConditionQuery all the s that meet the conditions from the database.id, and then based onidDelete and update one by one.

Object ​

Creating an Object ​

Single operation ​

Insert a single record. ​

Querying Records by ID ​

Update records by ID. ​

Delete records by ID. ​

Batch operation ​

Inserting records in batches ​

Creating Parent and Child Object Records in Batches ​

Conditional operation ​

condition ​

Single condition ​

Combined condition ​

operator ​

Querying records with conditions ​

Update records with conditions ​

Deleting records with conditions ​

Advanced Query ​

Specify Query Field Set ​

Result Sorting ​

Querying a group ​

Query deduplication ​

Query by page ​

Aggregate Query ​

Associated query ​

Transaction operation ​

System Object Operations ​