Tipos de dados SQLite: Uma análise detalhada das classes de armazenamento

O SQLite usa um sistema de tipos flexível baseado em classes de armazenamento e afinidade de tipos. Diferente dos RDBMSs tradicionais, que exigem tipagem rígida, o SQLite permite um tratamento mais dinâmico dos tipos. Entender esse modelo é importante pra garantir a consistência dos dados e evitar bugs sutis na lógica da aplicação.

Neste artigo, vou falar sobre o sistema de tipos do SQLite e mostrar como trabalhar com os tipos de dados mais comuns no SQLite. Se você é novo no SQL, considere começar com nosso curso Introdução ao SQL para construir uma base sólida. Além disso, acho que a Folha de Referência Básica do SQL, que você pode baixar, é uma referência útil porque tem todas as funções mais comuns do SQL.

Noções básicas do sistema de tipos do SQLite

Para trabalhar com o SQLite, é importante entender como ele guarda e interpreta os dados internamente. Vou explicar abaixo como um sistema tipo SQLite cria aplicativos confiáveis e eficientes.

Explicação sobre as classes de armazenamento

O SQLite usa cinco classes de armazenamento pra representar valores internamente:

• NULL significa que um valor está faltando ou não foi definido.

• INTEGER representa um número inteiro assinado armazenado em 1 a 8 bytes, dependendo da magnitude.

• REAL descreve um número de ponto flutuante armazenado como um valor IEEE-754 de 8 bytes.

• TEXT armazena sequências de texto codificadas em UTF-8, UTF-16BE ou UTF-16LE.

• BLOB armazena dados binários exatamente como foram inseridos, sem nenhuma transformação.

Tipos de dados SQLite. Fonte da imagem: OpenAI

Essas classes de armazenamento são diferentes dos tipos de dados declarados em um esquema de tabela. Uma coluna pode ser declarada como VARCHAR(50) ou BOOLEAN, mas o valor real é armazenado usando uma das cinco classes de armazenamento com base no seu conteúdo. Essa separação dá ao SQLite sua natureza dinâmica e flexível, mas também exige que os desenvolvedores fiquem atentos à validação de dados e à consistência de tipos no código de suas aplicações.

Afinidade do tipo e comportamento da coluna

A afinidade de tipo no SQLite é um tipo recomendado atribuído a uma coluna com base no tipo de dados declarado. O SQLite usa as regras a seguir para determinar a afinidade a partir do texto do tipo declarado.

• Colunas declaradas como “ VARCHAR ”, “ CHAR ” ou “ TEXT ” recebem uma afinidade “ TEXT ”.

• Tipos como INT e INTEGER têm afinidade com INTEGER.

• Declarações como DECIMAL, NUMERIC ou BOOLEAN recebem uma afinidade de NUMERIC.

• Tipos como REAL, FLOAT ou DOUBLE têm afinidade com REAL.

• A afinidade padrão é BLOB se nenhum tipo reconhecido for declarado.

Por exemplo, uma coluna declarada como “ VARCHAR(100) ” vai ter uma afinidad TEXT, o que significa que o SQLite vai tentar converter os valores em texto quando forem armazenados. Essa afinidade mostra como o SQLite força e guarda os dados, mas não impede que outros tipos sejam guardados nessa coluna.

Tipagem manifesta e suas implicações

O SQLite usa tipagem manifesta, o que significa que o tipo de dados está associado ao valor individual, não ao contêiner da coluna. Isso permite que vários tipos de valores coexistam em uma única coluna. Por exemplo, uma coluna declarada como “ INTEGER ” pode ter valores como “ TEXT ” ou “ BLOB ”.

A tipagem manifesta permite que os desenvolvedores insiram vários tipos de dados sem mexer no esquema. Mas, o problema é que isso pode causar dados inconsistentes, uma classificação imprevisível e uma dependência maior da lógica da aplicação para garantir as regras de tipo.

Recomendo fazer nosso curso de Design de Banco de Dados para aprendersobre gerenciamento de bancos de dados, incluindo como organizar dados em diferentes tipos e gerenciar bancos de dados.

Trabalhando com tipos de dados comuns no SQLite

Agora vamos ver como lidar com tipos de dados comuns no SQLite, como texto, números, booleanos, datas e dados binários.

Texto, números e booleanos

As declarações de dados mais usadas no SQLite são TEXT, INTEGER, REAL, NUMERIC e BOOLEAN, mas ele guarda os valores com base na afinidade dos tipos, não nos tipos estritos. Aqui estão alguns casos de uso para cada tipo de dados em um esquema do mundo real.

• TEXT armazena strings, que são ótimas pra nomes, e-mails, URLs e dados textuais em geral.

• INTEGER É ideal para números inteiros, como contagens, IDs e códigos, oferecendo armazenamento e classificação eficientes.

• REAL armazena números de ponto flutuante para medições, moedas ou valores não inteiros.

• A afinidade NUMERIC abrange valores declarados como DECIMAL, BOOLEAN ou similares. Dependendo do valor, o SQLite tenta armazená-los como INTEGER ou REAL.

• BOOLEAN não é uma classe de armazenamento nativa, mas é simulada armazenando FALSE ( 0 ) ou TRUE ( 1 ) como valores de INTEGER nos bastidores.

Eu recomendo usar INTEGER para booleanos com restrições explícitas do lado da aplicação, enquanto NUMERIC é mais legal para valores que precisam de uma análise numérica flexível.

Datas, horários e estratégias temporais

O SQLite não tem um tipo nativo DATE ou DATETIME, mas aceita os seguintes formatos para guardar dados temporais:

• TEXT: Armazena datas/horas como strings ISO8601, tipo YYYY-MM-DD HH:MM:SS. Esse formato é fácil de ler e funciona com várias ferramentas, mas pode ser mais lento pra comparações.

• REAL: Armazena datas como números de dias julianos (ponto flutuante), úteis para cálculos aritméticos de datas e consultas de intervalos, mas menos legíveis.

• INTEGER: Armazena carimbos de data/hora Unix, em segundos desde 1970-01-01. É eficiente para armazenamento e comparações numéricas rápidas, mas não é legível por humanos.

Quando estiver trabalhando com dados temporais, você pode usar essas funções: date(), time(), datetime(), julianday() e strftime() para analisar, formatar e converter entre formatos temporais.

Casos extremos binários e numéricos

O SQLite também aceita dados binários através da classe de armazenamento BLOB. Os BLOBs são úteis para guardar imagens, arquivos ou qualquer tipo de dados que não sejam texto. Quando estiver trabalhando com tipos de dados BLOB, sempre use consultas parametrizadas ou ligações para inserir BLOBs. Você também pode guardar arquivos binários grandes em outro lugar e usar caminhos ou hashes pra fazer referência a eles.

Embora o SQLite possa guardar números inteiros de até 8 bytes (-2^63 a 2^63-1), os valores além desse intervalo podem ser convertidos silenciosamente para o tipo de dados REAL. Isso pode fazer com que os valores fiquem meio imprecisos. O SQLite não tem uma regra rígida pra precisão decimal, então sempre use bibliotecas externas ou lógica de aplicativo pra dados financeiros quando precisar de precisão.

Aplicando estrutura com tabelas STRICT

Diferente do sistema tradicional de tipagem flexível, o SQLite trouxe tabelas de tipo flexível ( STRICT ) na versão 3.37.0. Uma tabela de tipo de dados " STRICT " tem um modo de tipagem mais rígido que exige que os valores das colunas tenham os mesmos tipos. Quando você declara uma tabela como STRICT, todo valor inserido precisa combinar com o tipo declarado da coluna, e qualquer incompatibilidade vai gerar um erro. 

O modo “ STRICT ” melhora a consistência e a validação dos dados. Isso também ajuda a detectar erros relacionados a tipos logo no começo do desenvolvimento e torna o comportamento do SQLite mais previsível, mais parecido com os RDBMSs tradicionais.

Antes de usar STRICT como desenvolvedor, é bom saber que ele só está disponível no SQLite 3.37.0 e versões mais recentes. Além disso, você não pode misturar a tipagem de manifestos e de e STRICT os na mesma tabela.

Considerações sobre desempenho e design

O SQLite tem um sistema de tipos flexível com implicações no desempenho. O SQLite usa codificação de comprimento variável para valores de tipo INTEGER e REAL, o que significa que números menores usam menos bytes. Isso ajuda a minimizar o tamanho do banco de dados, principalmente para conjuntos de dados grandes. 

A afinidade de tipos também influencia como os valores são armazenados e comparados. Tipos inconsistentes em uma coluna podem reduzir a eficácia dos índices, já que o SQLite pode fazer conversões de tipo extras durante as pesquisas, deixando as consultas mais lentas.

Pense nessas estratégias pra criar esquemas e consultas eficientes.

• Defina colunas com afinidades claras e consistentes para minimizar a sobrecarga de coerção de tipos.

• Pense em usar conversões explícitas nas consultas quando precisar converter tipos, pra garantir que tudo funcione direitinho.

• Use a afinidade INTEGER para IDs numéricos e contadores, REAL para dados de ponto flutuante e TEXT para strings.

• Evite misturar tipos de dados na mesma coluna para evitar problemas de classificação e comparação.

• Normalize esquemas para evitar coerção de tipos redundantes ou duplicação de dados.

• Use o EXPLAIN QUERY PLAN para ver como está o desempenho e ajustar as consultas.

Exemplos de tipos de dados SQLite com padrões de esquema

Os esquemas das tabelas a seguir mostram como usar os tipos de dados, afinidade de tipos e restrições do SQLite de um jeito legal.

Tabela de usuários com simulação booleana e restrições

No exemplo abaixo:

• is_active simula um booleano usando um objeto de tipo " INTEGER " com uma restrição de tipo " CHECK ".

• TEXT O tipo de dados garante a consistência dos valores de string, enquanto o tipo booleano ( INTEGER ) é para lógica do tipo booleano.

 -- Create users table with auto-incrementing rowid and enforce uniqueness
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    username TEXT NOT NULL UNIQUE, 
    email TEXT NOT NULL, 
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))  -- Simulates boolean
);

Ordena tabelas usando dados temporais e precisão numérica

Na consulta abaixo, total usa NUMERIC para valores parecidos com moedas, pra lidar com a precisão no nível do aplicativo. order_date é guardado como TEXT no formato ISO-8601, garantindo a legibilidade e a compatibilidade com funções de data.

-- Create orders table with row identifier and foreign key in practice
-- ISO date format "YYYY-MM-DD"
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,  
    customer_id INTEGER NOT NULL, 
    total NUMERIC NOT NULL, 
    order_date TEXT NOT NULL CHECK (length(order_date) = 10)  );

Tabela de arquivos com verificações binárias e de tipo

Na consulta abaixo, content usa BLOB para guardar dados binários brutos. uploaded_at guarda a hora como um INTEGER para economizar espaço e melhorar o desempenho.

-- Create files table with raw binary data and Unix timestamp (epoch seconds)
CREATE TABLE files (
    file_id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    content BLOB NOT NULL,  
    uploaded_at INTEGER NOT NULL 
);

Você pode conferir o tipo na hora com a seguinte consulta:

SELECT typeof(uploaded_at), typeof(content) FROM files;

Recomendo fazer nosso curso Introdução a bancos de dados relacionais em SQL para sabermais sobre estruturas de dados e como criar tabelas em bancos de dados.

Comparando o SQLite com outros bancos de dados

O sistema de tipos do SQLite é mais flexível do que os RDBMSs tradicionais, como MySQL ou PostgreSQL. A tabela abaixo compara o SQLite com esses bancos de dados.

Conclusão

O SQLite tem um sistema de tipagem flexível que oferece várias vantagens quando usado com cuidado. Os desenvolvedores podem criar esquemas que equilibram adaptabilidade e confiabilidade ao entender as classes de armazenamento, a afinidade de tipos e a tipagem de manifesto. Recursos como tabelas de STRICT, uso consistente de tipos e normalização ajudam a reforçar a estrutura onde é necessário.

Depois, recomendo fazer os cursos Análise exploratória de dados em SQL e Manipulação de dados em SQL pra aprender a analisar dados de diferentes tipos usando consultas SQL avançadas.