Here are some SQL statements you can try. Lines that begin with "--"
are comments. This assumes the identities schema contains a unique
identity_id.
This SQL also assumes your roundcube instance supports a single domain: domain.us. If it supports multiple domains, you'll need to enhance the substring statements to strip the hostname and include only the domain name (which may be tricky if you have hostnames such as host1.domain1.co.uk along side host2.domain2.us).
First you'll want to query everything to know how it looks, then I recommend turning off autocommit so you can rollback your change if this doesn't work.
-- query what you are about to change SELECT identity_id, email FROM identities ORDER BY identity_id;
-- If using MySQL ... SET autocommit = 0;
UPDATE identities SET email = CONCAT(SUBSTRING_INDEX(email, '@', 1), '@domain.us') WHERE identity_id = identity_id
-- End if MySQL
-- Else if using PostgreSQL (psql commandline) ... \set AUTOCOMMIT off
UPDATE identities SET email = SUBSTRING(email FROM '^(.*)@') || '@domain.us' WHERE identity_id = identity_id;
-- End if Postgres
-- Re-query what you changed SELECT identity_id, email FROM identities ORDER BY identity_id;
-- issue either: ROLLBACK; -- if you don't like the changes COMMIT; -- or commit if everything looks good
Good luck. -gnul
On Mon, May 18, 2009 at 2:24 PM, Eden Caldas edencaldas@gmail.com wrote:
I'm not an sql expert but I'll at least give you some directions.
First you need to know the users
SELECT identity_id, email FROM identities
You will get some output like this:
identity_id email 1 someemail@host.domain.us 2 email@host.domain.us 3 otheremail@host.domain.us 4 someotheremail@host.domain.us
The SQL command to change the first identity number 1 would be:
UPDATE
roundcubemail.identitiesSETidentities.identity_id=1This example assumes the database name is roundcubemail.
I think you can do a mass change doing a joint sql command. You can also use some spreadsheet software or advanced text file editor to replicate the UPDATE command to the other users.
Hope this is a good start
MAKE BACKUPS!
Eden Caldas
2009/5/18 Carlos Williams carloswill@gmail.com
On Mon, May 18, 2009 at 3:01 PM, Eden Caldas edencaldas@gmail.com wrote:
Set your domain there, to make mail be sent as user@domain.us This setting will only be applied to new users because existing users already logged in roundcube once and got the wrong settings.
Thanks. I did think to change that but when I tested it, I saw no change but that makes sense because you stated it's for new users since RC got it wrong in MySQL already.
Since roundcube keeps its configuration in a database (mysql, postgres, sqlite) you can always use sql to mass change the existing settings for previous users. If you use mysql as your database, the web application phpmyadmin can help.
I don't have or use phpadmin so do you per chance happen to know how I can manually do this via MySQL? _______________________________________________ List info: http://lists.roundcube.net/users/
List info: http://lists.roundcube.net/users/
List info: http://lists.roundcube.net/users/