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:
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.