svsBKdjN

1
2-- -----------------------------------------------------
3-- Table `conversations`
4-- -----------------------------------------------------
5CREATE TABLE IF NOT EXISTS `conversations` (
6  `id` INT NOT NULL AUTO_INCREMENT,
7  `unit_id` INT(11) NOT NULL,
8  `creator_user_id` INT(11) NOT NULL,
9  `recipient_user_id` INT(11) NOT NULL,
10  `created` TIMESTAMP NOT NULL,
11  PRIMARY KEY (`id`),
12  INDEX `fk_conversations_units1_idx` (`unit_id` ASC),
13  INDEX `fk_conversations_users1_idx` (`creator_user_id` ASC),
14  INDEX `fk_conversations_users2_idx` (`recipient_user_id` ASC),
15  CONSTRAINT `fk_conversations_units1`
16    FOREIGN KEY (`unit_id`)
17    REFERENCES `units` (`id`)
18    ON DELETE NO ACTION
19    ON UPDATE NO ACTION,
20  CONSTRAINT `fk_conversations_users1`
21    FOREIGN KEY (`creator_user_id`)
22    REFERENCES `users` (`id`)
23    ON DELETE NO ACTION
24    ON UPDATE NO ACTION,
25  CONSTRAINT `fk_conversations_users2`
26    FOREIGN KEY (`recipient_user_id`)
27    REFERENCES `users` (`id`)
28    ON DELETE NO ACTION
29    ON UPDATE NO ACTION)
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `conversation_messages`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `conversation_messages` (
37  `id` INT NOT NULL AUTO_INCREMENT,
38  `conversation_id` INT NOT NULL,
39  `sender_user_id` INT(11) NOT NULL,
40  `recipient_user_id` INT(11) NOT NULL,
41  `message` TEXT NOT NULL,
42  `is_read` TINYINT NOT NULL DEFAULT 0,
43  `created` TIMESTAMP NOT NULL,
44  PRIMARY KEY (`id`),
45  INDEX `fk_conversation_messages_conversations1_idx` (`conversation_id` ASC),
46  INDEX `fk_conversation_messages_users1_idx` (`sender_user_id` ASC),
47  INDEX `fk_conversation_messages_users2_idx` (`recipient_user_id` ASC),
48  CONSTRAINT `fk_conversation_messages_conversations1`
49    FOREIGN KEY (`conversation_id`)
50    REFERENCES `conversations` (`id`)
51    ON DELETE NO ACTION
52    ON UPDATE NO ACTION,
53  CONSTRAINT `fk_conversation_messages_users1`
54    FOREIGN KEY (`sender_user_id`)
55    REFERENCES `users` (`id`)
56    ON DELETE NO ACTION
57    ON UPDATE NO ACTION,
58  CONSTRAINT `fk_conversation_messages_users2`
59    FOREIGN KEY (`recipient_user_id`)
60    REFERENCES `users` (`id`)
61    ON DELETE NO ACTION
62    ON UPDATE NO ACTION)
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `conversation_message_translations`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `conversation_message_translations` (
70  `id` INT NOT NULL,
71  `language` VARCHAR(45) NOT NULL,
72  `message` TEXT NOT NULL,
73  `conversation_message_id` INT NOT NULL,
74  PRIMARY KEY (`id`),
75  INDEX `fk_table1_conversation_messages1_idx` (`conversation_message_id` ASC),
76  CONSTRAINT `fk_table1_conversation_messages1`
77    FOREIGN KEY (`conversation_message_id`)
78    REFERENCES `conversation_messages` (`id`)
79    ON DELETE NO ACTION
80    ON UPDATE NO ACTION)
81ENGINE = InnoDB;