17.06.2011, 22:54
(
Last edited by __; 14/06/2012 at 10:07 AM.
)
How to: SQLite
Tutorial by __
IntroductionTutorial by __
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
- Introduction
- Basic SQL pointers
- Getting to work
- Database editor
- Initiating a connection
- Select queries
- Update queries
- Delete queries
- Freeing results
- Basic Keywords/Clauses
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
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
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;
}
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;
}
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;
}
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'"));
}
Quote:
|
delete from `players` where `playerName` = 'Henry' |
Quote:
|
delete from `players` where `playerName` = 'Henry%' |
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' |
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 |
