Mercurial > hg > orthanc-book

changeset 750:8b8df1be0cf9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
documenting odbc
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 13 Aug 2021 14:06:23 +0200
parents 4ae741e351ab
children 37512473ffe9
files Sphinx/source/faq/features.rst Sphinx/source/plugins.rst Sphinx/source/plugins/mysql.rst Sphinx/source/plugins/odbc.rst Sphinx/source/plugins/postgresql.rst
diffstat 5 files changed, 480 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/Sphinx/source/faq/features.rst	Fri Aug 13 09:33:00 2021 +0200
+++ b/Sphinx/source/faq/features.rst	Fri Aug 13 14:06:23 2021 +0200
@@ -420,8 +420,8 @@
 of the REST API of Orthanc for more information.
 
 **Warning:** The database index back-end must support revisions. As of
-writing, only the **PostgreSQL plugin** in versions above 4.0
-implement support for revisions.
+writing, only the **PostgreSQL plugins** in versions above 4.0 and the
+**ODBC plugins** implement support for revisions.
 
 
 Synchronous vs. asynchronous C-MOVE SCP
--- a/Sphinx/source/plugins.rst	Fri Aug 13 09:33:00 2021 +0200
+++ b/Sphinx/source/plugins.rst	Fri Aug 13 14:06:23 2021 +0200
@@ -66,6 +66,7 @@
    plugins/google-cloud-platform.rst    
    plugins/python.rst 
    plugins/object-storage.rst   
+   plugins/odbc.rst
    plugins/osimis-webviewer.rst
    plugins/authorization.rst
 
--- a/Sphinx/source/plugins/mysql.rst	Fri Aug 13 09:33:00 2021 +0200
+++ b/Sphinx/source/plugins/mysql.rst	Fri Aug 13 14:06:23 2021 +0200
@@ -16,6 +16,12 @@
 For information about scalability, make sure to read the section about
 :ref:`multiple writers in large-scale deployments <multiple-writers>`.
 
+The source code of the MySQL/MariaDB plugins can be found in the
+``orthanc-databases`` `Mercurial repository
+<https://hg.orthanc-server.com/orthanc-databases/>`__, next to the
+source code of the :ref:`ODBC <odbc>` and :ref:`PostgreSQL
+<postgresql>` plugins.
+
 **Warning:** According to `this thread on our discussion group
 <https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/Fb4ShaYMBAAJ>`__,
 the MySQL/MariaDB plugins require MySQL 8.x if running on Microsoft
@@ -49,7 +55,7 @@
 Microsoft Windows
 ^^^^^^^^^^^^^^^^^
 
-Pre-compiled binaries for Microsoft Windows `are also available
+Pre-compiled binaries for Microsoft Windows 32bit `are also available
 <https://www.orthanc-server.com/browse.php?path=/plugin-mysql>`__.
 
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Sphinx/source/plugins/odbc.rst	Fri Aug 13 14:06:23 2021 +0200
@@ -0,0 +1,464 @@
+.. _odbc:
+
+
+ODBC plugins
+============
+
+.. 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
+<https://en.wikipedia.org/wiki/Open_Database_Connectivity>`__. 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
+<https://hg.orthanc-server.com/orthanc-databases/>`__, next to the
+source code of the :ref:`PostgreSQL <postgresql>` and
+:ref:`MySQL/MariaDB <mysql>` 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) 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 :ref:`revision
+  mechanism <revisions>` to protect metadata and attachments from
+  concurrent modifications.
+
+* Because of its genericity, the ODBC storage area plugin does not
+  implement the :ref:`read-range primitive <registry>`. 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
+  <https://lsb.orthanc-server.com/plugin-odbc/>`__ of the ODBC plugins
+  are not compatible with the ``libsqliteodbc`` `Debian/Ubuntu package
+  <http://www.ch-werner.de/sqliteodbc/>`__ because the latter package
+  was compiled with the ``HAVE_SQLITE3LOADEXTENSION`` flag.
+  
+* Finally, make sure to read the :ref:`recommended setup for best
+  scalability <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 :ref:`full replication procedure
+<replication>`. 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) | ODBC plugin                                            |
++------------------------------+--------------------------------------------------------+
+| MySQL (with revisions)       | ODBC plugin                                            |
++------------------------------+--------------------------------------------------------+
+| MySQL (without revisions)    | :ref:`MySQL plugin <mysql>`                            |
++------------------------------+--------------------------------------------------------+
+| PostgreSQL                   | :ref:`PostgreSQL plugin <postgresql>`                  |
++------------------------------+--------------------------------------------------------+
+| SQLite (with revisions)      | ODBC plugin                                            |
++------------------------------+--------------------------------------------------------+
+| SQLite (without revisions)   | No plugin needed                                       |
++------------------------------+--------------------------------------------------------+
+| Other                        | Create a :ref:`dedicated plugin <creating-plugins>`    |
+|                              | or implement a new dialect in the ODBC plugins         |
++------------------------------+--------------------------------------------------------+
+
+
++------------------------------+--------------------------------------------------------+
+| Type of storage area         | Recommended storage plugin                             |
++==============================+========================================================+
+| Filesystem                   | No plugin needed                                       |
++------------------------------+--------------------------------------------------------+
+| Microsoft SQL server (MSSQL) | ODBC plugin                                            |
++------------------------------+--------------------------------------------------------+
+| MySQL                        | :ref:`MySQL plugin <mysql>`                            |
++------------------------------+--------------------------------------------------------+
+| PostgreSQL                   | :ref:`PostgreSQL plugin <postgresql>`                  |
++------------------------------+--------------------------------------------------------+
+| SQLite                       | ODBC plugin                                            |
++------------------------------+--------------------------------------------------------+
+| Google Cloud Storage, Azure  | :ref:`Cloud object storage plugins <object-storage>`   |
+| blob storage, AWS S3         |                                                        |
++------------------------------+--------------------------------------------------------+
+| Other                        | Create a :ref:`dedicated plugin <creating-plugins>`,   |
+|                              | implement a new dialect in the ODBC plugins,           |
+|                              | or prototype using :ref:`Python <python_storage_area>` |
++------------------------------+--------------------------------------------------------+
+
+
+Compilation
+-----------
+
+Static linking
+^^^^^^^^^^^^^^
+
+.. highlight:: text
+
+The procedure to compile these plugins is similar to that for the
+:ref:`core of Orthanc <compiling>`. 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
+<https://www.orthanc-server.com/browse.php?path=/plugin-odbc>`__. A
+package for `Apple's Mac OS X
+<https://www.osimis.io/en/download.html>`__ is available courtesy of
+`Osimis <https://www.osimis.io/>`__.
+
+
+.. _odbc-ubuntu1604:
+
+Dynamic linking on Ubuntu 16.04
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+.. highlight:: text
+
+If static linking is not desired, here are build instructions for
+Ubuntu 16.04 (provided build dependencies for the :ref:`core of
+Orthanc <compiling>` 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
+-----
+
+.. highlight:: json
+
+You of course first have to :ref:`install Orthanc <binaries>`, 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
+  <https://en.wikipedia.org/wiki/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 <http://www.ch-werner.de/sqliteodbc/>`__ 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
+  <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server>`__.
+
+Once Orthanc is installed and the data sources have been defined, you
+must add a section in the :ref:`configuration file <configuration>`
+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,  // Optional
+      "ConnectionRetryInterval" : 5,    // Optional
+      "IndexConnectionsCount" : 1       // Optional
+    },
+    "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
+<https://www.connectionstrings.com/>`__, 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 <https://hg.orthanc-server.com/orthanc-databases/>`__.
+
+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
+<https://www.connectionstrings.com/>`__ for the supported ODBC drivers
+under Ubuntu 18.04.
+
+
+Microsoft SQL Server
+^^^^^^^^^^^^^^^^^^^^
+
+.. highlight:: bash
+
+1. Install the `ODBC driver for SQL server
+   <https://docs.microsoft.com/fr-fr/sql/connect/odbc/download-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
+   <https://hub.docker.com/_/microsoft-mssql-server>`__ 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'
+
+.. highlight:: text
+
+4. Create the following sample `unixODBC
+   <https://en.wikipedia.org/wiki/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
+   <https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client>`__
+   for Microsoft SQL Server.
+     
+.. highlight:: json
+
+5. Start Orthanc using the following :ref:`configuration file
+   <configuration>` 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 used when starting Docker.
+   * For security reasons, the ``Uid`` and ``Pwd`` parameters cannot
+     be set in ``~/.odbc.ini``.
+     
+  
+PostgreSQL
+^^^^^^^^^^
+
+1. Install the ``odbc-postgresql`` package.
+
+2. Create the following sample `unixODBC
+   <https://en.wikipedia.org/wiki/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 :ref:`configuration file
+   <configuration>` for ODBC::
+     
+     {
+       "Odbc" : {
+         "EnableIndex" : true,
+         "EnableStorage" : true,
+         "IndexConnectionString" : "DSN=orthanctest",
+         "StorageConnectionString" : "DSN=orthanctest"
+       }
+     }
+   
+
+MySQL
+^^^^^
+
+1. Install the official `Connect/ODBC package
+   <https://dev.mysql.com/downloads/connector/odbc/>`__ (it is not
+   packaged for Ubuntu 18.04).
+
+2. Create the following sample `unixODBC
+   <https://en.wikipedia.org/wiki/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 :ref:`configuration file
+   <configuration>` 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
+   <https://en.wikipedia.org/wiki/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 :ref:`configuration file
+   <configuration>` 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
+   :ref:`revision mechanism <revisions>`. 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 :ref:`large-scale deployment <scalability>`.
+
+
+Advanced options
+----------------
+
+Several advanced options are available as well to fine-tune the
+configuration of the ODBC plugins. They are documented below.
+
+
+.. _odbc-multiple-writers:
+
+Multiple writers or connections
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Starting with Orthanc 1.9.2, it is possible to use :ref:`multiple
+writers or connections in large-scale deployments
+<multiple-writers>`. 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.
--- a/Sphinx/source/plugins/postgresql.rst	Fri Aug 13 09:33:00 2021 +0200
+++ b/Sphinx/source/plugins/postgresql.rst	Fri Aug 13 14:06:23 2021 +0200
@@ -16,6 +16,11 @@
 For information about scalability, make sure to read the section about
 :ref:`multiple writers in large-scale deployments <multiple-writers>`.
 
+The source code of the PostgreSQL plugins can be found in the
+``orthanc-databases`` `Mercurial repository
+<https://hg.orthanc-server.com/orthanc-databases/>`__, next to the
+source code of the :ref:`ODBC <odbc>` and
+:ref:`MySQL/MariaDB <mysql>` plugins.
 
 
 Compilation
@@ -44,7 +49,7 @@
 Microsoft Windows and Apple OS X
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
-Pre-compiled binaries for Microsoft Windows `are also available
+Pre-compiled binaries for Microsoft Windows 32bit `are also available
 <https://www.orthanc-server.com/browse.php?path=/plugin-postgresql>`__.
 A package for `Apple's Mac OS X
 <https://www.osimis.io/en/download.html>`__