Renaming projects in DrProject

After going through one DrProject project rename, I thought I might document the process for the next poor soul who needs to do the same.

0: Backup your database

You weren't thinking of doing this without a backup, were you?vv

$ pg_dump $DATABASE > drp_db_dump

And, while we're here, we might as well stop the server, just to make sure no one mucks with our instance while we're doing the rename:

$ /etc/init.d/drproject stop

1: Update the project name in the database

Unfortunately this is not as simple as a simple "update" because DrProject's database does not have "ON UPDATE CASCADE" set. Fortunately, however, it DOES have "ON DELETE CASCADE" set, so we can just find all of those and add the UPDATE case in there too.

First, find all the existing CASCADE statements:

$ grep -A1 '^ALTER TABLE' drp_db_dump | grep -B1 CASCADE > drp_constraints

The drp_constraints file should look like this:

...
--
ALTER TABLE ONLY ticket
    ADD CONSTRAINT ticket_type_whatfor_type_name_fk FOREIGN KEY (type_whatfor, type_name) REFERENCES enums(whatfor, name) ON DELETE CASCADE;
--
...

Now it's time to make a script that will remove all these constraints, then re-add them with an "ON UPDATE CASCADE".

First, check that, when you run the command below, the output matches what I've got:

$ cat drp_constraints | grep -v '^--' | sed 'N;s/\n / /;P;D;'
ALTER TABLE ONLY attachment    ADD CONSTRAINT attachment_project_name_fk FOREIGN KEY (project_name) REFERENCES project(name) ON DELETE CASCADE;
ALTER TABLE ONLY dashboard_metrics    ADD CONSTRAINT dashboard_metrics_project_name_fk FOREIGN KEY (project_name) REFERENCES project(name) ON DELETE CASCADE;
ALTER TABLE ONLY dashboard_values    ADD CONSTRAINT dashboard_values_metric_id_fk FOREIGN KEY (metric_id) REFERENCES dashboard_metrics(id) ON DELETE CASCADE;
...
$

Then go for it:

$ cat drp_constraints | grep -v '^--' | sed 'N;s/\n / /;P;D;' | \
  awk '{ print "ALTER TABLE ONLY " $4 " DROP CONSTRAINT " $7 ";";
         print substr($0, 1, length($0)-1) " ON UPDATE CASCADE;";}'
  ALTER TABLE ONLY attachment DROP CONSTRAINT attachment_project_name_fk;
  ALTER TABLE ONLY attachment    ADD CONSTRAINT attachment_project_name_fk FOREIGN KEY (project_name) REFERENCES project(name) ON DELETE CASCADE ON UPDATE CASCADE;
  ALTER TABLE ONLY dashboard_metrics DROP CONSTRAINT dashboard_metrics_project_name_fk;
  ALTER TABLE ONLY dashboard_metrics    ADD CONSTRAINT dashboard_metrics_project_name_fk FOREIGN KEY (project_name) REFERENCES project(name) ON DELETE CASCADE ON UPDATE CASCADE;

(ha! I bet you never knew you could do that with sed!)

Great, so it all worked? Save it to a file!

$ !! > drp_constraint_fix

Finally, throw all that inside a transaction, add the "UPDATE TABLE" and we're all done!

$ echo 'BEGIN;' > drp_constraint_final
$ cat drp_constraint_fix >> drp_constraint_final
$ echo "UPDATE project SET name='!!!! NEW NAME !!!!' where name='!!!! OLD NAME !!!!'" >> drp_constraint_final
$ echo "COMMIT;" >> drp_constraint_final

Check that the drp_constraint_final file looks correct... Then load it in:

$ psql $DATABASE < drp_constraint_final

And, with any luck, you shouldn't get any errors and everything will be happy

2: Rename the Subversion repository

You will also have to rename the projects subversion repository:

$ cd $DrProject_ENVIRONMENT/repos/
$ mv $OLD_NAME $NEW_NAME

3: Update any links which may point to the old project

Stop for a minute and try to think if there are any links which may point to the old project... If there are, they may be worth updating.

4: You're done!

Phew!

Addendum

It's worth noting that renaming a project is not very easy because the technical difficulties have always outweighed the usefulness: I've been working with DrProject for almost three years now, and this is the first time (in memory) I've had to rename a project.

I estimate that it would take a bit less than a working week to properly implement this operation (that would include: hacking around SQLAlchemy's aversion to changing primary keys, figuring out a "sensible" thing to do when links point to a renamed project and then testing everything with both SQLite and Postgres), when Alan (our incredible sysadmin) and I were able to bang this procedure out in less than an hour.

It is also worth noting that this procedure was so very "easy" because Postgres ensures referential integrity (ie, it won't let you mess up your data). Had we been using a database like MySQL/MyISAM or SQLite which doesn't do this, life would have been a bit more difficult.

PS

Check out DrProject's successor, Basie! 100% re-written in Django, Basie is cooler than ever!