CSqlQuery

This page describes a new uPortal channel in the CVS HEAD towards uPortal 2.6.

Motivation

Some portal use cases amount to views that are digital dashboards aggregating indicators and information to better inform user behavior. These dashboards range from portal administative uses: (how many users are logged in? how many have customized their layouts? What are the most popular channels? How many users have logged in in the last 24 hours?) - through more general IT support uses: (How many open support requests are there? Of high urgency? How many students have yet to register for network access? How many IP addresses remain in the various subnet pools?) through Academic uses: (how many students have yet to register for courses? In my college? Who are my academic advisees? Who are the students in my classes?) - through miscellaneous other uses.

Much of this sort of information is available by querying RDBMSs.

CSqlQuery is intended to be a small, simple, generic SQL query channel. The channel takes as configuration a DataSource (named and provided via JNDI), a SQL query, and a path to an XSLT. A CPD formalizes this publication workflow, adding form fields and contextual help, creating a "SQL" channel type. The channel executes the SQL query and translates this to XML, which it then presents to to the XSLT selected by the stylesheet selector for rendering. A generic XSLT renders the ResultSet as a simple HTML table.

What is this useful for? With this channel, the effort of adding a new dashboard indicator to the portal exposing data from a databse is a matter of identifying the DataSource, composing the SQL query, optionally creating a customized XSLT to render the result with context and window dressing, and we're done. uPortal's built-in subscribe permissions mechanism controls access to the query. uPortal's layout management allows composing this and other widgets into comprehensive dashboard views.

This approach empowers an additional subset of university IT staff to compose portal content. Some staff are comfortable in SQL - indeed, may have day jobs centered around SQL in the context of their particular domain. This channel opens up the ability to use that SQL query knowledge to add content to a university portal. This does not necessarily imply that all these various staff will be Portal Administrators directly adding their SQL to the portal. They may be the "owners" of remote systems or functionality subsets working through a portal administrator or team. This channel approach still empowers a conversation around SQL rather than around XML, remoted XHTML, IChannel, JSR-168, or even WSRP (which under different circumstances may each be the best answer to a particular content integration problem.)

There are, no doubt, many problems worth solving "the right way", with a layered architecture, Spring PortletMVC, custom Java implementing "business logic", with DAOs, interfaces, and service beans galore. There are, however, some problems that are only worth solving if solving them can be done very cheaply and quickly. Some problems are throwaway problems - I want to watch network registrations ramp up this week, but then I don't care again for a year. This sort of channel gets at providing that quick and dirty first pass at solving a portal data presentation problem. That mere exposure of information solved, the problem might later be revisited to add the value a real dedicated portlet can provide in interactivity, dynamically configuring the query, etc.

This channel leverages what uPortal is good at - simple, generic, reusable components relying on the framework to provide access control.

Usage

Publication

I discuss here using the Channel Manager and the Channel Publishing Document mediated workflow for publishing the channel. You could instead use the non-web-UI approach to channel publication.

Log in as Portal Administrator

(Actually, any account with the Channel Publishing privilege will do, but Portal Administrator is the most commonly used such account.)

Access the Channel Manager

In HEAD, the Channel Manager link seems to have disappeared. A URL like this will get you to the Channel Manaager, so long as you're loggeed in as an Administrator. This is really annoying and should be fixed.

http://localhost:8080/uPortal/render.userLayoutRootNode.uP?uP_fname=portal/channelmanager/general#n2

Publish a New Channel

Choose the SQL channel type

Complete the channel publishing workflow

DataSource

Specify a JNDI name of the DataSource you'd like to query. The uPortal DataSource is typically named "PortalDB".

SQL

Specify a SQL query against the given DataSource.

XSLT URI

Specify a relative or absolute URI to an XSLT. The default currently just renders an HTML table with the content of the ResultSet.

Overall configuration review

Subcribe to and try out the channel

Click "Finished" to exit the channel publishing workflow. Return to the main portal UI. Click "Preferences" in the upper right to enter preferences mode. Create a new tab and on that tab subscribe to the new channel. The example was named "Users".

Here's what it might look like:

How to extract value from this channel

This channel is cute. Cute is fun for developers but doesn't create value.

Implementation notes

This channel is relatively simple and small. It extends CAbstractXslt, relying on that superclass to provide XML / XSLT rendering services. It takes all configuration as ChannelStaticData parameters, so its configuration is mediated by a CPD and it is immune to end-user-request-parameter-ChannelStaticData-parameter-injection-attacks (though a portal administrator can deliberately enable end-user-configuration by making parameters in the CPD end-user-modifiable).

SQL handling is simplified by use of Spring JDBC templates. It took some messing around to realize that a RowCallbackHandler would work in this case, since even though the ultimate Document is just one object, each ResultSet row is represented by an object, the corresponding XML element.

I have some passion around using this channel to demonstrate a particular approach to error handling. I think it should throw some specific exceptions and CError should, via one or more ErrorsToXml plugins, handle them. Thereby keeping the noise of error handling code out of this channel. Of course, that noise is justified if it adds enough value, but I suspect that at least first pass CError can do just as well.

Thoughts about where to go next, and where not to go

The beauty and power of this channel is in its simplicity. There is an elegance in maximizing the amount of work not done. The channel renders only one query, so the built-in portal permissions mechanism around channel subscription provides sufficient access control. This channel itself provides no edit UI, because the Channel Publishing Document configuration mechanism is sufficient to its needs. I would suggest strongly that as this channel is considered and refactored and enhanced - and it certainly should be both refactored and enhanced - any given enhancement be weighed carefully against its complexity cost. Problems that are amenable to additional complexity for additional features have other routes into the portal, such as by exposing XML that is rendered by CGenericXSLT.

Ideas for improvements to this channel include:

• The default XSLT should display the column headers.  DONE 10/10/06
• The CPD workflow should solicit a caption String, the channel should include it in the XML, and the default XSLT should display that caption if present, giving users context about what it is they're seeing. Statistics with no context aren't as helpful as statistics with context.
• The default XSLT should result in more yummy channel markup.
• The channel should remember (a hash on) the results from the last time it was rendered, and highlight itself (yellow is a common web idiom for this) when its newly rendered contents differ.
• The channel should be able to asynchronously (AJAX) refresh and highlight changes to its data.
• The channel should be a JSR-168 portlet so as to adopt Spring PortletMVC and be useful in other portals.
• There should be more and better documentation around this channel.
• The channel should have an About mode that briefly and in a classy way indicates where this channel came from and how you might go about engaging with the uPortal community to create more channels like it, whether by directly engaging in the opensource on developing cross-instituionally reusable components or engaging a professional services organization to do so on your behalf.
• This channel should provide better and more specific error handling. This might be accomplished by creating a couple generic exceptions around such error conditions as a missing required Channel Static Data parameter and plugging in a new ErrorToXml into the Error Channel to specifically handle these exceptions and some new markup generation in CError XSLT to provide an appropriate problem-specific UI. That is, the best way to add error handling to this channel may be to not add error handling to this channel and instead rely on the framework error handling facility.
• Implement ICacheable for caching performance
• Add some further XSLT to transform the XML data into SVG and you could have it produce graphs, e.g..

  Need example making SQL query end-user-modifiable

  This channel needs another publication in uPortal, where the SQL parameter is flagged "user-can-modify", so that the users able to subscribe to the channel can customize subscriptions of it with their very own SQL queries. It would be very important to restrict such a channel to be only available to administrators, but so restricted, this would empower these persons to configure their own "digital dashboards" of interesting queries withoutn each query having to be published via the Channel Manager.

  This approach most obviously works for Portal Administrators in the context of the PortalDb datasource, but conceivably other datasources could be exposed to potentially other groups.