Moving the files directory

Since the paths that drupal saves to its database are not relative to the files folder but relative from drupal's root or absolute, you have to update all the paths in the {files} table for the affected files.

Its a real pita. but doable with some simple scripting.

something like

  $files = select fid, path from {files}
  foreach $files as $file {
       $file['path'] = str_replace($oldpath, $newpath, $file['path'])
        update {files} set path = $file['path'] where fid=$file['fid'] 
  }

I see two problems here

1) identifying and moving the files

2) updating the database paths

Here is how I would solve them:

1) get a set of commands to move the files from the database with this query:

SELECT CONCAT('mv ',filepath,' ',REPLACE(filepath,'files/','sites/example.com/files/')) FROM FILES;

this will give you a response like:

mv files/file1.pdf sites/example.com/file2.pdf
mv files/file2.pdf sites/example.com/file2.pdf

Put that into a script file and execute it on the host in a shell session (assuming you don't have shell, copy/paste it into your ftp buffer)

2) run a single query to update the paths:

UPDATE FILES SET filepath = REPLACE(filepath,'files/','sites/example.com/files/');

I didn't test this, it's just theory and if you are using a non-standard upload method then YMMV in exactly what you have to do, but it should be easy enough to alter the queries to get your desired results.

Of course, I'd recommend doing this for a single file and/or on a test site before running it live. My paths may not fit your situation, but that seems like a trivial adjustment for your situation.

Whatever method you provide - I'd suggest contributing your final answer to the handbook.

--
Growing Venture Solutions
Drupal Implementation and Support in Denver, CO

Ran across the same issue for flexinode_file.inc, but it saves the hardcoded file in serialized_data, which is a pain. I did not want to do it by node, as I needed the node modification dates preserved, so whipped up the following. Used with the "execute PHP code" option in the devel block.

$sql = "select * from flexinode_data where field_id = 1";  //your field_id may vary
//print "SQL: '$sql'<BR />\n";  
$res = db_query($sql);
print "Number of hits: " . db_num_rows($res) . "<BR />\n";
while ($f = db_fetch_object($res)) {
    $s1 = unserialize($f->serialized_data);
    $s = str_replace('/old/path/to/files', '/new/path/to/files', $s1->filepath);
    print "OLD: '$s1->filepath' -> NEW: '$s'<BR />\n";
    $s1->filepath = $s;
    $sql = "UPDATE {flexinode_data} SET serialized_data = '%s' WHERE nid = %d and field_id = %d";
//print "SQL: '$sql'<BR />\n";    
    db_query($sql, serialize($s1), $f->nid, $f->field_id);
}
print 'DONE';

Clearly Drupal is doing the wrong thing here and that is because of its history as a single site only system. Everything should be easily portable between sites with none of these paths in the database.

One question is, how does Drupal move forward? Using relative paths is the sensible thing (so that Drupal looks for a file under /sites/xyz/files if that has been specified by $file_directory_path in settings.php). But that will break all existing installations when they upgrade.

Is there some place (like http://api.drupal.org/api/HEAD/function/file_create_url) where we can strip the "/file/" or "/sites/xzy/file/" prefix and replace it with $file_directory_path?

The suggestion above using a single SQL statement does _most_ of the job:

UPDATE FILES SET filepath = REPLACE(filepath,'files/','sites/example.com/files/');

However, be aware that (particularly in the variables table) there are also many links that need replacing. Furthermore, because of the way strings are stored in this table, you will need to not only alter the string, but also the recorded length of the string. It's painful on a big site (but not quite painful enough for me to actually write a script to do it...).

Just a pointer for whoever notices that e.g. their theme disappears when they move the files location, and perform the SQL query above, and it's still not working ;)

I've just done this by creating an SQL dump of my database (with drush 4.4), using a search and replace function in my text editor (vim) to replace all the hardcoded strings in the database, and then restoring the database (again, using drush). Here's how:

• I wanted to change my files directory from 'sites/default/files' to 'sites/example.com/files'.
• So, I first created the SQL dump file: drush -l example.com sql-dump --result-file=example.com.sql
• Then I created a copy of my dump file, in case I messed up: cp example.com.sql example.com.sql.bak
• Then I edited the dump file (vim example.com.sql) and ran the following commands:
  :%s/sites\/default\/files\//sites\/example.com\/files\//g
:%s/http:\/\/www.example.com\/sites\/default\/files\//http:\/\/www.example.com\/sites\/example.com\/files/g (this replaced inline links within the site's content.)
• Next, after saving the edited file (:wq), I re-imported the database from the edited dump file:
  drush sql-cli -l example.com < example.com.sql
• And finally, I cleared all caches to clean up: drush -l example.com cc all

Of course, although I use example.com in this write-up, I didn't do this on a live site! Also, take great care writing the vim commands above, because it's easy to make a mistake with all those escaped slashes.