CREATE TABLE POST(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(128),TEL VARCHAR(128),TEL2 VARCHAR(128),POST_ID VARCHAR(128))
CREATE TABLE OFFICIALTITLE(ID INTEGER NOT NULL PRIMARY KEY,TITLE_ID VARCHAR(128),NAME VARCHAR(128))
CREATE TABLE SISETU_YOYAKU(ID INTEGER NOT NULL PRIMARY KEY,SISETU_ID INTEGER DEFAULT -1,WORKER_ID INTEGER DEFAULT -1,YEAR INTEGER,MONTH INTEGER,DAY INTEGER,START_HOUR INTEGER,START_MINUTE INTEGER,END_HOUR INTEGER,END_MINUTE INTEGER,MEMO VARCHAR(256))
CREATE TABLE OFFICE_WORKER(ID INTEGER NOT NULL PRIMARY KEY,WORKERID VARCHAR(128) DEFAULT '----',PASSWORD VARCHAR(128) DEFAULT '---- ',F_NAME VARCHAR(255) DEFAULT '---- ',S_NAME VARCHAR(255) DEFAULT '----  ',F_NAME_KANA VARCHAR(255) DEFAULT '----   ',S_NAME_KANA VARCHAR(255) DEFAULT '----',ADDR VARCHAR(255) DEFAULT '----',TEL VARCHAR(255) DEFAULT '----',LNK_POST_ID INTEGER NOT NULL,LNK_TITLE_ID INTEGER NOT NULL,MAILADDR VARCHAR(255) DEFAULT '----    ',USER_ID INTEGER NOT NULL,CONSTRAINT FK_OFFICE_WORKER_LNK_POST_ID FOREIGN KEY(LNK_POST_ID) REFERENCES POST(ID),CONSTRAINT FK_OFFICE_WORKER_LNK_TITLE_ID FOREIGN KEY(LNK_TITLE_ID) REFERENCES OFFICIALTITLE(ID))
CREATE TABLE WEBER_AUTONUMBER(TABLE_NAME VARCHAR NOT NULL PRIMARY KEY,NEXT_NUMBER INTEGER NOT NULL)
CREATE TABLE WEBER_ROLE(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR NOT NULL)
CREATE TABLE WEBER_USER(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR NOT NULL,PASSWORD VARCHAR NOT NULL,ROLE_ID INTEGER NOT NULL,CONSTRAINT FK_WEBER_USER_ROLE_ID FOREIGN KEY(ROLE_ID) REFERENCES WEBER_ROLE(ID))
CREATE TABLE MESSAGE(ID INTEGER NOT NULL,USER_ID INTEGER NOT NULL,SUBJECT VARCHAR(128),CONTENTS VARCHAR(255),CONSTRAINT SYS_PK_MESSAGE PRIMARY KEY(ID,USER_ID),CONSTRAINT FK_MESSAGE_USER_ID FOREIGN KEY(USER_ID) REFERENCES WEBER_USER(ID))
CREATE TABLE GOSTATUS(ID INTEGER NOT NULL PRIMARY KEY,WORKERID INTEGER NOT NULL,STATUS VARCHAR(128),ETC VARCHAR(255),CONSTRAINT FK_GOSTATUS_WORKERID FOREIGN KEY(WORKERID) REFERENCES OFFICE_WORKER(ID))
CREATE TABLE SISETU(ID INTEGER NOT NULL PRIMARY KEY,SISETU_ID VARCHAR(128),NAME VARCHAR(128),PLACE VARCHAR(128),MEMO VARCHAR(128))
CREATE TABLE SCHEDULE(ID INTEGER NOT NULL PRIMARY KEY,WORKERID INTEGER NOT NULL,YEAR INTEGER,MONTH INTEGER,DAY INTEGER,CONTENTS VARCHAR(255),NOTE VARCHAR(255),STARTTIME INTEGER,STARTMINUTE INTEGER,ENDTIME INTEGER,ENDMINUTE INTEGER,CONSTRAINT FK_SCHEDULE_WORKERID FOREIGN KEY(WORKERID) REFERENCES OFFICE_WORKER(ID))
CREATE TABLE POST2(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(128))
CREATE TABLE COMPANY2(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(128))
CREATE TABLE CUSTOMER2(ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(255) DEFAULT '---- ',POST_ID INTEGER NOT NULL,COMPANY_ID INTEGER NOT NULL)
ALTER TABLE SISETU_YOYAKU ADD CONSTRAINT FK_SISETU_YOYAKU_SISETU_ID FOREIGN KEY(SISETU_ID) REFERENCES SISETU(ID)
ALTER TABLE SISETU_YOYAKU ADD CONSTRAINT FK_SISETU_YOYAKU_WORKER_ID FOREIGN KEY(WORKER_ID) REFERENCES OFFICE_WORKER(ID)
ALTER TABLE OFFICE_WORKER ADD CONSTRAINT FK_OFFICE_WORKER_USER_ID FOREIGN KEY(USER_ID) REFERENCES WEBER_USER(ID)
CREATE USER SA PASSWORD "" ADMIN
SET WRITE_DELAY 60
INSERT INTO POST VALUES(1,'\u672c\u793e','123','345','100')
INSERT INTO POST VALUES(2,'\u95a2\u897f\u55b6\u696d\u6240','123','345','110')
INSERT INTO POST VALUES(3,'\u6771\u4eac\u4e8b\u696d\u6240','123','345','120')
INSERT INTO POST VALUES(4,'\u798f\u5ca1\u55b6\u696d\u6240','123','345','130')
INSERT INTO OFFICIALTITLE VALUES(1,'100','\u4e00\u822c')
INSERT INTO OFFICIALTITLE VALUES(2,'110','\u4e3b\u4efb')
INSERT INTO OFFICIALTITLE VALUES(3,'120','\u8ab2\u9577')
INSERT INTO OFFICIALTITLE VALUES(4,'130','\u90e8\u9577')
INSERT INTO OFFICE_WORKER VALUES(1,'1','1','test','taro','----   ','----','---','---',1,1,'---',1)
INSERT INTO WEBER_AUTONUMBER VALUES('OFFICE_WORKER',10)
INSERT INTO WEBER_AUTONUMBER VALUES('OFFICIAL_TITLE',10)
INSERT INTO WEBER_AUTONUMBER VALUES('POST',10)
INSERT INTO WEBER_ROLE VALUES(1,'test_role')
INSERT INTO WEBER_ROLE VALUES(2,'test_role2')
INSERT INTO WEBER_ROLE VALUES(3,'test_role3')
INSERT INTO WEBER_USER VALUES(1,'test','test-pwd',1)
INSERT INTO WEBER_USER VALUES(2,'test','test-pwd2',2)
INSERT INTO WEBER_USER VALUES(3,'test','test-pwd3',3)
INSERT INTO WEBER_USER VALUES(4,'best','test-pwd',1)
INSERT INTO GOSTATUS VALUES(1,1,'---','---')
INSERT INTO POST2 VALUES(1,'\u672c\u793e')
INSERT INTO POST2 VALUES(2,'\u95a2\u897f\u55b6\u696d\u6240')
INSERT INTO POST2 VALUES(3,'\u6771\u4eac\u4e8b\u696d\u6240')
INSERT INTO POST2 VALUES(4,'\u798f\u5ca1\u55b6\u696d\u6240')
INSERT INTO COMPANY2 VALUES(1,'\u25cb\u00d7\u5546\u4e8b')
INSERT INTO COMPANY2 VALUES(2,'\u306a\u3093\u3060\u304b\u7269\u7523')
INSERT INTO COMPANY2 VALUES(100,'hoge com')
INSERT INTO CUSTOMER2 VALUES(1,'\u307b\u3052 \u592a\u90ce',1,1)
