[Maxips2]

Introduction

Hello everyone,

First of all I would like to say that this is the first time I release a plugin and work I've done with C++ in particular, so
please go easy on me with the comments.

This plugin provides some high level functions to communicate with MySQL and run queries on it.
At this point it's somewhat basic but I think it's useful for most of the queries you would normally run on an average SA-MP server database.

It is built to work with BlueG's MySQL plugin, with newer versions ( tested with R38, should work fine with R39 ).
That means that it can run unthreaded queries, threaded queries and parallel queries with the ability to pass
the result to a specified callback.

Installation

Like any other plugin, place the querybuilder.dll (or .so, will be compiled later hopefully) inside the plugins folder in your main SA-MP server directory.
If the plugins folder does not exist, create it.

Go to the server.cfg file and add 'querybuilder' to the plugins line.

place the querybuilder.inc in your pawn/include folder and include it in your gamemode/filterscript (below a_mysql).
Should look something like this:

pawn Код:

#include <a_samp>
#include <a_mysql>
#include <querybuilder>

Definitions

pawn Код:

QB_MAX_FIELD_LEN
QB_MAX_ENTRY_LEN
QB_MAX_SELECT_FIELDS_LEN

Enumerator

pawn Код:

enum e_QB_QUERY_TYPE
{
    query_type_default,
    query_type_threaded,
    query_type_parallel
}

Natives & Use

The plugin provides natives that will help you build insert, delete, update and select queries.
Most importantly your queries should be safe from SQL Injection since they are being escaped right before they are sent.

In addition to the high level functions, there are still two functions you can use to build your own queries with this.
The advantage of using those functions instead of the regular ones is the automatic escaping, and not really worrying about the size of the query (you are limited with PAWN).

Here's a list of all the natives this plugin provides:

Updating

pawn Код:

native Query:QueryBuilder_Update(table[], condition[] = "", connectionHandle = 1)
native QueryBuilder_UpdateString(Query:queryhandle, field[], str[])
native QueryBuilder_UpdateInt(Query:queryhandle, field[], value)
native QueryBuilder_UpdateFloat(Query:queryhandle, field[], Float:value)
native QueryBuilder_MultiUpdate(Query:queryhandle, fmat[], {Float,_}:...)

Inserting

pawn Код:

native Query:QueryBuilder_Insert(connectionHandle, table[], fmat[], {Float,_}:...)

Deleting

pawn Код:

native Query:QueryBuilder_Delete(table[], condition[] = "", connectionHandle = 1))

Selecting

pawn Код:

native Query:QueryBuilder_Select(const table[], const fields[] = "", const condition[] = "", connectionHandle = 1)

Misc.

pawn Код:

native Query:QueryBuilder_Build(connectionHandle = 1)
native QueryBuilder_Query(Query:queryhandle, const query[])
native Cache:QueryBuilder_Finish(Query:queryhandle, e_QB_QUERY_TYPE: query_type, bool: use_cache = true, callback[] = "", format[] = "", {Float,_}:...)
native QueryBuilder_QueryLen(Query:queryhandle)
native QueryBuilder_PrintQuery(Query:queryhandle)
native QueryBuilder_GetQuery(Query:queryhandle, dest[], maxlength = sizeof(dest))

Internal Use

pawn Код:

native _QB_Invoke_GetAddresses()
native _QB_SetMaxLengths(field, entry, fields)

Usage Concept

The idea is quite simple, initiate a query using one of the natives which return a value with Query tag and work with it.
Once you are done, call QueryBuilder_Finish with the parameters that suits your needs and there you go.
All queries must be finished using QueryBuilder_Finish.

Examples

Consider the following table in some database:
we'll call it players.

id	name	spawnx	spawny	spawnz	score
int(11)	varchar(24)	float	float	float	int(11)

• Updating

To update we must first call QueryBuilder_Update native and specify which table we wish to update, condition if necessary and connection handle if we wish to work with a specific connection handle (most users won't find it relevant, but it's there).

Initiating an update query:

pawn Код:

new condition[32];
format(condition, sizeof(condition), "id=%d", GetPlayerSQLId(playerid));

new Query:query = QueryBuilder_Update("players", condition);

Now that we got a query handle to work with it, we can update the fields we want:

pawn Код:

new strName[24];
GetPlayerName(playerid, strName, sizeof(strName));

QueryBuilder_UpdateString(query, "name", strName);
QueryBuilder_UpdateFloat(query, "spawnx", 0.0);
QueryBuilder_UpdateFloat(query, "spawny", 0.0);
QueryBuilder_UpdateFloat(query, "spawnz", 0.0);
QueryBuilder_UpdateInt(query, "score", GetPlayerScore(playerid));

Alternatively, we can use QueryBuilder_MultiUpdate for simplicity.
Equivalent code to the one above:

pawn Код:

new strName[24];
GetPlayerName(playerid, strName, sizeof(strName));

QueryBuilder_MultiUpdate(query, "sfffi",
    "name", strName,
    "spawnx", 0.0,
    "spawny", 0.0,
    "spawnz", 0.0,
    "score", GetPlayerScore(playerid));

QueryBuilder_MultiUpdate recives key-value pairs after the static arguments, which should be your field&value.

Note: when using QueryBuilder_MultiUpdate, the format specifiers MUST match the variables passed to the function or else there will be problems.

Here is a little table with the specifier and their meaning:

Specifier	Meaning
i,d	decimal (integer)
f	float
s	string

Now that we got our fields updated, all we need to do is send the query.

pawn Код:

QueryBuilder_Finish(query, query_type_threaded, false, "OnPlayerDataSaved", "i", playerid);

Filling the parameters this way will allow the query to run on a different thread (mysql_tquery)
and pass the result to OnPlayerDataSaved with an extra parameter, playerid, without caching.

Note: When using query_type_default (unthreaded, mysql_query) the native will return a Cache handle.

• Inserting

Now that we are more familiar with the functions and the way it all works, here's an example how to insert.
First we must initiate the insert query.

pawn Код:

new strName[24];
GetPlayerName(playerid, strName, sizeof(strName));

new Query:query = QueryBuilder_Insert(1, "players", "sfffi",
    "name", strName,
    "spawnx", 0.0,
    "spawny", 0.0,
    "spawnz", 0.0,
    "score", 0);

Note that the insert native already accepts key-value pairs at once.
Sending the query:

pawn Код:

QueryBuilder_UpdateFinish(query, query_type_threaded, true, "OnAccountCreated", "i", playerid);

Note that since it's an insert queriy it's better to enable caching and get the insert id:

pawn Код:

public OnAccountCreated(playerid)
{
    new insert_id = cache_insert_id();
    //...
}

• Deleting

As always, initiate the query:

pawn Код:

new condition[32];
format(condition, sizeof(condition), "id=%d", GetPlayerSQLId(playerid));

new Query:query = QueryBuilder_Delete("players", condition);

Note: not specifying condition in QueryBuilder_Delete will delete the table!

Send the query:

pawn Код:

QueryBuilder_Finish(query, query_type_threaded, false, "OnPlayerDeleted", "i", playerid);

• Selecting

As always, initiate the query:

pawn Код:

new condition[32];
new fields[] = "name, score";
format(condition, sizeof(condition), "id=%d", GetPlayerSQLId(playerid));

new Query:query = QueryBuilder_Select("players", fields, condition);

Note: not specifying condition in QueryBuilder_Select will select the entire table!
Note: fields must be separated by comma(,). Not specifying fields will select the entire row

Send the query:

pawn Код:

QueryBuilder_Finish(query, query_type_threaded, true, "OnPlayerLoad", "i", playerid);

And eventually handle all the loading in the callback using cache functions.

• Free Query

This section is a little bit different since the following functions will allow you to run whichever queries you want with no restrictions and conditions.

QueryBuilder_Query - runs a query on a given handle.
If no query is available you can initiate a blank one using QueryBuilder_Build which will give you a free query handle
to work with.

Bugs

I currently don't know about any bug, I have run many tests but you never know.
Unfortunately I didn't really get any help testing (hence this is beta, I would like to hear some feedback from users).
With that said, if you are using this and you do notice a bug please contact me (this thread or just PM me).

Credits

Incognito - Invoke.h & Invoke.cpp

Links

BlueG's MySQL Plugin
Compiled binaries (.dll only atm, sorry) and .inc file
Source Code

[LeaveMe]

Woaaah! Nice work. Don't give up with it!
Now even noobs can do SQL :P
Can I ask you to add something on it ?

[Maxips2]

Quote:

Originally Posted by LeaveMe Woaaah! Nice work. Don't give up with it! Now even noobs can do SQL :P Can I ask you to add something on it ?

Thanks, it's basically high level so there isn't much SQL knowledge involved in using this, but I do recommend learning a little bit SQL.

I welcome suggestions, you may post them here or PM me.

[Patrick]

Quote:

Originally Posted by Maxips2 Thanks, it's basically high level so there isn't much SQL knowledge involved in using this, but I do recommend learning a little bit SQL. I welcome suggestions, you may post them here or PM me.

Looks nice, much simplified but I won't rely on the plugin at the moment since its BETA, might be unstable nonetheless you have made a good effort on producing this.

Would be also nice if it allows decimals such as %.2f instead of %f

[Maxips2]

Quote:

Originally Posted by Patrick_ Looks nice, much simplified but I won't rely on the plugin at the moment since its BETA, might be unstable nonetheless you have made a good effort on producing this. Would be also nice if it allows decimals such as %.2f instead of %f

I completely understand, I mentioned that it's BETA because I didn't get any feedback on stability.
With all of my tests I didn't have any problems but as just a single person testing it, it's hard to be very reliable.

If anyone's interested in doing some further testing with this plugin and post some feedback I would really appreciate it.

About the decimals: there isn't any formatting done, just specifying the type of the value, otherwise the plugin has no way to know which type of value has been passed.

[iRaiDeN]

Good work Max, I responded to you at before.

[Niko_boy]

Good efforts indeed. and good luck with next updates, hope to see them soon!

[Maxips2]

Thanks. I hope to see some feedback so I know what to improve.