JCR-SQL2 optimize queries samples

Queries run on Magnolia 5.x

1. Search for specific Item type 

i.e. [mgnl:page] instead of using the default one [nt:base]
Example: Search for pages with stkNews template

select * from [nt:base] as t where isdescendantnode(t, '/demo-project') and  t.[mgnl:template]='standard-templating-kit:pages/stkNews'

This query takes 18ms to return.

select * from [mgnl:page] as t where isdescendantnode(t, '/demo-project') and  t.[mgnl:template]='standard-templating-kit:pages/stkNews'

This query takes 3ms to run.

2. Avoid the use of isdescendantnode if not necessary.

Example: Searching in data module, two different types of data with same property

SELECT * from [nt:base] AS t WHERE  ((ISDESCENDANTNODE(t, '/folder1type1')   AND [jcr:primaryType] = 'type1') or (ISDESCENDANTNODE(t, '/folder2type2')   AND [jcr:primaryType] = 'type2')) and t.categories = 'c6f40746-5be4-44c5-9eb1-0f5e59133350'

This query takes more than 6000ms to run

SELECT * from [nt:base] AS t WHERE   [jcr:primaryType] = 'type1' or [jcr:primaryType] = 'type2' and t.categories = 'c6f40746-5be4-44c5-9eb1-0f5e59133350'

This query takes 13ms to run, in this case we are looking for type1 that is just under folder1type1 and type2 that is just in folder2type2, so the isdescendantnode is redundant.

3. Performance

In order to check the queries performance and see if you need to optimize them, see previous post on http://tmiyar.blogspot.com.es/2012/06/queries-logging.html

4. query samples

* get latest modified pages in demo-project, here you need to know that any time you modify any component in the page, that page gets its modification date updated. That is why we only need to ask for mgnl:page
select * from [mgnl:page] as t where isdescendantnode(t, '/demo-project') order by t.[mgnl:lastModified]
the way to limit the result set is by code query.setLimit(3); before calling query.execute();

* find where a control is used in the configuration
select * from [mgnl:contentNode] as t where t.class like '%SelectFieldDefinition'
In this case you can decide to get the result type mgnl:content, you can do it in the jcr query tool.

* for queries that rely on a path with wildcards there is no other way that to use isdescendantnode and inschildnode joins that makes queries a bit more complex, in that cases I would recommend to use xpath as is not deprecated, it is faster and I dont think they will remove it.


Note: Nodes get cached by Jackrabbit, second time you run the query in our tool will be faster, keep that in mind when doing your tests.

How to see the status of timed activations

This article is for Magnolia 5.x versions.

The Need

It happens that when you decide to send a page for publication, you don't know what is going on until you see the green status dot, things could happen like you may forget you already sent that page for publication and you send it again.

In order to keep track at all times of the tasks in progress there is a little development you could implement, and I will show you how.

The aim is to add a new column to the pages app that will show the information like in the image below.

As you can see there is a new column named "Publication Date", in that column we will see either the date of publication, or if is an immediate publication that someone is reviewing we will see "InProgress". We could add the other status like "rejected" but I will just display this two.

The Code

There is only one class that we need to create, that will extend from AbstractColumnFormatter, we will query the "tasks" workspace and display the status. Right now there is API method to get a task directly from the identifier of the node, that is why you will see 2 queries instead of one.

The Configuration

Once you have the class in your project the only thing left is to add it to the treeView of the pages app

The last thing to do is to add read permissions on tasks workspace to the users/groups you want to be able to use this functionality.

The code is available here.

To Improve

Right now it works just for the website workspace, it can be improved to read the workspace name in the configuration. 

It could be good the show if the activation is recursive.

In case there are more than one activation scheduled for one item it should be displayed.

Supper cool addition would be a button to stop an scheduled activation. 

Magnolia Blossom Module, STK Module or both?

First a small introduction, quoting from our documentation pages:

Standard Templating Kit (STK)

"The Standard Templating Kit is a production-ready website framework that provides best practices and templates for common use cases. It includes ready-made functionality that can be extended for custom designs and content output. The STK adheres to best practices in CSS, semantic web and HTML, including HTML 5 video and forms support. It also provides a mobile variation which makes your STK-based sites available to mobile devices."

Blossom Module

"The Blossom module is the Spring integration for Magnolia CMS. Blossom enables you to create editing components that display a high level of dynamic behavior. These components can be used by editors to create truly interactive web pages. For example, the Spring Framework application stack makes it easier to develop integrations with business systems, so that they fetch the information that you want to present in your pages. As Blossom is built on the Spring Web MVC, familiarity with this framework will ensure a smooth experience working with the module."

I could be using all the templates, components and functionality provided by STK and create new ones within a Blossom module.

Let's see how:

We need to create our project, I always go for creating a webapp project with our maven archetype. Once we have the webapp project we use the archetype to create a blossom module.
In order to make all the STK functionalities available in our Blossom module we need to modify the file   blossom-servlet.xml that has been generated by the maven archetype in the resources folder of our new Blossom module and add the STK renderer context attributes as shown below.

This configuration will allow us to access to the SKT functionality from our template scripts.
Now, how we access to that functionality from the templates model/controller?
We can access to them using one of our API methods, see:

This would be the easiest way to access to STK functionality as all the components loaded by Guice are already available.

See how you reference a Blossom component within a STK template

See how this component is defined within the Blossom custom module:

JCR search text ignoring umlauts, accents

Problem.

Default Magnolia installation demo-project site, comes with a search box on the top right corner

If you try to search for something like résumé you would expect to get:

But instead you get:

Solution.

1. Configure tomcat encoding.

In order to be able to look for text with special characters, first thing you need to do is configure tomcat encoding. The default Tomcat encoding is ISO-8859-1, this encoding does not support some special characters, that means when rendering the text the accents, umlauts won't be displayed.

This link explains how to add the UTF-8 encoding into your Tomcat configuration:
http://struts.apache.org/release/2.0.x/docs/how-to-support-utf-8-uriencoding-with-tomcat.html

2. Create a new analyzer (optional).

You would probably like that when people makes a spelling mistake by missing the accents for example, that they get the results nevertheless, search for "camion" should return results with either "camion" or "camión".

Have a look at the following link, it explains step by step how to do it: http://docs.jboss.org/exojcr/1.12.13-GA/developer/en-US/html/ch-jcr-query-usecases.html#JCR.IgnoreAccentSymbols

Importing tags with Groovy

In this post I use a Groovy script to import custom tags into Magnolia CMS. The script saves the tags in the Data module. You can use them to categorize pages, images and documents.

Clients often ask me how to import an existing collection of tags into Magnolia CMS. Local governments use tags such as taxes, transportation or schools to categorize their content. Travel websites use geographical terms like london, paris and bangkok to tag places to visit. Such vocabularies grow large over time. Creating them from scratch is a lot of work.

I wrote a Groovy script that imports tags from XML. The example below finds any Flickr tags that are related to the given tag london. You can customize the script to import your own tags. I am not sure why I had not used Groovy much before. Once I tried it I loved it! You will too when you see how simple it is to implement a tag importer.

Requirements

• Magnolia CMS 4.5.7 with Groovy module. It's in the add-ons folder in the bundle.
• Flickr API key. Register for an API key in order to call the Flickr API.

Creating a Groovy script

1. In Magnolia CMS, go to Tools > Scripts and add a new Groovy script.
2. Paste the following code into your script.
3. Check the Is a script? box.

tag = 'london'
hm = MgnlContext.getHierarchyManager('data')

// Create a folder in the Data module
flickrTags = ContentUtil.createPath(hm, "/categorization/${tag}", new ItemType("dataFolder"))
hm.save()
itemType = new ItemType('category')

// Connect to Flickr REST API
URL url = new URL("http://api.flickr.com/services/rest/?method=flickr.tags.getRelated&api_key=5f26c50b6e67110809b117da0f2bb94f&tag=${tag}")

HttpURLConnection conn = (HttpURLConnection) url.openConnection()
    conn.setRequestMethod("GET")
    conn.setRequestProperty("Accept", "application/xml")

 if (conn.getResponseCode() != 200) {
   throw new RuntimeException("Failed : HTTP error code : "
   + conn.getResponseCode())
 }

// Parse the response XML 
rsp = new XmlSlurper().parse(conn.getInputStream())
rsp.tags.tag.each{
      content = ContentUtil.createPath(hm, "/categorization/${tag}/${it.text()}", itemType)
      content.name = it.text()
}

flickrTags.save()
conn.disconnect()
return "done"

Here's what the script does:

1. Create a new folder in the Data module with the parent tag name.
2. Connect to the Flickr REST API and request all tags related to the parent tag.
3. Parse the resulting XML.
4. Create categories under the previously created data folder.

Now the tags are available for categorizing content.

Bear in mind is that there is no error handling in this code to keep it short. This solution has been developed and tested with Magnolia 4.5.7

Localized 404 errors

Have you ever needed custom 404 error pages for your Magnolia CMS website? Such a requirement is quite straightforward to implement if the site uses only one language. It gets more complicated when multiple languages are used, each with its own domain such as mysite.de and mysite.fr. In such a case host based virtual URI mapping is useful as explained in How to set up a custom 404 handler.

But what if the languages are not host based? What if you don't have .de or .fr domains? The language of the page can be built into the URL such as mysite.com/de/page.html or mysite.com/fr/page.html. In this post I show how to direct the request to a custom 404 error page using a custom filter.

Tip! I started by investigating if I could configure the exceptions in the web.xml file. I thought the exception-type tag might be the answer. Turns out it doesn't work within the Magnolia CMS filter chain. In case you had the same idea I will save you the trouble of going that route.

The solution is to write a custom filter to handle HTTP errors. The custom filter will extend the default AggregatorFilter. Replace the default class with your custom class in the CMS filter chain in Configuration > /server/filters/cms/aggregator.

The class code should look like this:

public class CustomAggregatorFilter extends AggregatorFilter {
 private static final Logger log = LoggerFactory.getLogger(CustomAggregatorFilter.class);

 @Override
    public void doFilter(HttpServletRequest request, HttpServletResponse response, FilterChain chain) throws IOException, ServletException{

        boolean success;
        try {
            success = collect();
        }
        catch (AccessDeniedException e) {
            // don't throw further, simply return error and break filter chain
            log.debug(e.getMessage(), e);
            if (!response.isCommitted()) {
                response.setStatus(HttpServletResponse.SC_FORBIDDEN);
            }
            // stop the chain
            return;
        }
        catch (RepositoryException e) {
            log.error(e.getMessage(), e);
            throw new ServletException(e.getMessage(), e);
        }
        if (!success) {
            log.debug("Resource not found, redirecting request for [{}] to 404 URI", request.getRequestURI());

            if (!response.isCommitted()) {
             if(MgnlContext.getAggregationState().getLocale().getLanguage().equals("en")){
              RequestDispatchUtil.dispatch("permanent:/en.html", request, response);
             } else {
              RequestDispatchUtil.dispatch("permanent:/de.html", request, response);
             }
            }
            else {
                log.info("Unable to redirect to 404 page, response is already committed. URI was {}", request.getRequestURI());
            }
            // stop the chain
            return;
        }
        chain.doFilter(request, response);
    } 
}

Note that the filter only reacts to 404 errors. It checks the language from the aggregation state. This is the same language as used in the URL, such as mysite.com/de/page.html. Based on the language the filter makes a permanent redirect to the German error page that is part of the website. On the error page you can display whatever content you like, send parameters, log statements and so on.

The filter creates a permanent (301) link. This is the SEO friendly way. Search engines will index the pages while you can still keep track of the errors by setting them in the response the way you decide.

This is just a demonstration. It could be improved by adding the errors and URLs in the filter configuration, for example.

Want to learn more? Register now for Magnolia Conference 2012!

How To Export Website Content To Excel

Here is the thing, I have a big report on my site that grew quite big, now is not easy to review it without scrolling up and down.

Well, how about exporting just that content to an Excel file where we can get a graphic of the data, or maybe sort it by specific column or do complex calculations? Here is a very fast way to produce such file.

First we need to give a distinct id to our table, and any other data we will need to produce the output we desire, i.e. title of the report, on our template file:

<h1 id="toExcelReportTitle">Sample Report Title</h1> <table id="toExcelTable"</table> ....

Then we will need a button that when on clicked it will send a request to a servlet that will do the export.

The JQuery is used to load the HTML of the report table and the data of the report title into the parameters in the form. This way when we click on the save button, it will send this parameters to the servlet we have previously created.

The servlet will the read this parameters and write them to the response. This response will automatically be rendered into Excel form by setting the Content Type to application/vnd.ms-excel.



And this is a sample of the resulting excel file:





By exporting the content this way, we don't need to request the table page again, we will just get the already rendered data and sent it to the servlet that will send the data back as an excel file.