Moving the Mendeley Root Directory

I use Mendeley to organize PDFs and citation information for my collection of academic articles. Mendeley is great since it will attempt to extract citation information automatically from the PDF, supplementing with information from Google Scholar, other Mendeley users, and possibly other sources. I always check over the imported information and clean it up as necessary.

In any case, I recently got a new computer and am transitioning from Mac OS X to Ubuntu. Generally, Mendeley can just download all the citation information for my library to the new computer from its server, but I wanted to maintain the links to all the article PDFs. I suppose I could have asked Mendeley to upload all the PDFs to the server and then sync them on the new computer. But, I have no idea where it would have put the PDFs, and while I've shifted to putting most articles in a big, unsorted papers directory, I have "legacy" articles (from before Mendeley) that are in project folders all over the place—and I'd prefer to keep it that way.

The simplest way to maintain all the PDF locations would be to copy the Mendeley database (with all the local PDF locations) from the old computer to the new computer. The only problem was that Mac OS X and Ubuntu name their home directories differently—it's /Users in Mac OS X and /home in Ubuntu. (I'd have a similar problem if I needed to change usernames for some reason.) If I just copied the database over, all of my PDF links would be broken.

Fortunately, Mendeley uses an SQLite backend that is rather transparent, making it quite easy to fix all the PDF locations with a simple Perl script:

 #! perl  
   
 my $db = '[email-address]@www.mendeley.com.sqlite';  
 my $oldPrefix = '/Users';  
 my $newPrefix = '/home';  
   
 use DBI;  
   
 my $dbh = DBI->connect("dbi:SQLite:dbname=$db", '', '',  
             { RaiseError => 1, HandleError=>\&handle_error },)  
      or die $DBI::errstr;  
   
 my $sth = $dbh->prepare("SELECT * FROM files");  
 $sth->execute();  
   
 $sth->bind_columns(\my($hash, $url));  
 while ($sth->fetchrow_arrayref())  
 {  
  $url{$hash} = $url if ($url =~ s#^file://$oldPrefix#file://$newPrefix#);  
 }  
 $sth->finish();  
   
 for $hash (keys %url)  
 {  
  $url = $url{$hash};  
  $url =~ s/'/''/g;  
  $dbh->do("UPDATE files SET localUrl='$url' WHERE hash='$hash'");  
 }  
   
 $dbh->disconnect();  
   
 sub handle_error  
 {  
  my $error = shift;  
  print "Database error: $error\n";  
  return 1;  
 }  
   

Since I also have watched folders, I needed to update the watched file locations as well:

 #! perl  
   
 my $db = 'monitor.sqlite';  
 my $oldPrefix = '/Users';  
 my $newPrefix = '/home';  
   
 use DBI;  
   
 my $dbh = DBI->connect("dbi:SQLite:dbname=$db", '', '',  
             { RaiseError => 1, HandleError=>\&handle_error },)  
      or die $DBI::errstr;  
   
 my $sth = $dbh->prepare("SELECT name FROM monitoredfolders");  
 $sth->execute();  
 $sth->bind_columns(\my($dir));  
 while ($sth->fetchrow_arrayref())  
 {  
  push @dirs, $dir;  
 }  
 $sth->finish();  
   
 for $old (@dirs)  
 {  
  $old =~ s/'/''/g;  
  $new = $old;  
  $dbh->do("UPDATE monitoredfolders SET name='$new' WHERE name='$old'")  
   if ($new =~ s/^$oldPrefix/$newPrefix/);  
 }  
   
 my $sth = $dbh->prepare("SELECT name, directory FROM monitoredfiles");  
 $sth->execute();  
   
 $sth->bind_columns(\my($name, $dir));  
 while ($sth->fetchrow_arrayref())  
 {  
  $dir =~ s/^$oldPrefix/$newPrefix/;  
  $new = $name;  
  $file{$name} = [$new, $dir] if ($new =~ s/^$oldPrefix/$newPrefix/);  
 }  
 $sth->finish();  
   
 for $key (keys %file)  
 {  
  ($name, $dir) = @{$file{$key}};  
  $name =~ s/'/''/g;  
  $dir =~ s/'/''/g;  
  $key =~ s/'/''/g;  
  $dbh->do("UPDATE monitoredfiles SET name='$name', directory='$dir' WHERE name='$key'");  
 }  
   
 $dbh->disconnect();  
   
 sub handle_error  
 {  
  my $error = shift;  
  print "Database error: $error\n";  
  return 1;  
 }  
   

Problem solved!