Значения строк

1.Определения

«Значение» — это одно число, строка, BLOB или NULL. Иногда используется квалифицированное название «скалярное значение», чтобы подчеркнуть, что участвует только одна величина.

«Значение строки» — это упорядоченный список из двух или более скалярных значений. Другими словами, «значение строки» — это вектор или кортеж.

«Размер» значения строки — это количество скалярных значений, которые содержит значение строки. Размер значения строки всегда не меньше 2. Значение строки с одним столбцом — это просто скалярное значение. Значение строки без столбцов — это синтаксическая ошибка.

2.Синтаксис

SQLite позволяет выразить значения строк двумя способами:

1. В скобках, через запятые, список скалярных значений.
2. Выражение подзапроса с двумя или более результативными столбцами.

SQLite может использовать значения строк в двух контекстах:

1. Два значения строки одинакового размера можно сравнивать с помощью операторов <, <=, >, >=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN или CASE.
2. В операторе UPDATE список имён столбцов можно установить в значение строки того же размера.

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

2.1.Сравнения значений строк

Два значения строк сравниваются, рассматривая составляющие скалярные значения слева направо. NULL означает «неизвестно». Общий результат сравнения — NULL, если возможно получить результат либо истинным, либо ложным, заменив альтернативными значениями составляющие NULL. Следующий запрос демонстрирует некоторые сравнения значений строк:

SELECT
  (1,2,3) = (1,2,3),          -- 1
  (1,2,3) = (1,NULL,3),       -- NULL
  (1,2,3) = (1,NULL,4),       -- 0
  (1,2,3) < (2,3,4),          -- 1
  (1,2,3) < (1,2,4),          -- 1
  (1,2,3) < (1,3,NULL),       -- 1
  (1,2,3) < (1,2,NULL),       -- NULL
  (1,3,5) < (1,2,NULL),       -- 0
  (1,2,NULL) IS (1,2,NULL);   -- 1

Результат «(1,2,3)=(1,NULL,3)» — NULL, потому что результат может быть истинным, если мы заменим NULL на 2, или ложным, если мы заменим NULL на 9. Результат «(1,2,3)=(1,NULL,4)» — не NULL, потому что нет замены составляющих NULL, которые сделают выражение истинным, так как 3 никогда не будет равно 4 в третьем столбце.

Любое из значений строк в предыдущем примере можно заменить подзапросом, который возвращает три столбца, и результат будет таким же. Например:

CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c) VALUES(1,2,3);
SELECT (1,2,3)=(SELECT * FROM t1); -- 1

2.2.Операторы IN со значениями строк

Для оператора IN со значением строки левая часть (далее «ЛЧ») может быть либо списком значений в скобках, либо подзапросом с несколькими столбцами. Но правая часть (далее «ПЧ») должна быть выражением подзапроса.

CREATE TABLE t2(x,y,z);
INSERT INTO t2(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
SELECT
   (1,2,3) IN (SELECT * FROM t2),  -- 1
   (7,8,9) IN (SELECT * FROM t2),  -- 0
   (1,3,5) IN (SELECT * FROM t2);  -- NULL

2.3.Значения строк в операторах UPDATE

Значения строк также могут использоваться в предложении SET оператора UPDATE. ЛЧ должна быть списком имён столбцов. ПЧ может быть любым значением строки. Например:

UPDATE tab3 
   SET (a,b,c) = (SELECT x,y,z
                    FROM tab4
                   WHERE tab4.w=tab3.d)
 WHERE tab3.e BETWEEN 55 AND 66;

3.Примеры использования значений строк

3.1.Запросы со скроллирующим окном

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

SELECT * FROM contacts
 ORDER BY lastname, firstname
 LIMIT 7;

Когда пользователь прокручивает вниз, приложению нужно найти вторую группу из 7 записей. Один из способов сделать это — использовать предложение OFFSET:

SELECT * FROM contacts
 ORDER BY lastname, firstname
 LIMIT 7 OFFSET 7;

OFFSET даёт правильный ответ. Однако OFFSET требует времени, пропорционального значению смещения. То, что происходит на самом деле с «LIMIT x OFFSET y», заключается в том, что SQLite вычисляет запрос как «LIMIT x+y» и отбрасывает первые y значений, не возвращая их приложению. Таким образом, по мере того как окно прокручивается вниз к низу длинного списка, и значение y становится всё больше и больше, последовательные вычисления смещения занимают всё больше и больше времени.

Более эффективный подход заключается в запоминании последней отображаемой записи, а затем использовании сравнения значений строки в предложении WHERE:

SELECT * FROM contacts
 WHERE (lastname,firstname) > (?1,?2)
 ORDER BY lastname, firstname
 LIMIT 7;

Если фамилия и имя в нижней строке предыдущего экрана привязаны к ?1 и ?2, то вышеприведенный запрос вычисляет следующие 7 строк. И, предполагая наличие соответствующего индекса, он делает это очень эффективно — намного эффективнее, чем OFFSET.

3.2.Сравнение дат, хранящихся в отдельных полях

Обычный способ хранения даты в таблице базы данных — как одно поле, как временная метка Unix, число дня Юлиана или строка даты ISO-8601. Но некоторые приложения хранят даты как три отдельных поля для года, месяца и дня.

CREATE TABLE info(
  year INT,          -- 4 digit year
  month INT,         -- 1 through 12
  day INT,           -- 1 through 31
  other_stuff BLOB   -- blah blah blah
);

Когда даты хранятся таким образом, сравнения значений строк предоставляют удобный способ сравнения дат:

SELECT * FROM info
 WHERE (year,month,day) BETWEEN (2015,9,12) AND (2016,9,12);

3.3.Поиск по многоколоночным ключам

Предположим, что мы хотим узнать номер заказа, номер товара и количество для любого товара, в котором номер товара и количество совпадают с номером товара и количеством любого товара в заказе № 365:

SELECT ordid, prodid, qty
  FROM item
 WHERE (prodid, qty) IN (SELECT prodid, qty
                           FROM item
                          WHERE ordid = 365);

Вышеприведенный запрос можно переписать как объединение без использования значений строк:

SELECT t1.ordid, t1.prodid, t1.qty
  FROM item AS t1, item AS t2
 WHERE t1.prodid=t2.prodid
   AND t1.qty=t2.qty
   AND t2.ordid=365;

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

Даже в форме объединения запрос можно сделать более понятным с помощью значений строк:

SELECT t1.ordid, t1.prodid, t1.qty
  FROM item AS t1, item AS t2
 WHERE (t1.prodid,t1.qty) = (t2.prodid,t2.qty)
   AND t2.ordid=365;

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

3.4.Обновление нескольких столбцов таблицы на основе запроса

Обозначение значений строк полезно для обновления двух или более столбцов таблицы из результата одного запроса. Примером этого является функция полнотекстового поиска в системе управления версиями Fossil.

В системе полнотекстового поиска Fossil документы, участвующие в полнотекстовом поиске (страницы wiki, билеты, коммиты, файлы документации и т. д.) отслеживаются таблицей «ftsdocs» (full text search documents). Когда новые документы добавляются в репозиторий, они не индексируются сразу. Индексирование откладывается до запроса поиска. Таблица ftsdocs содержит поле «idxed», которое равно true, если документ был проиндексирован, и false, если нет.

Когда возникает запрос поиска и ожидаемые документы индексируются впервые, таблица ftsdocs должна быть обновлена, установив столбец idxed в значение true и также заполнив несколько других столбцов информацией, относящейся к поиску. Эта другая информация получается из объединения. Запрос выглядит следующим образом:

UPDATE ftsdocs SET
  idxed=1,
  name=NULL,
  (label,url,mtime) = 
      (SELECT printf('Check-in [%%.16s] on %%s',blob.uuid,
                     datetime(event.mtime)),
              printf('/timeline?y=ci&c=%%.20s',blob.uuid),
              event.mtime
         FROM event, blob
        WHERE event.objid=ftsdocs.rid
          AND blob.rid=ftsdocs.rid)
WHERE ftsdocs.type='c' AND NOT ftsdocs.idxed

(См. исходный код для получения дополнительной информации. Другие примеры здесь и здесь.)

Пять из девяти столбцов таблицы ftsdocs обновляются. Два из изменённых столбцов, «idxed» и «name», можно обновлять независимо от запроса. Но три столбца «label», «url» и «mtime» требуют объединения запроса с таблицами «event» и «blob». Без значений строк эквивалентный UPDATE потребовал бы повторения объединения трижды, один раз для каждого столбца, который нужно обновить.

3.5.Понятность представления

Иногда использование значений строк просто упрощает чтение и запись SQL. Рассмотрим два оператора UPDATE:

UPDATE tab1 SET (a,b)=(b,a);
UPDATE tab1 SET a=b, b=a;

Оба оператора UPDATE делают ровно то же самое. (Они генерируют идентичный байт-код.) Но первая форма, форма значения строки, кажется более понятной, что намерение оператора — поменять значения в столбцах A и B.

Или рассмотрите эти идентичные запросы:

SELECT * FROM tab1 WHERE a=?1 AND b=?2;
SELECT * FROM tab1 WHERE (a,b)=(?1,?2);

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

4.Обратная совместимость

Значения строк были добавлены в SQLite версии 3.15.0 (2016-10-14). Попытки использовать значения строк в предыдущих версиях SQLite приведут к синтаксическим ошибкам.