Fonctionnalités SQLite que vous avez peut-être manquées

Si vous utilisez SQLite, mais que vous ne suivez pas son développement , alors peut-être que certaines choses qui facilitent le code et que les requêtes sont allées plus vite sont passées inaperçues. Sous la coupe, j'ai essayé de lister les plus importants d'entre eux.

Codes partiels (index partiels)

Lors de la création d'un index, vous pouvez spécifier une condition pour qu'une ligne soit incluse dans l'index, par exemple, l'une des colonnes n'est pas vide, mais l'autre est égale à la valeur spécifiée.

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

Indices sur l'expression (index sur les expressions)

Si une expression est souvent utilisée dans les requêtes sur une table, vous pouvez créer un index dessus. Cependant, il faut garder à l'esprit que si l'optimiseur n'est pas très flexible, la permutation des colonnes dans l'expression entraînera le rejet de l'utilisation de l'index.

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 table

Colonne calculée (colonnes générées)

Si les données de la colonne sont le résultat de l'évaluation d'une expression sur d'autres colonnes, vous pouvez créer une colonne virtuelle. Il existe deux types: VIRTUEL (calculé à chaque fois que la table est lue et ne prend pas d'espace) et STORED (calculé lors de l'écriture de données dans la table et prend de l'espace). Bien sûr, vous ne pouvez pas écrire directement des données dans de telles colonnes.

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
);

Indice R-Tree

L'index est destiné à une recherche rapide dans une plage de valeurs / imbrication d'objets, c'est-à-dire tâches typiques des géo-systèmes, lorsque les objets rectangulaires sont spécifiés par leur position et leur taille et qu'il est nécessaire de trouver tous les objets qui se croisent avec celui en cours. Cet index est implémenté sous forme de table virtuelle (voir ci-dessous) et il s'agit d'un index uniquement dans son essence. La prise en charge des index R-Tree nécessite la construction de SQLite avec un indicateur SQLITE_ENABLE_RTREE(non coché par défaut).

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;

Renommer une colonne

SQLite prend mal en charge les changements dans la structure des tables, donc, après avoir créé une table, vous ne pouvez pas modifier une contrainte ou supprimer une colonne. Depuis la version 3.25.0, vous pouvez renommer une colonne, mais pas changer son type.

alter table tbl1 rename column a to b;

Pour les autres opérations, tout est également proposé pour créer une table avec la structure souhaitée, y transférer des données, supprimer l'ancienne et renommer la nouvelle.

Ajouter une ligne, sinon mettre à jour (Upsert)

En utilisant la classe d' on conflictopérateur insert, vous pouvez ajouter une nouvelle ligne, et si vous en avez déjà une avec la même valeur par clé, mettez-la à jour.

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;

Mettre à jour à partir de la déclaration

Si une ligne doit être mise à jour en fonction des données d'une autre table, vous deviez auparavant utiliser une sous-requête pour chaque colonne ou with. Depuis la version 3.33.0, l'opérateur a été updateétendu avec un mot from- clé et maintenant vous pouvez le faire

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;

Requêtes CTE, classe avec (Common Table Expression)

La classe withpeut être utilisée comme représentation temporaire d'une requête. Dans la version 3.34.0, la possibilité de l'utiliser à l' withintérieur est déclarée with.

with tab2 as (select * from tab1 where a > 10), 
  tab3 as (select * from tab2 inner join ...)
select * from tab3;

Avec l'ajout d'un mot-clé recursive, withil peut être utilisé pour les requêtes où vous souhaitez opérer sur des données associées.

--  
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;

Fonction de fenêtre ( fonctions de fenêtre)

Depuis la version 3.25.0, les fonctions de fenêtre, parfois appelées aussi fonctions analytiques, sont disponibles dans SQLite, vous permettant d'effectuer des calculs sur une donnée (fenêtre).

--    
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;

Utilitaires SQLite

En plus de l'interface de ligne de commande sqlite3 , deux autres utilitaires sont disponibles. Le premier - sqldiff , vous permet de comparer des bases de données (ou une table séparée) non seulement par structure, mais aussi par données. Le second, sqlite3_analizer, est utilisé pour afficher des informations sur la manière dont l'espace est effectivement utilisé par les tables et les index dans le fichier de base de données. Des informations similaires peuvent être obtenues à partir de la table virtuelle dbstat (nécessite un indicateur SQLITE_ENABLE_DBSTAT_VTABlors de la compilation de SQLite).



Depuis la version 3.22.0, l'interface de ligne de commande sqlite3 contient une commande (expérimentale) .expert qui peut vous dire quel index ajouter pour une requête d'entrée.

Créer un vide dans la sauvegarde

Depuis la version 3.27.0, la commande a été vacuumétendue avec un mot clé intoqui permet de créer une copie de la base de données sans l'arrêter directement depuis SQL. C'est une alternative simple à l' API de sauvegarde .

vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';

Fonction Printf

La fonction est analogue à la fonction C. Dans ce cas, les NULL-valeurs sont interprétées comme une chaîne vide pour %set 0pour l'espace réservé aux nombres.

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

Heure et date

Dans SQLite DateetTime . Bien qu'il soit possible de créer une table avec des colonnes de ces types, ce sera la même chose que de créer des colonnes sans spécifier de type, de sorte que les données de ces colonnes sont stockées sous forme de texte. C'est pratique lors de la visualisation des données, mais cela présente un certain nombre d'inconvénients: recherche inefficace, s'il n'y a pas d'index, les données prennent beaucoup de place et il n'y a pas de fuseau horaire. Pour éviter cela, vous pouvez stocker les données au format unix , c'est-à-dire le nombre de secondes depuis minuit le 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

Depuis la version 3.9.0, vous pouvez travailler avec json dans SQLite (un indicateur de SQLITE_ENABLE_JSON1compilation ou une extension chargée est requis ). Les données Json sont stockées sous forme de texte. Le résultat des fonctions est également du texte.

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] ()

Recherche en texte intégral

Comme json, la recherche en texte intégral nécessite qu'un indicateur soit défini SQLITE_ENABLE_FTS5lors de la compilation ou du chargement d'une extension. Pour travailler avec la recherche, une table virtuelle avec des champs indexés est d'abord créée, puis les données y sont chargées en utilisant la table habituelle insert. Il faut garder à l'esprit que pour son fonctionnement, l'extension crée des tables supplémentaires et la table virtuelle créée utilise leurs données.

create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender  body  fts5

Extensions

Les fonctionnalités SQLite peuvent être ajoutées via des modules chargeables. Certains d'entre eux ont déjà été mentionnés ci-dessus - json1 et les fts .



Les extensions peuvent être utilisées à la fois pour ajouter des fonctions définies par l'utilisateur (non seulement des fonctions scalaires, comme, par exemple crc32, mais aussi des fonctions d' agrégation ou même fenêtrées ) et des tables virtuelles. Les tables virtuelles sont des tables présentes dans la base de données, mais leurs données sont traitées par l' extension, alors que, selon l'implémentation, certaines d'entre elles nécessitent une création

create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;

D'autres, dits table , peuvent être utilisés immédiatement.

select value from generate_series(5, 100, 5);

...

Certaines des tables virtuelles sont répertoriées ici .



Une extension peut implémenter à la fois des fonctions et des tables virtuelles. Par exemple, json1 contient 13 fonctions scalaires et 2 fonctions d'agrégation et deux tables virtuelles json_eachet json_tree. Pour écrire votre propre fonction, il vous suffit d'avoir des connaissances de base en C et d'analyser le code d'extension du référentiel SQLite . Implémenter vos propres tables virtuelles est un peu plus compliqué (apparemment, c'est pourquoi il y en a peu). Ici, nous pouvons recommander le livre légèrement obsolète Using SQLite de Jay A. Kreibich , l'article de Michael Owens , le modèle du référentiel et le code generate_series en tant que fonctions table.



En outre, les extensions peuvent implémenter des éléments spécifiques au système d'exploitation, tels que le système de fichiers, pour assurer la portabilité. Les détails peuvent être trouvés ici .

miscellanea

• Utilisez '(guillemet simple) pour les constantes de chaîne et "(guillemet double) pour les noms de colonne et de table.
• Pour obtenir des informations sur le tableau tab1, vous pouvez utiliser
  
  
  
  

  --  main 
  select * from pragma_table_info('tab1');
  --  temp    (attach) 
  select * from pragma_table_info('tab1') where schema = 'temp'

• SQLite a son propre forum officiel , auquel participe le créateur de SQLite - Richard Hipp, et où vous pouvez publier un rapport de bogue.
  
  
• Éditeurs SQLite: SQLite Studio , DB Browser pour SQLite et (annonces!) Sqlite-gui (Windows uniquement).