Support Migrated Content with Redirects

I'm returning from my hiatus for a quick blurb discussing how to support all of those backlinks to old content that you might be migrating into Drupal.  The scenario: you have an old cms that your content was in and you are moving to Drupal.  Perhaps that old cms injected its tech base into your extensions and you used to have a bunch of articles like "asdf/this-article-is-pretty-sweet.aspx" or "kjhgg/asfjhsf/we-have-like-the-best-writers.fubar" and you have come to your senses and have decided that for proper SEO I want all of my articles in Drupal to look like articles/real pages to those bots with a valid web page extension and will instead end with .htm.  Maybe for SEO reasons, you are completely retooling your url structure and want something that makes sense for your new setup.

These are all valid scenarios and it is recommended to think about those urls especially when migrating and the option is on the table.  Just don't forget, you want to preserve those old dead links and provide a redirect to get  all those back links to the right place.  One approach could be to generate a ton of mod-rewrite lines and handle it at that level, that could be one option.  You are already writing a process to migrate those articles, and hopefully you are using the Migrate Module?  If not, seriously consider it.

Now that you've built your article migration out, you are so very close to making all of those backlinks redirect.  First, go out and get the Redirect module, which works with the Global Redirect module, which you should already have if you are concerned with SEO.  

Next, provide the existing alias for the content as a field (I'll call it ContentURLAlias) in your migration, but there is no need to map it.

The next step is to expand on the content migration and override the complete function.  When we do this, we will call the redirect methods to take the new path of the entity that has just been saved back to the database, and the alias passed in and create our redirect like so:

  public function complete($entity, stdClass $row) {
      $redirect = new stdClass();
      redirect_object_prepare($redirect, array(
          'source' => $row->ConentURLAlias,
          'source_options' => array(),
          'redirect' => $entity->uri['path'],
          'redirect_options' => array(),
          'language' => LANGUAGE_NONE,
        ));
      redirect_save($redirect);
  }

And that is it!  Along with the redirect module you'll get tracking statistics on these old backlinks so you can recall when they get stale and decide when the safest point would be to clean them up or retain them permanently.

I hope this was helpful!

Add Permissions to Workbench Moderation States in Drupal

I've been attempting to implement a workflow solution for my Drupal 7 project, and at this time the best option to me is the Workbench module:

http://drupal.org/project/workbench

Props to those guys for what I see as a good implementation.  The breakout of functionality into separate modules is quite nice.  It helps keep down on the bloat you might experience when all you might need is a few simple options.

Now I needed to implement a pretty simple workflow with a few states and various roles for the users who will be interacting with the content in those states.  My needs were almost met entirely with the Workbench and Workbench Moderation modules.  My Achilles heel lied with a basic requirement of limiting the permissions of some roles depending on what state the node was in.

The answer wasn't too hard, but I thought it would be useful for someone out there in what would be a common scenario.  What I like about this approach is that it makes the permission settings easy to export via the Features module and takes advantage of the existing permissions interface to get setup.  The biggest drawback is that it potentially adds to permissions sprawl.

All of my code is included in my own custom module so you don't need to modify any of the code within the Workbench modules.  The first step is to implement hook_permissions to add a permission setting for each state in your workflow:

function mymodule_permission() {
  $permissions = array();
  
  // Per state permissions.  Used by workbench_moderation_node_access().
  $states = workbench_moderation_states();
  foreach($states as $state) {
    $permissions['edit all content in ' . $state->name] = array(
        'title' => t('Edit all content in @state', array('@state' => $state->label)),
    );
    if (variable_get('workbench_moderation_per_node_type', FALSE)) {
      foreach ($node_types as $node_type) {
        $permissions["edit $node_type in " . $state->name] = array(
          'title' => t('Edit @node_type in @state', array('@node_type' => node_type_get_name($node_type), '@state' => $state->label)),
        );
      }
    }
  }
  return $permissions;
}

Note the extra code that supports the functionality that opens up different worfklow settings in Workbench for your content types.  Your implementation might not need this, but I wanted to be thorough.

Next we need to implement hook_node_access to potentially deny access to update operations on a node based on these access settings, again taking into account the per content type variable:

function mymodule_permission() {
  if(isset($node->workbench_moderation) && $op == 'update') {
    if(variable_get('workbench_moderation_per_node_type', FALSE) &&
          !user_access('edit ' . $node->type . ' in '.$node->workbench_moderation['current']->state, $account)) {
      return NODE_ACCESS_DENY;
    }
    else if(!variable_get('workbench_moderation_per_node_type', FALSE) &&
          !user_access('edit all content in '.$node->workbench_moderation['current']->state, $account)) {
      return NODE_ACCESS_DENY;
    }
  }  
  return NODE_ACCESS_IGNORE;
}

Here we make sure to return NODE_ACCESS_IGNORE if none of our cases match so we don't accidentally grant access or deny access for other modules are able to jump in and have their say.

That's all for this post and as always feel free to leave any questions!

Drupal Migrate Multiple Taxonomy Terms

I stumbled upon a blog post yesterday discussing a way to import a node with multiple terms tied it:

http://stellapower.net/blog/migrate-module-migrating-nodes-taxonomy-terms

I wanted to offer up an alternative approach that I've utilized that hopefully is easier to use without using hook_migrate_prepare_node.  I will start by assuming that our source data has a table I'll call node, with a 1 to many relationship with a term table.  From there, let's build a simple query object with just a couple of fields (not all that you would need, just an example).

$subquery = db_select('term', 't2');
$subquery->join('node', 'n2', 't2.nid=n2.nid');
$subquery->groupBy('t2.nid');
$subquery->addField('t2', 'nid');
$subquery->addExpression("GROUP_CONCAT(t2.label SEPARATOR '|')", 'labels');

$query = db_select('node', 'n')
	 ->fields('n', array('nid'));
$query->join($subquery, 't', 'n.nid=t.nid');
$query->addField('t', 'labels');

This example uses the built in pdo objects to build the select and then we can use mysql's GROUP_CONCAT function to build a column containing multiple values.  The migrate module the has built in support for separators via a function on a field mapping:

$this->addFieldMapping('field_terms', 'labels')
     ->separator('|');

I've been doing migrations from MS Sql Server, so if you are in that world you might be saying "we don't have group_concat".  True, but we can slap a view with a little hack to do the concat for us and do a select from Drupal through that view.  In my case we'll use the '!' character as a space replacement to preserve the original whitespace.  With the xml path hack, it will become space separated which we will convert to '|' and then convert the '!' back to whitespace.

SELECT * FROM node n
LEFT OUTER JOIN (
	select t.nid, 
		REPLACE(REPLACE(RTRIM((
			SELECT CAST(REPLACE(dat.DrupalTerm, ' ', '!') AS VARCHAR(MAX)) + ' ' 
			FROM term t2 WITH (nolock)
			WHERE t2.tid = t.tid FOR XML PATH (''))), ' ', ',')
			, '!', ' ') AS labels
	from term t WITH (nolock)
	GROUP BY t.nid
) my_terms ON n.nid = my_terms.nid

As always, let me know if you have any questions!

How To Install Drupal 7 on Centos/Fedora/RedHat

Recently I have experienced some pain in getting drupal going on a CentOS server. I had done all of my work based on the drupal ubuntu quickstart, so this was a bit of a painful transition. The following blog post proved to be extremely helpful:

http://linuxconfig.org/how-to-install-drupal-7-on-fedora-linux

However, it didn't get me 100% of the way. I got through the installation, however I was utilizing drush to manage the acquisition of all of my modules. Going down this route, it was very confusing when I could download my module, enable my module, yet some features were available through the UI, but they wouldn't show up on my module page and they wouldn't show up on the configuration page, and any time you tried to access pages related to the module the page would error out. In my server logs was a key clue:

PHP Fatal error: require_once(): Failed opening required '/var/www/html/sites/all/modules/features/features.admin.inc' (include_path='.:/usr/share/pear:/usr/share/php') in /var/www/html/includes/menu.inc on line 501

At this point, my frustration perhaps blinding me from the answer in front of my face. Looking through the installation steps, there were two steps that needed to be rerun anytime I was using drush to dl a new module:

chown -R apache.apache sites/all/modules/

chcon -R -t httpd_sys_content_rw_t sites/all/modules/

Once I reran these commands after each download I was good to go.

Migrate Module and Important SEO Data

Currently, the modules for some important SEO info are in a bit of flux, so at the time that this article is being written I have chosen to go with two modules, Page Titles and Metatags Quick:

http://drupal.org/project/page_title

http://drupal.org/project/metatags_quick

Assuming that you have already setup the modules, and for metatags you have enabled a field (my case will be the description), I will be focusing on getting data into these modules' fields using the Migrate Module.

Unfortunately, it doesn't appear (to me at least) that the Page Title data is exposed as a field in association with your node. So, my solution was to create an entire Migration class solely for the Page Title. If you take a look at the Migrate Extras module, it only supports Page Title as a destination in 6.x. This isn't a big deal for us because it is easy to deduce that Page Title is storing all of it's data in a table, so we can use the MigrateDestinationTable specifying the 'page_title' table:

$this->destination = new MigrateDestinationTable('page_title');

Unfortunately, the automatic deduction of the destination schema was giving me some hiccups which resulted in Migrate telling my that it couldn't find the column destid1 during the import. If you check the map tables for this migration you'll see it is completely missing any destination columns. So, my schema definition looks a little something like:

     

     array('type' => array('type' => 'varchar',

                           'length' => 15,

                           'not null' => TRUE,

                           'description' => 'Page Title Module Entity Type'),

           'id' => array('type' => 'int',

                         'unsigned' => TRUE,

                         'not null' => TRUE,

                         'description' => 'Page Title Entity ID',                       

             ))

The rest is pretty straightforward at this point. Now we have the page title ready to go, the next thing I wanted to include was the description metatag.

For this, I created a MigrateFieldHandler that does all the heaving lifting for me. Below is the code I utilized for the metatag field:

class MigrateMetaTagsQuickFieldHandler extends MigrateFieldHandler {

 public function __construct() {

   $this->registerTypes(array('metatags_quick'));

 }



 public function prepare($entity, array $field_info, array $instance, array $values) {

   $arguments = array();

   $language = $this->getFieldLanguage($entity, $field_info, $arguments);



   // Setup the standard Field API array for saving.

   $delta = 0;

   foreach ($values as $value) {

     $item = array();

     $item['metatags_quick'] = $value;



     $return[$language][$delta] = $item;

     $delta++;

   }



   return isset($return) ? $return : NULL;

 }

}

As usual, feel free to hit me up if you have any questions!

Update: This is now available via patch in an issue for the migrate extras module:

http://drupal.org/node/1264506

Update #2: After running this migration against production data, it turns out that we have some unfortunately long meta descriptions in the source data.  For my purposes, I'll get the data cleaned up after moving to Drupal, but in the meantime I needed to modify the length of the field in the Metatags Quick module, which is defaulted to 255 w/o any options for modification.  I have since created a patch that grants access to a max_length setting in an issue comment thread:

http://drupal.org/node/1272584#comment-5008562

Use Migrate Module to Format Incoming Data

This week's post will be brief and focus on a simple example of formatting some data on it's way through the Migrate module.

In my situation, I will be using Migrate for sync'ing some data from a MS SQL server db into a couple of Vocabularies in my Drupal environment. Unfortunately, my limitations on this db are such that I can't write a view to do this work for me, and I won't dare "clean" up some data for my needs.

So, I am sitting on a few rows of data that do not come across due to the use of a n-dash character which causes an error when attempting to save into the MySql db. My solution is to address this data issue in one of my Migration classes to clean the data on it's way in. I will be inserting the prepareRow function into my class as noted in the drupal docs:

http://drupal.org/node/1132582

I will be sure to highlight my big mistake because I overlooked a key detail in the article:

"The argument $row is a stdClass object containing the data as provided by the source"

When I first wrote my code, I was referencing the destination field name which never yielded any errors. I just didn't do anything. I didn't realize my folly until I was stepping through the code and I inspected the values directly. So remember, use the source column names! Last but not least, I expanded beyond just the n-dash character and replaced for a larger set of unfriendly characters:

 function prepareRow($row) {

   $row->Category = strtr(

     $row->Category,

     array(

       "\x80" => "e",  "\x81" => " ",    "\x82" => "'", "\x83" => 'f',

       "\x84" => '"',  "\x85" => "...",  "\x86" => "+", "\x87" => "#",

       "\x88" => "^",  "\x89" => "0/00", "\x8A" => "S", "\x8B" => "<",         "\x8C" => "OE", "\x8D" => " ",    "\x8E" => "Z", "\x8F" => " ",

       "\x90" => " ",  "\x91" => "`",    "\x92" => "'", "\x93" => '"',

       "\x94" => '"',  "\x95" => "*",    "\x96" => "-", "\x97" => "--",

       "\x98" => "~",  "\x99" => "(TM)", "\x9A" => "s", "\x9B" => ">",

       "\x9C" => "oe", "\x9D" => " ",    "\x9E" => "z", "\x9F" => "Y"));

 }

Hopefully the simplicity of this post doesn't disappoint, but this solution took me longer than it ever should have. I hope this post helps someone else from making the same mistake!

Using the Migrate module with MS SQL Server

This is officially the kickoff article for my blog. It has been something I wanted to start in the past, but have only found the motivation to do so recently. Now to the good stuff.

The topic of this article covers a project I'm involved in where we have to migrate data from a couple MS SQL Server DB's into our Drupal 7 install. We've decided to go with the Migrate module to knock this out. No big deal, right?

Upon a deeper dive, I've discovered in the documentation a subtle detail that Microsoft's sqlsrv module only functions on a Windows based Drupal install, which is not going to work for us. After tinkering around for a couple of days, I've come up with a rough workaround that I hope saves some time for others.

After some search, I was able to find an extremely helpful blog article that will gets the process started:

http://grover.open2space.com/content/use-php-and-pdo-connect-ms-sql-server

Read it carefully and follow the instructions up until the code examples, those won't totally help us, but I suppose they don't hurt.

At this point one additional modification will need to be made to freetds.conf. You should be able to find this in your /etc/freetds folder. When I started migrating the data I found that ntext, nvarchar(max) and varchar(max) were not compatible with freetds out of the box. So, we need to modify this conf in the global settings I add the following:

# TDS protocol version

tds version = 7.0

client charset = UTF-8

Now, let's go get the sqlsrv module, I'll do so via drush:

drush dl sqlsrv

Then copy the sqlsrv subfolder (not the module folder, but the child folder with the same name) to drupal/includes/database/ directory. This is where I'm going to be a bit lazy and retain the same name of Microsoft's module, so everything going forward will use the same name.

Next up, pop open your settings.php file to add your connection details:

 

$databases['default']['my_source_db'] = array(

 'driver' => 'sqlsrv',

 'database' => 'mydb',

 'username' => 'myuser',

 'password' => 'mypass',

 'host' => 'myhost',

 'prefix' => '',

);

Now, we have to update the database.inc file in the includes/database/sqlsrv/ folder to get this to work.

First change is in the $options array on line 52 to correctly apparent the port number for our OS:

 

   // Build the DSN.

   $options = array(

     'host=' . $connection_options['host'] . (!empty($connection_options['port']) ? ':' . $connection_options['port'] : ''),

     'dbname=' . $connection_options['database'],

   );

Then, directly following that, the first argument of the parent constructor needs to be modified to make use of dblib instead of sqlsrv per the previously linked article:

         

   // Launch the connection to the server.

   parent::__construct('dblib:' . implode(';', $options), $connection_options['username'], $connection_options['password'], array(

     /*PDO::SQLSRV_ATTR_DIRECT_QUERY => TRUE,*/

   ));

Don't forget to also comment out (or delete) that PDO::SQLSRV_... constant above.

One more change later in the file, around line 402 for me at this point. I comment out the section of code there that references the getColumnMeta function. I'll come back and update this if I come up with a better fix, but right now I don't have time for a real fix so binary columns are not going to bind correctly:

 

   /*

   $null = array();

   for ($i = 0; $i < $statement->columnCount(); $i++) {

     $meta = $statement->getColumnMeta($i);

     if ($meta['sqlsrv:decl_type'] == 'varbinary') {

       $null[$i] = NULL;

       $statement->bindColumn($i + 1, $null[$i], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

     }

   }

   */

This should complete your setup to begin using your Migrate module. There is one more point of interest here that was a gotcha for me. I got an error on the import as it couldn't locate the mapping tables on the source server b/c we are running against a different server (duh?). So, you will need to set the 'map_joinable' option in the MigrateSourceSQL constructor as such:

 

$this->source = new MigrateSourceSQL($query, $query->getFields(), null, array('map_joinable' => false));

Hopefully, someone else finds this helpful for them! Feel free to leave any comments, questions, or corrections.

Thanks for reading!