Queries e SQL para OTservers

[Omega 377]

Esse é um tutorial básico sobre SQL e as funções LUA que são usadas em OTServers.

Definição
Primeiramente, deve ser útil uma definição de banco de dados: são, fundamentalmente, conjuntos de informações armazenadas em tabelas. Para OTServers, são onde todas as informações sobre as contas e personagens são guardadas.
SQL (Structured Query Language) é uma linguagem que é usada por quase todos os bancos de dados (no nosso caso, todos). Quando você envia comandos em SQL para o seu banco de dados, você pode modificá-lo ou extrair valores dele.
Query (queries no plural) são consultas ou comandos que são enviadas para o banco de dados.

Introdução
Para nós, scripters, às vezes é fundamental tirar informações ou mudar valores dos personagens que só estão disponíveis no banco de dados, para isso existe uma biblioteca de funções que já vem com os servidores, a biblioteca db (de database, que significa banco de dados em inglês). Dela, usaremos principalmente duas funções:

db.getResult
Retorna os valores que foram selecionados em uma query

db.query ou db.executeQuery
Executa uma query usada para modificar informações no banco de dados. Essa função se chama db.executeQuery na maioria dos servidores, mas também pode ser utilizada como db.query em outros.

Em ambos os casos, a query deverá ser usada entre aspas.

Queries em SQL

Primeiramente, devemos saber como executar queries diretamente no banco de dados. Para isso, faça download de um banco de dados que eu disponibilizei abaixo como anexo. Use também o sqlite studio, que você pode baixar aqui.

Assim que você abrir o banco de dados com o sqlite studio você verá que está dividido em duas janelas: a da esquerda de navegação e a da direita com os dados em si (no caso está branco quando abre). Abra a árvore do banco de dados e selecione a tabela players clicando duas vezes. Você verá a estrutura do banco de dados, com os nomes das colunas e o tipo de dados de cada uma (INT, INTEGER, VARCHAR, BOOLEAN, etc). Em seguida, selecione a aba 'Data' para ver os dados que estão na tabela. Lá estarão todos os jogadores cadastrados no servidor, com várias informações. Essa é a aba importante para nós.

1. SELECT
Depois disso, vamos tentar executar um primeiro comando no banco de dados: use ALT+E para abrir o editor de queries e digite:

SELECT * FROM players

Então, aperte F9 e veja o resultado. O que deve acontecer é aparecer toda a tabela players que você já havia visto. Perceba como, em geral, SQL é uma linguagem bem direta: a query que você enviou para o banco de dados significa:

SELECIONE * DE jogadores

E ela faz isso, com a exceção de que * significa todos os dados da tabela.
OBS.: Apesar de quase toda query de SQL estar nesse padrão, ela é uma linguagem que não é case sensitive, logo

select * from players

Dá no mesmo. Mas fica menos legível, assim utilizamos letras maiúsculas para as palavras-chave dos comandos e letras minúsculas para os nomes de tabelas e colunas.

Vamos testar outra query, um pouco mais complicada:

SELECT name, level FROM players

Dessa vez não selecionamos todos os dados da tabela, apenas o nome e o level de cada jogador. A sintaxe do comando SELECT é:

SELECT nome_da_coluna_1, nome_da_coluna_2,..., nome_da_coluna_n FROM nome_da_tabela

Assim teremos como retorno os valores dessas colunas para cada elemento da tabela. Tente selecionar apenas o nome e a posição (x, y e z) de cada jogador e confira abaixo.

 

SELECT name, posx, posy, posz FROM players

 

Complicando um pouco mais, vamos ordenar a tabela players por level:

SELECT * FROM players ORDER BY level DESC

Adicionamos a palavra-chave ORDER BY e DESC. A primeira significa 'ordene por' e a segunda, 'decrescente'. Ou seja, ordenamos nossa tabela com base na coluna level de maneira decrescente. Se você ocultar o DESC, a ordem natural vai ser crescente.

Agora vamos filtrar nossos resultados:

SELECT name, level FROM players WHERE level > 100 ORDER BY level DESC

Assim obtemos apenas os jogadores com level maior que 100 e ordenados de maneira decrescente pelo level. A palavra-chave WHERE significa 'onde' e introduz uma condição para os resultados.
Complicando ainda um pouco mais, vamos fazer a mesma coisa, mas para os jogadores com level entre 100 e 200 (sem contar nenhum dos extremos).

SELECT name, level FROM players WHERE level > 100 AND level < 200 ORDER BY level DESC

Portanto podemos usar AND e OR para escolher melhor os resultados. Para selecionar os extremos, podemos usar os operadores '>=' (maior ou igual) e '<=' (menor ou igual) ou simplesmente:

SELECT name, level FROM players WHERE level BETWEEN 100 AND 200

Tente pegar os magic levels dos jogadores com level entre 50 e 100 (excluindo os extremos) e vocação 2 (druids), ordenados pelo level de maneira decrescente. Confira abaixo:

 

SELECT maglevel FROM players WHERE level > 50 AND level < 100 AND vocation = 2 ORDER BY level DESC

 

2. UPDATE
Nesse ponto, eu recomendo que você faça um backup do banco de dados de teste para que, caso você mexa em algo e não saiba reverter, possa trocar facilmente.

Esse é o comando em SQL para alterar informações em uma tabela. Vamos começar com um exemplo simples: mudar o nome de uma guilda. Faremos isso usando o comando UPDATE, cuja sintaxe é:

UPDATE nome_da_tabela SET nome_da_coluna = valor

Para mudar o nome de uma guilda, precisamos usar o UPDATE, mas se fizermos:

UPDATE guilds SET name = 'We Keel You'

Estaremos mudando o nome de todas as guildas do servidor para 'We Keel You' (as aspas devem estar presentes sempre que a informação que você está tratando é do tipo string, ou seja, um texto). Agora tente mudar apenas o nome da guilda Ownage para We Keel You. Confira abaixo:

 

UPDATE guilds SET name = 'We Keel You' WHERE name = "Ownage"

 

Atualize a tabela guilds apertando F5 e perceba que agora a última guilda listada se chama We Keel You.
Perceba que a maioria das tabelas tem uma coluna id. Essa é uma coluna importante para reconhecer cada elemento da tabela.

OBS.: Prefira sempre trabalhar com números do que com textos.

Agora vamos mudar o nome e a mensagem de uma guild:

UPDATE guilds SET name = "Bumbum de bebe", motd = "Seja bem vindo!" WHERE id = 2

Agora mudamos, com um UPDATE só, a mensagem (motd) e o nome da guilda com id 2 (Power Abusers).

 

Em alguns casos, queremos acrescentar e não mudar o valor de alguma coluna. Podemos fazer como o exemplo abaixo, no qual todos os jogadores serão promovidos, desde que já não o sejam.

UPDATE players SET vocation = vocation + 4 WHERE vocation <= 4

3. INSERT INTO
Esse é o comando em SQL para inserirmos dados em uma tabela. Para criar um jogador, por exemplo, podemos utilizar esse comando assim:

INSERT INTO players VALUES (1, "Char de Teste", 0, 1, 1, 8, 3, 150, 150, 4200, 114, 92, 0, 0, 0, ...)

Ou seja, estamos inserindo na tabela players os valores 1, "Char de Teste" , 0, 1, 1, 8, 3, 150, 150, 4200, 114, 92, 0, 0, 0, etc. Esses valores são inseridos na ordem das colunas, então 1 é o id, "Char de Teste" é o nome e assim por diante. Essa é uma das formas de se usar o comando INSERT INTO, mas existe outra:

INSERT INTO nome_da_tabela (coluna1, coluna2, coluna3, ...) VALUES (valor_da_coluna1, valor_da_coluna2, valor_da_coluna3, ...)

4. ALTER TABLE
Esse é um comando um pouco mais complicado porque sua sintaxe varia bastante dependendo do banco de dados. Em OTServers, esse comando é usado principalmente para acrescentar uma coluna em uma determinada tabela. Por exemplo, para criar um sistema VIP pelo banco de dados, poderíamos usá-lo. Sua sintaxe fica assim:

ALTER TABLE nome_da_tabela ADD nome_da_coluna tipo_de_dado (restrições e padrões)

O tipo de dado define quais dados são aceitáveis como valores da coluna. Por exemplo, uma coluna que conterá nomes deve ter como tipo de dado varchar(255). Esse é um padrão que eu vejo nos servidores e significa que serão aceitos caracteres variáveis com comprimento de até 255. Entretanto, o SQLite ignora qualquer número em parênteses e não define um máximo baseado nisso. O máximo é padronizado para 1000000 de bytes (sqlite.org/datatypes). Procure saber como funciona se você usa outro banco de dados (MySQL, por exemplo).
Para um número, use INT (que significa inteiro). Acho que são só esses dois tipos de dados que são comumente usados quando se trata de OTServers. Para mais informações, clique aqui.

As restrições são valores que não serão aceitos na coluna, como NOT NULL (para não aceitar valores nulos e facilitar posteriores tratamentos de erros), enquanto padrões são valores que serão inseridos automaticamente na coluna, se nenhum outro for especificado. Por exemplo, ao criar um jogador, vários valores tem padrão (default) 0. Não faz sentido um jogador começar com skull diferente disso, né? Ambas as restrições como valores padrão são opcionais. Exemplo:

ALTER TABLE players ADD vip_time INT NOT NULL DEFAULT 0

É possível criar colunas, mas nem sempre é possível deletá-las diretamente. Com SQLite, teríamos que passar todos os dados para uma tabela temporária e, dessa tabela, extrair apenas as colunas que queremos para deletar uma coluna. Portanto, sempre faça um backup quando for usar esse comando.

 

5. COMANDOS COMPOSTOS
Para introduzir essa seção do tutorial, tente esse exercício: selecione o valor do storage 2308 para o jogador "Hulk".

 

SELECT value FROM player_storage WHERE player_id = (SELECT id FROM players WHERE name = "Hulk") AND key = 2308

 

Esse comando é mais complicado que os outros pois ele combina dois SELECTs. Perceba que não é possível selecionar o valor do storage diretamente pelo nome, mas é possível determinar o storage pelo id e o id pelo nome. Então temos que acessar a tabela players para selecionar o id do Hulk e depois, com esse id, selecionar o valor do storage 2308.
Comandos dentro de outros comandos devem ser usados entre parênteses.

 

Também não podemos ver o skill de um jogador diretamente. Temos que saber seu id e, na tabela player_skills, comparar o número do skill com o id do jogador. Para obtermos os skills de um jogador, podemos executar o seguinte:

SELECT skillid, value FROM player_skills WHERE player_id = (SELECT id FROM players WHERE name = "Hulk")

Assim conseguindo obter os skills e seus valores do jogador Hulk.

Tente mudar o valor do storage com key 2308 do jogador "Hulk" para 10. Confira abaixo.

 

 

UPDATE player_storage set value = 10 WHERE key = 2308 and player_id = (SELECT id FROM players WHERE name = "Hulk")

 

Queries SQL em LUA

Agora, vamos voltar ao nosso mundo de scripting LUA. Agora que já sabemos como usar as queries, vamos aplicá-las: que tal criar um ranking com o TOP 10 do seu servidor em uma talkaction?
Primeiramente, vamos definir a query SQL que usaremos: queremos os nomes e os levels dos 10 maiores levels do servidor. Tente criar a sua e compare:

 

SELECT name, level FROM players ORDER BY level LIMIT 10

 

Sim, existe uma palavra-chave nova nessa query que não foi apresentada anteriormente. LIMIT define um limite de valores retornados. Assim impedimos que nossa lista fique maior do que os 10 que queremos mostrar.
Agora precisamos escolher a função que vamos usar no script. Voltando ao início do tutorial, percebe-se que o SELECT deverá ser associado ao db.getResult. Portanto, nossa função principal do script deve ser:

db.getResult("SELECT name, level FROM players ORDER BY level DESC LIMIT 10")

Colocando isso numa talkaction:

function onSay(cid, words, param)
    -- Salvando os resultados da query em uma variável
    local query = db.getResult("SELECT name, level FROM players ORDER BY level DESC LIMIT 10")
    -- Criando uma tabela para passar os resultados
    local list = {}
    -- Checando se a query foi mal-sucedida (se ocorreu alguam problema, o código irá retornar verdadeiro e nada será exibido)
    if query:getID() == -1 then
        return true
    end
    repeat
        -- Armazenando o nome da linha atual do resultado na variável name
        local name = query:getDataString("name")
        -- Armazenando o level da linha atual do resultado na variável level
        local level = query:getDataInt("level")
        -- Inserindo a string com o nome e o level na tabela
        table.insert(list, name.. " - level: "..level)
    -- Termina o loop se não houver mais resultados ou passa pro resultado seguinte
    until not query:next()
    -- Envia a mensagem pro jogador, concatenando os resultados com uma quebra de linha
    doShowTextDialog(cid, 2148, table.concat(list, "\n"))
    -- Libera os resultados armazenados
    query:free()
    return true
end

Explicando:

• Primeiramente salvamos nossos resultados em uma variável chamada query. Para isso, usamos o db.getResult citado anteriormente. Em seguida, criamos uma variável list, uma tabela vazia, que será utilizada para manipular os resultados.
• A checagem para saber se algum resultado foi obtido pela query é usar o query:getID(). Se ele for maior que -1, então existem resultados obtidos.
• Começamos um loop repeat para analisar cada linha do resultado
• Para pegar o "name" da linha de resultado que temos na query, utilizamos o query:getDataString("nome_da_coluna") e salvamos na variável name. Fazemos o mesmo para uma variável level, lembrando que o tipo de dado será Int (inteiro), então utilizamos query:getDataInt("nome_da_coluna")
• Insere na tabela list um texto do tipo: Eternal Oblivion - level: 300, de acordo com a linha atual do resultado
• O método query:next() retorna a próxima linha do resultado se houver ou nil. Assim, passamos pra próxima linha ou terminamos o loop
• Com o texto formado, utiliza-se a função doShowTextDialog para enviar para o jogador que usou a talkaction, juntando (concatenando) a tabela com os resultados com uma quebra de linha ("\n")
• Liberamos os resultados armazenados pela query e fechamos o script

E assim você acabou de criar uma talkaction com o ranking dos jogadores do seu servidor com base no level!

Então essa é a base necessária para se acessar o banco de dados através de scripting. O resto depende da sua criatividade.

Exercícios

1 - Selecione o maior magic level do servidor

 

SELECT maglevel FROM players ORDER BY maglevel DESC LIMIT 1

 

2 - Adicione 5 dias de dias premium para todas as contas

 

UPDATE accounts SET premdays = premdays + 5

 

3 - Selecione o nome do líder da guild com id 2

 

SELECT name FROM players WHERE id = (SELECT ownerid FROM guilds WHERE id = 2)

 

4 - Insira um novo storage com key 71236 e valor 1 para o top level do servidor

 

INSERT INTO player_storage (player_id, key, value) VALUES ((SELECT id FROM players ORDER BY level DESC LIMIT 1), 71236, 1)

 

5 - Crie uma talkaction que fará com que todos os jogadores sejam teletransportados para uma posição definida, estando offline ou online.

 

function onSay(cid, words, param)
    local param = string.explode(param, ',')
    local x, y, z = tonumber(param[1]), tonumber(param[2]), tonumber(param[3])
    for _, pid in pairs(getPlayersOnline()) do
        doTeleportThing(pid, {x=x, y=y, z=z})
    end
    db.query("UPDATE players SET posx="..x..", posy="..y..", posz="..z)
    return true
end

 

W3schools SQL Quiz

Fontes Consultadas
http://www.w3schools.com/sql/
http://www.sqlite.org/

Considerações
Seria injusto não dar os devidos créditos ao , cujos códigos serviram de base para meu aprendizado em relação à SQL e às funções LUA relacionadas.

Scan do arquivo

DB_Teste.rar

Editado Dezembro 4, 2013 por Omega

[Alexclusive 687]

Que tópico magnífico cara, muita coisa importante e boa de se aprender.

Pode ter certeza que irei da uma saudade nele.

 

Obrigado pelo excelente conteúdo.

 

Atenciosamente,

Alexandre.

[Nu77 81]

Ótimo tutorial, muitas pessoas tem duvidas sobre sql e esse tutorial vai ajudar muito.

rep+

Editado Dezembro 4, 2013 por SUJA

[Killua 389]

Nossa, ainda não tinha visto esse... Ótimo tutorial, Omega

[Wesley Cajueiro 0]

Nossa parabés Manin