SQLite: случайные значения первичного ключа

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

Мною были предприняты две попытки, которые дали отрицательный результат.

Первая неудачная попытка

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

create table table1 (
  id integer primary key default (random()),
  field1 integer
);

Таблица создается без ошибок, но при вставке записей значение первичного ключа задается как обычно, то есть условие default игнорируется.

Вторая неудачная попытка

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

create table table1 (
  id integer primary key,
  field1 integer
);

create trigger random_primary_key
instead of insert on table1
when new.id is not null
begin
  insert into table1 values (
    random(),
    new.field1
  );
end;

Но SQLite выдал ошибку:

Error: near line 30: cannot create INSTEAD OF trigger on table: table1

Оказалось, что триггеры, которые заменяют записи, могут работать только с представлениями (view). Что удалось узнать только из исходного кода SQLite. Так как в документации это не разъясняется. В файле src/trigger.c в том месте где генерируется выше названная ошибка сказано:

INSTEAD of triggers are only for views and views only support INSTEAD of triggers.

Перевод: триггер INSTEAD только для представлений и только представления поддерживают этот триггер.

Удачная попытка

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

create table table1 (
  id integer unique not null default (random()),
  field1 integer
);

У меня были сомнения, будут ли работать внешние ключи на уникальном поле. Эксперимент дал положительный результат.

Реклама

Извлечение древовидной структуры из базы данных

Для всех запросов в статье используется синтаксис СУБД SQLite. Для других СУБД синтаксис может незначительно отличаться.

Чтобы представить древовидную структуру в реляционной базе данных, можно создать следующую таблицу:

create table tree (
  id     integer,
  parent integer,
  name   text,

  primary key (id),
  foreign key (parent) references tree(id)
    on update cascade on delete cascade
);

В поле parent задается родитель для элемента древовидной структуры, причем в качестве значения этого поля используется значение поля id другого элемента. В поле name задается имя элемента.

Для наглядности зададимся древовидной структурой:

• Овощи
  • Картофель
    • Аспиа
    • Виталот
    • Диво
  • Томаты
    • Томат обыкновенный
    • Томат Перуанский
• Фрукты
  • Груши
    • Груша обыкновенная
  • Яблоки

Такую структуру можно создать следующими запросами:

insert into tree values ( 1, null, 'Овощи');
insert into tree values ( 2,    1, 'Картофель');
insert into tree values ( 3,    2, 'Аспиа');
insert into tree values ( 4,    2, 'Виталот');
insert into tree values ( 5,    2, 'Диво');
insert into tree values ( 6,    1, 'Томаты');
insert into tree values ( 7,    6, 'Томат обыкновенный');
insert into tree values ( 8,    6, 'Томат перуанский');
insert into tree values ( 9, null, 'Фрукты');
insert into tree values (10,    9, 'Груши');
insert into tree values (11,   10, 'Груша обыкновенная');
insert into tree values (12,    9, 'Яблоко');

Здесь null означает корневую группу.

Предположим, что требуется извлечь данные из таблицы tree в следующем виде:

/Овощи
/Фрукты
/Овощи/Картофель
/Овощи/Томаты
/Фрукты/Груши
/Фрукты/Яблоко
/Овощи/Картофель/Аспиа
/Овощи/Картофель/Виталот
/Овощи/Картофель/Диво
/Овощи/Томаты/Томат обыкновенный
/Овощи/Томаты/Томат перуанский
/Фрукты/Груши/Груша обыкновенная

Для этого понадобится рекурсивный запрос:

with cte as (
  select
    id,
    parent,
    '/' || name as name
  from tree where parent is null
  
  union all
  
  select
    tree.id,
    tree.parent,
    cte.name || '/' || tree.name
  from cte, tree on tree.parent = cte.id
)
select name from cte;

Рекурсивные запросы поддерживаются в SQLite начиная с версии 3.8.3 от 3 февраля 2014 года.

SQL-запросы в коде C/C++

SQL-запросы можно хранить в строковых переменных, например:

const char * query = "SELECT * FROM table1;";

Но если запрос достаточно большой, то его придется разбить на несколько строк, например:

const char * query =
    "SELECT column1, column2, column3"
    " FROM table1 INNER JOIN table2"
    " ON table1.column1 = table2.column2;";

Но такой запрос не скопируешь в какой-нибудь клиент СУБД, чтобы проверить как он исполняется. Мешают кавычки, без которых компилятор ругается.

Есть способ записи запросов, который бы удовлетворил обоим языкам и SQL и C/C++:

const char * query = 
    "SELECT column1, column2, column3 --\n\
     FROM table1 INNER JOIN table2 --\n\
     ON table1.column1 = table2.column2;";

Наклонная черта в конце строк позволяет на C/C++ переносить текст на другую строку, не используя кавычки. А два дефиса и перевод строки (\n) перед наклонной чертой сделают так, чтобы эта черта считалась комментарием на SQL.

Краткое описание строковых функций Transact-SQL

В таблице перечислены 23 строковые функции Transact-SQL которые поддерживает SQL Server 2005.

Функция	Описание
ASCII	Возвращает код ASCII первого символа указанного символьного выражения.
CHAR	Преобразует код int ASCII в символ.
NCHAR	Возвращает символ Юникода с указанным целочисленным кодом, определенным в стандарте Юникода.
SOUNDEX	Возвращает четырехсимвольный код (SOUNDEX) для оценки степени сходства двух строк.
PATINDEX	Для любого допустимого символьного или текстового типа данных возвращает позицию первого вхождения шаблона в указанном выражении или нуль, если шаблон не найден. Дополнительные сведения см. в разделе Совпадение по шаблону в условиях поиска.
SPACE	Возвращает строку пробелов.
CHARINDEX	Возвращает начальную позицию указанного выражения в символьной строке.
QUOTENAME	Возвращает строку Юникода с добавлением разделителей, образуя из строки ввода правильный идентификатор с разделителем Microsoft SQL Server 2005.
STR	Возвращает символьные данные, преобразованные из числовых данных.
DIFFERENCE	Возвращает целочисленную разницу между значениями SOUNDEX двух символьных выражений.
REPLACE	Заменяет все вхождения указанного строкового значения другим строковым значением.
STUFF	Замещает указанное число символов, начиная с определенной позиции, другим заданным набором символов.
LEFT	Возвращает указанное число символов символьного выражения слева.
REPLICATE	Повторяет значение строки указанное число раз.
SUBSTRING	Возвращает фрагмент символьного, двоичного, текстового или графического выражения. Дополнительные сведения о допустимых типах данных SQL Server 2005, которые могут быть использованы в этой функции, см. в разделе Типы данных (Transact-SQL).
LEN	Возвращает количество символов указанного строкового выражения, исключая конечные пробелы. Получить число байт, используемых для представления выражения, можно с помощью функции DATALENGTH.
REVERSE	Возвращает символьное выражение, где символы переставлены в обратном порядке (справа налево).
UNICODE	Возвращает целочисленное значение, соответствующее стандарту Юникод, для первого символа входного выражения.
LOWER	Возвращает символьное выражение после преобразования символов верхнего регистра в символы нижнего регистра.
RIGHT	Возвращает указанное число символов символьной строки справа.
UPPER	Возвращает символьное выражение, в котором символы нижнего регистра преобразованы в символы верхнего регистра.
LTRIM	Возвращает символьное выражение после удаления начальных пробелов.
RTRIM	Возвращает строковое выражение, удаляя все завершающие пробелы.