MetaTrader 5 / Тестер
Español Português
preview
Моделирование рынка (Часть 24): Первые шаги на SQL (VII)

Моделирование рынка (Часть 24): Первые шаги на SQL (VII)

MetaTrader 5 — Тестер |
41 0
Daniel Jose
Daniel Jose

Введение

В предыдущей статье, Моделирование рынка (Часть 23): Первые шаги в SQL (VI) объяснялось несколько важных моментов, которые необходимо понимать об SQL. Это для того, чтобы мы могли минимально работать с базами данных. Итак, поскольку нам ещё многое предстоит обсудить, и тема будет довольно сложной, мы не будем тратить время на вступление. Давайте сразу перейдем к главному.


Понимание проблемы

Базы данных — это не что иное, как набор записей в файле. Но чтобы понять, что мы пытаемся объяснить, давайте воспользуемся следующим кодом:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL
17. );
18. 
19. INSERT INTO tb_Symbols (id, symbol) VALUES
20.     (2, 'PETR4'),
21.     (1, 'ITUB3'),
22.     (3, 'VALE3');
23. 
24. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
25.     ('2023-07-10', '22.00', 1),
26.     ('2023-07-11', '22.20', 1),
27.     ('2023-07-12', '22.40', 1),
28.     ('2023-07-13', '22.30', 1),
29.     ('2023-07-14', '22.60', 1),
30.     ('2023-07-10', '26.00', 2),
31.     ('2023-07-11', '26.20', 2),
32.     ('2023-07-12', '26.40', 2),
33.     ('2023-07-13', '26.30', 2),
34.     ('2023-07-14', '26.60', 2),
35.     ('2023-07-10', '62.00', 3),
36.     ('2023-07-11', '62.20', 3),
37.     ('2023-07-12', '62.40', 3),
38.     ('2023-07-13', '62.30', 3),
39.     ('2023-07-14', '62.60', 3);
40. 
41. SELECT tq.of_day AS 'Data da cotação',
42.        tq.price AS 'Preço Atual',
43.        ts.symbol AS 'Nome do Ativo'
44.     FROM tb_Quotes AS tq, tb_Symbols AS ts
45.     WHERE tq.fk_id = ts.id
46.     ORDER BY price DESC;

Код 01

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

Изображение 01

Хорошо, на этом изображении показано то, что и ожидалось увидеть, так как мы создали три символа и добавили по пять котировок в каждый, что в сумме дало 15 записей. Но теперь перейдем к основной проблеме. Это происходит, в первую очередь, при попытке удалить что-либо из базы данных. "Но почему у нас начнутся проблемы при удалении записей из базы? Это не имеет особого смысла". По сути, смысла нет. Однако при использовании связанных баз данных или таблиц возникает проблема, которой нет при использовании другой схемы построения базы данных. Одну из этих проблем можно выявить, запустив программу, показанную ниже:

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. 
3. SELECT tq.of_day AS 'Data da cotação',
4.        tq.price AS 'Preço Atual',
5.        ts.symbol AS 'Nome do Ativo'
6.     FROM tb_Quotes AS tq, tb_Symbols AS ts
7.     WHERE tq.fk_id = ts.id
8.     ORDER BY price DESC;

Код 02

Можно подумать: "Хорошо, но что в этом плохого? Моя цель состояла именно в том, чтобы удалить все записи PETR4". На самом деле, уважаемые читатели, при выполнении первой строки SQL удалит записи PETR4. Всё именно так, поэтому при выполнении команды SELECT в строке 3 мы получаем такой результат:

Изображение 02

Данное изображение доказывает, что команда в строке 01 сработала. Но вы действительно знаете, что сделала команда в строке 01? И более того: вы действительно знаете, что происходило внутри базы данных? Многие бы сказали: "Конечно, я знаю, что произошло". Итак, уважаемые читатели, я хочу, чтобы вы ответили на следующий вопрос: создает ли код 01 реляционную базу данных или нет? Если да, то где устанавливается данное отношение? Если ответ отрицательный, как мы можем гарантировать это отношение? Я понимаю, что многие думают, будто умеют работать с базами данных только потому, что знают несколько SQL-команд. Но всё не так просто, как кажется. И это не так сложно, как многие представляют. В любом случае, я хочу, чтобы вы поняли одно:

Код 01 НЕ СОЗДАЕТ РЕЛЯЦИОННУЮ БАЗУ ДАННЫХ.

"Конечно, создает. Я вижу это, когда объявляю внешний ключ внутри таблицы и использую его для ссылки на данные из другой таблицы. Что вы имеете в виду, говоря, что база данных не реляционная? Вы что, с ума сошли?" Нет, я покажу вам, что база данных не является реляционной. Посмотрите на следующее изображение.

Изображение 03

Хотя мы не видим всех 30 возможных комбинаций, можно заметить, что у нас нет ссылки на символ PETR4. Однако значение fk_id, равное 2, присутствует, но это значение не включено в идентификатор символа. Это делает базу данных более подверженной сбоям в согласованности данных с течением времени, и причина этого будет объяснена чуть позже. А теперь давайте разберемся, что именно команда DELETE делает с базой данных. Поскольку база данных не является реляционной, обе таблицы независимы друг от друга и между данными сохраняется только простая ссылка.

Данная схема имеет свои преимущества, хотя и не гарантирует, что мы действительно создадим реляционную базу данных. А теперь мы разберемся в истинной причине. Как известно, id 2 свободен. Чтобы понять это, нам достаточно взглянуть на таблицу tb_Symbols, как показано ниже.

Изображение 04

Зная эту информацию, можно поместить в этот свободный id другой символ, например, BBDC4. Это делается следующим образом:

Изображение 05

Вопрос: Есть ли в этом что-нибудь неправильное? В принципе, нет, поскольку id 2 был, по сути, свободным. Но вот тут-то и возникает проблема. При добавлении данных в таблицу tb_Quotes в нашей базе данных могут появиться недопустимые значения. И весьма вероятно, что мы заметим это только после того, как потратим достаточно много времени на ввод данных в базу данных. И чем дольше мы ждем, тем меньше вероятность того, что мы заметим: что-то не так. Но предположим, что мы по какой-то причине, сразу после добавления BBDC4 в таблицу tb_Symbols, решаем сделать запрос к базе данных, как показано на следующем изображении.

Изображение 06

"Подождите минутку. Почему так? Я только что добавил BBDC4 в базу данных, и есть ли в ней уже информация?" Да, такое возможно, уважаемые читатели. В этом и заключается опасность делать что-либо, считая, что мы знаем, что делаем. Делать вещи по наивности или с чрезмерной уверенностью в своих знаниях в итоге приводит к подобным проблемам, которые часто проявляются лишь спустя долгое время. Таким образом, у нас больше нет возможности проверить достоверность данных, хранящихся в базе данных. Для решения этой проблемы нам пришлось бы изменить способ построения базы данных таким образом, чтобы существовала связь между первичным и внешним ключами. Таким образом, тот же код 01 следует выполнить, как показано ниже:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. INSERT INTO tb_Symbols (id, symbol) VALUES
21.     (2, 'PETR4'),
22.     (1, 'ITUB3'),
23.     (3, 'VALE3');
24. 
25. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
26.     ('2023-07-10', '22.00', 1),
27.     ('2023-07-11', '22.20', 1),
28.     ('2023-07-12', '22.40', 1),
29.     ('2023-07-13', '22.30', 1),
30.     ('2023-07-14', '22.60', 1),
31.     ('2023-07-10', '26.00', 2),
32.     ('2023-07-11', '26.20', 2),
33.     ('2023-07-12', '26.40', 2),
34.     ('2023-07-13', '26.30', 2),
35.     ('2023-07-14', '26.60', 2),
36.     ('2023-07-10', '62.00', 3),
37.     ('2023-07-11', '62.20', 3),
38.     ('2023-07-12', '62.40', 3),
39.     ('2023-07-13', '62.30', 3),
40.     ('2023-07-14', '62.60', 3);
41.     
42. SELECT tq.of_day AS 'Data da cotação',
43.        tq.price AS 'Preço Atual',
44.        ts.symbol AS 'Nome do Ativo'
45.     FROM tb_Quotes AS tq, tb_Symbols AS ts
46.     WHERE tq.fk_id = ts.id
47.     ORDER BY price DESC;

Код 03

Прошу заметить, что единственное различие между скриптами — это строка 17. Именно здесь мы обеспечиваем фактическую взаимосвязь между таблицами. "Хм, но если решение настолько простое, почему бы не создать всё так, как показано в коде 03?" Итак, уважаемый читатель, проблема в том, что при создании элементов, подобных тем, что показаны в коде 03, мы в конечном итоге усложняем удаление записей из базы данных. Чтобы разграничить эти вопросы, перейдём к новой теме.


Удаление записей в связанных таблицах

Чтобы понять, почему значительная часть пользователей предпочитает использовать код 01 вместо кода 03 для создания баз данных, давайте попробуем сделать то же самое, что и раньше. Другими словами, мы собираемся удалить символ PETR4 и заменить его символом BBDC4. Тогда можно попробовать сделать это, используя показанный ниже пример.

Изображение 07

Прошу заметить, что на изображении 07 выделенная точка пытается удалить PETR4 из таблицы tb_Symbols. Однако SQL сообщает нам, что это будет невозможно. Это связано с тем, что база данных была создана с использованием кода 03. Многие в итоге начинают раздражаться из-за SQL и ищут решение, и самое простое из них — использовать код 01, который рано или поздно всё равно вызовет проблемы. Отлично. Как видно на изображении 07, ответ SQL на запрос — это далеко не ошибка. Это указывает на то, что между записями в таблицах tb_Symbols и tb_Quotes действительно существует взаимосвязь, которая предотвращает случайное удаление каких-либо данных.

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

Изображение 08

А чтобы не осталось сомнений, код для изображения 08 показан ниже.

01. DELETE FROM tb_Quotes 
02.     WHERE fk_id = (SELECT ts.id FROM tb_Symbols AS ts WHERE ts.symbol = 'PETR4');
03. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
04. 
05. SELECT tq.of_day AS 'Data da cotação',
06.        tq.price AS 'Preço Atual',
07.        ts.symbol AS 'Nome do Ativo'
08.     FROM tb_Quotes AS tq, tb_Symbols AS ts
09.     WHERE tq.fk_id = ts.id
10.     ORDER BY price DESC;

Код 04

С такого ракурса кажется, что мы только усложняем вещи, но на самом деле мы делаем их безопаснее. Обратите внимание, что в строке 01 из кода 04 мы указываем SQL удалить что-то из таблицы tb_Quotes. В строке 02 мы укажем, что следует удалить. Прошу заметить, это следует интерпретировать следующим образом: SQL должен перейти к таблице tbQuotes и удалить все записи для символа PETR4, а затем перейти к таблице tb_Symbols и удалить запись символа PETR4. В таком ракурсе это кажется преувеличением, и, похоже, из этого ничего не выйдет, но посмотрите на результат, когда мы выполним запрос к таблице tb_Quotes.

Изображение 09

"Ух ты. Всё прошло так, как ожидалось?" Теперь давайте попробуем добавить BBDC4 и сразу после этого посмотрим, что находится в базе данных. Это можно увидеть на следующем изображении.

Изображение 10

Легко заметить, что в отмеченной области изображения 10 значение fk_id не совпадает с id от BBDC4. Однако анализ таких данных в большой базе данных может быть довольно запутанным. Но суть не в том, чтобы сосредоточиться на самом запросе, а в том, чтобы понять, что находится внутри базы данных. Но если мы выполним реляционный запрос, что мы и делаем на практике, результат будет таким:

Изображение 11

По всей видимости, здесь ничего не показывается, что, собственно, и было бы целью. В синей области отображается результат выполнения SQL-запроса. В ЗЕЛЕНОЙ области мы видим выполненную команду. Таким образом, SQL не обнаружил ни одного появления id от BBDC4 в старый id от PETR4. Иными словами, база данных остается целостной.

Ладно, это здорово, но теперь давайте поговорим о чем-то более интересном. И для этого мы перейдем к новой теме.


Использование триггеров

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

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

Идея триггера заключается в упрощении подобных задач, чтобы вместо нескольких команд DELETE использовалась только одна. Это кажется простым, и это действительно легко сделать, уважаемые читатели, но есть проблема. Каждая реализация SQL обрабатывает триггеры по-разному. Например, SQL-код, предназначенный для использования SQLite, может иметь иной механизм запуска, чем аналогичный код в SQL Server или даже MySQL. Хотя всё основано на SQL, способ обработки триггеров может существенно отличаться в разных реализациях. Даже две реализации SQLite могут обрабатывать триггеры по-разному, поскольку SQLite является открытым исходным кодом и, соответственно, может быть модифицирован для конкретных целей.

Один из способов реализации триггеров в SQLite показан ниже.

Изображение 12

Аналогичную схему можно увидеть по адресу lang_createtrigger. Там, помимо этой схемы, можно увидеть краткое объяснение принципа работы. Понять эту схематическую модель несложно. Кроме того, это значительно помогает понять последовательность команд, необходимых для создания триггера.

"Хорошо, но что же пытается нам указать вся эта сложная система стрелок и текста? Я почти ничего не понимаю". Успокойтесь, со временем вы привыкнете. Давайте разберемся в этом без лишнего стресса. Прошу заметить, что в верхней части изображения находится круг. Вот где начинается код. Каждая команда отмечена кругом, а стрелки указывают, какая команда должна быть следующей. Чтобы не делать это скучным и не оставлять чисто теоретическим, давайте посмотрим, как создать триггер. Помните код 03? Чтобы добавить триггер в данный код, нам нужно всего лишь создать нечто подобное тому, что показано на изображении 12. В результате получается такой код:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
13. BEGIN
14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
15. END;
16. 
17. CREATE TABLE IF NOT EXISTS tb_Quotes
18. (
19.     of_day NOT NULL,
20.     price NOT NULL,
21.     fk_id NOT NULL,
22.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
23. );
24. 
25. INSERT INTO tb_Symbols (id, symbol) VALUES
26.     (2, 'PETR4'),
27.     (1, 'ITUB3'),
28.     (3, 'VALE3');
29. 
30. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
31.     ('2023-07-10', '22.00', 1),
32.     ('2023-07-11', '22.20', 1),
33.     ('2023-07-12', '22.40', 1),
34.     ('2023-07-13', '22.30', 1),
35.     ('2023-07-14', '22.60', 1),
36.     ('2023-07-10', '26.00', 2),
37.     ('2023-07-11', '26.20', 2),
38.     ('2023-07-12', '26.40', 2),
39.     ('2023-07-13', '26.30', 2),
40.     ('2023-07-14', '26.60', 2),
41.     ('2023-07-10', '62.00', 3),
42.     ('2023-07-11', '62.20', 3),
43.     ('2023-07-12', '62.40', 3),
44.     ('2023-07-13', '62.30', 3),
45.     ('2023-07-14', '62.60', 3);
46.     
47. SELECT tq.of_day AS 'Data da cotação',
48.        tq.price AS 'Preço Atual',
49.        ts.symbol AS 'Nome do Ativo'
50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
51.     WHERE tq.fk_id = ts.id
52.     ORDER BY price DESC;
53.

Код 05

Теперь обратите внимание, в чем разница между кодами 03 и 05. Наверное, вы заметили, что разница заключается именно в том триггере, который мы создаём. В принципе, можно понять всё в коде 05, кроме одного. Откуда взялось это значение OLD? И как на самом деле работает этот триггер? Хорошо, давайте разберемся. Этот OLD напрямую связан с SQLite. Иными словами, это полностью зависит от реализации SQLite. В других реализациях может использоваться другое название, поэтому для получения более подробной информации необходимо обратиться к документации.

Но OLD означает старый. "В данном случае речь идёт об идентификаторе. Но о каком именно идентификаторе? Мы не передаем триггеру никаких параметров. Как мы можем сослаться на то, что не указываем?" Будьте спокойны. Мы уже сказали, что вся эта система с триггерами немного запутанная. Поэтому сначала нужно понять, что мы объясняли о работе с более простыми таблицами и системами. Триггеры — это наиболее продвинутая часть программирования на SQL. Так что не торопитесь.

Чтобы понять, откуда берется это значение OLD, нужно сначала разобраться, как работает код. Итак, после выполнения кода 05 мы удалим символ PETR4 из базы данных и заменим его символом BBDC4. Но мы сделаем это иначе, чем когда-либо прежде, именно потому, что теперь у нас есть триггер. Для внесения данного изменения мы используем приведенный ниже код.

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. INSERT INTO tb_Symbols(id, symbol) VALUES(2, 'BBDC4');
3. 
4. SELECT tq.of_day AS 'Data da cotação',
5.        tq.price AS 'Preço Atual',
6.        ts.symbol AS 'Nome do Ativo'
7.     FROM tb_Quotes AS tq, tb_Symbols AS ts
8.     WHERE tq.fk_id = ts.id
9.     ORDER BY price DESC;

Код 06

Таким образом, при выполнении кода 06 мы получим такой результат:

Изображение 13

"Что? Почему? Я знаю, ты меня обманываешь. Это шутка". Как бы мне хотелось, чтобы это было так, уважаемые читатели, но это совершенно верно, поскольку существует триггер. При выполнении строки 01 из кода 06 любая запись в базе данных, каким-либо образом связанная с символом PETR4, будет удалена. И поскольку id со значением два теперь свободен, мы можем, используя вторую строку кода 06, добавить символ BBDC4 с этим значением id. Вот почему я ещё раз настаиваю: прежде чем пытаться использовать триггеры, сначала попробуйте понять, как работает базовый SQL. В противном случае вы окончательно запутаетесь в коде, который будет выполняться.


Вставка данных с помощью триггеров

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

Чтобы понять, как вставлять данные с помощью триггеров, нужно начать с чего-то более простого. Давайте немного отступим и сначала рассмотрим несколько более базовых понятий. Это для того, чтобы вы не начали углубляться в то, что, возможно, ещё не было объяснено должным образом. Ещё раз напомним, что всё, что мы здесь рассмотрим, относится к стандартной реализации SQLite. В случае сомнений обратитесь к документации используемой вами реализации, чтобы понять, как настроить то, что мы увидим здесь. Итак, начнём с кода ниже:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );

Код 07

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

ПЕРЕД СОЗДАНИЕМ БАЗЫ ДАННЫХ нам необходимо определить, какие триггеры она будет содержать.

Судя по изложенной информации, у вас могло сложиться впечатление, что в базе данных можно добавлять или даже удалять триггеры в любое время. Но на самом деле, всё работает не совсем так. Когда SQL создаст файл, который будет содержать базу данных, всё необходимое должно быть должным образом определено. В некотором смысле, если это не предусмотрено реализацией, мы не сможем добавлять или удалять операционные процедуры из уже созданной базы данных. Примером реализации, которая позволяет добавлять или удалять элементы, является SQL Server. В нем можно в любое время добавлять или удалять методы управления, поскольку скрипты обычно не связаны с базой данных.

Но когда дело доходит до триггеров, обычно всё осуществляется иначе. Поэтому, прежде чем создавать случайные базы данных, надо постараться всё тщательно изучить. Таким образом можно избежать неожиданностей в будущем. Поэтому, следующий шаг это добавление триггера, который будет срабатывать в процессе вставки записей в базу данных. Таким образом, код 07 превратится в код 08, который показан ниже в полном объеме:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
23. END;

Код 08

Теперь не забудьте посмотреть на изображение 12: что создается здесь, в коде 08. Иными словами, что касается триггеров, то на этом же изображении отображаются команды DELETE, INSERT и UPDATE. Ещё раз напомним: необходимо понимать, что содержится в коде.

Цель обновления кода 07 для генерации кода 08 очень проста. При добавлении любого нового символа в таблицу tb_Symbols этот триггер автоматически приведет запись к верхнему регистру, чтобы буквы отображались в верхнем регистре. "Брат, ты что, шутишь?" Нет, обычно в базе данных необходимо, чтобы данные можно было каким-либо образом проверить и, при необходимости, скорректировать.

То, что мы делаем в коде 08, хотя и может показаться банальным, в определённых сценариях имеет смысл. Чтобы проверить, может ли код 08 достичь данной цели, мы воспользуемся приведенным ниже кодом. Напоминаем ещё раз, что можно запустить код 08, а спустя довольно длительное время запустить код 09, и всё должно работать совершенно нормально.

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. SELECT * FROM tb_Symbols;

Код 09

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

Изображение 14

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

Теперь, когда мы увидели результат, нам интересно понять, как работает триггер, реализованный в коде 08. Это связано с тем, что в коде 09 о нем нет упоминания. И подобные ситуации часто вызывают у многих начинающих пользователей SQL чувство беспокойства. В принципе, триггер не должен срабатывать, поскольку мы на него напрямую не ссылаемся.

Давайте теперь вернемся к коду 08, чтобы понять, как работает триггер. Строку 20 следует читать точно так, как она написана в коде 08. Пока не пытайтесь ничего интерпретировать, просто прочитайте то, что написано в строке 20. Чтобы помочь вам, мы покажем, как это следует сделать.

Если такое ещё не существует, создаем триггер с именем tr_InsertSymbol, который должен выполняться ПОСЛЕ вставки данных в таблицу tb_Symbols.

Прошу заметить, что это несложно. Главное — обращать внимание на то, чего мы хотим добиться. Хорошо, строки 21 и 23 содержат только зарезервированные слова SQL, обозначающие тело процедуры, с которой мы хотим работать. Для получения более подробной информации снова обратимся к изображению 12, поскольку показанное здесь является лишь иллюстративным примером. Теперь давайте разберемся со строкой 22. Его следует читать так же, как строку 20. И, еще раз, чтобы помочь вам в этом процессе, вам следует читать это следующим образом:

Теперь обновим таблицу tb_Symbols, сделав так, чтобы символ принимал значение upper(NEW.symbol), когда NEW.id равно id.

И снова мы увидели здесь нечто, зависящее от SQLite. Это происходит так, потому что ключевое слово NEW существует только в SQLite. Когда мы говорим об удалении записей, мы используем слово "OLD". А теперь мы используем слово "NEW". В принципе, это кажется довольно запутанным, "И действительно, я запутался, товарищ автор. Я никак не могу понять, когда использовать NEW, а когда OLD. Причина в том, что мы создали триггер под названием tr_InsertSymbol. Таким образом, когда мы добавляем данные в базу данных, мы обновляем базу данных, что, в принципе, делает значения не новыми, а устаревшими. Да это же безумие!"

Я знаю, уважаемые читатели. На самом деле, есть некоторые детали, которые поначалу могут немного запутать ситуацию, но по мере того, как мы будем их использовать и изучать, они перестанут вызывать путаницу и начнут обретать смысл. Тем не менее, я постараюсь упростить ситуацию, чтобы стало понятно, когда использовать NEW, а когда OLD. По крайней мере, этого достаточно, чтобы меньше путать читателя.

При выполнении операции DELETE удаляемая информация УЖЕ СУЩЕСТВУЕТ в базе данных и перестанет существовать. поэтому мы используем OLD. При выполнении операции INSERT информация НЕ СУЩЕСТВУЕТ в базе данных и будет создана, поэтому мы используем слово NEW. Путаницу вызывает именно тот факт, что триггер в строке 20 из кода 08 указывает на то, что триггер должен быть выполнен ПОСЛЕ того, как информация будет вставлена в базу данных. Однако следует учитывать, что эти данные являются новыми и ранее не существовали в базе данных.

Однако, ситуация несколько усложняется, когда триггер связан с командой UPDATE. В таком случае нам придётся мыслить несколько шире. Поэтому будьте внимательны. Информация, которая заменит уже существующую информацию в базе данных, должна рассматриваться как NEW. Однако информация, которая уже существует в базе данных и подлежит замене, должна рассматриваться как OLD. Если вы это понимаете, то можно будет обрабатывать любой скрипт-триггер, работающий со стандартной базой данных SQLite. Мы говорим о стандартной версии SQLite, потому что реализация может обрабатывать это по-разному. В таком случае, попробуйте изучить их документацию.


Выбор пути

Теперь, когда мы знаем, как работать с триггерами для вставки записей в базу данных, обеспечивая целостность данных, нам нужно рассмотреть ещё один, не менее важный аспект. Это связано с тем, что целостность базы данных гарантируется лишь частично. "Но почему вы говорите лишь частично?" Причина кроется в команде UPDATE, уважаемые читатели. Помните, что созданная гарантия основана на команде INSERT.

Но есть проблема с командой UPDATE, которую нам нужно решить. Рассмотрим такой пример: что произойдет, если мы захотим изменить что-либо в базе данных с помощью команды UPDATE? Поскольку SQL не знает, какие правила применять, любые данные, введенные с помощью команды UPDATE, будут приняты SQL и заменят любые ранее проверенные данные. Чтобы это продемонстрировать, взгляните на приведенный ниже код:

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. 
6. UPDATE tb_Symbols SET symbol = 'iTub4' WHERE id = 3;
7.
8. SELECT * FROM tb_Symbols;

Код 10

Код 10 необходимо выполнить в базе данных, созданной с использованием кода 08. Это необходимо для получения результата, показанного ниже:

Изображение 15

В обозначенной области в базе данных можно заметить нечто довольно странное. То, чего там быть не должно, именно потому, что все значения должны быть написаны заглавными буквами. Однако SQL изменил запись из-за строки 06 из кода 10 и, как следствие, целостность базы данных была нарушена. Обратите внимание, что все действия зависят от цели, которую необходимо достичь. Единой формулы не существует. Сначала нам нужно понять проблему, и только потом искать способы её решения. Однако всё это необходимо определить и установить до того, как база данных начнет получать конфиденциальные данные.

Итак, предположим следующее: как только запись создана, её больше нельзя изменить. Это позволит нам решить данный тип проблем, встречавшихся ранее. Однако, если мы разрешим обновление базы данных, то вставляемые в существующую запись данные, должны соответствовать определенным правилам. В таком случае нам придётся пойти другим путём, чтобы решить проблему с командой UPDATE.

Но мы также можем разрешить пользователю изменять имя символа. Однако, поступая таким образом, у пользователя создастся иллюзия, что имя было изменено. Но внутри базы данных фактически будет выполнено копирование данных с созданием новой записи. Старая запись относилась к ситуации, когда пользователь не мог напрямую получить доступ к данным. Однако администратор базы данных сможет восстановить все данные, поскольку исходная запись останется нетронутой. Такой подход приведет нас к другому решению, которое также потребует реализации. Иными словами, каждый случай — это отдельная ситуация. Единого решения, подходящего для всего, не существует.

Итак, чтобы лучше проиллюстрировать ситуацию, давайте посмотрим, как обрабатывается команда UPDATE, которая позволяет пользователю изменить имя символа, но таким образом, чтобы в конечном итоге имя оставалось в рамках тех же правил, что и при выполнении команды INSERT. Для этого мы могли бы использовать модификацию исходного скрипта, показанную ниже:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;

Код 11

"Но подождите секунду, Теперь ты точно шутишь. Как такое может быть, что код триггера команды UPDATE — это почти точная копия триггера команды INSERT? Ты уверен, что это будет работать? У меня есть сомнения, потому что если мы выполним операцию INSERT, то сработает триггер на строке 20. Но, поскольку мы используем команду UPDATE в строке 22, я полагаю, что триггер, реализованный в строке 25, также сработает. А при выполнении команды в строке 27 SQL войдет в цикл. Честно говоря, я не думаю, что это сработает".

Что ж, если вы действительно наблюдали за этой последовательностью выполнения, уважаемые читатели, это значит, что вы всё еще не поняли, как SQL работает с триггерами Но ничего страшного. Теперь, если мы создадим новую базу данных с использованием кода 11, а затем сразу же попытаемся снова запустить код 10 в этой новой базе данных, посмотрите, что произойдет:

Изображение 16

Теперь разберемся в следующем: когда пользователь запрашивает вставку записи в таблицу tb_Symbols, SQL может вставить запись, а может и не делать этого. Если вставить, SQL выполнит триггер на строке 20 из кода 11. Это, в свою очередь, приведет к выполнению строки 22, что обновит данные, которые содержатся в базе данных. Но строка 22 заставит SQL выполнить триггер в строке 25. До этого момента вы были правы, после выполнения строки 27 мы снова попросим SQL обновить базу данных.

Однако на этот раз мы принудительно приведем содержимое записи к определенному шаблону, который в точности совпадал с тем, что существовал на момент создания записи с помощью команды INSERT. Таким образом, нам удалось охватить как команду INSERT, так и команду UPDATE. Хотя это и не самый лучший способ для объяснений, это был самый простой способ, который я нашел, чтобы объяснить, как мы можем это сделать.

И последнее, что я хочу показать: если мы хотим предотвратить изменение некоторых записей командой UPDATE, относительно простой способ сделать это — использовать приведенный ниже код при создании базы данных.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;
29. 
30. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol_Before BEFORE UPDATE ON tb_Symbols
31.     WHEN upper(NEW.symbol) != upper(OLD.symbol)
32. BEGIN
33.     SELECT RAISE(ABORT, 'ERROR: Unable to update this record...');
34. END;

Код 12

Здесь всё немного по-другому. Но если мы снова используем код 10 на базе данных, созданной с помощью кода 12, мы увидим следующий результат:

Изображение 17

Теперь я хочу, чтобы вы внимательно посмотрели на это изображение 17 и попытались понять, что именно создал код 12, чтобы при выполнении кода 10 мы получили этот результат. Обратите внимание, что строка 01 из кода 10 будет выполнена успешно, создав таким образом записи в базе данных. Однако при выполнении строки 06 возникнет ошибка, как показано на изображении 17. Именно здесь я хочу, чтобы вы обратили внимание на данные, представленные на изображении 17. Посмотрите на строку 03 из сообщения. Теперь вернитесь к коду 12, и вы увидите, что это в точности то же самое сообщение, что и в строке 33. Как это возможно?

При попытке вставить какие-то данные в таблицу, мы выполним триггер в строке 20, сразу после вставки записи. Это заставит строку 22 дать команду на обновление записи. Хорошо, но перед обновлением будет выполнен триггер в строке 30, только после этого мы выполним триггер в строке 25. Прошу заметить, что мы изменили принцип работы кода 11. Именно для этого и нужна проверка строки 31. Обратите внимание, что данная проверка определяет, отличается или совпадает запись, которую мы пытаемся обновить, с той, которая уже существует в базе данных. Выполнив эту проверку, мы гарантируем, что запись не будет изменена. Это связано с тем, что если запись отличается, сработает триггер, и это приведет к выполнению строки 33. Когда SQL выполнит строку 33, тогда тому, кто запросил обновление, будет отправлено сообщение с объяснением, что операция не может быть выполнена по какой-либо причине.

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


Заключительные идеи

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

Многие вещи, которые, как я вижу, люди делают с помощью Python или даже Excel, можно было бы решить гораздо проще, если бы использовался SQL. Но так как SQL — это вещь, требующая изучения и внимания, большинство предпочитает ломать голову, пытаясь совместить данные и информацию, используя для этого классическое программирование. Однако, я гарантирую вам: изучение SQL действительно того стоит. Тем более, когда начинаешь понимать, как данные могут быть связаны друг с другом.

Таким образом, мы завершаем рассмотрение темы SQL. А вам предстоит продолжить обучение, начатое здесь. В следующей статье мы увидим, как всё показанное применяется на практике при объединении SQL с MQL5. Вы увидите, что до этого момента мы лишь слегка коснулись поверхности того, что мы на самом деле можем делать в MetaTrader 5.
Файл Описание
Experts\Expert Advisor.mq5
 Демонстрирует взаимодействие между Chart Trade и советником (для взаимодействия требуется Mouse Study).
 Indicators\Chart Trade.mq5 Создает окно для настройки отправляемого ордера (для взаимодействия требуется Mouse Study).
 Indicators\Market Replay.mq5 Создайте элементы управления для взаимодействия с сервисом репликации/моделирования (для взаимодействия требуется Mouse Study).
 Indicators\Mouse Study.mq5 Обеспечивает взаимодействие между графическими элементами управления и пользователем (необходимо как для работы системы репликации, так и на реальном рынке).
 Servicios\Market Replay.mq5 Создает и поддерживает сервис репликации/моделирования рынка (основной файл всей системы).
 Код VS C++ Server.cpp Создает и поддерживает сокет-сервер, разработанный на C++ (версия мини-чата).
 Code in Python\Server.py Создает и поддерживает сокет в Python для связи между MetaTrader 5 и Excel
 Indicators\Mini Chat.mq5 Позволяет реализовать мини-чат через индикатор (для работы требуется использование сервера)
 Experts\Mini Chat.mq5 Позволяет реализовать мини-чат в советнике (для работы требуется использование сервера).
 Scripts\SQLite.mq5 Демонстрирует использование скрипта SQL с помощью MQL5
 Files\Script 01.sql Демонстрирует создание простой таблицы с внешним ключом.
 Files\Script 02.sql Показывает добавление значений в таблицу

Перевод с португальского произведен MetaQuotes Ltd.
Оригинальная статья: https://www.mql5.com/pt/articles/13059

Прикрепленные файлы |
Загрузить ZIP
Anexo.zip (571.71 KB)

Предупреждение: все права на данные материалы принадлежат MetaQuotes Ltd. Полная или частичная перепечатка запрещена.

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

Daniel Jose
Daniel Jose

Другие статьи автора

Перейти к обсуждению на форуме трейдеров
Особенности написания Пользовательских Индикаторов Особенности написания Пользовательских Индикаторов
Написание пользовательских индикаторов в торговой системе MetaTrader 4
Упрощение работы с базами данных в MQL5 (Часть 1): Введение в базы данных и SQL Упрощение работы с базами данных в MQL5 (Часть 1): Введение в базы данных и SQL
Мы рассмотрим, как работать с базами данных в MQL5, используя встроенные функции языка. Мы рассмотрим все аспекты, от создания, вставки, обновления и удаления таблиц до импорта и экспорта данных, и все это с примерами кода. Данный материал служит прочной основой для понимания внутренних механизмов доступа к данным, подготавливая почву для обсуждения ORM (Object-Relational Mapping, объектно-реляционное отображение), где мы создадим его на языке MQL5.
Особенности написания экспертов Особенности написания экспертов
Написание и тестирование экспертов в торговой системе MetaTrader 4.
Разработка инструментария для анализа Price Action (Часть 31): Модуль распознавания свечных паттернов на Python (I) — ручное распознавание Разработка инструментария для анализа Price Action (Часть 31): Модуль распознавания свечных паттернов на Python (I) — ручное распознавание
Свечные паттерны лежат в основе торговли Price Action и дают ценные сигналы о возможном развороте рынка или продолжении тренда. Представьте надежный инструмент, который постоянно отслеживает каждый новый бар, выявляет ключевые формации, такие как паттерны поглощения, молоты, доджи и звезды, и сразу уведомляет вас, когда обнаруживает значимый торговый сетап. Именно такой функционал мы и разработали. Независимо от того, новичок вы или опытный трейдер, эта система выдает оповещения в реальном времени о свечных паттернах, позволяя открывать сделки увереннее и эффективнее. Читайте дальше, чтобы узнать, как это работает и как может улучшить вашу торговую стратегию.