TUTORIAL SQLITE3 PRISMA

Por Adalberto

 

Link para a lista de funções sqlite3 Prisma.

 

Nota: Problemas no Win resolvidos, releia a parte 4, exemplo já modificado.

Eu, pessoalmente recomendo muito o Linux, especialmente o Ubuntu. Pode acreditar, vai evitar tremendas dores de cabeça, inclusive se for correr um servidor nele.

Requisitos mínimos:

1. instalação completa prisma-1.0 mais recente;
2. saber criar um arquivo fonte com prismacod ou outro editor e executá-lo com o interpretador prisma.
3. Caso não tenha o básico de entendimento, sugiro que visite o site www.linguagemprisma.br4.biz, baixe prisma ‘tudo em um’, siga os primeiros passos e leia o manual básico da linguagem prisma;

Com este tutorial você aprenderá a:

       0. Definição resumida de banco de dados;

1. mostrar a versão do sqlite3;
2. abrir/criar banco de dados;
3. criar tabelas e preencher com dados;
4. ler e mostrar os dados;
5. pegar o número atual de registros id de uma tabela;
6. pegar o nome de todas as tabelas existentes em um bd;
7. deletar e atualizar os dados de uma tabela;

0 – Definição resumida de banco de dados;

Resumidamente, é um arquivo criado por um programa para armazenar dados de modo organizado, sendo possível, posteriormente, consultar ou modificar esses dados pelo programa;

1 – Mostrar a versão do sqlite3;

Crie um arquivo chamado sql_versao.prisma e copie o conteúdo abaixo:

 

No código fonte acima temos:

local sql = inclua’sqlite3′;

Esta linha serve para importar as funções da biblioteca sqlite3.pris para a variável ‘sql’;

imprima( sql.libversao() );

Acima temos duas funções:

*’imprima()* – que serve para mostrar um texto ou número na tela de comandos do Linux ou Windows.

*sql.libversao()* – esta função retorna um texto (string) descrevendo a versão do sqlite3.

2 – abrir/criar banco de dados;

salve como: cria_bd.prisma

 

Detalhes:

local sql = inclua ‘sqlite3’;

No trecho acima incluímos as funções da biblioteca sqlite3 na tabela sql;

ret, base = sql.abra(‘teste.bd’);

Nesta linha abrimos o arquivo ‘teste.bd’ de banco de dados ( único parâmetro é uma string descrevendo o nome do arquivo / caso não exista o arquivo, é criado um, caso exista, é aberto para leitura ou gravação) e são retornados dois valores: ret e base.

Entendendo os dois retornos:

*ret – é um número que indica êxito (zero) ou erro (qualquer outro número);

*base – é uma variável do banco de dados aberto. (os nomes dos retornos ficam a critério do programador, aqui eu escolhi ret e base mas poderiam ser outros.)

( Obs. a variável tabela sql recebeu o retorno da função inclua’sqlite3′; é por isso que podemos usar os métodos após escrever sql. )

 

Aqui pode ser lido como: se o retorno ret for igual a sql.SQLITE_OK então coloque na tela a frase de sucesso! Caso contrário (se ret não for igual a sql.SQLITE_OK) imprima a mensagem de erro.

*Nota – SQLITE_OK é uma variável reservada da biblioteca sqlite3 que tem como valor o número zero.

A função sql.mensagem_erro(base) retorna uma string descrevendo o útimo erro contido em ‘base’ que é a variável do banco de dados aberto, lembra. Único parâmetro é a variável do banco de dados aberto.

sql.feche(base);

E por fim, não se esqueça de fechar o banco de dados aberto com a função acima.

3 – criar tabelas e preencher com dados;

Quando criamos um arquivo de dados o que pretendemos é gravar dados nele, e de forma organizada para pesquisarmos esses dados futuramente.

*Nota – Daqui em diante, ocasionalmente, poderei usar o termo bd para dizer ‘banco de dados’ ou o ‘arquivo do banco de dados’!

Para colocarmos dados no bd de modo organizados criamos tabelas, uma espécie de estrutura que lembra um prateleira com várias colunas e divisões horizontais(linhas), para cada coluna criada devemos colocar o tipo de dado correspondente. Para melhor visualizarmos, observe o exemplo abaixo:

tabela clientes:

índices	NOME	ENDEREÇO	TELEFONE
1	Paulo Martins	Rua das Graças	99 9999 99999
2	Edmara Silva	Rua dos Diamantes	66 6666 66666
3	Pedro Alcântara	Rua Eldorado	55 5555 55555

Veja que a tabela acima é como uma prateleira com quatro colunas cada uma para guardar um dado específico. A primeira são os índices da sequência, a segunda coluna somente nomes, a terceira o endereço e a quarta os números de telefones. Assim os dados ficam organizados, não podemos trocar, por exemplo, colocar nomes terceira ou quarta coluna, pois os dados ficariam desorganizados.

É claro que num banco de dados as tabelas não serão visualmente como no exemplo acima, mas o modo de acessá-las será de forma similar, em linhas e colunas. Por exemplo, poderíamos acessar ‘Edmara Silva’ direcionando a pesquisa para segunda coluna na linha 2; ou seu telefone na coluna 4 e linha 2;

*Nota – Dentro de um mesmo bd podemos criar inúmeras tabelas e dentro destas tabelas podemos colocar bem mais de um dado.

Entendido um pouco sobre o que são tabelas, vamos aos códigos:

Salve como tabela_insere_dados.prisma

 

Detalhes:

Vamos pular o que já foi explicado nos exemplos anteriores, ok!

 

Este trecho acima é uma string de várias linhas em prisma contendo os comandos do sqlite3 (linguagem SQL); logo para usar o sqlite3 precisamos usar sua linguagem. Aqui passarei o básico.

*Nota – É bom usar duplos colchetes de strings multilinhas ( [[ … ]] ) pois precisamos usar aspas internas.

Vamos detalhar os comandos sqlite3 acima:

DROP TABLE IF EXISTS amigos;

Cuidado! Este trecho apaga totalmente a tabela amigos com seus dados se ela já existir.

CREATE TABLE amigos(Id INT, Nome TEXT, Idade INT);

O comando acima significa: CRIE TABELA (CREATE TABLE) amigos e entre parênteses são passados os nomes das colunas e o tipo de dados após os nomes:

Id (letra i maiuscula e d) é do tipo INT (inteiro – número sem casas decimais)

Nome é do tipo TEXT (‘string entre aspas simples’);

Idade é do tipo INT também, igual ao primeiro.

*Note que a linguagem sqlite3 segue uma sintaxe (regras) e cada comando termina com ponto e vírgula.

Observe que ao criar uma tabela, somos obrigados desde já a definir o número, tipo e nome das colunas podendo ser modificados posteriormente com funções do sqlite.

INSERT INTO amigos VALUES(1, ‘Marcos’, 52);

Aqui inserimos (INSERT) dentro (INTO) da tabela amigos os valores (VALUES): ( 1 , ‘Marcos’ , 52 );

*note que o nome está entre apas simples pois é do tipo texto.

*Note que entre parênteses os valores são postos na mesma ordem de criação das colunas da tabela.

E os demais comandos fazem a mesma coisa, só trocando os valores:

INSERT INTO amigos VALUES(2, ‘Maria’, 19);

INSERT INTO amigos VALUES(3, ‘Pricila’, 9);

Após criar uma string de comandos sqlite3 podemos executá-la com a função abaixo:

ret , erro_msg = sql.exec(base, str_exec );

Esta função recebe 3 parâmetros:

1 – variável do banco de dados.

2 – a string de comandos sqlite3.

3 – foi omitido, ele é opcional, trataremos adiante.

Com isso a string é executada, a tabela é criada (antes apagada se já existir) e os dados são inseridos.

E retorna 2 valores:

1 – ret é o número que descreve êxito caso seja zero (sql.SQLITE_OK) ou erro caso seja qualquer outro número.

2 – erro_msg é uma string que descreve o erro caso haja um.

Os outros comandos do exemplo são parecidos com o anterior, e já foi explicado!

Outro exemplo de inserção de dados no mesmo bd:

 

 

4 – ler e mostrar os dados;

4.1 lendo uma tabela completa

Salve como ler_tabela.prisma

 

Veja o resultado:

 

 

 

 

 

 

 

Detalhes do exemplo acima: (lembre-se, vamos pular o que já foi explicado nos exemplos anteriores)

 

 

 

Lembra do 3 parâmetro omitido no exemplo anterior? Certo, precisamos dele agora, é a função de retorno acima. Para criar essa função não há segredos, é uma função prisma normal, com o nome que preferir, os parâmetros principais são dois os valores e nomes das colunas.

O que acontece é que ao usar a função sql.exec(), esta percorrerá cada linha da tabela executando a função de retorno e passando os valores e nomes de colunas de cada linha precorrida.

*Note que valores e nome_coluna são tabelas prisma contendo os dados da tabela sqlite3. No caso, como criamos três colunas na tabela Carros usamos 3 índices para acessar os dados de valores e nome_coluna.

ret , erro_msg = sql.exec(base, “SELECT * FROM Carros” , “sql_call( %s , %s )” );

Esta é a função que executa a string sqlite3;

Algumas novidades aqui:

1 – passamos diretamente a string de comando sqlite3 dentro do parênteses. (tanto faz, por variável ou diretamente)

2 – usamos o terceiro parâmetro, omitido nos exemplos anteriores.

Vamos entender aqui o comando sqlite3.

“SELECT * FROM Carros” – pode ser lido como: selecione tudo (*) o que estiver (from) na tabela Carros.

“sql_call( %s , %s )” – para passar a função de retorno (call back) como parâmetro usamos uma string como se fôssemos utilizar a função executestring(); pois internamente, a biblioteca igsqlite3 usa essa função.

E os dois ‘%s‘ dentro do parênteses? Eles são necessários pois são caracteres de formatação que serão substituídos pelos parâmetros valores e nome_colunas a cada linha da tabela. Não se preocupe apenas utilize-os sempre!

Como explicado anteriormente essa função retorna dois valores, o número e a mensagem de erro.

Quando essa função acima é executada, ela executa o comando sqlite3 “SELECT * FROM Carros” e faz um loop (repetição) passando cada linha dessa tabela selecionada até chegar a última, isso acontece pois usamos o * que significa todos as linhas da tabela.

E se em vez de querer apenas imprimir os dados eu quisesse manipulá-los. Muito simples crie uma tabela prisma e passe todos os dados para ela usando a função de retorno.

Veja um exemplo abaixo:

 

Saída:

 

 

 

 

Para o ambiente gráfico foi usado a biblioteca ‘igbr’. No site oficial prisma há vários exemplos na seção página de posts.

4.2 lendo uma linha específica da tabela:

Salve como ler_linha.prisma

 

Vamos direto para as novidades aqui, comando sqlite3 novo:

str_exec = “SELECT Id, Nome , Preco FROM Carros WHERE Id = 3”;

Pode ser entendido como: SELECIOME as colunas Id, Nome e Preco DA tabela Carros ONDE Id for igual a 3.

Ao ser executada essa string de comando sqlite3, ela procura pela linha onde a coluna Id for igual a 3 e retorna os valores para a função de retorno sql_func ();

 

Esta função acima é a função call back(retorno) que é executada automaticamente pela sql.exec() e recebe os valores nome da coluna da tabela sqlite3 e seus dados em valor.

ret , msg = sql.exec(base , str_exec , ‘sql_func(%s,%s)’ );

E, por fim, executamos a string de comando sqlite3, relembre que o terceiro parâmetro é a string da função de retorno parênteses e os dois %s que serão substituídos pelos valores e colunas da tabela sqlite3.

*Nota – Veja que a função sql_func tem dois parâmetros valor e coluna que são os %s passados para sql.exec. Esses dois valores são tabelas prisma e o número de elementos depende do número de colunas criadas na tabela sqlite3.

Este exemplo acima funciona bem como consulta de dados. Caso queira pegar os dados, em vez de simplesmente imprimi-los, use uma tabela prisma para isso, assim:

 

Não esqueça no final de usar o comando sis.saia(0) para evitar erro no Windows após fechar o programa ou se estiver usando modulo igbr use: ig.conecte( janela, ig.destruido , ‘sis.saia(0)’ );

Pronto, agora podemos acessar os dados assim: dados.Id, dados.Nome , dados.Preco

Saída do exemplo acima:

 

 

 

 

4.3 Comandos parametrizados do sqlite3.

Comando sql.prepare_v2();

Detalhes:

“SELECT Id, Nome , Preco FROM Carros WHERE Id = ?”;

Essa é a string de comando sqlite. O ponto de interrogação será substituído por um valor posteriormente.

ret , res = sql.prepare_v2( base , sql_cmd );

Este comando compila (prepara) a string sqlite3. Parâmetros: 1- banco de dados aberto, 2- é a string sqlite3.

Dois retornos:

ret é um número que pode indicar êxito (zero) ou falha (qualquer outro número);

res é a string sqlite já compilada.

sql.vincule_int( res , 1, 4);

Esta função coloca o valor 4 no lugar do “?” dentro da string de comando sqlite.

Parâmetros: 1 – string sqlite compilada, 2 – índice sqlite, 3 – número que ficará no lugar do “?”;

pas = sql.passe(res);

A função sql.passe() analisa a string sqlite compilada e retorna uma linha se houver (pas); único parâmetro é a string compilada.

se (pas == sql.SQLITE_LIN) entao

A condição testa se pas (retorno de sql.passe() ) é um alinha sqlite válida.

sql.coluna_texto( res , 1)

Esta função retorna o valor da coluna 1 da tabela Carros. (Id)

sql.coluna_texto( res , 2) retorna o valor da coluna 2; (Nome)

sql.coluna_texto( res , 3) retorna o valor da coluna 3; (Preco);

Obs. Este método, particularmente, tem um pequeno grau de complicação mas é mais rápido e seguro.

4.3 Leitura de dados sqlite3. (Não ocorre erro no Windows, comando mais confiável);

Lendo todos os dados da tabela:

“SELECT * FROM Carros;”

Esta é a cláusula sqlite, ela seleciona todos (*) os dados da (from) tabela Carros.

ret , res = sql.prepare_v2( base , “SELECT * FROM Carros;” );

Acima compilamos a cláusula.

*Nota – Baixe a versão mais atual de Prisma para a função sql.coluna_nome()

Depois de compilar a string sqlite podemos usar a função sql.passe(). A cada repetição dessa função uma linha da tabela é percorrida. O retorno é um código de erro que pode indicar uma linha válida (sql.SQLITE_LIN que é igual a 100) ou não. Caso seja uma linha válida é possível obter os nomes das colunas com a função sql.coluna_nome() e o valor das colunas com a função sql.coluna_texto().

Dica – use a função convnumero() caso queira converter de string para número.

 

5 – pegar o número atual de registros id de uma tabela;

Muitas vezes podemos querer saber qual foi o id da última linha inserida em uma tabela, para isto temos a função:

sql.ultimo_id( base );

Para essa função funcionar devemos usar um tipo de coluna chamado de chave de auto contagem ou auto incremento.

Nesse tipo de coluna não precisamos colocar seu valor 1, 2, 3, 4 … a contagem é feita automaticamente. Veja abaixo:

Salve como ultimo_id.prisma

 

Novidades neste exemplo:

ret , base = sql.abra(sql.memoria);

Ao usar o comando acima com o parâmetro sql.memoria um banco de dados será criado na memória, não haverá nenhum arquivo.

str_exec = [[

CREATE TABLE Amigos(Id INTEGER PRIMARY KEY, Nome VARCHAR(55), idade INT);

INSERT INTO Amigos(Nome, idade) VALUES (‘Tom’, 15);

INSERT INTO Amigos(Nome, idade) VALUES (‘Rebecca’, 34);

INSERT INTO Amigos(Nome, idade) VALUES (‘Jim’, 44);

INSERT INTO Amigos(Nome, idade) VALUES (‘Roger’, 23);

INSERT INTO Amigos(Nome, idade) VALUES (‘Robert’, 32);

]];

Acima é a string de comandos sqlite3. Podemos perceber nela a chave de auto contagem na criação da tabela:

CREATE TABLE Amigos(Id INTEGER PRIMARY KEY, Nome VARCHAR(55), idade INT);

eis a chave: Id INTEGER PRIMARY KEY, será sempre assim não a modifique.

A segunda coluna criada é o Nome que é do tipo VARCHAR(55) (uma string de no máximo 55 caracteres) e a terceira coluna é idade do tipo INT (número inteiro);

Veja que quando usamos uma chave (KEY) de auto contagem, devemos especificar o nome das colunas entre parềnteses ao inserir os dados, deixando de fora a chave de auto contagem. Veja abaixo:

INSERT INTO Amigos(Nome, idade) VALUES (‘Tom’, 15);

E assim com os comandos restantes.

ult_id = sql.ultimo_id( base );

Aqui pegamos o número da última chave (INTEGER PRIMARY KEY), o retorno é um número prisma.

O resultado será 5 pois criamos somente 5 linhas no exemplo, mas pense em um arquivo com milhares de dados, esta função é muito útil para inserir um novo dado sem correr o risco de errar a contagem.

 

6 – pegar o nome de todas as tabelas existentes em um bd;

Algumas vezes nós podemos querer descobrir quais tabelas estão criadas em um bd, mas sem fazer ideia do nome delas. Para isso veja o exemplo abaixo:

Salve com o nome de nome_tabela.prisma

 

Detalhes:

 

 

Acima está a função de retorno (call back);

A novidade aqui é o comando sqlite3 que permite selecionar o nome de todas as tabelas do bd:

“SELECT name FROM sqlite_master WHERE type=’table’;”

Leia: SELECIONE name de sqlite_master ONDE o tipo for ‘tabela’;

sqlite_master é uma espécie de cabeçalho de informações do bd.

Este comando sqlite3 é passado como segundo parâmetro na função abaixo:

sql.exec(base, “SELECT name FROM sqlite_master WHERE type=’table’;” , “sql_call( %s , %s )” );

Que ao ser executado, executa também a função call back passando os dados selecionados no comando sqlite3.

7 – deletar e atualizar os dados de uma tabela;

7.1 Apagando uma linha da tabela:

Deletar uma linha é simples, veja o exemplo abaixo:

salve como deleta.prisma

 

A novidade neste exemplo é o comando sqlite3 para deletar uma linha a partir de um id:

DELETE from Carros where Id = 2;

Leia: Apague de carros a linha onde o id for igual a 2;

Para apagar todos os dados da tabela use:

DELETE from Carros;

Para apagar completamente uma tabela:

DROP TABLE IF EXISTS Carros;

 

7.2 Atualizando um dado da tabela:

Salve o programa como atualiza_bd.prisma

 

Detalhes:

O comando sqlite3 para atualizar um dado é:

[[ UPDATE Carros set Nome = ‘NADA_AQUI’ where Id = 1; ]]

Leia-se ATUALIZE Carros defina Nome igual a ‘NADA_AQUI’ onde o Id for igual a 1;

basta executar essa string e a linha que tiver o id 1 na coluna Nome será trocado o valor por ‘NADA AQUI’;

Obs. para mudar o valor de outras linhas troque o Id = 1 por Id = 2; ou 3, 4, 5 etc.

Para mudar não só a coluna nome mas outras colunas também basta acrescentar na mesma string sqlite3 o nome das outras colunas, assim:

[[ UPDATE Carros set Nome = ‘NADA_AQUI’ , Preco = 77777 where Id = 1; ]]

Veja que ao acrescentar mais uma coluna devemos separá-la por virgula.

[[ SELECT * from Carros; ]]

Este outro comando na mesma string sqlite3 serve para selecionar a tabela Carros para imprimirmos o valor modificado.

O comando sqlite3 completo acima com mais uma coluna para modificar o valor fica:

 

Executando essa string:

ret, msg = sql.exec(bd , sql_str , ‘sql_func(%s,%s)’ );

*Note que a string ficou atribuída na variável sql_str e passada como argumento na função sql.exec.

Dicas finais:

*Veja que é simples executar um comando sqlite usando a função sql.exec();

*Aqui explanei o básico, mas você pode facilmente aprender novos comandos sqlite e executar.

*Você pode salvar os comandos sqlite em um texto e usar a função de leitura de arquivo para obter o conteúdo em uma string e executá-la.

Qualquer dúvida:

Visite o site: linguagemprisma.br4.biz ou o fórum (link no site)

Até logo;

Att. Adalberto.

Fim