[Misiur]

Hello.

I wanted to create an include, but every scripter has to decide how to solve few things himself, so I wrote this tut instead. Also - this was supposed to be little snippet, and turned out to be a monster (in size, and in logic). Prepare for brainmelt, and enjoy!

Look at this simple code:

pawn Код:

enum PData {
    dbID,
    wafflez,
    pancakes[32]
}
new PInfo[MAX_PLAYERS][PData];

public OnGameModeExit() {
    foreach(new pid : Player) {
        SavePlayer(pid);
    }
}

stock SavePlayer(uid) {
    mysql_format(dbhandle, q, "UPDATE `users` SET wafflez = %d, pancakes = '%e' WHERE id = %d", PInfo[pid][wafflez] + 1, PInfo[pid][pancakes], PInfo[pid][dbID]);
    mysql_function_query(dbhandle, q, false, "", "");
}

This is how most of servers currently are saving user data. Works fine, so where's the problem? Well, if there are 500 players connected, you are executing 500 sql queries to database. A lot, ain't it? Queries are really heavy, and executing a lot of them is not a good idea. How to reduce this? Let's take a look at mysql reference http://dev.mysql.com/doc/refman/5.0/en/case.html . What can I do with this?

pawn Код:

UPDATE `users`
    SET wafflez = CASE id
        WHEN PInfo[1][dbID] THEN PInfo[1][wafflez]
        WHEN PInfo[2][dbID] THEN PInfo[2][wafflez]
        WHEN PInfo[3][dbID] THEN PInfo[3][wafflez]
    END,
    title = CASE id
        WHEN PInfo[1][dbID] THEN "PInfo[1][pancakes]"
        WHEN PInfo[2][dbID] THEN "PInfo[2][pancakes]"
        WHEN PInfo[3][dbID] THEN "PInfo[3][pancakes]"
    END
WHERE id IN (1,2,3)

(not an actual query)

Single query can handle a lot of updates at once. Cool, eh? Now, there are some limits like string length, etc.

Firstly, add new define, two indexes, and an array of bools to enum:

pawn Код:

static currentUser@ = 0, currentField@ = 0;

#define FIELDS_NUMBER 2

enum PData {
    dbID,
    wafflez,
    pancakes[32],
    bool:alreadySaved[FIELDS_NUMBER]
}

Indexes will be needed for recuring function, the constant defines how much fields do you want to update (we will update field by field, user by user)

pawn Код:

enum {
    DB_INT,
    DB_STR
}

enum dBInfo {
    fname[32],
    PData:pointer,
    _:type
}

static const dbprops[FIELDS_NUMBER][dBInfo] = {
    { "wafflez",  PData:wafflez,  DB_INT },
    { "pancakes", PData:pancakes, DB_STR }
};

We need to define types of data for query builder (tagof is black magic for me (inside enum everything supposedly returns the enum tag, as it should I guess)). For now INT and STR will be sufficient. You might want to add date functions later on.

For testing purposes I have database with fields id, wafflez (int), and pancakes (varchar).

The dBInfo contains informations about your fields: name, pointer to PData enum, and type of data.

pawn Код:

stock ResetPlayersStates() {
    currentUser@ = 0;
    currentField@ = 0;
    for(new pid = 0; pid < MAX_PLAYERS; ++pid) {
        if(IsPlayerConnected(pid)) {
            for(new j = 0; j < FIELDS_NUMBER; ++j) {
                PInfo[pid][alreadySaved][j] ^= PInfo[pid][alreadySaved][j];
            }
        }
    }
}

This function as you can see resets indexes and states for all users. It is used before each clean start of updating.

Quote:

Sidenote: variable ^= variable is exactly the same as variable = false, howerver this is my old asm habit

Now, the grand chunk of shitstorm

pawn Код:

#define MAX_SINGLE 64
stock SaveAllPlayers() {
    #define MAX_QUERY 128
    //Alots are awesome
    #define ALOT    32
    
    #if MAX_QUERY < ALOT || MAX_QUERY < 64
    #error Dont do this, k?
    #endif
    static const where[] = " END WHERE id IN ";
    new query[MAX_QUERY+1], first = 1, ids[ALOT+1], idlen = 0, pdbID = 0, bool:emptyRun = true, tmp[MAX_SINGLE + 1], bool:errorFlag = false;
    format(query, sizeof query, "UPDATE `users` SET `%s` = CASE `id` ", dbprops[currentField@][fname]);
    ids[0] = '(';
    for(; currentUser@ < MAX_PLAYERS; ++currentUser@) {
        if(PInfo[currentUser@][dbID]/*IsPlayerConnected(currentUser@)*/) {
            if(PInfo[currentUser@][alreadySaved][currentField@]) continue;
            //Database id string length
            pdbID = PInfo[currentUser@][dbID];
            idlen = 1;
            while (pdbID > 9) {
                idlen++;
                pdbID /= 10;
            }
            //Check if we are still under ALOT magic barrier
            //Current ids + current id length + comma separator + )
            if(strlen(ids) + idlen + (first ? 0 : 1) + 1 > ALOT) break;
            
            if(!first) strcat(ids, ",");
            format(ids, sizeof ids, "%s%d", ids, pdbID);
            
            tmp[0] = EOS;
            strcat(tmp, BuildSet(currentUser@, dbprops[currentField@]));
            
            //Check if we are still under MAX_QUERY barrier
            //Current query + where + all ids
            if(strlen(query) + strlen(where) + strlen(ids) + strlen(tmp) + (first ? 0 : 1) > MAX_QUERY) {
                if(first) errorFlag = true;
                break;
            }
            
            if(!first) strcat(query, ",");
            strcat(query, tmp);
            
            PInfo[currentUser@][alreadySaved][currentField@] = true;
            
            if(first) {
                first ^= first;
                emptyRun ^= emptyRun;
            }
        }
    }
    
    if(errorFlag) {
        //This shouldn't happen, but when the query isn't long enough even to fit one record, do eet
        print("GODDAMNIT");
        return 0;
    }
    if(!emptyRun) {
        strcat(ids, ")");
        strcat(query, where);
        strcat(query, ids);
    } else {
        if(currentField@ < FIELDS_NUMBER) ++currentField@;
        currentUser@ = 0;
        query[0] = EOS;
    }
    mysql_function_query(dbhandle, query, false, "", "");
    printf("Query: '%s'", query);
    
    #undef MAX_QUERY
    #undef ALOT
    
    return emptyRun && currentField@ == FIELDS_NUMBER ? 1 : SaveAllPlayers();
}

stock BuildSet(pid, variable[]) {
    new q[MAX_SINGLE + 1];
    switch(variable[type]) {
        case DB_INT:
            mysql_format(dbhandle, q, "WHEN %d THEN %d", PInfo[pid][dbID], PInfo[pid][PData:variable[pointer]]);
        default:
            //includes str
            mysql_format(dbhandle, q, "WHEN %d THEN '%e'", PInfo[pid][dbID], PInfo[pid][PData:variable[pointer]]);
    }
    //Add more cases for special tags, or handle it in some other way
    return q;
}

Let's get through this piece by piece:

pawn Код:

#define MAX_SINGLE 64
stock SaveAllPlayers() {
    #define MAX_QUERY 512
    //Alots are awesome
    #define ALOT    128
    
    #if MAX_QUERY < ALOT || MAX_QUERY < 64
    #error Dont do this, k?
    #endif
    static const where[] = " END WHERE id IN ";
    new query[MAX_QUERY+1], first = 1, ids[ALOT+1], idlen = 0, pdbID = 0, bool:emptyRun = true, tmp[MAX_SINGLE + 1], bool:errorFlag = false;
    format(query, sizeof query, "UPDATE `users` SET `%s` = CASE `id` ", dbprops[currentField@][fname]);

We are defining limits for strings, max_single is maximum length of single "WHEN THEN", alot is max length of ids list, max_query is max query. If max_query is shorter than alot, or max_query is shorter than 64 I'll rise compiler error because I like it.

where variable holds chunk between values and ids, we need it here because it's length will determine if there is any more empty buffer space.
first - bool used as int flagging if this is first real iteration
ids - string containing all ids used
idlen - tmp variable used to store length of current id
pdbID - copy of PInfo[currentUser@][pdbID] for determining id string length without actual converting to string
emptyRun - important variable flagging end of single field run, or whole function run. If there is no more things to update, finishes recursion
errorFlag - raised only when the buffer isn't long enough to hold even single update statement

Then we set the field name to be updated, and off we go

pawn Код:

ids[0] = '(';
    for(; currentUser@ < MAX_PLAYERS; ++currentUser@) {
        if(IsPlayerConnected(currentUser@)) {
            if(PInfo[currentUser@][alreadySaved][currentField@]) continue;
            //Database id string length
            pdbID = PInfo[currentUser@][dbID];
            idlen = 1;
            while (pdbID > 9) {
                idlen++;
                pdbID /= 10;
            }
            //Check if we are still under ALOT magic barrier
            //Current ids + current id length + comma separator + )
            if(strlen(ids) + idlen + (first ? 0 : 1) + 1 > ALOT) break;
            
            if(!first) strcat(ids, ",");
            format(ids, sizeof ids, "%s%d", ids, pdbID);
            
            tmp[0] = EOS;
            strcat(tmp, BuildSet(currentUser@, dbprops[currentField@]));

ids string will have final form something like "(1,2,3,4,5)", so we need the brace now.

The function is recursive, so we hold in alreadySaved for each field if the user was already saved. If he was, skip iteration.
Now we get the length of id to pdbID.

First check if we are still inside buffer length, then we add "," if this isn't first id (otherwise it'd be something like "(,1,2,3)").
tmp[0] = EOS resets the tmp variable (actually that's cheating but whateaver) - I need strcpy here, not strcat
BuildSet function will be explained in a while - basically it just builds the when-then part of query and loads data.

pawn Код:

//Check if we are still under MAX_QUERY barrier
            //Current query + where + all ids
            if(strlen(query) + strlen(where) + strlen(ids) + strlen(tmp) + (first ? 0 : 1) > MAX_QUERY) {
                if(first) errorFlag = true;
                break;
            }
            
            if(!first) strcat(query, ",");
            strcat(query, tmp);
            
            PInfo[currentUser@][alreadySaved][currentField@] = true;
            
            if(first) {
                first ^= first;
                emptyRun ^= emptyRun;
            }
        }
    }

Second buffer check, comma between when-thens, and setting alreadySaved for this field to true. If this is first iteration, then we mark that it isn't first any longer, and that the run isn't empty.

pawn Код:

if(errorFlag) {
        //This shouldn't happen, but when the query isn't long enough even to fit one record, do eet
        print("GODDAMNIT");
        return 0;
    }
    if(!emptyRun) {
        strcat(ids, ")");
        strcat(query, where);
        strcat(query, ids);
        mysql_function_query(dbhandle, query, false, "", "");
        printf("Query: '%s'", query);
    } else {
        if(currentField@ < FIELDS_NUMBER) ++currentField@;
        currentUser@ = 0;
        printf("Empty run, next field XOR end");
    }   
    
    #undef MAX_QUERY
    #undef ALOT
    
    return emptyRun && currentField@ == FIELDS_NUMBER ? 1 : SaveAllPlayers();
}

If error flag was raised, we panic. If the run wasn't empty we add final brace to ids, and we build final query and execute it. Otherwise we raise the current field counter, and reset the user loop. Then we get rid of definitions, aaand there is where magic happens.

If the run is empty and we already iterated all of the fields, we can finish the function. Otherwise we run it again (probably with incremented field index, or same field again, but for rest of the users).

pawn Код:

stock BuildSet(pid, variable[]) {
    new q[MAX_SINGLE + 1];
    switch(variable[type]) {
        case DB_INT:
            mysql_format(dbhandle, q, "WHEN %d THEN %d", PInfo[pid][dbID], PInfo[pid][PData:variable[pointer]]);
        default:
            //includes str
            mysql_format(dbhandle, q, "WHEN %d THEN '%e'", PInfo[pid][dbID], PInfo[pid][PData:variable[pointer]]);
    }
    //Add more cases for special tags, or handle it in some other way
    return q;
}

We create WHEN THEN for corresponding data type (DB_STR or DB_INT for now only), and return it.

Quote:

Sidenote: there are two ways you can create the query - field by field, or row by row. I don't know about internal database implementations, but I guess that row by row will be faster becuase it won't have to search for all the ids each time. Currently this function handles only one field at a time. However coding for row by row would be twice as long (at least for me), so I'll just leave this here.

Now: how to use this?

pawn Код:

public OnGameModeExit() {
    ResetPlayersStates();
    SaveAllPlayers();
    return 1;
}

Fin

Cool, we went from ~5 lines of code to... ALOT



You can YSI iterators to make stuff nicer, but you should get the idea behind reducing queries number. Oh, there might be some typos because I'm just writing it live in diffrent syntax for myself. Also remember that for few users this won't make a diffrence (or what's worse - this code execution will be slower than actual queries). In high level languages all of this would've been ~10 lines of code, but whatever. I bet ****** will facepalm when he sees this tutorial, but you can only learn by experimenting This was fun

GLHF

[iggy1]

Also a good idea to wrap many update queries in a transaction. This vastly reduces execution time.