Using OpenAF thread "boxes"

What is an OpenAF thread-box? It's basically being able to run a block of OpenAF code (a function) on a separate Java thread that will be interrupted either due to a timeout or the specific result of a control function.

It's specially useful for controlling the execution of custom code that might run for longer than expected (for example: executing a block of code that should run in seconds taking more than 5 minutes) or until a specific condition happens (for example: executing a block of code while no Ctrl-C is hit on the keyboard by the user).

Example with timeout

Let's examine an example:

> var res = $tb(() => {
    print("Start...");
    sleep(5000, true);
    print("End.");
})
.timeout(2500)
.exec();

Start...
> res
timeout

So what happened? The code should print Start wait 5 seconds and then print End. But since the thread-box should timeout after 2.5 seconds it just printed Start and then finished returning the string timeout.

If you remove the timeout:

> var res = $tb(() => {
    print("Start...");
    sleep(5000, true);
    print("End.");
})
.exec();

Start...
End.
> res
true

Now, without a timeout, it prints Start and End and return res.

Example with stopWhen

If you need to control the execution with a custom function you can use the stopWhen function:

> var c = 0;
> var res = $tb(() => {
    for(var ii = 0; ii < 10; ii++) {
        print(c);
        c++;
        sleep(100);
    }
})
.stopWhen(() => {
    return c > 5;
})
.exec();

0
1
2
3
4
5
> res
stop

Whenever the function returns true, the execution of the thread-box block of code will be interruped. To increase the interval between calls to the stopWhen function, 25ms, you can add extra time with a sleep call on body of the function.

Of course you can mix stopWhen and timeout if necessary.

Note: you can use threadBoxCtrlC function as a stopWhen function to exit whenever a Ctrl-C is hit on the keyboard by a user.

Using buffers in nAttrMon

If you have a large number of nAttrMon inputs and probably a smaller number of outputs the probability of having sets of inputs that all run at the same time is high.

There is no problem in this as nAttrMon will handle each input execution in parallel but you might find, by debugging nAttrMon, that your outputs and/or validations might be "overwhelmed".

The symptoms are usually big spikes on cpu usage (when all inputs start) and an overall delay of execution of plugs (plugs that should execute afterwards take longer than expected to start).

So if you have n inputs and all of them change attributes you will have n changes to nattrmon::cvals. If your outputs subscribe nattrmon::cvals changes each of those outputs will be triggered also n times to process each change. Of course the larger number of inputs the more threads will be executing in parallel.

This is usually the reason for larger than expected cpu usage spikes and delay of execution of other plugs because nAttrMon is busy executing outputs.

The same applies between inputs & validations and validations & outputs where the nattrmon::warnings channel is also used.

So what can be done to aliviate the load on outputs and validations?

nAttrMon buffers

The answer is buffering changes to nattrmon::cvals and nattrmon::warnings.

OpenAF provides a special channel type called 'buffer'. This channel implementation can receive a set of operations (e.g. set, setall, unset, unsetall) from a source channel and only trigger those operations, as batch operations (e.g. setall and unsetall), on a target channel on two conditions: maximum time after the first operation was buffered and maximum number of operations buffered.

The end result in nAttrMon is a single output execution for n inputs/validations executed at the same time and a single validation execution for _n_inputs executed at the same time. This solves part of the cpu usage spikes and potential delays on other plug executions when dealing with multiple simultaneous (or near simultaneous) inputs or validations.

What needs to be changed to use nAttrMon buffers

First activate the following flags on the nattrmon.yaml configuration file (based on the provided nattrmon.yaml.sample):

BUFFERCHANNELS: true
BUFFERBYNUMBER: 100
BUFFERBYTIME  : 1000

This creates two new channels: nattrmon::cvals::buffer and nattrmon::warnings::buffer. These channels will only change after nattrmon::cvals and nattrmon::warnings have 100 changes or more than 1000ms passed since the first change not reflected on the buffer channels.

Inputs

The inputs will continue to use internally nattrmon::cvals. So no changes are needed on inputs.

Validations

The validations can now use the nattrmon::cvals::buffer since they will also benefit from the buffering as the outputs do:

validation:
   name            : Set of generic validations
   chSubscribe     : nattrmon::cvals::buffer
   waitForFinish   : false
   killAfterMinutes: 5
   execFrom        : nValidation_Generic
   execArgs        :
   [...]

Note: you can use killAfterMinutes in any plug to ensure that if it goes over a specific amount of minutes nAttrMon will terminate the execution of that plug as a sanity measure. Use with care.

Note for custom validations: your validation should be able to handle a single change (args.op == "set") or an array of changes (args.op == "setall").

Outputs

The outputs can now use the nattrmon::cvals::buffer and nattrmon::warnings::buffer:

output:
    name         : Output Warnings by email
    chSubscribe  : nattrmon::warnings::buffer
    waitForFinish: false
    onlyOnEvent  : false
    [...]

output:
    name          : Output ES values
    chSubscribe   : nattrmon::cvals::buffer
    considerSetAll: true
    waitForFinish : true
    onlyOnEvent   : true

Note for custom outputs: your output should be able to handle a single change (args.op == "set") or an array of changes (args.op == "setall").

How to connect directly to a local JVM via JMX

OpenAF's JMX client enables the creation of JMX "client" scripts. To connect the "client" to the target JVM you need an JMX URL.

But if the JVMs are local to the same host you can request a "special" URL that will connect to them without the need to open any remote JMX ports and change the target Java startup arguments.

To do this first list the "recognized" running JVMs on your host:

> plugin("JMX");
> af.fromJavaMap(jmx.getLocals());
+---------+-----+-------+-----------------------------------+
| Locals: | [0] | name: | /openaf/openaf.jar --console      |
|         |     |   id: | 24720                             |
+---------+-----+-------+-----------------------------------+
|         | [1] | name: | /myApp/myapp.jar                  |
|         |     |   id: | 15004                             |
+---------+-----+-------+-----------------------------------+

Using the id number, for the local Java JVM you want to target, now call attach2Local like this, creating a new JMX object instance:

> var localJMX = new JMX(String(jmx.attach2Local(15004).get("URL")));

If successfull you will now be able to use the localJMX JMX client object:

> var obj = jmx.getObject("java.lang:type=Runtime");
> String(obj.get("ClassPath"));
"/myApp/myapp.jar"

Setting a proxy

Usually the default java proxy settings cover pretty much all cases. But there are a few cases where it would be helpful to programatically set a proxy.

Let's start with the basic proxy settings.

Setting a HTTP/HTTPs proxy

To set a HTTP/HTTPs proxy you will need the proxy host and the proxy port:

ow.loadObj();
ow.obj.setHTTPProxy("a.host", 1234);
ow.obj.setHTTPSProxy("a.host", 1234)

After this all HTTP/HTTPs communications in OpenAF will use the provided proxy.

Keep in mind that any external processes/scripts executed from OpenAF (e.g. executing sh("someCommand")) won't inherit these proxy settings.

Setting a SOCKS proxy

Nevertheless the most interesting case is connecting to a SOCKS proxy. If you are on machine A but you need to access resources through machine B running OpenAF on machine A like it was running on machine B you can establish a simple dynamic port forwarding with SSH. To establish this simple execute on machine A:

ssh -D 12345 myuser@machine.b

Afterwards, in OpenAF, just execute:

ow.loadObj();
ow.obj.setSOCKSProxy("127.0.0.1", 12345);

Now all network connections, from OpenAF, will go through th socks proxy actually feeling like you are executing OpenAF on machine B.

Note: if a SOCKS proxy user & password is needed you can add it as extra parameters.

Keep in mind that any external processes/scripts executed from OpenAF (e.g. executing sh("someCommand")) won't inherit these proxy settings.

Setting a FTP proxy

Although less used you can also set a proxy for FTP connections:

ow.loadObj();
ow.obj.setFTPProxy("a.host", 1234);

Dynamically adding a custom JDBC driver

The latest OpenAF versions enable the dynamic loading of custom JDBC drivers.

Let's take, for example, the CSV JDBC driver. After downloading the jar file to an empty folder let's also create a sample CSV file (test.csv):

"key";"value"
1; "Item 1"
2; "Item 2"
3; "Item 3"

Then, on an OpenAF script or console execute:

> loadExternalJars(".")
> var db = new DB("org.relique.jdbc.csv.CsvDriver", "jdbc:relique:csv:.?separator=;&fileExtension=.csv", "sa", "sa");
> sql db select * from test
key|  value
---+---------
1  | "Item 1"
2  | "Item 2"
3  | "Item 3"
[#3 rows]

So, what happened:

• The loadExternalJars function tries to dynamically load all .jar files on the path provider (using the OpenAF's custom class loader).
• When creating the DB object instance, OpenAf will try to find the provided JDBC class driver and will load it through an internal "proxy", if needed.
• The returned DB object instance will work as the H2, PostgreSQL, etc... drivers (within the limits of the JDBC driver).

nAttrMon multiple plugs in one file

When adding any nAttrMon plug (either inputs, validations or outputs), the advisable setup is to have a structured set of files on a folder hierarchy. In this structure each file is usually a single plug definition.

Nevertheless once you get more advanced setups there are cases where you might want to have more than one plug per file taking advantage of the plug "ignore"/"disabling" mechanisms.

Let's take a simple example of a nAttrMon input plug:

input:
  name         : Database queries
  cron         : "*/10 * * * *"
  waitForFinish: true
  onlyOnEvent  : true
  execFrom     : nInput_DB
  execArgs     :
     key: MYDB
     sqls:
        Database/Status XYZ: >
           SELECT control "Control", value "Value"
           FROM status
           WHERE control in ('X', 'Y', 'Z')

        Database/Status ABC: >
           SELECT control "Control", value "Value"
           FROM status
           WHERE control in ('A', 'B', 'C')

In this example both queries will execute every 10 minutes. But you might want different cron schedules temporarily to find some issue on ABC controls.

Usually you would create two files, comment the ABC query on the first and just have the ABC on the second. But now if you have to quickly ignore all database queries you will have to remember the extra temporary plug you created before. Could there be a better alternative?

The answer is yes. You can keep both SQLs on the same file and temporarily have different cron settings:

input:
  #--------------------------------
  - name         : Database queries
    cron         : "*/10 * * * *"
    waitForFinish: true
    onlyOnEvent  : true
    execFrom     : nInput_DB
    execArgs     :
       key: MYDB
       sqls:
          Database/Status XYZ: >
             SELECT control "Control", value "Value"
             FROM status
             WHERE control in ('X', 'Y', 'Z')

          #Database/Status ABC: >
          #   SELECT control "Control", value "Value"
          #   FROM status
          #   WHERE control in ('A', 'B', 'C')

  #--------------------------------------------
  - name         : Database queries (temporary)
    cron         : "*/1 * * * *"
    waitForFinish: true
    onlyOnEvent  : true
    execFrom     : nInput_DB
    execArgs     :
       key: MYDB
       sqls:
          Database/Status ABC : >
             SELECT control "Control", value "Value"
             FROM status
             WHERE control in ('A', 'B', 'C')             

What's the difference? Instead of defining a map, if you define an array nAttrMon it will consider two plugs instead of one. So now you can control the temporary different cron setting by just commenting lines while keeping everything in the same file.

Note: always have different plug names, otherwise the last will overwrite the previous.

Handling failure on REST calls

When calling other services through REST you always need to antecipate failure. It might be a network issue or the failure might be due to the service being down.

The default behaviour

In OpenAF when making a REST call if it fails it will look similar to this:

var res = $rest().get("http://127.0.0.1:12345");
if (isDef(res.error)) {
    logErr("There was an error contacting the service: " + res.error);
} else {
    // Process the result
}

Nevertheless you can add the throwExceptions flag so you can handle it differently:

try {
    var res = $rest({ 
        throwExceptions: true
    })
    .get("http://127.0.0.1:12345");

    // Process the result
} catch(e) {
    logErr("There was an error contacting the service: " + String(e));
}

Another simpler, more elegant, way

But your code starts getting full of exception handling and you just wanted a non-critical information for which a default reply it's okay. Let's say you have a service that returns an array of favourite fruits given a user.
The expected behaviour when everything is working would be:

So your code could look like this:

addFavouriteFruitsToDashboard(
    $rest()
    .post("http://127.0.0.1:12345/getFruits", { user: currentUser })
);

The only problem is if the service fails. Then you will have to either check the result or try/catch the function addFavouriteFruitsToDashboard call. But the $rest() shortcut can handle that for you with the option default. This option let's you define a default map in case something goes wrong. You still get the error entry but you can choose to handle it or note.
The previous code now can look more like this:

addFavouriteFruitsToDashboard(
    $rest({
        default: { 
            user  : currentUser, 
            fruits: []
        }
    })
    .post("http://127.0.0.1:12345/getFruits", { user: currentUser })
);

So in case of error, you will always have, at least, an empty fruits array. Because calling the _$rest()_function now with an error on the server (like turning it off) results in:

By the way...

By the way, if you want to test it yourself and need a quick dirty rest service you just have to run the following lines:

ow.loadServer();

var hs = ow.server.httpd.start(12345);
ow.server.httpd.route(hs, { 
    "/getFruits": r => {
        return ow.server.rest.reply("/getFruits", r, 
            (idxs, data, req) => { 
                return { 
                    user: data.user, 
                    fruits: [ 
                        'banana', 
                        'apple', 
                        'orange' 
                    ] 
                } 
            }
        )
    }
});

log("I'm ready!");
ow.server.daemon();

Quick OpenAF streams conversion

When using input or output streams in OpenAF you might want to quickly "convert them" to string or array of bytes and vice-versa. Usually for testing proposes but you can find the following functions handy any time.

Converting from String/Bytes to an Input Stream

If you need to get a string or an array of bytes into an input stream you can use af.fromBytes2InputStream or af.fromString2InputStream. Here is an example:

ioStreamReadLines(af.fromString2InputStream("Hello World!!\n"), (line) => {
    print(line);
});

Creating and converting an OutputStream to String/Bytes

If you wanna check what it's being output to a given output stream you can create an "in-memory" OutputStream (actually a java.io.ByteArrayOutputStream):

var ostream = af.newOutputStream();
ioStreamCopy(ostream, af.fromString2InputStream("Hello World!\n"));

// Converting to string
print(ostream.toString());  

// Converting to an array of bytes
//var b = ostream.toByteArray(); 

If you don't wanna copy from another input stream and just want to set the contents of the output stream:

var ostream = af.fromString2OutputStream("Hello world!\n");

Which is prettry much equivalent to the previous example. Of course there is also a af.fromBytes2OutputStream function.

Converting and InputStream to String/Bytes

Ok, now we have an input stream that we just wanna check it's contents on the form of an array of bytes:

var istream = io.readFileStream("myfile.txt");
var contents = af.fromInputStream2Bytes(istream);

Again, a af.fromInputStream2String is also available.

Getting a DB table's columns/fields

Ever wonder how the OpenAF-console can do commands like "dsql" to list all the columns of a table (or a database object) and quickly? It's all on the JDBC metadata for any query over all columns. No need to go to the specific database column catalog.

Getting the metadata

For each of the tables identified on the catalog of the corresponding database:

1. Performe a simple query to access the table's metadata BUT getting the JDBC ResultSet object:

var db = new DB(jdbcURL, jdbcUsername, jdbcPassword);
var rs = db.qsRS("select * from \"" + schemaName + "\".\"" + tableName + "\"");

Note: If you want to use a specific JDBC driver class just replace by "new DB(jdbcDriver, jdbcURL, jdbcUsername, jdbcPassword)" otherwise it will try to guess from the jdbcURL for known drivers.

2. Get the number of columns from the JDBC ResultSet object:

var numberOfColumns = rs.getMetadata().getColumnCount();

3. Get the columns' metadata:

var columns = []; 
for(let ci = 1; ci <= numberOfColumns; ci++) { 
    columns.push({ 
        name : rs.getMetaData().getColumnName(ci), 
        type:  rs.getMetaData().getColumnTypeName(ci).toUpperCase(), 
        size : rs.getMetaData().getColumnDisplaySize(ci),
        scale: rs.getMetaData().getScale(ci) 
    })
};

4. (please) Close the result set object and any possible transaction (e.g. because PostgreSQL):

rs.close();
db.rollback();

And there you have it, a columns array where each map entry has the necessary column information of the specific table:

> table columns
     name      |  type   |size|scale
---------------+---------+----+-----
ID             |NUMERIC  |11  |0
SOME           |VARCHAR  |35  |0
TKEY           |VARCHAR  |512 |0
VALUE          |NUMERIC  |25  |6
CREATED_BY     |VARCHAR  |64  |0
CREATED_DATE   |TIMESTAMP|22  |0
MODIFIED_BY    |VARCHAR  |64  |0
MODIFIED_DATE  |TIMESTAMP|22  |0
[#8 rows]

What about the list of tables?

Ok, for the list of tables you might really need to go the database's catalog. Here are some examples:

Oracle

To get all tables in a specific Oracle schema:

var tables = mapArray(db.qs("select owner || '.' || table_name from all_tables where owner = ?", ['mySchema'], true).results, [ "table_name" ]);

PostgreSQL

To get all tables in a specific PostgreSQL schema:

var tables = mapArray(db.qs("select table_schema || '.' || table_name from information_schema.tables where table_schema = ?", ['mySchema'], true).results, [ "table_name" ]);

H2

To get all tables in a specific H2 schema:

var tables = mapArray(db.qs("select table_schema || '.' || table_name from information_schema.tables where table_schema = ?", ['mySchema'], true).results, [ "table_name" ]);

Note: Yes, it's equal to PostgreSQL

Quickly build a REST service in OpenAF

Whenever you need a REST service on a rush you can have a fully functional REST service with OpenAF in a couple of minutes.

1. The function(s)

Let's start with a sample function in OpenAF that you need it to be available as a REST service:

function addNumbers(inputMap) {
    inputMap   = _$(inputMap).isMap().default({});
    inputMap.a = _$(inputMap.a).default(0);
    inputMap.b = _$(inputMap.b).default(0);

    return {
        a: Number(inputMap.a),
        b: Number(inputMap.b),
        res: Number(inputMap.a) + Number(inputMap.b)
    }
}

Advice: it's easier if it receives a map and returns a map.

Now save the addNumbers function in mylib.js.

2. Install some helper oPacks

$ opack install ojob-common
$ opack install openaf-templates

3. Setup the main oJob

Copy the openaf-templates/ojobs/restServices/restServices.yaml to your current folder, together with mylib.js from step 1, with the name main.yaml.

$ cp openaf-templates/ojobs/restServices/restServices.yaml main.yaml

Now let's edit the main.yaml:

1. Change the piddir line to "piddir: &PIDDIR myService.pid"
2. On the "Prepare my service" job change to something like this:

  - name: Prepare my service
    to  : REST Service
    args: 
      uri       : /add     # That's your new URI
      port      : *PORT

      # Your code for the GET verb
      execGET   : |
        loadLib("mylib.js");
        return addNumbers(request.params);

      # Your code for the POST verb
      execPOST  : |
        loadLib("mylib.js");
        return addNumbers(data);
      execPUT   : "return { result: 0 }"
      execDELETE: "return { result: 0 }"

You can quickly test it by executing:

$ ojob main.yaml

Now, on your favourite REST client, execute something similar to:

$ curl "http://127.0.0.1:8090/add?a=5&b=5"
{"a":5,"b":5,"res":10}
$ curl -XPOST "http://127.0.0.1:8090/add" -d "{'a':1,'b':3}" -H "Content-Type: application/json"
{"a":1,"b":3,"res":4}

It's working!

Let's docker it

Create a Dockerfile:

FROM openaf/openaf-ojobc

COPY mylib.js /openaf/mylib.js
COPY main.yaml /openaf/main.yaml

Build it:

$ docker build . -t myservice

Start it:

$ docker run --rm -ti -p 8090:8090 myservice

Test it:

$ curl "http://127.0.0.1:8090/add?a=5&b=5"
{"a":5,"b":5,"res":10}
$ curl -XPOST "http://127.0.0.1:8090/add" -d "{'a':1,'b':3}" -H "Content-Type: application/json"
{"a":1,"b":3,"res":4}

It's that easy.

Using ElasticSearch in OpenAF

OpenAF comes with builtin support for ElasticSearch. So, among other things, it can log directly to ElasticSearch whenever you use the log* functions. Nevertheless there is an oPack to make it all a little easier. Let's start by installing it:

$ opack install elasticsearch

The ElasticSearch oPack is basically a wrapper around some ElasticSearch functionality aiming to make easier daily ElasticSearch operations (e.g. creating/deleting indexes, export/import data, reindex indexes, etc...). We are going to describe some basic functionality. To start you need to instantiate an object poiting to your ElasticSearch cluster:

load("elasticsearch.js");
var es = new ElasticSearch("http://my.elastic.cluster:9200", "myUser", "myPassword");

Note: the user and password are optional and only needed if your ElasticSearch cluster is protected with user/password.

Creating an index

To create an index you just need to:

> es.createIndex("test", 1, 1);

This will create a new index "test" with 1 primary shard and 1 replica. This operation isn't usually necessary as ElasticSearch will just create any index you try to use.

You can check that it was created by executing:

> es.getIndices()

And checking the resulting array.

Adding/Changing data

To interact with ElasticSearch at the data level the easiest way in OpenAF is to use an OpenAF channel. To easily create an OpenAF channel to connect to the "test" index just execute:

es.createCh("test", ["canonicalPath"], "testCh");

This creates an OpenAF channel "testCh" that allows you to access the test index. You also need to provide a list of keys that can be used to retrieve an unique record (in this case "canonicalPath").

Note: You can also define a pattern of indexes instead of the exact name but you will be limited just to .get* functions. It's also possible instead of a string to provide a function that returns the name of the ElasticSearch index to use (e.g. for example, appending the current date).

To add new data just use the newly created channel:

$ch("testCh").set({
    canonicalPath: "/",
}, {
    isDirectory: yes,
    isFile: no,
    filename: "noname",
    filepath: "/",
    canonicalPath: "/"
    lastModified: 0,
    createTime: 0,
    size: 0
});

Retriving data

If you know how to use OpenAF channels now it becomes easier. To get a map value you just need:

var fileMap = $ch("test").get({ canonicalPath: "/" });

Batch get/set data

To batch insert/change data into ElasticSearch you need to divide all the requests in smaller chuncks of data. Then you just use the .setAll function:

$ch("test").setAll(["canonicalPath"], io.listFiles("/some/path").files);

To obtain a list of keys or values you can use getAll or getKeys:

var q = (m, s) => { return { size: s, query: { query_string: { query: m }}}; };
var listOfSmallFiles = $ch("test").getAll(q("size:<1 AND ", 1000));

Unlike the usual getAll and getKeys behaviour the elasticsearch OpenAF channel type will only retrieves a specific amount of records (default to 10). In this example we created a small function to allow you to query using Lucene query syntax and specifying the limit number of records you wish to retrieve (within the search API limits). Check the ElasticSearch search API for more.

Delete data

To delete data simply use the unset/unsetAll functions:

$ch("test").unset({ canonicalPath: "/" });

Quickly validate a YAML file

One of the cons of using YAML (e.g. and any other identation based languages) is forgetting about a tab or a wrong spacing that leads to errors. For example:

jobs:
  #-------------------
  - name: Hello World!
  exec: print('Hello World!')

todo:
  - Hello World!

The problem with this YAML file is on the 4th line since the 3rd line started a map as part of the jobs array but the 4th line is a map entry. One way to quickly check this is using another "one-liner":

$ openaf -i script -e "io.readFileYAML('aYAMLFile.yaml')"

In this case the result would be:

Error while executing operation: YAMLException: bad indentation of a mapping entry at line 4, column 3:
      exec: print('Hello World!')
      ^ (js-yaml_js#1)

Solving the issue:

jobs:
  #-------------------
  - name: Hello World!
    exec: print('Hello World!')

todo:
  - Hello World!

Executing the same one-liner now the result is no errors:

$ openaf -i script -e "io.readFileYAML('aYAMLFile.yaml')"
$

How to copy CLOBs between two databases

Specially in Oracle is not very easy to get the CLOB field value from a source database and insert/update it on another CLOB field on a target database.

In OpenAF the DB.q and DB.u functions are "CLOB/BLOB" aware and will try to convert them to strings to make it seamless. But there is the DB.Lob functions to handle them in particular.

The next example shows how to retrieve CLOB values from one database and inserting them on a temporary table on a target database:

log("Connecting...");

var db1 = new DB("jdbc:oracle:thin:@//1.2.3.1:1521/SOURCE", "loginSOURCE", "passwordSOURCE");
var db2 = new DB("jdbc:oracle:thin:@//1.2.3.2:1521/TARGET", "loginTARGET", "passwordTARGET");

log("Retrieving data...")

var res = db1.q("select obj_uuid, obj_definition from objects_table");

log("#" + res.results.length + " records retrieved");

log("Copying data...");
db2.u("truncate table TEMP_TABLE"); // Assuming you have a TEMP_TABLE already created on db2

var c = 0;
for(i in res.results) {
   var line = res.results[i];
   c += db2.uLobs("insert into TEMP_TABLE (OBJ_UUID, OBJ_DEFINITION) values (:1, :2)", [ line.OBJ_UUID, line.OBJ_DEFINITION ]);
}

log("#" + c + " records copied.");

db2.commit();
db2.close();
db1.close();

log("Done");

The result will be similar to:

Thu Apr 15 2015 12:32:25 GMT-0400 (EDT) | INFO | Connecting...
Thu Apr 15 2015 12:32:25 GMT-0400 (EDT) | INFO | Retrieving data...
Thu Apr 15 2015 12:32:26 GMT-0400 (EDT) | INFO | #3497 records retrieved
Thu Apr 15 2015 12:32:26 GMT-0400 (EDT) | INFO | Copying data...
Thu Apr 15 2015 12:32:30 GMT-0400 (EDT) | INFO | #3497 records copied.
Thu Apr 15 2015 12:32:30 GMT-0400 (EDT) | INFO | Done

Easily add a SSL certificate to access an URL

When accessing an URL that hosts a self-signed SSL certificate you might need to add the certificate to your's Java runtime environment trusted certificates. If you search around the internet you will find several articles explaining how to do this using Java's keytool utility (adding the certificate to the Java's keystore).

You might find a very old piece of code, posted on Sun's blog, which would connect to a host on a specific port, examine the certificate and added it to the Java's keystore. That code was packed in an OpenAF's oPack for ease of use.

Install it

To install it just execute:

$ opack install InstallCert

To use it

To use it change the current directory to the JRE/JDK's security path. This is usually on $JAVA_HOME/jre/lib/security. To use it:

$ cd $JAVA_HOME/jre/lib/security
$ opack exec InstallCert some.host:443

Note: you can use a different port (instead of 443) if needed.

During the execution you will be prompted to add the certificate to the trusted keystore by answering the number of the certificate. If needed execute more than one time to add all certificates or ensure that the certificate is now trusted (e.g. won't ask if you want to add it).

If the keystore has a different password

The code will generate a file called jssecacerts. If you already have a similar file with a different password from the default you may enter it like this:

$ cd $JAVA_HOME/jre/lib/security
$ opack exec InstallCert some.host:443 myNewPassword

oJob one-liners

On the article Micro remote HTTP file browser we described a fast way to build a micro HTTP server serving files from the current folder. But some (myself included) need faster ways to do it instead of creating an oJob file and executing it.

So doing the same thing on one line that you can just copy+paste whenever you need can be very pratical. But you will be sacrifying readability by anyone else and you. Since oJobs can be used in YAML or JSON this enables you to build a YAML oJob file and then convert it to JSON string where initilization parameters can be easily change. Additionally you can convert back to YAML very easily.

Example

Let's use the micro remote HTTP file browser example. Since YAML provides some features that JSON doesn't (like anchors) the original YAML needs some adaptation:

httpd.yaml

init:
  port: 8080
  path: "."
  uri : "/"

ojob:
  daemon    : true
  sequential: true
  opacks    :
    - oJob-common

include:
  - oJobHTTPd.yaml

todo:
  - name: Init
  - name: HTTP Start Server
    args: "({ port: global.init.port, mapLibs: true })"
  - name: HTTP File Browse
    args: "({ port: global.init.port, path: global.init.path, uri: global.init.uri })"

jobs:
  # ----------
  - name: Init
    exec: "global.init = args.init;"

Convert to a JSON one-liner

Then to convert this YAML file into a JSON string use openaf-console and execute:

> print(stringify(io.readFileYAML("httpd.yaml"), void 0, ""))

{"init":{"port":8080,"path":".","uri":"/"},"ojob":{"daemon":true,"sequential":true,"opacks":["oJob-common"]},"include":["oJobHTTPd.yaml"],"todo":[{"name":"Init"},{"name":"HTTP Start Server","args":"({ port: global.init.port, mapLibs: true })"},{"name":"HTTP File Browse","args":"({ port: global.init.port, path: global.init.path, uri: global.init.uri })"}],"jobs":[{"name":"Init","exec":"global.init = args.init;"}]}

Use the JSON one-liner

Then to use this JSON as an one-liner:

> oJobRun( /* one-liner begin */  {"init":{"port":8080,"path":".","uri":"/"},"ojob":{"daemon":true,"sequential":true,"opacks":["oJob-common"]},"include":["oJobHTTPd.yaml"],"todo":[{"name":"Init"},{"name":"HTTP Start Server","args":"({ port: global.init.port, mapLibs: true })"},{"name":"HTTP File Browse","args":"({ port: global.init.port, path: global.init.path, uri: global.init.uri })"}],"jobs":[{"name":"Init","exec":"global.init = args.init;"}]}  /* one-liner end */ );

To stop just hit "Ctrl-C".

Making changes

If you need to change the folder, port or uri used it's easy to just direclty change on the first init map:

{"init":{"port":8080,"path":".","uri":"/"},

To convert it back to YAML:

io.writeFileYAML("httpd.yaml", /* one-liner begin */  {"init":{"port":8080,"path":".","uri":"/"},"ojob":{"daemon":true,"sequential":true,"opacks":["oJob-common"]},"include":["oJobHTTPd.yaml"],"todo":[{"name":"Init"},{"name":"HTTP Start Server","args":"({ port: global.init.port, mapLibs: true })"},{"name":"HTTP File Browse","args":"({ port: global.init.port, path: global.init.path, uri: global.init.uri })"}],"jobs":[{"name":"Init","exec":"global.init = args.init;"}]}  /* one-line end */ );

Note: since you lose comments we would recommed you to just use the original YAML file.

Generating SQL with OpenAF templates

Since OpenAF is Java and Javascript it can take advantage of the two languages. The javascript Handlebars library comes already bundled in OpenAF and provides the ability to create any kind of text templates which can be reused and changed without any code changes to a script.

Simple example

To show this let's take the hypothetical example of creating several reference tables (for the sake of space on the article we are going to consider that the fields are fixed). Handlebars will render any template using a javascript object. So let's create one for our example:

tables.json

{
  "appUser": "PROJ_APP",
  "datUser": "PROJ_DAT",
  "tablespace": "PROJ_TAB",
  "tables": [
    {
      "tableName": "tab_1"
    },
    {
      "tableName": "tab_2"
    },
    {
      "tableName": "TAB_3"
    }
  ]
}

So we are specifying the application (app) and data (dat) users to use, the tablespace to use and the table names. Now let's create a Handlebars template file (hbs):

table_creation.hbs

-- Creating tables
--

{{#each TABLES}}
-- Table for {{../datUser}}.{{tableName}}
CREATE TABLE {{../datUser}}.{{tableName}} (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE {{../tablespace}}
{{/each}}

Looking at the JSON object this template is iterating on the tables array, getting the tableName. datUser and tablespace are gather from the parent to the tables array.

And let's create the OpenAF script to bundle everything:

ow.loadTemplate();
print(ow.template.parseHBS("table_creation.hbs", io.readFile("tables.json"));

And that's it, when you run it you will get this:

-- Creating tables
--

-- Table for PROJ_DAT.tab_1
CREATE TABLE PROJ_DAT.tab_1 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.tab_2
CREATE TABLE PROJ_DAT.tab_2 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.TAB_3
CREATE TABLE PROJ_DAT.TAB_3 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB

But if we are creating tables in a data schema for which we want to create synonyms on the application schema. Well, now we just need to change the template for that:

table_creation.hbs

-- Creating synonyms
--

{{#each TABLES}}
-- Synonym for {{../appUser}}.{{tableName}}
CREATE OR REPLACE SYNONYM {{../appUser}}.{{tableName}} FOR {{../datUser}}.{{tableName}};
GRANT ALL ON {{tableName}} TO {{../appUser}} WITH GRANT OPTION;
{{/each}}

-- Creating tables
--

{{#each TABLES}}
-- Table for {{../datUser}}.{{tableName}}
CREATE TABLE {{../datUser}}.{{tableName}} (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE {{../tablespace}}
{{/each}}

And execute the unchanged OpenAF script:

-- Creating synonyms
--

-- Synonym for PROJ_APP.tab_1
CREATE OR REPLACE SYNONYM PROJ_APP.tab_1 FOR PROJ_DAT.tab_1;
GRANT ALL ON tab_1 TO PROJ_APP WITH GRANT OPTION;
-- Synonym for PROJ_APP.tab_2
CREATE OR REPLACE SYNONYM PROJ_APP.tab_2 FOR PROJ_DAT.tab_2;
GRANT ALL ON tab_2 TO PROJ_APP WITH GRANT OPTION;
-- Synonym for PROJ_APP.TAB_3
CREATE OR REPLACE SYNONYM PROJ_APP.TAB_3 FOR PROJ_DAT.TAB_3;
GRANT ALL ON TAB_3 TO PROJ_APP WITH GRANT OPTION;

-- Creating tables
--

-- Table for PROJ_DAT.tab_1
CREATE TABLE PROJ_DAT.tab_1 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.tab_2
CREATE TABLE PROJ_DAT.tab_2 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.TAB_3
CREATE TABLE PROJ_DAT.TAB_3 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB

Using helpers

We could stop the example but if you notice some table names are not all upper cases. And we like our generated SQL to be pretty :) To do that we just make some slights changes to the OpenAF script to include a helper function:

ow.loadTemplate();
ow.template.addHelper("upper", function(aString) { return aString.toUpperCase();})
print(ow.template.parseHBS("table_creation.hbs", io.readFile("tables.json"));

and use the function inside the template:

table_creation.hbs

-- Creating tables
--

{{#each TABLES}}
-- Table for {{../datUser}}.{{upper tableName}}
CREATE TABLE {{../datUser}}.{{UPPER tableName}} (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE {{../tablespace}}
{{/each}}

The result:

-- Creating tables
--

-- Table for PROJ_DAT.TAB_1
CREATE TABLE PROJ_DAT.TAB_1 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.TAB_2
CREATE TABLE PROJ_DAT.TAB_2 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.TAB_3
CREATE TABLE PROJ_DAT.TAB_3 (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE PROJ_TAB

Using conditions

Well, not all the tables will be equally created. We might not want Oracle logging for some. Let's note that on the json data:

tables.json

{
  "appUser": "PROJ_APP",
  "datUser": "PROJ_DAT",
  "tablespace": "PROJ_TAB",
  "tables": [
    {
      "tableName": "tab_1"
    },
    {
      "tableName": "tab_2",
      "nologging": true
    },
    {
      "tableName": "TAB_3"
    }
  ]
}

And change the template:

table_creation.hbs

-- Creating tables
--

{{#each TABLES}}
-- Table for {{../datUser}}.{{tableName}}
CREATE TABLE {{../datUser}}.{{tableName}} (col1 NUMBER(8), col2 VARCHAR(500)) {{#if nologging}}NOLOGGING{{/IF}} TABLESPACE {{../tablespace}}
{{/each}}

Executing the unchanged script:

-- Creating tables
--

-- Table for PROJ_DAT.tab_1
CREATE TABLE PROJ_DAT.tab_1 (col1 NUMBER(8), col2 VARCHAR(500))  TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.tab_2
CREATE TABLE PROJ_DAT.tab_2 (col1 NUMBER(8), col2 VARCHAR(500)) NOLOGGING TABLESPACE PROJ_TAB
-- Table for PROJ_DAT.TAB_3
CREATE TABLE PROJ_DAT.TAB_3 (col1 NUMBER(8), col2 VARCHAR(500))  TABLESPACE PROJ_TAB

Using template parts for increase reusability

Well this way we are going to end with lots of hbs files and little reusability besides copy+paste. Can we solve that? Yes, we case use partials. So let's create two sub templates for synonyms and tables:

table_creation.hbs

-- Creating tables for {{for}}
--

{{#each TABLES}}
-- Table for {{../datUser}}.{{upper tableName}}
{{#if NUMBER}}
CREATE TABLE {{../datUser}}.{{UPPER tableName}} (col1 NUMBER(8), col2 NUMBER(25)) TABLESPACE {{../tablespace}}
{{ELSE}}
CREATE TABLE {{../datUser}}.{{UPPER tableName}} (col1 NUMBER(8), col2 VARCHAR(500)) TABLESPACE {{../tablespace}}
{{/IF}}
{{/each}}

Now one for the synonyms:

syn_creation.hbs

-- Creating synonyms for {{for}}
--

{{#each TABLES}}
-- Synonym for {{../appUser}}.{{upper tableName}}
CREATE OR REPLACE SYNONYM {{../appUser}}.{{UPPER tableName}} FOR {{../datUser}}.{{UPPER tableName}};
GRANT ALL ON {{UPPER tableName}} TO {{../appUser}} WITH GRANT OPTION;
{{/each}}

And a main template to refer to these two sub templates (partials):

sql_creation.hbs

{{> synonyms FOR="ref tables"}}
{{> TABLES   FOR="ref tables"}}

Now we just need to alter the OpenAF script to register the sub templates (partials):

ow.loadTemplate();
ow.template.addHelper("upper", function(aString) { return aString.toUpperCase();})
ow.template.addPartial("tables", io.readFileString("table_creation.hbs"));
ow.template.addPartial("synonyms", io.readFileString("syn_creation.hbs"));
print(ow.template.parseHBS("sql_creation.hbs", io.readFile("tables.json"));

And we are done. We now can improve the synonyms and table creation separately and reuse those for different proposes while keep the reference to the same template file.

What if we want to generate something else? Is this only for SQL?

Well, it's text based. Originally Handlebars is used for web templating parsing templates using javascript. So just change the template:

SQL generation report
---------------------

Using the schemas:
- DAT = {{datUser}}
- APP = {{appUser}}

and the tablespace {{tablespace}}, the following reference Oracle objects DDL were generated:

{{#each tables}}
- The {{#if number}}number {{/if}}reference table {{upper tableName}} for the schema {{../datUser}} and tablespace {{../tablespace}}.
- The synonym from {{../datUser}}'s {{upper tableName}} for {{../appUser}}.
{{/each}}

and it's done:

SQL generation report
---------------------

Using the schemas:
- DAT = PROJ_DAT
- APP = PROJ_APP

And tablespaces:
- LARGE = PROJ_TAB

The following reference Oracle objects DDL were generated:

- The reference table TAB_1 for the schema PROJ_DAT and tablespace PROJ_TAB.
- The synonym from PROJ_DAT's TAB_1 for PROJ_APP.
- The reference table TAB_2 for the schema PROJ_DAT and tablespace PROJ_TAB.
- The synonym from PROJ_DAT's TAB_2 for PROJ_APP.
- The reference table TAB_3 for the schema PROJ_DAT and tablespace PROJ_TAB.
- The synonym from PROJ_DAT's TAB_3 for PROJ_APP.

Where can I learn more about Handlebars?

There is more functionality available including pre-compiling templates for performance (ow.template.compile, ow.template.execCompiled, ow.template.loadCompiledHBS and ow.template.saveCompiledHBS). There are several examples through the internet. But you can start with:

• Expressions (escaping, whitespace control, helpers, ...): http://handlebarsjs.com/expressions.html
• Partials (dynamic partials, block partials, inline partials): http://handlebarsjs.com/partials.html
• Built-in helpers (if, unless, each, with, lookup, log, ...): http://handlebarsjs.com/builtin_helpers.html
• Block helpers: http://handlebarsjs.com/block_helpers.html

Executing code asynchronously (promises)

The execution of an OpenAF script is inherently sequential. Although it's easy to understand the execution flow (e.g. after a executes b) it might not have the best performance since it needs to wait for the end of the execution of the previous instruction to execute the next. Sometimes it makes sense because there is a dependency but in other cases it could be already executing something else asynchronously. OpenAF, through the Threads plugin, allows you access to the underneath great Java Threads functionality but the code may become hard to understand. An easy way to keep it simple but run asynchronously code is to use "promises". If you know how to use promises in other languages you will find it similar after checking the help information ("help $do"). For those that are not so familiar OpenAF tries to provide a easy and simple implementation.

You start be creating a promise for the code you want to run asynchronously:

var promise = $do(() => { 
   // my code
});

The difference is that no matter what "my code" is OpenAF will return you immediately a promise object. "my code" will execute asynchronously a.s.a.p. and the promise object will be updated accordingly. But once "my code" executes you usually want to chain other actions. To chain more code you can use ".then":

var promise = $do(() => { 
                 // my code
                 
                 return aResult;
              }).then((someResult) => {
                 // process the result

                 return processSuccess; 
              }).then((processStatus) => {
                 // etc... you get the point.
              });

What if any of these chained pieces of code throws an exception? At that point the promise won't be "fulfilled" but you can add ".catch" to handle it as you would do with a "try..catch":

var promise = $do(() => { 
                 // my code
                 
                 return aResult;
              }).then((someResult) => {
                 // process the result

                 return processSuccess; 
              }).then((processStatus) => {
                 // etc... you get the point.
              }).catch((error) => {
                 // handle the error
              });

So, what can you do with a promise object?

• You can wait for the end of the execution at any point blocking the current execution with $doWait(promise).
• If you have an array of promise objects you can use $doFirst (waits for one of the promises to be "fulfilled") and $doAll (waits for all of the promises to be "fulfilled"). Both will return a single promise object that once "fulfilled" resumes the current execution.

How does that look like:

var arrayOfPromises = [];
for (let idx in something) {
   arrayOfPromises.push($do( /* ... */ ));
}

// Yes, doFirst returns a promise, so you can chain more
arrayOfPromises.push($doFirst(arrayOfPromises)
                     .then(() => { 
                        print("One it's done!"); 
                     }));

var allPromises = $doAll(arrayOfPromises)
                  .then(() => {
                     print("Everything is done now!");
                  }));

/* do something else */

// Wait for all of them to finish
$doWait(allPromises);

Ok. But won't that just lunch a ton of threads "burning" down my machine? No. You don't need to worry about that. Underneath it will create a set of threads giving the number of identified compute cores and reuse them. So some promises will actually be waiting for others to finish and to have a thread available for them to execute.
In OpenAF there is also the function parallel4array and others to make it easy (and smarter) to asynchronously process an array since a promise for each might not actually give the best performance. But that will be the topic of another post.