Contents
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.
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:
Attachment 1
that corresponds to the
DICOM instance itself, and Attachment 3
that corresponds to the
tags before the pixel data).libsqliteodbc
Debian/Ubuntu package because the latter package
was compiled with the HAVE_SQLITE3LOADEXTENSION
flag.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 |
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.Pre-compiled binaries for Microsoft Windows 32bit are also available. A package for Apple’s Mac OS X is available courtesy of Osimis.
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
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:
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).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.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.
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.
Install the ODBC driver for SQL server (version 2017).
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
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'
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.
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.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:
odbc-postgresql
package.Create the following sample unixODBC configuration file:
$ cat ~/.odbc.ini
[orthanctest]
Driver = PostgreSQL Unicode
Servername = localhost
Database = orthanctest
UserName = postgres
Password = postgres
Port = 5432
Start Orthanc using the following configuration file for ODBC:
{
"Odbc" : {
"EnableIndex" : true,
"EnableStorage" : true,
"IndexConnectionString" : "DSN=orthanctest",
"StorageConnectionString" : "DSN=orthanctest"
}
}
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
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.
libsqliteodbc
package.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.
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.
Several advanced options are available as well to fine-tune the configuration of the ODBC plugins. They are documented below.
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.