Hi to all,
I managed to create a custom global address book in MYSQL.
This for anyone that is using SOGo client.
This is domain specific, so users in one domain will NOT be visible in another domain in a multi-domain environment.
In the Sogo Database create a table called contacts: below is the SQL statement for it:
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = “+00:00”;
– Table structure below:
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
c_uid text DEFAULT NULL,
c_name text DEFAULT NULL,
c_cn text DEFAULT NULL,
nsaimid text DEFAULT NULL,
mail varchar(128) NOT NULL,
mozillasecondemail text NOT NULL,
givenName text NOT NULL,
sn text NOT NULL,
telephoneNumber varchar(18) DEFAULT NULL,
homephone varchar(18) DEFAULT NULL,
mobile varchar(18) DEFAULT NULL COMMENT ‘Mobile’,
facsimiletelephonenumber varchar(18) DEFAULT NULL,
pager varchar(18) DEFAULT NULL,
mozillahomestreet text NOT NULL,
mozillahomestreet2 text NOT NULL,
mozillahomelocalityname text NOT NULL,
mozillahomestate text NOT NULL,
mozillahomepostalcode text NOT NULL,
mozillahomecountryname text NOT NULL,
title text NOT NULL,
ou text NOT NULL,
o text NOT NULL,
street text NOT NULL,
mozillaworkstreet2 text NOT NULL,
l text NOT NULL,
st text NOT NULL,
postalCode text NOT NULL,
c text NOT NULL,
birthyear int(11) NOT NULL,
birthmonth int(11) NOT NULL,
birthday int(11) NOT NULL,
description text NOT NULL,
photo longblob NOT NULL,
domain varchar(128) NOT NULL,
mozillahomeurl text DEFAULT NULL,
mozillaworkurl text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
== Below are two trigers to abdate a couple of files on inser and update.
DROP TRIGGER IF EXISTS adduiandcnname;
DELIMITER $$
CREATE TRIGGER adduiandcnname BEFORE INSERT ON contacts FOR EACH ROW BEGIN
set new.c_uid = new.mail, new.c_name = new.mail, new.c_cn = CONCAT(new.givenName, " ", new.sn), NEW.nsaimid = CONCAT(new.givenName, " ", new.sn);
end
$$
DELIMITER ;
DROP TRIGGER IF EXISTS updateUID;
DELIMITER $$
CREATE TRIGGER updateUID BEFORE UPDATE ON contacts FOR EACH ROW BEGIN
SET NEW.c_uid = NEW.mail, NEW.c_name = NEW.mail;
END
$$
DELIMITER ;
— Indexes for table contacts
ALTER TABLE contacts
ADD PRIMARY KEY (mail,domain);
The below list of field/columns/attributes do not show up.;
givenName text NOT NULL,
sn text NOT NULL,
telephoneNumber varchar(18) DEFAULT NULL,
birthyear int(11) NOT NULL,
birthmonth int(11) NOT NULL,
birthday int(11) NOT NULL,
photo longblob NOT NULL, <== Not concerned about this on it was just a trial to see if it works, I have set to blob but may be it need to be a text and pointing to a url, not much info about it.
I do not know why at this stage but trying to figure out the reason for it, most are of no real concern with the exception of this one 'telephoneNumber", others are just a nice to have but not critical.
Noe for the SOGo config.
SOGoUserSources = (
.
. other stuff…!!!
.
{
displayName = “Global Address Book”;
type = sql;
id = global_address_book;
viewURL = “mysql://sogo:bOLhJMp8AsFabEpnVJFePmnTKSH6x24J@127.0.0.1:3306/sogo/contacts”;
canAuthenticate = NO;
isAddressBook = YES;
listRequiresDot = NO;
SOGoEnableDomainBasedUID = YES;
DomainFieldName = “domain”;
},
.
. other stuff…!!!
.
);
Now to create a couple of PHP pages one for the admins to be able to Edit/Add/Delete records, and pone for the actual user to be able to update their own record only,.
Possibly create a DB trigger to add the user automatically ( basic information like email and name).
Will let you know who I go with this trigger.
This is an image of the SOGo Address book:
