[Plugin] MySQL & PostgreSQL Plugin
#1

MySQL & PostgreSQL Plugin
This plugin provides a set of natives used to communicate with SQL servers like MySQL and PostgreSQL. This plugin is fastest and most feature-rich plugin out yet (better than BlueG, StrickenKid, ADreNaLiNe-DJ). I'm waiting for your bug reports and suggestions. Thank you!

Building
  • Windows: Open the samp-plugin-sql.sln, choose the configuration that fits you best (Release, Release - MySQL or Release - PgSQL) and hit the `Build` button. Binaries will be located in the bin/ folder.
  • Linux: Type in terminal make MYSQL=1 (for MySQL), make PGSQL=1 (for PostgreSQL) or make MYSQL=1 PGSQL=1 (for both). Binaries will be located in the bin/ folder.
Installing
  • Move the binaries in the plugins/ folder.
    • On Windows, you probably need to copy libmysql.dll, libpq.dll and other libraries required (depending on what SQL engines are you using) in the root of the server.
    • On Linux, you probably need to copy libmysql.so, libpq.so and other libraries required (depending on what SQL engines are you using) in the root of the server.
  • Write the name of the binary at the end of the line containing plugins from server.cfg. On Linux it must contain the extension too!
  • Add the #include <sql> at the top of the script which is going to use the plugin.
Notes
  1. This plugin supports multiple connections to different or same servers, MySQL or PostgreSQL.
  2. This plugin supports both threaded and non-threaded queries, also cached and on-demand ("live", w/e) queries.
  3. This plugin's natives are very much alike with the SQLite natives. Code can be easily ported.
  4. It uses same natives for both MySQL and PostgreSQL. You can switch very easily between engines (as long as the queries are compatible.
Benchmarks
Quote:
Originally Posted by Fetch about one million rows
This plugin v2.0 - 12178 ms
BlueG's plugin R21 - 53357 ms
For downloads please check the website for anything else check the project on Github.com
Reply
#2

Very interesting, gonna test it.
Reply
#3

I'm assuming this plugin doesn't support threaded queries? or that's what the callback parameter in mysql_query for?
Reply
#4

Quote:
Originally Posted by Maxips2
View Post
I'm assuming this plugin doesn't support threaded queries? or that's what the callback parameter in mysql_query for?
It actually supports threaded queries.
Reply
#5

Suggestions:
- Multiple Result Sets
- Support to Stored Procedures
- NoSQL
- Prepared Statements
Reply
#6

Quote:
Originally Posted by Djole1337
View Post
It actually supports threaded queries.
It actually supports both threaded and non-threaded queries.

Quote:
Originally Posted by steki.
View Post
Suggestions:
- Multiple Result Sets
- Support to Stored Procedures
- NoSQL
- Prepared Statements
It already supports multiple result sets! I didn't test any stored procedures, but they should work. NoSQL support is going to be added once the MySQL code of this plugin is stable. I'm working on prepared statements.
Reply
#7

Looks interesting, might switch to this in the future.
Thanks for sharing, nice work!
Reply
#8

Once I see some speed tests for this plugin vs BlueG's, I'll be more interested.
Reply
#9

Quote:
Originally Posted by RealCop228
View Post
Once I see some speed tests for this plugin vs BlueG's, I'll be more interested.
I hope I'll have enough time tomorrow to post them.
Reply
#10

so why should anyone use this over BlueG's MySQL plugin?
Reply
#11

Quote:
Originally Posted by Kar
View Post
so why should anyone use this over BlueG's MySQL plugin?
For now, it only has a few features over BlueG's plugin (most important: multiple return sets, supporting both threaded and non-threaded queries), but I'll keep updating it. BlueG's code became too ugly for my taste.
Reply
#12

Quote:
Originally Posted by Dan..
View Post
For now, it only has a few features over BlueG's plugin (most important: multiple return sets, supporting both threaded and non-threaded queries), but I'll keep updating it. BlueG's code became too ugly for my taste.
Actually, BlueG's plugin does support both threaded and non-threaded queries. However, once you use R7 or above, you lose the ability to use non-threaded queries. He probably did this to increase performance of non-threaded queries. Quite frankly, people not using threaded queries should re-consider.
Reply
#13

Quote:
Originally Posted by RealCop228
View Post
Actually, BlueG's plugin does support both threaded and non-threaded queries. However, once you use R7 or above, you lose the ability to use non-threaded queries. He probably did this to increase performance of non-threaded queries. Quite frankly, people not using threaded queries should re-consider.
I found very useful non-threaded queries too. Retrieving small amount of information is faster using those queries.
Reply
#14

However good job mateЎЎЎ
Reply
#15

Ok, seems like I have to tell you that this is somehow pissing me off. I have nothing against any plugin release, even if it is MySQL, but the way you have been handling this situation is quite annoying.

In January I decided to stop working on my plugin and you did ask me to continue it at some point. After some small fixes, additions and some code design changes ("java style", as you told me), you seemed to have lost interest. Now out of sudden you release your own plugin without any notice to me, which is the reason why I'm angry.

Quote:

I hope this plugin will be faster and more stable than any other MySQL plugin released

Quote:

BlueG's code became too ugly for my taste.

This seems to be some kind of joke to me, because you are still using snippets of my plugin. The only difference (in the way how the plugin handles the MySQL stuff) I can see so far is, that you decided to use std::maps<> instead of vectors/queues, but I don't want to get into a further investigation.

All I can say is, fuck off Dan. (I don't care whenever you use parts of my plugin btw.)

BlueG
Reply
#16

The MySQL Plugin by BlueG only has little issues left. Instead of creating an entirely new plugin to test and blah blah. Create stress on yourself. The plugin issues could've just been fixed, making the plugin closer-to-perfection. I can't say perfection and let it stand on it's own of course. I'm gonna continue to use BlueG's plugin regardless of it's crashes to my server. Don't care. It used to crash my server 5-8+ times per day for months but I still continued to use it and still will up to this day. :P
Reply
#17

Quote:
Originally Posted by BlueG
View Post
Ok, seems like I have to tell you that this is somehow pissing me off. I have nothing against any plugin release, even if it is MySQL, but the way you have been handling this situation is quite annoying.

In January I decided to stop working on my plugin and you did ask me to continue it at some point. After some small fixes, additions and some code design changes ("java style", as you told me), you seemed to have lost interest. Now out of sudden you release your own plugin without any notice to me, which is the reason why I'm angry.
I've lost interest because the code was ugly and I felt bad to "correct" your code. I can give you examples of ugly code (it's about 90% of that plugin) if you want.

Quote:
Originally Posted by BlueG
View Post
This seems to be some kind of joke to me, because you are still using snippets of my plugin. The only difference (in the way how the plugin handles the MySQL stuff) I can see so far is, that you decided to use std::maps<> instead of vectors/queues, but I don't want to get into a further investigation.
Actual, the only SIMILARITY is the fact that I'm using your Mutex object and threads design. Everything else was written entirely by me (there might be some more similarities as I've seen your code and you inspired me, but I didn't intend to copy). CRY ME A RIVER, KID!

Quote:
Originally Posted by Y_Less
View Post
1) This has nearly half the number of functions of BlueG's - how are there more features?

2) You say you intend for this to be faster - have you got ANY plans on how to do this? Don't forget that the vast majority of the (lack of) speed in any MySQL plugin comes from MySQL itself. Even if your section of the code is 10x faster than BlueG's - if 99% of the code is internal MySQL stuff common to both then you have only improved the total execution time by 0.9%

3) If you thought the existing code was ugly, why not just clean it up a bit? That's got to be faster than developing (and testing) a whole new one, then getting people to convert.

4) There isn't really such a thing as a "quick" query - even just a "SELECT * WHERE" still has to compile the query and search the entire database. There was a reason non-threaded queries were removed, do you have any numbers, of ways of assessing what is "quick" and not "server hanging".

5) In what world is "mysql_connect" similar to "db_open"? Or the other functions - renaming everything is not my definition of an easy conversion!

6) You also mentioned improved stability - how are you going to assess and prove that too?
1. mysql_get_charset, mysql_set_charset, mysql_reconnect, mysql_reload, mysql_retrieve_row, mysql_stat - these are all the functions this plugin doesn't have. I've never used half of them so they weren't a priority for me. (Firstly, I wrote this plugin for MY server, before started working at BlueG's plugin)
However, my plugin still has multiple result sets and non-threaded queries (even if you don't like them, I find them useful).

2. It's clear that I can't optimize MySQL's api, but I'll try to optimise my code as much as possible. The difference would be narrow, but it would still be there.

3. I felt bad editing his code. I thought it was disrespectful of me.

4. It's quick because it doesn't have to be passed to another thread, execute it and call the callback. It's again, a narrow difference, but it does exists. I'm using non-threaded queries in OnGameModeInit and OnGameModeExit to load and save everything. They look better and I don't care if the server hangs as long as my data is loaded. I had a lazy MySQL server which loaded data from the server AFTER players connected.

5. Not all of them are alike, but most of them: db_close, db_free_result, db_num_rows, db_num_fields, db_field_name, db_query, ~db_get_field (has one extra parameter, `row`), ~db_get_field_assoc (same with `db_get_field`)

6. I'll run some 'hardcore' tests? I've succeeded in crashing BlueG's plugin sending a huge amount of data. I'll try the same with my plugin.

Quote:
Originally Posted by Kar
View Post
It used to crash my server 5-8+ times per day for months but I still continued to use it and still will up to this day. :P
That's what made me write another plugin.

--

You guys feed with hate. That's what I was afraid when I released this plugin.
Reply
#18

Quote:
Originally Posted by Y_Less
View Post
I hope you aren't referring to the non "java style" code (since this isn't Java)!
I don't care if this is not Java. I find Java coding convention clear and I got used to them. I use them even if I write code in C, PHP or Pawn (with little personal edits). You might find them interesting: http://www.oracle.com/technetwork/ja...nv-138413.html

Quote:
Originally Posted by Y_Less
View Post
You might want to check on that - I've found issues with the mutex setup in the past (can't remember what, but it will be in my IRC logs somewhere).
I'll do some tests.

Quote:
Originally Posted by Y_Less
View Post
I asked how you claimed to have more features, you just listed all the ones you don't have...
Quote:
Originally Posted by Dan..
View Post
However, my plugin still has multiple result sets and non-threaded queries (even if you don't like them, I find them useful).
Also, I've listed some (useless imo) features I don't have.

Quote:
Originally Posted by Y_Less
View Post
From what he said, it seems he knew you were editing the plugin and was OK with that. Plus, although his code doesn't have an explicit license, the virality of the GPL used in the mySQL headers makes it GPL compatible so anyone is free to modify the code as they like.
I really didn't care about the license, I just felt bad editing his work.

Quote:
Originally Posted by Y_Less
View Post
That can actually make your server run significantly slower - threads are not inherantly fast OR slow. Someone recently released a threaded command processor which turned out to be slower than a non-threaded version, I believe this would be the opposite.
I have both threaded and non-threaded queries. They are approached in two different ways and everyone is free to use what they like. In some situations, you'll find non-threaded queries more suitable or vice-versa.

Quote:
Originally Posted by Y_Less
View Post
You don't have db_close!
It's mysql_discconect. In the next release I'll make sure every function will have an equivalent to the SQLite natives.
Reply
#19

Any speed tests vs BlueG R7 plugin ?
Reply
#20

Quote:
Originally Posted by Ernis456
View Post
Any speed tests vs BlueG R7 plugin ?
I've posted the results. It seems this plugin is faster.
Reply


Forum Jump:


Users browsing this thread: 12 Guest(s)