drop table bn_projectinfo;
drop table bn_memberinfo;
drop table bn_subinfo;
drop table bn_buginfo;
drop table bn_bugmessage;

create table bn_projectinfo (
   projectid integer,
   projectname varchar(300) not null,
   description varchar(300) not null,
   allowpublicview boolean not null,
   allowpublicsubmit boolean not null,
   allowaddmemberbyguest boolean not null,
   isvalid boolean not null,
   startdate datetime not null
) type=innodb;

alter table bn_projectinfo add primary key (projectid);

create table bn_subinfo (
   projectid integer,
   subinfoid integer,
   subinfoname varchar(300),
   subinfodescription varchar(500),
   sortnum integer,
   isvalid boolean not null
) type=innodb;

create table bn_memberinfo (
   projectid integer,
   memberid integer,
   membername varchar(100),
   cryptedpassword varchar(100),
   mailaddr1 varchar(300),
   mailaddr2 varchar(300),
   comment varchar(400),
   isadmin boolean not null,
   isprojectadmin boolean not null,
   createdate datetime not null,
   isvalid boolean not null
) type=innodb;

alter table bn_memberinfo add primary key (projectid, memberid);

create table bn_buginfo (
   projectid integer,
   bugid integer,
   subject varchar(300) not null,
   subject_org varchar(300) not null,
   status integer not null,
   memberid integer,
   reportername varchar(100),
   updatername varchar(100),
   opendate datetime not null,
   updatedate datetime,
   isvalid boolean not null
) type=innodb;

alter table bn_buginfo add primary key (projectid, bugid);

create table bn_bugmessage (
   projectid integer,
   bugid integer,
   seq integer,
   memberid integer,
   name varchar(100),
   message text not null,
   messagedate datetime,
   oldstatus integer,
   newstatus integer not null,
   isvalid boolean not null
) type=innodb;

alter table bn_bugmessage add primary key (projectid, bugid, seq);