[TUT] How to Create ID System by Database

[Vazern]

Yesterday evening, I started my apprenticeship with db, I always wanted to learn, because with it, I can do mods, which I could not do without.

My introduction with Database was done by adapting a System ID to the DB in the login panel. I did this using the server-side of my login panel.

The first thing we do, is to use dbConnect (Wiki), which will make the connection to the db file.

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

It will create the "db" folder, in the resource files, and inside the folder, the "royalusers.db" file.

 

After that, we create a table using the SQL functions.

local newTable = dbExec(db, "CREATE TABLE IF NOT EXISTS RoyalUsers (id INT, name TEXT, login TEXT, pass TEXT, serial TEXT)")

RoyalUsers - Table-name

id INT, name TEXT, login TEXT, pass TEXT, serial TEXT - columns of the table.

INT - INTERNAL NUMBER

 

Now the part of using the table defined in the database.

function registerPlayer(user, pass, conf)
    if user == "" then
        outputLoginMsg(source, "Preencha todos os campos!", "error") 
    else 
        if pass == "" then
            outputLoginMsg(source, "Preencha todos os campos!", "error") 
        else
            if conf == "" then
                outputLoginMsg(source, "Preencha todos os campos!", "error") 
            else
                if conf == pass then
                    addAccount(tostring(user),tostring(pass))
                    triggerClientEvent(source, "onRoyalRegister", source)
                    local query = dbPoll(dbQuery(db, "SELECT * FROM RoyalUsers WHERE login=?", user),-1)
                    if #query == 0 then
                        dbExec(db, "INSERT INTO RoyalUsers VALUES (?, ?, ?, ?, ?)", countIDs(), getPlayerName(source), user, pass, getPlayerSerial(source))      
                    end
                    outputLoginMsg(source, "Conta criada com sucesso! Aguarde...", "success")
                    if not getAccount(user, pass) then
                        outputLoginMsg(source, "Esta conta já está sendo utilizada!", "error")
                    end
                else
                    outputLoginMsg(source, "As senhas não estão iguais.", "error")
                end
            end 
        end
    end
end
addEvent("onPlayerRequestRegister", true)
addEventHandler("onPlayerRequestRegister", root, registerPlayer)

function countIDs()
    local table = dbPoll(dbQuery(db, "SELECT * FROM RoyalUsers"), -1)
    local count = 1
    for i, result in pairs(table) do
        count = count + 1
    end
    return count
end

The login variable returns a table, it selects (SELECT) all the columns of the table, and checks if the login defined in the function does not exist in it, if it does not exist, it will add those information, the columns of the table RoyalUsers (INSERT INTO).

VALUES (?, ?, ?, ?, ?) - "?" It is the amount of arguments that will be used to insert into the column.

countIDs() - Function that returns the number of IDs in the table, and adds one more.

getPlayerName(source) - Gets the player's name, and adds the "name" column of the table.

user - Adds the user defined in the function, the column "login".

pass - Adds the password set in the function, the "pass" column.

getPlayerSerial(source) - Gets the player's serial, and adds the "serial" column of the table.

 

Having the part in which the data that the player registers, are saved in the database, this part is ready.
Now, just set the ID added to the database login, when the player logs in.

function loginPlayer(source, user, pass)
    if user == "" then
        outputLoginMsg(source, "Preencha todos os campos!", "error") 
    else 
        if pass == "" then
            outputLoginMsg(source, "Preencha todos os campos!", "error") 
        else
                local account = getAccount(user, pass)
                if account then
                    logIn(source, account, pass)
                    local queryTable = dbPoll(dbQuery(db, "SELECT * FROM RoyalUsers WHERE login='"..getAccountName(getPlayerAccount(source)).."'"), -1)
                    for i, id in pairs(queryTable) do
                        setElementData(source, "ID", id["id"])
                    end
                    setTimer(setCameraTarget, 3000, 1, source, source)
                    setTimer(triggerClientEvent, 14000, 1, source, "renderRoyalID", source)
                    triggerClientEvent(source, "onRoyalLogin", source)
                    outputLoginMsg(source, "Logado com sucesso! Aguarde...", "success")
                else
                    outputLoginMsg(source, "Usuário ou senha incorretos!", "error")
                end   
        end
    end
end
addEvent("onPlayerRequestLogin", true)

The queryTable gets all the columns of the table, where the login = login of the player.

After that, we loop the tables returned by the queryTable, and set the date "ID" to the player, according to the ID returned from the table defined in the loop.

 

I want to make it clear that the tutorial did not go well explained, I just want to share, what I learned yesterday, and if I did something wrong, please let me know.

[IIYAMA]

Quote

I want to make it clear that the tutorial did not go well explained, I just want to share, what I learned yesterday, and if I did something wrong, please let me know.

@VazErn

As long as it works, people are always happy.

 

 

A few tips:

A loop for this is not required, unless there are multiple similar database items.

for i, id in pairs(queryTable) do
    setElementData(source, "ID", id["id"])
end

 
setElementData(source, "ID", queryTable[1]["id"])

(untested, but should be working)

 

------------------------------------------------------------------------------------------------

 

The sky is the LIMIT

Quote

. You can use select statements along with specified conditions to retrieve desired data from your tables. By default, all records that satisfy those conditions are returned. However, you might just want a subset of records. In SQL, this can be accomplished using the LIMIT statement.

Conclusion:

SQL developers are in great demand since they work with one of the most popular and oldest programming languages ever. There are only a few places where you can implement performance gains within an application, only if your code is well written. Making minute changes such as including the LIMIT keyword in your SQL queries can have tremendous performance improvements. Also, you can improve the visibility and friendliness of any page on an application by limiting the number of records displayed.

https://blog.udemy.com/sql-limit/

 

If the database has already found what it is looking for, then why not stop looking for more?

LIMIT 1

Read more / how to use:  https://blog.udemy.com/sql-limit/

 

------------------------------------------------------------------------------------------------

Timers are working asynchronous

 

What would happen if a player leaves during those 3 or 14 seconds when the timers are still running?

setTimer(setCameraTarget, 3000, 1, source, source)
setTimer(triggerClientEvent, 14000, 1, source, "renderRoyalID", source)

Just think about it.

 

setTimer(function (element)
	if isElement(element) then
		-- do your thing
	end
end, 3000, 1, source)

 

Edited November 26, 2018 by IIYAMA

[Vazern]

Thanks for help @IIYAMA

[Juniorr]

For me to get the player ID I have to use:

getElementData( source, "ID" )

not? with me not caught (I'm new sorry :v)

[Tut]

Gravedigging post removed.