No SQLite, use tabelas STRICT por padrão

 (evanhahn.com)
1 pontos por GN⁺ 3 시간 전 | 1 comentários | Compartilhar no WhatsApp
  • Adicionar STRICT ao final da definição de uma tabela permite bloquear cedo erros de tipo, como texto arbitrário em uma coluna de inteiros, aumentando a integridade dos dados
  • Ao inserir ou atualizar, os tipos são verificados, mas valores que podem ser convertidos sem perda, como '123', são permitidos; os tipos de coluna ficam limitados a INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Para colunas que precisam armazenar vários tipos, é possível especificar ANY, combinando validação rigorosa e armazenamento flexível em uma mesma tabela
  • Não é possível transformar diretamente uma tabela existente em STRICT; é necessário criar uma nova tabela e copiar os dados, limpando ou convertendo dados antigos inválidos
  • Tabelas STRICT são compatíveis apenas com SQLite 3.37.0 ou superior e, embora haja um custo teórico de verificação, testes informais não identificaram diferenças claras de desempenho ou tamanho de arquivo

Criação de tabelas STRICT

  • As tabelas STRICT do SQLite aplicam uma verificação de tipos mais rigorosa, semelhante à de outros motores SQL
  • Basta acrescentar STRICT ao final da definição em CREATE TABLE
CREATE TABLE people (name TEXT) STRICT;

Verificação de tipos em inserções e atualizações

  • Tabelas SQLite comuns podem armazenar texto como 'garbage' até mesmo em colunas INTEGER, mas tabelas STRICT tratam incompatibilidades de tipo como erro
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- 정상 처리됨

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- 오류: cannot store TEXT value in INTEGER column
  • A mesma validação também se aplica a UPDATE, impedindo tipos incorretos ao alterar valores após o armazenamento
  • No entanto, se o valor puder ser convertido sem perda, ele também é permitido em tabelas STRICT
    • A string '123' pode ser convertida integralmente para o inteiro 123, então as duas inserções abaixo são tratadas da mesma forma
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

Restrições de tipo na definição da tabela

  • Em tabelas comuns, é possível usar na declaração de colunas até nomes de tipos que o SQLite não oferece suporte
    • GARBAGE, DATETIME, JSON, UUID e BLOBB são todos aceitos
    • Essas declarações podem resultar de erros de digitação ou de mal-entendidos sobre os tipos de dados suportados pelo SQLite
  • Tabelas STRICT geram erro já na criação quando se usa um nome de tipo não suportado
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
  • Os únicos tipos de coluna permitidos são INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Como todas as colunas precisam ter um tipo especificado, não é possível omitir o tipo como em CREATE TABLE tbl (name)

Mantendo flexibilidade com ANY

  • Se for necessário armazenar dados cujo tipo não é constante, é possível usar uma coluna ANY
  • Mesmo dentro de uma tabela STRICT, colunas ANY podem armazenar todos os tipos, incluindo inteiros, texto, números reais e BLOBs
CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

Migração de tabelas existentes

  • Como não há uma forma de converter diretamente uma tabela não estrita existente em uma tabela STRICT via ALTER, é mais simples criá-la estrita desde o início
  • Para converter uma tabela existente, é preciso seguir o processo de criar uma nova tabela STRICT → copiar os dados → substituir a tabela antiga
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • Se os dados existentes tiverem tipos incorretos, como texto em uma coluna de inteiros, a cópia pode gerar erro
    • Antes da migração, talvez seja necessário limpar os dados ou convertê-los com CAST
  • Também é possível aplicar STRICT apenas a novas tabelas, mas isso faz com que a intensidade da validação varie entre tabelas, o que pode tornar o comportamento mais difícil de prever do que se todas fossem flexíveis

Quando tipos flexíveis são adequados

  • A equipe de desenvolvimento do SQLite documenta separadamente as vantagens dos tipos flexíveis e apresenta casos em que o comportamento padrão é útil
  • Usos em que tabelas não estritas podem ser razoáveis incluem:
    • Um armazenamento chave-valor puro que guarda vários tipos diretamente
    • Um espaço para armazenar outros atributos de tipos diferentes
    • Casos em que é preciso importar CSVs desorganizados diretamente e preservar até valores inválidos sem perdê-los
  • Porém, tipos inesperados podem causar bugs sutis; em tabelas comuns, costuma ser melhor gerar erro imediatamente do que permitir silenciosamente
  • Há também um comentário no código-fonte do SQLite que chama tabelas não estritas de legacy, mas é difícil tratá-lo como uma base mais confiável do que a documentação oficial

Compatibilidade de versão

  • Tabelas STRICT foram introduzidas no SQLite 3.37.0, lançado em novembro de 2021
  • Versões anteriores do SQLite não podem usar tabelas STRICT
  • Versões antigas também não conseguem ler bancos de dados que contenham tabelas STRICT
    • Se você criar uma tabela STRICT no SQLite mais recente e depois abrir o mesmo banco de dados com o SQLite 3.36.0, ocorrerá um erro

Desempenho e espaço de armazenamento

  • Como tabelas STRICT fazem uma verificação adicional do tipo de dado em inserções e atualizações, elas podem ser mais lentas em teoria
  • Em um experimento informal que inseriu milhões de linhas em uma tabela com 100 colunas, não houve diferença clara de desempenho em vários computadores
  • O tamanho do arquivo de banco de dados em disco também foi igual, mas, por não ser um benchmark rigoroso, podem existir diferenças que não foram detectadas
  • Também é possível que o desempenho melhore por impedir que valores incompatíveis com a afinidade da coluna sejam salvos por engano, mas isso não foi verificado separadamente

Pontos a considerar ao adotar

  • Tabelas STRICT não resolvem todos os problemas de dados, mas reduzem erros relacionados a tipos e reforçam a integridade dos dados
  • Na maioria dos casos, a adoção é simples: basta adicionar STRICT à definição da tabela
  • Em tabelas comuns, os benefícios da validação rigorosa de tipos tendem a superar o esforço de migração, a incompatibilidade com versões antigas e a flexibilidade limitada

Leituras relacionadas

1 comentários

 
GN⁺ 3 시간 전
Comentários do Hacker News

  • Como o SQLite não tem um recurso para usar ALTER e mudar uma tabela para o modo estrito, sendo necessário copiar os dados de uma tabela não estrita para uma tabela estrita, foi adicionada ao sqlite-utils 4.1 uma função de conversão nos dois sentidos
    Na CLI, pode ser usado com uvx sqlite-utils transform data.db mytable --strict; em Python, com db.table("mytable").transform(strict=True)
    As notas de lançamento estão em https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., a documentação da API Python em https://sqlite-utils.datasette.io/en/stable/python-api.html#... e a documentação da CLI em https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  • https://sqlite.org/flextypegood.html explica por que a tipagem flexível é o padrão e por que isso provavelmente não vai mudar, mas isso não bate com a experiência prática
    Ao contrário da explicação de que, mesmo que o nome de um cliente seja colocado no Customer.creditScore inteiro, isso pode ser facilmente detectado e corrigido, na prática uma linha corrompida é difícil de recuperar e os dados podem até ter desaparecido por completo
    Também é muito difícil concordar com a lógica de que a checagem rígida de tipos só impede erros fáceis de encontrar e, por isso, acabaria dificultando a detecção e a correção de bugs

    • É difícil entender a visão de que a segurança e a confiabilidade de uma configuração de banco de dados mais robusta tornariam mais difícil corrigir bugs de dados
      No Postgres, a prática é adicionar o máximo possível de verificações e proteções, para não precisar sair procurando depois erros que nem deveriam ter acontecido
    • O MongoDB também tinha uma lógica parecida de que dava para armazenar qualquer coisa, mas a maioria acabou percebendo, depois do uso real, que isso geralmente é uma característica ruim
      Isso parece menos um princípio de engenharia rigoroso e mais um resultado da época em que o SQLite foi criado e das convicções fortes do autor, passando a impressão de manter a posição a qualquer custo depois de muitas críticas
      O tipo de flexibilidade desejado pode ser oferecido com tipos como JSON ou HSTORE no Postgres, mas quase sempre é melhor deixar isso como uma escolha quando necessário, em vez de impor tipagem ilimitada como padrão

  • Seria bom se STRICT fosse o padrão
    Tirando esse ponto, o desenvolvedor do SQLite é alguém incrível que criou uma ferramenta excelente

    • Nem mesmo chaves estrangeiras vêm ativadas por padrão, sendo necessário usar PRAGMA foreign_keys = ON;
      O problema maior é que não existe uma pragma equivalente para tabelas estritas, então é preciso adicionar o STRICT, que não é padrão, a todo CREATE TABLE
      Uma pragma global para STRICT chegou a ser considerada, mas não foi implementada; materiais relacionados podem ser vistos em https://sqlite.org/foreignkeys.html e https://sqlite.org/forum/forumpost/1b9d073a37ca5998
    • O SQLite prioriza a compatibilidade retroativa e quase nunca muda os padrões
      A ideia é evitar que um software para SQLite 3.53, ao ser atualizado para 3.54, passe de repente a criar tabelas estritas com CREATE TABLE, gere erros e quebre tudo
    • Há um problema parecido com as restrições de chave estrangeira, que também ficam desativadas por padrão por motivos de compatibilidade
      Isso faz pensar se houve um tempo em que o SQLite só suportava a sintaxe de chaves estrangeiras, sem implementar de fato a funcionalidade
    • Também faz falta um tipo de data/timestamp de verdade, e não apenas strings
    • Não há motivo para um banco de dados permitir por engano a inserção de tipos incorretos, e a filosofia de segurança de tipos do SQLite deixa a desejar
      Já houve caso de código distribuído para milhares de dispositivos armazenar as strings '1' e '0' em uma coluna booleana, e limpar isso depois não foi nada agradável
      Também não existe um tipo de timestamp, então é preciso guardar isso em uma coluna de texto, e até as funções padrão de data e hora geram yyyy-mm-dd HH:MM:SS, que assume UTC implicitamente, em vez do formato ISO yyyy-mm-ddTHH:MM:SSZ
      O SQLite é realmente um projeto excelente, mas algumas decisões de design são desconcertantes

  • Vindo de um ambiente de SQL corporativo, eu nunca levei o SQLite muito a sério porque os tipos de campo não eram, por padrão, realmente impostos, e até me surpreendi quando ele virou a base dos metadados de apps de smartphone
    Isso me lembra aquela velha história de rede em que alguém escolhe UDP por causa da baixa latência e da simplicidade, e depois acaba recriando manualmente na aplicação a maior parte dos mecanismos de confiabilidade do TCP

    • Ao implementar por conta própria, dá para fazer diferente do TCP e às vezes obter grandes ganhos, e QUIC e HTTP/3 são exemplos disso
      Mas não parece que implementar checagem de tipos manualmente em cima de um SQLite não estrito traga vantagens parecidas
    • Chaves estrangeiras nem eram suportadas antes de 2009 e até hoje não vêm ativadas por padrão: https://sqlite.org/foreignkeys.html
    • Às vezes essa abordagem até vira um negócio de sucesso: https://aeron.io/
    • Dá para entender não levar o SQLite muito a sério se a checagem de tipos não for totalmente suportada, mas a maioria dos bancos de dados também não oferece configurações padrão perfeitas
      O PostgreSQL também vem com padrões voltados para sistemas modestos, então é preciso configurar para obter o melhor desempenho
      Além disso, o TCP é baseado em fluxo, então muitos aplicativos sofrem com bloqueio HOL; e, se você criar manualmente uma camada de confiabilidade sobre UDP, depois de muito trabalho ainda pode acabar com algo melhor que TCP
    • Se você se interessa por Jeep ou Bronco, não vai procurar um avaliador de carros genérico
      Eles vão dizer que são barulhentos e têm dirigibilidade ruim, mas isso acontece porque abordam um produto pensado para outro uso como se apenas o próprio caso de uso deles importasse

  • Tabelas estritas têm a desvantagem de que alguns tipos, como Date, não podem ser usados, mas ainda assim deveriam ser o padrão
    Se vários aplicativos compartilham o banco de dados, deveria ser possível confiar no tipo declarado; se um deles gravar uma string numa coluna numérica, estraga tudo para os outros
    Por outro lado, no principal caso de uso do SQLite, como banco de dados embarcado, normalmente só existe um único aplicativo usando-o, então também há a vantagem de poder evoluir o esquema em vez de criar um banco novo e copiar tudo
    Nesse caso, o código da aplicação sabe o que esperar de cada coluna, inclusive tipos mistos

    • O SQLite só tem 5 tipos de dados: INTEGER, TEXT, BLOB, REAL, NUMERIC: https://sqlite.org/datatype3.html
    • O SQLite não tem tipo de data, e também não há como chamar EXPLAIN em uma consulta SELECT arbitrária para recuperar o nome de tipo definido apenas na forma
      Portanto, mesmo usando nomes como DATE ou DATETIME, não dá nem para inferi-los
    • Date não é um tipo real; isso apenas cria uma coluna com afinidade numérica

  • O modo estrito restringe a notação dos tipos de coluna, impedindo o uso de nomes mais significativos e fazendo com que o código que mapeia tipos do banco para tipos da aplicação não possa aproveitar esses nomes, então ele acaba até atrapalhando um sistema de tipos mais rigoroso na camada da aplicação
    Há mais detalhes em https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type..., e, se você trabalha com banco de dados usando crates como sqlx em Rust, eu diria que é melhor evitar o modo estrito

  • Eu quero tipos estritos em banco de dados e RPC, mas o SQLite tem um propósito um pouco diferente; se você realmente usá-lo, talvez passe a entender melhor https://sqlite.org/flextypegood.html
    Por exemplo, o objetivo de fazer com que um script qualquer, não escrito para SQLite, acabe funcionando por acaso é uma característica que normalmente nem entra nas considerações de outros SGBDs

    • Na lista de prós e contras desse documento, falta o argumento central
      A principal vantagem da tipagem flexível é que ela permite evoluir o esquema com facilidade
      Quando os requisitos de um banco embarcado mudam, em vez de criar um banco novo e migrar os dados, você pode alterar o esquema no próprio lugar; e, se só um aplicativo lê e escreve, não há surpresa se uma string aparecer numa coluna inteira
      Em contrapartida, se vários aplicativos compartilham o mesmo banco e são atualizados em ritmos diferentes, o esquema é um contrato, então um único tipo incorreto pode quebrar outro aplicativo
      Se uma tabela criada por um aplicativo for usada por outro, os tipos de dados combinados precisam ser seguidos com rigor
    • Na maioria dos casos, o código já é verificado em tempo de compilação para ver se respeita a estrutura de tipos, e o esquema SQLite desenvolvido junto com ele fornece as informações necessárias para a análise estática
      Verificar de novo em tempo de execução não traz ganho real, e um código que a análise estática confirmou que insere uma string não vai de repente sofrer mutação e passar a inserir um inteiro
      O SQLite é diferente do Postgres, onde vários aplicativos compartilham dados e é preciso confiar em terceiros
      Em um ambiente como o Postgres, a validação em tempo de execução é essencial, mas o SQLite foi concebido, por padrão, para um aplicativo e um banco de dados; portanto, basta confiar no próprio código, que pode ser avaliado em tempo de compilação
      Em situações atípicas em que vários aplicativos compartilham um único arquivo, é possível ativar tabelas estritas

  • Tipagem dinâmica pode servir para usos como um armazenamento simples de chave e valor, mas é preciso perguntar o que está mais de acordo com o princípio da menor surpresa: INTEGER aceitar 'hello world' sem erro, ou só permitir esse tipo de valor quando algo como a palavra-chave NONSTRICT ou o tipo ANY for explicitado
    A maioria dos usuários de SQLite provavelmente não esperaria que a primeira opção fosse possível

    • Isso talvez aconteça porque o SQLite queria ser um banco sem tipos, mas com uma sintaxe parecida com SQL padrão
      Isso também bate com a explicação de que ele quer que código feito para outros SGBDs acabe funcionando por acaso no SQLite
      Ainda assim, é muito surpreendente que o usuário não tenha omitido o tipo, tenha escrito INTEGER explicitamente e ainda assim consiga inserir texto

  • Já houve caso de UUIDs começando com algo como 08123… serem interpretados como octal, fazendo com que parte deles fosse convertida erroneamente para número
    Foi confuso e irritante, mas resolveu-se aplicando STRICT e recriando a tabela inteira

    • Isso não foi culpa do SQLite, mas de um driver da linguagem tentando ajudar demais
      O SQLite não tem tipo octal

  • Uso CREATE TABLE ... STRICT WITHOUT ROWID como padrão e não vejo motivo para, deliberadamente, criar de outra forma