@cute-dw/core

This TypeScript library is the main part of a more powerfull package designed for the fast WEB software development. The cornerstone of the library is the DataStore class, which might be useful when you need a full control of the data, but do not need the visual representation of it. The second important element of the library is the abstract DataWindow class, which implements the same interface as the DataStore class, but is designed for developing visual components using Angular/TypeScript. Another author's project (@cute-dw/ui) is an example of the implementation of this functionality based on the @angular/material component library.

Installation

npm install @cute-dw/core

Quick start

Without further ado, let's assume that somewhere in the cloud there is a data source containing the product table with the following structure, and we want to perform some CRUD operations on it:

  CREATE TABLE product_table (
    ID    BIGSERIAL NOT NULL,
    CODE  VARCHAR(30) NOT NULL,
    NAME  VARCHAR(80),
    NOTE  TEXT,
    QTY   INTEGER NOT NULL DEFAULT 0,
    PRICE NUMERIC(15,2) NOT NULL DEFAULT 0,
    ISACTIVE NUMERIC(1) NOT NULL DEFAULT 1,
    OPEN_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ID)  
  )

The following code snippet shows how you can describe the target data source (model) and read the data from it using the DataStore object:

  let options: DataStoreOptions = {};
  /* General DataStore options */
  options.datawindow = {
    name: "Product list",
    paginator: {pageSize: 20, pageSizeOptions: [10, 20, 50, 100] /*, showFirstLastPage: true*/}
  };
  /* Source table columns with which we want to work (display) */
  options.table = {
      columns: [
        { name: "id", type: "long", key: true, identity: true },
        { name: "code", type: "char(30)", values: [{label:"Code 001", value:"001", icon:"home"}, {label:"Code 002", value:"002", icon:"wifi"}] },
        { name: "name", type: "char(80)" },
        { name: "notactive", type: "number", values: [{label:"Yes",value:1}, {label:"No ", value:0}], initial: 0 },
        { name: "open_date", type: "datetime", initial: (new Date())}
      ],
      parameters: TableParam.map({ name: "isactive", type: "number"/*, defValue:0*/ }),
      //groups: ["Upper(left(name, 2))", "Upper(substr(name, 2, 2))"],
      startFilter: "code != null && id > 10000",
      selectMethod: productService.readProducts,
      updateMethod: productService.updateProduct,
      deleteMethod: productService.deleteProduct,
      updateWhere:  UpdateWhereClause.KeyAndModifiedCols,
      updateTable: "product_table"
  };
  /* DataWindow controls (optional for DataStore) */
  options.controls = {
      "rownum": { type: "compute", header: "#", width: 40, fwidth: 100, alignment: "center", expression: "GetRow()+1", sortable: false, microhelp: "Row number", sticky:"left" },
      "icon": { type: "compute", width: 50, expression: "((notactive==1)?'home':'face')", $class: "=((notactive==1)?'md-36':'md-24')", palette: "primary", $style: "={color: iif(GetRow()%2==0,'#ffbb00','')}", displayas: "picture",  alignment:"center", badge: "", badgecolor: "=iif(GetRow()%2==0,'primary','warn')", resizeable: true },
      "id": { type: "column", header: "ID", label:"Identity", width: 60, fwidth: 100, alignment: "center", editstyle: {name:"edit", readonly: true}, microhelp: "Row identity"},
      "code": { type: "column", label:"Product Code", width: 60, fwidth: 200, visible: true, required: true, alignment: "left", microhelp: "Product Code/Article", $style: "={color:(GetRow()%2==0)?'red':'blue'}", summable: true,
               editstyle: {name: "ddplb", placeholder: "Enter Product Code"}},
      "name": { type: "column", label: "Product Name", width: 500, visible: true, alignment: "justify", resizeable: false, microhelp: "Product full name", editstyle:{"name":"edit", placeholder:"Enter Product name"}},
      "notactive": { type: "column", width: 60, fwidth: 150, editstyle: { name: "radiobuttons"}, alignment: "center", summable:true, microhelp: "Is Product active?" },
      "open_date": {type: "column", label:"Created", width: 100, fwidth: 220, alignment: "center"},
      "cb_edit": { type: "compute", label: "Button Edit", width: 92, expression: "'Edit...'", icon: "more_vert",  displayas: "button",  alignment:"center", palette: "primary", microhelp: "Edit Product" },
  };
  /* Initialize object */
  this.ds = new DataStore<any>(options, this.productService);
  /* Set start filter value */
  this.ds.filterValue = "code != null && id > 20000";
  /* Set start sort condition */
  this.ds.sortValue = "Upper(left(name, 4)) asc \t";
  /* Subscribe on Database errors */
  this.ds.onDbError.subscribe((err)=>alert(err));
  /* Gets data from the data source with optional parameter values */
  this.ds.retrieve(1);  // Gets active products only

As we can see, the DataStore object initialization needs two arguments of the following types: DataStoreOptions and HttpService. The first argument is a plain JavaScript object with the predefined properties. The second one is an instance of the some injectable service object. DataStoreOptions contains several key namespaces:

• datawindow - defines general DataStore/DataWindow options.

• table - this namespace is required and describes columns of the retrieved data model, query parameters, methods for select/update data and other attributes. More about it see in the next sections.

• controls - optional namespace which defines a map object containing DataStore/DataWindow control objects. For table columns, control must have the type attribute value equals to column and key/name must be identical to one of the names in the table.columns array. Another group of controls may belong to the compute type. In this case the expression attribute must be fulfilled. Expression is a text string written on embedded domain-specific programming language DwScript and evaluated dynamically at run time. Evaluation context of the expression is the current row of the DataStore/DataWindow. Thus, any defined column/compute name can be used to evaluate its current value but NOT to change it. DwScript is a extensible language though it understands many specific and regular JavaScript functions and operators from a box. More about DwScript see in the next sections.

The following code snippet is an example of the futher data processing:

/* Get count of rows in the Primary buffer of the DataStore */
let rows: number = this.ds.rowCount();
let res: any;

for (let i:number = 0, i < rows; i++) {
  // we can read the column's data in the row...
  res = this.ds.getItem(i, "code");       // string
  res = this.ds.getItem(i, "id");         // number
  res = this.ds.getItem(i, "open_date");  // Date 
  res = this.ds.getItem(i, 3);            // 4-th column's data 
  // or calculate some expressions on it 
  res = this.ds.evaluate("Round(qty*price,2)", i);
}

// or in the 'for-of' style
for (let row of this.ds.rowsCursor("Primary!")) {
  res = row.code;   // row["code"]
  res = row.id;
  res = row.open_date;
  res = row.rownum; // computed field
}
// or more concise
for (let row of this.ds) {
  ...
  ...
}

Buffers

Data in the DataStore may reside in one of the following data buffers: Primary!, Filter!, Delete!. The default data storage location is the Primary! buffer. Thus, most methods of DataStore without special notes work with Primary! data. To get the number of records currently existing in the corresponding data buffer, you can use such methods:

let primaryCount  = this.dataStore.rowCount();
let filteredCount = this.dataStore.filteredCount();
let deletedCount  = this.dataStore.deletedCount();

Data retrieving

To retrieve rows from the remote data source you need to call retrieve method of the DataStore. If arguments are included, the argument values are set as http parameters for client service call:

/**
 * Example of Angular http service class
 */
@Injectable({
  providedIn: 'root'
})
export class ProductService extends HttpService{
  private baseUrl = "http://www.mycool.site:9090";

  constructor(http:HttpClient) {
    super(http);
  }

  getProducts(where?: HttpParams): Observable<Product[]> {
    return this.get(this.baseUrl+"/ws_products", {params: where});
  }

  ...

}

And in the some method of your class call something like this:

this.ds.retrieve(1).then(rows => console.log(rows) );

You can get a direct readonly reference to the Primary! data buffer of your DataStore using its data property. But a more rubust option is to get a subscription to any primary data changes in your DataStore object in the future using the dataStream property:

/* Class properties */
private _dataSource: any[] = [];

...

this.ds.dataStream.subscribe(data => this._dataSource = data);

Filtering

To move data to the Filter! buffer of the DataStore you have to set filter condition and then run filtering process something like this:

/* Example. Need to move all rows which `code` value ends with 'xyz' to Filter! buffer. */
/* Step 1. Set condition */
this.ds.filterValue = "like(code, '%xyz')";
/* Step 2. Run filtering process */
this.ds.applyFilter();
/* Handle result */
let rows = this.ds.filteredRows();

There are special methods for moving data between any DataStore buffers such as rowsCopy, rowsMove. More about it see DataStore's API documentation.

Sorting

To sort rows in the Primary! buffer of the DataStore/DataWindow we need to set sort condition first. Then, we can run data sort processing:

this.ds.sortValue = "code asc, qty desc";
this.ds.applySort();

Row/item status

DataStore/DataWindow supports the following states for each row and row's item (column) in each data buffer:

| Status | Applies to |Meaning | | :--- | --- |:--- | |NotModified! | Rows & Columns | The information in the row or column is unchanged from what was retrieved. | |DataModified!| Rows & Columns | The information in the column or one of the columns in the row has changed since it was retrieved. | |New! | Rows | The row is new but no values have been specified for its columns. (Applies to rows only, not to individual columns.) | |NewModified! | Rows | The row is new, and values have been assigned to its columns. In addition to changes caused by user entry or the setItem method, a new row gets the status NewModified! when one of its columns has a default value. (Apples to rows only, not to individual columns.) |

To get the row/item current status we can call a special method:

let rowNumber = 2;  // zero based
let status: ItemStatus | undefined;
/* Get the third row status in the Primary! buffer */
status = this.ds.getItemStatus(rowNumber, null);
/* Get the third row status in the Filter! buffer (if exists) */
status = this.ds.getItemStatus(rowNumber, null, "Filter!");
/* Get the column status of the third row */
status = this.ds.getItemStatus(rowNumber, "code", "Primary!");

Deleting rows

To delete row in the DataStore's Primary! buffer we need to call the corresponding deleteRow method. If it returns the successfull result, the specified row was moved from Primary! to Delete! buffer. Note that you can simply discard a range of specified rows from the DataStore buffer by calling the rowsDiscard method.

let rowToDelete = 5
console.log(this.ds.rowCount());      // 10 
console.log(this.ds.deletedCount());  // 0

this.ds.deleteRow(rowToDelete);

console.log(this.ds.rowCount());      // 9 
console.log(this.ds.deletedCount());  // 1

// Discard rows 
this.ds.rowsDiscard(0, 1);

console.log(this.ds.rowCount());      // 7 
console.log(this.ds.deletedCount());  // 1  !!!

// Delete multiple rows
this.ds.rowsMove(0, this.ds.RowCount(), "Primary!", this.ds, 0, "Delete!");
console.log(this.ds.rowCount());      // 0 
console.log(this.ds.deletedCount());  // 8

Inserting rows

There are several methods to insert row(s) to the Primary! buffer of the DataStore. First of all there is an insertRow method:

/* Append row to the end of the data list */
let row: number = this.ds.insertRow();
this.ds.setItem(row, "code", "XY-1000");
this.ds.setItem(row, "name", "Super product, L-1000");
/* Insert row before second row */
let row1:number = this.ds.insertRow(1); 
this.ds.setItem(row1, "code", "A-600");
this.ds.setItem(row1, "name", "Another cool product, 600kg");

If an initial value has been defined for a column in the DataStore's configuration object, this value is applied to the newly inserted row. The inserted row (with a status flag of New!) is not included in the modified count until data is entered in the row (its status flag becomes NewModified!).

Another option to append one or more rows to the DataStore is to use such methods as importString, rowsCopy, rowsMove.

Share data

Often we need to share our data repository between several components particularly if they display the identical data in different styles, for example grids, forms, graphs/charts, crosstabs, etc. To help solve such a problem DataStore has a special method shareData. This method shares data retrieved by one DataStore (or DataWindow control), which is referred to as the primary DataStore, with another DataStore (or DataWindow control), referred to as the secondary DataStore/DataWindow. The controls do not share formatting, only the data is shared, including data in buffers and sorting order. The order and type of the data columns of the primary and secondary DataStore/DataWindow must be identical.

this.mainDS.shareData(dwGrid);
this.mainDS.shareData(dwGraph);
...
this.mainDS.filterValue = "InList(code, 'ABC', 'DEF')";
this.mainDS.applyFilter();
// Now all our views display data for rows in which the "code" column has only two specified values
...
this.mainDS.filterValue = "";
this.mainDS.applyFilter();
// Now all filters are canceled
...
// Turns off the sharing of data buffers for a DataWindow control or DataStore.
this.mainDS.shareDataOff();

Save changes. Part 1.

Before sending any changes to a remote endpoint DataStore carefully analyzes the states of all its rows and columns. There are a few simple rules that DataStore uses to determine the type of data update operation:

• Row will be inserted if the row resides in the Primary! or Filter! buffer and its status is NewModified!
• Row will be modified if the row resides in the Primary! or Filter! buffer and its status is DataModified!
• Row will be deleted if the row resides in the Delete! buffer and its status is NOT New! or NewModified!

DataStore is NOT updateable if its datawindow.readonly configuration property is set to true or the value of table.updateTable property is empty. Table columns with the truthy key and/or updatable properties must also exist.

There is another important aspect that needs to be taken into account when sending data for updating and/or deletion in the SQL database on the backend. The value of table.updateWhere property. This value indicates which columns will be included in the WHERE clause of the SQL Update/Delete statement. updateWhere can impact performance or cause lost data when more than one user accesses the same tables at the same time. Values are:

• 0 - Key columns only (risk of overwriting another user's changes, but fast).
• 1 - Key columns and all updatable columns (risk of preventing valid updates; slow because SELECT statement is longer).
• 2 - Key and modified columns (allows more valid updates than 1 and is faster, but not as fast as 0).

The default value of updateWhere is 0, as this is a more appropriate value for the most web applications.

Save changes. Part 2

DataStore saves its changes to the backend by sending batch requests via methods that assigned to the following properties: table.insertMethod, table.updateMethod, table.deleteMethod, table.batchMethod. If the batchMethod is defined then the others are ignored. All client methods are required to implement the RpcMethodSignature interface, which defines only one required parameter. The value that will be assigned to this parameter by DataStore is an array of JSON objects that follow to JSON-RPC 2.0 specification. Thus table methods should send a POST request via http because others (PUT, DELETE, PATCH, etc.) don't make much sense in this case.

  // Product service (excerpt)

  ...
  // Example of the batch update method
  batchUpdate(body: any): Observable<any> {
    const options = this.updateOptions;
    return this.post(this.baseUrl+"/rpc/db/update", JSON.stringify(body), options);
  }
  ...

So, as mentioned above, each array's element is the client request object (rpc-method) and we can describe its type like this:

  type ColumnName  = string;
  type ValuesMap   = {[key: string]: any};
  type InsertedRow = {values: ValuesMap, table?: string, returning?: ColumnName[]|"*"};
  type UpdatedRow  = {set: ValuesMap, where: ValuesMap, table?: string, returning?: ColumnName[]|"*"};
  type DeletedRow  = {where: ValuesMap, table?: string};

  type JsonRpcRequest = {jsonrpc: "2.0", method: string, id: number|string|null};
  /* RPC-request types */
  type JsonRpcInsert = JsonRpcRequest & {params: InsertedRow};
  type JsonRpcUpdate = JsonRpcRequest & {params: UpdatedRow};
  type JsonRpcDelete = JsonRpcRequest & {params: DeletedRow};

By default, the value that is assigned to the method parameter has the following format: <entity>.<action>. The table.updateTable property value replaces the entity. The action can be one of the following options: insert, update, delete. Evantually the request method for, for example, inserting rows into product_table table looks like this: product_table.insert. Inserting and updating requests may have the returning property if the DataStore is interested in the new values of some table columns.

To change the method's name generation procedure assign a code value to table.rpcMethodFormat property.

The URL of the endpoint for RPC requests is not interseting for DataStore and may have any value which is defined in the appropriate http service object.

The backend MUST reply to the client's request using the same JSON-RPC 2.0 specification. The Server should respond with an Array containing the corresponding Response objects, after all of the batch Request objects have been processed. The response object can be one of the two types depending on the result of the operation:

  /* `returning` property is a map of values for homonymous property in the client's request object */
  type RpcResponseResult = {jsonrpc: "2.0", result: {sqlnrows?: number, returning?: {[key:string]:any}}, id: number|string|null};
  type RpcResponseError  = {jsonrpc: "2.0", error: {code: number, message: string,  data?: {sqlcode?: number, sqlstate?: string, sqlerrtext?: string}}, id: number|string|null};

DataStore sends a batch of update requests in the hope that the server will execute them in the transactional scope. If this is the case and some error occurs, the server application SHOULD save information about it, and execute the ROLLBACK statement. Then, the server SHOULD send to the client ONLY ONE error response object with an id value equal to null and error property populated.

The successful response SHOULD be an array of the Result objects where id property value MUST be identical to the request's id that was received from a client.

Save changes. Part 3

DataStore supports updatesPending readonly property to check if DataStore (or DataWindow control) has any pending updates. The value of this property can help you design your user interface more thoroughly.

Finally, we can run updating process like this:

this.ds.update().then(rc => console.log(rc))  // 1 - successfull

Method update returns a Promise object that represents the eventual completion (or failure) of our asynchronous update operation. When the updating process will end successfully Promise returns 1, else -1. If there are no pending updates Promise returns 0.

DataStore emits three events during its updating process: onUpdateStart, onUpdateEnd and onDbError. The first one is the cancelable event i.e. DataStore reacts on event's defaultPrevented property value. For example:

const usBeg = this.ds.onUpdateStart.subscribe(event => {
  if (someCondition == true) {
    // stop updating process
    event.preventDefault();
  }
});
const usEnd = this.ds.onUpdateEnd.subscribe(event => {
  console.log(event.rowsInserted);
  console.log(event.rowsUpdated);
  console.log(event.rowsDeleted);
});
const usErr = this.ds.onDbError.subscribe(event => {
  console.log(event)
});
...
...
usBeg.unsubscribe();
usEnd.unsubscribe();
usErr.unsubscribe();

More about supporting events in the DataStore see in the next sections.

Update JOINs

Let's imagine that we retrieve rows from more than one SQL table or an updatable VIEW. For example, consider this simple database:

CREATE TABLE employee (
  id BIGSERIAL NOT NULL,
  initials TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  birthday DATE,
  salary NUMERIC(15,2) NOT NULL DEFAULT 0,
  dep_id INTEGER NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (dep_id) REFERENCES department (id)
);

CREATE TABLE department (
  id   BIGSERIAL NOT NULL,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(100),
  note TEXT,
  PRIMARY KEY(id) 
);

And our SELECT statement could be like this:

SELECT emp.id, emp.initials, emp.gender, emp.birthday, emp.salary, emp.dep_id, dep.code, dep.name
  FROM employee emp 
    JOIN department dep ON (emp.dep_id = dep.id);

This is on the server. On the client side we would like to have a DataStore that would help us update employee table, for example. Examine the next excerpt of configuration object:

  ...
  options.table = {
      columns: [
        { name: "emp_id", type: "long", key: true, identity: true, updatable: true, dbname: "emp.id"},
        { name: "emp_initials", type: "text", updatable: true,  dbname: "emp.initials"},
        { name: "emp_gender", type: "char(1)", updatable: true, dbname: "emp.gender", initial: "M"},
        { name: "emp_birthday", type: "date", updatable: true, dbname: "birthday" },
        { name: "salary", type: "number", updatable: true, initial: 0},
        { name: "emp_dep_id", type: "long",  updatable: true, dbname: "emp.dep_id"},
        { name: "dep_code", type: "char(30)", updatable: false, dbname: "dep.code"},
        { name: "dep_name", type: "char(100)", updatable: false, dbname: "dep.name"}
      ],
      batchMethod: httpService.rpcUpdate, 
      updateTable: "my_employee_table_name",
      updateAlias: "emp"
  };
  ...

In the snippet above the value of updateTable property is selected arbitrary and doesn't equal to the database table name. The main thing is that it should be recognized on the server side. We set updatable property to true for all columns that correspondent to the employee table and false for the others. According to the dbname property, DataStore recognizes what columns belong to the updatable table. The format of the dbname property is the following: [table_alias.]column_name. If the table alias is not specified in the dbname property its value equals to the value of updateAlias (see emp_birthday column, for example). In turn, updateAlias property is also optional. If it is not specified, its value will be equal to the updateTable value that is required for the updatable DataStore (or the DataWindow control). Eventually, if the dbname property is absent in the column definition its value equals to the name property (for example, salary column).

When DataStore creates JSON-RPC requests it uses dbname value without the alias part and sets it as the key of the nested properties of the request's params value.

At least one updateable column with a truthy value of the key attribute must exists in the column list. Otherwise, the DataStore will not be available for updating.

Events

DataStore issues some events during its lifecycle, all of which are Observable objects. So, you can subscribe and unsubscribe from it at any time while the DataStore is available. Most of the events receive one object as its argument - the object of the class that is derived from DwEvent. Each dw-event class contains its own set of properties that reflect the context that was being executed at the time of their occurrence. The names and descriptions of the events are presented in the table below:

|Event | Parameter type | Description |
|:--- | :--- | :--- | |onDbCancel | void | Cancels the retrieval in process in a DataStore. Raised by dbCancel method call | |onDbError | DwDbmsErrorEvent | Triggered when an error response is received from the backend. | |onDwError | any | Raises when an internal error occurs in a data or property expression of the DataStore | |onHttpError | HttpErrorResponse | Triggered from a non-successful HTTP status, an error while executing the request, or some other failure which occurred during the parsing of the response | |onItemChanged | DwItemChangedEvent | Raised after successfull setItem method call | |onRetrieveEnd | DwRetrieveEndEvent | Occurs when the retrieval for the DataStore is complete | |onRetrieveStart| DwRetrieveStartEvent| Occurs when the retrieval for the DataWindow or DataStore is about to begin. | |onUpdateEnd | DwUpdateEndEvent | Occurs when all the updates to the database from the DataStore are complete |
|onUpdateStart | DwUpdateStartEvent | Occurs after a script calls the update method and just before changes in the DataStore are sent to the database. |

Row selection

DataStore can both highlight and remove highlightings from its rows. You can select all rows or a single row, perform a search or filter rows with the status selected and so on. Examine the following code snippet:

this.ds.selectRow(0, true); // highlights first row
if this.ds.isSelected(0) {
  console.log(this.ds.selectedCount());  // 1
}
this.ds.selectRow(0, false); // unselect the row

this.ds.selectRow(Infinity, true); // select all rows
this.ds.selectRow(Infinity, false); // remove all selections

DwScript language has a built-in IsSelected() function that you can use in your dynamic expressions like this:

let row = this.ds.find("price > 100 && IsSelected()", 0, this.ds.rowCount());
if (row >= 0) {
  ...
}

this.ds.filterValue = "IsSelected()";
this.ds.applyFilter();
// Now we have selected rows only in the primary buffer 

Grouping rows

You can group related rows together and, optionally, calculate statistics for each group separately. For example, you might want to group employee information by department and get total salaries for each department.

Each group is defined by one or more DataStore/DataWindow object columns or expressions. Each time the value in a grouping column changes, a break occurs and a new section begins.

To describe groups in your DataStore you should add group levels to the groups property of the DataStore configuration object. The group level definition contains a column name or some expression that will be evaluated over the each row of the table records. The following code snippet shows a table definition with two grouping levels, department code and employee status:

...
options.table = {
  columns: [...],
  groups: ["dep_code", "emp_status"],
  startSort: "dep_code asc, emp_status asc, emp_initials",
}
...

To correctly display grouping levels, the data should be sorted accordingly. You should call groupCalc method after change sort order or filter value of the DataStore/DataWindow data.

The DataStore's API has several methods that are designed to work with groups. Among them is findGroupChange which searches for the next break for the specified group. For example:

// This code finds the number of the row at which a break occurs in group 1. 
// It then checks whether the department number is 121. The search begins at row 0:
let found: boolean = false;
let breakRow: number = 0;

while (!found) {
    breakRow = this.ds.findGroupChange(breakRow, 1);

    // If no breaks are found, exit.
    if (breakRow < 0) break;

    // Have we found the section for Dept 121?
    if (this.ds.getItemNumber(breakRow, "dept_id") == 121) {
      found = true;
    } else {
      // Increment starting row to find next break
      breakRow = breakRow + 1;
    }
}

The DwScript language has many aggregate functions that you can use in the computed fields:

let res:number;
res = this.ds.evaluate("Sum(Round(qty*price,2), {range:'group_0'})", 0);
res = this.ds.evaluate("Count(dep_code, {range:'group_1'})", 10);
// for all rows
res = this.ds.evaluate("Max(salary, {range:'all'})", 0);
// or more concise
res = this.ds.evaluate("Max(salary)", 0); 

You should not use these functions in validation rules or filter expressions

DwScript language

DwScript is a domain-specific programming language embedded into DataStore/DataWindow functionality. The program code (expression) is parsed and evaluated dynamically at run time within some context scope. This context is always a row object that resides inside the DataStore/DataWindow's data array. Thus, any column/compute name of the DataStore`s row model is a valid identifier for the DwScript. The result of the evaluation process, if successful, is returned to the calling procedure. If a syntax error is detected during the parsing stage, a runtime error is generated.

DwScript language doesn't understand the assignment operator (=) for now, so you CANNOT change a value of any identifiers, either internals or externals.

DwScript is a JavaScript based language. It understands almost all set of the JavaScript language operators out of the box, but the names of the built-in functions, unlike the latter, do not depend on the case of characters, so for DwScript getRow, GetRow, GETROW are identifiers of the same function object. Use the syntax that suits you best.

Supported operators

|Type | Values| | --- | --- | |Arithmetic | +, -, *, /, **, % | |Relational | ==, ===, !=, !==, >, >=, <, <=, &&, ||, in, ?? | |Bitwise | &, |, ^, >>>, >>, << | |Unary | !, -, +, ~, typeof, void | |Conditional| (...) ? _ : _ | |Identifers| Model properties, functions | |Literals | string, numeric, // (RegExp), true, false, null, undefined | |Structures| Arrays, Objects |

Pre-defined functions

The following table contains the names and descriptions of DataStore/DataWindow pre-defined expression functions, that is divided by categories. The default value of the function parameter (if any) is designated as: parameter_name=...

General functions

|Function | Returns |Description | | --- | --- | --- | |Between(v, b, e)| boolean | Tests whether a value v is between b and e| |Fork(v, m, d=null) | any | Returns a key value of the map object m if it contains a key v, otherwise d| |IfNull(v, d) | any | Returns d value if v is null, else v | |IIF(b, t, f) | any | Returns t if the b is truthy, else f | |InList(v, ...a[]) | boolean | Returns true if the v is in the list a, else false | |IsNull(v) | boolean | Returns true if the v is null, else false | |IsNumber(v) | boolean | Returns true if the v is a number, else false | |NullIf(v, c) | any | Returns null if the v equals to c, else v | |String(v) | string | Converts v to string. |

String functions

|Function | Returns |Description | | --- | --- | --- | |Asc(s) | number | Converts the first character of a string s to its Unicode code point | |Char(n) | char | Converts an integer to a Unicode character| |CharAt(s, n)| char | Returns character of the string s at position n | |Dec(s) | Decimal | Converts the value of a string to a Decimal | |Decode(s, f=Base64) | string | Returns the result of decoding a string s in the format f | |Encode(s, f=Base64) | string | Returns the result of encoding a string s in the format f | |EndsWith(s, e) | boolean | Checks if a string s is ended on e| |Fill(s, n) | string | Builds a string of the specified length n by repeating the specified characters until the result string is long enough | |Format(s, ...a[]) | string | Replaces the format item in a specified template s with the text equivalent of the value in arguments array a| |Hex(n)| string | Converts number n to hexadecimal string| |Integer(s) | number | Converts string s to integer | |IsDate(s) | boolean| Tests whether a string value is a valid date| |IsNumber(s)| boolean| Reports whether the value of a string is a number| |LastToken(s, d)| string | Gets the last token of string s delimited by d| |Len(s) | number | Returns length of the string s| |Like(s, p) | boolean | Tests the string s against the pattern p, similar to the SQL LIKE function does | |Left(s, n)| string | Obtains n number of characters from the beginning of a string s| |LeftTrim(s, c=spaces)| string | Removes spaces from the beginning of a string | |Long(s) | Long | Converts the value of a string s to a Long data type | |Lower(s)| string | Converts all the characters in a string to lowercase | |Match(s, p)| boolean | Determines whether a string's value contains a particular RegExp pattern of characters| |Mid(s, f, n=all) | string | Obtains a specified number n of characters from a specified position f in a string | |Number(s)| number | Converts a string s to a number | |PadLeft(s, n, p=space)| string | Pads the left side of the string s by characer p up the length n | |PadRight(s, n, p=space)| string | Pads the right side of the string s by characer p up the length n | |Pos(s1, s2, n=0)| number | Finds one string within another string | |PosR(s1, s2, n=last)| number | Finds one string within another string starting from the end | |Real(s) | Real | Converts a string value to a Real datatype | |Repeat(s, n)| string | Repeats the string n times | |Replace(s, t, r)| string | Replaces a portion of one string with another | |Right(s, n)| string | Obtains n number of characters from the end of a string s| |RightTrim(s, c=spaces)| string | Removes spaces from the end of a string | |Space(n=1) | string | Builds a string of the specified length whose value consists of spaces | |StartsWith(s, w)| boolean | Checks if a string s is started with w string | |Substr(s, f, e)| string | Gets the substring of s starting from f position and ending on e excluding| |Token(s, d) | string | Gets the first part of the string s delimited by d| |Trim(s) | string | Removes leading and trailing spaces from a string | |TrimEnd(s) | string | Removes spaces from the end of a string | |TrimStart(s) | string | Removes spaces from the beginning of a string| |Upper(s) | string | Converts all characters in a string to uppercase letters | |WordCap(s) | string | Sets the first letter of each word in a string to a capital letter and all other letters to lowercase |

Date/Time functions

| Function | Returns | Description | | --- | --- | --- | |Date(v=now) | Date | Converts v to Date data type | |DateTime(v=now) | DateTime| Converts v to DateTime data type| |Day(d=now) | number | Obtains the day of the month in a date value| |DayName(d=now) | string | Gets the day of the week in a date value and returns the weekday's name | |DayNumber(d=now)| number | Gets the day of the week of a date value and returns the number of the weekday| |DaysAfter(d1,d2)| number |Gets the number of days one date occurs after another| |Hour(d=now) | number |Obtains the hour in a time value. The hour is based on a 24-hour clock.| |Hours(d=now) | number |Obtains the hour in a time value. The hour is based on a 24-hour clock. (The same as Hour)| |MilliSeconds(d=now) | number |Obtains the number of milliseconds in the date value| |Minutes(d=now) | number| Obtains the number of minutes in the date value| |Month(d=now) | number | Gets the month of a date value | |Now() | Date | Obtains the current time based on the system time of the client machine | |RelativeDate(d, n) | Date | Obtains the date that occurs a specified number of days n after or before another date | |RelativeTime(d, s) | Date | Obtains a time that occurs a specified number of seconds s after or before another time within a 24-hour period | |Seconds(d=now)| number | Gets the seconds of a Date object d, using local time | |SecondsAfter(d1,d2)| number | Gets the number of seconds one time occurs after another | |Time(s) | Time | Converts a string to a Time datatype | |Today() | Date | Obtains the system date and time | |TzOffset() | number | Gets the difference in minutes between the time on the local computer and Universal Coordinated Time (UTC) | |WeekDay(d=now)| number | Gets the day of the week, using local time | |YMD(y,m=0,d=1)| Date | Gets the Date object from numbers of year, month and day| |Year(d=now) | number | Gets the year of a date value |

Numeric functions

| Function | Returns | Description | | --- | --- | --- | |Abs(n)| number | Calculates the absolute value of a number | |Atan(n)| number | Calculates the arc tangent of an angle | |Ceil(n)| number | Retrieves the smallest whole number that is greater than or equal to a specified limit | |Cos(n)| number | Calculates the cosine of an angle | |Exp(n)| number | Raises e to the specified power| |Floor(n)| number | Returns the greatest integer less than or equal to its numeric argument.| |Greater(...n[])| number | Returns the larger of a set of supplied numeric expressions| |Int(n)| number | Gets the largest whole number less than or equal to a number | |Lesser(...n[])| number | Returns the smaller of a set of supplied numeric expressions| |Log(n)| number | Gets the natural logarithm of a number| |PI(n=1)| number | This is the ratio of the circumference of a circle to its diameter| |Rand(n=1)| number| Returns a pseudorandom number between 0 and 1| |Round(n, d=0)| number | Rounds a number to the specified number of decimal places| |Sign(n)| number | Reports whether the number is negative, zero, or positive by checking its sign| |Sin(n)| number |Calculates the sine of an angle| |Sqrt(n)| number | Calculates the square root of a number| |Tan(n)| number | Calculates the tangent of an angle| |Truncate(n, d=0)| number | Truncates a number to the specified number of decimal places|

DataWindow specific functions

| Function | Returns | Description | | --- | --- | --- | |Avg(x, r={range:"all"}) | number | Calculates the average of the values of the column or expression x in the specified range r of data | |Count(x, r={range:"all"}) | number | Calculates the total number of rows in the specified column or expression x in the specified range r of data| |CumulativePercent(x, r={range:"all"}) | number | Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column/expression x (a running percentage) in the specified range r of data| |CumulativeSum(x, r={range:"all"}) | number | Calculates the total value of the rows up to and including the current row in the specified column/expression x (a running total) in the specified range r of data| |CurrentRow()| number | Reports the number of the current row (the row with focus)| |Describe(...s[])| string[] | Reports the values of properties of a DataWindow object and controls within the object| |First(x, r={range:"all"}) | any | Reports the value in the first row in the specified column/expression x in the specified range r of data| |GetRow()| number | Reports the number of a row associated with a band in a DataWindow object| |IsRowModified()| boolean | Reports whether the row has been modified| |IsRowNew()| boolean| Reports whether the row has been newly inserted| |IsSelected()| boolean | Determines whether the row is selected| |Last(x, r={range:"all"}) | any | Gets the value in the last row in the specified column/expression x in the specified range r of data| |LookupDisplay(c,v)| string| Obtains the display value in the code table associated with the data value in the specified column| |Max(x, r={range:"all"}) | number | Gets the maximum value in the specified column/expression x in the specified range r of data | |Min(x, r={range:"all"}) | number | Gets the minimum value in the specified column/expression x in the specified range r of data | |Percent(x, r={range:"all"}) | number | Gets the percentage that the current value represents of the total of the values in the column/expression x in the specified range r of data| |RowCount()| number | Obtains the number of rows that are currently available in the primary buffer | |StDev(x, r={range:"all"})| number | Calculates an estimate of the standard deviation for the specified column/expression x in the specified range r of data | |StDevP(x, r={range:"all"})| number | Calculates the standard deviation for the specified column/expression x in the specified range r of data | |Sum(x, r={range:"all"}) | number | Calculates the sum of the values in the specified column/expression x in the specified range r of data| |VarE(x, r={range:"all"})| number | Calculates an estimate of the variance for the specified column/expression x in the specified range r of data | |VarP(x, r={range:"all"})| number | Calculates the variance for the specified column/expression x in the specified range r of data |

All aggregate functions of the DataStore/DataWindow ignore null values while processing the result of an expression

DataStore's API

| Method | Returns | Description | | --- | --- | --- | | applyFilter | Promise | Moves rows that do not meet the current filter criteria to the filter buffer | | applySort | Promise | Sorts the rows of the DataStore based on its current sort criteria | | create | ResultCode | Creates a DataWindow internal structure using the specified options | | dbCancel | ResultCode | Cancels a database retrieval in progress | | deletedCount | number |Returns the number of rows that have been deleted from the DataStore but have not yet been updated in the associated database table.| | deleteRow | ResultCode | Deletes the specified row from the DataStore | | describe | any[] | Returns requested information about the structure of the DataStore | | distinctValues | Set | Returns a sorted set of unique values calculated by the specified expression over each row of the DataStore | | exportToString | Promise | Saves the contents of a DataWindow or DataStore in the format you specify | | evaluate | any | Evaluates a DwScript expression for a particular data row | | filteredCount | number | Returns the number of rows that do not meet the current filter criteria | | find | number | Returns the number of the first row that meets the search criteria within a specified search range in the detail area of a DataStore | | findGroupChange | number | Searches starting at a specified row for the first break for the specified group in the DataStore | | findRequired | number | Identifies the required columns that the user has not filled | | getColumn | number | Returns the number of the current column in the DataStore | | getColumnName | string | Returns the name of the current column in the DataStore | | getItem | any | Returns the data in the specified row and column of the DataStore | | getItemDate | Date | Returns the date data in the specified row and column of the DataStore | | getItemDateTime | DateTime | Returns the datetime data in the specified row and column of the DataStore | | getItemDecimal | Decimal | Returns the decimal data in the specified row and column of the DataStore | | getItemNumber | number | Returns the numeric data in the specified row and column of the DataStore | | getItemStatus | ItemStatus | Returns the status of the item at the specified row and column location in the specified buffer | | getItemString | string | Returns the string data in the specified row and column of the DataStore | | getItemTime | Time | Returns the time data in the specified row and column of the DataStore | | getNextModified | number | Returns the number of the first row that was modified in the specified buffer in the specified DataStore after the specified row | | getRow | number | Returns an integer containing the number of the current row in the DataStore | | getRowFromRowId | number | Gets the row number of a row in a DataStore from the unique row identifier associated with that row | | getRowIdFromRow | number | Gets the unique row identifier of a row in a DataStore from the row number associated with that row | | getSelectedRow | number | Returns the number of the first selected row after the specified row number in the DataStore | | getValidate | string | Returns the validation rule used in the specified column of the DataStore | | getValue | CodeTableItem | Returns the specified item in the value list for the specified column | | groupCalc | ResultCode | Recalculates the breaks in the groups in the DataStore | | groupCount | number | Returns the number of groups that have been defined in the DataStore's configuration | | importString | Promise | Copies formatted data (CSV,TXT,JSON,XML) from a string to the DataStore | | insertRow | number | Inserts a new initialized row before the specified row in the DataStore | | isSelected | boolean | Returns true if the specified row in the DataStore is selected; returns false if the row is not selected or is greater than the number of rows in the DataStore | | modifiedCount | number | Returns the number of rows that have been modified in the DataStore but have not yet been updated in the associated database table | | modify | string | Uses the specification contained in a string to modify the DataStore | | reselectRow | Promise | Accesses the database to reselect all columns that can be updated and refreshes all timestamp columns in a row in the DataStore | | reselectRows | Promise | Accesses the database to retrieve rows with values of parameters, filter and sort criterias that is currently used in the DataStore | | reset | ResultCode | Clears all the data from a DataStore | | resetUpdate | ResultCode | Resets the update flags for the DataStore | | retrieve | Promise | Causes the DataStore to retrieve rows from the database | | rowCount | number | Returns the number of rows currently available in the DataStore (all the rows retrieved minus any deleted rows plus any inserted rows minus any rows that have been filtered out) | | rowsCopy | ResultCode | Copies a range of rows from one DataStore to another DataStore (or DataWindow control) or from one buffer to another within a single DataStore | | rowsDiscard | ResultCode | Discards a range of rows. The rows cannot be restored unless retrieved from the database | | rowsMove | ResultCode | Clears a range of rows from a DataStore and inserts the rows in another DataStore (or DataWindow control) or another buffer of the same DataStore | | selectRow | ResultCode | Selects or deselects the specified row of the DataStore | | selectedCount | number | Reports if the DataStore object has selected rows and how many | | setColumn | ResultCode | Makes the specified column the current column in the DataStore | | setItem | ResultCode | Sets the value of the specified row and column of the specified DataStore | | setItemStatus | ResultCode | Sets the status of a row in a specified column of the DataStore in the specified buffer | | setHttpService | ResultCode | Causes a DataWindow control or DataStore to use a programmer-specified http-service object | | setRow | ResultCode | Makes the specified row the current row in the DataStore | | setValidate | ResultCode | Changes the validation rule used for the specified column of the DataStore | | setValue | ResultCode | Sets the value of the specified item in the value list or the code table of the specified column of the DataStore | | shareData | ResultCode | Shares data between a primary DataStore (or DataWindow control) and a secondary DataStore (or DataWindow control) | | shareDataOff | ResultCode | Turns off sharing for the DataStore. If the DataStore is primary, all secondary DataStores (or DataWindow controls) are disconnected and their DataWindow objects no longer contain data | | update | Promise | Sends to the database all inserts, deletes, and updates of the DataStore |

More information about the methods and events of the DataStore/DataWindow class and its collaborators you can find in the *.d.ts files, which are located in the lib folder of the installed package.

CHANGELOG.md file contains the history of changes to this library.

License

MIT

Copyright

(c) 2022 ALEXANDER STRELKOV, [email protected]