roundcube, sqlite2, ubuntu 11.10 and cherokee

Roundcube only supports sqlite2(RC v0.7.1). Unfortunately Ubuntu 11.10 doesn’t have sqlite2 available in the repos(not sure why, security?), and MySQL/PostgreSQL seems like an overkill for a single user roundcube installation.

Fix: install php5-sqlite and get sqlite2 from natty(11.04). The module sqlite.so is actually loaded by default(/etc/php5/cgi/conf.d/sqlite.ini) but missing in the oneiric package.

apt-get install php5-sqlite
wget http://security.ubuntu.com/ubuntu/pool/main/p/php5/php5-sqlite_5.3.5-1ubuntu7.7_i386.deb
dpkg -x php5-sqlite_5.3.5-1ubuntu7.7_i386.deb /tmp
cp /tmp/usr/lib/php5/20090626+lfs/sqlite.so /usr/lib/php5/20090626+lfs/
service cherokee restart

Reference: https://bugs.launchpad.net/ubuntu/+source/php5/+bug/875262

Roundcube and automatic addressbook

With automatic addressbook plugin on roundcube, I could not create the MYSQL table. I’m running Ubuntu 10.04 LTS, with mysql-server 5.1.41. Got error message:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--!40008
   --ON DELETE CASCADE
   --ON UPDATE CASCADE
)' at line 15

Found this solution at roundcubeforum.net.

Use this command to create table:

CREATE TABLE `collected_contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `firstname` varchar(128) NOT NULL,
  `surname` varchar(128) NOT NULL,
  `vcard` text,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`contact_id`),
  KEY `user_collected_contacts_index` (`user_id`,`email`),
  CONSTRAINT `collected_contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;