bec83c721e
- 10개 모듈 추가/확장(au py rc st lg cl mm vl mg cm), 각 30 서비스 = 330 + tmsrv - build.sh 자동발견으로 11 모듈서버 + dbio 라이브러리 23 + 배치 22 빌드 - schema.d 11파일 → 75 테이블, 모듈별 disjoint - 부팅 리소스 규명: fs.mqueue.queues_max=8192(330큐>256), NDRX_MSGSIZEMAX 16000· MSGMAX 50(큐당 5.6MB→0.8MB), msgqueue ulimit 2GB → 전 12서버 runok - 검증: xadmin psc 333 AVAIL, 매입체인 XA 커밋, prepared_xacts=0 Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
112 lines
4.8 KiB
SQL
112 lines
4.8 KiB
SQL
-- cm 모듈 (공통 / common) 스키마
|
|
-- 공통코드/파라미터/휴일/환율/채번/공통로그 + seed 데이터. Prefix 65.
|
|
-- Loaded into PostgreSQL on first boot (docker-entrypoint-initdb.d).
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 공통코드 (cm_code)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cm_code (
|
|
code_grp TEXT NOT NULL, -- 코드 그룹 (예: CCY, CHANNEL)
|
|
code TEXT NOT NULL, -- 코드 값
|
|
code_name TEXT NOT NULL, -- 코드 명
|
|
sort_ord INTEGER NOT NULL DEFAULT 0,
|
|
use_yn TEXT NOT NULL DEFAULT 'Y',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (code_grp, code)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 공통 파라미터 (cm_param) - 시스템 운영 파라미터 (BIZ_DATE 포함)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cm_param (
|
|
param_key TEXT PRIMARY KEY,
|
|
param_val TEXT NOT NULL,
|
|
descr TEXT NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 휴일 (cm_holiday) - 영업일 계산에 사용
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cm_holiday (
|
|
holi_date DATE PRIMARY KEY,
|
|
holi_name TEXT NOT NULL,
|
|
nation TEXT NOT NULL DEFAULT 'KR'
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 환율 (cm_fx_rate) - 통화별 기준일 환율 (bps of 100 = KRW*100/unit)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cm_fx_rate (
|
|
ccy TEXT NOT NULL,
|
|
base_date DATE NOT NULL,
|
|
rate_bps BIGINT NOT NULL, -- KRW per unit * 100
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (ccy, base_date)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 채번 (cm_seq) - 이름있는 공통 시퀀스 (CM_SEQ 서비스)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cm_seq (
|
|
seq_name TEXT PRIMARY KEY,
|
|
seq_val BIGINT NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 공통 로그 (cm_log) - CM_LOG 서비스 + cm_purge_batch 대상
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS cm_log_seq;
|
|
CREATE TABLE IF NOT EXISTS cm_log (
|
|
log_id BIGINT PRIMARY KEY,
|
|
svc_name TEXT NOT NULL DEFAULT '',
|
|
log_level TEXT NOT NULL DEFAULT 'I', -- I/W/E
|
|
msg TEXT NOT NULL DEFAULT '',
|
|
biz_date DATE NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_cm_log_date ON cm_log (biz_date);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Seed rows
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO cm_code (code_grp, code, code_name, sort_ord) VALUES
|
|
('CCY', 'KRW', '원화', 1),
|
|
('CCY', 'USD', '미국 달러', 2),
|
|
('CCY', 'JPY', '일본 엔', 3),
|
|
('CCY', 'EUR', '유로', 4),
|
|
('CHANNEL', 'POS', 'POS 단말', 1),
|
|
('CHANNEL', 'EDC', 'EDC 단말', 2),
|
|
('CHANNEL', 'EDI', 'EDI 청구', 3),
|
|
('CHANNEL', 'FOREIGN', '해외 매입', 4),
|
|
('STATUS', 'A', '접수', 1),
|
|
('STATUS', 'S', '정산완료', 2),
|
|
('STATUS', 'C', '취소', 3)
|
|
ON CONFLICT (code_grp, code) DO NOTHING;
|
|
|
|
INSERT INTO cm_param (param_key, param_val, descr) VALUES
|
|
('BIZ_DATE', '2026-07-19', '현재 영업일'),
|
|
('VAT_RATE', '10', '부가세율(%)'),
|
|
('WHT_BPS', '2200', '원천징수율(bps)'),
|
|
('LOG_KEEP_DAYS','90', '공통로그 보존일수'),
|
|
('SETTLE_CYCLE','1', '정산주기(일)')
|
|
ON CONFLICT (param_key) DO NOTHING;
|
|
|
|
INSERT INTO cm_holiday (holi_date, holi_name, nation) VALUES
|
|
('2026-01-01', '신정', 'KR'),
|
|
('2026-03-01', '삼일절', 'KR'),
|
|
('2026-05-05', '어린이날', 'KR'),
|
|
('2026-08-15', '광복절', 'KR'),
|
|
('2026-12-25', '성탄절', 'KR')
|
|
ON CONFLICT (holi_date) DO NOTHING;
|
|
|
|
INSERT INTO cm_fx_rate (ccy, base_date, rate_bps) VALUES
|
|
('USD', '2026-07-19', 135000), -- 1350.00 KRW
|
|
('JPY', '2026-07-19', 900), -- 9.00 KRW
|
|
('EUR', '2026-07-19', 147000) -- 1470.00 KRW
|
|
ON CONFLICT (ccy, base_date) DO NOTHING;
|
|
|
|
INSERT INTO cm_seq (seq_name, seq_val) VALUES
|
|
('BATCH', 0), ('SLIP', 0), ('TICKET', 0)
|
|
ON CONFLICT (seq_name) DO NOTHING;
|