Возможности SQLite, которые вы могли пропустить

    Если вы используете SQLite, но не следите за его развитием, то возможно некоторые вещи, позволяющие сделать код проще, а запросы быстрее, прошли незамеченными. Под катом я постарался перечислить наиболее важные из них.

    Частичные индексы (Partial Indexes)

    При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.

    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

    Индексы на выражение (Indexes On Expressions)

    Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.

    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

    Вычисляемые колонки (Generated Columns)

    Если данные столбца представляют собой результат вычисления выражения по другим столбцам, то можно создать виртуальный столбец. Есть два вида: VIRTUAL (вычисляется каждый раз при чтении таблицы и не занимает места) и STORED (вычисляется при записи данных в таблицу и место занимает). Разумеется записывать данные в такие столбцы напрямую нельзя.

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

    R-Tree индекс

    Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).

    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;
    

    Переименование колонки

    В SQLite слабо поддерживает изменения в структуре таблиц, так, после создания таблицы, нельзя изменить ограничение (constraint) или удалить столбец. С версии 3.25.0 можно переименовать столбец, но не изменить его тип.

    alter table tbl1 rename column a to b;

    Для других операций всё также предлагается создать таблицу с нужной структурой, перелить туда данные, удалить старую и переименовать новую.

    Добавить строку, иначе обновить (Upsert)

    Используя класс on conflict оператора insert, можно добавить новую строку, а при уже имеющейся с таким же значением по ключу, обновить.

    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;

    Оператор Update from

    Если строка должна быть обновлена на основе данных другой таблицы, то ранее приходилось использовать вложенный запрос для каждого столбца или with. С версии 3.33.0 оператор update расширен ключевым словом from и теперь можно делать так

    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;

    CTE запросы, класс with (Common Table Expression)

    Класс with может использоваться как временное представление для запроса. В версии 3.34.0 заявлена возможность использования with внутри with.

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

    С добавлением ключевого слова recursive, with можно использовать для запросов, где требуется оперировать связанными данными.

    -- Генерация значений
    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;

    Оконные функции (Window Functions)

    С версии 3.25.0 в SQLite доступны оконные функции, также иногда называемые аналитическими, позволяющие проводить вычисления над частью данных (окном).

    -- Номер строки в результате
    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;
    

    Утилиты SQLite

    Помимо CLI sqlite3 доступны еще две утилиты. Первая — sqldiff, позволяет сравнивать базы (или отдельную таблицу) не только по структуре, но и по данным. Вторая — sqlite3_analizer используется для вывода информации о том, как эффективно используется место таблицами и индексами в файле базы данных. Аналогичную информацию можно получить из виртуальной таблицы dbstat (требует флаг SQLITE_ENABLE_DBSTAT_VTAB при компиляции SQLite).

    С версии 3.22.0 CLI sqlite3 содержит (экспериментальную) команду .expert, которая может подсказать какой индекс стоит добавить для вводимого запроса.

    Создание резервной копии Vacuum Into

    С версии 3.27.0 команда vacuum расширена ключевым словом into, позволяющим создать копию базы без её остановки прямо из SQL. Является простой альтернативой Backup API.

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

    Функция printf

    Функция является аналогом С-функции. При этом NULL-значения интерпретируются как пустая строка для %s и 0 для плейсхолдера числа.

    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
    

    Время и дата

    В SQLite нет типов Date и Time. Хотя и можно создать таблицу с колонками таких типов, это будет аналогично созданию колонок без указания типа, поэтому данные в таких колонках хранятся как текст. Это удобно при просмотре данных, однако имеет ряд недостатков: неэффективный поиск, если нет индекса, данные занимают много места, отсутсвует временная зона. Для избежания этого можно хранить данные как unix-время, т.е. число секунд, прошедших с полуночи 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

    С версии 3.9.0 в SQLite можно работать с json (требуется либо флаг SQLITE_ENABLE_JSON1 при компиляции или загруженное расширение). Данные json хранятся как текст. Результат функций — также текст.

    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] (строка)
    А используя вычисляемые колонки и индексы по ним, эти запросы можно ускорить.

    Полнотекстовый поиск

    Как и json, полнотекстовый поиск требует задания флага SQLITE_ENABLE_FTS5 при компиляции или загрузки расширения. Для работы с поиском, сперва создается виртуальная таблица с индексируемыми полями, а и потом туда загружаются данные, используя обычный insert. Следует иметь в виду, что для своей работы расширение создает дополнительные таблицы и созданная виртуальная таблица использует их данные.

    create virtual table emails using fts5(sender, body);
    SELECT * FROM emails WHERE emails = 'fts5'; -- sender или body содержит fts5
    

    Расширения

    Возможности SQLite могут быть добавлены через загружаемые модули. Некоторые из них уже были упомянуты выше — json1 и fts.

    Расширения могут использоваться как для добавления пользовательских функций (не только скалярных, как, например, crc32, но и агрегирующих или даже оконных), так и виртуальных таблиц. Виртуальные таблицы — это таблицы, которые присутствуют в базе, но их данные обрабатываются расширением, при этом, в зависимости от реализации, некоторые из них требуют создания

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

    Другие же, так называемые table-valued, могут использоваться сразу

    select value from generate_series(5, 100, 5);
    .
    Часть виртуальных таблиц перечислена здесь.

    Одно расширение может реализовать как функции, так и виртуальные таблицы. Например, json1 содержит 13 скалярных и 2 агрегирующие функции и две виртуальные таблицы json_each и json_tree. Чтобы написать свою функцию достаточно иметь базовые знания С и разобрать код расширений из репозитария SQLite. Реализация своих виртуальных таблиц несколько сложнее (видимо поэтому их мало). Тут можно рекомендовать не сильно устаревшую книгу Using SQLite by Jay A. Kreibich, статью Michael Owens, шаблон из репозитария и код generate_series, как table-valued функции.

    Помимо этого, расширения могут реализовать специфичные для операционной системы вещи, такие как файловая система, обеспечивающие портируемость. Подробности можно узнать здесь.

    Разное


    • Используйте ' (одинарная кавычка) для строковых констант и " (двойная кавычка) для имен столбцов и таблиц.
    • Чтобы получить информацию по таблице tab1 можно использовать

      -- В main схеме
      select * from pragma_table_info('tab1');
      -- В temp схеме или подключенной (attach) базе
      select * from pragma_table_info('tab1') where schema = 'temp'
    • У SQLite есть свой официальный форум, где участвует и создатель SQLite — Richard Hipp, и где можно оставить сообщение о баге.
    • Редакторы SQLite: SQLite Studio, DB Browser for SQLite и (реклама!) sqlite-gui (только Windows).
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 31

      +1

      Отличная статья, узнал несколько новых для себя возможностей!

        +1
        Спасибо за обзор, эта база получает всё больше и больше возможностей.
        Не знаете, есть ли в планах другие методы join, кроме nested loops? (п. 7.1 в www.sqlite.org/optoverview.html)
          +1
          главное, чтобы из SQLite не превратилась в «SQLarge».
            0
            есть ли в планах другие методы join, кроме nested loops

            Лучше это на форуме SQLite спросить :) Конечно странно, что hash-join не подвезли до сих пор.
          –4
          и (реклама!) sqlite-gui (только Windows).

          Не, ну так написать — надо еще постараться.
          VirusTotal GUI

          VirusTotal WF

          А еще и раздавать такое счастье… мне лень виртуалку стартовать, так что извините. Да и пайплайн для компиляции не настроен, чтобы свои бинарники получить.
            +9
            Обычное дело для не раздутых бинарников. Сам совсем недавно оказался в похожей ситуации, когда приходилось добавлять неиспользуемый код для одной компактной утилитки, просто чтобы успокоить антивирусы, и разблокировать свой сайт в Google Safe Browsing.

            Было 18 детектов на ровном месте, при том что утилитка никак не использует функции работы с сетью или файлами. Всё что она делала — выводила сгенерированный звук, и использовала только нужные для этого API. Такое изменение помогло — по факту оно добавляет в бинарник несколько килобайт кода стандартного рантайма, который не нужен в этой программе и не влияет на её логику, но выполняется при запуске. Это успокоило почти все антивирусы.

            Увы, но качество работы антивирусов оставляет желать лучшего. Эвристики очень часто определяют компактные программы как вредоносные. При этом, если задаться целью — нет ничего сложного в том, чтобы написать вредоносный код, чтобы антивирусы ничего не детектили. Обычно для этого достаточно просто выбирать библиотеки потолще и не запариваться с оптимизацией кода по размеру. На кучу стандартного библиотечного кода антивирусы смотрят с меньшим подозрением.
              +1
              просто чтобы успокоить антивирусы,

              Менять код, чтобы успокоить антивирусы, это по сути переговоры с террористами. Это иногда оправдано. Чтобы у снайперов было время занять удобные позиции.

                +2
                Согласен, но проигнорировать было сложно, так как все популярные браузера стали блокировать закачки с моего сайта, и соответственно пользователи жаловались на это. Я несколько раз подавал апелляции в Google, присылал ссылку на исходники и объяснял что эта программа делает, но там походу никто то что я писал не читал, мои апелляции просто отклоняли, а сайт попадал во всё большее количество чёрных списков, которые импортируют себе данные из Google Safe Browsing. Пришлось таки немного изменить код. Ещё был вариант поставить пароль на архив с программой, но мне такой вариант нравится ещё меньше, так как выглядит ещё более подозрительно для обычного пользователя.
                  +1

                  Это вы мне говорите? Я разрабатываю софт на ассемблере (https://fresh.flatassembler.net) – самая любимая жертва всех антивирусов.


                  Если процитировать Вечеровского (АБС):


                  — За мою работу они меня лупят уже вторую неделю. Вы здесь совсем ни при чем, бедные мои братишки, котики-песики.

                  :D

                    0
                    Я в курсе, что вы разрабатываете Fresh IDE и AsmBB на FASM =) Сам для некоторых проектов пользуюсь FASM (например, патч для Need For Speed III).

                    По идее использование цифровой подписи должно снизить градус подозрительности для антивирусов для подобных программ. Но, к сожалению, только цифровая подпись для организаций даёт максимальный уровень доверия. Цифровая подпись, доступная для частных лиц, мало того что стоит неоправданно дорого для хобби-проектов, так ещё и лишь частично снимает подозрения, то есть никаких гарантий, что антивирусы отвяжутся.
                      0

                      Сам факт, что программа является свободной и исходники доступны, является доказательством что программа не вирус и не вредонос.


                      К тому же, и я и вы говорим о инструментах предназначенных для программистов. Если программист не сумеет сам оценит опасность использования того или иного инструмента, то он просто не дорос до нужного уровня.

                        +3
                        Сам факт, что программа является свободной и исходники доступны, является доказательством что программа не вирус и не вредонос.

                        Это чего вдруг-то?

              +2
              Не, ну так написать — надо еще постараться.

              Быстро меня раскусили! Недооценил я Хабр :(

              Увы, я в курсе этой проблемы. Надо попробовать воспользоваться советом VEG (спасибо!) или собрать Студией. Пайплайн там разворачивать особо не требуется — я Code::Blocks 17 поэтому и выбрал, что он занимает всего ~200мб.
                0
                Вероятно, в вашем случае антивирусы тоже реагируют на нестандартную точку входа. По-видимому, когда антивирусы видят там привычную точку входа msvcrt, это делает их немного спокойнее. Ставить всю Visual Studio не обязательно — можно поставить только Build Tools. Как вариант, можно ещё собирать софтину при помощи Clang — он умеет использовать стандартный Windows SDK и хорошо совместим с MSVC, даже нестандартные расширения всякие поддерживает.
                  0
                  Виндовая затычка для антивируса еще при скачивании ругнулась, поэтому полез дальше копать.
                0

                Недавняя видео-конференция от создателя (DRH) про sqlite, c 11:09 — The S&T 2020 Conference — D. Richard Hipp, презентация — SQLite Status Report 2020 — Slide 0

                  0
                  С collation-ами всё также плохо?
                    +1

                    Стоит знать про автоматически создаваемую колонку rowid c уникальным индексом для записей в таблице. А то видел я как для уникального индекса автор вручную добавлял столбец с UUID, раздувая базу кажется втрое от достаточного размера.


                    С rowid прадва есть немного нюансов, например в настройках по-умолчанию он может быть переиспользован, если удалить строку и потом вставить другую.

                      +3

                      Вообще-то стоит, но все таки rowid не принципиально. Потому что, если в таблице есть "integer primary key", то это является по сути то же самое rowid, только переименованным.


                      И наоборот, если нет "integer primary key" то и знать что есть такая скрытая колона не обязательно. И даже вредно.


                      Потому что rowid, когда в таблице нет "integer primary key", меняется:


                      If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
                        0
                        Эта тема уже была озвучена Krovosos в статье SQLite — замечательная встраиваемая БД (часть 1). Поскольку, за небольшим исключениями, статьи не потеряли своей актуальности, то повторять про rowid я смысла не увидел.
                        +2
                        Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).

                        Иногда, этот флаг (как и некоторые другие) установлен по умолчанию. Вот, что по дефолту включено в SQLite 3.33.0 под Windows:

                        COMPILER=gcc-5.2.0
                        ENABLE_COLUMN_METADATA
                        ENABLE_FTS3
                        ENABLE_FTS5
                        ENABLE_JSON1
                        ENABLE_RTREE
                        THREADSAFE=1
                        

                        Команда для вывода опций компиляции:
                        WITH opts(n, opt) AS (
                          VALUES(0, NULL)
                          UNION ALL
                          SELECT n + 1,
                                 sqlite_compileoption_get(n)
                          FROM opts
                          WHERE sqlite_compileoption_get(n) IS NOT NULL
                        )
                        SELECT opt
                        FROM opts;
                        
                          0
                          Это sqlite3 для CLI собран с этими флагами, что разумно, т.к. это достаточно ходовые расширения и заставлять пользователей их устанавливать отдельно несколько странно, при том загрузка расширений по умолчанию выключена в целях безопасности (защита от дурака). Кстати, с 3.34.0 будет еще включать generate_series.

                          По умолчанию, если собирать из исходников, только THREADSAFE=1 есть.
                            0
                            Нет, речь именно про sqlite3.dll для win32 x86 (возможно, это же справедливо и для x64, не проверял), которая распространяется самими разработчиками: i.imgur.com/TB4Fr6L.png

                            А CLI собран с другими флагами:
                            # sqlite3.exe
                            SQLite version 3.33.0 2020-08-14 13:23:32
                            Enter ".help" for usage hints.
                            Connected to a transient in-memory database.
                            Use ".open FILENAME" to reopen on a persistent database.
                            sqlite> WITH opts(n, opt) AS (VALUES(0, NULL) UNION ALL SELECT n + 1, sqlite_compileoption_get(n) FROM opts WHERE sqlite_compileoption_get(n) IS NOT NULL) SELECT opt FROM opts;
                            
                            COMPILER=gcc-5.2.0
                            ENABLE_DBSTAT_VTAB
                            ENABLE_FTS3
                            ENABLE_FTS5
                            ENABLE_JSON1
                            ENABLE_RTREE
                            ENABLE_STMTVTAB
                            ENABLE_UNKNOWN_SQL_FUNCTION
                            THREADSAFE=0
                            

                            По умолчанию, если собирать из исходников, только THREADSAFE=1 есть.

                            Да, но это только если собирать самому из исходников, а не взять готовую dll.
                          0

                          Хорошая статья, добрая.


                          Конечно, надо помнить о том, что если используете относительно свежие фичи SQLite в своем продукте — необходимо будет убедиться, что в рабочем окружении продукта будет соответственно достаточно свежая библиотека SQLite. Ваш Кэп.


                          Круто, что кастомная лизензия позволяет статично и безнаказанно вкомпиливать в свой бинарь амальгаму из пары .c + .h исходников.

                            0
                            кастомная лизензия

                            Какая такая "кастомная"? Лицензия SQLite – "public domain" – делайте что хотите.

                              0
                              pacman -Qi sqlite | grep License
                              Licenses        : custom:Public Domain

                              Почему-то public domain считается кастомной. Наверное из-за редкости использования и сложности по compliance. Насколько я изучал тему, авторы SQLite очень заморочились на эту тему, чтобы даже в самых долбанутых юрисдикциях их код считался максимально свободным. Как минус, вы не можете просто отправить патч/pull request: его даже читать не будут, чтоб не понахватать оттуда чужих идей непонятного происхождения.

                                0

                                Нет, дело там, насколько я знаю в авторских прав.


                                И это проблема не только public domain, но и всех "permissive license" схемах: BSD, MIT и всякие такие.


                                Потому что, разрешение на использованиe они вам дают, но каждый, чей код используется в проекте, сохраняет за собой авторские права на своей части кода. Из за этого могут последовать всякие нехорошие юридические последствия, по крайней мере в США. Ну и управление проекта становится сложным и не гибким.


                                Поэтому, чтобы стать разработчиком SQLite (ну или в других проектах DRH), надо подписать отказ от авторских прав в пользу DRH, ну или его компании, не суть. Но вполне реально, если очень хочется.


                                Было дело, дискутировали с DRH эту тему, но я отказался подписывать – не люблю такие юридические пляски. Поэтому и использую EUPL (копи-лефт) лицензию.

                                  0
                                  Не понимаю проблему

                                  Если я для себя модифицировал код, зачем я должен отдавать права на этот код кому то левому.

                                  Да конечно, если я пожелаю отдать этот код в комунити чтобы оно его поддерживало, то можно и отдать права.

                                  И конечно, всегда остаются форки и игры с названием.
                                    0

                                    Так нет по сути никаких проблем. Просто особенности взаимодействия пермисив лицензии и законы авторского права.


                                    Иногда приходится делать дополнительные движения, что несколько хлопотно.

                                    0

                                    Пермиссивные лицензии не требуют copyright assignment.

                            Only users with full accounts can post comments. Log in, please.