Orthanc 0.8.6 and PostgreSQL

[Sébastien Jodogne]

Dear Orthanc users, 

Orthanc 0.8.6 is out!

The official announce can be found on Facebook:

http://goo.gl/sIlJCS

Furthermore, we have just released plugins to replace the default SQLite back-end of Orthanc, by the scalable and enterprise-ready PostgreSQL database engine:

https://code.google.com/p/orthanc-postgresql/

These plugins are available under the AGPL license. A free and open-source Vendor Neutral Archive is born!

Enjoy,
Sébastien-

[Raúl Alexis Betancor Santana]

Reading the docs about the PostgreSQL plugin ... found this:

Unlocking the database

The plugins implement a locking system to prevent other instances of Orthanc from using the same PostgreSQL database. If for some reason, Orthanc has not properly shutdown (for instance because of a power outage), you will have to manually unlock the database. This can be done by adding the --unlock flag at the command line:

# ./Orthanc Configuration.json --unlock

And I must ask WHY? ... why an instance of Orthanc lock the DB? ... why it need to do so?, one of the big reasons for using PostgreSQL as DB storage, it's to be able to use more than one Orthanc instance. So this have no sense to me ... will review the code, maybe I found the answer there ...

[Raúl Alexis Betancor Santana]

Taking a fast look ... I see things not having sense on a XXI century RDBMS powered app ...

from GlobalProperties.sql

Better if table have this structure:

CREATE TABLE GlobalProperties(

       instance VARCHAR(255),

       property INTEGER,

       value TEXT

       );

CREATE INDEX globalproperties_instance_idx on GlobalProperties (instance,property);

Also drop the ChangeGlobalProperty function, and do the INSERTS, UPDATES and DELETES within a transaction at app level. We are in 2015 ... you don't MANUALY LOCK a table for this.

[Sébastien Jodogne]

Dear Raúl,

I hereby want to remember 3 simple rules when you are discussing any free and open-source project:

1. Be respectful.
2. Be open.
3. Be constructive.

We have always been open to any opinion and I would like you to adopt the same attitude.

That being said, to answer your criticism about the locking mechanism, the code of the PostgreSQL plugins do not make any assumption about locking. Every sequence of DB operation is properly enclosed within transactions. The locking mechanism is just a feature to prevent two instances of Orthanc with diverging configurations to attack the same database (have for instance a look at the "MaximumPatientCount" option). If you have dug enough into the code, you will notice that the 2 plugins (for the index and for the file storage) do not use the same lock.

Don't like locking? Just comment out the content of the methods "GlobalProperties::Lock()" and "GlobalProperties::Unlock()". Not a big deal. Everything will run fine.

Regarding your second attack against "ChangeGlobalProperty", just have a look at the two following links:

http://stackoverflow.com/a/17267423/881731

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Last notice: Orthanc is designed to be able to use any DBMS, including embedded ones such as SQLite. Furthermore, the possibility of switching the back-end can be done from plugins. This implies a lot of compromises and a specific architecture to be fully agnostic of the actual back-end. At the price, of course, of a less straightforward way to code than if you would just support one DBMS as it is traditionally the case with other PACS/VNA systems.

Sébastien-

[Raúl Alexis Betancor Santana]

Hi Sebastien, as far as I concern:

1- I'm not been unrespectful

2- I'm open-minded, but you have to admit that using locking sentences from 80's design techniques on a 2015 developed soft, its not a good idea.

3- I'm constructive, I have write down the code to do a very important improvement (from my point of view, of course).

About the ChageGlobalProperty, there is a mantra on RDMS that said ... "If not supported by the motor, do not try to 'emulate' it on proc", why locking a FULL table to do an update of a value is not a good aproach is out of the scope of this message.

Supporting more DBMS in agnostic way doesn't mean doing like this, just my opinion, also it's not a good practice to do 'CREATE TABLE' statement from within the code, because you will end with a mess on updates of structures when upgrading from version to version, as you will have to write down code to check if you are working with the correct schema version and if not, upgrade it.

With the simple change I send, and changing the source code of the pluging to use the instance name as part of the globalprorties, you will be able to use a bigger RDBMS server, serving all the instances of Orthanc, and I think thats a very big improvement, as its DBMS agnostic, as if a DBMS doesn't support concurrence (as SQLite does), you DMBS pluging just will not allow you to doit. anything more needs to be changed.

[Emsy Chan]

Hi!

Umm... I'm going to ask a very dumb question here, which I hope you guys can tolerate... This may not even be in the correct forum to ask... I mean, it's a seriously dumb question to ask in a forum as high-level as this but I can't seem to figure out the answer after an hour of Googling... and I'm actually dying to try the PostgreSQL plug-in... I've actually installed a WAPP stack 2 weeks ago in anticipation for this plug-in...

Question:

How do I build the plug-in for Windows? Or can somebody provide the compiled libraries?

*Note: I downloaded and installed CMake, but if I'm not mistaken, the error message suggests that I need to install a compiler like Visual Studio, right? Which I don't have...

Hoping for some help / guidance here... 

Thanks and regards,

Emsy

[Sébastien Jodogne]

Hi Raúl,

Glad to hear that you didn't mean to be disrespectful. Following your advice, I have just committed 2 modifications:

1. Removal of the upserts mechanism. I replaced upserts by 2 operations: DELETE, then INSERT. The code is indeed clearer now (http://goo.gl/NHDOSk).
2. Introduction of an option to disable the locking mechanism that is used by default (http://goo.gl/GTL2Ap)

However, I do not want to store the content of several, distinct instances of Orthanc into the same PostgreSQL database as you suggested (cf. the "instance" column of your "GlobalProperties" table). For this purpose, you will have to create separate PostgreSQL databases.

Similarly, to keep the code as compact as possible, I will not put the one-liner SQL commands outside the C++ code.

Sébastien-

[Sébastien Jodogne]

Hi Emsy,

I have just placed precompiled DLLs of the PostgreSQL plugins on SourceForge:

https://sourceforge.net/projects/orthancserver/files/DevelopmentSnapshots/

These binaries are cross-compiled for Windows from Linux using MinGW.

Sébastien-

[Emsy Chan]

Just came back after the weekend. Thanks so much for the libraries! :)

Emsy

[vladimir...@gmail.com]

Hi Sebastien,

Thanks for this update. I am very new in all these medical stuff (but with 6 years as a Linux developer experience), but your project helps me a lot to understand DICOM.

It works well with PostgreSQL, but I am trying to use package in Fedora. So it has previous version, that I just installed and overwritten all files with these new. All works perfect, except systemd integration.

Where I can disscuss Systemd issues for orthanc package? May be I will find solution very soon and like to share it with your guy, who preparing packages for Linux distros.

[Sébastien Jodogne]

Dear Vladimir,

Are you aware of the existence of the official Fedora package, with systemd support?

https://apps.fedoraproject.org/packages/orthanc

I am currently too busy to upgrade to Orthanc 0.8.6, but maybe you could get in touch with Mario Ceresa, who co-maintains the package:

http://www.marioceresa.org/

Sébastien-

[jimmy....@strongholdsolutions.com]

Hi Sébastien,
I have the PostgreSQL configuration working (snippet attached):

.......

W0417 12:13:14.426609 11743 main.cpp:379] Loading plugin(s) from: /plugs/pgsql/libOrthancPostgreSQLIndex.so
W0417 12:13:14.427059 11743 PluginsManager.cpp:258] Registering plugin 'postgresql-index' (version 1.0)
W0417 12:13:14.427604 11743 PluginsManager.cpp:148] Using PostgreSQL index
W0417 12:13:14.427656 11743 PluginsManager.cpp:148] Locking of the PostgreSQL database is disabled
W0417 12:13:14.439491 11743 main.cpp:379] Loading plugin(s) from: /plugs/pgsql/libOrthancPostgreSQLStorage.so
W0417 12:13:14.439925 11743 PluginsManager.cpp:258] Registering plugin 'postgresql-storage' (version 1.0)
W0417 12:13:14.440147 11743 PluginsManager.cpp:148] Using PostgreSQL storage area
W0417 12:13:14.440201 11743 PluginsManager.cpp:148] Locking of the PostgreSQL database is disabled
NOTICE: relation "storagearea" already exists, skipping
W0417 12:13:14.456154 11743 main.cpp:379] Loading plugin(s) from: /plugs/view/libOrthancWebViewer.so
W0417 12:13:14.508535 11743 PluginsManager.cpp:258] Registering plugin 'web-viewer' (version 1.0)
W0417 12:13:14.508869 11743 PluginsManager.cpp:148] Initializing the Web viewer
W0417 12:13:14.509583 11743 PluginsManager.cpp:148] Web viewer using 4 threads for the decoding of the DICOM images
W0417 12:13:14.511929 11743 PluginsManager.cpp:148] Storing the cache of the Web viewer in folder: WebViewerCache
W0417 12:13:14.514219 11743 PluginsManager.cpp:148] Web viewer using a cache of 10 MB
W0417 12:13:14.515508 11743 main.cpp:379] Loading plugin(s) from: /plugs/wado/libWadoPlugin.so
W0417 12:13:14.521294 11743 PluginsManager.cpp:258] Registering plugin 'wado-sample' (version 1.0)
W0417 12:13:14.521807 11743 PluginsManager.cpp:148] Initializing WADO sample
W0417 12:13:14.524930 11755 ServerScheduler.cpp:134] The server scheduler has started
W0417 12:13:14.525842 11743 LuaContext.cpp:89] Lua says: Lua toolbox installed
....

but Orthanc ignores the directive to store data in my designated place. I want to put my images at the mount point /data/images for instance. I must confess I am new to PostgreSQL. Is this controlled from within the database now?
Best Regards

[Sébastien Jodogne]

Dear Jimmy,

If you enable the "postgresql-storage" plugin, then all the DICOM files get stored into PostgreSQL as large objects, and the "StorageDirectory" configuration option is ignored (because no file is written directly to the filesystem).

If you want to store the files on the filesystem, do not load "libOrthancPostgreSQLStorage.so" or set the following option in your configuration file to disable the storage plugin:

{
  [...]
  "PostgreSQL" : {
    "EnableIndex" : true,
    "EnableStorage" : false,
    [...]

  }

}

Sébastien-