[TUT] SQlite with explanations and examples

[Laxante101]

Hi!, my name is Laxante101, I'm a .Lua developer, And today I will try to help you understand SQlite 

 

                                                                                                         WHAT IS SQLITE?

SQLite is a relational database management system (RDBMS) that does not require a separate server to function. Unlike database systems like MySQL or PostgreSQL, which need an active server process, SQLite is "embedded" (that is, the database is stored in a local file on disk), and operations with they are made directly within the program that uses it.
Luckily for us, SQLite is already built into the MTA. This means that you can use SQLite databases directly in your MTA Lua codes without having to install anything additional "external" or configure an external database server. SQLite support is native to MTA, facilitating the use of databases for persistent storage of in game information.
It is normally used on servers that do not use the login panel, they use SQlite so their information that would be saved in accounts is now saved in the .db file.
Or on servers that don't use the original game money, they create other types of “money” like diamonds, stars which are all saved every day, well that's usually the case

---

                                  IMPORTANT DETAILS

•  Simplicity: Doesn't require anything other than a notepad
•  Portabilidade: Data is stored in a single .db  file, which makes backup and migration easier. 


SQlite Global Structure
 

1. Connect to Database with 

   dbConnect

2. Execute Queries using

   dbExec

 

          to modify data and

dbQuery

to recover data.

         3. Manipulate Results with

dbPoll

and process the returned data.

---

                                         Connection to the Database

the database file can be created automatically when connecting. The database file is saved in the server's root folder.

local db = dbConnect("sqlite", "storage.db")

or if you want to automatically create a folder for your file, or to save your .db files, if it is not created it creates it automatically, if it is created it just puts the file in the path.

local db = dbConnect("sqlite", "db/storage.db")

in this case the "db" folder will be created

---

                                               Creating Tablese data, you first need to create tables in the database. This is done using normal SQL commands like

consulta local = [[ 
    CREATE TABLE IF NOT EXISTS players ( 
        id INTEGER PRIMARY KEY AUTOINCREMENT , 
        name TEXT , 
        score INTEGER
     ) ]] 
dbExec ( db , query )            

Create a player table if it doesn't already exist

In this case, we are creating a players table with three columns:

• ID
• Name
• Score
   

Table Structure
 

• TEXT: STRINGS
• INTEGER: STORAGE NUMBERS
• REAL:STORES FLOATING POINT NUMBERS
• BLOB: STORES BINARY DATA (images, files).
• NULL: NIL VALUE

If you don't understand what a string or Boolean values are, learn about data types VIDEO HERE 

---

                                                      Entering Data

To add data to the database we use the SQL command

 INSERT INTO

function AddPlayerLX1(name, score)
    local query = "INSERT INTO jogadores (name, score) VALUES (?, ?)"
    dbExec(db, query, name, score)
end

AddPlayerLX1("juninho", 100)

The INSERT INTO command inserts a new player with the name "juninho" and score 100 into the players table.

Note: The question marks (?) are placeholders for the values that will be passed to dbExec. This helps prevent SQL injection. 
 

---

                                                 Deleting Data

To remove data from the database, we use the SQL DELETE command

DELETE

function DeletePlayerLX2(name)
    local query = "DELETE FROM players WHERE name = ?"
    dbExec(db, query, name)
end

DeletePlayerLX2("juninho")

 

 

---

                                                Error Handling

It is important to verify that database operations were successful. MTA doesn't automatically return detailed errors other than "/debugscript (1, 2, 3)" so let's add checks.

function AddPlayerLX3(name, score)
    local query = "INSERT INTO jogadores (name, score) VALUES (?, ?)"
    local sucess = dbExec(db, query, name, score)

    if sucess then
        outputDebugString("Sucess.")
    else
        outputDebugString("Error.")
    end
end

                                                                                                             IF SUCESS THEN
the success variable stores the result of the dbExec function. If the SQL command execution was successful (i.e. the player was added to the database), success will be true.
If success is true, the code inside the if block will be executed.

                                                                                                 else
If the success value is false (that is, if the player's insertion fails for some reason, such as an error in the database connection or SQL query), the code inside the else block will be executed

---

                                   Optimizations and Best Practices

Optimizations are great for your day-to-day life as a developer, this makes your code more beautiful, less likely to give you server overload errors, etc...
Remember to use dbFree to flush queries after use, especially if you are not using dbPoll.

local LX4 = dbQuery(db, "SELECT * FROM players")
dbFree(LX4)

There are several ways to create clean code, I left just one of them
Let's be clear: Since the SQLite database is a flat file, you can back it up by simply copying the .db file. To restore the database, simply replace the old file, this is a big advantage of using SQlite instead of using external databases.
OBS: All codes were made based on an example of player name and id points, not made in a real project. (just to make it clear





That's all I remembered, if there's anything I didn't make clear here you can say it and I'll edit it or respond to you