|
|
This guide (intended for Java developers) contains tutorials showing how to create tables and indexes and insert and query records in FairCom DB. It uses FairCom’s JSON NAV API, which allows a program to use JSON commands to control the database, and represents all data as JSON. Additionally, it automatically maps records in tables to and from JSON.
For complete documentation on the JSON NAV API, click here.
|
|
The JSON NAV API is an asynchronous, protocol agnostic, data definition, manipulation and query language that uses JSON to convey directions to, and receive data back from, the server. The current implementation of the JSON NAV API uses the WebSocket TCP/IP protocol standard.
While it is perfectly fine to use the JSON NAV API directly from any language that supports websockets and JSON, this Java client driver library for the JSON NAV API is a convenience library to make using the JSON NAV API easy. The client driver uses a single dependency on the popular, well established Java library org.java-websocket.
Let's get started!
|
|
This tutorial uses the JSON NAV API to manage and query the data in FairCom products, such as FairCom DB, FairCom Edge, and c-treeRTG.
This code was tested with Node.js v14.16.0 and NPM 6.14.11.
Take a look at your downloaded server package. Inside you will find a /drivers folder and inside that you will find a folder named /nodejs.json.nav.
On the command line, change into the tutorial folder: <faircom>/drivers/nodejs.json.nav
Install the WebSocket library named ws.
npm install ws
Make sure your FairCom DB server:
Starting with version 12.5, FairCom servers support the JSON NAV API, which runs over WebSocket. If there is a problem, such as a port conflict, see the following:
If the server is running on the same machine as where you are testing the client driver you can now issue the following command inside the nodejs.json.nav directory:
node cli ping
And if everything worked out, you should see the following two lines as a result:
myArgs: [ 'ping' ]
response: pong
Note: Connection information is located in the file, connection.cfg, located in the tutorial directory. You can change it when you want to run the tutorial against a different FairCom server or port.
In this section we will learn how to use the client driver to work with the FairCom DB.
Inside the nodejs.json.nav folder, create a new file called test.js
Paste the following code:
const JNAClient = require('./JNAClient.js');
var cfg = JSON.parse(require('fs').readFileSync('connection.cfg', 'utf8'));
var jnaClient = new JNAClient(cfg);
async function test() {
try {
await jnaClient.open();
await jnaClient.login("ADMIN", "ADMIN");
var result = await jnaClient.listDatabases();
result.data.forEach(row =>console.log(row[0]));
} catch (error) {
console.log(error);
}
await jnaClient.logout();
jnaClient.close();
}
test();
Let's go over it line by line.
const JNAClient = require('./JNAClient.js');
Here we import the NodeJS client driver library for the JSON NAV API.
With this class we can use all of the 30 or so methods of the JSON NAV API.
var cfg = JSON.parse(require('fs').readFileSync('connection.cfg', 'utf8'));
Here we load the connection configuration file. This will become very handy as soon as we need to configure SSL certificates and keys.
var jnaClient = new JNAClient(cfg);
This line instantiates the client driver class.
async function test() {
This line defines a new function called test that allows the usage of the await keyword. We signify this by prepending the function definition with the async keyword.
try {
await jnaClient.open();
Here we start a new try catch block. This is very important to ensure that we call logout on the server, so that the server can free up resources. Rest assured to know that the server API does have timeout based cleanup methods just in case we do forget to call logout, but it is much better to call the method directly. Next we open up the websocket connection using the asynchronous function open. We can wait for the result of any asynchronous function call by prepending it with the await keyword.
await jnaClient.login("ADMIN", "ADMIN");
This line authenticates the client to the server and in response we get an authentication token back from the server. The handling of the session is completely done by the JNAClient class and usually we don't need to worry about it. Should you ever need to use it for some reason, you can access it using this property: jnaClient.authToken
var result = await jnaClient.listDatabases();
This line sends a listDatabases request to the server and in response we get back a JSON document containing an error code and message as well as a list of databases - if the call was successful. If there is an error code greater than zero present, the call will throw an exception and the regular flow of code is interrupted.
Here is what a typical JSON server response to a listDatabases call looks like:
{
"errorCode": 0,
"errorMessage": "",
"errorData": null,
"warningCode": 0,
"apiVersion": "1.0.0",
"method": "listDatabases",
"params": {
"apiVersion": "1",
"authToken": "MRT…",
"maxRecords": 1000,
"partialDatabaseName": "",
"responseOptions": {
"dataFormat": "arrays"
}
},
"dataFormat": "arrays",
"fieldDefs": [
{ "name": "database", "type": "VARCHAR" },
{ "name": "path", "type": "VARCHAR" },
{ "name": "uid", "type": "BIGINT" }
],
"data": [
[ "ctreeSQL", ".\\ctreeSQL.dbs\\SQL_SYS", 999 ],
[ "faircom", ".\\faircom.dbs\\SQL_SYS", 1001 ],
[ "test_db", ".\\test_db.dbs\\SQL_SYS", 1007 ],
[ "test_db2", ".\\test_db2.dbs\\SQL_SYS", 1013 ]
]
}
Since the JNAClient takes care of checking for errors for us, we can focus on the returned data.
So this next line of code does exactly that:
result.data.forEach(row =>console.log(row[0]));
As you can see from the example JSON above, the response contains a field called 'data'. The corresponding value is an array, so we can use the array function forEach to iterate through the elements of the array. Now the elements of that array are an array as well. And these inner arrays contain three fields: The name of the database, the server relative path of the database and the unique ID. Here we simply print out the first element, the name, since this is the most important value to use in this example.
If you run this code, for example with 'node test.js' you should get something like the following output:
ctreeSQL
faircom
test_db
test_db2
Please note that we can also request the value of the 'data' field to be an object instead of an array using this parameter:
var result = await jnaClient.listDatabases('', {
"responseOptions": { "dataFormat": "objects" }
});
Doing this results in the data field in the response being composed of an array of objects, instead of an array of arrays. The data value could look something like this:
{
…,
"dataFormat": "objects",
"data": [
{"database": "ctreeSQL", "path": ".\\ctreeSQL.dbs\\SQL_SYS", "uid": 999},
{"database": "faircom", "path": ".\\faircom.dbs\\SQL_SYS", "uid": 1001},
…,
]
}
Thus we can still iterate the data array like before, but now we can access the row elements using their name, instead of their position.
result.data.forEach(row =>console.log(row.database));
This can be helpful in many circumstances, but of course wastes a lot more space. That's why this is optional and not the default.
await jnaClient.logout();
jnaClient.close();
Now we are done with this particular test case and we can tell the server to close the session and free up any associated resources. This is done by calling the asynchronous function logout. Finally we also have to tell the WebSocket that we are done with this connection and we do so by calling the synchronous function close. That's it for our first basic test script.
Exercise: Modify the script to call the function createDatabase. Pass in a string with the name of a not yet existing database. For example:
await jnaClient.createDatabase("test_db2");
Then change the call back to listDatabases and see if your new database is listed.
The JavaScript source code for more commands is located in the folder:
<faircom>/drivers/nodejs.json.nav/tutorials
The JavaScript source code that implements the JSON NAV Wire Protocol is in the files, JNAClient.js and cli.js, which are located in the folder, <faircom>/drivers/nodejs.json.nav/
The JSON API is supposed to be fast.
You can test this for yourself using the scripts in the benchmark folder.
Here are some numbers using this test machine:
CPU |
AMD Ryzen 9 3900X 12-Core Processor 3.8 GHz - 4.3 GHz |
RAM |
32 GB DDR4 1066.4 MHz |
Southbridge |
AMD X570 rev. 51 |
NVMe SSD |
INTEL SSDPEKNW020T8 2TB |
OS |
Windows 10 Pro |
Motherboard |
MSI X570-A PRO |
Node.js |
v14.16.0 |
NPM |
v6.14.11 |
On our test machine, we tested for 2 million records.
With transaction logging on, the result was 1 minute and 4 seconds.
With transaction logging off, the result was 22 seconds.
// debug is a bit field:
// 1 means print client request
// 2 means print server response
// 3 prints both
Please take a look at the file wss.example.cfg. Copy what you need into connection.cfg.
You must also make sure that you have correct cert.pem and key.pem files.
How to create those, however, is beyond the scope of this document.
|
|
Don't hesitate to contact us with questions, suggestions, and bug reports. We want you to be successful.
FairCom • 6300 W. Sugar Creek Drive Columbia, Missouri 65203-9052 • 800.234.8180
