postgresql_db_struct.sql

个人认为是最好的Java论坛源码

  user_new_privmsg INTEGER NOT NULL DEFAULT 0,
  user_unread_privmsg INTEGER NOT NULL DEFAULT 0,
  user_last_privmsg timestamp NULL,
  user_emailtime timestamp NULL,
  user_viewemail INTEGER DEFAULT 0,
  user_attachsig INTEGER DEFAULT 1,
  user_allowhtml INTEGER DEFAULT 0,
  user_allowbbcode INTEGER DEFAULT 1,
  user_allowsmilies INTEGER DEFAULT 1,
  user_allowavatar INTEGER DEFAULT 1,
  user_allow_pm INTEGER DEFAULT 1,
  user_allow_viewonline INTEGER DEFAULT 1,
  user_notify INTEGER DEFAULT 1,
  user_notify_pm INTEGER DEFAULT 1,
  user_popup_pm INTEGER DEFAULT 1,
  rank_id INTEGER DEFAULT 1,
  user_avatar VARCHAR(100) DEFAULT NULL,
  user_avatar_type INTEGER NOT NULL DEFAULT 0,
  user_email VARCHAR(255) NOT NULL DEFAULT '',
  user_icq VARCHAR(15) DEFAULT NULL,
  user_website VARCHAR(100) DEFAULT NULL,
  user_from VARCHAR(100) DEFAULT NULL,
  user_sig TEXT,
  user_sig_bbcode_uid VARCHAR(10) DEFAULT NULL,
  user_aim VARCHAR(255) DEFAULT NULL,
  user_yim VARCHAR(255) DEFAULT NULL,
  user_msnm VARCHAR(255) DEFAULT NULL,
  user_occ VARCHAR(100) DEFAULT NULL,
  user_interests VARCHAR(255) DEFAULT NULL,
  user_actkey VARCHAR(32) DEFAULT NULL,
  gender CHAR(1) DEFAULT NULL,
  themes_id INTEGER DEFAULT NULL,
  deleted INTEGER DEFAULT NULL,
  user_viewonline INTEGER DEFAULT 1,
  security_hash VARCHAR(32),
  user_karma NUMERIC(10,2),
  PRIMARY KEY  (user_id)
);


--
-- Table structure for table 'jforum_vote_desc'
--

CREATE SEQUENCE jforum_vote_desc_seq;
CREATE TABLE jforum_vote_desc (
  vote_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_vote_desc_seq'),
  topic_id INTEGER NOT NULL DEFAULT 0,
  vote_text TEXT NOT NULL,
  vote_start INTEGER NOT NULL DEFAULT 0,
  vote_length INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY  (vote_id)
);

--
-- Table structure for table 'jforum_vote_results'
--

CREATE TABLE jforum_vote_results (
  vote_id INTEGER NOT NULL DEFAULT 0,
  vote_option_id INTEGER NOT NULL DEFAULT 0,
  vote_option_text VARCHAR(255) NOT NULL DEFAULT '',
  vote_result INTEGER NOT NULL DEFAULT 0
);

--
-- Table structure for table 'jforum_vote_voters'
--

CREATE TABLE jforum_vote_voters (
  vote_id INTEGER NOT NULL DEFAULT 0,
  vote_user_id INTEGER NOT NULL DEFAULT 0,
  vote_user_ip CHAR(8) NOT NULL DEFAULT ''
);

--
-- Table structure for table 'jforum_words'
--

CREATE SEQUENCE jforum_words_seq;
CREATE TABLE jforum_words (
  word_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_words_seq'),
  word VARCHAR(100) NOT NULL DEFAULT '',
  replacement VARCHAR(100) NOT NULL DEFAULT '',
  PRIMARY KEY  (word_id)
);

--
-- Table structure for table 'jforum_search_words'
--
CREATE SEQUENCE jforum_search_words_seq;
CREATE TABLE jforum_search_words (
  word_id INT NOT NULL PRIMARY KEY DEFAULT NEXTVAL('jforum_search_words_seq'),
  word VARCHAR(100) NOT NULL,
  word_hash INT
);
CREATE INDEX idx_sw_word ON jforum_search_words(word);
CREATE INDEX idx_sw_hash ON jforum_search_words(word_hash);

-- 
-- Table structure for table 'jforum_search_wordmatch'
--
CREATE TABLE jforum_search_wordmatch (
  post_id INT NOT NULL,
  word_id INT NOT NULL,
  title_match INTEGER DEFAULT 0
);
CREATE INDEX idx_swm_post ON jforum_search_wordmatch(post_id);
CREATE INDEX idx_swm_word ON jforum_search_wordmatch(word_id);
CREATE INDEX idx_swm_title ON jforum_search_wordmatch(title_match);

--
-- Table structure for table 'jforum_search_results'
--
CREATE TABLE jforum_search_results (
  topic_id INT NOT NULL,
  session VARCHAR(50),
  search_time TIMESTAMP
);
CREATE INDEX idx_sr_topic ON jforum_search_results(topic_id);


CREATE TABLE jforum_search_topics (
  topic_id INTEGER NOT NULL,
  forum_id INTEGER NOT NULL DEFAULT 0,
  topic_title VARCHAR(100) NOT NULL DEFAULT '',
  user_id INTEGER NOT NULL DEFAULT 0,
  topic_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  topic_views INTEGER DEFAULT 1,
  topic_replies INTEGER DEFAULT 0,
  topic_status INTEGER DEFAULT 0,
  topic_vote INTEGER DEFAULT 0,
  topic_type INTEGER DEFAULT 0,
  topic_first_post_id INTEGER DEFAULT 0,
  topic_last_post_id INTEGER NOT NULL DEFAULT 0,
  moderated INTEGER DEFAULT 0,
  session VARCHAR(50),
  search_time TIMESTAMP
);

CREATE INDEX idx_st_topic ON jforum_search_topics(topic_id);
CREATE INDEX idx_st_forum ON jforum_search_topics(forum_id);
CREATE INDEX idx_st_user ON jforum_search_topics(user_id);
CREATE INDEX idx_st_fp ON jforum_search_topics(topic_first_post_id);
CREATE INDEX idx_st_lp ON jforum_search_topics(topic_last_post_id);

--
-- Table structure for table 'jforum_karma'
--
CREATE SEQUENCE jforum_karma_seq;
CREATE TABLE jforum_karma (
	karma_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_karma_seq'),
	post_id INTEGER NOT NULL,
	topic_id INTEGER NOT NULL,
	post_user_id INTEGER NOT NULL,
	from_user_id INTEGER NOT NULL,
	points INTEGER NOT NULL,
	rate_date TIMESTAMP DEFAULT NULL,
	PRIMARY KEY(karma_id)
);

CREATE INDEX idx_krm_post ON jforum_karma(post_id);
CREATE INDEX idx_krm_topic ON jforum_karma(topic_id);
CREATE INDEX idx_krm_user ON jforum_karma(post_user_id);
CREATE INDEX idx_krm_from ON jforum_karma(from_user_id);

--
-- Table structure for table 'jforum_bookmark'
--
CREATE SEQUENCE jforum_bookmarks_seq;
CREATE TABLE jforum_bookmarks (
	bookmark_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_bookmarks_seq'),
	user_id INTEGER NOT NULL,
	relation_id INTEGER NOT NULL,
	relation_type INTEGER NOT NULL,
	public_visible INTEGER DEFAULT 1,
	title VARCHAR(255),
	description VARCHAR(255),
	PRIMARY KEY(bookmark_id)
);

CREATE INDEX idx_bok_user ON jforum_bookmarks(user_id);
CREATE INDEX idx_bok_rel ON jforum_bookmarks(relation_id);

-- 
-- Table structure for table 'jforum_quota_limit'
--
CREATE SEQUENCE jforum_quota_limit_seq;
CREATE TABLE jforum_quota_limit (
	quota_limit_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_quota_limit_seq'),
	quota_desc VARCHAR(50) NOT NULL,
	quota_limit INTEGER NOT NULL,
	quota_type INTEGER DEFAULT 1,
	PRIMARY KEY(quota_limit_id)
);

--
-- Table structure for table 'jforum_extension_groups'
--
CREATE SEQUENCE jforum_extension_groups_seq;
CREATE TABLE jforum_extension_groups (
	extension_group_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_extension_groups_seq'),
	name VARCHAR(100) NOT NULL,
	allow INTEGER DEFAULT 1, 
	upload_icon VARCHAR(100),
	download_mode INTEGER DEFAULT 1,
	PRIMARY KEY(extension_group_id)
);

-- 
-- Table structure for table 'jforum_extensions'
--
CREATE SEQUENCE jforum_extensions_seq;
CREATE TABLE jforum_extensions (
	extension_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_extensions_seq'),
	extension_group_id INTEGER NOT NULL,
	description VARCHAR(100),
	upload_icon VARCHAR(100),
	extension VARCHAR(10),
	allow INTEGER DEFAULT 1,
	PRIMARY KEY(extension_id)
);

CREATE INDEX idx_ext_group ON jforum_extensions(extension_group_id);
CREATE INDEX idx_ext_ext ON jforum_extensions(extension);

--
-- Table structure for table 'jforum_attach'
--
CREATE SEQUENCE jforum_attach_seq;
CREATE TABLE jforum_attach (
	attach_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_attach_seq'),
	post_id INTEGER,
	privmsgs_id INTEGER,
	user_id INTEGER NOT NULL,
	PRIMARY KEY(attach_id)
);

CREATE INDEX idx_att_post ON jforum_attach(post_id);
CREATE INDEX idx_att_priv ON jforum_attach(privmsgs_id);
CREATE INDEX idx_att_user ON jforum_attach(user_id);

-- 
-- Table structure for table 'jforum_attach_desc'
--
CREATE SEQUENCE jforum_attach_desc_seq;
CREATE TABLE jforum_attach_desc (
	attach_desc_id INTEGER NOT NULL PRIMARY KEY DEFAULT NEXTVAL('jforum_attach_desc_seq'),
	attach_id INTEGER NOT NULL,
	physical_filename VARCHAR(255) NOT NULL,
	real_filename VARCHAR(255) NOT NULL,
	download_count INTEGER,
	description VARCHAR(255),
	mimetype VARCHAR(50),
	filesize NUMERIC(20),
	upload_time DATE,
	thumb INTEGER DEFAULT 0,
	extension_id INTEGER
);

CREATE INDEX idx_att_d_att ON jforum_attach_desc(attach_id);
CREATE INDEX idx_att_d_ext ON jforum_attach_desc(extension_id);

--
-- Table structure for table 'jforum_attach_quota'
--
CREATE SEQUENCE jforum_attach_quota_seq;
CREATE TABLE jforum_attach_quota (
	attach_quota_id INTEGER NOT NULL DEFAULT NEXTVAL('jforum_attach_quota_seq'),
	group_id INTEGER NOT NULL,
	quota_limit_id INTEGER NOT NULL,
	PRIMARY KEY(attach_quota_id)
);

CREATE INDEX idx_aq_group ON jforum_attach_quota(group_id);
CREATE INDEX idx_aq_ql ON jforum_attach_quota(quota_limit_id);