bkyungee
114 lines
3.8 KiB
Bash
Executable File
114 lines
3.8 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
# TROAD Oracle 컨테이너에 sql/*.sql 을 순서대로 적용한다.
|
|
#
|
|
# 사용법:
|
|
# ./apply-sql.sh # sql/ 전체 (파일명 순)
|
|
# ./apply-sql.sh sql/10_v_incident_ftms.sql sql/20_v_incident_ftms_data.sql
|
|
#
|
|
# 컨테이너가 기동/healthy 상태여야 한다. (start-all-server.sh 또는 docker compose up)
|
|
set -euo pipefail
|
|
|
|
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
|
|
CONTAINER="${ORACLE_CONTAINER:-exaichatbot-agent-oracle-troad}"
|
|
PDB="${ORACLE_PDB:-ORCL2}"
|
|
SYS_PASSWORD="${ORACLE_SYS_PASSWORD:-OracleExdev!2026}"
|
|
REMOTE_DIR="/tmp/troad-sql"
|
|
|
|
log() { printf '\033[36m[oracle-sql]\033[0m %s\n' "$*"; }
|
|
err() { printf '\033[31m[oracle-sql]\033[0m %s\n' "$*" >&2; }
|
|
|
|
if ! docker inspect "$CONTAINER" >/dev/null 2>&1; then
|
|
err "컨테이너를 찾을 수 없습니다: $CONTAINER"
|
|
err "먼저 기동하세요: docker compose -f docker-compose.oracle.yml up -d"
|
|
exit 1
|
|
fi
|
|
|
|
state="$(docker inspect -f '{{.State.Status}}' "$CONTAINER")"
|
|
if [[ "$state" != "running" ]]; then
|
|
err "컨테이너가 running 상태가 아닙니다 (현재: $state)"
|
|
exit 1
|
|
fi
|
|
|
|
# healthcheck 통과 대기 (최대 10분)
|
|
for _ in $(seq 1 120); do
|
|
health="$(docker inspect -f '{{if .State.Health}}{{.State.Health.Status}}{{else}}none{{end}}' "$CONTAINER")"
|
|
[[ "$health" == "healthy" || "$health" == "none" ]] && break
|
|
sleep 5
|
|
done
|
|
if [[ "${health:-}" != "healthy" && "${health:-}" != "none" ]]; then
|
|
err "Oracle 이 healthy 상태가 아닙니다 (현재: ${health:-unknown})"
|
|
exit 1
|
|
fi
|
|
|
|
if [[ $# -gt 0 ]]; then
|
|
FILES=("$@")
|
|
else
|
|
FILES=()
|
|
while IFS= read -r f; do FILES+=("$f"); done < <(find "$SCRIPT_DIR/sql" -maxdepth 1 -name '*.sql' | sort)
|
|
fi
|
|
|
|
if [[ ${#FILES[@]} -eq 0 ]]; then
|
|
err "적용할 SQL 파일이 없습니다."
|
|
exit 1
|
|
fi
|
|
|
|
docker exec "$CONTAINER" mkdir -p "$REMOTE_DIR"
|
|
|
|
for file in "${FILES[@]}"; do
|
|
path="$file"
|
|
[[ -f "$path" ]] || path="$SCRIPT_DIR/$file"
|
|
if [[ ! -f "$path" ]]; then
|
|
err "파일 없음: $file"
|
|
exit 1
|
|
fi
|
|
|
|
base="$(basename "$path")"
|
|
log "적용: $base"
|
|
docker cp "$path" "$CONTAINER:$REMOTE_DIR/$base"
|
|
|
|
# 한글 데이터를 위해 NLS_LANG 을 AL32UTF8 로 고정하고 PDB 에 접속한다.
|
|
output="$(docker exec -e NLS_LANG=.AL32UTF8 -i "$CONTAINER" bash -lc "
|
|
sqlplus -S -L sys/'${SYS_PASSWORD}'@localhost:1521/${PDB} as sysdba <<'EOSQL'
|
|
WHENEVER SQLERROR EXIT SQL.SQLCODE
|
|
WHENEVER OSERROR EXIT FAILURE
|
|
SET ECHO OFF
|
|
SET FEEDBACK OFF
|
|
SET PAGESIZE 0
|
|
SET LINESIZE 200
|
|
@${REMOTE_DIR}/${base}
|
|
EXIT
|
|
EOSQL
|
|
" 2>&1)" || {
|
|
err "실패: $base"
|
|
printf '%s\n' "$output" >&2
|
|
exit 1
|
|
}
|
|
|
|
[[ -n "${output// /}" ]] && printf '%s\n' "$output"
|
|
done
|
|
|
|
log "검증"
|
|
docker exec -e NLS_LANG=.AL32UTF8 -i "$CONTAINER" bash -lc "
|
|
sqlplus -S -L sys/'${SYS_PASSWORD}'@localhost:1521/${PDB} as sysdba <<'EOSQL'
|
|
SET PAGESIZE 50
|
|
SET LINESIZE 200
|
|
COLUMN OWNER FORMAT A12
|
|
COLUMN OBJECT_NAME FORMAT A30
|
|
COLUMN OBJECT_TYPE FORMAT A12
|
|
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
|
|
FROM ALL_OBJECTS
|
|
WHERE (OWNER = 'OPR_MAIN' AND OBJECT_NAME IN ('T_INCIDENT_FTMS', 'V_INCIDENT_FTMS', 'V_ROUTE_NEW_NM', 'V_ROUTE_TRFC_VER001', 'V_ACDT_BRNWS_VER001'))
|
|
OR (OWNER = 'TROAD_APP' AND OBJECT_NAME IN ('V_ROUTE_NEW_NM', 'V_ROUTE_TRFC_VER001', 'V_ACDT_BRNWS_VER001', 'T_ROUTE_NEW_NM', 'T_ROUTE_TRFC_VER001', 'T_ACDT_BRNWS_VER001'))
|
|
OR (OWNER = 'TLISDEV' AND OBJECT_NAME IN ('T_TRMB_CD_INFO01M1'))
|
|
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
|
|
SELECT (SELECT COUNT(*) FROM OPR_MAIN.V_INCIDENT_FTMS) AS V_INCIDENT_FTMS_ROWS FROM DUAL;
|
|
SELECT (SELECT COUNT(*) FROM TROAD_APP.V_ROUTE_NEW_NM) AS V_ROUTE_NEW_NM_ROWS FROM DUAL;
|
|
SELECT (SELECT COUNT(*) FROM TROAD_APP.V_ROUTE_TRFC_VER001) AS V_ROUTE_TRFC_ROWS FROM DUAL;
|
|
SELECT (SELECT COUNT(*) FROM TROAD_APP.V_ACDT_BRNWS_VER001) AS V_ACDT_BRNWS_ROWS FROM DUAL;
|
|
SELECT (SELECT COUNT(*) FROM TLISDEV.T_TRMB_CD_INFO01M1) AS T_TRMB_CD_INFO_ROWS FROM DUAL;
|
|
EXIT
|
|
EOSQL
|
|
"
|
|
|
|
log "완료"
|