PocketQuery is now searchable – Release 1.12

Ghost -

With version 1.12 of PocketQuery, Scandio is happy to announce a new promising key feature in our SQL Plugin: Indexing. Up to this release, data retrieved using the PocketQuery macro was not searchable in Confluence. Time for changes!

What’s PocketQuery

PocketQuery is a SQL connector plugin that makes it incredibly easy to extract content from different databases and display results in Confluence pages, using different kinds of visualizations. Since creation and management of queries, databases and templates is restricted to Confluence administrators, secure access to sensible data is granted at any time. Database queries can be executed and parametrized by users within Confluence pages in permitted spaces using the PocketQuery macro.

Why Indexing?

Imagine the following scenario: in your company’s database, you have contact data associated with different customers. For every customer you have a page in your Confluence system on which you display contact data for that customer. It might look like this:

You might now also want to search for these contacts using the Confluence search. Since the displayed data is retrieved from an external database by the PocketQuery macro, this is not a trivial issue. Previously, these contents were not searchable and you weren’t given any results when you searched e.g. for the contact “Batman”:

Enabling indexing for queries

With release 1.12 you will now be able to search data retrieved by PocketQuery. A Confluence administrator can enable indexing query-wise on the PocketQuery admin page:

If the Index checkbox is checked, all results retrieved with this query will be added to the Confluence index when (1) a page with a PocketQuery macro using this query is edited or (2) when the PocketQuery Index Job runs (see next section). Afterwards, you will find “Batman” using the Confluence search:

Regular updates for the PocketQuery index

One challenge using the index for PocketQuery results is that changes are not detected by Confluence like they are with the content of Confluence pages: Confluence won’t recognize when something changes in your external database and the results of your macro change in consequence. In order to keep index data up-to-date, we implemented a Scheduled Job. Its task is to reindex all pages that contain a PocketQuery macro with a query that has indexing enabled. You can configure the job’s schedule at Confluence Admin > Scheduled Jobs:

The default schedule is to trigger the job every night at 1 AM. You might want to change this regarding your specific environment. Note that the job might be quite performance-intensive since it may iterate over a large set of pages, depending on how many PocketQuery macros you use. Also, these queries are run against your external database to retrieve the latest results.

Additional fixes in version 1.12

There are two additional minor fixes in this release:

  • DB driver autocompletion: the driver field for databases in the PocketQuery admin is autocompleted when the url field loses focus. If you used a different driver than the one PocketQuery suggests, your value got overwritten when you changed the URL.
  • Autocomplete is now triggered only, when the driver field is still empty.
  • Auto-Commit: previously, the auto-commit feature for the JDBC connection was set to false. There was an issue for DB2 disabling this value. From now on, all JDBC connections established by PocketQuery run as auto-commit.

Summary

We at Scandio are glad to see that PocketQuery has grown quite popular among Confluence users. We hope that version 1.12 makes the plugin even more enjoyable and we wish you happy searching!

Where to go?