DEV Community: Eduard

How to work with InterSystems IRIS from DataGrip

Eduard — Mon, 24 Jan 2022 19:52:30 +0000

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

2. Go to + → Driver

3. Set Driver properties:

Name: InterSystems IRIS
Class: com.intersystems.jdbc.IRISDriver
Add JDBC Driver file: path to /<IRIS>/dev/java/lib/JDK18/intersystems-jdbc-3.2.0.jar (version can change, also if you don't have InterSystems IRIS installed, you can download the drivers here)
Add URL Template: Basic jdbc:IRIS://{host}[:{port}]/{database} (you can add additional parameters as described here)

4. Click OK to save the driver definition.

Part 2: Add InterSystems IRIS Instance

1. In the same DataSources window go to +→InterSystems IRIS

2. Specify connection settings depending on your instance and click Test Connection. You'll see an error or connection successful.

It it's a new local installation the defaults likely are:

Host: localhost
Port: 1972
User: _SYSTEM
Password: SYS
Database: USER

3. Click OK to save.

That's it! Now you can query and explore InterSystems IRIS through Datagrip.

Deploying a sharded InterSystems IRIS cluster with Docker and MergeCPF

Eduard — Thu, 14 Oct 2021 16:17:27 +0000

In this article, we will run an InterSystems IRIS cluster using docker and Merge CPF files - a new feature allowing you to configure servers with ease.

On UNIX® and Linux, you can modify the default iris.cpf using a declarative CPF merge file. A merge file is a partial CPF that sets the desired values for any number of parameters upon instance startup. The CPF merge operation works only once for each instance.

Our cluster architecture is very simple, it would consist of one Node1 (master node) and two Data Nodes (check all available roles). Unfortunately, docker-compose cannot deploy to several servers (although it can deploy to remote hosts), so this is useful for local development of sharding-aware data models, tests, and such. For a productive InterSystems IRIS Cluster deployment, you should use either ICM or IKO.

Docker-compose.yml

Let's start with docker-compose configuration:

docker-compose.yml

version: '3.7'
services:
  iris1:
    image: containers.intersystems.com/intersystems/iris:2020.3.0.221.0
    init: true
    command: --key /ISC/iris.key
    hostname: iris1
    environment:
     - ISC_DATA_DIRECTORY=/ISC/iris.sys.d/sys1
     - ISC_CPF_MERGE_FILE=/ISC/CPF2merge-master-instance.conf
    volumes:
     - ./:/ISC:delegated
    ports:
      - 9011:1972
      - 9012:52773

  iris2:
    image: containers.intersystems.com/intersystems/iris:2020.3.0.221.0
    command: --key /ISC/iris.key --before 'sleep 60'
    init: true
    hostname: iris2
    environment:
     - ISC_DATA_DIRECTORY=/ISC/iris.sys.d/sys2
     - ISC_CPF_MERGE_FILE=/ISC/CPF2merge-data-instance.conf
    volumes:
     - ./:/ISC:delegated
    depends_on:
      - iris1
    ports:
      - 9021:1972
      - 9022:52773

  iris3:
    image: containers.intersystems.com/intersystems/iris:2020.3.0.221.0
    command: --key /ISC/iris.key --before 'sleep 60'
    init: true
    hostname: iris3
    environment:
     - ISC_DATA_DIRECTORY=/ISC/iris.sys.d/sys3
     - ISC_CPF_MERGE_FILE=/ISC/CPF2merge-data-instance.conf
    volumes:
     - ./:/ISC:delegated
    depends_on:
      - iris1
    ports:
      - 9031:1972
      - 9032:52773

As you can see we're running a default intersystems/iris:2020.3.0.221.0 image, providing the license key (it must support sharding), persisting the data using Durable %SYS feature, and provide ISC_CPF_MERGE_FILE pointing at our merge files (which are different for Node1 and Data Nodes). Additionally Data Nodes are started a minute late allowing Node1 to start and that's an extremely conservative estimate, on a decent hardware startup time takes seconds tops.

Cluster configuration happens at CPF merge files, let's check them out

CPF2merge-data-instance.conf

[Startup]
PasswordHash=FBFE8593AEFA510C27FD184738D6E865A441DE98,u4ocm4qh
ShardRole=node1

[config]
MaxServerConn=64
MaxServers=64
globals=0,0,400,0,0,0
errlog=1000
routines=32
gmheap=256000
locksiz=1179648

What happens here?

In the [Startup] part we enable Sharding by assigning Node1 role to our cluster. And in [config]we expand our server a bit allowing more caches and connections. That's all!

CPF2merge-data-instance.conf

[Startup]

ShardClusterURL=IRIS://iris1:1972/IRISCLUSTER
ShardRole=DATA

For data nodes, we need to provide the URL of the Node1 and the node role.

Try it

Check the repository or run this code:

git clone https://github.com/intersystems-ru/iris-container-recipes.git
cd iris-container-recipes
cd cluster
// copy iris.key in cluster folder
docker-compose up -d

After starting the InterSystems IRIS cluster you can access it from the browser. User/pass is: _SYSTEM/SYS.

Conclusions

Merge CPF files is a great and simple tool allowing you to configure InterSystems IRIS instances.

Continuous Delivery of your InterSystems solution using GitLab - Part VIII: CD using ICM

Eduard — Mon, 23 Aug 2021 11:45:59 +0000

In this series of articles, I'd like to present and discuss several possible approaches toward software development with InterSystems technologies and GitLab. I will cover such topics as:

Git 101
Git flow (development process)
GitLab installation
GitLab Workflow
Continuous Delivery
GitLab installation and configuration
GitLab CI/CD
Why containers?
Containers infrastructure
CD using containers
CD using ICM

In this article, we'll build Continuous Delivery with InterSystems Cloud Manager. ICM is a cloud provisioning and deployment solution for applications based on InterSystems IRIS. It allows you to define the desired deployment configuration and ICM would provision it automatically. For more information take a look at First Look: ICM.

Workflow

In our Continuous Delivery configuration we would:

Push code into GitLab repository
Build docker image
Publish image to docker registry
Test it on a test server
If tests pass, deploy on a production server

Or in graphical format:

As you can see it's all pretty much the same except we would be using ICM instead of managing Docker containers manually.

ICM configuration

Before we can start upgrading containers, they should be provisioned. For that we need to define defaults.json and definitions.json, describing our architecture. I'll provide these 2 files for a LIVE server, definitions for a TEST server are the same, defaults are the same except for Tag and SystemMode values.

defaults.json:

{
    "Provider": "GCP",
    "Label": "gsdemo2",
    "Tag": "LIVE",
    "SystemMode": "LIVE",
    "DataVolumeSize": "10",
    "SSHUser": "sample",
    "SSHPublicKey": "/icmdata/ssh/insecure.pub",
    "SSHPrivateKey": "/icmdata/ssh/insecure",
    "DockerImage": "eduard93/icmdemo:master",
    "DockerUsername": "eduard93",
    "DockerPassword": "...",
    "TLSKeyDir": "/icmdata/tls",
    "Credentials": "/icmdata/gcp.json",
    "Project": "elebedyu-test",
    "MachineType": "n1-standard-1",
    "Region": "us-east1",
    "Zone": "us-east1-b",
    "Image": "rhel-cloud/rhel-7-v20170719",
    "ISCPassword": "SYS",
    "Mirror": "false"
}

definitions.json

[
    {
    "Role": "DM",
    "Count": "1",
    "ISCLicense": "/icmdata/iris.key"
    }
]

Inside the ICM container /icmdata folder is mounted from the host and:

TEST server definitions are placed in /icmdata/test folder
LIVE server definitions are placed in /icmdata/live folder

After obtaining all required keys:

keygenSSH.sh /icmdata/ssh
keygenTLS.sh /icmdata/tls

And placing required files in /icmdata:

iris.key
gcp.json (for deployment to Google Cloud Platform)

Call ICM to provision your instances:

cd /icmdata/test
icm provision
icm run
cd /icmdata/live
icm provision
icm run

This would provision one TEST and one LIVE server with one standalone InterSystems IRIS instance on each.

Please refer to ICM First Look for a more detailed guide.

Build

First, we need to build our image.

Our code would be, as usual, stored in the repository, CD configuration in gitlab-ci.yml but in addition (to increase security) we would store several server-specific files on a build server.

iris.key

License key. Alternatively, it can be downloaded during container build rather than stored on a server. It's rather insecure to store in the repository.

pwd.txt

File containing default password. Again, it's rather insecure to store it in the repository. Also, if you're hosting prod environment on a separate server it could have a different default password.

load_ci_icm.script

Initial script, it:

Loads installer
Installer does application initialization
Loads the code


set dir = ##class(%File).NormalizeDirectory($system.Util.GetEnviron("CI_PROJECT_DIR"))
do ##class(%SYSTEM.OBJ).Load(dir _ "Installer/Global.cls","cdk")
do ##class(Installer.Global).init()
halt

Note that the first line is intentionally left empty.

Several things are different from previous examples. First of all we are not enabling OS Authentication as ICM would interact with container instead of GitLab directly. Second of all I'm using Installer manifest to initialize our application to show different approaches to initialization. Read more on Installer in this article. Finally we'll publish our image in a Docher Hub as a private repo.

Installer/Global.cls

Our installer manifest looks like this:

<Manifest>
    <Log Text="Creating namespace ${Namespace}" Level="0"/>
    <Namespace Name="${Namespace}" Create="yes" Code="${Namespace}" Ensemble="" Data="IRISTEMP">
        <Configuration>
            <Database Name="${Namespace}" Dir="${MGRDIR}/${Namespace}" Create="yes" MountRequired="true" Resource="%DB_${Namespace}" PublicPermissions="RW" MountAtStartup="true"/>
        </Configuration>

        <Import File="${Dir}MyApp" Recurse="1" Flags="cdk" IgnoreErrors="1" />
    </Namespace>

    <Log Text="Mapping to USER" Level="0"/>
    <Namespace Name="USER" Create="no" Code="USER" Data="USER" Ensemble="0">
        <Configuration>
            <Log Text="Mapping MyApp package to USER namespace" Level="0"/>
            <ClassMapping From="${Namespace}" Package="MyApp"/>
        </Configuration>

        <CSPApplication  Url="/"      Directory="${Dir}client" AuthenticationMethods="64" IsNamespaceDefault="false" Grant="%ALL"  />
        <CSPApplication  Url="/myApp" Directory="${Dir}"       AuthenticationMethods="64" IsNamespaceDefault="false" Grant="%ALL"  />
    </Namespace>
</Manifest>

And it implements the following changes:

Creates application Namespace.
Creates application code database (data would be stored in USER database).
loads code into application code database.
Maps MyApp package to USER namespace.
Creates 2 web applications: for HTML and for REST.

gitlab-ci.yml

Now, to Continuous Delivery configuration:

build image:
  stage: build
  tags:
    - master
  script:
    - cp -r /InterSystems/mount ci
    - cd ci
    - echo 'SuperUser' | cat - pwd.txt load_ci_icm.script > temp.txt
    - mv temp.txt load_ci.script
    - cd ..
    - docker build --build-arg CI_PROJECT_DIR=$CI_PROJECT_DIR -t eduard93/icmdemo:$CI_COMMIT_REF_NAME .

What is going on here?

First of all, as docker build can access only subdirectories of a base build directory - in our case repository root, we need to copy our "secret" directory (the one with iris.key, pwd.txt and load_ci_icm.script) into the cloned repository.

Next, first terminal access requires a user/pass so we'd add them to load_ci.script (that's what empty line at the beginning of load_ci.script is for btw).

Finally, we build docker image and tag it appropriately: eduard93/icmdemo:$CI_COMMIT_REF_NAME

where $CI_COMMIT_REF_NAME is the name of a current branch. Note that the first part of the image tag should be named same as project name in GitLab, so it could be seen in GitLab Registry tab (instructions on tagging are available in Registry tab).

Dockerfile

Building a docker image is done using Dockerfile, here it is:

FROM intersystems/iris:2018.1.1-released

ENV SRC_DIR=/tmp/src
ENV CI_DIR=$SRC_DIR/ci
ENV CI_PROJECT_DIR=$SRC_DIR

COPY ./ $SRC_DIR

RUN cp $CI_DIR/iris.key $ISC_PACKAGE_INSTALLDIR/mgr/ \
 && cp $CI_DIR/GitLab.xml $ISC_PACKAGE_INSTALLDIR/mgr/ \
 && $ISC_PACKAGE_INSTALLDIR/dev/Cloud/ICM/changePassword.sh $CI_DIR/pwd.txt \
 && iris start $ISC_PACKAGE_INSTANCENAME \
 && irissession $ISC_PACKAGE_INSTANCENAME -U%SYS < $CI_DIR/load_ci.script \
 && iris stop $ISC_PACKAGE_INSTANCENAME quietly

We start from the basic iris container.

First of all, we copy our repository (and "secret" directory) inside the container.

Next, we copy license key to mgr directory.

Then we change the password to the value from pwd.txt. Note that pwd.txt is deleted in this operation.

After that, the instance is started and load_ci.script is executed.

Finally, iris instance is stopped.

Note that I'm using GitLab Shell executor and not Docker executor. Docker executor is used when you need something from inside of the image, for example, you're building an Android application in java container and you only need an apk. In our case, we need a whole container and for that, we need Shell executor. So we're running Docker commands via GitLab Shell executor.

Publish

Now, let's publish our image in a Docker Hub

publish image:
  stage: publish
  tags:
    - master
  script:
    - docker login -u eduard93 -p ${DOCKERPASSWORD}
    - docker push eduard93/icmdemo:$CI_COMMIT_REF_NAME

Note ${DOCKERPASSWORD} variable, it's a GitLab secret variable. We can add them in GitLab > Project > Settings > CI/CD > Variables:

Job logs also do not contain password value:

Running with gitlab-runner 10.6.0 (a3543a27)
  on icm 82634fd1
Using Shell executor...
Running on docker...
Fetching changes...
Removing ci/
HEAD is now at 8e24591 Add deploy to LIVE
Checking out 8e245910 as master...
Skipping Git submodules setup
$ docker login -u eduard93 -p ${DOCKERPASSWORD}
WARNING! Using --password via the CLI is insecure. Use --password-stdin.
Login Succeeded
$ docker push eduard93/icmdemo:$CI_COMMIT_REF_NAME
The push refers to repository [docker.io/eduard93/icmdemo]
master: digest: sha256:d1612811c11154e77c84f0c08a564a3edeb7ddbbd9b7acb80754fda97f95d101 size: 2620
Job succeeded

and on Docker Hub we can see our new image:

Run

We have our image, next let's run it on our test server. Here is the script.

run image:
  stage: run
  environment:
    name: $CI_COMMIT_REF_NAME
  tags:
    - master
  script:
    - docker exec icm sh -c "cd /icmdata/test && icm upgrade -image eduard93/icmdemo:$CI_COMMIT_REF_NAME"

With ICM we need to run only one command (icm upgrade) to upgrade existing deployment. We're calling it by running docker exec icm sh -c ... which executes a specified command inside the icm container. First we mode into /icmdata/test where our ICM deployment definition is defined for a TEST server. After that we call icm upgrade to replace currently existing container with a new container.

Test

Let's run some tests.

test image:
  stage: test
  tags:
    - master
  script:
    - docker exec icm sh -c "cd /icmdata/test && icm session -namespace USER -command 'do \$classmethod(\"%UnitTest.Manager\",\"RunTest\",\"MyApp/Tests\",\"/nodelete\")' | tee /dev/stderr | grep 'All PASSED' && exit 0 || exit 1"

Again, we're executing one command inside our icm container. icm session executes a command on a deployed node. The command runs unit tests. After that it pipes all output to the screen and also to grep to find Unit Tests results and exit the process successfully or with an error.

Deploy

Deploy on a Production server is absolutely the same as deploy on test, except for another directory for the LIVE deployment definition. In the case where tests failed this stage would not be executed.

deploy image:
  stage: deploy
  environment:
    name: $CI_COMMIT_REF_NAME
  tags:
    - master
  script:
    - docker exec icm sh -c "cd /icmdata/live && icm upgrade -image eduard93/icmdemo:$CI_COMMIT_REF_NAME"

Conclusion

ICM gives you a simple, intuitive way to provision cloud infrastructure and deploy services on it, helping you get into the cloud now without major development or retooling. The benefits of infrastructure as code (IaC) and containerized deployment make it easy to deploy InterSystems IRIS-based applications on public cloud platforms such as Google, Amazon, and Azure, or on your private VMware vSphere cloud. Define what you want, issue a few commands, and ICM does the rest.

Even if you are already using cloud infrastructure, containers, or both, ICM dramatically reduces the time and effort required to provision and deploy your application by automating numerous otherwise manual steps.

Links

Importing code into InterSystems IRIS from external libraries

Eduard — Tue, 13 Jul 2021 11:47:15 +0000

Intro

Recently I reread this article by @Bernd.Mueller. It's about calling DELFATE function from zlib library. In this article I'll demonstrate several different approaches to callout libraries, we'll build the same functionality (compress function) in several different languages and compare them.

NodeJS

Let's start with NodeJS. I'm taking the code almost directly from Bernd's article, except it does not use files, but rather direct http connection to pass data. For production use, it would be better to pass request as a body and encode both request and response as base64. Still, here's the code:

//zlibserver.js
const express = require('express');
const zlib = require('zlib');

var app = express();

 app.get('/zlibapi/:text', function(req, res) {
    res.type('application/json');

    var text=req.params.text;

    try {        
        zlib.deflate(text, (err, buffer) => {
           if (!err) {
                res.status(200).send(buffer.toString('binary'));
            } else {
                res.status(500).json( { "error" : err.message});
            // handle error
            }
        });
     }
    catch(err) {
      res.status(500).json({ "error" : err.message});
      return;
    }

});
app.listen(3000, function(){
    console.log("zlibserver started");
});

To start it execute in OS bash (assuming node and npm installed):

cd <repo>\node
npm install
node  ./zlibserver.js

We're running on port 3000, reading input string from request and returning compressed data in response as is. On a Caché side http request is used to interact with this api:

/// NodeJS implementation
/// do ##class(isc.zlib.Test).node()
ClassMethod node(text As %String = "Hello World", Output response As %String) As %Status
{
    kill response
    set req = ##class(%Net.HttpRequest).%New()
    set req.Server = "localhost"
    set req.Port = 3000
    set req.Location = "/zlibapi/" _ text
    set sc = req.Get(,,$$$NO)
    quit:$$$ISERR(sc) sc
    set response = req.HttpResponse.Data.Read($$$MaxStringLength)
    quit sc
}

Note, that I'm setting the third argument set sc = req.Get(,,$$$NO) - reset to zero. If you're writing interface to the outside http(s) server it's best to reuse one request object and just modify it as needed to perform new requests.

Java

Java Gateway allows calling arbitrary Java code. Coincidently Java has Deflater class which does exactly what we need:

package isc.zlib;

import java.util.Arrays;
import java.util.zip.Deflater;

public abstract class Java {

    public static byte[] compress(String inputString) {
        byte[] output = new byte[inputString.length()*3];
        try {
            // Encode a String into bytes
            byte[] input = inputString.getBytes("UTF-8");

            // Compress the bytes

            Deflater compresser = new Deflater();
            compresser.setInput(input);
            compresser.finish();
            int compressedDataLength = compresser.deflate(output);
            compresser.end();
            output = Arrays.copyOfRange(output, 0, compressedDataLength);

        } catch (java.io.UnsupportedEncodingException ex) {
            // handle
        }


        return output;
    }
}

The problem with this implementation is that it returns byte[] which becomes a Stream on Caché side. I have tried to return a string, but hadn't been able to found how to form proper binary string from byte[]. If you have any ideas please leave a comment.
To run it place jar from releases page into <instance>/bin folder, load ObjectScript code into your instance and execute:

write $System.Status.GetErrorText(##class(isc.zlib.Utils).createGateway())
write $System.Status.GetErrorText(##class(isc.zlib.Utils).updateJar())

Check createGateway method before running the command. Second argument javaHome assumes that JAVA_HOME environment variable is set. If it does not, specify home of Java 1.8 JRE as a second argument. After installing run this code to get compressed text:

set gateway = ##class(isc.zlib.Utils).connect()
set response = ##class(isc.zlib.Java).compress(gateway, text)

C

An InterSystems Callout library is a shared library that contains your custom Callout functions and the enabling code that allows Caché to use them.

Here's our Callout library:

#define ZF_DLL

// Ugly Windows hack
#ifndef ulong
   typedef unsigned long ulong;
#endif

#include "string.h"
#include "stdio.h"
#include "stdlib.h"
#include "zlib.h"
#include <cdzf.h>

int Compress(char* istream, CACHE_EXSTRP retval)
{
    ulong srcLen = strlen(istream)+1;      // +1 for the trailing `\0`
    ulong destLen = compressBound(srcLen); //  estimate size needed for the buffer
    char* ostream = malloc(destLen);
    int res = compress(ostream, &destLen, istream, srcLen);
    CACHEEXSTRKILL(retval);
    if (!CACHEEXSTRNEW(retval,destLen)) {return ZF_FAILURE;}
    memcpy(retval->str.ch,ostream,destLen);   // copy to retval->str.ch
    return ZF_SUCCESS;
}

ZFBEGIN
    ZFENTRY("Compress","cJ",Compress)
ZFEND

To run it place dll or so files from releases page into <instance>/bin folder. Repository also contains build scripts for Windows and Linux, execute them to build your own version.
Linux prerequisites:

apt install build-essential zlib1g zlib1g-devel

Windows prerequisites: WinBuilds - comes with zlib.

To interact with callout library execute:

set path =  ##class(isc.zlib.Test).getLibPath() //get path to library file
set response = $ZF(-3, path, "Compress", text)       // execute function
do $ZF(-3, "")                                  //unload library

System

A little unexpected in an article about callout mechanisms, but Caché also has built-in Compress (and Decompress function). Call it with:

set response = $extract($SYSTEM.Util.Compress(text), 2, *-1)

Remember that searching the docs or asking the questions here on the community may save you some time.

Comparison

I have run simple tests (1Kb text, 1 000 000 iterations) on Linux and Windows and got these results.

Windows:

Method	Callout	System	Java	Node
Time	22,77	33,41	152,73	622,51
Speed (Kb/s)	43912	29927	6547	1606
Overhead, %	-/-	46,73%	570,75%	2633,90%

Linux:

Method	Callout	System	Java	Node
Time	76,35	76,49	147,24	953,73
Speed (Kb/s)	13097	13072	6791	1049
Overhead, %	-/-	0,19%	92%	1149%

To run tests load code and call:

do ##class(isc.zlib.Test).test(textLength, iterations)

Conclusion

With InterSystems products, you can easily leverage existing code in other languages. However, choosing correct implementation is not always easy, you need to take several metrics into account, such as development speed, performance, and maintainability. Do you need to run on different operating systems? Finding answers to these questions can help you decide on the best implementation plan.

Class Queries in InterSystems IRIS

Eduard — Wed, 03 Mar 2021 11:04:30 +0000

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

Basic class queries

Simply put, basic class queries allow you to represent SQL SELECT queries. SQL optimizer and compiler handle them just as they would standard SQL queries, but they are more convenient when it comes to executing them from Caché Object Script context. They are declared as Query items in class definitions (similar to Method or Property) in the following way:

Type: %SQLQuery
All arguments of your SQL query must be listed in the list of arguments
Query type: SELECT
Use the colon to access each argument (similar to static SQL)
Define the ROWSPEC parameter which contains information about names and data types of the output results along with the order of fields
(Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't need to return ID, don't assign any values to CONTAINID
(Optional) Define the COMPILEMODE parameter which corresponds to the similar parameter in static SQL and specifies when the SQL expression must be compiled. When this parameter is set to IMMEDIATE (by default), the query will be compiled simultaneously with the class. When this parameter is set to DYNAMIC, the query will be compiled before its first execution (similar to dynamic SQL)
(Optional) Define the SELECTMODE parameter which specifies the format of the query results
Add the SqlProc property, if you want to call this query as an SQL procedure.
Set the SqlName property, if you want to rename the query. The default name of a query in SQL context is as follows: PackageName.ClassName_QueryName
Caché Studio provides the built-in wizard for creating class queries\ Sample definition of the Sample.Person class with the ByName query which returns all user names that begin with a specified letter

Class Sample.Person Extends %Persistent
{
Property Name As %String;
Property DOB As %Date;
Property SSN As %String;
Query ByName(name As %String = "") As %SQLQuery
    (ROWSPEC="ID:%Integer,Name:%String,DOB:%Date,SSN:%String",
     CONTAINID = 1, SELECTMODE = "RUNTIME",
     COMPILEMODE = "IMMEDIATE") [ SqlName = SP_Sample_By_Name, SqlProc ]
{
SELECT ID, Name, DOB, SSN
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}
}

You can call this query from Caché Object Script in the following way:

Set statement=##class(%SQL.Statement).%New()
Set status=statement.%PrepareClassQuery("Sample.Person","ByName")
If $$$ISERR(status) {
    Do $system.OBJ.DisplayError(status)
}
Set resultset=statement.%Execute("A")
While resultset.%Next() {
    Write !, resultset.%Get("Name")
}

Alternatively, you can obtain a resultset using the automatically generated method queryNameFunc:

Set resultset = ##class(Sample.Person).ByNameFunc("A")
While resultset.%Next() {
    Write !, resultset.%Get("Name")
}

This query can also be called from SQLcontext in these two ways:

Call Sample.SP_Sample_By_Name('A')
Select * from Sample.SP_Sample_By_Name('A')

This class can be found in the SAMPLES default Caché namespace And that's all about simple queries. Now let's proceed to custom ones.

Custom class queries

Though basic class queries work fine in most cases, sometimes it is necessary to execute full control over the query behavior in applications, e.g.:

Sophisticated selection criteria. Since in custom queries you implement a Caché Object Script method that returns the next row on your own, these criteria can be as sophisticated as you require.
If data is accessible only via API in a format that you don't want to use
If data is stored in globals (without classes)
If you need to escalate rights in order to access data
If you need to call an external API in order to access data
If you need to gain access to the file system in order to access data
You need to perform additional operations before running the query (e.g. establish a connection, check permissions, etc.)

So, how do you create custom class queries? First of all, you should define 4 methods which implement the entire workflow for your query, from initialization to destruction:

queryName --- provides information about a query (similar to basic class queries)
queryName*Execute* --- constructs a query
queryName*Fetch* --- obtains the next row result of a query
queryName*Close* --- destructs a query

Now let's analyze these methods in more detail.

The queryName method

The queryName method represents information about a query.

Type: %Query
Leave body blank
Define the ROWSPEC parameter which contains the information about names and data types of the output results along with the field order
(Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't return ID, don't assign any value to CONTAINID

For example, let's create the AllRecords query (queryName = AllRecords, and the method is simply called AllRecords) which will be outputting all instances of the new persistent class Utils.CustomQuery, one by one. First, let's create a new persistent class Utils.CustomQuery:

Class Utils.CustomQuery Extends (%Persistent, %Populate){
Property Prop1 As %String;
Property Prop2 As %Integer;
}

Now let's write the AllRecords query:

Query AllRecords() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllRecords, SqlProc ]
{
}

The queryNameExecute method\
The queryNameExecute method fully initializes a query. The signature of this method is as follows:

ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status

where:

qHandle is used for communication with other methods of the query implementation
This method must set qHandle into the state which will then be passed to the queryNameFetch method
qHandle can be set to OREF, variable or a multi-dimensional variable
args are additional parameters passed to the query. You can add as many args as you need (or don't use them at all)
The method must return query initialization status

But let's get back to our example. You are free to iterate through the extent in multiple ways (I will describe the basic working approaches for custom queries below), but as for this example let's iterate through the global using the $Order function. In this case, qHandle will be storing the current ID, and since we don't need any additional arguments, the arg argument is not required. The result looks like this:

ClassMethod AllRecordsExecute(ByRef qHandle As %Binary) As %Status {
    Set qHandle = ""    Quit $$$OK
}

The queryNameFetch method\
The queryNameFetch method returns a single result in $List form. The signature of this method is as follows:

ClassMethod queryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ]

where:

qHandle is used for communication with other methods of the query implementation
When the query is executed, qHandle is being assigned values specified by queryNameExecute or by previous call of queryNameFetch.
Row will be set either to a value of %List or to an empty string, if all data has been processed
AtEnd must be set to 1, once the end of data is reached.
The PlaceAfter keyword identifies the method's position in the int code . The "Fetch" method must be positioned after the "Execute" method, but this is important only for static SQL, i.e. cursors inside queries.

In general, the following operations are performed within this method:

Check whether we've reached the end of data
If there is still some data left: Create a new %List and assign a value to the Row variable
Otherwise, set AtEnd to 1
Prepare qHandle for the next result fetch
Return the status

This is how it will look like in our example:

ClassMethod AllRecordsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status {
    #; Iterating through ^Utils.CustomQueryD
    #; Writing the next id to qHandle and writing the global's value with the new id into val
    Set qHandle = $Order(^Utils.CustomQueryD(qHandle),1,val)
    #; Checking whether there is any more data left
       If qHandle = "" {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    }
    #; If not, create %List
    #; val = $Lb("", Prop1, Prop2) see Storage definition
    #; Row =$lb(Id,Prop1, Prop2)  see ROWSPEC for the AllRecords request
    Set Row = $Lb(qHandle, $Lg(val,2), $Lg(val,3))
    Quit $$$OK
}

The queryNameClose method\
The queryNameClose method terminates the query, once all the data is obtained. The signature of this method is as follows:

ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ]

where:

Caché executes this method after the final call to the queryNameFetch method
In other words, this is a query destructor
Therefore you should dispose all SQL cursors, queries and local variables in its implementation
The methods return the current status

In our example, we have to delete the local variable qHandle:

ClassMethod AllRecordsClose(ByRef qHandle As %Binary) As %Status {
    Kill qHandle
    Quit $$$OK
  }

And here we are! Once you compile the class, you will be able to use the AllRecords query from %SQL.Statement -- just as the basic class queries.

Iteration logic approaches for custom queries

So, what approaches can be used for custom queries? In general, there exist 3 basic approaches:

Iteration through a global

The approach is based on using $Order and similar functions for iteration through a global. It can be used in the following cases:

Data is stored in globals (without classes)
You want to reduce the number of glorefs is the code
The results must be/can be sorted by the global's subscript

Static SQL

The approach is based on cursors and static SQL. This is used for:

Making the int code more readable
Making the work with cursors easier
Speeding up the compilation process (static SQL is included into the class query and is therefore compiled only once).

Note:

Cursors generated from queries of the %SQLQuery type are named automatically, e.g. Q14.
All cursors used within a class must have different names.
Error messages are related to the internal names of cursors which have an additional characters at the end of their names. For example, an error in cursor Q140 is actually caused by cursor Q14.
Use PlaceAfter and make sure that cursors are used in the same int routinewhere they have been declared.
INTO must be used in conjunction with FETCH, but not DECLARE. Example of static SQL for Utils.CustomQuery:

Query AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllStatic, SqlProc ]
{
}

ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status
{
    &sql(DECLARE C CURSOR FOR
        SELECT Id, Prop1, Prop2
        FROM Utils.CustomQuery
     )
     &sql(OPEN C)
    Quit $$$OK
}

ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ]
{
    #; INTO must be with FETCH
    &sql(FETCH C INTO :Id, :Prop1, :Prop2)
    #; Check if we reached end of data
    If (SQLCODE'=0) {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    }
    Set Row = $Lb(Id, Prop1, Prop2)
    Quit $$$OK
}

ClassMethod AllStaticClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ]
{
    &sql(CLOSE C)
    Quit $$$OK
}

Dynamic SQL

The approach is based on other class queries and dynamic SQL. This is reasonable when in addition to an SQL query itself, you also need to perform some additional operations, e.g. execute an SQL query in several namespaces or escalate permissions before running the query.

Example of dynamic SQL for Utils.CustomQuery:

Query AllDynamic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllDynamic, SqlProc ]
{
}

ClassMethod AllDynamicExecute(ByRef qHandle As %Binary) As %Status
{
    Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery")
    Quit $$$OK
}

ClassMethod AllDynamicFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
{
    If qHandle.%Next()=0 {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    }
    Set Row = $Lb(qHandle.%Get("Id"), qHandle.%Get("Prop1"), qHandle.%Get("Prop2"))
    Quit $$$OK
}

ClassMethod AllDynamicClose(ByRef qHandle As %Binary) As %Status
{
    Kill qHandle
    Quit $$$OK
}

Alternative approach: %SQL.CustomResultSet

Alternatively, you can create a query by subclassing from the %SQL.CustomResultSet class. Benefits of this approach are as follows:

Slight increase in speed
ROWSPEC is unnecessary, since all metadata is obtained from the class definition
Compliance with the object-oriented design principles

To create query from the subclass of %SQL.CustomResultSet class, make sure to perform the following steps:

Define the properties corresponding to the resulting fields
Define the private properties where the query context will be stored
Override the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as well
Override the %Next method (similar to queryNameFetch) which obtains the next result. Fill in the properties. The method returns 0 if all the data has been processed and 1 if some data is still remaining
Override the %CloseCursor method (similar to queryNameClose) if necessary\ Example of %SQL.CustomResultSet for Utils.CustomQuery:

Class Utils.CustomQueryRS Extends %SQL.CustomResultSet
{
Property Id As %String;
Property Prop1 As %String;
Property Prop2 As %Integer;
Method %OpenCursor() As %Library.Status
{
    Set ..Id = ""
    Quit $$$OK
}

Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ]
{
    Set sc = $$$OK
    Set ..Id = $Order(^Utils.CustomQueryD(..Id),1,val)
    Quit:..Id="" 0
    Set ..Prop1 = $Lg(val,2)
    Set ..Prop2 = $Lg(val,3)
    Quit $$$OK
}
}

You can call it from InterSystems Object Script code in the following way:

 Set resultset= ##class(Utils.CustomQueryRS).%New()
       While resultset.%Next() {
        Write resultset.Id,!
 }

Another example is available in the SAMPLES namespace -- it's the Sample.CustomResultSet class which implements a query for Samples.Person.

Summary

Custom queries will help you to separate SQL expressions from Caché Object Script code and implement sophisticated behavior which can be too difficult for pure SQL.

References

Class queries

Iteration through a global

Static SQL

Dynamic SQL

%SQL.CustomResultSet

The Utils.CustomQuery class

The Utils.CustomQueryRS class

The author would like to thank Alexander Koblov for his assistance in writing this article.

Logging using macros in InterSystems IRIS

Eduard — Fri, 29 Jan 2021 11:42:56 +0000

In this article we will design and build a logging system based on macros in InterSystems IRIS. Some info on macros.

Logging system

Logging system is a useful tool for monitoring the work of an application that saves a lot of time during debugging and monitoring. Our system would consist of two parts:

Storage class (for log records)
Set of macros that automatically add a new record to the log

Storage class

Let's create a table of what we need to store and specify when this data can be obtained – during compilation or at runtime. This will be required when working on the second part of the system - macros, where we will aim to have as many loggable details during compilation as possible:

Information	Obtained during
Event type	Compilation
Class name	Compilation
Method name	Compilation
Arguments passed to a method	Compilation
Line number in the cls source code	Runtime
Line number in the generated int code	Runtime
Username	Runtime
Date/Time	Runtime
Message	Runtime
IP address	Runtime

Let's create an App.Log class containing the properties from the table above. When an App.Log object is created, User Name, Date/Time and IP address properties are filled out automatically.

App.Log class:

Class App.Log Extends %Persistent

{

/// Type of event
Property EventType As %String(MAXLEN = 10, VALUELIST = ",NONE,FATAL,ERROR,WARN,INFO,STAT,DEBUG,RAW") [InitialExpression = "INFO"];

/// Name of class, where event happened
Property ClassName As %Dictionary.Classname(MAXLEN = 256);

/// Name of method, where event happened
Property MethodName As %String(MAXLEN = 128);

/// Line of int code
Property Source As %String(MAXLEN = 2000);

/// Line of cls code
Property SourceCLS As %String(MAXLEN = 2000);

/// IRIS user
Property UserName As %String(MAXLEN = 128) [InitialExpression = {$username}];

/// Arguments' values passed to method
Property Arguments As %String(MAXLEN = 32000, TRUNCATE = 1);

/// Date and time
Property TimeStamp As %TimeStamp [InitialExpression = {$zdt($h, 3, 1)}];

/// User message
Property Message As %String(MAXLEN = 32000, TRUNCATE = 1);

/// User IP address
Property ClientIPAddress As %String(MAXLEN = 32) [InitialExpression = {..GetClientAddress()}];

/// Determine user IP address
ClassMethod GetClientAddress()
{
  // %CSP.Session source is preferable
  #dim %request As %CSP.Request
  If ($d(%request)) {
    Return %request.CgiEnvs("REMOTE\_ADDR")
  }
  Return $system.Process.ClientIPAddress()
}

}

Logging macros

Usually, macros are stored in separate *.inc files containing their definitions. The necessary files can be included into classes using the Include MacroFileName command, which in this case will look as follows: Include App.LogMacro.

To start, let's define the main macro that the user will add to their application's code:

#define LogEvent(%type, %message) Do ##class(App.Log).AddRecord($$$CurrentClass, $$$CurrentMethod, $$$StackPlace, %type, $$$MethodArguments, %message)

This macro accepts two input arguments: Event Type and Message. The Message argument is defined by the user, but the Event Type parameter will require additional macros with different names that will automatically identify the event type:

#define LogNone(%message) $$$LogEvent("NONE", %message)

#define LogError(%message) $$$LogEvent("ERROR", %message)

#define LogFatal(%message) $$$LogEvent("FATAL", %message)

#define LogWarn(%message) $$$LogEvent("WARN", %message)

#define LogInfo(%message) $$$LogEvent("INFO", %message)

#define LogStat(%message) $$$LogEvent("STAT", %message)

#define LogDebug(%message) $$$LogEvent("DEBUG", %message)

#define LogRaw(%message) $$$LogEvent("RAW", %message)

Therefore, in order to perform logging, the user only needs to place the $$$LogError("Additional message") macro in the application code.
All we need to do now is to define the $$$CurrentClass, $$$CurrentMethod, $$$StackPlace, $$$MethodArguments macros. Let's start with the first three:

#define CurrentClass ##Expression($$$quote(%classname))

#define CurrentMethod ##Expression($$$quote(%methodname))

#define StackPlace $st($st(-1),"PLACE")

%classname, %methodname variables are described in the documentation. The $stack function returns INT code line number. To convert it into CLS line number we can use this code.

Let's use the %Dictionary package to get a list of method arguments and their values. It contains all the information about the classes, including method descriptions. We are particularly interested in the %Dictionary.CompiledMethod class and its FormalSpecParsed property, which is a list:

$lb($lb("Name","Classs","Type(Output/ByRef)","Default value "),...)

corresponding to the method signature. For example:

ClassMethod Test(a As %Integer = 1, ByRef b = 2, Output c)

will have the following FormalSpecParsed value:

$lb(
  $lb("a","%Library.Integer","","1"),
  $lb("b","%Library.String","&","2"),
  $lb("c","%Library.String","*","")
)

We need to make $$$MethodArguments macro expand into the following code (for the Test method):

"a="_$g(a,"Null")_"; b="_$g(b,"Null")_"; c="_$g(c,"Null")_";"

To achieve this, we have to do the following during compilation:

Get a class name and a method name
Open a corresponding instance of the %Dictionary.CompiledMethod class and get its FormalSpec property
Convert it into a source code line

Let's add corresponding methods to the App.Log class:

ClassMethod GetMethodArguments(ClassName As %String, MethodName As %String) As %String
{
    Set list = ..GetMethodArgumentsList(ClassName,MethodName)
    Set string = ..ArgumentsListToString(list)
    Return string
}

ClassMethod GetMethodArgumentsList(ClassName As %String, MethodName As %String) As %List
{
    Set result = ""
    Set def = ##class(%Dictionary.CompiledMethod).%OpenId(ClassName _ "||" _ MethodName)
    If ($IsObject(def)) {
        Set result = def.FormalSpecParsed
    }
    Return result
}

ClassMethod ArgumentsListToString(List As %List) As %String
{
    Set result = ""
    For i=1:1:$ll(List) {
        Set result = result _ $$$quote($s(i>1=0:"",1:"; ") _ $lg($lg(List,i))_"=")
        _ "_$g(" _ $lg($lg(List,i)) _ ","_$$$quote(..#Null)_")_"
        _$s(i=$ll(List)=0:"",1:$$$quote(";"))
    }
    Return result
}

Let's now define the $$$MethodArguments macro as:

#define MethodArguments ##Expression(##class(App.Log).GetMethodArguments(%classname,%methodname))

Use case

Next, let's create an App.Use class with a Test method to demonstrate the capabilities of the logging system:

Include App.LogMacro
Class App.Use [ CompileAfter = App.Log ]
{
/// Do ##class(App.Use).Test()
ClassMethod Test(a As %Integer = 1, ByRef b = 2)
{
    $$$LogWarn("Text")
}
}

As a result, the $$$LogWarn("Text") macro in the int code converts into the following line:

Do ##class(App.Log).AddRecord("App.Use","Test",$st($st(-1),"PLACE"),"WARN","a="\_$g(a,"Null")\_"; b="\_$g(b,"Null")\_";", "Text")

Execution of this code will create a new App.Log record:

Improvements

Having created a logging system, here's some improvement ideas:

First of all, a possibility to process object-type arguments since our current implementation only logs object oref.
Second, a call to restore the context of a method from stored argument values.

Processing of object-type arguments

The line that puts an argument value to the log is generated in the ArgumentsListToString method and looks like this:

"\_$g(" \_ $lg($lg(List,i)) \_ ","\_$$$quote(..#Null)\_")\_"

Let's do some refactoring and move it into a separate GetArgumentValue method that will accept a variable name and class (all of which we know from FormalSpecParsed) and output a code that will convert the variable into a line. We'll use existing code for data types, and objects will be converted into JSON with the help of SerializeObject (for calling from the user code) and WriteJSONFromObject (for converting an object into JSON) methods:

ClassMethod GetArgumentValue(Name As %String, ClassName As %Dictionary.CacheClassname) As %String
{
    If $ClassMethod(ClassName, "%Extends", "%RegisteredObject") {
        // it's an object
        Return "_##class(App.Log).SerializeObject("_Name _ ")_"
    } Else {
        // it's a datatype
        Return "_$g(" _ Name _ ","_$$$quote(..#Null)_")_"
    }
}

ClassMethod SerializeObject(Object) As %String
{
    Return:'$IsObject(Object) Object
    Return ..WriteJSONFromObject(Object)
}

ClassMethod WriteJSONFromObject(Object) As %String [ ProcedureBlock = 0 ]
{
    Set OldIORedirected = ##class(%Device).ReDirectIO()
    Set OldMnemonic = ##class(%Device).GetMnemonicRoutine()
    Set OldIO = $io
    Try {
        Set Str=""

        //Redirect IO to the current routine - makes use of the labels defined below
        Use $io::("^"_$ZNAME)

        //Enable redirection
        Do ##class(%Device).ReDirectIO(1)

        Do ##class(%ZEN.Auxiliary.jsonProvider).%ObjectToJSON(Object)
    } Catch Ex {
        Set Str = ""
    }

    //Return to original redirection/mnemonic routine settings
    If (OldMnemonic '= "") {
        Use OldIO::("^"_OldMnemonic)
    } Else {
        Use OldIO
    }
    Do ##class(%Device).ReDirectIO(OldIORedirected)

    Quit Str

    // Labels that allow for IO redirection
    // Read Character - we don't care about reading
rchr(c)      Quit
    // Read a string - we don't care about reading
rstr(sz,to)  Quit
    // Write a character - call the output label
wchr(s)      Do output($char(s))  Quit
    // Write a form feed - call the output label
wff()        Do output($char(12))  Quit
    // Write a newline - call the output label
wnl()        Do output($char(13,10))  Quit
    // Write a string - call the output label
wstr(s)      Do output(s)  Quit
    // Write a tab - call the output label
wtab(s)      Do output($char(9))  Quit
    // Output label - this is where you would handle what you actually want to do.
    // in our case, we want to write to Str
output(s)    Set Str = Str_s Quit
}

A log entry with an object-type argument looks like this:

Restoring the context

The idea of this method is to make all arguments available in the current context (mostly in the terminal, for debugging). To this end, we can use the ProcedureBlock method parameter. When set to 0, all variables declared within such a method will remain available upon quitting the method. Our method will open an object of the App.Log class and deserialize the Arguments property.

ClassMethod LoadContext(Id) As %Status [ ProcedureBlock = 0 ]
{
    Return:'..%ExistsId(Id) $$$OK
    Set Obj = ..%OpenId(Id)
    Set Arguments = Obj.Arguments
    Set List = ..GetMethodArgumentsList(Obj.ClassName,Obj.MethodName)
    For i=1:1:$Length(Arguments,";")-1 {
        Set Argument = $Piece(Arguments,";",i)
        Set @$lg($lg(List,i)) = ..DeserializeObject($Piece(Argument,"=",2),$lg($lg(List,i),2))
    }
    Kill Obj,Arguments,Argument,i,Id,List
}

ClassMethod DeserializeObject(String, ClassName) As %String
{
    If $ClassMethod(ClassName, "%Extends", "%RegisteredObject") {
        // it's an object
        Set st = ##class(%ZEN.Auxiliary.jsonProvider).%ConvertJSONToObject(String,,.obj)
        Return:$$$ISOK(st) obj
    }
    Return String
}

This is how it looks in the terminal:

>zw
>do ##class(App.Log).LoadContext(2)
>zw

a=1
b=<OBJECT REFERENCE>[2@%ZEN.proxyObject]

>zw b
b=<OBJECT REFERENCE>[2@%ZEN.proxyObject]
+----------------- general information ---------------
|      oref value: 2
|      class name: %ZEN.proxyObject
| reference count: 2
+----------------- attribute values ------------------
|           %changed = 1
|     %data("prop1") = 123
|     %data("prop2") = "abc"
|             %index = ""

What's next?

The key potential improvement is to add another argument to the log class with an arbitrary list of variables created inside the method.

Conclusions

Macros can be quite useful for application development.

Questions

Is there a way to obtain line number during compilation?

Links

Developing REST API with a spec-first approach in InterSystems IRIS

Eduard — Sat, 09 Jan 2021 10:19:54 +0000

In this article, I would like to talk about the spec-first approach to REST API development.

While traditional code-first REST API development goes like this:

Writing code
REST-enabling it
Documenting it (as a REST API)

Spec-first follows the same steps but reverse. We start with a spec, also doubling as documentation, generate a boilerplate REST app from that and finally write some business logic.

This is advantageous because:

You always have relevant and useful documentation for external or frontend developers who want to use your REST API
Specification created in OAS (Swagger) can be imported into a variety of tools allowing editing, client generation, API Management, Unit Testing and automation or simplification of many other tasks
Improved API architecture. In code-first approach, API is developed method by method so a developer can easily lose track of the overall API architecture, however with the spec-first developer is forced to interact with an API from the position if API consumer which usually helps with designing cleaner API architecture
Faster development - as all boilerplate code is automatically generated you won't have to write it, all that's left is developing business logic.
Faster feedback loops - consumers can get a view of the API immediately and they can easier offer suggestions simply by modifying the spec

Let's develop our API in a spec-first approach!

Plan

Develop spec in swagger
- Docker
- Locally
- Online
Load spec into IRIS
- API Management REST API
- ^REST
- Classes
What happened with our spec?
Implementation
Further development
Considerations
- Special parameters
- CORS
Load spec into IAM

Develop specification

The first step is unsurprisingly writing the spec. InterSystems IRIS supports Open API Specification (from Swagger docs):

OpenAPI Specification (formerly Swagger Specification) is an API description format for REST APIs. An OpenAPI file allows you to describe your entire API, including:

Available endpoints (/users) and operations on each endpoint (GET /users, POST /users)

Operation parameters Input and output for each operation

Authentication methods

Contact information, license, terms of use and other information.

API specifications can be written in YAML or JSON. The format is easy to learn and readable to both humans and machines. The complete OpenAPI Specification can be found on GitHub: OpenAPI 3.0 Specification

We will use Swagger to write our API. There are several ways to use Swagger:

Online
Docker: docker run -d -p 8080:8080 swaggerapi/swagger-editor
Local installation

After installing/running Swagger, you should see this window in a web browser:

On the left side, you edit the API specification and on the right, you immediately see rendered API documentation/testing tool.

Let's load our first API spec into it (in YAML). It is a simple API with one GET request - returning random number in a specified range.

Math API Specification

swagger: "2.0"
info:
  description: "Math"
  version: "1.0.0"
  title: "Math REST API"
host: "localhost:52773"
basePath: "/math"
schemes:
  - http
paths:
  /random/{min}/{max}:
    get:
      x-ISC_CORS: true
      summary: "Get random integer"
      description: "Get random integer between min and max"
      operationId: "getRandom"
      produces:
      - "application/json"
      parameters:
      - name: "min"
        in: "path"
        description: "Minimal Integer"
        required: true
        type: "integer"
        format: "int32"
      - name: "max"
        in: "path"
        description: "Maximal Integer"
        required: true
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "OK"

Here's what it consists of.

Basic information about our API and used OAS version.

swagger: "2.0"
info:
  description: "Math"
  version: "1.0.0"
  title: "Math REST API"

Server host, protocol (http, https) and Web application names:

host: "localhost:52773"
basePath: "/math"
schemes:
  - http

Next we specify a path (so complete URL would be http://localhost:52773/math/random/:min/:max) and HTTP request method (get, post, put, delete):

paths:
  /random/{min}/{max}:
    get:

After that, we specify information about our request:

      x-ISC_CORS: true
      summary: "Get random integer"
      description: "Get random integer between min and max"
      operationId: "getRandom"
      produces:
      - "application/json"
      parameters:
      - name: "min"
        in: "path"
        description: "Minimal Integer"
        required: true
        type: "integer"
        format: "int32"
      - name: "max"
        in: "path"
        description: "Maximal Integer"
        required: true
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "OK"

In this part we define our request:

Enable this path for CORS (more on that later)
Provide summary and description
operationId allows in-spec reference, also it's a generated method name in our implementation class
produces - response format (such as text, xml, json)
parameters specify input parameters (be they in URL or body), in our case we specify 2 parameters - range for our random number generator
responses list possible responses form server

As you see this format is not particularly challenging, although there are many more features available, here's a specification.

Finally, let's export our definition as a JSON. Go To File → Convert and save as JSON. The specification should look like this:

Math API Specification

{
  "swagger": "2.0",
  "info": {
    "description": "Math",
    "version": "1.0.0",
    "title": "Math REST API"
  },
  "host": "localhost:52773",
  "basePath": "/math",
  "schemes": [
    "http"
  ],
  "paths": {
    "/random/{min}/{max}": {
      "get": {
        "x-ISC_CORS": true,
        "summary": "Get random integer",
        "description": "Get random integer between min and max",
        "operationId": "getRandom",
        "produces": [
          "application/json"
        ],
        "parameters": [
          {
            "name": "min",
            "in": "path",
            "description": "Minimal Integer",
            "required": true,
            "type": "integer",
            "format": "int32"
          },
          {
            "name": "max",
            "in": "path",
            "description": "Maximal Integer",
            "required": true,
            "type": "integer",
            "format": "int32"
          }
        ],
        "responses": {
          "200": {
            "description": "OK"
          }
        }
      }
    }
  }
}

Load specification into IRIS

Now that we have our spec, we can generate boilerplate code for this REST API in InterSystems IRIS.

To move to this stage we'll need three things:

REST Application name: package for our generated code (let's say math)
OAS spec in a JSON format: we just created it in a previous step
WEB Application name: a base path to access our REST API (/math in our case)

There are three ways to use our spec for code generation, they are essentially the same and just offer various ways to access the same functionality

Call ^%REST routine (Do ^%REST in an interactive terminal session), documentation.
Call %REST class (Set sc = ##class(%REST.API).CreateApplication(applicationName, spec), non-interactive), documentation.
Use API Management REST API, documentation.

I think documentation adequately describes required steps so just choose one. I'll add two notes:

In case (1) and (2) you can pass a dynamic object a filename or a URL
In cases (2) and (3) you must make an additional call to create a WEB application: set sc = ##class(%SYS.REST).DeployApplication(restApp, webApp, authenticationType), so in our case set sc = ##class(%SYS.REST).DeployApplication("math", "/math"), get values for authenticationType argument from %sySecurity include file, relevant entries are $$$Authe\*, so for unauthenticated access pass $$$AutheUnauthenticated. If omitted, the parameter defaults to password authentication.

What happened with our spec?

If you've created the app successfully, new math package should be created with three classes:

Spec - stores the specification as-is.
Disp - directly called when the REST service is invoked. It wraps REST handling and calls implementation methods.
Impl - holds the actual internal implementation of the REST service. You should edit only this class.

Documentationwith more information about the classes.

Implementation

Initially our implementation class math.impl contains only one method, corresponding to our /random/{min}/{max} operation:

/// Get random integer between min and max<br/>
/// The method arguments hold values for:<br/>
///     min, Minimal Integer<br/>
///     max, Maximal Integer<br/>
ClassMethod getRandom(min As %Integer, max As %Integer) As %DynamicObject
{
    //(Place business logic here)
    //Do ..%SetStatusCode(<HTTP_status_code>)
    //Do ..%SetHeader(<name>,<value>)
    //Quit (Place response here) ; response may be a string, stream or dynamic object
}

Let's start with the trivial implementation:

ClassMethod getRandom(min As %Integer, max As %Integer) As %DynamicObject
{
    quit {"value":($random(max-min)+min)}
}

And finally we can call our REST API by opening this page in browser : http://localhost:52773/math/random/1/100

The output should be:

{
    "value": 45
}

Also in the Swagger editor pressing Try it out button and filling the request parameters would also send the same request:

Congratulations! Our first REST API created with a spec-first approach is now live!

Further development

Of course, our API is not static and we need to add new paths and so on. With spec-first development, you start with modifying the specification, then updating the REST application (same calls as for creating the application) and finally writing the code. Note that spec updates are safe: your code is not affected, even if the path is removed from a spec, in implementation class the method would not be deleted.

Considerations

More notes!

Special parameters

InterSystems added special parameters to swagger specification, here they are:

Name	Datatype	Default	Place	Description
x-ISC_DispatchParent	classname	%CSP.REST	info	Superclass for dispatch class.
x-ISC_CORS	boolean	false	operation	Flag to indicate that CORS requests for this endpoint/method combination should be supported.
x-ISC_RequiredResource	array		operation	Comma-separated list of defined resources and their access modes (resource:mode) that are required for access to this endpoint of the REST service. Example: ["%Development:USE"]
x-ISC_ServiceMethod	string		operation	Name of the class method called on the back end to service this operation; default is operationId, which is normally suitable.

CORS

There are three ways to enable CORS support.

On a route by route basis by specifying x-ISC\_CORS as true. That's what we have done in our Math REST API.
On per API basis by adding

Parameter HandleCorsRequest = 1;

and recompiling the class. It would also survive spec update.

(Recommended) On per API basis by implementing custom dispatcher superclass (it should extend %CSP.REST) and writing CORS processing logic there. To use this superclass add x-ISC\_DispatchParent to your specification.

Load spec into IAM

Finally, let's add our spec into IAM so it would be published for other Developers.

If you have not started with IAM, check outthis article. It also covers offering REST API via IAM so I'm not describing it here. You might want to modify spec host and basepath parameters so that they point to IAM, rather than the InterSystems IRIS instance.

Open the IAM Administrator portal and go to the Specs tab in the relevant workspace.

Click the Add Spec button and input the name of the new API (math in our case). After creating new Spec in IAM click Edit and paste the spec code (JSON or YAML - it doesn't matter for IAM):

Don't forget to click Update File.

Now our API is published for Developers. Open Developer Portal and click Documentation in the upper right corner. In addition to the three default APIs our new Math REST API should be available:

Open it:

Now Developers can see the documentation for our new API and try it at the same place!

Conclusion

InterSystems IRIS simplifies the development process for a REST API and the spec-first approach allows faster and easier REST API life cycle management. With this approach, you can use a variety of tools for a variety of related tasks, such as client generation, unit testing, API Management, and many others.

Links

Generate Swagger spec from persistent and serial classes in InterSystems IRIS

Eduard — Mon, 28 Dec 2020 14:21:03 +0000

Recently I needed to generate a Swagger spec from persistent and serial classes, so I'm publishing my code (it's not complete - you still need to hash out the application specifics, but it's a start). It's available here.

Let's say you have these classes:

Class REST.Test.Person Extends %Persistent
{

/// Person's name.
Property Name As %String [ Required ];

/// Person's Social Security number. This is validated using pattern match.
Property SSN As %String [ Required ];

/// Person's Date of Birth.
Property DOB As %Date;

/// Person's home address. This uses an embedded object.
Property Home As Address;

/// Person's office address. This uses an embedded object.
Property Office As Address;

/// Person's spouse. This is a reference to another persistent object.
Property Spouse As Person;

/// A collection of strings representing the person's favorite colors.
Property FavoriteColors As list Of %String;

/// A collection of strings representing the person's favorite colors.
Property FavoriteNumbers As array Of %Integer;

/// Person's age.<br>
/// This is a calculated field whose value is derived from <property>DOB</property>.
Property Age As %Integer;

}

Class REST.Test.Address Extends %SerialObject
{

/// The street address.
Property Street As %String(MAXLEN = 80);

/// The city name.
Property City As %String(MAXLEN = 80);

/// The 2-letter state abbreviation.
Property State As %String(MAXLEN = 2);

/// The 5-digit U.S. Zone Improvement Plan (ZIP) code.
Property Zip As %String(MAXLEN = 5);
}

You can automatically generate this Swagger definition from them:

 REST.Test.Person:
   type: "object"
   properties:
     Age:
       type: "integer"
     DOB:
       type: "string"
     FavoriteColors:
       type: "array"
       items:
         type: "string"
     FavoriteNumbers:
       type: "object"
     Home:
       $ref: "#/definitions/REST.Test.Address"
     Name:
       type: "string"
     Office:
       $ref: "#/definitions/REST.Test.Address"
     SSN:
       type: "string"
     Spouse:
       $ref: "#/definitions/REST.Test.Person"
 REST.Test.Address:
   type: "object"
   properties:
     City:
       type: "string"
     State:
       type: "string"
     Street:
       type: "string"
     Zip:
       type: "string"

Main method: Utils.YAML:GenerateClasses

Test run: do ##class(Utils.YAML).Test()

Community Python Gateway for InterSystems IRIS: Introduction

Eduard — Thu, 03 Dec 2020 09:02:46 +0000

This series of articles would cover community project Python Gateway for InterSystems Data Platforms. Execute Python code and more from InterSystems IRIS. This project brings you the power of Python right into your InterSystems IRIS environment:

Execute arbitrary Python code
Seamlessly transfer data from InterSystems IRIS into Python
Build intelligent Interoperability business processes with Python Interoperability Adapter
Save, examine, modify and restore Python context from InterSystems IRIS

Overview

Machine learning (ML) - is the study of algorithms and statistical models to effectively perform a specific task without using explicit instructions, relying on patterns and inference instead.

Machine learning algorithms and models are becoming more and more commonplace. There is a variety of reasons for that, but it all comes down to affordability, simplicity and producing actionable results. Is clustering or even neural network modeling a new technology? Of course not, but nowadays you do not need to write hundreds of thousands lines of code to run one and the costs are much more manageable.

Tools are evolving - while we currently do not have completely GUI-based AI/ML tools, but the same progress we saw with many other computer technologies, most notable being BI tools (from writing code to utilizing frameworks to GUI-based configurable solutions) is seen with AI/ML tools. We already passed the point of writing code and are currently utilizing frameworks to configure and calculate the models.

Other improvements, i.e. distributing pre-trained model, where end user should just finish model training on a real-life data also simplify onboarding process. These advances make getting into data science a much easier endeavor for both individuals and companies.

On the other hand nowadays we collect more data about every transaction business makes. With a unified data platform such as InterSystems IRIS all this information can be accessed immediately and used as a fuel for predictive models.

With the other big mover – cloud, running AI/ML workloads becomes easier than ever. Even more important is that we can consume only the resources we require. Moreover, with massive parallelization offered by cloud platforms we can save on a time to a working solution.

But what about results? Here it gets a little trickier. There are lots of tools to build a model, and I’ll talk about them later, and it’s not always easy to build a good model, but what comes after? Extracting business value from a model is also a nontrivial endeavor. The root of the problem is the separation of analytical and transactional data flows and data models. When we train the model, we usually do that on a historical data in a warehouse system. But the greatest place for the built model to be is in the heart of transactional processing. What good is the best fraud detection model if we run it once a day? The criminals would be long gone with the money. We need to train the model on a historical data but we also need to apply the model in a real time on the new incoming data so that our business processes can act on predictions the model makes.

MLToolkit

MLToolkit is a comprehensive set of tools, which aims to do exactly that – bring predictive models and transactional environments together, so that the models you build can be easily leveraged right inside your business processes. Titular Python Gateway is a part of MLToolkit and provides integration with the Python language.

Landscape

Before we go further, I would like to describe several tools and libraries for Python, which we would use later.

Tools

Python is an interpreted, high-level, general-purpose programming language. The main advantage of the language is a big library of mathematical, ML and AI libraries. Same as ObjectScript it's an object-oriented language but everything is dynamic rather that static. Also, everything is an object. The later articles assume a passing familiarity with the language. If you want to start learning, I recommend starting with documentation.
For our later exercises install Python 3.6.7 64 bit.
IDE: I use PyCharm, bet there are a lot of them. If you're using Atelier, Eclipse for Python developers is a thing.
Notebook: instead of IDE you can write and share your scripts in a Web-based notebook. The most popular one is Jupyter.

Libraries

Here's a (incomplete) list of libraries used for Machine Learning.

Numpy is the fundamental package for scientific computing with Python.
Pandas is a library providing high-performance, easy-to-use data structures and data analysis tools.
Matplotlib is a 2D plotting library which produces figures in a variety of hardcopy formats and interactive environments across platforms.
Seaborn is a data visualization library based on matplotlib. It provides a high-level interface for drawing attractive and informative statistical graphics.
Sklearn is a machine Learning library.
XGBoost is an optimized distributed gradient boosting library designed to be highly efficient, flexible and portable. It implements machine learning algorithms under the Gradient Boosting framework.
Gensim is a library for unsupervised topic modeling and natural language processing.
Keras is a high-level neural networks API, written in Python and capable of running on top of TensorFlow, CNTK, or Theano.
Tensorflow is an end-to-end open source machine learning platform.
PyTorch deep learning platform similar to Tensorflow but Python focused.
Nyoka produces PMML from Python models.

Summary

AI/ML technologies allow business to be more effective and more adaptable. Moreover, today these technologies are becoming easier to build and deploy. Start investigating AI/ML technologies and how it can help your organization to grow and prosper. There are examples, stories and use cases from almost every industry. Do not miss your chance to use future technologies today.

What's next

In the next part we would install Python Gateway.

InterSystems IRIS - Iterate over dynamic object

Eduard — Fri, 13 Nov 2020 13:11:39 +0000

Here's a sample code to display JSON or dynamic object.

It shows how to iterate over object, get property values and their paths.

Class JSON.Test
{

/// do ##class(JSON.Test).Test()
ClassMethod Test()
{
    set json = "{""a"":1,""b"":2,""c"":{""c1"":3,""c2"":4}, ""d"": [5, {""e_e"":6}, 7]}"

    set obj = {}.%FromJSON(json)

    do ..Iterate(obj)
}

ClassMethod Iterate(obj As %DynamicAbstractObject, level = 0, path = "obj")
{
    set indent = $j("", level * 4)
    #dim iterator As %Iterator.Array
    set iterator = obj.%GetIterator()

    while iterator.%GetNext(.key, .value) {
        set type = obj.%GetTypeOf(key)
        write indent, "Key: ", key, !
        write indent, "Type: ", type, !

        if $classname(obj) = "%Library.DynamicArray" {
            set newPath = path _ ".%GetAt(" _ key _ ")"
        } else {
            if $zname(key, 6) = 1 {
                set newPath = path _  "." _ key
            } else {
                set newPath = path _  ".""" _ key _ """"
            }
        }

        write indent, "Path: ", newPath, !
        if $isObject(value) {
            write indent, "Value: ", !
            do ..Iterate(value, level + 1, newPath)
        } else {
            write indent, "Value: ", value, !
        }
        write !
    }
}

}

Output from running Test method:

Key: a
Type: number
Path: obj.a
Value: 1

Key: b
Type: number
Path: obj.b
Value: 2

Key: c
Type: object
Path: obj.c
Value:
    Key: c1
    Type: number
    Path: obj.c.c1
    Value: 3

    Key: c2
    Type: number
    Path: obj.c.c2
    Value: 4


Key: d
Type: array
Path: obj.d
Value:
    Key: 0
    Type: number
    Path: obj.d.%GetAt(0)
    Value: 5

    Key: 1
    Type: object
    Path: obj.d.%GetAt(1)
    Value:
        Key: e_e
        Type: number
        Path: obj.d.%GetAt(1)."e_e"
        Value: 6


    Key: 2
    Type: number
    Path: obj.d.%GetAt(2)
    Value: 7

Adding your own provider to MFT

Eduard — Mon, 19 Oct 2020 16:22:08 +0000

Managed File Transfer (MFT) feature of InterSystems IRIS enables easy inclusion of a third-party file transfer service directly into an InterSystems IRIS production. Currently, DropBox, Box, and Kiteworks cloud disks are available.

In this article, I'd like to describe how to add more cloud storage platforms.

Here's what we're going to talk about:

What is MFT
Reference: Dropbox
- Connection
- Interoperability
- Direct access
Interfaces you need to implement
- Connection
- Logic
Installation

What is MFT

MFT provides bidirectional file transfer, you can both download files from a cloud storage and upload files there.

Check out this video on Learning.InterSystems.com for an introduction to MFT.

Reference: Dropbox

Before we start writing our own MFT adapter, let's run an existing one. I chose Dropbox.

Basic access

First of all, we need to configure Dropbox access:

Register new Dropbox account.
Create new Dropbox App.
- Remember your: App Key and App Secret
- Set your Redirect URL, probably: http://localhost:57772/csp/sys/oauth2/OAuth2.Response.cls
Create a new SSL Configuration (if you don't have one)
Create a new MFT configuration at: http://localhost:57772/csp/sys/sec/%25CSP.UI.Portal.MFT.Connection.zen?isNew=1
- Remove "Use SSL" flag (unless your web server is available over https).
- Email address field should match your Dropbox email address.
Press Get Access Token button (on MFT connections list page) to authorize your application.
If everything went alright, the status of the new MFT connection should be Authorized.

Interoperability

Next, we need to configure Interoperability production to use the new connection

To receive files from Dropbox into a local directory:

Create EnsLib.MFT.Service.Passthrough service.
- MFT Connection Name: Dropbox
- MFT Source Folders: the path to Dropbox folder, from which the files would be downloaded (i.e. /inbox/)
- Target Config Names: BP or BO to send files to, for example, EnsLib.File.PassthroughOperation
Create EnsLib.File.PassthroughOperation operation.
- File Path: the path to a local directory to which the files from Dropbox would be downloaded (i.e. C:\\temp\in\)
- Charset: Binary
- File Name: %f

That's all. After starting the production, files from the specified Dropbox folder would be downloaded into the local directory.

To upload files to Dropbox, the process should be done in reverse:

Create EnsLib.MFT.Operation.Passthrough service.
- MFT Connection Name: Dropbox
- Default MFT Folder: the path to Dropbox folder, into which the files would be uploaded (i.e. /sent/)
Create EnsLib.File.PassthroughServiceoperation.
- File Path: the path to a local directory from which the files would be uploaded to Dropbox (i.e. C:\\temp\out\)
- Target Config Names: BP or BO to send files to, in our case EnsLib.MFT.Operation.Passthrough
- Charset: Binary

And with this, files placed into C:\\temp\out\ folder would be uploaded into /sent/ Dropbox folder.

For a more comprehensive guide refer to MFT First look.

"Direct" access

Interoperability BS/BO are wrappers over a direct access. When writing your own adapter it's better to use direct access to check if things work as expected. Here's a sample method to get file information

ClassMethod dropboxInfo(file = "/1.txt")
{
    // Establishing Dropbox connection
    set mftConnectionName = "Dropbox"
    set mftConnection = ##class(%MFT.API).GetConnection(mftConnectionName, .sc)
    write:$$$ISERR(sc) $System.Status.GetErrorText(sc)

    // Getting information about file
    // Some other methods: GetFileInfo GetFolderInfo CreateFolder DeleteFile GetUser ShareFolder UnshareFolder DownloadStream UploadFile
    set sc = $classmethod($$$EnsCoreMFTAPIClass,"GetFileInfo", mftConnection, file, .itemInfo)
    write:$$$ISERR(sc) $System.Status.GetErrorText(sc)

    // Displaying information about file
    #dim itemInfo As %MFT.ItemInfo
    zw itemInfo
}

MFT interface

MFT provider consists of two parts. To create your own provider you need to implement them.

Technical API (connection)

Connection is responsible for sending requests and parsing results. It should extend %SYS.MFT.Connection.Base and implement the following methods:

DefaultURL that returns the root of service API
CreateClient that creates OAuth client
RevokeToken to log out of the application

Other methods could be overloaded as required bit these three are a must. Additionally, for Yandex I created these three methods:

MakeRequest - to execute normal API requests
MakeDownloadRequest - to download files
MakeUploadRequest - to upload files

These methods structure are completely dependent on target API architecture. In my case, a majority of requests were similar, with different requests required for downloading and uploading files so I ended up with three methods.

Logical API

Is responsible for consuming the API, it uses the connection to execute requests and it should extend %MFT.API and reside in the %MFT package (we'll talk about working around that requirement later). The methods it must overload could be separated into four categories:

Info	Create/Delete	Sharing	Load
GetFileInfo GetFolderInfo GetFolderContents	CreateFolder DeleteFolder DeleteFile	ShareFolder UnshareFolder UnshareFolderAll	UploadStream DownloadStream

These methods seem self-explanatory. But please ask in comments if the purpose of these methods seems unclear. There's also documentation in %MFT.API class. I recommend implementing these methods left to right, you can also skip Sharing methods. That brings us to 8 methods that must be implemented.

Additionally, if your cloud disk provider has user management (i.e. teams and so on), you can manage that too via:

GetUser
GetUserById
GetUserList
CreateUser
DeleteUser
DeleteUserById

As Yandex.Disk does not have user management I skipped these methods (they should still be implemented, but left effectively empty).

Finally GetRequestId method should be implemented to convert paths into ids (that could be faster), otherwise, it should return path.

Along with some other methods that are about the same for all providers here's a code snippet you can start writing your adapter with:

/// Get the form of id for a file or folder that is most efficient for subsequent calls.
/// GetRequestId will return either an id of the form "id:<id>" or a full path depending on which is more efficient.
/// This method is included to allow the id for future requests to be saved in the most efficient form.
ClassMethod GetRequestId(connection As %SYS.MFT.Connection.Base, itemInfo As %MFT.ItemInfo) As %String
{
    Quit itemInfo.GetPath()
}

/// Retrieve the %MFT.UserInfo for current user
ClassMethod GetUser(connection As %SYS.MFT.Connection.Base, username As %String, Output userInfo As %MFT.UserInfo) As %Status
{
    Set userInfo = ##class(%MFT.UserInfo).%New()
    Set userInfo.Username = connection.Username
    Quit sc
}

/// Retrieve the %MFT.UserInfo specified by the service defined Userid.
/// If the user does not exist, then $$$OK is returned as status and userInfo is returned as "".
ClassMethod GetUserById(connection As %SYS.MFT.Connection.Base, userid As %String, Output userInfo As %MFT.UserInfo) As %Status
{
    Quit ..GetUser(connection, userid, .userInfo)
}

/// Return the list of all currently defined users for this team or enterprise.
ClassMethod GetUserList(connection As %SYS.MFT.Connection.Base, Output userList As %MFT.UserList) As %Status
{
    Set sc = $$$OK
    Set userList = ##class(%MFT.UserList).%New()
    Set sc = ..GetUser(connection, "", .userInfo)
    Quit:$$$ISERR(sc) sc

    Do userList.Users.Insert(userInfo)

    Quit sc
}

/// Create a new user.
/// Unable to do it in Yandex
ClassMethod CreateUser(connection As %SYS.MFT.Connection.Base, userInfo As %MFT.UserInfo) As %Status
{
    Quit $$$ERROR($$$MFTBadConnection)
}

/// Delete new user.
/// Unable to do it in Yandex
ClassMethod DeleteUser(connection As %SYS.MFT.Connection.Base, username As %String) As %Status
{
    Quit $$$ERROR($$$MFTBadConnection)
}

/// Delete the user that is specified by the id.
ClassMethod DeleteUserById(connection As %SYS.MFT.Connection.Base, userid As %String) As %Status
{
    Quit $$$ERROR($$$MFTBadConnection)
}

/// Unshare a folder from everyone, user is ignored
ClassMethod UnshareFolder(connection As %SYS.MFT.Connection.Base, path As %String, user As %String) As %Status
{
    Quit ..UnshareFolderAll(connection, path)
}

/// MountFolder is a Dropbox specific method to mount a shared folder that was shared by a different user.
/// MountFolder is treated as a NOP for all other services.
ClassMethod MountFolder(connection As %SYS.MFT.Connection.Box, folderName As %String) As %Status
{
    // A NOP if not Dropbox
    Quit $$$OK
}

/// UnmountFolder is a Dropbox specific method to unmount a shared folder that was shared by a different user.
/// UnmountFolder is treated as a NOP for all other services.
ClassMethod UnmountFolder(connection As %SYS.MFT.Connection.Box, folderName As %String) As %Status
{
    // A NOP if not Dropbox
    Quit $$$OK
}

/// Update the specified remote file with the contents of the specified local file.
/// filePath must be a file path.  An id may not be specified.
/// If replace is true, then an existing file of the same name will be replaced.
/// The default is to return an error if a replacement is attempted.
ClassMethod UploadFile(connection As %SYS.MFT.Connection.Base, localFilePath As %String, filePath As %String, replace As %Boolean = 0, Output itemInfo As %MFT.ItemInfo) As %Status
{
    Set stream=##class(%FileBinaryStream).%New()
    Set stream.Filename=localFilePath
    Quit ..UploadStream(.connection,stream,filePath,replace,.itemInfo)
}

/// Download the specified remote file and store at the location given by localFilePath.
/// filePath may be a file path.
ClassMethod DownloadFile(connection As %SYS.MFT.Connection.Base, filePath As %String, localFilePath As %String) As %Status
{
    Set stream=##class(%FileBinaryStream).%New()
    Set stream.Filename=localFilePath
    Quit ..DownloadStream(.connection,filePath,stream)
}

Additional data

Some other classes are used to transmit information, they are:

%MFT.ItemInfo is a detailed description of a file or folder - you'll need it.
Other classes are %MFT.UserInfo for user information and %MFT.FolderContents/%MFT.UserList to list several items or users respectively.

Note, that these classes, despite being % classes, store the data in the namespace they are called from. They are storing data in ^MFT.* globals.

Installation

As our API should be in a %MFT package we would use mapping to map code from the specialized database into our target namespace (or %SYS). That way InterSystems IRIS can be safely updated and our work wouldn't be overwritten. An Interesting thing about it is that we need to load classes only into target namespace - %SYS in our case.

First of all, we need to download our code, to do that:

Download and import Installer into any Interoperability-enabled namespace.
Execute: write $System.Status.GetErrorText(##class(MFT.Installer).Install())

It would:

Create MFTLIB database
Add mapping of %MFT.Addons and %SYS.MFT.Connection.Addons packages into %SYS namespace from MFTLIB database
Download the rest of the code from GitHub, correctly importing % and non % classes

Next, we need to configure Yandex application:

Register on Yandex.
Create Yandex App
- Check Веб-сервисы
- Set Redirect URI: http://Host:Port/csp/sys/oauth2/OAuth2.Response.cls (https, if UseSSL = 1, for development you can set it to http://localhost:57772/csp/sys/oauth2/OAuth2.Response.cls)
- Give disk access Яндекс.Диск REST API
- Get ID, Pass
Execute: write $System.Status.GetErrorText(##class(MFT.Yandex).Install(Login, ID, Pass, Host, Port, UseSSL))
- Login - your Yandex email
- Host, Port - same as the callback
- UseSSL - use SSL for callback? Your server needs to support https
Open http://Host:Port/csp/sys/sec/%25CSP.UI.Portal.MFT.ConnectionList.zen
Press Get Access Token and complete authorization.
If everything went fine the Status would be Authorized.
Execute: write $System.Status.GetErrorText(##class(MFT.Yandex).ConfigureProduction(yandexSource, fileDestination, fileSource, yandexDestination))
- yandexSource and fileDestination - Yandex.Disk folder to download files from, they are stored in a local destination folder.
- fileSource and yandexDestination - local folder from which files are uploaded to Yandex.Disk.
- Important: Yandex.Disk folder names should end with / (i.e. out in a disk root would be /out/)
Open production MFT.Production and start it.
Add file(s) to yandexSource and fileSource to see how it works.

Note, that unlike Dropbox, I'm creating service automatically, like this:

ClassMethod Install(username As %String, clientId As %String, clientSecret As %String, host As %String = "localhost", port As %Integer = {$get(^%SYS("WebServer","Port"), 57772)}, useSSL As %Boolean = {$$$NO})
{
    New $Namespace
    Set $Namespace = "%SYS"

     Do:'##class(Security.SSLConfigs).Exists(..#SSLConfig) ##class(Security.SSLConfigs).Create(..#SSLConfig)

    Set sys = ##class(%SYS.MFT.Connection.Addons.Yandex).%New()
    Set sys.Name = "Yandex"
    Set sys.Service = "Addons.Yandex"
    Set sys.ApplicationName = "Yandex"
    Set sys.SSLConfiguration = ..#SSLConfig
    Set sys.Username = username
    Set sys.URL = ..#URL

    $$$QuitOnError(##class(%SYS.MFT.Connection.Addons.Yandex).CreateClient(sys.Name, ..#SSLConfig, clientId, clientSecret, ,host, port,,useSSL))

    Quit sys.%Save()
}

Note the Service value: Addons.Yandex. Service name, when appended to %MFT. should yield the name of the logical API class. Since we can't modify %MFT package directly we can add a subpackage to it.

Conclusion

MFT is a useful technology and can be easily extended to support cloud providers you need.

Classes, tables and globals - how it all works?

Eduard — Wed, 08 Jul 2020 15:54:15 +0000

When I describe InterSystems IRIS to more technically-minded people, I always start with how it is a multimodel DBMS at its core.

In my opinion that is its main advantage (on the DBMS side). And the data is stored only once. You just choose the access API you want to use.

You want some sort of summary for your data? Use SQL!
Do you want to work extensively with one record? Use objects!
Want to access or set one value and you know the key? Use globals!

On first blush it's a nice story - short and concise and it gets the message across, but when people really start working with InterSystems IRIS the questions start. How are classes and tables and globals related? What are they to each other? How's the data really stored?

In this article I would try to answer these questions and explain what's really going on.

Part one. Model bias.

People working with data are often biased towards the model they work with.

Developers think in objects. For them databases and tables are boxes you interact with via CRUD (Create-Read-Update-Delete, preferably over ORM) but the underlying conceptual model is objects (of course it's mainly true for developers in object-oriented languages - so most of us).

On the other hand, as a consequence of spending so much time in relational DBMSes, DBAs often think of data as tables. Objects are just wrappers over rows in this case.

And with InterSystems IRIS, a persistent class is also a table, which stores data in global, so some clarification is required.

Part two. An example.

Let's say you created class Point:

Class try.Point Extends %Persistent [DDLAllowed]
{
    Property X;
    Property Y;
}

You can also create the same class with DDL/SQL:

CREATE Table try.Point (
    X VARCHAR(50),
    Y VARCHAR(50))

After compilation, our new class would have auto-generated a storage structure which maps data that is natively stored in globals to columns (or properties if you're an object-oriented thinker):

Storage Default
{
<Data name="PointDefaultData">
    <Value name="1">
        <Value>%%CLASSNAME</Value>
    </Value>
    <Value name="2">
        <Value>X</Value>
    </Value>
    <Value name="3">
        <Value>Y</Value>
    </Value>
</Data>
<DataLocation>^try.PointD</DataLocation>
<DefaultData>PointDefaultData</DefaultData>
<IdLocation>^try.PointD</IdLocation>
<IndexLocation>^try.PointI</IndexLocation>
<StreamLocation>^try.PointS</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

What is going on here?

From the bottom-up (bold words are important, ignore the rest):

Type: generated storage type, in our case the default storage for persistent objects
StreamLocation - global where we store streams
IndexLocation - global for indices
IdLocation - global where we store ID autoincremental counter
DefaultData - storage XML element which maps global value to columns/properties
DataLocation - global in which to store data

Now our "DefaultData" is PointDefaultData so let's look closer at its structure. Essentially it says that global node has this structure:

1 - %%CLASSNAME
2 - X
3 - Y

So we might expect our global to look like this:

^try.PointD(id) = %%CLASSNAME, X, Y

But if we output our global it would be empty because we didn't add any data:

zw ^try.PointD

Let's add one object:

set p = ##class(try.Point).%New()
set p.X = 1
set p.Y = 2
write p.%Save()

And here's our global

zw ^try.PointD
^try.PointD=1
^try.PointD(1)=$lb("",1,2)

As you see our expected structure %%CLASSNAME, X, Y is set with $lb("",1,2) which corresponds to X and Y properties of our object (%%CLASSNAME is system property, ignore it).

We can also add a row via SQL:

INSERT INTO try.Point (X, Y) VALUES (3,4)

Now our global looks like this:

zw ^try.PointD
^try.PointD=2
^try.PointD(1)=$lb("",1,2)
^try.PointD(2)=$lb("",3,4)

So the data we add via objects or SQL are stored in globals according to storage definitions (side note: you can manually modify the storage definition by replacing X and Y in PointDefaultData - check what happens to the new data!).

Now, what happens when we want to execute a SQL query?

SELECT * FROM try.Point

It is translated into ObjectScript code that iterates over the ^try.PointD global and populates columns based on the storage definition - the PointDefaultData part of it precisely.

Now for modifications. Let's delete all the data from the table:

DELETE FROM try.Point

And let's see our global at this point:

zw ^try.PointD
^try.PointD=2

Note that only ID counter is left, so new object/row would have an ID=3. Also our class and table continue to exist.

But what happens when we run:

DROP TABLE try.Point

It would destroy the table, the class, and delete the global.

zw ^try.PointD

If you followed this example, I hope you now have a better understanding of how globals, classes and tables integrate and complement each other. Using the right API for the job at hand results in faster, more agile, less buggy development.

DEV Community: Eduard

How to work with InterSystems IRIS from DataGrip

Deploying a sharded InterSystems IRIS cluster with Docker and MergeCPF

Docker-compose.yml

CPF2merge-data-instance.conf

CPF2merge-data-instance.conf

Try it

Conclusions

Continuous Delivery of your InterSystems solution using GitLab - Part VIII: CD using ICM

Importing code into InterSystems IRIS from external libraries

Intro

NodeJS

Java

C

System

Comparison

Conclusion

Links

Class Queries in InterSystems IRIS

Basic class queries

Custom class queries

Iteration logic approaches for custom queries

Alternative approach: %SQL.CustomResultSet

Summary

References

Logging using macros in InterSystems IRIS

Developing REST API with a spec-first approach in InterSystems IRIS

Generate Swagger spec from persistent and serial classes in InterSystems IRIS

Community Python Gateway for InterSystems IRIS: Introduction

Other articles

Overview

MLToolkit

Landscape

Tools

Libraries

Summary

What's next

Links

InterSystems IRIS - Iterate over dynamic object

Adding your own provider to MFT

What is MFT

Reference: Dropbox

Basic access

Interoperability

"Direct" access

MFT interface

Technical API (connection)

Logical API

Additional data

Installation

Conclusion

Links

Classes, tables and globals - how it all works?

Part one. Model bias.

Part two. An example.