@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, [e-mail]