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