On almost every topic
jQuery DataTables, CMIS and Alfresco

This is a follow up to the post jQuery DataTables and Alfresco. In this tutorial we replace the OpenSearch service with a CMIS back-end service to retrieve content items from an Alfresco repository and populate a jQuery DataTable

Download the sample code

You can download the sample code here. The example code also provides an Alfresco JavaScript that can be used to create some content items. To do this you need to add the script to the Scripts space in the Data Dictionary and run the script using a Run Action.

About CMIS

CMIS (Content Management Interoperability Services) is SQL for content repositories. It allows access to different content management systems using an open standard maintained by OASIS. OASIS is an important consortium that develops and promotes open standards.

CMIS enables developers to create, update or delete documents or folders and to search for documents or folders using a vendor independent language. It is a very promising standard, especially since all major vendors of Enterprise Content Management software adopted the standard including Microsoft, Oracle, EMC Corporation (Documentum), IBM, SAP, Alfresco and Open Text. Alfresco was the first to provide full support for the standard.

For these examples we will use the Restful Atompub binding that uses XML as a data format. There is also an OASIS Subcommittee working on a browser binding to make it even easier to use CMIS in browser based applications. This binding will use JSON as a data format in stead of XML.

Prerequisites

This tutorial is a continuation of jQuery DataTables and Alfresco.

It might help to familiarize yourself with CMIS. For developers I can recommend the excellent CMIS introduction by Jeff Potts.

Create the file

The first step is to set up a basic HTML file for the client-side table. We will start with a simple table listing the name of the documents returned by the CMIS service. Create a file cmissearch.html in the ROOT folder that we used in the previous tutorial about DataTables and Alfresco:

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>jQuery DataTables and Alfresco CMIS Example</title>
    <style type="text/css" title="currentStyle">
      @import "media/css/demo_page.css";
      @import "media/css/demo_table.css";
    </style>
    <script type="text/javascript" src="media/js/jquery.js"></script>
    <script type="text/javascript" src="media/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="media/js/jquery.setFilteringDelay.js"></script>	
    <script type="text/javascript" src="media/js/jquery.base64.js"></script>
    <script type="text/javascript">
      <!-- here we will write our client-side JavaScript -->
    </script>
  </head>
  <body id="dt_example">
    <div id="container">
      <h1>jQuery DataTables and Alfresco CMIS Example</h1>
      <div id="dynamic">
        <table class="display" id="example">
	  <thead>
	    <tr>
	      <th width="30%">Name</th>
	    </tr>
	  </thead>
	  <tbody>
            <tr>
	      <td colspan="5" class="dataTables_empty">Loading data from server</td>
	    </tr>
	  </tbody>
	</table>
      </div>
    </div>
  </body>
</html>

As you might have noticed I added a new jQuery plug-in called jquery.base64.js. We need this plug-in to encode the username and password that we need to authenticate against the CMIS service. You can find it here.

Add the template

The next step is to add a template for the CMIS request. This is a bit of a hack, but it works. This approach was inspired by this post by developer Ben Nadel. Add the following script tag to the header of the HTML file:

<script id="cmis-template" type="application/cmis-template">
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <cmis:query xmlns:cmis="http://docs.oasis-open.org/ns/cmis/core/200908/">
    <cmis:statement>SELECT cmis:name 
	  FROM cmis:document 
	  WHERE CONTAINS('${sSearch}') 
	  ORDER BY cmis:name</cmis:statement>
    <cmis:maxItems>${iDisplayLength}</cmis:maxItems>
    <cmis:skipCount>${iDisplayStart}</cmis:skipCount>
  </cmis:query>
</script>

I am going to use this template to prepare my requests to the server. It contains a query request that searches for text in content items and orders the result by name.

Initialize the DataTable

We will now setup the basic implementation of the client-side JavaScript. This part is only slightly different from the approach in the previous post about DataTables:

function fnGetJSONData( sSource, aoData, fnCallback ) {
   /* this function will execute the keyword search */
}
	
$(document).ready(function() {
  $('#example').dataTable( {
    "bServerSide": true,
    "sAjaxSource": "/alfresco/s/cmis/queries",
    "bSort": false,
    "bPaginate": true,
    "sPaginationType": "full_numbers",
    "fnInitComplete": function(){this.fnSetFilteringDelay(500)},
    "fnServerData": fnGetJSONData
  } );
} );

Implement the callback function

The final part is the implementation of the callback function. Here is the code:

function fnGetJSONData( sSource, aoData, fnCallback ) {

  var params = {};
  for ( var i=0 ; i var cmisTemplate = $( "#cmis-template" );
			
    var requestBody = cmisTemplate.html().replace('${sSearch}', params["sSearch"])
      .replace('${iDisplayStart}', params["iDisplayStart"])
      .replace('${iDisplayLength}', params["iDisplayLength"]);
			
    $.ajax( {
      "dataType": 'xml',
      "type": "POST", 
      "url": sSource, 
      "contentType" : "application/cmisquery+xml",
      "beforeSend" : function(req) {
        req.setRequestHeader("Authorization", "Basic " + $.base64Encode("admin:admin"));
      },
      "processData" : false,
      "data" : $.trim( requestBody ),
      "success": function (data,textStatus,xmlHttpRequest) {
				
        var jData = $( data );	

        var json = {"sEcho": params["sEcho"],"aaData" : []};
        json.iTotalRecords = jData.find("[nodeName='opensearch:totalResults']").text();
        json.iTotalDisplayRecords = json.iTotalRecords;
					
        var items = jData.find("entry").each(function(){
          json.aaData.push([
            $(this).find("title").text(),
          ]);
        });
           
        fnCallback(json);
      },
      error: function(){
        console.log( "ERROR", arguments );
      }
    } );					
  }
}

Even this part is not that different from what we did in the previous post. I have highlighted the major differences. 

The first step is to prepare the request body. In order to execute a CMIS query request, I created the template with id cmis-template. It contains a couple of interpolations. By using a global replace we can fill the template with our search properties:

var requestBody = cmisTemplate.html().replace('${sSearch}', params["sSearch"])
  .replace('${iDisplayStart}', params["iDisplayStart"])
  .replace('${iDisplayLength}', params["iDisplayLength"]);

The next step is the call to the backend. There are a couple of differences from the function used for the Open Search client. We need to post our data to the back-end server with a specific content type (application/cmisquery+xml) for CMIS and the service requires authentication. For now we will simply add a request header with a basic authentication:

"beforeSend" : function(req) {
  req.setRequestHeader("Authorization", "Basic " + $.base64Encode("admin:admin"));
},

When posting data we need to add the request to the request body. By setting the processData parameter to false, we can prevent the request method to process our request data. Otherwise it will try to convert our request body into request parameters.

I also applied the trim function to the request body to remove leading and trailing whitespace:

"data" : $.trim( requestBody ),

This is all we need to do in order to execute the request to the server.

In order to populate the table, we need to convert the XML response into a JSON array. This part is quite similar to the OpenSearch examples of the previous post about DataTables. We add the skipcount and the total number of records returned by the search and then iterate over the entries of the Atom feed to add a title for each content item retrieved by the query request:

var json = {"sEcho": params["sEcho"],"aaData" : []};
json.iTotalRecords = jData.find("[nodeName='opensearch:totalResults']").text();
json.iTotalDisplayRecords = json.iTotalRecords;
					
var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("title").text(),
  ]);
});

Now if we test this client page the result should be similar to this:

CMIS DataTables

Add columns

Once this is done we can easily extend our table to provide additional information. To add the name, the author and the modified date, for example, you only need to add the parameters to the JSON array:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("*[propertyDefinitionId='cmis:name']").find("[nodeName='cmis:value']").text(),
    $(this).find("title").text(),
    $(this).find("author").text(),
    $(this).find("updated").text()
  ]);
});

And the columns to the table:

<thead>
  <tr>
    <th width="30%">Name</th>
    <th width="30%">Title</th>
    <th width="20%">Author</th>
    <th width="20%">Updated</th>
  </tr>
</thead>

A tool like Firebug can be helpful here to analyze the response from the server to figure out how to retrieve the values from the XML returned by the CMIS service.

When you reload the page and execute a search you will now see a page similar to this:

CMIS DataTables

Retrieving aspects

This table does not display the values for the title and author that we entered as author and title in Alfresco. They are the name and created properties in stead. In order to show the desired author and title, we need to extend our query to retrieve the author and title that are part of the so called cm:titled and cm:author aspects.

Aspects are an important content modeling concept in Alfresco. They provide a more dynamic way to add metadata properties to documents and folders. For Alfresco developers it is a best practice to use aspects to create a content model. Even when you need content types it is recommended to create aspects for logical chunks of metadata and add them to a type definition as mandatory aspects. Aspects provide much more flexibility and make your content model easier to maintain. Aspects however are not recognized by CMIS as a concept, although that might change in a future version.

To add the properties from the aspects that contain the author and title information we need to add two joins to our query:

SELECT d.*, t.*, a.* 
  FROM cmis:document AS d 
  JOIN cm:titled AS t ON d.cmis:objectId = t.cmis:objectId 
  JOIN cm:author AS a ON d.cmis:objectId = a.cmis:objectId 
  WHERE CONTAINS(d,'${sSearch}') 
  ORDER BY d.cmis:name

Note: when you add aspects to your query using JOIN the search will only return folders or documents that have these aspects applied.

Additionally you can use IN_FOLDER() to match the immediate children of a folder or IN_TREE to match any object beneath the folder:

SELECT d.*, t.*, a.*
  FROM cmis:document AS d 
  JOIN cm:titled AS t ON d.cmis:objectId = t.cmis:objectId 
  JOIN cm:author AS a ON d.cmis:objectId = a.cmis:objectId 
  WHERE IN_FOLDER(d, 'workspace://SpacesStore/40312a4b-7767-4586-a58b-18d050ffe0ca')
  AND CONTAINS(d,'${sSearch}')
  ORDER BY d.cmis:name

To match docments that follow the file name pattern *.txt you can add a LIKE predicate to the WHERE clause for the cmis:name property:

SELECT d.*, t.*, a.*
  FROM cmis:document AS d 
  JOIN cm:titled AS t ON d.cmis:objectId = t.cmis:objectId 
  JOIN cm:author AS a ON d.cmis:objectId = a.cmis:objectId 
  WHERE IN_FOLDER(d, 'workspace://SpacesStore/40312a4b-7767-4586-a58b-18d050ffe0ca') 
  AND CONTAINS(d,'${sSearch}')
  AND d.cmis:name LIKE '%.txt'
  ORDER BY d.cmis:name

Once we added the aspects, we can retrieve the actual values for the author and the title using the following code:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("*[propertyDefinitionId='cmis:name']").find("[nodeName='cmis:value']").text(),
    $(this).find("*[propertyDefinitionId='cm:title']").find("[nodeName='cmis:value']").text(),
    $(this).find("*[propertyDefinitionId='cm:author']").find("[nodeName='cmis:value']").text(),
    $(this).find("updated").text()
  ]);
});

When we reload this page we will see the actual author and title properties:

CMIS Search 3

Add some formatting

Finally we will add some formatting to our table by adding an icon to the name column and by formatting the date. In order to format the date I added a custom jQuery UI library with only the core component and the jQuery UI Datepicker. The datepicker contains a date parser and formatter that is able to process a date in ISO8601 format as used by the Atom publishing protocol. Add the jQuery UI library to the header of the page:

<script type="text/javascript" src="media/js/jquery-ui-1.8.11.custom.min.js"></script>

I added the reference to the icon to the JSON array:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("[nodeName='alf:icon']").text(),
    $(this).find("*[propertyDefinitionId='cmis:name']").find("[nodeName='cmis:value']").text(),
    $(this).find("*[propertyDefinitionId='cm:title']").find("[nodeName='cmis:value']").text(),
    $(this).find("*[propertyDefinitionId='cm:author']").find("[nodeName='cmis:value']").text(),
    $(this).find("updated").text()
  ]);
});

And the column for the icon to the table:

<thead>
  <tr>
    <th width="0%">Icon</th>
    <th width="30%">Name</th>
    <th width="30%">Title</th>
    <th width="20%">Author</th>
    <th width="20%">Updated</th>
  </tr>
</thead>

And finally I added the column rendering to the table initialization code after the sPaginationType parameter:

"aoColumns": [
  {"bVisible": false},
  { "fnRender": function ( oObj ) {
    return '<img src="' + oObj.aData[0] + '" alt="Icon" /> ' + oObj.aData[1];
  } },
  null,
  null,
  { "fnRender": function ( oObj ) {
    var updated = $.datepicker.parseDate($.datepicker.ATOM, oObj.aData[4]);
    return $.datepicker.formatDate('yy-mm-dd', updated);
  }}
],

Now if you reload the page and execute a query the result should look similar to this page:

CMIS Search 4

Conclusion

In this post I returned to the jQuery DataTables to show how you can use the CMIS standard to populate a DataTable using a CMIS service request. I used Alfresco for this example, but this approach should also work for other systems that support CMIS (except for the joins to include the aspects). Once the CMIS browser binding is finalized, it will be even easier to develop CMIS clients using a framework like jQuery or YUI since it will replace the XML data format with JSON.

Read the previous tutorial

Read the first post jQuery DataTables and Alfresco. It covers jQuery DataTables with Alfresco’s OpenSearch keyword search as a back-end service. Read the third post in this series here. It covers jQuery DataTables and Spring web Scripts.

Update History

  • March 22, 2011: added formatting section and reference to previous tutorial
  • March 23, 2011: added note about aspects and some additional query examples
  • March 27, 2011: added a reference to the third tutorial in this series
  1. psdtohtmlshop reblogged this from bpeters
  2. la-mutuelle-sante reblogged this from bpeters
  3. net-mutuelle-sante reblogged this from bpeters
  4. pneu-moins-cher reblogged this from bpeters
  5. maillot-de-football-pas-cher reblogged this from bpeters
  6. ac-voyage reblogged this from bpeters
  7. achat-de-pneu-pas-cher reblogged this from bpeters
  8. lewesde reblogged this from bpeters
  9. xmyfuckingmurderx reblogged this from bpeters
  10. bpeters posted this