Скачать MetaTrader 5

SQL и MQL5: Работаем с базой данных SQLite

14 февраля 2014, 10:14
o_o
6
10 636

Small. Fast. Reliable.
Choose any of three.

 

Вступление

Многие разработчики задумывались над необходимостью использовать в своих проектах базы данных для хранения информации, но каждый раз останавливались, зная, какими дополнительными затратами времени сопровождается установка SQL-сервера. Если для программистов выполнить это не так сложно (или СУБД уже установлена для других целей), то для обычного пользователя данная операция вызовет много вопросов, либо вообще отобьет желание устанавливать софт.

В таком случае программист просто не связывается с СУБД, понимая, что кроме него этим решением воспользуются единицы. В конечном итоге в ход идет работа с файлами (часто не одним при разрозненности данных): использование CSV, реже XML или JSON, либо запись бинарных данных с жестким размером структуры и т.п.

Но, как оказывается, есть отличная альтернатива SQL-серверу! При этом не нужно устанавливать дополнительный софт, вся работа идет локально в вашем проекте, но при этом вы используете всю мощь языка SQL. Имя этой альтернативы - SQLite.

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

Необходимые условия для работы со статьей:

  • Хорошее настроение ;)
  • Распакуйте архив из приложения к статье в папку рабочего терминала MetaTrader 5
  • Установите любую удобную для вас утилиту просмотра SQLite баз (например, SQLiteStudio)
  • Добавьте в закладки официальную документацию по SQLite http://www.sqlite.org

Содержание

1. Принципы SQLite
2. Работа с SQLite API
    2.1. Открытие и закрытие базы данных
    2.2. Выполнение SQL запросов
    2.3. Получение данных из таблиц
    2.4. Запись параметрических данных со связыванием
    2.5. Транзакции / Multirow insert (пример создания таблицы сделок торгового счета)
3. Сборка 64-битной DLL версии SQLite3


1. Принципы SQLite

SQLite – это СУБД, главной особенностью которой является отсутствие "слушающего" SQL-сервера. Сервером уже является ваше приложение. Работа с базой данных в SQLite – это фактически работа с файлом (на диске или в памяти). Все данные вы можете архивировать или же переносить на другой компьютер без какой либо потребности специально их устанавливать.

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

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

Работать с SQLite возможно тремя способами:

  1. воспользоваться DLL файлом с полным набором API функций;
  2. использовать shell команды к EXE файлу;
  3. скомпилировать ваш проект, включив в него исходные коды SQLite API.

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

 

2. Работа с SQLite3 API

В работе коннектора будут использованы следующие SQLite функции:

//--- общие функции
sqlite3_open
sqlite3_prepare
sqlite3_step
sqlite3_finalize
sqlite3_close
sqlite3_exec

//--- функции для получения описания ошибки
sqlite3_errcode
sqlite3_extended_errcode
sqlite3_errmsg

//--- функции для сохранения данных
sqlite3_bind_null
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_double
sqlite3_bind_text
sqlite3_bind_blob

//--- функции для получения данных
sqlite3_column_count
sqlite3_column_name
sqlite3_column_type
sqlite3_column_bytes
sqlite3_column_int
sqlite3_column_int64
sqlite3_column_double
sqlite3_column_text
sqlite3_column_blob

Также нам понадобятся низкоуровневые функции msvcrt.dll для работы с указателями:

strlen
strcpy
memcpy

Так как я создаю коннектор, который работает в 32 и 64-битных терминалах, то необходимо позаботиться о размере указателя, отправляемого в функции API. Разделим их имена:

// для 32-битного терминала
#define PTR32                int
#define sqlite3_stmt_p32     PTR32
#define sqlite3_p32          PTR32
#define PTRPTR32             PTR32

// для 64-битного терминала
#define PTR64                long
#define sqlite3_stmt_p64     PTR64
#define sqlite3_p64          PTR64
#define PTRPTR64             PTR64

Все API функции перегрузим для 32 и 64-битных указателей при необходимости. Обращаю внимание, что все указатели в коннекторе будут 64-битного размера. Их перевод в 32-битный размер будет происходить непосредственно в перегруженных API функциях. Исходный код импорта функций API находится в файле SQLite3Import.mqh


Типы данных SQLite

В третьей версии SQLite предусматривается пять типов данных

Тип
Описание
NULL Отсутствие данных.
INTEGER Целое размером 1, 2, 3, 4, 6 или 8 байт в зависимости от величины хранимого значения.
REAL Вещественное размером 8 байт.
TEXT Строка с конечным символом \0 в кодировке UTF-8 или UTF-16.
BLOB Произвольные бинарные данные


Для указания типов полей при создании таблицы в SQL запросе можно воспользоваться также и другими именами типов, например, BIGINT или INT, которые приняты в разных СУБД. В этом случае SQLite переведет их в один из своих внутренних типов, в данном случае - в INTEGER. Более подробно про типы данных и их связи вы можете прочитать в документации http://www.sqlite.org/datatype3.html


2.1. Открытие и закрытие базы данных

Как вы уже знаете, база данных в SQLite3 - это обычный файл. Открытие базы данных фактически есть открытие файла и получение его хендла.

Открытие происходит с помощью функции sqlite3_open:

int sqlite3_open(const uchar &filename[], sqlite3_p64 &ppDb);

filename [in]  - путь к файлу, либо имя файла, если файл открывается в текущем месторасположении.
ppDb     [out] - переменная, куда будет записать адрес хендла файла базы.

Возвращает SQLITE_OK в случае успеха или код ошибки.

Закрывается файл базы функцией sqlite3_close:

int sqlite3_close(sqlite3_p64 ppDb);

ppDb [in] - хендл файла

Возвращает SQLITE_OK в случае успеха или код ошибки.


Создадим в коннекторе функции открытия и закрытия БД.

//+------------------------------------------------------------------+
//| CSQLite3Base class                                               |
//+------------------------------------------------------------------+
class CSQLite3Base
  {
   sqlite3_p64       m_db;             // pointer to database file
   bool              m_bopened;        // flag "Is m_db handle valid"
   string            m_dbfile;         // path to database file

public:
                     CSQLite3Base();   // constructor
   virtual          ~CSQLite3Base();   // destructor


public:
   //--- connection to database 
   bool              IsConnected();
   int               Connect(string dbfile);
   void              Disconnect();
   int               Reconnect();
  };
//+------------------------------------------------------------------+
//| Constructor                                                      |
//+------------------------------------------------------------------+
CSQLite3Base::CSQLite3Base()
  {
   m_db=NULL;
   m_bopened=false;
  }
//+------------------------------------------------------------------+
//| Destructor                                                       |
//+------------------------------------------------------------------+
CSQLite3Base::~CSQLite3Base()
  {
   Disconnect();
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::IsConnected()
  {
   return(m_bopened && m_db);
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Connect(string dbfile)
  {
   if(IsConnected())
      return(SQLITE_OK);
   m_dbfile=dbfile;
   return(Reconnect());
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void CSQLite3Base::Disconnect()
  {
   if(IsConnected())
      ::sqlite3_close(m_db);
   m_db=NULL;
   m_bopened=false;
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Reconnect()
  {
   Disconnect();
   uchar file[];
   StringToCharArray(m_dbfile,file);
   int res=::sqlite3_open(file,m_db);
   m_bopened=(res==SQLITE_OK && m_db);
   return(res);
  }

Открывать и закрывать базу данных коннектор уже умеет. Давайте проверим работу простым скриптом:

#include <MQH\Lib\SQLite3\SQLite3Base.mqh>

CSQLite3Base sql3; // database connector
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void OnStart()
{   
//--- open database connection
   if(sql3.Connect("SQLite3Test.db3")!=SQLITE_OK)
      return;
//--- close connection
    sql3.Disconnect();
}

Запустите скрипт в дебаг-режиме, сделайте глубокий вдох и проверьте работу каждой строки. В результате работы появится файл базы данных в папке установки терминала MetaTrader 5. Порадуйтесь, что все прошло удачно и переходите к следующему разделу.


2.2. Выполнение SQL запросов

Любой SQL запрос в SQLite3 проходит минимум три этапа:

  1. sqlite3_prepare - верификация и получение списка инструкций (statement);
  2. sqlite3_step - выполнение этих инструкций;
  3. sqlite3_finalize - завершение и очистка памяти.

Эта схема, в основном, подходит для создания или удаления таблиц, либо для записи небинарных данных, т.е. для случаев, когда запрос SQL не подразумевает возврата каких-либо данных, кроме как статуса успешности его выполнения.

Если же запрос подразумевает получение данных или запись бинарных данных, то на втором этапе нам надо будет воспользоваться функциями sqlite3_column_хх или sqlite3_bind_хх, соответственно. Эти функции подробно рассмотрим в следующем разделе.

Напишем метод CSQLite3Base::Query для выполнения простого SQL запроса:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Query(string query)
  {
//--- check connection
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//--- check query string
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
   sqlite3_stmt_p64 stmt=0; // variable for pointer
//--- get pointer
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[];
   StringToCharArray(query,str);
//--- prepare statement and check result
   int res=::sqlite3_prepare(m_db,str,-1,pstmt,NULL);
   if(res!=SQLITE_OK)
      return(res);
//--- execute
   res=::sqlite3_step(pstmt);
//--- clean
   ::sqlite3_finalize(pstmt);
//--- return result
   return(res);
  }

Как видите функции sqlite3_prepare, sqlite3_step и sqlite3_finalize идут друг за другом.

Рассмотрим применение CSQLite3Base::Query при работе с таблицами в SQLite:

// Создание таблицы (CREATE TABLE)
sql3.Query("CREATE TABLE IF NOT EXISTS `TestQuery` (`ticket` INTEGER, `open_price` DOUBLE, `comment` TEXT)");

После выполнения данной команды в базе данных появится таблица:

// Переименование таблицы  (ALTER TABLE  RENAME)
sql3.Query("ALTER TABLE `TestQuery` RENAME TO `Trades`");

// Добавление столбца (ALTER TABLE  ADD COLUMN)
sql3.Query("ALTER TABLE `Trades` ADD COLUMN `profit`");

После выполнения этих команд имеем таблицу с новым названием и добавленным полем:

// Добавление ряда (INSERT INTO)
sql3.Query("INSERT INTO `Trades` VALUES(3, 1.212, 'info', 1)");

// Обновление ряда (UPDATE)
sql3.Query("UPDATE `Trades` SET `open_price`=5.555, `comment`='New price'  WHERE(`ticket`=3)")

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

И, наконец, следующие последовательно выполненные команды очистят базу данных.

// Удаление всех рядов из таблицы (DELETE FROM)
sql3.Query("DELETE FROM `Trades`")

// Удаление таблицы (DROP TABLE)
sql3.Query("DROP TABLE IF EXISTS `Trades`");

// Сжатие базы данных (VACUUM)
sql3.Query("VACUUM");

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

const PTR64 sqlite3_errmsg(sqlite3_p64 db);

db [in] - хендл, полученный функцией sqlite3_open

Возвращает указатель на строку с описанием ошибки.

В коннекторе добавим метод для получения этой строки из указателя с помощью strcpy и strlen.

//+------------------------------------------------------------------+
//| Error message                                                    |
//+------------------------------------------------------------------+
string CSQLite3Base::ErrorMsg()
  {
   PTR64 pstr=::sqlite3_errmsg(m_db);  // get message string
   int len=::strlen(pstr);             // length of string
   uchar str[];
   ArrayResize(str,len+1);             // prepare buffer
   ::strcpy(str,pstr);                 // read string to buffer
   return(CharArrayToString(str));     // return string
  }


2.3. Получение данных из таблиц

Я уже упоминал в начале раздела 2.2, чтение данных выполняется с помощью функций sqlite3_column_хх. Схематически это можно изобразить так:

  1. sqlite3_prepare
  2. sqlite3_column_count - узнаем число столбцов полученной таблицы
  3. Пока результат шага sqlite3_step == SQLITE_ROW
    1. sqlite3_column_хх - читаем ячейки этой строки
  4. sqlite3_finalize

Так как мы подходим к обширному разделу по чтению и записи данных, настал удобный момент описать три класса-контейнера, с помощью которых будет происходить весь обмен. Требуемую модель данных нам диктует сам способ хранения информации в БД:

База данных
|
Таблица – это массив рядов.
|
Ряд – это массив ячеек.
|
Ячейка – это байтовый буфер произвольной длины.


//+------------------------------------------------------------------+
//| CSQLite3Table class                                              |
//+------------------------------------------------------------------+
class CSQLite3Table
  {

public:
   string            m_colname[]; // column name
   CSQLite3Row       m_data[];    // database rows
//...
  };
//+------------------------------------------------------------------+
//| CSQLite3Row class                                                |
//+------------------------------------------------------------------+
class CSQLite3Row
  {

public:
   CSQLite3Cell      m_data[];
//...
  };
//+------------------------------------------------------------------+
//| CSQLite3Cell class                                               |
//+------------------------------------------------------------------+
class CSQLite3Cell
  {

public:
   enCellType        type;
   CByteImg          buf;
//...
  };

Как видите, связи CSQLite3Row и CSQLite3Table примитивны - это обычные массивы данных. Класс ячейки CSQLite3Cell тоже имеет массив данных uchar + поле Тип данных. Байтовый массив обслуживает класс CByteImage (по аналогии с известным вам CFastFile).

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

enum enCellType
  {
   CT_UNDEF,
   CT_NULL,
   CT_INT,
   CT_INT64,
   CT_DBL,
   CT_TEXT,
   CT_BLOB,
   CT_LAST
  };

Обратите внимание, что к пяти базовым типам SQLite3 добавлен тип CT_UNDEF для идентификации начального состояния ячейки. Целый тип INTEGER разделен на два CT_INT и CT_INT64 в соответствии с аналогично разделенными функциями sqlite3_bind_intXX, sqlite3_column_intXX.

Получение данных

Для получения данных из ячейки создадим метод, обобщающий функции вида sqlite3_column_хх, который будет проверять тип данных, их размер и записывать их в CSQLite3Cell.

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::ReadStatement(sqlite3_stmt_p64 stmt,int column,CSQLite3Cell &cell)
  {
   cell.Clear();
   if(!stmt || column<0)
      return(false);
   int bytes=::sqlite3_column_bytes(stmt,column);
   int type=::sqlite3_column_type(stmt,column);
//---
   if(type==SQLITE_NULL)
      cell.type=CT_NULL;
   else if(type==SQLITE_INTEGER)
     {
      if(bytes<5)
         cell.Set(::sqlite3_column_int(stmt,column));
      else
         cell.Set(::sqlite3_column_int64(stmt,column));
     }
   else if(type==SQLITE_FLOAT)
      cell.Set(::sqlite3_column_double(stmt,column));
   else if(type==SQLITE_TEXT || type==SQLITE_BLOB)
     {
      uchar dst[];
      ArrayResize(dst,bytes);
      PTR64 ptr=0;
      if(type==SQLITE_TEXT)
         ptr=::sqlite3_column_text(stmt,column);
      else
         ptr=::sqlite3_column_blob(stmt,column);
      ::memcpy(dst,ptr,bytes);
      if(type==SQLITE_TEXT)
         cell.Set(CharArrayToString(dst));
      else
         cell.Set(dst);
     }
   return(true);
  }

Функция получилась довольно объемная, но, по сути, она лишь читает данные из текущей инструкции и сохраняет их в ячейку.

Также перегрузим функцию CSQLite3Base::Query, добавив первым параметром контейнер-таблицу CSQLite3Table для принимаемых данных.

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Query(CSQLite3Table &tbl,string query)
  {
   tbl.Clear();
//--- check connection
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//--- check query string
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
//---
   sqlite3_stmt_p64 stmt=NULL;
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[]; StringToCharArray(query,str);
   int res=::sqlite3_prepare(m_db, str, -1, pstmt, NULL); if(res!=SQLITE_OK) return(res);
   int cols=::sqlite3_column_count(pstmt); // get column count
   bool b=true;
   while(::sqlite3_step(pstmt)==SQLITE_ROW) // in loop get row data
     {
      CSQLite3Row row; // row for table
      for(int i=0; i<cols; i++) // add cells to row
        {
         CSQLite3Cell cell;
         if(ReadStatement(pstmt,i,cell)) row.Add(cell); else { b=false; break; }
        }
      tbl.Add(row); // add row to table
      if(!b) break; // if error enabled
     }
// get column name
   for(int i=0; i<cols; i++)
     {
      PTR64 pstr=::sqlite3_column_name(pstmt,i); if(!pstr) { tbl.ColumnName(i,""); continue; }
      int len=::strlen(pstr);
      ArrayResize(str,len+1);
      ::strcpy(str,pstr);
      tbl.ColumnName(i,CharArrayToString(str));
     }
   ::sqlite3_finalize(stmt);  // clean
   return(b?SQLITE_DONE:res); // return result code
  }

Все нужные функции для получения данных есть. Переходим к их примерам:

// Чтение данных (SELECT)
CSQLite3Table tbl;
sql3.Query(tbl, "SELECT * FROM `Trades`")

Выведем результат данного запроса на печать в терминал командой Print(TablePrint(tbl)). В журнале увидим следующее (порядок снизу вверх):

// Пример расчета стат. информации из таблиц (COUNT, MAX, AVG ...)
sql3.Query(tbl, "SELECT COUNT(*) FROM `Trades` WHERE(`profit`>0)")   
sql3.Query(tbl, "SELECT MAX(`ticket`) FROM `Trades`")
sql3.Query(tbl, "SELECT SUM(`profit`) AS `sumprof`, AVG(`profit`) AS `avgprof` FROM `Trades`")

// Получение имен всех таблиц в базе
sql3.Query(tbl, "SELECT `name` FROM `sqlite_master` WHERE `type`='table' ORDER BY `name`;");

Результат данного запроса выведем аналогично командой Print(TablePrint(tbl)). Увидим имеющуюся таблицу:

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


2.4. Запись параметрических данных со связыванием

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

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

Чтоб применить связывание, необходимо вместо передаваемых данных поставить шаблон. Я рассмотрю один из вариантов - это знак "?". То есть UPDATE-запрос будет выглядеть так:

UPDATE `Trades` SET `open_price`=?, `comment`=? WHERE(`ticket`=3)


Затем, нужно выполнить подряд две функции: sqlite3_bind_double и sqlite3_bind_text, чтоб поместить данные в open_price и comment. В общем, схему работы с bind-функциями можно представить в виде:

  1. sqlite3_prepare
  2. По порядку вызываем sqlite3_bind_хх и записываем требуемые данные в инструкцию
  3. sqlite3_step
  4. sqlite3_finalize

По количеству типов sqlite3_bind_xx полностью повторяют рассмотренные функции чтения, поэтому их легко объединить в коннекторе в CSQLite3Base::BindStatement:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::BindStatement(sqlite3_stmt_p64 stmt,int column,CSQLite3Cell &cell)
  {
   if(!stmt || column<0)
      return(false);
   int bytes=cell.buf.Len();
   enCellType type=cell.type;
//---
   if(type==CT_INT)        return(::sqlite3_bind_int(stmt, column+1, cell.buf.ViewInt())==SQLITE_OK);
   else if(type==CT_INT64) return(::sqlite3_bind_int64(stmt, column+1, cell.buf.ViewInt64())==SQLITE_OK);
   else if(type==CT_DBL)   return(::sqlite3_bind_double(stmt, column+1, cell.buf.ViewDouble())==SQLITE_OK);
   else if(type==CT_TEXT)  return(::sqlite3_bind_text(stmt, column+1, cell.buf.m_data, cell.buf.Len(), SQLITE_STATIC)==SQLITE_OK);
   else if(type==CT_BLOB)  return(::sqlite3_bind_blob(stmt, column+1, cell.buf.m_data, cell.buf.Len(), SQLITE_STATIC)==SQLITE_OK);
   else if(type==CT_NULL)  return(::sqlite3_bind_null(stmt, column+1)==SQLITE_OK);
   else                    return(::sqlite3_bind_null(stmt, column+1)==SQLITE_OK);
  }

Единственная задача этого метода - записать буфер переданной ячейки в инструкцию.

Аналогичным образом добавим метод CQLite3Table::QueryBind, первым аргументом которого является строка данных на запись:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::QueryBind(CSQLite3Row &row,string query) // UPDATE <table> SET <row>=?, <row2>=?  WHERE (cond)
  {
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//---
   if(StringLen(query)<=0 || ArraySize(row.m_data)<=0)
      return(SQLITE_DONE);
//---
   sqlite3_stmt_p64 stmt=NULL;
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[];
   StringToCharArray(query,str);
   int res=::sqlite3_prepare(m_db, str, -1, pstmt, NULL);
   if(res!=SQLITE_OK)
      return(res);
//---
   bool b=true;
   for(int i=0; i<ArraySize(row.m_data); i++)
     {
      if(!BindStatement(pstmt,i,row.m_data[i]))
        {
         b=false;
         break;
        }
     }
   if(b)
      res=::sqlite3_step(pstmt); // executed
   ::sqlite3_finalize(pstmt);    // clean
   return(b?res:SQLITE_ERROR);   // result
  }

Его задача - записать строку в соответствующие параметры.


2.5. Транзакции / Multirow inserts

Перед рассмотрением этой темы вам понадобится знание еще одной функции SQLite API. В прошлом разделе я рассмотрел трехэтапную обработку запросов prepare+step+finalize. Альтернативным и в некоторых случаях простым, а в некоторых - даже необходимым решением является функция sqlite3_exec:

int sqlite3_exec(sqlite3_p64 ppDb, const char &sql[], PTR64 callback, PTR64 pvoid, PTRPTR64 errmsg);

ppDb [in] - хендл базы данных
sql  [in] - SQL запрос
Три остальных параметра пока что не рассматриваем применительно к MQL5.

Возвращает SQLITE_OK при успешном выполнении или код ошибки.

Ее суть – выполнить запрос за один вызов без создания трехэтапных конструкций.

Добавим ее вызов в коннектор:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Exec(string query)
  {
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
   uchar str[];
   StringToCharArray(query,str);
   int res=::sqlite3_exec(m_db,str,NULL,NULL,NULL);
   return(res);
  }

Использовать получившийся метод просто. Например, команду удаления таблицы (DROP TABLE) или сжатия базы (VACUUM) можно выполнить так:

sql3.Exec("DROP TABLE `Trades`");

sql3.Exec("VACUUM");


Транзакции

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

for (int i=0; i<N; i++)
   sql3.Query("INSERT INTO `Table` VALUES(1, 2, 'text')");

то получим о о ч ч е н ь м е е е д л е н н о е выполнение. На вставку тысячи строк потратится более 10(!) секунд, ибо так в SQLite делать не рекомендуется. Выходом из данной ситуации является использование транзакций: все SQL-инструкции заносятся в один общий список и затем проводятся одним запросом.

Для записи начала и окончания транзакции используются SQL-операторы:

BEGIN
...
COMMIT

Все что внутри - атомарно выполнится на последнем операторе COMMIT. Для ситуации, когда надо прервать цикл, или по какой-то причине не выполнять уже добавленные инструкции служит оператор ROLLBACK.

В качестве примера сделаем добавление всех сделок счета в таблицу.

#include <MQH\Lib\SQLite3\SQLite3Base.mqh>
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void OnStart()
  {
   CSQLite3Base sql3;

//--- open database connection
   if(sql3.Connect("Deals.db3")!=SQLITE_OK) return;
//---
   if(sql3.Query("CREATE TABLE IF NOT EXISTS `Deals` (`ticket` INTEGER PRIMARY KEY, `open_price` DOUBLE, `profit` DOUBLE, `comment` TEXT)")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }

//--- create transaction
   if(sql3.Exec("BEGIN")!=SQLITE_OK)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   HistorySelect(0,TimeCurrent());
//--- dump all deals from terminal to table 
   for(int i=0; i<HistoryDealsTotal(); i++)
     {
      CSQLite3Row row;
      long ticket=(long)HistoryDealGetTicket(i);
      row.Add(ticket);
      row.Add(HistoryDealGetDouble(ticket, DEAL_PRICE));
      row.Add(HistoryDealGetDouble(ticket, DEAL_PROFIT));
      row.Add(HistoryDealGetString(ticket, DEAL_COMMENT));
      if(sql3.QueryBind(row,"REPLACE INTO `Deals` VALUES("+row.BindStr()+")")!=SQLITE_DONE)
        {
         sql3.Exec("ROLLBACK");
         Print(sql3.ErrorMsg());
         return;
        }
     }
//--- end transaction
   if(sql3.Exec("COMMIT")!=SQLITE_OK)
      return;

//--- get statistical information from table
   CSQLite3Table tbl;
   CSQLite3Cell cell;

   if(sql3.Query(tbl,"SELECT COUNT(*) FROM `Deals` WHERE(`profit`>0)")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   tbl.Cell(0,0,cell);
   Print("Count(*)=",cell.GetInt64());
//---
   if(sql3.Query(tbl,"SELECT SUM(`profit`) AS `sumprof`, AVG(`profit`) AS `avgprof` FROM `Deals`")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   tbl.Cell(0,0,cell);
   Print("SUM(`profit`)=",cell.GetDouble());
   tbl.Cell(0,1,cell);
   Print("AVG(`profit`)=",cell.GetDouble());
  }

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

В журнале терминала вывелась статистическая информация

Поэкспериментируйте с данным скриптом: закомментируйте строки с BEGIN, ROLLBACK и COMMIT, и если у вас на счете более сотни сделок, то вы сразу же ощутите разницу. Кстати, по некоторым тестам, транзакции SQLite работают быстрее, чем в MySQL или PostgreSQL.


3. Сборка 64-битной версии sqlite3_64.dll

  1. Скачиваем исходный код SQLite (amalgamation) и вытягиваем из него файл sqlite3.c.
  2. Скачиваем DLL sqlite-dll-win32 и распаковываем из нее файл sqlite3.dll.
  3. В папке, где распаковали dll файл, выполняем в консоли команду LIB.EXE /DEF:sqlite3.def. Убедитесь что пути к файлу lib.exe прописаны в системной переменной PATH, либо найдите его у вас в Visual Studio.
  4. Создаем проект DLL, выбрав конфигурацию Release для 64 битных платформ.
  5. Добавляем в проект скачанный файл sqlite3.c и полученный файл sqlite3.def. Если на некоторые функции из def файла компилятор будет "ругаться", в этом случае просто закомментируйте их.
  6. В настройках проекта установим следующие параметры:
    C/C++ --> General --> Debug Information Format = Program Database (/Zi)
    C/C++ --> Precompiled Headers --> Create/Use Precompiled Header = Not Using Precompiled Headers (/Yu)
  7. Компилируем и получаем 64-битную dll.


Заключение

Надеюсь, что статья станет вашей настольной инструкцией в деле освоения SQLite. Вполне возможно, что вы будете использовать ее в своих будущих проектах. Этот краткий обзор дал вам некоторое представление о функциональности SQLite, как об идеальном и надежном решении для приложений.

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

Удачи и профитов!

Прикрепленные файлы |
sql_ft__mql5.zip (662.69 KB)
revers45
revers45 | 14 фев 2014 в 21:17
alsu:

Есть пара важных дополнений (и предложений к усовершенствованию:))

1. Автор пользует функцию sqlite_open(), но есть еще и более гибкая функция sqlite_open_v2(), которая умеет работать с флагами открытия, а значит: - управлять разграничением доступа к файлу БД; - создавать временные in-memory базы; - работать с БД по URI, а не только в локальной файловой системе и т.д.

2. Я пользовался(юсь) sqlite для решения задачи ускорения оптимизации советника в тех случаях, когда он использует "тяжелый" в расчетах индикатор. Логичным решением в такой ситуации выглядит сохранение показаний индикатора за период оптимизации в базе данных при первом прогоне, а при последующих - непосредственная добыча уже рассчитанных показаний из БД. Так вот, если пользоваться движком sqlite в "дефолтном виде", процесс посылки множества INSERT и SELECT запросов начинает занимать ооочень много времени. Практическим путем было установлено, что выход состоит в использовании либо 1) in-memory базы данных, что не всегда хорошо, если мы хотим оставить данные на потом и 2) #pragma-директив движка SQL.

Второй вариант боле предпочтителен, так как избавляет от необходимости дампа базы данных из RAM на диск. Делается это так: обязательно перед созданием первой таблицы в базе данных необходимо отправить ей следующие запросы:

Ну, а после этого уже можно

и т.д.

Всем удачи!

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

То же самое можно сказать и о предложенном автором статьи, в качестве примера применения SQLite, списка торговых операций.

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

arbitrageur
arbitrageur | 15 фев 2014 в 19:09
Есть колончатая SQL СУБД называется MonetDB. Это бесплатна СУБД, предназначенная для хранения данных в столбцах, у этой базы неплохая скорость и надежность работы. Если я не ошибаюсь к МТ представленным в топике образом можно подключить любую СУБД стандарта SQL.
Alexey Subbotin
Alexey Subbotin | 16 фев 2014 в 09:32
revers45:

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

 

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


Eugeniy Lugovoy
Eugeniy Lugovoy | 14 апр 2014 в 15:09

Возможность использования есть и это хорошо. Другое дело что SQLite не стоит использовать для серьезных проектов. Во всяком случае я бы не рекомендовал. Сам сталкивался не раз с проблемой коллизий на ней. Скажем, если торговый робот прикреплен к различным чартам, а использует одну базу, причем обращение происходит к одной таблице общего назначения (скажем регистрация/изменение сессий, аккаунтов), то в любом случае вы получите ошибку типа "таблица заблокирована". И не важно что транзакции все завершены, курсоры все закрыты и БД открыта была в shared режиме. Эта проблема известна и разработчикам SQLite.

Как по мне, так из файловых БД с поддержкой SQL лучше MS Access. Как ни ругайте мелкомягких, но я от SQLite ушел на MS Access и вовсе не жалею. OleDB драйвер Jet 4.0 ставится даже с Win98, так что проекты работают на всех OC Windows.

delphiec
delphiec | 10 ноя 2015 в 13:18

Скрипт не рабочий, из "коробки" не работает, на помойку такие проекты.

В консоли выдает  https://s.mail.ru/9dWTNLqx6RT2/img-2015-11-10-20-15-44.png

В таблице https://s.mail.ru/QZyK6HwhMvo9/img-2015-11-10-20-16-18.png

Код https://s.mail.ru/2ooLdMg5MrHP/img-2015-11-10-20-16-56.png 

Создание цифровых фильтров, незапаздывающих по времени Создание цифровых фильтров, незапаздывающих по времени

В статье рассматривается один из подходов к определению полезного сигнала (тенденции) потоковых данных. Небольшие практические тесты фильтрации (сглаживания) биржевых котировок демонстрируют потенциальную возможность создания цифровых фильтров (индикаторов), которые не запаздывают по времени и не перерисовываются на последних барах.

Рецепты MQL5 - Разработка мультивалютного индикатора волатильности на MQL5 Рецепты MQL5 - Разработка мультивалютного индикатора волатильности на MQL5

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

Рецепты MQL5 - Разработка мультивалютного индикатора для анализа расхождения цен Рецепты MQL5 - Разработка мультивалютного индикатора для анализа расхождения цен

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

Видео: Как работают торговые сигналы на платформе MetaTrader Видео: Как работают торговые сигналы на платформе MetaTrader

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