wiki:RenameUsersScript

Renaming user accounts in the database

Description

The following script renames user accounts in a SQLite database. This is particularly useful when you imported a database from Bugzilla, which uses e-mail addresses as user names, and want to use different user names in your Trac or Subversion.

The original version showed how to do it with a canned list of old and new names in the script. This version accepts an old name and a new name on the command line, making sure there are two arguments before proceeding. This seems more immediately useful to me.

#!/bin/bash

if [ $# -ne 2 ]
then
  echo "Usage: renameuser oldname newname"
  exit 1
fi

OLDNAME=$1
NEWNAME=$2

sqlite3 trac.db <<EOF
UPDATE "permission" SET username='$NEWNAME' WHERE username='$OLDNAME';
UPDATE "auth_cookie" SET name='$NEWNAME' WHERE name='$OLDNAME';
UPDATE "session" SET sid='$NEWNAME' WHERE sid='$OLDNAME';
UPDATE "session_attribute" SET sid='$NEWNAME' WHERE sid='$OLDNAME';
UPDATE "wiki" SET author='$NEWNAME' WHERE author='$OLDNAME';
UPDATE "attachment" SET author='$NEWNAME' WHERE author='$OLDNAME';
UPDATE "ticket" SET owner='$NEWNAME' WHERE owner='$OLDNAME';
UPDATE "ticket" SET owner='$NEWNAME' WHERE owner='$OLDNAME';
UPDATE "ticket" SET reporter='$NEWNAME' WHERE reporter='$OLDNAME';
UPDATE "ticket_change" SET author='$NEWNAME' WHERE author='$OLDNAME';
UPDATE "ticket_change" SET newvalue='$NEWNAME' WHERE newvalue='$OLDNAME' AND (field='qa_contact' OR field='owner' OR field='reporter');
UPDATE "ticket_change" SET oldvalue='$NEWNAME' WHERE oldvalue='$OLDNAME' AND (field='qa_contact' OR field='owner');
UPDATE "component" SET owner='$NEWNAME' WHERE owner='$OLDNAME';
EOF

Help, I'm quite new to TracWiki. Could somebody please help me with the double quote and ampersand characters? Thanks! --langec

Done, you don't need to escape them, Trac takes care of it -- Lucas Bonnet

PL/SQL version

For people using Postgresql as their database backend, here is a PL/SQL function.

CREATE FUNCTION trac_rename(text, text) RETURNS void AS $$
DECLARE
  oldv ALIAS FOR $1;
  newv ALIAS FOR $2;

  oldvre text := '\y' || oldv || '\y';
BEGIN
  UPDATE report            SET author   = newv WHERE author   = oldv;
  UPDATE ticket_change     SET author   = newv WHERE author   = oldv;
  UPDATE revision          SET author   = newv WHERE author   = oldv;
  UPDATE wiki              SET author   = newv WHERE author   = oldv;
  UPDATE attachment        SET author   = newv WHERE author   = oldv;
  UPDATE component         SET owner    = newv WHERE owner    = oldv;
  UPDATE ticket            SET owner    = newv WHERE owner    = oldv;
  UPDATE ticket            SET reporter = newv WHERE reporter = oldv;
  UPDATE ticket_change     SET newvalue = newv WHERE newvalue = oldv AND (field='qa_contact' OR field='owner' OR field='reporter');
  UPDATE ticket_change     SET oldvalue = newv WHERE oldvalue = oldv AND (field='qa_contact' OR field='owner' OR field='reporter');
  UPDATE permission        SET username = newv WHERE username = oldv;
  UPDATE auth_cookie       SET name     = newv WHERE name     = oldv;
  UPDATE session           SET sid      = newv WHERE sid      = oldv;
  UPDATE session_attribute SET sid      = newv WHERE sid      = oldv;

  UPDATE ticket
    SET   cc = regexp_replace(cc, oldvre, newv, 'g')
    WHERE (SELECT count(1) FROM regexp_matches(cc, oldvre)) <> 0;

  UPDATE ticket_change
    SET   newvalue = regexp_replace(newvalue, oldvre, newv, 'g')
    WHERE (SELECT count(1) FROM regexp_matches(newvalue, oldvre)) <> 0
      AND (field='cc');

  UPDATE ticket_change
    SET   oldvalue = regexp_replace(oldvalue, oldvre, newv, 'g')
    WHERE (SELECT count(1) FROM regexp_matches(oldvalue, oldvre)) <> 0
      AND (field='cc');

END;
$$ LANGUAGE plpgsql;

Bugs/Feature Requests

Existing bugs and feature requests for RenameUsersScript are here.

If you have any issues, create a new ticket.

Download

Download the zipped source from here.

Source

You can check out RenameUsersScript from here using Subversion, or browse the source with Trac.

Example

$ cd trac-env/db $ rename-users

Recent Changes

[1973] by langec on 2007-02-14 16:49:32
New hack RenameUsersScript, created by langec

Author/Contributors

Author: langec
Maintainer: langec
Contributors:

Last modified 4 days ago Last modified on Oct 22, 2014 4:14:06 PM