5 * @param int $from_version
6 * @return array|string[]
8 function dav_get_update_statements($from_version)
12 if ($from_version <= 0) {
13 $stms[] = "ALTER TABLE `dav_calendars` ADD `uri` VARCHAR( 50 ) NULL DEFAULT NULL AFTER `description` , ADD `has_vevent` TINYINT NOT NULL DEFAULT '1' AFTER `uri` , ADD `has_vtodo` TINYINT NOT NULL DEFAULT '1' AFTER `has_vevent`";
15 $stms[] = "UPDATE `dav_calendars` SET `uri` = 'private' WHERE `namespace` = 1";
16 $stms[] = "UPDATE `dav_calendars` SET `uri` = 'friendica-mine' WHERE `namespace` = 2 AND `namespace_id` = 1";
17 $stms[] = "UPDATE `dav_calendars` SET `uri` = 'friendica-contacts' WHERE `namespace` = 2 AND `namespace_id` = 2";
18 $stms[] = "ALTER TABLE `dav_calendars` DROP PRIMARY KEY ";
19 $stms[] = "ALTER TABLE `dav_calendars` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ";
21 $stms[] = "ALTER TABLE `dav_calendarobjects` ADD `calendar_id` INT NOT NULL AFTER `id` ";
22 $stms[] = "UPDATE `dav_calendarobjects` a JOIN `dav_calendars` b ON a.`namespace` = b.`namespace` AND a.`namespace_id` = b.`namespace_id` SET a.`calendar_id` = b.`id`";
23 $stms[] = "ALTER TABLE `dav_calendarobjects` DROP `namespace` , DROP `namespace_id` ;";
24 $stms[] = "ALTER TABLE `dav_calendarobjects` ADD INDEX ( `calendar_id` ) ";
25 $stms[] = "ALTER TABLE `dav_calendarobjects` ADD `componentType` ENUM( 'VEVENT', 'VTODO' ) NOT NULL DEFAULT 'VEVENT' AFTER `calendardata` ,
26 ADD `firstOccurence` TIMESTAMP NOT NULL AFTER `lastmodified` ,
27 ADD `lastOccurence` TIMESTAMP NOT NULL AFTER `firstOccurence`";
29 $stms[] = "DROP TABLE `dav_jqcalendar`";
30 $stms[] = "CREATE TABLE IF NOT EXISTS `dav_jqcalendar` (
31 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
32 `ical_recurr_uri` varchar(100) DEFAULT NULL,
33 `calendar_id` int(10) unsigned NOT NULL,
34 `calendarobject_id` int(10) unsigned NOT NULL,
35 `Subject` varchar(1000) NOT NULL,
36 `StartTime` timestamp NULL DEFAULT NULL,
37 `EndTime` timestamp NULL DEFAULT NULL,
38 `IsEditable` tinyint(3) unsigned NOT NULL,
39 `IsAllDayEvent` tinyint(4) NOT NULL,
40 `IsRecurring` tinyint(4) NOT NULL,
41 `Color` CHAR(6) DEFAULT NULL,
43 KEY `calendarByStart` (`calendar_id`,`StartTime`),
44 KEY `calendarobject_id` (`calendarobject_id`,`ical_recurr_uri`)
45 ) DEFAULT CHARSET=utf8 ";
48 $stms[] = "ALTER TABLE `dav_notifications` ADD `calendar_id` INT NOT NULL AFTER `ical_recurr_uri` ";
49 $stms[] = "ALTER TABLE `dav_notifications` DROP INDEX `ical_uri` , ADD INDEX `ical_uri` ( `calendar_id` , `ical_uri` , `ical_recurr_uri` ) ";
50 $stms[] = "TRUNCATE TABLE `dav_notifications`";
51 $stms[] = "ALTER TABLE `dav_notifications` DROP `namespace` , DROP `namespace_id`";
53 $stms[] = "TRUNCATE TABLE `dav_cal_virtual_object_cache`";
54 $stme[] = "ALTER TABLE `dav_cal_virtual_object_cache` ADD `calendar_id` INT UNSIGNED NOT NULL AFTER `id` ";
55 $stms[] = "ALTER TABLE `dav_cal_virtual_object_cache` DROP INDEX `ref_type` , ADD INDEX `ref_type` ( `calendar_id` , `data_end` ) ";
56 $stms[] = "ALTER TABLE `dav_cal_virtual_object_cache` DROP `uid`, DROP `namespace` , DROP `namespace_id` ";
58 $stms[] = "CREATE TABLE `friendica`.`dav_cal_virtual_object_sync` (
59 `calendar_id` INT UNSIGNED NOT NULL ,
60 `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
61 PRIMARY KEY ( `calendar_id` )
62 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
64 $stms[] = "DROP TABLE `dav_cache_synchronized` ";
66 $stms[] = "UPDATE `dav_calendars` SET `namespace` = 1, `namespace_id` = `uid`"; // last
67 $stms[] = "ALTER TABLE `dav_calendars` DROP `uid`";
76 function dav_get_create_statements()
80 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_addressbooks_community` (
81 `uid` int(11) NOT NULL,
82 `ctag` int(11) unsigned NOT NULL DEFAULT '1',
84 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
86 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_addressbooks_phone` (
87 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
88 `uid` int(11) NOT NULL,
89 `principaluri` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
90 `displayname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
91 `uri` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
92 `description` text COLLATE utf8_unicode_ci,
93 `ctag` int(11) unsigned NOT NULL DEFAULT '1',
95 UNIQUE KEY `principaluri` (`principaluri`,`uri`)
96 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
98 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_cache_synchronized` (
99 `uid` mediumint(8) unsigned NOT NULL,
100 `namespace` smallint(6) NOT NULL,
101 `namespace_id` int(11) NOT NULL,
102 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
103 PRIMARY KEY (`uid`,`namespace`,`namespace_id`),
104 KEY `namespace` (`namespace`,`namespace_id`)
105 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
107 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_caldav_log` (
108 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
109 `uid` mediumint(9) NOT NULL,
110 `ip` varchar(15) NOT NULL,
111 `user_agent` varchar(100) NOT NULL,
112 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
113 `method` varchar(10) NOT NULL,
114 `url` varchar(100) NOT NULL,
116 KEY `mitglied` (`uid`)
117 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
119 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_calendarobjects` (
120 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
121 `namespace` mediumint(9) NOT NULL,
122 `namespace_id` int(10) unsigned NOT NULL,
124 `uri` varchar(200) NOT NULL,
125 `lastmodified` timestamp NULL DEFAULT NULL,
126 `etag` varchar(15) NOT NULL,
127 `size` int(10) unsigned NOT NULL,
129 UNIQUE KEY `uri` (`uri`,`namespace`,`namespace_id`),
130 KEY `namespace` (`namespace`,`namespace_id`)
131 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
133 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_calendars` (
134 `namespace` mediumint(9) NOT NULL,
135 `namespace_id` int(10) unsigned NOT NULL,
136 `uid` mediumint(8) unsigned NOT NULL,
137 `calendarorder` int(11) NOT NULL DEFAULT '1',
138 `calendarcolor` varchar(20) NOT NULL DEFAULT '#5858FF',
139 `displayname` varchar(200) NOT NULL,
140 `timezone` text NOT NULL,
141 `description` varchar(500) NOT NULL,
142 `uri` varchar(50) DEFAULT NULL,
143 `has_vevent` tinyint(4) NOT NULL DEFAULT '1',
144 `has_vtodo` tinyint(4) NOT NULL DEFAULT '1',
145 `ctag` int(10) unsigned NOT NULL,
146 PRIMARY KEY (`namespace`,`namespace_id`),
148 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
150 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_cal_virtual_object_cache` (
151 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
152 `uid` mediumint(8) unsigned NOT NULL,
153 `namespace` mediumint(9) NOT NULL,
154 `namespace_id` int(11) NOT NULL DEFAULT '0',
155 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
156 `data_uri` char(80) NOT NULL,
157 `data_subject` varchar(1000) NOT NULL,
158 `data_location` varchar(1000) NOT NULL,
159 `data_description` text NOT NULL,
160 `data_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
161 `data_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
162 `data_allday` tinyint(4) NOT NULL,
163 `data_type` varchar(20) NOT NULL,
164 `ical` text NOT NULL,
165 `ical_size` int(11) NOT NULL,
166 `ical_etag` varchar(15) NOT NULL,
168 KEY `ref_type` (`namespace`,`namespace_id`),
169 KEY `mitglied` (`uid`,`data_end`),
170 KEY `data_uri` (`data_uri`)
171 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
173 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_cards` (
174 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
175 `namespace` tinyint(3) unsigned NOT NULL,
176 `namespace_id` int(11) unsigned NOT NULL,
177 `contact` int(11) DEFAULT NULL,
178 `carddata` mediumtext COLLATE utf8_unicode_ci,
179 `uri` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
180 `lastmodified` int(11) unsigned DEFAULT NULL,
181 `manually_edited` tinyint(4) NOT NULL DEFAULT '0',
182 `manually_deleted` tinyint(4) NOT NULL DEFAULT '0',
183 `etag` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
184 `size` int(10) unsigned NOT NULL,
186 UNIQUE KEY `namespace` (`namespace`,`namespace_id`,`contact`),
187 KEY `contact` (`contact`)
188 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
190 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_jqcalendar` (
191 `id` int(11) NOT NULL AUTO_INCREMENT,
192 `uid` int(10) unsigned NOT NULL,
193 `ical_uri` varchar(200) NOT NULL,
194 `ical_recurr_uri` varchar(100) NOT NULL,
195 `namespace` mediumint(9) NOT NULL,
196 `namespace_id` int(11) NOT NULL,
197 `permission_edit` tinyint(4) NOT NULL DEFAULT '1',
198 `Subject` varchar(1000) DEFAULT NULL,
199 `Location` varchar(1000) DEFAULT NULL,
201 `StartTime` timestamp NULL DEFAULT NULL,
202 `EndTime` timestamp NULL DEFAULT NULL,
203 `IsAllDayEvent` smallint(6) NOT NULL,
204 `Color` varchar(20) DEFAULT NULL,
205 `RecurringRule` varchar(500) DEFAULT NULL,
207 KEY `user` (`uid`,`StartTime`),
208 KEY `zuord_typ` (`namespace`,`namespace_id`),
209 KEY `ical_uri` (`ical_uri`,`ical_recurr_uri`)
210 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
212 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_locks` (
213 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
214 `owner` varchar(100) DEFAULT NULL,
215 `timeout` int(10) unsigned DEFAULT NULL,
216 `created` int(11) DEFAULT NULL,
217 `token` varchar(100) DEFAULT NULL,
218 `scope` tinyint(4) DEFAULT NULL,
219 `depth` tinyint(4) DEFAULT NULL,
222 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
225 $arr[] = "CREATE TABLE IF NOT EXISTS `dav_notifications` (
226 `id` int(11) NOT NULL AUTO_INCREMENT,
227 `uid` int(10) unsigned NOT NULL,
228 `ical_uri` varchar(200) NOT NULL,
229 `ical_recurr_uri` varchar(100) NOT NULL,
230 `namespace` mediumint(8) unsigned NOT NULL,
231 `namespace_id` int(10) unsigned NOT NULL,
232 `alert_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
233 `rel_type` enum('second','minute','hour','day','week','month','year') NOT NULL,
234 `rel_value` mediumint(9) NOT NULL,
235 `notified` tinyint(4) NOT NULL DEFAULT '0',
237 KEY `notified` (`notified`,`alert_date`),
238 KEY `ical_uri` (`uid`,`ical_uri`,`ical_recurr_uri`)
239 ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
247 function dav_check_tables()
249 $dbv = get_config("dav", "db_version");
250 if ($dbv == CALDAV_DB_VERSION) return 0; // Correct
251 if (is_numeric($dbv) || $dbv == "CALDAV_DB_VERSION") return 1; // Older version (update needed)
252 return -1; // Not installed
259 function dav_create_tables()
261 $stms = dav_get_create_statements();
265 foreach ($stms as $st) {
267 if ($db->error) $errors[] = $db->error;
270 if (count($errors) == 0) set_config("dav", "db_version", CALDAV_DB_VERSION);
278 function dav_upgrade_tables()
280 $dbv = get_config("dav", "db_version");
281 if ($dbv == "CALDAV_DB_VERSION") $ver = 0;
282 else $ver = IntVal($dbv);
283 $stms = dav_get_update_statements($ver);
287 foreach ($stms as $st) {
289 if ($db->error) $errors[] = $db->error;
292 if (count($errors) == 0) set_config("dav", "db_version", CALDAV_DB_VERSION);