[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)
+- 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)
AW: MySQL & PostgreSQL Plugin - Nanory - 22.07.2013
Doesn't work.
Re: AW: MySQL & PostgreSQL Plugin - Maxips2 - 24.07.2013
Quote:
|
Originally Posted by Nanory
 "r" stands for "Result-ID".
sql_query returns the result, but i can't give OnPlayerLogin the result id, if sql_query has to be executed first. This is the reason, why i have to write down two format parameters but only one variable. Later I use the result id ("queryid") in OnPlayerLogin |
Example:
pawn Code:
foward MySQLCallback(Result: sqlresult);
public MySQLCallback(Result: sqlresult)
{
// Do something with the result here...
}
// Somewhere in your script where the query should be executed:
sql_query(connection, query, QUERY_CACHED | QUERY_THREADED, "MySQLCallback", "r");
Great release, after using it for a little bit I have to say that its really good.
I would like to see some more documentation on this plugin however.
Good work
EDIT: I actually wanted to ask a question about sql_wait.
What does it actually do? prevent from any other queries to be executed, or hang the server? (till all activity is over)
Re: MySQL & PostgreSQL Plugin - Djole1337 - 24.07.2013
I have to say, so far (1~ month since I am using this plugin) I had no crash or any problem unlike with BlueG's plugin. Good work indeed.
Re: MySQL & PostgreSQL Plugin - BlackBank - 26.07.2013
For people that still have problems or errors on Debian (Like i had with v2.3).
I have recompiled it on Debian 64-bit, and it works great on my debian server (The MYSQL Static version):
http://blackbanks.net/mysql_static.so.tar.gz
Re: MySQL & PostgreSQL Plugin - Dan.. - 27.07.2013
Quote:
|
Originally Posted by BlackBank3
For people that still have problems or errors on Debian (Like i had with v2.3).
I have recompiled it on Debian 64-bit, and it works great on my debian server (The MYSQL Static version): http://blackbanks.net/mysql_static.so.tar.gz |
AW: MySQL & PostgreSQL Plugin - maddinat0r - 27.07.2013
Quote:
|
Originally Posted by Dan..
[...]I've done some more tests and it looks like this is still the fastest and most feature-rich plugin out.
|
Quote:
|
BlueG's: - y_inline support - visual logging (HTML log) - multi-threading - ORM-like system (R33+) Dan's: - unthreaded queries - PostgreSQL support |
Speed tests:
Quote:
|
Windows (4x3.4GHz, logging disabled) ------- Only executing queries: v2.0: 3186 ms v2.4: 2404 ms R25: 3396 ms R26: 3152 ms R27: 3057 ms R28: 2609 ms R33: 1752 ms //not sure about this result Getting data with field names (sql_get_field_assoc/cache_get_field_content) + _int & _float versions: v2.0: 9902 ms v2.4: 6610 ms R25: 9562 ms R26: 6722 ms R27: 5595 ms R28: 6544 ms R33: 5447 ms Getting data with field indices (sql_get_field/cache_get_row) + _int & _float versions: v2.0: 7191 ms v2.4: 4597 ms R25: 6127 ms R26: 4739 ms R27: 3717 ms R28: 3706 ms R33: 3358 ms |
Re: MySQL & PostgreSQL Plugin - BlackBank - 27.07.2013
Quote:
|
Originally Posted by Dan..
I've already released version 2.4, which was built on CentOS 5 to make sure nobody encounters any problems with missing libraries. I've done some more tests and it looks like this is still the fastest and most feature-rich plugin out.
|
AW: MySQL & PostgreSQL Plugin - AlexLS95 - 27.07.2013
In Version 2.3 I had some problems with connection loss and that the server freezes sometimes when he should execute a query. The server didn't crash but he freezes and the cpu usage is at 100%.
Here is the sql_log.txt when it's starting.
pawn Code:
....
[20:52:04][debug] Natives::sql_query: Query executed (query->id = 8818, query->error = 0). No callback found!
[20:52:04][debug] Natives::sql_free_result: Freeing query (query->id = 8818)...
[20:52:08][debug] Natives::sql_query: Scheduling query (query->id = 8819, query->query = SELECT rcl_players.skin, rcl_houses.key1, rcl_houses.posX, rcl_houses.posY, rcl_houses.posZ FROM rcl_houses LEFT JOIN rcl_players ON(rcl_houses.key1 = rcl_players.name) WHERE posInt = 0 AND posVWorld = 0 AND key1 <> '-' AND key1 <> 'Immobilienfirma' AND houseStat = 3 AND (maxKeys = 1 OR maxKeys = 2 OR maxKeys = 3) AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'StaffBankSF' AND key1 <> 'SellerFuelSpinybed' AND key1 <> 'SellerFuelEmerIsle' AND key1 <> 'SellerFuelRedsWest' AND key1 <> 'SellerFuelLasVentu' AND key1 <> 'SellerFuelStrip' AND key1 <> 'SellerFuelFortCars' AND key1 <> 'SellerFuelBoneCoun' AND key1 <> 'SellerFuelElQuebra' AND key1 <> 'SellerFuelTierRoba' AND key1 <> 'SellerFuelMontgome' AND key1 <> 'SellerFuelDillimor' AND key1 <> 'SellerFuelFliCount' AND key1 <> 'SellerFuelMulholla' AND key1 <> 'SellerFuelIdewood' AND key1 <> 'SellerFuelBayside' AND key1 <> 'SellerFuelAirSF' AND key1 <> 'SellerFuelWhetston' AND key1 <> 'SellerFuelAnglePin' AND key1 <> 'SellerFuelJuniHoll' AND key1 <> 'SellerFuelEastBas' AND key1 <> 'SellerFuelGarcia' AND key1 <> 'SellerFuelDoherty' AND key1 <> 'SellerItemSF2' AND key1 <> 'SellerItemSF1' AND key1 <> 'SellerDonutSF' AND key1 <> 'SellerCnBAnglePine' AND key1 <> 'SellerCnBOcansFlat' AND key1 <> 'SellerCnBDowntown' AND key1 <> 'SellerWSPEsplNorth' AND key1 <> 'SellerWSPFinancial' AND key1 <> 'SellerBSCBGSF' AND key1 <> 'SellerBSDowntown' AND key1 <> 'SellerBSJuniHill' AND key1 <> 'SellerBSGarcia' AND key1 <> 'SellerKebabBay' AND key1 <> 'SellerAmmuSF' AND key1 <> 'PatientEMS', query->callback = SQL_LoadTaxiPassenger) for execution...
[20:52:08][debug] worker[1]: Executing query (query->id = 8819, query->query = SELECT rcl_players.skin, rcl_houses.key1, rcl_houses.posX, rcl_houses.posY, rcl_houses.posZ FROM rcl_houses LEFT JOIN rcl_players ON(rcl_houses.key1 = rcl_players.name) WHERE posInt = 0 AND posVWorld = 0 AND key1 <> '-' AND key1 <> 'Immobilienfirma' AND houseStat = 3 AND (maxKeys = 1 OR maxKeys = 2 OR maxKeys = 3) AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'XXXXX' AND key1 <> 'StaffBankSF' AND key1 <> 'SellerFuelSpinybed' AND key1 <> 'SellerFuelEmerIsle' AND key1 <> 'SellerFuelRedsWest' AND key1 <> 'SellerFuelLasVentu' AND key1 <> 'SellerFuelStrip' AND key1 <> 'SellerFuelFortCars' AND key1 <> 'SellerFuelBoneCoun' AND key1 <> 'SellerFuelElQuebra' AND key1 <> 'SellerFuelTierRoba' AND key1 <> 'SellerFuelMontgome' AND key1 <> 'SellerFuelDillimor' AND key1 <> 'SellerFuelFliCount' AND key1 <> 'SellerFuelMulholla' AND key1 <> 'SellerFuelIdewood' AND key1 <> 'SellerFuelBayside' AND key1 <> 'SellerFuelAirSF' AND key1 <> 'SellerFuelWhetston' AND key1 <> 'SellerFuelAnglePin' AND key1 <> 'SellerFuelJuniHoll' AND key1 <> 'SellerFuelEastBas' AND key1 <> 'SellerFuelGarcia' AND key1 <> 'SellerFuelDoherty' AND key1 <> 'SellerItemSF2' AND key1 <> 'SellerItemSF1' AND key1 <> 'SellerDonutSF' AND key1 <> 'SellerCnBAnglePine' AND key1 <> 'SellerCnBOcansFlat' AND key1 <> 'SellerCnBDowntown' AND key1 <> 'SellerWSPEsplNorth' AND key1 <> 'SellerWSPFinancial' AND key1 <> 'SellerBSCBGSF' AND key1 <> 'SellerBSDowntown' AND key1 <> 'SellerBSJuniHill' AND key1 <> 'SellerBSGarcia' AND key1 <> 'SellerKebabBay' AND key1 <> 'SellerAmmuSF' AND key1 <> 'PatientEMS')...
[20:52:08][debug] Natives::sql_ping: Pinging handler (handler->id = 1)...
[20:52:08][debug] Natives::sql_query: Executing query (query->id = 8820, query->query = DELETE FROM rcl_items WHERE location = '0' AND id = '47' AND type = '0')...
[21:09:30][warning] Natives::sql_debug: Switching the log levels to (0, 2)... <--- I killed and restarted the server with putty
....
pawn Code:
....
[20:52:04] [chat] [UC] [XXXXXXX]: super
[20:52:52] Incoming connection: XXX.XXX.XXX.XXX:55094
[20:52:52] Incoming connection: XXX.XXX.XXX.XXX:60945
[20:52:55] Incoming connection: XXX.XXX.XXX.XXX:2860
[20:52:55] Incoming connection: XXX.XXX.XXX.XXX:63265
[20:53:08] Incoming connection: XXX.XXX.XXX.XXX:56401
[20:53:22] Kicking XXX.XXX.XXX.XXX because they didn't logon to the game.
[20:53:22] Kicking XXX.XXX.XXX.XXX because they didn't logon to the game.
[20:53:22] Incoming connection: XXX.XXX.XXX.XXX:50279
[20:53:25] Kicking XXX.XXX.XXX.XXX because they didn't logon to the game.
[20:53:25] Kicking XXX.XXX.XXX.XXX because they didn't logon to the game.
....
Re: AW: MySQL & PostgreSQL Plugin - BlackBank - 27.07.2013
Quote:
|
Originally Posted by AlexLS95
In Version 2.3 I had some problems with connection loss and that the server freezes sometimes when he should execute a query. The server didn't crash but he freezes and the cpu usage is at 100%.
Here is the sql_log.txt when it's starting. pawn Code:
pawn Code:
|
AW: MySQL & PostgreSQL Plugin - AlexLS95 - 27.07.2013
The Select Query is THREADED and CACHED, so I don't need to free it manually.
I forgot to write that I am using now the 1.0 version again and I don't have such problems anymore.
Re : MySQL & PostgreSQL Plugin - HitnKill - 28.07.2013
Wow this plugin it's very interesting because i have MYSQL R6 and it's too long to update on R7 or more with my all query SELECT
The Query non threaded and cached is more fast than MYSQL R6 ?
I can have more example querys non threaded and cached?
PHP Code:
new Result:vehicles = sql_query(conn, "SELECT * FROM vehicles", QUERY_CACHED);
// using cache, even though this is a non-threaded query ^
// awesome or what?
for (new i = 0, rows = sql_num_rows(vehicles); i != rows; ++i) {
new vehicle[eVehicle];
vehicle[vId] = sql_get_field_assoc_int(vehicles, "id");
vehicle[vModelID] = sql_get_field_assoc_int(vehicles, "modelid");
vehicle[vColor1] = sql_get_field_assoc_int(vehicles, "color1");
vehicle[vColor2] = sql_get_field_assoc_int(vehicles, "color2");
vehicle[vPos][0] = sql_get_field_assoc_float(vehicles, "pos_x");
vehicle[vPos][1] = sql_get_field_assoc_float(vehicles, "pos_y");
vehicle[vPos][2] = sql_get_field_assoc_float(vehicles, "pos_z");
vehicle[vPos][3] = sql_get_field_assoc_float(vehicles, "rot");
sql_next_row(vehicles);
}
sql_free_result(vehicles);
PHP Code:
sql_next_row(vehicles);
Re: Re : MySQL & PostgreSQL Plugin - Dan.. - 30.07.2013
Quote:
|
Originally Posted by Pain123
A feature comparison:
I think your plugin also uses multi-threading, but only with 2+ connections. Speed tests: Your plugin doesn't work on my Linux machine, it says some "clock_gettime" function or something is missing. |
- visual logging (HTML log) - Yeah sure, because a pile of shit (HTML tags) is always better. Let's also take into consideration that there are many browsers that can open a .HTML file that's over 100mbs.
- multi-threading - I lol'd in real life when I read this. The way I handle multi-threading is the real way. Your plugin fetches malformed results. Anyway, you can manually implement a pool in Pawn or wait for me to do it in v2.7.
? ORM-like system (R33+) - proof or gtfo :hurr:
+ unthreaded queries - People don't have to put much effort to convert their old scripts and get rid of bugs. In some cases (especially in OnGameModeInit and OnGameModeExit) unthreaded queries are required.
+ PostgreSQL support - Cool or what?
+ striking resemblance with SQLite api - It comes in handy for users that want to convert their old scripts.
+ multiple result sets
+ proper result handling
+ "live" (a.k.a. non-cached) queries - Especially when a large amount of data has to be loaded.
+ parameters can be pushed by reference - Great, ain't it?
+ many more that I'm not bothered to post
Your plugin might be faster (I still don't believe your results), but mine offers reliability.
P.S. I fixed that clock_gettime() issue. Thanks for the report!
I also did a quich benchmark, output:
Code:
GO!!! (Dan) | now = 45693129 (start) connected | now = 45693134 | now - last = 5 | now - start = 5 scheduled | now = 45693136 | now - last = 2 | now - start = 7 done | now = 45693137 | now - last = 1 | now - start = 8 executed | now = 45702784 | now - last = 9647 | now - start = 9655 ---------------------------------------------------------------------------- GO!!! (BlueG) | now = 45729908 (start) connected | now = 45729913 | now - last = 5 | now - start = 5 scheduled | now = 45729915 | now - last = 2 | now - start = 7 done | now = 45729919 | now - last = 4 | now - start = 11 executed | now = 45742206 | now - last = 12287 | now - start = 12298
Quote:
|
Originally Posted by BlackBank3
The plugin is loading correctly now, but if i use one of the functions in my gamemode, my server crash immediately.
|
Quote:
|
Originally Posted by Avenger™
Still like blueg's
|
Quote:
|
Originally Posted by HitnKill
Wow this plugin it's very interesting because i have MYSQL R6 and it's too long to update on R7 or more with my all query SELECT
The Query non threaded and cached is more fast than MYSQL R6 ? I can have more example querys non threaded and cached? PHP Code:
PHP Code:
|
sql_next_row retrieves the next row.
For those who haven't noticed, I created a FAQ page on Github's Wiki.
Re: MySQL & PostgreSQL Plugin - Maxips2 - 30.07.2013
Any documentation on this plugin?
And another question (I asked before, but seems like it was ignored).
What does sql_wait do? (I know it waits for sql activity to finish, but what does it do to the server?)
Re: MySQL & PostgreSQL Plugin - Dan.. - 30.07.2013
Quote:
|
Originally Posted by Maxips2
Any documentation on this plugin?
And another question (I asked before, but seems like it was ignored). What does sql_wait do? (I know it waits for sql activity to finish, but what does it do to the server?) |
If you are too lazy, skip at the end. There are some useful links, including an example.
sql_wait makes the server sleep until all scheduled queries (of a connection) were executed. It comes in handy in OnGameModeExit when you try to save your information and you must wait for them to finish before the server quits.
Re: MySQL & PostgreSQL Plugin - Gigi-The-Beast - 30.07.2013
Sql_wait is useful if you are closing the server, so it will force the server to wait for all the queries to finish and then shut down.
edit: dan was faster.
Re: MySQL & PostgreSQL Plugin - Maxips2 - 30.07.2013
Quote:
|
Originally Posted by Dan..
https://github.com/udan11/samp-plugi...sked-Questions
If you are too lazy, skip at the end. There are some useful links, including an example. sql_wait makes the server sleep until all scheduled queries (of a connection) were executed. |
Re: MySQL & PostgreSQL Plugin - Dan.. - 30.07.2013
Quote:
|
Originally Posted by Maxips2
So the server is basically hanging?
|
Re: MySQL & PostgreSQL Plugin - Kar - 30.07.2013
Can you update your "Originally Posted by Fetch about one million rows" on R33 release of Pain's plugin?
Reasons: "Plugin rewritten." - udan11 authored a day ago
Also R21 - R33 suffered major updates.
Re: MySQL & PostgreSQL Plugin - BlackBank - 30.07.2013
Nice done Dan, the plugin works great now on my Windows machine, not tested at Linux yet.
Re: MySQL & PostgreSQL Plugin - Dan.. - 31.07.2013
Quote:
|
Originally Posted by Kar
Can you update your "Originally Posted by Fetch about one million rows" on R33 release of Pain's plugin?
Reasons: "Plugin rewritten." - udan11 authored a day ago Also R21 - R33 suffered major updates. |
Код:
GO!!! (Dan) | now = 45693129 (start) connected | now = 45693134 | now - last = 5 | now - start = 5 scheduled | now = 45693136 | now - last = 2 | now - start = 7 done | now = 45693137 | now - last = 1 | now - start = 8 executed | now = 45702784 | now - last = 9647 | now - start = 9655 ---------------------------------------------------------------------------- GO!!! (BlueG) | now = 45729908 (start) connected | now = 45729913 | now - last = 5 | now - start = 5 scheduled | now = 45729915 | now - last = 2 | now - start = 7 done | now = 45729919 | now - last = 4 | now - start = 11 executed | now = 45742206 | now - last = 12287 | now - start = 12298
