При разработке инструментов, которые взаимодействуют с разными СУБД, приходится учитывать много нюансов, потому что одни и теже вещи реализованы по-разному и стандарт SQL поддерживается по-разному. Если рассмотреть достаточное количество СУБД, то окажется, что нет универсального способа удалить функцию или процедуру.
Читать далее Как удалить процедуру или функцию из базы данныхВ 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-запросы можно хранить в строковых переменных, например:
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.
В таблице перечислены 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 | Возвращает строковое выражение, удаляя все завершающие пробелы. |
Дмитрий Бравиков 