[DB] Updates and fixes to soccer schema

[babrar]

Issue Link: #5 --> Update Soccer DB Schema

• Enforce referential integrity between:
  • soccer games and their respective leagues
  • soccer events and their corresponding games
• Add a soccergameends event to signal the end of a soccer game

[sid42]

testing looked ok?

[babrar]

@sid42 @Shreyas15srivatsa Schema contains breaking changes. The new sport field in leagues is the culprit. Holding off on merge.

But since this is the new direction we are going in (#5), I'd suggest building mantle/stats off of this schema in your future work. Once everything is working again, this PR can be merged (after some updates).

[babrar]

Added more breaking changes since this already has breaking changes. Schema should be more accurate now.

[babrar]

@Shreyas15srivatsa Creating a basketball game by hitting /game/create with req body as: (yeah ik these teams don't play basketball 😬)

{
    "home_team": "Arsenal",
    "away_team": "Real Madrid"
}

Raises a 500 error with the following stacktrace:

2023-02-24T02:04:56.075041629Z 172.20.0.1 - - [24/Feb/2023 02:04:56] "POST /league/join HTTP/1.1" 200 -
2023-02-24T02:05:03.939941091Z 172.20.0.1 - - [24/Feb/2023 02:05:03] "POST /game/create HTTP/1.1" 500 -
2023-02-24T02:05:03.999229008Z Error on request:
2023-02-24T02:05:03.999259841Z Traceback (most recent call last):
2023-02-24T02:05:03.999262049Z   File "/usr/local/lib/python3.8/site-packages/werkzeug/serving.py", line 333, in run_wsgi
2023-02-24T02:05:03.999264008Z     execute(self.server.app)
2023-02-24T02:05:03.999265716Z   File "/usr/local/lib/python3.8/site-packages/werkzeug/serving.py", line 320, in execute
2023-02-24T02:05:03.999267508Z     application_iter = app(environ, start_response)
2023-02-24T02:05:03.999272758Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2551, in __call__
2023-02-24T02:05:03.999277508Z     return self.wsgi_app(environ, start_response)
2023-02-24T02:05:03.999279216Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2531, in wsgi_app
2023-02-24T02:05:03.999284299Z     response = self.handle_exception(e)
2023-02-24T02:05:03.999286091Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 271, in error_router
2023-02-24T02:05:03.999287883Z     return original_handler(e)
2023-02-24T02:05:03.999289508Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2528, in wsgi_app
2023-02-24T02:05:03.999291258Z     response = self.full_dispatch_request()
2023-02-24T02:05:03.999292924Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1825, in full_dispatch_request
2023-02-24T02:05:03.999294633Z     rv = self.handle_user_exception(e)
2023-02-24T02:05:03.999296258Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 271, in error_router
2023-02-24T02:05:03.999297966Z     return original_handler(e)
2023-02-24T02:05:03.999314883Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1823, in full_dispatch_request
2023-02-24T02:05:03.999316799Z     rv = self.dispatch_request()
2023-02-24T02:05:03.999318466Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1799, in dispatch_request
2023-02-24T02:05:03.999320549Z     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
2023-02-24T02:05:03.999322174Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 467, in wrapper
2023-02-24T02:05:03.999323758Z     resp = resource(*args, **kwargs)
2023-02-24T02:05:03.999325383Z   File "/usr/local/lib/python3.8/site-packages/flask/views.py", line 107, in view
2023-02-24T02:05:03.999327091Z     return current_app.ensure_sync(self.dispatch_request)(**kwargs)
2023-02-24T02:05:03.999328758Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 582, in dispatch_request
2023-02-24T02:05:03.999330591Z     resp = meth(*args, **kwargs)
2023-02-24T02:05:03.999332174Z   File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 154, in decorator
2023-02-24T02:05:03.999435883Z     return current_app.ensure_sync(fn)(*args, **kwargs)
2023-02-24T02:05:03.999442674Z   File "/backend/services/mantle/resources/game.py", line 25, in post
2023-02-24T02:05:03.999444341Z     stats_response = stats_client.CreateGame(
2023-02-24T02:05:03.999446049Z   File "/usr/local/lib/python3.8/site-packages/grpc/_channel.py", line 946, in __call__
2023-02-24T02:05:03.999447841Z     return _end_unary_response_blocking(state, call, False, None)
2023-02-24T02:05:03.999450008Z   File "/usr/local/lib/python3.8/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
2023-02-24T02:05:03.999451799Z     raise _InactiveRpcError(state)
2023-02-24T02:05:03.999453258Z grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
2023-02-24T02:05:03.999455258Z  status = StatusCode.UNKNOWN
2023-02-24T02:05:03.999456924Z  details = "Exception calling application: badly formed hexadecimal UUID string"
2023-02-24T02:05:03.999458758Z  debug_error_string = "UNKNOWN:Error received from peer ipv4:172.20.0.5:50052 {created_time:"2023-02-24T02:05:03.924934091+00:00", grpc_status:2, grpc_message:"Exception calling application: badly formed hexadecimal UUID string"}"
2023-02-24T02:05:03.999460924Z >

is this expected?

[sid42]

I think you need to provide league_id in the req body with it being a uuid

[KnotScientific]

@Shreyas15srivatsa Creating a basketball game by hitting /game/create with req body as: (yeah ik these teams don't play basketball 😬)

{
    "home_team": "Arsenal",
    "away_team": "Real Madrid"
}

Raises a 500 error with the following stacktrace:

2023-02-24T02:04:56.075041629Z 172.20.0.1 - - [24/Feb/2023 02:04:56] "POST /league/join HTTP/1.1" 200 -
2023-02-24T02:05:03.939941091Z 172.20.0.1 - - [24/Feb/2023 02:05:03] "POST /game/create HTTP/1.1" 500 -
2023-02-24T02:05:03.999229008Z Error on request:
2023-02-24T02:05:03.999259841Z Traceback (most recent call last):
2023-02-24T02:05:03.999262049Z   File "/usr/local/lib/python3.8/site-packages/werkzeug/serving.py", line 333, in run_wsgi
2023-02-24T02:05:03.999264008Z     execute(self.server.app)
2023-02-24T02:05:03.999265716Z   File "/usr/local/lib/python3.8/site-packages/werkzeug/serving.py", line 320, in execute
2023-02-24T02:05:03.999267508Z     application_iter = app(environ, start_response)
2023-02-24T02:05:03.999272758Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2551, in __call__
2023-02-24T02:05:03.999277508Z     return self.wsgi_app(environ, start_response)
2023-02-24T02:05:03.999279216Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2531, in wsgi_app
2023-02-24T02:05:03.999284299Z     response = self.handle_exception(e)
2023-02-24T02:05:03.999286091Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 271, in error_router
2023-02-24T02:05:03.999287883Z     return original_handler(e)
2023-02-24T02:05:03.999289508Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2528, in wsgi_app
2023-02-24T02:05:03.999291258Z     response = self.full_dispatch_request()
2023-02-24T02:05:03.999292924Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1825, in full_dispatch_request
2023-02-24T02:05:03.999294633Z     rv = self.handle_user_exception(e)
2023-02-24T02:05:03.999296258Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 271, in error_router
2023-02-24T02:05:03.999297966Z     return original_handler(e)
2023-02-24T02:05:03.999314883Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1823, in full_dispatch_request
2023-02-24T02:05:03.999316799Z     rv = self.dispatch_request()
2023-02-24T02:05:03.999318466Z   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1799, in dispatch_request
2023-02-24T02:05:03.999320549Z     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
2023-02-24T02:05:03.999322174Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 467, in wrapper
2023-02-24T02:05:03.999323758Z     resp = resource(*args, **kwargs)
2023-02-24T02:05:03.999325383Z   File "/usr/local/lib/python3.8/site-packages/flask/views.py", line 107, in view
2023-02-24T02:05:03.999327091Z     return current_app.ensure_sync(self.dispatch_request)(**kwargs)
2023-02-24T02:05:03.999328758Z   File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 582, in dispatch_request
2023-02-24T02:05:03.999330591Z     resp = meth(*args, **kwargs)
2023-02-24T02:05:03.999332174Z   File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 154, in decorator
2023-02-24T02:05:03.999435883Z     return current_app.ensure_sync(fn)(*args, **kwargs)
2023-02-24T02:05:03.999442674Z   File "/backend/services/mantle/resources/game.py", line 25, in post
2023-02-24T02:05:03.999444341Z     stats_response = stats_client.CreateGame(
2023-02-24T02:05:03.999446049Z   File "/usr/local/lib/python3.8/site-packages/grpc/_channel.py", line 946, in __call__
2023-02-24T02:05:03.999447841Z     return _end_unary_response_blocking(state, call, False, None)
2023-02-24T02:05:03.999450008Z   File "/usr/local/lib/python3.8/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
2023-02-24T02:05:03.999451799Z     raise _InactiveRpcError(state)
2023-02-24T02:05:03.999453258Z grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
2023-02-24T02:05:03.999455258Z    status = StatusCode.UNKNOWN
2023-02-24T02:05:03.999456924Z    details = "Exception calling application: badly formed hexadecimal UUID string"
2023-02-24T02:05:03.999458758Z    debug_error_string = "UNKNOWN:Error received from peer ipv4:172.20.0.5:50052 {created_time:"2023-02-24T02:05:03.924934091+00:00", grpc_status:2, grpc_message:"Exception calling application: badly formed hexadecimal UUID string"}"
2023-02-24T02:05:03.999460924Z >

is this expected?

Need to pass usertoken/JWT when u login as the header

[Shreyas15srivatsa]

I think you need to provide league_id in the req body with it being a uuid

Yep, you're missing the league_id

[Shreyas15srivatsa]

What does psycopg2.extras.register_uuid() do?

It's apparently needed to pass uuid to the Postgres cursor. Source: https://stackoverflow.com/a/59268003

[babrar]

Yep, you're missing the league_id

LGTM. Works now. Good for merge ✅