[Digikam-devel] [digikam] [Bug 322946] MYSQL : picture collection and database on a server can handle only one user

[via KDE Bugzilla]

https://bugs.kde.org/show_bug.cgi?id=322946

--- Comment #6 from swatilodha27 at gmail.com ---
(In reply to Richard Mortimer from comment #5)
> Before I answer the direct question let me make it clear that a "user" in
> this context is actually a "connection" to the shared MySQL database from a
> running digikam instance. It does not matter whether that connection uses
> the same login details as another connection or not. It is still operating
> on the same data.
> 
> MySQL has many methods to handle concurrency but locking the database is
> generally considered a last case scenario and even then it should only be
> used for short periods of time (sub-second) because any longer and other
> connections trying to access/change the database would just wait (hang)
> until the lock was released.
> 
> It is difficult to provide a general solution description but there are a
> number of tools that can be used to build up a solutions:
> 
> - first off the database has to be considered the master copy of the data.
> This is especially important if/when multiple instances of digikam are
> accessing the database simultaneously. At that point the internal digikam
> data structures have to be considered a cache (local copy) of the data that
> may be out of date. Really digikam has to be prepared to accept/work around
> SQL commands that fail because someone else has changed the shared database
> state.
> 
> - this also means that the referential integrity constraints/checks are
> vital to the correct operation of the database because they help to ensure
> that no-one add duplicates or add references to tags/images that may have
> been added/deleted by someone else.

After your yesterday's patch on this
https://bugs.kde.org/show_bug.cgi?id=355831, I think referential integrity is
mostly done, so duplicates could now be avoided.

> 
> - to my mind this is where the most important preparations for a
> multi-digikam shared operation mode come in. The database access routines in
> digikam should (and many are) be arranged as logical operations, e.g. add a
> tag and give me the database id of the tag. Those operations have to be well
> tested and able to work around changes made by other users. For instance the
> add a tag operation SQL might fail because another digikam has already added
> that tag. In that instance it should find the requested tag and load the
> data/return the tag id from the other record.

For 1) and 3) point you wrote:
In order to ensure that SQL query made by a user doesn't fail (if another user
has just made a change in the DB), I think we could use INSERT IGNORE INTO or
REPLACE statements, instead of simply INSERT query?
If there's no duplicate, INSERT IGNORE INTO will INSERT as general, else IGNORE
will discard silently without an error. Thus, preventing duplicate records.
Or REPLACE could be better, in order to get the latest change in the DB.

> - related to that digikam would need the ability to "rescan" the database in
> a similar manner to the way that it can rescan a filesystem. This allows it
> to easily pick up changes made by other users and integrate them into the
> Tags/Albums trees. I suspect that much of the images operation is already
> working in that way because digikam generally queries the database to find
> images etc.

How to go with the "re-scanning" of DB, so it's updated for all users, if
changes are made by another user?

> - the next thing related to concurrency is to consider the use of
> transactions to ensure that a group of operations either completes or does
> not. This may be of use when adding something like an image and there are a
> number of different tables that need updating with the properties of that
> image. This may not be strictly necessary because it might be enough that
> each table entry is considered on its own merits. But without it some image
> metadata may not get saved to the database if digikam crashes halfway
> through an operation.

Using TRANSACTIONS for all SQL queries? Or with add/delete/update a
Tag/image/album would suffice? 

> There is nothing stopping digikam allowing multiple users to have concurrent
> access to the database. I've used it myself but at the moment care has to be
> taken by the user to make it a mostly read-only sharing otherwise strange
> behaviour may occur. But there is a lot of preparation that could be made to
> prepare for more multi-user operation. Possibly the best way to do this is
> to write unit tests of the individual operations on something like the tags
> tree and within those tests explicitly inject changes as though they were
> from another user to make sure that the code handles them correctly.

If I'm right, making DB "read only" for users could definitely prevent lot of
confusion that could occur in DB.

-- 
You are receiving this mail because:
You are the assignee for the bug.