root/trunk/data/sql/schema.sql

Revision 62, 6.6 kB (checked in by fabien, 3 years ago)

fixed 2 bugs in search engine

Line 
1 # This is a fix for InnoDB in MySQL >= 4.1.x
2 # It "suspends judgement" for fkey relationships until are tables are set.
3
4 SET FOREIGN_KEY_CHECKS = 0;
5 # -----------------------------------------------------------------------
6 # ask_question
7 # -----------------------------------------------------------------------
8 DROP TABLE IF EXISTS `ask_question`;
9
10 CREATE TABLE `ask_question`(
11     `id` INTEGER NOT NULL AUTO_INCREMENT,
12     `user_id` INTEGER ,
13     `title` TEXT ,
14     `stripped_title` TEXT ,
15     `body` TEXT ,
16     `html_body` TEXT ,
17     `interested_users` INTEGER default 0 ,
18     `reports` INTEGER default 0 ,
19     `created_at` DATETIME ,
20     `updated_at` DATETIME , 
21     PRIMARY KEY(`id`), 
22     INDEX `ask_question_FI_1` (`user_id`),
23     CONSTRAINT `ask_question_FK_1`
24       FOREIGN KEY (`user_id`)
25       REFERENCES `ask_user` (`id`)
26 )
27 Type=InnoDB;
28 # -----------------------------------------------------------------------
29 # ask_answer
30 # -----------------------------------------------------------------------
31 DROP TABLE IF EXISTS `ask_answer`;
32
33 CREATE TABLE `ask_answer`(
34     `id` INTEGER NOT NULL AUTO_INCREMENT,
35     `question_id` INTEGER ,
36     `user_id` INTEGER ,
37     `body` TEXT ,
38     `html_body` TEXT ,
39     `relevancy_up` INTEGER default 0 ,
40     `relevancy_down` INTEGER default 0 ,
41     `reports` INTEGER default 0 ,
42     `created_at` DATETIME , 
43     PRIMARY KEY(`id`), 
44     INDEX `ask_answer_FI_1` (`question_id`),
45     CONSTRAINT `ask_answer_FK_1`
46       FOREIGN KEY (`question_id`)
47       REFERENCES `ask_question` (`id`)
48       ON DELETE CASCADE,
49     INDEX `ask_answer_FI_2` (`user_id`),
50     CONSTRAINT `ask_answer_FK_2`
51       FOREIGN KEY (`user_id`)
52       REFERENCES `ask_user` (`id`)
53 )
54 Type=InnoDB;
55 # -----------------------------------------------------------------------
56 # ask_user
57 # -----------------------------------------------------------------------
58 DROP TABLE IF EXISTS `ask_user`;
59
60 CREATE TABLE `ask_user`(
61     `id` INTEGER NOT NULL AUTO_INCREMENT,
62     `nickname` VARCHAR(50) ,
63     `first_name` VARCHAR(100) ,
64     `last_name` VARCHAR(100) ,
65     `email` VARCHAR(100) ,
66     `sha1_password` VARCHAR(40) ,
67     `salt` VARCHAR(32) ,
68     `has_paypal` INTEGER default 0 ,
69     `want_to_be_moderator` INTEGER default 0 ,
70     `is_moderator` INTEGER default 0 ,
71     `is_administrator` INTEGER default 0 ,
72     `deletions` INTEGER default 0 ,
73     `created_at` DATETIME , 
74     PRIMARY KEY(`id`))
75 Type=InnoDB;
76 # -----------------------------------------------------------------------
77 # ask_interest
78 # -----------------------------------------------------------------------
79 DROP TABLE IF EXISTS `ask_interest`;
80
81 CREATE TABLE `ask_interest`(
82     `question_id` INTEGER NOT NULL ,
83     `user_id` INTEGER NOT NULL ,
84     `created_at` DATETIME , 
85     PRIMARY KEY(`question_id`,`user_id`), 
86     CONSTRAINT `ask_interest_FK_1`
87       FOREIGN KEY (`question_id`)
88       REFERENCES `ask_question` (`id`)
89       ON DELETE CASCADE,
90     INDEX `ask_interest_FI_2` (`user_id`),
91     CONSTRAINT `ask_interest_FK_2`
92       FOREIGN KEY (`user_id`)
93       REFERENCES `ask_user` (`id`)
94 )
95 Type=InnoDB;
96 # -----------------------------------------------------------------------
97 # ask_relevancy
98 # -----------------------------------------------------------------------
99 DROP TABLE IF EXISTS `ask_relevancy`;
100
101 CREATE TABLE `ask_relevancy`(
102     `answer_id` INTEGER NOT NULL ,
103     `user_id` INTEGER NOT NULL ,
104     `score` INTEGER ,
105     `created_at` DATETIME , 
106     PRIMARY KEY(`answer_id`,`user_id`), 
107     CONSTRAINT `ask_relevancy_FK_1`
108       FOREIGN KEY (`answer_id`)
109       REFERENCES `ask_answer` (`id`)
110       ON DELETE CASCADE,
111     INDEX `ask_relevancy_FI_2` (`user_id`),
112     CONSTRAINT `ask_relevancy_FK_2`
113       FOREIGN KEY (`user_id`)
114       REFERENCES `ask_user` (`id`)
115 )
116 Type=InnoDB;
117 # -----------------------------------------------------------------------
118 # ask_question_tag
119 # -----------------------------------------------------------------------
120 DROP TABLE IF EXISTS `ask_question_tag`;
121
122 CREATE TABLE `ask_question_tag`(
123     `question_id` INTEGER NOT NULL ,
124     `user_id` INTEGER NOT NULL ,
125     `created_at` DATETIME ,
126     `tag` VARCHAR(100) ,
127     `normalized_tag` VARCHAR(100) NOT NULL , 
128     PRIMARY KEY(`question_id`,`user_id`,`normalized_tag`), 
129     KEY `normalized_tag_index` (`normalized_tag`), 
130     CONSTRAINT `ask_question_tag_FK_1`
131       FOREIGN KEY (`question_id`)
132       REFERENCES `ask_question` (`id`)
133       ON DELETE CASCADE,
134     INDEX `ask_question_tag_FI_2` (`user_id`),
135     CONSTRAINT `ask_question_tag_FK_2`
136       FOREIGN KEY (`user_id`)
137       REFERENCES `ask_user` (`id`)
138 )
139 Type=InnoDB;
140 # -----------------------------------------------------------------------
141 # ask_search_index
142 # -----------------------------------------------------------------------
143 DROP TABLE IF EXISTS `ask_search_index`;
144
145 CREATE TABLE `ask_search_index`(
146     `question_id` INTEGER ,
147     `word` VARCHAR(255) ,
148     `weight` INTEGER , 
149     KEY `word_index` (`word`), 
150     INDEX `ask_search_index_FI_1` (`question_id`),
151     CONSTRAINT `ask_search_index_FK_1`
152       FOREIGN KEY (`question_id`)
153       REFERENCES `ask_question` (`id`)
154       ON DELETE CASCADE)
155 Type=InnoDB;
156 # -----------------------------------------------------------------------
157 # ask_report_question
158 # -----------------------------------------------------------------------
159 DROP TABLE IF EXISTS `ask_report_question`;
160
161 CREATE TABLE `ask_report_question`(
162     `question_id` INTEGER NOT NULL ,
163     `user_id` INTEGER NOT NULL ,
164     `created_at` DATETIME , 
165     PRIMARY KEY(`question_id`,`user_id`), 
166     CONSTRAINT `ask_report_question_FK_1`
167       FOREIGN KEY (`question_id`)
168       REFERENCES `ask_question` (`id`)
169       ON DELETE CASCADE,
170     INDEX `ask_report_question_FI_2` (`user_id`),
171     CONSTRAINT `ask_report_question_FK_2`
172       FOREIGN KEY (`user_id`)
173       REFERENCES `ask_user` (`id`)
174 )
175 Type=InnoDB;
176 # -----------------------------------------------------------------------
177 # ask_report_answer
178 # -----------------------------------------------------------------------
179 DROP TABLE IF EXISTS `ask_report_answer`;
180
181 CREATE TABLE `ask_report_answer`(
182     `answer_id` INTEGER NOT NULL ,
183     `user_id` INTEGER NOT NULL ,
184     `created_at` DATETIME , 
185     PRIMARY KEY(`answer_id`,`user_id`), 
186     CONSTRAINT `ask_report_answer_FK_1`
187       FOREIGN KEY (`answer_id`)
188       REFERENCES `ask_answer` (`id`)
189       ON DELETE CASCADE,
190     INDEX `ask_report_answer_FI_2` (`user_id`),
191     CONSTRAINT `ask_report_answer_FK_2`
192       FOREIGN KEY (`user_id`)
193       REFERENCES `ask_user` (`id`)
194 )
195 Type=InnoDB;
196  
197  
198  
199  
200  
201  
202  
203  
204  
205 # This restores the fkey checks, after having unset them
206 # in database-start.tpl
207
208 SET FOREIGN_KEY_CHECKS = 1;
209
Note: See TracBrowser for help on using the browser.