SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 09/07/2013, 11:11 AM   #1
Konstantinos
Spam Machine
 
Konstantinos's Avatar
 
Join Date: Dec 2011
Posts: 11,982
Reputation: 1392
Default How to use SQLite

« Last updated: May 30, 2016 »

Introduction:

First of all, I want to say that it's my first tutorial and that I've seen people using SQLite lately with few incorrect ways and since SQLite is great and it can be used for SA:MP even in nowadays, I decided to write a tutorial about how to use SQLite for a very simple register/login system. I know there are not many tutorials for SQLite and that also encouraged me for writing this tutorial.

You may wonder what is SQLite, where you can find it or how to install it. I'm going to explain everything, so no worries!

- SQLite is a SQL Database system which is an open source.
- SQLite was built into the SA:MP server, so you don't need to search for it, since the server package includes it.
- You don't even need to include "a_sampdb.inc" file, as long as we use "a_samp.inc" and thats in all the cases.

It does not require any plugin or anything else. You can find the database to the scriptfiles directory (when it will be created) and don't forget to create the file extension .db

You can manage your database file with a manager, I recommend SQLite Database Browser. If you ****** it, you'll find results, goto the first one and just download it.

SQLite has been updated and it doesn't use only 10 natives anymoreIf you want to take a look at the functions, you can goto to the SA:MP Wiki (http://wiki.sa-mp.com/wiki/SQLite).
Some of those functions have not been documented though. Those are:
PHP Code:
native db_get_field_int(DBResult:resultfield 0);
// directly returns an integer value according to the field ID specified
native Float:db_get_field_float(DBResult:resultfield 0);
// directly returns a float value according to the field ID specified

native db_get_field_assoc_int(DBResult:result, const field[]);
// directly returns an integer value according to the field name specified
native Float:db_get_field_assoc_float(DBResult:result, const field[]);
// directly returns an float value according to the field name specified

native db_get_mem_handle(DB:db);
// Get memory handle for an SQLite database that was opened with db_open.
native db_get_result_mem_handle(DBResult:result);
// Get memory handle for an SQLite query that was executed with db_query.

native db_debug_openfiles();
native db_debug_openresults(); 
For the last two functions, it contains "debug" on their name so it is pointing out what their use could be but I couldn't seem to be able to get any output after calling them and doing stuff.


Two new console variables were added in 0.3.7 R2:

db_logging - Logs sqlite db_* function errors to the main server_log.
db_log_queries - Logs all sqlite db_query() calls including the query string.


Let's get started:

Whenever a server starts, the database should be opened too. That means that in OnGameModeInit or in OnFilterScriptInit callback, we must open our database. The function we will use is: db_open. I believe the name is self-explanatory. If you read the wiki before, you will notice that this function returns an index (starting at 1) of the database connection (return type for this function has changed since version 0.3.7 R2). We've to store the database connection somewhere. In order to do that, we need to create a variable with the tag DB: before the name of our variable. Remember that it should be a global variable so we can access the id in other callbacks too!
PHP Code:
#include <a_samp> 
// Including samp's main include file is a must (which includes SQLite natives as well).

native WP_Hash(buffer[], len, const str[]); // Including the native of Whirlpool 
PHP Code:
new
    
DBDatabase// The variable we mentioned before to store our database connection. Notice the DB: tag before its name

main() {}

public 
OnGameModeInit()
{
    if ((
Database db_open("server.db")) == DB0)  // We open the database with name server.db and store the database connection to the "Database" variable. Directly checking if the connection handle is invalid
    
// If it returns 0, the the database connection failed so let's inform us through console. You may exit the server if you want to.
        
print("Failed to open a connection to \"server.db\"");
    }
    else 
// if it created successfully a connection, let's execute those two queries:
    
{
        
db_query(Database"PRAGMA synchronous = OFF"); // Whenever the synchronous is FULL, it writes the data with safety but it takes too long when there are more than 100 rows to insert/update. By turning it OFF, we gain speed but a risk for the file to be currupted in a bad situation. Keep getting backups frequently.
        
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)");
    }
    return 
1;

I would strongly recommend to execute the query about creating the table only once so it doesn't have to execute it everytime the server starts.

You may wonder why do we need the index of the database connection and why don't we create a local variable to open the database. The example of code bellow will explain it itself. We send a query to create the table "users" if it does not exist. And we have the fields: userid, username, password and admin. As you can see, we use INTEGER PRIMARY KEY AUTOINCREMENT for the "userid" field and that will be auto increased everytime we insert a new row. Do not worry if there is a table inside the database. It was created by SQLite to store the last row in a specific table. The "username" is string, so we use VARCHAR(lenght). The max lenght of the player's name is 24; however when a player connects, any name length beyond 20 characters gets rejected. The length specified doesn't really matter though as it is ignored by SQLite (it converts VARCHAR(N) to TEXT) anyway. We have to use the lenght inside parentheses such as (24) for our case . You will also notice the COLLATE NOCASE. That's very important to use it because if a player registers with the name "Zeus" and then he changed his name to "ZeuS", just the case of the letters, it will force him to register again. As long as we use "COLLATE NOCASE", "Zeus" and "ZEUS" or anything else is the same! Same goes for the "password" field about VARCHAR datatype. 129 lenght like Whirlpool (Actually it is 128 characters + NULL for PAWN but as we said, the length itself is ignored). Last, you will see that the field "admin" has the following INTEGER DEFAULT 0 NOT NULL. We set the default value to 0, so we don't need to insert 0 when a player will register. That's a very useful thing when you get more columns and the query's size will be long enough.

PHP Code:
public OnGameModeExit()
{
    
db_close(Database);// We need the index of the database connection in order to close the database!
    
return 1;

We need to close the database when the server closes too. That is done using db_close function which requires the index of the database connection we stored previously to "Database" variable.

We have the basic at the moment, opening/closing our database. We need to store player's variables for the Userid, name, password and admin. It's just an example and those are very basic. You can create your own after reading this tutorial (I hope you will understand and I will explain it as simple as I can). I also use Whirlpool to hash the passwords - security of our players IS important! Having the password saved as plain text is really bad idea. I would also recommend using Salt along with the hash and there are tutorials for that on ****** if you want to learn more about it.
We'll need the enumeration and a variable (array) to store the data for the players.
PHP Code:
enum USER_DATA
{
    
USER_ID,
    
USER_NAME[MAX_PLAYER_NAME],
    
USER_PASSWORD[129],
    
USER_ADMIN
};

new
    
User[MAX_PLAYERS][USER_DATA]; 
One other thing to consider doing is re-defining MAX_PLAYERS as by default is defined as 1000. Anyway, I would suggest you to add those under the defines/includes. Don't forget to reset the variables when a player connects/disconnects, just for safety. People can join with the same playerid and get the statistics of the previous user who might was owner! Using this method is the best in my opinion to save time writing all the data to reset then and it is evenly fast!

PHP Code:
public OnPlayerConnect(playerid)
{
    new 
        
tmp[USER_DATA];

    
User[playerid] = tmp;
    return 
1;

PHP Code:
public OnPlayerDisconnect(playeridreason)
{
    new 
        
tmp[USER_DATA];

    
User[playerid] = tmp;
    return 
1;

It's time to check whether a player is registered or not! But before that, let's define the dialog IDs. I find myself using an enumerator more convinient than messing with #define pattern.
PHP Code:
enum
{
    
DIALOG_REGISTER// DIALOG_REGISTER is 0
    
DIALOG_LOGIN // DIALOG_LOGIN is 1
}; 

A new specifier was added to format function by the way and that's %q. It escapes strings for SQLite. If you do not use it, then you may be victim of SQL injection. Safety after all.

PHP Code:
public OnPlayerConnect(playerid)
{
    
// Resetting our variables, like we said before
    
new 
        
tmp[USER_DATA];

    
User[playerid] = tmp;

    
// ---

    
GetPlayerName(playeridUser[playerid][USER_NAME], MAX_PLAYER_NAME); // We store player's name to that variable, just to prevent from getting the name all the time. It saves time!
    // It's also a better method than calling a function that returns the name. Imagine now if you want to use the player's name a couple of times, you'll have to keep calling the function - not efficient at all.

    
new
        
Query[82], // A string to store our query. You can always calculate the query's length + the length of our arguments passed to the specifiers + 1 for NULL if you are not lazy. Always though make sure the size is long enough to store the whole query otherwise it will fail.
        
DBResultResult// The result. Notice the DBResult: tag before the variable, just like the DB: tag we saw before. Those are necessary otherwise you'll get warning for tag mismatch.

    
format(Querysizeof Query"SELECT password FROM users WHERE username = '%q' LIMIT 1"User[playerid][USER_NAME]); // We select the password only so we can check later on if the passwords match, only if the player is already registered of course.
    
Result db_query(DatabaseQuery); // We store the result index returned by db_query so we can retrieve the rows of that result and also free the result.
    
    
if (db_num_rows(Result))// If there are rows (in our case the maximum number of rows in 1 as there cannot exist more players with that name), that means the player is registered
    
{
        
db_get_field_assoc(Result"password"User[playerid][USER_PASSWORD], 129);// And we store the password to our variable, so we'll be able to check if the password is equal to the password the player enters later in the dialog input
        
ShowPlayerDialog(playeridDIALOG_LOGINDIALOG_STYLE_PASSWORD"Login""Type in your password below to log in.""Login""Leave");// Showing the dialog for login.
    
}
    else 
// Otherwise.. meaning that there are not rows, so the player is going to register
    

        
ShowPlayerDialog(playeridDIALOG_REGISTERDIALOG_STYLE_PASSWORD"Register""Type in a password below to register an account.""Register""Leave");// Showing the dialog to the player to register
    
}
    
db_free_result(Result);// And we free the result
    
return 1;

db_num_rows and db_get_field_assoc are now protected against NULL references and they will not crash the server like they used to. db_get_field_assoc also returns 0 if the column index is not available.

It's not necessary to use the grave accent character (`) like some other people do, it's just a habbit. It's not a rule to use it and neither a rule not to use them at all. Both cases work just fine! You should remember that when you insert a string into a query, you must escape the text especially if the text was passed was inputted by a person to avoid SQL Injection as we mentioned before. When you use for string, we have to use apostrophe ' ' around; however, it's not necessary to use it for integers and floats.
Using LIMIT clause will stop searching for more results if the number of maximum rows specified is reached. Another thing to take into consideration is using indexes - it can surely speed up the query. You can create an index for the "username" field but you better not have it in the script itself. You can execute it through the SQLite Browser. In case you are interested, here's how it would look:
PHP Code:
CREATE INDEX index_name ON table_name (column_name);
// in our case, that would be:
CREATE INDEX index_username ON users (username); 
You may as well create it as UNIQUE INDEX since there will be no more than one user with that name.

Anyway, we execute the SQL query; however, we store the result to our variable "Result". After that, we use the db_num_rows function which returns the number of rows from the SQL query. It takes as parameter a result, and that's the result we stored when executing the query.
In case we have rows (not 0), the player is registered. We want to get the result from the query. We selected the "password" field and we will store it to our variable that stores the hashed password. Then we're going to compare the two passwords and check if the password is correct in order to load the data to the player, else if the password is incorrect, it keeps sending the dialog with the message that the password was incorrect. You may add that the player has 3 attempts to give the correct password, but it's up to you. I will only explain you how SQLite works and how you can use it for a register/login system. Let's take a look below:
PHP Code:
db_get_field_assoc(Result"password"User[playerid][USER_PASSWORD], 129); 
If there are rows, the player is registered, and we get the data that the "password" field contains.
The parameters are:
PHP Code:
(DBResult:dbresult, const field[], result[], maxlength
Our result, the field's name "password", somewhere to store it - a destination array, the max lenght (the variable's size is 129 which is 128 by WP_Hash output + 1 NULL).
Bare in mind that using [I]db_get_field[I] function is faster because it doesn't have to search for the name of the field we specified if we know the order.


That's it. If there are rows, it stores the password to User[playerid][USER_PASSWORD] and shows us the dialog to login, otherwise to register. But it's not the end of it. We used a SELECT statement, and that needs a result.

WARNING: Whenever we use SELECT statement, we must free the result.
PHP Code:
db_free_result(Result); 
in order to free the memory. Otherwise, we're going to have memory leaks. If you execute an UPDATE or INSERT INTO statement, you don't need to free any result since we don't have one.

Let's move on! The callback for the dialog's response:
PHP Code:
public OnDialogResponse(playeriddialogidresponselistiteminputtext[])
{
    switch (
dialogid)
    {
        case 
DIALOG_REGISTER:
        {
            
// if the player did not response to the dialog, meaning pressed either Esc or ckicked on "Leave" button, will be kicked
            
if (!response) return Kick(playerid);
            
            if (!(
<= strlen(inputtext) <= 20))
            { 
// if the length of the desired password is not beetween 3-20 characters
                
SendClientMessage(playerid0xFF0000FF"[ERROR]: Invalid length on the password. It should be between 3-20 characters" );// send the message error for the valid lenght of the password
                
ShowPlayerDialog(playeridDIALOG_REGISTERDIALOG_STYLE_PASSWORD"Register""Type in a password below to register an account.""Register""Leave" );// re-show the register dialog
                
return 1// stop the code below from being executed
            
}
            
            new 
// a query, remember to change the size of it if you add more stuff
                
Query[208];
                
            
WP_Hash(User[playerid][USER_PASSWORD], 129inputtext); // Hash the password from the inputtext and store it to User[playerid][USER_PASSWORD]
            
format(Querysizeof Query"INSERT INTO users (username, password) VALUES ('%q', '%s')"User[playerid][USER_NAME], User[playerid][USER_PASSWORD]);// Insert into users the name and the password. The userid gets increased automatically and the admin is by default 0 value. We don't have to escape password as the hashed output provided by Whirlpool contains only alphabet characters and numbers.
            
db_query(DatabaseQuery);// execute the SQL query

            
SendClientMessage(playerid0x00FF00FF"[SERVER]: You have just registered to our server! You have been automatically logged in!");//send a message just to inform the player that he's now registered!

            // We have to retrieve the value generated from the Auto Increment for the "userid" field.
            // last_insert_rowid() function returns the last inserted row in the last session as its name suggests already.
            
new
                
DBResultResult;
                
            
Result db_query(Database"SELECT last_insert_rowid()"); // it will return the value for field "userid" generated
            
User[playerid][USER_ID] = db_get_field_int(Result); // The difference in this function except that it doesn't support the name of the field but the field ID instead, it is that returns an integer directly without having to store it to a temporary string and then use strval function to convert string to integer.
            // There is a second parameter "field" which is by default 0. We know that only 1 row and 1 field will be selected so we may not specify it.
            // REMEMBER! Fields starts from 0.

            
db_free_result(Result); // Last, we free the result memory

        
}
        case 
DIALOG_LOGIN:
        {
            
// if the player did not response to the dialog, meaning pressed either Esc or ckicked on "Leave" button, will be kicked
            
if (!response) return Kick(playerid);
            
            new
                
buf[129]; //make a string to store the hashed password we entered

            
WP_Hash(buf129inputtext); // Hash the password the player inputs

            
if (strcmp(bufUser[playerid][USER_PASSWORD]))// Comparing if the string1 is not equal to string2. If they're not, it returns a value different from 0.
            
// if the password is incorrect
                
SendClientMessage(playerid0xFF0000FF"[ERROR]: Incorrect password");// let them know by sending a message
                
ShowPlayerDialog(playeridDIALOG_LOGINDIALOG_STYLE_PASSWORD"Login""Type in your password below to log in.""Login""Leave" );//re-show the login dialog
                
return 1// stop the code below from being executed
            
}
            
            
// if the player responded to the dialog and the password is correct, let's load the rest of their data
            
new
                
DBResultResult// to store the result, we have declared a string already above (that being "buf")

            
format(bufsizeof buf"SELECT * FROM users WHERE username = '%q' LIMIT 1"User[playerid][USER_NAME]);// we select every field this time from the table users using * which means ALL
            
Result db_query(Databasebuf); // we store the result index so we can extract the data and free its result later
            
            
if (db_num_rows(Result))// if there are rows - number of rows are not 0 in other words
            
{
                
// get the data from the result where the field is "userid" and return directly its value to store it to our variable
                
User[playerid][USER_ID] = db_get_field_assoc_int(Result"userid");
                
User[playerid][USER_ADMIN] = db_get_field_assoc_int(Result"admin");
                
// Same as above, getting the data from the "admin" field and store it to the variable associated with the player's admin level

                
SendClientMessage(playerid0x00FF00FF"[SERVER]: You have successfully logged in to your account!");// and a message to let them know they have successfully logged in!
            
}
            
db_free_result(Result);//freeing the memory result, REMEMBER "SELECT" statement!
        
}
        default: return 
0// dialog not found, search in other scripts
    
}
    return 
1// dialog was found

As of SA-MP 0.3x, any action (client messages, dialogs etc) taken directly before Kick function will not be sent/shown. If you want to show a message to the player, you will have to delay the kick. An example can be found on the SA:MP Wiki.

The only thing left right now is updating the player whenever they disconnect from the server.
PHP Code:
public OnPlayerDisconnect(playeridreason)
{
    new
        
Query[51]; // a string to store the query

    
format(Querysizeof Query"UPDATE users SET admin = %d WHERE userid = %d"User[playerid][USER_ADMIN], User[playerid][USER_ID]);// UPDATE table SET field = value WHERE field = something
    // We update the table users and we set to the "admin" field the value we insert. If the value has changed before in-game, it will update it with the new one. Where the userid is the unique ID per-player.
    
db_query(DatabaseQuery);//executing the query

    // --- 

    // Resetting our variables..
    
new 
        
tmp[USER_DATA];

    
User[playerid] = tmp;
    return 
1;

Keep in mind that you should only update data that are needed when a player disconnect. I had to use an example for you, readers, but it's recommended that if you later on have a command that sets the player's level to update the field in the table directly there so you won't have to update it when a player disconnects.

You have also noticed that we update according to player's unique ID in our database because as you have probably figured out searching by integers is much faster than strings.

Another example could be the player's IP in which in this tutorial I have not used. Updating the IP to the table after player logs in is the best place to do it.

That's the end of the tutorial I guess! I wish I explained them well for you and you can now make your own register/login system.
__________________
Life is like riding a bicycle. To keep your balance, you must keep moving.

[Tutorial] How to use SQLite
[FilterScript] Tune System

Last edited by Konstantinos; 30/05/2016 at 02:20 PM.
Konstantinos is offline   Reply With Quote
Old 09/07/2013, 11:35 AM   #2
Konstantinos
Spam Machine
 
Konstantinos's Avatar
 
Join Date: Dec 2011
Posts: 11,982
Reputation: 1392
Default Re: How to use SQLite

Thank you very much. I hope the tutorial was well-explained for everyone. It took me an hour to write it, but I believe it's worth since there're not many tutorials about SQLite or not well-explained like people would like!
__________________
Life is like riding a bicycle. To keep your balance, you must keep moving.

[Tutorial] How to use SQLite
[FilterScript] Tune System
Konstantinos is offline   Reply With Quote
Old 09/07/2013, 12:18 PM   #3
Niko_boy
High-roller
 
Niko_boy's Avatar
 
Join Date: Aug 2010
Location: Somewhere i belong
Posts: 1,423
Reputation: 138
Default Re: How to use SQLite

i never used DB_Escape tbh , but idk how to check for SQL injection actually i dont know what it is
i am a regular SQLIte user btw
nice tutorial anyways
__________________
nope[IMG]http://*******/1r0SOkH_[/IMG]
•••[CLOSED]LCS•Freeroam•DM•Stunts•••AutoArena [0.3z][No SkinShot][sixtytiger.com]Want a decent Attack Defend Gamemode?
N/A176.31.229.148:7830Get This! Attack-Defend(v2.3.1)
Niko_boy is offline   Reply With Quote
Old 09/07/2013, 12:31 PM   #4
Konstantinos
Spam Machine
 
Konstantinos's Avatar
 
Join Date: Dec 2011
Posts: 11,982
Reputation: 1392
Default Re: How to use SQLite

You should. I will give you an example of what SQL Injection is.

Let's say your mode has a dialog (input) and someone insert a "name" to check something for a user. He can input:
Code:
"Zeus';DROP TABLE users;"
And it will just delete your table "users". DB_Escape is used for this reason. To prevent someone from doing something bad to your database. You can also read more about DB_Escape/SQL Injection to the wiki (http://wiki.sa-mp.com/wiki/Escaping_Strings_SQLite) or wikipedia (http://en.wikipedia.org/wiki/SQL_injection)

You just need to check any string you insert in a query with:
Code:
// An example:
"SELECT username FROM users WHERE ip = '%s'", DB_Escape( ip ) // etc..

Glad to hear that you use SQLite, it's great and I can confirm it because I know that a very popular server in the past used SQLite and had over 500,000 registered users and everything worked fine!

Thanks for your kind words.
__________________
Life is like riding a bicycle. To keep your balance, you must keep moving.

[Tutorial] How to use SQLite
[FilterScript] Tune System
Konstantinos is offline   Reply With Quote
Old 09/07/2013, 01:09 PM   #5
Yordan_Kronos
Huge Clucker
 
Yordan_Kronos's Avatar
 
Join Date: Feb 2012
Location: Targovishte,Bulgaria
Posts: 275
Reputation: 19
Default Re: How to use SQLite

Excellent 10/10
__________________
Mappings
Yordan_Kronos is offline   Reply With Quote
Old 09/07/2013, 01:26 PM   #6
DaRk_RaiN
High-roller
 
DaRk_RaiN's Avatar
 
Join Date: Sep 2012
Posts: 989
Reputation: 207
Default Re: How to use SQLite

Awesome tutorial, i'll start reading.
Way to go Dwane

Last edited by DaRk_RaiN; 04/12/2013 at 10:47 AM.
DaRk_RaiN is offline   Reply With Quote
Old 09/07/2013, 01:28 PM   #7
StreetGT
Banned
 
Join Date: Dec 2008
Location: Portugal
Posts: 1,079
Reputation: 108
Default Re: How to use SQLite

Good job!
It's really useful for people starting with SQL!
Approved!
StreetGT is offline   Reply With Quote
Old 09/07/2013, 01:40 PM   #8
Konstantinos
Spam Machine
 
Konstantinos's Avatar
 
Join Date: Dec 2011
Posts: 11,982
Reputation: 1392
Default Re: How to use SQLite

Quote:
Originally Posted by Yordan_Kronos View Post
Excellent 10/10
Quote:
Originally Posted by DaRk_RaiN View Post
Awesome tutorial, i'll start reading.
Way to go Dwane
Quote:
Originally Posted by StreetGT View Post
Good job!
It's really useful for people starting with SQL!
Approved!
Thank you! I really appreciate it.

@DaRk_RaiN - Glad to see you remember my old name, dude!
__________________
Life is like riding a bicycle. To keep your balance, you must keep moving.

[Tutorial] How to use SQLite
[FilterScript] Tune System
Konstantinos is offline   Reply With Quote
Old 09/07/2013, 03:34 PM   #9
Red_Dragon.
High-roller
 
Red_Dragon.'s Avatar
 
Join Date: Sep 2012
Posts: 1,401
Reputation: 46
Default Re: How to use SQLite

Pretty awesome!
Red_Dragon. is offline   Reply With Quote
Old 09/07/2013, 04:37 PM   #10
Zeyo
Little Clucker
 
Join Date: Jan 2012
Posts: 17
Reputation: 0
Default Re: How to use SQLite

Yay, its awesome. Thank you Zeus_
Zeyo 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 help CreativityLacker Scripting Help 2 12/05/2013 10:37 AM
SQLITE help Ryan_Obeles Scripting Help 9 28/04/2013 01:55 AM
Little help on sqlite FireCat Help Archive 4 20/08/2011 01:45 PM


All times are GMT. The time now is 08:03 AM.


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