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