[Voxel]

Easy SQLite Database Start
By Voxel

Hello guys,

Since i started with SQLite i strugled with it but finally i understand how it works and how to modify a database and add fields to it such as kills and deaths etc. I noticed when i started SQLite it was pretty hard to get the hang off (atleast for me) so i am releasing a simple filterscript for newbies to SQL so they can see how things like money and score can be added.

For this filterscript i used the SQL tutorial of Konstantinos, you can check out the tutorial over here: (wich i suggest you do)
https://sampforum.blast.hk/showthread.php?tid=449536
and all credit goes to him for making this awesome tutorial!

Features:

- Login/ Register system with dialogs
- SQLite saving (money etc)
- No includes or plug-ins required
- Easy to edit!

Install:

Copy the script in a Pawno file and compile, put the AMX in your scriptfiles folder and add it in your server.cfg!

Get it here:
http://pastebin.com/1Am1PwXd

Or you can check out the filterscript here:

pawn Код:

/*               * * * * INFO * * * *               */
/*
    Date started:   17-11-2013
    Time:           10:20 AM
    Creator:        Voxel
    Server Name:    SQLBASE
    Database:       SQLite
    Version:        0.1
    Website:        http://www.sa-mp.com
*/
/*             * * * * INCLUDES * * * *             */
#include <a_samp>
/*             * * * * DEFINES * * * *              */
#define MAX_INI_ENTRY_TEXT 80
//INFO
#define Web "www.sa-mp.com"
#define Version "v0.1"
#define Creator "Voxel"
#define Name "SQL BASE""
#define CName "SQLB"
#define chat "{FFFFFF}*"
//COLORS
#define COL_WHITE          "{FFFFFF}"
#define COL_GREY           "{C3C3C3}"
#define COL_GREEN          "{37DB45}"
#define COL_RED            "{F81414}"
#define COL_YELLOW         "{F3FF02}"
#define COL_ORANGE         "{F9B857}"
#define COL_BLUE           "{0049FF}"
#define COL_PINK           "{FF00EA}"
#define COL_LIGHTBLUE      "{00C0FF}"
#define COL_LGREEN         "{C9FFAB}"
/*             * * * * NATIVES * * * *             */
native WP_Hash(buffer[], len, const str[]);
/*           * * * * ENUMERATORS * * * *           */
enum USER_DATA
{
    USER_ID,
    USER_NAME[MAX_PLAYER_NAME],
    USER_PASSWORD[129],
    USER_ADMIN,
    USER_VIP,
    USER_MONEY,
    USER_SCORE,
    USER_KILLS,
    USER_DEATHS,
    USER_KD,

    bool: USER_LOGGED_IN
};
/*         * * * * GLOBAL VARIABLES * * * *        */
new User[MAX_PLAYERS][USER_DATA];
new DB: Database;
/*              * * * * MAIN * * * *               */
main()
{
    print("=====================================");
    print("|             SQL BASE              |");
    print("|            *INITIATED*            |");
    print("|           Version: v0.1           |");
    print("|           Creator: Voxel          |");
    print("=====================================");
}

public OnFilterScriptInit()
{
    SetGameModeText("SQLBASE"Version"");
    Database = db_open("server.db");
    db_query(Database, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 0 NOT NULL, score INTEGER DEFAULT 0 NOT NULL, kills INTEGER DEFAULT 0 NOT NULL, deaths INTEGER DEFAULT 0 NOT NULL, kd INTEGER DEFAULT 0 NOT NULL)");
    AddPlayerClass(1, 2528.9143,-1667.7504,15.1689,91.2860,0,0,0,0,0,0);
    return 1;
}

public OnFilterScriptExit()
{
    db_close(Database);
    print("=====================================");
    print("|             SQL BASE              |");
    print("|            *SHUT DOWN*            |");
    print("|           Version: v0.1           |");
    print("|           Creator: Voxel          |");
    print("=====================================");
    return 1;
}

public OnPlayerRequestClass(playerid, classid)
{
    return 1;
}

public OnPlayerConnect(playerid)
{
    for(new i; i < _: USER_DATA; ++i) User[playerid][USER_DATA: i] = 0;

    GetPlayerName(playerid, User[playerid][USER_NAME], MAX_PLAYER_NAME);

    new Query[71], DBResult: Result;
    format(Query, sizeof(Query), "SELECT password FROM users WHERE username = '%s' LIMIT 0, 1", DB_Escape(User[playerid][USER_NAME]));
    Result = db_query(Database, Query);
    if(db_num_rows(Result))
    {
        db_get_field_assoc(Result, "password", User[playerid][USER_PASSWORD], 129);
        ShowPlayerDialog(playerid, 0, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SGZM"COL_WHITE" Login pannel", ""chat" Please login to play on "COL_BLUE"Survival Games Zombie Mode\n"COL_WHITE"Enter your "COL_BLUE"password"COL_WHITE":", "Login", "Exit");
    }
    else ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SGZM"COL_WHITE" Register pannel", ""chat" Please register to play on "COL_BLUE"Survival Games Zombie Mode\n"COL_WHITE"Enter a "COL_BLUE"password"COL_WHITE":", "Register", "Exit");
    db_free_result(Result);
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    if(User[playerid][USER_LOGGED_IN] == true)
    {
        new Query[128];
        format(Query, sizeof(Query), "UPDATE users SET admin = %d, vip = %d, money = %d, score = %d, kills = %d, deaths = %d, kd = %d WHERE username = '%s'", User[playerid][USER_ADMIN], User[playerid][USER_VIP], GetPlayerMoney(playerid), GetPlayerScore(playerid), User[playerid][USER_KILLS], User[playerid][USER_DEATHS], User[playerid][USER_KD], DB_Escape(User[playerid][USER_NAME]));
        db_query(Database, Query);
    }
    for(new i; i < _: USER_DATA; ++i) User[playerid][USER_DATA: i] = 0;
    return 1;
}

public OnPlayerSpawn(playerid)
{
    return 1;
}

public OnPlayerDeath(playerid, killerid, reason)
{
    return 1;
}
/*             * * * * DIALOGS * * * *           */
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == 0)
    {
        if(response)
        {
            if(!inputtext[0]) return ShowPlayerDialog(playerid, 0, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SGZM"COL_WHITE" Login pannel", ""chat" Please login to play on "COL_BLUE"Survival Games Zombie Mode\n "COL_WHITE"Enter your "COL_BLUE"password"COL_WHITE":", "Login", "Exit");
            new buf[129];
            WP_Hash(buf, 129, inputtext);
            if(!strcmp(buf, User[playerid][USER_PASSWORD], false))
            {
                new Query[75], DBResult: Result;
                format(Query, sizeof(Query), "SELECT * FROM users WHERE username = '%s' LIMIT 0, 1", DB_Escape(User[playerid][USER_NAME]));
                Result = db_query(Database, Query);
                if(db_num_rows(Result))
                {
                    db_get_field_assoc(Result, "userid", Query, 7);
                    User[playerid][USER_ID] = strval(Query);

                    db_get_field_assoc(Result, "admin", Query, 3);
                    User[playerid][USER_ADMIN] = strval(Query);

                    db_get_field_assoc(Result, "vip", Query, 3);
                    User[playerid][USER_VIP] = strval(Query);

                    db_get_field_assoc(Result, "money", Query, 10);
                    User[playerid][USER_MONEY] = strval(Query);

                    db_get_field_assoc(Result, "score", Query, 7);
                    User[playerid][USER_SCORE] = strval(Query);

                    db_get_field_assoc(Result, "kills", Query, 10);
                    User[playerid][USER_KILLS] = strval(Query);

                    db_get_field_assoc(Result, "deaths", Query, 10);
                    User[playerid][USER_DEATHS] = strval(Query);

                    db_get_field_assoc(Result, "kd", Query, 10);
                    User[playerid][USER_KD] = strval(Query);

                    User[playerid][USER_LOGGED_IN] = true;

                    GivePlayerMoney(playerid, User[playerid][USER_MONEY]);
                    SetPlayerScore(playerid, User[playerid][USER_SCORE]);

                    SendClientMessage(playerid, 0x00FF00FF, ""chat" "COL_LIGHTBLUE"You have successfully logged in to your account!");
                }
                db_free_result(Result);
            }
            else
            {
                SendClientMessage(playerid, -1, ""chat" "COL_RED"Incorrect password!");
                ShowPlayerDialog(playerid, 0, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SGZM"COL_WHITE" Login pannel", ""chat" Please login to play on "COL_BLUE"Survival Games Zombie Mode\n"COL_WHITE"Enter your "COL_BLUE"password"COL_WHITE":", "Login", "Exit");

            }
        }
        else Kick(playerid);
        return 1;
    }
    if(dialogid == 1)
    {
        if(response)
        {
            if(!IsValidPassword(inputtext))
            {
                SendClientMessage(playerid, -1, ""chat" "COL_RED"The password is invalid, Valid characters are: A-Z, a-z, 0-9");
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SERVER NAME"COL_WHITE" Register pannel", ""chat" Please register to play on "COL_BLUE"SERVER NAME\n"COL_WHITE"Enter a "COL_BLUE"password"COL_WHITE":", "Register", "Exit");
                return 1;
            }
            if(strlen(inputtext) < 3 || strlen(inputtext) > 24)
            {
                SendClientMessage(playerid, -1, ""chat" "COL_RED"The password is invalid, Its lenght should be 3-24 characters");
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, ""COL_BLUE"SERVER NAME"COL_WHITE" Register pannel", ""chat" Please register to play on "COL_BLUE"SERVER NAME\n"COL_WHITE"Enter a "COL_BLUE"password"COL_WHITE":", "Register", "Exit");
                return 1;
            }
            new Query[208];
            WP_Hash(User[playerid][USER_PASSWORD], 129, inputtext);
            format(Query, sizeof(Query), "INSERT INTO users (username, password) VALUES ('%s', '%s')", DB_Escape(User[playerid][USER_NAME]), DB_Escape(User[playerid][USER_PASSWORD]));
            db_query(Database, Query);

            User[playerid][USER_LOGGED_IN] = true;
            SendClientMessage(playerid, 0x00FF00FF, ""chat" "COL_LIGHTBLUE"You have just registered to our server! You have been automatically logged in!");
        }
        else Kick(playerid);
        return 1;
    }
    return 1;
}
/*           * * * * STOCKS * * * *                */
/*          * * * * DB ESCAPE * * * *              */
stock DB_Escape(text[])
{
    new ret[ 80 * 2 ], ch, i, j;
    while((ch = text[i++]) && j < sizeof(ret))
    {
        if(ch == '\'')
        {
            if(j < sizeof(ret) - 2)
            {
                ret[j++] = '\'';
                ret[j++] = '\'';
            }
        }
        else if(j < sizeof(ret))
        {
            ret[j++] = ch;
        }
        else
        {
            j++;
        }
    }
    ret[sizeof(ret) - 1] = '\0';
    return ret;
}

stock IsValidPassword(const password[])
{
    for(new i = 0; password[i] != EOS; ++i)
    {
        switch(password[i])
        {
            case '0'..'9', 'A'..'Z', 'a'..'z': continue;
            default: return 0;
        }
    }
    return 1;
}

I made a tutorial for the commands and features! check it out:
http://forum.sa-mp.com/showthread.ph...08#post2785408

I hope you guys find this usefull and good luck with SQL !

Also check this out: (thanks to [uL]Pottus)
https://sampforum.blast.hk/showthread.php?tid=303682

[Pottus]

You should really toss SQLitei in there as well to take advantage of getting query errors.

https://sampforum.blast.hk/showthread.php?tid=303682

[Voxel]

Quote:

Originally Posted by [uL]Pottus You should really toss SQLitei in there as well to take advantage of getting query errors. https://sampforum.blast.hk/showthread.php?tid=303682

havent gotten into that yet but thank you for replying! i will add it to the topic

[AIped]

You are 6 months to late for me i gave up on SQLite XD..i remember that i couldnt find out why the savings in SQLite suddenly stopped working. I followed a few tutorials but releasing a actual FS as a good base is a very good idea of you. I will +rep you for that

[SimpalK]

Nice one, Repped +1.

[Voxel]

Thank you guys!

[Wizzy951]

Thanks to Konstantinos ! because of this

[Voxel]

Quote:

Originally Posted by Wizzy951 Thanks to Konstantinos ! because of this

i already added his credits to this topic, no need to say it here again, after all im the one that put this all together for you guys to use and learn off.

[Tomix]

Very nice filterscript I will use it thx mate

[Voxel]

Quote:

Originally Posted by Tomix Very nice filterscript I will use it thx mate

Thanks and good luck!

[Voxel]

Updated with some stuff!

[Voxel]

Please notice that the tutorial link that is posted at the bottom of this topic is part of this filterscript.