[Slice]

Tutorial by FireCat: https://sampforum.blast.hk/showthread.php?pid=2116226#pid2116226

Hey,
This is an include that utilizes SQLite to its full capacity to provide a user database with great simplicity and probably the best possible performance you can get with SQLite.
There's no need for plugins, filterscripts, or loads of other includes. It won't interfere with any other code as long as it's included before any other includes.

NOTE
The latest version should be very stable. If you experience any problems, report them here!

Key features

• FAST
• No SQL knowledge neccessary - BUD will take care of all the communication with SQLite.
• Easy to set up users, and change their information.
• Columns can be added to the database with just one line of code.
• Several variables can be read/written at once with just one function call!
• Whirlpool-compatible. You can, by putting "#define BUD_USE_WHIRLPOOL true" before including BUD, enable using the whirlpool plugins. Recommended.
• BUD::RunQueryEx - formats, escapes, and runs SQLite queries!

Example usage

pawn Код:

new
    userid = BUD::GetNameUID( "Slice" ) // This will get the name's unique ID; it's needed to get/set information.
;

if ( userid != BUD::INVALID_UID )
{
    BUD::MultiSet( userid, "siiif", // string, integer, integer, integer, float
        "email", "slices.em@i.l",
        "money", 5000, // Give Slice $5000!
        "kills", 1337,
        "deaths", -1,
        "exp", 5.0 // Give Slice a head start!
    );

    new email[ 32 ], money, kills, deaths, Float:exp;

    BUD::MultiGet( userid, "s[32]iiif", // string size 32, integer, integer, integer, float
        "email", email,
        "money", money,
        "kills", kills,
        "deaths", deaths,
        "exp", exp
    );
    
    printf( "BUD::MultiGet returned:\nemail: %s\nmoney: %d\nkills: %d\ndeaths: %d\nEXP: %f", email, money, kills, deaths, exp );
}

Functions available
TODO: Write documentation about all these - when (or if) I get Wiki access, I will!

Код:

        BUD::Setting( setting[], value )
bool    BUD::Initialize( )
bool    BUD::Exit( )
        BUD::VerifyColumn( column[], type[, default value ] )
bool    BUD::IsNameRegistered( name[] )
bool    BUD::RegisterName( name[], password[] )
        BUD::UnregisterName( name[] )
bool    BUD::CheckAuth( name[], password[] )
        BUD::GetNameUID( name[] )
Float   BUD::GetFloatEntry( uid, entry[] )
        BUD::GetIntEntry( uid, entry[] )
        BUD::GetStringEntry( uid, entry[], &value[][, size ] )
bool    BUD::MultiGet( uid, type definitions, ( entry, &variable )... )
bool    BUD::MultiSet( uid, type definitions, ( entry, value )... )
bool    BUD::SetIntEntry( uid, entry[], value )
bool    BUD::SetFloatEntry( uid, entry[], Float:value )
bool    BUD::SetStringEntry( uid, entry[], value[][, size ] )

Download
Download the include: bud.inc
Download a small test script: bud-test.pwn

[Zimon95]

Very nice, keep it up! But I prefer MySQL...

[DarkPower]

awesome work

[Slice]

Quote:

Originally Posted by Zimon95 Very nice, keep it up! But I prefer MySQL...

When I'm done with this, I will make something similar, but more powerful, for MySQL. You won't have to change a line of code to switch to the MySQL version (once it's finished) if you're using this.

[HyperZ]

Great job.

[RedFusion]

Very nice, So clean and simple, and still so useful. 10/10

[MrDeath537]

VERY nice work g_aSlice, I'm doing some tests

Thanks for post!

[anonymousx]

AWESOME, very useful for my new gm

[Retardedwolf]

Awesome. can't wait for the 'more powerful version'.

[Double-O-Seven]

This looks awesome!
Terribly beautiful code!

[Slice]

Thanks for the feedback, people.

Quote:

Originally Posted by anonymousx AWESOME, very useful for my new gm

Please let me know how it goes! If you need any help with it, post here.

Quote:

Originally Posted by ****** That is beautiful code - I especially like the "BUD::" scoping on everything! This especially made me happy: pawn Код: stock BUD::JSCHash(const pass[],passhash[MAX_PASSWORD_LENGTH + 1]) // ****** Just to give credit where credit's due, that's actually written by my dad (JSC) and ported by me.

Thanks! oh, and I'll add mr. JSC to the credits.

[Slice]

Update
New functions available:

Код:

DBResult    BUD::RunQuery( query[], bool:store_results )
            BUD::EscapeSqlString( string[ ] )
            BUD::GetSortedData( &results[][],  column )
bool        BUD::GetNamesForSortedData( results[][], num_results, &names[][] )

There is no need for db_free_result after using BUD::RunQuery! BUD will take care of that when the function that sent the query is done running.

To protect your query from SQL injections, simply run BUD::EscapeSqlString with the string you will insert as an argument; if you're doing this for something enclosed in ` quotes, do:

pawn Код:

BUD::EscapeSqlString( string, '`' );

Example usage of BUD::GetSortedData to list the top killers:

pawn Код:

new BUD::Results:brTest<10>;

new iResults = BUD::GetSortedData( brTest, "kills" );

new szaNames[ 10 ][ MAX_PLAYER_NAME ];

BUD::GetNamesForSortedData( brTest, iResults, szaNames );

if ( iResults == BUD::INVALID_RESULTS )
    printf( "BUD::GetSortedData failed." );
else
{
    for ( new i = 0; i < iResults; i++ )
    {
        printf( "User: %s (%d)\t%d", szaNames[ i ], brTest[ i ][ 0 ], brTest[ i ][ 1 ] );
    }
}

Please help me test these new functions, I haven'd had much time to do testing.

Download
The links are in the main post.

[Scenario]

Interesting... I'll have to give this a shot sometime soon.

[Luka P.]

Very nice code!

[Retardedwolf]

For some reasons I'm not able to use the function `BUD::GetStringEntry`, it would give me a `Function should return a value ` but if I use `BUD::MultiGet` it would work.

Another one is getting `BUD - Error: The number of type definitions doesn't match the number of entries passed to BUD::MultiSet; typedefs: 10, entries: 6.` with this code.

pawn Код:

stock
    saveStatistics ( playerid )
{
    Character[playerid][cLogged] = 0;
    BUD::MultiSet ( Character[playerid][cUID], "iiiiis[16]",
        "cALevel", Character[playerid][cALevel],
        "cKills", Character[playerid][cKills]  ,
        "cDeaths", Character[playerid][cDeaths],
        "cMoney", Character[playerid][cMoney]  ,
        "cHSpree", Character[playerid][cHSpree],
        "cIP", getCharIP ( playerid ) );
    return 1;
}

I don't know if these two issues is due to my code or whatsoever.

[Aleluja]

Nice work!

[Extremo]

I like your idea but I would love to see more customization. Considering that not everyone makes a small mod here and that there are usually multiple databases. Like users could be split for security reasons, accessing multiple databases over mutliple users(later with MySQL). Adding Transactions with BEGIN and COMMIT, which can very important. Of course yet again with MySQL :P

Thinking about redundant information, it would be very good if you managed to make this multi-database-ish.

As in, I could save a vehicle type by ID and only link the ID to the user, but the vehicle type is dependant on the ID and there should be no ID without vehicle type, hence foreign keys come in play and so on.

Great work! I am thinking of implementing this if the Development is at a constant speed. Though I would prefer a MySQL version, so I might end up writting my own - if this is too slow for me. Though if you need some help with this, I doubt it but if you do, hit me up! I would like to stay in contact with you as this reminds me a lot to hibernate lol

Regards,
Extremo.

[Slice]

Update
Bug fixes and such; for example, names are no longer case-sensitive.

Quote:

Originally Posted by Retardedwolf For some reasons I'm not able to use the function `BUD::GetStringEntry`, it would give me a `Function should return a value ` but if I use `BUD::MultiGet` it would work. Another one is getting `BUD - Error: The number of type definitions doesn't match the number of entries passed to BUD::MultiSet; typedefs: 10, entries: 6.` with this code. pawn Код: stock     saveStatistics ( playerid ) {     Character[playerid][cLogged] = 0;     BUD::MultiSet ( Character[playerid][cUID], "iiiiis[16]",         "cALevel", Character[playerid][cALevel],         "cKills", Character[playerid][cKills]  ,         "cDeaths", Character[playerid][cDeaths],         "cMoney", Character[playerid][cMoney]  ,         "cHSpree", Character[playerid][cHSpree],         "cIP", getCharIP ( playerid ) );     return 1; } I don't know if these two issues is due to my code or whatsoever.

Hey,
You only specify string sizes in BUD::MultiGet (to prevent buffer overflow), so if you change the format string to "iiiiis" it should work.
I'm not sure what's up with BUD::GetStringEntry, I released an update a couple minutes ago and it's working fine in that version.

Quote:

Originally Posted by Extremo I like your idea but I would love to see more customization. Considering that not everyone makes a small mod here and that there are usually multiple databases. Like users could be split for security reasons, accessing multiple databases over mutliple users(later with MySQL). Adding Transactions with BEGIN and COMMIT, which can very important. Of course yet again with MySQL :P Thinking about redundant information, it would be very good if you managed to make this multi-database-ish. As in, I could save a vehicle type by ID and only link the ID to the user, but the vehicle type is dependant on the ID and there should be no ID without vehicle type, hence foreign keys come in play and so on. Great work! I am thinking of implementing this if the Development is at a constant speed. Though I would prefer a MySQL version, so I might end up writting my own - if this is too slow for me. Though if you need some help with this, I doubt it but if you do, hit me up! I would like to stay in contact with you as this reminds me a lot to hibernate lol Regards, Extremo.

Good ideas, thanks. There will be a couple more tables in the SQLite version soon (bans, for example). I PMed you my Skype, I'll hit you up when I get home from work.

Quote:

Originally Posted by ****** What would be good is if you could write a "BUD::RunQueryEx(query[], booltore_results, {Float,_}:...)" function which would automatically escape strings in the passed parameters according to the format in "query".

I'm trying to get it working (using ZeeX's and yours code for passing on arguments). It's giving me a headache.

[Slice]

Thanks. Page 15 in your optimization topic has information about the parameter pass through code.
After learning the basics of PAWN, I learned most of the more advanced stuff by looking through YSI, btw.

Regarding memcpy: I was going to make a post about that in code optimizations. It is significantly faster than any other string copying method, afaik.

For reference, here are my results and how I got them:

Код:

String copying benchmark
 Bench for memcpy: executes, by average, 2208 times/ms.
 Bench for format: executes, by average, 1279 times/ms.
 Bench for strcpy 1: executes, by average, 142 times/ms.
 Bench for strcpy 2: executes, by average, 89 times/ms.

Note: Never, ever actually use format to copy strings as it doesn't support all characters.

Code used to measure it here.

[MrDeath537]

Quote:

Originally Posted by MrDeath VERY nice work g_aSlice, I'm doing some tests Thanks for post!

Ready, it works really nice, I couldn't find bugs .

__________________________________________

pawn Код:

BUD::MultiSet(userID, "sfiii",  "TestString", "abcdefghijklmnopqrstuvwxyz",
                                "TestFloat", 123.456,
                                "Kills", 12345,
                                "Deaths", 1000,
                                "Money", 999999);

// -------------------------------------

BUD::MultiGet(userID, "s[5]s[32]fiii",  "TestString", string1,
                                        "TestString", string2,
                                        "TestFloat", float1,
                                        "Kills", int1,
                                        "Deaths", int2,
                                        "Money", int3);

I don't know if it works fine, but the first string gives me "abcd" instead of "abcde", Is that ok?


__________________________________________

Edit:

Quote:

Originally Posted by g_aSlice Код: String copying benchmark Bench for memcpy: executes, by average, 2208 times/ms. Bench for format: executes, by average, 1279 times/ms. Bench for strcpy 1: executes, by average, 142 times/ms. Bench for strcpy 2: executes, by average, 89 times/ms.

I made strcpy_3 using for and strlen:

pawn Код:

stock strcpy_3(dest[], source[])
{
    new
        len = strlen(source),
        i
        ;

    for (i = 0; i < len; i ++)
    {
        dest[i] = source[i];
    }
}

Код:

[18:14:49] String copying benchmark
[18:14:50]  Bench for memcpy: executes, by average, 590 times/ms.
[18:14:51]  Bench for format: executes, by average, 492 times/ms.
[18:14:52]  Bench for strcpy 1: executes, by average, 54 times/ms.
[18:14:53]  Bench for strcpy 2: executes, by average, 50 times/ms.
[18:14:54]  Bench for strcpy 3: executes, by average, 60 times/ms.

I thought for will be faster than do and while