Using this method, you can import address books for your users or even populate the Global Address Book if you happen to have installed the plugin.
If you are using the Global Address Book plugin for Roundcube, you can populate the global address book with an import of a spreadsheet or database directly into the database. To do this, you will need to format you import file in a manner consistent with a .sql import script. This will allow you to forego creating all the entries by hand in the web interface.
If you are importing to the Global Address Book, the username is “[global_addressbook_user]”, you can skip the rest of this paragraph. If you are importing for a specific user, you will need to find the username in the database server but likely the user is the first part of the user’s email address. If you want to validate the user, log into the system-mysql database using the method in the next section and run the following queries:
use roundcubemail; select * from users;
You will need to export your data and create a text file (for example ‘import.sql’) that will have all the data that you intent to import. The format of an import will look like this if you are importing to the Global Address Book:
insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing','testing@example.com','testing','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]";
These are the valude that you will need in the query:
Example raw VCard
BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD
Here is a long example of a 5 user import (note, if you have trouble importing, you can split up your file and address syntax issues):
insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testingi','testing@example.com','testing','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing1','testing1@example.com','testing1','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example1 Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing2','testing2@example.com','testing2','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing3 testing','testing3@example.com','testing3','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing4 testing','testing4@example.com','testing4','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]";
If you are importing to a specific user (for example ‘bsmith’), you will replace “[global_addressbook_user]” with “bsmith”.
You will need the root password for the system mysql/mariadb database server. Run the following:
cat /var/clearos/system_database/root
Take note of this password. The output will look like this:
password = 1vzF9cGDdCcRo8ifjuhg1fjlgIJ4r2MgXL2GAMOz
Once your data is assembled, you will be able to import the file by using something similar to this:
/usr/clearos/sandbox/usr/bin/mysql -p roundcubemail < /root/import.sql
Inside the Roundcube interface, check your data to see if it is present.
Contacts deleted in the Roundcube interface are not truly deleted you can restore contacts in the table by updating the ‘del’ attribute.