NotesDominoQuery sample

I have put together a small sample to demonstrate how to use NotesDominoQuery from LotusScript.

I created a new Class DQLWrapper. A little bit over the top, I know.

%REM
	Library 10010.dql
	Created Dec 30, 2018 by Ulrich Krause/singultus
%END REM
Option Declare

%REM
	Class DqlWrapper
%END REM
Public Class DqlWrapper
	
	m_query As String
	m_session As NotesSession
	m_db As NotesDatabase
	m_ndq As NotesDominoQuery
	
	%REM
		Sub New
	%END REM
	Public Sub New(strDbFilePath As String)
		Set me.m_session = New NotesSession
		Set me.m_db = me.m_session.Getdatabase("",strDbFilePath, False)
		If ( me.m_db.Isopen ) then
			Set me.m_ndq = me.m_db.Createdominoquery()
		Else
			' // do some error handling
		End if
	End Sub

	%REM
		Public function executeQuery()
	%END REM	
	Public function executeQuery() As NotesDocumentCollection
		If ( me.m_query <> "" ) then
			Set executeQuery = me.m_ndq.Execute(me.m_query)
		Else
			Set executeQuery = nothing
		End If
	End Function

	%REM
		Public Function explainQuery()
	%END REM	
	Public Function explainQuery() As String
		If ( me.m_query <> "" ) Then
			explainQuery = me.m_ndq.Explain(me.m_query)
		Else
			explainQuery = ""
		End If
		
	End Function
	
	%REM
		Public Function explainQuery()
	%END REM	
	Public Function parseQuery() As String
		If ( me.m_query <> "" ) Then
			parseQuery = me.m_ndq.parse(me.m_query)
		Else
			parseQuery = ""
		End If
		
	End Function	

	%REM
		Property query
	%END REM	
	Public Property Set query As String
		me.m_query = query
	End property
	
End Class

The query itself is executed from an agent that runs on the server. At the moment it is not possible to run a query client/ server.

Here is the code for the agent

%REM
	Agent dql.execute
	Created Dec 30, 2018 by Ulrich Krause/singultus
%END REM
Option Public
Option Declare
Use "10010.dql"

Sub Initialize
	Dim query As String
	Dim col As  NotesDocumentCollection
	query = "firstname = 'Ulrich' And lastname = 'Krause'"
	
	Dim dql As New DQlWrapper("names.nsf")
	dql.query = query
	
	If (  LCase(dql.parseQuery()) ="success" ) Then
		
		Set col = dql.executeQuery()
		MsgBox "QRY returns # docs: " + CStr(col.count)
		
		If ( col.count > 0 ) then
			Dim doc As NotesDocument
			Set doc = col.Getfirstdocument()
			MsgBox "UNID of first doc: " + doc.Universalid
		End if
	Else 
		
		MsgBox dql.explainQuery()
	End If
	
End Sub

You can now start the agent from the server console. You will get the number of documents for this query and the UNID of the first document found.

te amgr run "ec11.nsf" 'dql.execute'
[0DFC:001F-0FFC] 30.12.2018 13:49:10 AMgr: Start executing agent 'dql.execute' in 'ec11.nsf'
[0DFC:001F-0FFC] 30.12.2018 13:49:10 Agent Manager: Agent message: QRY returns # docs: 1
[0DFC:001F-0FFC] 30.12.2018 13:49:10 Agent Manager: Agent message: UNID of first doc: D8436D0F4E546BA3C12573FE0070AE88
[0DFC:001F-0FFC] 30.12.2018 13:49:10 AMgr: Agent 'dql.execute' in 'ec11.nsf' completed execution

If your query contains errors / is not understandable, you will see an output similar like this on your console

[0DFC:0020-11D0] 30.12.2018 13:59:45   Agent Manager: Agent 'dql.execute' error: Domino Query execution error:   Query is not understandable -  syntax error     - processing or expecting operator (=, <, <= …) token syntax    (Call hint: OSCalls::OSLocalAllc, Core call 
0) firstname = 'Ulrich' And lastname IS 'Krause' …………………………….^……….. ****
[0DFC:0020-11D0] 30.12.2018 13:59:45 AMgr: Agent 'dql.execute' in 'ec11.nsf' completed execution


[Solved] – PROTON_SSL=1

This is a follow up to my recent post https://www.eknori.de/2018-12-28/problem-using-proton-with-proton_ssl1-enabled/

Due to my misunderstanding and also a lack in documentation, I was not able to get PROTON with PROTON_SSL=1 and PROTON_AUTHENTICATION=client_certs running.

But thanks to our great community and a little help from Jan Krejcárek (SUTOL), I was finally able to solve the puzzle.

I must admit that I am not an expert in this field. So please bare with me if things are obvious for you. They are (not yet) for me.

My understanding of using TLS/SSL with PROTON was that I could use my existing Let’s Encrypt certificate to communicate via HTTPS instead of HTTP with my server URL.

But this is not the case. TLS/SSL encryption only enables a secured communication between the domino.db module and the PROTON addin sitting on the Domino server.

Here is an image.

The second thing that I got wrong was the fact that the existing certificate and it’s CA would be enough to enable the secured communication. In my first test, I had set PROTON_AUTHENTICATION=anonymous.

So why bother with client certificates if they are not needed at this point?

I followed Jan’s advice to try with a new generated self-signed certificate. The AppDevPack contains shell scripts to create some sample certs and also lets you create a keyring file that can be used with PROTON.

I have modified the make_keyring.sh file a bit because it complained about missing environment variables.

# added UKR, 12/2018
export NOTESDATA=/local/notesdata
export NOTESBIN=/opt/ibm/domino/notes/10000000/linux

I then created the certs and keyring and copied them to the proper location.

The original source code has used

// proton config
const serverConfig = {
    "hostName": "serv02.fritz.box",
    "connection": {
        "port": 3002
    }
}

to define the serverConfig. This must be enhanced, if you are going the secure way.

I created a new module using Jan’s code

const fs = require('fs');
const path = require('path');

/**
 * Internal functions that reads a content of a file to a buffer.
 * @param {string} fileName 
 */
const readFile = fileName => {
        return fs.readFileSync(path.resolve(fileName));
}

/**
 * (c) Jan Krejcárek
 * Creates an object in a structure required for the DominoDB module
 * to initialize o connection to the Domino server.
 * @param {string} serverHostName Host name of the Domino server
 * @param {string} port TCP port number where a Proton task listens for connection requests
 * @param {string} rootCertificatePath Path to the certificate used to establish a TLS connection
 * @param {string} clientCertificatePath Path to the application certificate used to authenticate the application
 * @param {string} clienKeyPath Path to the private key of the application
 */
const config = (serverHostName, port, rootCertificatePath, clientCertificatePath, clienKeyPath) => {
    const rootCertificate = readFile(rootCertificatePath);
    const clientCertificate = readFile(clientCertificatePath);
    const clientKey = readFile(clienKeyPath);
    
    const serverConfig = {
            hostName: serverHostName,
            connection: {
                    port: port,
                    secure: true
            },
            credentials: {
                    rootCertificate,
                    clientCertificate,
                    clientKey
            }    
    };

    return serverConfig;
};

module.exports = config;

All parts in the credentials section are mandatory. Even if you use PROTON_AUTHENTICATION=anonymous with PROTON_SSL=1, you must have certificates and keys for the client as well.

And we can now use this module in app.js

const protonConfig = require('./protonConfigSSL.js');
const serverConfig = protonConfig("serv02.fritz.box", "3002", "./certs/proton-self/ca.crt", "./certs/proton-self/app1.crt", "./certs/proton-self/app1.key");

My PROTON configuration is

[029971:000009-00007F78D8293700] PROTON_AUTHENTICATION=client_cert
[029971:000009-00007F78D8293700] PROTON_KEYFILE=proton-self.kyr
[029971:000009-00007F78D8293700] PROTON_LISTEN_ADDRESS=serv02.fritz.box
[029971:000009-00007F78D8293700] PROTON_LISTEN_PORT=3002
[029971:000009-00007F78D8293700] PROTON_SSL=1
[029971:000009-00007F78D8293700] PROTON_TRACE_REQUEST=0
[029971:000009-00007F78D8293700] PROTON_TRACE_SESSION=0

And after restarting PROTON ( restart task proton ) and starting my application ( npm start ), I was able to open hp.nsf in the browser.

I also found an interesting article by Sven Hasselbach about how to protect PROTON keys.

I hope that this will help others starting with this stuff to save some time.

Recommended reading:

Heiko Voigt: DominoDB and a big NO-NO !

Sven Hasselbach ( response to Heiko’s post ): DominoDB and a big NO-NO?