Mercurial > hg > orthanc-book
comparison Sphinx/source/plugins/odbc.rst @ 751:37512473ffe9
improved odbc
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Fri, 13 Aug 2021 15:49:42 +0200 |
parents | 8b8df1be0cf9 |
children | 2f70f622059b |
comparison
equal
deleted
inserted
replaced
750:8b8df1be0cf9 | 751:37512473ffe9 |
---|---|
28 genericity: Some specific optimisations can only be done if focusing | 28 genericity: Some specific optimisations can only be done if focusing |
29 on one single database. | 29 on one single database. |
30 | 30 |
31 That being said, there are multiple use cases for the ODBC plugins: | 31 That being said, there are multiple use cases for the ODBC plugins: |
32 | 32 |
33 * Connection to Microsoft SQL Server (MSSQL) is only possible with | 33 * Connection to Microsoft SQL Server (MSSQL), including Microsoft |
34 ODBC. Note that the ODBC plugins were only validated against MSSQL | 34 Azure SQL, is only possible with ODBC. Note that the ODBC plugins |
35 2017 and MSSQL 2019, under GNU/Linux. | 35 were only validated against MSSQL 2017 and MSSQL 2019, under |
36 GNU/Linux. | |
36 | 37 |
37 * Contrarily to the built-in SQLite engine and to the MySQL/MariaDB | 38 * Contrarily to the built-in SQLite engine and to the MySQL/MariaDB |
38 index plugin, the ODBC index plugin supports the :ref:`revision | 39 index plugin, the ODBC index plugin supports the :ref:`revision |
39 mechanism <revisions>` to protect metadata and attachments from | 40 mechanism <revisions>` to protect metadata and attachments from |
40 concurrent modifications. | 41 concurrent modifications. |
68 | 69 |
69 +------------------------------+--------------------------------------------------------+ | 70 +------------------------------+--------------------------------------------------------+ |
70 | Database management system | Recommended index plugin | | 71 | Database management system | Recommended index plugin | |
71 +==============================+========================================================+ | 72 +==============================+========================================================+ |
72 | Microsoft SQL server (MSSQL) | ODBC plugin | | 73 | Microsoft SQL server (MSSQL) | ODBC plugin | |
74 | or Microsoft Azure SQL | | | |
73 +------------------------------+--------------------------------------------------------+ | 75 +------------------------------+--------------------------------------------------------+ |
74 | MySQL (with revisions) | ODBC plugin | | 76 | MySQL (with revisions) | ODBC plugin | |
75 +------------------------------+--------------------------------------------------------+ | 77 +------------------------------+--------------------------------------------------------+ |
76 | MySQL (without revisions) | :ref:`MySQL plugin <mysql>` | | 78 | MySQL (without revisions) | :ref:`MySQL plugin <mysql>` | |
77 +------------------------------+--------------------------------------------------------+ | 79 +------------------------------+--------------------------------------------------------+ |
90 | Type of storage area | Recommended storage plugin | | 92 | Type of storage area | Recommended storage plugin | |
91 +==============================+========================================================+ | 93 +==============================+========================================================+ |
92 | Filesystem | No plugin needed | | 94 | Filesystem | No plugin needed | |
93 +------------------------------+--------------------------------------------------------+ | 95 +------------------------------+--------------------------------------------------------+ |
94 | Microsoft SQL server (MSSQL) | ODBC plugin | | 96 | Microsoft SQL server (MSSQL) | ODBC plugin | |
97 | or Microsoft Azure SQL | | | |
95 +------------------------------+--------------------------------------------------------+ | 98 +------------------------------+--------------------------------------------------------+ |
96 | MySQL | :ref:`MySQL plugin <mysql>` | | 99 | MySQL | :ref:`MySQL plugin <mysql>` | |
97 +------------------------------+--------------------------------------------------------+ | 100 +------------------------------+--------------------------------------------------------+ |
98 | PostgreSQL | :ref:`PostgreSQL plugin <postgresql>` | | 101 | PostgreSQL | :ref:`PostgreSQL plugin <postgresql>` | |
99 +------------------------------+--------------------------------------------------------+ | 102 +------------------------------+--------------------------------------------------------+ |
164 | 167 |
165 | 168 |
166 Usage | 169 Usage |
167 ----- | 170 ----- |
168 | 171 |
169 .. highlight:: json | |
170 | |
171 You of course first have to :ref:`install Orthanc <binaries>`, with a | 172 You of course first have to :ref:`install Orthanc <binaries>`, with a |
172 version above 0.9.5. You then have to **configure an ODBC data | 173 version above 0.9.5. You then have to **configure an ODBC data |
173 source** dedicated to Orthanc. The procedure depends upon your | 174 source** dedicated to Orthanc. The procedure depends upon your |
174 operating system: | 175 operating system: |
175 | 176 |
190 * If you are interested in interfacing Orthanc with Microsoft SQL | 191 * If you are interested in interfacing Orthanc with Microsoft SQL |
191 Server, the corresponding ODBC drivers can be `downloaded from | 192 Server, the corresponding ODBC drivers can be `downloaded from |
192 Microsoft | 193 Microsoft |
193 <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server>`__. | 194 <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server>`__. |
194 | 195 |
196 .. highlight:: json | |
197 | |
195 Once Orthanc is installed and the data sources have been defined, you | 198 Once Orthanc is installed and the data sources have been defined, you |
196 must add a section in the :ref:`configuration file <configuration>` | 199 must add a section in the :ref:`configuration file <configuration>` |
197 that specifies the **data source(s) to be used**. You also have to | 200 that specifies the **data source(s) to be used**. You also have to |
198 tell Orthanc in which path it can find the plugins: This is done by | 201 tell Orthanc in which path it can find the plugins: This is done by |
199 properly modifying the ``Plugins`` option. You could for instance | 202 properly modifying the ``Plugins`` option. You could for instance |
204 "Odbc" : { | 207 "Odbc" : { |
205 "EnableIndex" : true, | 208 "EnableIndex" : true, |
206 "EnableStorage" : true, | 209 "EnableStorage" : true, |
207 "IndexConnectionString" : "DSN=index", | 210 "IndexConnectionString" : "DSN=index", |
208 "StorageConnectionString" : "DSN=storage", | 211 "StorageConnectionString" : "DSN=storage", |
209 "MaximumConnectionRetries" : 10, // Optional | 212 "MaximumConnectionRetries" : 10, |
210 "ConnectionRetryInterval" : 5, // Optional | 213 "ConnectionRetryInterval" : 5, |
211 "IndexConnectionsCount" : 1 // Optional | 214 "IndexConnectionsCount" : 1 |
212 }, | 215 }, |
213 "Plugins" : [ | 216 "Plugins" : [ |
214 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcIndex.so", | 217 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcIndex.so", |
215 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcStorage.so" | 218 "/home/user/orthanc-databases/BuildOdbc/libOrthancOdbcStorage.so" |
216 ] | 219 ] |
293 Server = tcp:localhost,1433 | 296 Server = tcp:localhost,1433 |
294 Database = orthanctest | 297 Database = orthanctest |
295 | 298 |
296 Note that there exist `many more configuration options | 299 Note that there exist `many more configuration options |
297 <https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client>`__ | 300 <https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client>`__ |
298 for Microsoft SQL Server. | 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. | |
299 | 304 |
300 .. highlight:: json | 305 .. highlight:: json |
301 | 306 |
302 5. Start Orthanc using the following :ref:`configuration file | 307 5. Start Orthanc using the following :ref:`configuration file |
303 <configuration>` for ODBC:: | 308 <configuration>` for ODBC:: |
313 | 318 |
314 In the connection strings: | 319 In the connection strings: |
315 | 320 |
316 * ``DSN`` corresponds to the name of the entry in ``~/.odbc.ini``. | 321 * ``DSN`` corresponds to the name of the entry in ``~/.odbc.ini``. |
317 * ``Uid`` is the user name in MSSQL (by default, the Docker image uses ``sa``). | 322 * ``Uid`` is the user name in MSSQL (by default, the Docker image uses ``sa``). |
318 * ``Pwd`` is the password that has been used when starting Docker. | 323 * ``Pwd`` is the password that has been provided in the ``SA_PASSWORD`` |
324 environment variable when starting Docker. | |
319 * For security reasons, the ``Uid`` and ``Pwd`` parameters cannot | 325 * For security reasons, the ``Uid`` and ``Pwd`` parameters cannot |
320 be set in ``~/.odbc.ini``. | 326 be set in ``~/.odbc.ini``. |
321 | 327 |
322 | 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 | |
341 | |
323 PostgreSQL | 342 PostgreSQL |
324 ^^^^^^^^^^ | 343 ^^^^^^^^^^ |
325 | 344 |
326 1. Install the ``odbc-postgresql`` package. | 345 1. Install the ``odbc-postgresql`` package. |
346 | |
347 .. highlight:: text | |
327 | 348 |
328 2. Create the following sample `unixODBC | 349 2. Create the following sample `unixODBC |
329 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | 350 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: |
330 | 351 |
331 $ cat ~/.odbc.ini | 352 $ cat ~/.odbc.ini |
335 Database = orthanctest | 356 Database = orthanctest |
336 UserName = postgres | 357 UserName = postgres |
337 Password = postgres | 358 Password = postgres |
338 Port = 5432 | 359 Port = 5432 |
339 | 360 |
361 .. highlight:: json | |
362 | |
340 3. Start Orthanc using the following :ref:`configuration file | 363 3. Start Orthanc using the following :ref:`configuration file |
341 <configuration>` for ODBC:: | 364 <configuration>` for ODBC:: |
342 | 365 |
343 { | 366 { |
344 "Odbc" : { | 367 "Odbc" : { |
354 ^^^^^ | 377 ^^^^^ |
355 | 378 |
356 1. Install the official `Connect/ODBC package | 379 1. Install the official `Connect/ODBC package |
357 <https://dev.mysql.com/downloads/connector/odbc/>`__ (it is not | 380 <https://dev.mysql.com/downloads/connector/odbc/>`__ (it is not |
358 packaged for Ubuntu 18.04). | 381 packaged for Ubuntu 18.04). |
382 | |
383 .. highlight:: text | |
359 | 384 |
360 2. Create the following sample `unixODBC | 385 2. Create the following sample `unixODBC |
361 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | 386 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: |
362 | 387 |
363 $ cat ~/.odbc.ini | 388 $ cat ~/.odbc.ini |
367 Database = orthanctest | 392 Database = orthanctest |
368 UID = root | 393 UID = root |
369 PWD = root | 394 PWD = root |
370 Port = 3306 | 395 Port = 3306 |
371 | 396 |
397 .. highlight:: json | |
398 | |
372 3. Start Orthanc using the following :ref:`configuration file | 399 3. Start Orthanc using the following :ref:`configuration file |
373 <configuration>` for ODBC:: | 400 <configuration>` for ODBC:: |
374 | 401 |
375 { | 402 { |
376 "Odbc" : { | 403 "Odbc" : { |
387 SQLite | 414 SQLite |
388 ^^^^^^ | 415 ^^^^^^ |
389 | 416 |
390 1. Install the ``libsqliteodbc`` package. | 417 1. Install the ``libsqliteodbc`` package. |
391 | 418 |
419 .. highlight:: text | |
420 | |
392 2. Create the following sample `unixODBC | 421 2. Create the following sample `unixODBC |
393 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: | 422 <https://en.wikipedia.org/wiki/UnixODBC>`__ configuration file:: |
394 | 423 |
395 $ cat ~/.odbc.ini | 424 $ cat ~/.odbc.ini |
396 [index] | 425 [index] |
403 | 432 |
404 Note that we define two different data sources, one for the index | 433 Note that we define two different data sources, one for the index |
405 and another for the storage area, because a SQLite database can | 434 and another for the storage area, because a SQLite database can |
406 only be opened by one client at once. | 435 only be opened by one client at once. |
407 | 436 |
437 .. highlight:: json | |
438 | |
408 3. Start Orthanc using the following :ref:`configuration file | 439 3. Start Orthanc using the following :ref:`configuration file |
409 <configuration>` for ODBC:: | 440 <configuration>` for ODBC:: |
410 | 441 |
411 { | 442 { |
412 "Odbc" : { | 443 "Odbc" : { |