開発依頼 #4806: [orca] webORCA연동관련테이블생성 - RC-LS, CheckEye DX - Redmine for HICS

開発依頼 #4806

金基鐘이(가) 12달 전에 변경

<div>(테이블리스트)</div>

<div> </div>

<div>WebORCA_CLOUD情報</div>

<div> 1. 서버증명서정보 <div>서버증명서정보 : CMI1050M ( 2건등록 )</div>

<div> 2. 클라이언트증명서정보 <div>클라이언트증명서정보 : CMI1051M ( 1건등록 )</div>

<div>WebORCA_ONPRE情報</div>

<div> 1. 클라이언트접속정보 <div>클라이언트연결이력정보 : CMI1052M</div>

<div> </div>

<div>접속로그정보</div>

<div> 1. 클라이언트연결이력정보 : CMR1051H</div>

<div> </div>

<div> </div>

<div>(스크립트)</div>

<div>-- Drop table</div>

<div> </div>

<div>-- DROP TABLE us_cm.cmi1050m</div>

<div>--서버증명서정보</div>

<div>CREATE TABLE us_cm.cmi1050m (</div>

<div><span style="white-space: normal;">svr_url_fg varchar(1) NOT NULL, -- 運営/デモ区分</span></div>

<div><span style="white-space: normal;">svr_url varchar(200) NULL DEFAULT NULL::character varying, -- 運営/デモURL</span></div>

<div><span style="white-space: normal;">ca_cert varchar(4096) NULL DEFAULT NULL::character varying, -- 証明書(ROOT.CRT)</span></div>

<div><span style="white-space: normal;">cre_user varchar(20) NULL DEFAULT NULL::character varying, -- 作成者ID</span></div>

<div><span style="white-space: normal;">cre_dttm timestamp NULL, -- 作成日時</span></div>

<div><span style="white-space: normal;">upd_user varchar(20) NULL DEFAULT NULL::character varying, -- 更新者ID</span></div>

<div><span style="white-space: normal;">upd_dttm timestamp NULL, -- 更新日時</span></div>

<div><span style="white-space: normal;">CONSTRAINT ix_cmi1050m_pk PRIMARY KEY (svr_url_fg)</span></div>

<div>);</div>

<div>COMMENT ON TABLE us_cm.cmi1050m IS 'サーバー証明書情報';</div>

<div> </div>

<div>-- Column comments</div>

<div> </div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.svr_url_fg IS '運営/デモ区分';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.svr_url IS '運営/デモURL';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.ca_cert IS '証明書(ROOT.CRT) ';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.cre_user IS '作成者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.cre_dttm IS '作成日時';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.upd_user IS '更新者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1050m.upd_dttm IS '更新日時';</div>

<div> </div>

<div>-- Permissions</div>

<div> </div>

<div>ALTER TABLE us_cm.cmi1050m OWNER TO owlpg;</div>

<div>GRANT ALL ON TABLE us_cm.cmi1050m TO owlpg;</div>

<div> </div>

<div>--クライアント증명서정보</div>

<div>CREATE TABLE us_cm.cmi1051m (</div>

<div><span style="white-space: normal;">cstm_id varchar(10) NOT NULL, -- 顧客ID</span></div>

<div><span style="white-space: normal;">svr_url_fg varchar(1) NULL DEFAULT NULL::character varying, -- 運営/デモ区分</span></div>

<div><span style="white-space: normal;">cert varchar(4096) NULL DEFAULT NULL::character varying, -- クライアント証明書</span></div>

<div><span style="white-space: normal;">cert_key varchar(4096) NULL DEFAULT NULL::character varying, -- クライアント証明書秘密鍵</span></div>

<div><span style="white-space: normal;">key_pass varchar(100) NULL DEFAULT NULL::character varying, -- 証明書パスワード</span></div>

<div><span style="white-space: normal;">api_key varchar(500) NULL DEFAULT NULL::character varying, -- 日レセAPIキー</span></div>

<div><span style="white-space: normal;">cre_user varchar(20) NULL DEFAULT NULL::character varying, -- 作成者ID</span></div>

<div><span style="white-space: normal;">cre_dttm timestamp NULL, -- 作成日時</span></div>

<div><span style="white-space: normal;">upd_user varchar(20) NULL DEFAULT NULL::character varying, -- 更新者ID</span></div>

<div><span style="white-space: normal;">upd_dttm timestamp NULL, -- 更新日時</span></div>

<div><span style="white-space: normal;">CONSTRAINT ix_cmi1051m_pk PRIMARY KEY (cstm_id)</span></div>

<div>);</div>

<div>COMMENT ON TABLE us_cm.cmi1051m IS 'クライアント証明書情報';</div>

<div> </div>

<div>-- Column comments</div>

<div> </div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.cstm_id IS '顧客ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.svr_url_fg IS '運営/デモ区分';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.cert IS 'クライアント証明書';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.cert_key IS 'クライアント証明書秘密鍵';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.key_pass IS '証明書パスワード';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.api_key IS '日レセAPIキー';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.cre_user IS '作成者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.cre_dttm IS '作成日時';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.upd_user IS '更新者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1051m.upd_dttm IS '更新日時';</div>

<div> </div>

<div>-- Permissions</div>

<div> </div>

<div>ALTER TABLE us_cm.cmi1051m OWNER TO owlpg;</div>

<div>GRANT ALL ON TABLE us_cm.cmi1051m TO owlpg;</div>

<div> </div>

<div>--WebORCA接続履歴情報</div>

<div>CREATE TABLE us_cm.cmr1051h (</div>

<div><span style="white-space: normal;">cstm_id varchar(10) NOT NULL, -- 顧客ID</span></div>

<div><span style="white-space: normal;">karte_id varchar(200) NULL DEFAULT NULL::character varying, -- 電子カルテＵＩＤ</span></div>

<div><span style="white-space: normal;">op_flg varchar(1) NULL DEFAULT NULL::character varying, -- operaion flag(1:req,2:rlt)</span></div>

<div><span style="white-space: normal;">msg_xml varchar(4096) NULL DEFAULT NULL::character varying, -- 要求及び結果内容</span></div>

<div><span style="white-space: normal;">cre_user varchar(20) NULL DEFAULT NULL::character varying, -- 作成者ID</span></div>

<div><span style="white-space: normal;">cre_dttm timestamp NULL, -- 作成日時</span></div>

<div><span style="white-space: normal;">upd_user varchar(20) NULL DEFAULT NULL::character varying, -- 更新者ID</span></div>

<div><span style="white-space: normal;">upd_dttm timestamp NULL, -- 更新日時</span></div>

<div><span style="white-space: normal;">CONSTRAINT ix_cmr1051h_pk PRIMARY KEY (cstm_id)</span></div>

<div>);</div>

<div>COMMENT ON TABLE us_cm.cmr1051h IS 'WebORCA接続履歴情報';</div>

<div> </div>

<div>-- Column comments</div>

<div> </div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.cstm_id IS '顧客ID';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.karte_id IS '運営/デモ区分';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.op_flg IS 'クライアント証明書';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.msg_xml IS 'クライアント証明書秘密鍵';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.cre_user IS '作成者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.cre_dttm IS '作成日時';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.upd_user IS '更新者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmr1051h.upd_dttm IS '更新日時';</div>

<div> </div>

<div>-- Permissions</div>

<div> </div>

<div>ALTER TABLE us_cm.cmr1051h OWNER TO owlpg;</div>

<div>GRANT ALL ON TABLE us_cm.cmr1051h TO owlpg;</div>

<div> </div>

<div>
<div>--cmi1052m WebORCA_ONPRE情報</div>

<div>CREATE TABLE us_cm.cmi1052m (</div>

<div><span style="white-space: normal;">cstm_id varchar(10) NOT NULL, -- 顧客ID</span></div>

<div><span style="white-space: normal;">ip varchar(100) NULL DEFAULT NULL::character varying, -- クライアントIP</span></div>

<div><span style="white-space: normal;">pt varchar(20) NULL DEFAULT NULL::character varying, -- クライアントPORT</span></div>

<div><span style="white-space: normal;">orca_dbuser varchar(100) NULL DEFAULT NULL::character varying, -- orcaユーザーID</span></div>

<div><span style="white-space: normal;">orca_dbpass varchar(100) NULL DEFAULT NULL::character varying, -- orcaパスワード</span></div>

<div><span style="white-space: normal;">cre_user varchar(20) NULL DEFAULT NULL::character varying, -- 作成者ID</span></div>

<div><span style="white-space: normal;">cre_dttm timestamp NULL, -- 作成日時</span></div>

<div><span style="white-space: normal;">upd_user varchar(20) NULL DEFAULT NULL::character varying, -- 更新者ID</span></div>

<div><span style="white-space: normal;">upd_dttm timestamp NULL, -- 更新日時</span></div>

<div><span style="white-space: normal;">CONSTRAINT ix_cmi1052m_pk PRIMARY KEY (cstm_id)</span></div>

<div>);</div>

<div>COMMENT ON TABLE us_cm.cmi1052m IS 'WebORCA_ONPRE情報';</div>

<div> </div>

<div>-- Column comments</div>

<div> </div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.cstm_id IS '顧客ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.ip IS 'クライアントIP';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.pt IS 'クライアントPORT';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.orca_dbuser IS 'orcaユーザーID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.orca_dbpass IS 'orcaパスワード';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.cre_user IS '作成者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.cre_dttm IS '作成日時';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.upd_user IS '更新者ID';</div>

<div>COMMENT ON COLUMN us_cm.cmi1052m.upd_dttm IS '更新日時';</div>

<div> </div>

<div>-- Permissions</div>

<div> </div>

<div>ALTER TABLE us_cm.cmi1052m OWNER TO owlpg;</div>

<div>GRANT ALL ON TABLE us_cm.cmi1052m TO owlpg;</div>

<div> </div>

<div>--20240628추가<br />
ALTER TABLE us_cm.cmi1051m ADD COLUMN open_yn varchar(1) NULL DEFAULT 'N'::character VARYING ;</div>
</div>

뒤로