Save/export database to server location, share a database?

[kentm]

Hi,

I'm exploring using xnView as a tool for our studio and want a way to share a database between multiple people. Is something like this possible?

[m.Th.]

Hi,

I'm exploring using xnView as a tool for our studio and want a way to share a database between multiple people. Is something like this possible?

Yes.

Basically, you need to put at least xnview.db in a shared location where all the workstations will have Read/Write access and configure all the instances of XnView MP to point there.

This can be done from Tools | Settings | Integration | Paths - change the Folder for Database (.db) to 'Custom' and select the same LAN path for all the workstations. Restart the program(s) after.

To "save/export" the database there, just copy it if you want, even if the program copies it automatically at destination if it doesn't exist already a DB.

Perhaps is better to know some things:

The Catalog is divided in two databases, one for thumbs (thumbs.db) and one for metadata+catalog info (xnview.db). The one which is necessary to be shared is the (much) smaller one - xnview.db. If you want to share also the thumbs - do as you wish, but please take in account that isn't always the best thing to do. Thumbs.db is much bigger and in a concurrent environment can slow the things down - at least we have/had such problems with another system based on MS SQL Server. You need to do a benchmark for this. Also, please bear in mind that thumbs.db is completely deletable (there is nothing else than thumbs inside) and if it is located on a local SSD or a fast HDD with WebP compression it scales very well and generates the thumbs very fast.

See here for other related things: http://www.xnview.com/wiki/index.php/Xn ... e_solution

Also perhaps is better to know some things about concurrency: XnView MP (like almost all other DAMs in its category) uses SQLite as DB backend. SQLite locks the entire file at write - thing which means that for few milliseconds (depending on your storage - SSD, RAID arrays etc.) only one writer will be there. In real world this means that on a file server which is at 2014 level (SSD in RAID etc.) "only" 20-30 simultaneous writers can write in good conditions in XnView MP's DB. In fact, the only time consuming/stressing write operation in XnView is thumbnail generation. That's why I told you to put that DB locally - but it is up to you: it will work ok also on your file server, especially in day-by-day operation when you will have few users which will generate the thumbs simultaneously. I remind you that we speak about writers and not only 'normal' writers but simultaneous writers.

In the case of 'readers' the number is "unlimited". (Ok, limited only by the storage's IOPS and LAN's bandwidth). The same is in the case of 'catalogers' (the users which assign Rating, Colors, Keywords etc.) - if they aren't 40-50 which work simultaneously and they doesn't do 'big batch cataloging' (for ex. assigning a keyword for 100s of photos), their impact is negligible.

[kentm]

Excellent and thorough reply - thanks very much. We will try this out and will certainly have more questions!

What we're looking for is a way that multiple people can add keywords and/or tags to images and have these available to all people the next time they load the db...

Have you experienced what happens if two people try to save/write to a db at the same time? Or does the app just lock the db file as soon as the first save begins?

[m.Th.]

Excellent and thorough reply - thanks very much. We will try this out and will certainly have more questions!

What we're looking for is a way that multiple people can add keywords and/or tags to images and have these available to all people the next time they load the db...

All the catalog data (Rating/Colors/Keywords) is available at the next query from database. It isn't needed to restart the program.

It is a classical scenario in which only the data needed for the current program status is fetched from the database and it is kept in in-memory structures. The program does not monitor if other 'writer' modifies the data from the database, neither it checks for newer version(s) when it needs to (re)fetch something from the storage layer. Hence, if the User 1 works on c:\Folder 1 and User 2 works on c:\Folder 2 simultaneously, when the User 1 finishes his work on Folder 1 and goes to Folder 2, the program, which under the hood asks the database for the Folder 2's data, will fetch also all the committed (saved) work of the User 2. Hence, User 1 will see 'magically' in c:\Folder 2 all the work already done by the User 2.

Of course, the user can use also the manual refresh (View | Refresh... or press F5 with the default keymapping) or any other means which implies a data refresh/refetch (change folders, filtering etc.) in order to see the new data from the other users.

Have you experienced what happens if two people try to save/write to a db at the same time? Or does the app just lock the db file as soon as the first save begins?

Yes. But we need to make the things much clearer here:

The database "saves" (ok, commits) in this scenario aren't at all a big, continuous thing like saving a 30-40 MB CMYK TIFF.

In usual cases, they tend to be rather a very small and quick bursts of commits repeated at rare intervals because the time needed to assign the catalog info is much much bigger than the commit time. Remember that 95% or more from time is spent by the user analyzing the photos in order to decide what Rating/Colors/Keywords (if any) will be assigned. Because these times vary from photo to photo and user to user the possibility that many users will commit simultaneously is very very small.

Hence, even if, indeed, SQLite puts a write lock on the file, this will be very fast (milliseconds) and the possibility of collisions (write conflicts) are very rare. And even if two users will happen to write "at the same time", the DB engine will handle this gracefully by delaying the 2nd one with the milliseconds needed to complete the write transaction of the 1st one. If you put on top of it the write cache of the server's operating system as well as the cache of the storage subsystem (HDD/SSD, controllers etc.) you will quickly find that in practice this is absolutely negligible.

The real source of delays in such environments are nowadays rather unrelated with the concurrency in the catalog database. They tend to come mostly from the wake-up time of the storage devices which usually are in nearline mode (they usually are turned off or put in sleep mode by the controller and are turned on at first access).

[kentm]

Excellent info, again - thanks.

[orloff34]

What we're looking for is a way that multiple people can add keywords and/or tags to images and have these available to all people the next time they load the db...

Hi,

we have the same intention.

In our environment XnviewMP is installed with Ubunutu 12.04 on a desktop PC with two (or more) users, which is quiet a normal configuration. I believed it is good way to store the db in a shared folder accessible with read/write permissions for all users so that each user can use the rating, color flags or assign categories and the next user can see that and work with the images depending on data set for the pictures. Simultaneous access of more than 1 user is totally excluded.

I experienced following:
Color labels, rating and categories are save in relation with the user who has assigned the data. Only this user is able to change this data. The second user can see the result using the filter for rating (color label or categories), but files are not marked wih the symbols in the overlay. If the second user assigns again the same data, eg. rating, the image files with their thumbnails come up twice in the rating filter! For images with categories set by the first user the second user can apply the filter but will not get thumbnail displayed.

So storing the database in a shared folder is not working perfectly.

I think it makes sense to think about which data shall be assigned and changed by all users and which not. Therefore I suggest to have global options for rating, color label and category meta data where it can be decided whether the data is stored for the individual user (private) or for all users (public). If the option is set to public (for all users) the data shall be writable/readable by all users otherwise if it is set to private the user cannot see that data nor work with it, like filtering.

These global options might be stored in a simple ini (text) file in the same folder as the database so that not every user can change the options with the settings dialogue. At least with Linux it can be protected using the file permissions. This ini file is read with start-up of xnview and the options are set in the database accordingly. If the ini-file is missing the options are set to private by default.

Kind regards
orloff34

[schultzter]

Good morning...

This is a popular topic with Shotwell and Digikam users as well. Digikam has the possibility to use a MySQL/MariaDB database but in my experience that hasn't worked out too well. Everyone seems to use SQLite in a locally stored database file.

The conclusion seems to be that using SQLite on a network across NFS is dangerous because locking may not be respected in that environment and that can cause issues (like duplicate data or even database corruption). I don't know about using Samba (CIFS/SMB/Windows sharing) to access the database across a network but in my environment that causes other issues with authentication and permissions.

I would love it if it was possible to put the database in a central location. My actual photos are on my NAS, so they are accessible to all my devices (including the TV). But I can only use XnView on my PC in the basement because that's where it's database is.

Unfortunately, I think a truly multi-user solution requires a multi-user backend, like MariaDB or something.

[m.Th.]

What we're looking for is a way that multiple people can add keywords and/or tags to images and have these available to all people the next time they load the db...

Hi,

we have the same intention.

In our environment XnviewMP is installed with Ubunutu 12.04 on a desktop PC with two (or more) users, which is quiet a normal configuration. I believed it is good way to store the db in a shared folder accessible with read/write permissions for all users so that each user can use the rating, color flags or assign categories and the next user can see that and work with the images depending on data set for the pictures. Simultaneous access of more than 1 user is totally excluded.

I experienced following:
Color labels, rating and categories are save in relation with the user who has assigned the data. Only this user is able to change this data. The second user can see the result using the filter for rating (color label or categories), but files are not marked wih the symbols in the overlay. If the second user assigns again the same data, eg. rating, the image files with their thumbnails come up twice in the rating filter! For images with categories set by the first user the second user can apply the filter but will not get thumbnail displayed.

So storing the database in a shared folder is not working perfectly.

It is the normal way of working.

It can be changed but with my great (perhaps) and bitter (for sure) experience from the past, the things must be very carefully planned. SQLite isn't a multi-user db engine, hence only one user have access on a certain piece of data at a certain moment in time. A multi-user db will make this thing "better" but will open an entire can of worms because then users will have the temptation to work on the same folder/image generating conflicts of interests (who will win and post the data which will remain?).

WRT 'files are (NOT) marked' it is a 'normal' behavior in multi-user environment: an automatic refresh engine can flood the network with requests and the file server will be turn down with the simultaneous requests from the workstations.

That's why the refresh is usually 'on demand' (even if some DB engines like Firebird implement the notion of 'events'): The user must refresh manually their data. Usually this isn't an issue because:

- usually the users (must) work on different folders
- the program anyway does an refresh when it (re)query the data from database when it does a folder change, sort, filter etc. That's why when you filtered your data, the changes from the other workstation(s) appeared in your environment.

I think it makes sense to think about which data shall be assigned and changed by all users and which not. Therefore I suggest to have global options for rating, color label and category meta data where it can be decided whether the data is stored for the individual user (private) or for all users (public). If the option is set to public (for all users) the data shall be writable/readable by all users otherwise if it is set to private the user cannot see that data nor work with it, like filtering.

These global options might be stored in a simple ini (text) file in the same folder as the database so that not every user can change the options with the settings dialogue. At least with Linux it can be protected using the file permissions. This ini file is read with start-up of xnview and the options are set in the database accordingly. If the ini-file is missing the options are set to private by default.

Kind regards
orloff34

For this you can have different databases: you can have different ini files which can point to different databases and run them in the command line. I think it is something like xnview -ini dbA.ini

[m.Th.]

Good morning...

This is a popular topic with Shotwell and Digikam users as well. Digikam has the possibility to use a MySQL/MariaDB database but in my experience that hasn't worked out too well. Everyone seems to use SQLite in a locally stored database file.

Yes, sure. (perhaps, unfortunately). Photogs aren't sysadmins hence a central SQL/DB server is a hassle to set up and maintain. The real solution is IMHO an old school ISAM (oh ye, FoxPro, where art thou?) which needs only a shared folder with the DB to work like you and others say. Of course, this solution doesn't scale like a dedicated SQL engine (that's why they disappeared) but for a team till 20 men is very very good. The closest thing to this model is nowadays SQLite.

The conclusion seems to be that using SQLite on a network across NFS is dangerous because locking may not be respected in that environment and that can cause issues (like duplicate data or even database corruption).

This is very bad because this means that NFS has very nasty bugs WRT locking. SQLite doesn't do any trickery. It just uses the normal OS 'file open'.

I don't know about using Samba (CIFS/SMB/Windows sharing) to access the database across a network but in my environment that causes other issues with authentication and permissions.

Sure. The OSes/workstations must be configured in order that all the users must have read/write access on the share with the DB.

I would love it if it was possible to put the database in a central location. My actual photos are on my NAS, so they are accessible to all my devices (including the TV). But I can only use XnView on my PC in the basement because that's where it's database is.

No, you can put it on the network. Just be sure to configure XnView to point at \\myServer\myShare and to have the permissions set up accordingly.

Unfortunately, I think a truly multi-user solution requires a multi-user backend, like MariaDB or something.

I'm not the defender of SQLite here or XnView MP. But till 10-15 writers with nowadays storage and network speed you can work nicely. Just don't do mad things. But true multi-user backend would be Oracle's BerkleyDB which has SQLite's lexer on top. But this costs for non open source projects.