Visualização automática de esquemas de databases do Sqlite

Neste post usamos ferramentas open source para visualização de esquemas de base de dados (databases). Mostramos um passo a passo para criação de bases com SQL. Em seguida, construímos um esquema para uma base simplificada de um campeonato de futebol e geramos seu diagrama. Diferentemente de ferramentas como o draw.io, em que precisamos preencher e configurar cada bloco, veremos como gerar o diagrama do esquema completo de forma automática.

"Um banco de dados devidamente normalizado pode acabar em muitas pequenas tabelas conectadas por uma rede complexa de referências de chave estrangeira. Como uma cidade do mundo real, é muito fácil se orientar quando você estiver familiarizado, mas quando você chega, realmente ajuda ter um mapa." (tradução de https://github.com/ncruces/sqlite-schema-diagram) 🗃️ 🗺️

Instalação dos pré-requisitos

Para gerar o diagrama relacional, de forma automática, usamos o sqlite-schema-diagram. Seus pré-requisitos são o Graphviz e o Sqlite (v.3).

1. Graphviz

O Graphviz é um programa muito utilizado para visualização de diferentes tipos de grafos, redes, diagramas, etc.

Para instalar o Graphviz acessamos a seção de download do seu site. Após baixá-lo, realizamos a instalação através do passo a passo que eles indicam.

Sobre as instalações: Para quem estiver num computador com Linux, bastará rodar pelo terminal os códigos de instalação que serão mostrados. Já para outros sistemas operacionais, não entraremos nos detalhes da instalação, mas deixamos as linhas gerais para que isso seja feito. Os programas aqui usados possuem instalações usuais como de a de qualquer outro programa.

Para quem utiliza Linux, é possível instalar o Graphviz pelo terminal com:

sudo apt install graphviz

Para testar se o Graphviz foi instalado corretamente, podemos tentar checar sua versão (-V):

dot -V

Se der erro quando rodarmos o comando acima, precisamos rever a instalação do Graphviz.

2. SQLite

O banco de dados usado neste post é o Sqlite. Assim como alguns outros databases, o Sqlite não necessita de um servidor a parte para funcionar. Outra característica interessante é sua capacidade de manipular e armazenar vários tipos de arquivo: NULL (nulo), INTEGER (0, 1, 2, ...), REAL (floats como 3.14159 ou 2.718281), TEXT (strings) e BLOB (armazenado exatamente como o valor do input).

Ele é bastante versátil, pois pode ser guardado em disco como um arquivo (por ex., com extensão .db) que roda em diferentes plataformas (Linux, Android, etc). É possível enviarmos e compartilharmos seus databases assim como, usualmente, se faz com qualquer outro arquivo. Seus databases .db podem ir até 281 terabytes (TB). Detalhes dessas e outras informações sobre suas capacidades podem ser encontradas em sua página oficial.

Nota: Na documentação do Sqlite, explicam que tipos como datas (datetime) podem ser armazenados como um desses dos tipos de dados padrão do Sqlite. Por ex., podem ser armazenadas como TEXT se o valor estiver na forma YYYY-MM-DD HH:MM:SS.SSS, sendo Y = year (ano), M = month (mês), D = day (dia), H = hour (hora), M = minute (minuto) e S = seconds (segundos).

Para instalar o Sqlite, seguimos os passos de instalação de sua página oficial. Clique aqui para acessá-la.

Para quem usar Linux, basta digitar:

sudo apt install sqlite3

Após a instalação do Sqlite, verifique se tudo ocorreu ok, digitando o seguinte comando:

sqlite3

A saída esperada é exibida na imagem abaixo:

3.sqlite-schema-diagram

Por fim, devemos baixar o arquivo sqlite-schema-diagram.sql e a licença COPYING do seguinte repositório no Github: https://github.com/ncruces/sqlite-schema-diagram.

Com isso finalizamos a configuração dos pré-requisitos necessários. 🎬

Estrutura dos arquivos num diretório local (pasta-local) após as etapas acima:

pasta-local/
│
├── COPYING
│
└── sqlite-schema-diagram.sql

Breve introdução ao SQLite CLI

Para iniciar a CLI (command line interface) do Sqlite digitamos sqlite3 num terminal. Após isso, é esperado que apareça um sqlite> no terminal. Caso utilize o Crtl+C e Crtl+V, facilita se copiar apenas os comando que vierem após o sqlite>.

Para rodar um comando no Sqlite basta entrar no Sqlite e digitá-lo. Por ex., o .help é executado com:

sqlite> .help

Atenção: O sqlite> exibido nos códigos indicam que estamos com o Sqlite em execução. Se rodarmos o .help diretamente num terminal, é esperado que ocorra um erro.

Uma lista dos comandos do Sqlite (os dot commands, comandos com pontos .) está disponível neste link.

Criação de tabelas no database: Para criar uma tabela com a CLI do Sqlite, usamos os comandos usuais do SQL, por exemplo:

sqlite>
  CREATE TABLE pessoas (    
    nome TEXT,
    ano_nascimento INT,
    email TEXT
  );

Esse comando cria uma tabela chamada pessoas com colunas nome, data_nascimento e email.

Após rodar o comando acima no Sqlite, podemos checar, com o comando .tables, se a tabela pessoas foi criada:

sqlite> .tables

Essa checagem é exibida a seguir:

Inserção de uma linha na tabela pessoas: Inserimos uma linha, correspondente a um novo registro, na tabela pessoas com o comando SQL INSERT INTO. Por exemplo:

sqlite>
  INSERT INTO pessoas VALUES(
    'Chico Bento', 1963, 'email_chico_bento@turmadamonica.com.br'
  );

É possível checar se o registro de exemplo foi inserido corretamente com o comando SELECT:

sqlite>
  SELECT * FROM pessoas;

Nota: Não estaremos populando a base de dados que criaremos, pois o foco deste post é exibir de forma automática um diagrama que permita facilitar no entendimento de como as tabelas do database se relacionam.

Salvando o database como um arquivo: Para salvar o database usamos o .save:

sqlite> .save database_teste.db

Após salvar a base de dados, podemos sair do Sqlite:

sqlite> .quit

Versão atualizada dos arquivos locais até o momento:

pasta-local/
│
├── COPYING
│
├── database_teste.db
│
└── sqlite-schema-diagram.sql

Documentação da CLI: Uma lista completa com exemplos de uso dos comandos da CLI do Sqlite está disponível em: https://www.sqlite.org/cli.html.

Visualizando um database de futebol

Agora iremos criar um database que tenha relação com um campeonato de futebol de salão (futsal) amador. Nessa modalidade, o time é formado por 5 jogadores, um goleiro e mais quatro na linha.

Primeiramente, iniciamos o Sqlite com o comando sqlite3. Após isso, usamos os seguintes comandos SQL para criar o database, que chamaremos de database_fut.db:

-- schema_fut.sql
CREATE TABLE times (
  time_id INTEGER NOT NULL AUTO_INCREMENT,
  nome_time TEXT NOT NULL,
  pais TEXT,
  jogador_0_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_1_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_2_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_3_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_4_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_5_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_6_id NOT NULL REFERENCES jogadores(jogador_id),
  jogador_7_id NOT NULL REFERENCES jogadores(jogador_id),
  site TEXT,
  data DATETIME NOT NULL,
  PRIMARY KEY (time_id)
);

CREATE TABLE jogadores (
    jogador_id INTEGER NOT NULL,
    time_atual_id INTEGER,
    nome_jogador TEXT NOT NULL,
    posicao_campo TEXT,
    PRIMARY KEY (jogador_id)
);

CREATE TABLE jogos (
    jogo_id INTEGER NOT NULL,
    time_casa_id TEXT NOT NULL REFERENCES times(time_id),    
    time_visitante_id TEXT NOT NULL REFERENCES times(time_id),
    rodada_id INTEGER NOT NULL REFERENCES rodadas(rodada_id),
    placar TEXT NOT NULL,
    nome_campeonato TEXT,
    data DATETIME NOT NULL,
    PRIMARY KEY (jogo_id)
);

CREATE TABLE rodadas (
    rodada_id INTEGER NOT NULL,
    classificacao_id TEXT NOT NULL REFERENCES classificacoes,
    nome_campeonato TEXT NOT NULL,
    data DATETIME NOT NULL,
    PRIMARY KEY (rodada_id)
);

CREATE TABLE classificacoes (    
    classificacao_id TEXT NOT NULL,
    rodada_id INTEGER NOT NULL,
    classificacao_atual TEXT NOT NULL,
    data DATETIME NOT NULL,
    PRIMARY KEY (classificacao_id)
);

Lembrando: Após rodar os comandos que criam a tabela devemos salvar a tabela com .save <nome-do-database.db> e sair do Sqlite com o <.quit>:

sqlite3> 
  .save database_fut.db
  .quit

Gerando o diagrama automaticamente: Agora vamos gerar a visualização do esquema do database_fut.db criado. Para isso, primeiramente criamos um .DOT do Graphviz com o seguinte comando:

sqlite3 database_fut.db -init sqlite-schema-diagram.sql "" > schema_fut.dot

Em seguida, exportamos o schema_fut.dot para o formato .PNG com:

dot -Tpng schema_fut.dot > schema_fut.png

Após o último passo mencionado os arquivos locais ficam da seguinte forma:

pasta-local/
│
├── COPYING
│
├── database_fut.db
│
├── database_teste.db
│
├── schema_fut.dot
│
├── schema_fut.png
│
└── sqlite-schema-diagram.sql

Próximos passos

Com isso finalizamos nosso post. Para incrementar o que vimos neste post, poderíamos pegar um esquema de database mais complexo ou reformular nosso exemplo do campeonato de futebol para ficar mais realista. A visualização das relações entre as tabelas do database podem ajudar bastante no seu entendimento.