在 MQL5 中实现其他语言的实用模块（第 01 部分）：构建受 Python 启发的 SQLite3 库（基础篇）

class="macro">#include <Windows.h>
HINSTANCE hSQLite = LoadLibraryW(L"sqlite3.dll");
typedef class="type">int (WINAPI *SQLITE3_OPEN)(LPCWSTR, class="type">void**);
typedef class="type">int (WINAPI *SQLITE3_EXEC)(class="type">void*, LPCSTR, class="type">void*, class="type">void*, class="type">char**);
typedef class="type">int (WINAPI *SQLITE3_CLOSE)(class="type">void*);
SQLITE3_OPEN sqlite3_open = (SQLITE3_OPEN)GetProcAddress(hSQLite, "sqlite3_open16");
SQLITE3_EXEC sqlite3_exec = (SQLITE3_EXEC)GetProcAddress(hSQLite, "sqlite3_exec");
SQLITE3_CLOSE sqlite3_close = (SQLITE3_CLOSE)GetProcAddress(hSQLite, "sqlite3_close");
class="type">void *db = NULL;
sqlite3_open(L"trade_log.db", &db);
sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS ticks(symbol TEXT, price REAL)", NULL, NULL, NULL);
sqlite3_close(db);

class="kw">import sqlite3
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE
    )
""") # Execute a query
try:    
    cursor.execute("INSERT INTO users(name, age, email) VALUES(?, ?, ?)",
                   ("Bruh", class="num">30, "bruh@example.com"))
    conn.commit() # commit the transaction | save the new information to a database
    
except sqlite3.DatabaseError as e:
    print("Insert failed:", e)
conn.close() # closing the database
class="type">void OnStart()
  {
class=class="str">"cmt">//---
   
   class="type">int db_handle = DatabaseOpen("example.db", DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE);
   if (db_handle == INVALID_HANDLE)
     {
       printf("Failed to open a database. Error = %s",ErrorDescription(GetLastError()));
       class="kw">return;
     }
    
    class="type">class="kw">string sql =      
       "  CREATE TABLE IF NOT EXISTS users("
       "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
       "  name TEXT NOT NULL,"
       "  age INTEGER,"
       "  email TEXT UNIQUE"
       ")";
      
    if (!DatabaseExecute(db_handle, sql)) class=class="str">"cmt">//Execute a sql query
     {
       printf("Failed to execute a query to the database. Error = %s",ErrorDescription(GetLastError()));
       class="kw">return;
     }  
    
    if (!DatabaseTransactionBegin(db_handle)) class=class="str">"cmt">//Begin the transaction
     {
       printf("Failed to begin a transaction. Error = %s",ErrorDescription(GetLastError()));
       class="kw">return;
     }  
    
    sql = "INSERT INTO users(name, age, email) VALUES(&class="macro">#x27;Bruh&class="macro">#x27;, class="num">30, &class="macro">#x27;bruh@example.com&class="macro">#x27;)";
    
    if (!DatabaseExecute(db_handle, sql)) class=class="str">"cmt">//Execute a query
     {
       printf("Failed to execute a query to the database. Error = %s",ErrorDescription(GetLastError()));
       class="kw">return;
     }
   
   if (!DatabaseTransactionCommit(db_handle)) class=class="str">"cmt">//Commit the transaction | push the changes to a database
     {
       printf("Failed to commit a transaction. Error = %s",ErrorDescription(GetLastError()));

      class="kw">return;
      }
   
   DatabaseClose(db_handle); class=class="str">"cmt">//Close the database
}

class="kw">import sqlite3
con = sqlite3.connect("example.db")
class CSqlite3
  {
class="kw">protected:
   class="type">int m_request;
   
class="kw">public:
                       class="type">int m_db_handle;
 class=class="str">"cmt">//... Other functions
 }
class="type">bool CSqlite3::connect(const class="type">class="kw">string db_name, const class="type">bool common=false, const class="type">bool database_in_memory=false)
  {  
   class="type">int flags = DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE;
   if (common) class=class="str">"cmt">//Open the database from the common folder
      flags |= DATABASE_OPEN_COMMON;
   if (database_in_memory) class=class="str">"cmt">//Open the database in memory 
      flags |= DATABASE_OPEN_MEMORY;
   m_db_handle = DatabaseOpen(db_name, flags);
  
class=class="str">"cmt">//---
  
   if (m_db_handle == INVALID_HANDLE)
     {
       printf("func=%s line=%d, Failed to open a database. Error = %s",__FUNCTION__, __LINE__, ErrorDescription(GetLastError()));
       class="kw">return false;
     }
      
   class="kw">return true;
 }

execute_res_structure CSqlite3::execute(const class="type">class="kw">string sql)
{
  execute_res_structure res;  
  class="type">class="kw">string trimmed_sql = sql;
  StringTrimLeft(trimmed_sql); class=class="str">"cmt">//Trim the leading white space
  
  class="type">class="kw">string first_word = trimmed_sql;
  
  class=class="str">"cmt">// Find the index of the first space(to isolate the first SQL keyword)
  class="type">int space_index = StringFind(trimmed_sql, " ");
  if (space_index != -class="num">1)
    first_word = StringSubstr(trimmed_sql, class="num">0, space_index); class=class="str">"cmt">// Extract the first word from the query
  
  StringToUpper(first_word); class=class="str">"cmt">//Convert the first word in the query to uppercase for comparison
  
  if (first_word == "SELECT")
    {
      class=class="str">"cmt">// SELECT query – prepare and expect data 
      m_request = DatabasePrepare(m_db_handle, sql);
      res.request = m_request;
      
      class="kw">return res;
    }
  else
    {
      class=class="str">"cmt">// INSERT/UPDATE/DELETE – execute directly
      if (!m_transaction_active)
        {
          if (!this.begin())
            class="kw">return res;
          else
            m_transaction_active_auto = true; class=class="str">"cmt">//The transaction was started automatically by the execute function
        }
      
      ResetLastError();              
      if (!DatabaseExecute(m_db_handle, sql))
        {
          printf("func=%s line=%d, Failed to execute a query to the database. Error = %s",__FUNCTION__,__LINE__,ErrorDescription(GetLastError()));
        }
    }