Brief: integration

• Data import
• Data export (EXPORT)
• Integration

Data import

Flat import

The IMPORT operator creates an action that reads a file, splits it into columns (fields), and writes each of them into its own property or parameter.

IMPORT [importFormat] FROM fileExpr importDestination

The destination is specified in one of two ways (IMPORT operator):

TO [(objClassId1, objClassId2, ..., objClassIdK)] propertyId1 [= columnId1], ..., propertyIdN [= columnIdN] [WHERE whereId]
FIELDS [(objClassId1 objAlias1, objClassId2 objAlias2, ..., objClassIdK objAliasK)] propClassId1 [propAlias1 =] columnId1 [NULL], ..., propClassIdN [propAliasN =] columnIdN [NULL] [DO actionOperator [ELSE elseActionOperator]]

Rows map onto imported objects: for a numeric class the object is the row number starting from 0, for a concrete user class a new object is created per row. There is at most one such object, INTEGER named row by default. The mark of an imported row is written into the property from WHERE whereId, by default into System.imported[INTEGER] (data import).

importSkus (FILE f) {
    IMPORT XLS SHEET 2 FROM f TO field1 = C, field2, field3 = F, field4 = A;
}

Structured import and forms

Form import is the operation opposite to opening the form in the structured view: the file's values are written into the form's properties so that exporting the form back recreates the original file (Brief: data export).

IMPORT formName [importFormat] [FROM (fileExpr | groupId1 = fileExpr1 [, ..., groupIdM = fileExprM])]

The hierarchical formats (JSON, XML) are read from one file, the flat ones (CSV, XLS, DBF, TABLE) from one file per object group; the empty group is named root. Without FROM, System.importFile is used.

An imported form is restricted: objects of numeric or concrete user classes only, exactly one object per group, properties and filters changeable (as a rule, data properties). For every object read, the default value is written to each filter of its group: FILTERS order(od) = o records the link to the upper object, FILTERS imported(o) the mark of a read object; without such a filter System.imported[INTEGER] is not filled. Flat import is a special case of it, with the form built by the platform itself. The mechanism is in a structured view, and the form's views are in Brief: forms.

Formats and field mapping

A format is specified by its keyword and its own options:

JSON [ROOT rootExpr] [WHERE whereExpr] [CHARSET charsetStr]
XML [ROOT rootExpr] [ATTR] [WHERE whereExpr] [CHARSET charsetStr]
CSV [separator] [HEADER | NOHEADER] [ESCAPE | NOESCAPE] [WHERE whereExpr] [CHARSET charsetStr]
XLS [HEADER | NOHEADER] [SHEET (sheetExpr | ALL)] [WHERE whereExpr]
DBF [MEMO memoExpr] [WHERE whereExpr] [CHARSET charsetStr]
TABLE [WHERE whereExpr]

XLS reads both xls and xlsx; there is no separate XLSX keyword on import. Without an explicit format it is determined by the file's class — JSONFILE, XMLFILE, CSVFILE, EXCELFILE, DBFFILE, TABLEFILE — and for the FILE class by the extension.

The column for a property is given as = columnId, a simple name or a string literal; without it the column following the one given for the previous property is taken. In FIELDS without an alias the file's field name becomes the parameter name. WHERE whereExpr selects rows by a textual condition of the form field sign value.

Handling imported data

DO belongs to the FIELDS form: the names listed in FIELDS become local parameters, DO is executed for each imported record with those parameters in its context, and ELSE runs when no record was imported. The TO form has no DO at all — the values stay in the listed properties, and the imported rows are iterated over by the mark property.

importOrders (FILE t) {
    IMPORT FROM t FIELDS INTEGER a, DATE b, BPSTRING[50] c DO
        NEW o = Order {
            number(o) <- a;
            date(o) <- b;
            customer(o) <- c;
        }
}

Import writes values as an ordinary property change, so what is written lands in the current change session and is applied together with events and constraints.

The file itself comes from a property value: the READ operator reads it by URL, an external call returns it, or the user picks it. Recommendations for writing an import are in Rules: data import.

Data export (EXPORT)

Exporting properties and forms

The EXPORT operator creates an action that exports data to a file — either from a list of properties or from a form opened in the structured view:

EXPORT [exportFormat] FROM [columnId1 =] propertyExpr1, ..., [columnIdN = ] propertyExprN
  [WHERE whereExpr] [ORDER orderExpr1 [DESC], ..., orderExprL [DESC]]
  [TOP topExpr] [OFFSET offsetExpr] [TO propertyId]
EXPORT formName [OBJECTS objName1 = expr1, ..., objNameK = exprK] [exportFormat]
  [TOP topSelect]
  [OFFSET offsetSelect]
  [TO exportTo]

In the first form each listed expression becomes a column of the result, WHERE sets the rows, ORDER their order. In the second form the structure of the export is set by the form itself: its object groups, the properties shown for them, its filters; the objects fixed in the OBJECTS block act as additional filters. The mechanism is described in data export.

exportSkus (Store store) {
    EXPORT CSV FROM id = id(Sku s), name = name(s) WHERE in(store, s) ORDER name(s);
}

Flat and hierarchical structure

Exporting a list of properties always gives a flat result — a single table of rows.

Exporting a form carries the hierarchy of its object groups into the result, but only in the JSON and XML formats. In the flat formats (CSV, XLS, XLSX, DBF, TABLE) each object group is exported to a separate file, and exporting a form to a single file in a flat format is not supported. The objects fixed in the OBJECTS block do not participate in building the group hierarchy.

How a form turns into a data structure is described in the structured view.

Formats and options

The format is written before the list of exported data as one of the variants of the EXPORT operator:

JSON [CHARSET charsetStr]
XML [HEADER | NOHEADER] [ROOT rootExpr] [TAG tagExpr] [ATTR] [CHARSET charsetStr]
CSV [separator] [HEADER | NOHEADER] [ESCAPE | NOESCAPE] [CHARSET charsetStr]
XLS [SHEET sheetExpr] [HEADER | NOHEADER]
XLSX [SHEET sheetExpr] [HEADER | NOHEADER]
DBF [CHARSET charsetStr]
TABLE

Format	Options and their defaults
JSON	CHARSET — UTF-8
XML	HEADER — the <?xml ...?> line; ROOT — the root element; TAG — the record element; ATTR — values as attributes; CHARSET — UTF-8
CSV	separator — ;; NOHEADER; ESCAPE; CHARSET — UTF-8
XLS, XLSX	SHEET — the sheet name; NOHEADER
DBF	CHARSET — CP1251
TABLE	none

Where the result goes

The TO block sets the property without parameters, of a file class (FILE, RAWFILE, JSONFILE and so on), that the result is written to. When a list of properties is exported, and when a form is exported to a hierarchical format (JSON, XML), a single file is produced, and without TO it goes into the System.exportFile property. When the value class of the destination is FILE, the file extension matches the name of the format in lower case (json, xml, csv, xls, xlsx, dbf, table).

When a form is exported to a flat format, each object group produces its own file, so the destination is set separately for each group — TO groupId1 = propertyId1, .... Groups not listed are not exported: there is no System.exportFile fallback here. The empty object group is named root.

exportSku (Store store) {
    LOCAL exportedFile = FILE ();
    // flat format: the destination is set for the object group s of the exportSku form
    EXPORT exportSku OBJECTS st = store DBF CHARSET 'CP866' TO s = exportedFile;
}

Defaults that shape the result

• The format, if not specified, is JSON.
• WHERE, if not specified, is the disjunction of all exported properties: the exported object sets are those for which at least one of them is not NULL.
• Column names, if not set, are expr1, ..., exprN by the position of the expression in the list.
• A NULL value is omitted from the record in JSON and XML (the key or the element is absent) and is written as an empty cell in the flat formats; the record itself remains while the WHERE condition holds.
• ORDER takes arbitrary expressions: an expression that is not among the exported ones is added to the internal query as a hidden column and does not appear in the result.
• Exporting a single value without a column name gives the value itself in JSON, not an object with a field.

Integration

Access from outside

From outside, an action is called with parameters, and the values of the listed properties without parameters come back as the result. The action is specified in one of three ways: EXEC — by name, EVAL and EVAL ACTION — by supplied lsFusion code (from inside, the same code is run by the EVAL operator).

Requests are served by the application server (port 7651) and by the web server:

• Action API — /exec, /eval, /eval/action, both servers;
• Form API — /form, web server only: driving a form in the interactive view;
• File API — /files/list, /files/read, /files/search, web server only: reading the application's classpath.

Whether such a call is accepted at all is set by a working parameter — enableAPI for the Action and File APIs, enableUI for the Form API, which the platform counts as user interface rather than as a program interface. An action that turns out to be interactive is routed to a running client of the user, and needs enableUI on top of the enableAPI check. The @@api action option allows one specific action at enableAPI=0 (an authenticated user is still required), and @@noauth bypasses both the authentication check and enableAPI. The annotation marks a named action, so it does not extend to /eval and /eval/action, which run arbitrary code, nor to the File API, where no action is called. The mechanism is access from an external system.

From the same JVM or the same SQL server the elements are reached directly — by Java code (Java API for integrations) or by SQL against the platform's tables (access from an internal system).

External calls (EXTERNAL)

The EXTERNAL operator creates an action performing a single call to an external system: parameters go in PARAMS, results into the properties without parameters listed in TO.

EXTERNAL externalCall [PARAMS paramExpr1, ..., paramExprN] [TO propertyId1, ..., propertyIdM]

externalCall:

HTTP [CLIENT] [requestType] connectionStrExpr httpOption1 ... httpOptionN
TCP [CLIENT] connectionStrExpr
UDP [CLIENT] connectionStrExpr
SQL connectionStrExpr EXEC execStrExpr
LSF connectionStrExpr lsfExecType execStrExpr
DBF connectionStrExpr APPEND [CHARSET charsetLiteral]

HTTP is a request to the given string, TCP and UDP send a file's bytes to a socket, SQL runs a command on a third-party SQL server, LSF calls an action on another lsFusion server, DBF appends table rows to a .dbf file (access to an external system). SQL, TCP and DBF connections are reused inside a NEWCONNECTION block; a file is fetched by URL with the READ operator.

readRate () {
    EXTERNAL HTTP GET r'https://www.lsfusion.org/rate?cur=$1' PARAMS r'USD' TO exportFile;
}

Internal calls (INTERNAL)

The INTERNAL operator runs code inside the deployment's own components: Java in the application-server JVM, a JavaScript function or a resource in the user's web client (CLIENT), SQL against the platform's own database (DB).

INTERNAL [CLIENT] [syncType] className [(classId1, ..., classIdN)] [NULL]
INTERNAL [syncType] <{anyTokens}> [NULL]
INTERNAL internalCall [PARAMS paramExpr1, ..., paramExprN] [TO propertyId1, ..., propertyIdM]

The Java target is a class extending InternalAction, given by its name or as an inline code fragment in <{ }>. Such code writes values straight into lsFusion properties within the same change session; what it can reach is in Java API for integrations. The mechanism of all three types is internal call (INTERNAL), and the operator's place next to FORMULA is access to an internal system.

setNoCancelInTransaction() INTERNAL <{ context.getSession().setNoCancelInTransaction(true); }>;

Declarative integration (FORMULA, CUSTOM, JSON)

The FORMULA operator creates a property computed by an SQL expression, possibly a different one per DBMS; the table-valued form maps the property onto a whole table (custom formula).

FORMULA [NULL] [className [valueId]] implList [( paramList )] [NULL]

CUSTOM hands rendering to a JavaScript function in the client: on an object group as CUSTOM renderFunction [OPTIONS optionsExpr] (object blocks), on a property as CUSTOM renderFunction [CHANGE [editFunction]] (properties and actions block). The function is given the view's own local controller, which reads and changes what this view shows; the form controller — and with it the server calls — is reached from it as controller.form (How-to: Custom Components).

The JSON and JSONTEXT operators create a property building JSON out of a list of properties or out of a form.

jsonKeyword FROM [columnId1 =] propertyExpr1, ..., [columnIdN =] propertyExprN
  [WHERE whereExpr]
  [ORDER orderExpr1 [DESC], ..., orderExprL [DESC]]
  [TOP topExpr] [OFFSET offsetExpr]
jsonKeyword ( formName [OBJECTS objName1 = expr1, ..., objNameK = exprK]
  [FILTERS filterExpr1, ..., filterExprP]
  [TOP topSelect] [OFFSET offsetSelect] )

jsonKeyword is JSON or JSONTEXT.