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!