--    Bugslife table schema
--    db mySql 3.29.47
--    create 
--    auter

drop database bugslife;

create database bugslife; 

--システムテーブル
CREATE TABLE SysConf (	
    sysno TINYINT UNSIGNED not null AUTO_INCREMENT	,
    sysid VARCHAR(16) not null	,
    sysname VARCHAR(128) not null	,
    sysinfo TEXT	,
    currentPno CHAR(2) not null	,
    currentAppver CHAR(2) not null	,
    currentDbver CHAR(2) not null	,
    currentHtmlver CHAR(2) not null	,
    currentSpecver CHAR(2) not null	,
    attachPath VARCHAR(64) not null	,
    updateUno INT not null	,
    updateDate TIMESTAMP not null	,
	smtpAdress VARCHAR(64),
	fromAdress VARCHAR(64),
primary key (sysno)
);	

--ユーザテーブル
CREATE TABLE UserInfo (	
    uno INT UNSIGNED not null AUTO_INCREMENT	,
    uid VARCHAR(16) not null	,
    password VARCHAR(16) not null	,
    name VARCHAR(20) not null	,
    nickname VARCHAR(40) not null	,
    email text not null	,
    company VARCHAR(40)	,
    section VARCHAR(40)	,
    tel VARCHAR(16)	,
    memo VARCHAR(255)	,
    dispFlag TINYINT UNSIGNED DEFAULT 1	,
    info VARCHAR(255)	,
    permission INT UNSIGNED not null	,
    lastLogoffDate DATETIME	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP	,
	mailSubscription INT UNSIGNED DEFAULT 0,
primary key (uno),
UNIQUE (uid),
UNIQUE (nickname)
);	
--バグテーブル
CREATE TABLE Bug (	
    bno INT UNSIGNED not null AUTO_INCREMENT	,
    bid VARCHAR(32)	,
    title VARCHAR(128) not null	,
    location CHAR(2) DEFAULT '00'	,
    subsystem CHAR(2) DEFAULT '00'	,
    functionName CHAR(2) DEFAULT '00'	,
    screen VARCHAR(255)	,
    browser CHAR(2) DEFAULT '00'	,
    os CHAR(2) DEFAULT '00'	,
    tid VARCHAR(32)	,
    appVersion CHAR(2) DEFAULT '00'	,
    dbVersion CHAR(2) DEFAULT '00'	,
    htmlVersion CHAR(2) DEFAULT '00'	,
    specVersion CHAR(2) DEFAULT '00'	,
    issuer INT UNSIGNED not null	,
    issuDate DATETIME	,
    registDate DATETIME not null	,
    severity CHAR(2) DEFAULT '00'	,
    priority CHAR(2) DEFAULT '00'	,
    status TINYINT UNSIGNED not null	,
    statusWhitebord VARCHAR(160)	,
    resolution CHAR(2) DEFAULT '00'	,
    milestone CHAR(2) DEFAULT '00'	,
    scid VARCHAR(32)	,
    keyword1 VARCHAR(64)	,
    keyword2 VARCHAR(64)	,
    keyword3 VARCHAR(64)	,
    cpid1 VARCHAR(32)	,
    cpid2 VARCHAR(32)	,
    cpid3 VARCHAR(32)	,
    cpid4 VARCHAR(32)	,
    cpid5 VARCHAR(32)	,
    reletedDoc VARCHAR(64)	,
    auther INT UNSIGNED DEFAULT 0	,
    authDate DATETIME	,
    errorType CHAR(2) DEFAULT '00'	,
    errorCause CHAR(2) DEFAULT '00'	,
    errorPoint CHAR(2) DEFAULT '00'	,
    issuPno CHAR(2) DEFAULT '00'	,
    trainPno CHAR(2) DEFAULT '00'	,
    shouldFindPno CHAR(2) DEFAULT '00'	,
    errorDelayReason CHAR(2) DEFAULT '00'	,
    releaseSchDate DATETIME	,
    analyst INT UNSIGNED DEFAULT 0	,
    analizeDate DATETIME	,
    updateSrc VARCHAR(255)	,
    updateDoc VARCHAR(255)	,
    updater INT UNSIGNED DEFAULT 0	,
    bugUpdateDate DATETIME	,
    releaseDate DATETIME	,
    comfirmer INT UNSIGNED DEFAULT 0	,
    comfirmDate DATETIME	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
    persistDate DATETIME not null	,
    messageId VARCHAR(64),
    dispatch INT UNSIGNED DEFAULT 0,
primary key (bno)
);

--バグ変更履歴テーブル
CREATE TABLE BugActivity (	
    seq INT UNSIGNED not null AUTO_INCREMENT	,
    bno INT UNSIGNED not null	,
    item VARCHAR(255)	,
    oldValue VARCHAR(255)	,
    newValue VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,seq)
);	

--添付ファイルテーブル
CREATE TABLE Attachment (	
    bno INT UNSIGNED not null	,
    ano INT UNSIGNED not null AUTO_INCREMENT	,
    fileType TINYINT UNSIGNED not null	,
    mimeType VARCHAR(64) not null	,
    fileName VARCHAR(64)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
    size INT UNSIGNED DEFAULT 0,
primary key (ano)
);	

--追加情報テーブル
CREATE TABLE AddText (	
    bno INT UNSIGNED not null	,
    entryDate DATETIME not null	,
    entryUno INT UNSIGNED not null	,
    textType TINYINT UNSIGNED not null	,
    content TEXT not null	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,entryDate,textType)
);	

--バグ依存関係テーブル
CREATE TABLE BugDepends (	
    bno INT UNSIGNED not null	,
    dependBno INT UNSIGNED not null	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,dependBno)	
);	

--ＣＣテーブル
CREATE TABLE Cc (	
    bno INT UNSIGNED not null	,
    uno INT UNSIGNED not null	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,uno)
);	

--バグ画面テーブル
CREATE TABLE BugScreen (	
    bno INT UNSIGNED not null	,
    scid VARCHAR(32) not null	,
    status TINYINT UNSIGNED DEFAULT 0	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,scid)
);	

--バグコンポーネントテーブル
CREATE TABLE BugComponent (	
    bno INT UNSIGNED not null	,
    cpid INT UNSIGNED not null	,
    status INT UNSIGNED not null DEFAULT 0	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (bno,cpid)
);	

--画面テーブル
CREATE TABLE Screen (	
    scid VARCHAR(32) not null	,
    name VARCHAR(128) not null	,
    resource VARCHAR(255)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (scid)
);	

--コンポーネントテーブル
CREATE TABLE ComponentName (	
    cpid VARCHAR(32) not null	,
    name VARCHAR(128) not null	,
    resource VARCHAR(255)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (cpid)
);	

--
-- 定数テーブル
--

--対処方法テーブル
CREATE TABLE Resolution (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--優先度テーブル
CREATE TABLE Priority (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--重要度テーブル
CREATE TABLE Severity (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--ロケーションテーブル
CREATE TABLE Location (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--ＯＳ名テーブル
CREATE TABLE Os (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--ブラウザ名テーブル
CREATE TABLE Browser (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--エラー内容テーブル
CREATE TABLE ErrorType (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--エラー原因テーブル
CREATE TABLE ErrorCause (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--エラー機能箇所テーブル
CREATE TABLE ErrorPoint (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--エラー摘出遅延理由テーブル
CREATE TABLE ErrorDelayReason (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--アプリケーションバージョンテーブル
CREATE TABLE AppVersion (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--ＤＢバージョンテーブル
CREATE TABLE DbVersion (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--ＨＴＭＬバージョンテーブル
CREATE TABLE HtmlVersion (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--機能バージョンテーブル
CREATE TABLE SpecVersion (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--工程名テーブル
CREATE TABLE ProcessName (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--マイルストーンテーブル
CREATE TABLE Milestone (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--サブシステムテーブル
CREATE TABLE Subsystem (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
--機能名テーブル
CREATE TABLE FunctionName (	
    code CHAR(2) not null	,
    name VARCHAR(64) not null	,
    shortName VARCHAR(32)	,
    info VARCHAR(255)	,
    updateUno INT UNSIGNED not null	,
    updateDate TIMESTAMP not null	,
primary key (code)
);
-- 統計用のテンポラリテーブル
CREATE TABLE TmpSt1 (	
    cDate VARCHAR(7) not null,
    count INT not null	,
primary key (cDate)
);	
-- 統計用のテンポラリテーブル
CREATE TABLE TmpSt2 (	
    cDate VARCHAR(7) not null,
    count INT not null	,
primary key (cDate)
);	
-- 統計用のテンポラリテーブル
CREATE TABLE TmpSt3 (	
    cDate VARCHAR(7) not null,
    count INT not null	,
primary key (cDate)
);	
-- 統計用のテンポラリテーブル
CREATE TABLE TmpSt4 (	
    cDate VARCHAR(7) not null,
    count INT not null	,
primary key (cDate)
);	
-- 統計用のテンポラリテーブル
CREATE TABLE TmpSt5 (	
    cDate VARCHAR(7) not null,
    count INT not null	,
primary key (cDate)
);	
