Ability to change username

[TabulateJarl8]

Hey! First of all, thanks for all your work, I love this project. However, I was wondering how difficult it would be to implement username changing. I'm not really familiar with go or else I'd do it myself, but I figured that I would propose the feature in case it was something of interest to you.

The reason this came up was that I used a different username from my GitHub on my personal instance, and I'm thinking of changing the avatar backend to fetch from GitHub instead of Gravatar. Thanks for all your work!

[muety]

Hi @TabulateJarl8, I'm glad you like Wakapi! Thanks for bringing this up.

My first thought was that changing the username is too difficult, because the it's is also used as the user's primary key across the whole application and thus is quite critical. However, I now had a deeper look into it and, thanks to the fact that we're using ON UPDATE CASCADE almost everywhere, it should actually be quite straightforward.

For MySQL, we'd essentially just need:

 -- change primary key and all foreign key refs
update users set id = 'newuser' where id = 'olduser';
-- update kv table which doesn't use foreign keys
-- fixes "sub_reminder_olduser", "first_heartbeat_olduser", "last_successful_import_olduser"
update key_string_values set `key` = regexp_replace(`key`, '(.+)_olduser$', '$1_newuser') where `key` regexp '.+_olduser$'

For SQLite, things are a bit more complex. First of all, you must not forget to run pragma foreign_keys = ON; before the actual query. But moreover, there is to regexp group replacement function in SQLite (not sure about Postgres), so we'd either have to update the entries in key_string_values programmatically or simple delete them, which, I think, is acceptable.

delete from key_string_values where key like '%_olduser';

Afterwards, it's important to clear the cache of UserService.

Will add this as a low-prio feature request and implement it at some point.

PS: If you're super keen, feel free to do some research what the Postgres- and SQL Server equivalent of the above regexp_replace query are.

[TabulateJarl8]

Thanks so much! I'll check everything out later this week when I'm free

[TabulateJarl8]

Seems that Postgres also has REGEXP_REPLACE(source, pattern, replacement_string,[, flags]).

SQL Server doesn't seem to have a direct equivalent; most people say that for real regex support you need to create a custom CLR function in C# which seems a little overkill for this. However if all we need is a simple wildcard, SQL Server has a LIKE keyword which uses % as an equivalent to .* in regex, which is pretty close. I'm not too familiar with SQL Server, but maybe something similar to this?

UPDATE key_string_values
SET key = REPLACE(key, '_olduser', '_newuser')
WHERE key LIKE '%_olduser'

I wouldn't trust that to work and haven't tested it, but maybe it's at least close. Note that this also doesn't have an equivalent to $ so that would be sacrificed as well.