Colunas geradas pelo SQLite

Resumo : neste tutorial, você aprenderá sobre as colunas geradas pelo SQLite.

Introdução às colunas geradas pelo SQLite

SQLite introduziu as colunas geradas desde a versão 3.31.0.

Por definição, colunas geradas são as colunas de uma tabela cujos valores são derivados de uma expressão que envolve outras colunas da mesma tabela.

As colunas geradas também são conhecidas como colunas computadas.

Para definir uma coluna gerada, use a GENERATED ALWAYSsintaxe de restrição de coluna da seguinte forma:

column_name data_type 
    [GENERATED ALWAYS] AS expression 
    [VIRTUAL | STORED]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, você especifica o expressionque calcula os valores column_nameapós as GENERATED ALWAYSpalavras-chave. Normalmente expressionenvolve colunas da mesma linha na mesma tabela.

As GENERATED ALWAYSpalavras-chave são opcionais. Portanto, você pode torná-lo mais curto da seguinte maneira:

column_name data_type AS expression [VIRTUAL | STORED]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Uma coluna gerada pode ser VIRTUALou STORED.

Se uma coluna gerada for VIRTUAL, o SQLite não armazena os valores da coluna fisicamente. Em vez disso, quando você lê os valores da coluna gerada, o SQLite calcula esses valores com base na expressão especificada na declaração da coluna.

Caso uma coluna gerada seja STORED, o SQLite armazena os valores da coluna fisicamente. Em outras palavras, a STOREDcoluna gerada ocupa espaços no arquivo do banco de dados. SQLite atualiza os valores da STOREDcoluna gerada quando você grava no banco de dados.

SQLite usa VIRTUALpor padrão quando você não especifica explicitamente VIRTUALou STOREDna declaração de coluna gerada.

Na prática, você utiliza a STOREDopção quando deseja otimizar para leitura e a opção VIRTUAL quando deseja otimizar para escrita.

Exemplo de coluna gerada por SQLite

Primeiro, crie uma tabela chamada productsusando a seguinte CREATE TABLEinstrução:

CREATE TABLE products(
    name TEXT NOT NULL,
    price REAL NOT NULL,
    discount REAL NOT NULL,
    tax REAL NOT NULL,
    net_price REAL GENERATED ALWAYS 
        AS (price * (1-discount) * (1+tax))
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Na productstabela, net_priceé uma coluna gerada cujo valor deriva das colunas de preço, desconto e imposto.

Como não especificamos VIRTUALou STOREDpara a net_pricecoluna, a net_pricecoluna é usada VIRTUALpor padrão.

Segundo, insira uma nova linha na productstabela. Observe que ele não fornece valores para a net_pricecoluna:

INSERT INTO products(name, price, discount, tax)
VALUES('ABC Widget',100, 0.05, 0.07);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados da productstabela:

SELECT * FROM products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resultado:

name        price       discount    tax         net_price
----------  ----------  ----------  ----------  ----------
ABC Widget  100.0       0.05        0.07        101.65
Linguagem de código:  Sessão Shell  ( shell )

Como você pode ver claramente na saída, o valor da net_pricecoluna é calculado com base nos valores das colunas price, discounte tax.

Recursos de coluna gerada pelo SQLite

As colunas geradas possuem os seguintes recursos:

• Uma coluna gerada pode ter um tipo de dados. O SQLite converterá o valor para expressionesse tipo de dados usando as mesmas regras de afinidade das colunas regulares.
• Uma coluna gerada pode ter restrições NOT NULL, CHECK, UNIQUEe FOREIGN KEY.
• Uma coluna gerada pode fazer parte de um ou mais índices.
• Uma coluna gerada pode fazer referência a outras colunas, incluindo outras colunas geradas na mesma tabela, desde que não faça referência a si mesma.

SQLite coloca as seguintes restrições nas colunas geradas:

• Uma coluna gerada não pode ter um valor padrão.
• Uma coluna gerada não pode ser usada como parte de um arquivo PRIMARY KEY.
• Se uma tabela tiver uma coluna gerada, ela deverá ter pelo menos uma coluna gerada.
• NÃO é possível usar a ALTER TABLE ADD COLUMNinstrução para adicionar uma STOREDcoluna. Porém, é possível usar a ALTER TABLE ADD COLUMNinstrução para adicionar uma VIRTUALcoluna.
• Uma coluna gerada não pode fazer referência a si mesma, direta ou indiretamente.
• A expressão não pode usar subconsultas , funções agregadas , funções de janela ou funções com valor de tabela. Ele só pode fazer referência a constantes literais, colunas nas mesmas linhas e funções determinísticas escalares.
• A expressão de uma coluna gerada pode fazer referência a uma INTEGER PRIMARY KEYcoluna, mas não pode fazer referência direta a uma ROWIDcoluna.

Resumo

• Colunas geradas são colunas cujos valores derivam de outras colunas da mesma tabela.
• Use GENERATED ALWAYSrestrição para declarar uma coluna gerada.
• Use a opção VIRTUALou STOREDpara uma coluna gerada. A STOREDcoluna ocupa espaço no arquivo de banco de dados, enquanto a VIRTUALcoluna não. Por padrão, o SQLite usa a VIRTUALopção se você não a especificar.