At many places in midPoint we can (and sometimes have to) specify queries in order to find one or more objects in the system. We do this e.g. when we want to restrict objects (like users, roles, resources or services) shown on the screen, when selecting objects that are to be included within a report, when specifying objects that are to be processed by a background task, when account owner is to be determined, or when assignment target is to be found. All of this is done via midPoint query language – abstract XML-based language that is designed to specify constraints on objects, and optionally paging and sorting instructions. This language is very powerful. The negative side of that power is complexity: it is quite hard to write (correct) queries by hand. Because of this, we’ve recently added a simple, yet helpful feature to midPoint: query playground.
Page with query playground can be found in the Configuration section, just above About menu item:
Basic functionality of Query playground page
The page allows you to enter a query, and execute it.
Query can be entered by selecting type of object to be queried (e.g. UserType, RoleType, OrgType, and so on), and the XML representation of the query itself, like this:
Fortunately, there’s an easier way to write queries: As you can see on the bottom of the screenshot, you can choose a sample that is automatically copied into query window; then you simply modify the sample to obtain the functionality you need. The list is growing as we, our partners and customers find interesting queries to share. Currently there are the following samples:
What to do with a query? Of course, we can try to execute it, in order to see if it is valid and if it produces expected results. You can try it by clicking on Translate and execute button. The result is shown below:
The result isn’t particularly detailed, as we are not interested in object content here. It is just a diagnostic information telling us a type of an object (“user”), its OID (e.g. 00000000-0000-0000-0000-000000000002) and name (e.g. “administrator”). But it is quite sufficient with regards to the goal of this page: to tune a query.
If we are not satisfied with the results, we can change the query. And after it’s OK, we are ready to use it. If it is to be part of the midPoint customization (e.g. as a correlation rule, assignmentTargetSearch or associationTargetSearch expression, or report objects selection), we can simply copy-and-paste it into appropriate midPoint object. If we would like to use it to restrict objects shown on the screen (i.e. on Users, Roles, Services or Resources page), we can use green button Use in object list that will take us onto appropriate screen. This button is shown only when compatible object type – UserType, RoleType, ServiceType or ResourceType is selected:
The object list then looks like this. Note that in addition to “Search” menu item, there is a “Debug” item that will return us back into playground if selected. This link is shown always when object list search is switched into Advanced filter mode, provided you are authorized to use Query playground.
Translation into Hibernate
For advanced users there is one more possibility: inspect generated Hibernate query; or even execute it (in case of a user with administrator rights).
What is Hibernate query? First of all, it is necessary to know that a query can be interpreted either by midPoint repository, by a resource or by the embedded Activiti workflow engine used by midPoint. Query playground currently supports repository queries only. Because midPoint internally uses Hibernate to implement its repository, midPoint queries are translated into HQL (Hibernate query language) as part of their processing. And query in HQL is then translated into SQL query (by Hibernate engine), which is then executed by a particular database.
Why is Hibernate query important? Well, for 99{1acb01afcc33953bed28d92be9d735e579722d77ac7936fdfc467b8d28505288} you can safely ignore it. But if you are diagnosing a query that does not work as you expect, or if you are simply curious how things are implemented, you are invited to have a look at HQL code. An example:
And for the very advanced users, there is a possibility to execute arbitrary Hibernate queries as well.
with the result of:
What next?
The query playground will be part of midPoint 3.4.1 release. But we certainly don’t want to stop here. Besides adding JSON and YAML as alternative representations of XML queries in forthcoming midPoint 3.5 release we would like to create more human-friendly query language, probably similar to that used by Atlassian JIRA. And, as for editors, we strive for advanced features like auto-completion of keywords and item names, as well as intelligent syntax highlighting – for XML, JSON, YAML as well as the new query language.
More information about current functionality available in midPoint query language can be found in our wiki.