How to get contact adress book from rouncube database

[sumesh]

Hi Chris,

If you have domain in old server (not yet removed then you can use the below commands to retrieve the email accounts in the csv format. 

I assume you have 3 email accounts under your domain like 

email.address1@freedomain.com
email.address2@freedomain.com
email.address3@freedomain.com

First you need to create a csv file using the below command 

===
echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address1.contacts.csv

echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address2.contacts.csv

echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address3.contacts.csv
===

Assume the db name of the roundcube is roundcubemail (if you have removed the domain in old server, then better restore the database to a new name like rouundcube_backup and we can generate the csv files from that backup as well) 

Now we need the id of each email account , please use the below commands

mysql > use roundcubemail;

mysql > select user_id from users where username='email.address1@freedomain.com';
+---------+
| user_id |
+---------+
|     335 |
+---------+
1 row in set (0.00 sec)

mysql > select user_id from users where username='email.address2@freedomain.com';
+---------+
| user_id |
+---------+
|     483 |
+---------+
1 row in set (0.00 sec)

mysql > select user_id from users where username='email.address3@freedomain.com';
+---------+
| user_id |
+---------+
|     583 |
+---------+
1 row in set (0.00 sec)

Once the Id's are ready, you can simply generate the email accounts address book using the below commands

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=335;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address1.contacts.csv

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=483;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address2.contacts.csv

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=583;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address3.contacts.csv

You have the required csv format backup and can use to import the address book in the respective email accounts via roundcube

Hope this helps