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 <[email protected]> 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 [email protected] > 2 [email protected] > 3 [email protected] > 4 [email protected] > > The SQL command to change the first identity number 1 would be: > > UPDATE `roundcubemail`.`identities` SET `email` = '[email protected]' > WHERE `identities`.`identity_id` =1 > > This 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 <[email protected]> >> >> On Mon, May 18, 2009 at 3:01 PM, Eden Caldas <[email protected]> wrote: >> > Set your domain there, to make mail be sent as [email protected] >> > 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/
