Queries

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:

Datasource: The datasource against which the query is being run (referencing a datasource entity). You can filter the list of queries by their datasources using the dropdown above the query list.
Name: The name identifying the query.
Template: A template used to display the result of the query in the Confluence page containing the PocketQuery macro. References a template entity. If the Default template is selected, a default PocketQuery template is used.
Converter: Converter used to transform the response of the datasource before it is handed on to the template. See the “Converters” section below.
Spaces: A string specifying in what spaces and/or space categories this query should be available. This is a comma-separated list of spacekeys and space categories prefixed by "category" and a colon. Example: spaceKey1, spaceKey2, category:mySpaceCategory.
Cache for duration of: A string specifying how long the results of this query will be cached. If this is left empty, nothing will be cached (results are always recent). The time is specified like “3d” (3 days), “12h” (12 hours), “50m” (50 minutes) - like in the work log of JIRA issues, for example. Caching can improve performance significantly, especially for more intense queries. Also see the section “Caching Query Results” for further information.
Add results to Confluence search: Boolean value indicating if the results of query should be added to the Confluence index. This makes the results searchable by the Confluence search. Also see the section “Adding Results to the Search Index” for further information.

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:

Statement: The query statement. It consists of arbitrary SQL code, that forms exactly one single SQL statement. (If you need multiple statements, please use multiple queries or create a stored procedure in your database.) The code can optionally be enhanced by parameters prefixed by a colon in the form :parameterName. These parameter placeholders (“named parameters”) are filled during runtime of the PocketQuery Macro (see the example statements below for further explanation). It is possible to use wildcards within theses parameters (see section "Using Wildcards for Parameters" below for more information).
Parameter Types: By default all parameters are of type string. Currently also the types Integer, ListOfStrings, ListOfIntegers, Boolean and Constant are supported, which have to be specified explicitly. For each parameter, add its name on the left and from the select box on the right choose the correct type. Examples where this is required is the LIMIT clause (e.g. “LIMIT :number“) or the IN clause (e.g. “:name IN (:myListOfStrings)“). See the “Parameter Types” section below.

Parameter Types

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

SQL

SELECT Name, Population
FROM Country
WHERE Continent = :continent

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

SQL

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

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

Numbers
Boolean values
Lists for IN-clauses
Constant values in the SELECT clause

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:

String: This is the default parameter type that does not need to be specified. Values provided for query parameters will be wrapped in single quotes.
Number: Your numeric value will be inserted in the query with no quotes.
ListOfIntegers: Your integer list will be inserted as comma-separated list of values. This is useful for IN-clauses with numbers, e.g.... IN(1,2,3,4,5) ...
ListOfStrings: Your list of strings will be inserted as comma-separated list of values with single quotes. This is useful for IN-clauses with strings, e.g. ... IN('one','two','three') ...
Boolean: Your true/false value will be inserted without quotes.
Constant: This is useful for parameters in the SELECT clause like SELECT :mycolumn ... where you simply want the value to be inserted as a constant.

Example query statements

Below are some random example statements that may give you some direction.

SQL

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:

REST URL: The URL path that is appended to the root URL of this datasource when calling it. It may contain parameters prefixed by a colon in the form :parameterName. These parameter placeholders (“named parameters”) are filled during runtime of the PocketQuery Macro. It is possible to use wildcards within theses parameters (see section “Using Wildcards for Parameters” below for more information). If special characters occur, they need to be url encoded (e.g. the percentage sign “%” is encoded as %25, “ä” is encoded as %C3%A4). The structure of the REST URL depends entirely on the REST API used - please refer to its documentation.

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, or 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:

@username - the username of the current Confluence user.
@userfullname - the full name of the current Confluence user.
@usermail - the email address of the current Confluence user.
@pagetitle - the title of the current Confluence page/blogpost.
@pageid - the ID of the current Confluence page/blogpost.
@spacekey - the space key of the current Confluence page/blogpost.
@spacecategory - searches for a space category starting with pq- in the current space and uses the part after the dash as value. For example, a space category pq-myvalue will be used as myvalue for the query parameter. Currently only one such wildcard can be used per space.

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:

a page (or other content object) with a PocketQuery macro using this query is edited, or
the PocketQuery Index Job runs (see below)

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 should change consequently. 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 are in your instance. 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:

query name
query parameters
query statement

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

CODE

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

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

there is no cached item yet for the current triple (queryName, queryParameters, queryStatement)
the query parameters change
the statement changes