forked from rosh/MQL5Book
140 lines
4.3 KiB
MQL5
140 lines
4.3 KiB
MQL5
//+------------------------------------------------------------------+
| |
//| DBquotesImport.mq5 |
| |
//| Copyright 2022, MetaQuotes Ltd. |
| |
//| https://www.mql5.com |
| |
//+------------------------------------------------------------------+
| |
#property description "Creates and populates a database table with specific quotes."
| |
#property script_show_inputs
| |
| |
#define PRTF // disable bulk logging
| |
#include "..\..\Include\DBSQLite.mqh"
| |
#undef PRTF
| |
#include "..\..\Include\Periods.mqh"
| |
#include "..\..\Include\PRTF.mqh"
| |
#include "..\..\Include\TypeName.mqh"
| |
| |
//+------------------------------------------------------------------+
| |
//| Inputs |
| |
//+------------------------------------------------------------------+
| |
input string Database = "MQL5Book/DB/Quotes";
| |
input int TransactionSize = 1000;
| |
| |
//+------------------------------------------------------------------+
| |
//| Example struct with common field types |
| |
//+------------------------------------------------------------------+
| |
struct MqlRatesDB: public MqlRates
| |
{
| |
/* reference:
| |
| |
datetime time;
| |
double open;
| |
double high;
| |
double low;
| |
double close;
| |
long tick_volume;
| |
int spread;
| |
long real_volume;
| |
*/
| |
| |
bool bindAll(DBQuery &q) const
| |
{
| |
return q.bind(0, time)
| |
&& q.bind(1, open)
| |
&& q.bind(2, high)
| |
&& q.bind(3, low)
| |
&& q.bind(4, close)
| |
&& q.bind(5, tick_volume)
| |
&& q.bind(6, spread)
| |
&& q.bind(7, real_volume);
| |
}
| |
| |
long rowid(const long setter = 0)
| |
{
| |
// rowid is assigned by the time
| |
return time;
| |
}
| |
};
| |
| |
DB_FIELD_C1(MqlRatesDB, datetime, time, DB_CONSTRAINT::PRIMARY_KEY);
| |
DB_FIELD(MqlRatesDB, double, open);
| |
DB_FIELD(MqlRatesDB, double, high);
| |
DB_FIELD(MqlRatesDB, double, low);
| |
DB_FIELD(MqlRatesDB, double, close);
| |
DB_FIELD(MqlRatesDB, long, tick_volume);
| |
DB_FIELD(MqlRatesDB, int, spread);
| |
DB_FIELD(MqlRatesDB, long, real_volume);
| |
| |
| |
//+------------------------------------------------------------------+
| |
//| Read a bunch of MqlRates[] and write to the database |
| |
//+------------------------------------------------------------------+
| |
bool ReadChunk(DBSQLite &db, const int offset, const int size)
| |
{
| |
MqlRates rates[];
| |
MqlRatesDB ratesDB[];
| |
const int n = CopyRates(_Symbol, PERIOD_CURRENT, offset, size, rates);
| |
if(n > 0)
| |
{
| |
DBTransaction tr(db, true);
| |
Print(rates[0].time);
| |
ArrayResize(ratesDB, n);
| |
for(int i = 0; i < n; ++i)
| |
{
| |
ratesDB[i] = rates[i];
| |
}
| |
| |
return db.insert(ratesDB);
| |
}
| |
else
| |
{
| |
// normally finishes with HISTORY_NOT_FOUND
| |
// when maximal number of bars loaded
| |
// according to terminal settings
| |
Print("CopyRates failed: ", _LastError, " ", E2S(_LastError));
| |
}
| |
return false;
| |
}
| |
| |
//+------------------------------------------------------------------+
| |
//| Script program start function |
| |
//+------------------------------------------------------------------+
| |
void OnStart()
| |
{
| |
Print("");
| |
DBSQLite db(Database + _Symbol + PeriodToString());
| |
if(!PRTF(db.isOpen())) return;
| |
| |
// remove the table (if exists)
| |
PRTF(db.deleteTable(TYPENAME(MqlRatesDB)));
| |
| |
// create empty table
| |
if(!PRTF(db.createTable<MqlRatesDB>(true))) return;
| |
| |
int offset = 0;
| |
while(ReadChunk(db, offset, TransactionSize) && !IsStopped())
| |
{
| |
offset += TransactionSize;
| |
}
| |
| |
DBRow *rows[];
| |
if(db.prepare(StringFormat("SELECT COUNT(*) FROM %s",
| |
TYPENAME(MqlRatesDB))).readAll(rows))
| |
{
| |
Print("Records added: ", rows[0][0].integer_value);
| |
}
| |
}
| |
//+------------------------------------------------------------------+
| |
/*
| |
| |
db.isOpen()=true / ok
| |
db.deleteTable(typename(MqlRatesDB))=true / ok
| |
db.createTable<MqlRatesDB>(true)=true / ok
| |
2022.06.29 20:00:00
| |
2022.05.03 04:00:00
| |
2022.03.04 10:00:00
| |
...
| |
CopyRates failed: 4401 HISTORY_NOT_FOUND
| |
Records added: 100000
| |
| |
*/
| |
//+------------------------------------------------------------------+
|