- Знакомство с принципами работы с базой данных в MQL5
- Основы SQL
- Структура (схема) таблиц: типы данных и ограничения
- Интеграция ООП (MQL5) и SQL: концепция ORM
- Создание, открытие и закрытие базы данных
- Выполнение запросов без привязки к данным MQL5
- Проверка существования таблицы в базе данных
- Подготовка запросов с привязкой: DatabasePrepare
- Удаление и сброс подготовленных запросов
- Привязка данных к параметрам запроса:DatabaseBind/Array
- Выполнение подготовленных запросов: DatabaseRead/Bind
- Раздельное чтение полей: DatabaseColumn-функции
- Примеры CRUD-операций в SQLite через объекты ORM
- Транзакции
- Импорт и экспорт таблицы базы данных
- Печать таблиц и SQL-запросов в журнал
- Пример поиска торговой стратегии средствами SQLite
Примеры CRUD-операций в SQLite через объекты ORM
Мы изучили все функции, необходимые для реализации полного жизненного цикла информации в базе данных, то есть CRUD (Create, Read, Update, Delete). Но прежде чем приступать к практике, нужно завершить прослойку ORM.
Из нескольких предыдущих разделов нам уже ясно, что единицей работы с базой данных является запись: это может быть запись в таблице базы или элемент в результатах выполнения запроса. Для чтения одной записи на уровне ORM введем класс DBRow. Каждая запись порождается SQL-запросом, поэтому в конструктор передается его дескриптор.
Как мы знаем, запись может состоять из нескольких столбцов, количество и типы которых позволяют узнать DatabaseColumn-функции. Для экспозиции этой информации в MQL-программу, использующую DBRow, мы зарезервировали соответствующие переменные: columns и массив структур DBRowColumn (в последней — три поля для хранения имени, типа и размера столбца).
Кроме того, объекты DBRow могут при необходимости кэшировать в себе значения, полученные из базы. Для этой цели применен массив data типа MqlParam. Поскольку мы заранее не знаем, значения какого типа окажутся в конкретной колонке, используем MqlParam как разновидность универсального типа Variant, доступного в других средах программирования.
class DBRow
|
В переменной cursor отслеживается номер текущей записи из результатов запроса. Пока запрос не выполнен, cursor равен -1.
За выполнение запроса отвечает виртуальный метод DBread, вызывающий DatabaseRead.
protected:
|
Зачем нам потребовался виртуальный метод, мы раскроем чуть позже. Публичный метод next, в котором используется DBread, обеспечивает "пролистывание" записей результата и выглядит следующим образом.
public:
|
Если обращение к запросу выполняется первый раз, мы выделяем память и считываем информацию о столбцах. Если было запрошено кэширование, дополнительно заполняем массив data. Для этого для каждого столбца вызывается перегруженный оператор '[]'. В нем, в зависимости от типа значения, мы вызываем соответствующую DatabaseColumn-функцию и помещаем полученное значение в то или иное поле структуры MqlParam.
virtual MqlParam operator[](const int i = 0) const
|
Для полноценного считывания двоичных данных из BLOB-полей предусмотрен метод getBlob (используйте тип uchar в качестве S, чтобы получить массив байтов, если нет более конкретной информации о формате содержимого).
template<typename S>
|
Для описанных методов процесс выполнения запроса и чтения его результатов можно представить таким псевдо-кодом (в нем оставлены за кадром уже существующие классы DBSQLite и DBQuery — скоро мы сведем их воедино):
int query = ...
|
Прописывать каждый раз цикл по столбцам в явном виде не очень красиво, поэтому в классе предусмотрен метод для получения значений всех полей записи.
void readAll(MqlParam ¶ms[]) const
|
Также для удобства в класс добавлены перегрузки оператора '[]' и метода getBlob для чтения полей по их именам вместо индексов. Например,
class DBRow
|
Таким образом, вы можете обращаться к избранным столбцам.
int query = ...
|
Но все же получение элементов записи по отдельности, в виде массива MqlParam нельзя назвать по-настоящему объектным подходом. Было бы желательно считывать запись таблицы базы целиком в объект — прикладную структуру. Напомним, что MQL5 API предоставляет подходящую функцию: DatabaseReadBind. Именно здесь нам приходит на помощь возможность описать класс-наследник DBRow и переопределить в нем виртуальный метод DBRead.
Этот класс DBRowStruct является шаблоном и ожидает в качестве параметра S одну из простых структур, разрешенных для привязки в DatabaseReadBind.
template<typename S>
|
С производным классом мы можем практически бесшовно получать объекты из базы.
int query = ...
|
Теперь настало время для превращения псевдо-кода в рабочий код за счет увязки DBRow/DBRowStruct с DBQuery. Добавим в DBQuery автоуказатель на объект DBRow, который будет содержать данные о текущей записи из результатов запроса (если он был выполнен). Использование автоуказателя освобождает вызывающий код от забот об освобождении объектов DBRow: они удаляются вместе с DBQuery или при повторном создании из-за перезапуска запроса (если такое потребуется). Инициализацию объекта DBRow или DBRowStruct производит шаблонный метод start.
class DBQuery
|
Тип DBValue представляет собой структуру-пустышку, которая нужна только для того, чтобы инструктировать программу о создании базового объекта DBRow, не нарушая при этом компилируемость строки с вызовом DatabaseReadBind.
С методом start все вышеприведенные фрагменты псевдокода становятся рабочими за счет такой подготовки запроса:
DBSQLite db("MQL5Book/DB/Example1"); // открываем базу
|
Данный пример считывает из базы данных мета-информацию о конфигурации конкретной таблицы (мы её создали в примере DBcreateTableFromStruct.mq5 в разделе Выполнение запросов без привязки к данным MQL5): каждый столбец описывается отдельной записью с несколькими полями (стандарт SQLite), что формализовано в структуре DBTableColumn.
struct DBTableColumn
|
Чтобы избавить пользователя от необходимости каждый раз писать цикл с переводом записей результатов в объекты-структуры, в классе DBQuery есть шаблонный метод readAll, который заполняет передаваемый по ссылке массив структур информацией из результатов запроса. Аналогичный метод readAll позволяет заполнить массив указателей на объекты DBRow (это больше подойдет для приема результатов синтетических запросов с колонками из разных таблиц).
В квартете операций CRUD метод DBRowStruct::get отвечает за букву R (Read). Чтобы сделать чтение объекта более функционально полным поддержим точечное восстановление объекта из базы по его идентификатору.
Подавляющее большинство таблиц в базах SQLite имеет первичный ключ rowid (если только разработчик по тем или иным причинам не использовал опцию "WITHOUT ROWID" в описании), поэтому новый метод read будет принимать в качестве параметра значение ключа. По умолчанию, название таблицы подразумевается равным типу принимающей структуры, но можем быть изменено на альтернативное через параметр table. Учитывая, что подобный запрос носит разовый характер и должен вернуть одну запись, имеет смысл поместить метод read непосредственно в класс DBSQLite и управлять короткоживущими объектами DBQuery и DBRowStruct<S> внутри.
class DBSQLite
|
Основную работу выполняет SQL-запрос "SELECT * FROM '%s' WHERE %s=%ld;", возвращающий запись со всеми полями из указанной таблицы по совпадению ключа rowid.
Теперь создать конкретный объект из базы можно так (подразумевается, что интересующий нас идентификатор должен быть где-то сохранен).
DBSQLite db("MQL5Book/DB/Example1");
|
Наконец, в некоторых сложных случаях, когда требуется максимальная гибкость в составлении запроса (например, комбинация нескольких таблиц — как правило, SELECT с JOIN, или вложенные запросы), нам все же придется разрешить задавать SQL-команду в явном виде для получения выборки, хотя это и нарушает принцип ORM. Эту возможность открывает метод DBSQLite::prepare, который мы уже представляли раньше в контексте управления подготовленными запросами.
На этом все основные способы чтения можно считать рассмотренными.
Однако нам пока нечего читать из базы, потому что мы перескочили через этап добавления записей.
Попробуем реализовать создание объекта (C). Напомним, что в нашей объектной концепции типы структур полуавтоматически определяют под себя таблицы базы данных (с помощью макросов DB_FIELD). Например, структура Struct позволила создать в базе таблицу "Struct" с набором столбцов, соответствующих полям структуры. Мы обеспечили это шаблонным методом createTable в классе DBSQLite. Теперь по аналогии необходимо написать шаблонный метод insert, который добавлял бы запись в эту таблицу.
В метод передается объект структуры, для типа которой должен существовать заполненный массив DBEntity<S>::prototype (он заполняется макросами). Благодаря этому массиву мы можем сформировать список параметров (точнее, их заместителей '?n'): это поручено статическому методу qlist. Однако подготовка запроса еще полдела — чуть ниже нужно будет выполнить привязку входных данных на основе свойств объекта.
В команду "INSERT" добавлена инструкция "RETURNING rowid", поэтому при успешном выполнении запроса мы ожидаем единственную строку результата с одним значением: новым rowid.
class DBSQLite
|
В исходном коде метода insert есть один нюанс, на который следует обратить особое внимание. Для привязки значений к параметрам запроса вызывается метод object.bindAll(q). Это означает, что в прикладной структуре, которую требуется интегрировать с базой, нужно реализовать такой метод, предоставляющий для "движка" все переменные-члены.
Кроме того, для идентификации объектов предполагается наличие поля с первичным ключом, и лишь объект "знает", что это за поле, поэтому в структуре имеется метод rowid, позволяющий, с одной стороны, передать в объект идентификатор, присвоенный его записи в базе, а с другой стороны — узнать у объекта этот идентификатор, если тот уже был присвоен ранее.
Метод для изменения записи DBSQLite::update (U) во многом схож с insert, и потому с ним предлагается ознакомиться самостоятельно. Его основой является SQL-запрос "UPDATE '%s' SET (%s)=(%s) WHERE rowid=%ld;", в который предполагается передача всех полей структуры (bindAll() объекта) и ключ (rowid() объекта).
Наконец, упомянем, что точечное удаление (D) записи по объекту реализовано в методе DBSQLite::remove (слово delete является оператором MQL5).
Покажем все методы в примере скрипта DBfillTableFromStructArray.mq5, где определена новая структура Struct.
Полями структуры сделаем несколько значений общеупотребительных типов.
struct Struct
|
В строковом поле image вызывающий код будет указывать название графического ресурса или имя файла, а в момент привязки к базе соответствующие двоичные данные будут копироваться как BLOB. Впоследствии, когда мы будем считывать данные из базы в объекты Struct, двоичные данные будут попадать в строку image, но, разумеется, с искажениями (потому что строка прервется на первом нулевом байте). Для точного извлечения BLOB-ов из базы нужно будет вызвать метод DBRow::getBlob (на основе DatabaseColumnBlob).
Создание мета-информации о полях структуры Struct обеспечивают следующие макросы. На их основе MQL-программа может автоматически создать таблицу в базе под объекты Struct, а также инициировать привязку передаваемых в запросы данных на основе свойств объектов (не следует путать эту привязку с обратной привязкой для получения результатов запроса, т.е. DatabaseReadBind).
DB_FIELD_C1(Struct, long, id, DB_CONSTRAINT::PRIMARY_KEY);
|
Для наполнения небольшого тестового массива структур в скрипте имеются входные переменные: в них указывается тройка валют, котировки которых попадут в поле number. Также мы встроили в скрипт два стандартных изображения с целью проверки работы с BLOB-ами: они "отправятся" в поле image. Поле timestamp будет автоматически заполняться нашими ORM-классами текущей временной меткой вставки или модификации записи. Первичный ключ в поле id должна будет заполнять сама SQLite.
#resource "\\Images\\euro.bmp"
|
Поскольку значения для входных переменных запросов (те самые '?n') привязываются, в конечном счете, с помощью функций DatabaseBind или DatabaseBindArray под номерами, наша структура должна в методе bindAll установить соответствие между номерами и своими полями: предполагается простая нумерация по порядку декларирования.
struct Struct
|
Метод rowid очень прост.
struct Struct
|
Определив структуру, опишем тестовый массив из 4 элементов. Только 2 из них имеют привязанные изображения. Идентификаторы у всех объектов нулевые, т.к. они еще не в базе.
Struct demo[] =
|
В главной функции OnStart создадим или откроем базу (по умолчанию MQL5Book/DB/Example2.sqlite), на всякий случай попытаемся удалить таблицу "Struct", чтобы при повторных запусках скрипта обеспечить воспроизводимость результатов и отладки, затем создадим таблицу под структуру Struct.
void OnStart()
|
Вместо того, чтобы добавлять объекты по одному, в цикле вроде такого:
// -> этот вариант (отложен в сторону)
|
воспользуемся альтернативной реализацией метода insert, который принимает на вход сразу массив объектов и обрабатывает их в едином запросе — так эффективнее (но общая канава метода — такая же, как в показанном выше методе insert для одного объекта).
db.insert(demo); // в объектах проставляются новые rowid
|
Теперь попробуем отобрать из базы записи по некоторому условию, например, те, у которых не назначено изображение. Для этого подготовим SQL-запрос, обернутый в объект DBQuery, и далее получим его результаты двумя способами: через привязку к структурам Struct или через экземпляры универсального класса DBRow.
DBQuery *query = db.prepare(StringFormat("SELECT * FROM %s WHERE image IS NULL",
|
Оба варианта должны дать один и тот же результат, хотя и представленный по-разному (см. лог ниже).
Далее наш скрипт делает паузу на 1 секунду, чтобы можно было заметить изменения во временных метках следующих записей, которые мы изменим.
Print("Pause...");
|
Назначим объектам в массиве result[] изображение "yuan.bmp", расположенное в папке рядом со скриптом. И тут же обновим объекты в базе.
for(int i = 0; i < ArraySize(result); ++i)
|
После запуска скрипта вы можете убедиться в наличии BLOB-ов у всех четырех записей в навигаторе БД, встроенном в MetaEditor, а также в различии временных меток у первых двух и последних двух записей.
Продемонстрируем извлечение двоичных данных. Сначала покажем, как BLOB отображается в строковое поле image (двоичные данные — не для журнала, мы делаем так только для демонстрации).
const long id1 = 1;
|
Затем прочитаем данные целиком с помощью getBlob (полная длина больше, чем у строки выше).
DBRow *r;
|
Мы должны получить файл temp.bmp.raw, идентичный MQL5/Files/Images/dollar.bmp.raw, который в целях отладки создается в методе Struct::bindAll. Таким образом, легко убедиться в точном соответствии записанных и прочитанных двоичных данных.
Обратите внимание, что поскольку мы сохраняем в базу двоичное содержимое ресурса, оно не является исходным файлом формата BMP: ресурсы производят нормализацию цвета и хранят массив пикселей без заголовка с мета-информацией об изображении.
В процессе работы скрипт формирует подробный лог. В частности, создание базы и таблицы отмечается следующими строками.
db.isOpen()=true / ok
|
SQL-запрос вставки массива объектов подготавливается однократно, а затем многократно исполняется с предварительной привязкой разных данных (здесь показана только одна итерация). Количество вызовов DatabaseBind-функций соответствует переменным '?n' в запросе ('?4' автоматически заменена нашими классами на вызов функции SQL STRFTIME('%s') для получения метки текущего времени UTC).
sql=INSERT INTO 'Struct' VALUES(?1,?2,?3,STRFTIME('%s'),?5) RETURNING rowid; / ok
|
Далее в журнал выводится массив структур с уже назначенными первичными ключами rowid в первой колонке.
[id] [name] [number] [timestamp] [image]
|
Выбор записей без изображений выдает следующий результат (мы выполняем этот запрос дважды разными методами: первый раз заполняем массив структур Struct, а второй — массив DBRow, из которого для каждого поля получаем "значение" в виде MqlParam).
DatabasePrepare(db,sql)=196609 / ok
|
Вторая часть скрипта обновляет пару найденных записей без изображений и добавляет в них BLOB-ы.
Pause...
|
Наконец, получение двоичных данных двумя способами — несовместимым, через строковое поле image в результате чтения объекта целиком DatabaseReadBind (это сделано только для визуализации последовательности байтов в журнале) и совместимым, через DatabaseRead и DatabaseColumnBlob — дает отличные результаты: разумеется, второй способ правильный: длина и содержимое BLOB-а в 4096 байтах восстановлены.
sql=SELECT * FROM 'Struct' WHERE rowid=1; / ok
ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ɬ7�ȫ6�ũ6�Ĩ5���5�¦5�Ĩ5�ƪ6�ȫ6�Ȭ7�ɬ7�ɬ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7�ʭ7��҉��֒��ٛ...
|
Подводя промежуточный итог разработки собственной оболочки ORM, приведем обобщенную схему её классов.
Диаграмма классов ORM (MQL5<->SQL)