Raised Eyebrow Blog

I recently attended the Vancouver Drupal 7 Code Sprint/Workshop to try to help out with tackling some of the critical issues left on the Drupal 7 issue queue. The sprint was organized by Audrey Foo and Chris Ng and hosted by FCV at their offices in downtown Vancouver. There were of course two Drupal stars in attendance, Angie ‘webchick’ Byron and Károly ‘chx’ Négyesi. Both of whom have probably more experience between them than all of the Drupal people I know combined.

The purpose of the sprint was to try to teach some folk, like me, how to create and review a patch on the issue queue, in an effort to move things along with the release of Drupal7. I helped out a little by putting together a quick Google Docs presentation on how to install Drush. Drush is a handy command line tool for working with Drupal. One of it’s most popular features is the ability to download and enable core and modules very quickly.

Following that Yi Yang from FCV, with the help of Angie, walked us through the process of creating a patch and then reviewing a patch. I thought I’d post my notes from the session to help others out. I was totally confused about the process prior to Angie being a star and providing a simple list of steps to complete.

CREATING A PATCH

1. Download most recent version of Drupal 7: drush dl drupal-7.x --package-handler=cvs
2. hack core to fix the issue!
3. Go to your Drupal Root directory
4. Make the patch: cvs diff -up modules /path/to/file (optional) > 12345.patch (Compare my local version to CVS version)
5. Upload it to the issue queue
6. Reset your codebase: cvs up -dPC
7. Goto 2

REVIEWING A PATCH

1. Download most recent version of Drupal 7: drush dl drupal-7.x --package-handler=cvs
2. Optional: Download dreditor greasemonkey script
3. Find a patch
4. Download patch to your Drupal root directory
5. Apply the patch: patch -p0 < 12345.patch ('offset" is ok. "fuzz" is ok. FAILED is bad)
6. Test, test, test!
7. Review the code!
8. Post a comment, change issue status accordingly
9. Reset your Drupal code: cvs up -dPC
10. Goto 2

Big thanks to Audrey, Chris for the space and food and to Angie and Károly for their brains :)

Photos

This is the second part of Drupal Case Study on integrating the CKAN data repository with Drupal 6. Part 1 covered the following:

• What is CKAN?
• CKAN’s API
• The Foundation
• The Build
• Theming
• Homepage Chart

Caching

API calls are expensive. There’s no doubt about that. Particularly when you’re returning large amounts of data. To avoid any issues of the CKAN API being exhausted from requests and to ensure that the site remained responsive, I decided to leverage Drupals caching mechanisms and pretty much cached everything I could, within reason. The Chart, Tag Cloud, Tag lists, Ministry lists, All Packages list and all individual packages are cached. The issue with caching on this site is that if a package gets updated on the CKAN instance, we need to know about that on our Drupal site immediately and then clear the appropriate caches so that the most recent data can be retrieved.

For caching I created a table called ‘cache_ckan’, that stores everything I need. To create this table I used the schema of the existing cache table and put that in my .install file in my module directory.

/**
 * Implementation of hook_install().
 */
function ckan_install() {
  drupal_install_schema('ckan');
}
 
/**
 * Implementation of hook_uninstall().
 */
function ckan_uninstall() {
  drupal_uninstall_schema('ckan');
}
 
/**
 * Implementation of hook_schema().
 */
function ckan_schema() {
  $schema = array();
  $schema['cache_ckan'] = drupal_get_schema_unprocessed('system', 'cache');
  return $schema;
}

Whenever this module is enabled this schema will be run and the table will be created.

What is stored in the ckan_cache table?

There are various items stored in the cache table.

1. The Homepage chart data
2. Tag lists
3. Ministry lists
4. List of all datasets

Let’s take the list of all packages as an example. I covered how I implemented the paging in my previous post. As this list is paginated it’s important that every page be cached to improve the speed of the site. As the paging mechanism is already implemented it’s just a case of creating a cache table entry (ckan:all{page-number}) for each page, and then checking for it’s existence when loading the page.

if(($cache = cache_get('ckan:all'.$page, 'cache_ckan')) && !empty($cache->data)) { // If cached data exists for this page...
	$results = $cache->data;
} else {
	$ckan = ckan_ckan();
 
	$start = 0;
	$items_per_page = variable_get('ckan_items_per_page', 4);
	if($page) {
		// If we're in a page, we need to set where to start the list
		$start = $page * $items_per_page;
	}
 
	// Set the offset to the number of records in
	$offset = $start;
	// Limit to the number of items per page 
	$limit = $items_per_page;
 
	try {
		$results = $ckan->advancedSearch(array('groups' => 'canadagov', 'all_fields' => '1', 'offset' => $offset, 'limit' => $limit));
	} catch (Exception $e){
		return $e->getMessage();
	}
 
	// If the API call worked
	watchdog('ckan', 'Called CKAN API for list of all packages');
    	cache_set('ckan:all'.$page, $results, 'cache_ckan');
}

This method is very simple and very effective. It means the pages load lightning fast and only one page of data at a time is retrieved.

How does the cache get cleared/updated

Datasets/Packages change all the time on the CKAN instance, so how do you make sure that the Drupal site has the most current data. This module has two ways of managing that.

1. Using hook_form to redirect to CKAN

As the CKAN nodes on Drupal are created on the fly and hold very little information, there is really no need to access the EDIT form for these nodes. Whenever an admin user clicks the edit tab on the node, they are automatically redirected to the appropriate CKAN package editing screen. hook_form is called to retrieve the form that is displayed when one attempts to “create/edit” an item. For CKAN content types, the user is redirect to the CKAN instance.

/**
 * Implementation of hook_form
 *
 * Redirect the user to ca.ckan.net package edit screen on edit
 */
function ckan_form(&$node, $form_state) {
  if($node->type == 'ckan') {
  	drupal_goto('http://ca.ckan.net/package/edit/'.$node->body);
  }
}

When the CKAN form is submitted, CKAN then redirects back to the Drupal site and calls a specific URL that tells Drupal to call CKAN again to get the package information and populate the node. To clarify, the process is

1. Redirect http://www.datadotgc.ca/node/X/edit to http://ca.ckan.net/package/edit/{name of X}
2. On save of CKAN Package, redirect to http://www.datadotgc.ca/{special_url}/{name_of_X}
3. Load the node with {name_of_X}
4. Call CKAN to get the (updated) data for Package {name_of_X}
5. Save the node with updated data

Using Cron and an Atom Feed

CKAN provides an Atom feed of recent updates to the Packages. Cron checks this feed every time it runs. If the feed has changed since the last cron run, then we know there have been updates and we clear all of the caches.

/**
 * Implementation of hook_cron()
 *
 **/
function ckan_cron() {
	// Get the md5sum of the current atom feed
	$current_feed = trim(md5_file('http://ca.ckan.net/revision/list?format=atom'));
	watchdog('ckan', 'Current feed md5: '. $current_feed);
	// Retrieve the previously stored md5sum
	$previous_feed = variable_get('ckan_atom_feed_md5', $current_feed);
	watchdog('ckan', 'Previous feed md5: '.$previous_feed);
 
	// If there have been changes
	if($current_feed != $previous_feed) {
		watchdog('ckan', 'ATOM feed has updated, clearing caches and deleting nodes');
		// Flush all the caches
		cache_clear_all('*', 'cache_ckan', TRUE);
  	        // Set the previous feed md5
		variable_set('ckan_atom_feed_md5', $current_feed);
	}
}

Tag cloud creation

I borrowed some code from the Tagadelic module to achieve the tag cloud

/**
 * Build a tag cloud based on the settings provided
 *
 * @return	String	A themed list of weighted tags
 */
function ckan_tag_cloud() {
	// If there is cached data
	if(($cache = cache_get('ckan:tags', 'cache_ckan')) && !empty($cache->data)) {
		$results = unserialize($cache->data);	
	} else {
		$ckan = ckan_ckan();
		$results = $ckan->getTagCount();
		watchdog('ckan', 'Called CKAN API for tag cloud');
		cache_set('ckan:tags', serialize($results), 'cache_ckan');
	}
 
	// Let's sort them by weight first off
	foreach ($results as $key => $row) {
    $tag[$key]  = $row[0];
    $weight[$key] = $row[1];
	}
	array_multisort($weight, SORT_DESC, $results);
 
	// Now let's get the top X number of tags
	$results = array_slice($results, 0, variable_get('ckan_tagcloud_total', 40));
 
	// Now build the tags
	$tags = ckan_tag_build_weighted($results);
	// Sort them
	$tags = ckan_tag_sort($tags);
	// Theme them
	$output = theme('ckan_weighted_tags', $tags);
	return $output;
}
 
/**
 * Theme function that renders the HTML for the tags
 * @ingroup themable
 */
function theme_ckan_weighted_tags($tags) {
  $output = '';
  foreach ($tags as $tag) {
    $output .= l($tag['name'], 'data/tag/'.$tag['name'], array('attributes' => array('class' => "tagcloud level".$tag['weight'], 'rel' => 'tag'))) ." \n";
  }
  return $output;
}

Using the CKAN Search API for all lists

Ok, so what’s this all about? CKAN has some nice API calls like /api/rest/package/PACKAGE-REF that return a list of Packages. However these return the name/id of the Package ONLY. In our case, for our listings, we wanted other data, such as the tags attached to the Package as well as a brief description.

The only way to get this data was to do a search API call /api/search/package and pass some extra parameters, in this case all_fields=1 and department={name of Ministry}.

all_fields=1 tells the search to return all Package fields, not just the name/id; just as is if you called /api/rest/package/PACKAGE-REF.

department={name of Ministry} tells the search to return all packages that have a department of {name of Ministry}. The lovely folks at CKAN added this functionality for us on request.

What does this look like, well it’s pretty simple really. Call the advancedSearch() function. Pass it a few parameters and it returns you all the data you need. Here’s the function itself:

public function advancedSearch($parameters){
	foreach($parameters as $key => $value) {
		$querystring .= $key .'='. urlencode($value) .'&';
	}
	$results = $this->transfer('api/search/package?'. $querystring);
	if (!$results->count){
		throw new CkanException("Search Error");
	}
	return $results;	
}

And here is that function being called for the list of Ministry Packages. The offset and limit are for the paging mechanism:

// Call the function
$results = $ckan->advancedSearch(array('department' => $ministry, 'all_fields' => '1', 'offset' => $offset, 'limit' => $limit));

There’s a lot more functionality in this module, more than I can go through in a blog post, even 5 posts. If you’re trying to integrate Drupal with a CKAN instance and are not sure where to start then please leave a comment and I’ll get back in touch.

We recently launched http://www.datadotgc.ca, an open data collection portal for Canada, built to help poke the Canadian government in the right direction, towards something like similar sites in the UK (data.gov.uk) and the US (data.gov). Read David Eaves’ explanation of its purpose. For the benefit of the programming and Drupal community, I’m going to run through, with the aid of code samples, the development of the Drupal module to communicate with the CKAN API (which is where the data is stored). I’ll also walk through Theming, integration with Google Charts, Tag Clouds and most importantly, caching.

What is CKAN?

CKAN is a registry or catalogue system for datasets or other “knowledge” resources. CKAN aims to make it easy to find, share and reuse open content and data, especially in ways that are machine automatable.

CKAN is a nice big database that is built to accept user input of the type of data we’re trying to collect for datadotgc.ca. It has a slick front and back end that allows administrative access to the collected data.
You can find out more on their website.

CKAN’s API

In order to utilize the power of CKAN I needed to link it up to Drupal. CKAN has a powerful and flexible API that I used extensively in the module.

The Foundation

Early on in the project I got in touch with the wonderful team at CKAN and they then put me in touch with Sean Burlington from the data.gov.uk development team. They had also built their site in Drupal and Seán had lots of information on how they tweaked their CKAN site to work with Drupal. He worked hard to open source some of the work that they had done, and released it just in time for us to get started. Seáns module provided the basic API connectivity we needed to get started and was the foundation for our module.

The Build

How do you integrate Drupal with the CKAN API? Let’s start with the basics:

CKAN stores the individual datasets that you see on Datadotgc.ca as ‘Packages‘. It became clear that these ‘Packages’ could be directly mapped to the standard node architecture in Drupal. To achieve this I created a content type in the module that stored all the data I needed.

/**
 * Define module-provided node types.
 */
function ckan_node_info() {
  return array(
  'ckan' => array(
    'name'           => t('CKAN Package'),
    'module'         => 'ckan',
    'description'    => t('A package of Open Data.'),
    'has_title'      => TRUE,
    'title_label'    => t('Title'),
    'has_body'       => TRUE,
    'body_label'     => t('Package Description'),
    'min_word_count' => 0,
    'locked'         => TRUE
    )
  );
}
 
function ckan_create_node($ckan_data) {
  $node = array(
    'title'   => $ckan_data->title,
    'uid'     => 1,
    'body'    => $ckan_data->name,
    'promote' => 1,
    'path'    => 'dataset/' . $ckan_data->name,
    'type'    => 'ckan',
    'comment' => 2,
  );
}

As you can see from the code, the only data elements to be set when a node is created are Title, Body and Path. The body of the node is set to be the name of the CKAN package, which is in fact a simple string: geogratisnat_hydrography_v100.

The more complex CKAN data was not mapped to any CCK fields as you might think, but instead it is pulled from CKAN when the node is loaded. This simplifies the Drupal side of things by ensuring that we don’t have to keep track of any changes to the structure or contents of the dataset that may happen on the CKAN side.

Here is an example of some package data:

[maintainer] => Government of Canada, Natural Resources Canada, Centre for Topographic Information (Sherbrooke)
[name] => 1996_population_census_data_canada
[author] => Government of Canada, Natural Resources Canada, Canada Centre for Remote Sensing, GeoAccess Division, The Atlas of Canada
[url] => ftp://ftp.geogratis.gc.ca/atlas/Population_Ecumene_Census/1996/
[notes] => The parts of Canada making up the 1996 Settled Area, (or Population Ecumene), represents a selection of the 5984 Census Subdivisions (CSD) as defined by Statistics Canada for the 1996 Census. The selection process essentially removes those CSDs with very large areas and/or very low populations. Some of British Columbia's CSD boundaries have been further modified to better conform to the distinctive settlement patterns in the Cordilleran regions. The 1996 Settled Area is an attempt to balance the needs of national scale choropleth mapping with the spatial reality that the majority of Canada's land area contains very few people. The Settled Area represents more than 98% of the Canadian population captured in the 1996 Census of Canada.
[title] => 1996 Population (Ecumene) Census Data, Canada
[download_url] => ftp://ftp.geogratis.gc.ca/atlas/Population_Ecumene_Census/1996/1996.zip

When a node is loaded, the package data is pulled from CKAN and then cached for later use.

/**
 * Load node-type-specific information
 */
function ckan_load($node){
  $ckan = ckan_ckan();
  if(($cache = cache_get('ckan:'. $node->body, 'cache_ckan')) && !empty($cache->data)) {
    // Get the cached data
    $node->ckan = $cache->data;
  } else {
    try {
      // Call the API to get the package data
      $node->ckan = $ckan->getPackage($node->body);
    } catch (Exception $e){
      drupal_set_message($e->getMessage(), 'error');
    }
    // Cache this package data for later use
    cache_set('ckan:'. $node->body, $node->ckan, 'cache_ckan');	
    watchdog('ckan', 'Called CKAN API for '.$node->body.' package - ckan_load()');
  }
  return $node;
}

Once the CKAN data has been added to the node object it’s relatively easy to output this data in a node template. I created a template file in my theme called node_ckan.tpl.php and here’s an example of how I displayed some of the CKAN package data in there:

< ?php if ($title): ?>
 <h1 id="page-title" class="title tk-museo-slab">< ?php print $title; ?></h1>
< ?php endif; ?>
 
< ?php if ($ckan->name): ?>
  <div class="package-name">(< ?php print $ckan->name; ?>)</div>
< ?php endif; ?>
 
< ?php if ($ckan->url): ?>
 <div class="package-link">< ?php print l($ckan->url, $ckan->url, $options = array('attributes' => array('class' => 'link'))); ?></div>
< ?php endif; ?>

You can see from all of the above examples I’m using a class object called “ckan” to store our data. This came from Sean’s module and is a simple class that provides the connectivity to the CKAN API. Here’s a brief synopsis of how it works:

1. First I need a way to connect to the API. That’s relatively straightforward using the curl libraries in php.
   ^?View Code PHP

   $ch = curl_init($this->url . $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_HEADER, 0); curl_setopt($ch, CURLOPT_TIMEOUT, 30); $result = curl_exec($ch); $info = curl_getinfo($ch); curl_close($ch);

2. Now create some functions that allow various parts of the API to be called. Below is an example of two class functions which call the API.
   ^?View Code PHP

   // Get an individual package public function getPackage($package) { $package = $this->transfer('api/rest/package/' . urlencode($package)); if (!$package->name){ throw new CkanException("Package Load Error"); } return $package; }   // Get a list of all packages public function getPackageList(){ $list = $this->transfer('api/rest/package/'); if (!is_array($list)){ throw new CkanException("Package List Error"); } return $list; }

All of these API calls return a JSON object which is then decoded into an object using the PHP function:

json_decode($results);

Theming

Once that data has been successfully retrieved and decoded from JSON into something that’s easier to handle, it needs to be themed. For this I created two theming functions; one for creating an individual list item, the other to create a formatted list of these individual items. I call the theme function whenever I get results from the API, and pass it the results, along with the title I want to appear on the listings page.

// Theme the results retrieved from API call
theme('ckan_results', $results, 'All Packages');
 
/**
 * Theme search results
 */
function theme_ckan_results($results, $title = '') {
  // Two global variables needed by the pager.
  // Taken from pager_query() in pager.inc
  global $pager_page_array, $pager_total;
 
  $output = '';
 
  // Grab the 'page' query parameter.
  // Taken from pager_query() in pager.inc
  $page = isset($_GET['page']) ? $_GET['page'] : '';
 
  // Convert comma-separated $page to an array, used by other functions.
  // Taken from pager_query() in pager.inc
  $pager_page_array = explode(',', $page);
 
  // Generate the data for page the requested and add it to the output.
  $items_per_page = variable_get('ckan_items_per_page', 4);
  // If there are less results than the specified number of items per page, reset the number of items per page
  if($results->count < $items_per_page) { $items_per_page = $results->count; }
 
  // Initialize pager
  $start = 0;
  // If it's not the first page
  if($page) {
    // Set the data to start displaying on the correct page
    $start = $page * $items_per_page;
  }
 
   if($title) {
     $output = '<h1 id="page-title">'.$title.'</h1>';
   }
 
  $output .= '<h3 class="resultscount">Your search returned '.$results->count .' records</h3>';
 
  // Theme the individual results
  for ($i = 0; $i < $items_per_page; $i++) {
      $output .=  theme('ckan_item', $results->results[$i]);
  }
 
  // Put some magic in the two global variables
  // Based on code in pager_query() in pager.inc
  $total_results = $results->count;
  $pager_total[0] = ceil($total_results / $items_per_page); //ckan_number_of_pages();
  $pager_page_array[0] =
    max(0, min(
      (int)$pager_page_array[0],
      ((int)$pager_total[0]) - 1)
    );
 
  // Add the pager to the output.
  $output .= theme('pager', NULL, $items_per_page, 0);
 
  return $output;
}
 
/**
 * Theme individual search items
 */
function theme_ckan_item($item) {
  // Link the title to the dataset
  $output .= '<h2>' . l($item->title, 'dataset/' . urlencode(check_plain($item->name))) . '</h2>';
  // Truncate the notes field
  if($item->notes) {
    $output .= '<p>' . truncate_utf8($item->notes, 250, $wordsafe = FALSE, $dots = TRUE) . '</p>';
  }
  // Output any tags	
  if(count($item->tags) > 0) {
    foreach($item->tags as $key => $value) {
      $items[] = l($value, 'data/tag/'.$value);
    }
  $seperated = implode(', ', $items);
  $output .= '<p><strong>Tags:</strong> ' .$seperated. '</p>';
  }	
return $output;
}

One thing you need to consider when displaying a list of results is having a pager built in so that you can break the list into bite-sized chunks. This took quite a while to figure out how to do. The problem was that the API call resulted in a lot of data and that resulted in a significant delay loading the page, due to the amount of time to complete the round trip to the API, along with the time taken to render all of that in a pager. CKAN however is very clever. When you call the API and ask for a list of packages, it returns the packages, but it also returns, as a variable, the count of the records your query generated. As well as that you can use the parameters ‘offset‘ and ‘limit‘ just like in SQL. What’s even more clever here is that it still returns the variable that holds the count of the records the query generated, but it also only returns the number of packages determined by the ‘offset‘ and ‘limit‘ parameters.

So if an API call to list all packages for a certain tag would normally return 200 records, and you specify a limit of 10 and an offset of 10, the data returned will contain a count of the number of records normally generated by that call, 200, but will only return 10 packages in the data, as specified by the offset and limit. This came in extremely useful for the pager as I just passed an offset and limit each time a page was loaded and then cached the returned data.

$ckan = ckan_ckan();
$start = 0;
$items_per_page = variable_get('ckan_items_per_page', 4);
if($page) {
  // If we're in a page, we need to set where to start the list
  $start = $page * $items_per_page;
}
 
// Set the offset
$offset = $start;
// Limit to the number of items per page 
$limit = $items_per_page;
 
// Get the list of tags with their count
try {
  $results = $ckan->advancedSearch(array('department' => $ministry, 'all_fields' => '1', 'offset' => $offset, 'limit' => $limit));
} catch (Exception $e){
  drupal_goto(variable_get('ckan_no_results_page', 'sorry'));
}

Homepage Chart

There was a requirement for a graph on the homepage the displayed the number of packages attributed to each Government Ministry. The quickest way to do this was using Google Chart Tools. It was relatively straightforward to get the data we needed. I did however have to do some funky sorting to get the data in the correct order. I also found a wonderful tutorial that really helped to clear up some of the label/legend issues I was having.

/**
 * Function to build a Google Chart
 *
 * @return	  string	HTML code with img tag
 *
 **/
function ckan_chart() {
  // If there is a cached version of the chart
  if(($cache = cache_get('ckan:chart', 'cache_ckan')) && !empty($cache->data)) {
    $image = $cache->data;
  } else {
    watchdog('ckan', 'Called Google API to build chart');
    // Get the list of ministries
    $ministries = explode("\r\n", filter_xss(variable_get('ckan_ministry_list', '')));
    // Set up our data array
    $data = array();
    foreach($ministries as $ministry) {
      $ckan = ckan_ckan();
      // Get the list of tags with their count
      try {
        $results = $ckan->advancedSearch(array('department' => $ministry, 'all_fields' => '0', 'offset' => '0', 'limit' => '1'));
        $count = $results->count;
      } catch (Exception $e){
        $count = 0;
      }
      // Cache the count to use on the Ministry list page '/ministry'
      cache_set('ckan:ministry_'. $ministry .'_count', $count, 'cache_ckan');
      $chart->data[$ministry. ' ('. $count . ')'] = $count;
    }
    // Sort the array in reverse order - most packages first and maintain index association
    arsort($chart->data);
    // Return all the keys of the data array - the names of the ministries
    $chart->legend = array_keys($chart->data);
    // Get the range of the chart - highest + a quarter
    $range = round(current($chart->data) * 1.25, -1);
    // Grid spacing  100/MaxRange*IntervalAmount
    $grid = 100/$range * 50;
    // Chart size, must be less than 30k pixels
    $chart->size = array(
      '590',
      '380'
    );
 
    // Create query
    $chart->query =
      'cht=bhg&'.	// Type
      'chd=t:'.implode(',', $chart->data).'&'.	// Data
      'chs='.$chart->size[0].'x'. $chart->size[1].'&'.	// Size
      'chco=cc0000&'.	// Color ( Remove # from string )
      'chxt=x,y&'.	// X,Y axis labels
      'chxr=0,0,'.$range.'&'. // Range
      'chxs=1,000000,13|0,000000,13&'. 	// Axis colors and font size
      'chg='.$grid.',0,5,5&'. // Grid verticalgridlines, horizontalgridlines, linesize, gapsize
      'chds=0,'.$range.'&'.	// Scale
      'chma=0,0,0,0&'. //left_margin, right_margin, top_margin, bottom_margin| legend_width, legend_height
      'chbh=13,0,2&'.	// bar_width_or_scale, space_between_bars, space_between_groups
      'chxl=1:|'.implode('|', array_reverse($chart->legend, TRUE)).'&'; //|Jan|Feb|Mar|Apr|May'
 
    $api_path = 'http://chart.apis.google.com/chart?';
    $url = $chart->query;
    $image = sprintf('<img src="%s" alt="%s" style="width:%spx;height:%spx;" />', $api_path.$url, 'Who\'s Sharing', $chart->size[0], $chart->size[1]);
      cache_set('ckan:chart', $image, 'cache_ckan');
  }	
  return $image;
}

So that’s a brief(not-so) overview of some of the fundamentals of how I integrated Drupal with CKAN and was able to create nodes and listings directly from API calls.

In my next post I’ll cover some very important areas of the module development such as:

• Caching
• Tag cloud creation
• Using the CKAN Search API for all lists

Recently, due to Views lack of ability to OR it’s filters together and due to some serious constraints in relation to arguments and relationships, I found myself needing to figure out how to rewrite the SQL of a Drupal view so that I could get the records that I needed.

It seems like there are two ways to do this. You can either rewrite the SQL directly, or try to hack the query object that views provides.

A quick Google search and I ended up on Drupal.org

Which then led me to the Views documentation

From here I was able to deduce that the

hook_views_pre_execute()

hook was what I needed.

I created my module and added the hook:

/**
 * Implementation of hook_views_pre_execute()
 *
 **/
function other_views_pre_execute(&$view) {
  switch($view->name) {
    // If it's my view
    case 'my_view':
      switch($view->current_display) {
        // If it's the block on the author page
	case 'block_5':
	  // Set the extra argument
	  $view->build_info['query_args'][2] = $view->build_info['query_args'][1];
	  // Rewrite the query
	  $view->build_info['query'] = "SELECT ... ";
	  break;
       }
   }
}

Lets step through this:

function other_views_pre_execute(&$view) {

We need to make sure the word hook is replaced with the name of our module, in this case it’s called ‘other’

switch($view->name) {
  // If it's my view
  case 'my_view':

Each view is represented by a unique name. We need to modify the query for a certain view, not all of them, in this case the view is called ‘my_view’.

switch($view->current_display) {
  // If it's the block on the author page
  case 'block_5':

Views can have multiple displays, such as blocks, pages and attachments. In this case we want to target the block display with the name/id ‘block_5′

// Set the extra argument
$view->build_info['query_args'][2] = $view->build_info['query_args'][1];
// Rewrite the query
$view->build_info['query'] = "SELECT ... ";
break;

In this case our query is going to have an extra argument added to the query. These arguments are an array $view->build_info['query_args']. They appear in the form ‘%s’ and ‘%d’ in the query text, like this:

WHERE (node.status <> 0) AND (node.type in ('%s'))
AND ((node_node_data_field_publication_centre_authors.title = '%s') OR (node_node_data_field_publication_first_author.title ='%s'))

In this case the first ‘%s’ = $view->build_info['query_args'][0], the second one = $view->build_info['query_args'][1] and so on.

And that’s it. Your query is rewritten. One thing to watch our for, do not add any extra fields in the SELECT statement, this can cause all sorts of crazy problems.

The alternative to using the pre_execute hook is to use the query_alter hook.

This seems a lot trickier and even Earl Miles himself hates this hook, so I suggest staying away from it, but if you’re interested in what it looks like here’s a quick Krumo screenshot of what it looks like. This was achieved using the devel module along with the kpr() command. So

kpr($view->query); 

Anatomy of a View Query

Here’s a really quick and useful trick to know.

Sometimes you might want your form to redirect to somewhere and pass a few bits of information in the querystring, so that you can do something when you get there.

For example, I needed a recent form to redirect depending on which checkboxes were checked to build a certain URL:

if($form_state['values']['filters']['open']) {	$query[] = 'o=1'; } 
if($form_state['values']['filters']['down']) {	$query[] = 'd=1'; }
$form_state['redirect'] = array('data/search/'.$keyword, implode('&', $query));

If “open” checkbox is checked, it will redirect to “data/search/keyword?o=1″. I then get the value of this using $_GET['o']. Simple eh?

DrupalCon San Francisco 2010 is just around the corner and Raised Eyebrow hopes to “own the podium”, sorry I couldn’t resist. Chris and I have been to 2 Drupalcon’s so far in our Drupal lifetime, and have decided it is time to give something back. We’ve come up with 2 Session Proposals that are overviews about how we used Drupal to create two killer applications for managing a small Web Business.

One stores all kinds of client information; FTP connection info., Drupal logins, client contact info., newsletter provider and vendor info. The other is an online training manual for our clients, enabling us to train them remotely, give them access to it wherever they can find a browser and reduces the need to print large binders that end up getting lost or in the landfill/recycling.

So if you’re interested in hearing more about these the please go cast your vote by following the links below:

Manage your Web Studio using Drupal
Using Drupal to train clients on how to use Drupal

The closing date for voting for Voting is March 1st, so get voting. See you in San Fran.

There are many ways to skin the cat when it comes to putting video on a Drupal site. I’ve tried and tested quite a few methods since my first introduction to Drupal 2 years ago. I’ve used Embedded Media Field as well as Video Filter but finally settled on the combination of FileField with JWPlayer or Flowplayer and in some cases the Media Mover Module for moving files to Amazon S3 storage. I’m going to use our recent launch of the CCPA website as a case study for how we currently handle video delivery. So let’s dissect this a little.

Uploading files

The video files need to be uploaded before we display them. This is best achieved using the wonderful Filefield Module. This is quite a simple yet powerful module developed/maintained by Lullabot, Nate Haug (quicksketch), whom I’ve had the pleasure of being trained by at one of their excellent Drupal Theming workshops. Once you install and enable the module you then add a new CCK field, of type “filefield”. In our case we have a content type called “Multimedia”. We add the field to this content type. You then need to configure the following:

1. Permitted upload file extensions

In most cases this is relatively straightforward, it’s just one file type. If you’re using JWPlayer or Flowplayer it will be FLV. Both these players are built to play Flash Video files (FLV). If you have Quicktime MOV’s or AVI’s that you want to upload then you’ll need to consider different options for playing video. For the purpose of this case study we’re just uploading FLV files.

2. File size restrictions

It’s very important that you set these, otherwise you may end up with users trying to upload 200MB videos, not a very good idea. I set this low as a learning feature for clients. Any reasonably long FLV file that is over 40MB is probably not optimized as well as it should be.

3. Path Settings

I like to keep all files that admin/editor users upload in a folder called uploads so that it’s easy to manage them later if they need to be exported etc.

Multimedia File Field Settings

Create a placeholder image

Most video players require some sort of poster/placeholder image to display before the video plays. In this case I created another FileField for the placeholder image. We’ll use that later on in conjunction with the ImageCache module to achieve our desired results.

Moving Files to Amazon S3

We’ve been using Amazon S3 for storing video files on quite a number of sites recently. One reason is that we were looking for a location off the webserver that could deliver the video, without impacting the performance of the server, so that in the event of a traffic spike the webserver wouldn’t fall over. We could also have used Amazon EC2 or another CDN service for this, however as most of our clients have a very regional (BC) audience. Most CDN’s have nodes in various locations across the US and Europe and this would have served no real improvement as the nearest cached version will always be in the same place for everyone.

So if they’re uploading the files directly to the Drupal site, how to the files get delivered from Amazon S3. That’s where the Media Mover module comes in. This module has many purposes, but for our needs it simply harvests all the files uploaded via the “Multimedia” content type and moves those files to Amazon S3 so that we can deliver them from there.

Download, install and enable the Media Mover module. You’ll also need an S3 account and will need to set that up via the module setting page. You then need to add a Configuration via https://www.yoursite.ca/admin/build/media_mover/add.

Media Mover has 4 actions which it performs on your files:

• Harvest – Define/collect the files you want to perform actions on
• Process – Perform certain actions on the files
• Storage – Where to store files once the actions have been carried out
• Complete – Final actions to perform on the files

So in this case we just want to harvest all Multimedia files and store them on Amazon S3.

So for clarification here Media Mover does NOT MOVE the files to Amazon S3, it simply COPIES them over to S3 and the original files remain on your server.

Media Mover Settings

Delivering the moved Video files

So this is where the Drupal theming trickery comes in. Flowplayer and JWPlayer are both Flash based FLV video players than can be called using Javascript and that’s exactly what I do on this site. In plain english this is what happens:

1. Output the placeholder image to screen as a link.
2. Use Javascript so that when the user clicks on the image the video plays.
3. Deliver the video from the file on Amazon S3 rather than the file on the webserver (Drupal site).

We need to modify three files to achive the above:

• template.php
• multimedia.js (a newly created JS file)
• node-multimedia.tpl.php (a custom template file for all multimedia types)

template.php file

Setting up all the variables we’re going to need to use as well as making the javascript available

if($variables['field_file'][0]['view']) { //If there is a file and there is something to display...
  if ($variables['field_aspect_ratio'][0]['value']) { //Aspect ratio handling
    $variables['aspect_ratio'] = $variables['field_aspect_ratio'][0]['value'];
  } else {
  $variables['aspect_ratio'] = 'normal';
  }
  $variables['multimedia_type'] = 'video'; //Set the type of multimedia - we also have audio and interactive...
  custom_theme_get_media_mover_files($variables['field_file'][0], $variables['media_mover'][3]); //Set the filepath to the media moved filepath...
  drupal_add_js(array('videoplayerpath' => path_to_theme() .'/scripts/plugins/flowplayer/flowplayer-3.1.1.swf'), 'setting'); //Set a JS variable to retrieve later...
  drupal_add_js(path_to_theme() .'/scripts/plugins/flowplayer/example/flowplayer-3.1.1.min.js', 'theme'); //Call the player...
  drupal_add_js(path_to_theme() .'/scripts/multimedia.js', 'theme');//Call the custom JQuery to handle creating the player...
}
 
/**
 * A function that takes a file object and a media_mover element array and set the file path to
 * its media moved path on Amazon S3 or wherever it moved to.
 *
 * It uses the unique file_id identifier to match file with media_mover file.
 *
 * $file = $variables['file_image'][0];
 * $media_mover = $variables['media_mover'][{id of media mover configuration}];
 *
 * @param 		&$file A Drupal file array (by reference)
 * @param 		$media_mover A media_mover file/element array
 */
function custom_theme_get_media_mover_files(&$file, $media_mover) {
  if(module_exists('media_mover_api') && $media_mover) { // If media mover is installed...
    foreach($media_mover as $media) { // Loop through each media_moved file...
      if($media['fid'] == $file['fid']) { // If they match (file id is a unique identifier...
        $file['filepath'] = $media['complete_file']; // Replace the attached file path with the media moved file path...
      }
    }
  }
} // custom_theme_get_media_mover_files()

Let me explain one thing in regards to line 9. I’ve created a custom function and I’m passing

$variables['media_mover'][3]

to my custom function. When you create a Media Mover configuration and map it to a CCK field, it creates an array in $variables to keep track of the Media Mover object. The array is called ‘media_mover’ and the number 3 in this case is the ID of the Media Mover configuration.

node-multimedia.tpl.php

Set up the template. Create a wrapper div with the placeholder image as the background image (this is run through imagecache) and display the play button as a link with the path set to the path of the Amazon S3 file. This link will also have an id attribute of ‘multimedia’. This is necessary as it allows us to attach the player, via Javascript, to this link.

<div id="containing-block">
<div id="video-wrapper" class="<?php print $aspect_ratio;?>">
<div>
     &lt; ?php print l('<img src="/'.path_to_theme().'/images/ccpa-button-play-large.png" alt="Play this video" />', $field_file[0]['filepath'], $options = array('html' => TRUE, 'attributes' => array('id' => 'multimedia', 'class' => $multimedia_type))); ?></div>
</div>
</div>

multimedia.js file

Hook the Flowplayer to the link, with id of ‘multimedia’, that we created in the template.

Drupal.behaviors.showMultimedia = function(context) {
  var interactive_path = $('#multimedia').attr('href'); /*Get the path to the video*/
  var interactive_image = $('#multimedia').css('background-image');	/*Get the path to the placeholder image*/
  interactive_image = interactive_image.slice(4,interactive_image.length-1);/*Tidying up the interactive image path*/
 
  if($('#multimedia').hasClass('video')) {/*If the link has a class of video*/
  $('#multimedia').flowplayer( /*Initialize the flowplayer and configure the controls*/
    Drupal.settings.basePath + Drupal.settings.videoplayerpath, /*Path to the player, gotten from temaplate.php*/
    {
      plugins: {
	controls: {
	  stop: true,
	  backgroundColor: '#efefef',
	  backgroundGradient: 'none',
	  borderRadius: '0px',
	  bufferColor: '#d2d6ab',
	  bufferGradient: 'none',
	  buttonColor: '#777777',
	  buttonOverColor: '#99a134',
	  durationColor: '#cccccc',
	  height: 25,
	  opacity: 1.0,
	  progressColor: '#99a134',
	  sliderColor: '#9999999',
	  sliderGradient: 'none',
	  timeBgColor: '#777777',
	  timeColor: '#ffffff',
	  tooltipColor: '#000000',
	  tooltipTextColor: '#ffffff',
	  volumeSliderColor: '#777777',
	  volumeSliderGradient: 'none'
	}
      }
    });
  }
};

I hope that was easy to follow. Now there’s one more thing to cover and that’s Aspect Ratio.

Aspect Ratio

The issue of aspect ratio is very important when figuring out how to display video. Not so recently YouTube switched all video display to the 16:9 ratio thus setting the stage for the proliferation of the widescreen aspect ratio across the web. So how do you allow the user to upload a video and choose it’s aspect ratio. I’m sure there are other ways to do this via Metadata etc, but for our needs on this site I used a CCK field. This is a simple CCK field set with three options:

1. None (defaults to 4:3)
2. Normal (4:3)
3. Widescreen (16:9)

Aspect Ratio Field Settings

We then check the value of this field in template.php above:

if ($variables['field_aspect_ratio'][0]['value']) { //Aspect ratio handling
    $variables['aspect_ratio'] = $variables['field_aspect_ratio'][0]['value'];
  } else {
  $variables['aspect_ratio'] = 'normal';
  }

and set a variable called ‘aspect_ratio” which we apply as a class to the div wrapping the video in the node-multimedia.tpl.php:

<div id="containing-block">
<div id="video-wrapper" class="<?php print $aspect_ratio;?>">
<div>
     < ?php print l('<img src="/'.path_to_theme().'/images/ccpa-button-play-large.png" alt="Play this video" />', $field_file[0]['filepath'], $options = array('html' => TRUE, 'attributes' => array('id' => 'multimedia', 'class' => $multimedia_type))); ?></div>
</div>
</div>

We have also created image cache presets for the placeholder images to account for both aspect ratios. These are named ‘multimedia_normal’ and ‘multimedia_widescreen’ and these have the appropriate dimensions associated with them:

Image Cache Presets

So using the amazing article on A List Apart for creating intrinsic ratios for video we use CSS to resize the player based on the aspect ratio chosen by the user.

style.css file

/* -- Multimedia -- */
#containing-block {
  width: 100%;
}
 
#video-wrapper {
  position: relative;
  padding-top: 25px;
  height: 0;
}
 
  #video-wrapper div,
  #video-wrapper embed,
  #video-wrapper object {
    position: absolute;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
  }
 
  #video-wrapper.normal {
    padding-bottom: 75%;
  }
 
  #video-wrapper.widescreen {
    padding-bottom: 56.25%;
  }
 
  * html #video-wrapper {
    margin-bottom: 45px;
    margin-bot\tom: 0;
  }
 
#video-wrapper #multimedia.audio {
  display:block;
  height:100%;
  margin:1em 0 0 0;
  text-align: center;
  width:100%;
}
 
#video-wrapper #multimedia {
  display:block;
}
 
  #video-wrapper.normal #multimedia img {
    /*margin:118.5px 0 0;*/
    margin:32% 42%;
  }
 
  #video-wrapper.widescreen #multimedia img {
    margin:22% 41%;
  }

And the end result looks something like this http://www.policyalternatives.ca/multimedia/matthew-poverty-and-looking-after-each-other-tough-times. They haven’t added any widescreen content yet, just testing content.

So what are the advantages of doing things this way?

1. You can easily use any player to play your flash files (all you need to do  is change the path to your player and a few configuration params in multimedia.js)
2. All your video content is hosted on and delivered from Amazon S3. But there is also a copy on your local server in the event of something going wrong on Amazon S3
3. You don’t have to worry about your video looking skewed due to aspect ratio problems
4. You can add many other apsect ratios pretty quickly
5. The video file is still downloadable when javascript is not present or disabled

I’d love to get feedback on how other do this, please leave a comment or send me an email and let me know how you deliver Video content on your site.

If you use Ubercart on your Drupal website you may be familiar with product classes. Ubercart has, by default, a product content type, however say you have other content types on your site that you want to sell, you can create product classes from those – http://www.ubercart.org/docs/user/3341/understanding_product_classes.

This works nicely until that moment when you want to rename your content type machine name.. e.g from “contenttype” to “content_type”

I did that recently thinking everything would be hunky dory once I made the change, the results were not so nice. So to help prevent this from happening to anyone else, here’s a simple rule to follow before making changes to your content type machine name.

Make sure you remove your content type as a product class before changing it’s machine name.

It’s that easy, simply disassociate your content type from Ubercart. The page to do this can be a little scary (www.yoursite.com/admin/store/products/classes), but just click the delete link on the class you want to disassociate and you’ll get a “Are you sure you want to do this message”, go ahead, it won’t delete your nodes, it just removes a flag that tells Ubercart they’re products. Also, if you’re worried about losing your product data, like SKU, Price, Shipping info etc, in my case it retained all product info when I re-instated it. Please backup your data before doing this, I cannot be responsible for any problems this causes on your site. Now, go to your content type, change the machine name.

Oh, and don’t forget to double check all your views are still working, you may need to edit them to ensure they’ve picked up the new content type machine name. I guess this is a good lesson to remind you to give your content types a machine name that you will not want change later.

I’d been a little skeptical about the idea that anyone can contribute to the Open Source community by giving a little help now and again. This skepticism came from the flaming given for asking ‘newbie’ questions, asking a question in the wrong room or from suffering the raging ego of a well seasoned and highly adored contributor. Today however, I feel much more positive about my ability to give back to Drupal and the Open Source community, and it all came from a simple (or maybe not so simple) thank you. Here’s the story:

Yesterday afternoon, while tearing my hair out over some tricky views problem that I could not find a solution to, I logged in to IRC, which I don’t usually do, to see if I could get some help.

In the #drupal room I asked my question and as usual didn’t receive any replies (which is why I don’t usually log in). It’s a bit of a difficult VIEWS problem and those usually don’t get answered quickly in IRC. While waiting for a response I noticed a question that was similar to mine but not as complex. I took some time (2 minutes) to review the code and I was able to speculate on what the problem was. I posted a proposed solution and it worked. I didn’t really think much of it until later on when I received an email via drupal.org from the person I helped asking me for my e-mail address. I sent it on and this morning I awoke to the email below. Until now I had been wary of devoting my time to hanging around in the IRC channels. This experience has changed my feelings on this quite a bit and I hope to spend a little more of my time in there, when I have it, helping others. In this case, it definitely did pay to help others, but the thank you email was more than enough, it made my day, my week, my month and maybe even my year :) Here’s to contributing more to the community. It doesn’t take much, and just a simple thank you for the help, when given, makes all the difference.

Hi Colin,

Thanks. I am in Fort Smith, NT just on the Alberta/NWT border directly north of Edmonton.
I am a Fire Scientist, I model Wildfire on the landscape. Yesterday I was completing the Drupal Integration part of a multi-year government project, most of us running on little or no sleep and less patience. I CVS updated VIEWS and it became way more strict about the PHP code and broke my drupal. I had been begging for assistance in different irc drupal chats for about 45 mintues when you came along. It really was like saving our team, when the view worked, the entire fire science team cheered. We just wanted to say a real heartfelt thanks.
I am sending you, $20 via email, its a small gesture, as our team knows that $20 will buy a friday case of beer anywhere in canada except north of Norman Wells NWT ;-) Perhaps you can claim to be the first in your office to have been mailed a case of beer by the drupal community – LOL

I’ve been working with Coda now for a few weeks and it’s been going pretty smoothly. There are a few bugs in the software, or they may even be feature requests :) When creating a file locally and subsequently uploading it to the server the file permissions are set to 700. This was causing all sorts of weirdness on a Drupal site that I’m currently theming. This is definitely a bug, however it’s pretty easy to overcome. In “Preferences -> Transfers” there is an option to “Set permissions on upload”. Set this to 644. This is the recommended permission for files in your theme folder in Drupal. You should now have no more problems when uploading files. If you’ve already uploaded files, it’s pretty easy to change via the command line using the chmod command:

chmod 644 filename.php