Bulk query - Printable Version

Bulk query - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: Bulk query (/showthread.php?tid=418636)

Bulk query - Misiur - 25.02.2013

pawn Код:

stock NS-><EndSession(pid)> {
    new tmp[64];
    mysql_format(dbhandle, tmp, "UPDATE `%s` SET session_end = NOW() WHERE id = %d", dbtables[TB_SESSIONS], GetPVarInt(pid, "session_id"));
    mysql_function_query(dbhandle, tmp, false, "", "");
    
    //Kill session
    SetPVarInt(pid, "session_end", 0);
    SetPVarInt(pid, "session_start", 0);
    
    return 1;
}

This is my function killing session for single user. But when I kill server, there can be even 500 queries at once, so that's not good.

pawn Код:

stock NS-><EndAllSessions()> {
    //No players connected
    if(!Iter_Count(Player)) return 1;
    #define QLEN (511)
    new tmp[QLEN];
    
    format(tmp, sizeof tmp, "UPDATE `%s` SET session_end = NOW() WHERE id IN (", dbtables[TB_SESSIONS]);
    new j = strlen(tmp), last = Iter_Last(Player), len;
    foreach(new pid : Player) {
        if(GetPVarInt(pid, "session_start")) {
            //Id is bigint(20)
            new uid[21];
            //User id as a string
            valstr(uid, GetPVarInt(pid, "uid"));
            len = strlen(uid);
            new bool:isLast = pid == last;
            //Current index + id length + comma separator >= string length - ')'
            if(j + len + (isLast ? 0 : 1) >= QLEN - 1) return NS-><EndAllSessions()>;
            j += len;
            strcat(tmp, uid);
            if(!isLast) {
                tmp[++j] = ',';
            }
            
            //Kill session
            SetPVarInt(pid, "session_start", 0);
            SetPVarInt(pid, "session_end", 0);
        }
    }
    #undef QLEN
    tmp[j] = ')';
    mysql_function_query(dbhandle, tmp, false, "", "");
    return 1;
}

Can I optimise this any more?

Re: Bulk query - Misiur - 25.02.2013

I assumed that using built-in dynamic pvars is better than chunky enum-arrays. Are they that bad?

Re: Bulk query - Vince - 25.02.2013

You could set the session_end field to 0 when the player connects. Then, when you perform a server restart, you can just update everything with a single query since you now have a 'where' clause you can use.

Re: Bulk query - Misiur - 25.02.2013

Session is created when player connects, but there is case where session in database has no session_end. If there weren't I'd just search for session_end IS NULL. Otherwise here's final function:

pawn Код:

stock NS-><EndAllSessions()> {
    //No players connected
    if(!Iter_Count(Player)) return 1;
    #define QLEN (511)
    new tmp[QLEN + 1];
     
    format(tmp, sizeof tmp, "UPDATE `%s` SET session_end = NOW() WHERE id IN (", dbtables[TB_USERS]);
    new j = strlen(tmp), last = Iter_First(Player), len, pid, bool:isFirst;
    foreach(pid : Player) {
        if(!Player[pid][session_start]) continue;
        //Id is bigint(20)
        new uid[21];
        //User id as a string
        valstr(uid, pid);
        len = strlen(uid);
        isFirst = pid == last;
        //Current index + id length + comma separator >= string length - ')'
        if(j + len + (isFirst ? 0 : 1) >= QLEN) break;
        if(!isFirst) {
            tmp[j++] = ',';
        }
        j += len;
        strcat(tmp, uid);
    }
    #undef QLEN
    tmp[j] = ')';
    tmp[j+1] = EOS;
    //mysql_function_query(dbhandle, tmp, false, "", "");
    return pid == Iter_Last(Player) ? 1 : NS-><EndAllSessions()>;
}