sql_common.c

打魔兽战网的都知道他是什么

    if ((result = sql->query_res(query)) != NULL)
    {
	if (sql->num_rows(result) < 1)
	{
	    sql->free_result(result);
	    return 0;		/* empty clan list */
	}

	while ((row = sql->fetch_row(result)) != NULL)
	{
	    if (row[0] == NULL)
	    {
		eventlog(eventlog_level_error, __FUNCTION__, "got NULL cid from db");
		continue;
	    }

	    clan = xmalloc(sizeof(t_clan));

	    if (!(clan->clanid = atoi(row[0])))
	    {
		eventlog(eventlog_level_error, __FUNCTION__, "got bad cid");
		sql->free_result(result);
		return -1;
	    }

	    clan->clantag = atoi(row[1]);

	    clan->clanname = xstrdup(row[2]);
	    clan->clan_motd = xstrdup(row[3]);
	    clan->creation_time = atoi(row[4]);
	    clan->created = 1;
	    clan->modified = 0;
	    clan->channel_type = prefs_get_clan_channel_default_private();
	    clan->members = list_create();

	    snprintf(query, sizeof(query), "SELECT "SQL_UID_FIELD", status, join_time FROM %sclanmember WHERE cid='%u'", tab_prefix, clan->clanid);

	    if ((result2 = sql->query_res(query)) != NULL)
	    {
		if (sql->num_rows(result2) >= 1)
		    while ((row2 = sql->fetch_row(result2)) != NULL)
		    {
			member = xmalloc(sizeof(t_clanmember));
			if (row2[0] == NULL)
			{
			    eventlog(eventlog_level_error, __FUNCTION__, "got NULL uid from db");
			    continue;
			}
			if (!(member_uid = atoi(row2[0])))
			    continue;
			if (!(member->memberacc = accountlist_find_account_by_uid(member_uid)))
			{
			    eventlog(eventlog_level_error, __FUNCTION__, "cannot find uid %u", member_uid);
			    xfree((void *) member);
			    continue;
			}
			member->status = atoi(row2[1]);
			member->join_time = atoi(row2[2]);
			member->clan	  = clan;

			if ((member->status == CLAN_NEW) && (time(NULL) - member->join_time > prefs_get_clan_newer_time() * 3600))
			{
			    member->status = CLAN_PEON;
			    clan->modified = 1;
			    member->modified = 1;
			}

			list_append_data(clan->members, member);

			account_set_clanmember(member->memberacc, member);
			eventlog(eventlog_level_trace, __FUNCTION__, "added member: uid: %i status: %c join_time: %u", member_uid, member->status + '0', (unsigned) member->join_time);
		    }
		sql->free_result(result2);
		cb(clan);
	    } else
		eventlog(eventlog_level_error, __FUNCTION__, "error query db (query:\"%s\")", query);
	}

	sql->free_result(result);
    } else
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error query db (query:\"%s\")", query);
	return -1;
    }
    return 0;
}

extern int sql_write_clan(void *data)
{
    t_sql_res *result;
    t_sql_row *row;
    t_elem *curr;
    t_clanmember *member;
    t_clan *clan = (t_clan *) data;
    int num;

    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    snprintf(query, sizeof(query), "SELECT count(*) FROM %sclan WHERE cid='%u'", tab_prefix, clan->clanid);
    if ((result = sql->query_res(query)) != NULL)
    {
	row = sql->fetch_row(result);
	if (row == NULL || row[0] == NULL)
	{
	    sql->free_result(result);
	    eventlog(eventlog_level_error, __FUNCTION__, "got NULL count");
	    return -1;
	}
	num = atol(row[0]);
	sql->free_result(result);
	if (num < 1)
	    snprintf(query, sizeof(query), "INSERT INTO %sclan (cid, short, name, motd, creation_time) VALUES('%u', '%d', '%s', '%s', '%u')", tab_prefix, clan->clanid, clan->clantag, clan->clanname, clan->clan_motd, (unsigned) clan->creation_time);
	else
	    snprintf(query, sizeof(query), "UPDATE %sclan SET short='%d', name='%s', motd='%s', creation_time='%u' WHERE cid='%u'", tab_prefix, clan->clantag, clan->clanname, clan->clan_motd, (unsigned) clan->creation_time, clan->clanid);
	if (sql->query(query) < 0)
	{
	    eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
	    return -1;
	}
	LIST_TRAVERSE(clan->members, curr)
	{
	    unsigned int uid;

	    if (!(member = elem_get_data(curr)))
	    {
		eventlog(eventlog_level_error, __FUNCTION__, "got NULL elem in list");
		continue;
	    }
	    if ((member->status == CLAN_NEW) && (time(NULL) - member->join_time > prefs_get_clan_newer_time() * 3600))
	    {
		member->status = CLAN_PEON;
		member->modified = 1;
	    }
	    if (member->modified)
	    {
		uid = account_get_uid(member->memberacc);
		snprintf(query, sizeof(query), "SELECT count(*) FROM %sclanmember WHERE "SQL_UID_FIELD"='%u'",  tab_prefix, uid);
		if ((result = sql->query_res(query)) != NULL)
		{
		    row = sql->fetch_row(result);
		    if (row == NULL || row[0] == NULL)
		    {
			sql->free_result(result);
			eventlog(eventlog_level_error, __FUNCTION__, "got NULL count");
			return -1;
		    }
		    num = atol(row[0]);
		    sql->free_result(result);
		    if (num < 1)
			snprintf(query, sizeof(query), "INSERT INTO %sclanmember (cid, "SQL_UID_FIELD", status, join_time) VALUES('%u', '%u', '%d', '%u')", tab_prefix, clan->clanid, uid, member->status, (unsigned) member->join_time);
		    else
			snprintf(query, sizeof(query), "UPDATE %sclanmember SET cid='%u', status='%d', join_time='%u' WHERE "SQL_UID_FIELD"='%u'", tab_prefix, clan->clanid, member->status, (unsigned) member->join_time, uid);
		    if (sql->query(query) < 0)
		    {
			eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
			return -1;
		    }
		} else
		{
		    eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
		    return -1;
		}
		member->modified = 0;
	    }
	}
    } else
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
	return -1;
    }

    return 0;
}

extern int sql_remove_clan(int clantag)
{
    t_sql_res *result;
    t_sql_row *row;

    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    snprintf(query, sizeof(query), "SELECT cid FROM %sclan WHERE short = '%d'", tab_prefix, clantag);
    if (!(result = sql->query_res(query)))
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error query db (query:\"%s\")", query);
	return -1;
    }

    if (sql->num_rows(result) != 1)
    {
	sql->free_result(result);
	return -1;		/*clan not found or found more than 1 */
    }

    if ((row = sql->fetch_row(result)))
    {
	unsigned int cid = atoi(row[0]);
	snprintf(query, sizeof(query), "DELETE FROM %sclanmember WHERE cid='%u'", tab_prefix, cid);
	if (sql->query(query) != 0)
	    return -1;
	snprintf(query, sizeof(query), "DELETE FROM %sclan WHERE cid='%u'", tab_prefix, cid);
	if (sql->query(query) != 0)
	    return -1;
    }

    sql->free_result(result);

    return 0;
}

extern int sql_remove_clanmember(int uid)
{
    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    snprintf(query, sizeof(query), "DELETE FROM %sclanmember WHERE "SQL_UID_FIELD"='%u'", tab_prefix, uid);
    if (sql->query(query) != 0)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
	return -1;
    }

    return 0;
}

extern int sql_load_teams(t_load_teams_func cb)
{
    t_sql_res *result;
    t_sql_row *row;
    t_team *team;
    int i;

    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    if (cb == NULL)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "get NULL callback");
	return -1;
    }

    snprintf(query, sizeof(query), "SELECT teamid, size, clienttag, lastgame, member1, member2, member3, member4, wins,losses, xp, level, rank FROM %sarrangedteam WHERE teamid > 0", tab_prefix);
    if ((result = sql->query_res(query)) != NULL)
    {
	if (sql->num_rows(result) < 1)
	{
	    sql->free_result(result);
	    return 0;		/* empty team list */
	}

	while ((row = sql->fetch_row(result)) != NULL)
	{
	    if (row[0] == NULL)
	    {
		eventlog(eventlog_level_error, __FUNCTION__, "got NULL teamid from db");
		continue;
	    }

	    team = xmalloc(sizeof(t_team));

	    if (!(team->teamid = atoi(row[0])))
	    {
		eventlog(eventlog_level_error, __FUNCTION__, "got bad teamid");
		sql->free_result(result);
		return -1;
	    }

	    team->size = atoi(row[1]);
	    team->clienttag=tag_str_to_uint(row[2]);
	    team->lastgame = strtoul(row[3],NULL,10);
	    team->teammembers[0] = strtoul(row[4],NULL,10);
	    team->teammembers[1] = strtoul(row[5],NULL,10);
	    team->teammembers[2] = strtoul(row[6],NULL,10);
	    team->teammembers[3] = strtoul(row[7],NULL,10);
	    
	    for (i=0; i<MAX_TEAMSIZE;i++)
	    {
	       if (i<team->size)
	       {
		    if ((team->teammembers[i]==0))
		    {
	    		eventlog(eventlog_level_error,__FUNCTION__,"invalid team data: too few members");
	    		free((void *)team);
	    		goto load_team_failure;
		    }
	       }
	       else
	       {
	   	    if ((team->teammembers[i]!=0))
		    {
	    		eventlog(eventlog_level_error,__FUNCTION__,"invalid team data: too many members");
	    		free((void *)team);
	    		goto load_team_failure;
		    }

	       }
	       team->members[i] = NULL;
	    }
	
	    team->wins = atoi(row[8]);
	    team->losses = atoi(row[9]);
	    team->xp = atoi(row[10]);
	    team->level = atoi(row[11]);
	    team->rank = atoi(row[12]);

	    eventlog(eventlog_level_trace,__FUNCTION__,"succesfully loaded team %u",team->teamid);
	    cb(team);
	    load_team_failure:
	    ;    
	}

	sql->free_result(result);
    } else
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error query db (query:\"%s\")", query);
	return -1;
    }
    return 0;
}

extern int sql_write_team(void *data)
{
    t_sql_res *result;
    t_sql_row *row;
    t_team *team = (t_team *) data;
    int num;

    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    snprintf(query, sizeof(query), "SELECT count(*) FROM %sarrangedteam WHERE teamid='%u'", tab_prefix, team->teamid);
    if ((result = sql->query_res(query)) != NULL)
    {
	row = sql->fetch_row(result);
	if (row == NULL || row[0] == NULL)
	{
	    sql->free_result(result);
	    eventlog(eventlog_level_error, __FUNCTION__, "got NULL count");
	    return -1;
	}
	num = atol(row[0]);
	sql->free_result(result);
	if (num < 1)
	    snprintf(query, sizeof(query), "INSERT INTO %sarrangedteam (teamid, size, clienttag, lastgame, member1, member2, member3, member4, wins,losses, xp, level, rank) VALUES('%u', '%c', '%s', '%u', '%u', '%u', '%u', '%u', '%d', '%d', '%d', '%d', '%d')", tab_prefix,  team->teamid, team->size + '0', clienttag_uint_to_str(team->clienttag),(unsigned int)team->lastgame, team->teammembers[0], team->teammembers[1], team->teammembers[2], team->teammembers[3], team->wins, team->losses, team->xp, team->level, team->rank);
	else
	    snprintf(query, sizeof(query), "UPDATE %sarrangedteam SET size='%c', clienttag='%s', lastgame='%u', member1='%u', member2='%u', member3='%u', member4='%u', wins='%d', losses='%d', xp='%d', level='%d', rank='%d' WHERE teamid='%u'", tab_prefix, team->size + '0', clienttag_uint_to_str(team->clienttag),(unsigned int)team->lastgame, team->teammembers[0], team->teammembers[1], team->teammembers[2], team->teammembers[3], team->wins, team->losses, team->xp, team->level, team->rank, team->teamid);
	if (sql->query(query) < 0)
	{
	    eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
	    return -1;
	}
    } else
    {
	eventlog(eventlog_level_error, __FUNCTION__, "error trying query: \"%s\"", query);
	return -1;
    }

    return 0;
}

extern int sql_remove_team(unsigned int teamid)
{
    if (!sql)
    {
	eventlog(eventlog_level_error, __FUNCTION__, "sql layer not initilized");
	return -1;
    }

    snprintf(query, sizeof(query), "DELETE FROM %sarrangedteam WHERE teamid='%u'", tab_prefix, teamid);
    if (sql->query(query) != 0)
        return -1;

    return 0;
}

#endif				/* WITH_SQL */