Database

[JorgeMiguelGomes]

@supernyv correct me if I'm wrong, but wouldn't it be safer for the app to check if the databases needed already exist, or not, and in case they don't exist instead of throwing an error proceed with creating all the needed databases?

[supernyv]

Hi @Jorge That's a valid concern, but there are a couple of things to consider: The app only communicates with the database via a user that is created in the database server for such purposes. This user typically has predefined privileges on specific database(s) in the server, both for security and integrity reasons. For security reasons because any user that can create a database can also delete it, and we try to avoid having everyone have such privileges. For integrity reasons because having databases created from the app could lead to creation of multiple databases by different app users, instead of having just one database for all users, a situation very hard to control, if not impossible. So, the database is created only once, from the server side (I can assist with this), and used by all the users of the app via a dedicated user created in the database server itself (vost_user in this case but it could be any name).

Nonetheless, what we can do is to modify the app to give more meaningful errors when it cannot connect to the database (for example due to internet issues), because it is assumed that the database should already exist and configured properly for the app to connect to it.

[JorgeMiguelGomes]

@supernyv those are really valid points. I'm pinging @tomahock because he is the person that I will deal with the deployment of the app in our server.

[tomahock]

Hi @jorge That's a valid concern, but there are a couple of things to consider: The app only communicates with the database via a user that is created in the database server for such purposes. This user typically has predefined privileges on specific database(s) in the server, both for security and integrity reasons. For security reasons because any user that can create a database can also delete it, and we try to avoid having everyone have such privileges. For integrity reasons because having databases created from the app could lead to creation of multiple databases by different app users, instead of having just one database for all users, a situation very hard to control, if not impossible. So, the database is created only once, from the server side (I can assist with this), and used by all the users of the app via a dedicated user created in the database server itself (vost_user in this case but it could be any name).

Nonetheless, what we can do is to modify the app to give more meaningful errors when it cannot connect to the database (for example due to internet issues), because it is assumed that the database should already exist and configured properly for the app to connect to it.

Hi @supernyv

Can you clarify some points:

• it's a sqlite database or a mariadb or mysql?
• How can we define the host, user and password? Could it be in env vars?
• Do you have some migrations? It will be executed on app startup? Or we need to run it manually?

Cheers!

[supernyv]

Hi @tomahock,

• It is a MySQL database

• I had prepared a pdf document to guide through the whole process, from setting up the server, creating the database and a dedicated database user for the app. (Kindly see the attachment below). But again, I'm available to assist if needed.

• No need for any migration, the database will be created with blank tables which will be populated by the app users. The requirement here is that the database and the user will be created outside the app, once, before runing the app

pdf: Download_MySQL and Setup Database.pdf

[tomahock]

hi @supernyv

Since we gonna run it on docker containers, i'm not able to use 'localhost' as the database host by default.

The must be some config that I can edit to place the host. It would also be recommended that I could define a stronger user and password, in that config file or in an env var.

Can you point me the config file so I can change the configs?

Cheers!

[supernyv]

Hi @tomahock , Yes, a stronger user and password are necessary. For Docker containers, I haven't used them before, but I believe that once you install the MySQL Server, setup the database and create the user, the host should be the IP address of the container. I'll need to read more on that.

[supernyv]

Hi @tomahock,

I have installed a linux OS (Ubuntu), I'll go through the process of running the database in a Docker container then communicate any findings I have. Do you have any other contraints/requirements that I should be aware of?

[supernyv]

The steps I have taken to run the MySQL Database Server in a Docker container:

• Step 1 : Installed a MySQL Workbench to be used as interface from the host: for some unknown reasons $sudo apt install mysql-workbench couldn't work (even with mysql-workbench-community instead of mysql-workbench simply). I was able to install it using $sudo snap install mysql-workbench-community.
• Step 2: Pulled a new image for MySQL server and run a container for it: $ sudo docker run --name mysql_server -p 3307:3306 -e MYSQL_ROOT_PASSWORD=2024.db_admin -d mysql:8.0 I decided to map the container's 3306 port to the host's 3307 and chose a simple password for the root user (2024.db_admin)
• Step 3: Then I launched the MySQL Workbench that I installed in step 1 and connected it with the server created in step 2 via the "MySQL Connection" setup in the Workbench. From there, creating the database and user is similar to what was sent in the pdf guide. Exactly the same process.

Kindly note that I assumed a default Docker Network configuration, so I did not bother with the IP addresses at this point. But that might change when I try to connect the LightHouse App with the Database Server. Let me know if you have set up some network for your Docker containers that I can try to emulate.

[tomahock]

hi @supernyv

That's ok!

The app is now ready to connect to a databse with the host mysql_server user root and password 2024.db_admin ?

I really just need to know this config so I can fire up things on this side!

Cheers

[supernyv]

Hi @tomahock, yes, that's it:

host = "127.0.0.1", port = 3307, user = "root", password = "2024.db_admin", db = "vost_db"

Unless @JorgeMiguelGomes wants a different name for the database instead of vost_db. And for the user and password, we actually have two options, either using the root (risky, with all permissions on the server) or using a dedicated user created for the app and with limited privileges (previously user = "vost_user", password = "vost" but we can create a new user with stronger credentials if needed).

So, yes, the current configuration is functional.

[tomahock]

@supernyv we have a lift off :D

[supernyv]

Great @tomahock !

[supernyv]

But there seem to be a problem, the navigation bar is not showing all the options. I noticed this happens sometimes but I am not completely sure why yet, although I suspect the problem might come from the Dash Mantine Provider used to wrap the whole App. If you manually navigate to a different page (for example by typing and going to http://127.0.0.1:8050/application, or by clicking on the links from the homepage), everything should be back to normal

[supernyv]

In the latest commit I have removed the dmc.MantineProvider as the main container of the app (this greatly facilitated theme mode switch between dark and light). I believe that this solves the problem of disappearing options from the navigation bar.

[supernyv]

Hi @tomahock

The admin page of the app is now complete. To get the app ready for launch, kindly consider the steps below:

1. Set up the database server (mysql_server in this case), and configure the root user as discussed previously.
2. Connect to the database server as the root user, preferably via MySQL workbench.
3. Set up the database (vost_db in this case), preferably via the "forward engineer" feature of MySQL workbench and selecting the provided vost_db_model.mwb from the assets folder of the WatchTower app. Note: This step must be done again even if you already created the database previously, as the model kept evolving throughout development. Running the model will delete the database before creating it again, with updated structure.
4. Still in MySQL Workbench, upload the countries_list.csv into the database's country table. The csv file is provided in the assets folder of the WatchTower app.
5. Still in MySQL Workbench, open and run the following queries provided in the assets folder of the WatchTower app:
   • script_platforms_insert.sql
   • script_report_class_insert.sql
6. Launch the WatchTower app
7. Let one user (who will become the first admin) apply using the application page of the app. This will also be a good test to ensure the app is working as expected.
8. Go back to the MySQL Workbench, open and run the following script provided in the assets folder of the WatchTower app: script_promote_first_admin.sql. (Note: edit the work_email value to match that of the first_user)

Done. Now the app should be ready for more users.