Remove duplicate database schema definitions

[laurentS]

There are 2 files:

• initdb.sql
• set_up_db.sql

which contain the exact same definition. There should be a way to have a single source of truth, remove one of them.

[thenav56]

@laurentS Adding some more detail on this issue.

Usages

• initdb.sql is used by Postgres on initialization (first start-up).
• set_up_db.sql is defined for mapswipe_worker, it is used to create a temporary database for testing.
• Custom SQL script used to clean data on the test.

  python-mapswipe-workers/mapswipe_workers/tests/integration/base.py

  Lines 50 to 69 in ade1375

  	cls.__db_query(
  	"""
  	-- temp tables
  	TRUNCATE TABLE results_temp;
  	TRUNCATE TABLE results_user_groups_temp;
  	TRUNCATE TABLE user_groups_temp;
  	TRUNCATE TABLE user_groups_user_memberships_temp;
  	TRUNCATE TABLE users_temp;
  	TRUNCATE TABLE user_groups_membership_logs_temp;
  	-- normal tables
  	TRUNCATE TABLE results_user_groups CASCADE;
  	TRUNCATE TABLE tasks CASCADE;
  	TRUNCATE TABLE user_groups_user_memberships CASCADE;
  	TRUNCATE TABLE user_groups CASCADE;
  	TRUNCATE TABLE groups CASCADE;
  	TRUNCATE TABLE projects CASCADE;
  	TRUNCATE TABLE users CASCADE;
  	TRUNCATE TABLE user_groups_membership_logs;
  	"""
  	)

Reason behind set_up_db.sql

Currently, we have a hybrid way of running test cases.

• Using existing local Postgres database (Which is also used during development)
• Create a temporary database using set_up_db.sql which is initiated and deleted after the test run.
  • Why?
    • Isolated test instance.
    • Data cleanup after test completion.
    • Not changing the running local/remote database on the test run.

Proposed solution.

Django has a good data migration pipeline. And We have already set up and defined initial migrations in the Django server which is used by the community dashboard. Maybe we can move the python-worker functionality to the Django server and use Django to create/update the database instead of using raw SQL. This will create a better pipeline for testing and database change management.

[laurentS]

@thenav56 I agree that the isolated test db is great, no questions there. My concern was more around the duplicated source of truth for the db schema (the 2 files above), as it's easy to have the 2 files diverge in content.
I'm conscious it's not obvious how to merge these 2 files due to docker build environment constraints, which is why I haven't offered a solution :)

Using django's migration system should fix this indeed.