A query fetches data from one of your datasources. Its properties largely depend on the type of datasource being used.


Properties

All queries consist of the following properties:

Depending on the type of the query, there are some more properties. Please refer to the sections below.


SQL Queries on JDBC datasources

Additional Properties

For JDBC datasources the following additional properties are available:

Parameter Types 

By default, all parameters in a query will become strings. For example:

SELECT Name, Population
FROM Country
WHERE Continent = :continent

This will become something like the following after the replacement took place:

SELECT Name, Population
FROM Country
WHERE Continent = 'Europe'

Now in certain cases this behaviour is not intended. Examples are:

That’s why we implemented the Parameter Types configuration option for queries. You can configure types for all your query parameters in the section "Parameter Types" by inserting their names and selecting their respective types from the dropdown. If parameters are not specified the default value will be “String”.

Currently supported parameter types:

Example query statements

SELECT Name, GovernmentForm, Region, HeadOfState 
FROM Country 
WHERE Continent = :continent;

SELECT Name 
FROM Country 
INNER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode 
WHERE Language = :language;

SELECT * 
FROM City 
WHERE Population > :population;

SELECT Name, GovernmentForm, Region, HeadOfState 
FROM Country 
WHERE Population > :minPopulation AND Population < :maxPopulation;

SELECT Name 
FROM Country 
LIMIT :max;  -- "max" needs the parameter type "Integer"

SELECT Name, GovernmentForm 
FROM Country 
WHERE Name IN (:names);  -- "names" needs the parameter type "ListOfStrings"


Queries on REST Datasources

Additional Properties

For REST datasources the following additional properties are available:


Using Parameters in Queries

As mentioned above, both SQL queries and REST calls may include PocketQuery parameters. These parameters can be set when adding the PocketQuery Macro to a page which makes the query much more versatile.

PocketQuery parameters are marked with a colon in front of their name (:parameterName). For SQL queries, it is possible to set the type of the parameter to influence how the final values will be included in the SQL query. See the section Parameter Types above for more details. For REST calls there are no different parameter types - all parameter values will be URL encoded and then included in the REST call URL.


Using Wildcards for Parameters

Instead of using fixed values, it is possible to use wildcards to set a PocketQuery parameter. These wildcards will be replaced by the actual content before the query is executed. The wildcards can be set by the user when adding the PocketQuery macro to a page, and since version 3.2.0 it is also possible to set wildcards directly within the query statement or REST URL itself. This way the macro user herself has no opportunity to adjust the values manually which can be important for security.

The following wildcards can be set as parameters values:

For users that are not logged into Confluence @username, @userfullname and @usermail will return "anonymous".

Note: When using a wildcard within the query statement or REST URL, it has to be prefixed with a colon to mark it as a parameter. So the resulting syntax is something like "SELECT * FROM mytable WHERE id LIKE :@pageid".


Adding Query Results to the Search Index

For each query you can decide whether its results should be added to the Confluence search index. They can then be found using the search bar on the top right, which makes content discovery much easier.

If the Index checkbox is checked, all results retrieved with this query will be added to the Confluence index when

PocketQuery Index Job

One challenge using the Confluence search 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. (Feel free to disable the job if you don’t want to make use of this feature at all.)


Caching Query Results

PocketQuery has a caching mechanism implemented that enables to cache the result of queries. This can reduce traffic with your datasource significantly. Without caching, every PocketQuery macro will trigger an interaction with the datasource when it is run (i.e. when a Confluence page with a macro is viewed).

For every query in the PocketQuery Admin, you can define how long the results of the query should be cached. When a PocketQuery macro with that query is run, it is checked if the result in the cache is older than the specified time, and only if so, the contents are refreshed by a new query. The cache duration can be specified in seconds (e.g. “45s”), minutes (e.g. “45m”), hours (e.g. “12h”) or days (e.g. “3d”).

You can clear the cache separately for every query by clicking at the “Clear cache” label above the cache setting (within the PocketQuery Administration when editing a query):

Cache Administration

Since results from the database can become arbitrarily big, it is important to limit the cache size to some amount. We set a default to 100 items which means that only a total of 100 query results can live in the cache. You can administer that limit at Confluence Admin > Cache Management.

Click on “Show advanced view” on top of the list:

Locate the PocketQuery cache row and click on “Adjust size”. From time to time it might also be useful to flush the whole PocketQuery cache and start from scratch again. You can do so by clicking “Flush”.

How it works

Results in the cache are always identified by three parts:

More formally, each result gets a cache key by the following pattern:

queryName:::MD5(queryParameterString):::MD5(queryStatement)

This means, a new cache item is created when the PocketQuery macro is run if: