BEGIN TRANSACTION;
/* Add 'driver' column to 'calendars' table */
DROP TABLE IF EXISTS temp_migration_caldav_calendars;
ALTER TABLE calendars ADD COLUMN driver varchar(255) NOT NULL default 'database';
INSERT OR REPLACE INTO system (name, value) VALUES ('calendar-database-version', '2022120700');
/* Migrate caldav_calendars to new schema, add entries to 'calendars' table */
CREATE TABLE temp_migration_caldav_calendars (
id integer NOT NULL PRIMARY KEY,
calendar_id integer NOT NULL,
readonly tinyint(1) NOT NULL DEFAULT '1',
caldav_url varchar(255) NOT NULL,
caldav_tag varchar(255) DEFAULT NULL,
caldav_user varchar(255) DEFAULT NULL,
caldav_pass varchar(1024) DEFAULT NULL,
caldav_oauth_provider varchar(255) DEFAULT NULL,
caldav_last_change timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_caldav_calendars_calendar_id FOREIGN KEY (calendar_id)
REFERENCES calendars(calendar_id) ON DELETE CASCADE
);
INSERT INTO calendars (user_id, name, color, showalarms, driver)
SELECT user_id, name, color, showalarms, "caldav" as driver FROM caldav_calendars;
/* This trigger is broken on older schema. Kill it and recreate it. */
DROP TRIGGER IF EXISTS UpdateLastTimeForCaldavCalendar;
CREATE TRIGGER UpdateLastTimeForCaldavCalendar
AFTER UPDATE
ON caldav_calendars
FOR EACH ROW
BEGIN
UPDATE caldav_calendars SET caldav_last_change = CURRENT_TIMESTAMP WHERE calendar_id = old.calendar_id;
END;
/* 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;
/* Update events table to reference proper fkey now that it is in calendars */
DROP TABLE IF EXISTS temp_caldav_events;
CREATE TABLE temp_caldav_events (
event_id integer NOT NULL PRIMARY KEY,
calendar_id integer NOT NULL,
recurrence_id integer NOT NULL DEFAULT '0',
uid varchar(255) NOT NULL DEFAULT '',
instance varchar(16) NOT NULL DEFAULT '',
isexception tinyint(1) NOT NULL DEFAULT '0',
created datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
changed datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
sequence integer NOT NULL DEFAULT '0',
start datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
end datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
recurrence varchar(255) DEFAULT NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
location varchar(255) NOT NULL DEFAULT '',
categories varchar(255) NOT NULL DEFAULT '',
url varchar(255) NOT NULL DEFAULT '',
all_day tinyint(1) NOT NULL DEFAULT '0',
free_busy tinyint(1) NOT NULL DEFAULT '0',
priority tinyint(1) NOT NULL DEFAULT '0',
sensitivity tinyint(1) NOT NULL DEFAULT '0',
status varchar(32) NOT NULL DEFAULT '',
alarms text NULL DEFAULT NULL,
attendees text DEFAULT NULL,
notifyat datetime DEFAULT NULL,
caldav_url varchar(255) NOT NULL,
caldav_tag varchar(255) DEFAULT NULL,
caldav_last_change timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uk_caldav_event UNIQUE (calendar_id,recurrence_id,uid,caldav_tag,instance),
CONSTRAINT fk_caldav_events_calendar_id FOREIGN KEY (calendar_id)
REFERENCES calendars(calendar_id)
);
DROP TRIGGER IF EXISTS UpdateLastTimeForCalDAVEvents;
DROP TABLE IF EXISTS caldav_events;
ALTER TABLE temp_caldav_events RENAME TO caldav_events;
CREATE TRIGGER UpdateLastTimeForCalDAVEvents
AFTER UPDATE
ON caldav_events
FOR EACH ROW
BEGIN
UPDATE caldav_events SET caldav_last_change = CURRENT_TIMESTAMP WHERE event_id = old.event_id;
END;
/* Considering what we inserted to calendars, a "naive/cross" join is in fact precisely
what we want to see below to repopulate the fixed table. */
INSERT INTO temp_migration_caldav_calendars (calendar_id, readonly, caldav_url, caldav_tag, caldav_user, caldav_pass, caldav_oauth_provider, caldav_last_change)
SELECT c.calendar_id, cc.readonly, cc.caldav_url, cc.caldav_tag, cc.caldav_user, cc.caldav_pass, cc.caldav_oauth_provider, cc.caldav_last_change
FROM calendars AS c JOIN caldav_calendars AS cc WHERE c.driver = "caldav";
/* Deal with old index that will soon be no longer valid */
DROP INDEX IF EXISTS caldav_user_name_idx;
/* Now take the plunge and update to the new version of the table */
DROP TABLE 'caldav_calendars';
ALTER TABLE 'temp_migration_caldav_calendars' RENAME TO 'caldav_calendars';
/* 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';
DROP TRIGGER IF EXISTS caldav_calendars_merged_insert;
DROP TRIGGER IF EXISTS caldav_calendars_merged_update;
DROP TRIGGER IF EXISTS caldav_calendars_merged_delete;
CREATE TRIGGER caldav_calendars_merged_insert
INSTEAD OF INSERT ON caldav_calendars_merged
BEGIN
INSERT INTO calendars (calendar_id, user_id, name, color, showalarms, driver)
VALUES (NEW.calendar_id, NEW.user_id, NEW.name, NEW.color, NEW.showalarms, NEW.driver);
INSERT INTO caldav_calendars (calendar_id, readonly, caldav_url, caldav_tag, caldav_user, caldav_pass, caldav_oauth_provider, caldav_last_change)
VALUES ((SELECT calendar_id FROM calendars ORDER BY calendar_id DESC LIMIT 1), NEW.readonly, NEW.caldav_url, NEW.caldav_tag, NEW.caldav_user, NEW.caldav_pass, NEW.caldav_oauth_provider, coalesce(NEW.caldav_last_change,CURRENT_TIMESTAMP));
END;
CREATE TRIGGER caldav_calendars_merged_update
INSTEAD OF UPDATE OF name, color, showalarms, readonly, caldav_url, caldav_tag, caldav_user, caldav_pass, caldav_oauth_provider, caldav_last_change ON caldav_calendars_merged
BEGIN
UPDATE calendars SET name=NEW.name, color=NEW.color, showalarms=NEW.showalarms WHERE calendar_id=NEW.calendar_id;
UPDATE caldav_calendars SET readonly=NEW.readonly, caldav_url=NEW.caldav_url, caldav_tag=NEW.caldav_tag, caldav_user=NEW.caldav_user, caldav_pass=NEW.caldav_pass, caldav_oauth_provider=NEW.caldav_oauth_provider, caldav_last_change=coalesce(NEW.caldav_last_change,CURRENT_TIMESTAMP);
END;
/* NOTE: I am only allowing deletion *by calendar_id*. Since that's what happens in code, I consider this acceptable. */
CREATE TRIGGER caldav_calendars_merged_delete
INSTEAD OF DELETE ON caldav_calendars_merged
BEGIN
DELETE FROM calendars WHERE calendar_id=NEW.calendar_id;
DELETE FROM caldav_calendars WHERE calendar_id=NEW.calendar_id;
END;
/* Delete iTIP related tables, as we don't use them anyways */
DROP TABLE IF EXISTS ical_calendars;
DROP INDEX IF EXISTS ical_user_name_idx;
DROP TRIGGER IF EXISTS UpdateLastTimeForiCalCalendar;
DROP TABLE IF EXISTS ical_events;
DROP INDEX IF EXISTS ical_uid_idx;
DROP INDEX IF EXISTS ical_recurrence_idx;
DROP INDEX IF EXISTS ical_calendar_notify_idx;
DROP TRIGGER IF EXISTS UpdateLastTimeForiCalEvents;
DROP TABLE IF EXISTS ical_attachments;
DELETE FROM system WHERE name='calendar-ical-version';
INSERT OR REPLACE INTO system (name, value) VALUES ('calendar-caldav-version', '2022120700');
END TRANSACTION;