- improved performance of MySQL calls (Alexei)

git-svn-id: svn://svn.zabbix.com/trunk@2812 97f52cf1-0a1b-0410-bd0e-c28be96e8082

5 files changed, 109 insertions, 102 deletions

diff --git a/src/libs/zbxdbhigh/db.c b/src/libs/zbxdbhigh/db.c
index 77280a0a..5545deb5 100644
--- a/src/libs/zbxdbhigh/db.c
+++ b/src/libs/zbxdbhigh/db.c
@@ -878,13 +878,13 @@ int DBdelete_history_pertial(int itemid)
 
 void DBupdate_triggers_status_after_restart(void)
 {
-	int	i;
 	char	sql[MAX_STRING_LEN];
 	int	lastchange;
 	int	now;
 
 	DB_RESULT	result;
 	DB_RESULT	result2;
+	DB_ROW	row;
 	DB_TRIGGER	trigger;
 
 	zabbix_log(LOG_LEVEL_DEBUG,"In DBupdate_triggers_after_restart()");
@@ -895,10 +895,10 @@ void DBupdate_triggers_status_after_restart(void)
 	zabbix_log(LOG_LEVEL_DEBUG,"SQL [%s]",sql);
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		trigger.triggerid=atoi(DBget_field(result,i,0));
-		trigger.value=atoi(DBget_field(result,i,1));
+		trigger.triggerid=atoi(row[0]);
+		trigger.value=atoi(row[1]);
 
 		snprintf(sql,sizeof(sql)-1,"select min(i.nextcheck+i.delay) from hosts h,items i,triggers t,functions f where f.triggerid=t.triggerid and f.itemid=i.itemid and h.hostid=i.hostid and i.nextcheck<>0 and t.triggerid=%d and i.type<>%d",trigger.triggerid,ITEM_TYPE_TRAPPER);
 		zabbix_log(LOG_LEVEL_DEBUG,"SQL [%s]",sql);
diff --git a/src/zabbix_server/actions.c b/src/zabbix_server/actions.c
index db29b465..06898698 100644
--- a/src/zabbix_server/actions.c
+++ b/src/zabbix_server/actions.c
@@ -133,19 +133,18 @@ static	void	send_to_user_medias(DB_TRIGGER *trigger,DB_ACTION *action, int useri
 	DB_MEDIA media;
 	char sql[MAX_STRING_LEN];
 	DB_RESULT result;
-
-	int	i;
+	DB_ROW	row;
 
 	snprintf(sql,sizeof(sql)-1,"select mediatypeid,sendto,active,severity,period from media where active=%d and userid=%d",MEDIA_STATUS_ACTIVE,userid);
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		media.mediatypeid=atoi(DBget_field(result,i,0));
-		media.sendto=DBget_field(result,i,1);
-		media.active=atoi(DBget_field(result,i,2));
-		media.severity=atoi(DBget_field(result,i,3));
-		media.period=DBget_field(result,i,4);
+		media.mediatypeid=atoi(row[0]);
+		media.sendto=row[1];
+		media.active=atoi(row[2]);
+		media.severity=atoi(row[3]);
+		media.period=row[4];
 
 		zabbix_log( LOG_LEVEL_DEBUG, "Trigger severity [%d] Media severity [%d] Period [%s]",trigger->priority, media.severity, media.period);
 		if(((1<<trigger->priority)&media.severity)==0)
@@ -184,8 +183,7 @@ static	void	send_to_user(DB_TRIGGER *trigger,DB_ACTION *action)
 {
 	char sql[MAX_STRING_LEN];
 	DB_RESULT result;
-
-	int	i;
+	DB_ROW	row;
 
 	if(action->recipient == RECIPIENT_TYPE_USER)
 	{
@@ -195,9 +193,9 @@ static	void	send_to_user(DB_TRIGGER *trigger,DB_ACTION *action)
 	{
 		snprintf(sql,sizeof(sql)-1,"select u.userid from users u, users_groups ug where ug.usrgrpid=%d and ug.userid=u.userid", action->userid);
 		result = DBselect(sql);
-		for(i=0;i<DBnum_rows(result);i++)
+		while((row=DBfetch(result)))
 		{
-			send_to_user_medias(trigger, action, atoi(DBget_field(result,i,0)));
+			send_to_user_medias(trigger, action, atoi(row[0]));
 		}
 		DBfree_result(result);
 	}
@@ -575,9 +573,9 @@ static int get_next_command(char** command_list, char** alias, int* is_group, ch
 static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigger_value, DB_CONDITION *condition)
 {
 	DB_RESULT result;
+	DB_ROW	row;
 	char sql[MAX_STRING_LEN];
 
-	int	i;
 	int	ret = SUCCEED;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In check_action_condition [actionid:%d,conditionid:%d:cond.value:%s]", condition->actionid, condition->conditionid, condition->value);
@@ -586,12 +584,12 @@ static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigge
 	{
 		snprintf(sql,sizeof(sql)-1,"select distinct hg.groupid from hosts_groups hg,hosts h, items i, functions f, triggers t where hg.hostid=h.hostid and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and t.triggerid=%d", trigger->triggerid);
 		result = DBselect(sql);
-		for(i=0;i<DBnum_rows(result);i++)
+		while((row=DBfetch(result)))
 		{
 			ret = FAIL;
 			if(condition->operator == CONDITION_OPERATOR_EQUAL)
 			{
-				if(atoi(condition->value) == atoi(DBget_field(result,i,0)))
+				if(atoi(condition->value) == atoi(row[0]))
 				{
 					ret = SUCCEED;
 					break;
@@ -599,7 +597,7 @@ static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigge
 			}
 			else if(condition->operator == CONDITION_OPERATOR_NOT_EQUAL)
 			{
-				if(atoi(condition->value) != atoi(DBget_field(result,i,0)))
+				if(atoi(condition->value) != atoi(row[0]))
 				{
 					ret = SUCCEED;
 					break;
@@ -619,11 +617,11 @@ static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigge
 		ret = FAIL;
 		snprintf(sql,sizeof(sql)-1,"select distinct h.hostid from hosts h, items i, functions f, triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and t.triggerid=%d", trigger->triggerid);
 		result = DBselect(sql);
-		for(i=0;i<DBnum_rows(result);i++)
+		while((row=DBfetch(result)))
 		{
 			if(condition->operator == CONDITION_OPERATOR_EQUAL)
 			{
-				if(atoi(condition->value) == atoi(DBget_field(result,i,0)))
+				if(atoi(condition->value) == atoi(row[0]))
 				{
 					ret = SUCCEED;
 					break;
@@ -631,7 +629,7 @@ static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigge
 			}
 			else if(condition->operator == CONDITION_OPERATOR_NOT_EQUAL)
 			{
-				if(atoi(condition->value) != atoi(DBget_field(result,i,0)))
+				if(atoi(condition->value) != atoi(row[0]))
 				{
 					ret = SUCCEED;
 					break;
@@ -771,14 +769,15 @@ static int	check_action_condition(DB_TRIGGER *trigger,int alarmid,int new_trigge
 static int	check_action_conditions(DB_TRIGGER *trigger,int alarmid,int new_trigger_value, int actionid)
 {
 	DB_RESULT result;
+	DB_ROW row;
 	char sql[MAX_STRING_LEN];
 
 	DB_CONDITION	condition;
 	
 	int	ret = SUCCEED;
-	int	i;
 	int	old_type = -1;
 	int	ret_and = SUCCEED;
+	int	i;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In check_action_conditions [actionid:%d]", actionid);
 
@@ -791,13 +790,14 @@ static int	check_action_conditions(DB_TRIGGER *trigger,int alarmid,int new_trigg
 	}
 	else
 	{
-		for(i=0;i<DBnum_rows(result);i++)
+		i=0;
+		while((row=DBfetch(result)))
 		{
-			condition.conditionid=atoi(DBget_field(result,i,0));
-			condition.actionid=atoi(DBget_field(result,i,1));
-			condition.conditiontype=atoi(DBget_field(result,i,2));
-			condition.operator=atoi(DBget_field(result,i,3));
-			condition.value=DBget_field(result,i,4);
+			condition.conditionid=atoi(row[0]);
+			condition.actionid=atoi(row[1]);
+			condition.conditiontype=atoi(row[2]);
+			condition.operator=atoi(row[3]);
+			condition.value=row[4];
 
 			/* If old AND condition is FALSE */
 			if( (condition.conditiontype != old_type) && (ret_and == FAIL))
@@ -818,6 +818,7 @@ static int	check_action_conditions(DB_TRIGGER *trigger,int alarmid,int new_trigg
 			}
 
 			old_type = condition.conditiontype;
+			i++;
 		}
 	}
 
@@ -829,12 +830,12 @@ static int	check_action_conditions(DB_TRIGGER *trigger,int alarmid,int new_trigg
 void	apply_actions(DB_TRIGGER *trigger,int alarmid,int trigger_value)
 {
 	DB_RESULT result;
+	DB_ROW row;
 	
 	DB_ACTION action;
 
 	char sql[MAX_STRING_LEN];
 
-	int	i;
 	int	now;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In apply_actions(triggerid:%d,alarmid:%d,trigger_value:%d)",trigger->triggerid, alarmid, trigger_value);
@@ -866,25 +867,25 @@ void	apply_actions(DB_TRIGGER *trigger,int alarmid,int trigger_value)
 	result = DBselect(sql);
 	zabbix_log( LOG_LEVEL_DEBUG, "SQL [%s]", sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		action.actionid=atoi(DBget_field(result,i,0));
+		action.actionid=atoi(row[0]);
 
 		if(check_action_conditions(trigger, alarmid, trigger_value, action.actionid) == SUCCEED)
 		{
-			action.actiontype=atoi(DBget_field(result,i,9));
-			action.userid=atoi(DBget_field(result,i,1));
-			action.delay=atoi(DBget_field(result,i,2));
+			action.actiontype=atoi(row[9]);
+			action.userid=atoi(row[1]);
+			action.delay=atoi(row[2]);
 			
-			strscpy(action.subject,DBget_field(result,i,3));
-			strscpy(action.message,DBget_field(result,i,4));
+			strscpy(action.subject,row[3]);
+			strscpy(action.message,row[4]);
 			substitute_macros(trigger, &action, action.message);
 			substitute_macros(trigger, &action, action.subject);
 
-			action.recipient=atoi(DBget_field(result,i,5));
-			action.maxrepeats=atoi(DBget_field(result,i,6));
-			action.repeatdelay=atoi(DBget_field(result,i,7));
-			strscpy(action.scripts,DBget_field(result,i,8));
+			action.recipient=atoi(row[5]);
+			action.maxrepeats=atoi(row[6]);
+			action.repeatdelay=atoi(row[7]);
+			strscpy(action.scripts,row[8]);
 
 			if(action.actiontype == ACTION_TYPE_MESSAGE)
 				send_to_user(trigger,&action);
diff --git a/src/zabbix_server/alerter/alerter.c b/src/zabbix_server/alerter/alerter.c
index aa5fbc0f..e44ccc9d 100644
--- a/src/zabbix_server/alerter/alerter.c
+++ b/src/zabbix_server/alerter/alerter.c
@@ -182,11 +182,12 @@ int main_alerter_loop()
 	char	error[MAX_STRING_LEN];
 	char	error_esc[MAX_STRING_LEN];
 
-	int	i,res, now;
+	int	res, now;
 
 	struct	sigaction phan;
 
 	DB_RESULT	result;
+	DB_ROW		row;
 	DB_ALERT	alert;
 	DB_MEDIATYPE	mediatype;
 
@@ -204,25 +205,25 @@ int main_alerter_loop()
 		snprintf(sql,sizeof(sql)-1,"select a.alertid,a.mediatypeid,a.sendto,a.subject,a.message,a.status,a.retries,mt.mediatypeid,mt.type,mt.description,mt.smtp_server,mt.smtp_helo,mt.smtp_email,mt.exec_path,a.delay from alerts a,media_type mt where a.status=%d and a.retries<3 and (a.repeats<a.maxrepeats or a.maxrepeats=0) and a.nextcheck<=%d and a.mediatypeid=mt.mediatypeid order by a.clock", ALERT_STATUS_NOT_SENT, now);
 		result = DBselect(sql);
 
-		for(i=0;i<DBnum_rows(result);i++)
+		while((row=DBfetch(result)))
 		{
-			alert.alertid=atoi(DBget_field(result,i,0));
-			alert.mediatypeid=atoi(DBget_field(result,i,1));
-			alert.sendto=DBget_field(result,i,2);
-			alert.subject=DBget_field(result,i,3);
-			alert.message=DBget_field(result,i,4);
-			alert.status=atoi(DBget_field(result,i,5));
-			alert.retries=atoi(DBget_field(result,i,6));
-
-			mediatype.mediatypeid=atoi(DBget_field(result,i,7));
-			mediatype.type=atoi(DBget_field(result,i,8));
-			mediatype.description=DBget_field(result,i,9);
-			mediatype.smtp_server=DBget_field(result,i,10);
-			mediatype.smtp_helo=DBget_field(result,i,11);
-			mediatype.smtp_email=DBget_field(result,i,12);
-			mediatype.exec_path=DBget_field(result,i,13);
-
-			alert.delay=atoi(DBget_field(result,i,14));
+			alert.alertid=atoi(row[0]);
+			alert.mediatypeid=atoi(row[1]);
+			alert.sendto=row[2];
+			alert.subject=row[3];
+			alert.message=row[4];
+			alert.status=atoi(row[5]);
+			alert.retries=atoi(row[6]);
+
+			mediatype.mediatypeid=atoi(row[7]);
+			mediatype.type=atoi(row[8]);
+			mediatype.description=row[9];
+			mediatype.smtp_server=row[10];
+			mediatype.smtp_helo=row[11];
+			mediatype.smtp_email=row[12];
+			mediatype.exec_path=row[13];
+
+			alert.delay=atoi(row[14]);
 
 			phan.sa_handler = &signal_handler;
 			sigemptyset(&phan.sa_mask);
diff --git a/src/zabbix_server/functions.c b/src/zabbix_server/functions.c
index f3c5af5a..70dc7444 100644
--- a/src/zabbix_server/functions.c
+++ b/src/zabbix_server/functions.c
@@ -71,12 +71,12 @@ void	update_functions(DB_ITEM *item)
 {
 	DB_FUNCTION	function;
 	DB_RESULT	result;
+	DB_ROW		row;
 	char		sql[MAX_STRING_LEN];
 	char		value[MAX_STRING_LEN];
 	char		value_esc[MAX_STRING_LEN];
 	char		*lastvalue;
 	int		ret=SUCCEED;
-	int		i;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In update_functions(%d)",item->itemid);
 
@@ -84,12 +84,12 @@ void	update_functions(DB_ITEM *item)
 
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		function.function=DBget_field(result,i,0);
-		function.parameter=DBget_field(result,i,1);
-		function.itemid=atoi(DBget_field(result,i,2));
-		lastvalue=DBget_field(result,i,3);
+		function.function=row[0];
+		function.parameter=row[1];
+		function.itemid=atoi(row[2]);
+		lastvalue=row[3];
 
 		zabbix_log( LOG_LEVEL_DEBUG, "ItemId:%d Evaluating %s(%d)\n",function.itemid,function.function,function.parameter);
 
@@ -230,20 +230,19 @@ void	update_services_rec(int serviceid)
 void	update_services(int triggerid, int status)
 {
 	char	sql[MAX_STRING_LEN];
-	int	i;
+	DB_ROW	row;
 
 	DB_RESULT result;
 
 	snprintf(sql,sizeof(sql)-1,"update services set status=%d where triggerid=%d",status,triggerid);
 	DBexecute(sql);
 
-
 	snprintf(sql,sizeof(sql)-1,"select serviceid from services where triggerid=%d", triggerid);
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		update_services_rec(atoi(DBget_field(result,i,0)));
+		update_services_rec(atoi(row[0]));
 	}
 
 	DBfree_result(result);
@@ -274,8 +273,7 @@ void	update_triggers(int itemid)
 	time_t	now;
 	DB_TRIGGER	trigger;
 	DB_RESULT	result;
-
-	int	i;
+	DB_ROW		row;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In update_triggers [%d]", itemid);
 
@@ -286,14 +284,14 @@ void	update_triggers(int itemid)
 
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row==DBfetch(result)))
 	{
-		trigger.triggerid=atoi(DBget_field(result,i,0));
-		strscpy(trigger.expression,DBget_field(result,i,1));
-		trigger.status=atoi(DBget_field(result,i,2));
-		trigger.priority=atoi(DBget_field(result,i,4));
-		trigger.value=atoi(DBget_field(result,i,5));
-		strscpy(trigger.description,DBget_field(result,i,6));
+		trigger.triggerid=atoi(row[0]);
+		strscpy(trigger.expression,row[1]);
+		trigger.status=atoi(row[2]);
+		trigger.priority=atoi(row[4]);
+		trigger.value=atoi(row[5]);
+		strscpy(trigger.description,row[6]);
 
 		strscpy(exp, trigger.expression);
 		if( evaluate_expression(&exp_value, exp, error, sizeof(error)) != 0 )
diff --git a/src/zabbix_server/housekeeper/housekeeper.c b/src/zabbix_server/housekeeper/housekeeper.c
index 2243ed44..d05e4952 100644
--- a/src/zabbix_server/housekeeper/housekeeper.c
+++ b/src/zabbix_server/housekeeper/housekeeper.c
@@ -320,9 +320,9 @@ static int housekeeping_process_log()
 	DB_HOUSEKEEPER	housekeeper;
 
 	DB_RESULT	result;
+	DB_ROW		row;
 	int		res = SUCCEED;
 
-	int		i;
 	long		deleted;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In housekeeping_process_log()");
@@ -331,12 +331,12 @@ static int housekeeping_process_log()
 	snprintf(sql,sizeof(sql)-1,"select housekeeperid, tablename, field, value from housekeeper order by tablename");
 	result = DBselect(sql);
 
-	for(i=0;i<DBnum_rows(result);i++)
+	while((row=DBfetch(result)))
 	{
-		housekeeper.housekeeperid=atoi(DBget_field(result,i,0));
-		housekeeper.tablename=DBget_field(result,i,1);
-		housekeeper.field=DBget_field(result,i,2);
-		housekeeper.value=atoi(DBget_field(result,i,3));
+		housekeeper.housekeeperid=atoi(row[0]);
+		housekeeper.tablename=row[1];
+		housekeeper.field=row[2];
+		housekeeper.value=atoi(row[3]);
 
 		snprintf(sql,sizeof(sql)-1,"delete from %s where %s=%d limit 500",housekeeper.tablename, housekeeper.field,housekeeper.value);
 		DBexecute(sql);
@@ -376,6 +376,7 @@ static int housekeeping_alerts(int now)
 	char		sql[MAX_STRING_LEN];
 	int		alert_history;
 	DB_RESULT	result;
+	DB_ROW		row;
 	int		res = SUCCEED;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In housekeeping_alerts(%d)", now);
@@ -383,12 +384,9 @@ static int housekeeping_alerts(int now)
 	snprintf(sql,sizeof(sql)-1,"select alert_history from config");
 	result = DBselect(sql);
 
-	if(DBnum_rows(result) == 0)
-	{
-		zabbix_log( LOG_LEVEL_ERR, "No records in table 'config'.");
-		res = FAIL;
-	}
-	else
+	row=DBfetch(result);
+
+	if(row)
 	{
 		alert_history=atoi(DBget_field(result,0,0));
 
@@ -396,6 +394,11 @@ static int housekeeping_alerts(int now)
 		DBexecute(sql);
 		zabbix_log( LOG_LEVEL_DEBUG, "Deleted [%ld] records from table [alerts]", DBaffected_rows());
 	}
+	else
+	{
+		zabbix_log( LOG_LEVEL_ERR, "No records in table 'config'.");
+		res = FAIL;
+	}
 
 	DBfree_result(result);
 	return res;
@@ -407,27 +410,26 @@ static int housekeeping_alarms(int now)
 	int		alarm_history;
 	DB_RESULT	result;
 	DB_RESULT	result2;
-	int 		i, alarmid;
+	DB_ROW		row1;
+	DB_ROW		row2;
+	int 		alarmid;
 	int		res = SUCCEED;
 
 	zabbix_log( LOG_LEVEL_DEBUG, "In housekeeping_alarms(%d)", now);
 
 	snprintf(sql,sizeof(sql)-1,"select alarm_history from config");
 	result = DBselect(sql);
-	if(DBnum_rows(result) == 0)
-	{
-		zabbix_log( LOG_LEVEL_ERR, "No records in table 'config'.");
-		res = FAIL;
-	}
-	else
+	row1=DBfetch(result);
+	
+	if(row1)
 	{
-		alarm_history=atoi(DBget_field(result,0,0));
+		alarm_history=atoi(row1[0]);
 
 		snprintf(sql,sizeof(sql)-1,"select alarmid from alarms where clock<%d", now-24*3600*alarm_history);
 		result2 = DBselect(sql);
-		for(i=0;i<DBnum_rows(result2);i++)
+		while((row2=DBfetch(result2)))
 		{
-			alarmid=atoi(DBget_field(result2,i,0));
+			alarmid=atoi(row2[0]);
 			
 			snprintf(sql,sizeof(sql)-1,"delete from acknowledges where alarmid=%d",alarmid);
 			DBexecute(sql);
@@ -439,6 +441,11 @@ static int housekeeping_alarms(int now)
 
 		zabbix_log( LOG_LEVEL_DEBUG, "Deleted [%ld] records from table [alarms]", DBaffected_rows());
 	}
+	else
+	{
+		zabbix_log( LOG_LEVEL_ERR, "No records in table 'config'.");
+		res = FAIL;
+	}
 	
 	DBfree_result(result);
 	return res;