Merge pull request 'Check pooled database connections before reusing them' (!137) from fix-db-disconnect into main

Reviewed-on: #137

.drone.yml

@@ -13,9 +13,19 @@ trigger:
   branch:
     - main
 
+services:
+  - name: mariadb
+    image: mariadb:11.8.3@sha256:851a6020c97b9eae7736b6fb275800601d64635222054d3a1b1b3c4abdfa117a
+    environment:
+      MARIADB_ROOT_PASSWORD: notused
+      MARIADB_DATABASE: ki
+      MARIADB_USER: ki
+      MARIADB_PASSWORD: testing
+      TZ: Europe/Berlin
+
 steps:
   - name: install-lint-test
-    image: python:3.10.18-alpine@sha256:24cab748bf7bd8e3d2f9bb4e5771f17b628417527a4e1f2c59c370c2a8a27f1c
+    image: python:3.11.13-alpine@sha256:8d8c6d3808243160605925c2a7ab2dc5c72d0e75651699b0639143613e0855b8
     env:
       PYROOT: '/pyroot'
       PYTHONUSERBASE: '/pyroot'
@@ -26,7 +36,7 @@ steps:
       - pipenv install --dev
       - pipenv run flake8
       - pipenv run reuse lint
-      - SQLALCHEMY_DATABASE_URI=sqlite:// pipenv run python -m unittest discover ki
+      - SQLALCHEMY_DATABASE_URI=mariadb+pymysql://ki:testing@mariadb:3306/ki pipenv run python -m unittest discover ki
   - name: docker-dry-run
     image: plugins/docker:20.18.6@sha256:59c993e3c4e6c097a0e2d274419aac0d7d8e929773f0ba1af44078e54389834f
     settings:
@@ -102,9 +112,19 @@ trigger:
   event:
     - tag
 
+services:
+  - name: mariadb
+    image: mariadb:11.8.3@sha256:851a6020c97b9eae7736b6fb275800601d64635222054d3a1b1b3c4abdfa117a
+    environment:
+      MARIADB_ROOT_PASSWORD: notused
+      MARIADB_DATABASE: ki
+      MARIADB_USER: ki
+      MARIADB_PASSWORD: testing
+      TZ: Europe/Berlin
+
 steps:
   - name: install-lint-test
-    image: python:3.10.18-alpine@sha256:24cab748bf7bd8e3d2f9bb4e5771f17b628417527a4e1f2c59c370c2a8a27f1c
+    image: python:3.11.13-alpine@sha256:8d8c6d3808243160605925c2a7ab2dc5c72d0e75651699b0639143613e0855b8
     env:
       PYROOT: '/pyroot'
       PYTHONUSERBASE: '/pyroot'
@@ -114,7 +134,7 @@ steps:
       - pipenv install --dev
       - pipenv run flake8
       - pipenv run reuse lint
-      - SQLALCHEMY_DATABASE_URI=sqlite:// pipenv run python -m unittest discover ki
+      - SQLALCHEMY_DATABASE_URI=mariadb+pymysql://ki:testing@mariadb:3306/ki pipenv run python -m unittest discover ki
   - name: docker-publish
     image: plugins/docker:20.18.6@sha256:59c993e3c4e6c097a0e2d274419aac0d7d8e929773f0ba1af44078e54389834f
     settings:

.pre-commit-config.yaml

@@ -19,7 +19,7 @@ repos:
       exclude: ^(migrations).*$
     - id: unittest
       name: unittest
-      entry: python -m unittest discover ki
+      entry: env SQLALCHEMY_DATABASE_URI=sqlite:// python -m unittest discover ki
       language: system
       exclude: .*
       always_run: true

.python-version

@@ -1 +1 @@
-3.10.18
+3.11.13

Dockerfile

@@ -2,7 +2,7 @@
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 
-FROM python:3.10.18-alpine@sha256:24cab748bf7bd8e3d2f9bb4e5771f17b628417527a4e1f2c59c370c2a8a27f1c AS builder
+FROM python:3.11.13-alpine@sha256:8d8c6d3808243160605925c2a7ab2dc5c72d0e75651699b0639143613e0855b8 AS builder
 
 ENV PYROOT=/pyroot
 ENV PYTHONUSERBASE=$PYROOT
@@ -20,7 +20,7 @@ RUN PIP_USER=1 PIP_IGNORE_INSTALLED=1 pipenv install --system --deploy --ignore-
 RUN pip3 uninstall --yes pipenv
 
 
-FROM python:3.10.18-alpine@sha256:24cab748bf7bd8e3d2f9bb4e5771f17b628417527a4e1f2c59c370c2a8a27f1c AS ki-backend
+FROM python:3.11.13-alpine@sha256:8d8c6d3808243160605925c2a7ab2dc5c72d0e75651699b0639143613e0855b8 AS ki-backend
 
 ENV PYROOT=/pyroot
 ENV PYTHONUSERBASE=$PYROOT

Pipfile

@@ -27,7 +27,7 @@ pre-commit = "==2.21.0"
 reuse = "==4.0.3"
 
 [requires]
-python_version = "3.10"
+python_version = "3.11"
 
 [scripts]
 clean = "rm data/ki.sqlite"

Pipfile.lock

@@ -1,11 +1,11 @@
 {
     "_meta": {
         "hash": {
-            "sha256": "d02162eab1995247a9fd1bdd475877457c277b2a8c608a4df0366126b1051ed1"
+            "sha256": "5f6e83db58b1232fbef791f93bba91d2b1874da7ca2cbeca4a4e556d9f3aefb1"
         },
         "pipfile-spec": 6,
         "requires": {
-            "python_version": "3.10"
+            "python_version": "3.11"
         },
         "sources": [
             {

README.md

@@ -39,7 +39,7 @@ Folgende Kanäle gibt es für die Kommunikation über das Kompetenzinventar:
 
 ### Abhängigkeiten
 
-- Python 3.10
+- Python 3.11
 - [Pipenv](https://github.com/pypa/pipenv)
 
 

app.py

@@ -38,7 +38,7 @@ app.config["KI_LDAP_AUTH_PASSWORD"] = os.getenv("KI_LDAP_AUTH_PASSWORD")
 app.config["KI_LDAP_BASE_DN"] = os.getenv("KI_LDAP_BASE_DN")
 
 CORS(app)
-db = SQLAlchemy(app, session_options={"future": True})
+db = SQLAlchemy(app, session_options={"future": True}, engine_options={"pool_pre_ping": True})
 migrate = Migrate(app, db, compare_type=True)
 
 app.logger.info("Hello from KI")

ki/test/ApiTest.py

@@ -18,7 +18,6 @@ class ApiTest(unittest.TestCase):
         app.debug = True
         app.config["KI_AUTH"] = "file"
         app.config["TESTING"] = True
-        app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"
 
         self.client = app.test_client()
 
@@ -32,7 +31,8 @@ class ApiTest(unittest.TestCase):
 
     def tearDown(self):
         with app.app_context():
-            db.drop_all()
+            config = migrate.get_config()
+            command.downgrade(config, "base")
             db.engine.dispose()
 
     def login(self, username, password):

migrations/versions/b5023977cbda_extend_skill_length_to_50_chars.py

@@ -22,3 +22,10 @@ def upgrade():
                 existing_type=sa.VARCHAR(length=25),
                 type_=sa.String(length=50),
                 existing_nullable=False)
+
+def downgrade():
+    with op.batch_alter_table("skill") as batch_op:
+        batch_op.alter_column('name',
+                existing_type=sa.String(length=50),
+                type_=sa.VARCHAR(length=25),
+                existing_nullable=False)