Códigos parciais (índices parciais)
Ao construir um índice, você pode especificar uma condição para que uma linha seja incluída no índice, por exemplo, uma das colunas não está vazia, mas a outra é igual ao valor especificado.create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using indexÍndices na expressão (índices em expressões)
Se uma expressão é freqüentemente usada em consultas a uma tabela, você pode construir um índice sobre ela. Porém, deve-se ter em mente que enquanto o otimizador não é muito flexível, a permutação de colunas na expressão levará à rejeição do uso do índice.create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan tableColuna calculada (colunas geradas)
Se os dados da coluna são o resultado da avaliação de uma expressão em outras colunas, você pode criar uma coluna virtual. Existem dois tipos: VIRTUAL (calculado toda vez que a tabela é lida e não ocupa espaço) e ARMAZENADO (calculado ao gravar dados na tabela e ocupa espaço). Obviamente, você não pode gravar dados diretamente nessas colunas.create table tab1 (
a integer primary key,
b int,
c text,
d int generated always as (a * abs(b)) virtual,
e text generated always as (substr(c, b, b + 1)) stored
);Índice R-Tree
O índice destina-se a uma pesquisa rápida em uma gama de valores / aninhamento de objetos, ou seja, tarefas típicas de geossistemas, quando objetos retangulares são especificados por sua posição e tamanho e é necessário encontrar todos os objetos que se cruzam com o atual. Este índice é implementado como uma tabela virtual (veja abaixo) e é um índice apenas em sua essência. O suporte ao índice R-Tree requer a construção de SQLite com um sinalizadorSQLITE_ENABLE_RTREE(não verificado por padrão).
create virtual table idx_rtree using rtree (
id, --
minx, maxx, -- c x
miny, maxy, -- c y
data --
);
insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);
select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00 and maxy <= 35.44;
Renomeando uma coluna
O SQLite oferece suporte insuficiente para alterações na estrutura das tabelas, portanto, após criar uma tabela, você não pode alterar uma restrição ou eliminar uma coluna. Desde a versão 3.25.0, você pode renomear uma coluna, mas não alterar seu tipo.alter table tbl1 rename column a to b;Para as demais operações, tudo se propõe também a criar uma tabela com a estrutura desejada, transferir os dados para lá, deletar a antiga e renomear a nova.
Adicionar linha, caso contrário, atualizar (Upsert)
Usando a classe deon conflictoperadores insert, você pode adicionar uma nova linha e, se já tiver uma com o mesmo valor por chave, atualize-a.
create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial')
on conflict (word) do update set count = count + 1;Atualização da declaração
Se uma linha precisa ser atualizada com base nos dados de outra tabela, você precisa usar uma subconsulta para cada coluna ouwith. Desde a versão 3.33.0, o operador foi updateestendido com uma palavra-chave frome agora você pode fazer isso
update inventory
set quantity = quantity - daily.amt
from (select sum(quantity) as amt, itemid from sales group by 2) as daily
where inventory.itemid = daily.itemid;Consultas CTE, classe com (Expressão de tabela comum)
A classewithpode ser usada como uma representação temporária para um pedido. Na versão 3.34.0, withestá declarada a possibilidade de utilizá-lo internamente with.
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
Com a adição de uma palavra-chave
recursive, withela pode ser usada para consultas em que você deseja operar em dados relacionados.
--
with recursive cnt(x) as (
values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;
--
create table tab1 (id, parent_id);
insert into tab1 values
(1, null), (10, 1), (11, 1), (12, 10), (13, 10),
(2, null), (20, 2), (21, 2), (22, 20), (23, 21);
--
with recursive tc (id) as (
select id from tab1 where id = 10
union
select tab1.id from tab1, tc where tab1.parent_id = tc.id
)
--
with recursive tc (id, parent_id) as (
select id, parent_id from tab1 where id in (12, 21)
union
select tc.parent_id, tab1.parent_id
from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;
-- , .
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null),
('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'),
('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');
with recursive
under_alice (name, level) as (
values('Alice', 0)
union all
select org.name, under_alice.level + 1
from org join under_alice on org.boss = under_alice.name
order by 2
)
select substr('..........', 1, level * 3) || name from under_alice;Função de janela ( funções de janela)
Desde a versão 3.25.0, as funções de janela, também chamadas de funções analíticas, estão disponíveis no SQLite, permitindo que você execute cálculos em um dado (janela).--
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;
--
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
(2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'),
(5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');
--
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;
-- (, c)
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;
--
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;
Utilitários SQLite
Além da CLI sqlite3 , mais dois utilitários estão disponíveis. O primeiro - sqldiff , permite comparar bancos de dados (ou uma tabela separada) não apenas por estrutura, mas também por dados. O segundo, sqlite3_analizer, é usado para exibir informações sobre como o espaço é efetivamente usado por tabelas e índices no arquivo de banco de dados. Informações semelhantes podem ser obtidas na tabela virtual dbstat (requer um sinalizadorSQLITE_ENABLE_DBSTAT_VTABao compilar o SQLite).
Desde a versão 3.22.0 CLI sqlite3 contém um comando (experimental) .expert que pode dizer qual índice adicionar para a consulta que está sendo inserida.
Crie um backup de vácuo em
Desde a versão 3.27.0, o comando foivacuumestendido com uma palavra-chave intoque permite criar uma cópia do banco de dados sem interrompê-lo diretamente do SQL. É uma alternativa simples para a API de backup .
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Função Printf
A função é análoga à função C. Nesse caso, osNULL-valores são interpretados como uma string vazia para %se 0para o marcador de posição de número.
select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0
Hora e data
Em SQLite DateeTime . Embora seja possível criar uma tabela com colunas desses tipos, será o mesmo que criar colunas sem especificar um tipo, portanto, os dados em tais colunas são armazenados como texto. Isso é conveniente ao visualizar os dados, mas tem uma série de desvantagens: pesquisa ineficiente, se não houver índice, os dados ocupam muito espaço e não há fuso horário. Para evitar isso, você pode armazenar os dados como tempo unix , ou seja, número de segundos desde a meia-noite 01/01/1970.
select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); -->
select strftime('%s', 'now'); -- Unix-
select strftime('%s', 'now', '+2 day'); --> unix-
-- unix- - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')Json
Desde a versão 3.9.0, você pode trabalhar com json no SQLite (um sinalizador deSQLITE_ENABLE_JSON1tempo de compilação ou uma extensão carregada é necessária ). Os dados Json são armazenados como texto. O resultado das funções também é texto.
select json_array(1, 2, 3); --> [1,2,3] ()
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} ()
select json_extract('{"a":[2,5],"b":10}', '$.a[0]'); --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} ()
select value from json_each(json_array(2, 5)); --> 2 2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] ()Pesquisa de texto completo
Como o json, a pesquisa de texto completo requer que um sinalizador seja definidoSQLITE_ENABLE_FTS5ao compilar ou carregar uma extensão. Para trabalhar com pesquisa, primeiro é criada uma tabela virtual com campos indexados e, em seguida, os dados são carregados usando a tabela usual insert. Deve-se ter em mente que para seu funcionamento a extensão cria tabelas adicionais e a tabela virtual criada utiliza seus dados.
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
Extensões
Os recursos SQLite podem ser adicionados por meio de módulos carregáveis. Alguns deles já foram mencionados acima - json1 e os fts .As extensões podem ser usadas para adicionar funções definidas pelo usuário (não apenas escalares, como, por exemplo
crc32, mas também agregando ou mesmo em janelas ) e tabelas virtuais. As tabelas virtuais são tabelas que estão presentes no banco de dados, mas seus dados são processados pela extensão, enquanto, dependendo da implementação, algumas delas requerem criação
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;Outros, os chamados valores de tabela , podem ser usados imediatamente
select value from generate_series(5, 100, 5);Algumas das tabelas virtuais estão listadas aqui .
Uma extensão pode implementar funções e tabelas virtuais. Por exemplo, json1 contém 13 funções escalares e 2 funções agregadas e duas tabelas virtuais
json_eache json_tree. Para escrever sua própria função, você só precisa ter conhecimento básico de C e analisar o código de extensão do repositório SQLite . Implementar suas próprias tabelas virtuais é um pouco mais complicado (aparentemente é por isso que existem poucas delas) Aqui, podemos recomendar o livro ligeiramente desatualizado Using SQLite de Jay A. Kreibich , o artigo de Michael Owens , o modelo do repositório e o código generate_series como funções com valor de tabela.
Além disso, as extensões podem implementar itens específicos do sistema operacional, como o sistema de arquivos, para fornecer portabilidade. Os detalhes podem ser encontrados aqui .
miscelânea
- Use
'(aspas simples) para constantes de string e"(aspas duplas) para nomes de colunas e tabelas. - Para obter informações sobre a tabela tab1, você pode usar
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - O SQLite tem seu próprio fórum oficial , onde o criador do SQLite - Richard Hipp participa, e onde você pode postar um relatório de bug.
- Editores SQLite: SQLite Studio , DB Browser for SQLite e (ads!) Sqlite-gui (somente Windows).