Filepath fix for Drupal multisite installation

I can't believe this is still an issue in Drupal 6: whenever you move your site to a multisite installation (or whenever you want to rename the location where you sites files directory is), you need to hack the database. This has all changed now in Drupal 7 with the new fancy file support, but in the meantime for those still working with Drupal 6, I'd like to share the following tip. Drupal stores the path to the file relative to its root (where index.php is). This does not make sense to me since we can set where the files are located in the file system page at admin/settings/file-system on a per-site basis. Typically what you will find in the filepath column of the files table is something like "sites/default/files/newsletter3.pdf". It turns out that you can remove the entire prefix sites/default/files/ from this column and Drupal file figure out that it has to use the path you set in the filesystem page. So run the following SQL query from the SQL cli (use Drush for a better experience):

UPDATE `files` SET `filepath` = REPLACE(`filepath`, "sites/default/files/", "")

The only problem is that the new files will still be uploaded with the wrong hardcoded filepath, and if you move your site again, you will need to rerun this query :(

This post is based on and improves http://agaric.com/note/fixing-file-paths-after-a-change-location-a-sites...