Creating and Using Dependent Choice Lists

March 18, 2015

By: Dwayne Parkinson – Solution Architect

Creating and Using Dependent Choice Lists

In WebCenter Content there is a nifty little feature that gives you the ability to build something called a Dependent Choice List. A Dependent Choice List (or DCL if you like three letter acronyms) is a special metadata configuration where the values in one metadata field depend on the selected value in some other field. An easy example a region and country; where the list of available countries is limited based on which global region you select. Let’s dive right in and create our own Region and Country DCL so you can see how they work.

Step 1: Create Two Metadata Fields

All of the work to create a DCL will be done in the Configuration Manager. Select the Administration menu, then start the Admin Applets. Finally, invoke the Configuration Manager tool and select the Information Fields tab.

Create a field for Region and a field for Country by clicking on the Add button.

1

Fill in the name of the new field and then click on OK.

2

On the detail screen for your new Region field just accept the defaults for now by clicking OK. Do the same for Country. You will notice that once the new fields are added the Update Database Design button is enabled.

3

Click that button to add your new fields to the database.

Step 2: Create a Region Table

Now that we’ve got a field for Region let’s create a table to hold our regions. Still from within the Configuration Manager, select the Tables tab and click add. Create a region table with an ID and a Region Name as shown below.

4

Step 3: Create a Country Table

While still in the Tables tab, click Add again and create a table to hold our Countries as shown below.

5

There’s one very important trick to this table. You must have a column which will store the region ID that is associated with each country. In the table above, ID is the Country ID and RegionID is the ID of the region that is associated to the country.

Step 4: Add a Region View

In order to add data to our tables we will need to create a view over them. Select the Views tab from within the Configuration Manager and then choose the Add button to add a view for our Regions table. Select the Regions table from the list and click on Next as shown below.

6

Add both the ID and RegionName columns from our table to the view and click on Finish as shown below.

7

Finally, give our view a name of vRegion and be sure to set the Visible Column to RegionName as shown below.

8

Step 5: Add a Country View

Everyone loves a country view right? Let’s add one following the same procedure we did with Regions. Click the Add button from the Views tab and select the Country table as shown below.   Then click Next.

9

Now we’ll add all three columns of the Country table to our view as shown below.

10

We’ll name this view vCountry and then be sure to set the Visible Column to CountryName as shown below.

11

Step 6: Create a Relationship

In order for the two tables to know about each other, we must create a relationship. Still within the Configuration Manager, select the Relationships tab and click Add. Then create the relationship as shown below.

12

 

To make our lives easier we’ll name the relationship exactly what it is; RegionToCountry. The Parent Info contains the table that holds our Parent table. In this case Region, since countries are contained within regions (i.e. United States is in the North America and Caribbean region). The second drop down in the Parent Info is used to designate the column in that table that will be used to tie the Child table.

For the Child Info we select the Country table as our Child table. Now we’re going to select the RegionID from the Country table as the column which should match the ID field in the Parent Info section. That effectively links the two tables together and establishes a relationship.

Step 6: Update the Region Metadata Field

With our tables, views and relationships in place, we can now go back and finish configuring the Region metadata field. Still from within the Configuration Manager, select the Information Fields tab and then highlight the Region field and click Edit. On the Edit screen, check the box to Enable Option List and then click the Configure button.

13

As shown below, check the box next to Use view and select the vRegion view.

14

Click OK to complete the configuration. The Region metadata field will now get values directly from our region view.

Step 7: Update the Country Metadata Field

Just as we did with Region, highlight the Country field, click edit, check the box to Enable Option List and click the Configure button. Check the Use View checkbox and select vCountry as the view as shown below.

15

We have one very important last step to complete this configuration. We have to tell the Country field that it is dependent on the Region field. To do that, enable the Dependent Field checkbox and select the Region metadata field.

The last step is to tell the Country field how it depends on the Region field. This is done by setting the relationship. Select our RegionToCountry relationship and click OK.

Step 8: Add Some Data

To make everything work, we have to add data to our tables. From within Configuration Manager, click on the Views tab, highlight the vRegion view and then click the Edit Values button (not the Edit button).

16

On the next screen click the Add button to add ID’s and values to the Regions as shown below.

17

Once the Regions have been entered, go ahead and enter some countries. Select the vCountry view and click Edit Values just as you did with Regions. Then enter some countries as shown below.

18

It’s very important that the RegionID is entered correctly in your vCountry view or your dependent choice list won’t work correctly.

Step 9: Test Your DCL

Now it’s time to test. Close the Configuration Manager and then press CTRL + F5 so your browser will refresh the page and clear out the cache. This is important because drop down menu values are often cached. If you don’t clear the cache you will make changes in the Configuration Manager and wonder why you’re not seeing them on the web page.

If you select New Check-In you should see a Region metadata field with data that is contained in the vRegions view.

19

When you select one of the options, you should see that the options for the Countries drop down changes to match only those countries that are within the Region you selected.

20

DCL Chaining

You can set up many levels of DCL’s in WebCenter Content so field 1 depends on field 2 which depends on field 3 which depends on field 4. As you add levels you add complexity, but the system is capable of handling it if those pesky humans can just manage to keep the data correct.

Multi Select Limitations

As nifty as WebCenter Content DCL’s are they have a couple of important limitations. The first limitation is that DCL’s don’t work as expected if the Parent field is a “multi-select” list. In other words, if I wanted to choose Africa and Asia as my regions and then expect my dependent drop down of Countries to include all countries from both Africa and Asia… that’s not going to work. The dependent list will show only countries for the region that is currently in the drop down selector tool. It will not show any countries for the previously selected regions in the multi-select list to the right of Regions field.

Interestingly, you can have the Child field be a multi-select list. Using our example, perhaps the business wants to be able to select multiple countries from anywhere in the world, but they don’t want a drop down with 300 countries. Using the DCL we created above, the user could select the region, select a country, change the region, select another country and change the region again to select a third country. Now they’ve selected three countries from three different regions without having to scroll through hundreds of countries.

Complex Relationship Limitations

The other significant limitation to WebCenter Content DCL’s is that they’re not meant for complex relationships. While you can “fake” a couple of things, they simply aren’t meant for complex logic. For example, if we had two independent fields of Color and Texture and we wanted a third field to show different values for Blue Smooth things, Blue Rough things, Red Smooth things and Red Rough things.   That sort of logic quickly goes beyond the intended use of a DCL.

Summary

The WebCenter Content Dependent Choice List (DCL) is a fantastic tool for representing simple relationships between metadata fields. It makes the user experience easier and helps to maintain data integrity. Hopefully the example above will give you everything you need to deliver some very nice features to your business. If you find that the limitations of DCL’s are cropping up in your business, please contact TEAM Informatics. We’ve got some custom solutions that we can use to deliver the functionality you need to overcome DCL limitations and add tremendous value to the business.

Want to meet us at Collaborate 15 in Vegas to talk about your WebCenter needs? Fill out the contact form and we will work with you to arrange a meeting!


Friendly URLs in WebCenter Sites

March 4, 2015

By: Darek Blankenbuhler – Application Consultant

Having friendlier, cleaner looking URLs has always been a challenge in WCS. In the past it would require a developer to write a Java class. Once the class had been written it would need to be configured and deployed on every instance of WebCenter Sites in your setup. If you wanted a second assembler for another asset type or blobs that would require even more configuration and coding to work properly. Suffice it to say that in the past making a friendlier URL was a difficult task especially if friendly URLs were not set up during the initial build. This has all changed with the advent of WebCenter Sites 11.1.1.8. Friendly URLs have been built right into the Advanced Interface and no longer require as much specialized knowledge. An administrator can find this new feature in the Admin tab under Asset Types inside the Asset that is being configured.

Fr1

Today I want to set up a new pattern for the Page Asset type. As you can see there are no URL patterns set for pages yet. So, I will go ahead and click Add New, and I will be brought to the following page:

Fr2

Now I am ready to start creating my first URL pattern. As you can see I can be as generic or as specific as I want to be. No longer do I have to worry about writing my templates and URL assembler to work together to render the final product. The system will go ahead and take care of that for me. The URL is comprised of the different attribute values available to the asset. In the case that the Subtype is set to “Any” I will only have access to the default basic attributes that come along with every asset type that is created. By selecting a subtype I can now use attributes that are a part of that asset type in the URL.

Fr3

The primary downside to this is the fact that now the “Standard” subtype will have its own pattern. If I only set up the “Standard Page” URL only page assets with the subtype “Standard” will have a friendly URL. It is a good idea to set up a pattern for “Any” type as before going live. Last we will put it all together with the pattern itself. The pattern uses the Java Expression Language to build the URL. As you can see I can put as much or as little information in the URL as I want too. When I have finished configuring the URL, at the bottom of the page I can get a preview URL for existing pages.

fr4

This way I can test to make sure my URLs are going to come out clean looking before I implement the new URLs. Then I can save and I have completed building the URLs. URL Patterns are stored as a part of the asset type. Which means that all I have to do is publish the asset type to get the URL pattern to be pushed between systems – this tool is kept as simple as possible.

All in all, the new URL pattern tool is a powerful and easy to use tool and a welcome addition to WebCenter Sites. Whether it is the ability to generate URLs using any information in the asset or the ability to make the URL assembler as specific as we want. URL assemblers are now much easier to make and implement.

Want to meet us at Collaborate 15 in Vegas to talk about your WebCenter needs? Fill out the contact form and we will work with you to arrange a meeting!


Content Security in Alfresco One

February 10, 2015

By: Jon Chartrand – Solution Architect

teamandalfresco

Locking down content is a primary concern for those leveraging – or considering – Alfresco One for its content management (CM) capabilities. Thankfully, Alfresco makes this simple by allowing and controlling access across five separate “tiers”, each of which provide opportunities for users and managers to specify and manipulate privileges to both users and groups. Beyond that, keep in mind that users, groups, and site participation can be controlled outside of Alfresco at the enterprise level with LDAP/Active Directory, Kerberos, NTLM, and others. This allows for IT-managed oversight and tight controls on the first two tiers of Alfresco security.

Platform Tier (Access/privileges to the application)

The Alfresco platform is relatively unique in that a user can be granted access to “just” the application itself. This means they cannot access or participate in any of the Site-based activities but they can log into Alfresco and utilize both the “My Files” and “Shared Files” portion of the personal dashboard. This creates opportunities for content to be shared or collaborated-on with a relative outsider to the platform without worrying about them accessing more sensitive materials contained within an Alfresco site. Those considered ‘Alfresco Users’ can be created in Alfresco directly by an administrator or provisioned in your enterprise SSO architecture.

Site(s) Tier (Access/privileges to the Site)

Alfresco’s repository and social functions are broken out into logical groupings called Sites. Each site represents one or more users engaged in a common theme, effort, or goal which has been granted its own area of the platform. More than just sharing files, users of the sites can collaborate with one another through calendaring and events, managing and updating a wiki, contributing to a blog, establishing lists of links, and other activities. For now, however, we’re going to examine security solely against the mirror of content management. Privileges within a site are dictated by the role each user is granted. This assignment can be managed either by a site administrator or at the enterprise level.

Users are granted one of four standard roles within an Alfresco site: consumer, contributor, collaborator, or manager. When focused on content, the role privileges shake out like this:
Consumer: Read
Contributor: Read, Upload, Create
Collaborator: Read, Upload, Create, Checkout, Edit
Manager: Full Control

This allows site managers the ability to grant a range of permissions to each user allowing them to participate in the consumption, contribution, collaboration, or management of content as necessary. Another additional feature is the ability to grant site-level roles to groups of users in Alfresco. This makes setting, managing, and revoking access for multiple users an extremely simple affair. When it comes to privilege overlap, such as for a user granted an explicit role by username and an inherited one through a group, Alfresco operates on a ‘most-permissions’ model. This means the user in this case would be granted whatever the widest permissions allowed based on either role assigned.

Folder(s) Tier (Access/privileges to the folder)

Within either the Shared Files or a sites’ Document Library, the content is managed by whatever logical organization of folders is chosen by the contributors or site manager. This makes both contribution and consumption a simple matter – just like using a Windows file share. The great thing about Alfresco folders though is that users can manage permissions on any self-made folder with a remarkably high degree of granularity.

1

The permissions window for a folder, seen here, provides an easy to manipulate interface for altering the roles and permissions everyone has to it. The first thing to note, however, is that folders automatically inherit the permissions of their direct parent. At the top of the screen you can see the checkmark next to “Inherit Permissions” which provides a visual indicator of this and requires that the user specifically undo this function (with a confirmation) to be able to set their own permissions. This process means users cannot “stumble” into setting custom permissions on a folder without an explicit and forewarned act.

2

Once the inheritance is removed, the user is now free to assign both users and groups to the folder permissions. Alfresco folders have a slightly different role-scheme than Sites do.

3

Rearranged from the order in the image to instead display by order of increasing permissions:
Consumer can read content
Editor can read and edit content
Contributor can read and add content
Collaborator can read, edit, and add content
Coordinator can read, edit, add, and delete content (full access)

These roles mean that users have a wide range of options for how they would like others to interact with their folder(s) and the content within. The scenarios where this kind of non-inherited permission capability would have great value are nearly endless in a collaborative environment like Alfresco.

File(s) Tier (Access/privileges to the file)

Taking the next step through the tiers, Alfresco also allows customizing the permissions of individual files within each folder as well. Users who own the content or are designated managers of the site the content lives within can access and alter the permissions, making the same level of customizability at the folder level available at the file level. This is accessed in exactly the same way and with the same interface, making for a consistent experience when dealing with permissions.

4

Like folders, files inherit their permissions from their parent – in this case the folder the content currently lives in. Updating the permissions means first disabling (and confirming) the removal of that inheritance, but once complete the author or manager has access to the same set of roles as is available at the folder level. This means authors and managers have exceptionally granular control over their content; who can see it, who can edit it, and who can delete it.

Of course, we’d be remiss if we didn’t discuss some of the dangers this level of granularity can evoke. For example, if a file is set to allow PersonA access as a Collaborator but is then moved to a folder that PersonA does not have permissions to read, it effectively renders the file permissions moot. Likewise, if a site manager establishes specific permissions on a folder and an author overrides those permissions at the file level, this could lead to complications in accountability and auditability. As such, altering permissions at either a folder or file level is something that should always be considered carefully and in consultation with an administrator or site manager.

Record(s) Tier

An optional addition to an Alfresco installation is the Records Module. This creates and enables an entire suite of Records Management capabilities within Alfresco One including locking, cut-offs, disposition schedules, freezes/holds, and comprehensive audits. When it comes to content, declaring something a record indicates to the environment that this item is complete and needs to be sealed for future discovery or destruction purposes. This declaration makes the content-now-a-record essentially inviolable to everyone but administrators and provides deep auditing capabilities – including storing the audit report as a record itself. While this tier of security isn’t customizable in the same way the others are, it certainly represents another way in which content can be secured and controlled in Alfresco One.

Conclusion

To review, the five tiers of security within Alfresco One are:

1. Platform (Access/privileges to the application)
2. Site(s) (Access/privileges to the Site)
3. Folder(s) (Access/privileges to the folder)
4. File(s) (Access/privileges to the file)
5. Record(s) (Cannot be changed)

Each of these represents a different level of control you have over your content in an Alfresco installation. This means you and your users have unparalleled control over who can see, upload, edit, and delete materials in a content management system which already provides so many benefits over traditional file shares like file versioning, collaboration, and workflows.

If you’re interested in learning more about security in Alfresco One or how Alfresco can help you keep your content under control while also enabling collaboration across your enterprise, let us know!


Easy Mass Metadata Updates In WebCenter Content

October 2, 2014

By: Dwayne Parkinson – Solution Architect

*This blog is informational in nature on an approach to mass updating WebCenter Content. Because each WebCenter implementation is unique in nature, it is not intended as a “how to” guide and any use of this information is at your own discretion.

If I had $100,000 for every time someone wanted to perform a mass update in WebCenter Content, I’d be a very wealthy man. The request doesn’t happen enough for me to lower the price, but it’s still a fairly regular request so I think it’s worth knowing how to answer the question: “How can I update all of these content items with a new metadata value?” Fortunately WebCenter Content offers a number of ways to do this.

Before we dive into the details I’ll just state the obvious disclaimer. Performing mass updates to large groups of content can have some unintended consequences so be careful!

Records Management Global Update

Surprise!!! There’s an incredibly easy to use feature called “Global Update” and as the name suggests, it allows you to perform global updates to metadata. The only caveat is that you must have WebCenter Content: Records Management enabled to use this feature. From the Records menu select Global Updates. From there you will see three options that you can update globally: Retention Categories, Records Folders and <insert heavenly choir of angels singing here> Content!

Untitled

When you select the Content option you will see the Global Updates: Content screen.

Untitled2

As shown above, you can define a query to select the content you want using standard metadata fields or free text searching and then define the metadata field to update. Notice the Preview option allows you to see the results of your query to insure you are updating only the content you want to update.

For reasons unknown to me, this incredibly useful option is hidden by default from the RM admin user. You will need to enable this feature by doing the following:

1) Login in as weblogic or Admin user.

2) Navigate to Administration > Admin Applets

3) Select the User Admin Applet

4) In the User Admin Applet > select the top menu “Security” option and then select the “Permission by Role” option.

5) In The “Permisions by Role” section, Highlight  the RMadmin Role and then select “Edit RMA Rights” option.

6) Select the “Admin” Tab from the “Edit RMA rights” section, then check the “No Security” option and select OK to save.

7) Exit out of the applet, and navigate to “Records “ and choose “Global Updates” to confirm this feature has been enabled.

 

Folder Metadata Propagation

For content that is stored in folders there is an exceptionally easy way to perform a mass update of metadata values. However, it’s important to know that there are two versions of folders implemented in WebCenter Content and as a best practice you have either one or the other, not both.

Let’s start with the “old” version of folders which is called Folders_g. If you click on the Browse Content menu and you see an option for Contribution Folders, then you have Folders_g.

Untitled3

To perform a mass metadata update for all content items in a Folders_g folder you browse to the folder containing the content you want to update. At that point you click on the Information icon.

Untitled4

On the next screen you select the Actions menu and choose Update to update the metadata for the folder.

Untitled5

Once you’ve updated the metadata from the folder, you still have one more step. Select the Actions menu again and choose Propagate. This will propagate the metadata change to all content items in that folder.

If you have the new version of folders, called Framework Folders, when you click on the Browse Content menu you will see an option that says Folders.

Untitled6

As with Folders_G you navigate to the folder which contains the items you want to update. Once you’ve selected your folder choose the Edit menu and update the metadata values for the folder.

Untitled7

After the metadata for the folder has been updated, select the Edit menu again and choose Propagate. This will update all of the content items in the folder with the new metadata value.

Yes, I know what you’re thinking. Folder propagation is great but it leads to an obvious question: What if not everything in the folder needs to be updated or your content is spread across hundreds of folders?

Query Folder Propagation

With the advent of Framework Folders, Oracle invented a new gizmo called a Query Folder. A Query Folder is a folder which contains the results of a query. To add a Query Folder you simply browse to any folder in your system that you want to contain the Query Folder, click the Add button and choose New Query Folder.

Untitled8 copy

The Query Folder creation screen allows you to use Full-Text Search or Query Builder options to determine which content items should be included in the Query Folder.

Untitled8

Once created, you will see a new Query Folder on your system and it will have a different icon that is a folder with a magnifying glass rather than just a folder.

Untitled10

This new Query Folder contains call content items that match the query defined when the folder was created. With the new folder in place you can use the Folder Propagation techniques above to update all of the content items in your Query Folder. This two step process of creating a Query Folder containing just the content items you want plus folder propagation, gives you the ability to have fine grained control over your mass update.

Conclusion

As you might expect, there are a number of hard and complex ways to perform mass metadata updates as well, but with the tools above, making mass metadata updates can be quick and easy.

If you have questions about WebCenter and want to talk to someone at TEAM, contact us today!

 


TEAM Announces Gold Systems Integrator Partnership with Alfresco Software, Inc.

August 4, 2014

ARDEN HILLS, MINNESOTA — July 31st, 2014

TEAM Informatics, Inc., a leading enterprise software products firm, announces its acceptance as a Gold Partner with Alfresco, named a Visionary in Enterprise Content Management by Gartner Magic Quadrant. TEAM announced the alpha release of its Google Search Appliance (GSA) Connector for Alfresco earlier this year and has continued to expand the partnership. TEAM’s connector product allows the GSA to index and search content held within the Alfresco repository, allowing customers to search content with the familiar Google interface.

“As TEAM continues to find a growing interest in Alfresco as a comprehensive content solution, we have begun developing our capability to provide quality Alfresco development services to our customers,” said Doug Thompson, President of TEAM. ”Our proven success with Oracle WebCenter and Enterprise Search provides the foundation for us to expand our services to include the Alfresco platform.”

TEAM’s recognized ability to provide its customers with successful content management solutions puts the company at the forefront of the enterprise content market. Find more information on the Alfresco Partnership, the GSA to Alfresco Connector and other TEAM products at www.teaminformatics.com

Watch the Alfresco One Introduction

Watch the Document Management with Alfresco One Overview

Watch the Collaboration with Alfresco One Overview

Watch the demonstration of TEAM’s Alfresco Connector for the Google Search Appliance

 


Customizing WebCenter Content: Part 5 – Custom Service

July 2, 2014

By: Dwayne Parkinson – Solution Architect

As we conclude our Blog series on customizing WebCenter Content we are going to finally create a custom WebCenter Content service which will leverage the query and template we created in earlier Blog entries.  Once we have a custom service created, it can be placed on WebCenter Content menus, called from applications, used in iDoc Script and other WebCenter Content services. While our service will perform relatively simple functions, you will see how the same steps can be used to build fairly elaborate customizations.

Create a Custom Service

A Service is yet another type of resource that exists within a custom component, so the first step to creating a custom service is to create a custom component.  We already have a custom component from our previous Blog entries, so using the Component Wizard we’ll open the example component we created in the previous Blog entries and put our service in that component.

To create a service you click the Add button in the Custom Resource Definition pane of the Component Wizard.  On the next screen select the Service checkbox and accept the default names.

cw51

Make sure the service is in the “resources” folder and click on Next.

The next screen will ask for a Table Name which is used to store information about the custom service.  Accept the default table name here as well.

cw52

Finally we get to the point where we can add our custom service and tie all of our work together.  The top half of the screen defines our service while the bottom half tells what actions the service will perform.
cw53

The Name field is the name of your service.  The standard convention in WebCenter Content is for all service names to be in all upper case with words separated by an underscore.  The service name will be what you see in the URL when the service is called.  So in our case the portion of the URL with our service would look something like this: idcplg?IdcService=EXAMPLE_GET_FULL_NAME

The next field designates the class of service.  The class of service is really referring to a Java class and it determines what actions can be performed by the service.  For our example we’ll use the class of Service.

The Template field is used when there is a template to display results of the service.  We are going to use our EXAMPLE_PAGE template resource that we created in a previous Blog entry.  This template is a page that says “Hello World” with standard WebCenter Content page begin, end, etc.

The Service Type field we will leave blank.  The type of Sub-Service is only used when you are creating a service that will only be called from another service.  Our example service will be initiated directly from the URL so we will leave this blank.

The Access Level must have Scriptable checked for your service to execute.

The Subjects Notified is for defining which subsystems within WebCenter Content should be aware of this service.  For our example (and in most cases) this will be blank.

Finally, the Error Message is a text field that defines the error message returned by the service unless one of the actions overrides it.  For our purposes we will leave this blank and now we’re ready to start adding some actions to our service.

Adding Service Actions

The action list defines which actions the service will perform and in what order.  To add an action, click the Add button in the Action section.

cw54

The Type of action indicates what type of resource we will be invoking from our service.  The table below provides information on what each type of action does.

Action Type Description
Select Query Executes a select query, but does not return the results. Instead, the query will return whether the execution succeeded or failed.
Execute Query Executes data manipulation queries (insert, update, delete). The execution will not return any results, but will alter the database.
Java Method Executes a Java method. The method can be contained in the Service or a handler.
Load Option List Loads an option list into local data of the service. The option list is available to use in a template.
Select Cache Query Executes a select query and puts the results into Content Server’s backend data. The output will be available as a ResultSet.

 

Since we will be using the query resource we created in a previous Blog entry to return the full user name, we will use the Select Cache Query.

The next step in adding an action is to specify the name of the action.

cw55

In our case the name of our query was QfullName so we will type that in to the action field.  NOTE: There is a quirk in action field.  When you tab out of the action field the first time it may default back to the first entry from the drop down list and you will need to retype your action name.

The Parameters section specifies the parameters for the action.  Since we’re using a Select Cache Query, the parameter we specify will be used to store the results returned from our query.  By entering FullName, the service will generate a result set with the name FullName.  The result set will contain the results of our query.

The Control Mask is beyond the scope of this Blog, but documentation can be found here: http://docs.oracle.com/cd/E23943_01/doc.1111/e11011/c03_customizing_services.htm#CSSRG2048

The Error Message is the text that will be displayed if this action produces an error.  We will leave it blank and allow system generated errors to be seen.

Now we can click on OK and complete the definition of our service.

Testing the Service

To test our service we must first enable our custom component using the Component Wizard and then restart the content server so our component can be loaded.  Once that’s complete, sign on and change the URL so it calls our EXAMPLE_GET_FULL_NAME service as shown below.

cw56

Notice that there’s a parameter of dName after our service.  This parameter is required by the QfullName query we used so it must be supplied after the service call.

When we call the service you’ll notice that the EXAMPLE_PAGE template is displayed.  This shows us that the template we hooked up to the service is working as expected since we’re seeing our “Hello World” message.

Of course we also want to know if our service actually returned the full name of the user by executing the QfullName query.  To do that let’s execute our service again and append &IsSoap=1 to the end of the URL as shown below.

cw57

When using the IsSoap parameter, the results are redirected from our EXAMPLE_PAGE template to a SOAP envelope which is nothing more than an XML file.  As you can see, the result set of FullName contains a row that says the full name for user weblogic is Web Logic.  (Remember, we specified FullName as the parameter on our Select Cache Query and that’s how the result set gets its name)

The Finished Product

We would be remiss if we left our discussion of custom components, services and templates without actually displaying the results of our query on our template.  While it’s beyond the scope of this Blog to dive deeply into the iDoc scripting language, thankfully we only need a few lines of iDoc Script.  Within the Component Wizard, highlight the template resource and select our EXAMPLE_PAGE template.  Now we can click on the Launch Editor button in the lower right corner of the screen and add the highlighted lines of iDoc Script to our template.

cw58

Once those three lines are added, the server must be restarted and then we can execute the service again.  With our template changes in place, the service will show the following screen.

cw59

Conclusion

We have covered a lot of ground to merely show Hello World and the full name of a user, but the principles we’ve demonstrated are incredibly powerful.  As you can see, WebCenter Content is not just a piece of software; it’s a framework that can be extended for developing truly custom solutions to meet the unique needs of your organization.

For more information about the possibilities of extending WebCenter Content to meet the requirements for your next project or to request customized training for your development team please contact TEAM Informatics and ask about our extensive WebCenter Content customization capabilities.

 


Customizing WebCenter Content: Part 4 – Query Resources

June 25, 2014

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.

cq1

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/resources/core/query.htm.

cq2

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

Column Description
name The name column specifies the name for the query. The name needs to be unique. If the name is not unique, it could override an existing query. It is recommended that a convention is used to uniquely identify queries. See the naming standards below.
queryStr The queryStr column specifies the SQL query. The SQL query can update, delete, insert, or select rows from the database. Parameters may be added to the query by using a ‘?’.
parameters The parameters column specifies all of the parameters for the query. The parameters must have a name and data type associated with them. The order of the parameters is important.

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

Prefix Description
Q SQL queries that select row(s) from a database table.
U SQL queries that update row(s) in a database table.
D SQL queries that delete row(s) from a database table.
I SQL queries that insert a row into a database table.

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 /resources folder of the component.

cq3

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.

cq5

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.

@ResultSet ResourceDefinition4type

filename

tables

loadOrder

query

resources/examplecomponent_query.htm

exampleComponent_Queries

10

@end

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.

cq6

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.

cq7

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.

cq8

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

Action Type Description
Select Query Executes a select query, but does not return the results. Instead, the query will return whether the execution succeeded or failed.
Execute Query Executes data manipulation queries (insert, update, delete). The execution will not return any results, but will alter the database.
Select Cache Query Executes a select query and puts the results into Content Server’s backend data. The output will be available as a ResultSet.

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.


Follow

Get every new post delivered to your Inbox.

Join 65 other followers