db_mysql.cpp

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

	Dest_terminal_Id,Msg_Length,Msg_Content,Reserved,MsgID) \
	VALUES(%d,'%s',%lld,%d,'%s',%d,%d,%d,\
	%d,%d,%d,%d,'%s',%d,'%s',\
	%d,%d,%d,'%s','%s','%s','%s','%s','%s',%d,'%s',%d,'%s','%s',%lld);", \
	TabName, \
	MtLog->SrcNode, \
	MtLog->SrcRecvTime, \
	MtLog->MsgId_Echo, \
	MtLog->DestNode, \
	MtLog->SentTime, \
	MtLog->RelayTimes, \
	ntohl(MtLog->Submit->Head.Total_Length), \
	ntohl(MtLog->Submit->Head.Sequence_Id), \
	MtLog->Submit->Pk_total, \
	MtLog->Submit->Pk_number, \
	MtLog->Submit->Registered_Delivery, \
	MtLog->Submit->Msg_level, \
	MtLog->Submit->Service_Id, \
	MtLog->Submit->Fee_UserType, \
	MtLog->Submit->Fee_terminal_Id, \
	MtLog->Submit->TP_pId, \
	MtLog->Submit->TP_udhi, \
	MtLog->Submit->Msg_Fmt, \
	MtLog->Submit->Msg_src, \
	MtLog->Submit->FeeType, \
	MtLog->Submit->FeeCode, \
	MtLog->Submit->ValId_Time, \
	MtLog->Submit->At_Time, \
	MtLog->Submit->Src_Id, \
	MtLog->Submit->DestUsr_tl, \
	MtLog->Submit->Dest_terminal_Id, \
	MtLog->Submit->Msg_Length, \
	Content, \
	Reserved, \
	MtLog->Submit->Msg_Id);

  	SafeQuery(szSql);
	return 0;
}

/*===========================================================================
-----------------------------写下行转发记录缓存表----------------------------
===========================================================================*/
int DB_MySQL::WrMT_Queue(void * MtLog, int Protocol, char *TabName)
{
	char Content[281];
	char Reserved[17];

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

		sprintf(szSql, \
		"INSERT INTO %s\
		(SrcNode,SrcRecvTime,MsgId_Echo,DestNode,ScheduleTime,Result,RelayTimes,\
		Protocol,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,\
		Dest_terminal_Id,Msg_Length,Msg_Content,Reserved) \
		VALUES(%d,'%s',%lld,%d,'%s',%d,%d,\
		%d,%d,%d,%d,%d,%d,\
		%d,'%s',%d,'%s',%d,%d,%d,\
		'%s','%s','%s','%s','%s','%s',%d,\
		'%s',%d,'%s','%s');", \
		TabName, \
		CMPP_MtLog->SrcNode, \
		CMPP_MtLog->SrcRecvTime, \
		CMPP_MtLog->MsgId_Echo, \
		CMPP_MtLog->DestNode, \
		CMPP_MtLog->SentTime, \
		(char)CMPP_MtLog->Result, \
		CMPP_MtLog->RelayTimes, \
		Protocol, \
		ntohl(CMPP_MtLog->Submit->Head.Total_Length), \
		ntohl(CMPP_MtLog->Submit->Head.Sequence_Id), \
		CMPP_MtLog->Submit->Pk_total, \
		CMPP_MtLog->Submit->Pk_number, \
		CMPP_MtLog->Submit->Registered_Delivery, \
		CMPP_MtLog->Submit->Msg_level, \
		CMPP_MtLog->Submit->Service_Id, \
		CMPP_MtLog->Submit->Fee_UserType, \
		CMPP_MtLog->Submit->Fee_terminal_Id, \
		CMPP_MtLog->Submit->TP_pId, \
		CMPP_MtLog->Submit->TP_udhi, \
		CMPP_MtLog->Submit->Msg_Fmt, \
		CMPP_MtLog->Submit->Msg_src, \
		CMPP_MtLog->Submit->FeeType, \
		CMPP_MtLog->Submit->FeeCode, \
		CMPP_MtLog->Submit->ValId_Time, \
		CMPP_MtLog->Submit->At_Time, \
		CMPP_MtLog->Submit->Src_Id, \
		CMPP_MtLog->Submit->DestUsr_tl, \
		CMPP_MtLog->Submit->Dest_terminal_Id, \
		CMPP_MtLog->Submit->Msg_Length, \
		Content, \
		Reserved);
	}
	else if(Protocol==SMGP_PROTOCOL)
	{
	}

  	SafeQuery(szSql);
	return 0;
}

/*===========================================================================
---------------------------从上行转发缓存表提取记录--------------------------
===========================================================================*/
int DB_MySQL::ReadMO_Queue(char *SqlCommand, Cmpp_MO_Log *pMoLog, int * RecordNum)
{
  int nCount=0;
  memset(pMoLog,0,(*RecordNum)*sizeof(Cmpp_MO_Log));
  SafeQuery(SqlCommand);

  res = mysql_store_result(&conn);
  while((row = mysql_fetch_row(res)))
  {
		//printf("\n ReadMO_Queue: pMoLog=%d. &pMoLog[%d].Id=%d,atoi(row[0])=%d, row19=%s.\n",&pMoLog[nCount],nCount,&pMoLog[nCount].Id,atoi(row[0]),row[19]);
		pMoLog[nCount].Id = atoi(row[0]);
		pMoLog[nCount].SrcNode = atoi(row[1]);
		strncpy(pMoLog[nCount].SrcRecvTime,row[2], sizeof(pMoLog[nCount].SrcRecvTime));
		pMoLog[nCount].SrcMsgId = atoll(row[3]);
		pMoLog[nCount].DestNode = atoi(row[4]);
		strncpy(pMoLog[nCount].SentTime,row[5],sizeof(pMoLog[nCount].SentTime));
		pMoLog[nCount].Result = atoi(row[6]);
		pMoLog[nCount].RelayTimes = atoi(row[7]);
		int PackLen = atoi(row[8]);
		//printf("\n ReadMO_Queue: PackLen=%d, row8=%s, Msg_Length=%d. \n",PackLen,row[8],atoi(row[18]));
		pMoLog[nCount].Deliver = (CMPP_Deliver *)malloc(sizeof(CMPP_Deliver));
		memset(pMoLog[nCount].Deliver,0,PackLen);
		pMoLog[nCount].Deliver->Head.Total_Length = htonl(PackLen);
		pMoLog[nCount].Deliver->Head.Sequence_Id = htonl(atoi(row[9]));
		pMoLog[nCount].Deliver->Head.Command_Id = htonl(CMPP_DELIVER);
		pMoLog[nCount].Deliver->Msg_Id = atoll(row[10]);
		strncpy(pMoLog[nCount].Deliver->Dest_Id, row[11], sizeof(pMoLog[nCount].Deliver->Dest_Id));
		strncpy(pMoLog[nCount].Deliver->Service_Id,row[12], sizeof(pMoLog[nCount].Deliver->Service_Id));
		pMoLog[nCount].Deliver->TP_pid = atoi(row[13]);
		pMoLog[nCount].Deliver->TP_udhi = atoi(row[14]);
		pMoLog[nCount].Deliver->Msg_Fmt = atoi(row[15]);
		strncpy(pMoLog[nCount].Deliver->Src_terminal_Id,row[16], sizeof(pMoLog[nCount].Deliver->Src_terminal_Id));
		pMoLog[nCount].Deliver->Registered_Delivery = atoi(row[17]);
		pMoLog[nCount].Deliver->Msg_Length = atoi(row[18]);
		memcpy(pMoLog[nCount].Deliver->Deliver_Msg.Msg_Content,row[19],pMoLog[nCount].Deliver->Msg_Length);
		memcpy(pMoLog[nCount].Deliver->Reserved,row[20],sizeof(pMoLog[nCount].Deliver->Reserved));
		nCount++;
  }
  *RecordNum = nCount;  //返回查询所得记录数
  mysql_free_result(res);
  return 0;
}

/*===========================================================================
---------------------------从下行转发缓存表提取记录--------------------------
===========================================================================*/
int DB_MySQL::ReadMT_Queue(char *SqlCommand, Cmpp_MT_Log *pMtLog, int * RecordNum)
{
  int nCount=0;
  //printf("\n ReadMT_Queue: 11111 \n");
  memset(pMtLog,0,(*RecordNum)*sizeof(Cmpp_MT_Log));
  SafeQuery(SqlCommand);

  res = mysql_store_result(&conn);
  while((row = mysql_fetch_row(res)))
  {
		pMtLog[nCount].Id = atoi(row[0]);
		pMtLog[nCount].SrcNode = atoi(row[1]);
		strncpy(pMtLog[nCount].SrcRecvTime,row[2], sizeof(pMtLog[nCount].SrcRecvTime));
		pMtLog[nCount].MsgId_Echo = atoll(row[3]);
		pMtLog[nCount].DestNode = atoi(row[4]);
		strncpy(pMtLog[nCount].SentTime,row[5], sizeof(pMtLog[nCount].SentTime));
		pMtLog[nCount].Result = atoi(row[6]);
		pMtLog[nCount].RelayTimes = atoi(row[7]);

		int Protocol = atoi(row[8]);
		int PackLen = atoi(row[9]);
		if(Protocol==CMPP_PROTOCOL)
		{
			pMtLog[nCount].Submit = (CMPP_Submit *)malloc(sizeof(CMPP_Submit));
			memset(pMtLog[nCount].Submit,0,PackLen);
			pMtLog[nCount].Submit->Head.Total_Length = htonl(PackLen);
			pMtLog[nCount].Submit->Head.Sequence_Id = htonl(atoi(row[10]));
			pMtLog[nCount].Submit->Head.Command_Id = htonl(CMPP_SUBMIT);
			pMtLog[nCount].Submit->Pk_total = atoi(row[11]);
			pMtLog[nCount].Submit->Pk_number = atoi(row[12]);
			pMtLog[nCount].Submit->Registered_Delivery = atoi(row[13]);
			pMtLog[nCount].Submit->Msg_level = atoi(row[14]);
			strncpy(pMtLog[nCount].Submit->Service_Id , row[15], sizeof(pMtLog[nCount].Submit->Service_Id));
			pMtLog[nCount].Submit->Fee_UserType = atoi(row[16]);
			strncpy(pMtLog[nCount].Submit->Fee_terminal_Id , row[17], sizeof(pMtLog[nCount].Submit->Fee_terminal_Id));
			pMtLog[nCount].Submit->TP_pId = atoi(row[18]);
			pMtLog[nCount].Submit->TP_udhi = atoi(row[19]);
			pMtLog[nCount].Submit->Msg_Fmt = atoi(row[20]);
			strncpy(pMtLog[nCount].Submit->Msg_src , row[21], sizeof(pMtLog[nCount].Submit->Msg_src));
			strncpy(pMtLog[nCount].Submit->FeeType , row[22], sizeof(pMtLog[nCount].Submit->FeeType));
			strncpy(pMtLog[nCount].Submit->FeeCode , row[23], sizeof(pMtLog[nCount].Submit->FeeCode));
			strncpy(pMtLog[nCount].Submit->ValId_Time , row[24], sizeof(pMtLog[nCount].Submit->ValId_Time));
			strncpy(pMtLog[nCount].Submit->At_Time , row[25], sizeof(pMtLog[nCount].Submit->At_Time));
			strncpy(pMtLog[nCount].Submit->Src_Id , row[26], sizeof(pMtLog[nCount].Submit->Src_Id));
			pMtLog[nCount].Submit->DestUsr_tl = atoi(row[27]);
			strncpy(pMtLog[nCount].Submit->Dest_terminal_Id , row[28], sizeof(pMtLog[nCount].Submit->Dest_terminal_Id));
			pMtLog[nCount].Submit->Msg_Length = atoi(row[29]);
			memcpy(pMtLog[nCount].Submit->Msg_Content, row[30], pMtLog[nCount].Submit->Msg_Length);
			memcpy(pMtLog[nCount].Submit->Reserve, row[31], sizeof(pMtLog[nCount].Submit->Reserve));
		}
		else if(Protocol==SMGP_PROTOCOL)
		{
			SMGP_Submit * pSubmit = (SMGP_Submit *)malloc(sizeof(SMGP_Submit));
			memset(pSubmit,0,PackLen);
			pSubmit->msgHead.nPacketLength = htonl(PackLen);
			pSubmit->msgHead.nSequenceId = htonl(atoi(row[10]));
			pSubmit->msgHead.nRequestId = htonl(REQUEST_ID_SUBMIT);
			pSubmit->nMsgType = atoi(row[32]);
			pSubmit->nNeedReport = atoi(row[13]);
			pSubmit->nPriority = atoi(row[14]);
			strncpy(pSubmit->sServerId,row[15],sizeof(pSubmit->sServerId));
			strncpy(pSubmit->sFeeType,row[22],sizeof(pSubmit->sFeeType));
			strncpy(pSubmit->sFeeCode,row[23],sizeof(pSubmit->sFeeCode));
			strncpy(pSubmit->sFixedFee,row[33],sizeof(pSubmit->sFixedFee));
			pSubmit->nMsgFormat = atoi(row[20]);
			strncpy(pSubmit->sValidTime,row[24],sizeof(pSubmit->sValidTime));
			strncpy(pSubmit->sAtTime,row[25],sizeof(pSubmit->sAtTime));
			strncpy(pSubmit->sReplyPath,row[26],sizeof(pSubmit->sReplyPath));
			strncpy(pSubmit->sChargeTermId,row[17],sizeof(pSubmit->sChargeTermId));
			pSubmit->nDestTermIdCount = atoi(row[27]);
			strncpy(pSubmit->sDestTermId,row[28],sizeof(pSubmit->sDestTermId));
			pSubmit->nMsgLength = atoi(row[29]);
			memcpy(pSubmit->sMsgContent,row[30],sizeof(pSubmit->sMsgContent));
			pMtLog[nCount].Submit = (CMPP_Submit *)pSubmit;
		}
		nCount++;
  }//while
  *RecordNum = nCount;  //返回查询所得记录数  
  mysql_free_result(res);
  return 0;
}

/*===========================================================================
------------------从CMPP_Submit下行转发日志表中提取MsgId_Echo----------------
===========================================================================*/
int DB_MySQL::GetMsgId_Echo(char *CommandStr, int *RecordId, long long *MsgId_Echo)
{
	int nCount=0;
	SafeQuery(CommandStr);

	res = mysql_store_result(&conn);
	while((row = mysql_fetch_row(res)))
	{
		if(nCount==0)
		{
			*RecordId = atoi(row[0]);
			*MsgId_Echo = atoll(row[1]);
		}
		nCount++;
	}//while
	
	mysql_free_result(res);
	return nCount;
}

/*===========================================================================
---------------------------统计符合查询条件的记录数--------------------------
===========================================================================*/
int DB_MySQL::DBcheck(char * CommandStr, int * RecordNum)
{
  if( (strncmp(CommandStr,"SELECT",6)!=0) && (strncmp(CommandStr,"select",6)!=0) )
    {
      printf("DBcheck:::Not 'Select' Command!");
      return 1;
    }
  if(mysql_query(&conn,CommandStr))
    {
      fprintf(stderr,"DBcheck::: Failed: %s\n", mysql_error(&conn));
      return 1;
    }
  res = mysql_store_result(&conn);
  *RecordNum = mysql_num_rows(res);
  mysql_free_result(res);
  return 0;
}
/*===========================================================================
------------------------获得符合查询语句的记录结构体数组----------------------
=============================================================================*/
//说明：将记录集转化为结构体数组，若为select的是单个字段，则返回一维数组
//各关键字之间只能用一个空格！！
//select后面只能跟*号或者是单个字段名（不支持select多字段）
//DataType=1,表示传入的RecordStruct是Session结构体指针
//DataType=2,表示传入的RecordStruct是char指针
//DataType=3,表示传入的RecordStruct是int指针
//*RecordNum为双向传输的参数，作输入参数时表示想得到的最大记录数，以防写溢出
//然后作为输出参数表示实际获得的记录数。

#define STRINGLEN 255
#define MAXRECORDS 100
int DB_MySQL::DBquery(char * CommandStr, void * RecordStruct, int DataType, int * RecordNum)
{
  //char QueryKey[30];