firebird_schema.sql

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

#
# Firebird Schema for phpBB 3.x - (c) phpBB Group, 2005
#
# $Id: firebird_schema.sql,v 1.87 2006/11/25 20:00:56 naderman Exp $
#


# Table: 'phpbb_attachments'
CREATE TABLE phpbb_attachments (
	attach_id INTEGER NOT NULL,
	post_msg_id INTEGER DEFAULT 0 NOT NULL,
	topic_id INTEGER DEFAULT 0 NOT NULL,
	in_message INTEGER DEFAULT 0 NOT NULL,
	poster_id INTEGER DEFAULT 0 NOT NULL,
	is_orphan INTEGER DEFAULT 1 NOT NULL,
	physical_filename VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	real_filename VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	download_count INTEGER DEFAULT 0 NOT NULL,
	attach_comment BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	extension VARCHAR(100) CHARACTER SET NONE DEFAULT '' NOT NULL,
	mimetype VARCHAR(100) CHARACTER SET NONE DEFAULT '' NOT NULL,
	filesize INTEGER DEFAULT 0 NOT NULL,
	filetime INTEGER DEFAULT 0 NOT NULL,
	thumbnail INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_attachments ADD PRIMARY KEY (attach_id);;

CREATE INDEX phpbb_attachments_filetime ON phpbb_attachments(filetime);;
CREATE INDEX phpbb_attachments_post_msg_id ON phpbb_attachments(post_msg_id);;
CREATE INDEX phpbb_attachments_topic_id ON phpbb_attachments(topic_id);;
CREATE INDEX phpbb_attachments_poster_id ON phpbb_attachments(poster_id);;
CREATE INDEX phpbb_attachments_is_orphan ON phpbb_attachments(is_orphan);;

CREATE GENERATOR phpbb_attachments_gen;;
SET GENERATOR phpbb_attachments_gen TO 0;;

CREATE TRIGGER t_phpbb_attachments FOR phpbb_attachments
BEFORE INSERT
AS
BEGIN
	NEW.attach_id = GEN_ID(phpbb_attachments_gen, 1);
END;;


# Table: 'phpbb_acl_groups'
CREATE TABLE phpbb_acl_groups (
	group_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	auth_option_id INTEGER DEFAULT 0 NOT NULL,
	auth_role_id INTEGER DEFAULT 0 NOT NULL,
	auth_setting INTEGER DEFAULT 0 NOT NULL
);;

CREATE INDEX phpbb_acl_groups_group_id ON phpbb_acl_groups(group_id);;
CREATE INDEX phpbb_acl_groups_auth_opt_id ON phpbb_acl_groups(auth_option_id);;

# Table: 'phpbb_acl_options'
CREATE TABLE phpbb_acl_options (
	auth_option_id INTEGER NOT NULL,
	auth_option VARCHAR(50) CHARACTER SET NONE DEFAULT '' NOT NULL,
	is_global INTEGER DEFAULT 0 NOT NULL,
	is_local INTEGER DEFAULT 0 NOT NULL,
	founder_only INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_acl_options ADD PRIMARY KEY (auth_option_id);;

CREATE INDEX phpbb_acl_options_auth_option ON phpbb_acl_options(auth_option);;

CREATE GENERATOR phpbb_acl_options_gen;;
SET GENERATOR phpbb_acl_options_gen TO 0;;

CREATE TRIGGER t_phpbb_acl_options FOR phpbb_acl_options
BEFORE INSERT
AS
BEGIN
	NEW.auth_option_id = GEN_ID(phpbb_acl_options_gen, 1);
END;;


# Table: 'phpbb_acl_roles'
CREATE TABLE phpbb_acl_roles (
	role_id INTEGER NOT NULL,
	role_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	role_description BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	role_type VARCHAR(10) CHARACTER SET NONE DEFAULT '' NOT NULL,
	role_order INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_acl_roles ADD PRIMARY KEY (role_id);;

CREATE INDEX phpbb_acl_roles_role_type ON phpbb_acl_roles(role_type);;
CREATE INDEX phpbb_acl_roles_role_order ON phpbb_acl_roles(role_order);;

CREATE GENERATOR phpbb_acl_roles_gen;;
SET GENERATOR phpbb_acl_roles_gen TO 0;;

CREATE TRIGGER t_phpbb_acl_roles FOR phpbb_acl_roles
BEFORE INSERT
AS
BEGIN
	NEW.role_id = GEN_ID(phpbb_acl_roles_gen, 1);
END;;


# Table: 'phpbb_acl_roles_data'
CREATE TABLE phpbb_acl_roles_data (
	role_id INTEGER DEFAULT 0 NOT NULL,
	auth_option_id INTEGER DEFAULT 0 NOT NULL,
	auth_setting INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_acl_roles_data ADD PRIMARY KEY (role_id, auth_option_id);;


# Table: 'phpbb_acl_users'
CREATE TABLE phpbb_acl_users (
	user_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	auth_option_id INTEGER DEFAULT 0 NOT NULL,
	auth_role_id INTEGER DEFAULT 0 NOT NULL,
	auth_setting INTEGER DEFAULT 0 NOT NULL
);;

CREATE INDEX phpbb_acl_users_user_id ON phpbb_acl_users(user_id);;
CREATE INDEX phpbb_acl_users_auth_option_id ON phpbb_acl_users(auth_option_id);;

# Table: 'phpbb_banlist'
CREATE TABLE phpbb_banlist (
	ban_id INTEGER NOT NULL,
	ban_userid INTEGER DEFAULT 0 NOT NULL,
	ban_ip VARCHAR(40) CHARACTER SET NONE DEFAULT '' NOT NULL,
	ban_email VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	ban_start INTEGER DEFAULT 0 NOT NULL,
	ban_end INTEGER DEFAULT 0 NOT NULL,
	ban_exclude INTEGER DEFAULT 0 NOT NULL,
	ban_reason BLOB SUB_TYPE TEXT CHARACTER SET NONE DEFAULT '' NOT NULL,
	ban_give_reason BLOB SUB_TYPE TEXT CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_banlist ADD PRIMARY KEY (ban_id);;

CREATE INDEX phpbb_banlist_ban_end ON phpbb_banlist(ban_end);;
CREATE INDEX phpbb_banlist_ban_user ON phpbb_banlist(ban_userid, ban_exclude);;
CREATE INDEX phpbb_banlist_ban_email ON phpbb_banlist(ban_email, ban_exclude);;
CREATE INDEX phpbb_banlist_ban_ip ON phpbb_banlist(ban_ip, ban_exclude);;

CREATE GENERATOR phpbb_banlist_gen;;
SET GENERATOR phpbb_banlist_gen TO 0;;

CREATE TRIGGER t_phpbb_banlist FOR phpbb_banlist
BEFORE INSERT
AS
BEGIN
	NEW.ban_id = GEN_ID(phpbb_banlist_gen, 1);
END;;


# Table: 'phpbb_bbcodes'
CREATE TABLE phpbb_bbcodes (
	bbcode_id INTEGER DEFAULT 0 NOT NULL,
	bbcode_tag VARCHAR(16) CHARACTER SET NONE DEFAULT '' NOT NULL,
	bbcode_helpline VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	display_on_posting INTEGER DEFAULT 0 NOT NULL,
	bbcode_match BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	bbcode_tpl BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	first_pass_match BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	first_pass_replace BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	second_pass_match BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	second_pass_replace BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_bbcodes ADD PRIMARY KEY (bbcode_id);;

CREATE INDEX phpbb_bbcodes_display_on_post ON phpbb_bbcodes(display_on_posting);;

# Table: 'phpbb_bookmarks'
CREATE TABLE phpbb_bookmarks (
	topic_id INTEGER DEFAULT 0 NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	order_id INTEGER DEFAULT 0 NOT NULL
);;

CREATE INDEX phpbb_bookmarks_order_id ON phpbb_bookmarks(order_id);;
CREATE INDEX phpbb_bookmarks_topic_user_id ON phpbb_bookmarks(topic_id, user_id);;

# Table: 'phpbb_bots'
CREATE TABLE phpbb_bots (
	bot_id INTEGER NOT NULL,
	bot_active INTEGER DEFAULT 1 NOT NULL,
	bot_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	user_id INTEGER DEFAULT 0 NOT NULL,
	bot_agent VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	bot_ip VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_bots ADD PRIMARY KEY (bot_id);;

CREATE INDEX phpbb_bots_bot_active ON phpbb_bots(bot_active);;

CREATE GENERATOR phpbb_bots_gen;;
SET GENERATOR phpbb_bots_gen TO 0;;

CREATE TRIGGER t_phpbb_bots FOR phpbb_bots
BEFORE INSERT
AS
BEGIN
	NEW.bot_id = GEN_ID(phpbb_bots_gen, 1);
END;;


# Table: 'phpbb_config'
CREATE TABLE phpbb_config (
	config_name VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	config_value VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	is_dynamic INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_config ADD PRIMARY KEY (config_name);;

CREATE INDEX phpbb_config_is_dynamic ON phpbb_config(is_dynamic);;

# Table: 'phpbb_confirm'
CREATE TABLE phpbb_confirm (
	confirm_id CHAR(32) CHARACTER SET NONE DEFAULT '' NOT NULL,
	session_id CHAR(32) CHARACTER SET NONE DEFAULT '' NOT NULL,
	confirm_type INTEGER DEFAULT 0 NOT NULL,
	code VARCHAR(8) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_confirm ADD PRIMARY KEY (session_id, confirm_id);;

CREATE INDEX phpbb_confirm_confirm_type ON phpbb_confirm(confirm_type);;

# Table: 'phpbb_disallow'
CREATE TABLE phpbb_disallow (
	disallow_id INTEGER NOT NULL,
	disallow_username VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE
);;

ALTER TABLE phpbb_disallow ADD PRIMARY KEY (disallow_id);;


CREATE GENERATOR phpbb_disallow_gen;;
SET GENERATOR phpbb_disallow_gen TO 0;;

CREATE TRIGGER t_phpbb_disallow FOR phpbb_disallow
BEFORE INSERT
AS
BEGIN
	NEW.disallow_id = GEN_ID(phpbb_disallow_gen, 1);
END;;


# Table: 'phpbb_drafts'
CREATE TABLE phpbb_drafts (
	draft_id INTEGER NOT NULL,
	user_id INTEGER DEFAULT 0 NOT NULL,
	topic_id INTEGER DEFAULT 0 NOT NULL,
	forum_id INTEGER DEFAULT 0 NOT NULL,
	save_time INTEGER DEFAULT 0 NOT NULL,
	draft_subject VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	draft_message BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_drafts ADD PRIMARY KEY (draft_id);;

CREATE INDEX phpbb_drafts_save_time ON phpbb_drafts(save_time);;

CREATE GENERATOR phpbb_drafts_gen;;
SET GENERATOR phpbb_drafts_gen TO 0;;

CREATE TRIGGER t_phpbb_drafts FOR phpbb_drafts
BEFORE INSERT
AS
BEGIN
	NEW.draft_id = GEN_ID(phpbb_drafts_gen, 1);
END;;


# Table: 'phpbb_extensions'
CREATE TABLE phpbb_extensions (
	extension_id INTEGER NOT NULL,
	group_id INTEGER DEFAULT 0 NOT NULL,
	extension VARCHAR(100) CHARACTER SET NONE DEFAULT '' NOT NULL
);;

ALTER TABLE phpbb_extensions ADD PRIMARY KEY (extension_id);;


CREATE GENERATOR phpbb_extensions_gen;;
SET GENERATOR phpbb_extensions_gen TO 0;;

CREATE TRIGGER t_phpbb_extensions FOR phpbb_extensions
BEFORE INSERT
AS
BEGIN
	NEW.extension_id = GEN_ID(phpbb_extensions_gen, 1);
END;;


# Table: 'phpbb_extension_groups'
CREATE TABLE phpbb_extension_groups (
	group_id INTEGER NOT NULL,
	group_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	cat_id INTEGER DEFAULT 0 NOT NULL,
	allow_group INTEGER DEFAULT 0 NOT NULL,
	download_mode INTEGER DEFAULT 1 NOT NULL,
	upload_icon VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	max_filesize INTEGER DEFAULT 0 NOT NULL,
	allowed_forums BLOB SUB_TYPE TEXT CHARACTER SET NONE DEFAULT '' NOT NULL,
	allow_in_pm INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_extension_groups ADD PRIMARY KEY (group_id);;


CREATE GENERATOR phpbb_extension_groups_gen;;
SET GENERATOR phpbb_extension_groups_gen TO 0;;

CREATE TRIGGER t_phpbb_extension_groups FOR phpbb_extension_groups
BEFORE INSERT
AS
BEGIN
	NEW.group_id = GEN_ID(phpbb_extension_groups_gen, 1);
END;;


# Table: 'phpbb_forums'
CREATE TABLE phpbb_forums (
	forum_id INTEGER NOT NULL,
	parent_id INTEGER DEFAULT 0 NOT NULL,
	left_id INTEGER DEFAULT 0 NOT NULL,
	right_id INTEGER DEFAULT 0 NOT NULL,
	forum_parents BLOB SUB_TYPE TEXT CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_desc BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	forum_desc_bitfield VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_desc_options INTEGER DEFAULT 7 NOT NULL,
	forum_desc_uid VARCHAR(5) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_link VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_password VARCHAR(40) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_style INTEGER DEFAULT 0 NOT NULL,
	forum_image VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_rules BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
	forum_rules_link VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_rules_bitfield VARCHAR(255) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_rules_options INTEGER DEFAULT 7 NOT NULL,
	forum_rules_uid VARCHAR(5) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_topics_per_page INTEGER DEFAULT 0 NOT NULL,
	forum_type INTEGER DEFAULT 0 NOT NULL,
	forum_status INTEGER DEFAULT 0 NOT NULL,
	forum_posts INTEGER DEFAULT 0 NOT NULL,
	forum_topics INTEGER DEFAULT 0 NOT NULL,
	forum_topics_real INTEGER DEFAULT 0 NOT NULL,
	forum_last_post_id INTEGER DEFAULT 0 NOT NULL,
	forum_last_poster_id INTEGER DEFAULT 0 NOT NULL,
	forum_last_post_subject VARCHAR(100) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_last_post_time INTEGER DEFAULT 0 NOT NULL,
	forum_last_poster_name VARCHAR(255) CHARACTER SET UTF8 DEFAULT '' NOT NULL COLLATE UNICODE,
	forum_last_poster_colour VARCHAR(6) CHARACTER SET NONE DEFAULT '' NOT NULL,
	forum_flags INTEGER DEFAULT 32 NOT NULL,
	display_on_index INTEGER DEFAULT 1 NOT NULL,
	enable_indexing INTEGER DEFAULT 1 NOT NULL,
	enable_icons INTEGER DEFAULT 1 NOT NULL,
	enable_prune INTEGER DEFAULT 0 NOT NULL,
	prune_next INTEGER DEFAULT 0 NOT NULL,
	prune_days INTEGER DEFAULT 0 NOT NULL,
	prune_viewed INTEGER DEFAULT 0 NOT NULL,
	prune_freq INTEGER DEFAULT 0 NOT NULL
);;

ALTER TABLE phpbb_forums ADD PRIMARY KEY (forum_id);;

CREATE INDEX phpbb_forums_left_right_id ON phpbb_forums(left_id, right_id);;
CREATE INDEX phpbb_forums_forum_lastpost_id ON phpbb_forums(forum_last_post_id);;