· 8 years ago · Mar 29, 2018, 02:14 AM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Feb 3 14:56:59 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema messenger
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `messenger` ;
14
15-- -----------------------------------------------------
16-- Schema messenger
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `messenger` DEFAULT CHARACTER SET utf8 ;
19SHOW WARNINGS;
20USE `messenger` ;
21
22-- -----------------------------------------------------
23-- Table `users`
24-- -----------------------------------------------------
25DROP TABLE IF EXISTS `users` ;
26
27SHOW WARNINGS;
28CREATE TABLE IF NOT EXISTS `users` (
29 `id` INT NOT NULL AUTO_INCREMENT,
30 `phone` VARCHAR(16) NOT NULL,
31 `email` VARCHAR(255) NOT NULL,
32 `password` VARCHAR(40) NULL,
33 `first_name` VARCHAR(20) NULL,
34 `last_name` VARCHAR(20) NULL,
35 `middle_name` VARCHAR(20) NULL,
36 `verification_code` CHAR(6) NULL,
37 `is_active` TINYINT(1) NULL DEFAULT 0,
38 `is_reported` TINYINT(1) NULL DEFAULT 0,
39 `is_blocked` TINYINT(1) NULL DEFAULT 0,
40 `created_at` DATETIME NULL,
41 `updated_at` DATETIME NULL,
42 PRIMARY KEY (`id`))
43ENGINE = InnoDB;
44
45SHOW WARNINGS;
46CREATE UNIQUE INDEX `phone_UNIQUE` ON `users` (`phone` ASC);
47
48SHOW WARNINGS;
49CREATE UNIQUE INDEX `email_UNIQUE` ON `users` (`email` ASC);
50
51SHOW WARNINGS;
52
53-- -----------------------------------------------------
54-- Table `contacts`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS `contacts` ;
57
58SHOW WARNINGS;
59CREATE TABLE IF NOT EXISTS `contacts` (
60 `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Sync the contacts to this table',
61 `first_name` VARCHAR(20) NULL,
62 `middle_name` VARCHAR(20) NULL,
63 `last_name` VARCHAR(20) NULL,
64 `phone` VARCHAR(14) NOT NULL,
65 `email` VARCHAR(255) NOT NULL,
66 `created_at` VARCHAR(45) NULL,
67 PRIMARY KEY (`id`))
68ENGINE = InnoDB;
69
70SHOW WARNINGS;
71
72-- -----------------------------------------------------
73-- Table `conversation`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `conversation` ;
76
77SHOW WARNINGS;
78CREATE TABLE IF NOT EXISTS `conversation` (
79 `id` INT NOT NULL AUTO_INCREMENT,
80 `title` VARCHAR(40) NULL,
81 `creator_id` INT NOT NULL,
82 `channel_id` VARCHAR(45) NULL,
83 `created_at` DATETIME NULL,
84 `updated_at` DATETIME NULL,
85 `deleted_at` DATETIME NULL,
86 PRIMARY KEY (`id`))
87ENGINE = InnoDB;
88
89SHOW WARNINGS;
90
91-- -----------------------------------------------------
92-- Table `messages`
93-- -----------------------------------------------------
94DROP TABLE IF EXISTS `messages` ;
95
96SHOW WARNINGS;
97CREATE TABLE IF NOT EXISTS `messages` (
98 `id` INT NOT NULL,
99 `conversation_id` INT NOT NULL,
100 `sender_id` INT NOT NULL,
101 `message_type` ENUM('text', 'image', 'vedio', 'audio') NULL,
102 `message` VARCHAR(255) NULL,
103 `attachment_thumb_url` VARCHAR(255) NULL,
104 `attachment_url` VARCHAR(255) NULL,
105 `created_at` DATETIME NULL,
106 `guid` VARCHAR(100) NULL,
107 `deleted_at` DATETIME NULL,
108 PRIMARY KEY (`id`))
109ENGINE = InnoDB;
110
111SHOW WARNINGS;
112
113-- -----------------------------------------------------
114-- Table `participants`
115-- -----------------------------------------------------
116DROP TABLE IF EXISTS `participants` ;
117
118SHOW WARNINGS;
119CREATE TABLE IF NOT EXISTS `participants` (
120 `id` INT NOT NULL AUTO_INCREMENT,
121 `conversation_id` INT NOT NULL,
122 `users_id` INT NOT NULL,
123 `type` ENUM('single', 'group') NOT NULL,
124 PRIMARY KEY (`id`))
125ENGINE = InnoDB;
126
127SHOW WARNINGS;
128
129-- -----------------------------------------------------
130-- Table `reports`
131-- -----------------------------------------------------
132DROP TABLE IF EXISTS `reports` ;
133
134SHOW WARNINGS;
135CREATE TABLE IF NOT EXISTS `reports` (
136 `id` INT NOT NULL AUTO_INCREMENT,
137 `users_id` INT NOT NULL,
138 `participants_id` INT NOT NULL,
139 `report_type` VARCHAR(45) NULL,
140 `notes` TEXT NULL,
141 `created_at` DATETIME NULL,
142 PRIMARY KEY (`id`))
143ENGINE = InnoDB;
144
145SHOW WARNINGS;
146
147-- -----------------------------------------------------
148-- Table `block_list`
149-- -----------------------------------------------------
150DROP TABLE IF EXISTS `block_list` ;
151
152SHOW WARNINGS;
153CREATE TABLE IF NOT EXISTS `block_list` (
154 `id` INT NOT NULL AUTO_INCREMENT,
155 `users_id` INT NOT NULL,
156 `participants_id` INT NOT NULL,
157 `created_at` DATETIME NULL,
158 PRIMARY KEY (`id`))
159ENGINE = InnoDB;
160
161SHOW WARNINGS;
162
163-- -----------------------------------------------------
164-- Table `deleted_conversations`
165-- -----------------------------------------------------
166DROP TABLE IF EXISTS `deleted_conversations` ;
167
168SHOW WARNINGS;
169CREATE TABLE IF NOT EXISTS `deleted_conversations` (
170 `id` INT NOT NULL,
171 `conversation_id` INT NOT NULL,
172 `users_id` INT NOT NULL,
173 `created_at` DATETIME NULL,
174 PRIMARY KEY (`id`))
175ENGINE = InnoDB;
176
177SHOW WARNINGS;
178
179-- -----------------------------------------------------
180-- Table `devices`
181-- -----------------------------------------------------
182DROP TABLE IF EXISTS `devices` ;
183
184SHOW WARNINGS;
185CREATE TABLE IF NOT EXISTS `devices` (
186 `id` INT NOT NULL AUTO_INCREMENT,
187 `users_id` INT NOT NULL,
188 `device_id` VARCHAR(120) NULL,
189 `type` ENUM('APPLE') NULL,
190 `device_token` VARCHAR(120) NULL,
191 PRIMARY KEY (`id`))
192ENGINE = InnoDB;
193
194SHOW WARNINGS;
195
196-- -----------------------------------------------------
197-- Table `access`
198-- -----------------------------------------------------
199DROP TABLE IF EXISTS `access` ;
200
201SHOW WARNINGS;
202CREATE TABLE IF NOT EXISTS `access` (
203 `id` INT NOT NULL,
204 `users_id` INT NOT NULL,
205 `token` VARCHAR(60) NULL,
206 `created_at` DATETIME NULL,
207 `deleted_at` DATETIME NULL,
208 `devices_id` INT NOT NULL,
209 PRIMARY KEY (`id`))
210ENGINE = InnoDB;
211
212SHOW WARNINGS;
213
214-- -----------------------------------------------------
215-- Table `deleted_messages`
216-- -----------------------------------------------------
217DROP TABLE IF EXISTS `deleted_messages` ;
218
219SHOW WARNINGS;
220CREATE TABLE IF NOT EXISTS `deleted_messages` (
221 `id` INT NOT NULL AUTO_INCREMENT,
222 `messages_id` INT NOT NULL,
223 `users_id` INT NOT NULL,
224 `created_at` DATETIME NULL,
225 PRIMARY KEY (`id`))
226ENGINE = InnoDB;
227
228SHOW WARNINGS;
229
230-- -----------------------------------------------------
231-- Table `user_contact`
232-- -----------------------------------------------------
233DROP TABLE IF EXISTS `user_contact` ;
234
235SHOW WARNINGS;
236CREATE TABLE IF NOT EXISTS `user_contact` (
237 `user_id` INT NOT NULL,
238 `contact_id` INT NOT NULL,
239 `first_name` VARCHAR(45) NULL,
240 `last_name` VARCHAR(45) NULL,
241 `created_at` DATETIME NULL,
242 `updated_at` DATETIME NULL,
243 PRIMARY KEY (`contact_id`, `user_id`))
244ENGINE = InnoDB;
245
246SHOW WARNINGS;
247
248SET SQL_MODE=@OLD_SQL_MODE;
249SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
250SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;