Viewing File: /usr/local/cpanel/base/3rdparty/roundcube/plugins/calendar/SQL/mysql.2022120700.migration.sql

START TRANSACTION;

-- Add 'driver' column to 'calendars' table --
ALTER TABLE `calendars` ADD COLUMN `driver` varchar(255) NOT NULL default 'database';

-- Begin migration of caldav_calendars --
ALTER TABLE `caldav_calendars` DROP CONSTRAINT `fk_caldav_calendars_user_id`;
ALTER TABLE `caldav_events` DROP CONSTRAINT `fk_caldav_events_calendar_id`;

INSERT INTO `calendars` (`user_id`, `name`, `color`, `showalarms`, `driver`)
  SELECT `user_id`, `name`, `color`, `showalarms`, "caldav" as `driver` FROM `caldav_calendars`;
UPDATE `caldav_calendars` SET `calendar_id`=(SELECT `calendar_id` FROM `calendars` WHERE `driver`="caldav");
ALTER TABLE `caldav_calendars` MODIFY calendar_id int(11) UNSIGNED NOT NULL;
ALTER TABLE `caldav_calendars` DROP PRIMARY KEY;
ALTER TABLE `caldav_calendars` ADD COLUMN `id` int(11) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY;
ALTER TABLE `caldav_calendars` DROP COLUMN `user_id`;
ALTER TABLE `caldav_calendars` DROP COLUMN `name`;
ALTER TABLE `caldav_calendars` DROP COLUMN `color`;
ALTER TABLE `caldav_calendars` DROP COLUMN `showalarms`;

-- Add constraint last --
ALTER TABLE `caldav_calendars` ADD CONSTRAINT `fk_caldav_calendars_calendar_id` FOREIGN KEY (calendar_id) REFERENCES calendars(calendar_id) ON DELETE CASCADE;
ALTER TABLE `caldav_events` ADD CONSTRAINT `fk_caldav_events_calendar_id` FOREIGN KEY (calendar_id) REFERENCES calendars(calendar_id) ON DELETE CASCADE;

-- Blow away/update the cached event data to force refetch --
UPDATE `caldav_calendars` SET `caldav_last_change`=0;
DELETE FROM `caldav_events`;
DELETE FROM `caldav_attachments`;

-- Create a "writeable" view via triggers to simplify the code --
DROP VIEW IF EXISTS `caldav_calendars_merged`;
CREATE VIEW `caldav_calendars_merged` AS
  SELECT c.calendar_id, c.user_id, c.name, c.color, c.showalarms, c.driver, cc.readonly, cc.caldav_url, cc.caldav_tag, cc.caldav_user, cc.caldav_pass, cc.caldav_oauth_provider, cc.caldav_last_change, c.calendar_id AS id
  FROM `calendars` as c JOIN `caldav_calendars` as cc ON c.calendar_id = cc.calendar_id
  WHERE c.driver='caldav';

-- Delete iTIP related tables, as we don't use them anyways --
DROP TRIGGER IF EXISTS UpdateLastTimeForiCalEvents;
DROP TRIGGER IF EXISTS UpdateLastTimeForiCalCalendar;
DROP TABLE IF EXISTS ical_attachments;
DROP TABLE IF EXISTS ical_events;
DROP TABLE IF EXISTS ical_calendars;

REPLACE INTO `system` (`name`, `value`) VALUES ('calendar-caldav-version', '2022120700');

COMMIT;