KoreanFoodie's Study
[C++ 게임 서버] 7-2. DB Bind 본문
[C++ 게임 서버] 7-2. DB Bind
핵심 :
1. TMP 를 활용하여 BindParam/BindCol 을 간략화해 보자.
이전에 DBConnection 을 만들면서, BindParam 을 이용해 데이터를 저장하고, BindCol 을 이용해 DB 에서 값을 조회했다.
그런데 사실 이런 함수들은 타입별로 만들어 줘야 한다. 그래서 DBConnection 에 다음 함수들을 추가하고, 기존 BindParam/BindCol 을 private 으로 빼 줬다.
public:
bool BindParam(int32 paramIndex, bool* value, SQLLEN* index);
bool BindParam(int32 paramIndex, float* value, SQLLEN* index);
bool BindParam(int32 paramIndex, double* value, SQLLEN* index);
bool BindParam(int32 paramIndex, int8* value, SQLLEN* index);
bool BindParam(int32 paramIndex, int16* value, SQLLEN* index);
bool BindParam(int32 paramIndex, int32* value, SQLLEN* index);
bool BindParam(int32 paramIndex, int64* value, SQLLEN* index);
bool BindParam(int32 paramIndex, TIMESTAMP_STRUCT* value, SQLLEN* index);
bool BindParam(int32 paramIndex, const WCHAR* str, SQLLEN* index);
bool BindParam(int32 paramIndex, const BYTE* bin, int32 size, SQLLEN* index);
bool BindCol(int32 columnIndex, bool* value, SQLLEN* index);
bool BindCol(int32 columnIndex, float* value, SQLLEN* index);
bool BindCol(int32 columnIndex, double* value, SQLLEN* index);
bool BindCol(int32 columnIndex, int8* value, SQLLEN* index);
bool BindCol(int32 columnIndex, int16* value, SQLLEN* index);
bool BindCol(int32 columnIndex, int32* value, SQLLEN* index);
bool BindCol(int32 columnIndex, int64* value, SQLLEN* index);
bool BindCol(int32 columnIndex, TIMESTAMP_STRUCT* value, SQLLEN* index);
bool BindCol(int32 columnIndex, WCHAR* str, int32 size, SQLLEN* index);
bool BindCol(int32 columnIndex, BYTE* bin, int32 size, SQLLEN* index);
private:
bool BindParam(SQLUSMALLINT paramIndex, SQLSMALLINT cType, SQLSMALLINT sqlType, SQLULEN len, SQLPOINTER ptr, SQLLEN* index);
bool BindCol(SQLUSMALLINT columnIndex, SQLSMALLINT cType, SQLULEN len, SQLPOINTER value, SQLLEN* index);
각 함수들에 대한 구현은... 사실 Casting 만 잘 해주면 된다.
bool DBConnection::BindParam(int32 paramIndex, bool* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_TINYINT, SQL_TINYINT, size32(bool), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, float* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_FLOAT, SQL_REAL, 0, value, index);
}
bool DBConnection::BindParam(int32 paramIndex, double* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_DOUBLE, SQL_DOUBLE, 0, value, index);
}
bool DBConnection::BindParam(int32 paramIndex, int8* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_TINYINT, SQL_TINYINT, size32(int8), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, int16* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_SHORT, SQL_SMALLINT, size32(int16), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, int32* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_LONG, SQL_INTEGER, size32(int32), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, int64* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_SBIGINT, SQL_BIGINT, size32(int64), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, TIMESTAMP_STRUCT* value, SQLLEN* index)
{
return BindParam(paramIndex, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, size32(TIMESTAMP_STRUCT), value, index);
}
bool DBConnection::BindParam(int32 paramIndex, const WCHAR* str, SQLLEN* index)
{
SQLULEN size = static_cast<SQLULEN>((::wcslen(str) + 1) * 2);
*index = SQL_NTSL;
if (size > WVARCHAR_MAX)
return BindParam(paramIndex, SQL_C_WCHAR, SQL_WLONGVARCHAR, size, (SQLPOINTER)str, index);
else
return BindParam(paramIndex, SQL_C_WCHAR, SQL_WVARCHAR, size, (SQLPOINTER)str, index);
}
bool DBConnection::BindParam(int32 paramIndex, const BYTE* bin, int32 size, SQLLEN* index)
{
if (bin == nullptr)
{
*index = SQL_NULL_DATA;
size = 1;
}
else
*index = size;
if (size > BINARY_MAX)
return BindParam(paramIndex, SQL_C_BINARY, SQL_LONGVARBINARY, size, (BYTE*)bin, index);
else
return BindParam(paramIndex, SQL_C_BINARY, SQL_BINARY, size, (BYTE*)bin, index);
}
bool DBConnection::BindCol(int32 columnIndex, bool* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_TINYINT, size32(bool), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, float* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_FLOAT, size32(float), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, double* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_DOUBLE, size32(double), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, int8* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_TINYINT, size32(int8), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, int16* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_SHORT, size32(int16), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, int32* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_LONG, size32(int32), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, int64* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_SBIGINT, size32(int64), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, TIMESTAMP_STRUCT* value, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_TYPE_TIMESTAMP, size32(TIMESTAMP_STRUCT), value, index);
}
bool DBConnection::BindCol(int32 columnIndex, WCHAR* str, int32 size, SQLLEN* index)
{
return BindCol(columnIndex, SQL_C_WCHAR, size, str, index);
}
bool DBConnection::BindCol(int32 columnIndex, BYTE* bin, int32 size, SQLLEN* index)
{
return BindCol(columnIndex, SQL_BINARY, size, bin, index);
}
bool DBConnection::BindParam(SQLUSMALLINT paramIndex, SQLSMALLINT cType, SQLSMALLINT sqlType, SQLULEN len, SQLPOINTER ptr, SQLLEN* index)
{
SQLRETURN ret = ::SQLBindParameter(_statement, paramIndex, SQL_PARAM_INPUT, cType, sqlType, len, 0, ptr, 0, index);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
HandleError(ret);
return false;
}
return true;
}
bool DBConnection::BindCol(SQLUSMALLINT columnIndex, SQLSMALLINT cType, SQLULEN len, SQLPOINTER value, SQLLEN* index)
{
SQLRETURN ret = ::SQLBindCol(_statement, columnIndex, cType, value, len, index);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
HandleError(ret);
return false;
}
return true;
}
아마 정신이 혼미해지면서 스크롤을 바로 내렸을 것이다. 🤣 (필자도 내림)
이제 GameServer 에서는 다음과 같이 쿼리를 쏴주면 되는데...
dbConn->Unbind();
// 넘길 인자 바인딩
int32 gold = 100;
SQLLEN len = 0;
WCHAR name[100] = L"루키스";
SQLLEN nameLen = 0;
TIMESTAMP_STRUCT ts = {};
ts.year = 2021;
ts.month = 6;
ts.day = 5;
SQLLEN tsLen = 0;
// 넘길 인자 바인딩
ASSERT_CRASH(dbConn->BindParam(1, &gold, &len));
ASSERT_CRASH(dbConn->BindParam(2, name, &nameLen));
ASSERT_CRASH(dbConn->BindParam(3, &ts, &tsLen));
// SQL 실행
ASSERT_CRASH(dbConn->Execute(L"INSERT INTO [dbo].[Gold]([gold], [name], [createDate]) VALUES(?, ?, ?)"));
//////////////////////////////////////////////////////////////////
dbConn->Unbind();
int32 gold = 100;
SQLLEN len = 0;
// 넘길 인자 바인딩
ASSERT_CRASH(dbConn->BindParam(1, &gold, &len));
int32 outId = 0;
SQLLEN outIdLen = 0;
ASSERT_CRASH(dbConn->BindCol(1, &outId, &outIdLen));
int32 outGold = 0;
SQLLEN outGoldLen = 0;
ASSERT_CRASH(dbConn->BindCol(2, &outGold, &outGoldLen));
WCHAR outName[100];
SQLLEN outNameLen = 0;
ASSERT_CRASH(dbConn->BindCol(3, outName, len32(outName), &outNameLen));
TIMESTAMP_STRUCT outDate = {};
SQLLEN outDateLen = 0;
ASSERT_CRASH(dbConn->BindCol(4, &outDate, &outDateLen));
// SQL 실행
ASSERT_CRASH(dbConn->Execute(L"SELECT id, gold, name, createDate FROM [dbo].[Gold] WHERE gold = (?)"));아, 뭔가 딱봐도 쿼리문이 많아질 수록 점점 코드가 더러워지는 느낌이다.
이런 문제를 해결하기 위해, DBBind 라는, 템플릿을 활용한 클래스를 만들어 BindParam/BindCol 을 하는 부분을 조금 간소화해 보자.
DBBind.h
template<int32 C>
struct FullBits { enum { value = (1 << (C - 1)) | FullBits<C-1>::value }; };
template<>
struct FullBits<1> { enum { value = 1 }; };
template<>
struct FullBits<0> { enum { value = 0 }; };
template<int32 ParamCount, int32 ColumnCount>
class DBBind
{
public:
DBBind(DBConnection& dbConnection, const WCHAR* query)
: _dbConnection(dbConnection), _query(query)
{
::memset(_paramIndex, 0, sizeof(_paramIndex));
::memset(_columnIndex, 0, sizeof(_columnIndex));
_paramFlag = 0;
_columnFlag = 0;
dbConnection.Unbind();
}
bool Validate()
{
return _paramFlag == FullBits<ParamCount>::value && _columnFlag == FullBits<ColumnCount>::value;
}
bool Execute()
{
ASSERT_CRASH(Validate());
return _dbConnection.Execute(_query);
}
bool Fetch()
{
return _dbConnection.Fetch();
}
public:
template<typename T>
void BindParam(int32 idx, T& value)
{
_dbConnection.BindParam(idx + 1, &value, &_paramIndex[idx]);
_paramFlag |= (1LL << idx);
}
void BindParam(int32 idx, const WCHAR* value)
{
_dbConnection.BindParam(idx + 1, value, &_paramIndex[idx]);
_paramFlag |= (1LL << idx);
}
template<typename T, int32 N>
void BindParam(int32 idx, T(&value)[N])
{
_dbConnection.BindParam(idx + 1, (const BYTE*)value, size32(T) * N, &_paramIndex[idx]);
_paramFlag |= (1LL << idx);
}
template<typename T>
void BindParam(int32 idx, T* value, int32 N)
{
_dbConnection.BindParam(idx + 1, (const BYTE*)value, size32(T) * N, &_paramIndex[idx]);
_paramFlag |= (1LL << idx);
}
template<typename T>
void BindCol(int32 idx, T& value)
{
_dbConnection.BindCol(idx + 1, &value, &_columnIndex[idx]);
_columnFlag |= (1LL << idx);
}
template<int32 N>
void BindCol(int32 idx, WCHAR(&value)[N])
{
_dbConnection.BindCol(idx + 1, value, N - 1, &_columnIndex[idx]);
_columnFlag |= (1LL << idx);
}
void BindCol(int32 idx, WCHAR* value, int32 len)
{
_dbConnection.BindCol(idx + 1, value, len - 1, &_columnIndex[idx]);
_columnFlag |= (1LL << idx);
}
template<typename T, int32 N>
void BindCol(int32 idx, T(&value)[N])
{
_dbConnection.BindCol(idx + 1, value, size32(T) * N, &_columnIndex[idx]);
_columnFlag |= (1LL << idx);
}
protected:
DBConnection& _dbConnection;
const WCHAR* _query;
SQLLEN _paramIndex[ParamCount > 0 ? ParamCount : 1];
SQLLEN _columnIndex[ParamCount > 0 ? ParamCount : 1];
uint64 _paramFlag;
uint64 _columnFlag;
};먼저, FullBits 라는 녀석은 TMP 를 활용해 Bit 를 채워주는 녀석이다. 예를 들어, FullBits<3> 은 재귀적으로 호출이 되어 제일 오른쪽 3 개의 비트가 1로 채워져 있는 value 를 갖게 될 것이다.
우리는 이런 FullBits 를 _paramFlag 로 사용하여 검증에 활용할 것이다.
DBBind 클래스의 인자를 잘 보면, ParamCount 와 ColumnCount 가 들어가 있다. ParamCount 는 인풋의 갯수, ColumnCount 는 아웃풋의 갯수라고 생각하면 된다.
BindParam 함수를 잘 보면, 인풋 타입 T(ParamCount 의 타입)을 받아 BindParam 을 해 주고, BindCol 은 아웃풋 타입 N(ColumnCount 의 타입)을 이용하는 것을 확인할 수 있다.
이 DBBind 클래스를 활용하면, 위의 쿼리문이 아래처럼 간단해지게 된다 :
DBConnection* dbConn = GDBConnectionPool->Pop();
DBBind<3, 0> dbBind(*dbConn, L"INSERT INTO [dbo].[Gold]([gold], [name], [createDate]) VALUES(?, ?, ?)");
int32 gold = 100;
dbBind.BindParam(0, gold);
WCHAR name[100] = L"루키스";
dbBind.BindParam(1, name);
TIMESTAMP_STRUCT ts = {2021, 6, 5};
dbBind.BindParam(2, ts);
ASSERT_CRASH(dbBind.Execute());
/////////////////////////////////////////////////////////////////////////////
DBConnection* dbConn = GDBConnectionPool->Pop();
DBBind<1, 4> dbBind(*dbConn, L"SELECT id, gold, name, createDate FROM [dbo].[Gold] WHERE gold = (?)");
int32 gold = 100;
dbBind.BindParam(0, gold);
int32 outId = 0;
int32 outGold = 0;
WCHAR outName[100];
TIMESTAMP_STRUCT outDate = {};
dbBind.BindCol(0, OUT outId);
dbBind.BindCol(1, OUT outGold);
dbBind.BindCol(2, OUT outName);
dbBind.BindCol(3, OUT outDate);
ASSERT_CRASH(dbBind.Execute());'Game Dev > Game Server' 카테고리의 다른 글
| [C++ 게임 서버] 7-4. ORM (0) | 2023.12.22 |
|---|---|
| [C++ 게임 서버] 7-3. XML Parser (0) | 2023.12.21 |
| [C++ 게임 서버] 7-1. DB Connection (0) | 2023.12.21 |
| [C++ 게임 서버] 6-7. JobTimer (0) | 2023.12.21 |
| [C++ 게임 서버] 6-6. JobQueue #5 (0) | 2023.12.20 |
'Game Dev/Game Server' Related Articles
more
Comments