51 lines
2.0 KiB
SQL
51 lines
2.0 KiB
SQL
-- MariaDB 초기화 스크립트
|
|
-- 데이터베이스와 사용자 생성
|
|
|
|
-- 기본 데이터베이스 생성 (이미 환경변수로 생성되지만 추가 설정용)
|
|
CREATE DATABASE IF NOT EXISTS wace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 추가 데이터베이스 생성 예시
|
|
CREATE DATABASE IF NOT EXISTS appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
CREATE DATABASE IF NOT EXISTS logdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 사용자 권한 설정
|
|
GRANT ALL PRIVILEGES ON wace.* TO 'wace'@'%';
|
|
GRANT ALL PRIVILEGES ON appdb.* TO 'wace'@'%';
|
|
GRANT SELECT, INSERT, UPDATE ON logdb.* TO 'wace'@'%';
|
|
|
|
-- 권한 적용
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- 샘플 테이블 생성
|
|
USE wace;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS user_profiles (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
first_name VARCHAR(50),
|
|
last_name VARCHAR(50),
|
|
phone VARCHAR(20),
|
|
address TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 샘플 데이터 삽입
|
|
INSERT INTO users (username, email, password_hash) VALUES
|
|
('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'),
|
|
('testuser', 'test@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
|
|
|
|
INSERT INTO user_profiles (user_id, first_name, last_name, phone) VALUES
|
|
(1, '관리자', '시스템', '010-1234-5678'),
|
|
(2, '테스트', '사용자', '010-9876-5432');
|