79 lines
2.1 KiB
Transact-SQL
79 lines
2.1 KiB
Transact-SQL
-- MSSQL 초기화 스크립트
|
|
-- 데이터베이스와 사용자 생성
|
|
|
|
USE master;
|
|
GO
|
|
|
|
-- wace 데이터베이스 생성
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'wace')
|
|
BEGIN
|
|
CREATE DATABASE wace
|
|
COLLATE Korean_Wansung_CI_AS;
|
|
END
|
|
GO
|
|
|
|
-- wace 사용자 생성 및 권한 부여
|
|
USE wace;
|
|
GO
|
|
|
|
-- 로그인 생성
|
|
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'wace')
|
|
BEGIN
|
|
CREATE LOGIN wace WITH PASSWORD = 'wace0909!!';
|
|
END
|
|
GO
|
|
|
|
-- 사용자 생성
|
|
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'wace')
|
|
BEGIN
|
|
CREATE USER wace FOR LOGIN wace;
|
|
ALTER ROLE db_owner ADD MEMBER wace;
|
|
END
|
|
GO
|
|
|
|
-- 샘플 테이블 생성
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
|
|
BEGIN
|
|
CREATE TABLE users (
|
|
id INT IDENTITY(1,1) PRIMARY KEY,
|
|
username NVARCHAR(50) NOT NULL UNIQUE,
|
|
email NVARCHAR(100) NOT NULL UNIQUE,
|
|
password_hash NVARCHAR(255) NOT NULL,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'user_profiles')
|
|
BEGIN
|
|
CREATE TABLE user_profiles (
|
|
id INT IDENTITY(1,1) PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
first_name NVARCHAR(50),
|
|
last_name NVARCHAR(50),
|
|
phone NVARCHAR(20),
|
|
address NVARCHAR(MAX),
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE(),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
END
|
|
GO
|
|
|
|
-- 샘플 데이터 삽입
|
|
IF NOT EXISTS (SELECT * FROM users WHERE username = 'admin')
|
|
BEGIN
|
|
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, N'관리자', N'시스템', '010-1234-5678'),
|
|
(2, N'테스트', N'사용자', '010-9876-5432');
|
|
END
|
|
GO
|
|
|
|
PRINT 'MSSQL wace 데이터베이스 초기화 완료';
|
|
GO
|