Automatic batch editing of Drupal nodes and CCK file fields

In a previous post I explained why we decided to convert most of the images on this site from PNG to JPG and how we used ImageMagick to batch it.

What I didn't get into is how we updated Drupal, our CMS,  to point to all these newly converted files. Manually uploading and updating nearly two hundred new images through a web form is time consuming and boring. Finding a non labor intensive solution can also be time consuming... but so much more interesting!

Once you know how, it's not really that difficult.

Here's a simple example:

<?php

error_reporting(E_ALL);
require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$results = db_query('select nid from node where type="appliance"');
while ($result = db_fetch_object($results)) {
  $node = node_load($result->nid);

  # useful if you don't know $node like the back of your hand
  print "<pre>";
  print htmlspecialchars(print_r($node, TRUE), ENT_QUOTES);
  print "</pre>";

  # somewhat useless example edit
  $name = strtolower($node->field_name[0]['value']);
  $node->field_name[0]['value'] = $name;
  node_save($node);
}

?>

Save this as a temporary PHP script in your web root, point your browser to the location, and walla! You just lowercased the value of the CCK name field for all appliance nodes.

For your custom edits you should of course replace the value passed to db_query(). You can use Views to help you create this SQL query:

Leveraging Views as an SQL query builder is especially helpful with more complex queries.

But wait there's a catch!

It turns out CCK file fields are a special case and you can't edit them with a simple node_save(). That's because $node contains a reference to the field field object, not the object itself.

This slowed me down for a bit, but eventually I figured it out how to use drupal_write_record to update the file field table:

<?php

error_reporting(E_ALL);
require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

function f(&$s) {
    $s = str_replace("png", "jpg", $s);
}

drupal_flush_all_caches();

$results = db_query('select nid from node where type="appliance"');
while ($result = db_fetch_object($results)) {
  $node = node_load($result->nid);

  $file = $node->field_icon[0];

  f($file['filename']);
  f($file['filepath']);
  f($file['filemime']);

  $realpath = "/path/to/webroot/" . $file[filepath];
  $stat = stat($realpath);
  if(!$stat)
    continue;

  $file['filesize'] = $stat[7];

  print "<pre>";

  print htmlspecialchars(print_r($file, TRUE), ENT_QUOTES);
  drupal_write_record('files', $file, 'fid');

  print "</pre>";
}

drupal_flush_all_caches();

?>