Getting started with DQL

Do you remember? A little over 2 weeks ago was Engage. How quickly everything can change…

When you were at Engage you might have seen the session I did with Oliver Busse on DQL. The slides and demo code can be found here and here. We touched how to get started with DQL briefly, and based on the questions we received I think it’s a good idea to write a couple of articles on the topic. This is the first.

Fakenews

Let me start by explaining what you need to start using DQL: that would be a HCL/IBM Domino server, version 10 or higher. That’s it. No more. No AppDev pack. No Node.js.

Apparently this still confuses a lot of people. So what about that AppDev pack? Well you only need it if you want to talk to Domino from a Node.js server: the domino-db part is the part you need to install in your Node.js project. It also requires an extra component on the server called Proton. If you’re not interested in that yet and just want to write LotusScript/ Java, then you’re all set.

By the way: since Domino 11 the documentation is part of the standard server help and not available on the AppDev pack site anymore.

From a (vanilla) Domino v10+ server you can run your first DQL query with just a couple of lines of code in either LotusScript or Java: create an instance from the DominoQuery (NotesDominoQuery in LS) and query away!

In LotusScript:

Sub Initialize

Dim session As New NotesSession
Dim db As NotesDatabase
Dim dql As NotesDominoQuery
Dim col As NotesDocumentCollection
Dim doc As NotesDocument

Set db = session.Currentdatabase
Set dql = db.Createdominoquery()

Set col = dql.Execute("your query goes here")

Set doc = col.Getfirstdocument()

Do While Not doc Is Nothing

  ' do whatever you want

  Set doc = col.Getnextdocument(doc)
Loop

End Sub

Or Java:

Session session = ExtLibUtil.getCurrentSession();

Database db = session.getCurrentDatabase();
DominoQuery dql = db.createDominoQuery();
DocumentCollection result = dql.execute("your query here");

Document doc = result.getFirstDocument();

while (null != doc) {

  //do what you want with the doc

  Document tmp = dc.getNextDocument(doc);
  doc.recycle();
  doc = tmp;
}

Note that the result of executing a DQL query is an (unsorted) document collection. You can loop over it to get the information from the result set you need.

DQL Design Catalog

When you run the query above, the DQL engine will perform a scan of all the documents in your database. That’s because it doesn’t know yet what views it can use to optimise the query. For that it needs the so called Design Catalog. You can think of it as a summary of the elements in the design of your database that DQL might/ can use to optimise queries. Before version 11, this information was stored in a separate database (GQFdsgn.cat), but since version 11 it is stored in ‘hidden’ design elements in the NSF.

To create the design catalog you call load updall -e <your-db> or if you want to update it load updall -d <your -db>.

The design catalog isn’t automatically updated when the design changes, so you should take care of that yourself. It’s good to know that the design catalog can also be updated programmatically by calling setRebuildDesignCatalog() or setRefreshDesignCatalog(). You can also list all current indexes by calling listIndexes(). These are all Java methods, but the names in LotusScript are similar. You call them from an instance of the DominoQuery class.

If you don’t have a design catalog in your database, any query that needs it will throw an error (e.g. a view query):

Domino Query execution error: Entry not found in index - syntax error
Error validating view column name - ['contacts'.city] .. 
invalid view name or database needs to be cataloged via updall -e 
(Call hint: NSFDbGetNamedObjectID, Core call #0) ******************

DQL Exploring

HCL / IBM gave you the DQL Explorer application to play with DQL. But I think it’s overcomplicated for an introduction: it was written in React, which a lot people aren’t familiar with. I think this just adds to the confusion. So I wrote a simpler one as an XPages application: just 1 XPage with an input field to enter a query and a search button that call a Java method. The result is shown in a repeat control. You can try download it here or try it out:

Simple DQL Explorer

From the XPage you can click one of the sample queries and try them out. Check the ‘Explain’ checkbox to get more details on how Domino executed the query (this is also a standard feature of the DQL engine: call the explain() method on your DominoQuery instance).

FYI: The demo app runs on a very basic VPS with 2 GB RAM that runs Domino 11 (so please be gentle). It queries a database with 100,000 fakenames, created using https://www.fakenamegenerator.com/. The fakenames database can also be downloaded from the link at the top of this post.

Enough for now, in the next articles I want to cover more search syntax, and approaches how to use search results in your app (e.g. REST) and sorting and paging of results.

XPages partial refresh issue in Domino 10.0.1

A couple of days ago I tweeted about an XPages partial refresh issue I ran into in Domino 10.0.1. I got it on a freshly installed Domino 10.0.1 server with our main XPages app on it. Within a couple of clicks it started throwing errors, so I was kind of disappointed.

Did some more investigation and was able to reproduce the issue in an isolated sample .

IBM/ HCL support read my tweet and got in touch (+1 for that!). Created a case for this issue and I must honestly say that they handled very well.

Turns out that the error handling for partial refreshes has changed in v10. I didn’t know that and you probably did neither. It’s more strict in v10 and will throw an error in some specific scenarios (like my example that worked perfectly in 9.0.1).

The solution/ workaround is to revert to the way errors are handled (or are actually NOT handled 😉 )  in 9.0.1 by adding this to your XSP properties in the NSF:

xsp.error.disable.detection.set01=true

UPDATE

IBM/ HCL published a technote describing the changes partial refresh behaviour in v10. You can read it here.

Query Domino data with Domino JNA (part 3): REST API and infinite scroll

With the demos from part 1 and part 2 we now have a list running in XPages that has sorting, paging and filtering. But what if you want to use Domino JNA in a REST API? To serve a JavaScript/ Angular/ React/ Vue application or, why not, an app running in Office 365?

The good news is that we already have most of the code for that. So we can take the code from demo 6, do a little refactoring, write a class for the REST API, and we’re done! In this post I’ll show you how to create the JNA-powered REST API and use it in a JavaScript grid component (for Domino people: a ‘view’) called AG Grid. Want to see the demo first?

REST API in Domino

The main reason why we need to refactor the controller class from demo 6. is that REST APIs are stateless, so we need to get rid of all Java class variables. We end up with one (public static) getEntries() method and add parameters to set the start index, number of entries to return, sort order and filter.

Creating a REST API in Domino can be done in multiple ways:

For the purpose of this demo, and to keep things simple, I’ve used the ExtLib REST control. In short: you  create an XPage (e.g. api.xsp), add the ExtLib control, set the path (the part in the URL after the XPage name, e.g. ‘contacts’), configure it as a custom REST service and point it to a Java class (eu.linqed.api.ContactsService). In that class you extend com.ibm.xsp.extlib.component.rest.CustomServiceBean and implement the doService() method to handle the request. See the demo database for an example. With that in place, we now have a working REST API serving the contacts data. The data can be sorted, paged and filtered by combining URL parameters (start, count, sortCol, sortAsc and filter).

Using the REST API in an app

For this post I created a small JavaScript demo app that uses ag-grid to show the contacts. ag-grid is a very feature-rich, open source component to build grids (sorry if that sounded like marketing – I’m not affiliated in any way with them). It comes with built-in support for sorting, filtering and infinite scrolling using a virtual row model. Plus it has versions for all the major JavaScript frameworks. Since I’m the most comfortable writing Angular I used that one.

After following the getting started tutorial I ended up with a working grid, linked to their sample data. We’re now ready to change that and link it to the Domino JNA-driven REST API we just created. The documentation describes what events we can use.

Most changes need to be made in the app.component.ts file: the component definition and configuration for the grid. We’ll first change the endpoint and immediately run into an error: the Angular app is running on a different server, so the browser shows a CORS security warning. You can fix that by (1) adding an Access-Control-Allow-Origin header to your servers’ response or (2) simply run the Angular on your Domino server. When you’re running Domino behind a proxy you can add it to the proxy configuration. Or you (normally) would create rule for your website document in names.nsf. Unfortunately those rules don’t seem to be added when you’re using an ExtLib REST Control, so you either need to use a proxy (=recommended) or add the header to the ContactsService class.

We then have the change the column definition to display properties from the contacts received from the REST API in the ‘columnDefs’ property of AG Grid (lines 25-30).

Infinite scrolling on the grid is enabled by setting the ‘infinite’ row model, according to this guide. The datasource can be found in rows 102-156. The basic idea is that when the user (almost) scrolled to the end of the list, it sends a request to the REST API to load more data. With that request it includes the current ‘state’ of the grid (sort column, filtering). You can see the request being made on line 135. Note that I’m connecting to the endpoint configured in the ‘environments’ configuration file.

Last thing is to handle a user entering something in the search field or sorting the grid. We change the ‘state’ object of the grid in the appropriate events (onGridSortChanged, onGridFilterChanged) and the grid takes care of the rest!

Have fun with the demo and don’t forget the explore the source code of the demo database and Angular demo app.