db_mysql.cpp

SMS gateway. SMS protocol for CHINA mobile, unicom, lingtong. Using mysql to exchange message.

/**********************************************************************
  FileName            : DB_MySQL.cpp
  Description         : 数据库操作接口文件
  Version             : 1.0
  Date                : 2003年10月07日
  Author              : 刘荣辉
  Other               : 
***********************************************************************/
//#include "stdafx.h"
#include "DB_MySQL.h"

/*===========================================================================
=============================================================================*/
DB_MySQL::DB_MySQL()
{}
DB_MySQL::~DB_MySQL()
{}
/*===========================================================================
=============================================================================*/
int DB_MySQL::SafeQuery(char * CommandStr)	//操作失败时自动重连数据库
{
  int recon_try = 0;
  
  while(1)
  {
	 if(mysql_query(&conn,CommandStr))
	 {
	      if(recon_try==0)
		  {
			  printf("SafeQuery: Failed: %s\n", mysql_error(&conn));
			  printf("SafeQuery: Command=[%s]",CommandStr);
		  }
	      DBClose();	//关闭连接
	      if(recon_try < 11)
	      {
			  printf("\nSafeQuery: [%d] try to reconnect database [%d] seconds later...\n",recon_try,recon_try*recon_try*2);
		      sleep(recon_try*recon_try*2);
		      DBConn();		//重新连接
	      }
	      else
	      {
	      	  printf("\n Can't Operate Database! Program is to EXIT!\n");
	      	  exit(1);	//退出程序
	      }
		  recon_try++;
	 }
	 else break;
  }
  return 0;
}
/*===========================================================================
========================统计符合某查询条件的记录的条数=======================
=============================================================================*/
int DB_MySQL::GetRecordNum(char * SqlCommand)
{
	char CommandHead[30];
	int RecordNum = 0;
	
	int mylen=strlen("select count(*) from");
	strncpy(CommandHead,SqlCommand,mylen);
	for(int i=0; i<mylen; i++)	//全部转化成小写
		CommandHead[i] = tolower(CommandHead[i]);
	//printf("\n GetRecordNum: CommandHead=[%s]. \n",CommandHead);
	if(strncmp(CommandHead,"select count(*) from", mylen)!=0)	//检查语句是否合法
		return -1;
	SafeQuery(SqlCommand);
	res = mysql_store_result(&conn);
	row = mysql_fetch_row(res);
	RecordNum = atoi(row[0]);
	mysql_free_result(res);
	return RecordNum;
}

/*===========================================================================
=============================从数据库获取下节点信息===========================
=============================================================================*/
int DB_MySQL::GetDnNodeInfo(CP_Info *CP_Array, int MaxNum)
{
  int nCount=0; 
  strcpy(szSql,"SELECT ServiceCode,ServiceID,ServiceName,CPID,InterfaceType,MOTable,NodeIp,Username,\
	  Secret,WantReport,TestTime,RecvQ_Size,SendQ_Size,SentW_Size,FeeType,FeeCode,Encoding,\
	  ShowLog,Seperator,URL from Services;");
  SafeQuery(szSql);

  res = mysql_store_result(&conn);
  while((row = mysql_fetch_row(res))) 
  {
    strcpy(CP_Array[nCount].ServiceCode,row[0]);
    strcpy(CP_Array[nCount].ServiceID,row[1]);
    strcpy(CP_Array[nCount].ServiceName,row[2]);
    CP_Array[nCount].CPID = atoi(row[3]);
    CP_Array[nCount].InterfaceType = atoi(row[4]);
    strcpy(CP_Array[nCount].MOTable,row[5]);
    strcpy(CP_Array[nCount].NodeIp,row[6]);
    strcpy(CP_Array[nCount].Username,row[7]);
    strcpy(CP_Array[nCount].Secret,row[8]);
    CP_Array[nCount].WantReport = atoi(row[9]);
    CP_Array[nCount].TestTime = atoi(row[10]);
    CP_Array[nCount].RecvQ_Size = atoi(row[11]);
    CP_Array[nCount].SendQ_Size = atoi(row[12]);
    CP_Array[nCount].SentW_Size = atoi(row[13]);
    strcpy(CP_Array[nCount].FeeType,row[14]);
    strcpy(CP_Array[nCount].FeeCode,row[15]);
    CP_Array[nCount].Encoding = atoi(row[16]);
    CP_Array[nCount].ShowLog = atoi(row[17]);
    strcpy(CP_Array[nCount].Seperator,row[18]);
    strcpy(CP_Array[nCount].URL,row[19]);
    nCount++;
	if(nCount==MaxNum) break;   //已经得到期望的记录数，不再取多余的记录,以免写溢出
  }

  nCount = mysql_num_rows(res);
  mysql_free_result(res);
  return nCount; //返回实际查询所得记录数 
}

/*===========================================================================
=============================从数据库获取MT路由信息===========================
=============================================================================*/
int DB_MySQL::GetMtRoute(MtRouteInfo *MtRoute, char * SqlCommand)
{
  int nCount=0;
  //printf("\n GetMtRoute: SqlCommand=[%s]. \n",SqlCommand);
  SafeQuery(SqlCommand);

  res = mysql_store_result(&conn);
  while((row = mysql_fetch_row(res))) 
  {
    MtRoute[nCount].PhoneSeg = atoi(row[0]);
    MtRoute[nCount].UpNodeID = atoi(row[1]);
    nCount++;
  }

  nCount = mysql_num_rows(res);
  mysql_free_result(res);
  return nCount; //返回实际查询所得记录数 
}

/*===========================================================================
-----------------------------写本地业务接口数据表----------------------------
===========================================================================*/
int DB_MySQL::WrMO_Interface(int SrcProtocol, void * MyDeliver, char *TabName)
{
	char Content[281];
	char Reserved[17];

	memset(Content,0,sizeof(Content));
	memset(Reserved,0,sizeof(Reserved));

	if(SrcProtocol==CMPP_PROTOCOL)
	{
		CMPP_Deliver *pDeliver = (CMPP_Deliver *)MyDeliver;
		//计算Reserved字段的开始地址
		char *pReserved;
		pReserved = (char *)pDeliver + ntohl(pDeliver->Head.Total_Length) - sizeof(pDeliver->Reserved);
		mysql_real_escape_string(&conn,Reserved, pReserved, sizeof(pDeliver->Reserved));
		mysql_real_escape_string(&conn,Content, pDeliver->Deliver_Msg.Msg_Content, pDeliver->Msg_Length);
		sprintf(szSql, \
			"INSERT INTO %s(SrcProtocol,Msg_Id,Dest_Id,Service_Id,Msg_Fmt,\
			Src_terminal_Id,Registered_Delivery,Msg_Length,Msg_Content,Reserved) \
			VALUES(%d,%lld,'%s','%s',%d,'%s',%d,%d,'%s','%s');", \
		TabName, \
		SrcProtocol, \
		pDeliver->Msg_Id, \
		pDeliver->Dest_Id, \
		pDeliver->Service_Id, \
		pDeliver->Msg_Fmt, \
		pDeliver->Src_terminal_Id, \
		pDeliver->Registered_Delivery, \
		pDeliver->Msg_Length, \
		Content, \
		Reserved);
	}
	else if(SrcProtocol==SMGP_PROTOCOL)
	{}
	else if(SrcProtocol==SGIP_PROTOCOL)
	{}

  	SafeQuery(szSql);
	return 0;
}

/*===========================================================================
-------------------------写CMPP_Deliver上行转发记录表------------------------
===========================================================================*/
int DB_MySQL::WrCMPP_MOLog(Cmpp_MO_Log * MoLog, char *TabName)
{
	char Content[281];
	char Reserved[17];

	memset(Content,0,sizeof(Content));
	memset(Reserved,0,sizeof(Reserved));

	//计算Reserved字段的开始地址
	char *pReserved;
	pReserved = (char *)MoLog->Deliver + ntohl(MoLog->Deliver->Head.Total_Length) - sizeof(MoLog->Deliver->Reserved);
	mysql_real_escape_string(&conn,Reserved,pReserved,sizeof(MoLog->Deliver->Reserved));
	mysql_real_escape_string(&conn,Content,MoLog->Deliver->Deliver_Msg.Msg_Content,MoLog->Deliver->Msg_Length);

	sprintf(szSql, \
	"INSERT INTO %s(SrcNode,SrcRecvTime,SrcMsgId,DestNode,SentTime,RelayTimes,\
	Total_Length,Sequence_Id,Msg_Id,Dest_Id,Service_Id,TP_pid,TP_udhi,Msg_Fmt,\
	Src_terminal_Id,Registered_Delivery,Msg_Length,Msg_Content,Reserved) \
	VALUES(%d,'%s',%lld,%d,'%s',%d,\
	%d,%d,%lld,'%s','%s',%d,%d,%d,\
	'%s',%d,%d,'%s','%s');", \
	TabName, \
	MoLog->SrcNode, \
	MoLog->SrcRecvTime, \
	MoLog->SrcMsgId, \
	MoLog->DestNode, \
	MoLog->SentTime, \
	MoLog->RelayTimes, \
	ntohl(MoLog->Deliver->Head.Total_Length), \
	ntohl(MoLog->Deliver->Head.Sequence_Id), \
	MoLog->Deliver->Msg_Id, \
	MoLog->Deliver->Dest_Id, \
	MoLog->Deliver->Service_Id, \
	MoLog->Deliver->TP_pid, \
	MoLog->Deliver->TP_udhi, \
	MoLog->Deliver->Msg_Fmt, \
	MoLog->Deliver->Src_terminal_Id, \
	MoLog->Deliver->Registered_Delivery, \
	MoLog->Deliver->Msg_Length, \
	Content, \
	Reserved);

  	SafeQuery(szSql);
	return 0;
}

/*===========================================================================
-----------------------------写上行转发记录缓存表----------------------------
===========================================================================*/
int DB_MySQL::WrMO_Queue(Cmpp_MO_Log *CMPP_MoLog, char *TabName)
{
	char Content[281];
	char Reserved[17];

	memset(Content,0,sizeof(Content));
	memset(Reserved,0,sizeof(Reserved));

	//计算Reserved字段的开始地址
	char *pReserved;
	pReserved = (char *)CMPP_MoLog->Deliver + ntohl(CMPP_MoLog->Deliver->Head.Total_Length) - sizeof(CMPP_MoLog->Deliver->Reserved);
	mysql_real_escape_string(&conn,Reserved,pReserved,sizeof(CMPP_MoLog->Deliver->Reserved));
	mysql_real_escape_string(&conn,Content,CMPP_MoLog->Deliver->Deliver_Msg.Msg_Content,CMPP_MoLog->Deliver->Msg_Length);

	sprintf(szSql, \
	  "INSERT INTO %s(SrcNode,SrcRecvTime,SrcMsgId,DestNode,ScheduleTime,\
	  Result,RelayTimes,Total_Length,Sequence_Id,Msg_Id,Dest_Id,Service_Id,TP_pid,\
	  TP_udhi,Msg_Fmt,Src_terminal_Id,Registered_Delivery,Msg_Length,Msg_Content,\
	  Reserved) VALUES(%d,'%s',%lld,%d,'%s',\
	  %d,%d,%d,%d,%lld,'%s','%s',%d,\
	  %d,%d,'%s',%d,%d,'%s','%s');", \
	TabName, \
	CMPP_MoLog->SrcNode, \
	CMPP_MoLog->SrcRecvTime, \
	CMPP_MoLog->SrcMsgId, \
	CMPP_MoLog->DestNode, \
	CMPP_MoLog->SentTime, \
	(char)CMPP_MoLog->Result, \
	CMPP_MoLog->RelayTimes, \
	ntohl(CMPP_MoLog->Deliver->Head.Total_Length), \
	ntohl(CMPP_MoLog->Deliver->Head.Sequence_Id), \
	CMPP_MoLog->Deliver->Msg_Id, \
	CMPP_MoLog->Deliver->Dest_Id, \
	CMPP_MoLog->Deliver->Service_Id, \
	CMPP_MoLog->Deliver->TP_pid, \
	CMPP_MoLog->Deliver->TP_udhi, \
	CMPP_MoLog->Deliver->Msg_Fmt, \
	CMPP_MoLog->Deliver->Src_terminal_Id, \
	CMPP_MoLog->Deliver->Registered_Delivery, \
	CMPP_MoLog->Deliver->Msg_Length, \
	Content, \
	Reserved);

  	SafeQuery(szSql);
	return 0;
}

/*===========================================================================
-------------------------写CMPP_Submit下行转发记录表------------------------
===========================================================================*/
int DB_MySQL::WrCMPP_MTLog(Cmpp_MT_Log * MtLog, char *TabName)
{
	char Content[281];
	char Reserved[17];

	memset(Content,0,sizeof(Content));
	memset(Reserved,0,sizeof(Reserved));

	//计算Reserved字段的开始地址
	char *pReserved;
	pReserved = (char *)MtLog->Submit + ntohl(MtLog->Submit->Head.Total_Length) - sizeof(MtLog->Submit->Reserve);
	mysql_real_escape_string(&conn,Reserved,pReserved,sizeof(MtLog->Submit->Reserve));
	mysql_real_escape_string(&conn,Content,MtLog->Submit->Msg_Content,MtLog->Submit->Msg_Length);

	sprintf(szSql, \
	"INSERT INTO %s\
	(SrcNode,SrcRecvTime,MsgId_Echo,DestNode,SentTime,RelayTimes,Total_Length,Sequence_Id,\
	Pk_total,Pk_number,Registered_Delivery,Msg_level,Service_Id,Fee_UserType,Fee_terminal_Id,\
	TP_pid,TP_udhi,Msg_Fmt,Msg_src,FeeType,FeeCode,Valid_Time,At_Time,Src_Id,DestUsr_tl,\