create.sql

javaBB,一套很不錯的JSP源碼,特共享給大家

-- TT 2006-10-09: drop table jbb_answer_notify
DROP TABLE jbb_answer_notify;
DROP TABLE jbb_badwords;
DROP TABLE jbb_category;
DROP TABLE jbb_forum;
-- TT 2006-10-09: drop table jbb_forum_top_user
DROP TABLE jbb_forum_top_user;
DROP TABLE jbb_posts;
DROP TABLE jbb_privmsg_inbox;
DROP TABLE jbb_privmsg_outbox;
DROP TABLE jbb_smiles;
DROP TABLE jbb_topics;
DROP TABLE jbb_users;

-- TT 2006-10-09: create table jbb_answer_notify
CREATE TABLE jbb_answer_notify (
 id_topic bigint default '0' NOT NULL,
 id_user bigint default '0' NOT NULL,
 PRIMARY KEY  (id_topic,id_user)
);
--KEY jbb_answer_notify_ind1_fk (id_topic),
--KEY jbb_answer_notify_ind2_fk (id_user),
--KEY JBB_ANSWER_NOTIFY_FK (id_topic),
--KEY JBB_ANSWER_NOTIFYIDEX_FK (id_user)


CREATE TABLE jbb_badwords (
 id_badword bigint NOT NULL PRIMARY KEY,
 word varchar(50) NULL,
 replacement varchar(50) NULL
);

CREATE TABLE jbb_category (
 id_category bigint NOT NULL PRIMARY KEY,
 nm_category varchar(200) NULL,
 cat_order int default '0' NOT NULL
);

CREATE TABLE jbb_forum (
 id_forum bigint NOT NULL PRIMARY KEY,
 nome varchar(200) NULL,
 descricao varchar(200) NULL,
 forum_status int NULL,
 forum_order int NULL,
 id_category bigint default '0' NOT NULL,
 last_post_id bigint default NULL,
 topic_count bigint default NULL,
 post_count bigint default NULL,
 last_page_post bigint default '1',
 last_post_user_name varchar(255) default NULL,
 last_post_user_id bigint default NULL,
 last_post_date timestamp NOT NULL,
 last_topic_id bigint default NULL
);

-- TT 2006-10-09: create table jbb_forum_top_user
CREATE TABLE jbb_forum_top_user (
 id_user int default '0' NOT NULL,
 post_count int default '0' NOT NULL,
 date_row timestamp default CURRENT_TIMESTAMP NOT NULL,
 id_forum int default NULL
);
--date_row timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
--KEY id_user (id_user),
--KEY id_forum (`id_forum`)

-- TT 2006-10-09: remove post_body, create jbb_posts_text instead
CREATE TABLE jbb_posts (
 id_post bigint NOT NULL PRIMARY KEY,
 id_user bigint default '0' NOT NULL,
 id_topic bigint default '0' NOT NULL,
 data_post timestamp NOT NULL,
 assunto varchar(200) default NULL,
--post_body longvarchar,
 sig int default '0',
 ip varchar(20) default NULL,
 post_state int default '0'
);

-- TT 2006-10-09: create jbb_posts_text
CREATE TABLE jbb_posts_text (
 id_post bigint default '0' NOT NULL PRIMARY KEY,
 post_body longvarchar
);

CREATE TABLE jbb_privmsg_inbox (
 id_privmsg int NOT NULL PRIMARY KEY,
 id_user_from int default '0' NOT NULL,
 id_user_to int default '0' NOT NULL,
 privmsg_topic varchar(255) default '' NOT NULL,
 privmsg_text longvarchar NOT NULL,
 privmsg_date timestamp NOT NULL,
 allow_bbcode int default '0',
 allow_smiles int default '0',
 attach_sign int default '1',
 flag_read int default '0'
);

CREATE TABLE jbb_privmsg_outbox (
 id_privmsg int NOT NULL PRIMARY KEY,
 id_user_from int default '0' NOT NULL,
 id_user_to int default '0' NOT NULL,
 privmsg_topic varchar(255) default '' NOT NULL,
 privmsg_text longvarchar NOT NULL,
 privmsg_date timestamp NOT NULL,
 allow_bbcode int default '0',
 allow_smiles int default '0',
 attach_sign int default '1',
 flag_read int default '0'
);

-- TT 2006-10-09: create jbb_rank table, change autoincrement
CREATE TABLE jbb_rank (
--rank_id int NOT NULL auto_increment PRIMARY KEY,
 rank_id int GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
 rank_name varchar(255) default NULL,
 rank_min int default NULL,
 rank_max int default NULL,
 rank_image varchar(255) default NULL
);

CREATE TABLE jbb_smiles (
 id bigint NOT NULL PRIMARY KEY,
 symbol varchar(50) default '' NOT NULL,
 emoticon varchar(75) default '' NOT NULL,
 filename varchar(100) default '' NOT NULL
);

CREATE TABLE jbb_topics (
 id_topic bigint NOT NULL PRIMARY KEY,
 id_user bigint default '0' NOT NULL,
 id_forum bigint default '0' NOT NULL,
 title_topic varchar(200) default '' NOT NULL,
 data_topico timestamp NOT NULL,
 visualizacoes int default '0',
 respostas int default '0',