PocketQuery 2.0 is released!

Ghost -

( Note: this is a news article about the new PocketQuery version 2.0. If you’re searching for the documentation, find it here.)

Our goal with PocketQuery releases was to eat humble pie: we wanted to release new features with frequent new versions. We failed totally with that goal. We worked about one year on version 2.0 and put all new features into this release. Goals sometimes remain just goals.

The good news is: PocketQuery 2.0 is released! And it assembles a large set of new powerful features that will make your work with PocketQuery much smoother! Let me introduce…

Caching

PocketQuery results can now be cached! We’ve constantly been asked about a caching mechanism in PocketQuery such that not every request to a page with a PocketQuery macro will result in a database query.

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 database 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” icon:

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:

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. Click on “Flush”.

How it works

How do we cache? An interesting question that was. We had to consider what constitutes one PocketQuery query to the database. First, we have the query. But results depend on the named parameters as well. So query parameters secondly. But then also a Confluence administrator can change the statement SQL code of the query. So we came to our new cache formula: name + parameters + statement.

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

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

The cache is implemented using the Atlassian cache API which means that the feature will also be data center compatible.

Dynamic Load

PocketQuery macros can now be loaded in the background. Have you ever been annoyed that PocketQuery macros and the database queries delay your page load? Enter dynamic load! PocketQuery macros can now be loaded asynchronously in the background (Ajax) while your page will completely load first. This will be especially suitable for you if you have multiple PocketQuery macros on one page and/or more long running queries.

And this might be interesting as well: the feature works well with other macro parameters. If you check the feature, you will first also see a new parameter “Include reload button”. If this is checked, a reload button will be shown above the result and the contents of this macro can be refreshed. This will be very useful if your database contents change more frequently.

If you also check “Enable dynamic parameters”, you will be able to specify the PocketQuery query parameters in the URL in syntax pq_parameterName and the dynamic load process will pick them up.

As you can see in the screenshot above, dynamic parameters also go along well with the “Change params template”. If you check this option, a form for your parameters will be shown. If you change the parameters here, the macro will be reloaded as well. In this case, the URL will change appropriately. This will keep your browser history working if you have dynamic load enabled and will also give you the ability to send permalinks to a PocketQuery showing results for specific query parameters.

Space Categories

Queries can now be restricted to spaces with specific space categories. As you probably know, you can label your space with categories.

You can now restrict PocketQuery queries to spaces with certain categories. This way, the query won’t appear in the macro browser if your space doesn’t have this category.

So now the “Spaces” input for a PocketQuery query is now a comma-separated list of SpaceKeys and/or space categories in the syntax category:categoryName. Example: worldSpace,ITSUPPORT,category:pocketquery

Parameter Types

You can now use the IN and LIMIT clauses. In the past, all statement parameters were inserted as strings with single quotes around them. This effectively prevented the usage of clauses that needed to be of other types.

For example, the clause SELECT * FROM Country LIMIT :Limit was translated to SELECT * FROM Country LIMIT ‘10’. Which is invalid SQL. The same applied to IN: SELECT * FROM Country WHERE Country IN (:Countries) which was translated to SELECT * FROM Country WHERE Country IN (‘Italy,France,Greece’) which is invalid SQL syntax as well.

You can now associate these parameters with certain types. This affects how your statement will be built. The default type is String (this must not be provided). For :Limit in the first example you would specify the type ‘Integer’. For :Countries in the second example you would specify ‘ListOfStrings’.

The syntax for the input is paramName1=paramType1&paramName2=paramType2.

Available types are currently:

  • String (default)
  • Integer
  • ListOfStrings
  • ListOfIntegers

Import/Export

You can now export and import all PocketQuery data to and from XML. Go to Confluence Admin > PocketQuery > Import/Export:

Clicking “Export” will export all databases, queries and templates to an XML file that can be imported later on.

Important Note: if you import entities with names that already exist in your instance, these entities will be overwritten by the import. Overwritten data will not be recoverable!

Read from Path

You can now deploy queries and templates in another Confluence plugin This feature is more for Confluence plugin developers - hello to you from Scandio :-)

Instead of writing the SQL and Velocity code for queries and templates, you can now also deploy these resources as files in another Confluence plugin. These files can then be referenced in the query and template text area fields.

Query example: if you have a *.sql file in your plugin at src/main/resources/pocketquery/queries/CountriesByLanguage.sql, you can include the SQL code in that file by using this code as your statement:

-- @param path:/pocketquery/queries/CountriesByLanguage.sql

Template example: if you have a *.vm file in your plugin at src/main/resources/pocketquery/templates/CountriesByLanguage.vm, you can include the Velocity code in that file by using this code as your template:

## @param path:/pocketquery/templates/CountriesByLanguage.vm

This can be very useful, especially because you can have your PocketQuery queries and template in version control. This way you can also track changes to these files, revert back, etc.

Empty Result Message

The text of the empty result message is now configurable. If the result of your query was empty, PocketQuery will print this message:

The text of this message can now be configured using a custom template and the template parameter emptymsg:

## @param emptymsg:The query returned no result!!**

Simply put this on top of your template.

Where to go?