Mercurial > hg > orthanc-book
annotate Sphinx/source/plugins/odbc.rst @ 996:d43d74afdfad
logs-no-thread
author | Alain Mazy <am@osimis.io> |
---|---|
date | Wed, 06 Dec 2023 16:31:42 +0100 |
parents | 05b106383b2a |
children | 17bf6fafb884 |
rev | line source |
---|---|
750 | 1 .. _odbc: |
2 | |
3 | |
4 ODBC plugins | |
5 ============ | |
6 | |
7 .. contents:: | |
8 | |
9 The Orthanc project provides two **official** plugins to replace the | |
10 default storage area (on the filesystem) and the default SQLite index | |
11 by a `ODBC database | |
12 <https://en.wikipedia.org/wiki/Open_Database_Connectivity>`__. This | |
13 can notably be used to connect Orthanc to Microsoft SQL Server. | |
14 | |
15 The source code of the ODBC plugins can be found in the | |
16 ``orthanc-databases`` `Mercurial repository | |
991
1316bc62b5d5
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
789
diff
changeset
|
17 <https://orthanc.uclouvain.be/hg/orthanc-databases/>`__, next to the |
750 | 18 source code of the :ref:`PostgreSQL <postgresql>` and |
19 :ref:`MySQL/MariaDB <mysql>` plugins. | |
20 | |
21 | |
22 When to use ODBC? | |
23 ----------------- | |
24 | |
25 In general, you'll always get better performance by using native C/C++ | |
26 plugins dedicated to one single database engine, instead of the ODBC | |
27 plugins that can connect to any database driver. This is the price of | |
28 genericity: Some specific optimisations can only be done if focusing | |
29 on one single database. | |
30 | |
31 That being said, there are multiple use cases for the ODBC plugins: | |
32 | |
751 | 33 * Connection to Microsoft SQL Server (MSSQL), including Microsoft |
34 Azure SQL, is only possible with ODBC. Note that the ODBC plugins | |
35 were only validated against MSSQL 2017 and MSSQL 2019, under | |
36 GNU/Linux. | |
750 | 37 |
38 * Contrarily to the built-in SQLite engine and to the MySQL/MariaDB | |
39 index plugin, the ODBC index plugin supports the :ref:`revision | |
40 mechanism <revisions>` to protect metadata and attachments from | |
41 concurrent modifications. | |
42 | |
43 * Because of its genericity, the ODBC storage area plugin does not | |
44 implement the :ref:`read-range primitive <registry>`. As a | |
45 consequence, using it will write two attachments for each stored | |
46 DICOM instance (namely, ``Attachment 1`` that corresponds to the | |
47 DICOM instance itself, and ``Attachment 3`` that corresponds to the | |
48 tags before the pixel data). | |
49 | |
50 * The Linux Standard Base (LSB) `pre-compiled binaries | |
993
05b106383b2a
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
991
diff
changeset
|
51 <https://orthanc.uclouvain.be/downloads/linux-standard-base/orthanc-odbc/index.html>`__ of the ODBC plugins |
750 | 52 are not compatible with the ``libsqliteodbc`` `Debian/Ubuntu package |
53 <http://www.ch-werner.de/sqliteodbc/>`__ because the latter package | |
54 was compiled with the ``HAVE_SQLITE3LOADEXTENSION`` flag. | |
55 | |
56 * Finally, make sure to read the :ref:`recommended setup for best | |
57 scalability <scalability>`. | |
58 | |
59 Very importantly, pay attention to the fact that the ODBC plugins use | |
60 a different database schema than the built-in SQLite driver, and than | |
61 the PostgreSQL/MariaDB/MySQL plugins. As a consequence, it is **not | |
62 possible to switch back and forth** between ODBC and the native | |
63 drivers without running a :ref:`full replication procedure | |
64 <replication>`. As a consequence, pay attention to choose the right | |
65 plugin from the beginning, as you will need to stick to it. | |
66 | |
67 Summarizing, here are two tables containing our recommendations about | |
68 when to use the ODBC plugins: | |
69 | |
70 +------------------------------+--------------------------------------------------------+ | |
71 | Database management system | Recommended index plugin | | |
72 +==============================+========================================================+ | |
73 | Microsoft SQL server (MSSQL) | ODBC plugin | | |
751 | 74 | or Microsoft Azure SQL | | |
750 | 75 +------------------------------+--------------------------------------------------------+ |
76 | MySQL (with revisions) | ODBC plugin | | |
77 +------------------------------+--------------------------------------------------------+ | |
78 | MySQL (without revisions) | :ref:`MySQL plugin <mysql>` | | |
79 +------------------------------+--------------------------------------------------------+ | |
80 | PostgreSQL | :ref:`PostgreSQL plugin <postgresql>` | | |
81 +------------------------------+--------------------------------------------------------+ | |
82 | SQLite (with revisions) | ODBC plugin | | |
83 +------------------------------+--------------------------------------------------------+ | |
84 | SQLite (without revisions) | No plugin needed | | |
85 +------------------------------+--------------------------------------------------------+ | |
86 | Other | Create a :ref:`dedicated plugin <creating-plugins>` | | |
87 | | or implement a new dialect in the ODBC plugins | | |
88 +------------------------------+--------------------------------------------------------+ | |
89 | |
90 | |
91 +------------------------------+--------------------------------------------------------+ | |
92 | Type of storage area | Recommended storage plugin | | |
93 +==============================+========================================================+ | |
94 | Filesystem | No plugin needed | | |
95 +------------------------------+--------------------------------------------------------+ | |
96 | Microsoft SQL server (MSSQL) | ODBC plugin | | |
751 | 97 | or Microsoft Azure SQL | | |
750 | 98 +------------------------------+--------------------------------------------------------+ |
99 | MySQL | :ref:`MySQL plugin <mysql>` | | |
100 +------------------------------+--------------------------------------------------------+ | |
101 | PostgreSQL | :ref:`PostgreSQL plugin <postgresql>` | | |
102 +------------------------------+--------------------------------------------------------+ | |
103 | SQLite | ODBC plugin | | |
104 +------------------------------+--------------------------------------------------------+ | |
105 | Google Cloud Storage, Azure | :ref:`Cloud object storage plugins <object-storage>` | | |
106 | blob storage, AWS S3 | | | |
107 +------------------------------+--------------------------------------------------------+ | |
108 | Other | Create a :ref:`dedicated plugin <creating-plugins>`, | | |
109 | | implement a new dialect in the ODBC plugins, | | |
110 | | or prototype using :ref:`Python <python_storage_area>` | | |
111 +------------------------------+--------------------------------------------------------+ | |
112 | |
113 | |
114 Compilation | |
115 ----------- | |
116 | |
117 Static linking | |
118 ^^^^^^^^^^^^^^ | |
119 | |
120 .. highlight:: text | |
121 | |
122 The procedure to compile these plugins is similar to that for the | |
123 :ref:`core of Orthanc <compiling>`. The following commands should work | |
124 for most UNIX-like distribution (including GNU/Linux):: | |
125 | |
126 $ mkdir BuildOdbc | |
127 $ cd BuildOdbc | |
128 $ cmake ../Odbc -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release | |
129 $ make | |
130 | |
131 The compilation will produce 2 shared libraries, each containing one plugin for Orthanc: | |
132 | |
133 * ``OrthancOdbcIndex`` replaces the default SQLite index of Orthanc by ODBC. | |
134 * ``OrthancOdbcStorage`` makes Orthanc store the DICOM files it receives into ODBC. | |
135 | |
136 | |
137 Microsoft Windows and Apple OS X | |
138 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
139 | |
140 Pre-compiled binaries for Microsoft Windows 32bit `are also available | |
993
05b106383b2a
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
991
diff
changeset
|
141 <https://orthanc.uclouvain.be/downloads/windows-32/orthanc-odbc/index.html>`__. A |
750 | 142 package for `Apple's Mac OS X |
143 <https://www.osimis.io/en/download.html>`__ is available courtesy of | |
144 `Osimis <https://www.osimis.io/>`__. | |
145 | |
146 | |
147 .. _odbc-ubuntu1604: | |
148 | |
149 Dynamic linking on Ubuntu 16.04 | |
150 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
151 | |
152 .. highlight:: text | |
153 | |
154 If static linking is not desired, here are build instructions for | |
155 Ubuntu 16.04 (provided build dependencies for the :ref:`core of | |
156 Orthanc <compiling>` have already been installed):: | |
157 | |
158 $ sudo apt-get install libodbc1 unixodbc unixodbc-dev | |
159 $ mkdir BuildOdbc | |
160 $ cd BuildOdbc | |
161 $ cmake ../Odbc -DCMAKE_BUILD_TYPE=Release \ | |
162 -DALLOW_DOWNLOADS=ON \ | |
163 -DUSE_SYSTEM_GOOGLE_TEST=OFF \ | |
164 -DUSE_SYSTEM_ORTHANC_SDK=OFF | |
165 $ make | |
166 | |
167 | |
168 | |
169 Usage | |
170 ----- | |
171 | |
172 You of course first have to :ref:`install Orthanc <binaries>`, with a | |
173 version above 0.9.5. You then have to **configure an ODBC data | |
174 source** dedicated to Orthanc. The procedure depends upon your | |
175 operating system: | |
176 | |
177 * Many UNIX-like platforms (including Debian and Ubuntu) use `unixODBC | |
178 <https://en.wikipedia.org/wiki/UnixODBC>`__. You first have to | |
179 install at least one ODBC driver (e.g. on Debian, installing the | |
180 packages ``libsqliteodbc`` and ``odbc-postgresql`` will respectively | |
181 install the driver for SQLite and for PostgreSQL). Secondly, you | |
182 have to edit your ``~/.odbc.ini`` to define the data sources | |
183 (i.e. the actual databases). | |
184 | |
185 * On Microsoft Windows, the configuration tool ``odbcad32.exe`` ("ODBC | |
186 Data Source Administrator") allows to define the data sources. You | |
187 also have to install at least one ODBC driver. For instance, the | |
188 `SQLite ODBC Driver <http://www.ch-werner.de/sqliteodbc/>`__ can be | |
189 used to access SQLite. | |
190 | |
191 * If you are interested in interfacing Orthanc with Microsoft SQL | |
192 Server, the corresponding ODBC drivers can be `downloaded from | |
193 Microsoft | |
194 <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server>`__. | |
195 | |
751 | 196 .. highlight:: json |
197 | |
750 | 198 Once Orthanc is installed and the data sources have been defined, you |
199 must add a section in the :ref:`configuration file <configuration>` | |
200 that specifies the **data source(s) to be used**. You also have to | |
201 tell Orthanc in which path it can find the plugins: This is done by | |
202 properly modifying the ``Plugins`` option. You could for instance | |
203 adapt the following configuration file:: | |
204 | |
205 { | |
206 "Name" : "MyOrthanc", | |
207 "Odbc" : { | |
208 "EnableIndex" : true, | |
209 "EnableStorage" : true, | |
210 "IndexConnectionString" : "DSN=index", | |
211 "StorageConnectionString" : "DSN=storage", | |
751 | 212 "MaximumConnectionRetries" : 10, |
213 "ConnectionRetryInterval" : 5, | |
214 "IndexConnectionsCount" : 1 | |
750 | 215 }, |
216 "Plugins" : [ | |
217 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcIndex.so", | |
218 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcStorage.so" | |
219 ] | |
220 } | |
221 | |
222 The values of ``IndexConnectionString`` and | |
223 ``StorageConnectionString`` are known as `ODBC connection strings | |
224 <https://www.connectionstrings.com/>`__, and define how to connect to | |
225 the ODBC data source. These connection strings are specific to the | |
226 different types of ODBC drivers. In the following sections, we'll | |
227 review connection strings for SQLite, PostgreSQL, MySQL and Microsoft | |
228 SQL Server. | |
229 | |
230 **Important:** The ``EnableIndex`` and ``EnableStorage`` options must | |
231 be explicitly set to ``true``, otherwise Orthanc will continue to use | |
232 its default SQLite back-end and the filesystem storage area. | |
233 | |
234 **Remark 1:** When using the ODBC storage area plugin, the DICOM files | |
235 are stored as large objects in the database. This might actually | |
236 consume more space than the DICOM file itself. | |
237 | |
238 **Remark 2:** A typical usage of the ODBC plugins is to enable only | |
239 the index plugin, and to use the default filesystem storage for DICOM | |
240 files (on a NAS with proper disaster recovery strategies). | |
241 | |
242 Orthanc must of course be **restarted** after the modification of its | |
243 configuration file. | |
244 | |
245 | |
246 Supported ODBC drivers | |
247 ---------------------- | |
248 | |
249 The ODBC plugins for Orthanc are universal, in the sense that they can | |
250 connect to any ODBC driver. However, there are some minor variations | |
251 in the SQL language, that are known as "dialects" in the `source code | |
991
1316bc62b5d5
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
789
diff
changeset
|
252 of the plugins <https://orthanc.uclouvain.be/hg/orthanc-databases/>`__. |
750 | 253 |
254 As of ODBC plugins 1.0, the supported dialects are Microsoft SQL | |
255 Server, PostgreSQL, MySQL and SQLite. Orthanc auto-detects the dialect | |
256 to be used. Adapting the ODBC plugins to support more dialects should | |
257 be fairly easy by adding new values to the | |
258 ``OrthancDatabases::Dialect`` enumeration in the C++ source code. | |
259 | |
260 Also, note that the database for the index and the database for the | |
261 storage area can mix different type of ODBC drivers. | |
262 | |
263 We now review sample `connection strings | |
264 <https://www.connectionstrings.com/>`__ for the supported ODBC drivers | |
265 under Ubuntu 18.04. | |
266 | |
267 | |
268 Microsoft SQL Server | |
269 ^^^^^^^^^^^^^^^^^^^^ | |
270 | |
271 .. highlight:: bash | |
272 | |
273 1. Install the `ODBC driver for SQL server | |
274 <https://docs.microsoft.com/fr-fr/sql/connect/odbc/download-odbc-driver-for-sql-server>`__ | |
275 (version 2017). | |
276 | |
277 2. A **non-persistent** developer instance of MSSQL 2019 can be | |
278 started using the `Docker image provided by Microsoft | |
279 <https://hub.docker.com/_/microsoft-mssql-server>`__ as follows:: | |
280 | |
281 $ docker run --name mssql --rm -t -i -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=MyStrOngPa55word!' \ | |
282 -e MSSQL_MEMORY_LIMIT_MB=512 -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest | |
283 | |
284 3. Create a database dedicated to Orthanc in MSSQL:: | |
285 | |
286 $ /opt/mssql-tools/bin/sqlcmd -S 192.168.0.17 -U sa -P 'MyStrOngPa55word!' -Q 'CREATE DATABASE orthanctest' | |
287 | |
288 .. highlight:: text | |
289 | |
290 4. Create the following sample `unixODBC | |
291 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | |
292 | |
293 $ cat ~/.odbc.ini | |
294 [orthanctest] | |
295 Driver = ODBC Driver 17 for SQL Server | |
296 Server = tcp:localhost,1433 | |
297 Database = orthanctest | |
298 | |
299 Note that there exist `many more configuration options | |
300 <https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client>`__ | |
751 | 301 for Microsoft SQL Server. In particular, ``Encrypt`` and |
302 ``TrustServerCertificate`` and ``Connect Timeout`` can be | |
303 interesting in the case of a connection to Microsoft Azure SQL. | |
750 | 304 |
305 .. highlight:: json | |
306 | |
307 5. Start Orthanc using the following :ref:`configuration file | |
308 <configuration>` for ODBC:: | |
309 | |
310 { | |
311 "Odbc" : { | |
312 "EnableIndex" : true, | |
313 "EnableStorage" : true, | |
314 "IndexConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!", | |
315 "StorageConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!" | |
316 } | |
317 } | |
318 | |
319 In the connection strings: | |
320 | |
321 * ``DSN`` corresponds to the name of the entry in ``~/.odbc.ini``. | |
322 * ``Uid`` is the user name in MSSQL (by default, the Docker image uses ``sa``). | |
751 | 323 * ``Pwd`` is the password that has been provided in the ``SA_PASSWORD`` |
324 environment variable when starting Docker. | |
750 | 325 * For security reasons, the ``Uid`` and ``Pwd`` parameters cannot |
326 be set in ``~/.odbc.ini``. | |
751 | 327 |
328 **Remark:** It is actually not necessary to create an entry in | |
329 ``~/.odbc.ini``. All the parameters can indeed be provided directly | |
330 in the connection strings, for instance:: | |
331 | |
332 { | |
333 "Odbc" : { | |
334 "EnableIndex" : true, | |
335 "EnableStorage" : true, | |
336 "IndexConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!", | |
337 "StorageConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!" | |
338 } | |
339 } | |
340 | |
789 | 341 **Remark:** On Windows systems, we have noticed that the ODBC drivers character encoding |
342 seems to depend on a system level configuration. This configuration needs to enforce UTF-8. | |
343 Therefore, it is advised to configure the system locale as follow: | |
344 | |
345 .. image:: ../images/odbc-windows-system-locale.png | |
346 :align: center | |
347 :width: 600px | |
348 | |
349 | | |
751 | 350 |
750 | 351 PostgreSQL |
352 ^^^^^^^^^^ | |
353 | |
354 1. Install the ``odbc-postgresql`` package. | |
355 | |
751 | 356 .. highlight:: text |
357 | |
750 | 358 2. Create the following sample `unixODBC |
359 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | |
360 | |
361 $ cat ~/.odbc.ini | |
362 [orthanctest] | |
363 Driver = PostgreSQL Unicode | |
364 Servername = localhost | |
365 Database = orthanctest | |
366 UserName = postgres | |
367 Password = postgres | |
368 Port = 5432 | |
369 | |
751 | 370 .. highlight:: json |
371 | |
750 | 372 3. Start Orthanc using the following :ref:`configuration file |
373 <configuration>` for ODBC:: | |
374 | |
375 { | |
376 "Odbc" : { | |
377 "EnableIndex" : true, | |
378 "EnableStorage" : true, | |
379 "IndexConnectionString" : "DSN=orthanctest", | |
380 "StorageConnectionString" : "DSN=orthanctest" | |
381 } | |
382 } | |
383 | |
384 | |
385 MySQL | |
386 ^^^^^ | |
387 | |
388 1. Install the official `Connect/ODBC package | |
389 <https://dev.mysql.com/downloads/connector/odbc/>`__ (it is not | |
390 packaged for Ubuntu 18.04). | |
391 | |
751 | 392 .. highlight:: text |
393 | |
750 | 394 2. Create the following sample `unixODBC |
395 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | |
396 | |
397 $ cat ~/.odbc.ini | |
398 [orthanctest] | |
399 Driver = MySQL ODBC 8.0 Unicode Driver | |
400 Servername = localhost | |
401 Database = orthanctest | |
402 UID = root | |
403 PWD = root | |
404 Port = 3306 | |
405 | |
751 | 406 .. highlight:: json |
407 | |
750 | 408 3. Start Orthanc using the following :ref:`configuration file |
409 <configuration>` for ODBC:: | |
410 | |
411 { | |
412 "Odbc" : { | |
413 "EnableIndex" : true, | |
414 "EnableStorage" : true, | |
415 "IndexConnectionString" : "DSN=orthanctest;charset=utf8", | |
416 "StorageConnectionString" : "DSN=orthanctest;charset=utf8" | |
417 } | |
418 } | |
419 | |
420 The ``charset=utf8`` option is necessary if using MySQL 8.x. | |
421 | |
422 | |
423 SQLite | |
424 ^^^^^^ | |
425 | |
426 1. Install the ``libsqliteodbc`` package. | |
427 | |
751 | 428 .. highlight:: text |
429 | |
750 | 430 2. Create the following sample `unixODBC |
431 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | |
432 | |
433 $ cat ~/.odbc.ini | |
434 [index] | |
435 Driver=SQLite3 | |
436 Database=/tmp/test-odbc-index.sqlite | |
437 | |
438 [storage] | |
439 Driver=SQLite3 | |
440 Database=/tmp/test-odbc-storage.sqlite | |
441 | |
442 Note that we define two different data sources, one for the index | |
443 and another for the storage area, because a SQLite database can | |
444 only be opened by one client at once. | |
445 | |
751 | 446 .. highlight:: json |
447 | |
750 | 448 3. Start Orthanc using the following :ref:`configuration file |
449 <configuration>` for ODBC:: | |
450 | |
451 { | |
452 "Odbc" : { | |
453 "EnableIndex" : true, | |
454 "EnableStorage" : true, | |
455 "IndexConnectionString" : "DSN=index", | |
456 "StorageConnectionString" : "DSN=storage", | |
457 "IndexConnectionsCount" : 1 | |
458 } | |
459 } | |
460 | |
461 **Remark 1:** As written just above, one SQLite database should | |
462 only be opened by one client at a time. This implies that the | |
463 ``IndexConnectionsCount`` must be set to ``1``, and that the index | |
464 and storage area must never have connection strings corresponding | |
465 to the same SQLite database. | |
466 | |
467 **Remark 2:** As written above, the ODBC plugin supports the | |
468 :ref:`revision mechanism <revisions>`. This contrasts with the | |
469 built-in SQLite database of Orthanc. So, it might be interesting to | |
470 use the ODBC index plugin instead of the built-in SQLite database | |
471 of Orthanc, if you are a developer who wants to test revisions | |
472 before a :ref:`large-scale deployment <scalability>`. | |
473 | |
474 | |
475 Advanced options | |
476 ---------------- | |
477 | |
478 Several advanced options are available as well to fine-tune the | |
479 configuration of the ODBC plugins. They are documented below. | |
480 | |
481 | |
482 .. _odbc-multiple-writers: | |
483 | |
484 Multiple writers or connections | |
485 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
486 | |
487 Starting with Orthanc 1.9.2, it is possible to use :ref:`multiple | |
488 writers or connections in large-scale deployments | |
489 <multiple-writers>`. Here is the list of configuration that control | |
490 this behaviour: | |
491 | |
492 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to | |
493 connect to the database, as well as how many times Orthanc replays | |
494 transactions to deal with collisions between multiple writers. | |
495 | |
496 * ``IndexConnectionsCount`` controls the number of connections from | |
497 the index plugin to the ODBC database. It is set to ``1`` by | |
498 default, which corresponds to the old behaviour of Orthanc <= 1.9.1. | |
499 | |
500 * ``ConnectionRetryInterval`` is only used when opening one database | |
501 connection to ODBC. | |
502 | |
503 * These options cannot be used in the case of SQLite databases, that | |
504 only support one client at once. |