10.10.2015, 03:38
(
Last edited by ThePhenix; 24/10/2015 at 05:00 PM.
)
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.
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;
}
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);
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.
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
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.
Code:
native SL::Close(handle)
Download:
Easy SQLite v2.0
You can comment any suggestions, bugs, typos or feedback below.
