1------------------------------------------------------------ 2-- Fully-Unicode compliant SOGo MySQL schema 3-- 4-- Required MySQL version: >= 5.5.5 5-- Required SOGo version: >= 4.0.0 6-- 7-- This schema includes some specific table creation 8-- parameters needed for complete Unicode coverage 9-- (required for emoji character support, for instance). 10-- 11-- Note: SOGo automagically creates those tables on start, 12-- but *without* full Unicode compliance. So this schema 13-- must be imported before SOGo runs for the first time. 14-- 15-- Strongly suggested MySQL configuration settings 16-- (innodb* parameters are mandatory): 17-- 18-- [client] 19-- default-character-set = utf8mb4 20-- 21-- [mysql] 22-- default-character-set = utf8mb4 23-- 24-- [mysqld] 25-- character-set-client-handshake = FALSE 26-- character-set-server = utf8mb4 27-- collation-server = utf8mb4_unicode_ci 28-- innodb_file_per_table = TRUE 29-- innodb_file_format = barracuda 30-- innodb_large_prefix = TRUE 31-- 32-- This schema assumes the SOGo table names are configured 33-- like the following; adjust if needed: 34-- 35-- OCSAclURL -> sogo_acl 36-- OCSCacheFolderURL -> sogo_cache_folder 37-- OCSEMailAlarmsFolderURL -> sogo_alarms_folder 38-- OCSFolderInfoURL -> sogo_folder_info 39-- OCSSessionsFolderURL -> sogo_sessions_folder 40-- OCSStoreURL -> sogo_store 41-- SOGoProfileURL -> sogo_user_profile 42-- 43-- SOGo needs to know MySQL has full Unicode coverage; 44-- the following needs to be put in sogo.conf: 45-- 46-- MySQL4Encoding = "utf8mb4"; 47-- 48------------------------------------------------------------ 49CREATE TABLE sogo_acl ( 50 c_folder_id int(11) NOT NULL, 51 c_object varchar(255) NOT NULL, 52 c_uid varchar(255) NOT NULL, 53 c_role varchar(80) NOT NULL, 54 KEY sogo_acl_c_folder_id_idx (c_folder_id), 55 KEY sogo_acl_c_uid_idx (c_uid) 56) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 57 58CREATE TABLE sogo_alarms_folder ( 59 c_path varchar(255) NOT NULL, 60 c_name varchar(255) NOT NULL, 61 c_uid varchar(255) NOT NULL, 62 c_recurrence_id int(11) DEFAULT NULL, 63 c_alarm_number int(11) NOT NULL, 64 c_alarm_date int(11) NOT NULL 65) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 66 67CREATE TABLE sogo_cache_folder ( 68 c_uid varchar(255) NOT NULL, 69 c_path varchar(255) NOT NULL, 70 c_parent_path varchar(255) DEFAULT NULL, 71 c_type tinyint(3) unsigned NOT NULL, 72 c_creationdate int(11) NOT NULL, 73 c_lastmodified int(11) NOT NULL, 74 c_version int(11) NOT NULL DEFAULT '0', 75 c_deleted tinyint(4) NOT NULL DEFAULT '0', 76 c_content longtext, 77 PRIMARY KEY (c_uid,c_path) 78) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 79 80CREATE TABLE sogo_folder_info ( 81 c_folder_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, 82 c_path varchar(255) NOT NULL, 83 c_path1 varchar(255) NOT NULL, 84 c_path2 varchar(255) DEFAULT NULL, 85 c_path3 varchar(255) DEFAULT NULL, 86 c_path4 varchar(255) DEFAULT NULL, 87 c_foldername varchar(255) NOT NULL, 88 c_location varchar(2048) DEFAULT NULL, 89 c_quick_location varchar(2048) DEFAULT NULL, 90 c_acl_location varchar(2048) DEFAULT NULL, 91 c_folder_type varchar(255) NOT NULL, 92 PRIMARY KEY (c_path), 93 UNIQUE KEY c_folder_id (c_folder_id) 94) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 95 96CREATE TABLE sogo_quick_appointment ( 97 c_folder_id int(11) NOT NULL, 98 c_name varchar(255) NOT NULL, 99 c_uid varchar(255) NOT NULL, 100 c_startdate int(11) DEFAULT NULL, 101 c_enddate int(11) DEFAULT NULL, 102 c_cycleenddate int(11) DEFAULT NULL, 103 c_title varchar(1000) NOT NULL, 104 c_participants text, 105 c_isallday int(11) DEFAULT NULL, 106 c_iscycle int(11) DEFAULT NULL, 107 c_cycleinfo text, 108 c_classification int(11) NOT NULL, 109 c_isopaque int(11) NOT NULL, 110 c_status int(11) NOT NULL, 111 c_priority int(11) DEFAULT NULL, 112 c_location varchar(255) DEFAULT NULL, 113 c_orgmail varchar(255) DEFAULT NULL, 114 c_partmails text, 115 c_partstates text, 116 c_category varchar(255) DEFAULT NULL, 117 c_sequence int(11) DEFAULT NULL, 118 c_component varchar(10) NOT NULL, 119 c_nextalarm int(11) DEFAULT NULL, 120 c_description text, 121 PRIMARY KEY (c_folder_id,c_name) 122) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 123 124CREATE TABLE sogo_quick_contact ( 125 c_folder_id int(11) NOT NULL, 126 c_name varchar(255) NOT NULL, 127 c_givenname varchar(255) DEFAULT NULL, 128 c_cn varchar(255) DEFAULT NULL, 129 c_sn varchar(255) DEFAULT NULL, 130 c_screenname varchar(255) DEFAULT NULL, 131 c_l varchar(255) DEFAULT NULL, 132 c_mail text DEFAULT NULL, 133 c_o varchar(255) DEFAULT NULL, 134 c_ou varchar(255) DEFAULT NULL, 135 c_telephonenumber varchar(255) DEFAULT NULL, 136 c_categories varchar(255) DEFAULT NULL, 137 c_component varchar(10) NOT NULL, 138 c_hascertificate int(11) DEFAULT 0, 139 PRIMARY KEY (c_folder_id,c_name) 140) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 141 142CREATE TABLE sogo_sessions_folder ( 143 c_id varchar(255) NOT NULL, 144 c_value varchar(255) NOT NULL, 145 c_creationdate int(11) NOT NULL, 146 c_lastseen int(11) NOT NULL, 147 PRIMARY KEY (c_id) 148) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 149 150CREATE TABLE sogo_store ( 151 c_folder_id int(11) NOT NULL, 152 c_name varchar(255) NOT NULL DEFAULT '', 153 c_content mediumtext NOT NULL, 154 c_creationdate int(11) NOT NULL, 155 c_lastmodified int(11) NOT NULL, 156 c_version int(11) NOT NULL, 157 c_deleted int(11) DEFAULT NULL, 158 PRIMARY KEY (c_folder_id,c_name) 159) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 160 161CREATE TABLE sogo_user_profile ( 162 c_uid varchar(255) NOT NULL, 163 c_defaults longtext, 164 c_settings longtext, 165 PRIMARY KEY (c_uid) 166) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; 167 168