#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;}/*執(zhí)行不返回數(shù)據(jù)字段的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;}/*執(zhí)行返回一個且只為int的數(shù)據(jù)字段的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);//定位到數(shù)據(jù)集指定位置						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;}/*在插入前首先降低原有數(shù)據(jù)的重要性*/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;}/*每個欄目每個數(shù)據(jù)來源同時最多只存放指定數(shù)量的數(shù)據(jù),超出部分將被刪除*/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;	/////////////////	//更新原有數(shù)據(jù)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)	{//沒有完全添加到數(shù)據(jù)庫,恢復weight到相應值		//update_weight(count - insert_count, class_id, source_id);	}		/////////////////	//刪除超出指定數(shù)量的數(shù)據(jù)	remove_over_news(EACH_SOURCE_NUM_MAX, class_id, source_id);	/////////////////		return count;}