PostgreSQL init script do not have `caldav_calendars` DDLs

[daincredibleholg]

I ran into a weird issue while trying out the installation of this plugin (alongside carddav). Roundcube's error log show me these lines whenever I try to add a calendar:

[28-Mar-2017 21:06:53 +0000]: <5gprk9u8> DB Error: [7] ERROR:  relation "caldav_calendars" does not exist
LINE 2:                 FROM caldav_calendars
                             ^ (SQL Query: SELECT *, calendar_id AS id
                FROM caldav_calendars
                WHERE user_id='1'
                ORDER BY name) in /var/www//htdocs/program/lib/Roundcube/rcube_db.php on line 539 (POST /?_task=calendar&_action=refresh)

Doing a quick grep for this table reveals the issue, the MySQL init scripts to have, the PHP code references it, but Postgres has no idea of what we are talking about:

htdocs $  grep -ir caldav_calendars | grep -v ^logs
plugins/calendar/drivers/caldav/SQL/mysql.initial.sql:CREATE TABLE IF NOT EXISTS `caldav_calendars` (
plugins/calendar/drivers/caldav/SQL/mysql.initial.sql:  CONSTRAINT `fk_caldav_calendars_user_id` FOREIGN KEY (`user_id`)
plugins/calendar/drivers/caldav/SQL/mysql.initial.sql:  REFERENCES `caldav_calendars`(`calendar_id`) ON DELETE CASCADE ON UPDATE CASCADE
plugins/calendar/drivers/caldav/SQL/mysql/2015022500.sql:CREATE TABLE IF NOT EXISTS `caldav_calendars` (
plugins/calendar/drivers/caldav/SQL/mysql/2015022500.sql:  CONSTRAINT `fk_caldav_calendars_user_id` FOREIGN KEY (`user_id`)
plugins/calendar/drivers/caldav/SQL/mysql/2015022500.sql:INSERT INTO caldav_calendars SELECT calendar_id, user_id, `name`, color, showalarms, url as caldav_url,
plugins/calendar/drivers/caldav/caldav_driver.php:    private $db_calendars = 'caldav_calendars';
plugins/calendar/drivers/caldav/caldav_driver.php:        $this->db_calendars = $this->rc->config->get('db_table_caldav_calendars', $db->table_name($this->db_calendars));
plugins/calendar/drivers/caldav/caldav_driver.php:        $hidden = array_filter(explode(',', $this->rc->config->get('hidden_caldav_calendars', '')));
plugins/calendar/drivers/caldav/caldav_driver.php:        $hidden = array_flip(explode(',', $this->rc->config->get('hidden_caldav_calendars', '')));
plugins/calendar/drivers/caldav/caldav_driver.php:        return $this->rc->user->save_prefs(array('hidden_caldav_calendars' => join(',', array_keys($hidden))));

Looks like this needs to be copied over to PostgreSQL as well?

[daincredibleholg]

Ok, on second glimpse, it appears that caldav doesn't support PostgreSQL at all :( I'll try and have a look at it, but maybe there is already something available or easy to fix for you?

[fasterit]

We got this from a user (updated postgres.initial.sql):

/**
 * CalDAV Client
 *
 * @version @package_version@
 * @author Hugo Slabbert <hugo@slabnet.com>
 *
 * Copyright (C) 2014, Hugo Slabbert <hugo@slabnet.com>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

CREATE TYPE caldav_type AS ENUM ('vcal','vevent','vtodo','');

CREATE SEQUENCE caldav_calendars_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE TABLE IF NOT EXISTS caldav_calendars (
  calendar_id integer DEFAULT nextval('caldav_calendars_seq'::regclass) NOT NULL,
  calendar_type caldav_type NOT NULL,
  user_id integer NOT NULL
        REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
  name character varying(255) NOT NULL, 
  color character varying(8) NOT NULL,
  showalarms smallint NOT NULL DEFAULT 1,
  caldav_url character varying(255) NOT NULL,
  caldav_tag character varying(255) DEFAULT NULL,
  caldav_user character varying(255) DEFAULT NULL,
  caldav_pass character varying(1024) DEFAULT NULL,
  caldav_last_change timestamp without time zone DEFAULT now() NOT NULL,
  PRIMARY KEY (calendar_id)
);

CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
BEGIN
    NEW.caldav_last_change = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER update_timestamp
  BEFORE INSERT OR UPDATE
  ON caldav_calendars
  FOR EACH ROW
  EXECUTE PROCEDURE upd_timestamp();

CREATE SEQUENCE caldav_events_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE TABLE IF NOT EXISTS caldav_events (
  event_id integer DEFAULT nextval('caldav_events_seq'::regclass) NOT NULL,
    calendar_id integer NOT NULL
        REFERENCES caldav_calendars (calendar_id) ON UPDATE CASCADE ON DELETE CASCADE,
    recurrence_id integer NOT NULL DEFAULT 0,
    uid varchar(255) NOT NULL DEFAULT '',
    instance varchar(16) NOT NULL DEFAULT '',
    isexception smallint NOT NULL DEFAULT '0',
    created timestamp without time zone DEFAULT now() NOT NULL,
    changed timestamp without time zone DEFAULT now(),
    sequence integer NOT NULL DEFAULT 0,
    "start" timestamp without time zone DEFAULT now() NOT NULL,
    "end" timestamp without time zone DEFAULT now() NOT NULL,
    recurrence varchar(255) DEFAULT NULL,
    title character varying(255) NOT NULL DEFAULT '',
    description text NOT NULL DEFAULT '',
    location character varying(255) NOT NULL DEFAULT '',
    categories character varying(255) NOT NULL DEFAULT '',
    url character varying(255) NOT NULL DEFAULT '',
    all_day smallint NOT NULL DEFAULT 0,
    free_busy smallint NOT NULL DEFAULT 0,
    priority smallint NOT NULL DEFAULT 0,
    sensitivity smallint NOT NULL DEFAULT 0,
    status character varying(32) NOT NULL DEFAULT '',
    alarms text DEFAULT NULL,
    attendees text DEFAULT NULL,
    notifyat timestamp without time zone DEFAULT NULL,
    caldav_url character varying(255) NOT NULL,
    caldav_tag character varying(255) DEFAULT NULL,
    caldav_last_change timestamp without time zone DEFAULT now() NOT NULL,
    PRIMARY KEY (event_id)
);

CREATE TRIGGER update_timestamp
  BEFORE INSERT OR UPDATE
  ON caldav_events
  FOR EACH ROW
  EXECUTE PROCEDURE upd_timestamp();

CREATE INDEX caldav_events_calendar_id_notifyat_idx ON caldav_events (calendar_id, notifyat);
CREATE INDEX caldav_events_uid_idx ON caldav_events (uid);
CREATE INDEX caldav_events_recurrence_id_idx ON caldav_events (recurrence_id);

CREATE SEQUENCE caldav_attachments_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE TABLE caldav_attachments (
    attachment_id integer DEFAULT nextval('caldav_attachments_seq'::regclass) NOT NULL,
    event_id integer NOT NULL
        REFERENCES caldav_events (event_id) ON DELETE CASCADE ON UPDATE CASCADE,
    filename character varying(255) NOT NULL DEFAULT '',
    mimetype character varying(255) NOT NULL DEFAULT '',
    size integer NOT NULL DEFAULT 0,
    data text NOT NULL DEFAULT '',
    PRIMARY KEY (attachment_id)
);

CREATE INDEX caldav_attachments_user_id_idx ON caldav_attachments (event_id);

INSERT INTO system (name, value) VALUES ('calendar-database-version', '2015022700');

Does it work for you? NB: We don't run pg with this ourselves but we'd be happy to commit this if you can confirm it works.

[daincredibleholg]

Hi ya.

So, did some testing and I got a bit further, but not quite to the end.

The DB related errors disappear, but it looks like something in the code is missing (not sure if this is related though). This is the exception I get:

[30-Mar-2017 09:59:57 UTC] PHP Fatal error:  Uncaught Error: Call to undefined method caldav_driver::insert_default_calendar() in /var/www/webmail/htdocs/plugins/calendar/calendar.php:182
Stack trace:
#0 /var/www/webmail/htdocs/program/lib/Roundcube/rcube_plugin_api.php(439): calendar->startup(Array)
#1 /var/www/webmail/htdocs/index.php(87): rcube_plugin_api->exec_hook('startup', Array)
#2 {main}
  thrown in /var/www/webmail/htdocs/plugins/calendar/calendar.php on line 182

This happens when I enable the default calendar (via $config['calendar_preinstalled_calendars']). Sadly, I can not enable debugging, as setting $config['calendar_caldav_debug'] = true; seems to do nothing and trying to disable the default isn't helping as the creation of new calendars doesn't work (I think there is an open issue for that).

Is there a way to enable debugging, so I can dive into it a bit more?

[daincredibleholg]

Alright, I did a test install with MySQL and can confirm that the behaviour is the same. So the not working + button and the error when using a default caldav calendar are bugs non-related to this issue. So I think it is save to say that the SQL works :)

[fasterit]

See issue #12 ... This is a known bug, you can either check out at commit 29c9d22 or apply PR #7.