sqlite_schema.sql

通过基于Windows的图形化界面

#
# SQLite Schema for phpBB 3.x - (c) phpBB Group, 2005
#
# $Id: sqlite_schema.sql,v 1.85 2007/01/18 10:00:39 acydburn Exp $
#

BEGIN TRANSACTION;

# Table: 'phpbb_attachments'
CREATE TABLE phpbb_attachments (
	attach_id INTEGER PRIMARY KEY NOT NULL ,
	post_msg_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	topic_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	in_message INTEGER UNSIGNED NOT NULL DEFAULT '0',
	poster_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	is_orphan INTEGER UNSIGNED NOT NULL DEFAULT '1',
	physical_filename varchar(255) NOT NULL DEFAULT '',
	real_filename varchar(255) NOT NULL DEFAULT '',
	download_count INTEGER UNSIGNED NOT NULL DEFAULT '0',
	attach_comment text(65535) NOT NULL DEFAULT '',
	extension varchar(100) NOT NULL DEFAULT '',
	mimetype varchar(100) NOT NULL DEFAULT '',
	filesize INTEGER UNSIGNED NOT NULL DEFAULT '0',
	filetime INTEGER UNSIGNED NOT NULL DEFAULT '0',
	thumbnail INTEGER UNSIGNED NOT NULL DEFAULT '0'
);

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);

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

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 PRIMARY KEY NOT NULL ,
	auth_option varchar(50) NOT NULL DEFAULT '',
	is_global INTEGER UNSIGNED NOT NULL DEFAULT '0',
	is_local INTEGER UNSIGNED NOT NULL DEFAULT '0',
	founder_only INTEGER UNSIGNED NOT NULL DEFAULT '0'
);

CREATE INDEX phpbb_acl_options_auth_option ON phpbb_acl_options (auth_option);

# Table: 'phpbb_acl_roles'
CREATE TABLE phpbb_acl_roles (
	role_id INTEGER PRIMARY KEY NOT NULL ,
	role_name varchar(255) NOT NULL DEFAULT '',
	role_description text(65535) NOT NULL DEFAULT '',
	role_type varchar(10) NOT NULL DEFAULT '',
	role_order INTEGER UNSIGNED NOT NULL DEFAULT '0'
);

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);

# Table: 'phpbb_acl_roles_data'
CREATE TABLE phpbb_acl_roles_data (
	role_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	auth_option_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	auth_setting tinyint(2) NOT NULL DEFAULT '0',
	PRIMARY KEY (role_id, auth_option_id)
);


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

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 PRIMARY KEY NOT NULL ,
	ban_userid INTEGER UNSIGNED NOT NULL DEFAULT '0',
	ban_ip varchar(40) NOT NULL DEFAULT '',
	ban_email varchar(100) NOT NULL DEFAULT '',
	ban_start INTEGER UNSIGNED NOT NULL DEFAULT '0',
	ban_end INTEGER UNSIGNED NOT NULL DEFAULT '0',
	ban_exclude INTEGER UNSIGNED NOT NULL DEFAULT '0',
	ban_reason text(65535) NOT NULL DEFAULT '',
	ban_give_reason text(65535) NOT NULL DEFAULT ''
);

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);

# Table: 'phpbb_bbcodes'
CREATE TABLE phpbb_bbcodes (
	bbcode_id tinyint(3) NOT NULL DEFAULT '0',
	bbcode_tag varchar(16) NOT NULL DEFAULT '',
	bbcode_helpline varchar(255) NOT NULL DEFAULT '',
	display_on_posting INTEGER UNSIGNED NOT NULL DEFAULT '0',
	bbcode_match text(65535) NOT NULL DEFAULT '',
	bbcode_tpl mediumtext(16777215) NOT NULL DEFAULT '',
	first_pass_match mediumtext(16777215) NOT NULL DEFAULT '',
	first_pass_replace mediumtext(16777215) NOT NULL DEFAULT '',
	second_pass_match mediumtext(16777215) NOT NULL DEFAULT '',
	second_pass_replace mediumtext(16777215) NOT NULL DEFAULT '',
	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 UNSIGNED NOT NULL DEFAULT '0',
	user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	order_id INTEGER UNSIGNED NOT NULL DEFAULT '0'
);

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 PRIMARY KEY NOT NULL ,
	bot_active INTEGER UNSIGNED NOT NULL DEFAULT '1',
	bot_name text(65535) NOT NULL DEFAULT '',
	user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	bot_agent varchar(255) NOT NULL DEFAULT '',
	bot_ip varchar(255) NOT NULL DEFAULT ''
);

CREATE INDEX phpbb_bots_bot_active ON phpbb_bots (bot_active);

# Table: 'phpbb_config'
CREATE TABLE phpbb_config (
	config_name varchar(255) NOT NULL DEFAULT '',
	config_value varchar(255) NOT NULL DEFAULT '',
	is_dynamic INTEGER UNSIGNED NOT NULL DEFAULT '0',
	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) NOT NULL DEFAULT '',
	session_id char(32) NOT NULL DEFAULT '',
	confirm_type tinyint(3) NOT NULL DEFAULT '0',
	code varchar(8) NOT NULL DEFAULT '',
	seed INTEGER UNSIGNED NOT NULL DEFAULT '0',
	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 PRIMARY KEY NOT NULL ,
	disallow_username varchar(255) NOT NULL DEFAULT ''
);


# Table: 'phpbb_drafts'
CREATE TABLE phpbb_drafts (
	draft_id INTEGER PRIMARY KEY NOT NULL ,
	user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	topic_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	save_time INTEGER UNSIGNED NOT NULL DEFAULT '0',
	draft_subject text(65535) NOT NULL DEFAULT '',
	draft_message mediumtext(16777215) NOT NULL DEFAULT ''
);

CREATE INDEX phpbb_drafts_save_time ON phpbb_drafts (save_time);

# Table: 'phpbb_extensions'
CREATE TABLE phpbb_extensions (
	extension_id INTEGER PRIMARY KEY NOT NULL ,
	group_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	extension varchar(100) NOT NULL DEFAULT ''
);


# Table: 'phpbb_extension_groups'
CREATE TABLE phpbb_extension_groups (
	group_id INTEGER PRIMARY KEY NOT NULL ,
	group_name varchar(255) NOT NULL DEFAULT '',
	cat_id tinyint(2) NOT NULL DEFAULT '0',
	allow_group INTEGER UNSIGNED NOT NULL DEFAULT '0',
	download_mode INTEGER UNSIGNED NOT NULL DEFAULT '1',
	upload_icon varchar(255) NOT NULL DEFAULT '',
	max_filesize INTEGER UNSIGNED NOT NULL DEFAULT '0',
	allowed_forums text(65535) NOT NULL DEFAULT '',
	allow_in_pm INTEGER UNSIGNED NOT NULL DEFAULT '0'
);


# Table: 'phpbb_forums'
CREATE TABLE phpbb_forums (
	forum_id INTEGER PRIMARY KEY NOT NULL ,
	parent_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	left_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	right_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_parents mediumtext(16777215) NOT NULL DEFAULT '',
	forum_name text(65535) NOT NULL DEFAULT '',
	forum_desc text(65535) NOT NULL DEFAULT '',
	forum_desc_bitfield varchar(255) NOT NULL DEFAULT '',
	forum_desc_options INTEGER UNSIGNED NOT NULL DEFAULT '7',
	forum_desc_uid varchar(5) NOT NULL DEFAULT '',
	forum_link varchar(255) NOT NULL DEFAULT '',
	forum_password varchar(40) NOT NULL DEFAULT '',
	forum_style tinyint(4) NOT NULL DEFAULT '0',
	forum_image varchar(255) NOT NULL DEFAULT '',
	forum_rules text(65535) NOT NULL DEFAULT '',
	forum_rules_link varchar(255) NOT NULL DEFAULT '',
	forum_rules_bitfield varchar(255) NOT NULL DEFAULT '',
	forum_rules_options INTEGER UNSIGNED NOT NULL DEFAULT '7',
	forum_rules_uid varchar(5) NOT NULL DEFAULT '',
	forum_topics_per_page tinyint(4) NOT NULL DEFAULT '0',
	forum_type tinyint(4) NOT NULL DEFAULT '0',
	forum_status tinyint(4) NOT NULL DEFAULT '0',
	forum_posts INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_topics INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_topics_real INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_last_post_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_last_poster_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_last_post_subject text(65535) NOT NULL DEFAULT '',
	forum_last_post_time INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_last_poster_name varchar(255) NOT NULL DEFAULT '',
	forum_last_poster_colour varchar(6) NOT NULL DEFAULT '',
	forum_flags tinyint(4) NOT NULL DEFAULT '32',
	display_on_index INTEGER UNSIGNED NOT NULL DEFAULT '1',
	enable_indexing INTEGER UNSIGNED NOT NULL DEFAULT '1',
	enable_icons INTEGER UNSIGNED NOT NULL DEFAULT '1',
	enable_prune INTEGER UNSIGNED NOT NULL DEFAULT '0',
	prune_next INTEGER UNSIGNED NOT NULL DEFAULT '0',
	prune_days INTEGER UNSIGNED NOT NULL DEFAULT '0',
	prune_viewed INTEGER UNSIGNED NOT NULL DEFAULT '0',
	prune_freq INTEGER UNSIGNED NOT NULL DEFAULT '0'
);

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);

# Table: 'phpbb_forums_access'
CREATE TABLE phpbb_forums_access (
	forum_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	session_id char(32) NOT NULL DEFAULT '',
	PRIMARY KEY (forum_id, user_id, session_id)
);


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


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

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 PRIMARY KEY NOT NULL ,
	group_type tinyint(4) NOT NULL DEFAULT '1',
	group_founder_manage INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_name varchar(255) NOT NULL DEFAULT '',
	group_desc text(65535) NOT NULL DEFAULT '',
	group_desc_bitfield varchar(255) NOT NULL DEFAULT '',
	group_desc_options INTEGER UNSIGNED NOT NULL DEFAULT '7',
	group_desc_uid varchar(5) NOT NULL DEFAULT '',
	group_display INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_avatar varchar(255) NOT NULL DEFAULT '',
	group_avatar_type tinyint(4) NOT NULL DEFAULT '0',
	group_avatar_width tinyint(4) NOT NULL DEFAULT '0',
	group_avatar_height tinyint(4) NOT NULL DEFAULT '0',
	group_rank INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_colour varchar(6) NOT NULL DEFAULT '',
	group_sig_chars INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_receive_pm INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_message_limit INTEGER UNSIGNED NOT NULL DEFAULT '0',
	group_legend INTEGER UNSIGNED NOT NULL DEFAULT '1'
);

CREATE INDEX phpbb_groups_group_legend ON phpbb_groups (group_legend);

# Table: 'phpbb_icons'
CREATE TABLE phpbb_icons (
	icons_id INTEGER PRIMARY KEY NOT NULL ,
	icons_url varchar(255) NOT NULL DEFAULT '',
	icons_width tinyint(4) NOT NULL DEFAULT '0',
	icons_height tinyint(4) NOT NULL DEFAULT '0',
	icons_order INTEGER UNSIGNED NOT NULL DEFAULT '0',
	display_on_posting INTEGER UNSIGNED NOT NULL DEFAULT '1'
);

CREATE INDEX phpbb_icons_display_on_posting ON phpbb_icons (display_on_posting);

# Table: 'phpbb_lang'
CREATE TABLE phpbb_lang (
	lang_id INTEGER PRIMARY KEY NOT NULL ,
	lang_iso varchar(30) NOT NULL DEFAULT '',
	lang_dir varchar(30) NOT NULL DEFAULT '',
	lang_english_name varchar(100) NOT NULL DEFAULT '',
	lang_local_name varchar(255) NOT NULL DEFAULT '',
	lang_author varchar(255) NOT NULL DEFAULT ''
);

CREATE INDEX phpbb_lang_lang_iso ON phpbb_lang (lang_iso);

# Table: 'phpbb_log'
CREATE TABLE phpbb_log (
	log_id INTEGER PRIMARY KEY NOT NULL ,
	log_type tinyint(4) NOT NULL DEFAULT '0',
	user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	forum_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	topic_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	reportee_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
	log_ip varchar(40) NOT NULL DEFAULT '',
	log_time INTEGER UNSIGNED NOT NULL DEFAULT '0',
	log_operation text(65535) NOT NULL DEFAULT '',
	log_data mediumtext(16777215) NOT NULL DEFAULT ''
);