[Tutorial] How to: SQLite
#1

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.
Reply
#2

Reserved post space for when I exceed the character limit for the first post.
Reply
#3

Nice tutorial, great tutorials coming from you ___ hope to see more. SQLite tutorial is great, I guess this is the first and best SQLite tutorial now? I didn't see many before infact none...

EDIT:
pawn Code:
#include <a_sampdb>
Isn't needed, well I think its already included in a_samp though yeah, you could include it to have no mistakes and know what you're doing.

Maybe the comment you posted for "Reserving a place for the other part because of the limit" you could contain some information for opening, editing databases, where to get it etc.. I already know that you've contained that in the tutorial but for people that want to really learn SQLite maybe show them a bit more to it?
Reply
#4

http://www.sqlite.org/

they have everything you need to know about it on that site
Reply
#5

Quote:
Originally Posted by cessil
View Post
http://www.sqlite.org/

they have everything you need to know about it on that site
Not including the SA-MP functions and how to use SQLite in Pawn.

Quote:
Originally Posted by Lorenc_
View Post
Nice tutorial, great tutorials coming from you ___ hope to see more. SQLite tutorial is great, I guess this is the first and best SQLite tutorial now? I didn't see many before infact none...

EDIT:
pawn Code:
#include <a_sampdb>
Isn't needed, well I think its already included in a_samp though yeah, you could include it to have no mistakes and know what you're doing.

Maybe the comment you posted for "Reserving a place for the other part because of the limit" you could contain some information for opening, editing databases, where to get it etc.. I already know that you've contained that in the tutorial but for people that want to really learn SQLite maybe show them a bit more to it?
Read the first post entirely and you'll notice how I've gone over that information already.
Reply
#6

My bad then, theres a program already called "SQLite Database Browser 2.0 b1" For people that don't use firefox.

Only for windows as far as I know, I use it for everything..
Reply
#7

I know there are many other programs, but I've only listed one that I know that works well and is easy to use.
Reply
#8

Finished the tutorial.

Updated the SQLite editors list, added an explanation of clauses/keywords and finished the explanation of delete queries.
Reply
#9

Iv used MYSQL in the past (not in pawn)
and ofcorse the dreaded MS Access.

What your not telling us is where do we put the .db file?
How to create the tables?
My guess is the .db file goes into the "scriptfiles" folder?
And we are to use one of the SQLite editors to add/alter/delete tables?
I know there are query's for this but you do not explain any of that.

I will probably figure this out before you answer but for the next guy.. the info would be useful.

thanks!
Reply
#10

The .db file goes in the /scriptfiles/ directory, didn't think this would matter.

Tables are easily created depending on the editor you use - I don't think there's a real need to cover this as it's very basic and there's enough tutorials for this already.

SQLite editors can manipulate the database, yes, they can do everything your queries do not.

I have edited the main post to explain this a little better, thanks for your feedback.
Reply
#11

To be honest, I didn't read the tutorial but on look it looks great
Reply
#12

I've been hassling with y_ini, and I don't want to use MySQL, because it would be a bitch to work with. Thank goodness I came across this tutorial. You cleared a lot of things up for me.
Reply
#13

Quote:
Originally Posted by [HLF]Southclaw
Посмотреть сообщение
This is a great tutorial, exactly what I need, except from a few things...

Can you explain things such as what does 'free result' mean?
I've added this as #9 on the tutorial.

Quote:
Originally Posted by [HLF]Southclaw
Посмотреть сообщение
And how to create tables/create rows.
You should do this using one of the database editor tools. I don't really think it's worth explaining how to do it in SQL here.

Quote:
Originally Posted by [HLF]Southclaw
Посмотреть сообщение
I'm making a small player list database and I'm wondering how I 'add' a player row, will that row inherit the columns of that DB or will I have to update each column to create it?
You have to insert a row, then you can edit it. Sorry if that's a confusing answer, but I'm not sure I fully understood your question.
Reply
#14

Wow nice work, Btw is it like MySql? Connects server to website?
Reply
#15

Quote:
Originally Posted by Rudy_
Посмотреть сообщение
Wow nice work, Btw is it like MySql? Connects server to website?
Read the first post. It's basically a portable file with all the information stored in.
Reply
#16

Quote:
Originally Posted by VincentDunn
Посмотреть сообщение
I've been hassling with y_ini, and I don't want to use MySQL, because it would be a bitch to work with. Thank goodness I came across this tutorial. You cleared a lot of things up for me.
MySQL is still great if you want to work with external tools (namely, a web control panel/UCP).
Reply
#17

Love the guide man. Didn't know much about SQLite, considering I barely ever use it.
Reply
#18

Quote:
Originally Posted by [HLF]Southclaw
Посмотреть сообщение
Thanks for the quick response!

Sorry I didn't explain well, basically I just want to add a record when a player registers with basic information such as the player name and IP. And also a Primary Key as the player's ID, so this can act as a 'registered players count' also.


So when the player registers, what do I format the query as and what keyword do I use?


Edit: Think I figured it out! "INSERT INTO" should create a new row?
Yes. If you use the editor to create your database structure (your fields, etc.), then you can create an 'ID' field and set that as the auto-incrementing primary key. Every time you use "INSERT INTO" for that table, it will add your new row and it will set the ID to +1 from the previous row's ID field.

You can then use a SELECT query to get the highest ID to retrieve the count by the method you want:
Код:
SELECT ID from players ORDER BY ID DESC LIMIT 1
Something like the code above should work.

Alternatively, you could just use the num_rows function which calculates how many rows there are in the table. If you rely on the "ID" field, deleted accounts won't be considered and will still add to the count.

For that, you can use something like this for your query:
Код:
SELECT COUNT(*) FROM players
with this function. You don't want to use * to select everything, since you're just selecting a count of how many users there are, selecting * selects everything in your table and would frankly be a waste of memory.
Reply
#19

It's a bit slow at updating queries.

Takes 3secs to run this code.

Код:
stock SaveObjectVariables(ObjectId) {
	KillTimer(oTimerId[ObjectId]);

	new Query[250],
		DBResult: Result;

	oDB = db_open("Objects.db");
	format(Query, 250, "SELECT * FROM object_data WHERE Id=%d", ObjectId);
	Result = db_query(oDB, Query);

	if(db_num_rows(Result) != 1) {
		format(Query, 250, "DELETE FROM object_data WHERE Id=%d", ObjectId), db_free_result(db_query(oDB, Query));
		format(Query, 250, "INSERT INTO object_data (Id) VALUES (%d)", ObjectId), db_free_result(db_query(oDB, Query));
	}

	db_free_result(Result);
	format(Query, 250, "UPDATE object_data SET ModelId=%d WHERE Id=%d", oModelId[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET Spawned=%d WHERE Id=%d", oSpawned[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnWorld=%d WHERE Id=%d", oSpawnWorld[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnInterior=%d WHERE Id=%d", oSpawnInterior[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnX=%f WHERE Id=%d", oSpawnX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnY=%f WHERE Id=%d", oSpawnY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnZ=%f WHERE Id=%d", oSpawnZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRX=%f WHERE Id=%d", oSpawnRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRY=%f WHERE Id=%d", oSpawnRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRZ=%f WHERE Id=%d", oSpawnRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosWorld=%d WHERE Id=%d", oPosWorld[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosInterior=%d WHERE Id=%d", oPosInterior[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosX=%f WHERE Id=%d", oPosX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosY=%f WHERE Id=%d", oPosY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosZ=%f WHERE Id=%d", oPosZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRX=%f WHERE Id=%d", oPosRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRY=%f WHERE Id=%d", oPosRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRZ=%f WHERE Id=%d", oPosRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET Timestamp=%d WHERE Id=%d", oTimestamp[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET IsGate=%d WHERE Id=%d", oIsGate[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedX=%f WHERE Id=%d", oClosedX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedY=%f WHERE Id=%d", oClosedY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedZ=%f WHERE Id=%d", oClosedZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRX=%f WHERE Id=%d", oClosedRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRY=%f WHERE Id=%d", oClosedRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRZ=%f WHERE Id=%d", oClosedRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedX=%f WHERE Id=%d", oOpenedX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedY=%f WHERE Id=%d", oOpenedY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedZ=%f WHERE Id=%d", oOpenedZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRX=%f WHERE Id=%d", oOpenedRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRY=%f WHERE Id=%d", oOpenedRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRZ=%f WHERE Id=%d", oOpenedRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	db_close(oDB);

	printf("Object %d has saved.", ObjectId);

	oTimerId[ObjectId] = SetTimerEx("UpdateObject", 500, true, "d", ObjectId);
	return true;
}
Wish it was a little faster.
Reply
#20

Quote:
Originally Posted by TheLazySloth
Посмотреть сообщение
Wish it was a little faster.
Sweet mother of holy.

You can set more than one value in one query by separating them by commas.

Example.
Код:
UPDATE `Objects` SET X = 800.0, Y = 6.0, Z = 10.0 WHERE ID = 6
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)