149 lines
7.0 KiB
JavaScript
149 lines
7.0 KiB
JavaScript
const pool = require("./db/pool.js");
|
|
|
|
async function runPatch() {
|
|
const env = process.env.NODE_ENV;
|
|
const tbProject = env === 'production' ? 'tb_project' : '_test_tb_project';
|
|
const client = await pool.connect();
|
|
try {
|
|
console.log("🚀 Starting Admin Dashboard DB Patch...");
|
|
|
|
// 1. code_master 테이블 생성
|
|
console.log("Creating ver4.code_master table...");
|
|
await client.query(`
|
|
CREATE TABLE IF NOT EXISTS ver4.code_master (
|
|
main_code VARCHAR(30) PRIMARY KEY,
|
|
main_code_nm VARCHAR(100) NOT NULL,
|
|
use_yn CHAR(1) DEFAULT 'Y',
|
|
rmk VARCHAR(255)
|
|
);
|
|
`);
|
|
|
|
// 2. code_detail 테이블 생성
|
|
console.log("Creating ver4.code_detail table...");
|
|
await client.query(`
|
|
CREATE TABLE IF NOT EXISTS ver4.code_detail (
|
|
main_code VARCHAR(30) REFERENCES ver4.code_master(main_code) ON DELETE CASCADE,
|
|
sub_code VARCHAR(30) NOT NULL,
|
|
base_code VARCHAR(61) UNIQUE NOT NULL,
|
|
code_nm VARCHAR(100) NOT NULL,
|
|
sort_ord INT DEFAULT 1,
|
|
use_yn CHAR(1) DEFAULT 'Y',
|
|
rmk VARCHAR(255),
|
|
PRIMARY KEY (main_code, sub_code)
|
|
);
|
|
`);
|
|
|
|
// 3. tb_system_policy 테이블 생성
|
|
console.log("Creating ver4.tb_system_policy table...");
|
|
await client.query(`
|
|
CREATE TABLE IF NOT EXISTS ver4.tb_system_policy (
|
|
policy_id SERIAL PRIMARY KEY,
|
|
policy_key VARCHAR(50) UNIQUE NOT NULL,
|
|
limit_file_count INT DEFAULT 100,
|
|
limit_days INT DEFAULT 30,
|
|
is_active BOOLEAN DEFAULT FALSE,
|
|
upd_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
`);
|
|
|
|
// 4. tb_banner_notice 테이블 생성
|
|
console.log("Creating ver4.tb_banner_notice table...");
|
|
await client.query(`
|
|
CREATE TABLE IF NOT EXISTS ver4.tb_banner_notice (
|
|
banner_id SERIAL PRIMARY KEY,
|
|
project_id VARCHAR(50),
|
|
reg_date DATE DEFAULT CURRENT_DATE,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
notice_text TEXT NOT NULL,
|
|
status_code VARCHAR(61) REFERENCES ver4.code_detail(base_code)
|
|
);
|
|
`);
|
|
|
|
// 외래키 제약조건을 환경(env)에 맞춰 동적으로 설정 (기존 제약조건이 다르게 걸려있으면 재설정)
|
|
await client.query(`
|
|
ALTER TABLE ver4.tb_banner_notice
|
|
DROP CONSTRAINT IF EXISTS tb_banner_notice_project_id_fkey
|
|
`);
|
|
|
|
await client.query(`
|
|
ALTER TABLE ver4.tb_banner_notice
|
|
ADD CONSTRAINT tb_banner_notice_project_id_fkey
|
|
FOREIGN KEY (project_id) REFERENCES ver4.${tbProject}(project_id) ON DELETE CASCADE
|
|
`);
|
|
|
|
// 5. tb_auto_clean_log 테이블 생성
|
|
console.log("Creating ver4.tb_auto_clean_log table...");
|
|
await client.query(`
|
|
CREATE TABLE IF NOT EXISTS ver4.tb_auto_clean_log (
|
|
log_id SERIAL PRIMARY KEY,
|
|
clean_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
project_id VARCHAR(50) DEFAULT 'SYSTEM',
|
|
clean_path TEXT NOT NULL,
|
|
criteria_info VARCHAR(100),
|
|
result_status VARCHAR(20) NOT NULL
|
|
);
|
|
`);
|
|
|
|
console.log("Tables created successfully. Now Seeding data...");
|
|
|
|
// 6. code_master 기초 데이터 Seeding
|
|
const masterSeeds = [
|
|
{ main_code: 'PROJECT_CATEGORY', main_code_nm: '프로젝트 구분', use_yn: 'Y', rmk: '현장 구분 대분류 코드' },
|
|
{ main_code: 'USER_GROUP', main_code_nm: '사용자 권한그룹', use_yn: 'Y', rmk: '어드민 및 유저 권한그룹 대분류' },
|
|
{ main_code: 'NOTICE_STATUS', main_code_nm: '배너 송출상태', use_yn: 'Y', rmk: '실시간 배너 송출 상태 대분류' }
|
|
];
|
|
|
|
for (const seed of masterSeeds) {
|
|
await client.query(`
|
|
INSERT INTO ver4.code_master (main_code, main_code_nm, use_yn, rmk)
|
|
VALUES ($1, $2, $3, $4)
|
|
ON CONFLICT (main_code)
|
|
DO UPDATE SET main_code_nm = EXCLUDED.main_code_nm, use_yn = EXCLUDED.use_yn, rmk = EXCLUDED.rmk;
|
|
`, [seed.main_code, seed.main_code_nm, seed.use_yn, seed.rmk]);
|
|
}
|
|
|
|
// 7. code_detail 기초 데이터 Seeding
|
|
const detailSeeds = [
|
|
// 프로젝트 카테고리
|
|
{ main_code: 'PROJECT_CATEGORY', sub_code: 'tdc', base_code: 'PROJECT_CATEGORY_tdc', code_nm: 'TDC', sort_ord: 1 },
|
|
{ main_code: 'PROJECT_CATEGORY', sub_code: 'gpd', base_code: 'PROJECT_CATEGORY_gpd', code_nm: 'GPD', sort_ord: 2 },
|
|
{ main_code: 'PROJECT_CATEGORY', sub_code: 'bimproject', base_code: 'PROJECT_CATEGORY_bimproject', code_nm: 'BIM프로젝트', sort_ord: 3 },
|
|
{ main_code: 'PROJECT_CATEGORY', sub_code: 'overseas', base_code: 'PROJECT_CATEGORY_overseas', code_nm: '해외현장', sort_ord: 4 },
|
|
// 사용자 그룹
|
|
{ main_code: 'USER_GROUP', sub_code: 'super', base_code: 'USER_GROUP_super', code_nm: '수퍼관리자', sort_ord: 1 },
|
|
{ main_code: 'USER_GROUP', sub_code: 'dev', base_code: 'USER_GROUP_dev', code_nm: '개발자', sort_ord: 2 },
|
|
{ main_code: 'USER_GROUP', sub_code: 'general', base_code: 'USER_GROUP_general', code_nm: '일반사용자', sort_ord: 3 },
|
|
// 배너 상태
|
|
{ main_code: 'NOTICE_STATUS', sub_code: 'active', base_code: 'NOTICE_STATUS_active', code_nm: '송출중', sort_ord: 1 },
|
|
{ main_code: 'NOTICE_STATUS', sub_code: 'scheduled', base_code: 'NOTICE_STATUS_scheduled', code_nm: '예약됨', sort_ord: 2 },
|
|
{ main_code: 'NOTICE_STATUS', sub_code: 'expired', base_code: 'NOTICE_STATUS_expired', code_nm: '만료', sort_ord: 3 }
|
|
];
|
|
|
|
for (const seed of detailSeeds) {
|
|
await client.query(`
|
|
INSERT INTO ver4.code_detail (main_code, sub_code, base_code, code_nm, sort_ord)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
ON CONFLICT (main_code, sub_code)
|
|
DO UPDATE SET base_code = EXCLUDED.base_code, code_nm = EXCLUDED.code_nm, sort_ord = EXCLUDED.sort_ord;
|
|
`, [seed.main_code, seed.sub_code, seed.base_code, seed.code_nm, seed.sort_ord]);
|
|
}
|
|
|
|
// 8. tb_system_policy 글로벌 공통 자동 삭제 정책 1건 Seeding
|
|
await client.query(`
|
|
INSERT INTO ver4.tb_system_policy (policy_key, limit_file_count, limit_days, is_active)
|
|
VALUES ('GLOBAL_DELETE_POLICY', 100, 30, FALSE)
|
|
ON CONFLICT (policy_key) DO NOTHING;
|
|
`);
|
|
|
|
console.log("🎉 Seeding completed successfully!");
|
|
} catch (err) {
|
|
console.error("❌ DB Patch Error:", err);
|
|
} finally {
|
|
client.release();
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
runPatch();
|