int CDBTask::InitMySqlDb(int iIndex)
{
try
{
CMysql * pMysql = m_vMysql[iIndex];
std::string strMySqlHost;
std::string strMySqlUser;
std::string strMySqlPass;
((CPRServer*)(CMyserver::GetInstance()))->GetMyConf().GetResourceDBInfo(strMySqlHost, strMySqlUser, strMySqlPass, iIndex);
if (!pMysql->IfConnected(strMySqlHost))
{
pMysql->Init();
my_bool bReconnect = 1;
pMysql->SetOptions(MYSQL_OPT_RECONNECT, &bReconnect);
pMysql->Connect(strMySqlHost, strMySqlUser, strMySqlPass, 20);
}
}
catch (CMysqlException &e)
{
WriteRunInfo::WriteError("[CDBTask::InitMySqlDb] %s",e.GetErrMsg());
return -1;
}
return 0;
}
int main(){
//打开表接口
CMysql mysql;
CTable* pTable = mysql.OpenTableInterface("test",1);
CString xxx = (*pTable)[0]["name"].toValue();
//增
//设置记录
CRecord rec(pTable);
rec["name"] = "e";
rec["number"] = 5;
//pTable->Add(&rec);
//定义条件
CCondition con;
//条件and
con && (CTableField("test","name") == "d");
con || (CTableField("test","name") == "e");
con && (CTableField("test","number") <= 5);
con(0,4);
con["test.name"]--;
//条件or，左联合
//con || (CTableField("User","ID") <= CTableField("Department","UserID"));
//条件not
//!con;
//删
//int x = pTable->Delete(&con);
//改
CUpdate upd;
upd["ID"] = 10005;
pTable->UpdateRecord(&upd,&con);
//查
CSelect sel;
sel("test")["name"];
sel("test")["ID"];
sel("Depart")["name"];
CTable table = pTable->SelectRecord(&sel,&con);
CTable *pTable1 = mysql.OpenTableInterface("Depart");
CString strr = (*pTable1)[0]["depart.name"].toValue();
int xxxx = table.size();
CString strDepartName = table[0]["depart.name"].toValue();
int nUserID = table[0]["User.ID"].toValue();
//rec["User"].pTable->mapAttri["User"].Name = "123456";
//修改字段属性
rec["User"]->nLength = 256;
pTable->Add(&rec);
return 0;
}
int MysqlSelectTest(CMysql &hMySql)
{
char szSql[] =
"select * from gslbcc2.Tbl_Domain_Info where strDomain='www.qq.com.';";
try
{
hMySql.Query(szSql);
hMySql.StoreResult();
while (NULL != hMySql.FetchRow())
{
char * pszDomain = hMySql.GetField("strDomain");
char * pszAux = hMySql.GetField("ulAux");
size_t nAux = (pszAux) ? atoi(pszAux) : 0;
Info("[MysqlSelectTest] OK! strDomain[%s], ulAux[%u]",pszDomain, nAux);
}
} catch (exception &ex)
{
Err("[MysqlSelectTest] Fail!");
return -1;
}
return 0;
}
int MysqlUpdateTest(CMysql &hMySql)
{
char szSql[] =
"update gslbcc2.Tbl_Domain_Info set ulAux='100' where strDomain='www.qq.com.';";
try
{
hMySql.Query(szSql);
} catch (CCommonException & ex)
{
Err("[MysqlUpdateTest] Fail!");
return -1;
}
Info("[MysqlUpdateTest] OK!");
return 0;
}
int CInsertTools::LoadPeers()
{
CMysql Mysql;
char szSqlBuf[1024];
struct timespec tm1, tm2;
try
{
Mysql.Init();
Mysql.Connect(m_conf.GetConfItem().vecDbItems[0].strIp, m_conf.GetConfItem().vecDbItems[0].strUserName, m_conf.GetConfItem().vecDbItems[0].strPasswd);
snprintf(szSqlBuf, sizeof(szSqlBuf), "select * from %s.%s;", m_conf.GetConfItem().strDbName.c_str(), ONLINE_PEER_TABLE.c_str());
Mysql.Query(szSqlBuf);
clock_gettime(CLOCK_MONOTONIC, &tm1);
int nIndex = 0;
long lUseTime = 0;
int nSleepTime = (int)((1000 / (float)m_conf.GetConfItem().nSpeedPerSec) * 0.85 * 100 * 1000);
string strPeerId;
while(Mysql.FetchRow())
{
strPeerId = Mysql.GetField("fpeerid");
CInsertPeerCmd reqCmd(strPeerId);
reqCmd.Process();
TransMsg(reqCmd.GetBuf(), reqCmd.GetBufLen(), 0, MACHINE_TYPE, ONLINE_SYN);
nIndex ++;
if(nIndex % 100 == 0)
{
usleep(nSleepTime);
}
if(nIndex >= m_conf.GetConfItem().nSpeedPerSec)
{
clock_gettime(CLOCK_MONOTONIC, &tm2);
lUseTime = (tm2.tv_sec - tm1.tv_sec) * 1000000000 + (tm2.tv_nsec - tm1.tv_nsec);
lUseTime = lUseTime / 1000;
WRITE_ALL_LOG(DEBUG_LOG_TYPE, "send %d peer info, use time %ld microsec", nIndex, lUseTime);
nIndex = 0;
lUseTime = 0;
clock_gettime(CLOCK_MONOTONIC, &tm1);
}
}
Mysql.FreeResult();
}
catch (CMysqlException& e)
{
WriteRunInfo::WriteError("CInsertTools::LoadPeers, sql %s, error %s", szSqlBuf, e.GetErrMsg());
return -1;
}
return 0;
}
int main()
{
// test db read
CMysql cMysql;
int levelType = (int)EContest_Level;
try
{
cMysql.init("127.0.0.1", "root", "123456", 3306, "utf8");
std::ostringstream ossSqlR;
ossSqlR << "SELECT type,contestinfo FROM xcb.contestinfo WHERE type=" << levelType;
cMysql << ossSqlR.str() << std::endl;
std::cout << "SQL='" << ossSqlR.str() << "'" << std::endl;
unsigned int nResultRows = cMysql.resultRows();
// warning
if (0 == nResultRows)
{
std::cout << "DB_QUERY_RES_NOT_EXIST " << std::endl;
return DB_QUERY_RES_NOT_EXIST;
}
// if only rows
if (1 == nResultRows)
{
if (!cMysql.nextRow())
{
std::cout << "DB_QUERY_RES_FETCH_ROW_ERROR " << std::endl;
return DB_QUERY_RES_FETCH_ROW_ERROR;
}
contestLevelSave* contestLevelInfo = (contestLevelSave*)(cMysql.fieldValue("contestinfo"));
std::ostringstream ossContestInfo;
ossContestInfo << "读取等级排名（共" << contestLevelInfo->wNumber << " ）如下：\n"
<< "XCBID: \t LEVEL: \t EXPPERSENT: \t NEWPLACE: \t OLDPLACE: \t\n\n";
for (int i = 0; i < contestLevelInfo->wNumber; i++ )
{
ossContestInfo << contestLevelInfo->qwXCBID << "\t"
<< contestLevelInfo->wLevel << "\t"
<< contestLevelInfo->wExpPersent << "\t"
<< i << "\t"
<< contestLevelInfo->wOldPlace << "\t\n";
}
std::cout << ossContestInfo.str() << std::endl;
//// get contestinfo field
//std::istringstream issContestinfo(cMysql.fieldString("contestinfo"));
//// if string and return
//std::string sContestinfo(issContestinfo.str());
//// if int
//unsigned int unContestinfo = 0;
//issContestinfo >> unContestinfo;
}
// // warning (opt)
// if (1 != nResultRows)
// {
// std::cout << "DB_QUERY_RES_ILLOGIC " << std::endl;
// return DB_QUERY_RES_ROW_ILLOGIC;
// }
// // next row
// for ( unsigned int i = 0; i < nResultRows; i++ )
// {
// if (!cMysql.nextRow())
// {
// std::cout << "DB_QUERY_RES_FETCH_ROW_ERROR " << std::endl;
// return DB_QUERY_RES_FETCH_ROW_ERROR;
// }
//// fetch row
// std::cout << "" << std::endl;
// }
}
catch (CMysqlException& e)
{
std::cout << e.showMessage() << std::endl;
return DB_CMYSQL_EXCEPTION;
}
//// test db write
//try
//{
// std::string sContestinfo("wangxianming");
// cMysql.reInit();
// std::ostringstream ossSqlW;
// ossSqlW << "UPDATE xcb.contestinfo SET contestinfo='" << CMysql::escapeString(sContestinfo) << "' WHERE type=" << levelType;
// cMysql << ossSqlW.str() << std::endl;
// std::cout << "SQL='" << ossSqlW.str() << "'" << std::endl;
// if (1 != cMysql.affectedRows())
// {
// std::cout << "DB_QUERY_RES_FIELD_ILLOGIC " << std::endl;
// return DB_QUERY_RES_FIELD_ILLOGIC;
// }
//}
//catch (CMysqlException& e)
//{
// std::cout << e.showMessage() << std::endl;
// return DB_CMYSQL_EXCEPTION;
//}
return DB_SERVICE_SUCCESS;
}
int CInsertTools::LoadRc()
{
CMysql Mysql;
char szSqlBuf[1024];
string strPeerId;
long long llFileSize;
string strGcid;
int nCapability;
unsigned char nDownloadScale;
struct timespec tm1, tm2;
int nIndex = 0;
long lUseTime = 0;
int nSleepTime = (int)((1000 / (float)m_conf.GetConfItem().nSpeedPerSec) * 0.85 * 100 * 1000);
clock_gettime(CLOCK_MONOTONIC, &tm1);
for (int i = 0; i < m_conf.GetConfItem().nDbNum; i ++)
{
try
{
Mysql.Init();
Mysql.Connect(m_conf.GetConfItem().vecDbItems[i].strIp, m_conf.GetConfItem().vecDbItems[i].strUserName, m_conf.GetConfItem().vecDbItems[i].strPasswd);
for (int j = 0; j < m_conf.GetConfItem().nTableNum; j ++)
{
stringstream strTableName;
strTableName << RC_PEER_TABLE;
strTableName << j;
snprintf(szSqlBuf, sizeof(szSqlBuf), "select * from %s.%s;", m_conf.GetConfItem().strDbName.c_str(), strTableName.str().c_str());
strTableName.clear();
Mysql.Query(szSqlBuf);
while(Mysql.FetchRow())
{
strGcid = Mysql.GetField("fcid");
strPeerId = Mysql.GetField("fpeerid");
llFileSize = strtoul(Mysql.GetField("ffilesize"), 0, 10);
nCapability = strtoul(Mysql.GetField("fcapability"), 0, 10);
nDownloadScale = strtoul(Mysql.GetField("downpercent"), 0, 10);
CInsertRcCmd reqCmd(strGcid, strPeerId, llFileSize, nCapability, nDownloadScale);
reqCmd.Process();
TransMsg(reqCmd.GetBuf(), reqCmd.GetBufLen(), 0, MACHINE_TYPE, CONNECT_SERVER_COMMON);
nIndex ++;
if(nIndex % 100 == 0)
{
usleep(nSleepTime);
}
if(nIndex >= m_conf.GetConfItem().nSpeedPerSec)
{
clock_gettime(CLOCK_MONOTONIC, &tm2);
lUseTime = (tm2.tv_sec - tm1.tv_sec) * 1000000000 + (tm2.tv_nsec - tm1.tv_nsec);
lUseTime = lUseTime / 1000;
WRITE_ALL_LOG(DEBUG_LOG_TYPE, "send %d rc info, use time %ld microsec", nIndex, lUseTime);
nIndex = 0;
lUseTime = 0;
clock_gettime(CLOCK_MONOTONIC, &tm1);
}
}
Mysql.FreeResult();
}
for (int j = 0; j < m_conf.GetConfItem().nTableNum; j ++)
{
stringstream strTableName;
strTableName << RC_PEER_TEMP_TABLE;
strTableName << j;
snprintf(szSqlBuf, sizeof(szSqlBuf), "select * from %s.%s;", m_conf.GetConfItem().strDbName.c_str(), strTableName.str().c_str());
strTableName.clear();
Mysql.Query(szSqlBuf);
while(Mysql.FetchRow())
{
strGcid = Mysql.GetField("fcid");
strPeerId = Mysql.GetField("fpeerid");
llFileSize = strtoul(Mysql.GetField("ffilesize"), 0, 10);
nCapability = strtoul(Mysql.GetField("fcapability"), 0, 10);
nDownloadScale = strtoul(Mysql.GetField("downpercent"), 0, 10);
CInsertRcCmd reqCmd(strGcid, strPeerId, llFileSize, nCapability, nDownloadScale);
reqCmd.Process();
TransMsg(reqCmd.GetBuf(), reqCmd.GetBufLen(), 0, MACHINE_TYPE, CONNECT_SERVER_COMMON);
nIndex ++;
if(nIndex % 100 == 0)
{
usleep(nSleepTime);
}
if(nIndex >= m_conf.GetConfItem().nSpeedPerSec)
{
clock_gettime(CLOCK_MONOTONIC, &tm2);
lUseTime = (tm2.tv_sec - tm1.tv_sec) * 1000000000 + (tm2.tv_nsec - tm1.tv_nsec);
lUseTime = lUseTime / 1000;
WRITE_ALL_LOG(DEBUG_LOG_TYPE, "send %d rc temp info, use time %ld microsec", nIndex, lUseTime);
nIndex = 0;
lUseTime = 0;
clock_gettime(CLOCK_MONOTONIC, &tm1);
}
}
Mysql.FreeResult();
}
}
catch (CMysqlException& e)
{
WriteRunInfo::WriteError("CInsertTools::LoadPeers, sql %s, error %s", szSqlBuf, e.GetErrMsg());
return -1;
}
}
return 0;
}