IMC-Drupal/Upgrade/SingleToMultiSite

Moving from single site to multi-site setup

Notes from IMC York Upgrade The primary problem is that the site uses the default /files/ directory, we don't want many sites with the same files directory.

Investigating the MySQL data

• Copy of db made
• queries of data to find references to files/ directory in tables, checked by eye. Working through each table looking for possible locations. Tables and their columns that came up:

select * from audio_file where filepath regexp ".*files\/.*";
select * from comments where comment regexp "(york\.indymedia\.org\.uk|\")\/files\/.*";
select * from files where filepath regexp ".*files\/.*";
select * from flexinode_data where textual_data regexp "(york\.indymedia\.org\.uk|\")\/files\/.*"; 
select * from node_revisions where body regexp "(york\.indymedia\.org\.uk|\")\/files\/.*";
select * from node_revisions where teaser regexp "(york\.indymedia\.org\.uk|\")\/files\/.*";
select * from variable where value regexp ".*files.*";

• • audio_file.filepath contains path from root drupal dir including files/ - no false positives
  • comments.comment contains <img> tags needing changing - suprisingly no false positives
  • files.filepath as audio_file
  • flexinode_data.textual_data contains <img><a> tags needing changing - suprisingly no false positives. I guess flexinode_data.serialized_data may contain on other sites.
  • node_revisions.body and node_revisions.teaser as flexinode_data.textual_data but much much more I don't think there are false positives - will need to double-check.
  • variable.value without the / throws up the files directory setting - which could be changed easily manually from admin. Also theme variables matching files/ for images.
  • Interesting seems we didn't set any url_aliases for files directories. The video module table is there, but not yet used, guess this would have yet more matches if it had been.

• Finally running this perl script to do a search, replace and update for fields in the columns identified

#! /usr/bin/perl -w

use DBI;

# 0 - standard output; 1 - verbose output;
my $debug = 1;
# 0 - write data; 1 - don't write data
my $test = 1;

# database
my $database="databasename";
my $user="username";
my $password="passwork";

# tables with fields containing file references to change
#   'table', 'index', 'field to search', 'regex'
my @tables =
(
  ['audio_file', 'vid', 'filepath', 's;files/;files/yimc/;g'],
  ['comments', 'cid', 'comment', 's;(york\.indymedia\.org\.uk|\")/files/;$1/files/yimc/;g'],
  ['files', 'fid', 'filepath', 's;files/;files/yimc/;g'],
  ['flexinode_data', 'nid', 'textual_data', 's;(york\.indymedia\.org\.uk|\")/files/;$1/files/yimc/;g'],
  ['node_revisions', 'vid', 'body, teaser', 's;(york\.indymedia\.org\.uk|\")/files/;$1/files/yimc/;g'],
  ['variable', 'name', 'value', 's;files;files/yimc;g']
);

print "Connecting to $database\n";
my $dbh = DBI->connect("dbi:mysql:$database", $user, $password);

for (@tables) {
  my ($table, $index, $field, $regex) = @$_;
  my $n = 0;
  print "\n\n * Accessing $table\n";

  my $sth = $dbh->prepare("SELECT $index,$field FROM $table");
  my $q=$field."=?"; $q =~ s/\s*,\s*/=?, /g;
  my $inh = $dbh->prepare("UPDATE $table SET $q WHERE $index=?");

  $sth->execute;

  while (@row=$sth->fetchrow_array) {
   my $alt = 0;
    for (@row[1..$#row]) {
      $n++, $alt++ if (eval($regex));
    }
    if ($alt!=0) {
      print "    Key: $row[0], altered $alt times: ".join(', ',@row[1..$#row])."\n" if $debug>0;
      $inh->execute(@row[1..$#row],$row[0]) if $test<1;
    }
  }
  print "  $n rows altered\n";
}

print "\nend\n";

Be careful not to run the script twice once writing to the db is turned on - like I did :) it still matches /files/yimc as much as /files/ of course

Note from clara: the script is set to be run as a test first and therefire will not write the changes. if you know it runs the correct changes set it to write and do it again.