[Plugin] MySQL & PostgreSQL Plugin

[Plugin] MySQL & PostgreSQL Plugin - 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: Plugin Development (https://sampforum.blast.hk/forumdisplay.php?fid=18)
+--- Thread: [Plugin] MySQL & PostgreSQL Plugin (/showthread.php?tid=429464)

Pages: 1 2 3 4 5 6 7 8 9

Re: MySQL Plugin - Dan.. - 12.05.2013

Quote:

Originally Posted by ******

I just tried out 2.0 and it seems you HAVE to have both DBs installed - is there any way to make that not the case as it seems very inefficient for using just one.

Oh, I forgot to include the actual libpq. I was too lazy to compile it at the I started working on the PostgreSQL integration and I used a cheap workaround. Thank you for reporting, I'll fix it asap.

Quote:

Edit: Also, I thought about your suggestion to make yid PRIMARY_KEY and realised that I could do that if it was in a separate table, so did so. Thanks.

Or... you can have two columns in the same table:
- ID - the ID of the account
- Owner - the owner's ID; if this account is the "main-account", it is 0

Re: MySQL Plugin - Dan.. - 13.05.2013

Quote:

Originally Posted by ******

OK, I have a few new points.

1) You could really do with more Mutexes IMHO. Currently if "ProcessQueryThread" is running, "sql_query" (and probably other natives, I've not looked) will hang until all pending queries are complete, making multiple threads almost pointless in that case. Or instead of mutexes a lock-free single producer/single consumer list implementation (I'm know these exist). In short, the mutexes are held by a thread for FAR too long.

Edit: http://www.boost.org/doc/libs/1_53_0.../lockfree.html

2) On a similar note, even non-threaded queries currently obtain the Mutex and thus wait for pending operations when there's no need for them to enter a protected section at all.

Yes. I'm aware of that. I've been looking for a thread-safe map, but failed to find any. Boost seems to provide only stacks and queues.

Quote:

Originally Posted by ******

3) It would be VERY helpful if "Unload" completed pending queries instead of dropping them, for example to save user data on server end.

When the server crashes the queries are discarded anyway. I was thinking on creating a new native (something like mysql_wait) which would block the execution of the script until all sql queries have been executed. (Very useful if you use threaded queries to save data in OnGameModeExit)

Re: MySQL Plugin - Dan.. - 13.05.2013

Quote:

Originally Posted by ******

If you care, I've started updating some of the code. I split the queries in to two containers - your map for "is_valid" checks and referencing them by ID, and a second thread-safe queue for passing queries between threads for concurrent ones. I've removed most of the locks as they protected reads from the only thread that can modify some of the data, so there wasn't any point protecting them. It also switched to using "const" methods where possible as C++11 defines those as thread read safe.

I looked at your fork a bit. I saw that you don't lock the mutex anymore in sql_query and ProcessQueryThread which might crash your server as they run in separate threads (Pawn thread and its own thread) and they both edit the query map.

Re: MySQL Plugin - Dan.. - 13.05.2013

Quote:

Originally Posted by ******

Yeah, it's no where near complete yet (I've not even tested compiling).

In my opinion, using a thread-safe map is still the best choice. This way, I'll have to use mutexes only for MySQL lib.

Re: MySQL Plugin v1.0 STABLE - jordy.kiesebrink - 13.05.2013

Dear Dan...

I'm working on a server with your SQL plugin but I have a problem with getting rows from the database.
It always return 0 rows but there are rows in the databse for sure. I updates them with a command and used the QUERY_THREADED function for it.

Could you help me solving my bug?, This is my current function which is checking if a player is muted.

pawn Код:

forward CheckMuted(playerid);
public CheckMuted(playerid) {
    new query[250];
    format(query, sizeof(query), "SELECT user_name FROM users WHERE user_id = '%d' AND user_muted = '1'", playersInfo[playerid][pID]);
    new Result:Muted = mysql_query(mysql, query, QUERY_THREADED);
    new rows = mysql_num_rows(Muted);
    printf("[mysql] Mute Query %d %s %d.", playersInfo[playerid][pID], playersInfo[playerid][pUsername], rows);
    if(rows == 1) {
        playersInfo[playerid][pMute] = 1;
        return 1;
    } else {
        return 0;
    }
}

then on my function I created to check if a player is loaded succesfully I call the function to check if the player is muted

pawn Код:

CheckMuted(playerid);

So in the database the value from user_muted is setted to 1 with the command :mute and the pMuted also to 1. then onplayertext it is checking if the pMuted status is 1 and then it rejects the ability to talk:

pawn Код:

public OnPlayerText(playerid, text[]) {
    if(playersInfo[playerid][pMute] != 1) {
        new string[2000];
        format(string, sizeof(string), "{FFFFFF}(%i) %s", playerid, text); /* with player ID in chat */
        SendPlayerMessageToAll(playerid, string);
    } else {
        SendClientMessage(playerid, COLOR_RED, "(Error!) You are muted, You are not allowed to talk");
    }
    return 0;
}

so when a player quits his pMute will be resetted so the playerid wont be stored to prevent bugs with a new player who is using that id:

pawn Код:

playersInfo[playerid][pMute] = 0;

but his user_muted is still on 1 so now when he is loaded it needs to call the function checkmuted and thats working fine but it wont get any rows as confirmed in my log

Код:

[13/05/2013 18:45:15] [mysql] Mute Query 5 Jordy2 0.

so.. Could u help me fixing my problem with getting rows from my database. becouse this is offcurse not the only function what it getting rows..

Thanks in Advance
Kind regarts, Jordy

AW: MySQL Plugin - AlexLS95 - 13.05.2013

You use a Threaded Query, so the result is send to the Callback which you haven't defined in mysql_query. You use it like unthreaded Querys

pawn Код:

new Result:Muted = mysql_query(mysql, query, QUERY_THREADED);

shout be

pawn Код:

new Result:Muted = mysql_query(mysql, query, QUERY_NONE);

pawn Код:

new Result:Muted = mysql_query(mysql, query, QUERY_CACHED);

And you have to use in unthreaded Querys

pawn Код:

mysql_free_result(Muted);

at the end. You should put it after

pawn Код:

new rows = mysql_num_rows(Muted);

Re: AW: MySQL Plugin - jordy.kiesebrink - 13.05.2013

@AlexLS95: Thank you the QUERY_NONE and mysql_free_result was indeed fixing it

I have another question: now I know I needed these functions how can I give a format for each founded row:

I want to make a punishments commando where you can check the punishments from someone or from yourself with the following command:

pawn Код:

COMMAND:punishments(playerid, params[]) {// needs to be fixed
    if(CheckARank(playerid, 2, 4)) {
        new targetid;
        if(!sscanf(params, "U(-1)", targetid)) {
            if(targetid == -1) {
                new query[500], string[128], variable[960], reason[500];
                format(query, sizeof(query), "SELECT * FROM warnings WHERE warning_userid = '%d'", playersInfo[playerid][pID]);
                new Result:Warnings = mysql_query(mysql, query, QUERY_NONE);
                new rows = mysql_num_rows(Warnings);
                mysql_free_result(Warnings);
                mysql_get_field_assoc(Result:Warnings, "warning_reason", reason, sizeof(reason));
                format(string, sizeof(string), "\n%s", reason);
                strcat(variable, string);
                if(rows == 0) return SendClientMessage(playerid, COLOR_RED, "(Error!) No warnings found!");
                if(rows == 1) { format(string, sizeof(string), "%d Punishment in your file", rows); }
                if(rows > 1) { format(string, sizeof(string), "%d Punishments in your file", rows); }
                ShowPlayerDialog(playerid, DIALOG_PUNISHMENTS, DIALOG_STYLE_LIST, string, variable, "Close", "");
            } else {
                new query[500], string[128], variable[960], reason[500];
                format(query, sizeof(query), "SELECT * FROM warnings WHERE warning_userid = '%d'", playersInfo[targetid][pID]);
                new Result:Warnings = mysql_query(mysql, query, QUERY_NONE);
                new rows = mysql_num_rows(Warnings);
                mysql_free_result(Warnings);
                mysql_get_field_assoc(Result:Warnings, "warning_reason", reason, sizeof(reason));
                format(string, sizeof(string), "\n%s", reason);
                strcat(variable, string);
                if(rows == 0) return SendClientMessage(playerid, COLOR_RED, "(Error!) No warnings found!");
                if(rows == 1) { format(string, sizeof(string), "%d Punishment in %s's file", rows, playersInfo[targetid][pUsername]); }
                if(rows > 1) { format(string, sizeof(string), "%d Punishments in %s's file", rows, playersInfo[targetid][pUsername]); }
                ShowPlayerDialog(playerid, DIALOG_PUNISHMENTS, DIALOG_STYLE_LIST, string, variable, "Close", "");
            }
        }
    }
    return 1;
}

The rows are founded are counted and the dialog is perfect but for some reason the reasons won't display? any idea

Re: MySQL Plugin - Dan.. - 14.05.2013

You must call mysql_free_result after any function that is using the result (like mysql_get_field_assoc).

Re: MySQL Plugin - Gamer_Z - 14.05.2013

1) try to avoid mutexes (really, please don't use them), use std::atomic instead or Concurrency::concurrent_queue / concurrent_vector etc.
2) any more "advanced server owner" will probably make his own plugin using ODB (like me :P Ye, making a gamemode with GDK)
Consider adding support for it somehow :P

I freakin' hate making SQL queries. So I don't do it.

Also now I'm thinking of that, why do you use mutexes at all? They're system wide locks, not process wide locks.

Re: MySQL Plugin - Gamer_Z - 14.05.2013

Quote:

Originally Posted by ******

I actually doubt that - I know several owners of several large servers, and they don't. "Advanced server owners" know not to waste time duplicating efforts re-writing things that already exist.

Well if they like creating SQL queries ofcourse, but like I said, I like ODB

I'm okay with the little overhead it adds as long as I don't have to bang my head against the wall to make databases / schemas / relations / SQL queries.

Re: MySQL Plugin - Dan.. - 14.05.2013

Quote:

Originally Posted by Gamer_Z

1) try to avoid mutexes (really, please don't use them), use std::atomic instead or Concurrency::concurrent_queue / concurrent_vector etc.

I have some ideas. I hope I'll implement them soon.

Quote:

Originally Posted by Gamer_Z

2) any more "advanced server owner" will probably make his own plugin using ODB (like me :P Ye, making a gamemode with GDK)
Consider adding support for it somehow :P

That looks interesting. I'll look into.

Re: MySQL Plugin - Gamer_Z - 14.05.2013

Quote:

Originally Posted by Dan..

I have some ideas. I hope I'll implement them soon.

That looks interesting. I'll look into.

have a look at my GPS plugin if you want to see some threading code without locks

Re: MySQL Plugin - jordy.kiesebrink - 14.05.2013

Quote:

Originally Posted by Dan..

You must call mysql_free_result after any function that is using the result (like mysql_get_field_assoc).

Thank you I was doing it before the row was called in the function, dumb.. Anyway thanks.!

Re: MySQL Plugin - Dan.. - 15.05.2013

Quote:

Originally Posted by Gamer_Z

have a look at my GPS plugin if you want to see some threading code without locks

You are using thread-safe containers, a thing which I'm going to do asap I find a thread-safe associative container.

Re: MySQL Plugin - Edvin - 16.05.2013

If you will use "mysql_connect" to connect to a database, you can use also "pgsql_connect" to connect also to that database?

Re: MySQL Plugin - InfiniTy. - 16.05.2013

Quote:

Originally Posted by Edvin

If you will use "mysql_connect" to connect to a database, you can use also "pgsql_connect" to connect also to that database?

mysql_connect is for mysql

pgsql_connect is for postgresql.

Re: MySQL Plugin - Dan.. - 16.05.2013

Quote:

Originally Posted by Edvin

If you will use "mysql_connect" to connect to a database, you can use also "pgsql_connect" to connect also to that database?

sql_connect - connects to both MySQL and PostgreSQL servers
mysql_connect - connects to MySQL servers (only)
pgsql_connect - connects to PostgreSQL servers (only)

However, that feature is in 2.0 and it hasn't been released officially (yet).

Re: MySQL Plugin - fordawinzz - 18.05.2013

I get the file or function not found error , dunno why. I've put the libmysql in my root folder .

@ the plugin actually loades.

Re: MySQL Plugin - Dan.. - 18.05.2013

Use nativechecker or crashdetect and tell me what function is missing exactly. Are you using the mysql.inc file provided in the .zip / .tar.gz file?

Re: MySQL Plugin - fordawinzz - 18.05.2013

Yes, I'm using it.

crashdetect:

Код:

[17:15:38] [debug] Run time error 19: "File or function is not found"
[17:15:38] [debug]  sql_connect
[17:15:38] [debug]  sql_query
[17:15:38] [debug]  sql_num_rows
[17:15:38] [debug]  sql_get_field_assoc_int
[17:15:38] [debug]  sql_get_field_assoc
[17:15:38] [debug] Run time error 19: "File or function is not found"
[17:15:38] [debug]  sql_connect
[17:15:38] [debug]  sql_query
[17:15:38] [debug]  sql_num_rows
[17:15:38] [debug]  sql_get_field_assoc_int
[17:15:38] [debug]  sql_get_field_assoc