· 9 years ago · Dec 20, 2016, 04:56 AM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `DoneBox` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `DoneBox` ;
7
8-- -----------------------------------------------------
9-- Table `DoneBox`.`users`
10-- -----------------------------------------------------
11CREATE TABLE IF NOT EXISTS `DoneBox`.`users` (
12 `id` CHAR(16) BINARY NOT NULL ,
13 `username` VARCHAR(50) NULL ,
14 `password` VARCHAR(150) NULL ,
15 `email` VARCHAR(100) NULL ,
16 `recovery_answer_1` TEXT NULL ,
17 `recovery_answer_2` TEXT NULL ,
18 `phone` VARCHAR(45) NULL ,
19 PRIMARY KEY (`id`) )
20ENGINE = InnoDB;
21
22CREATE UNIQUE INDEX `username_UNIQUE` ON `DoneBox`.`users` (`username` ASC) ;
23
24
25-- -----------------------------------------------------
26-- Table `DoneBox`.`Imap`
27-- -----------------------------------------------------
28CREATE TABLE IF NOT EXISTS `DoneBox`.`Imap` (
29 `id` CHAR(16) BINARY NOT NULL ,
30 `host` VARCHAR(100) NULL ,
31 `port` INT NULL ,
32 `ssl` TINYINT(1) NULL ,
33 PRIMARY KEY (`id`) )
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `DoneBox`.`smtp`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `DoneBox`.`smtp` (
41 `id` CHAR(16) BINARY NOT NULL ,
42 `host` VARCHAR(100) NULL ,
43 `port` INT NULL ,
44 `ssl` TINYINT(1) NULL ,
45 PRIMARY KEY (`id`) )
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `DoneBox`.`pop3`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `DoneBox`.`pop3` (
53 `id` CHAR(16) BINARY NOT NULL ,
54 `host` VARCHAR(100) NULL ,
55 `port` INT NULL ,
56 `ssl` TINYINT(1) NULL ,
57 PRIMARY KEY (`id`) )
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `DoneBox`.`email_provider`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `DoneBox`.`email_provider` (
65 `id` CHAR(16) NOT NULL ,
66 `domain` VARCHAR(100) NULL ,
67 `long_domain` VARCHAR(100) NULL ,
68 `Imap_id` CHAR(16) BINARY NOT NULL ,
69 `pop3_id` CHAR(16) BINARY NOT NULL ,
70 `smtp_id` CHAR(16) BINARY NOT NULL ,
71 PRIMARY KEY (`id`, `Imap_id`, `pop3_id`, `smtp_id`) ,
72 CONSTRAINT `fk_email_provider_Imap1`
73 FOREIGN KEY (`Imap_id` )
74 REFERENCES `DoneBox`.`Imap` (`id` )
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION,
77 CONSTRAINT `fk_email_provider_pop31`
78 FOREIGN KEY (`pop3_id` )
79 REFERENCES `DoneBox`.`pop3` (`id` )
80 ON DELETE NO ACTION
81 ON UPDATE NO ACTION,
82 CONSTRAINT `fk_email_provider_smtp1`
83 FOREIGN KEY (`smtp_id` )
84 REFERENCES `DoneBox`.`smtp` (`id` )
85 ON DELETE NO ACTION
86 ON UPDATE NO ACTION)
87ENGINE = InnoDB;
88
89CREATE INDEX `fk_email_provider_Imap1` ON `DoneBox`.`email_provider` (`Imap_id` ASC) ;
90
91CREATE INDEX `fk_email_provider_pop31` ON `DoneBox`.`email_provider` (`pop3_id` ASC) ;
92
93CREATE INDEX `fk_email_provider_smtp1` ON `DoneBox`.`email_provider` (`smtp_id` ASC) ;
94
95
96-- -----------------------------------------------------
97-- Table `DoneBox`.`email_account`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `DoneBox`.`email_account` (
100 `id` CHAR(16) BINARY NOT NULL ,
101 `email` VARCHAR(100) NULL ,
102 `password` VARCHAR(150) NULL ,
103 `signature` VARCHAR(45) NULL ,
104 `email_provider_id` CHAR(16) NOT NULL ,
105 `users_id` CHAR(16) BINARY NOT NULL ,
106 PRIMARY KEY (`id`, `email_provider_id`, `users_id`) ,
107 CONSTRAINT `fk_email_account_email_provider1`
108 FOREIGN KEY (`email_provider_id` )
109 REFERENCES `DoneBox`.`email_provider` (`id` )
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `fk_email_account_users1`
113 FOREIGN KEY (`users_id` )
114 REFERENCES `DoneBox`.`users` (`id` )
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119CREATE INDEX `fk_email_account_email_provider1` ON `DoneBox`.`email_account` (`email_provider_id` ASC) ;
120
121CREATE INDEX `fk_email_account_users1` ON `DoneBox`.`email_account` (`users_id` ASC) ;
122
123
124-- -----------------------------------------------------
125-- Table `DoneBox`.`contexts`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `DoneBox`.`contexts` (
128 `id` CHAR(16) BINARY NOT NULL ,
129 `name` VARCHAR(100) NULL ,
130 `users_id` CHAR(16) BINARY NOT NULL ,
131 PRIMARY KEY (`id`, `users_id`) ,
132 CONSTRAINT `fk_contexts_users1`
133 FOREIGN KEY (`users_id` )
134 REFERENCES `DoneBox`.`users` (`id` )
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139CREATE INDEX `fk_contexts_users1` ON `DoneBox`.`contexts` (`users_id` ASC) ;
140
141
142-- -----------------------------------------------------
143-- Table `DoneBox`.`projects`
144-- -----------------------------------------------------
145CREATE TABLE IF NOT EXISTS `DoneBox`.`projects` (
146 `id` CHAR(16) BINARY NOT NULL ,
147 `name` VARCHAR(100) NULL ,
148 `description` LONGTEXT NULL ,
149 `contexts_id` CHAR(16) BINARY NOT NULL ,
150 `users_id` CHAR(16) BINARY NOT NULL ,
151 PRIMARY KEY (`id`, `contexts_id`, `users_id`) ,
152 CONSTRAINT `fk_projects_contexts1`
153 FOREIGN KEY (`contexts_id` )
154 REFERENCES `DoneBox`.`contexts` (`id` )
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `fk_projects_users1`
158 FOREIGN KEY (`users_id` )
159 REFERENCES `DoneBox`.`users` (`id` )
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164CREATE INDEX `fk_projects_contexts1` ON `DoneBox`.`projects` (`contexts_id` ASC) ;
165
166CREATE INDEX `fk_projects_users1` ON `DoneBox`.`projects` (`users_id` ASC) ;
167
168
169-- -----------------------------------------------------
170-- Table `DoneBox`.`email_messages`
171-- -----------------------------------------------------
172CREATE TABLE IF NOT EXISTS `DoneBox`.`email_messages` (
173 `id` CHAR(16) NOT NULL ,
174 `sender` VARCHAR(100) NULL ,
175 `sent_on` DATETIME NULL ,
176 `subject` VARCHAR(45) NULL ,
177 `body` LONGTEXT NULL ,
178 `is_unread` TINYINT(1) NULL ,
179 `email_account_id` CHAR(16) BINARY NOT NULL ,
180 `users_id` CHAR(16) BINARY NOT NULL ,
181 `projects_id` CHAR(16) BINARY NOT NULL ,
182 `contexts_id` CHAR(16) BINARY NOT NULL ,
183 PRIMARY KEY (`id`, `email_account_id`, `users_id`, `projects_id`, `contexts_id`) ,
184 CONSTRAINT `fk_email_messages_email_account1`
185 FOREIGN KEY (`email_account_id` )
186 REFERENCES `DoneBox`.`email_account` (`id` )
187 ON DELETE NO ACTION
188 ON UPDATE NO ACTION,
189 CONSTRAINT `fk_email_messages_users1`
190 FOREIGN KEY (`users_id` )
191 REFERENCES `DoneBox`.`users` (`id` )
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION,
194 CONSTRAINT `fk_email_messages_projects1`
195 FOREIGN KEY (`projects_id` )
196 REFERENCES `DoneBox`.`projects` (`id` )
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION,
199 CONSTRAINT `fk_email_messages_contexts1`
200 FOREIGN KEY (`contexts_id` )
201 REFERENCES `DoneBox`.`contexts` (`id` )
202 ON DELETE NO ACTION
203 ON UPDATE NO ACTION)
204ENGINE = InnoDB;
205
206CREATE INDEX `fk_email_messages_email_account1` ON `DoneBox`.`email_messages` (`email_account_id` ASC) ;
207
208CREATE INDEX `fk_email_messages_users1` ON `DoneBox`.`email_messages` (`users_id` ASC) ;
209
210CREATE INDEX `fk_email_messages_projects1` ON `DoneBox`.`email_messages` (`projects_id` ASC) ;
211
212CREATE INDEX `fk_email_messages_contexts1` ON `DoneBox`.`email_messages` (`contexts_id` ASC) ;
213
214
215-- -----------------------------------------------------
216-- Table `DoneBox`.`social_account`
217-- -----------------------------------------------------
218CREATE TABLE IF NOT EXISTS `DoneBox`.`social_account` (
219 `id` CHAR(16) BINARY NOT NULL ,
220 `service` VARCHAR(100) NULL ,
221 `username` VARCHAR(100) NULL ,
222 `send` INT(11) NULL ,
223 `receive` INT(11) NULL ,
224 `key` VARCHAR(200) NULL ,
225 `json` LONGTEXT NULL ,
226 `users_id` CHAR(16) BINARY NOT NULL ,
227 PRIMARY KEY (`id`, `users_id`) ,
228 CONSTRAINT `fk_social_account_users1`
229 FOREIGN KEY (`users_id` )
230 REFERENCES `DoneBox`.`users` (`id` )
231 ON DELETE NO ACTION
232 ON UPDATE NO ACTION)
233ENGINE = InnoDB;
234
235CREATE INDEX `fk_social_account_users1` ON `DoneBox`.`social_account` (`users_id` ASC) ;
236
237
238-- -----------------------------------------------------
239-- Table `DoneBox`.`social_messages`
240-- -----------------------------------------------------
241CREATE TABLE IF NOT EXISTS `DoneBox`.`social_messages` (
242 `id` CHAR(16) BINARY NOT NULL ,
243 `sender` VARCHAR(200) NULL ,
244 `messaage_id` VARCHAR(200) NULL ,
245 `operation` TEXT NULL ,
246 `transient` TEXT NULL ,
247 `stream` VARCHAR(45) NULL ,
248 `time` TIME NULL ,
249 `from_me` INT(11) NULL ,
250 `to_me` VARCHAR(45) NULL ,
251 `json` VARCHAR(45) NULL ,
252 `social_account_id` CHAR(16) BINARY NOT NULL ,
253 `users_id` CHAR(16) BINARY NOT NULL ,
254 `projects_id` CHAR(16) BINARY NOT NULL ,
255 `contexts_id` CHAR(16) BINARY NOT NULL ,
256 PRIMARY KEY (`id`, `social_account_id`, `users_id`, `projects_id`, `contexts_id`) ,
257 CONSTRAINT `fk_social_messages_social_account1`
258 FOREIGN KEY (`social_account_id` )
259 REFERENCES `DoneBox`.`social_account` (`id` )
260 ON DELETE NO ACTION
261 ON UPDATE NO ACTION,
262 CONSTRAINT `fk_social_messages_users1`
263 FOREIGN KEY (`users_id` )
264 REFERENCES `DoneBox`.`users` (`id` )
265 ON DELETE NO ACTION
266 ON UPDATE NO ACTION,
267 CONSTRAINT `fk_social_messages_contexts1`
268 FOREIGN KEY (`contexts_id` )
269 REFERENCES `DoneBox`.`contexts` (`id` )
270 ON DELETE NO ACTION
271 ON UPDATE NO ACTION,
272 CONSTRAINT `fk_social_messages_projects1`
273 FOREIGN KEY (`projects_id` )
274 REFERENCES `DoneBox`.`projects` (`id` )
275 ON DELETE NO ACTION
276 ON UPDATE NO ACTION)
277ENGINE = InnoDB;
278
279CREATE INDEX `fk_social_messages_social_account1` ON `DoneBox`.`social_messages` (`social_account_id` ASC) ;
280
281CREATE INDEX `fk_social_messages_users1` ON `DoneBox`.`social_messages` (`users_id` ASC) ;
282
283CREATE INDEX `fk_social_messages_contexts1` ON `DoneBox`.`social_messages` (`contexts_id` ASC) ;
284
285CREATE INDEX `fk_social_messages_projects1` ON `DoneBox`.`social_messages` (`projects_id` ASC) ;
286
287
288-- -----------------------------------------------------
289-- Table `DoneBox`.`syndicate_feeds`
290-- -----------------------------------------------------
291CREATE TABLE IF NOT EXISTS `DoneBox`.`syndicate_feeds` (
292 `id` CHAR(16) BINARY NOT NULL ,
293 `name` VARCHAR(100) NULL ,
294 `url` VARCHAR(200) NULL ,
295 `users_id` CHAR(16) BINARY NOT NULL ,
296 PRIMARY KEY (`id`, `users_id`) ,
297 CONSTRAINT `fk_syndicate_feeds_users1`
298 FOREIGN KEY (`users_id` )
299 REFERENCES `DoneBox`.`users` (`id` )
300 ON DELETE NO ACTION
301 ON UPDATE NO ACTION)
302ENGINE = InnoDB;
303
304CREATE INDEX `fk_syndicate_feeds_users1` ON `DoneBox`.`syndicate_feeds` (`users_id` ASC) ;
305
306
307-- -----------------------------------------------------
308-- Table `DoneBox`.`syndicate_items`
309-- -----------------------------------------------------
310CREATE TABLE IF NOT EXISTS `DoneBox`.`syndicate_items` (
311 `id` CHAR(16) BINARY NOT NULL ,
312 `title` VARCHAR(100) NULL ,
313 `date` DATE NULL ,
314 `url` VARCHAR(200) NULL ,
315 `body` LONGTEXT NULL ,
316 `syndicate_feeds_id` CHAR(16) BINARY NOT NULL ,
317 `users_id` CHAR(16) BINARY NOT NULL ,
318 `projects_id` CHAR(16) BINARY NOT NULL ,
319 `contexts_id` CHAR(16) BINARY NOT NULL ,
320 PRIMARY KEY (`id`, `syndicate_feeds_id`, `users_id`, `projects_id`, `contexts_id`) ,
321 CONSTRAINT `fk_syndicate_items_syndicate_feeds1`
322 FOREIGN KEY (`syndicate_feeds_id` )
323 REFERENCES `DoneBox`.`syndicate_feeds` (`id` )
324 ON DELETE NO ACTION
325 ON UPDATE NO ACTION,
326 CONSTRAINT `fk_syndicate_items_users1`
327 FOREIGN KEY (`users_id` )
328 REFERENCES `DoneBox`.`users` (`id` )
329 ON DELETE NO ACTION
330 ON UPDATE NO ACTION,
331 CONSTRAINT `fk_syndicate_items_projects1`
332 FOREIGN KEY (`projects_id` )
333 REFERENCES `DoneBox`.`projects` (`id` )
334 ON DELETE NO ACTION
335 ON UPDATE NO ACTION,
336 CONSTRAINT `fk_syndicate_items_contexts1`
337 FOREIGN KEY (`contexts_id` )
338 REFERENCES `DoneBox`.`contexts` (`id` )
339 ON DELETE NO ACTION
340 ON UPDATE NO ACTION)
341ENGINE = InnoDB;
342
343CREATE INDEX `fk_syndicate_items_syndicate_feeds1` ON `DoneBox`.`syndicate_items` (`syndicate_feeds_id` ASC) ;
344
345CREATE INDEX `fk_syndicate_items_users1` ON `DoneBox`.`syndicate_items` (`users_id` ASC) ;
346
347CREATE INDEX `fk_syndicate_items_projects1` ON `DoneBox`.`syndicate_items` (`projects_id` ASC) ;
348
349CREATE INDEX `fk_syndicate_items_contexts1` ON `DoneBox`.`syndicate_items` (`contexts_id` ASC) ;
350
351
352-- -----------------------------------------------------
353-- Table `DoneBox`.`activation_profile`
354-- -----------------------------------------------------
355CREATE TABLE IF NOT EXISTS `DoneBox`.`activation_profile` (
356 `id` CHAR(16) BINARY NOT NULL ,
357 `activation_code` VARCHAR(40) NULL ,
358 `date` DATE NULL ,
359 `users_id` CHAR(16) BINARY NOT NULL ,
360 PRIMARY KEY (`id`, `users_id`) ,
361 CONSTRAINT `fk_activation_profile_users1`
362 FOREIGN KEY (`users_id` )
363 REFERENCES `DoneBox`.`users` (`id` )
364 ON DELETE NO ACTION
365 ON UPDATE NO ACTION)
366ENGINE = InnoDB;
367
368CREATE INDEX `fk_activation_profile_users1` ON `DoneBox`.`activation_profile` (`users_id` ASC) ;
369
370
371CREATE USER `levi` IDENTIFIED BY '<snip>';
372
373grant DELETE on TABLE `DoneBox`.`Imap` to levi;
374grant INSERT on TABLE `DoneBox`.`Imap` to levi;
375grant SELECT on TABLE `DoneBox`.`Imap` to levi;
376grant UPDATE on TABLE `DoneBox`.`Imap` to levi;
377grant DELETE on TABLE `DoneBox`.`social_messages` to levi;
378grant INSERT on TABLE `DoneBox`.`social_messages` to levi;
379grant SELECT on TABLE `DoneBox`.`social_messages` to levi;
380grant UPDATE on TABLE `DoneBox`.`social_messages` to levi;
381grant DELETE on TABLE `DoneBox`.`contexts` to levi;
382grant SELECT on TABLE `DoneBox`.`contexts` to levi;
383grant UPDATE on TABLE `DoneBox`.`contexts` to levi;
384grant INSERT on TABLE `DoneBox`.`contexts` to levi;
385grant DELETE on TABLE `DoneBox`.`pop3` to levi;
386grant INSERT on TABLE `DoneBox`.`pop3` to levi;
387grant SELECT on TABLE `DoneBox`.`pop3` to levi;
388grant UPDATE on TABLE `DoneBox`.`pop3` to levi;
389grant DELETE on TABLE `DoneBox`.`syndicate_feeds` to levi;
390grant SELECT on TABLE `DoneBox`.`syndicate_feeds` to levi;
391grant UPDATE on TABLE `DoneBox`.`syndicate_feeds` to levi;
392grant INSERT on TABLE `DoneBox`.`syndicate_feeds` to levi;
393grant DELETE on TABLE `DoneBox`.`email_account` to levi;
394grant SELECT on TABLE `DoneBox`.`email_account` to levi;
395grant UPDATE on TABLE `DoneBox`.`email_account` to levi;
396grant INSERT on TABLE `DoneBox`.`email_account` to levi;
397grant DELETE on TABLE `DoneBox`.`projects` to levi;
398grant INSERT on TABLE `DoneBox`.`projects` to levi;
399grant SELECT on TABLE `DoneBox`.`projects` to levi;
400grant UPDATE on TABLE `DoneBox`.`projects` to levi;
401grant DELETE on TABLE `DoneBox`.`syndicate_items` to levi;
402grant INSERT on TABLE `DoneBox`.`syndicate_items` to levi;
403grant SELECT on TABLE `DoneBox`.`syndicate_items` to levi;
404grant UPDATE on TABLE `DoneBox`.`syndicate_items` to levi;
405grant DELETE on TABLE `DoneBox`.`email_messages` to levi;
406grant INSERT on TABLE `DoneBox`.`email_messages` to levi;
407grant SELECT on TABLE `DoneBox`.`email_messages` to levi;
408grant UPDATE on TABLE `DoneBox`.`email_messages` to levi;
409grant DELETE on TABLE `DoneBox`.`smtp` to levi;
410grant INSERT on TABLE `DoneBox`.`smtp` to levi;
411grant SELECT on TABLE `DoneBox`.`smtp` to levi;
412grant UPDATE on TABLE `DoneBox`.`smtp` to levi;
413grant DELETE on TABLE `DoneBox`.`users` to levi;
414grant INSERT on TABLE `DoneBox`.`users` to levi;
415grant SELECT on TABLE `DoneBox`.`users` to levi;
416grant UPDATE on TABLE `DoneBox`.`users` to levi;
417grant DELETE on TABLE `DoneBox`.`email_provider` to levi;
418grant INSERT on TABLE `DoneBox`.`email_provider` to levi;
419grant SELECT on TABLE `DoneBox`.`email_provider` to levi;
420grant UPDATE on TABLE `DoneBox`.`email_provider` to levi;
421grant DELETE on TABLE `DoneBox`.`social_account` to levi;
422grant INSERT on TABLE `DoneBox`.`social_account` to levi;
423grant SELECT on TABLE `DoneBox`.`social_account` to levi;
424grant UPDATE on TABLE `DoneBox`.`social_account` to levi;
425
426SET SQL_MODE=@OLD_SQL_MODE;
427SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
428SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;