267 lines
10 KiB
MQL5
267 lines
10 KiB
MQL5
//+------------------------------------------------------------------+
|
||
//| DBfillTableFromStructArray.mq5 |
|
||
//| Copyright 2022, MetaQuotes Ltd. |
|
||
//| https://www.mql5.com |
|
||
//+------------------------------------------------------------------+
|
||
#property description "Creates and populates a table based on struct declaration. If the database doesn't exist, it creates it beforehand."
|
||
#property script_show_inputs
|
||
|
||
#resource "\\Images\\euro.bmp"
|
||
#resource "\\Images\\dollar.bmp"
|
||
|
||
#include "..\..\Include\DBSQLite.mqh"
|
||
#include "..\..\Include\TypeName.mqh"
|
||
|
||
input string Database = "MQL5Book/DB/Example2";
|
||
input string EURUSD = "EURUSD";
|
||
input string USDCNH = "USDCNH";
|
||
input string USDJPY = "USDJPY";
|
||
|
||
//+------------------------------------------------------------------+
|
||
//| Example struct with common field types |
|
||
//+------------------------------------------------------------------+
|
||
struct Struct
|
||
{
|
||
long id;
|
||
string name;
|
||
double number;
|
||
datetime timestamp;
|
||
string image; // assume filename or resource name on input
|
||
// this will be defined as BLOB in the table,
|
||
// on output we use string only for partial logging;
|
||
// arrays are not supported in DB-bound structs,
|
||
// so string is only chance to detect non-null blobs,
|
||
// to get actual blob data call DBRow::getBlob
|
||
|
||
// used to insert/update table record based on the object
|
||
bool bindAll(DBQuery &q) const
|
||
{
|
||
uint pixels[] = {};
|
||
uint w, h;
|
||
if(StringLen(image))
|
||
{
|
||
if(StringFind(image, "::") == 0)
|
||
{
|
||
ResourceReadImage(image, pixels, w, h);
|
||
FileSave(StringSubstr(image, 2) + ".raw", pixels); // debug output (not a BMP, no header)
|
||
}
|
||
else
|
||
{
|
||
const string res = "::" + image;
|
||
ResourceCreate(res, image);
|
||
ResourceReadImage(res, pixels, w, h);
|
||
ResourceFree(res);
|
||
}
|
||
}
|
||
return (id == 0 ? q.bindNull(0) : q.bind(0, id)) // if id is NULL, it will get new rowid
|
||
&& q.bind(1, name)
|
||
&& q.bind(2, number)
|
||
// && q.bind(3, timestamp) // this is handled by CURRENT_TIMESTAMP
|
||
&& q.bindBlob(4, pixels);
|
||
}
|
||
|
||
// used for single record selection in update/delete, and array insertion
|
||
long rowid(const long setter = 0)
|
||
{
|
||
if(setter) id = setter;
|
||
return id;
|
||
}
|
||
};
|
||
|
||
// NB: if PRIMARY_KEY constraint is not specified for one of integer fields,
|
||
// implicit 'rowid' column will be added as primary key automatically by SQL;
|
||
// this does even spare 1 byte per record!
|
||
|
||
DB_FIELD_C1(Struct, long, id, DB_CONSTRAINT::PRIMARY_KEY);
|
||
DB_FIELD(Struct, string, name);
|
||
DB_FIELD(Struct, double, number);
|
||
DB_FIELD_C1(Struct, datetime, timestamp, DB_CONSTRAINT::CURRENT_TIMESTAMP);
|
||
DB_FIELD(Struct, blob, image);
|
||
|
||
Struct demo[] =
|
||
{
|
||
{0, "dollar", 1.0, 0, "::Images\\dollar.bmp"},
|
||
{0, "euro", SymbolInfoDouble(EURUSD, SYMBOL_ASK), 0, "::Images\\euro.bmp"},
|
||
{0, "yuan", 1.0 / SymbolInfoDouble(USDCNH, SYMBOL_BID), 0, NULL},
|
||
{0, "yen", 1.0 / SymbolInfoDouble(USDJPY, SYMBOL_BID), 0, NULL},
|
||
};
|
||
|
||
//+------------------------------------------------------------------+
|
||
//| Script program start function |
|
||
//+------------------------------------------------------------------+
|
||
void OnStart()
|
||
{
|
||
Print("");
|
||
DBSQLite db(Database);
|
||
if(!PRTF(db.isOpen())) return;
|
||
|
||
// remove the table (if exists from previous run)
|
||
PRTF(db.deleteTable(TYPENAME(Struct)));
|
||
|
||
// create empty table
|
||
if(!PRTF(db.createTable<Struct>(true))) return;
|
||
|
||
// insertion of array of objects
|
||
db.insert(demo); // this assigns rowid's inside objects
|
||
/*
|
||
// alternative with separate calls per object
|
||
for(int i = 0; i < ArraySize(demo); ++i)
|
||
{
|
||
PRTF(db.insert(demo[i])); // this returns rowid on every call
|
||
}
|
||
*/
|
||
ArrayPrint(demo);
|
||
|
||
// retrieve a couple of records with no images
|
||
DBQuery *query = db.prepare(StringFormat("SELECT * FROM %s WHERE image IS NULL",
|
||
TYPENAME(Struct)));
|
||
|
||
// approach 1: via specific struct
|
||
Struct result[];
|
||
PRTF(query.readAll(result));
|
||
ArrayPrint(result);
|
||
|
||
query.reset();
|
||
|
||
// approach 2: via universal DBRow container
|
||
DBRow *rows[];
|
||
query.readAll(rows);
|
||
for(int i = 0; i < ArraySize(rows); ++i)
|
||
{
|
||
Print(i);
|
||
MqlParam fields[];
|
||
rows[i].readAll(fields);
|
||
ArrayPrint(fields);
|
||
}
|
||
|
||
Print("Pause...");
|
||
Sleep(1000); // wait to make it clear how timestamp changes on update
|
||
|
||
// update both records: apply new image
|
||
for(int i = 0; i < ArraySize(result); ++i)
|
||
{
|
||
result[i].image = "yuan.bmp";
|
||
db.update(result[i]);
|
||
}
|
||
|
||
// demonstrate blob reading for specific record
|
||
// first, show how Blob is mapped into standard string field in object
|
||
// (binary content causes problems)
|
||
const long id1 = 1;
|
||
Struct s;
|
||
if(db.read(id1, s))
|
||
{
|
||
Print("Length of string with Blob: ", StringLen(s.image));
|
||
Print(s.image); // just a demo - don't print binary data into the log
|
||
}
|
||
|
||
// second, show how exact binary content is retrieved from DB table
|
||
DBRow *r;
|
||
if(db.read(id1, r, "Struct"))
|
||
{
|
||
uchar bytes[];
|
||
Print("Actual size of Blob: ", r.getBlob("image", bytes));
|
||
FileSave("temp.bmp.raw", bytes); // not a BMP, no header
|
||
// you should get temp.bmp.raw which is equal to MQL5/Files/Images/dollar.bmp.raw
|
||
}
|
||
|
||
// uncomment this to test "DELETE FROM TABLE" query for specific object/record
|
||
// db.remove(s);
|
||
}
|
||
//+------------------------------------------------------------------+
|
||
/*
|
||
1-st run with default inputs
|
||
|
||
db.isOpen()=true / ok
|
||
db.deleteTable(typename(Struct))=true / ok
|
||
sql=CREATE TABLE IF NOT EXISTS Struct (id INTEGER PRIMARY KEY,
|
||
name TEXT ,
|
||
number REAL ,
|
||
timestamp INTEGER CURRENT_TIMESTAMP,
|
||
image BLOB ); / ok
|
||
db.createTable<Struct>(true)=true / ok
|
||
sql=INSERT INTO 'Struct' VALUES(?1,?2,?3,STRFTIME('%s'),?5) RETURNING rowid; / ok
|
||
DatabasePrepare(db,sql)=131073 / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(query)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(query)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(query)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(query)=true / ok
|
||
[id] [name] [number] [timestamp] [image]
|
||
[0] 1 "dollar" 1.00000 1970.01.01 00:00:00 "::Images\dollar.bmp"
|
||
[1] 2 "euro" 1.00402 1970.01.01 00:00:00 "::Images\euro.bmp"
|
||
[2] 3 "yuan" 0.14635 1970.01.01 00:00:00 null
|
||
[3] 4 "yen" 0.00731 1970.01.01 00:00:00 null
|
||
DatabasePrepare(db,sql)=196609 / ok
|
||
DatabaseReadBind(query,object)=true / ok
|
||
DatabaseReadBind(query,object)=true / ok
|
||
DatabaseReadBind(query,object)=false / DATABASE_NO_MORE_DATA(5126)
|
||
query.readAll(result)=true / ok
|
||
[id] [name] [number] [timestamp] [image]
|
||
[0] 3 "yuan" 0.14635 2022.08.20 13:14:38 null
|
||
[1] 4 "yen" 0.00731 2022.08.20 13:14:38 null
|
||
DatabaseRead(query)=true / ok
|
||
DatabaseRead(query)=true / ok
|
||
DatabaseRead(query)=false / DATABASE_NO_MORE_DATA(5126)
|
||
0
|
||
[type] [integer_value] [double_value] [string_value]
|
||
[0] 4 3 0.00000 null
|
||
[1] 14 0 0.00000 "yuan"
|
||
[2] 13 0 0.14635 null
|
||
[3] 10 1661001278 0.00000 null
|
||
[4] 0 0 0.00000 null
|
||
1
|
||
[type] [integer_value] [double_value] [string_value]
|
||
[0] 4 4 0.00000 null
|
||
[1] 14 0 0.00000 "yen"
|
||
[2] 13 0 0.00731 null
|
||
[3] 10 1661001278 0.00000 null
|
||
[4] 0 0 0.00000 null
|
||
Pause...
|
||
sql=UPDATE 'Struct' SET (id,name,number,timestamp,image)=(?1,?2,?3,STRFTIME('%s'),?5) WHERE rowid=3; / ok
|
||
DatabasePrepare(db,sql)=262145 / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(handle)=false / DATABASE_NO_MORE_DATA(5126)
|
||
sql=UPDATE 'Struct' SET (id,name,number,timestamp,image)=(?1,?2,?3,STRFTIME('%s'),?5) WHERE rowid=4; / ok
|
||
DatabasePrepare(db,sql)=327681 / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBind(handle,index,value)=true / ok
|
||
DatabaseBindArray(handle,index,value)=true / ok
|
||
DatabaseRead(handle)=false / DATABASE_NO_MORE_DATA(5126)
|
||
sql=SELECT * FROM 'Struct' WHERE rowid=1; / ok
|
||
DatabasePrepare(db,sql)=393217 / ok
|
||
DatabaseReadBind(query,object)=true / ok
|
||
Length of string with Blob: 922
|
||
ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ɬ7<C9AC>ȫ6<C8AB>ũ6<C5A9>Ĩ5<C4A8><35><EFBFBD>5<EFBFBD>¦5<C2A6>Ĩ5<C4A8>ƪ6<C6AA>ȫ6<C8AB>Ȭ7<C8AC>ɬ7<C9AC>ɬ7<C9AC>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD>ʭ7<CAAD><37>҉<EFBFBD><D289>֒<EFBFBD><D692>ٛ<EFBFBD><D99B>ܣ<EFBFBD>...
|
||
sql=SELECT * FROM 'Struct' WHERE rowid=1; / ok
|
||
DatabasePrepare(db,sql)=458753 / ok
|
||
DatabaseRead(query)=true / ok
|
||
Actual size of Blob: 4096
|
||
|
||
[// option
|
||
sql=DELETE FROM 'Struct' WHERE rowid=1; / ok
|
||
DatabasePrepare(db,sql)=524289 / ok
|
||
DatabaseRead(handle)=false / DATABASE_NO_MORE_DATA(5126)
|
||
]
|
||
|
||
*/
|
||
//+------------------------------------------------------------------+
|