· 8 years ago · Mar 03, 2018, 06:34 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 INDEX `forum_questions_FI_1` (`forum_user_account_id`),
64 CONSTRAINT `forum_questions_FK_1`
65 FOREIGN KEY (`forum_user_account_id`)
66 REFERENCES `forum_user_accounts` (`id_user`)
67)Type=InnoDB;
68
69#-----------------------------------------------------------------------------
70#-- forum_question_categories
71#-----------------------------------------------------------------------------
72
73DROP TABLE IF EXISTS `forum_question_categories`;
74
75
76CREATE TABLE `forum_question_categories`
77(
78 `forum_question_id` INTEGER NOT NULL,
79 `forum_category_id` INTEGER NOT NULL,
80 `level_category` TINYINT default 0 NOT NULL,
81 PRIMARY KEY (`forum_question_id`,`forum_category_id`)
82)Type=InnoDB;
83
84#-----------------------------------------------------------------------------
85#-- forum_answers
86#-----------------------------------------------------------------------------
87
88DROP TABLE IF EXISTS `forum_answers`;
89
90
91CREATE TABLE `forum_answers`
92(
93 `forum_answer_id` INTEGER NOT NULL AUTO_INCREMENT,
94 `forum_user_account_id` INTEGER NOT NULL,
95 `forum_question_id` INTEGER NOT NULL,
96 `answer` TEXT NOT NULL,
97 `created_at` DATETIME,
98 PRIMARY KEY (`forum_answer_id`),
99 INDEX `forum_answers_FI_1` (`forum_user_account_id`),
100 CONSTRAINT `forum_answers_FK_1`
101 FOREIGN KEY (`forum_user_account_id`)
102 REFERENCES `forum_user_accounts` (`id_user`),
103 INDEX `forum_answers_FI_2` (`forum_question_id`),
104 CONSTRAINT `forum_answers_FK_2`
105 FOREIGN KEY (`forum_question_id`)
106 REFERENCES `forum_questions` (`forum_question_id`)
107)Type=InnoDB;
108
109#-----------------------------------------------------------------------------
110#-- forum_answer_comments
111#-----------------------------------------------------------------------------
112
113DROP TABLE IF EXISTS `forum_answer_comments`;
114
115
116CREATE TABLE `forum_answer_comments`
117(
118 `forum_answer_comment_id` INTEGER NOT NULL AUTO_INCREMENT,
119 `forum_answer_id` INTEGER NOT NULL AUTO_INCREMENT,
120 `forum_user_account_id` INTEGER NOT NULL,
121 `comment` TEXT NOT NULL,
122 PRIMARY KEY (`forum_answer_comment_id`),
123 INDEX `forum_answer_comments_FI_1` (`forum_answer_id`),
124 CONSTRAINT `forum_answer_comments_FK_1`
125 FOREIGN KEY (`forum_answer_id`)
126 REFERENCES `forum_answers` (`forum_answer_id`),
127 INDEX `forum_answer_comments_FI_2` (`forum_user_account_id`),
128 CONSTRAINT `forum_answer_comments_FK_2`
129 FOREIGN KEY (`forum_user_account_id`)
130 REFERENCES `forum_user_accounts` (`id_user`)
131)Type=InnoDB;
132
133#-----------------------------------------------------------------------------
134#-- forum_user_accounts
135#-----------------------------------------------------------------------------
136
137DROP TABLE IF EXISTS `forum_user_accounts`;
138
139
140CREATE TABLE `forum_user_accounts`
141(
142 `id_user` INTEGER NOT NULL,
143 PRIMARY KEY (`id_user`),
144 CONSTRAINT `forum_user_accounts_FK_1`
145 FOREIGN KEY (`id_user`)
146 REFERENCES `user` (`id_user`)
147)Type=InnoDB;
148
149#-----------------------------------------------------------------------------
150#-- forum_answers_qualifications
151#-----------------------------------------------------------------------------
152
153DROP TABLE IF EXISTS `forum_answers_qualifications`;
154
155
156CREATE TABLE `forum_answers_qualifications`
157(
158 `forum_answer_id` INTEGER NOT NULL,
159 `forum_user_account_id` INTEGER NOT NULL,
160 `qualification` TINYINT NOT NULL,
161 `created_at` DATETIME,
162 PRIMARY KEY (`forum_answer_id`,`forum_user_account_id`),
163 INDEX `forum_answers_qualifications_FI_1` (`forum_user_account_id`),
164 CONSTRAINT `forum_answers_qualifications_FK_1`
165 FOREIGN KEY (`forum_user_account_id`)
166 REFERENCES `forum_user_accounts` (`id_user`)
167)Type=InnoDB;
168
169# This restores the fkey checks, after having unset them earlier
170SET FOREIGN_KEY_CHECKS = 1;