[Stats MySQL] Проверка кода, вопросы

[zhorzh78]

Javekson, столбец не float

 

[Javekson]

zhorzh78, Skill FLOAT NOT NULL, \

 

[Dager]

Javekson, SELECT COUNT(*) FROM stats WHERE Skill > 104.61

 

[Javekson]

Dager, нету разницы

 

[CrazyHackGUT]

Ничего не магия. Так работает тип данных FLOAT. У него точность - как у дробовика. MySQL приравнивает у себя внутри там значение 104.61 к 104.6099999. Вы попробуйте сделать поиск по 104.611, и увидите свой любимый ноль.

А если нужна точность - используйте DOUBLE или DECIMAL вместо FLOAT.

 

[Javekson]

CrazyHackGUT, благодарю, это решила проблему.
Как я уже сейчас только понял, что в переменной g_eData[iKiller][PD_SKILL] хранится 6 цифр после запятой, во всяком случае столько он мне вывел. В базу записывает только 4 цифры(столько вижу визуально), когда я ввожу ранг, он считает Skill из базы(где было 4 цифры), но сравнивает с переменной(где 6 цифр), отсюда проблемы и возникали иногда. DOUBLE решил вопрос.

8 Апр 2019

Собственно целиком набросок кода статистики игроков. Функционала практически нету, из всего мне нужен был лишь топ и ранг.
Обновление данных происходит в реалтайме, каждое убийство. Смена/обновление никнейма так-же в реал тайме.

Фантом, сильно не кричи по поводу по поводу cteate table каждую карту )))))

Код прилагаю для проверки, критики. советов и т.д.

#include <amxmodx>
#include <reapi>
#include <sqlx>

#pragma semicolon 1

const INACTIVITY_DAYS                = 7;                // Удалять неактивных игроков через 'n' дней
const SECONDS_IN_DAY                = 86400;                // Количество секунд в одном дне

const QUERY_STRLEN                    = 512;
const PATH_STRLEN                    = 64;
const MESSAGE_STRLEN                = 512;
const TIME_STRLEN                    = 32;
const ELLIPSES_ARG                    = 4;
const AUTHID_STRLEN                    = 24;
const NAME_STRLEN                    = 32;
const MOTD_STRLEN                    = 1536;
const CLASS_STRLEN                    = 8;
const LEVEL_STRLEN                    = 3;
new const TAB[]                        = "^t^t^t";

enum {
    SQL_CREATE,
    SQL_LOAD,
    SQL_INSERT,
    SQL_UPDATE,
    SQL_UPD_NAME,
    SQL_UPD_SEEN,
    SQL_CLEAR,
    SQL_SIZE,
    SQL_RANK,
    SQL_TOP
}

enum _:PLAYER_DATA {
    PD_AUTHID[AUTHID_STRLEN],
    PD_NAME[NAME_STRLEN],
    PD_KILLS,
    PD_DEATHS,
    PD_HEADS,
    Float:PD_SKILL,
    PD_SEEN
}

enum _:SKILL_INFO {
    Float:SI_VALUE,
    SI_LEVEL[LEVEL_STRLEN],
    SI_CLASS[CLASS_STRLEN]
}

new const SKILL_DATA[][SKILL_INFO] = {
    {0.0,        "L-",        "Lm"},
    {60.0,        "L",        "L"},
    {75.0,        "L+",        "Lp"},
    {85.0,        "M-",        "Mm"},
    {100.0,        "M",        "M"},
    {115.0,        "M+",        "Mp"},
    {130.0,        "H-",        "Hm"},
    {140.0,        "H",        "H"},
    {150.0,        "H+",        "Hp"},
    {165.0,        "P-",        "Pm"},
    {180.0,        "P",        "P"},
    {195.0,        "P+",        "Pp"},
    {210.0,        "G",        "G"}
};

new const SQL_HOST[]                = "";
new const SQL_USER[]                = "";
new const SQL_PASSWORD[]            = "";
new const SQL_DATABASE[]            = "";
new const SQL_TABLE[]                = "stats";

new Handle:g_hSQLTuple;
new g_sSQLQuery[QUERY_STRLEN];
new g_iSQLSize;
new g_eData[MAX_PLAYERS + 1][PLAYER_DATA];
new g_sLogsDir[PATH_STRLEN];

public plugin_init() {
    register_plugin("Players Statistics", "1.0", "Javekson");
    RegisterHookChain(RG_CBasePlayer_Killed, "CBasePlayer_Killed", .post = true);
    RegisterHookChain(RG_CBasePlayer_SetClientUserInfoName, "CBasePlayer_SetClientUserInfoName", .post = true);
   
    register_clcmd("say /top", "SQL_Top");
    register_clcmd("say /rank", "SQL_Rank");
}

public plugin_cfg() {
    get_localinfo("amxx_logs", g_sLogsDir, charsmax(g_sLogsDir));
    add(g_sLogsDir, charsmax(g_sLogsDir), "/players_statistics");
    if(!dir_exists(g_sLogsDir)) mkdir(g_sLogsDir);
   
    SQL_Create();
}

public client_putinserver(id) {
    if(is_user_bot(id) || is_user_hltv(id)) {
        return PLUGIN_CONTINUE;
    }
   
    new sAuthID[AUTHID_STRLEN], sName[NAME_STRLEN];
    get_user_authid(id, sAuthID, charsmax(sAuthID));
    get_user_name(id, sName, charsmax(sName));
   
    g_eData[id][PD_AUTHID] = sAuthID;
    g_eData[id][PD_NAME] = sName;
    g_eData[id][PD_KILLS] = 0;
    g_eData[id][PD_DEATHS] = 0;
    g_eData[id][PD_HEADS] = 0;
    g_eData[id][PD_SKILL] = 100.0;
    g_eData[id][PD_SEEN] = get_systime();
   
    SQL_Load(id);
   
    return PLUGIN_CONTINUE;
}

public CBasePlayer_Killed(const iVictim, const iKiller) {
    if(iVictim == iKiller) return HC_CONTINUE;
    if(!is_user_connected(iKiller)) return HC_CONTINUE;
   
    g_eData[iKiller][PD_KILLS]++;
    g_eData[iVictim][PD_DEATHS]++;
   
    if(get_member(iVictim, m_bHeadshotKilled)) {
        g_eData[iKiller][PD_HEADS]++;
    }
   
    new Float:fDelta = 1.0 / (1.0 + floatpower(10.0,(g_eData[iKiller][PD_SKILL] - g_eData[iVictim][PD_SKILL]) / 100.0));
    new Float:fKillerKoeff = (g_eData[iKiller][PD_KILLS] < 100) ? 2.0 : 1.5;
    new Float:fVictimkKoeff = (g_eData[iVictim][PD_KILLS] < 100) ? 2.0 : 1.5;
   
    g_eData[iKiller][PD_SKILL] += (fKillerKoeff * fDelta);
    g_eData[iVictim][PD_SKILL] -= (fVictimkKoeff * fDelta);
   
    SQL_Update(iVictim, iKiller);
   
    return HC_CONTINUE;
}

public CBasePlayer_SetClientUserInfoName(const id, sInfoBuffer[], sNewName[]) {
    formatex(g_eData[id][PD_NAME], sizeof(g_eData[][PD_NAME]), "%s", sNewName);
    SQL_UpdName(id);
}

SQL_Create() {
    g_hSQLTuple = SQL_MakeDbTuple(SQL_HOST, SQL_USER, SQL_PASSWORD, SQL_DATABASE);
   
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "CREATE TABLE IF NOT EXISTS %s( \
        AuthID            VARCHAR(24)        NOT NULL, \
        Name            VARCHAR(64)        NOT NULL, \
        Kills            INT(11)            NOT NULL, \
        Deaths            INT(11)            NOT NULL, \
        Heads            INT(11)            NOT NULL, \
        Skill            DOUBLE            NOT NULL, \
        Seen            INT(11)            NOT NULL, \
        PRIMARY KEY(AuthID), INDEX(Skill))",
        SQL_TABLE
    );
   
    new aData[1]; aData[0] = SQL_CREATE;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_Load(const id) {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "SELECT * \
        FROM %s \
        WHERE AuthID = '%s'",
        SQL_TABLE,
        g_eData[id][PD_AUTHID]
    );
   
    new aData[2]; aData[0] = SQL_LOAD; aData[1] = id;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_Insert(const id) {
    new sQuoteStringName[NAME_STRLEN * 2];
    SQL_QuoteString(Empty_Handle, sQuoteStringName, charsmax(sQuoteStringName), g_eData[id][PD_NAME]);
   
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "INSERT INTO %s ( \
        AuthID, \
        Name, \
        Kills, \
        Deaths, \
        Heads, \
        Skill, \
        Seen) \
        \
        VALUES ( \
        '%s', \
        '%s', \
        %d, \
        %d, \
        %d, \
        %f, \
        %d)",
        SQL_TABLE,
        g_eData[id][PD_AUTHID],
        sQuoteStringName,
        g_eData[id][PD_KILLS],
        g_eData[id][PD_DEATHS],
        g_eData[id][PD_HEADS],
        g_eData[id][PD_SKILL],
        g_eData[id][PD_SEEN]
    );
   
    g_iSQLSize++;
   
    new aData[1]; aData[0] = SQL_INSERT;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_Update(const iVictim, const iKiller) {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "UPDATE %s SET \
        Kills = %d, \
        Deaths = %d, \
        Heads = %d, \
        Skill = %f \
        WHERE AuthID = '%s'; \
        \
        UPDATE %s SET \
        Kills = %d, \
        Deaths = %d, \
        Heads = %d, \
        Skill = %f \
        WHERE AuthID = '%s'",
        SQL_TABLE,
        g_eData[iKiller][PD_KILLS],
        g_eData[iKiller][PD_DEATHS],
        g_eData[iKiller][PD_HEADS],
        g_eData[iKiller][PD_SKILL],
        g_eData[iKiller][PD_AUTHID],
       
        SQL_TABLE,
        g_eData[iVictim][PD_KILLS],
        g_eData[iVictim][PD_DEATHS],
        g_eData[iVictim][PD_HEADS],
        g_eData[iVictim][PD_SKILL],
        g_eData[iVictim][PD_AUTHID]
    );
   
    new aData[1]; aData[0] = SQL_UPDATE;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_UpdName(const id) {
    new sQuoteStringName[NAME_STRLEN * 2];
    SQL_QuoteString(Empty_Handle, sQuoteStringName, charsmax(sQuoteStringName), g_eData[id][PD_NAME]);
   
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "UPDATE %s SET \
        Name = '%s' \
        WHERE AuthID = '%s'",
        SQL_TABLE,
        sQuoteStringName,
        g_eData[id][PD_AUTHID]
    );
   
    new aData[1]; aData[0] = SQL_UPD_NAME;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_UpdSeen(const id) {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "UPDATE %s SET \
        Seen = %d \
        WHERE AuthID = '%s'",
        SQL_TABLE,
        g_eData[id][PD_SEEN],
        g_eData[id][PD_AUTHID]
    );
   
    new aData[1]; aData[0] = SQL_UPD_SEEN;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_Clear() {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "DELETE FROM %s \
        WHERE Seen < %d",
        SQL_TABLE,
        get_systime() - INACTIVITY_DAYS * SECONDS_IN_DAY
    );
   
    new aData[1]; aData[0] = SQL_CLEAR;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

SQL_Size() {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "SELECT COUNT(*) \
        FROM %s",
        SQL_TABLE
    );
   
    new aData[1]; aData[0] = SQL_SIZE;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

public SQL_Rank(const id) {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "SELECT COUNT(*) \
        FROM %s \
        WHERE Skill > %f \
        ORDER BY Skill DESC",
        SQL_TABLE,
        g_eData[id][PD_SKILL]
    );
   
    new aData[2]; aData[0] = SQL_RANK; aData[1] = id;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

public SQL_Top(const id) {
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "SELECT * \
        FROM %s \
        ORDER BY Skill DESC \
        LIMIT 15",
        SQL_TABLE
    );
   
    new aData[2]; aData[0] = SQL_TOP; aData[1] = id;
    SQL_ThreadQuery(g_hSQLTuple, "QueryHandler", g_sSQLQuery, aData, sizeof(aData));
}

public QueryHandler(iFailState, Handle:hQuery, sError[], iErrNum, aData[], iSize, Float:fQueueTime) {
    if(iFailState != TQUERY_SUCCESS) {
        SQL_GetQueryString(hQuery, g_sSQLQuery, charsmax(g_sSQLQuery));
        Logging(g_sLogsDir, "stats_error_", "^"[Query]: %s^"", g_sSQLQuery);
        Logging(g_sLogsDir, "stats_error_", "^"[Error]: %s^"", sError);
        return PLUGIN_CONTINUE;
    }
   
    switch(aData[0]) {
        case SQL_CREATE: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_CREATE] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            SQL_Clear();
        }
       
        case SQL_LOAD: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_LOAD] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            new id = aData[1];
           
            if(SQL_MoreResults(hQuery)) {
                g_eData[id][PD_KILLS] = SQL_ReadResult(hQuery, 2);
                g_eData[id][PD_DEATHS] = SQL_ReadResult(hQuery, 3);
                g_eData[id][PD_HEADS] = SQL_ReadResult(hQuery, 4);
                SQL_ReadResult(hQuery, 5, g_eData[id][PD_SKILL]);
               
                new sName[NAME_STRLEN];
                SQL_ReadResult(hQuery, 1, sName, charsmax(sName));
               
                if(!equal(g_eData[id][PD_NAME], sName)) {
                    SQL_UpdName(id);
                }
               
                SQL_UpdSeen(id);
               
                return PLUGIN_CONTINUE;
            }
           
            SQL_Insert(id);
           
            return PLUGIN_CONTINUE;
        }
       
        case SQL_INSERT: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_INSERT] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
        }
       
        case SQL_UPDATE: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_UPDATE] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
        }
       
        case SQL_UPD_NAME: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_NAME] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
        }
       
        case SQL_UPD_SEEN: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_SEEN] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
        }
       
        case SQL_CLEAR: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_CLEAR] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            SQL_Size();
        }
       
        case SQL_SIZE: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_SIZE] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            if(SQL_MoreResults(hQuery)) {
                g_iSQLSize = SQL_ReadResult(hQuery, 0);
            }
        }
       
        case SQL_RANK: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL_RANK] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            new id = aData[1];
           
            new iRank = SQL_ReadResult(hQuery, 0) + 1;
           
            new iPtr = SkillLevel(g_eData[id][PD_SKILL]);
           
            client_print_color(id, print_team_default,
                "Вы занимаете %d из %d (Убито %d. Смертей %d. В голову %d. Скилл %s (%.2f))",
                iRank,
                g_iSQLSize,
                g_eData[id][PD_KILLS],
                g_eData[id][PD_DEATHS],
                g_eData[id][PD_HEADS],
                SKILL_DATA[iPtr][SI_LEVEL],
                g_eData[id][PD_SKILL]
            );
        }
       
        case SQL_TOP: {
            Logging(g_sLogsDir, "stats_inform_", "^"[SQL__TOP] %s Queue Time %s %f^"", TAB, TAB, fQueueTime);
           
            new sMotd[MOTD_STRLEN], sClass[CLASS_STRLEN], iLen, iLenTemp, iPos;
           
            iLen = formatex(sMotd[iLen], charsmax(sMotd),
                "<meta charset=utf-8> \
                <link href=^"http://gmforce.ru/stats/stats.css^" rel=stylesheet type=text/css> \
                <body> \
                <div>Топ лучших игроков</div></br> \
                <table> \
                <tr> \
                <th width=3%%># \
                <th width=37%%>Игрок \
                <th width=12%%>Убийств \
                <th width=12%%>Смертей \
                <th width=12%%>В голову \
                <th width=12%%>Скилл \
                <th width=12%%>Уровень"
            );
           
            while(SQL_MoreResults(hQuery)) {
                new sName[NAME_STRLEN * 3], iKills, iDeaths, iHeads, Float:fSkill;
               
                SQL_ReadResult(hQuery, 1, sName, charsmax(sName));
               
                replace(sName, charsmax(sName), "<", "&lt;");
               
                iKills = SQL_ReadResult(hQuery, 2);
                iDeaths = SQL_ReadResult(hQuery, 3);
                iHeads = SQL_ReadResult(hQuery, 4);
                SQL_ReadResult(hQuery, 5, fSkill);
               
                iPos++;
                sClass = (iPos % 2) ? "class=b" : "";
               
                new iPtr = SkillLevel(fSkill);
               
                iLen += formatex(sMotd[iLen], charsmax(sMotd) - iLen,
                    "<tr %s><td>%d<td>%s<td>%d<td>%d<td>%d<td>%.0f<td class=%s>",
                    sClass, iPos, sName, iKills, iDeaths, iHeads, fSkill, SKILL_DATA[iPtr][SI_CLASS]
                );
               
                if(iLen >= MOTD_STRLEN) {
                    sMotd[iLenTemp] = 0;
                    break;
                }
                iLenTemp = iLen;
               
                SQL_NextRow(hQuery);
            }
           
            new id = aData[1];
            show_motd(id, sMotd, "Топ игроков");
        }
    }
    return PLUGIN_CONTINUE;
}

stock SkillLevel(Float:iSkill) {
    new iPtr;
    for(new i; i < sizeof(SKILL_DATA); i++) {
        if(iSkill >= SKILL_DATA[i][SI_VALUE]) {
            iPtr = i;
        }
    }
    return iPtr;
}

stock Logging(const sLogsDir[], const sFileName[], const sMessage[], any:...) {
    new sFmtMsg[MESSAGE_STRLEN], sTime[TIME_STRLEN], sLogFile[PATH_STRLEN + 32], iFileID;
    vformat(sFmtMsg, charsmax(sFmtMsg), sMessage, ELLIPSES_ARG);
    get_time("%d.%m.%Y.log", sTime, charsmax(sTime));
    formatex(sLogFile, charsmax(sLogFile), "%s/%s%s", sLogsDir, sFileName, sTime);
    iFileID = fopen(sLogFile, "at");
    get_time("%d.%m.%Y - %H:%M:%S", sTime, charsmax(sTime));
    fprintf(iFileID, "^"%s^" %s^n", sTime, sFmtMsg);
    fclose(iFileID);
}