1 Topic by hata_ph (edited by hata_ph 2010-06-02 13:35:40)

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Topic: Pull all email user from SQL to roundcube's global address book plugin

I am using roundcubemail-0.3.1 and I notice the global address book plugin is very useful and I was wondering is there possible to pull all the mail user to the global address list same as the ldap global address book? I am using MySQL as backend.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by hansolo

  • hansolo
  • Member
  • Offline
  • Registered: 2010-05-21
  • Posts: 26

Re: Pull all email user from SQL to roundcube's global address book plugin

not sure about Address book plugins (I'm not that far in exploring iRedMail yet)
but if this is what you're looking for -> RoundCube-Plugin-Global-Address-Book
try it.

3 Reply by hata_ph

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Pull all email user from SQL to roundcube's global address book plugin

I have already using the global address book plugin but i need to manually add in all my user's email address to it. What I am hoping is let iRedMail auto add/pull in my existing user to the global address database...

4 Reply by icedevil

  • icedevil
  • icedevil
  • Member
  • Offline
  • From: Bucharest, Romania
  • Registered: 2010-03-15
  • Posts: 51

Re: Pull all email user from SQL to roundcube's global address book plugin

hata_ph wrote:

I am using roundcubemail-0.3.1 and I notice the global address book plugin is very useful and I was wondering is there possible to pull all the mail user to the global address list same as the ldap global address book? I am using MySQL as backend.

logon to mysql or to phpmyadmin and run:

INSERT INTO roundcubemail.contacts (changed,del,name,email,firstname,surname,user_id)
SELECT CURDATE() as changed, '0' AS del,name,username,SUBSTRING_INDEX(username,'.',1) AS firstname,SUBSTRING_INDEX(SUBSTRING_INDEX(username,'@',1),'.',-1) AS lastname,'115' AS user_id FROM vmail.mailbox

< INFO >
I don't know if this is a global ID for global address book. First i inserted an user trough roundcube global addressbook interface, after this i cheked the inserted user_id value. CAUTION! You may have other id in here, please make one manual insert FIRST and check it.
I got '115' AS user_id .

The firstname and last name is extracted like this because my emails are like: firstname.lastname@domain.com

good luck!

5 Reply by hata_ph (edited by hata_ph 2010-06-04 09:11:32)

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Pull all email user from SQL to roundcube's global address book plugin

The SQL statement do work but it will not check for duplicate entry...I am not SQL guru so maybe someone can help on enhancing the script for checking duplicate entry...thanks

6 Reply by icedevil

  • icedevil
  • icedevil
  • Member
  • Offline
  • From: Bucharest, Romania
  • Registered: 2010-03-15
  • Posts: 51

Re: Pull all email user from SQL to roundcube's global address book plugin

by default, you can't have duplicate "username" in vmail.mailbox
If you reffer to roundcubemail.contacts ... it dosen't matter if you have duplicates with different user_id.

Regards

7 Reply by hata_ph

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Pull all email user from SQL to roundcube's global address book plugin

I mean when I run the sql script twice, there will have 2 set of duplicate address entry appear in the global address book...

8 Reply by icedevil

  • icedevil
  • icedevil
  • Member
  • Offline
  • From: Bucharest, Romania
  • Registered: 2010-03-15
  • Posts: 51

Re: Pull all email user from SQL to roundcube's global address book plugin

hata_ph wrote:

I mean when I run the sql script twice, there will have 2 set of duplicate address entry appear in the global address book...

you can delete all before u make update

USE vmail;
DELETE FROM roundcubemail.contacts  WHERE user_id = '115';
INSERT INTO roundcubemail.contacts (changed,del,name,email,firstname,surname,user_id)
SELECT CURDATE() as changed, '0' AS del,name,username,SUBSTRING_INDEX(username,'.',1) AS firstname,SUBSTRING_INDEX(SUBSTRING_INDEX(username,'@',1),'.',-1) AS lastname,'115' AS user_id FROM vmail.mailbox;

9 Reply by hata_ph

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Pull all email user from SQL to roundcube's global address book plugin

icedevil wrote:
hata_ph wrote:

I mean when I run the sql script twice, there will have 2 set of duplicate address entry appear in the global address book...

you can delete all before u make update

USE vmail;
DELETE FROM roundcubemail.contacts  WHERE user_id = '115';
INSERT INTO roundcubemail.contacts (changed,del,name,email,firstname,surname,user_id)
SELECT CURDATE() as changed, '0' AS del,name,username,SUBSTRING_INDEX(username,'.',1) AS firstname,SUBSTRING_INDEX(SUBSTRING_INDEX(username,'@',1),'.',-1) AS lastname,'115' AS user_id FROM vmail.mailbox;

The script work like a charm....thanks....

10 Reply by hata_ph

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Pull all email user from SQL to roundcube's global address book plugin

First manually add a email account into your roundcube address book...look for the user_id value...replace the 115 value with your own db value...then try again...

< INFO >
I don't know if this is a global ID for global address book. First i inserted an user trough roundcube global addressbook interface, after this i cheked the inserted user_id value. CAUTION! You may have other id in here, please make one manual insert FIRST and check it.
I got '115' AS user_id .

The firstname and last name is extracted like this because my emails are like: firstname.lastname@domain.com