--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Odbc/OdbcDatabase.cpp	Tue Aug 10 20:08:53 2021 +0200
@@ -0,0 +1,635 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2021 Osimis S.A., Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#include "OdbcDatabase.h"
+
+#include "../Common/ImplicitTransaction.h"
+#include "../Common/RetryDatabaseFactory.h"
+#include "../Common/Utf8StringValue.h"
+#include "OdbcPreparedStatement.h"
+#include "OdbcResult.h"
+
+#include <Logging.h>
+#include <OrthancException.h>
+#include <Toolbox.h>
+
+#include <boost/algorithm/string/predicate.hpp>
+#include <sqlext.h>
+
+
+namespace OrthancDatabases
+{
+  static void SetAutoCommitTransaction(SQLHDBC handle,
+                                       bool autocommit)
+  {
+    // Go to autocommit mode
+    SQLPOINTER value = (SQLPOINTER) (autocommit ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF);
+      
+    if (!SQL_SUCCEEDED(SQLSetConnectAttr(handle, SQL_ATTR_AUTOCOMMIT, value, SQL_IS_UINTEGER)))
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                      "Cannot switch the autocommit mode");
+    }
+  }
+    
+
+  class OdbcDatabase::OdbcImplicitTransaction : public ImplicitTransaction
+  {
+  private:
+    OdbcDatabase&  db_;
+
+  protected:
+    virtual IResult* ExecuteInternal(IPrecompiledStatement& statement,
+                                     const Dictionary& parameters) ORTHANC_OVERRIDE
+    {
+      return dynamic_cast<OdbcPreparedStatement&>(statement).Execute(parameters);
+    }
+
+    virtual void ExecuteWithoutResultInternal(IPrecompiledStatement& statement,
+                                              const Dictionary& parameters) ORTHANC_OVERRIDE
+    {
+      std::unique_ptr<IResult> result(Execute(statement, parameters));
+    }
+      
+  public:
+    OdbcImplicitTransaction(OdbcDatabase& db) :
+      db_(db)
+    {
+      SetAutoCommitTransaction(db_.GetHandle(), true);
+    }
+
+    virtual bool DoesTableExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return db_.DoesTableExist(name.c_str());
+    }
+
+    virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
+    virtual void ExecuteMultiLines(const std::string& query) ORTHANC_OVERRIDE
+    {
+      db_.ExecuteMultiLines(query);
+    }
+  };
+    
+    
+  class OdbcDatabase::OdbcExplicitTransaction : public ITransaction
+  {
+  private:
+    OdbcDatabase&  db_;
+    bool           isOpen_;
+
+    void EndTransaction(SQLSMALLINT completionType)
+    {
+      if (!isOpen_)
+      {
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls, "Transaction is already finalized");
+      }
+      else if (SQL_SUCCEEDED(SQLEndTran(SQL_HANDLE_DBC, db_.GetHandle(), completionType)))
+      {
+        isOpen_ = false;
+      }
+      else
+      {
+        SQLCHAR stateBuf[SQL_SQLSTATE_SIZE + 1];
+        SQLSMALLINT stateLength = 0;
+      
+        const SQLSMALLINT recNum = 1;
+        
+        if (SQL_SUCCEEDED(SQLGetDiagField(SQL_HANDLE_DBC, db_.GetHandle(),
+                                          recNum, SQL_DIAG_SQLSTATE, &stateBuf, sizeof(stateBuf), &stateLength)))
+        {
+          const std::string state(reinterpret_cast<const char*>(stateBuf));
+
+          if (state == "40001")
+          {
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseCannotSerialize);
+          }
+        }
+        
+        switch (completionType)
+        {
+          case SQL_COMMIT:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Cannot commit transaction");
+
+          case SQL_ROLLBACK:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Cannot rollback transaction");
+
+          default:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+        }
+      }
+    }
+      
+  public:
+    OdbcExplicitTransaction(OdbcDatabase& db) :
+      db_(db),
+      isOpen_(true)
+    {
+      SetAutoCommitTransaction(db_.GetHandle(), false);
+    }
+
+    virtual ~OdbcExplicitTransaction()
+    {
+      if (isOpen_)
+      {
+        LOG(INFO) << "An active ODBC transaction was dismissed";
+        if (!SQL_SUCCEEDED(SQLEndTran(SQL_HANDLE_DBC, db_.GetHandle(), SQL_ROLLBACK)))
+        {
+          LOG(ERROR) << "Cannot rollback transaction";
+        }
+      }
+    }
+
+    virtual bool IsImplicit() const ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
+    virtual void Commit() ORTHANC_OVERRIDE
+    {
+      EndTransaction(SQL_COMMIT);
+    }
+
+    virtual void Rollback() ORTHANC_OVERRIDE
+    {
+      EndTransaction(SQL_ROLLBACK);
+    }
+
+    virtual bool DoesTableExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return db_.DoesTableExist(name.c_str());
+    }
+
+    virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
+    virtual void ExecuteMultiLines(const std::string& query) ORTHANC_OVERRIDE
+    {
+      db_.ExecuteMultiLines(query);
+    }
+
+    virtual IResult* Execute(IPrecompiledStatement& statement,
+                             const Dictionary& parameters) ORTHANC_OVERRIDE
+    {
+      return dynamic_cast<OdbcPreparedStatement&>(statement).Execute(parameters);
+    }
+
+    virtual void ExecuteWithoutResult(IPrecompiledStatement& statement,
+                                      const Dictionary& parameters) ORTHANC_OVERRIDE
+    {
+      std::unique_ptr<IResult> result(Execute(statement, parameters));
+    }
+  };
+
+
+  static bool ParseThreePartsVersion(unsigned int& majorVersion,
+                                     const std::string& version)
+  {
+    std::vector<std::string> tokens;
+    Orthanc::Toolbox::TokenizeString(tokens, version, '.');
+
+    try
+    {
+      if (tokens.size() == 3u)
+      {
+        int tmp = boost::lexical_cast<int>(tokens[0]);
+        if (tmp >= 0)
+        {
+          majorVersion = static_cast<unsigned int>(tmp);
+          return true;
+        }
+      }
+    }
+    catch (boost::bad_lexical_cast&)
+    {
+    }
+
+    return false;
+  }
+  
+    
+  OdbcDatabase::OdbcDatabase(OdbcEnvironment& environment,
+                             const std::string& connectionString) :
+    dbmsMajorVersion_(0)
+  {
+    LOG(INFO) << "Creating an ODBC connection: " << connectionString;
+      
+    /* Allocate a connection handle */
+    if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, environment.GetHandle(), &handle_)))
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseUnavailable,
+                                      "Cannot create ODBC connection");
+    }
+
+    /* Connect to the DSN mydsn */
+    SQLCHAR* tmp = const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>(connectionString.c_str()));
+    SQLCHAR outBuffer[2048];
+    SQLSMALLINT outSize = 0;
+
+    bool success = true;
+      
+    if (SQL_SUCCEEDED(SQLDriverConnect(handle_, NULL, tmp, SQL_NTS /* null-terminated string */,
+                                       outBuffer, sizeof(outBuffer), &outSize, SQL_DRIVER_COMPLETE)))
+    {
+      LOG(INFO) << "Returned connection string: " << outBuffer;        
+    }
+    else
+    {
+      success = false;
+    }
+
+    if (!SQL_SUCCEEDED(SQLSetConnectAttr(handle_, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER) SQL_TXN_SERIALIZABLE, SQL_NTS)))
+    {
+      /**
+       * Switch to the "serializable" isolation level that is expected
+       * by Orthanc. This is already the default for MySQL and MSSQL,
+       * but is needed for PostgreSQL.
+       * https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels
+       **/
+      success = false;
+    }
+
+    SQLCHAR versionBuffer[2048];
+    SQLSMALLINT versionSize;
+
+    if (success &&
+        SQL_SUCCEEDED(SQLGetInfo(handle_, SQL_DBMS_NAME, outBuffer, sizeof(outBuffer) - 1, &outSize)) &&
+        SQL_SUCCEEDED(SQLGetInfo(handle_, SQL_DBMS_VER, versionBuffer, sizeof(versionBuffer) - 1, &versionSize)))
+    {
+      std::string dbms(reinterpret_cast<const char*>(outBuffer), outSize);
+      std::string version(reinterpret_cast<const char*>(versionBuffer), versionSize);
+
+      LOG(WARNING) << "DBMS Name: " << dbms;
+      LOG(WARNING) << "DBMS Version: " << version;
+        
+      if (dbms == "PostgreSQL")
+      {
+        dialect_ = Dialect_PostgreSQL;
+      }
+      else if (dbms == "SQLite")
+      {
+        dialect_ = Dialect_SQLite;
+        ExecuteMultiLines("PRAGMA FOREIGN_KEYS=ON");  // Necessary for cascaded delete to work
+        ExecuteMultiLines("PRAGMA ENCODING=\"UTF-8\"");
+
+        // The following lines speed up SQLite
+        
+        /*ExecuteMultiLines("PRAGMA SYNCHRONOUS=NORMAL;");
+          ExecuteMultiLines("PRAGMA JOURNAL_MODE=WAL;");
+          ExecuteMultiLines("PRAGMA LOCKING_MODE=EXCLUSIVE;");
+          ExecuteMultiLines("PRAGMA WAL_AUTOCHECKPOINT=1000;");*/
+      }
+      else if (dbms == "MySQL")
+      {
+        dialect_ = Dialect_MySQL;
+
+        if (!ParseThreePartsVersion(dbmsMajorVersion_, version))
+        {
+          SQLFreeHandle(SQL_HANDLE_DBC, handle_);
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Cannot parse the version of MySQL: " + version);
+        }
+      }
+      else if (dbms == "Microsoft SQL Server")
+      {
+        dialect_ = Dialect_MSSQL;
+
+        if (!ParseThreePartsVersion(dbmsMajorVersion_, version))
+        {
+          SQLFreeHandle(SQL_HANDLE_DBC, handle_);
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Cannot parse the version of SQL Server: " + version);
+        }
+      }
+      else
+      {
+        SQLFreeHandle(SQL_HANDLE_DBC, handle_);
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Unknown SQL dialect for DBMS: " + dbms);
+      }
+    }
+    else
+    {
+      success = false;
+    }
+
+    if (!success)
+    {
+      std::string error = FormatError();
+      SQLFreeHandle(SQL_HANDLE_DBC, handle_); // Cannot call FormatError() below this point
+        
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseUnavailable, "Error in SQLDriverConnect():\n" + error);
+    }
+  }
+
+    
+  OdbcDatabase::~OdbcDatabase()
+  {
+    LOG(INFO) << "Destructing an ODBC connection";
+      
+    if (!SQL_SUCCEEDED(SQLDisconnect(handle_)))
+    {
+      LOG(ERROR) << "Cannot disconnect from driver";
+    }
+      
+    if (!SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_DBC, handle_)))
+    {
+      LOG(ERROR) << "Cannot destruct the ODBC connection";
+    }
+  }
+
+
+  std::string OdbcDatabase::FormatError()
+  {
+    return OdbcEnvironment::FormatError(handle_, SQL_HANDLE_DBC);
+  }
+
+
+  void OdbcDatabase::ListTables(std::set<std::string>& target)
+  {
+    target.clear();
+
+    OdbcStatement statement(GetHandle());
+
+    if (SQL_SUCCEEDED(SQLTables(statement.GetHandle(), NULL, 0, NULL, 0, NULL, 0,
+                                const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>("'TABLE'")), SQL_NTS)))
+    {
+      OdbcResult result(statement, dialect_);
+
+      while (!result.IsDone())
+      {
+        if (result.GetFieldsCount() < 5)
+        {
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Invalid result for SQLTables()");
+        }
+        else
+        {
+          if (result.GetField(2).GetType() == ValueType_Utf8String &&
+              result.GetField(3).GetType() == ValueType_Utf8String &&
+              dynamic_cast<const Utf8StringValue&>(result.GetField(3)).GetContent() == "TABLE")
+          {
+            std::string name = dynamic_cast<const Utf8StringValue&>(result.GetField(2)).GetContent();
+            Orthanc::Toolbox::ToLowerCase(name);
+            target.insert(name);
+          }
+        }
+
+        result.Next();
+      }
+    }
+  }
+
+
+  bool OdbcDatabase::DoesTableExist(const std::string& name)
+  {
+    std::set<std::string> tables;
+    ListTables(tables);
+    return (tables.find(name) != tables.end());
+  }
+
+  
+  void OdbcDatabase::ExecuteMultiLines(const std::string& query)
+  {
+    OdbcStatement statement(GetHandle());
+
+    std::vector<std::string> lines;
+    Orthanc::Toolbox::TokenizeString(lines, query, ';');
+      
+    for (size_t i = 0; i < lines.size(); i++)
+    {
+      std::string line = Orthanc::Toolbox::StripSpaces(lines[i]);
+      if (!line.empty())
+      {
+        LOG(INFO) << "Running ODBC SQL: " << line;
+        SQLCHAR* tmp = const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>(line.c_str()));
+
+        SQLRETURN code = SQLExecDirect(statement.GetHandle(), tmp, SQL_NTS);
+
+        if (code != SQL_NO_DATA &&
+            code != SQL_SUCCESS &&
+            code != SQL_SUCCESS_WITH_INFO)
+        {
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                          "Cannot execute multi-line SQL:\n" + statement.FormatError());
+        }
+      }
+    }
+  }
+
+
+  IPrecompiledStatement* OdbcDatabase::Compile(const Query& query)
+  {
+    return new OdbcPreparedStatement(GetHandle(), GetDialect(), query);
+  }
+
+    
+  ITransaction* OdbcDatabase::CreateTransaction(TransactionType type)
+  {
+    /**
+     * In ODBC, there is no "START TRANSACTION". A transaction is
+     * automatically created with each connection, and the "READ
+     * ONLY" status can only be set at the statement level
+     * (cf. SQL_CONCUR_READ_ONLY). One can only control the
+     * autocommit: https://stackoverflow.com/a/35351267/881731
+     **/
+    switch (type)
+    {
+      case TransactionType_Implicit:
+        return new OdbcImplicitTransaction(*this);
+          
+      case TransactionType_ReadWrite:
+      case TransactionType_ReadOnly:
+        return new OdbcExplicitTransaction(*this);
+          
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+    }
+  }
+
+
+  unsigned int OdbcDatabase::GetDbmsMajorVersion() const
+  {
+    return dbmsMajorVersion_;
+  }    
+
+
+  IDatabaseFactory* OdbcDatabase::CreateDatabaseFactory(unsigned int maxConnectionRetries,
+                                                        unsigned int connectionRetryInterval,
+                                                        const std::string& connectionString,
+                                                        bool checkEncodings)
+  {
+    class Factory : public RetryDatabaseFactory
+    {
+    private:
+      OdbcEnvironment environment_;
+      std::string     connectionString_;
+      bool            checkEncodings_;
+
+      bool LookupConnectionOption(std::string& value,
+                                  const std::string& option) const
+      {
+        std::vector<std::string> tokens;
+        Orthanc::Toolbox::TokenizeString(tokens, connectionString_, ';');
+
+        for (size_t i = 0; i < tokens.size(); i++)
+        {
+          if (boost::starts_with(tokens[i], option + "="))
+          {
+            value = tokens[i];
+            return true;
+          }
+        }
+
+        return false;
+      }
+
+      
+      void CheckMSSQLEncodings(OdbcDatabase& db)
+      {
+        // https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server
+        if (db.GetDbmsMajorVersion() <= 14)
+        {
+          // Microsoft SQL Server up to 2017
+
+          std::string value;
+          if (LookupConnectionOption(value, "AutoTranslate"))
+          {
+            if (value != "AutoTranslate=no")
+            {
+              LOG(WARNING) << "For UTF-8 to work properly, it is strongly advised to set \"AutoTranslate=no\" in the "
+                           << "ODBC connection string when connecting to Microsoft SQL Server with version <= 2017";
+            }
+          }
+          else
+          {
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                            "Your Microsoft SQL Server has version <= 2017, and thus doesn't support UTF-8; "
+                                            "Please upgrade or add \"AutoTranslate=no\" to your ODBC connection string");
+          }
+        }
+        else
+        {
+          std::string value;
+          if (LookupConnectionOption(value, "AutoTranslate") &&
+              value != "AutoTranslate=yes")
+          {
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                            "Your Microsoft SQL Server has version >= 2019, and thus fully supports UTF-8; "
+                                            "Please set \"AutoTranslate=yes\" in your ODBC connection string");
+          }
+        }
+      }
+
+
+      void CheckMySQLEncodings(OdbcDatabase& db)
+      {
+        // https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html
+
+        std::string value;
+        if (LookupConnectionOption(value, "charset"))
+        {
+          if (value != "charset=utf8")
+          {
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                            "For compatibility with UTF-8 in Orthanc, your connection string to MySQL "
+                                            "must *not* set the \"charset\" option to another value than \"utf8\"");
+          }
+        }
+        else if (db.GetDbmsMajorVersion() < 8)
+        {
+          // MySQL up to 5.7
+          LOG(WARNING) << "It is advised to set the \"charset=utf8\" option in your connection string if using MySQL <= 5.7";
+        }
+        else
+        {
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
+                                          "For compatibility with UTF-8 in Orthanc, your connection string to MySQL >= 8.0 "
+                                          "*must* set the \"charset=utf8\" in your connection string");
+        }
+      }
+      
+
+    protected:
+      IDatabase* TryOpen()
+      {
+        std::unique_ptr<OdbcDatabase> db(new OdbcDatabase(environment_, connectionString_));
+
+        if (checkEncodings_)
+        {
+          switch (db->GetDialect())
+          {
+            case Dialect_MSSQL:
+              CheckMSSQLEncodings(*db);
+              break;
+
+            case Dialect_MySQL:
+              CheckMySQLEncodings(*db);
+              break;
+
+            case Dialect_SQLite:
+            case Dialect_PostgreSQL:
+              // Nothing specific to be checked wrt. encodings
+              break;
+
+            default:
+              throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+          }
+        }
+
+        if (db->GetDbmsMajorVersion() >= 15)
+        {
+          /**
+           * SQL Server 2019 introduces support for UTF-8. Note that
+           * "ALTER" cannot be run inside a transaction, and must be
+           * done *before* the creation of the tables.
+           * https://docs.microsoft.com/en-US/sql/relational-databases/collations/collation-and-unicode-support#utf8
+           *
+           * Furthermore, this call must be done by both
+           * "odbc-index" and "odbc-storage" plugins, because
+           * altering collation is an operation that requires
+           * exclusive lock: If "odbc-storage" is the first plugin
+           * to be loaded and doesn't set the UTF-8 collation,
+           * "odbc-index" cannot start because it doesn't have
+           * exclusive access.
+           **/
+          db->ExecuteMultiLines("ALTER DATABASE CURRENT COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8");
+        }
+        
+        return db.release();
+      }
+      
+    public:
+      Factory(unsigned int maxConnectionRetries,
+              unsigned int connectionRetryInterval,
+              const std::string& connectionString,
+              bool checkEncodings) :
+        RetryDatabaseFactory(maxConnectionRetries, connectionRetryInterval),
+        connectionString_(connectionString),
+        checkEncodings_(checkEncodings)
+      {
+      }
+    };
+
+    return new Factory(maxConnectionRetries, connectionRetryInterval, connectionString, checkEncodings);
+  }
+}