# NTsky 's System SQL
# SQL --> Mysql 5.0
# VERSION 1.0
# Date: 2005/11/10 13:50:34  
/*
  命名规则 : 
  ntsky -- 组织名称
  bbs   -- 项目名称
  table -- 表
  procedure -- 存储过程
  view  -- 视图
  
  vi (View) 
  sp (store procedure)
  fn (function)
  tb (table)
  
  example ntsky_t_user (组织名称_项目名_类型(表)_表名)
 */
# grant select,insert,update,delete on *.* to root@"%" Identified by ""; 
# mysql --default-character-set=utf8 -u root -p
# source d:\cvs\project\forum\sql\ntsky_bbs.sql

-- alter table test add column aa varhcar(50) default '';

DROP DATABASE IF EXISTS ntsky_bbs;
CREATE DATABASE ntsky_bbs;
USE ntsky_bbs;

DROP TABLE IF EXISTS ntsky_t_user;
CREATE TABLE ntsky_t_user(
	-- 必填信息
	id			INT NOT NULL ,					-- 用户ID
	username		VARCHAR(20) NOT NULL ,				-- 用户名
	password		CHAR(32) NOT NULL ,				-- 密码 (MD5加密)
	sex			TINYINT(1) NOT NULL ,				-- 用户性别(1、男 2、女)
	email			VARCHAR(100) NOT NULL ,				-- 用户E-mail
	question		VARCHAR(50) NOT NULL ,				-- 用户问题
	answer			VARCHAR(50) NOT NULL ,				-- 用户答案(MD5加密)
	roles			VARCHAR(50) NOT NULL default 0 ,		-- 用户从属于的角色(默认刚注册的用户为0)
	register_time		DATETIME NOT NULL ,				-- 注册时间
	login_times		INT default 0 ,					-- 用户登陆次数	
	last_login_time		DATETIME NOT NULL ,				-- 最后登陆时间(删除过期用户)
	last_login_ip		VARCHAR(25) NOT NULL,				-- 用户登陆的IP
	is_lock			TINYINT(1) default 0 ,				-- 用户账号是否被锁定(1表示禁用,0表示正常)	

	birthday		CHAR(10) default '',				-- 生日
	website			VARCHAR(100) default '' ,			-- 网站
	im_qq			VARCHAR(12) default '' ,			-- QQ号码
	im_msn			VARCHAR(50) default '',				-- MSN号码
	im_icq			VARCHAR(20) default '' ,			-- icq号码
	im_yahoo		VARCHAR(50) default '' ,			-- 用户yahoo通号码
	gmail			VARCHAR(100) default '',			-- Gmail信息
	address			VARCHAR(100) default '' ,			-- 地址
	face			VARCHAR(50) default '',				-- 头像
	signature		VARCHAR(255) default '',			-- 签名
	intro			TEXT default '',				-- 介绍
	money			INT default 0 ,					-- 金钱
	-- experience		INT default 0 ,					-- 经验
	integral		INT default 0 ,					-- 积分
	total_topic		INT default 0 ,					-- 发表主题数
	total_post		INT default 0 ,					-- 发表贴子数
	online_time		INT default 0 ,					-- 在线时间
	PRIMARY KEY (id) ,
	KEY `idx_username` (`username`)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '论坛用户表';
INSERT INTO ntsky_t_user(id,username,password,sex,email,question,answer,roles,register_time,last_login_time,last_login_ip,is_lock,face) VALUES(1,'admin','10f37e6eac91e4fbab892d3eca1fb2b2',1,'yntsky@gmail.com','What is your name?','10f37e6eac91e4fbab892d3eca1fb2b2','1',now(),now(),'127.0.0.1',0,'images/face/default.gif');

-- 论坛表
DROP TABLE IF EXISTS ntsky_t_forum;
CREATE TABLE ntsky_t_forum(
	id			SMALLINT NOT NULL ,			-- 论坛编号
	name			VARCHAR(100) NOT NULL ,			-- 论坛名称
	parent_id		SMALLINT NOT NULL default 0 ,		-- 父论坛编号
	parent_enum		VARCHAR(255) default '' ,		-- 父类的集合
	depth			SMALLINT NOT NULL default 0 ,		-- 深度	
	display_order		SMALLINT NOT NULL default 0 ,		-- 排序 
	branch_id		SMALLINT NOT NULL default 0 ,		-- 分枝编号
	masters			VARCHAR(100) NOT NULL ,			-- 管理员
	description		TEXT NOT NULL ,				-- 论坛介绍
	rules			TEXT default '' ,			-- 版块规则
	date_created		DATETIME NOT NULL ,			-- 创建日期
	sign_image		VARCHAR(50) default '' ,		-- 标识图片

	-- total_topic 		BIGINT default 0 ,			-- 主题总数
	-- total_post		BIGINT default 0 ,			-- 帖子信息总数
	-- total_today_post	INT default 0 ,				-- 今日帖子总数
	-- last_post		VARCHAR(255)  ,				-- 最新发表贴子
	-- status		TINYINT(1) default 0 ,			-- 论坛状态 0、没有新帖 1、有新帖 2、关闭的论坛
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '论坛表';

INSERT INTO ntsky_t_forum(id,name,parent_id,parent_enum,depth,display_order,branch_id,masters,description,date_created) VALUES(1,'test',0,0,1,1,1,'ntsky','text','2006-04-05 00:00:00');

-- 专题表
DROP TABLE IF EXISTS ntsky_t_category;
CREATE TABLE ntsky_t_category (
	id			SMALLINT NOT NULL ,			-- 类别编号
	forum_id		SMALLINT NOT NULL ,			-- 论坛编号
	name			VARCHAR(100) ,				-- 类别名称
	display_order		SMALLINT default 0 ,			-- 类别排序
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '专题表';
INSERT INTO ntsky_t_category VALUES(1,14,'开心一笑',0);

-- 主题表
DROP TABLE IF EXISTS ntsky_t_topic;
CREATE TABLE ntsky_t_topic(
	id			INT NOT NULL ,				-- 主题编号
	forum_id		SMALLINT NOT NULL ,			-- 论坛编号
	category_id		SMALLINT default 0 ,			-- 类别编号
	mood			SMALLINT(3) NOT NULL default 0 ,	-- 发帖心情
	title			VARCHAR(255) NOT NULL ,			-- 标题
	username		VARCHAR(20) NOT NULL ,			-- 发表用户
	views			BIGINT default 0,			-- 查看次数
	status			TINYINT(1) default 0 ,			-- 帖子状态 1、精华贴，2、贴子被锁定
	is_top			TINYINT(1) default 0 ,			-- 是否置顶主题 0、否 1、是
	is_vote			TINYINT(1) default 0 ,			-- 是否为投票贴
	date_created		DATETIME NOT NULL ,			-- 发布时间
	-- last_post_id		BIGINT default 0 ,			-- 最后发表的帖子
	last_post_user		VARCHAR(20) NOT NULL ,			-- 最后发表的帖子的用户
	last_post_time		DATETIME  ,				-- 最后发表时间
	replies			INT default 0 ,				-- 回复总数
	is_delete		TINYINT(1) default 0 ,			-- 贴子状态(0 --正常  1 -- 已删除)
	PRIMARY KEY(id),
	KEY `idx_title` (`title`),
	KEY `idx_username` (`username`)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '主题表';

-- 主题回复表
DROP TABLE IF EXISTS ntsky_t_post;
CREATE TABLE ntsky_t_post(
	id			BIGINT NOT NULL ,			-- 回复信息编号
	forum_id		SMALLINT NOT NULL ,			-- 论坛编号
	topic_id		INT NOT NULL ,				-- 主题编号
	title			VARCHAR(255) default '' ,		-- 标题
	content			MEDIUMTEXT NOT NULL ,			-- 内容
	user_id			VARCHAR(20) NOT NULL default ''  references ntsky_t_user(id),	-- 发帖用户ID
	username		VARCHAR(20) NOT NULL default '',	-- 发帖用户名
	ip			VARCHAR(20) NOT NULL default '',	-- 发帖IP
	date_created		DATETIME NOT NULL ,			-- 发布时间
	date_edited		DATETIME  ,				-- 修改时间
	edit_count		SMALLINT(3) default 0,			-- 修改次数	
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '主题回复表';

-- 投票表
DROP TABLE IF EXISTS ntsky_t_poll;
CREATE TABLE ntsky_t_poll(
	id			INT NOT NULL ,				-- 投票编号
	topic_id		INT NOT NULL ,				-- 主题编号
	content			VARCHAR(255) NOT NULL ,   		-- 投票内容
	time_out		INT default 0 ,				-- 过期时间
	date_created		DATETIME NOT NULL,			-- 投票创建时间
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '投票信息';

-- 投票结果表
DROP TABLE IF EXISTS ntsky_t_poll_result;
CREATE TABLE ntsky_t_poll_result(
	id			INT NOT NULL ,				-- 结果编号
	poll_id			INT NOT NULL ,				-- 投票编号
	-- topic_id		INT NOT NULL ,				-- 主题编号
	option_id		INT NOT NULL ,				-- 选项编号
	option_text		varchar(255) NOT NULL ,			-- 选项值
	votes			INT default 0 ,				-- 得票数(投票后计算结果)
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '答案列表';

-- 投票用户表(防止重复投票)
DROP TABLE IF EXISTS ntsky_t_poll_voter;
CREATE TABLE ntsky_t_poll_voter(
	id			BIGINT NOT NULL ,			-- 
	poll_id			INT NOT NULL ,				-- 投票编号
	poll_result_id		INT NOT NULL ,				-- 投票结果
	poll_user		INT NOT NULL ,				-- 投票用户
	poll_ip			VARCHAR(20) 				-- 投票用户Ip
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '投票用户表';


-- 个人收藏夹
DROP TABLE IF EXISTS ntsky_t_favorite;
CREATE TABLE ntsky_t_favorite(
	id			BIGINT NOT NULL ,			-- 收藏夹编号
	name			VARCHAR(100) NOT NULL,			-- 名称
	url			TEXT NOT NULL ,				-- 地址
	user_id			INT NOT NULL ,				-- 用户ID
	date_created		CHAR(19) NOT NULL ,			-- 创建时间
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '个人收藏表';

-- 附件表
DROP TABLE IF EXISTS ntsky_t_attachment;
CREATE TABLE ntsky_t_attachment(
	id			INT NOT NULL ,				-- 附件编号
	origin_filename		VARCHAR(255) NOT NULL ,			-- 原始文件名
	filename		VARCHAR(255) NOT NULL ,			-- 文件名
	filesize		INT NOT NULL ,				-- 文件大小
	filetype		VARCHAR(50) NOT NULL ,			-- 文件类型(图片,flash,midea,文件)
	folder			VARCHAR(50) NOT NULL default '' ,	-- 文件目录
	comments		VARCHAR(200) ,				-- 描述
	download_times		INT default 0,				-- 下载次数
	date_created		DATETIME NOT NULL ,			-- 创建日期
	user_id			VARCHAR(20) NOT NULL ,			-- 创建者
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '附件表';
-- INSERT INTO ntsky_t_attachment(id,topic_id,name,size,type,comments,download_times,date_created,creator) values(1,1,'aaa.gif','11M','gif','1234',1,'2006-01-01 00:00:00','123');


-- 友情链接表
DROP TABLE IF EXISTS ntsky_t_link;
CREATE TABLE ntsky_t_link(
	id			INT NOT NULL ,				-- 友情链接编号
	name			VARCHAR(50) ,				-- 链接名称
	url			VARCHAR(100) NOT NULL ,			-- 链接地址
	is_logo			TINYINT(1) NOT NULL default 0 ,		-- 是否为Logo(0、文字 1、图片)
	logo			VARCHAR(100) ,				-- 图片路径
	description		VARCHAR(200) ,				-- 该链接的详细介绍信息
	display_order		INT default 0 ,				-- 排序
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '友情链接表';

-- 消息表
DROP TABLE IF EXISTS ntsky_t_message;
CREATE TABLE ntsky_t_message(
	id			BIGINT NOT NULL ,			-- 消息编号
	sender			VARCHAR(30) NOT NULL ,			-- 发送者
	receiver		TEXT NOT NULL ,				-- 接收者(接收者之间用",分隔")
	title			VARCHAR(100) NOT NULL ,			-- 消息标题
	content			TEXT ,					-- 消息内容
	send_time		DATETIME ,				-- 消息发送时间
	flag			TINYINT(1) NOT NULL default 0,		-- 消息标记 (1 -- 管理信息 0 -- 普通信息)
	is_read			TINYINT(1) default 0,			-- 是否为新消息(1、表示已读消息 0、表示新消息)	
	is_del_sender		TINYINT(1) default 0,			-- 是否删除发送的信息
	is_del_receiver		TINYINT(1) default 0,			-- 是否删除接收的信息
	status			TINYINT default 1,			-- 1、正常消息 2、草稿 3、是否已经被删除
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '消息表';
INSERT INTO ntsky_t_message(id,sender,receiver,title,content,send_time,flag,is_read) VALUES(1,'ntsky','ntsky','ntsky 1.0 complent','ntskyq','2006-01-01 00:00:00',1,1);

-- 公告表
DROP TABLE IF EXISTS ntsky_t_announcement;
CREATE TABLE ntsky_t_announcement(
	id			INT NOT NULL ,				-- 公告编号
	title			VARCHAR(100) NOT NULL ,			-- 公告标题
	content			TEXT NOT NULL ,				-- 公告内容
	author			VARCHAR(20) ,				-- 发布作者
	forum_id		SMALLINT NOT NULL ,			-- 论坛编号
	date_created		CHAR(19) NOT NULL ,			-- 创建时间	
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '公告表';
INSERT INTO `ntsky_t_announcement` VALUES (2,'test announce.','<p><strong>test announce.</strong></p>','ntsky',0,'2006-06-19 20:41:38');

-- 论坛统计表
DROP TABLE IF EXISTS ntsky_t_stat;
CREATE TABLE ntsky_t_stat(
	id			BIGINT NOT NULL ,			-- 统计编号
	-- forum_id		INT NOT NULL ,				-- 论坛编号
	-- topic_id		INT NOT NULL ,				-- 主题编号
	username		VARCHAR(20) NOT NULL ,			-- 用户名(guest)
	place			VARCHAR(200) NOT NULL ,			-- 访问者IP+地点
	referer			VARCHAR(50) NOT NULL ,			-- 所在页面
	agent			VARCHAR(100) NOT NULL ,			-- 浏览器头信息
	language		VARCHAR(20) NOT NULL ,			-- 用户系统语言
	view_time		DATETIME NOT NULL ,			-- 访问时间
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '论坛统计表';
INSERT INTO ntsky_t_stat(id,username,place,referer,agent,language,view_time) VALUES(1,'ntsky','深圳南山(test)','bbs/index.jsp','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)','zh-cn','2006-04-04 00:00:00');

DROP TABLE IF EXISTS ntsky_t_act_log;
CREATE TABLE ntsky_t_act_log(
	act_id			BIGINT NOT NULL ,			-- 操作日志编号
	name			VARCHAR(255) NOT NULL ,			-- 操作名称
	username		VARCHAR(100) NOT NULL ,			-- 操作人
	ip			VARCHAR(20) NOT NULL ,			-- ip
	time			DATETIME NOT NULL ,			-- 操作时间
	PRIMARY KEY(act_id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '操作日志表';

-- 论坛表分类
DROP TABLE IF EXISTS ntsky_t_common;
CREATE TABLE ntsky_t_common(
	id			INT NOT NULL  ,
	name			VARCHAR(255) NOT NULL ,
	type			TINYINT(2) NOT NULL , -- 1、头像 2、帮助
	ordering		TINYINT(2) default 0,
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO ntsky_t_common(id,name,type) VALUES(10,'登录和注册','2');
INSERT INTO ntsky_t_common(id,name,type) VALUES(11,'用户设置','2');
INSERT INTO ntsky_t_common(id,name,type) VALUES(12,'论坛使用','2');
INSERT INTO ntsky_t_common(id,name,type) VALUES(13,'关于 NTsky','2');
INSERT INTO ntsky_t_common(id,name,type) VALUES(14,'其它','2');


INSERT INTO ntsky_t_common(id,name,type) VALUES(1,'动物','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(2,'卡通','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(3,'景点','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(4,'自然','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(5,'浪漫','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(6,'科幻','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(7,'笑脸','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(8,'运动','1');
INSERT INTO ntsky_t_common(id,name,type) VALUES(9,'其它','1');

-- 论坛帮助表
DROP TABLE IF EXISTS ntsky_t_help;
CREATE TABLE ntsky_t_help(
	id			INT NOT NULL COMMENT '帮助编号' ,
	title			VARCHAR(255) NOT NULL COMMENT '帮助标题',
	content			TEXT NOT NULL COMMENT '帮助内容',
	type			TINYINT NOT NULL COMMENT '类别',
	date_created		DATETIME NOT NULL COMMENT '创建时间',
	is_hand			TINYINT(1) default 0, -- 0、在前台显示，1、作为隐藏HELP手动调用								 
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '论坛帮助表';
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(1,'我为什么要注册？','发帖、阅读其他人的帖子前是否需要登录，这取决于论坛管理员是否设置了论坛的游客权限。大多数设置允许不需要注册登录就可以阅读信息。通过注册您可以享受到所有的附加功能，例如：设置您的头像、 收藏感兴趣的帖子、给用户发送邮件、私人留言、访问一些受保护的论坛等等，注册步骤也非常简单，一分钟就能完成。',10,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(2,'怎样才能注册？','要注册一个新帐号，你需要访问注册并且按照要求填写注册表单。您必须提供一个用户名和一个有效的电子信箱地址。管理员可能要求你指定密码，如果不需要指定密码，那么在注册完成后您将收到确认邮件。 ',10,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(3,'我忘记了密码. ','假如您忘记了密码，您可以访问 "找回密码" 页输入您注册时的电子信箱地址，会有一份有新密码的邮件发送到您的注册信箱。因为密码是不可逆加密存储，所以我们无法找回您的原始密码。一旦您收到您的新密码，您可以登录并修改您的密码。',10,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(4,'已注册，但是现在不能登录？ ','请先检查你的用户名和密码是否正确。如果仍然不能登录，你的帐号可能由于长期未登录已被删除。请与论坛管理员或者版主联系。 ',10,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(5,'如何在帖子里添加签名？','签名就是附加在你发表于论坛上的每一篇帖子后面的信息. 你可以在个人资料页面编辑你的签名. 此签名将显示在你所发表的任何信息的尾部。 ',11,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(6,'我如何设置头像？','如果管理员已启用头像，当你查看个人资料时可以看见头像区。在这里你可以选择一个你喜欢的头像或是上传或是输入一个图片的网址来作为你的头像 ',11,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(7,'如何发布新帖子？','在论坛的各个版块中，点“（发表贴子）”即可进入功能齐全的发帖界面。当然您也可以使用板块下面的“发表”发表新帖(如果此选项打开)。注意，一般论坛都设置为需要登录后才能发帖。',12,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(8,'如何回复帖子？','在浏览帖子时，点“回复帖子”即可进入功能齐全的回复界面。当然您也可以使用浏览帖子时下面的“快速回复”发表回复(如果此选项打开)。注意，一般论坛都设置为需要登录后才能回复。',12,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(9,'该怎样发起一个投票？','您可以像发帖一样在板块中发起投票。每行输入一个可能的选项，您可以通过阅读这个投票贴选出自己的答案，每人只能投票一次，发起之后将不能再对您的选择做出更改。',12,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(10,'新技术bbs免费吗?','是的,本软件是个开源产品,您可以从本站下载后任意使用.',13,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(11,'新技术bbs采用架构?','新技术采用Java编写,页面使用了xhtml1.1+css2进行重构,前端使用freemarker+sitemesh+webwork,业务层使用spring,持久层使用hibernate,支持目前流行的所有数据库.',13,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(12,'如何获得新技术bbs?','您可以在http://www.ntsky.com获得下载,如果您是技术人员,可以使用cvs进行checkout.',13,'2006-01-01 00:00:01');
INSERT INTO ntsky_t_help(id,title,content,type,date_created) VALUES(13,'什么是RSS?','RSS是在线共享内容的一种简易方式（也叫聚合内容，Really Simple Syndication）。通常在时效性比较强的内容上使用RSS订阅能更快速获取信息，网站提供RSS输出，有利于让用户获取网站内容的最新更新。网络用户可以在客户端借助于支持RSS的聚合工具软件（例如SharpReader,NewzCrawler、FeedDemon），在不打开网站内容页面的情况下阅读支持RSS输出的网站内容。',14,'2006-01-01 00:00:01');



-- 组信息表
DROP TABLE IF EXISTS ntsky_t_group;
CREATE TABLE ntsky_t_group (
	id			INT DEFAULT '0' NOT NULL,		-- 组编号
	creator			VARCHAR(255) NOT NULL ,			-- 组群创建者
	name			VARCHAR(255) NOT NULL,			-- 组名
	description		TEXT NOT NULL ,				-- 组介绍
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '组信息表';

-- 论坛角色表
DROP TABLE IF EXISTS ntsky_t_role;
CREATE TABLE ntsky_t_role(
	id			SMALLINT NOT NULL ,			-- 角色编号
	name			VARCHAR(255) NOT NULL ,			-- 角色名称
	type			TINYINT NOT NULL ,			-- 角色类别 (0、注册用户组;1、系统用户组;2、特殊用户组)
	description		TEXT default '',			-- 角色描述
	-- permissions		TEXT NOT NULL ,				-- 角色对应的权限
	-- parentRole		INT NOT NULL ,				-- 父角色
	icon			VARCHAR(255) NOT NULL ,			-- 等级图片
	min_topic		INT NOT NULL ,				-- 最少主题
	permissions		TEXT NOT NULL ,				-- 权限数组 (权限点=值|权限点=值)

	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '论坛角色表';
-- 系统用户
INSERT INTO ntsky_t_role VALUES(0,'guest',1,'过客','level1.gif',-1,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canEditPost=0|canDeleteOwnPost=0|canMoveOwn=0|canPostPoll=0|canVote=0|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=0|canViewUser=1|canUseSignature=0|canCustomFace=0|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=0|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(1,'系统管理员',1,'system manager','level100.gif',-1,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=30720|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=1|canMove=0|canTopTopic=1|canEditOther=1|canBestTopic=1|canLockTopic=1|canAnnounce=1|canEditForum=1|canCategory=1|canBatchDelete=1|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(2,'超级版主',1,'可以管理多个版块','level101.gif',-1,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=30720|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=1|canMove=0|canTopTopic=1|canEditOther=1|canBestTopic=1|canLockTopic=1|canAnnounce=1|canEditForum=1|canCategory=1|canBatchDelete=1|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(3,'版主',1,'可以管理单个版块','level102.gif',-1,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=30720|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=1|canMove=0|canTopTopic=1|canEditOther=1|canBestTopic=1|canLockTopic=1|canAnnounce=1|canEditForum=1|canCategory=1|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(4,'注册用户',1,'刚注册的用户','level1.gif',0,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=0|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=0|canCustomFace=0|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');

-- 注册用户
INSERT INTO ntsky_t_role VALUES(5,'初级会员',0,'注册基本会员','level1.gif',0,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canPostNew=1|canReplyPost=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=0|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(6,'中级会员',0,' ','level2.gif',50,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canReplyOther=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(7,'高级会员',0,' ','level3.gif',100,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canReplyOther=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(8,'铜牌会员',0,' ','level4.gif',200,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canReplyOther=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(9,'银牌会员',0,' ','level5.gif',400,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canReplyOther=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');
INSERT INTO ntsky_t_role VALUES(10,'金牌会员',0,' ','level6.gif',1000,'canViewForum=1|canViewTopic=1|canViewBestTopic=1|canPostNew=1|canReplyPost=1|canReplyOther=1|canEditPost=1|canDeleteOwnPost=1|canMoveOwn=1|canPostPoll=1|canVote=1|canGetAttachment=0|canUploadAttachment=0|uploadAttachmentSize=1024|canViewUser=1|canUseSignature=1|canCustomFace=1|canDelete=0|canMove=0|canTopTopic=0|canEditOther=0|canBestTopic=0|canLockTopic=0|canAnnounce=0|canEditForum=0|canCategory=0|canBatchDelete=0|canSendMessage=1|mostReceiverUsers=10|messageMaxSize=100|inboxSize=1024');

DROP TABLE IF EXISTS ntsky_t_admin;
CREATE TABLE ntsky_t_admin(
	id			SMALLINT NOT NULL ,			-- 管理编号
	username		VARCHAR(20) NOT NULL ,			-- 用户名
	password		CHAR(32) NOT NULL ,			-- 密码
	permissions		TEXT NOT NULL ,				-- 权限列表
	last_login_time		CHAR(19) ,				-- 最后登陆时间(删除过期用户)
	last_login_ip		VARCHAR(50) ,				-- 用户登陆的IP
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '管理员登陆表';

INSERT INTO ntsky_t_admin(id,username,password,permissions,last_login_time,last_login_ip) VALUES(1,'admin','e10adc3949ba59abbe56e057f20f883e','0','2005-04-04 00:00:00','127.0.0.1');

-- 头像数据信息表
DROP TABLE IF EXISTS ntsky_t_user_face;
CREATE TABLE ntsky_t_user_face(
	id			SMALLINT NOT NULL ,			-- 头像编号
	name			VARCHAR(20) NOT NULL ,			-- 头像名称
	path			VARCHAR(255) NOT NULL ,			-- 路径
	type			TINYINT(2) NOT NULL ,			-- 头像类别
	PRIMARY KEY(id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '头像数据信息表';

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(1,'蓝色的鱼','images/face/blueFish.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(2,'黑猩猩','images/face/chimpanzee.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(3,'狗','images/face/dog.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(4,'海豚','images/face/dolphin.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(5,'青蛙','images/face/frog.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(6,'豚鼠','images/face/guineaPig.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(7,'水母','images/face/jellyFish.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(8,'小猫','images/face/kitty.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(9,'瓢虫','images/face/ladyBug.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(10,'猴子','images/face/monkey.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(11,'鸵鸟','images/face/ostrich.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(12,'猫头鹰','images/face/owl.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(13,'熊猫','images/face/panda.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(14,'猪','images/face/pig.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(15,'蝾螈','images/face/salamander.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(16,'蜘蛛','images/face/spider.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(17,'海星','images/face/starfish.jpg',1);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(18,'老虎','images/face/tiger.jpg',1);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(19,'飞机','images/face/airplane.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(20,'愤怒的猫','images/face/angryCat.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(21,'愤怒的狗','images/face/angryDog.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(22,'怪鱼','images/face/blueDragon.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(23,'厨师','images/face/chef.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(24,'牛仔','images/face/cowboy.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(25,'螃蟹','images/face/crab.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(26,'跳舞的企鹅','images/face/dancingPenguin.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(27,'医生','images/face/doctor.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(28,'邪恶的护士','images/face/evilNurse.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(29,'喷火的青蛙','images/face/fireFrog.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(30,'友好的狮子','images/face/friendlyLion.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(31,'发光的星星','images/face/shinyStarFace.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(32,'受惊吓的男人','images/face/shockedMan.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(33,'购物女孩','images/face/shoppingGirl.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(34,'唱歌的女人','images/face/singingWoman.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(35,'微笑的梨','images/face/smilingPear.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(36,'超人','images/face/superHero.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(37,'雨伞','images/face/umbrella.jpg',2);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(38,'听随身听的人','images/face/walkmanGuy.jpg',2);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(39,'佛像','images/face/buddha.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(40,'埃及','images/face/egypt.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(41,'希腊','images/face/greece.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(42,'伦敦','images/face/london.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(43,'纽约','images/face/newYork.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(44,'塔','images/face/pagoda.jpg',3);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(45,'狮身人面像','images/face/sphinx.jpg',3);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(46,'仙人掌','images/face/cactus.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(47,'花','images/face/flower.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(48,'叶子','images/face/leaf.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(49,'柑桔','images/face/orange.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(50,'红色的花','images/face/redFlower.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(51,'红辣椒','images/face/redpepper.jpg',4);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(52,'荷花','images/face/waterLily.jpg',4);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(53,'蜡烛','images/face/candle.jpg',5);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(54,'飞扬的心','images/face/flyingHeart.jpg',5);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(55,'艺妓','images/face/geisha.jpg',5);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(56,'红桃 A','images/face/heartAce.jpg',5);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(57,'橙色的心','images/face/orangeHeart.jpg',5);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(58,'外星人','images/face/alien.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(59,'外星人的脑袋','images/face/alienHead.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(60,'绿色的外星人','images/face/greenAlien.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(61,'小机器人','images/face/littleRobot.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(62,'机器虫子','images/face/robotInsect.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(63,'太空','images/face/space.jpg',6);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(64,'UFO','images/face/UFO.jpg',6);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(65,'大笑的星星','images/face/laughingStar.jpg',7);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(66,'粉红恶魔','images/face/pinkDevil.jpg',7);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(67,'傲慢的猫','images/face/proudCat.jpg',7);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(68,'傲慢的狗','images/face/proudDog.jpg',7);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(69,'玩跷跷板的小孩','images/face/seeSawBoy.jpg',7);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(70,'棒球','images/face/baseball.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(71,'篮球','images/face/basketball.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(72,'潜水','images/face/diving.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(73,'高尔夫球','images/face/golf.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(74,'曲棍球','images/face/hockey.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(75,'高尔夫球','images/face/golf.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(76,'溜冰鞋','images/face/iceskates.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(77,'山地自行车','images/face/mountainBiking.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(78,'滑雪板','images/face/snowBoarding.jpg',8);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(79,'瑜伽','images/face/yoga.jpg',8);

INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(80,'热气球','images/face/balloon.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(81,'海滨气球','images/face/beachball.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(82,'帆船','images/face/boat.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(83,'小丑鞋','images/face/clownshoe.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(84,'咖啡杯','images/face/coffeeCup.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(85,'骰子','images/face/dice.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(86,'潜水鸭子','images/face/diverDuck.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(87,'飞盘','images/face/flipFlop.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(88,'小矮人','images/face/gnome.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(89,'帽子','images/face/hat.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(90,'骷髅','images/face/skull.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(91,'太阳','images/face/sun.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(92,'骷髅','images/face/skull.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(93,'寿司','images/face/sushi.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(94,'玩具熊','images/face/teddy.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(95,'三轮车','images/face/tricycle.jpg',9);
INSERT INTO ntsky_t_user_face(id,name,path,type) VALUES(96,'小货车','images/face/wagon.jpg',9);

-- 页面样式
DROP TABLE IF EXISTS ntsky_t_style;
CREATE TABLE ntsky_t_style(
	style_id		INT NOT NULL ,				-- 页面样式编号
	name			VARCHAR(255) NOT NULL ,			-- 页面样式名称
	main_css		TEXT NOT NULL ,				-- 主样式
	layout_css		TEXT NOT NULL ,				-- 模板样式
	image_dir		VARCHAR(20) NOT NULL default '',	-- 图片路径
	page_index		TEXT NOT NULL ,				-- 首页代码
	PRIMARY KEY(style_id)
)
TYPE=InnoDB DEFAULT CHARSET=utf8 COMMENT '页面样式表';



-- 用户表增加历史密码，用于取得密码
alter table ntsky_t_user add column history_passwords text default '';