· 8 years ago · Mar 04, 2018, 05:06 AM
1
2# This is a fix for InnoDB in MySQL >= 4.1.x
3# It "suspends judgement" for fkey relationships until are tables are set.
4SET FOREIGN_KEY_CHECKS = 0;
5
6#-----------------------------------------------------------------------------
7#-- forum_category_levels
8#-----------------------------------------------------------------------------
9
10DROP TABLE IF EXISTS `forum_category_levels`;
11
12
13CREATE TABLE `forum_category_levels`
14(
15 `forum_category_level_id` TINYINT NOT NULL AUTO_INCREMENT,
16 `forum_category_level_code` VARCHAR(15) NOT NULL,
17 PRIMARY KEY (`forum_category_level_id`),
18 UNIQUE KEY `forum_category_levels_U_1` (`forum_category_level_code`)
19)Type=InnoDB;
20
21#-----------------------------------------------------------------------------
22#-- forum_categories
23#-----------------------------------------------------------------------------
24
25DROP TABLE IF EXISTS `forum_categories`;
26
27
28CREATE TABLE `forum_categories`
29(
30 `forum_category_id` INTEGER NOT NULL AUTO_INCREMENT,
31 `forum_category_level_id` TINYINT NOT NULL,
32 `is_active` TINYINT default 0 NOT NULL,
33 `id_parent` INTEGER,
34 PRIMARY KEY (`forum_category_id`),
35 KEY `forum_categories_I_1`(`id_parent`),
36 CONSTRAINT `forum_categories_FK_1`
37 FOREIGN KEY (`id_parent`)
38 REFERENCES `forum_categories` (`forum_category_id`)
39)Type=InnoDB;
40
41#-----------------------------------------------------------------------------
42#-- forum_categories_i18n
43#-----------------------------------------------------------------------------
44
45DROP TABLE IF EXISTS `forum_categories_i18n`;
46
47
48CREATE TABLE `forum_categories_i18n`
49(
50 `forum_category_id` INTEGER NOT NULL,
51 `culture` VARCHAR(7) NOT NULL,
52 `name` VARCHAR(100) NOT NULL,
53 `name_slug` VARCHAR(100) NOT NULL,
54 PRIMARY KEY (`forum_category_id`,`culture`),
55 KEY `forum_categories_i18n_I_1`(`name_slug`),
56 CONSTRAINT `forum_categories_i18n_FK_1`
57 FOREIGN KEY (`forum_category_id`)
58 REFERENCES `forum_categories` (`forum_category_id`)
59)Type=InnoDB;
60
61#-----------------------------------------------------------------------------
62#-- forum_questions
63#-----------------------------------------------------------------------------
64
65DROP TABLE IF EXISTS `forum_questions`;
66
67
68CREATE TABLE `forum_questions`
69(
70 `forum_question_id` INTEGER NOT NULL AUTO_INCREMENT,
71 `forum_user_account_id` INTEGER NOT NULL,
72 `short_description` VARCHAR(255),
73 `long_description` TEXT,
74 `is_closed` TINYINT default 0 NOT NULL,
75 `closed_date` DATETIME,
76 `created_at` DATETIME,
77 `updated_at` DATETIME,
78 PRIMARY KEY (`forum_question_id`),
79 INDEX `forum_questions_FI_1` (`forum_user_account_id`),
80 CONSTRAINT `forum_questions_FK_1`
81 FOREIGN KEY (`forum_user_account_id`)
82 REFERENCES `forum_user_accounts` (`id_user`)
83)Type=InnoDB;
84
85#-----------------------------------------------------------------------------
86#-- forum_question_categories
87#-----------------------------------------------------------------------------
88
89DROP TABLE IF EXISTS `forum_question_categories`;
90
91
92CREATE TABLE `forum_question_categories`
93(
94 `forum_question_id` INTEGER NOT NULL,
95 `forum_category_id` INTEGER NOT NULL,
96 PRIMARY KEY (`forum_question_id`,`forum_category_id`)
97)Type=InnoDB;
98
99#-----------------------------------------------------------------------------
100#-- forum_answers
101#-----------------------------------------------------------------------------
102
103DROP TABLE IF EXISTS `forum_answers`;
104
105
106CREATE TABLE `forum_answers`
107(
108 `forum_answer_id` INTEGER NOT NULL AUTO_INCREMENT,
109 `forum_user_account_id` INTEGER NOT NULL,
110 `forum_question_id` INTEGER NOT NULL,
111 `answer` TEXT NOT NULL,
112 `created_at` DATETIME,
113 PRIMARY KEY (`forum_answer_id`),
114 INDEX `forum_answers_FI_1` (`forum_user_account_id`),
115 CONSTRAINT `forum_answers_FK_1`
116 FOREIGN KEY (`forum_user_account_id`)
117 REFERENCES `forum_user_accounts` (`id_user`),
118 INDEX `forum_answers_FI_2` (`forum_question_id`),
119 CONSTRAINT `forum_answers_FK_2`
120 FOREIGN KEY (`forum_question_id`)
121 REFERENCES `forum_questions` (`forum_question_id`)
122)Type=InnoDB;
123
124#-----------------------------------------------------------------------------
125#-- forum_answer_comments
126#-----------------------------------------------------------------------------
127
128DROP TABLE IF EXISTS `forum_answer_comments`;
129
130
131CREATE TABLE `forum_answer_comments`
132(
133 `forum_answer_comment_id` INTEGER NOT NULL AUTO_INCREMENT,
134 `forum_answer_id` INTEGER NOT NULL,
135 `forum_user_account_id` INTEGER NOT NULL,
136 `comment` TEXT NOT NULL,
137 PRIMARY KEY (`forum_answer_comment_id`),
138 INDEX `forum_answer_comments_FI_1` (`forum_answer_id`),
139 CONSTRAINT `forum_answer_comments_FK_1`
140 FOREIGN KEY (`forum_answer_id`)
141 REFERENCES `forum_answers` (`forum_answer_id`),
142 INDEX `forum_answer_comments_FI_2` (`forum_user_account_id`),
143 CONSTRAINT `forum_answer_comments_FK_2`
144 FOREIGN KEY (`forum_user_account_id`)
145 REFERENCES `forum_user_accounts` (`id_user`)
146)Type=InnoDB;
147
148#-----------------------------------------------------------------------------
149#-- forum_user_accounts
150#-----------------------------------------------------------------------------
151
152DROP TABLE IF EXISTS `forum_user_accounts`;
153
154
155CREATE TABLE `forum_user_accounts`
156(
157 `id_user` INTEGER NOT NULL,
158 PRIMARY KEY (`id_user`),
159 CONSTRAINT `forum_user_accounts_FK_1`
160 FOREIGN KEY (`id_user`)
161 REFERENCES `user` (`id_user`)
162)Type=InnoDB;
163
164#-----------------------------------------------------------------------------
165#-- forum_answers_qualifications
166#-----------------------------------------------------------------------------
167
168DROP TABLE IF EXISTS `forum_answers_qualifications`;
169
170
171CREATE TABLE `forum_answers_qualifications`
172(
173 `forum_answer_id` INTEGER NOT NULL,
174 `forum_user_account_id` INTEGER NOT NULL,
175 `qualification` TINYINT NOT NULL,
176 `created_at` DATETIME,
177 PRIMARY KEY (`forum_answer_id`,`forum_user_account_id`),
178 INDEX `forum_answers_qualifications_FI_1` (`forum_user_account_id`),
179 CONSTRAINT `forum_answers_qualifications_FK_1`
180 FOREIGN KEY (`forum_user_account_id`)
181 REFERENCES `forum_user_accounts` (`id_user`)
182)Type=InnoDB;
183
184# This restores the fkey checks, after having unset them earlier
185SET FOREIGN_KEY_CHECKS = 1;