Customizing WebCenter Content: Part 4 - Query Resources

By: Dwayne Parkinson - Solution Architect

The past few Blog entries have covered some WebCenter Content building blocks that are necessary before we begin customizing the system.  In this entry we will explore the query resource which is that last of our basic building blocks before we start gluing everything together and creating our own fully functional custom components.

The query resource allows a component store and execute any number of custom SQL queries. The SQL queries run directly against Content Server’s database. The queries defined in the query resource can be executed from a service, Idoc Script, or Java and are therefore incredibly useful.

Content Server Database

The query resource requires some information about Content Server’s database.  To develop query resources you will need a connection to the Content Server’s database and some sort of SQL tool like Oracle SQL Developer.  Once you have established a connection to your server you should see tables similar to those shown below.

Content Server Tables in Oracle SQL Developer

Creating a query resource will require knowledge of the existing tables and SQL. A few of the most popular tables used for customizations are:

-       DOCMETA: Metadata (including custom metadata fields) for all content items on the content server.

-       DOCUMENTS: The document name and other document related information.

-       REVISIONS: Information about each revision of a content item such as the document type, title, creation date, author, etc.

-       USERS: Contains information about the user profiles in the system.

The query resource may also define new tables and access those tables, but that’s beyond the scope of this Blog entry.

Content Server Queries

Before creating a query resource for a component, we can look at Content Server’s core query resource for a good reference. Content Server’s core query resource contains hundreds of queries that may be used in a component. The core query resource can be accessed at <install_dir>/ucm/idc/info/core/query.htm.

Content Server’s Core Queries

The query resource above is the HTML representation of the query definition. Notice that the table is broken into three columns. The columns are name, queryStr, and parameters. Although with the Component Wizard these will be managed automatically, knowing the columns is useful.

Query Resource Columns

Query Naming Conventions

When you are creating a custom component with a query resource, the SQL queries defined in the query resource should have a unique name. Content Server generally follows a standard naming convention to help manage the types of queries. When adding a query resource to a component, it is recommended that a prefix convention is added to the name of the query as well. For example, a query inside of the component ‘exampleComponent’ may be QfullName. The table below shows the standard prefixes throughout Content Server.

Standard Query Naming Convention

Adding a Query Resource

Once a component has been created, a query resource can be added from the Add Resource form.  Notice that the query will be stored in an htm page within the /info folder of the component.

Add Resource Form

By selecting the query resource, the file name should change to follow the standard “_query.htm” convention.  The query resource can be used to define new queries, override existing queries, and expand existing queries.

An existing query may be loaded and edited by selecting one by name from the list of queries for a particular query resource or a new query may be added.  As expected the Add Query form requires a Query name to be specified.  The Edit Query form does not have the opportunity to specify the query name, but otherwise it is identical.  In both cases you must provide the SQL query text, and optionally specify parameters.

Adding a Query to the Resource

The Component Wizard will then add the new query to the query resource and behind the scenes a “table” will be created to store the information.  It’s important to note that if you want to rename a query you must manually edit the htm files created by the Component Wizard.  For example if you wanted to change the query above to be QgetFullName once it had been created you would edit the html tables manually.

Beyond the query resource file, the Component Wizard will automatically add the query resource to the glue file as well.

The example above shows an example of a query resource, the path to the resource, the query table name, and the load order.

Query Parameters

The use of parameters is important in queries. Content Server supports the use of parameters in queries by using a “?” place holder for each parameter. Each of the parameters used in the SQL query must associate with a parameter that is defined in the parameter list for the query.

Adding a Parameter to the Query

The query above requires that a User Name is provided. The query will need to have a parameter added to the list of parameters before it will run successfully. Notice that in the example only one parameter is used, but with multiple parameters the order of the parameters in the list is important and the interface allows the parameters to be shifted up and down by using the Up and Down buttons.

Adding the Parameter to the Resource

The parameters will need to have a name and type specified. The types that are available are directly associated with the database data types. The type needs to match the data type that exists in the database table.

The name, queryStr, and parameters will then be added to the HTM file which stores the query information. The columns will match all of the information that was specified inside of the Component Wizard’s interfaces.

The HTML representation of the query resource

Service Query Action

Now that we’ve got a query defined we need some way to execute the query.  As previously mentioned, we could execute the query from Java, iDoc Script or from a Service.  In the next Blog entry we will go into more details on creating a service and executing our query.  For now it’s important to know that when you create a service that executes a query you have to tell the Content Server what to expect when the query is finished because the return results differ.  A query that performs an SQL Update or Insert will return different results than one that performs a Select.

When creating a query service there are three action types that may be used.

Service Query Action Types

As you might guess, selecting the right Action Type is a critical step for the query service to operate as expected.

Summary

In the next Blog entry we’ll dive into more detail on how to create a service to execute a query.  Since the entire WebCenter Content platform is built on the concept of executing services, our ability to build services which return and update data from the content server files is incredibly powerful.

For more information on customizing WebCenter Content and customized training to suit the needs of your organization, please contact TEAM Informatics and let us help you increase the ROI on your WebCenter Content investment.