WSO2 Governance Registry - Developer Guide [ Documentation Index ]

WSO2 Governance Registry - Developer User Guide

Overview

Custom Queries interface allows you to search the registry using a custom sql queries. In a custom query you are expected to return one of following items.

Resources
Comments
Ratings
Tags

Quering for Resources

In quering 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 together act as an id for the resource. Example1: Following query will returns one or more resources that has a given pattern in the description


SELECT REG_PATH_ID, REG_NAME FROM REG_RESOURCE WHERE REG_DESCRIPTION LIKE ?

Example2: A code snipet that do a custom query.


    // first you should 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("/custom-queries", q1);



    // then you should give the parameters and the query location you just put

        Map parameters = new HashMap();

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

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

    String[] paths = (String[])result.getContent();

Quering 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. If the properties versioning is set 'true' (which is the default), the foriegn key relationship happens in the following way.

Figure 1 : Foriegn key structure for properties table - (Versioning properties set to 'true')

Example3: Following query will returns one or more resources that has 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=?

If the versioning properties is set to 'false', then the foriegn key in focus are

Figure 2 : Foriegn key structure for properties table - (Versioning properties set to 'false')

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

Example4: Following query will returns one or more non-collections that has 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=?

Example5: Following query will returns one or collections that has 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=?

Quering for Resources By Tag

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

Simmilar to properties, tags are also stored in two modes. Here is when versioning tags are set to 'true'.

Figure 1 : Foriegn key structure for tags table - (Versioning properties set to 'true')

Example6: Following query will returns one or more resources 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_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 foriegn key in focus is

Figure 2 : Foriegn key structure for tags table - (Versioning tags set to 'false')

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

Example7: Following query will 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=?

Example8: Following query will 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=?

Quering for Ratings

In quering 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

Example9: Here is an example of query to return ratings of a resource(non-collections) 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=?

Example10: A code snipet 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("/custom-queries", q1);



        Map parameters = new HashMap();

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

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



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

        String ratingResource = registry.get(ratingPath);

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

    }

Note that when you are putting the query as a resource you should set the property "RESULT_TYPE_PROPERTY_NAME" to "RATINGS_RESULT_TYPE".

Quering 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

Example11: Here is an example query to search comments on resources (non-collections) that has 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 ?

Example12: The code to execute this query. Note that 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("/custom-queries", q1);



        Map parameters = new HashMap();

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

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



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

        String commentResource = registry.get(commentPath);

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

    }

Quering 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

Example13: Here is an example query to search tags on resources (non-collections) that has 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 ?

Example14: The code to execute this query. Note that 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("/custom-queries", q1);



        Map parameters = new HashMap();

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

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



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

        String tagResource = registry.get(tagPath);

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

    }

WSO2 Governance Registry v3.0.2

Downloads

Documentation

Samples

Getting Help

Get Involved

Project Information