[ThePhenix]

Easy - SQLite

Overview:

I'm basically, copying most(maybe all) the information from my previous thread:

Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

As this include, uses the same syntax on the functions with the difference that this include is bound to SQLite and replaces the SQL:: syntax to SL::


This include allows you to create and manage several SQLite queries in a very simplified way, it means that you no longer need to use mysql_query or anything like it. But you will be able to manage queries like you used to in previous systems like INI for example.


Some advantages of this include:

- No speed reductions in comparison to normal queries.
- Easier management of queries in comparison to normal ones.
- This include has a similar scheme to Y_INI (Thanks ****** for that)
- Ability to update large amounts of data without the need to concatenate strings, which is a pain if you have a lot of data to be updated.
- Ability to create and manage tables script side.
- You don't need a high knowledge in MySL as this include simplifies its usage to the fullest.
- Overall, you won't need to worry about setting up a lot of queries together in order to proceed with some action.


Functions:

Code:

native DB:SL::Connect(database[]);
native SL::DeleteRow(const table[], const column[], columnID, DB:database = DB:1);
native SL::DeleteRowEx(const table[], const column[], columnID[], DB:database = DB:1);
native SL::GetIntEntry(const table[], const field[], const column[], columnID, DB:database = DB:1);
native Float:SL::GetFloatEntry(const table[], const field[], const column[], columnID, DB:database = DB:1);
native SL::GetStringEntry(const table[], const field[], const column[], columnID, dest[], len = sizeof(dest), DB:database = DB:1);
native SL::GetStringEntryEx(const table[], const field[], const column[], const scolumn[], dest[], len = sizeof(dest), DB:database = DB:1)
native SL::GetIntEntryEx(const table[], const field[], const column[], scolumn[], DB:database = DB:1);
native Float:SL::GetFloatEntryEx(const table[], const field[], const column[], scolumn[], DB:database = DB:1);
native SL::CreateTable(const tablename[], DB:database = DB:1);
native SL::AddTableEntry(handle, const field[], SL::datatypes: type = SL_TYPE_INT, maxlength = 11, bool:auto_increment = false, bool:setprimary = false);
native SL::OpenTable_Read(const table[], const column[], columnID, DB:database = DB:1);
native SL::OpenTable_ReadEx(const table[], const column[], columnID[], DB:database = DB:1);
native SL::ReadInt(handle, const field[], &dest);
native SL::ReadFloat(handle, const field[], &Float:dest);
native SL::ReadString(handle, const field[], dest[], len = sizeof(dest));
native SL::OpenTable_Update(const table[], const column[], columnID, DB:database = DB:1);
native SL::OpenTable_UpdateEx(const table[], const column[], columnID[], DB:database = DB:1);
native SL::Open(SL::qtypes:type, const table[], const column[] = "", columnID = -1, DB:database = DB:1);
native SL::ToggleAutoIncrement(handle, bool:toggle);
native SL::OpenEx(SL::qtypes:type, const table[], const column[] = "", columnID[] = "", DB:database = DB:1)
native SL::UpdateIntEntry(handle, const field[], value);
native SL::UpdateFloatEntry(handle, const field[], Float:value);
native SL::UpdateStringEntry(handle, const field[], const value[], bool:use_real_escape = true);
native SL::Close(handle);
native SL::SetIntEntry(const table[], const field[], value, const column[], columnID, DB:database = DB:1);
native SL::SetIntEntryEx(const table[], const field[], value, const column[], columnID[], DB:database = DB:1);
native SL::SetFloatEntry(const table[], const field[], Float:value, const column[], columnID, DB:database = DB:1);
native SL::SetFloatEntryEx(const table[], const field[], Float:value, const column[], columnID[], DB:database = DB:1);
native SL::SetStringEntry(const table[], const field[], const value[], const column[], columnID, bool:use_real_escape = true, DB:database = DB:1);
native SL::SetStringEntryEx(const table[], const field[], const value[], const column[], columnID[], bool:use_real_escape = true, DB:database = DB:1);
native SL::OpenTable_Insert(const table[], DB:database = DB:1);
native SL::InsertIntEntry(handle, const field[], value);
native SL::InsertFloatEntry(handle, const field[], Float:value);
native SL::InsertStringEntry(handle, const field[], const value[], bool:use_real_escape = true);
native SL::ExistsTable(const tablename[], DB:database = DB:1);
native SL::CountRows(const tablename[], DB:database = DB:1);
native SL::CountTables(DB:database = DB:1);
native SL::DropTable(const tablename[], DB:database = DB:1);

v2.0

native SL::Open(SL::qtypes:type, const table[], const column[] = "", columnID = -1, DB:database = DB:1);
native SL::ToggleAutoIncrement(handle, bool:toggle);
native SL::OpenEx(SL::qtypes:type, const table[], const column[] = "", columnID[] = "", DB:database = DB:1)


The following functions replace Insert and Update functions:

native SL::WriteFloat(handle, const field[], Float:value);
native SL::WriteInt(handle, const field[], Float:value);
native SL::WriteString(handle, const field[], const value[]);


This include is mainly for people without experience with SLite as it simplifies its usage.

Login/Register example:

PHP Code:

/* 
 *  Simple register/login system using easy_sqlite.inc! 
*/ 
#include <a_samp> 
#include <easy-sqlite> 
enum p_info 
{ 
    p_id, 
    p_name[24], 
    p_password[64], 
    p_score, 
    Float:p_posx, 
    Float:p_posy, 
    Float:p_posz, 
    p_loggedin 
}; 
new UserInfo[MAX_PLAYERS][p_info];
#define DIALOG_LOGIN 0 
#define DIALOG_REGISTER 1 
stock ret_pName(playerid) 
{ 
    new name[24]; 
    GetPlayerName(playerid, name, sizeof(name)); 
    return name; 
} 
main() 
{ 
     
} 
public OnGameModeInit() 
{ 
    //Connecting to the database 
    SL::Connect("server.db"); 
    //Checking if the table 'players' exists 
     
     
    //Checking if the table 'players' exists 
    if(!SL::ExistsTable("players")) 
    { 
        //If not, then create a table called 'players'. 
        new handle = SL::Open(SL::CREATE, "players"); //Opening a valid handle to create a table called 'players' 
        SL::AddTableEntry(handle, "p_id", SL_TYPE_INT, 11, true); 
        SL::AddTableEntry(handle, "p_name", SL_TYPE_VCHAR, 24); 
        SL::AddTableEntry(handle, "p_password", SL_TYPE_VCHAR, 64); 
        SL::AddTableEntry(handle, "p_score", SL_TYPE_INT); 
        SL::AddTableEntry(handle, "p_posx", SL_TYPE_FLOAT); 
        SL::AddTableEntry(handle, "p_posy", SL_TYPE_FLOAT); 
        SL::AddTableEntry(handle, "p_posz", SL_TYPE_FLOAT); 
        SL::Close(handle);//Closing the previous opened handle. 
        print("Table 'players' was successfully created");
    } 
    else 
    {
        print("Table 'players' was successfully loaded");
    }
    return 1; 
} 
public OnPlayerConnect(playerid) 
{ 
    UserInfo[playerid][p_loggedin] = 0; UserInfo[playerid][p_score] = 0;  UserInfo[playerid][p_posx] = 1958.3783; 
    UserInfo[playerid][p_posy] = 1343.1572; UserInfo[playerid][p_posz] = 15.3746;  
    if(SL::RowExistsEx("players", "p_name", ret_pName(playerid))) //Check if the name is registered in the database 
    { 
        //Get the player password and unique ID. 
        new handle = SL::OpenEx(SL::READ, "players", "p_name", ret_pName(playerid)); 
        SL::ReadString(handle, "p_password", UserInfo[playerid][p_password], 64); 
        SL::ReadInt(handle, "p_id", UserInfo[playerid][p_id]); 
        SL::Close(handle); 
        //Show the login dialog 
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "{0080FF}Login", "Please input your password below to log in.", "Login", "Exit"); 
    } 
    else 
    { 
        //If not registered, then show the register DIALOG. 
        ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "{0080FF}Register", "Please input a password below to register in.", "Login", "Exit"); 
    } 
    return 1; 
} 
public OnPlayerSpawn(playerid) 
{ 
    SetPlayerPos(playerid, UserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]); 
    return 1; 
} 
public OnPlayerDisconnect(playerid, reason) 
{ 
    if(UserInfo[playerid][p_loggedin] == 1) 
    { 
        //Save the player data. 
        GetPlayerPos(playerid, UserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]); 
        new handle = SL::Open(SL::UPDATE, "players", "p_id", UserInfo[playerid][p_id]); 
        SL::WriteInt(handle, "p_score", GetPlayerScore(playerid)); 
        SL::WriteFloat(handle, "p_posx", UserInfo[playerid][p_posx]); 
        SL::WriteFloat(handle, "p_posy", UserInfo[playerid][p_posy]); 
        SL::WriteFloat(handle, "p_posz", UserInfo[playerid][p_posz]); 
        SL::Close(handle); 
    } 
    return 1; 
} 
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[]) 
{ 
    switch(dialogid) 
    { 
        case DIALOG_REGISTER: 
        { 
            if(!response) return Kick(playerid); 
            if(strlen(inputtext) < 5) 
            { 
                ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "{0080FF}Register", "Please input a password below to register in.", "Login", "Exit"); 
                return 1; 
            } 
            SHA256_PassHash(inputtext, "", UserInfo[playerid][p_password], 64); 
            new handle = SL::Open(SL::INSERT, "players"); 
            SL::ToggleAutoIncrement(handle, true);
            SL::WriteString(handle, "p_name", ret_pName(playerid)); 
            SL::WriteString(handle, "p_password", UserInfo[playerid][p_password]); 
            SL::WriteInt(handle, "p_score", 0); 
            SL::WriteFloat(handle, "p_posx", 0.0); 
            SL::WriteFloat(handle, "p_posy", 0.0); 
            SL::WriteFloat(handle, "p_posz", 0.0); 
            UserInfo[playerid][p_id] = SL::Close(handle);//If auto_increment was set to true, SL::Close will return the ID of the inserted row. 
            SendClientMessage(playerid, -1, "Successfully registered in!"); 
            UserInfo[playerid][p_loggedin] = 1; 
        } 
        case DIALOG_LOGIN: 
        { 
            if(!response) Kick(playerid);  
            new hash[64]; 
            SHA256_PassHash(inputtext, "", hash, 64); 
            if(!strcmp(hash, UserInfo[playerid][p_password])) 
            {  
                //Load player data 
                new handle = SL::Open(SL::READ, "players", "p_id", UserInfo[playerid][p_id]); 
                SL::ReadInt(handle, "p_score", UserInfo[playerid][p_score]); 
                SL::ReadFloat(handle, "p_posx", UserInfo[playerid][p_posx]); 
                SL::ReadFloat(handle, "p_posy", UserInfo[playerid][p_posy]); 
                SL::ReadFloat(handle, "p_posz", UserInfo[playerid][p_posz]); 
                SL::Close(handle);//You must close the handle. 
                SetPlayerScore(playerid, UserInfo[playerid][p_score]); 
                UserInfo[playerid][p_loggedin] = 1; 
                SendClientMessage(playerid, -1, "Successfully logged in!"); 
                 
            } 
            else  
            { 
                ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "{0080FF}Login", "Please input your password below to log in.", "Login", "Exit"); 
            } 
        } 
    } 
    return 1; 
} 


Constants:

Code:

SL_TYPE_INT //Describes an integer
SL_TYPE_VCHAR //Describes a varchar
SL_TYPE_FLOAT //Describes a float
SL_INVALID_HANDLE //Describes an invalid handle
SL::UPDATE //Describes an update handle
SL::INSERT //Describes an insert handle
SL::READ //Describes a read handle
SL::CREATE //Describes a handle to create a table

Note:

Functions like:

Code:

native SL::CreateTable(const tablename[], connectionHandle = 1);
native SL::OpenTable_Read(const table[], const column[], columnID, connectionHandle = 1);
native SL::OpenTable_ReadEx(const table[], const column[], columnID[], connectionHandle = 1);
native SL::OpenTable_Update(const table[], const column[], columnID, connectionHandle = 1);
native SL::OpenTable_UpdateEx(const table[], const column[], columnID[], connectionHandle = 1);
native SL::OpenTable_Insert(const table[], connectionHandle = 1);

Were replaced to SL::Open | SL:OpenEx accordingly in v2.0 (The above functions are still used internally).

Creating a table:

PHP Code:

new handle = SL::Open(SL::CREATE, "players"); //Opening a valid handle to create a table called 'players'
SL::AddTableEntry(handle, "p_id", SL_TYPE_INT, 11, true);
SL::AddTableEntry(handle, "p_name", SL_TYPE_VCHAR, 24);
SL::AddTableEntry(handle, "p_password", SL_TYPE_VCHAR, 64);
SL::AddTableEntry(handle, "p_score", SL_TYPE_INT);
SL::AddTableEntry(handle, "p_posx", SL_TYPE_FLOAT);
SL::AddTableEntry(handle, "p_posy", SL_TYPE_FLOAT);
SL::AddTableEntry(handle, "p_posz", SL_TYPE_FLOAT);
SL::Close(handle);//Closing the previous opened handle. 


Inserting data:

PHP Code:

new handle = SL::Open(SL::INSERT, "players");
SL::ToggleAutoIncrement(handle, true);//Toggles auto increment, SL::Close will return the ID of the latest inserted ID
SL::WriteString(handle, "p_name", ret_pName(playerid));
SL::WriteString(handle, "p_password", UserInfo[playerid][p_password]);
SL::WriteInt(handle, "p_score", 0);
SL::WriteFloat(handle, "p_posx", 0.0);
SL::WriteFloat(handle, "p_posy", 0.0);
SL::WriteFloat(handle, "p_posz", 0.0);
UserInfo[playerid][p_id] = SL::Close(handle);//Returns the ID of the latest inserted ID 


Updating data:

PHP Code:

new handle = SL::Open(SL::UPDATE, "players", "p_id", UserInfo[playerid][p_id]);
SL::WriteInt(handle, "p_score", GetPlayerScore(playerid));
SL::WriteFloat(handle, "p_posx", UserInfo[playerid][p_posx]);
SL::WriteFloat(handle, "p_posy", UserInfo[playerid][p_posy]);
SL::WriteFloat(handle, "p_posz", UserInfo[playerid][p_posz]);
SL::Close(handle); 


"Reading"data:

PHP Code:

new handle = SL::Open(SL::READ, "players", "p_id", UserInfo[playerid][p_id]);
SL::ReadInt(handle, "p_score", UserInfo[playerid][p_score]);
SL::ReadFloat(handle, "p_posx", UserInfo[playerid][p_posx]);
SL::ReadFloat(handle, "p_posy", UserInfo[playerid][p_posy]);
SL::ReadFloat(handle, "p_posz", UserInfo[playerid][p_posz]);
SL::Close(handle);//You must close the handle. 


In order to run the action, you must use:

Code:

native SL::Close(handle)

- If you want to update a lot of data of a single shot, then use SL::UPDATE.

Download:

Easy SQLite v2.0


You can comment any suggestions, bugs, typos or feedback below.