Day: May 21, 2021

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