database.cpp

一个C++写的RSS解析器,需要libxml2.rpm支持

#include <stdio.h>
#include <string.h>

#include "utils.h"
#include "database.h"


#include "dbconn.h"
#include <mysql/mysql.h>



MYSQL mysql_conn; /* Connection handle */


#define MAX_LENGTH      1024
#define COLUMN_LEN	512

#define EACH_SOURCE_NUM_MAX	10


#define INSERT_SUPERLINK_WITH_TEXT "insert into t_superlink (\
	title, link, source_id, language, class_id, issue_time, description) values(\
	'%s',  '%s', %d,   	'%s',     %d,       '%s', 		 '%s')"

#define INSERT_SUPERLINK_WITH_PICTURE "insert into t_superlink (\
	title, link, source_id, language, class_id, issue_time, description, url, type) values(\
	'%s',  '%s', %d,   	'%s',     %d,	 '%s', 	     '%s', 	  '%s',%d)"
	
#define INSERT_NEWS "insert into t_news (\
	title, link, source_id, language, class_id, issue_time, description, weight) values(\
	'%s',  '%s', %d,   	'%s',     %d,       '%s', 		 '%s',	  %d)"
	
#define SELECT_SUBJECT "select class_id from t_subject where code=%d and subcode=%d"
#define SELECT_SOURCE "select source_id from t_source where class_id=%d and name='%s'"

#define SELECT_NEWS "select count(*) from t_news where class_id=%d and source_id=%d"
#define UPDATE_NEWS "update t_news set weight=weight+%d where class_id=%d and source_id=%d"
//#define DELETE_NEWS "delete from t_news where weight>%d and class_id=%d and source_id=%d"
#define DELETE_NEWS "delete from t_news where class_id=%d order by weight, issue_time desc"


int get_Imgurl(char* data, char* url)
{
	if(!data)
		return 0;
		
	char* pBegin = NULL;
	char* pEnd = NULL;
	char* pTemp = NULL;

	pBegin = strstr(data, "&lt;img");
	if(((pBegin = strstr(data, "&lt;img")) == NULL) && ((pBegin = strstr(data, "&lt;IMG")) == NULL) && \
		((pBegin = strstr(data, "<img")) == NULL) && ((pBegin = strstr(data, "<IMG")) == NULL))
		return 0;

	pTemp = strstr(pBegin, "=&quot;");
	if(pTemp != NULL)
	{
		pBegin = pTemp + strlen("=&quot;");
		pEnd = strstr(pTemp, "&quot;");
	}else 
	{

		pTemp = strstr(pBegin, "=\"");
		if(pTemp != NULL)
		{
			pBegin = pTemp + strlen("=\"");
			pEnd = strstr(pTemp + strlen("=\""), "\"");
		}
	}

	if(!pBegin || !pEnd)
		return 0;
		
	memcpy(url, pBegin, pEnd - pBegin);	

	return 1;
	
}

void db_fini() 
{ 
	mysql_close(&mysql_conn);
}

int db_init() 
{
	int ret = -1;
	
	if(&mysql_conn)
		mysql_close(&mysql_conn);	
	
	if(mysql_init(&mysql_conn) != NULL)
	{
		if (mysql_real_connect(&mysql_conn, DB_HOST, DB_USER, 
			DB_PASSWD, DB_NAME, MYSQL_PORT, DB_SOCKET, 0) != NULL)
		{
			ret = 0;
		}else
			DEBUG_SCREEN("Connection to database failed.\n");
	}
	else
		DEBUG_SCREEN("Initialization failed.\n");

	return ret;
}


/*
执行不返回数据字段的sql语句
*/
int excute_sql(char* sqlcmd)
{
	int ret = -1;
	unsigned long total = 0;
	
	MYSQL_ROW cur;
	MYSQL_RES *result = NULL; /* Result handle */
	MYSQL_FIELD *fields = NULL;
	
	if (mysql_query(&mysql_conn, sqlcmd) == 0)
	{	
		result = mysql_store_result(&mysql_conn);
		if(result != NULL)
		{
			total = mysql_num_rows(result);

			ret = total;
		}	
	}
	
	return ret;
}

/*
执行返回一个且只为int的数据字段的sql语句
返回值为查询的唯一值
*/
int query_sql_int(char* selectsql)
{
	int ret = -1;
	unsigned long total = 0;
	
	MYSQL_ROW cur;
	MYSQL_RES *result = NULL; /* Result handle */
	MYSQL_FIELD *fields = NULL;
	
	if (mysql_query(&mysql_conn, selectsql) == 0)
	{	
		result = mysql_store_result(&mysql_conn);
		if(result != NULL)
		{
			total = mysql_num_rows(result);

			mysql_field_seek(result, 0);
			fields = mysql_fetch_fields(result);
			mysql_data_seek(result, 0);//定位到数据集指定位置
			
			while ((cur = mysql_fetch_row(result)))
			{
				ulong *lengths = mysql_fetch_lengths(result);
				for (int i = 0; i < mysql_num_fields(result); i++)
				{
					if(lengths[i] != 0)
					{
						//printf("%s=%s\n", fields[i].name, cur[i]);
						ret = atoi(cur[i]);
					}
				}
			
			}
		
		}	
	}
	
	return ret;
}


int query_classid(int key, int subkey)
{
	char selectsql[512];
	sprintf(selectsql, SELECT_SUBJECT, key, subkey);


	return query_sql_int(selectsql);
}


int query_sourceid(int class_id, char* name)
{
	char selectsql[512];
	sprintf(selectsql, SELECT_SOURCE, class_id, name);

	return query_sql_int(selectsql);;
}


int set_superlink(struct Outline* outline, int class_id, int source_id, struct Channel* channel)
{
	if(channel == NULL)
		return 0;

	struct Channel* pCurrent = channel;
	struct ITEM* pItem = NULL;
	
	int count = 0;
	int ret = 0;
	int isOk = 0;

	char sql[MAX_LENGTH * 4];
	
	while(pCurrent)
	{
		pItem = pCurrent->pItem;
		while(pItem)
		{
			memset(sql, 0 , MAX_LENGTH * 4);

			//如果rss文件中存在source字段，则使用该字段作为链接来源
			if(outline->kind == 0)
			{
				isOk = 1;
				
				if(pItem->description && strlen(pItem->description) > COLUMN_LEN)
					memset(pItem->description + COLUMN_LEN, 0, strlen(pItem->description) - COLUMN_LEN);
				

				sprintf(sql, INSERT_SUPERLINK_WITH_TEXT, pItem->title, pItem->link, source_id, pCurrent->language, class_id, pItem->pubdate, pItem->description?pItem->description:"");
			}else if(outline->kind == 1)
			{
				isOk = 0;

				char url[128] = {0};
				ret = get_Imgurl(pItem->description, url);
				if(ret > 0)
				{
					isOk = 1;
								
					sprintf(sql, INSERT_SUPERLINK_WITH_PICTURE, pItem->title, pItem->link, source_id, pCurrent->language, class_id, pItem->pubdate, "", url, outline->kind);
				}
			}
			
			if(isOk)
				if((ret = mysql_query(&mysql_conn, sql)) == 0)
				{
					pItem->is_inserted = 1;
					count++;
				}

			pItem = pItem->pNext;
		}

		
		
		pCurrent = pCurrent->pNext;
	
	}
	
	return count;
}


/*
在插入前首先降低原有数据的重要性
*/
int update_weight(int weight_offset, int class_id, int source_id)
{
	int ret = -1;

	char updatesql[128];
	sprintf(updatesql, UPDATE_NEWS, weight_offset, class_id, source_id);

	ret = excute_sql(updatesql);
	
	return ret;
}

/*
每个栏目每个数据来源同时最多只存放指定数量的数据,超出部分将被删除
*/
int remove_over_news(int num_max, int class_id, int source_id)
{
	int ret = -1;

	char removesql[128];
	sprintf(removesql, DELETE_NEWS, num_max, class_id, source_id);

	ret = excute_sql(removesql);
	
	return ret;
}

int set_news(int insert_count, struct Outline* outline, int class_id, int source_id, struct Channel* channel)
{
	if(channel == NULL)
		return 0;

	/////////////////
	//更新原有数据weight
	int update_num = update_weight(insert_count, class_id, source_id);
	/////////////////
	
	struct Channel* pCurrent = channel;        
	struct ITEM* pItem = NULL;
	
	int count = 0;
	int ret = 0;
	int isOk = 0;

	char sql[MAX_LENGTH * 4];
	int weight = 0;
	
	while(pCurrent)
	{
		weight = 1;
		pItem = pCurrent->pItem;
		while(pItem)
		{
			if(pItem->is_inserted == 1)
			{
				memset(sql, 0 , MAX_LENGTH * 4);
	
				sprintf(sql, INSERT_NEWS, pItem->title, pItem->link, source_id, pCurrent->language, class_id, pItem->pubdate, pItem->description?pItem->description:"", weight);
				
				if((ret = mysql_query(&mysql_conn, sql)) == 0)
				{
					weight++;
					count++;
				}
			}

			pItem = pItem->pNext;
		}

		pCurrent = pCurrent->pNext;
	
	}
	
	if(count != insert_count)
	{//没有完全添加到数据库,恢复weight到相应值
		//update_weight(count - insert_count, class_id, source_id);
	}
	
	/////////////////
	//删除超出指定数量的数据
	remove_over_news(EACH_SOURCE_NUM_MAX, class_id, source_id);
	/////////////////
	
	return count;
}