Batch edit one Goto Adress used in hundred of Aliases Adresses

[agaricus-campestris]

Hi,

Context :

I have ViMbAdmin V3.0.15, I know I need to update it, but I can't really do it right now.

Also, I'm not sure how this could be done on server-side or if there is a feature in newer versions, I couldn't find what I'm looking for with searching for « batch » words on Wiki & Git Issues, I'm sorry if I make you lose time here.

Question :

Is there any way, within Web Admin or on Command Line Server side, to batch edit one Goto adress that is used for a hundred Aliases Addresses ? I do have a lot of them so I really don't want to modify all that manually :s

Thanks a lot to anyone reading this, I hope my question is understandable as I'm not english native speaker,

[barryo]

Hi @ValentinDrean - I'm not sure what this feature would look like in the UI. But to confirm - you are correct - no such functionality exists.

If this were me, I think I'd handle with with a script and hit the database directly.

[agaricus-campestris]

Thank you @barryo so much for this super quick answer (◍•ᴗ•◍)

Even if you closed this, I respond here to show the method I used, so if there is anyone seeing this, maybe it'll help.
⚠️ Always dry-run / simulate sql queries when using UPDATE and double check values you're editing by using SELECT, otherwise you may be messing your ViMbAdmin database a lot ⚠️

I'm not a SQL-ninja but I managed to solve this by using replace() SQL function :

UPDATE `alias` SET `goto` = REPLACE(goto, 'A@XXX.org', 'newMailB@XXX.org');

THIS, was working because A@XXX.org was completely unique in my goto column, IF I had other adresses used in goto column like somethingA@XXX.org : replace() function would have changed it to newMailB@XXX.org, which we don't want because A@XXX.org & somethingA@XXX.org are not the same address.

So please be careful, as my solution may not work as expected 😨.

This would be similar if you wan't to remove one same address that appears in multiple goto rows, and this would even be harder because of commas everywhere.

PS : speaking of commas, is this normal database scheme having multiple values in a delimited string column (for goto) ? Sorry maybe this is a noob question.

Thanks again, hope this will help somehow,

[barryo]

PS : speaking of commas, is this normal database scheme having multiple values in a delimited string column (for goto) ? Sorry maybe this is a noob question.

Not a noob question but a very good one. The answer is no but this is essentially what we inherited from Postfix / Postfixadmin and we wanted to go for ease of migration way back when we started.

[barryo]

On the SQL, what I'd be inclined to do is have a script like this (php-ish / pseudo code):

$alias_address  = 'myalias@example.com';
$destinations = [get your destinations from SQL / API / file / etc and assume it's an array];

// I'm assuming you're getting the "new" destinations here - what you want it to be. 

// I'd iterate over destinations here and trim and ensure they were valid email addresses

// then I'd prep the SQL column:
$goto = implode( ',', $destinations);  // turns array into comma separate string

For the database side then I'd run a SQL query like:

UPDATE aliases SET goto = '$goto', modified = NOW() WHERE address = '$alias_address';