ODBC plugins

Contents

• ODBC plugins

  • When to use ODBC?

  • Compilation

    • Static linking

    • Microsoft Windows and Apple OS X

    • Dynamic linking on Ubuntu 16.04

  • Usage

  • Supported ODBC drivers

    • Microsoft SQL Server

    • PostgreSQL

    • MySQL

    • SQLite

  • Advanced options

    • Multiple writers or connections

The Orthanc project provides two official plugins to replace the default storage area (on the filesystem) and the default SQLite index by a ODBC database. This can notably be used to connect Orthanc to Microsoft SQL Server.

The source code of the ODBC plugins can be found in the orthanc-databases Mercurial repository, next to the source code of the PostgreSQL and MySQL/MariaDB plugins.

When to use ODBC?

In general, you’ll always get better performance by using native C/C++ plugins dedicated to one single database engine, instead of the ODBC plugins that can connect to any database driver. This is the price of genericity: Some specific optimisations can only be done if focusing on one single database.

That being said, there are multiple use cases for the ODBC plugins:

• Connection to Microsoft SQL Server (MSSQL), including Microsoft Azure SQL, is only possible with ODBC. Note that the ODBC plugins were only validated against MSSQL 2017 and MSSQL 2019, under GNU/Linux.

• Contrarily to the built-in SQLite engine and to the MySQL/MariaDB index plugin, the ODBC index plugin supports the revision mechanism to protect metadata and attachments from concurrent modifications.

• Because of its genericity, the ODBC storage area plugin does not implement the read-range primitive. As a consequence, using it will write two attachments for each stored DICOM instance (namely, Attachment 1 that corresponds to the DICOM instance itself, and Attachment 3 that corresponds to the tags before the pixel data).

• The Linux Standard Base (LSB) pre-compiled binaries of the ODBC plugins are not compatible with the libsqliteodbc Debian/Ubuntu package because the latter package was compiled with the HAVE_SQLITE3LOADEXTENSION flag.

• Finally, make sure to read the recommended setup for best scalability.

Very importantly, pay attention to the fact that the ODBC plugins use a different database schema than the built-in SQLite driver, and than the PostgreSQL/MariaDB/MySQL plugins. As a consequence, it is not possible to switch back and forth between ODBC and the native drivers without running a full replication procedure. As a consequence, pay attention to choose the right plugin from the beginning, as you will need to stick to it.

Summarizing, here are two tables containing our recommendations about when to use the ODBC plugins:

Database management system	Recommended index plugin
Microsoft SQL server (MSSQL) or Microsoft Azure SQL	ODBC plugin
MySQL (with revisions)	ODBC plugin
MySQL (without revisions)	MySQL plugin
PostgreSQL	PostgreSQL plugin
SQLite (with revisions)	ODBC plugin
SQLite (without revisions)	No plugin needed
Other	Create a dedicated plugin or implement a new dialect in the ODBC plugins

Type of storage area	Recommended storage plugin
Filesystem	No plugin needed
Microsoft SQL server (MSSQL) or Microsoft Azure SQL	ODBC plugin
MySQL	MySQL plugin
PostgreSQL	PostgreSQL plugin
SQLite	ODBC plugin
Google Cloud Storage, Azure blob storage, AWS S3	Cloud object storage plugins
Other	Create a dedicated plugin, implement a new dialect in the ODBC plugins, or prototype using Python

Compilation

Static linking

The procedure to compile these plugins is similar to that for the core of Orthanc. The following commands should work for most UNIX-like distribution (including GNU/Linux):

$ mkdir BuildOdbc
$ cd BuildOdbc
$ cmake ../Odbc -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release
$ make

The compilation will produce 2 shared libraries, each containing one plugin for Orthanc:

• OrthancOdbcIndex replaces the default SQLite index of Orthanc by ODBC.

• OrthancOdbcStorage makes Orthanc store the DICOM files it receives into ODBC.

Microsoft Windows and Apple OS X

Pre-compiled binaries for Microsoft Windows 32bit are also available, as well as for Apple macOS.

Dynamic linking on Ubuntu 16.04

If static linking is not desired, here are build instructions for Ubuntu 16.04 (provided build dependencies for the core of Orthanc have already been installed):

$ sudo apt-get install libodbc1 unixodbc unixodbc-dev
$ mkdir BuildOdbc
$ cd BuildOdbc
$ cmake ../Odbc -DCMAKE_BUILD_TYPE=Release \
                -DALLOW_DOWNLOADS=ON \
                -DUSE_SYSTEM_GOOGLE_TEST=OFF \
                -DUSE_SYSTEM_ORTHANC_SDK=OFF
$ make

Usage

You of course first have to install Orthanc, with a version above 0.9.5. You then have to configure an ODBC data source dedicated to Orthanc. The procedure depends upon your operating system:

• Many UNIX-like platforms (including Debian and Ubuntu) use unixODBC. You first have to install at least one ODBC driver (e.g. on Debian, installing the packages libsqliteodbc and odbc-postgresql will respectively install the driver for SQLite and for PostgreSQL). Secondly, you have to edit your ~/.odbc.ini to define the data sources (i.e. the actual databases).

• On Microsoft Windows, the configuration tool odbcad32.exe (“ODBC Data Source Administrator”) allows to define the data sources. You also have to install at least one ODBC driver. For instance, the SQLite ODBC Driver can be used to access SQLite.

• If you are interested in interfacing Orthanc with Microsoft SQL Server, the corresponding ODBC drivers can be downloaded from Microsoft.

Once Orthanc is installed and the data sources have been defined, you must add a section in the configuration file that specifies the data source(s) to be used. You also have to tell Orthanc in which path it can find the plugins: This is done by properly modifying the Plugins option. You could for instance adapt the following configuration file:

{
  "Name" : "MyOrthanc",
  "Odbc" : {
    "EnableIndex" : true,
    "EnableStorage" : true,
    "IndexConnectionString" : "DSN=index",
    "StorageConnectionString" : "DSN=storage",
    "MaximumConnectionRetries" : 10,
    "ConnectionRetryInterval" : 5,
    "IndexConnectionsCount" : 1
  },
  "Plugins" : [
    "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcIndex.so",
    "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcStorage.so"
  ]
}

The values of IndexConnectionString and StorageConnectionString are known as ODBC connection strings, and define how to connect to the ODBC data source. These connection strings are specific to the different types of ODBC drivers. In the following sections, we’ll review connection strings for SQLite, PostgreSQL, MySQL and Microsoft SQL Server.

Important: The EnableIndex and EnableStorage options must be explicitly set to true, otherwise Orthanc will continue to use its default SQLite back-end and the filesystem storage area.

Remark 1: When using the ODBC storage area plugin, the DICOM files are stored as large objects in the database. This might actually consume more space than the DICOM file itself.

Remark 2: A typical usage of the ODBC plugins is to enable only the index plugin, and to use the default filesystem storage for DICOM files (on a NAS with proper disaster recovery strategies).

Orthanc must of course be restarted after the modification of its configuration file.

Supported ODBC drivers

The ODBC plugins for Orthanc are universal, in the sense that they can connect to any ODBC driver. However, there are some minor variations in the SQL language, that are known as “dialects” in the source code of the plugins.

As of ODBC plugins 1.0, the supported dialects are Microsoft SQL Server, PostgreSQL, MySQL and SQLite. Orthanc auto-detects the dialect to be used. Adapting the ODBC plugins to support more dialects should be fairly easy by adding new values to the OrthancDatabases::Dialect enumeration in the C++ source code.

Also, note that the database for the index and the database for the storage area can mix different type of ODBC drivers.

We now review sample connection strings for the supported ODBC drivers under Ubuntu 18.04.

Microsoft SQL Server

1. Install the ODBC driver for SQL server (version 2017).

2. A non-persistent developer instance of MSSQL 2019 can be started using the Docker image provided by Microsoft as follows:

   $ docker run --name mssql --rm -t -i -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=MyStrOngPa55word!' \
     -e MSSQL_MEMORY_LIMIT_MB=512 -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
   

3. Create a database dedicated to Orthanc in MSSQL:

   $ /opt/mssql-tools/bin/sqlcmd -S 192.168.0.17 -U sa -P 'MyStrOngPa55word!' -Q 'CREATE DATABASE orthanctest'
   

4. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver = ODBC Driver 17 for SQL Server
   Server = tcp:localhost,1433
   Database = orthanctest
   

   Note that there exist many more configuration options for Microsoft SQL Server. In particular, Encrypt and TrustServerCertificate and Connect Timeout can be interesting in the case of a connection to Microsoft Azure SQL.

5. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!",
       "StorageConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!"
     }
   }
   

   In the connection strings:

   • DSN corresponds to the name of the entry in ~/.odbc.ini.

   • Uid is the user name in MSSQL (by default, the Docker image uses sa).

   • Pwd is the password that has been provided in the SA_PASSWORD environment variable when starting Docker.

   • For security reasons, the Uid and Pwd parameters cannot be set in ~/.odbc.ini.

Remark: It is actually not necessary to create an entry in ~/.odbc.ini. All the parameters can indeed be provided directly in the connection strings, for instance:

{
  "Odbc" : {
    "EnableIndex" : true,
    "EnableStorage" : true,
    "IndexConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!",
    "StorageConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!"
  }
}

Remark: On Windows systems, we have noticed that the ODBC drivers character encoding seems to depend on a system level configuration. This configuration needs to enforce UTF-8. Therefore, it is advised to configure the system locale as follow:

PostgreSQL

1. Install the odbc-postgresql package.

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver      = PostgreSQL Unicode
   Servername  = localhost
   Database    = orthanctest
   UserName    = postgres
   Password    = postgres
   Port        = 5432
   

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest",
       "StorageConnectionString" : "DSN=orthanctest"
     }
   }
   

MySQL

1. Install the official Connect/ODBC package (it is not packaged for Ubuntu 18.04).

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver      = MySQL ODBC 8.0 Unicode Driver
   Servername  = localhost
   Database    = orthanctest
   UID         = root
   PWD         = root
   Port        = 3306
   

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest;charset=utf8",
       "StorageConnectionString" : "DSN=orthanctest;charset=utf8"
     }
   }
   

   The charset=utf8 option is necessary if using MySQL 8.x.

SQLite

1. Install the libsqliteodbc package.

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [index]
   Driver=SQLite3
   Database=/tmp/test-odbc-index.sqlite
   
   [storage]
   Driver=SQLite3
   Database=/tmp/test-odbc-storage.sqlite
   

   Note that we define two different data sources, one for the index and another for the storage area, because a SQLite database can only be opened by one client at once.

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=index",
       "StorageConnectionString" : "DSN=storage",
       "IndexConnectionsCount" : 1
     }
   }
   

   Remark 1: As written just above, one SQLite database should only be opened by one client at a time. This implies that the IndexConnectionsCount must be set to 1, and that the index and storage area must never have connection strings corresponding to the same SQLite database.

   Remark 2: As written above, the ODBC plugin supports the revision mechanism. This contrasts with the built-in SQLite database of Orthanc. So, it might be interesting to use the ODBC index plugin instead of the built-in SQLite database of Orthanc, if you are a developer who wants to test revisions before a large-scale deployment.

Advanced options

Several advanced options are available as well to fine-tune the configuration of the ODBC plugins. They are documented below.

Multiple writers or connections

Starting with Orthanc 1.9.2, it is possible to use multiple writers or connections in large-scale deployments. Here is the list of configuration that control this behaviour:

• MaximumConnectionRetries governs how many times Orthanc tries to connect to the database, as well as how many times Orthanc replays transactions to deal with collisions between multiple writers.

• IndexConnectionsCount controls the number of connections from the index plugin to the ODBC database. It is set to 1 by default, which corresponds to the old behaviour of Orthanc <= 1.9.1.

• ConnectionRetryInterval is only used when opening one database connection to ODBC.

• These options cannot be used in the case of SQLite databases, that only support one client at once.