Updating WordPress site URLs for different hosts

Why oh why does WordPress insist on storing the site URLs within the database?
Every other project I can think of either keeps it in a config file or doesn’t bother with one and just works with whatever URL it finds itself on.

This is most irritating when you have:

• more than one developer working on the same project
• more than one system for hosting the project (dev, test, live, etc)

One way to cope with this irritation is to have a collection of .sql files to handily update the database from the CLI.

For example, a WP site has the URL www.idophp.co.uk on a live host and uses idophp.iain on a dev host.

We would have 2 .sql files such as live_options.sql and dev_options.sql.

In live_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://idophp.iain', 'http://www.idophp.co.uk') WHERE option_value LIKE 'http://idophp.iain%';

In dev_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.idophp.co.uk', 'http://idophp.iain') WHERE option_value LIKE 'http://www.idophp.co.uk%';

These settings can be imported over the top of a database without altering anything but the site URLs using:

$ mysql -u idophp -p idophp < live_options.sql