29210372/Article-22608-News-Calendar-SQLite-Persistence-Layer
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
Article-22608-News-Calendar.../News Database.mqh

482 lines
No EOL
20 KiB
MQL5
Raw Permalink Blame History

//+------------------------------------------------------------------+
//| News Database.mqh |
//| Copyright 2026, Allan Munene Mutiiria. |
//| https://t.me/Forex_Algo_Trader |
//+------------------------------------------------------------------+
#property copyright "Copyright 2026, Allan Munene Mutiiria."
#property link "https://t.me/Forex_Algo_Trader"
//--- Include guard
#ifndef NEWS_DATABASE_MQH
#define NEWS_DATABASE_MQH
//--- Include core data definitions and state
#include "News Core.mqh"
//--- Declare debugLogging extern when not compiled from main (Logic.mqh uses same guard)
#ifndef NEWS_COMPILED_FROM_MAIN
extern bool debugLogging; // Print debug info to journal
#endif
//+------------------------------------------------------------------+
//| Database State |
//+------------------------------------------------------------------+
int g_news_db = INVALID_HANDLE; // SQLite database handle (-1 when not open)
//--- Database stored in the COMMON terminal folder so both live chart
//--- and strategy tester agents can access the same file
#define NEWS_DB_PATH "Database\\NewsCalendarEA.db" // Path relative to Common/Files/
//+------------------------------------------------------------------+
//| Open or create database and ensure required tables exist |
//+------------------------------------------------------------------+
bool News_DbOpen()
{
//--- Return early if database is already open
if(g_news_db != INVALID_HANDLE) return true;
//--- Open from Common/Files/ folder for shared live and tester access
g_news_db = DatabaseOpen(NEWS_DB_PATH,
DATABASE_OPEN_READWRITE |
DATABASE_OPEN_CREATE |
DATABASE_OPEN_COMMON);
if(g_news_db == INVALID_HANDLE)
{
Print("News_DbOpen: failed to open '", NEWS_DB_PATH, "' error=", GetLastError());
return false;
}
//--- Create events table with all calendar value columns
if(!DatabaseExecute(g_news_db,
"CREATE TABLE IF NOT EXISTS events ("
" value_id INTEGER PRIMARY KEY,"
" event_id INTEGER NOT NULL,"
" event_time INTEGER NOT NULL,"
" currency TEXT NOT NULL,"
" event_name TEXT NOT NULL,"
" importance TEXT NOT NULL,"
" has_actual INTEGER NOT NULL DEFAULT 0,"
" actual REAL NOT NULL DEFAULT 0,"
" has_forecast INTEGER NOT NULL DEFAULT 0,"
" forecast REAL NOT NULL DEFAULT 0,"
" has_previous INTEGER NOT NULL DEFAULT 0,"
" previous REAL NOT NULL DEFAULT 0,"
" has_revised INTEGER NOT NULL DEFAULT 0,"
" revised_prev REAL NOT NULL DEFAULT 0,"
" unit INTEGER NOT NULL DEFAULT 0,"
" multiplier INTEGER NOT NULL DEFAULT 0,"
" digits INTEGER NOT NULL DEFAULT 0,"
" updated_at INTEGER NOT NULL DEFAULT 0"
");"))
{
Print("News_DbOpen: create events table failed: ", GetLastError());
DatabaseClose(g_news_db);
g_news_db = INVALID_HANDLE;
return false;
}
//--- Create index on event_time for fast time-window queries
DatabaseExecute(g_news_db,
"CREATE INDEX IF NOT EXISTS idx_events_time ON events(event_time);");
//--- Create triggered table for trade deduplication across restarts
if(!DatabaseExecute(g_news_db,
"CREATE TABLE IF NOT EXISTS triggered ("
" event_id INTEGER PRIMARY KEY,"
" triggered_at INTEGER NOT NULL"
");"))
{
Print("News_DbOpen: create triggered table failed: ", GetLastError());
DatabaseClose(g_news_db);
g_news_db = INVALID_HANDLE;
return false;
}
if(debugLogging) Print("News_DbOpen: ready at '", NEWS_DB_PATH, "'");
return true;
}
//+------------------------------------------------------------------+
//| Close the database cleanly |
//+------------------------------------------------------------------+
void News_DbClose()
{
if(g_news_db == INVALID_HANDLE)
{
Print(">>> News DB: Close requested but database was not open");
return;
}
DatabaseClose(g_news_db);
g_news_db = INVALID_HANDLE;
Print(">>> News DB: Database closed cleanly");
}
//+------------------------------------------------------------------+
//| Upsert a single event row into the events table |
//+------------------------------------------------------------------+
bool News_DbUpsertEvent(long valueId, const NewsEvent &ev, datetime updatedAt)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return false;
//--- Prepare INSERT OR REPLACE statement with all event columns
int stmt = DatabasePrepare(g_news_db,
"INSERT OR REPLACE INTO events ("
" value_id,event_id,event_time,currency,event_name,importance,"
" has_actual,actual,has_forecast,forecast,"
" has_previous,previous,has_revised,revised_prev,"
" unit,multiplier,digits,updated_at"
") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
if(stmt == INVALID_HANDLE) return false;
//--- Bind all parameter values to statement slots
DatabaseBind(stmt, 0, valueId);
DatabaseBind(stmt, 1, ev.eventId);
DatabaseBind(stmt, 2, (long)ev.eventDateTime);
DatabaseBind(stmt, 3, ev.currency);
DatabaseBind(stmt, 4, ev.event);
DatabaseBind(stmt, 5, ev.importance);
DatabaseBind(stmt, 6, (int)ev.hasActual);
DatabaseBind(stmt, 7, ev.actual);
DatabaseBind(stmt, 8, (int)ev.hasForecast);
DatabaseBind(stmt, 9, ev.forecast);
DatabaseBind(stmt, 10, (int)ev.hasPrevious);
DatabaseBind(stmt, 11, ev.previous);
DatabaseBind(stmt, 12, (int)ev.hasRevised);
DatabaseBind(stmt, 13, ev.revisedPrevious);
DatabaseBind(stmt, 14, ev.unit);
DatabaseBind(stmt, 15, ev.multiplier);
DatabaseBind(stmt, 16, ev.digits);
DatabaseBind(stmt, 17, (long)updatedAt);
//--- Execute and finalize the prepared statement
const bool ok = DatabaseRead(stmt);
DatabaseFinalize(stmt);
return ok;
}
//+------------------------------------------------------------------+
//| Load events for a time window from DB into g_news_allEvents[] |
//+------------------------------------------------------------------+
int News_DbLoadEventsForWindow(datetime startDt, datetime endDt)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return 0;
//--- Prepare time-window query ordered ascending by event time
int stmt = DatabasePrepare(g_news_db,
"SELECT event_id,event_time,currency,event_name,importance,"
" has_actual,actual,has_forecast,forecast,"
" has_previous,previous,has_revised,revised_prev,"
" unit,multiplier,digits"
" FROM events"
" WHERE event_time >= ? AND event_time <= ?"
" ORDER BY event_time ASC;");
if(stmt == INVALID_HANDLE) return 0;
//--- Bind time window bounds
DatabaseBind(stmt, 0, (long)startDt);
DatabaseBind(stmt, 1, (long)endDt);
//--- Reset event array and read each row into it
ArrayResize(g_news_allEvents, 0);
int idx = 0;
while(DatabaseRead(stmt))
{
//--- Declare temporary locals; MQL5 forbids struct refs from arrays
NewsEvent e;
long evId = 0, evTime = 0;
int hasA = 0, hasF = 0, hasP = 0, hasR = 0;
int unitV = 0, multV = 0, digV = 0;
string cur = "", evName = "", imp = "";
double act = 0, fcast = 0, prev = 0, revPrev = 0;
//--- Read each column from the current row
DatabaseColumnLong (stmt, 0, evId);
DatabaseColumnLong (stmt, 1, evTime);
DatabaseColumnText (stmt, 2, cur);
DatabaseColumnText (stmt, 3, evName);
DatabaseColumnText (stmt, 4, imp);
DatabaseColumnInteger(stmt, 5, hasA);
DatabaseColumnDouble (stmt, 6, act);
DatabaseColumnInteger(stmt, 7, hasF);
DatabaseColumnDouble (stmt, 8, fcast);
DatabaseColumnInteger(stmt, 9, hasP);
DatabaseColumnDouble (stmt, 10, prev);
DatabaseColumnInteger(stmt, 11, hasR);
DatabaseColumnDouble (stmt, 12, revPrev);
DatabaseColumnInteger(stmt, 13, unitV);
DatabaseColumnInteger(stmt, 14, multV);
DatabaseColumnInteger(stmt, 15, digV);
//--- Populate event struct from column values
e.eventId = evId;
e.eventDateTime = (datetime)evTime;
e.eventDate = TimeToString((datetime)evTime, TIME_DATE);
e.eventTime = TimeToString((datetime)evTime, TIME_MINUTES);
e.currency = cur;
e.event = evName;
e.importance = imp;
e.hasActual = (hasA != 0);
e.actual = act;
e.hasForecast = (hasF != 0);
e.forecast = fcast;
e.hasPrevious = (hasP != 0);
e.previous = prev;
e.hasRevised = (hasR != 0);
e.revisedPrevious = revPrev;
e.unit = unitV;
e.multiplier = multV;
e.digits = digV;
//--- Append populated struct to the event array
ArrayResize(g_news_allEvents, idx + 1);
g_news_allEvents[idx] = e;
idx++;
}
DatabaseFinalize(stmt);
if(debugLogging) Print("News_DbLoadEventsForWindow: loaded ", idx, " events");
return idx;
}
//+------------------------------------------------------------------+
//| Prune events older than keepDays to prevent database bloat |
//+------------------------------------------------------------------+
void News_DbPruneOldEvents(int keepDays = 7)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return;
//--- Compute cutoff timestamp and delete rows older than it
const long cutoff = (long)TimeCurrent() - (long)keepDays * 86400;
int stmt = DatabasePrepare(g_news_db,
"DELETE FROM events WHERE event_time < ?;");
if(stmt == INVALID_HANDLE) return;
DatabaseBind(stmt, 0, cutoff);
DatabaseRead(stmt);
DatabaseFinalize(stmt);
}
//+------------------------------------------------------------------+
//| Persist a triggered event ID to DB for restart safety |
//+------------------------------------------------------------------+
bool News_DbMarkTriggered(long evId)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return false;
//--- Insert event ID with current timestamp; ignore duplicate
int stmt = DatabasePrepare(g_news_db,
"INSERT OR IGNORE INTO triggered (event_id,triggered_at) VALUES (?,?);");
if(stmt == INVALID_HANDLE) return false;
DatabaseBind(stmt, 0, evId);
DatabaseBind(stmt, 1, (long)TimeCurrent());
const bool ok = DatabaseRead(stmt);
DatabaseFinalize(stmt);
return ok;
}
//+------------------------------------------------------------------+
//| Load triggered event IDs from last 24h into g_news_triggeredIds[]|
//+------------------------------------------------------------------+
void News_DbLoadTriggered()
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return;
//--- Query triggered IDs from the past 24 hours
const long cutoff = (long)TimeCurrent() - 86400;
int stmt = DatabasePrepare(g_news_db,
"SELECT event_id FROM triggered WHERE triggered_at >= ?;");
if(stmt == INVALID_HANDLE) return;
DatabaseBind(stmt, 0, cutoff);
//--- Populate g_news_triggeredIds array from query results
ArrayResize(g_news_triggeredIds, 0);
int n = 0;
while(DatabaseRead(stmt))
{
long id = 0;
DatabaseColumnLong(stmt, 0, id);
ArrayResize(g_news_triggeredIds, n + 1);
g_news_triggeredIds[n++] = id;
}
DatabaseFinalize(stmt);
if(n > 0 && debugLogging)
Print("News_DbLoadTriggered: restored ", n, " triggered IDs");
}
//+------------------------------------------------------------------+
//| Check if event ID was already triggered (memory array fast path) |
//+------------------------------------------------------------------+
bool News_DbAlreadyTriggered(long evId)
{
//--- Linear scan of the in-memory triggered IDs array
const int n = ArraySize(g_news_triggeredIds);
for(int i = 0; i < n; i++)
if(g_news_triggeredIds[i] == evId) return true;
return false;
}
//+------------------------------------------------------------------+
//| Check if database already has events for a given time window |
//+------------------------------------------------------------------+
bool News_DbHasDataForWindow(datetime startDt, datetime endDt)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE) return false;
//--- Count rows in the requested window
int stmt = DatabasePrepare(g_news_db,
"SELECT COUNT(*) FROM events WHERE event_time >= ? AND event_time <= ?;");
if(stmt == INVALID_HANDLE) return false;
DatabaseBind(stmt, 0, (long)startDt);
DatabaseBind(stmt, 1, (long)endDt);
int cnt = 0;
if(DatabaseRead(stmt)) DatabaseColumnInteger(stmt, 0, cnt);
DatabaseFinalize(stmt);
return (cnt > 0);
}
//+------------------------------------------------------------------+
//| Draw one frame of the download progress bar onto canvas |
//+------------------------------------------------------------------+
void News_DbDrawProgressBar(CCanvas &canv, int pbW, int pbH,
int pct, int saved, int total)
{
//--- Clear canvas to transparent
canv.Erase(0x00000000);
//--- Draw outer rounded pill background using theme colors
News_FillRoundRect(canv, 0, 0, pbW, pbH, 10, ColorToARGB(g_news_bg, 255));
News_DrawRoundRectBorder(canv, 0, 0, pbW, pbH, 10, 1,
ColorToARGB(g_news_borderAccent, 255));
//--- Stamp centered title text with current progress counts
canv.FontSet("Arial Bold", -100);
canv.TextOut(pbW / 2, 7,
"Downloading News Events: " + IntegerToString(pct) +
"% (" + IntegerToString(saved) + " / " + IntegerToString(total) + ")",
ColorToARGB(g_news_titleText, 255), TA_CENTER | TA_TOP);
//--- Draw the track capsule background below the title
const int padX = 14;
const int trackL = padX;
const int trackR = pbW - padX;
const int trackW = trackR - trackL;
const int trackY = pbH - 18;
const int barH = 10;
News_FillRoundRect(canv, trackL, trackY, trackW, barH, barH / 2,
ColorToARGB(g_news_panelAlt, 255));
News_DrawRoundRectBorder(canv, trackL, trackY, trackW, barH, barH / 2, 1,
ColorToARGB(g_news_border, 255));
//--- Fill the track from center outward proportional to percent complete
const int fillW = (int)(trackW * pct / 100.0);
const int fillL = trackL + (trackW - fillW) / 2;
if(fillW > 0)
News_FillRoundRect(canv, fillL, trackY, fillW, barH, barH / 2,
ColorToARGB(g_news_accent, 255));
//--- Push canvas frame to chart
canv.Update();
ChartRedraw(0);
}
//+------------------------------------------------------------------+
//| Download calendar data for date range and save to database |
//+------------------------------------------------------------------+
void News_DbDownloadAndSave(datetime startDt, datetime endDt)
{
//--- Abort if database is not open
if(g_news_db == INVALID_HANDLE)
{
Print("News_DbDownloadAndSave: DB not open - aborting");
return;
}
Print(">>> News DB: Fetching calendar events from ",
TimeToString(startDt), " to ", TimeToString(endDt), " ...");
//--- Query all raw calendar values in the requested time window
MqlCalendarValue values[];
const int total = CalendarValueHistory(values, startDt, endDt, NULL, NULL);
if(total <= 0)
{
Print(">>> News DB: No events found for specified range");
return;
}
Print(">>> News DB: ", total, " raw calendar records found - saving to DB...");
//--- Create progress bar canvas centered at the top of the chart
const int chartW = (int)ChartGetInteger(0, CHART_WIDTH_IN_PIXELS);
const int PB_W = 420;
const int PB_H = 46;
const int PB_X = (chartW - PB_W) / 2;
const int PB_Y = 20;
const string PB_NAME = "NewsDB_ProgressCanvas";
CCanvas pbCanv;
if(pbCanv.CreateBitmapLabel(PB_NAME, PB_X, PB_Y, PB_W, PB_H,
COLOR_FORMAT_ARGB_NORMALIZE))
{
//--- Configure progress bar canvas object properties
ObjectSetInteger(0, PB_NAME, OBJPROP_SELECTABLE, false);
ObjectSetInteger(0, PB_NAME, OBJPROP_HIDDEN, true);
ObjectSetInteger(0, PB_NAME, OBJPROP_ZORDER, 2000);
}
//--- Draw initial 0% progress frame
News_DbDrawProgressBar(pbCanv, PB_W, PB_H, 0, 0, total);
//--- Begin transaction, iterate all values, upsert each event record
DatabaseTransactionBegin(g_news_db);
const datetime updatedAt = TimeCurrent();
int saved = 0;
for(int i = 0; i < total; i++)
{
//--- Fetch associated event metadata and country info
MqlCalendarEvent ev;
if(!CalendarEventById(values[i].event_id, ev)) continue;
MqlCalendarCountry ctry;
CalendarCountryById(ev.country_id, ctry);
MqlCalendarValue val;
CalendarValueById(values[i].id, val);
//--- Populate NewsEvent struct from calendar API data
NewsEvent ne;
ne.eventDate = TimeToString(values[i].time, TIME_DATE);
ne.eventTime = TimeToString(values[i].time, TIME_MINUTES);
ne.currency = ctry.currency;
ne.event = ev.name;
ne.importance = News_GetImpactLabel(ev.importance);
ne.hasActual = val.HasActualValue();
ne.hasForecast = val.HasForecastValue();
ne.hasPrevious = val.HasPreviousValue();
ne.hasRevised = val.HasRevisedValue();
ne.actual = val.HasActualValue() ? val.GetActualValue() : 0.0;
ne.forecast = val.HasForecastValue() ? val.GetForecastValue() : 0.0;
ne.previous = val.HasPreviousValue() ? val.GetPreviousValue() : 0.0;
ne.revisedPrevious = val.HasRevisedValue() ? val.GetRevisedValue() : 0.0;
ne.unit = (int)ev.unit;
ne.multiplier = (int)ev.multiplier;
ne.digits = (int)ev.digits;
ne.eventDateTime = values[i].time;
ne.eventId = (long)values[i].event_id;
//--- Upsert the event into the database
News_DbUpsertEvent((long)values[i].id, ne, updatedAt);
saved++;
//--- Refresh progress bar every 5 records to reduce redraw overhead
if(i % 5 == 0 || i == total - 1)
{
const int pct = (int)((i + 1) * 100.0 / total);
News_DbDrawProgressBar(pbCanv, PB_W, PB_H, pct, saved, total);
}
}
//--- Commit all upserts as a single transaction
DatabaseTransactionCommit(g_news_db);
//--- Hold at 100% briefly so the user sees completion before canvas disappears
News_DbDrawProgressBar(pbCanv, PB_W, PB_H, 100, saved, total);
Sleep(800);
//--- Destroy progress bar canvas and clean up the chart object
pbCanv.Destroy();
ObjectDelete(0, PB_NAME);
ChartRedraw(0);
Print(">>> News DB: Download complete. ", saved, " events saved to ", NEWS_DB_PATH,
" for range ", TimeToString(startDt), " to ", TimeToString(endDt),
". Use it for offline testing...");
//--- Open the DB file to log its current size on disk
ResetLastError();
int fh = FileOpen(NEWS_DB_PATH,
FILE_READ | FILE_BIN | FILE_COMMON | FILE_SHARE_READ | FILE_SHARE_WRITE);
if(fh == INVALID_HANDLE)
{
Print(">>> News DB: Could not open DB file to read size (error ", GetLastError(), ")");
}
else
{
//--- Format byte count into KB or MB for readability
const ulong dbBytes = FileSize(fh);
FileClose(fh);
string sizeStr;
if(dbBytes >= 1048576)
sizeStr = DoubleToString((double)dbBytes / 1048576.0, 2) + " MB";
else if(dbBytes >= 1024)
sizeStr = DoubleToString((double)dbBytes / 1024.0, 1) + " KB";
else
sizeStr = IntegerToString((long)dbBytes) + " bytes";
Print(">>> News DB: Database file size: ", dbBytes, " bytes (", sizeStr, ")...");
}
}
#endif // NEWS_DATABASE_MQH
Reference in a new issue View git blame Copy permalink