firebird_schema.sql

这些都是我以前学习是用到的源码

CREATE GENERATOR phpbb_forums_gen;;
SET GENERATOR phpbb_forums_gen TO 0;;

CREATE TRIGGER t_phpbb_forums FOR phpbb_forums
BEFORE INSERT
AS
BEGIN
	NEW.forum_id = GEN_ID(phpbb_forums_gen, 1);
END;;


# Table: 'phpbb_forums_access'
CREATE TABLE phpbb_forums_access (
	forum_id INTEGER DEFAULT 0 NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	session_id CHAR(32) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_forums_access ADD PRIMARY KEY (forum_id, user_id, session_id);;


# Table: 'phpbb_forums_track'
CREATE TABLE phpbb_forums_track (
	user_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	mark_time INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_forums_track ADD PRIMARY KEY (user_id, forum_id);;


# Table: 'phpbb_forums_watch'
CREATE TABLE phpbb_forums_watch (
	forum_id INTEGER DEFAULT 0 NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	notify_status INTEGER DEFAULT 0 NOT NULL
);;

CREATE INDEX phpbb_forums_watch_forum_id ON phpbb_forums_watch(forum_id);;
CREATE INDEX phpbb_forums_watch_user_id ON phpbb_forums_watch(user_id);;
CREATE INDEX phpbb_forums_watch_notify_stat ON phpbb_forums_watch(notify_status);;

# Table: 'phpbb_groups'
CREATE TABLE phpbb_groups (
	group_id INTEGER NOT NULL,
	group_type INTEGER DEFAULT 1 NOT NULL,
	group_founder_manage INTEGER DEFAULT 0 NOT NULL,
	group_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	group_desc BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	group_desc_bitfield VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	group_desc_options INTEGER DEFAULT 7 NOT NULL,
	group_desc_uid VARCHAR(5) CHARACTER SET NONE DEFAULT '' NOT NULL,
	group_display INTEGER DEFAULT 0 NOT NULL,
	group_avatar VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	group_avatar_type INTEGER DEFAULT 0 NOT NULL,
	group_avatar_width INTEGER DEFAULT 0 NOT NULL,
	group_avatar_height INTEGER DEFAULT 0 NOT NULL,
	group_rank INTEGER DEFAULT 0 NOT NULL,
	group_colour VARCHAR(6) CHARACTER SET NONE DEFAULT '' NOT NULL,
	group_sig_chars INTEGER DEFAULT 0 NOT NULL,
	group_receive_pm INTEGER DEFAULT 0 NOT NULL,
	group_message_limit INTEGER DEFAULT 0 NOT NULL,
	group_legend INTEGER DEFAULT 1 NOT NULL
);;

ALTER TABLE phpbb_groups ADD PRIMARY KEY (group_id);;

CREATE INDEX phpbb_groups_group_legend ON phpbb_groups(group_legend);;

CREATE GENERATOR phpbb_groups_gen;;
SET GENERATOR phpbb_groups_gen TO 0;;

CREATE TRIGGER t_phpbb_groups FOR phpbb_groups
BEFORE INSERT
AS
BEGIN
	NEW.group_id = GEN_ID(phpbb_groups_gen, 1);
END;;


# Table: 'phpbb_icons'
CREATE TABLE phpbb_icons (
	icons_id INTEGER NOT NULL,
	icons_url VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	icons_width INTEGER DEFAULT 0 NOT NULL,
	icons_height INTEGER DEFAULT 0 NOT NULL,
	icons_order INTEGER DEFAULT 0 NOT NULL,
	display_on_posting INTEGER DEFAULT 1 NOT NULL
);;

ALTER TABLE phpbb_icons ADD PRIMARY KEY (icons_id);;

CREATE INDEX phpbb_icons_display_on_posting ON phpbb_icons(display_on_posting);;

CREATE GENERATOR phpbb_icons_gen;;
SET GENERATOR phpbb_icons_gen TO 0;;

CREATE TRIGGER t_phpbb_icons FOR phpbb_icons
BEFORE INSERT
AS
BEGIN
	NEW.icons_id = GEN_ID(phpbb_icons_gen, 1);
END;;


# Table: 'phpbb_lang'
CREATE TABLE phpbb_lang (
	lang_id INTEGER NOT NULL,
	lang_iso VARCHAR(30) CHARACTER SET NONE DEFAULT '' NOT NULL,
	lang_dir VARCHAR(30) CHARACTER SET NONE DEFAULT '' NOT NULL,
	lang_english_name VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	lang_local_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	lang_author VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE
);;

ALTER TABLE phpbb_lang ADD PRIMARY KEY (lang_id);;

CREATE INDEX phpbb_lang_lang_iso ON phpbb_lang(lang_iso);;

CREATE GENERATOR phpbb_lang_gen;;
SET GENERATOR phpbb_lang_gen TO 0;;

CREATE TRIGGER t_phpbb_lang FOR phpbb_lang
BEFORE INSERT
AS
BEGIN
	NEW.lang_id = GEN_ID(phpbb_lang_gen, 1);
END;;


# Table: 'phpbb_log'
CREATE TABLE phpbb_log (
	log_id INTEGER NOT NULL,
	log_type INTEGER DEFAULT 0 NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	topic_id INTEGER DEFAULT 0 NOT NULL,
	reportee_id INTEGER DEFAULT 0 NOT NULL,
	log_ip VARCHAR(40) CHARACTER SET NONE DEFAULT '' NOT NULL,
	log_time INTEGER DEFAULT 0 NOT NULL,
	log_operation BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	log_data BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_log ADD PRIMARY KEY (log_id);;

CREATE INDEX phpbb_log_log_type ON phpbb_log(log_type);;
CREATE INDEX phpbb_log_forum_id ON phpbb_log(forum_id);;
CREATE INDEX phpbb_log_topic_id ON phpbb_log(topic_id);;
CREATE INDEX phpbb_log_reportee_id ON phpbb_log(reportee_id);;
CREATE INDEX phpbb_log_user_id ON phpbb_log(user_id);;

CREATE GENERATOR phpbb_log_gen;;
SET GENERATOR phpbb_log_gen TO 0;;

CREATE TRIGGER t_phpbb_log FOR phpbb_log
BEFORE INSERT
AS
BEGIN
	NEW.log_id = GEN_ID(phpbb_log_gen, 1);
END;;


# Table: 'phpbb_moderator_cache'
CREATE TABLE phpbb_moderator_cache (
	forum_id INTEGER DEFAULT 0 NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	username VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	group_id INTEGER DEFAULT 0 NOT NULL,
	group_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	display_on_index INTEGER DEFAULT 1 NOT NULL
);;

CREATE INDEX phpbb_moderator_cache_disp_idx ON phpbb_moderator_cache(display_on_index);;
CREATE INDEX phpbb_moderator_cache_forum_id ON phpbb_moderator_cache(forum_id);;

# Table: 'phpbb_modules'
CREATE TABLE phpbb_modules (
	module_id INTEGER NOT NULL,
	module_enabled INTEGER DEFAULT 1 NOT NULL,
	module_display INTEGER DEFAULT 1 NOT NULL,
	module_basename VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	module_class VARCHAR(10) CHARACTER SET NONE DEFAULT '' NOT NULL,
	parent_id INTEGER DEFAULT 0 NOT NULL,
	left_id INTEGER DEFAULT 0 NOT NULL,
	right_id INTEGER DEFAULT 0 NOT NULL,
	module_langname VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	module_mode VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	module_auth VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_modules ADD PRIMARY KEY (module_id);;

CREATE INDEX phpbb_modules_left_right_id ON phpbb_modules(left_id, right_id);;
CREATE INDEX phpbb_modules_module_enabled ON phpbb_modules(module_enabled);;
CREATE INDEX phpbb_modules_class_left_id ON phpbb_modules(module_class, left_id);;

CREATE GENERATOR phpbb_modules_gen;;
SET GENERATOR phpbb_modules_gen TO 0;;

CREATE TRIGGER t_phpbb_modules FOR phpbb_modules
BEFORE INSERT
AS
BEGIN
	NEW.module_id = GEN_ID(phpbb_modules_gen, 1);
END;;


# Table: 'phpbb_poll_options'
CREATE TABLE phpbb_poll_options (
	poll_option_id INTEGER DEFAULT 0 NOT NULL,
	topic_id INTEGER DEFAULT 0 NOT NULL,
	poll_option_text BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	poll_option_total INTEGER DEFAULT 0 NOT NULL
);;

CREATE INDEX phpbb_poll_options_poll_opt_id ON phpbb_poll_options(poll_option_id);;
CREATE INDEX phpbb_poll_options_topic_id ON phpbb_poll_options(topic_id);;

# Table: 'phpbb_poll_votes'
CREATE TABLE phpbb_poll_votes (
	topic_id INTEGER DEFAULT 0 NOT NULL,
	poll_option_id INTEGER DEFAULT 0 NOT NULL,
	vote_user_id INTEGER DEFAULT 0 NOT NULL,
	vote_user_ip VARCHAR(40) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

CREATE INDEX phpbb_poll_votes_topic_id ON phpbb_poll_votes(topic_id);;
CREATE INDEX phpbb_poll_votes_vote_user_id ON phpbb_poll_votes(vote_user_id);;
CREATE INDEX phpbb_poll_votes_vote_user_ip ON phpbb_poll_votes(vote_user_ip);;

# Table: 'phpbb_posts'
CREATE TABLE phpbb_posts (
	post_id INTEGER NOT NULL,
	topic_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	poster_id INTEGER DEFAULT 0 NOT NULL,
	icon_id INTEGER DEFAULT 0 NOT NULL,
	poster_ip VARCHAR(40) CHARACTER SET NONE DEFAULT '' NOT NULL,
	post_time INTEGER DEFAULT 0 NOT NULL,
	post_approved INTEGER DEFAULT 1 NOT NULL,
	post_reported INTEGER DEFAULT 0 NOT NULL,
	enable_bbcode INTEGER DEFAULT 1 NOT NULL,
	enable_smilies INTEGER DEFAULT 1 NOT NULL,
	enable_magic_url INTEGER DEFAULT 1 NOT NULL,
	enable_sig INTEGER DEFAULT 1 NOT NULL,
	post_username VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	post_subject VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	post_text BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	post_checksum VARCHAR(32) CHARACTER SET NONE DEFAULT '' NOT NULL,
	post_attachment INTEGER DEFAULT 0 NOT NULL,
	bbcode_bitfield VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	bbcode_uid VARCHAR(5) CHARACTER SET NONE DEFAULT '' NOT NULL,
	post_postcount INTEGER DEFAULT 1 NOT NULL,
	post_edit_time INTEGER DEFAULT 0 NOT NULL,
	post_edit_reason VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	post_edit_user INTEGER DEFAULT 0 NOT NULL,
	post_edit_count INTEGER DEFAULT 0 NOT NULL,
	post_edit_locked INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_posts ADD PRIMARY KEY (post_id);;

CREATE INDEX phpbb_posts_forum_id ON phpbb_posts(forum_id);;
CREATE INDEX phpbb_posts_topic_id ON phpbb_posts(topic_id);;
CREATE INDEX phpbb_posts_poster_ip ON phpbb_posts(poster_ip);;
CREATE INDEX phpbb_posts_poster_id ON phpbb_posts(poster_id);;
CREATE INDEX phpbb_posts_post_approved ON phpbb_posts(post_approved);;
CREATE INDEX phpbb_posts_tid_post_time ON phpbb_posts(topic_id, post_time);;

CREATE GENERATOR phpbb_posts_gen;;
SET GENERATOR phpbb_posts_gen TO 0;;

CREATE TRIGGER t_phpbb_posts FOR phpbb_posts
BEFORE INSERT
AS
BEGIN
	NEW.post_id = GEN_ID(phpbb_posts_gen, 1);
END;;


# Table: 'phpbb_privmsgs'
CREATE TABLE phpbb_privmsgs (
	msg_id INTEGER NOT NULL,
	root_level INTEGER DEFAULT 0 NOT NULL,
	author_id INTEGER DEFAULT 0 NOT NULL,
	icon_id INTEGER DEFAULT 0 NOT NULL,
	author_ip VARCHAR(40) CHARACTER SET NONE DEFAULT '' NOT NULL,
	message_time INTEGER DEFAULT 0 NOT NULL,
	enable_bbcode INTEGER DEFAULT 1 NOT NULL,
	enable_smilies INTEGER DEFAULT 1 NOT NULL,
	enable_magic_url INTEGER DEFAULT 1 NOT NULL,
	enable_sig INTEGER DEFAULT 1 NOT NULL,
	message_subject VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	message_text BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	message_edit_reason VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	message_edit_user INTEGER DEFAULT 0 NOT NULL,
	message_attachment INTEGER DEFAULT 0 NOT NULL,
	bbcode_bitfield VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	bbcode_uid VARCHAR(5) CHARACTER SET NONE DEFAULT '' NOT NULL,
	message_edit_time INTEGER DEFAULT 0 NOT NULL,
	message_edit_count INTEGER DEFAULT 0 NOT NULL,
	to_address BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	bcc_address BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_privmsgs ADD PRIMARY KEY (msg_id);;

CREATE INDEX phpbb_privmsgs_author_ip ON phpbb_privmsgs(author_ip);;
CREATE INDEX phpbb_privmsgs_message_time ON phpbb_privmsgs(message_time);;
CREATE INDEX phpbb_privmsgs_author_id ON phpbb_privmsgs(author_id);;
CREATE INDEX phpbb_privmsgs_root_level ON phpbb_privmsgs(root_level);;

CREATE GENERATOR phpbb_privmsgs_gen;;
SET GENERATOR phpbb_privmsgs_gen TO 0;;

CREATE TRIGGER t_phpbb_privmsgs FOR phpbb_privmsgs
BEFORE INSERT
AS
BEGIN
	NEW.msg_id = GEN_ID(phpbb_privmsgs_gen, 1);
END;;


# Table: 'phpbb_privmsgs_folder'
CREATE TABLE phpbb_privmsgs_folder (
	folder_id INTEGER NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	folder_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	pm_count INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_privmsgs_folder ADD PRIMARY KEY (folder_id);;

CREATE INDEX phpbb_privmsgs_folder_user_id ON phpbb_privmsgs_folder(user_id);;

CREATE GENERATOR phpbb_privmsgs_folder_gen;;
SET GENERATOR phpbb_privmsgs_folder_gen TO 0;;

CREATE TRIGGER t_phpbb_privmsgs_folder FOR phpbb_privmsgs_folder
BEFORE INSERT
AS
BEGIN
	NEW.folder_id = GEN_ID(phpbb_privmsgs_folder_gen, 1);
END;;


# Table: 'phpbb_privmsgs_rules'
CREATE TABLE phpbb_privmsgs_rules (
	rule_id INTEGER NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	rule_check INTEGER DEFAULT 0 NOT NULL,
	rule_connection INTEGER DEFAULT 0 NOT NULL,
	rule_string VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	rule_user_id INTEGER DEFAULT 0 NOT NULL,
	rule_group_id INTEGER DEFAULT 0 NOT NULL,
	rule_action INTEGER DEFAULT 0 NOT NULL,
	rule_folder_id INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_privmsgs_rules ADD PRIMARY KEY (rule_id);;

CREATE INDEX phpbb_privmsgs_rules_user_id ON phpbb_privmsgs_rules(user_id);;

CREATE GENERATOR phpbb_privmsgs_rules_gen;;
SET GENERATOR phpbb_privmsgs_rules_gen TO 0;;

CREATE TRIGGER t_phpbb_privmsgs_rules FOR phpbb_privmsgs_rules
BEFORE INSERT
AS
BEGIN
	NEW.rule_id = GEN_ID(phpbb_privmsgs_rules_gen, 1);
END;;


# Table: 'phpbb_privmsgs_to'