Migrate Django SQLite database to MongoDB #17
Description
Answers to the anticipated line of questioning
Why is SQLite being used at all?
This stems from an issue with docker compose. Build-time is the same across all containers, so when the Django tvapi image is being built with docker compose, so is the MongoDB database. However, to use the admin features for Django we need a to specify a database that can be accessed at build-time. What we would ideally want is Mongo to be built and running before starting the docker build for tvapi.
A decision was made to not use a build script or have two compose.yaml files as this would be too complicated for Simons Observatory administrators who would not be expecting this topology. The results is a non-ideal solution to use SQLite for purpose of storing admin usernames and passwords at build-time for the Django tvapi.
Why use the Django Admin?
The admin console is a built-in way (no coding required) to edit a database tables that are used as variable states for some asynchronous processes like the scheduler queue and database scans in tvapi. This is less important at there are now that views allow users to override and set most variable states without admin login. Still, the admin page allows for a finer control then what has been configured to be controlled by views.
Why migrate at all?
SQLite is bad at handling asynchronous commands, if two commands acting on the same database element are send too close together in time, then one command will exit with a python exception OperationalError. SQLite is missing a lot of database features, but it works well enough for single threaded application, too bad this is not a single threaded application.
These operational errors can be wrapped in Python try statements, but this greatly increases code complexity, and slows the down the database events with time.sleeps() calls that hope that SQLite database becomes unlocked later.
See the image below that show one error state that is blocking the queue from progressing.
It is suspected that on August 19, 2023, the queue was never unlocked when next command failed to update the database status to "running", due to an OperationalError being raised. This made now logged error as this event happened on a newly spawned (non-blocking) thread.
What should the migration look like?
There are two possible paths, that could solve the code-complexity and concurrency issues.
1. Migrate everything to MongoDB except the admin and password storage
This would solve the concurrency issues, and allow use to continue to use docker compose. This will require a wait-for-it script to be built, so that MongoDB image have time to initialize before receiving write commands.
2. Migrate everything to MongoDB
This will require one of the following:
a. Removing the admin component to Django.
b. Looking into another service like Kubernetes to replace docker compose
c. Making MongoDB a stand alone service on the host computer, taking it out of a Docker container.
d. Using a build script to build and start a mongoDB container before building and starting the Django TV app container.
a. - c. solutions but will require a extra research to implement compared to strategy 1, d. asks administrators do something slightly inconvenient, and require a review of the permissions required to run such a script on SO computers.