Improved collaboration feature - project lock

[kaixxx]

In another discussion (#875), the question about possible improvements in the collaboration features in QualCoder popped up. I think this is an important topic that deserves its own thread.

@MicRaving:

We're trying to use QualCoder on a project with five coders and the current version of QualCoder is not really practical.

The current way of collaborating on a project (as described in the wiki: https://github.com/ccbogel/QualCoder/wiki#collaboration-on-a-qualcoder-project) comes down to: Only one person at a time can work on a project, the project files have to be manually exchanged via email or other means.
This is a little cumbersome, but also has a lot of advantages: QualCoder doesn’t need any centralized database server, which makes it very easy to install and use and is also a big plus for data privacy (see the statement of @ccbogel here: #875 (comment)).

To keep these advantages but also improve the collaboration, I would suggest the following approach:

• In a collaborative environment, QualCoder-projects must be kept on a file server or shared network drive, where the whole team has access.
• Whenever a project is opened, QualCoder creates a lock file in the project folder. If a second instance of QualCoder (on the same or any other computer) tries to access the project, it will refuse to open it until the first instance has closed it and released the lock.
• The library portalocker can be used to manage these lock files – even with a timeout, so that the lock is automatically released if the first instance crashes or looses network connectivity (https://portalocker.readthedocs.io/en/latest/).

@MicRaving, @ccbogel: What do you think about this approach? A project would still be accessible only by one person at a time. But the whole sharing process would be much easier and safer since no files have to be send back and forth via e-mail.

Be aware though that this is not going to work in a cloud-based shared folder like dropbox. The problem is that the lock file is not going to be shared instantly, so it would still be possible that two people access the project at the same time.

[ccbogel]

I wanted to add. Currently using the sqlite database I had a person attempt to use it via a cloud location and the database connection was lost at one stage. If the connection is lost during the middle of substantial operations - e.g. such as shifting codes, merging, codes, merging categories etc, then the part completed operation would essentially wreck the coding of some of the data.

I think it is probably better for someone to fork the code and develop with a multiuser database, such as Mysql. Taguette does this. I have used Mysql but it's not easy to set up unless installing with WAMP or similar. But I am not confident with this level of development and will leave that to others. Also, as changes are made, then these changes would have to be somehow pushed out to other concurrent users.

[kaixxx]

I think it is probably better for someone to fork the code and develop with a multiuser database, such as Mysql.

This would be a massive undertaking. The biggest problem is not so much the database, but to update the interface with every single change in the database without interrupting what the user is doing right now. Think about the situation where you are working on a document and somebody else is adding codings to it or even edits the text. You would need to update the text and the codings in the current document window without loosing cursor position, etc. This would be a nightmare to implement.
I don't see the person who is willing (and able) to takle this. I dont't have the skills either.

If the connection is lost during the middle of substantial operations - e.g. such as shifting codes, merging, codes, merging categories etc, then the part completed operation would essentially wreck the coding of some of the data.

This would also happen with a central database server if you loose connection in the middle of such a compilcated transaction. Or am I missing something?

My approach would be a small improvement which is easy to implement. We must make clear though that this requires a very stable network connection and a real network drive so that any changes are written directly to the file on the network. This is NOT going to work with OneDrive or Dropbox.

[MicRaving]

In another discussion (#875), the question about possible improvements in the collaboration features in QualCoder popped up. I think this is an important topic that deserves its own thread.

@MicRaving:

We're trying to use QualCoder on a project with five coders and the current version of QualCoder is not really practical.

The current way of collaborating on a project (as described in the wiki: https://github.com/ccbogel/QualCoder/wiki#collaboration-on-a-qualcoder-project) comes down to: Only one person at a time can work on a project, the project files have to be manually exchanged via email or other means. This is a little cumbersome, but also has a lot of advantages: QualCoder doesn’t need any centralized database server, which makes it very easy to install and use and is also a big plus for data privacy (see the statement of @ccbogel here: #875 (comment)).

To keep these advantages but also improve the collaboration, I would suggest the following approach:

* In a collaborative environment, QualCoder-projects must be kept on a file server or shared network drive, where the whole team has access.

* Whenever a project is opened, QualCoder creates a lock file in the project folder. If a second instance of QualCoder (on the same or any other computer) tries to access the project, it will refuse to open it until the first instance has closed it and released the lock.

* The library `portalocker` can be used to manage these lock files – even with a timeout, so that the lock is automatically released if the first instance crashes or looses network connectivity (https://portalocker.readthedocs.io/en/latest/).

@MicRaving, @ccbogel: What do you think about this approach? A project would still be accessible only by one person at a time. But the whole sharing process would be much easier and safer since no files have to be send back and forth via e-mail.

Be aware though that this is not going to work in a cloud-based shared folder like dropbox. The problem is that the lock file is not going to be shared instantly, so it would still be possible that two people access the project at the same time.

This approach would probably already solve our main problem. We tried collaborating in QualCoder in a OneDrive folder and it created a new database each time we worked simultaneously or had any sync errors. This resulted in a project that was corrupted beyond any recovery after 2-3 times.

For us, it would be great to have a way to work with different coders simultaneously. Maybe a way to achieve that would be to have each coder working on a different project or in a different database that is treated as part of one umbrella project. Alternatively, the databases could be merged by the lead researcher when everyone is done, probably with a manual switch or a merge function. Would this be possible in sqlite?

[kaixxx]

the databases could be merged by the lead researcher when everyone is done

This is already possible: https://github.com/ccbogel/QualCoder/wiki/12-Imports-and-exports#merge-qualcoder-projects
If you do deductive coding and have a fixed code system, this 'coding seperately then merging' should work very well. If you want to add new codes during the analysis, keeping the code system in sync across coders can be difficult.

[ccbogel]

Yes I did create a merge projects function. Under Help > Special functions.
It is not without risk.
It's functions are:
Copies unmatched files from source project folders to destination project folders.
Adds new (unmatched) source categories to destination database.
Adds new (unmatched) source code names to destination database.
Adds journals and stored_sql to destination database, only if they have unique names,
Adds text codings, text annotations, image codings, av codings to destination database.
Adds cases and case_text (links to text file segments and images and A/V)
Add attributes for files and cases.
Existing attribute values in destination are not over-written, unless already blank.

If there is a difference between supposedly identical files. e.g. a minor text edit in one identical text file. this will affect imported coding's on that text file.
All files should ideally be located within the project folder. External (relatively) linked files, would need to be re-linked - really only an issue for image or A/V files.

Considerations:
Ideally, everyone would have the same identical files within the project.
Ideally using the same set of code names - but this could be worked around later if merging or renaming codes occurred in the collated project.
Coder names - would need to be unique for each coder. So, when collated the individual coders work can be identified.
Alternatively, coder names could be the same, providing they each worked on unique files to code. But that all depends on your end goals really. If you had a need to collate all under one coder name.

[kaixxx]

Very nice writeup, also regarding the potential pitfalls that should be considered when using this approach. The last part ('Considerations') could be added to the Wiki.

[ccbogel]

@kaixxx added that to the wiki.

[kaixxx]

I had a quick look:

Neither MAXQDA nor ATLAS.ti (Desktop) allow concurrent access to the same project. This is even the case if you use the "ATLAS.ti project cloud". Only the online version of ATLAS.ti seems to allow multiple user working on the same project at the same time.
https://www.maxqda.com/help-max12/teamwork/can-maxqda-support-teamwork
https://doc.atlasti.com/ManualWin/Project/ProjectCloud.html

[ccbogel]

Well that is interesting, and even so for these funded projects, I believe that they also must have realised that multi user collaboration is a really hard thing to successfully achieve.
So MAXQDA does something similar with projects being merged.
And Atlas.ti send a copy of the project to an invited member, but not actually collaborate on the same project at the same time.

[kaixxx]

@ccbogel: What do you think, should I try to implement the 'lock file approach' described above and make a pull request? I could also include some safety measures like creating an automated backup before 'dangerous' actions like merging codes/categories, etc.

[ccbogel]

Ok I made an update and moved the save_backup() method into the App class. So this can be reaedilycalled elsewhere in the program.

[kaixxx]

I've created a new branch "colab" and worked on the implementation of the backup-functionality. After a little brainstorming with ChatGPT I came up with an elegant solution, a context manager. It's very easy to use:

with self.app.backup_protected_operation():
   # do large operation

# continue like normal

The context manager will create a temporary backup, execute the large operation and automatically delete the backup afterwards if everything went fine. If an error occurs, the temporary backup will not be deleted and the user gets a notification (in the error message) that they can go back to this backup if needed. I've not implemented an automatic rollback to the backup because that seems quite complicated and error prone.
I've used the function DialogManageFiles.add_attribute as an example to try this context manager out.

One question: Did you add the "suffix" parameter in save_backup especially for these temporary backups? I've used a slightly different implementation. Temporary backups get a special suffix but also a unique number added. This is to make sure they are always created, even if a temporary backup with the same name already exists.
Since I don't use the "suffix" parameter, I think we might as well remove it. Or do you need it for something else?

[ccbogel]

I added it for the merge projects backup, but if there is a different implementation, the suffix can be removed.

[kaixxx]

I added it for the merge projects backup

OK. I've looked at it and think it's best to leave it like it is.
My temporary backups are getting deleted immediately once the operation finished without exceptions. In the case of merging projects, it might be a good idea to keep the backup a little longer so the user can check that everything went well.

[ccbogel]

yes ok

[ccbogel]

@kaixxx there is this inormation also if useful:
https://www.sqlite.org/lockingv3.html

[kaixxx]

Interesting, I'm gonna read this tomorrow on the train.

[kaixxx]

I've had a look at the source you provided:
It seems that SQLite also provides quite sopisticated methods for concurrent access of multiple users working in the same database at the same time. It only needs exclusive access to the file very briefly during write operations.
But since we don't want to go in the direction of a true multi-user-application yet (for the reasons discussed above), I'm going to implement our own locking mechanism which will block concurrent access altogether.

[ccbogel]

yes ok

[kaixxx]

I’ve now implemented the lock file approach and it is working very well. See here for details: 0e77eb8

Unfortunately, the backup solution I proposed earlier turned out to be more problematic than anticipated. Technically, there is nothing wrong with it. However, further testing revealed that it can significantly slow down QualCoder, especially when dealing with a large corpus of data or when used over a slow network drive. Consequently, I have concluded that this approach is not very practical. This is somewhat frustrating, given the amount of time and effort I have invested in this functionality. Anyhow, this is how we learn…

Good news: I found a better solution. SQLite comes with its own mechanism for maintaining database consistency, even if an error occurs in the middle of a complex operation. This feature is known as "atomic commits," as described in this document: https://www.sqlite.org/atomiccommit.html. (This might be well known to you, but it was new to me.)
The basic principle is as follows: In a complex operation involving many changes (numerous calls to cursor.execute()), none of these changes are actually written to the disk until you explicitly call connection.commit(). If an error arises, you can manually call connection.rollback() to revert all pending changes since the last commit. Alternatively, if the app crashes and the connection is lost or closed, SQLite will automatically perform this rollback.
To leverage this, the most critical change to the code is to avoid calling commit() in the middle of a complex operation, but rather only at the very end. This practice ensures that either all changes are written to the database simultaneously or the database remains completely unchanged. See this example for a typical implementation: e09b68b

My plan is to go through the code to identify other operations that need adjustments to align with the 'atomic commits' pattern. I will then delete my 'temporary backup' implementation, as it's no longer necessary. Is that OK with you? Afterward, I intend to create a pull request for the entire change, as well as a separate one for the wiki.

[ccbogel]

Hi. "Unfortunately, the backup solution I proposed earlier turned out to be more problematic than anticipated. Technically, there is nothing wrong with it. However, further testing revealed that it can significantly slow down QualCoder, especially when dealing with a large corpus of data or when used over a slow network drive." Yes I have done different things over time and found they did not work as intended. Lots of work to learn something.

OK - I knew MySQL had this function, as not aware of it in Sqlite.