2 # phpBB2 - MySQL schema
4 # $Id: mysql_schema.sql,v 1.35.2.11 2005/12/29 11:51:12 acydburn Exp $
8 # Table structure for table 'phpbb_auth_access'
10 CREATE TABLE phpbb_auth_access (
11 group_id mediumint(8) DEFAULT '0' NOT NULL,
12 forum_id smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
13 auth_view tinyint(1) DEFAULT '0' NOT NULL,
14 auth_read tinyint(1) DEFAULT '0' NOT NULL,
15 auth_post tinyint(1) DEFAULT '0' NOT NULL,
16 auth_reply tinyint(1) DEFAULT '0' NOT NULL,
17 auth_edit tinyint(1) DEFAULT '0' NOT NULL,
18 auth_delete tinyint(1) DEFAULT '0' NOT NULL,
19 auth_sticky tinyint(1) DEFAULT '0' NOT NULL,
20 auth_announce tinyint(1) DEFAULT '0' NOT NULL,
21 auth_vote tinyint(1) DEFAULT '0' NOT NULL,
22 auth_pollcreate tinyint(1) DEFAULT '0' NOT NULL,
23 auth_attachments tinyint(1) DEFAULT '0' NOT NULL,
24 auth_mod tinyint(1) DEFAULT '0' NOT NULL,
25 KEY group_id (group_id),
26 KEY forum_id (forum_id)
31 # Table structure for table 'phpbb_user_group'
33 CREATE TABLE phpbb_user_group (
34 group_id mediumint(8) DEFAULT '0' NOT NULL,
35 user_id mediumint(8) DEFAULT '0' NOT NULL,
36 user_pending tinyint(1),
37 KEY group_id (group_id),
42 # Table structure for table 'phpbb_groups'
44 CREATE TABLE phpbb_groups (
45 group_id mediumint(8) NOT NULL auto_increment,
46 group_type tinyint(4) DEFAULT '1' NOT NULL,
47 group_name varchar(40) NOT NULL,
48 group_description varchar(255) NOT NULL,
49 group_moderator mediumint(8) DEFAULT '0' NOT NULL,
50 group_single_user tinyint(1) DEFAULT '1' NOT NULL,
51 PRIMARY KEY (group_id),
52 KEY group_single_user (group_single_user)
56 # --------------------------------------------------------
58 # Table structure for table 'phpbb_banlist'
60 CREATE TABLE phpbb_banlist (
61 ban_id mediumint(8) UNSIGNED NOT NULL auto_increment,
62 ban_userid mediumint(8) NOT NULL,
63 ban_ip char(8) NOT NULL,
64 ban_email varchar(255),
66 KEY ban_ip_user_id (ban_ip, ban_userid)
70 # --------------------------------------------------------
72 # Table structure for table 'phpbb_categories'
74 CREATE TABLE phpbb_categories (
75 cat_id mediumint(8) UNSIGNED NOT NULL auto_increment,
76 cat_title varchar(100),
77 cat_order mediumint(8) UNSIGNED NOT NULL,
79 KEY cat_order (cat_order)
83 # --------------------------------------------------------
85 # Table structure for table 'phpbb_config'
87 CREATE TABLE phpbb_config (
88 config_name varchar(255) NOT NULL,
89 config_value varchar(255) NOT NULL,
90 PRIMARY KEY (config_name)
94 # --------------------------------------------------------
96 # Table structure for table 'phpbb_confirm'
98 CREATE TABLE phpbb_confirm (
99 confirm_id char(32) DEFAULT '' NOT NULL,
100 session_id char(32) DEFAULT '' NOT NULL,
101 code char(6) DEFAULT '' NOT NULL,
102 PRIMARY KEY (session_id,confirm_id)
106 # --------------------------------------------------------
108 # Table structure for table 'phpbb_disallow'
110 CREATE TABLE phpbb_disallow (
111 disallow_id mediumint(8) UNSIGNED NOT NULL auto_increment,
112 disallow_username varchar(25) DEFAULT '' NOT NULL,
113 PRIMARY KEY (disallow_id)
117 # --------------------------------------------------------
119 # Table structure for table 'phpbb_forum_prune'
121 CREATE TABLE phpbb_forum_prune (
122 prune_id mediumint(8) UNSIGNED NOT NULL auto_increment,
123 forum_id smallint(5) UNSIGNED NOT NULL,
124 prune_days smallint(5) UNSIGNED NOT NULL,
125 prune_freq smallint(5) UNSIGNED NOT NULL,
126 PRIMARY KEY(prune_id),
127 KEY forum_id (forum_id)
131 # --------------------------------------------------------
133 # Table structure for table 'phpbb_forums'
135 CREATE TABLE phpbb_forums (
136 forum_id smallint(5) UNSIGNED NOT NULL,
137 cat_id mediumint(8) UNSIGNED NOT NULL,
138 forum_name varchar(150),
140 forum_status tinyint(4) DEFAULT '0' NOT NULL,
141 forum_order mediumint(8) UNSIGNED DEFAULT '1' NOT NULL,
142 forum_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
143 forum_topics mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
144 forum_last_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
146 prune_enable tinyint(1) DEFAULT '0' NOT NULL,
147 auth_view tinyint(2) DEFAULT '0' NOT NULL,
148 auth_read tinyint(2) DEFAULT '0' NOT NULL,
149 auth_post tinyint(2) DEFAULT '0' NOT NULL,
150 auth_reply tinyint(2) DEFAULT '0' NOT NULL,
151 auth_edit tinyint(2) DEFAULT '0' NOT NULL,
152 auth_delete tinyint(2) DEFAULT '0' NOT NULL,
153 auth_sticky tinyint(2) DEFAULT '0' NOT NULL,
154 auth_announce tinyint(2) DEFAULT '0' NOT NULL,
155 auth_vote tinyint(2) DEFAULT '0' NOT NULL,
156 auth_pollcreate tinyint(2) DEFAULT '0' NOT NULL,
157 auth_attachments tinyint(2) DEFAULT '0' NOT NULL,
158 PRIMARY KEY (forum_id),
159 KEY forums_order (forum_order),
161 KEY forum_last_post_id (forum_last_post_id)
165 # --------------------------------------------------------
167 # Table structure for table 'phpbb_posts'
169 CREATE TABLE phpbb_posts (
170 post_id mediumint(8) UNSIGNED NOT NULL auto_increment,
171 topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
172 forum_id smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
173 poster_id mediumint(8) DEFAULT '0' NOT NULL,
174 post_time int(11) DEFAULT '0' NOT NULL,
175 poster_ip char(8) NOT NULL,
176 post_username varchar(25),
177 enable_bbcode tinyint(1) DEFAULT '1' NOT NULL,
178 enable_html tinyint(1) DEFAULT '0' NOT NULL,
179 enable_smilies tinyint(1) DEFAULT '1' NOT NULL,
180 enable_sig tinyint(1) DEFAULT '1' NOT NULL,
181 post_edit_time int(11),
182 post_edit_count smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
183 PRIMARY KEY (post_id),
184 KEY forum_id (forum_id),
185 KEY topic_id (topic_id),
186 KEY poster_id (poster_id),
187 KEY post_time (post_time)
191 # --------------------------------------------------------
193 # Table structure for table 'phpbb_posts_text'
195 CREATE TABLE phpbb_posts_text (
196 post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
197 bbcode_uid char(10) DEFAULT '' NOT NULL,
198 post_subject char(60),
200 PRIMARY KEY (post_id)
204 # --------------------------------------------------------
206 # Table structure for table 'phpbb_privmsgs'
208 CREATE TABLE phpbb_privmsgs (
209 privmsgs_id mediumint(8) UNSIGNED NOT NULL auto_increment,
210 privmsgs_type tinyint(4) DEFAULT '0' NOT NULL,
211 privmsgs_subject varchar(255) DEFAULT '0' NOT NULL,
212 privmsgs_from_userid mediumint(8) DEFAULT '0' NOT NULL,
213 privmsgs_to_userid mediumint(8) DEFAULT '0' NOT NULL,
214 privmsgs_date int(11) DEFAULT '0' NOT NULL,
215 privmsgs_ip char(8) NOT NULL,
216 privmsgs_enable_bbcode tinyint(1) DEFAULT '1' NOT NULL,
217 privmsgs_enable_html tinyint(1) DEFAULT '0' NOT NULL,
218 privmsgs_enable_smilies tinyint(1) DEFAULT '1' NOT NULL,
219 privmsgs_attach_sig tinyint(1) DEFAULT '1' NOT NULL,
220 PRIMARY KEY (privmsgs_id),
221 KEY privmsgs_from_userid (privmsgs_from_userid),
222 KEY privmsgs_to_userid (privmsgs_to_userid)
226 # --------------------------------------------------------
228 # Table structure for table 'phpbb_privmsgs_text'
230 CREATE TABLE phpbb_privmsgs_text (
231 privmsgs_text_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
232 privmsgs_bbcode_uid char(10) DEFAULT '0' NOT NULL,
234 PRIMARY KEY (privmsgs_text_id)
238 # --------------------------------------------------------
240 # Table structure for table 'phpbb_ranks'
242 CREATE TABLE phpbb_ranks (
243 rank_id smallint(5) UNSIGNED NOT NULL auto_increment,
244 rank_title varchar(50) NOT NULL,
245 rank_min mediumint(8) DEFAULT '0' NOT NULL,
246 rank_special tinyint(1) DEFAULT '0',
247 rank_image varchar(255),
248 PRIMARY KEY (rank_id)
252 # --------------------------------------------------------
254 # Table structure for table `phpbb_search_results`
256 CREATE TABLE phpbb_search_results (
257 search_id int(11) UNSIGNED NOT NULL default '0',
258 session_id char(32) NOT NULL default '',
259 search_array text NOT NULL,
260 PRIMARY KEY (search_id),
261 KEY session_id (session_id)
265 # --------------------------------------------------------
267 # Table structure for table `phpbb_search_wordlist`
269 CREATE TABLE phpbb_search_wordlist (
270 word_text varchar(50) binary NOT NULL default '',
271 word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
272 word_common tinyint(1) unsigned NOT NULL default '0',
273 PRIMARY KEY (word_text),
274 KEY word_id (word_id)
277 # --------------------------------------------------------
279 # Table structure for table `phpbb_search_wordmatch`
281 CREATE TABLE phpbb_search_wordmatch (
282 post_id mediumint(8) UNSIGNED NOT NULL default '0',
283 word_id mediumint(8) UNSIGNED NOT NULL default '0',
284 title_match tinyint(1) NOT NULL default '0',
285 KEY post_id (post_id),
286 KEY word_id (word_id)
290 # --------------------------------------------------------
292 # Table structure for table 'phpbb_sessions'
294 # Note that if you're running 3.23.x you may want to make
295 # this table a type HEAP. This type of table is stored
296 # within system memory and therefore for big busy boards
297 # is likely to be noticeably faster than continually
298 # writing to disk ...
300 CREATE TABLE phpbb_sessions (
301 session_id char(32) DEFAULT '' NOT NULL,
302 session_user_id mediumint(8) DEFAULT '0' NOT NULL,
303 session_start int(11) DEFAULT '0' NOT NULL,
304 session_time int(11) DEFAULT '0' NOT NULL,
305 session_ip char(8) DEFAULT '0' NOT NULL,
306 session_page int(11) DEFAULT '0' NOT NULL,
307 session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
308 session_admin tinyint(2) DEFAULT '0' NOT NULL,
309 PRIMARY KEY (session_id),
310 KEY session_user_id (session_user_id),
311 KEY session_id_ip_user_id (session_id, session_ip, session_user_id)
314 # --------------------------------------------------------
316 # Table structure for table `phpbb_sessions_keys`
318 CREATE TABLE phpbb_sessions_keys (
319 key_id varchar(32) DEFAULT '0' NOT NULL,
320 user_id mediumint(8) DEFAULT '0' NOT NULL,
321 last_ip varchar(8) DEFAULT '0' NOT NULL,
322 last_login int(11) DEFAULT '0' NOT NULL,
323 PRIMARY KEY (key_id, user_id),
324 KEY last_login (last_login)
328 # --------------------------------------------------------
330 # Table structure for table 'phpbb_smilies'
332 CREATE TABLE phpbb_smilies (
333 smilies_id smallint(5) UNSIGNED NOT NULL auto_increment,
335 smile_url varchar(100),
336 emoticon varchar(75),
337 PRIMARY KEY (smilies_id)
341 # --------------------------------------------------------
343 # Table structure for table 'phpbb_themes'
345 CREATE TABLE phpbb_themes (
346 themes_id mediumint(8) UNSIGNED NOT NULL auto_increment,
347 template_name varchar(30) NOT NULL default '',
348 style_name varchar(30) NOT NULL default '',
349 head_stylesheet varchar(100) default NULL,
350 body_background varchar(100) default NULL,
351 body_bgcolor varchar(6) default NULL,
352 body_text varchar(6) default NULL,
353 body_link varchar(6) default NULL,
354 body_vlink varchar(6) default NULL,
355 body_alink varchar(6) default NULL,
356 body_hlink varchar(6) default NULL,
357 tr_color1 varchar(6) default NULL,
358 tr_color2 varchar(6) default NULL,
359 tr_color3 varchar(6) default NULL,
360 tr_class1 varchar(25) default NULL,
361 tr_class2 varchar(25) default NULL,
362 tr_class3 varchar(25) default NULL,
363 th_color1 varchar(6) default NULL,
364 th_color2 varchar(6) default NULL,
365 th_color3 varchar(6) default NULL,
366 th_class1 varchar(25) default NULL,
367 th_class2 varchar(25) default NULL,
368 th_class3 varchar(25) default NULL,
369 td_color1 varchar(6) default NULL,
370 td_color2 varchar(6) default NULL,
371 td_color3 varchar(6) default NULL,
372 td_class1 varchar(25) default NULL,
373 td_class2 varchar(25) default NULL,
374 td_class3 varchar(25) default NULL,
375 fontface1 varchar(50) default NULL,
376 fontface2 varchar(50) default NULL,
377 fontface3 varchar(50) default NULL,
378 fontsize1 tinyint(4) default NULL,
379 fontsize2 tinyint(4) default NULL,
380 fontsize3 tinyint(4) default NULL,
381 fontcolor1 varchar(6) default NULL,
382 fontcolor2 varchar(6) default NULL,
383 fontcolor3 varchar(6) default NULL,
384 span_class1 varchar(25) default NULL,
385 span_class2 varchar(25) default NULL,
386 span_class3 varchar(25) default NULL,
387 img_size_poll smallint(5) UNSIGNED,
388 img_size_privmsg smallint(5) UNSIGNED,
389 PRIMARY KEY (themes_id)
393 # --------------------------------------------------------
395 # Table structure for table 'phpbb_themes_name'
397 CREATE TABLE phpbb_themes_name (
398 themes_id smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
399 tr_color1_name char(50),
400 tr_color2_name char(50),
401 tr_color3_name char(50),
402 tr_class1_name char(50),
403 tr_class2_name char(50),
404 tr_class3_name char(50),
405 th_color1_name char(50),
406 th_color2_name char(50),
407 th_color3_name char(50),
408 th_class1_name char(50),
409 th_class2_name char(50),
410 th_class3_name char(50),
411 td_color1_name char(50),
412 td_color2_name char(50),
413 td_color3_name char(50),
414 td_class1_name char(50),
415 td_class2_name char(50),
416 td_class3_name char(50),
417 fontface1_name char(50),
418 fontface2_name char(50),
419 fontface3_name char(50),
420 fontsize1_name char(50),
421 fontsize2_name char(50),
422 fontsize3_name char(50),
423 fontcolor1_name char(50),
424 fontcolor2_name char(50),
425 fontcolor3_name char(50),
426 span_class1_name char(50),
427 span_class2_name char(50),
428 span_class3_name char(50),
429 PRIMARY KEY (themes_id)
433 # --------------------------------------------------------
435 # Table structure for table 'phpbb_topics'
437 CREATE TABLE phpbb_topics (
438 topic_id mediumint(8) UNSIGNED NOT NULL auto_increment,
439 forum_id smallint(8) UNSIGNED DEFAULT '0' NOT NULL,
440 topic_title char(60) NOT NULL,
441 topic_poster mediumint(8) DEFAULT '0' NOT NULL,
442 topic_time int(11) DEFAULT '0' NOT NULL,
443 topic_views mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
444 topic_replies mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
445 topic_status tinyint(3) DEFAULT '0' NOT NULL,
446 topic_vote tinyint(1) DEFAULT '0' NOT NULL,
447 topic_type tinyint(3) DEFAULT '0' NOT NULL,
448 topic_first_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
449 topic_last_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
450 topic_moved_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
451 PRIMARY KEY (topic_id),
452 KEY forum_id (forum_id),
453 KEY topic_moved_id (topic_moved_id),
454 KEY topic_status (topic_status),
455 KEY topic_type (topic_type)
459 # --------------------------------------------------------
461 # Table structure for table 'phpbb_topics_watch'
463 CREATE TABLE phpbb_topics_watch (
464 topic_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
465 user_id mediumint(8) NOT NULL DEFAULT '0',
466 notify_status tinyint(1) NOT NULL default '0',
467 KEY topic_id (topic_id),
468 KEY user_id (user_id),
469 KEY notify_status (notify_status)
473 # --------------------------------------------------------
475 # Table structure for table 'phpbb_users'
477 CREATE TABLE phpbb_users (
478 user_id mediumint(8) NOT NULL,
479 user_active tinyint(1) DEFAULT '1',
480 username varchar(25) NOT NULL,
481 user_password varchar(32) NOT NULL,
482 user_session_time int(11) DEFAULT '0' NOT NULL,
483 user_session_page smallint(5) DEFAULT '0' NOT NULL,
484 user_lastvisit int(11) DEFAULT '0' NOT NULL,
485 user_regdate int(11) DEFAULT '0' NOT NULL,
486 user_level tinyint(4) DEFAULT '0',
487 user_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
488 user_timezone decimal(5,2) DEFAULT '0' NOT NULL,
489 user_style tinyint(4),
490 user_lang varchar(255),
491 user_dateformat varchar(14) DEFAULT 'd M Y H:i' NOT NULL,
492 user_new_privmsg smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
493 user_unread_privmsg smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
494 user_last_privmsg int(11) DEFAULT '0' NOT NULL,
495 user_login_tries smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
496 user_last_login_try int(11) DEFAULT '0' NOT NULL,
497 user_emailtime int(11),
498 user_viewemail tinyint(1),
499 user_attachsig tinyint(1),
500 user_allowhtml tinyint(1) DEFAULT '1',
501 user_allowbbcode tinyint(1) DEFAULT '1',
502 user_allowsmile tinyint(1) DEFAULT '1',
503 user_allowavatar tinyint(1) DEFAULT '1' NOT NULL,
504 user_allow_pm tinyint(1) DEFAULT '1' NOT NULL,
505 user_allow_viewonline tinyint(1) DEFAULT '1' NOT NULL,
506 user_notify tinyint(1) DEFAULT '1' NOT NULL,
507 user_notify_pm tinyint(1) DEFAULT '0' NOT NULL,
508 user_popup_pm tinyint(1) DEFAULT '0' NOT NULL,
509 user_rank int(11) DEFAULT '0',
510 user_avatar varchar(100),
511 user_avatar_type tinyint(4) DEFAULT '0' NOT NULL,
512 user_email varchar(255),
513 user_icq varchar(15),
514 user_website varchar(100),
515 user_from varchar(100),
517 user_sig_bbcode_uid char(10),
518 user_aim varchar(255),
519 user_yim varchar(255),
520 user_msnm varchar(255),
521 user_occ varchar(100),
522 user_interests varchar(255),
523 user_actkey varchar(32),
524 user_newpasswd varchar(32),
525 PRIMARY KEY (user_id),
526 KEY user_session_time (user_session_time)
530 # --------------------------------------------------------
532 # Table structure for table 'phpbb_vote_desc'
534 CREATE TABLE phpbb_vote_desc (
535 vote_id mediumint(8) UNSIGNED NOT NULL auto_increment,
536 topic_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
537 vote_text text NOT NULL,
538 vote_start int(11) NOT NULL DEFAULT '0',
539 vote_length int(11) NOT NULL DEFAULT '0',
540 PRIMARY KEY (vote_id),
541 KEY topic_id (topic_id)
545 # --------------------------------------------------------
547 # Table structure for table 'phpbb_vote_results'
549 CREATE TABLE phpbb_vote_results (
550 vote_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
551 vote_option_id tinyint(4) UNSIGNED NOT NULL DEFAULT '0',
552 vote_option_text varchar(255) NOT NULL,
553 vote_result int(11) NOT NULL DEFAULT '0',
554 KEY vote_option_id (vote_option_id),
555 KEY vote_id (vote_id)
559 # --------------------------------------------------------
561 # Table structure for table 'phpbb_vote_voters'
563 CREATE TABLE phpbb_vote_voters (
564 vote_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
565 vote_user_id mediumint(8) NOT NULL DEFAULT '0',
566 vote_user_ip char(8) NOT NULL,
567 KEY vote_id (vote_id),
568 KEY vote_user_id (vote_user_id),
569 KEY vote_user_ip (vote_user_ip)
573 # --------------------------------------------------------
575 # Table structure for table 'phpbb_words'
577 CREATE TABLE phpbb_words (
578 word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
579 word char(100) NOT NULL,
580 replacement char(100) NOT NULL,
581 PRIMARY KEY (word_id)