1/**
2 * libkolab database schema
3 *
4 * @version 1.2
5 * @author Thomas Bruederli
6 * @licence GNU AGPL
7 **/
8
9/*!40014  SET FOREIGN_KEY_CHECKS=0 */;
10
11DROP TABLE IF EXISTS `kolab_folders`;
12
13CREATE TABLE `kolab_folders` (
14  `folder_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
15  `resource` VARCHAR(255) BINARY NOT NULL,
16  `type` VARCHAR(32) NOT NULL,
17  `synclock` INT(10) NOT NULL DEFAULT '0',
18  `ctag` VARCHAR(40) DEFAULT NULL,
19  `changed` DATETIME DEFAULT NULL,
20  `objectcount` BIGINT DEFAULT NULL,
21  PRIMARY KEY(`folder_id`),
22  INDEX `resource_type` (`resource`, `type`)
23) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
24
25DROP TABLE IF EXISTS `kolab_cache`;
26
27DROP TABLE IF EXISTS `kolab_cache_contact`;
28
29CREATE TABLE `kolab_cache_contact` (
30  `folder_id` BIGINT UNSIGNED NOT NULL,
31  `msguid` BIGINT UNSIGNED NOT NULL,
32  `uid` VARCHAR(512) NOT NULL,
33  `created` DATETIME DEFAULT NULL,
34  `changed` DATETIME DEFAULT NULL,
35  `data` LONGTEXT NOT NULL,
36  `tags` TEXT NOT NULL,
37  `words` TEXT NOT NULL,
38  `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
39  `name` VARCHAR(255) NOT NULL,
40  `firstname` VARCHAR(255) NOT NULL,
41  `surname` VARCHAR(255) NOT NULL,
42  `email` VARCHAR(255) NOT NULL,
43  CONSTRAINT `fk_kolab_cache_contact_folder` FOREIGN KEY (`folder_id`)
44    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
45  PRIMARY KEY(`folder_id`,`msguid`),
46  INDEX `contact_type` (`folder_id`,`type`),
47  INDEX `contact_uid2msguid` (`folder_id`,`uid`,`msguid`)
48) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
49
50DROP TABLE IF EXISTS `kolab_cache_event`;
51
52CREATE TABLE `kolab_cache_event` (
53  `folder_id` BIGINT UNSIGNED NOT NULL,
54  `msguid` BIGINT UNSIGNED NOT NULL,
55  `uid` VARCHAR(512) NOT NULL,
56  `created` DATETIME DEFAULT NULL,
57  `changed` DATETIME DEFAULT NULL,
58  `data` LONGTEXT NOT NULL,
59  `tags` TEXT NOT NULL,
60  `words` TEXT NOT NULL,
61  `dtstart` DATETIME,
62  `dtend` DATETIME,
63  CONSTRAINT `fk_kolab_cache_event_folder` FOREIGN KEY (`folder_id`)
64    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
65  PRIMARY KEY(`folder_id`,`msguid`),
66  INDEX `event_uid2msguid` (`folder_id`,`uid`,`msguid`)
67) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
68
69DROP TABLE IF EXISTS `kolab_cache_task`;
70
71CREATE TABLE `kolab_cache_task` (
72  `folder_id` BIGINT UNSIGNED NOT NULL,
73  `msguid` BIGINT UNSIGNED NOT NULL,
74  `uid` VARCHAR(512) NOT NULL,
75  `created` DATETIME DEFAULT NULL,
76  `changed` DATETIME DEFAULT NULL,
77  `data` LONGTEXT NOT NULL,
78  `tags` TEXT NOT NULL,
79  `words` TEXT NOT NULL,
80  `dtstart` DATETIME,
81  `dtend` DATETIME,
82  CONSTRAINT `fk_kolab_cache_task_folder` FOREIGN KEY (`folder_id`)
83    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
84  PRIMARY KEY(`folder_id`,`msguid`),
85  INDEX `task_uid2msguid` (`folder_id`,`uid`,`msguid`)
86) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
87
88DROP TABLE IF EXISTS `kolab_cache_journal`;
89
90CREATE TABLE `kolab_cache_journal` (
91  `folder_id` BIGINT UNSIGNED NOT NULL,
92  `msguid` BIGINT UNSIGNED NOT NULL,
93  `uid` VARCHAR(512) NOT NULL,
94  `created` DATETIME DEFAULT NULL,
95  `changed` DATETIME DEFAULT NULL,
96  `data` LONGTEXT NOT NULL,
97  `tags` TEXT NOT NULL,
98  `words` TEXT NOT NULL,
99  `dtstart` DATETIME,
100  `dtend` DATETIME,
101  CONSTRAINT `fk_kolab_cache_journal_folder` FOREIGN KEY (`folder_id`)
102    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
103  PRIMARY KEY(`folder_id`,`msguid`),
104  INDEX `journal_uid2msguid` (`folder_id`,`uid`,`msguid`)
105) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
106
107DROP TABLE IF EXISTS `kolab_cache_note`;
108
109CREATE TABLE `kolab_cache_note` (
110  `folder_id` BIGINT UNSIGNED NOT NULL,
111  `msguid` BIGINT UNSIGNED NOT NULL,
112  `uid` VARCHAR(512) NOT NULL,
113  `created` DATETIME DEFAULT NULL,
114  `changed` DATETIME DEFAULT NULL,
115  `data` LONGTEXT NOT NULL,
116  `tags` TEXT NOT NULL,
117  `words` TEXT NOT NULL,
118  CONSTRAINT `fk_kolab_cache_note_folder` FOREIGN KEY (`folder_id`)
119    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
120  PRIMARY KEY(`folder_id`,`msguid`),
121  INDEX `note_uid2msguid` (`folder_id`,`uid`,`msguid`)
122) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
123
124DROP TABLE IF EXISTS `kolab_cache_file`;
125
126CREATE TABLE `kolab_cache_file` (
127  `folder_id` BIGINT UNSIGNED NOT NULL,
128  `msguid` BIGINT UNSIGNED NOT NULL,
129  `uid` VARCHAR(512) NOT NULL,
130  `created` DATETIME DEFAULT NULL,
131  `changed` DATETIME DEFAULT NULL,
132  `data` LONGTEXT NOT NULL,
133  `tags` TEXT NOT NULL,
134  `words` TEXT NOT NULL,
135  `filename` varchar(255) DEFAULT NULL,
136  CONSTRAINT `fk_kolab_cache_file_folder` FOREIGN KEY (`folder_id`)
137    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
138  PRIMARY KEY(`folder_id`,`msguid`),
139  INDEX `folder_filename` (`folder_id`, `filename`),
140  INDEX `file_uid2msguid` (`folder_id`,`uid`,`msguid`)
141) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
142
143DROP TABLE IF EXISTS `kolab_cache_configuration`;
144
145CREATE TABLE `kolab_cache_configuration` (
146  `folder_id` BIGINT UNSIGNED NOT NULL,
147  `msguid` BIGINT UNSIGNED NOT NULL,
148  `uid` VARCHAR(512) NOT NULL,
149  `created` DATETIME DEFAULT NULL,
150  `changed` DATETIME DEFAULT NULL,
151  `data` LONGTEXT NOT NULL,
152  `tags` TEXT NOT NULL,
153  `words` TEXT NOT NULL,
154  `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
155  CONSTRAINT `fk_kolab_cache_configuration_folder` FOREIGN KEY (`folder_id`)
156    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
157  PRIMARY KEY(`folder_id`,`msguid`),
158  INDEX `configuration_type` (`folder_id`,`type`),
159  INDEX `configuration_uid2msguid` (`folder_id`,`uid`,`msguid`)
160) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
161
162DROP TABLE IF EXISTS `kolab_cache_freebusy`;
163
164CREATE TABLE `kolab_cache_freebusy` (
165  `folder_id` BIGINT UNSIGNED NOT NULL,
166  `msguid` BIGINT UNSIGNED NOT NULL,
167  `uid` VARCHAR(512) NOT NULL,
168  `created` DATETIME DEFAULT NULL,
169  `changed` DATETIME DEFAULT NULL,
170  `data` LONGTEXT NOT NULL,
171  `tags` TEXT NOT NULL,
172  `words` TEXT NOT NULL,
173  `dtstart` DATETIME,
174  `dtend` DATETIME,
175  CONSTRAINT `fk_kolab_cache_freebusy_folder` FOREIGN KEY (`folder_id`)
176    REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE,
177  PRIMARY KEY(`folder_id`,`msguid`),
178  INDEX `freebusy_uid2msguid` (`folder_id`,`uid`,`msguid`)
179) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
180
181/*!40014  SET FOREIGN_KEY_CHECKS=1 */;
182
183REPLACE INTO `system` (`name`, `value`) VALUES ('libkolab-version', '2019092900');
184