/* create database  */
# CREATE DATABASE JAVASCUD DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# USE JAVASCUD;

/*
    %TABPRE% : JS_
*/

/*==========================dbpool check table===================================== */

Drop Table If Exists JS_SYSINFO;

CREATE TABLE JS_SYSINFO
(
    N_NO            SMALLINT        NOT NULL,                   /* no */
    C_MEMO          VARCHAR(50)     NULL,                       /* memo */
  PRIMARY KEY  (N_NO)   
) TYPE=INNODB DEFAULT CHARSET=utf8;

insert into JS_SYSINFO (N_NO,C_MEMO) values (1,"for sys check,no delete!");

/*==========================dbkey table===================================== */

Drop Table If Exists JS_DBKEY;

CREATE TABLE JS_DBKEY
(
    N_TYPE          SMALLINT        NOT NULL,                   /* key type */
    N_KEY           INT             NOT NULL,                   /* current KEY */
    C_MEMO          VARCHAR(50)     NULL,                       /* memo */
    N_CACHESIZE     SMALLINT        NOT NULL Default 10,        /* cache size */
  PRIMARY KEY  (N_TYPE) 
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================project catagory table ===================================== */

Drop Table If Exists JS_PROJECTCATA;

CREATE TABLE JS_PROJECTCATA
(
    CID           SMALLINT        NOT NULL,                   /* catagory ID */
    TITLE         VARCHAR(200)    NULL,                       /* catagory title */
    SHOWORDER     SMALLINT        NOT NULL Default 99,        /* show order */
    ENABLE        TINYINT         NOT NULL Default 1,         /* enable */
  PRIMARY KEY  (CID)  
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================project table===================================== */

Drop Table If Exists JS_PROJECT;

CREATE TABLE JS_PROJECT 
(
    NO              INT             NOT NULL,                   /* project no */
    CID             SMALLINT        NOT NULL,                   /* catagory cid */
    NAME            VARCHAR(100)    NULL,                       /* name */
    SHORTNAME		VARCHAR(20)		NULL,						/* short name */
    PROJECTKEY      Varchar(20)     NULL,                       /* project key */
    PROJECTDESC     TEXT            NULL,                       /* descript: */
    URL             Varchar(200)    NULL,                       /* website url */
    APPLYTIME       DATETIME        NULL,                       /* apply time */
    APPLYUSER       Varchar(20)     NULL,                       /* apply user */
    APPROVED        TinyInt         NOT NULL Default 0,         /* approved? */
    APPROVETIME     DATETIME        NULL,                       /* approved time */
    ENABLE			TinyInt			Not Null Default 1,			/* enable or not */
  PRIMARY KEY  (NO)  
) TYPE=INNODB DEFAULT CHARSET=utf8;

/*==========================project users===================================== */

Drop Table If Exists JS_PROJECTMEMBER;

CREATE TABLE JS_PROJECTMEMBER 
(
    PROJECTNO       INT             NOT NULL,                   /* project no */
    USERNAME        Varchar(20)     NULL,                       /* memebers username */
    ADDTIME			DateTime		NULL,						/* add time */
  PRIMARY KEY  (PROJECTNO,USERNAME)  
) TYPE=INNODB DEFAULT CHARSET=utf8;



/*==========================global section setting===================================== */
Drop Table If Exists JS_SECTIONSCHEME;

CREATE TABLE JS_SECTIONSCHEME 
(
    SECTIONKEY      Varchar(20)     NULL,                       /* section key */
    CLASSNAME       VARCHAR(100)    NULL,                       /* class name */
    SHOWORDER       SMALLINT        NOT NULL Default 99,        /* show order */
    ENABLE			TinyInt			Not NUll default 1,			/* enable */
  PRIMARY KEY  (SECTIONKEY)  
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================global section config===================================== */
Drop Table If Exists JS_SECTIONCONFIG;

CREATE TABLE JS_SECTIONCONFIG 
(
	NAME	 VARCHAR( 50 ) 	NOT NULL ,
	VALUE 	 VARCHAR( 200 ) ,
	PRIMARY KEY ( NAME ) 
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================project section base===================================== */

Drop Table If Exists JS_PROJECTSECTION;

CREATE TABLE JS_PROJECTSECTION 
(
    PROJECTNO       INT             NOT NULL,                   /* project no */
    SECTIONKEY      Varchar(20)     NULL,                       /* section key */
    ENABLE			TinyInt			Not NUll default 1,			/* enable */
    APPLYTIME       DATETIME        NULL,                       /* apply time */
    APPLYUSER       Varchar(20)     NULL,                       /* apply user */
    APPROVED        TinyInt         NOT NULL Default 0,         /* approved? */
    APPROVETIME     DATETIME        NULL,                       /* approved time */
  PRIMARY KEY  (PROJECTNO,SECTIONKEY)  
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================project section roleuser===================================== */

Drop Table If Exists JS_PROJECTSEC_RUSER;

CREATE TABLE JS_PROJECTSEC_RUSER 
(
    PROJECTNO       INT             NOT NULL,                   /* project no */
    SECTIONKEY      Varchar(20)     NULL,                       /* section key */
    ROLENAME        VARCHAR(30)     NULL,                       /* rolename */
    USERNAME        Varchar(20)     NULL,                       /* username */
    ADDTIME			DateTime		Null,						/* add time */
  PRIMARY KEY  (PROJECTNO,SECTIONKEY,ROLENAME,USERNAME)  
) TYPE=INNODB DEFAULT CHARSET=utf8;

/*==========================project section pairinfo===================================== */


Drop Table If Exists JS_PROJECTSEC_PAIRINFO;

CREATE TABLE JS_PROJECTSEC_PAIRINFO 
(
    PROJECTNO       INT             NOT NULL,                   /* project no */
    SECTIONKEY      Varchar(20)     NULL,                       /* section key */
    PAIRNAME        VARCHAR(50)     NULL,                       /* name */
    PAIRVALUE       Varchar(250)    NULL,                       /* value */
  PRIMARY KEY  (PROJECTNO,SECTIONKEY,PAIRNAME)  
) TYPE=INNODB DEFAULT CHARSET=utf8;




/*==========================project apply table===================================== */

Drop Table If Exists JS_APPLY_PROJECT;

CREATE TABLE JS_APPLY_PROJECT 
(
    NO              INT             NOT NULL,                   /* apply no */
    CID             SMALLINT        NOT NULL,                   /* catagory cid */
    NAME            VARCHAR(100)    NULL,                       /* name */
    SHORTNAME		VARCHAR(20)		NULL,						/* short name */
    PROJECTKEY      Varchar(20)     NULL,                       /* project key */
    PROJECTDESC     TEXT            NULL,                       /* descript: */
    APPLYDESC       TEXT            NULL,                       /* apply descript: */
    URL             Varchar(200)    NULL,                       /* website url */
    APPLYTIME       DATETIME        NULL,                       /* apply time */
    APPLYUSER       Varchar(20)     NULL,                       /* apply user */
    APPROVED        TinyInt         NOT NULL Default 0,         /* approved? */
    DEALTIME        DATETIME        NULL,                       /* deal time */
    DEALED			TinyInt			Not Null Default 0,			/* deal or not */
    COMMENT         TEXT            NULL,                       /* comment */
  PRIMARY KEY  (NO)  
) TYPE=INNODB DEFAULT CHARSET=utf8;


/*==========================project applu users===================================== */

Drop Table If Exists JS_PROJECTMEMBER_APPLY;

CREATE TABLE JS_PROJECTMEMBER_APPLY 
(
	NO				INT				NOT NULL,					/* apply no */
    PROJECTNO       INT             NOT NULL,                   /* project no */
    USERNAME        Varchar(20)     NULL,                       /* memebers username */
    APPLYTIME       DATETIME        NULL,                       /* apply time */
    APPLYDESC       VARCHAR(250)    NULL,                       /* apply reson */
    DEALTIME        DATETIME        NULL,                       /* deal time */
    DEALED			TinyInt			Not Null Default 0,			/* deal or not */
    DEALDESC        VARCHAR(250)    NULL,                       /* deal desc */
    APPROVED        TinyInt         NOT NULL Default 0,         /* approved? */
  PRIMARY KEY  (no)  
) TYPE=INNODB DEFAULT CHARSET=utf8;



/*==========================common settings ===================================== */

Drop Table If Exists JS_COMMONCONFIG;

CREATE TABLE JS_COMMONCONFIG 
(
	TYPE			INT				 Default 1 NOT NULL,		 /* type */
    NAME            Varchar(50)      NULL,                       /* config name */
    CAPTION         Varchar(100)     NULL,                       /* config caption */
    DESCRIPTION     VARCHAR(250)     NULL,                       /* desc */
    DATATYPE        VARCHAR(100)	 not NULL,                   /* datatype */
    CTRLTYPE		Int				 Not Null ,					 /* ctrl type */
    VALUE        	TEXT    		 NULL,                       /* value */
    SHOWORDER       Int         	 NOT NULL Default 0,         /* showorder */
  PRIMARY KEY  (NAME)  
) TYPE=INNODB DEFAULT CHARSET=utf8;



/*==========================project section roleuser===================================== */

Drop Table If Exists JS_COMMON_ROLEUSER;

CREATE TABLE JS_COMMON_ROLEUSER 
(
    ROLENAME        VARCHAR(30)     NULL,                       /* rolename */
    USERNAME        Varchar(20)     NULL,                       /* username */
    ADDTIME			DateTime		Null,						/* add time */
  PRIMARY KEY  (ROLENAME,USERNAME)  
) TYPE=INNODB DEFAULT CHARSET=utf8;