[Plugin] [REL] MySQL Plugin (Google Project)

[BlueG]

MySQL Plugin

27/01/2013:
Due to a lack of time, as well as interest, I have decided to open a google project site. I have removed the download links some days ago, because I was thinking about a good solution of how to let this plugin "alive". I would appreciate if some scripters/developers out there are going to continue the project with new features or important bug fixes. Some people seem to face bugs and crashes all the time, while others have been using it for ages without any problems. I have to mention that I'm not going to update the download links (for newer versions) in this thread, because the google project site will be the new placeholder. Once again, anyone is welcome to work on this plugin. If anyone is interested in working or rather continuing the plugin, feel free to contact me via google or the SA-MP forums and I will be able to add you as a contributor/committer on the project site. You will find downloads for R5-R7 below.

BlueG

Project home
Latest changes
Source Code

How to install:


Move mysql.dll (windows) or mysql.so (linux) to your 'plugins/' directory. On windows you'll have to download the mysql library (libmysql.dll, see downloads) and move it to your main server directory. The server configuration (server.cfg) has to be edited aswell.

• Windows

  plugins mysql

• Linux

  plugins mysql.so

PAWN Scripting: (a_mysql.inc)

• Native functions: (All parameters in squared brackets are optional)
• * = R5, R6 (supports threaded and non-threaded scripts)
• * = R7 (supports only threaded scripts)
• Caching guide - Click!
  
  
  • mysql_affected_rows( [connectionHandle = 1] ) **
  • mysql_close( [connectionHandle = 1] ) **
    • Make sure you call this on OnGameModeExit()/OnFilterscriptExit()
  • mysql_connect( const host[], const user[], const database[], const password[] ) *
  • mysql_connect( const host[], const user[], const database[], const password[] [, port = 3306] ) *
    • Returns a connection handle you can use for multiple connections
  • mysql_debug( enable = 1 ) **
    • Enables the debug modus and creates a log file named mysql.log
  • mysql_errno( [connectionHandle = 1] ) **
  • mysql_fetch_int( [connectionHandle = 1] ) *
  • mysql_fetch_field_row( string[], const fieldname[] [,connectionHandle = 1] ) **
  • mysql_fetch_float( &Float:result [,connectionHandle = 1] ) *
  • mysql_fetch_row_format( string[], const delimiter[] = "|" [,connectionHandle = 1] ) **
  • mysql_field_count( [connectionHandle = 1] ) **
  • mysql_format( connectionHandle, output[], format[], {Float,_}:... ) * (works on R6-2 aswell)
    

    Supported formats: (including precisions)
    - %e (escapes a string at once, no mysql_real_escape_string() needed)
    - %s (string)
    - %d / %i (integer)
    - %f (float)
    - %x (hex)
    - %b (binary)
    
    new szDestination[100];
    mysql_format(connectionHandle, szDestination, "SELECT * FROM `%s` WHERE `bar` = '%e' AND `foobar` = '%.2f' LIMIT %d", "foobar", "escape'me\"please", 1.2345, 1337);
    // the variable 'szDestination' contains now the formatted query (including the escaped string)
    mysql_query(szDestination);

  • mysql_free_result( [connectionHandle = 1] ) **
  • mysql_get_charset( destination[] [,connectionHandle = 1] ) **
  • mysql_insert_id( [connectionHandle = 1] ) **
  • mysql_num_rows( [connectionHandle = 1] ) **
  • mysql_num_fields( [connectionHandle = 1] ) **
  • mysql_ping( [connectionHandle = 1] ) **
    • Returns true (1) if the connection is alive, else (-1)
  • mysql_query( query[] [,resultid = (-1), extraid = (-1), connectionHandle = 1] ) *
    • Setting result id to something else than (-1), triggers the OnQueryFinish callback
  • mysql_query_callback( index, query[], callback[] [, extraid = (-1), connectionHandle = 1] ) **
    • Allows you to create custom callbacks for better structured coding (See sample_script.pwn below)
  • mysql_real_escape_string( const source[], destination[] [,connectionHandle = 1] ) **
  • mysql_reconnect( [connectionHandle = 1] ) **
  • mysql_reload( [connectionHandle = 1] ) **
  • mysql_retrieve_row( [connectionHandle = 1] ) **
    • Returns true (1) incase there are still rows to retrieve, else false (0)
  • mysql_set_charset( charset[] [,connectionHandle = 1] ) **
  • mysql_stat( const destination[] [,connectionHandle = 1] ) **
  • mysql_store_result( [connectionHandle = 1] ) **
  • mysql_warning_count( [connectionHandle = 1] ) **
  • cache_get_row( row, idx, dest[] [, connectionHandle = 1] ) *
  • cache_get_field( field_index, dest[] [, connectionHandle = 1] ) *
  • cache_get_data( &num_rows, &num_fields [, connectionHandle = 1] ) *
  • cache_get_field_content( row, const field_name[], dest[] [, connectionHandle = 1] ) *
  • enable_mutex( bool:enable ) *
    • Disable mutexes incase you experience random crashes. (seems to happen on some linux distros only)
  • mysql_function_query( connectionHandle, query[], bool:cache, callback[], format[], {Float,_}:... ) *
    

    mysql_function_query(connectionHandle, "UPDATE `foo` SET `bar` = 1", false, "CustomResponse", "dsf", 1337, "string", 1.5634);
    
    public CustomResponse(integer, string[], Float:float)
    {
        printf("Query successful executed. Data: %d | %s | %f", integer, string, float);
        return 1;
    }

• Pawn Callback:
  • OnQueryFinish( query[], resultid, extraid, connectionHandle ) * - Sample usage (updated)
    • This is just called if you used the 'resultid' parameter in the query function
  • OnQueryError( errorid, error[], resultid, extraid, callback[], query[], connectionHandle ) *
  • OnQueryError( errorid, error[], callback[], query[], connectionHandle ) *

Compiling Notes:

• Windows: Open the project file with Microsoft Visual C++ and click the build button. (MySQL Visual Studio/C++ is required of course)
• Linux: Install gcc, gpp & mysql-client and type "make" in the source files folder.

Downloads: (for newer updates, use the google project site above!)

NOTE: DON'T USE VERSION R7 OR ABOVE IF YOU HAVE NO IDEA WHAT THREADING MEANS OR HOW IT WORKS. YOU MIGHT HARM YOUR SERVER WHEN USING IT NOT PROPERLY.

• Windows: (requires .NET Framework)
  • R7: Server plugin (VS9)
  • R6: Server plugin (VS9)
  • R5: Server plugin (VS9)
  • libmysql.dll
• Linux: (x86 (32-bit) version is cross compatible)
  • R7: Ubuntu, x86 (compiled on Ubuntu)
  • R6: CentOS, Debian 5, x86 (compiled on CentOS), x86 (compiled on Debian5)
  • R5: CentOS, Debian 5, x86
  • libmysqlclient.so.15 - Getting this error.. 'libmysqlclient.so.15: cannot open shared [..]' ? - Read this
• Scripting:
  • R7: a_mysql.inc, Sample Script (shows how to use the cache_*() functions), Blank Gamemode (uses the cache method)
  • R5, R6: a_mysql.inc, Sample PAWN script
  • Archive: PHP class, Modified sample script (by nemesis) (only >= R5 compatible)
• Source: (Cross-Compatible)
  • R7: Source code
  • R6: Source code
  • R5: Source code

Thanks to:

• Chaprnks, Gehaktbal, JaTochNietDan, LS:RP Tester team, Webflori (Testing)
• AndreT (Testing/Cache Tutorial)
• JernejL (Testing/Suggestions)
• krisk (Testing/Suggestions)
• Kye (Coding support)
• Mow (Compiling/Testing/Hosting)
• nemesis (Testing)
• Sergei (Testing/Suggestions/Wiki documentation)
• xxmitsu (Testing/Compiling)

If you have any questions, post them here!

[JaTochNietDan]

Good job GStylezzz

[Bardokas]

Need something better to get values. It would be great to have mysql_get_field() or c_strtok() like in [RAZ]ADreNaLiNe-DJ plugin are.

[BlueG]

Originally Posted by Bardokas

Need something better to get values. It would be great to have mysql_get_field() or c_strtok() like in [RAZ]ADreNaLiNe-DJ plugin are.

There is already a strtok function in PAWN.'mysql_fetch_row' returns like the following format if you selected more than one row in the query:

row1 row2 row3

Now you can just use strtok to split it in tokens.Also 'mysql_get_field' is probably a good idea for the next version.

[Bardokas]

I know strtok function in PAWN, but in C i think it will be faster?

[Meester Proper]

hey

does the memory deallocation work here correctly?

[Balon]

I think so. The code is much cleaner here and I hadn't any problems with this plugin on Debian 4.0 ETCH.

[Extremo]

Somebody is finally adding some more stuff to mysql, great job!

[Yaheli_Faro]

Do I have to know PHP to make a page that shows user stats online?

[JaTochNietDan]

Originally Posted by Yaheli

Do I have to know PHP to make a page that shows user stats online?

Yes, quite limited PHP though, I thought it was hard at first but after going through other Control Panels code its actually quite easy going to PHP from some PAWN knowledge.