KoreanFoodie's Study
[C++ 게임 서버] 7-4. ORM 본문
[C++ 게임 서버] 7-4. ORM
핵심 :
1. ORM 을 사용하면, DB 를 간편하게 관리할 수 있다.
이번 글에서는 간단한 ORM 을 만들어 보도록 하겠다. 참고로 ORM 이란...
라고 한다. 자세한 설명은 이 블로그를 참고하자.
음.. 일단 DB 모델을 표현하는 DBModel 과, 현재 DBModel 에 맞게 Table 을 구성해 주는 DBSynchronizer 클래스를 만들 것이다.
그런데... 이게 하나하나 설명하기에는 양이 많고, 굳이 암기할 필요는 없기에... 나중에 필요할 때 슬쩍(?) 들춰보는 것으로 하고, 예제 코드만 기록하고 넘어가도록 하겠다. 절대 귀찮아서 때우려고 하는게 아니다. 😅
더보기
DBModel
#pragma once
#include "Types.h"
NAMESPACE_BEGIN(DBModel)
USING_SHARED_PTR(Column);
USING_SHARED_PTR(Index);
USING_SHARED_PTR(Table);
USING_SHARED_PTR(Procedure);
/*-------------
DataType
--------------*/
enum class DataType
{
None = 0,
TinyInt = 48,
SmallInt = 52,
Int = 56,
Real = 59,
DateTime = 61,
Float = 62,
Bit = 104,
Numeric = 108,
BigInt = 127,
VarBinary = 165,
Varchar = 167,
Binary = 173,
NVarChar = 231,
};
/*-------------
Column
--------------*/
class Column
{
public:
String CreateText();
public:
String _name;
int32 _columnId = 0; // DB
DataType _type = DataType::None;
String _typeText;
int32 _maxLength = 0;
bool _nullable = false;
bool _identity = false;
int64 _seedValue = 0;
int64 _incrementValue = 0;
String _default;
String _defaultConstraintName; // DB
};
/*-----------
Index
------------*/
enum class IndexType
{
Clustered = 1,
NonClustered = 2
};
class Index
{
public:
String GetUniqueName();
String CreateName(const String& tableName);
String GetTypeText();
String GetKeyText();
String CreateColumnsText();
bool DependsOn(const String& columnName);
public:
String _name; // DB
int32 _indexId = 0; // DB
IndexType _type = IndexType::NonClustered;
bool _primaryKey = false;
bool _uniqueConstraint = false;
Vector<ColumnRef> _columns;
};
/*-----------
Table
------------*/
class Table
{
public:
ColumnRef FindColumn(const String& columnName);
public:
int32 _objectId = 0; // DB
String _name;
Vector<ColumnRef> _columns;
Vector<IndexRef> _indexes;
};
/*----------------
Procedures
-----------------*/
struct Param
{
String _name;
String _type;
};
class Procedure
{
public:
String GenerateCreateQuery();
String GenerateAlterQuery();
String GenerateParamString();
public:
String _name;
String _fullBody; // DB
String _body; // XML
Vector<Param> _parameters; // XML
};
/*-------------
Helpers
--------------*/
class Helpers
{
public:
static String Format(const WCHAR* format, ...);
static String DataType2String(DataType type);
static String RemoveWhiteSpace(const String& str);
static DataType String2DataType(const WCHAR* str, OUT int32& maxLen);
};
NAMESPACE_END
//////////////////////////////////////////////////////////////////////////////////////
#include "pch.h"
#include "DBModel.h"
using namespace DBModel;
#include <regex>
/*-------------
Column
--------------*/
String Column::CreateText()
{
return DBModel::Helpers::Format(
L"[%s] %s %s %s",
_name.c_str(),
_typeText.c_str(),
_nullable ? L"NULL" : L"NOT NULL",
_identity ? DBModel::Helpers::Format(L"IDENTITY(%d, %d)", _seedValue, _incrementValue).c_str() : L"");
}
/*-----------
Index
------------*/
String Index::GetUniqueName()
{
String ret;
ret += _primaryKey ? L"PK " : L" ";
ret += _uniqueConstraint ? L"UK " : L" ";
ret += (_type == IndexType::Clustered ? L"C " : L"NC ");
for (const ColumnRef& column : _columns)
{
ret += L"*";
ret += column->_name;
ret += L" ";
}
return ret;
}
String Index::CreateName(const String& tableName)
{
String ret = L"IX_" + tableName;
for (const ColumnRef& column : _columns)
{
ret += L"_";
ret += column->_name;
}
return ret;
}
String Index::GetTypeText()
{
return (_type == IndexType::Clustered ? L"CLUSTERED" : L"NONCLUSTERED");
}
String Index::GetKeyText()
{
if (_primaryKey)
return L"PRIMARY KEY";
if (_uniqueConstraint)
return L"UNIQUE";
return L"";
}
String Index::CreateColumnsText()
{
String ret;
const int32 size = static_cast<int32>(_columns.size());
for (int32 i = 0; i < size; i++)
{
if (i > 0)
ret += L", ";
ret += DBModel::Helpers::Format(L"[%s]", _columns[i]->_name.c_str());
}
return ret;
}
bool Index::DependsOn(const String& columnName)
{
auto findIt = std::find_if(_columns.begin(), _columns.end(),
[&](const ColumnRef& column) { return column->_name == columnName; });
return findIt != _columns.end();
}
/*-----------
Table
------------*/
ColumnRef Table::FindColumn(const String& columnName)
{
auto findIt = std::find_if(_columns.begin(), _columns.end(),
[&](const ColumnRef& column) { return column->_name == columnName; });
if (findIt != _columns.end())
return *findIt;
return nullptr;
}
/*----------------
Procedures
-----------------*/
String Procedure::GenerateCreateQuery()
{
const WCHAR* query = L"CREATE PROCEDURE [dbo].[%s] %s AS BEGIN %s END";
String paramString = GenerateParamString();
return DBModel::Helpers::Format(query, _name.c_str(), paramString.c_str(), _body.c_str());
}
String Procedure::GenerateAlterQuery()
{
const WCHAR* query = L"ALTER PROCEDURE [dbo].[%s] %s AS BEGIN %s END";
String paramString = GenerateParamString();
return DBModel::Helpers::Format(query, _name.c_str(), paramString.c_str(), _body.c_str());
}
String Procedure::GenerateParamString()
{
String str;
const int32 size = static_cast<int32>(_parameters.size());
for (int32 i = 0; i < size; i++)
{
if (i < size - 1)
str += DBModel::Helpers::Format(L"\t%s %s,\n", _parameters[i]._name.c_str(), _parameters[i]._type.c_str());
else
str += DBModel::Helpers::Format(L"\t%s %s", _parameters[i]._name.c_str(), _parameters[i]._type.c_str());
}
return str;
}
/*-------------
Helpers
--------------*/
String Helpers::Format(const WCHAR* format, ...)
{
WCHAR buf[4096];
va_list ap;
va_start(ap, format);
::vswprintf_s(buf, 4096, format, ap);
va_end(ap);
return String(buf);
}
String Helpers::DataType2String(DataType type)
{
switch (type)
{
case DataType::TinyInt: return L"TinyInt";
case DataType::SmallInt: return L"SmallInt";
case DataType::Int: return L"Int";
case DataType::Real: return L"Real";
case DataType::DateTime: return L"DateTime";
case DataType::Float: return L"Float";
case DataType::Bit: return L"Bit";
case DataType::Numeric: return L"Numeric";
case DataType::BigInt: return L"BigInt";
case DataType::VarBinary: return L"VarBinary";
case DataType::Varchar: return L"Varchar";
case DataType::Binary: return L"Binary";
case DataType::NVarChar: return L"NVarChar";
default: return L"None";
}
}
String Helpers::RemoveWhiteSpace(const String& str)
{
String ret = str;
ret.erase(
std::remove_if(ret.begin(), ret.end(), [=](WCHAR ch) { return ::isspace(ch); }),
ret.end());
return ret;
}
DataType Helpers::String2DataType(const WCHAR* str, OUT int32& maxLen)
{
std::wregex reg(L"([a-z]+)(\\((max|\\d+)\\))?");
std::wcmatch ret;
if (std::regex_match(str, OUT ret, reg) == false)
return DataType::None;
if (ret[3].matched)
maxLen = ::_wcsicmp(ret[3].str().c_str(), L"max") == 0 ? -1 : _wtoi(ret[3].str().c_str());
else
maxLen = 0;
if (::_wcsicmp(ret[1].str().c_str(), L"TinyInt") == 0) return DataType::TinyInt;
if (::_wcsicmp(ret[1].str().c_str(), L"SmallInt") == 0) return DataType::SmallInt;
if (::_wcsicmp(ret[1].str().c_str(), L"Int") == 0) return DataType::Int;
if (::_wcsicmp(ret[1].str().c_str(), L"Real") == 0) return DataType::Real;
if (::_wcsicmp(ret[1].str().c_str(), L"DateTime") == 0) return DataType::DateTime;
if (::_wcsicmp(ret[1].str().c_str(), L"Float") == 0) return DataType::Float;
if (::_wcsicmp(ret[1].str().c_str(), L"Bit") == 0) return DataType::Bit;
if (::_wcsicmp(ret[1].str().c_str(), L"Numeric") == 0) return DataType::Numeric;
if (::_wcsicmp(ret[1].str().c_str(), L"BigInt") == 0) return DataType::BigInt;
if (::_wcsicmp(ret[1].str().c_str(), L"VarBinary") == 0) return DataType::VarBinary;
if (::_wcsicmp(ret[1].str().c_str(), L"Varchar") == 0) return DataType::Varchar;
if (::_wcsicmp(ret[1].str().c_str(), L"Binary") == 0) return DataType::Binary;
if (::_wcsicmp(ret[1].str().c_str(), L"NVarChar") == 0) return DataType::NVarChar;
return DataType::None;
}
DBSynchronizer
#pragma once
#include "DBConnection.h"
#include "DBModel.h"
/*--------------------
DBSynchronizer
---------------------*/
class DBSynchronizer
{
enum
{
PROCEDURE_MAX_LEN = 10000
};
enum UpdateStep : uint8
{
DropIndex,
AlterColumn,
AddColumn,
CreateTable,
DefaultConstraint,
CreateIndex,
DropColumn,
DropTable,
StoredProcecure,
Max
};
enum ColumnFlag : uint8
{
Type = 1 << 0,
Nullable = 1 << 1,
Identity = 1 << 2,
Default = 1 << 3,
Length = 1 << 4,
};
public:
DBSynchronizer(DBConnection& conn) : _dbConn(conn) { }
~DBSynchronizer();
bool Synchronize(const WCHAR* path);
private:
void ParseXmlDB(const WCHAR* path);
bool GatherDBTables();
bool GatherDBIndexes();
bool GatherDBStoredProcedures();
void CompareDBModel();
void CompareTables(DBModel::TableRef dbTable, DBModel::TableRef xmlTable);
void CompareColumns(DBModel::TableRef dbTable, DBModel::ColumnRef dbColumn, DBModel::ColumnRef xmlColumn);
void CompareStoredProcedures();
void ExecuteUpdateQueries();
private:
DBConnection& _dbConn;
Vector<DBModel::TableRef> _xmlTables;
Vector<DBModel::ProcedureRef> _xmlProcedures;
Set<String> _xmlRemovedTables;
Vector<DBModel::TableRef> _dbTables;
Vector<DBModel::ProcedureRef> _dbProcedures;
private:
Set<String> _dependentIndexes;
Vector<String> _updateQueries[UpdateStep::Max];
};
/////////////////////////////////////////////////////////////////////////////////////
#include "pch.h"
#include "DBSynchronizer.h"
#include "DBBind.h"
#include "CoreGlobal.h"
#include "XmlParser.h"
#include <regex>
/*---------------------
StoredProcedures
----------------------*/
namespace SP
{
const WCHAR* QTablesAndColumns =
L" SELECT c.object_id, t.name AS tableName, c.name AS columnName, c.column_id, c.user_type_id, c.max_length,"
" c.is_nullable, c.is_identity, CAST(ic.seed_value AS BIGINT) AS seedValue, CAST(ic.increment_value AS BIGINT) AS incValue,"
" c.default_object_id, dc.definition as defaultDefinition, dc.name as defaultConstraintName"
" FROM sys.columns AS c"
" JOIN sys.tables AS t"
" ON c.object_id = t.object_id"
" LEFT JOIN sys.default_constraints AS dc"
" ON c.default_object_id = dc.object_id"
" LEFT JOIN sys.identity_columns AS ic"
" ON c.object_id = ic.object_id AND c.column_id = ic.column_id"
" WHERE t.type = 'U'"
" ORDER BY object_id ASC, column_id ASC;";
class GetDBTables : public DBBind<0, 13>
{
public:
GetDBTables(DBConnection& conn) : DBBind(conn, QTablesAndColumns) {}
void Out_ObjectId(OUT int32& value) { BindCol(0, value); }
template<int32 N> void Out_TableName(OUT WCHAR(&value)[N]) { BindCol(1, value); }
template<int32 N> void Out_ColumnName(OUT WCHAR(&value)[N]) { BindCol(2, value); }
void Out_ColumnId(OUT int32& value) { BindCol(3, value); }
void Out_UserType(OUT int32& value) { BindCol(4, value); }
void Out_MaxLength(OUT int32& value) { BindCol(5, value); }
void Out_IsNullable(OUT bool& value) { BindCol(6, value); }
void Out_IsIdentity(OUT bool& value) { BindCol(7, value); }
void Out_SeedValue(OUT int64& value) { BindCol(8, value); }
void Out_IncrementValue(OUT int64& value) { BindCol(9, value); }
void Out_DefaultObjectId(OUT int32& value) { BindCol(10, value); }
template<int32 N> void Out_DefaultDefinition(OUT WCHAR(&value)[N]) { BindCol(11, value); }
template<int32 N> void Out_DefaultConstraintName(OUT WCHAR(&value)[N]) { BindCol(12, value); }
};
const WCHAR* QIndexes =
L" SELECT i.object_id, i.name as indexName, i.index_id, i.type, i.is_primary_key,"
" i.is_unique_constraint, ic.column_id, COL_NAME(ic.object_id, ic.column_id) as columnName"
" FROM sys.indexes AS i"
" JOIN sys.index_columns AS ic"
" ON i.object_id = ic.object_id AND i.index_id = ic.index_id"
" WHERE i.type > 0 AND i.object_id IN(SELECT object_id FROM sys.tables WHERE type = 'U')"
" ORDER BY i.object_id ASC, i.index_id ASC;";
class GetDBIndexes : public DBBind<0, 8>
{
public:
GetDBIndexes(DBConnection& conn) : DBBind(conn, QIndexes) {}
void Out_ObjectId(OUT int32& value) { BindCol(0, value); }
template<int32 N> void Out_IndexName(OUT WCHAR(&value)[N]) { BindCol(1, value); }
void Out_IndexId(OUT int32& value) { BindCol(2, value); }
void Out_IndexType(OUT int32& value) { BindCol(3, value); }
void Out_IsPrimaryKey(OUT bool& value) { BindCol(4, value); }
void Out_IsUniqueConstraint(OUT bool& value) { BindCol(5, value); }
void Out_ColumnId(OUT int32& value) { BindCol(6, value); }
template<int32 N> void Out_ColumnName(OUT WCHAR(&value)[N]) { BindCol(7, value); }
};
const WCHAR* QStoredProcedures =
L" SELECT name, OBJECT_DEFINITION(object_id) AS body FROM sys.procedures;";
class GetDBStoredProcedures : public DBBind<0, 2>
{
public:
GetDBStoredProcedures(DBConnection& conn) : DBBind(conn, QStoredProcedures) {}
template<int32 N> void Out_Name(OUT WCHAR(&value)[N]) { BindCol(0, value); }
void Out_Body(OUT WCHAR* value, int32 len) { BindCol(1, value, len); }
};
}
/*--------------------
DBSynchronizer
---------------------*/
DBSynchronizer::~DBSynchronizer()
{
}
bool DBSynchronizer::Synchronize(const WCHAR* path)
{
ParseXmlDB(path);
GatherDBTables();
GatherDBIndexes();
GatherDBStoredProcedures();
CompareDBModel();
ExecuteUpdateQueries();
return true;
}
void DBSynchronizer::ParseXmlDB(const WCHAR* path)
{
XmlNode root;
XmlParser parser;
ASSERT_CRASH(parser.ParseFromFile(path, OUT root));
Vector<XmlNode> tables = root.FindChildren(L"Table");
for (XmlNode& table : tables)
{
DBModel::TableRef t = MakeShared<DBModel::Table>();
t->_name = table.GetStringAttr(L"name");
Vector<XmlNode> columns = table.FindChildren(L"Column");
for (XmlNode& column : columns)
{
DBModel::ColumnRef c = MakeShared<DBModel::Column>();
c->_name = column.GetStringAttr(L"name");
c->_typeText = column.GetStringAttr(L"type");
c->_type = DBModel::Helpers::String2DataType(c->_typeText.c_str(), OUT c->_maxLength);
ASSERT_CRASH(c->_type != DBModel::DataType::None);
c->_nullable = !column.GetBoolAttr(L"notnull", false);
const WCHAR* identityStr = column.GetStringAttr(L"identity");
if (::wcslen(identityStr) > 0)
{
std::wregex pt(L"(\\d+),(\\d+)");
std::wcmatch match;
ASSERT_CRASH(std::regex_match(identityStr, OUT match, pt));
c->_identity = true;
c->_seedValue = _wtoi(match[1].str().c_str());
c->_incrementValue = _wtoi(match[2].str().c_str());
}
c->_default = column.GetStringAttr(L"default");
t->_columns.push_back(c);
}
Vector<XmlNode> indexes = table.FindChildren(L"Index");
for (XmlNode& index : indexes)
{
DBModel::IndexRef i = MakeShared<DBModel::Index>();
const WCHAR* typeStr = index.GetStringAttr(L"type");
if (::_wcsicmp(typeStr, L"clustered") == 0)
i->_type = DBModel::IndexType::Clustered;
else if (::_wcsicmp(typeStr, L"nonclustered") == 0)
i->_type = DBModel::IndexType::NonClustered;
else
CRASH("Invalid Index Type");
i->_primaryKey = index.FindChild(L"PrimaryKey").IsValid();
i->_uniqueConstraint = index.FindChild(L"UniqueKey").IsValid();
Vector<XmlNode> columns = index.FindChildren(L"Column");
for (XmlNode& column : columns)
{
const WCHAR* nameStr = column.GetStringAttr(L"name");
DBModel::ColumnRef c = t->FindColumn(nameStr);
ASSERT_CRASH(c != nullptr);
i->_columns.push_back(c);
}
t->_indexes.push_back(i);
}
_xmlTables.push_back(t);
}
Vector<XmlNode> procedures = root.FindChildren(L"Procedure");
for (XmlNode& procedure : procedures)
{
DBModel::ProcedureRef p = MakeShared<DBModel::Procedure>();
p->_name = procedure.GetStringAttr(L"name");
p->_body = procedure.FindChild(L"Body").GetStringValue();
Vector<XmlNode> params = procedure.FindChildren(L"Param");
for (XmlNode& paramNode : params)
{
DBModel::Param param;
param._name = paramNode.GetStringAttr(L"name");
param._type = paramNode.GetStringAttr(L"type");
p->_parameters.push_back(param);
}
_xmlProcedures.push_back(p);
}
Vector<XmlNode> removedTables = root.FindChildren(L"RemovedTable");
for (XmlNode& removedTable : removedTables)
{
_xmlRemovedTables.insert(removedTable.GetStringAttr(L"name"));
}
}
bool DBSynchronizer::GatherDBTables()
{
int32 objectId;
WCHAR tableName[101] = { 0 };
WCHAR columnName[101] = { 0 };
int32 columnId;
int32 userTypeId;
int32 maxLength;
bool isNullable;
bool isIdentity;
int64 seedValue;
int64 incValue;
int32 defaultObjectId;
WCHAR defaultDefinition[101] = { 0 };
WCHAR defaultConstraintName[101] = { 0 };
SP::GetDBTables getDBTables(_dbConn);
getDBTables.Out_ObjectId(OUT objectId);
getDBTables.Out_TableName(OUT tableName);
getDBTables.Out_ColumnName(OUT columnName);
getDBTables.Out_ColumnId(OUT columnId);
getDBTables.Out_UserType(OUT userTypeId);
getDBTables.Out_MaxLength(OUT maxLength);
getDBTables.Out_IsNullable(OUT isNullable);
getDBTables.Out_IsIdentity(OUT isIdentity);
getDBTables.Out_SeedValue(OUT seedValue);
getDBTables.Out_IncrementValue(OUT incValue);
getDBTables.Out_DefaultObjectId(OUT defaultObjectId);
getDBTables.Out_DefaultDefinition(OUT defaultDefinition);
getDBTables.Out_DefaultConstraintName(OUT defaultConstraintName);
if (getDBTables.Execute() == false)
return false;
while (getDBTables.Fetch())
{
DBModel::TableRef table;
auto findTable = std::find_if(_dbTables.begin(), _dbTables.end(), [=](const DBModel::TableRef& table) { return table->_objectId == objectId; });
if (findTable == _dbTables.end())
{
table = MakeShared<DBModel::Table>();
table->_objectId = objectId;
table->_name = tableName;
_dbTables.push_back(table);
}
else
{
table = *findTable;
}
DBModel::ColumnRef column = MakeShared<DBModel::Column>();
{
column->_name = columnName;
column->_columnId = columnId;
column->_type = static_cast<DBModel::DataType>(userTypeId);
column->_typeText = DBModel::Helpers::DataType2String(column->_type);
column->_maxLength = (column->_type == DBModel::DataType::NVarChar ? maxLength / 2 : maxLength);
column->_nullable = isNullable;
column->_identity = isIdentity;
column->_seedValue = (isIdentity ? seedValue : 0);
column->_incrementValue = (isIdentity ? incValue : 0);
if (defaultObjectId > 0)
{
column->_default = defaultDefinition;
uint64 p = column->_default.find_first_not_of('(');
column->_default = column->_default.substr(p, column->_default.size() - p * 2);
column->_defaultConstraintName = defaultConstraintName;
}
}
table->_columns.push_back(column);
}
return true;
}
bool DBSynchronizer::GatherDBIndexes()
{
int32 objectId;
WCHAR indexName[101] = { 0 };
int32 indexId;
int32 indexType;
bool isPrimaryKey;
bool isUniqueConstraint;
int32 columnId;
WCHAR columnName[101] = { 0 };
SP::GetDBIndexes getDBIndexes(_dbConn);
getDBIndexes.Out_ObjectId(OUT objectId);
getDBIndexes.Out_IndexName(OUT indexName);
getDBIndexes.Out_IndexId(OUT indexId);
getDBIndexes.Out_IndexType(OUT indexType);
getDBIndexes.Out_IsPrimaryKey(OUT isPrimaryKey);
getDBIndexes.Out_IsUniqueConstraint(OUT isUniqueConstraint);
getDBIndexes.Out_ColumnId(OUT columnId);
getDBIndexes.Out_ColumnName(OUT columnName);
if (getDBIndexes.Execute() == false)
return false;
while (getDBIndexes.Fetch())
{
auto findTable = std::find_if(_dbTables.begin(), _dbTables.end(), [=](const DBModel::TableRef& table) { return table->_objectId == objectId; });
ASSERT_CRASH(findTable != _dbTables.end());
Vector<DBModel::IndexRef>& indexes = (*findTable)->_indexes;
auto findIndex = std::find_if(indexes.begin(), indexes.end(), [indexId](DBModel::IndexRef& index) { return index->_indexId == indexId; });
if (findIndex == indexes.end())
{
DBModel::IndexRef index = MakeShared<DBModel::Index>();
{
index->_name = indexName;
index->_indexId = indexId;
index->_type = static_cast<DBModel::IndexType>(indexType);
index->_primaryKey = isPrimaryKey;
index->_uniqueConstraint = isUniqueConstraint;
}
indexes.push_back(index);
findIndex = indexes.end() - 1;
}
// 인덱스가 걸린 column 찾아서 매핑해준다.
Vector<DBModel::ColumnRef>& columns = (*findTable)->_columns;
auto findColumn = std::find_if(columns.begin(), columns.end(), [columnId](DBModel::ColumnRef& column) { return column->_columnId == columnId; });
ASSERT_CRASH(findColumn != columns.end());
(*findIndex)->_columns.push_back(*findColumn);
}
return true;
}
bool DBSynchronizer::GatherDBStoredProcedures()
{
WCHAR name[101] = { 0 };
Vector<WCHAR> body(PROCEDURE_MAX_LEN);
SP::GetDBStoredProcedures getDBStoredProcedures(_dbConn);
getDBStoredProcedures.Out_Name(OUT name);
getDBStoredProcedures.Out_Body(OUT & body[0], PROCEDURE_MAX_LEN);
if (getDBStoredProcedures.Execute() == false)
return false;
while (getDBStoredProcedures.Fetch())
{
DBModel::ProcedureRef proc = MakeShared<DBModel::Procedure>();
{
proc->_name = name;
proc->_fullBody = String(body.begin(), std::find(body.begin(), body.end(), 0));
}
_dbProcedures.push_back(proc);
}
return true;
}
void DBSynchronizer::CompareDBModel()
{
// 업데이트 목록 초기화.
_dependentIndexes.clear();
for (Vector<String>& queries : _updateQueries)
queries.clear();
// XML에 있는 목록을 우선 갖고 온다.
Map<String, DBModel::TableRef> xmlTableMap;
for (DBModel::TableRef& xmlTable : _xmlTables)
xmlTableMap[xmlTable->_name] = xmlTable;
// DB에 실존하는 테이블들을 돌면서 XML에 정의된 테이블들과 비교한다.
for (DBModel::TableRef& dbTable : _dbTables)
{
auto findTable = xmlTableMap.find(dbTable->_name);
if (findTable != xmlTableMap.end())
{
DBModel::TableRef xmlTable = findTable->second;
CompareTables(dbTable, xmlTable);
xmlTableMap.erase(findTable);
}
else
{
if (_xmlRemovedTables.find(dbTable->_name) != _xmlRemovedTables.end())
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Removing Table : [dbo].[%s]\n", dbTable->_name.c_str());
_updateQueries[UpdateStep::DropTable].push_back(DBModel::Helpers::Format(L"DROP TABLE [dbo].[%s]", dbTable->_name.c_str()));
}
}
}
// 맵에서 제거되지 않은 XML 테이블 정의는 새로 추가.
for (auto& mapIt : xmlTableMap)
{
DBModel::TableRef& xmlTable = mapIt.second;
String columnsStr;
const int32 size = static_cast<int32>(xmlTable->_columns.size());
for (int32 i = 0; i < size; i++)
{
if (i != 0)
columnsStr += L",";
columnsStr += L"\n\t";
columnsStr += xmlTable->_columns[i]->CreateText();
}
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Creating Table : [dbo].[%s]\n", xmlTable->_name.c_str());
_updateQueries[UpdateStep::CreateTable].push_back(DBModel::Helpers::Format(L"CREATE TABLE [dbo].[%s] (%s)", xmlTable->_name.c_str(), columnsStr.c_str()));
for (DBModel::ColumnRef& xmlColumn : xmlTable->_columns)
{
if (xmlColumn->_default.empty())
continue;
_updateQueries[UpdateStep::DefaultConstraint].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] ADD CONSTRAINT [%s] DEFAULT (%s) FOR [%s]",
xmlTable->_name.c_str(),
DBModel::Helpers::Format(L"DF_%s_%s", xmlTable->_name.c_str(), xmlColumn->_name.c_str()).c_str(),
xmlColumn->_default.c_str(),
xmlColumn->_name.c_str()));
}
for (DBModel::IndexRef& xmlIndex : xmlTable->_indexes)
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Creating Index : [%s] %s %s [%s]\n", xmlTable->_name.c_str(), xmlIndex->GetKeyText().c_str(), xmlIndex->GetTypeText().c_str(), xmlIndex->GetUniqueName().c_str());
if (xmlIndex->_primaryKey || xmlIndex->_uniqueConstraint)
{
_updateQueries[UpdateStep::CreateIndex].push_back(DBModel::Helpers::Format(
L"ALTER TABLE [dbo].[%s] ADD CONSTRAINT [%s] %s %s (%s)",
xmlTable->_name.c_str(),
xmlIndex->CreateName(xmlTable->_name).c_str(),
xmlIndex->GetKeyText().c_str(),
xmlIndex->GetTypeText().c_str(),
xmlIndex->CreateColumnsText().c_str()));
}
else
{
_updateQueries[UpdateStep::CreateIndex].push_back(DBModel::Helpers::Format(
L"CREATE %s INDEX [%s] ON [dbo].[%s] (%s)",
xmlIndex->GetTypeText().c_str(),
xmlIndex->CreateName(xmlTable->_name).c_str(),
xmlTable->_name.c_str(),
xmlIndex->CreateColumnsText().c_str()));
}
}
}
CompareStoredProcedures();
}
void DBSynchronizer::ExecuteUpdateQueries()
{
for (int32 step = 0; step < UpdateStep::Max; step++)
{
for (String& query : _updateQueries[step])
{
_dbConn.Unbind();
ASSERT_CRASH(_dbConn.Execute(query.c_str()));
}
}
}
void DBSynchronizer::CompareTables(DBModel::TableRef dbTable, DBModel::TableRef xmlTable)
{
// XML에 있는 컬럼 목록을 갖고 온다.
Map<String, DBModel::ColumnRef> xmlColumnMap;
for (DBModel::ColumnRef& xmlColumn : xmlTable->_columns)
xmlColumnMap[xmlColumn->_name] = xmlColumn;
// DB에 실존하는 테이블 컬럼들을 돌면서 XML에 정의된 컬럼들과 비교한다.
for (DBModel::ColumnRef& dbColumn : dbTable->_columns)
{
auto findColumn = xmlColumnMap.find(dbColumn->_name);
if (findColumn != xmlColumnMap.end())
{
DBModel::ColumnRef& xmlColumn = findColumn->second;
CompareColumns(dbTable, dbColumn, xmlColumn);
xmlColumnMap.erase(findColumn);
}
else
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Dropping Column : [%s].[%s]\n", dbTable->_name.c_str(), dbColumn->_name.c_str());
if (dbColumn->_defaultConstraintName.empty() == false)
_updateQueries[UpdateStep::DropColumn].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] DROP CONSTRAINT [%s]", dbTable->_name.c_str(), dbColumn->_defaultConstraintName.c_str()));
_updateQueries[UpdateStep::DropColumn].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] DROP COLUMN [%s]", dbTable->_name.c_str(), dbColumn->_name.c_str()));
}
}
// 맵에서 제거되지 않은 XML 컬럼 정의는 새로 추가.
for (auto& mapIt : xmlColumnMap)
{
DBModel::ColumnRef& xmlColumn = mapIt.second;
DBModel::Column newColumn = *xmlColumn;
newColumn._nullable = true;
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Adding Column : [%s].[%s]\n", dbTable->_name.c_str(), xmlColumn->_name.c_str());
_updateQueries[UpdateStep::AddColumn].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] ADD %s %s",
dbTable->_name.c_str(), xmlColumn->_name.c_str(), xmlColumn->_typeText.c_str()));
if (xmlColumn->_nullable == false && xmlColumn->_default.empty() == false)
{
_updateQueries[UpdateStep::AddColumn].push_back(DBModel::Helpers::Format(L"SET NOCOUNT ON; UPDATE [dbo].[%s] SET [%s] = %s WHERE [%s] IS NULL",
dbTable->_name.c_str(), xmlColumn->_name.c_str(), xmlColumn->_default.c_str(), xmlColumn->_name.c_str()));
}
if (xmlColumn->_nullable == false)
{
_updateQueries[UpdateStep::AddColumn].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] ALTER COLUMN %s",
dbTable->_name.c_str(), xmlColumn->CreateText().c_str()));
}
if (xmlColumn->_default.empty() == false)
{
_updateQueries[UpdateStep::AddColumn].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] ADD CONSTRAINT [DF_%s_%s] DEFAULT (%s) FOR [%s]",
dbTable->_name.c_str(), dbTable->_name.c_str(), xmlColumn->_name.c_str(), xmlColumn->_default.c_str(), xmlColumn->_name.c_str()));
}
}
// XML에 있는 인덱스 목록을 갖고 온다.
Map<String, DBModel::IndexRef> xmlIndexMap;
for (DBModel::IndexRef& xmlIndex : xmlTable->_indexes)
xmlIndexMap[xmlIndex->GetUniqueName()] = xmlIndex;
// DB에 실존하는 테이블 인덱스들을 돌면서 XML에 정의된 인덱스들과 비교한다.
for (DBModel::IndexRef& dbIndex : dbTable->_indexes)
{
auto findIndex = xmlIndexMap.find(dbIndex->GetUniqueName());
if (findIndex != xmlIndexMap.end() && _dependentIndexes.find(dbIndex->GetUniqueName()) == _dependentIndexes.end())
{
DBModel::IndexRef xmlIndex = findIndex->second;
xmlIndexMap.erase(findIndex);
}
else
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Dropping Index : [%s] [%s] %s %s\n", dbTable->_name.c_str(), dbIndex->_name.c_str(), dbIndex->GetKeyText().c_str(), dbIndex->GetTypeText().c_str());
if (dbIndex->_primaryKey || dbIndex->_uniqueConstraint)
_updateQueries[UpdateStep::DropIndex].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] DROP CONSTRAINT [%s]", dbTable->_name.c_str(), dbIndex->_name.c_str()));
else
_updateQueries[UpdateStep::DropIndex].push_back(DBModel::Helpers::Format(L"DROP INDEX [%s] ON [dbo].[%s]", dbIndex->_name.c_str(), dbTable->_name.c_str()));
}
}
// 맵에서 제거되지 않은 XML 인덱스 정의는 새로 추가.
for (auto& mapIt : xmlIndexMap)
{
DBModel::IndexRef xmlIndex = mapIt.second;
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Creating Index : [%s] %s %s [%s]\n", dbTable->_name.c_str(), xmlIndex->GetKeyText().c_str(), xmlIndex->GetTypeText().c_str(), xmlIndex->GetUniqueName().c_str());
if (xmlIndex->_primaryKey || xmlIndex->_uniqueConstraint)
{
_updateQueries[UpdateStep::CreateIndex].push_back(DBModel::Helpers::Format(L"ALTER TABLE [dbo].[%s] ADD CONSTRAINT [%s] %s %s (%s)",
dbTable->_name.c_str(), xmlIndex->CreateName(dbTable->_name).c_str(), xmlIndex->GetKeyText().c_str(), xmlIndex->GetTypeText().c_str(), xmlIndex->CreateColumnsText().c_str()));
}
else
{
_updateQueries[UpdateStep::CreateIndex].push_back(DBModel::Helpers::Format(L"CREATE %s INDEX [%s] ON [dbo].[%s] (%s)",
xmlIndex->GetTypeText(), xmlIndex->CreateName(dbTable->_name).c_str(), dbTable->_name.c_str(), xmlIndex->CreateColumnsText().c_str()));
}
}
}
void DBSynchronizer::CompareColumns(DBModel::TableRef dbTable, DBModel::ColumnRef dbColumn, DBModel::ColumnRef xmlColumn)
{
uint8 flag = 0;
if (dbColumn->_type != xmlColumn->_type)
flag |= ColumnFlag::Type;
if (dbColumn->_maxLength != xmlColumn->_maxLength && xmlColumn->_maxLength > 0)
flag |= ColumnFlag::Length;
if (dbColumn->_nullable != xmlColumn->_nullable)
flag |= ColumnFlag::Nullable;
if (dbColumn->_identity != xmlColumn->_identity || (dbColumn->_identity && dbColumn->_incrementValue != xmlColumn->_incrementValue))
flag |= ColumnFlag::Identity;
if (dbColumn->_default != xmlColumn->_default)
flag |= ColumnFlag::Default;
if (flag)
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Updating Column [%s] : (%s) -> (%s)\n", dbTable->_name.c_str(), dbColumn->CreateText().c_str(), xmlColumn->CreateText().c_str());
}
// 연관된 인덱스가 있으면 나중에 삭제하기 위해 기록한다.
if (flag & (ColumnFlag::Type | ColumnFlag::Length | ColumnFlag::Nullable))
{
for (DBModel::IndexRef& dbIndex : dbTable->_indexes)
if (dbIndex->DependsOn(dbColumn->_name))
_dependentIndexes.insert(dbIndex->GetUniqueName());
flag |= ColumnFlag::Default;
}
if (flag & ColumnFlag::Default)
{
if (dbColumn->_defaultConstraintName.empty() == false)
{
_updateQueries[UpdateStep::AlterColumn].push_back(DBModel::Helpers::Format(
L"ALTER TABLE [dbo].[%s] DROP CONSTRAINT [%s]",
dbTable->_name.c_str(),
dbColumn->_defaultConstraintName.c_str()));
}
}
DBModel::Column newColumn = *dbColumn;
newColumn._default = L"";
newColumn._type = xmlColumn->_type;
newColumn._maxLength = xmlColumn->_maxLength;
newColumn._typeText = xmlColumn->_typeText;
newColumn._seedValue = xmlColumn->_seedValue;
newColumn._incrementValue = xmlColumn->_incrementValue;
if (flag & (ColumnFlag::Type | ColumnFlag::Length | ColumnFlag::Identity))
{
_updateQueries[UpdateStep::AlterColumn].push_back(DBModel::Helpers::Format(
L"ALTER TABLE [dbo].[%s] ALTER COLUMN %s",
dbTable->_name.c_str(),
newColumn.CreateText().c_str()));
}
newColumn._nullable = xmlColumn->_nullable;
if (flag & ColumnFlag::Nullable)
{
if (xmlColumn->_default.empty() == false)
{
_updateQueries[UpdateStep::AlterColumn].push_back(DBModel::Helpers::Format(
L"SET NOCOUNT ON; UPDATE [dbo].[%s] SET [%s] = %s WHERE [%s] IS NULL",
dbTable->_name.c_str(),
xmlColumn->_name.c_str(),
xmlColumn->_name.c_str(),
xmlColumn->_name.c_str()));
}
_updateQueries[UpdateStep::AlterColumn].push_back(DBModel::Helpers::Format(
L"ALTER TABLE [dbo].[%s] ALTER COLUMN %s",
dbTable->_name.c_str(),
newColumn.CreateText().c_str()));
}
if (flag & ColumnFlag::Default)
{
if (dbColumn->_defaultConstraintName.empty() == false)
{
_updateQueries[UpdateStep::AlterColumn].push_back(DBModel::Helpers::Format(
L"ALTER TABLE [dbo].[%s] ADD CONSTRAINT [%s] DEFAULT (%s) FOR [%s]",
dbTable->_name.c_str(),
DBModel::Helpers::Format(L"DF_%s_%s", dbTable->_name.c_str(), dbColumn->_name.c_str()).c_str(),
dbColumn->_default.c_str(), dbColumn->_name.c_str()));
}
}
}
void DBSynchronizer::CompareStoredProcedures()
{
// XML에 있는 프로시저 목록을 갖고 온다.
Map<String, DBModel::ProcedureRef> xmlProceduresMap;
for (DBModel::ProcedureRef& xmlProcedure : _xmlProcedures)
xmlProceduresMap[xmlProcedure->_name] = xmlProcedure;
// DB에 실존하는 테이블 프로시저들을 돌면서 XML에 정의된 프로시저들과 비교한다.
for (DBModel::ProcedureRef& dbProcedure : _dbProcedures)
{
auto findProcedure = xmlProceduresMap.find(dbProcedure->_name);
if (findProcedure != xmlProceduresMap.end())
{
DBModel::ProcedureRef xmlProcedure = findProcedure->second;
String xmlBody = xmlProcedure->GenerateCreateQuery();
if (DBModel::Helpers::RemoveWhiteSpace(dbProcedure->_fullBody) != DBModel::Helpers::RemoveWhiteSpace(xmlBody))
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Updating Procedure : %s\n", dbProcedure->_name.c_str());
_updateQueries[UpdateStep::StoredProcecure].push_back(xmlProcedure->GenerateAlterQuery());
}
xmlProceduresMap.erase(findProcedure);
}
}
// 맵에서 제거되지 않은 XML 프로시저 정의는 새로 추가.
for (auto& mapIt : xmlProceduresMap)
{
GConsoleLogger->WriteStdOut(Color::YELLOW, L"Updating Procedure : %s\n", mapIt.first.c_str());
_updateQueries[UpdateStep::StoredProcecure].push_back(mapIt.second->GenerateCreateQuery());
}
}
추가로, 로그를 찍는 ConsoleLog 클래스도 만들어 준다.
enum class Color
{
BLACK,
WHITE,
RED,
GREEN,
BLUE,
YELLOW,
};
class ConsoleLog
{
enum { BUFFER_SIZE = 4096 };
public:
ConsoleLog();
~ConsoleLog();
public:
void WriteStdOut(Color color, const WCHAR* str, ...);
void WriteStdErr(Color color, const WCHAR* str, ...);
protected:
void SetColor(bool stdOut, Color color);
private:
HANDLE _stdOut;
HANDLE _stdErr;
};
////////////////////////////////////////////////////////////////////////////////////////
#include "pch.h"
#include "ConsoleLog.h"
ConsoleLog::ConsoleLog()
{
_stdOut = ::GetStdHandle(STD_OUTPUT_HANDLE);
_stdErr = ::GetStdHandle(STD_ERROR_HANDLE);
}
ConsoleLog::~ConsoleLog()
{
}
void ConsoleLog::WriteStdOut(Color color, const WCHAR* format, ...)
{
if (format == nullptr)
return;
SetColor(true, color);
va_list ap;
va_start(ap, format);
::vwprintf(format, ap);
va_end(ap);
fflush(stdout);
SetColor(true, Color::WHITE);
}
void ConsoleLog::WriteStdErr(Color color, const WCHAR* format, ...)
{
WCHAR buffer[BUFFER_SIZE];
if (format == nullptr)
return;
SetColor(false, color);
va_list ap;
va_start(ap, format);
::vswprintf_s(buffer, BUFFER_SIZE, format, ap);
va_end(ap);
::fwprintf_s(stderr, buffer);
fflush(stderr);
SetColor(false, Color::WHITE);
}
void ConsoleLog::SetColor(bool stdOut, Color color)
{
static WORD SColors[]
{
0,
FOREGROUND_RED | FOREGROUND_GREEN | FOREGROUND_BLUE,
FOREGROUND_RED | FOREGROUND_INTENSITY,
FOREGROUND_GREEN | FOREGROUND_INTENSITY,
FOREGROUND_BLUE | FOREGROUND_INTENSITY,
FOREGROUND_RED | FOREGROUND_GREEN | FOREGROUND_INTENSITY
};
::SetConsoleTextAttribute(stdOut ? _stdOut : _stdErr, SColors[static_cast<int32>(color)]);
}
위의 클래스들을 이용하면, GameServer 에서는 아래와 같이 DB 를 편리하게 관리할 수 있게 된다! 😉
ASSERT_CRASH(GDBConnectionPool->Connect(1, L"Driver={SQL Server Native Client 11.0};Server=(localdb)\\MSSQLLocalDB;Database=ServerDb;Trusted_Connection=Yes;"));
DBConnection* dbConn = GDBConnectionPool->Pop();
DBSynchronizer dbSync(*dbConn);
dbSync.Synchronize(L"GameDB.xml");
아참, GameDB.xml 은 다음과 같이 일부 수정이 된다(identity 부분이 추가된 것을 볼 수 있다).
<?xml version="1.0" encoding="utf-8"?>
<GameDB>
<Table name="Gold" desc="골드 테이블">
<Column name="id" type="int" notnull="true" identity="1,1" />
<Column name="gold" type="int" notnull="false" />
<Column name="name" type="nvarchar(50)" notnull="false" />
<Column name="createDate" type="datetime" notnull="false" />
<Index type="clustered">
<PrimaryKey/>
<Column name="id" />
</Index>
</Table>
<Procedure name="spInsertGold">
<Param name="@gold" type="int"/>
<Param name="@name" type="nvarchar(50)"/>
<Param name="@createDate" type="datetime"/>
<Body>
<![CDATA[
INSERT INTO [dbo].[Gold]([gold], [name], [createDate]) VALUES(@gold, @name, @createDate);
]]>
</Body>
</Procedure>
<Procedure name="spGetGold">
<Param name="@gold" type="int"/>
<Body>
<![CDATA[
SELECT id, gold, name, createDate FROM [dbo].[Gold] WHERE gold = (@gold)
]]>
</Body>
</Procedure>
</GameDB>'Game Dev > Game Server' 카테고리의 다른 글
| [C++ 게임 서버] 7-5. Procedure Generator (0) | 2023.12.22 |
|---|---|
| [C++ 게임 서버] 7-3. XML Parser (0) | 2023.12.21 |
| [C++ 게임 서버] 7-2. DB Bind (0) | 2023.12.21 |
| [C++ 게임 서버] 7-1. DB Connection (0) | 2023.12.21 |
| [C++ 게임 서버] 6-7. JobTimer (0) | 2023.12.21 |
'Game Dev/Game Server' Related Articles
more
Comments