View source for SOGo

[[Category:201110]]
[[Category:Ubuntu]]
[[Category:Ubuntu Guide]]
[[Category:Groupware]]

See also:
* [[Raspberry Pi SOGo Groupware]]

Sources:
* http://www.sogo.nu
* http://www.linuxjournal.com/article/10894
* http://forum.zentyal.org/index.php?topic=3453.0
* http://wiki.debian.org/SOGo
* SOGo Installation Guide.pdf (1.3.8a)

SOGo is a  groupware solution for e-mail, calendar, tasks and contacts via a web interface

== Installation ==

Note: the Inverse repository (http://www.inverse.ca) is no longer (2016) a free service, a support contract is needed to get automatic updates.

Nightly builds might be the way to get new versions:
* https://sogo.nu/nc/support/faq/article/how-to-install-nightly-sogo-versions-on-ubuntu.html

Change log:
* https://sogo.nu/bugs/changelog_page.php
* https://sogo.nu/bugs/view_all_bug_page.php?filter=113607

Add key and source:

<syntaxhighlight lang=bash>
sudo apt-key adv --keyserver keys.gnupg.net --recv-key 0x810273C4
sudo apt-get update
sudo gedit /etc/apt/sources.list
</syntaxhighlight>

<blockquote>
<syntaxhighlight lang=bash>
#SOGo 3.x.x nightly repository
deb http://packages.inverse.ca/SOGo/nightly/3/ubuntu/ trusty trusty
</syntaxhighlight>
</blockquote>

Install sogo:

<syntaxhighlight lang=bash>
sudo apt-get update 
sudo apt-get install sogo
</syntaxhighlight>

== Configuration ==

=== Basic configuration ===

The system configuration is stored in the file /etc/sogo/sogo.conf. You can edit this file with a text-editor.

* The format of the configuration file is very sensitive: one type error and SOGo won't start with a 'dictionary error'.
* In earlier versions the configuration was stored in "/home/sogo/GNUstep/Defaults/.GNUstepDefaults", this file would still have precedence so make sure it is gone.
* Authentication 'type = sql' is (I think) the easiest to configure and shown here.

Backup the original file and keep it as an example:

<source lang=bash>
sudo cp /etc/sogo/sogo.conf /etc/sogo/sogo.conf.backup
</source>

Start editing:

<source lang=bash>
sudo gedit /etc/sogo/sogo.conf
</source>

Basic configuration (MySQL database and authentication 'type = sql'):

<source lang=bash>
  SOGoLanguage = English;
  SOGoTimeZone = Europe/Amsterdam;
  SOGoMailDomain = wilbertvolkers.linkpc.net;

  SOGoAppointmentSendEMailNotifications = NO;
  SOGoACLsSendEMailNotifications = NO;
  SOGoFoldersSendEMailNotifications = NO;

  SOGoProfileURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_user_profile";
  OCSFolderInfoURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_folder_info";
  OCSSessionsFolderURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_sessions_folder";
  OCSEMailAlarmsFolderURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_alarms_folder";

  SOGoUserSources = 
    (
      {
	type = sql; 
	id = directory; 
	displayName = "SOGo directory"; 
	viewURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_users";
	canAuthenticate = YES; 
	isAddressBook = YES; 
	userPasswordAlgorithm = md5; 
      }
    );
</source>

NB: used @127.0.0.1 in stead of @localhost for the sogo database user so we can disable DNS-lookup in the database server

=== Disable e-mail component ===

<source lang=bash>
sudo nano /etc/sogo/sogo.conf
</source>

<source lang=bash>
   //start with Calendar:
   SOGoLoginModule = Calendar;

   SOGoUserSources =
    (
      {
        type = sql;
        id = directory;
        viewURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_users";
        canAuthenticate = YES;
        isAddressBook = YES;
        userPasswordAlgorithm = md5;

        //Disable mail component:
        ModulesConstraints = {
          Mail = {
           objectClass = nonExistingClass;
          }
        }
      }
    );
</source>

== MySQL database ==

=== Create sogo database and user ===

Log in as [[MySQL]] root user and create a user ''sogo'' and a database ''sogo'', enter the password, identical to ''**sogodbpasswd**'' as mentioned above:

<syntaxhighlight lang=bash>
sudo mysql -u root mysql -p
</syntaxhighlight>

<blockquote>
<syntaxhighlight lang=sql>
CREATE DATABASE `sogo` CHARACTER SET='utf8';
CREATE USER 'sogo'@'127.0.0.1' IDENTIFIED BY '**sogodbpasswd**';
GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
\q
</syntaxhighlight>
</blockquote>

=== Create users ===

When you use authentication 'type = sql' then you need to create the users in de database.

<syntaxhighlight lang=bash>
sudo mysql -u sogo sogo -p
</syntaxhighlight>

<blockquote>
<syntaxhighlight lang=sql>
CREATE TABLE sogo_users (
	c_uid VARCHAR(10) PRIMARY KEY, 
	c_name VARCHAR(10), 
	c_password VARCHAR(32), 
	c_cn VARCHAR(128), 
	mail VARCHAR(128));
INSERT INTO sogo_users VALUES ('wilbertid', 'wilbertname', MD5('**wilbert-sogouser-pwd**'), 'Wilbert Volkers', 'wilbertemail@yahoo.com');
INSERT INTO sogo_users VALUES ('judyid', 'judyname', MD5('**judy-sogouser-pwd**'), 'Judy Xu', 'judyemail@yahoo.com');

---- update user password:
-- UPDATE sogo_users set c_password = MD5('pwd') where c_name='wilbert';
\q
</syntaxhighlight>
</blockquote>

=== Start or restart SOGo ===

<syntaxhighlight lang=bash>
sudo service sogo stop
sudo service sogo start
</syntaxhighlight>

You can test the configuration by going to http://127.0.0.1:20000/SOGo and logging in. For the correct operation SOGo must be accessed through Apache webserver.

== Apache webserver ==

=== Configure ===

[[Apache2 webserver]] HTTPS configuration, disable the default SOGo.conf. See also [[Apache2 configuration for SOGo and MediaWiki]].

<syntaxhighlight lang=bash>
sudo mv /etc/apache2/conf.d/SOGo.conf /etc/apache2/conf.d/SOGo.conf.backup
</syntaxhighlight>

To enable SSL you need to [[Create an Apache2 self signed certificate]]. In this example the files ''server.crt'' and ''server.key'' are used. To configure Apache create the following file:

To configure Apache2 proxy for both http NAT (wilbertvolkers.linkpc.net:80) and https WAN, repeat the virtual host with the below changes. Additionally you can configure your host file: [[Configure local DNS file]] to keep LAN requests within the LAN and keep everything working when the router is configured to not allow external access.

== Configure IMAP and SMTP server ==

The system configuration is stored in the file /etc/sogo/sogo.conf. You can edit this file with a text-editor, uncomment the options that you will use.

SOGo Installation Guide.pdf (1.3.8a) page 27

SMTP (no encryption):
<syntaxhighlight lang=bash>
  SOGoMailingMechanism smtp
  SOGoSMTPServer htc.smtp.mail.yahoo.com
</syntaxhighlight>

We can use the 'sendmail' -method as an alternative:

<syntaxhighlight lang=bash>
  SOGoMailingMechanism sendmail
  WOSendMail /usr/sbin/sendmail
</syntaxhighlight>

IMAP setup:

<syntaxhighlight lang=bash>
  SOGoDraftsFolderName Drafts
  SOGoSentFolderName Sent
  SOGoTrashFolderName Trash
  SOGoIMAPServer imaps://htc.imap.mail.yahoo.com:993
  SOGoForceIMAPLoginWithEmail YES
</syntaxhighlight>

Note: 
* The SOGo e-mail and account password match that of the login and password of the SMTP and IMAP server.
* The IMAP and SMTP server must be the same for all users
* SMTP TLS/SSL (port 465) is not yet supported (20111102)
* sendmail is insecure and I was not yet able to set it up

== E-Mail reminders ==

Enable the SOGoEnableEMailAlarms and set the OCSEMailAlarmsFolderURL

Create a cronjob (every minute) that will run under the sogo user
	https://help.ubuntu.com/community/CronHowto

An entry in the crontab should be defined like :

<syntaxhighlight lang=bash>
sudo crontab -e
 * * * * * /usr/sbin/sogo-ealarms-notify
</syntaxhighlight>

== SOGo-tool ==

Use the sogo-tool to perform administrative tasks

<source lang=bash>
sudo su - sogo
/usr/sbin/sogo-tool check-doubles user personal
</source>

<blockquote>
<source lang=bash>
2011-10-21 23:23:42.731 sogo-tool[13796] sogo-tool [-v|--verbose] [-h|--help] command [argument1] ...
  -v, --verbose	enable verbose mode
  -h, --help	display this help information

  argument1, ...	arguments passed to the specified command

  Available commands:
	restore	-- restore user folders
	backup	-- backup user folders
	user-preferences	-- set user defaults / settings in the database
	check-doubles	-- check user addressbooks with duplicate contacts
	remove-doubles	-- remove duplicate contacts from the specified user addressbook
	remove	-- remove user data and settings from the db
	rename-user	-- update records pertaining to a user after a change of user id
</source>
</blockquote>

== Android client ==

* https://f-droid.org/repository/browse/?fdfilter=davdroid&fdid=at.bitfire.davdroid

Notes:
* See [[Create_certificates_for_Apache2#Trust_self-signed_certificate_on_Android]] to allow use of a self-signed certificate
* Sony Xperia contacts, go to the settings menu and change the 'filter' settings, by default it will not show 'All other contacts'.

== Fixing problems with synchronization ==

When there is an error with the first synchronization (a SLOW-SYNC) you can end up with double contacts and double calendar items. Note that sometimes a SLOW-SYNC is done automatically after an error occurred. I use PIM backup on my mobile device and make backups of the [[MySQL]] database. 

The following should remove double items but often has problems in it:

<syntaxhighlight lang=bash>
sudo su - sogo
/usr/sbin/sogo-tool remove-doubles username personal
</syntaxhighlight>

=== Log files ===

If you have a problem check the log files for errors and use the internet to see if your problem has been solved, the log file can be found here:

* /var/log/sogo/sogo.log

Examples:
* ERROR Unknown column c_description in field list 
** https://lists.inverse.ca/sogo/arc/users/2015-05/msg00120.html

=== Database ===

I had some problems with getting a clean start with SOGo. So instead I removed the Calendar and Contact record from the table "sogo_folder_info", and I removed the user tables "sogo<username>...". These were newly created after logging into SOGo. The items do have to be on one of the other clients to save your data.

=== Database SQL, remove double items ===

Here are some statements to delete double address-book or calendar -items directly from the sogo database. Modify the tablenames ( wilbert<aclnumber1> and wilbert<aclnumber2>) to match your database, be careful and make a backup before you mess things up.

Double items can be caused by:
* sync-errors and retrying
* rewriting of the contact display field (c_cn) by synchronizers 
* ...

<syntaxhighlight lang=sql>
-- DID YOU MAKE A BACKUP?!

-- calendar

-- mark double calendar items in base table and set sync values
/*
update sogo.sogowilbert00664f71ae3 set 
    c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
    , c_deleted=1 
  where c_deleted is null and c_name in (
*/
    select distinct q2.c_name
      , q2.*
      from sogo.sogowilbert00664f71ae3_quick q1
           , sogo.sogowilbert00664f71ae3_quick q2
      where q1.c_name<q2.c_name
        and
        ( ifNULL(q1.c_startdate,'#'), ifNULL(q1.c_title,'#')
        , ifNULL(q1.c_enddate,'#'), ifNULL(q1.c_cycleenddate,'#')
        , ifNULL(q1.c_participants,'#'), ifNULL(q1.c_isallday,'#'), ifNULL(q1.c_iscycle,'#'), ifNULL(q1.c_cycleinfo,'#')
        , ifNULL(q1.c_classification,'#'), ifNULL(q1.c_isopaque,'#'), ifNULL(q1.c_status,'#'), ifNULL(q1.c_priority,'#') 
        , ifNULL(q1.c_location ,'#'), ifNULL(q1.c_orgmail,'#'), ifNULL(q1.c_partmails,'#'), ifNULL(q1.c_partstates,'#') 
        , ifNULL(q1.c_category ,'#'), ifNULL(q1.c_sequence,'#'), ifNULL(q1.c_component,'#'), ifNULL(q1.c_nextalarm,'#') )
        =
        ( ifNULL(q2.c_startdate ,'#'), ifNULL(q2.c_title,'#')
        , ifNULL(q2.c_enddate,'#'), ifNULL(q2.c_cycleenddate,'#')
        , ifNULL(q2.c_participants,'#'), ifNULL(q2.c_isallday,'#'), ifNULL(q2.c_iscycle,'#'), ifNULL(q2.c_cycleinfo,'#')
        , ifNULL(q2.c_classification,'#'), ifNULL(q2.c_isopaque,'#'), ifNULL(q2.c_status,'#'), ifNULL(q2.c_priority,'#') 
        , ifNULL(q2.c_location,'#'), ifNULL(q2.c_orgmail,'#'), ifNULL(q2.c_partmails,'#'), ifNULL(q2.c_partstates,'#')
        , ifNULL(q2.c_category,'#'), ifNULL(q2.c_sequence,'#'), ifNULL(q2.c_component,'#'), ifNULL(q2.c_nextalarm,'#') )
   )
  ;
  
-- delete marked calendar items from quick table
delete from sogo.sogowilbert00664f71ae3_quick where c_name in (
  select c_name from sogo.sogowilbert00664f71ae3 where c_deleted=1 )
  ;
</syntaxhighlight>

<syntaxhighlight lang=sql>
-- addressbook, 

-- double address-book items in base table
-- Note: many of the values are in c_content column (eg. notes), that column is not included in this comparison

/*
update sogo.sogowilbert0073981fbf8 set 
    c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
    , c_deleted=1 
  where c_deleted is null and c_name in (
*/
    select distinct q2.c_name
      , q2.*
      from sogo.sogowilbert0073981fbf8_quick q1
           , sogo.sogowilbert0073981fbf8_quick q2
      where q1.c_name<q2.c_name
        and
       ( ifNULL(q1.c_givenname,'#'), ifNULL(q1.c_sn,'#')
         -- , ifNULL(q1.c_cn,'#')
         , ifNULL(q1.c_screenname,'#'), ifNULL(q1.c_l,'#'), ifNULL(q1.c_mail,'#'), ifNULL(q1.c_o,'#')
         , ifNULL(q1.c_ou,'#'), ifNULL(q1.c_telephoneNumber,'#'), ifNULL(q1.c_categories,'#')
         , ifNULL(q1.c_component,'#') )
       =
       ( ifNULL(q2.c_givenname,'#'), ifNULL(q2.c_sn,'#')
         -- , ifNULL(q2.c_cn,'#')
         , ifNULL(q2.c_screenname,'#'), ifNULL(q2.c_l,'#'), ifNULL(q2.c_mail,'#'), ifNULL(q2.c_o,'#')
         , ifNULL(q2.c_ou,'#'), ifNULL(q2.c_telephoneNumber,'#'), ifNULL(q2.c_categories,'#')
         , ifNULL(q2.c_component,'#') )
      order by q2.c_cn
  ;

-- delete double calendar items from quick table
delete from sogo.sogowilbert0073981fbf8_quick where c_name in (
  select c_name from sogo.sogowilbert0073981fbf8 where c_deleted=1 )
  ;
</syntaxhighlight>

=== Database SQL, encoding problems ===

Using the text 'birthday dmy=28/..' in the contacts note field caused problems because the '=' is used as an escape character in the communication. The following lines were used as a workaround.

<syntaxhighlight lang=sql>
-- DID YOU MAKE A BACKUP?!

-- fix '=' problems
update sogo.sogowilbert0073981fbf8
  set c_content=replace(c_content, 'dmy=3D','dmy ')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  where c_content like '%dmy=3D%'
    and c_deleted is null
  ;

update sogo.sogowilbert0073981fbf8
  set c_content=replace(c_content, 'dmy=3D','dmy ')
  where c_content like '%dmy=3D%'
  ;
</syntaxhighlight>

=== Database SQL, timezone ===

Enabling the 'all day' event setting will remove the time component and the timezone, however, it seems that in some cases the ';' character will remain, causing all kinds of problems. In other words 'DTSTART;TZID=Europe/Amsterdam:20120604T123000' becomes 'DTSTART;:20120604' in stead of 'DTSTART:20120604'.

ds-server.log:

<pre>
... [funambol.sogo] [ERROR] ... [sogo-cal] Exception occured in vCalendarV2toV1(): java.lang.StringIndexOutOfBoundsException: String index out of range: 0
java.lang.StringIndexOutOfBoundsException: String index out of range: 0 at java.lang.String.charAt(Unknown Source) ...
</pre>

SQL fix:

<syntaxhighlight lang=sql>
-- DID YOU MAKE A BACKUP?!

-- fix 'DTSTART;:' and 'DTEND;:' problems (empty timezone)
SELECT * 
  FROM sogo.sogowilbert0073981fbf8
  WHERE c_content LIKE '%DTSTART;:%' or c_content LIKE '%DTEND;:%'
    AND c_deleted IS NULL
  ;
UPDATE sogo.sogowilbert0073981fbf8
  SET c_content=REPLACE(c_content, 'DTSTART;:','DTSTART:')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  WHERE c_content LIKE '%DTSTART;:%'
    AND c_deleted IS NULL
  ;
UPDATE sogo.sogowilbert0073981fbf8
  SET c_content=REPLACE(c_content, 'DTEND;:','DTEND:')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  WHERE c_content LIKE '%DTEND;:%'
    AND c_deleted IS NULL
  ;
 </syntaxhighlight>

=== Database SQL, text wrapping in c_content ===

Sometimes data is illegally broken over multiple lines causing this error, this happens for example with a contacts ''Additional Email''. Better leave this field empty. 

ds-server.log:

<pre>
..  Lexical error at line ... column ... Encountered: "\r" (13), after ...
</pre>

You could also try to make an SQL statement to find and remove these newlines from the c_content column.

Note the use of carriage return \r and linefeed \n.

<syntaxhighlight lang=sql>
select replace(c_content, 'SOME_TEXT:data\r\n', '')
  from sogowilbert01234
  where c_content like '%SOME_TEXT:data\r\n%'
    and c_name='<id>'
 ;

update sogowilbert01234
  set c_content=replace(c_content, 'SOME_TEXT:data\r\n', '')
  where c_content like '%SOME_TEXT:data\r\n%'
    and c_name='<id>'
 ;
</syntaxhighlight>

=== Database SQL, integer date representation ===

We are talking about the UNIXTIME which is the number of seconds since 1970. The MySQL function FROM_UNIXTIME( integer_date ) converts to a date but does not allow for negative values (pre 1970) the following works well in all cases.

<syntaxhighlight lang=sql>
select c_name, c_title, DATE_ADD(FROM_UNIXTIME(0), INTERVAL c_startdate SECOND) as startdate, c_isallday, c_cycleinfo
  from sogo.sogowilbert00664f71ae3_quick
  where lower(c_title) like '%valentijn%'
  ;
</syntaxhighlight>

=== Outlook VB script ===

NB: the Outlook script is not available here

I use a VB script in outlook to remove double items. There must be a better way though:

* Sync devices with funambol / sogo
* Sync Outlook with funambol / sogo
* Outlook VB Script to remove double items
* Clean funambol / sogo (does not remove the user, only the data)

<blockquote>
<syntaxhighlight lang=bash>
sudo su - sogo
/usr/sbin/sogo-tool remove <user>
exit
</syntaxhighlight>
</blockquote>

* Sync Outlook with funambol / sogo


To force a full sync on all the items, use the funamboladmin tool:

<syntaxhighlight lang=bash>
sudo /opt/Funambol/admin/bin/funamboladmin
</syntaxhighlight>

Go to 'Principles', click 'Search', double click the 'Principle'. Select each item-type and click 'Reset' to remove. This way you will force a 'SLOW SYNC'.

=== Beta version ===

Occurrence of double items where also reported here: 
* https://core.forge.funambol.org/ds/viewMessage.do?dsForumId=405&dsMessageId=154889

There it is suggested to use a newer (beta) version of the connector from the same server 'funambol-sogo-1.0.9.s4j'. Didn't solve the problem at the time though.

== Remove ==

=== Remove SOGo ===

<syntaxhighlight lang=bash>
sudo service sogo stop
</syntaxhighlight>

Edit pre removal script, change 'exit $?' to 'exit 0'

<syntaxhighlight lang=bash>
sudo gedit /var/lib/dpkg/info/sogo.prerm
</syntaxhighlight>

Remove:

<syntaxhighlight lang=bash>
sudo apt-get --purge autoremove sogo
</syntaxhighlight>

Remove key, first find it:

<syntaxhighlight lang=bash>
apt-key list
</syntaxhighlight>

The key will look like:

<blockquote>
<syntaxhighlight lang=bash>
pub   2048D/810273C4 2011-05-10
uid                  Inverse Support (package signing) <support@inverse.ca>
sub   2048g/BF6A9A6D 2011-05-10
</syntaxhighlight>
</blockquote>

Delete:

<syntaxhighlight lang=bash>
sudo apt-key del 810273C4
</syntaxhighlight>

=== Remove PostgreSQL ===

<syntaxhighlight lang=bash>
sudo /etc/init.d/postgresql stop
sudo apt-get --purge autoremove sope4.9-gdl1-postgresql
sudo apt-get --purge autoremove postgresql pgadmin3
</syntaxhighlight>

=== Remove funambol-sogo connector ===

<syntaxhighlight lang=bash>
sudo /opt/Funambol/bin/funambol stop
</syntaxhighlight>

Remove ",funambol-sogo-1.0.8" (without .s4j) at the end of the "modules-to-install" line:

<syntaxhighlight lang=bash>
sudo gedit /opt/Funambol/ds-server/install.properties 
</syntaxhighlight>

Note: this next action will reset the funambol database, all settings made with funamboladmin, users and userdata. Answer y to all questions until BUILD SUCCESFUL appears.

<syntaxhighlight lang=bash>
cd /opt/Funambol/
sudo ./bin/install-modules
</syntaxhighlight>

<syntaxhighlight lang=bash>
sudo /opt/Funambol/bin/funambol start
</syntaxhighlight>

== Other ==

In Apache2 error.log (/var/log/apahe2/error.log) this error was logged:
* PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php5/20090626/msql.so"  

To resolve this I created a link 'msql.so' to 'mysql.so' in the same directory.

== PostgreSQL database (not updated) ==

PostgreSQL database and gui

<syntaxhighlight lang=bash>
sudo apt-get install postgresql pgadmin3
</syntaxhighlight>

sogo - postgresql-jdbc connector
<syntaxhighlight lang=bash>
sudo apt-get install sope4.9-gdl1-postgresql
</syntaxhighlight>

Create database, use “sogo” as password

<syntaxhighlight lang=bash>
sudo su - postgres
createuser --no-superuser --no-createdb --no-createrole --encrypted --pwprompt sogo
createdb -O sogo sogo
sudo gedit /var/lib/postgresql/data/pg_hba.conf
</syntaxhighlight>

 #20111009 wjv add:
 host    sogo        sogo        127.0.0.1/32          md5

<syntaxhighlight lang=bash>
sudo service postgresql restart
</syntaxhighlight>

Create tables:

<syntaxhighlight lang=bash>
sudo su - sogo
psql
</syntaxhighlight>

<syntaxhighlight lang=sql>
CREATE TABLE sogo_users (
	c_uid VARCHAR(10) PRIMARY KEY, 
	c_name VARCHAR(10), 
	c_password VARCHAR(32), 
	c_cn VARCHAR(128), 
	mail VARCHAR(128));
INSERT INTO sogo_users VALUES ('wilbert', 'wilbert', MD5('su***'), 'Wilbert Volkers', 'wilbertvolkers@hotmail.com');
INSERT INTO sogo_users VALUES ('judy', 'judy', MD5('xu'), 'Judy Xu', 'judyxu1221@yahoo.com');
--UPDATE sogo_users set c_password = MD5('pwd') where c_uid='id';
\q
</syntaxhighlight>

<syntaxhighlight lang=bash>
SOGoUserSources = ({
	type = sql; 
	id = directory; 
	displayName = "SOGo directory"; 
	viewURL = "postgresql://sogo:sogo@127.0.0.1:5432/sogo/sogo_users";
	canAuthenticate = YES; 
	isAddressBook = YES; 
	userPasswordAlgorithm = md5; 
	});
</syntaxhighlight>

<syntaxhighlight lang=bash>
SOGoProfileURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_user_profile';
OCSFolderInfoURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_folder_info';
OCSSessionsFolderURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_sessions_folder';
OCSEMailAlarmsFolderURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_alarms_folder';

#sql authentication superusers (http://www.mail-archive.com/users@sogo.nu/msg01988.html)
SOGoSuperUsernames='("admin","wilbert")';
</syntaxhighlight>

== SOGo - Funambol connector (not updated) ==

[[Funambol]] allows you to synchronize data between different clients. For example SOGo, Outlook, Windows mobile, Evolution.

For support:
* http://wiki.contribs.org/Funambol
* http://wiki.contribs.org/Funambol#Create_SOGo_Sync_Sources

For the latest version and documentation check here:
* http://www.inverse.ca/english/contributions/funambol.html

=== Install ===

<syntaxhighlight lang=bash>
sudo -i
cd /opt/Funambol/ds-server/modules
wget http://www.sogo.nu/uploads/Funambol/funambol-sogo-1.0.8.s4j
exit
</syntaxhighlight>

Edit:

<syntaxhighlight lang=bash>
sudo gedit /opt/Funambol/ds-server/install.properties 
</syntaxhighlight>

<blockquote>
<syntaxhighlight lang=bash>
...
#
# Modules definitions
#
modules-to-install=content-provider-10.0.0,email-connector-10.0.0,foundation-10.0.0,phones-support-10.0.0,webdemo-10.0.0,funambol-sogo-1.0.8
</syntaxhighlight>
</blockquote>

Note: this next action will reset the funambol database, press y until you get ''BUILD SUCCESFUL'':

<syntaxhighlight lang=bash>
cd /opt/Funambol/
sudo ./bin/install-modules
</syntaxhighlight>

=== Install JSON ===

<syntaxhighlight lang=bash>
sudo -i
cd /opt/Funambol/tools/tomcat/lib/
wget http://json-simple.googlecode.com/files/json_simple-1.1.jar
exit
</syntaxhighlight>

=== Configure ===

Start funamboladmin:

<syntaxhighlight lang=bash>
sudo /opt/Funambol/admin/bin/funamboladmin
</syntaxhighlight>

The ''officer'' will auto-create Funambol user accounts for every SOGo user. Go to 'Server settings' (double click) -> 'Engine' -> 'Officer' change from:

<blockquote>
<syntaxhighlight lang=xml>
com/funambol/server/security/UserProvisioningOfficer.xml
</syntaxhighlight>
</blockquote>

into:

<blockquote>
<syntaxhighlight lang=xml>
ca/inverse/sogo/security/SOGoOfficer.xml
</syntaxhighlight>
</blockquote>

Go to 'Modules' -> 'sogo' -> 'FunambolSOGoConnector' -> 'SOGo SyncSource' (double click) and add three SyncSources. Fill-in the values and click 'Add'.

<blockquote>
{|cellspacing="0" border="1"
|-
!
!card
!cal
!todo
|-
|Source URI:
|sogo-card
|sogo-cal
|sogo-todo
|-
|Name:
|sogo-card
|sogo-cal
|sogo-todo
|-
|Supported type:
|text/x-vcard
|text/x-vcal
|text/x-vtodo
|-
|Database URL:
|colspan="3"|jdbc:mysql://localhost/sogo
|-
|Db username:
|colspan="3"|sogo
|-
|Db password:
|colspan="3"|sogo
|}
</blockquote>

<syntaxhighlight lang=bash>
sudo gedit /opt/Funambol/config/ca/inverse/sogo/security/SOGoOfficer.xml
</syntaxhighlight>

<blockquote>
<syntaxhighlight lang=xml>
...
<void property="host">
    <string>localhost</string>
</void>
<void property="port">
    <string>20000</string>
</void>
...
</syntaxhighlight>
</blockquote>

=== Restart ===

<syntaxhighlight lang=bash>
sudo /etc/init.d/sogo stop
sudo /opt/Funambol/bin/funambol stop
sudo /opt/Funambol/bin/funambol start
sudo /etc/init.d/sogo start
</syntaxhighlight>