5 * @param int $from_version
6 * @return array|string[]
8 function dav_get_update_statements($from_version)
12 if ($from_version == 1) {
13 $stms[] = "ALTER TABLE `dav_calendarobjects`
14 ADD `calendar_id` INT NOT NULL AFTER `namespace_id` ,
15 ADD `user_temp` INT NOT NULL AFTER `calendar_id` ";
16 $stms[] = "ALTER TABLE `dav_calendarobjects`
17 ADD `componentType` ENUM( 'VEVENT', 'VTODO' ) NOT NULL DEFAULT 'VEVENT' AFTER `lastmodified` ,
18 ADD `firstOccurence` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER `componentType` ,
19 ADD `lastOccurence` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER `firstOccurence`";
20 $stms[] = "UPDATE dav_calendarobjects a JOIN dav_calendars b ON a.namespace = b.namespace AND a.namespace_id = b.namespace_id SET a.user_temp = b.uid";
21 $stms[] = "DROP TABLE IF EXISTS
22 `dav_addressbooks_community` ,
23 `dav_addressbooks_phone` ,
24 `dav_cache_synchronized` ,
27 `dav_cal_virtual_object_cache` ,
31 `dav_notifications` ;";
33 $stms = array_merge($stms, dav_get_create_statements(["dav_calendarobjects"]));
35 $user_ids = q("SELECT DISTINCT `uid` FROM %s%scalendars", CALDAV_SQL_DB, CALDAV_SQL_PREFIX);
36 foreach ($user_ids as $user) $stms = array_merge($stms, wdcal_create_std_calendars_get_statements($user["uid"], false));
38 $stms[] = "UPDATE dav_calendarobjects a JOIN dav_calendars b
39 ON b.`namespace` = " . CALDAV_NAMESPACE_PRIVATE . " AND a.`user_temp` = b.`namespace_id` AND b.`uri` = 'private'
40 SET a.`calendar_id` = b.`id`";
42 $stms[] = "ALTER TABLE `dav_calendarobjects` DROP `namespace`, DROP `namespace_id`, DROP `user_temp`";
46 if (in_array($from_version, [1, 2])) {
47 $stms[] = "CREATE TABLE IF NOT EXISTS `dav_addressbooks` (
48 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
49 `namespace` mediumint(9) NOT NULL,
50 `namespace_id` int(11) unsigned NOT NULL,
51 `displayname` varchar(200) NOT NULL,
52 `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
53 `needs_rebuild` TINYINT NOT NULL DEFAULT '1',
54 `uri` varchar(50) NOT NULL,
55 `ctag` int(11) unsigned NOT NULL DEFAULT '1',
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
59 $stms[] = "CREATE TABLE IF NOT EXISTS `dav_addressbookobjects` (
60 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
61 `addressbook_id` int(11) unsigned NOT NULL,
62 `contact` int(11) DEFAULT NULL,
63 `carddata` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
64 `uri` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
65 `lastmodified` timestamp NULL DEFAULT NULL,
66 `needs_rebuild` tinyint(4) NOT NULL DEFAULT '0',
67 `manually_deleted` tinyint(4) NOT NULL DEFAULT '0',
68 `etag` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
69 `size` int(10) unsigned NOT NULL,
71 UNIQUE KEY `namespace` (`addressbook_id`,`contact`),
72 KEY `contact` (`contact`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
80 * @param array $except
83 function dav_get_create_statements($except = [])
87 if (!in_array("dav_caldav_log", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_caldav_log` (
88 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
89 `uid` mediumint(9) NOT NULL,
90 `ip` varchar(15) NOT NULL,
91 `user_agent` varchar(100) NOT NULL,
92 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
93 `method` varchar(10) NOT NULL,
94 `url` varchar(100) NOT NULL,
96 KEY `mitglied` (`uid`)
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
99 if (!in_array("dav_calendarobjects", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_calendarobjects` (
100 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
101 `calendar_id` int(11) NOT NULL,
103 `uri` varchar(200) NOT NULL,
104 `lastmodified` timestamp NULL DEFAULT NULL,
105 `componentType` enum('VEVENT','VTODO') NOT NULL DEFAULT 'VEVENT',
106 `firstOccurence` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
107 `lastOccurence` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
108 `etag` varchar(15) NOT NULL,
109 `size` int(10) unsigned NOT NULL,
111 UNIQUE KEY `uri` (`uri`),
112 KEY `calendar_id` (`calendar_id`)
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
115 if (!in_array("dav_calendars", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_calendars` (
116 `id` int(11) NOT NULL AUTO_INCREMENT,
117 `namespace` mediumint(9) NOT NULL,
118 `namespace_id` int(10) unsigned NOT NULL,
119 `calendarorder` int(11) NOT NULL DEFAULT '1',
120 `calendarcolor` char(6) NOT NULL DEFAULT '5858FF',
121 `displayname` varchar(200) NOT NULL,
122 `timezone` text NOT NULL,
123 `description` varchar(500) NOT NULL,
124 `uri` varchar(50) NOT NULL DEFAULT '',
125 `has_vevent` tinyint(4) NOT NULL DEFAULT '1',
126 `has_vtodo` tinyint(4) NOT NULL DEFAULT '1',
127 `ctag` int(10) unsigned NOT NULL,
129 UNIQUE KEY (`namespace` , `namespace_id` , `uri`),
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
133 if (!in_array("dav_cal_virtual_object_cache", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_cal_virtual_object_cache` (
134 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
135 `calendar_id` int(10) unsigned NOT NULL,
136 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
137 `data_uri` char(80) NOT NULL,
138 `data_summary` varchar(1000) NOT NULL,
139 `data_location` varchar(1000) NOT NULL,
140 `data_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
141 `data_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
142 `data_allday` tinyint(4) NOT NULL,
143 `data_type` varchar(20) NOT NULL,
144 `calendardata` text NOT NULL,
145 `size` int(11) NOT NULL,
146 `etag` varchar(15) NOT NULL,
148 KEY `data_uri` (`data_uri`),
149 KEY `ref_type` (`calendar_id`,`data_end`)
150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
152 if (!in_array("dav_cal_virtual_object_sync", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_cal_virtual_object_sync` (
153 `calendar_id` int(10) unsigned NOT NULL,
154 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
155 PRIMARY KEY (`calendar_id`)
156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
158 if (!in_array("dav_jqcalendar", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_jqcalendar` (
159 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
160 `ical_recurr_uri` varchar(100) DEFAULT NULL,
161 `calendar_id` int(10) unsigned NOT NULL,
162 `calendarobject_id` int(10) unsigned NOT NULL,
163 `Summary` varchar(100) NOT NULL,
164 `StartTime` timestamp NULL DEFAULT NULL,
165 `EndTime` timestamp NULL DEFAULT NULL,
166 `IsEditable` tinyint(3) unsigned NOT NULL,
167 `IsAllDayEvent` tinyint(4) NOT NULL,
168 `IsRecurring` tinyint(4) NOT NULL,
169 `Color` char(6) DEFAULT NULL,
171 KEY `calendarByStart` (`calendar_id`,`StartTime`),
172 KEY `calendarobject_id` (`calendarobject_id`,`ical_recurr_uri`)
173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
175 if (!in_array("dav_notifications", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_notifications` (
176 `id` int(11) NOT NULL AUTO_INCREMENT,
177 `ical_recurr_uri` varchar(100) DEFAULT NULL,
178 `calendar_id` int(11) NOT NULL,
179 `calendarobject_id` int(10) unsigned NOT NULL,
180 `action` enum('email','display') NOT NULL DEFAULT 'email',
181 `alert_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
182 `notified` tinyint(4) NOT NULL DEFAULT '0',
184 KEY `notified` (`notified`,`alert_date`),
185 KEY `calendar_id` (`calendar_id`,`calendarobject_id`)
186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
188 if (!in_array("dav_addressbooks", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_addressbooks` (
189 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
190 `namespace` mediumint(9) NOT NULL,
191 `namespace_id` int(11) unsigned NOT NULL,
192 `displayname` varchar(200) NOT NULL,
193 `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
194 `needs_rebuild` TINYINT NOT NULL DEFAULT '1',
195 `uri` varchar(50) NOT NULL,
196 `ctag` int(11) unsigned NOT NULL DEFAULT '1',
198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
200 if (!in_array("dav_addressbookobjects", $except)) $arr[] = "CREATE TABLE IF NOT EXISTS `dav_addressbookobjects` (
201 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
202 `addressbook_id` int(11) unsigned NOT NULL,
203 `contact` int(11) DEFAULT NULL,
204 `carddata` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
205 `uri` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
206 `lastmodified` timestamp NULL DEFAULT NULL,
207 `needs_rebuild` tinyint(4) NOT NULL DEFAULT '0',
208 `manually_deleted` tinyint(4) NOT NULL DEFAULT '0',
209 `etag` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
210 `size` int(10) unsigned NOT NULL,
212 UNIQUE KEY `namespace` (`addressbook_id`,`contact`),
213 KEY `contact` (`contact`)
214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
222 function dav_check_tables()
224 $x = q("DESCRIBE %s%scalendars", CALDAV_SQL_DB, CALDAV_SQL_PREFIX);
226 if (count($x) == 9) return 1; // Version 0.1
228 $x2 = q("show tables like '%s%saddressbooks'", CALDAV_SQL_DB, CALDAV_SQL_PREFIX);
229 if (!$x2 || count($x2) == 0) return 2; // Version 0.2
231 if (count($x) == 12) return 0; // Correct
233 return -2; // Unknown version
240 function dav_create_tables()
242 $stms = dav_get_create_statements();
245 foreach ($stms as $st) { // @TODO Friendica-dependent
247 if (dba::errorMessage()) {
248 $errors[] = dba::errorMessage();
258 function dav_upgrade_tables()
260 $ver = dav_check_tables();
261 if (!in_array($ver, [1, 2])) return ["Unknown error"];
262 $stms = dav_get_update_statements($ver);
266 foreach ($stms as $st) { // @TODO Friendica-dependent
268 if (dba::errorMessage()) {
269 $errors[] = dba::errorMessage();