com.atlassian.confluence.content.render.xhtml.migration.exceptions.UnknownMacroMigrationException: The macro 'next_previous_links' is unknown.

Executing a Query

Owned by Former user (Deleted)
Last updated: Sept 06, 2016
5 min read

A custom query stored in the registry can be executed by giving the path of the stored query and giving a Map of the required parameters. The following code illustrates:

registry.executeQuery("/path/to/storedQuery", parameter_map); // parameter map contains key value pairs required by the query

Forming custom queries

Custom Queries interface allows the user to search the registry using a custom SQL queries. In a custom query, the user is expected to return one of the following items.

Querying for resources

In Querying resources you will be working on the "REG_RESOURCE" table. "REG_RESOURCE" table contains the following fields:

  • REG_RESOURCE
  • REG_PATH_ID
  • REG_NAME
  • REG_VERSION
  • REG_MEDIA_TYPE
  • REG_CREATOR
  • REG_CREATED_TIME
  • REG_LAST_UPDATOR
  • REG_LAST_UPDATED_TIME
  • REG_DESCRIPTION
  • REG_CONTENT_ID

In order to return a resource, you always need to return the "REG_PATH_ID, REG_NAME" in your custom query. This is because ("REG_PATH_ID, REG_NAME") fields act together as an ID for the resource.

For example, the following query will return one or more resources that have a given pattern in the description:

SELECT REG_PATH_ID, REG_NAME FROM REG_RESOURCE WHERE REG_DESCRIPTION LIKE?

The following example shows a code snippet that does a custom query.

1. Put a resource with the content as the query.

String sql1 = "SELECT REG_PATH_ID, REG_NAME FROM REG_RESOURCE WHERE REG_DESCRIPTION LIKE ?";

Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.RESOURCES_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);

2. Give the parameters and the query location you have just putted.

Map parameters = new HashMap();

parameters.put("1", "%service%");

Resource result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);

String[] paths = (String[])result.getContent();
Querying for resources by properties

You can query for resources by a special property values. Before that we we will check the schema of the properties tables.

REG_PROPERTY

REG_ID

REG_NAME

REG_VALUE

REG_RESOURCE_PROPERTY

REG_PROPERTY_ID

REG_VERSION

REG_PATH_ID

REG_RESOURCE_NAME

There are two modes that properties are stored.

1. If the properties versioning is set true (which is the default), the foreign key relationship happens in the following way.

For example, the following query returns one or more resources that have a given property.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_PROPERTY PP, REG_RESOURCE_PROPERTY RP WHERE

R.REG_VERSION=RP.REG_VERSION AND RP.REG_PROPERTY_ID=PP.REG_ID AND PP.REG_NAME=? AND PP.REG_VALUE=?

2. If the versioning properties is set to false, then the foreign key in focus are:

In this mode, you should write separate queries to retrieve collections and non-collections.

For example, the query below returns one or more non-collections that have a given property.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_PROPERTY PP, REG_RESOURCE_PROPERTY RP WHERE

R.REG_PATH_ID=RP.REG_PATH_ID AND R.REG_NAME=RP.REG_RESOURCE_NAME AND RP.REG_PROPERTY_ID=PP.REG_ID AND PP.REG_NAME=? AND PP.REG_VALUE=?

The following query returns one or more collections that have a given property.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_PROPERTY PP, REG_RESOURCE_PROPERTY RP WHERE

R.REG_PATH_ID=RP.REG_PATH_ID AND R.REG_NAME IS NULL AND RP.REG_RESOURCE_NAME IS NULL AND

RP.REG_PROPERTY_ID=PP.REG_ID AND PP.REG_NAME=? AND PP.REG_VALUE=?
Querying for resources by tag

You can by tags.The table structure to store tags is in the same pattern as the tables for store properties. Here is the table structure for tags.

REG_TAG

REG_ID

REG_TAG_NAME

REG_USER_ID

REG_TAGGED_TIME

REG_RESOURCE_TAG

REG_TAG_ID

REG_VERSION

REG_PATH_ID

REG_RESOURCE_NAME

Similar to properties, tags are also stored in two modes. Here is when versioning tags are set to{{true}}.

For example, the following query returns one or more resources that have a given tag.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_TAG T, REG_RESOURCE_TAG RT WHERE R.REG_VERSION=RT.REG_VERSION

AND RT.REG_TAG_ID=T.REG_ID AND T.REG_TAG_NAME=?

If the versioning tags is set to false, then the foreign key in focus is:

In this mode you should write separate queries to retrieve collections and non-collections.

For example, the following query returns one or more non-collections that has a given tag.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_TAG T, REG_RESOURCE_TAG RT WHERE R.REG_NAME=RT.REG_RESOURCE_NAME AND

R.REG_PATH_ID=RT.REG_PATH_ID AND RT.REG_TAG_ID=T.REG_ID AND T.REG_TAG_NAME=?

The query below returns one or collections that has a given tag.

SELECT R.REG_PATH_ID, R.REG_NAME FROM REG_RESOURCE R, REG_TAG T, REG_RESOURCE_TAG RT WHERE

R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME IS NULL AND RT.REG_RESOURCE_NAME IS NULL AND RT.REG_TAG_ID=T.REG_ID AND T.REG_TAG_NAME=?

Querying for comments

Here is the table structure related to comments.

REG_COMMENT

REG_ID

REG_COMMENT_TEXT

REG_USER_ID

REG_COMMENT_TIME

REG_RESOURCE_COMMENT

REG_COMMENT_ID

REG_VERSION

REG_PATH_ID

REG_RESOURCE_NAME

Here is an example query to search comments on resources (non-collections) that have a description with a given pattern:

SELECT RC.REG_COMMENT_ID FROM REG_RESOURCE_COMMENT RC, REG_RESOURCE R

WHERE (R.REG_VERSION=RC.REG_VERSION OR

(R.REG_PATH_ID=RC.REG_PATH_ID AND R.REG_NAME=RC.REG_RESOURCE_NAME))

AND R.REG_DESCRIPTION LIKE ?

The example below shows the code to execute the query.

Note

You have to set "RESULT_TYPE_PROPERTY_NAME" to "COMMENTS_RESULT_TYPE."

String sql1 = "SELECT RC.REG_COMMENT_ID FROM REG_RESOURCE_COMMENT RC, REG_RESOURCE R " +

"WHERE (R.REG_VERSION=RC.REG_VERSION OR " +

"(R.REG_PATH_ID=RC.REG_PATH_ID AND R.REG_NAME=RC.REG_RESOURCE_NAME)) " +

"AND R.REG_DESCRIPTION LIKE ?";



Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.COMMENTS_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);


Map parameters = new HashMap();

parameters.put("1", "%production%");

Collection result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);



for (String commentPath: result.getChildren()) {

        String commentResource = registry.get(commentPath);

        System.out.println(commentResource.getContent()));

    }

Querying for ratings

In Querying rating you will be working on the "REG_RATING" and "REG_RESOURCE_RATING" tables. Here are the table structure related to ratings.

REG_RATING

REG_ID

REG_RATING

REG_USER_ID

REG_RATED_TIME

REG_RESOURCE_RATING

REG_RATING_ID

REG_VERSION

REG_PATH_ID

REG_RESOURCE_NAME

Here is an example of query to return ratings of a resource (non-collection) which is authored by a given name:

SELECT RT.REG_RATING_ID FROM REG_RESOURCE_RATING RT, REG_RESOURCE R

WHERE (R.REG_VERSION=RT.REG_VERSION OR

(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME))

AND R.REG_CREATOR=?

The following example shows a code snippet that execute this query and print the result rating values:

String sql1 = "SELECT RT.REG_RATING_ID FROM REG_RESOURCE_RATING RT, REG_RESOURCE R " +

"WHERE (R.REG_VERSION=RT.REG_VERSION OR " +

"(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME)) " +

"AND R.REG_AUTHOR=?";

Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.RATINGS_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);



Map parameters = new HashMap();

parameters.put("1", "admin");

Collection result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);



for (String ratingPath: result.getChildren()) {

        String ratingResource = registry.get(ratingPath);

        System.out.println(ratingResource.getContent());

    }

Note

When you put the query as a resource, you should set the property "RESULT_TYPE_PROPERTY_NAME" to "RATINGS_RESULT_TYPE".

Querying for tags

Here is the table structure related to Tags.

REG_TAG

REG_ID

REG_TAG_NAME

REG_USER_ID

REG_TAGGED_TIME

REG_RESOURCE_TAG

REG_TAG_ID

REG_VERSION

REG_PATH_ID

REG_RESOURCE_NAME

Here is an example query to search tags on resources (non-collections) that have a description with a given pattern.

SELECT RT.REG_TAG_ID FROM REG_RESOURCE_TAG RT, REG_RESOURCE R

WHERE (R.REG_VERSION=RT.REG_VERSION OR

(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME))

AND R.REG_DESCRIPTION LIKE ?

In the following example, there is the code to execute the query.

Note

You have to set "RESULT_TYPE_PROPERTY_NAME" to "TAG_RESULT_TYPE."

String sql1 = "SELECT RT.REG_TAG_ID FROM REG_RESOURCE_TAG RT, REG_RESOURCE R " +

"WHERE (R.REG_VERSION=RT.REG_VERSION OR " +

"(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME)) " +

"AND R.REG_DESCRIPTION LIKE ?";



Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.TAGS_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);



Map parameters = new HashMap();

parameters.put("1", "%production%");

Collection result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);



for (String tagPath: result.getChildren()) {

        String tagResource = registry.get(tagPath);

        System.out.println(tagResource.getContent()));

    }
com.atlassian.confluence.content.render.xhtml.migration.exceptions.UnknownMacroMigrationException: The macro 'next_previous_links2' is unknown.