[__]

How to: SQLite
Tutorial by __

Introduction
A lot of you may not understand how useful SQLite actually is, which is why I'm creating this tutorial.

People spend hours tinkering with MySQL in SA-MP, they spend ages tinkering with things trying to get MySQL to work - it's honestly loads of hassle that's not really worth doing. SQLite gives you almost all of the advantages you get in MySQL, but SQLite doesn't require external plugins - because it's natively included in SA-MP, all database information is saved in portable file(s) and you don't have to worry about configuring loads of details for SQLite - it's just as good as plug and play!

Contents of this tutorial

1. Introduction
2. Basic SQL pointers
3. Getting to work
4. Database editor
5. Initiating a connection
6. Select queries
7. Update queries
8. Delete queries
9. Freeing results
10. Basic Keywords/Clauses

Basic SQL pointers
SQL is essentially a universal language amongst database management, almost all databases rely on SQL.

SQL stands for Structured Query Language.

In SQL, there are many types of queries you can formulate. There are many different keywords allocated to SQL that can be manipulated to formulate a final query that would execute against a database.

SQL databases have tables - like a normal table in say a word document, you have rows and columns. Rows are the record entries and fields are the specific items. See here for a much more practical answer.

SQL is used world-wide, MySQL, SQLite and many other database systems are based around SQL (as the names obviously hint). SQL is used by such a vast array of companies/organisations and individuals, it's even used by government agencies.

SQLite databases in SA-MP exist in the scriptfiles directory, but can be anywhere else inside the directory (i.e. sub folders).

Getting to work
As said above, there are many different keywords that can be manipulated to formulate a query. Some of the most popular keywords are as follows:

• Select
• Update
• Delete

The keywords are generally used to execute a query, but compiled with the queries tend to be 'FROM' clauses to select which table the query should affect, and 'WHERE' clauses to help you refine your query, so you only limit your query to affect the data you want.

Many different scripters, coders and programmers use a different style of casing for formulating their queries - it doesn't particularly matter, but it's just like indentation in scripting - you do things the way you see fit, the code works and if you can read the code well and easily add on to it while it being neat enough for you to work with, then you stick to that form of indentation.

Database editor
Similar to phpMyAdmin for MySQL, there are many great editors you can use to edit your SQLite database, so you can organize tables and whatnot. I'd personally recommend SQLite Manager if you have Firefox.

If you don't have Firefox, this may be an adequate solution.

You can use database editors to create, edit and delete your tables and your database in general. Everything you don't want to do in queries can essentially be done in the editor.

Initiating a connection to your database

We can start off by creating a connection for our database, we'll start off at the top of our pawn file:

pawn Code:

#include <a_samp>

main() { } 

new DB: someDB; // Handle for database. Our queries need to use this so the queries know where to handle things

public OnGameModeInit() { 
   someDB = db_open("your.db"); // file 'your.db' will be our SQLite database
}

// We can now use SQLite throughout the gamemode

Select queries
A select query to get a players' motto from the SQLite database via a cmd would be like this:

pawn Code:

CMD:getmyname(playerid, params[]) {
    new szQuery[74], szOut[32], DBResult: query, szPlayerName[MAX_PLAYER_NAME];

    GetPlayerName(playerid, szPlayerName, MAX_PLAYER_NAME);
    format(szQuery, sizeof(szQuery), "select * from `players` where `playername` = '%s'", szPlayerName);
    query = db_query(someDB, szQuery);
    if(db_num_rows(query) > 0) {
        SendClientMessage(playerid, 0, "There is more than 0 rows, meaning there is at least 1 row!");
        SendClientMessage(playerid, 0, "So there is a registered account with my name!");
        db_get_field_assoc(query, "Motto", szOut, sizeof(szOut));
        SendClientMessage(playerid, 0, "Your motto is:");
        SendClientMessage(playerid, 0, szOut);
        db_free_result(query);
    }
    
    return 1;
}

Another thing with queries is that you have to select what you want, or select a wildcard to select absolutely everything. If you select the wildcard (*) then you're going to get every field from the row it finds saved to memory, which is a waste of memory and time - so it's worth only selecting what you need. Let's try this query again, efficiently.

pawn Code:

CMD:getmyname(playerid, params[]) {
    new szQuery[79], szOut[32], DBResult: query, szPlayerName[MAX_PLAYER_NAME];

    GetPlayerName(playerid, szPlayerName, MAX_PLAYER_NAME);
    format(szQuery, sizeof(szQuery), "select `Motto` from `players` where `playername` = '%s'", szPlayerName);
    query = db_query(someDB, szQuery);
    if(db_num_rows(query) > 0) {
        SendClientMessage(playerid, 0, "There is more than 0 rows, meaning there is at least 1 row!");
        SendClientMessage(playerid, 0, "So there is a registered account with my name!");
        db_get_field_assoc(query, "Motto", szOut, sizeof(szOut));
        SendClientMessage(playerid, 0, "Your motto is:");
        SendClientMessage(playerid, 0, szOut);
        db_free_result(query); // Clear the results saved in memory because we don't need them, they're in szOut
    }
    
    return 1;
}

We've performed a basic select query.

Update queries
Update queries are used to update records for rows.

Let's change a player's motto to something custom that they want.

pawn Code:

// prevent sql injection: https://sampwiki.blast.hk/wiki/Escaping_Strings_SQLite
#define MAX_INI_ENTRY_TEXT 80

stock DB_Escape(text[])
{
    new
        ret[MAX_INI_ENTRY_TEXT * 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;
}

CMD:setmotto(playerid, params[]) {
    if(isnull(params)) return SendClientMessage(playerid, 0, "/setmotto (your motto)");
    if(strlen(params) > 23) return SendClientmessage(playerid, 0, "Motto too long!");
    
    new szQuery[109], szPlayerName[MAX_PLAYER_NAME];
    
    GetPlayerName(playerid, szPlayerName, MAX_PLAYER_NAME);
    format(szQuery, sizeof(szQuery), "update `players` set `Motto` = '%s' where `playername` = '%s'", DB_Escape(params), szPlayerName);
    db_free_result(db_query(someDB, szQuery));
    return 1;
}

We've done everything right here to update a player's motto. We're escaping the input to prevent SQL injection and we're doing everything else right. We're even freeing the result we get.

Delete queries
You can delete data from your database with the DELETE keyword in SQL. This is quite a basic and useful keyword, but you need to specify what data you want to delete. You can use where clauses to specify which data you want to delete.

pawn Code:

CMD:deletehenry(playerid, params[]) {
   return db_free_result(db_query(someDB, "delete from `players` where `playerName` = 'Henry'"));
}

The code above will execute this query:

Quote:

delete from `players` where `playerName` = 'Henry'

This will search inside your 'players' table for any records that have the name of 'Henry' and it'll delete them all. This will delete records with a direct match, if you want to delete all records that have 'Henry' somewhere in the name (like, 'Henry_Carpet') then you'll need to use wildcards, you can do so like this:

Quote:

delete from `players` where `playerName` = 'Henry%'

Doing so will delete any records that have the player name of 'Henry' anywhere in the player name string. This won't delete records that have characters h, e, n, r and y, this will only delete records that have 'Henry' concatenated together as a part of your string.

Freeing results
Similar to the use in MySQL plugins, you can use a function to free a result set from memory.

Whenever you perform a select query, it will store data about the query in memory. When you're done with the data, you can simply free it from memory, giving space in memory for you to store more future queries.

You can free query data by using the db_free_result function. If you click the link, you will be given details in regards to the syntax of db_free_result.

Basic Keywords/Clauses
WHERE
The 'where' clause is used to extract records from your table/database with the criteria that you specify. You usually have to implement a 'where' clause in a query to limit which results you get.

An example of the 'where' clause in action:

Quote:

delete from `players` where `playerName` = 'Henry'

The code above deletes all records from your 'players' table who have the name of just 'Henry'.

ORDER BY
You can use this keyword to organize which order a query will return results in. This is very useful for code to operate a 'top list' of your users, or something. An example of this could be:

Quote:

SELECT * FROM `players` ORDER BY `playerScore` DESC

The code above grabs all players, and orders the query results by 'playerScore' in a descending order, which means anyone with a high 'playerScore' will be amongst the first returned as a result in the query above.