Update TM API backend

[Rub21]

From: https://github.com/OpenHistoricalMap/issues/issues/865

Since we are using a newly updated version for the TM frontend, it is required to update the TM API backend. The latest changes are available here: https://github.com/OpenHistoricalMap/tasking-manager.

cc. @erictheise @danrademacher

[Rub21]

I ran flask db upgrade command but seems there are some other stuff related for migration of the db I got error such as:

[2024-08-10 02:39:11,040] ERROR in app: Exception on /api/v2/system/heartbeat/ [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "release_version" does not exist
LINE 2: FROM release_version
             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 489, in wrapper
    resp = resource(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/views.py", line 109, in view
    return current_app.ensure_sync(self.dispatch_request)(**kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 604, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/usr/src/app/backend/api/system/general.py", line 158, in get
    release = ReleaseVersion.get()
  File "/usr/src/app/backend/models/postgis/release_version.py", line 20, in get
    return ReleaseVersion.query.first()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2747, in first
    return self.limit(1)._iter().first()  # type: ignore
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
    return self._execute_internal(

and

2024-08-10 02:41:19,789] ERROR in app: Exception on /api/v2/system/banner/ [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "banner" does not exist
LINE 2: FROM banner
             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 489, in wrapper
    resp = resource(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/views.py", line 109, in view
    return current_app.ensure_sync(self.dispatch_request)(**kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 604, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/usr/src/app/backend/api/system/banner.py", line 28, in get
    banner = Banner.get()
  File "/usr/src/app/backend/models/postgis/banner.py", line 43, in get
    banner = Banner.query.first()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2747, in first
    return self.limit(1)._iter().first()  # type: ignore
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2141, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "banner" does not exist
LINE 2: FROM banner
             ^

[SQL: SELECT banner.id AS banner_id, banner.message AS banner_message, banner.visible AS banner_visible
FROM banner
 LIMIT %(param_1)s]
[parameters: {'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/f405)

@willemarcel do you know something about these issues?

[willemarcel]

@Rub21 It seems the migration didn't create some new database tables. Could you check if the banner and release_version tables exist?

[Rub21]

According to what I have been reviewing, currently, we are using a very old version of the database, postgres:11 an postgis: 2.5. It should be updated first or migrated to a higher version of PostgreSQL.

So the plan would be:

• Upgrade the PostgreSQL version. @willemarcel, could you recommend which version would be best?
• Migrate the database backup to the new PostgreSQL version.
• Run the migrations with flask db upgrade.

After this, let’s see how it goes.

[Rub21]

According to the TM repo, it seems the PostgreSQL version could be "postgis/postgis:14-3.3" https://github.com/hotosm/tasking-manager/blob/develop/docker-compose.yml#L13

[Rub21]

After extensive debugging and thanks to @willemarcel for the advice, we successfully ran the Tasking Manager by upgrading the database from PostgreSQL 11 to PostgreSQL 14 and also updating the TM API source code.

Staging website: https://tasks-staging.openhistoricalmap.org

Currently, the statistics are not working on the frontend, but they are functioning on the API: https://tm-api.staging.openhistoricalmap.org/api/v4/system/statistics/. something that i still need to check.

[Rub21]

@erictheise Could you take a look at the stats section? It is not displaying, but the request actually returns data.

[erictheise]

We spawned a separate issue about the stats at #876 so it might be possible to close this one, @Rub21.