· 4 years ago · Aug 19, 2021, 05:48 AM
1-- MySQL Script generated by MySQL Workbench
2-- Wed Aug 18 12:44:26 2021
3-- -----------------------------------------------------
4-- Schema mydb
5-- -----------------------------------------------------
6-- -----------------------------------------------------
7-- Schema lms_production
8-- -----------------------------------------------------
9
10-- -----------------------------------------------------
11-- Schema lms_production
12-- -----------------------------------------------------
13-- -----------------------------------------------------
14-- Table `lms_production`.`comments`
15-- -----------------------------------------------------
16CREATE TABLE IF NOT EXISTS `lms_production`.`comments` (
17 `id` INT NOT NULL,
18 `user_id` INT NOT NULL,
19 `parent_comment_id` INT NOT NULL,
20 `message` TEXT NULL,
21 `is_visible` TINYINT NULL,
22 `is_archived` TINYINT NULL,
23 `is_pinned` TINYINT NULL,
24 `cache_downvotes_count` TINYINT NULL,
25 `cache_upvotes_count` TINYINT NULL,
26 `created_at` DATETIME NULL,
27 `updated_at` DATETIME NULL,
28 PRIMARY KEY (`id`),
29 INDEX `fk_comments_users1_idx` (`user_id` ASC) INVISIBLE,
30 INDEX `fk_comments_comments1_idx` (`parent_comment_id` ASC) VISIBLE,
31 CONSTRAINT `fk_comments_users1`
32 FOREIGN KEY (`user_id`)
33 REFERENCES `lms_production`.`users` (`id`)
34 ON DELETE NO ACTION
35 ON UPDATE NO ACTION,
36 CONSTRAINT `fk_comments_comments1`
37 FOREIGN KEY (`parent_comment_id`)
38 REFERENCES `lms_production`.`comments` (`id`)
39 ON DELETE NO ACTION
40 ON UPDATE NO ACTION)
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `lms_production`.`comment_subjects`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `lms_production`.`comment_subjects` (
48 `id` INT NOT NULL,
49 `subject` VARCHAR(225) NULL,
50 `table_name` VARCHAR(225) NULL,
51 `created_at` DATETIME NULL,
52 `updated_at` DATETIME NULL,
53 PRIMARY KEY (`id`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `lms_production`.`comment_threads`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `lms_production`.`comment_threads` (
61 `id` INT NOT NULL,
62 `track_id` INT NOT NULL,
63 `comment_id` INT NOT NULL,
64 `comment_subject_id` INT NOT NULL,
65 `chapter_module_id` INT NOT NULL,
66 `reply_comment_ids` TEXT NULL,
67 `cache_reply_count` TINYINT NULL,
68 `created_at` DATETIME NULL,
69 `updated_at` DATETIME NULL,
70 PRIMARY KEY (`id`),
71 INDEX `fk_comment_threads_tracks1_idx` (`track_id` ASC) VISIBLE,
72 INDEX `fk_comment_threads_comments1_idx` (`comment_id` ASC) VISIBLE,
73 INDEX `fk_comment_threads_comment_subjects1_idx` (`comment_subject_id` ASC) VISIBLE,
74 INDEX `fk_comment_threads_chapter_modules1_idx` (`chapter_module_id` ASC) VISIBLE,
75 CONSTRAINT `fk_comment_threads_tracks1`
76 FOREIGN KEY (`track_id`)
77 REFERENCES `lms_production`.`tracks` (`id`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION,
80 CONSTRAINT `fk_comment_threads_comments1`
81 FOREIGN KEY (`comment_id`)
82 REFERENCES `lms_production`.`comments` (`id`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION,
85 CONSTRAINT `fk_comment_threads_comment_subjects1`
86 FOREIGN KEY (`comment_subject_id`)
87 REFERENCES `lms_production`.`comment_subjects` (`id`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION,
90 CONSTRAINT `fk_comment_threads_chapter_modules1`
91 FOREIGN KEY (`chapter_module_id`)
92 REFERENCES `lms_production`.`chapter_modules` (`id`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `lms_production`.`comment_votes`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `lms_production`.`comment_votes` (
102 `id` INT NOT NULL,
103 `user_id` INT NOT NULL,
104 `comment_id` INT NOT NULL,
105 `is_upvote` TINYINT NULL,
106 `created_at` DATETIME NULL,
107 `updated_at` DATETIME NULL,
108 PRIMARY KEY (`id`),
109 INDEX `fk_comment_votes_users1_idx` (`user_id` ASC) VISIBLE,
110 INDEX `fk_comment_votes_comments1_idx` (`comment_id` ASC) VISIBLE,
111 CONSTRAINT `fk_comment_votes_users1`
112 FOREIGN KEY (`user_id`)
113 REFERENCES `lms_production`.`users` (`id`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION,
116 CONSTRAINT `fk_comment_votes_comments1`
117 FOREIGN KEY (`comment_id`)
118 REFERENCES `lms_production`.`comments` (`id`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `lms_production`.`notification_types`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `lms_production`.`notification_types` (
128 `id` INT NOT NULL,
129 `subject_matter` VARCHAR(225) NULL,
130 `craeted_at` DATETIME NULL,
131 `updated_at` DATETIME NULL,
132 PRIMARY KEY (`id`))
133ENGINE = InnoDB;
134
135
136-- -----------------------------------------------------
137-- Table `lms_production`.`notifications`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `lms_production`.`notifications` (
140 `id` INT NOT NULL,
141 `user_id` INT NOT NULL,
142 `notification_type_id` INT NOT NULL,
143 `is_viewed` TINYINT NULL,
144 `added_at` DATETIME NULL,
145 `created_at` DATETIME NULL,
146 `updated_at` DATETIME NULL,
147 PRIMARY KEY (`id`),
148 INDEX `fk_notifications_users1_idx` (`user_id` ASC) VISIBLE,
149 INDEX `fk_notifications_notification_types1_idx` (`notification_type_id` ASC) VISIBLE,
150 CONSTRAINT `fk_notifications_users1`
151 FOREIGN KEY (`user_id`)
152 REFERENCES `lms_production`.`users` (`id`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION,
155 CONSTRAINT `fk_notifications_notification_types1`
156 FOREIGN KEY (`notification_type_id`)
157 REFERENCES `lms_production`.`notification_types` (`id`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION)
160ENGINE = InnoDB;
161
162
163-- -----------------------------------------------------
164-- Table `lms_production`.`user_forum_summaries`
165-- -----------------------------------------------------
166CREATE TABLE IF NOT EXISTS `lms_production`.`user_forum_summaries` (
167 `id` INT NOT NULL,
168 `user_id` INT NOT NULL,
169 `forum_json` TEXT NULL,
170 `created_at` DATETIME NULL,
171 `updated_at` DATETIME NULL,
172 PRIMARY KEY (`id`),
173 INDEX `fk_user_forum_summaries_users1_idx` (`user_id` ASC) VISIBLE,
174 CONSTRAINT `fk_user_forum_summaries_users1`
175 FOREIGN KEY (`user_id`)
176 REFERENCES `lms_production`.`users` (`id`)
177 ON DELETE NO ACTION
178 ON UPDATE NO ACTION)
179ENGINE = InnoDB;
180
181
182SET SQL_MODE=@OLD_SQL_MODE;
183SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
184SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
185