

/*==============================================================*/
/* Table: LoginHistory                                          */
/*==============================================================*/
create table LoginHistory (
LoginID              numeric(6)           identity,
UserName             varchar(30)          not null,
Alias                varchar(30)          not null,
IP                   varchar(20)          not null default 'a',
LoginTime            datetime             not null,
LogoutTime           datetime             not null,
RoomID               int                  not null
);



/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/
create table Room (
RoomID               int                  not null,
RoomName             varchar(20)          not null,
MaxUser              int                  not null default 100,
NearMaxUser          int                  not null default 80,
IniNo                int                  not null default 1,
TemplateNo           int                  not null default 1,
LogRoom              int                  not null default 0,
LogCommandInfo       int                  not null default 1,
LogUserLoginInfo     int                  not null default 1,
LogSecret            int                  not null default 1,
LogIP                int                  not null default 0,
LastMessage          int                  not null default 6,
RoomArea             int                  not null default 0,
RoomType             int                  not null default 0,
AllowGrade           int                  not null default 0,
AllowMember          int                  not null default 0,
AllowUserName        int                  not null default 0,
AllowGuest           int                  not null default 1,
CompType             int                  not null default 1,
CryptType            int                  not null default 1,
AllowPublic          int                  not null default 1,
Background           varchar(100)          not null default '/images/bbb.jpg',
LogoutUrl            varchar(50)          not null default '/',
ExternValue          varchar(50)          not null default '',
Administrators       varchar(255)         not null default 'admin',
RoomTopic            varchar(200)         not null default '',
RoomPassword         varchar(20)          not null default '',
PrivateRoom          int                  not null default 0,
SupportAudio         int                  not null default 1,
SupportVideo         int                  not null default 1,
RoomDisplay          int                 not null default 1,
Other         	     int                 not null default 0,
UserType             int                 not null default 0,
constraint PK_ROOM primary key  (RoomID)
)



/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
UserName             varchar(30)          not null,
Alias                varchar(30)          not null,
Password             varchar(40)          not null default 'a',
Age                  int                  not null default 1,
Sex                  int                  not null default 2,
Member		     int 		  not null default 0,	
Grade                int                  not null default 0,
Credit               int                  not null default 0,
Email                varchar(50)          not null default 'a',
Oicq                 varchar(20)          not null default '',
Icon                 varchar(20)          not null default '',
Photo                varchar(100)          not null default 'a',
Resume               varchar(200)          not null default 'a',
Guest                int                  not null default 3,
RegistTime           datetime             not null,
LoginTime            datetime             not null,
LogoutTime           datetime             not null,
Question             varchar(50)          not null default '',
Answer               varchar(50)          not null default '',
TotalTime            int                  not null default 0,
Status               int                  not null default 1,
IP                   varchar(20)          not null default 'a',
RoomID               int                  not null default 0,
UserType             int                  not null default 0,
constraint PK_USERINFO primary key  (UserName)
);

create table TransferCredit (
TransferCreditID      int           identity,
InitUserName          varchar(30)          not null,
InitIP                varchar(20)          not null,
InitCredit            int                 not null,
PassUserName          varchar(30)          not null,
PassIP                varchar(20)          not null,
PassCredit            int                 not null,
TransferTime            datetime          not null 
);

create table UserRoom (
UserName        varchar(30)          not null,
RoomID          int          not null,
primary key (UserName,RoomID)
);

create table ChangeCredit (
ChangeID              numeric(6)           identity,
AdminUserName        varchar(30)          not null,
AdminIP              varchar(20)          not null,
UserName        varchar(30)          not null,
ChangeTime	 datetime          not null,
OrgCredit        int               not null,
NowCredit	int		not null,
AddCredit	int		not null
primary key (ChangeID)
);

insert into Room(RoomID,RoomName) values(101,'demo room');


