Programmatically create views/indexes for DQL

When working with Domino Query Language you might need one ore more views in your application. Creating a view is not rocket science at all, but views for DQL have specific requirements. https://help.hcltechsw.com/dom_designer/12.0.0/basic/dql_view_column.html Chances are that you do not recall all of them and you’ll end up with a view that would not work.

LotusScript to the rescue. The NotesDominoQuery class has some methods to let you create and remove required views automatically. The createIndex() method creates views that will match all requirements. Super easy. Barely an inconvenience. https://help.hcltechsw.com/dom_designer/11.0.1/basic/H_CREATEINDEX_METHOD_NDQ.html#reference_mvq_brw_ljb

Aside from the parameters for viewname and columns/fields, the method also has two optional parameters. Both parameters can be omitted. I even see this as best practice.
While one can discuss whether the index for the view should be built immediately or later ( parameter: nobuild ), I don’t see why a view that is only used programmatically should be visible, and thus can be seen by all users of the database. (parameter: visible)

Another problem is the naming of the views. These should be different from the other views to avoid conflicts and to emphasize that the views were created specifically for use with DQL. Currently there is no way to determine this by the properties of the respective design element.

I have created an idea on aha.io to automatically add a comment to a view that have been created using the createIndex() method. https://domino-ideas.hcltechsw.com/ideas/DDXP-I-819

An I also have added another idea to add a new property (“namespace”) to the NotesDominoQuery class. https://domino-ideas.hcltechsw.com/ideas/DDXP-I-818

A namespace becomes handy when you have multiple NotesDominoQuery objects in your code and each of those objects use their own views/indexes.

Surely you can manually add a prefix to the viewname. But I think this is error-prone in complex applications.
It is much easier to use a namespace, which is automatically passed to the corresponding methods as a property of the respective NotesDominoQuery object. I have created a small LotusScript wrapper class to illustrate the principle.

%REM
	Class cNotesDominoQuery
%END REM
Public Class cNotesDominoQuery
	m_session As NotesSession
	m_db As NotesDatabase
	m_dbFilePath As String
	m_NotesDominoQuery As NotesDominoQuery
	m_namespace As String
	m_nobuild As Boolean

	%REM
		Sub New
	%END REM
	Public Sub New(dbFilePath As String)
		Set m_session = New NotesSession
		Set me.m_db = me.m_session.Getdatabase("",dbFilePath, False)
		If me.m_db.Isopen Then
			Set me.m_NotesDominoQuery = me.m_db.CreateDominoQuery()
			me.m_NotesDominoQuery.RefreshDesignCatalog = True
			me.m_nobuild = false
		End If
	End Sub
	
	%REM
		Sub createIndex
	%END REM
	Public Sub createIndex (idxName As String, idxFields As Variant)
		Call me.m_NotesDominoQuery.Createindex(me.m_namespace & idxName, _
		idxFields,, me.m_nobuild)
	End Sub

	%REM
		Sub updateIndex
	%END REM
	Public Sub updateIndex (idxName As String, idxFields As Variant)
		On Error Resume Next
		Call me.removeindex(idxName)
		Call me.createIndex(idxName, idxFields)
	End Sub

	%REM
		Sub removeIndex
	%END REM	
	Public Sub removeIndex(idxName As String )
		Call me.m_NotesDominoQuery.Removeindex(me.m_namespace & idxName)
	End Sub
	
	%REM
		Property Set namespace
	%END REM
	Public Property Set namespace As String
		me.m_namespace = namespace
	End Property
	
	%REM
		Property Set nobuild
	%END REM
	Public Property Set nobuild As boolean
		me.m_nobuild = nobuild
	End Property
	
	%REM
		Property Get object
	%END REM
	Public Property Get object As NotesDominoQuery
		Set object = me.m_NotesDominoQuery
	End Property
End Class

In the example 2 NotesDominoQuery objects are created and assigned to the namespace ns1. and ns2.

Afterwards a view/index can be created for each object, which have the same names. The createIndex() method of the class creates the views considering the assigned namespace. Name conflicts between the two objects are thus avoided as well as conflicts with existing views.

The removeIndex() method works in the same way and deletes only the views/indexes of the respective namespace.

The updateIndex() method also takes the namespace into account.

Dim cNotesDominoQuery As New cNotesDominoQuery("dql.nsf")
cNotesDominoQuery.namespace = "ns1."
	
Call cNotesDominoQuery.removeIndex("bySubject")	
Call cNotesDominoQuery.createIndex("bySubject", "subject")
	
Dim cNotesDominoQuery2 As New cNotesDominoQuery("dql.nsf")
cNotesDominoQuery2.namespace = "ns2."
	
Call cNotesDominoQuery2.removeIndex("bySubject")
Call cNotesDominoQuery2.createIndex("bySubject", "subject")
Call cNotesDominoQuery2.updateIndex("bySubject", "quantity")

Run the above code in an agent. On the console, you will see an output similar to this.

dql.nsf harvested, 0 catalog documents removed, 2 view designs or aliases cataloged out of 2 total with 1 field-usable columns 33.795 msecs and LastModified of 21.05.2021 07:52:18
 DELETE INDEX operation of Index ns1.bySubject on dql.nsf  SUCCEEDED 
 Index (ns1.bySubject) (using hidden view) on dql.nsf  successfully populated, and cataloged for field subject - index will be usable for all DQL terms and sorting using the field name subject
 dql.nsf harvested, 0 catalog documents removed, 3 view designs or aliases cataloged out of 3 total with 1 field-usable columns 34.354 msecs and LastModified of 21.05.2021 07:52:18
 CREATE INDEX operation of Index ns1.bySubject on dql.nsf  SUCCEEDED 
 dql.nsf harvested, 0 catalog documents removed, 2 view designs or aliases cataloged out of 2 total with 1 field-usable columns 33.279 msecs and LastModified of 21.05.2021 07:52:18
 DELETE INDEX operation of Index ns2.bySubject on dql.nsf  SUCCEEDED 
 Index (ns2.bySubject) (using hidden view) on dql.nsf  successfully populated, and cataloged for field subject - index will be usable for all DQL terms and sorting using the field name subject
 dql.nsf harvested, 0 catalog documents removed, 3 view designs or aliases cataloged out of 3 total with 1 field-usable columns 33.625 msecs and LastModified of 21.05.2021 07:52:18
 CREATE INDEX operation of Index ns2.bySubject on dql.nsf  SUCCEEDED 
 dql.nsf harvested, 0 catalog documents removed, 2 view designs or aliases cataloged out of 2 total with 1 field-usable columns 34.456 msecs and LastModified of 21.05.2021 07:52:18
 DELETE INDEX operation of Index ns2.bySubject on dql.nsf  SUCCEEDED 
 Index (ns2.bySubject) (using hidden view) on dql.nsf  successfully populated, and cataloged for field quantity - index will be usable for all DQL terms and sorting using the field name quantity
 dql.nsf harvested, 0 catalog documents removed, 3 view designs or aliases cataloged out of 3 total with 2 field-usable columns 35.770 msecs and LastModified of 21.05.2021 07:52:18
 CREATE INDEX operation of Index ns2.bySubject on dql.nsf  SUCCEEDED 


Programmatically Update /Add Database to DQL Design Catalog

For high speed access to internal information about views and view columns, DQL processing uses design data extracted from view notes. Currently this information will be stored in a new database, named GQFdsgn.cat.

It is created using new updall flags. It does not replicate and is solely used as a fast-path tool to access design data at runtime.

Here is some LotusScript code to add / update the design of a named Notes application to the catalog. The NSFDesignHarvest call is currently undocumented. Use it at your own risk.

'DECLARATIONS

Public Const UPDATE_DESIGN_CATALOG = 0
Public Const ADD_TO_DESIGN_CATALOG = 1

Const NNOTES ="nnotes.dll"
Const LIBNOTES ="libnotes.so"

Declare Public Function WIN_NSFDbOpen Lib NNOTES Alias "NSFDbOpen" _
(ByVal dbName As String, hDb As Long) As Integer
Declare Public Function LIN_NSFDbOpen Lib LIBNOTES Alias "NSFDbOpen" _
(ByVal dbName As String, hDb As Long) As Integer
Declare Public Function WIN_NSFDbClose Lib NNOTES Alias "NSFDbClose" _
(ByVal hDb  As Long) As Integer
Declare Public Function LIN_NSFDbClose Lib LIBNOTES Alias "NSFDbClose" _
(ByVal hDb  As Long) As Integer
Declare Public Function WIN_NSFDesignHarvest Lib NNOTES Alias "NSFDesignHarvest" _
(ByVal hDb  As Long, ByVal flag  As Long) As Integer
Declare Public Function LIN_NSFDesignHarvest Lib LIBNOTES Alias "NSFDesignHarvest" _
(ByVal hDb  As Long, ByVal flag  As Long) As Integer

' API FUNCTIONS
Private Function NSFDbOpen( db As String, hDB As Long) As Integer
	If isDefined("WINDOWS") Then
		NSFDbOpen = WIN_NSFDbOpen(db,hDb)
	ElseIf isDefined("LINUX") Then
		NSFDbOpen = LIN_NSFDbOpen(db,hDb)
	End If
End Function

Private Function NSFDbClose (hDb As Long)
	If isDefined("WINDOWS") Then
		NSFDbClose = WIN_NSFDbClose(hDb)
	ElseIf isDefined("LINUX") Then
		NSFDbClose = LIN_NSFDbClose(hDb)
	End If
End Function

Private Function NSFDesignHarvest (hDb As Long, flag As long) As Integer
	If isDefined("WINDOWS") Then
		NSFDesignHarvest = WIN_NSFDesignHarvest(hDb, flag)
	ElseIf isDefined("LINUX") Then
		NSFDesignHarvest = LIN_NSFDesignHarvest(hDb, flag)
	End If
End Function

Public Function catalogDesign(sDb As String, flag As Long) As Integer
	
	Dim hDb As Long
	Dim rc As Integer
	
	If flag > 1 Then flag = 1
	If flag < 0 Then flag = 0
	
	rc = NSFDbOpen(sDb, hDb)
	
	If rc = 0 Then
		rc = NSFDesignHarvest(hDb, flag)
		rc = NSFDbClose(hDb)
	End If
	
	catalogDesign = rc 
	
End Function


NotesDominoQuery – Find the needle in the haystack (LS)

Think about a big database with lots of documents in it and you want to find only one particular document. You can do that with FTSearch, or you can use a db.search with some formula.

As of V10.0.x, you also have DQL and the new NotesDominoQuery class.
The class is available in Lotsscript and Java.
I want to demonstrate in this sample, how you can find the needle in the haystack with DQL in Lotusscript.

My database has about 12.500.000 Documents. It is one of our customers database at midpoints. The amount of documents was created by accident. Some call it a bug. Anyway, the database is a good playground.

The code is typical for a LS developer. It initiates objects and stuff, assigns variables like our dqlTerm (line 6 see the similarity to the @formula, you would probably use with db.search? ), does a check, if the target database is open (line 9 ) and also has some basic error handling (17, 20-21). We will come to that later.

I am running the sample on the client. As of today , you cannot run a query client / server. I will show in another post, how you can run the query on the server and work with the results on the client. But that is another story.

public Sub foo() 
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim dqlTerm As String
	dqlTerm = "form = 'frm.rules.device.rule' And rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"
	
	Set db = session.Getdatabase("","trul-big.nsf", False)
	If db.Isopen Then
		
		Dim dql As NOTESDOMINOQUERY
		Set dql = db.CreateDominoQuery()
		
		Dim parse_result As String
		parse_result = dql.parse(dqlTerm)

		If LCase(parse_result) = "success" Then
			Set col = dql.Execute(dqlTerm)
			MsgBox dql.Explain(dqlTerm)
		Else
			MsgBox parse_result
		End If
		
	End If
End Sub

Before you run the code and try to search such a huge amount of data, you need to set some notes.ini variables.

QUERY_MAX_DOCS_SCANNED=13000000 
QUERY_MAX_VIEW_ENTRIES_SCANNED=13000000

By now, there is no other way to increase the number of documents. There are setters in the NotesDominoQuery class, but those setters are broken. This is a known issue and HCL is working on a solution.

When we now run the code, we will get the following result

It took 51.3 secs to find 110642 documents that use the form “frm.rules.device.rule” and zero to none secs to find 1 document in that resultset.

Would it be faster or slower, if we modify our query to use only the rule_unid field and search over the entire set of documents?

dqlTerm = "rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

Here is the result

32.2 secs to find the needle in the haystack. Use this kind of single field search, if you are sure that the field “rule_unid” is only used on one form.

But even in the case that the field is used on another form; to filter the resulting NotesDocument collection afterwards is much faster than the AND dqlTerm from the original code.

Maybe, you already have a view in your application where the rule_unid is in a sorted column.

Then we can use a modified dqlTerm to find the document in that view.

Again, let’s change our dqlTerm a little bit

dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

And here is the result

THAT is pretty cool, isn’t it? 5.6 msecs to find the needle in the haystack.

Can it be even faster? I think no, but let us take another look at the code that does the query.

        Dim parse_result As String
        parse_result = dql.parse(dqlTerm)
 
        If LCase(parse_result) = "success" Then
            Set col = dql.Execute(dqlTerm)
            MsgBox dql.Explain(dqlTerm)
        Else
            MsgBox parse_result
        End If

We can safely remove all of our “Error handling”, that means lines 14-17 and 20-22. Why? Because dql.Excecute(dqlTerm) does the parse before executing the query.

So we end up with the following code

public Sub foo() 
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim dqlTerm As String
	
	dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

	Set db = session.Getdatabase("","trul-big.nsf", False)
	If db.Isopen Then
		Dim dql As NOTESDOMINOQUERY
		Set dql = db.CreateDominoQuery()
		Set col = dql.Execute(dqlTerm)
		MsgBox dql.Explain(dqlTerm)
	End If
End Sub

When we run the code, we get

Keep in mind that the numbers vary a litte bit from run to run.

If we now modify our dqlTerm and add an error, we are shown a nice dialog box explaining the error in detail.

That’s all for today, I hope you find this information useful.


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