SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Filterscripts

Reply
 
Thread Tools Display Modes
Old 17/11/2013, 08:35 AM   #1
Voxel
Gangsta
 
Voxel's Avatar
 
Join Date: Sep 2013
Location: The Netherlands
Posts: 578
Reputation: 70
Default Easy SQLite Database Start (for beginners)

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)
http://forum.sa-mp.com/showthread.php?t=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 Code:
/*               * * * * INFO * * * *               */
/*
    Date started:   17-11-2013
    Time:           10:20 AM
    Creator:        Voxel
    Server Name:    SQLBASE
    Database:       SQLite
    Version:        0.1
    Website:        [url]www.sa-mp.com[/url]
*/

/*             * * * * 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)
http://forum.sa-mp.com/showthread.php?t=303682
__________________
My Work:
[GameMode]The Nether Survival
[Filterscript]SQLite Database Start
[Tutorial]SQLite Commands
[Tutorial]Loot/ Inventory System
[Tutorial]Simple Hunger/Thirst System
[Tutorial]Converting Binary
[Map]Tropical Island + Pier
[Map]San Fierro Apocalypse

Last edited by Voxel; 30/11/2013 at 07:54 PM.
Voxel is offline   Reply With Quote
Old 17/11/2013, 08:40 AM   #2
Pottus
High-roller
 
Pottus's Avatar
 
Join Date: Jun 2012
Posts: 4,885
Reputation: 1337
Default Re: Easy SQLite Database Start (for beginners)

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

http://forum.sa-mp.com/showthread.php?t=303682
Pottus is offline   Reply With Quote
Old 17/11/2013, 08:42 AM   #3
Voxel
Gangsta
 
Voxel's Avatar
 
Join Date: Sep 2013
Location: The Netherlands
Posts: 578
Reputation: 70
Default Re: Easy SQLite Database Start (for beginners)

Quote:
Originally Posted by [uL]Pottus View Post
You should really toss SQLitei in there as well to take advantage of getting query errors.

http://forum.sa-mp.com/showthread.php?t=303682
havent gotten into that yet but thank you for replying! i will add it to the topic
__________________
My Work:
[GameMode]The Nether Survival
[Filterscript]SQLite Database Start
[Tutorial]SQLite Commands
[Tutorial]Loot/ Inventory System
[Tutorial]Simple Hunger/Thirst System
[Tutorial]Converting Binary
[Map]Tropical Island + Pier
[Map]San Fierro Apocalypse
Voxel is offline   Reply With Quote
Old 17/11/2013, 08:56 AM   #4
AIped
Gangsta
 
AIped's Avatar
 
Join Date: Feb 2011
Location: Netherlands
Posts: 618
Reputation: 46
Default Re: Easy SQLite Database Start (for beginners)

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
AIped is offline   Reply With Quote
Old 17/11/2013, 09:03 AM   #5
SimpalK
Banned
 
Join Date: Apr 2013
Location: On Earth
Posts: 91
Reputation: 14
Default Re: Easy SQLite Database Start (for beginners)

Nice one, Repped +1.
SimpalK is offline   Reply With Quote
Old 17/11/2013, 09:04 AM   #6
Voxel
Gangsta
 
Voxel's Avatar
 
Join Date: Sep 2013
Location: The Netherlands
Posts: 578
Reputation: 70
Default Re: Easy SQLite Database Start (for beginners)

Thank you guys!
__________________
My Work:
[GameMode]The Nether Survival
[Filterscript]SQLite Database Start
[Tutorial]SQLite Commands
[Tutorial]Loot/ Inventory System
[Tutorial]Simple Hunger/Thirst System
[Tutorial]Converting Binary
[Map]Tropical Island + Pier
[Map]San Fierro Apocalypse
Voxel is offline   Reply With Quote
Old 17/11/2013, 01:40 PM   #7
Wizzy951
Huge Clucker
 
Wizzy951's Avatar
 
Join Date: Jul 2013
Posts: 285
Reputation: 41
Default Re: Easy SQLite Database Start (for beginners)

Thanks to Konstantinos ! because of this
Wizzy951 is offline   Reply With Quote
Old 17/11/2013, 01:49 PM   #8
Voxel
Gangsta
 
Voxel's Avatar
 
Join Date: Sep 2013
Location: The Netherlands
Posts: 578
Reputation: 70
Default Re: Easy SQLite Database Start (for beginners)

Quote:
Originally Posted by Wizzy951 View Post
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.
__________________
My Work:
[GameMode]The Nether Survival
[Filterscript]SQLite Database Start
[Tutorial]SQLite Commands
[Tutorial]Loot/ Inventory System
[Tutorial]Simple Hunger/Thirst System
[Tutorial]Converting Binary
[Map]Tropical Island + Pier
[Map]San Fierro Apocalypse
Voxel is offline   Reply With Quote
Old 18/11/2013, 06:28 PM   #9
Tomix
Big Clucker
 
Join Date: Apr 2013
Posts: 128
Reputation: -3
Default Re: Easy SQLite Database Start (for beginners)

Very nice filterscript I will use it thx mate
Tomix is offline   Reply With Quote
Old 19/11/2013, 03:09 PM   #10
Voxel
Gangsta
 
Voxel's Avatar
 
Join Date: Sep 2013
Location: The Netherlands
Posts: 578
Reputation: 70
Default Re: Easy SQLite Database Start (for beginners)

Quote:
Originally Posted by Tomix View Post
Very nice filterscript I will use it thx mate
Thanks and good luck!
__________________
My Work:
[GameMode]The Nether Survival
[Filterscript]SQLite Database Start
[Tutorial]SQLite Commands
[Tutorial]Loot/ Inventory System
[Tutorial]Simple Hunger/Thirst System
[Tutorial]Converting Binary
[Map]Tropical Island + Pier
[Map]San Fierro Apocalypse
Voxel is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sqlite database mixmadamich Scripting Help 1 29/08/2013 01:56 PM
SQLite database Seregamil Русский/Russian 8 01/10/2012 06:40 PM
SQLite database Seregamil Scripting Help 0 30/09/2012 10:12 AM
[GameMode] [Gm] Easy gamemode for beginners SKD Gamemode Scripts 6 01/09/2012 02:17 PM
Database SQLite problems. Edvin Scripting Help 5 16/01/2012 12:36 PM


All times are GMT. The time now is 02:28 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.