add pagination

Reviewed-on: kompetenzinventar/ki-backend#54

.drone.yml

@@ -26,6 +26,9 @@ steps:
       from_secret: "docker_username"
     password:
       from_secret: "docker_password"
+  when:
+    branch:
+      - main
 
 image_pull_secrets:
 - dockerconfig

.pre-commit-config.yaml

@@ -2,6 +2,7 @@
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 
+repos:
 - repo: local
   hooks:
     - id: flake8

README.md

@@ -22,7 +22,7 @@ SPDX-License-Identifier: AGPL-3.0-or-later
 Ggf. vorher aufräumen
 
 ```
-rm data/ki.sqlite
+rm storage/ki.sqlite
 ```
 
 ```

app.py

@@ -38,7 +38,7 @@ app.config["KI_LDAP_BASE_DN"] = os.getenv("KI_LDAP_BASE_DN")
 
 CORS(app)
 db = SQLAlchemy(app)
-migrate = Migrate(app, db)
+migrate = Migrate(app, db, compare_type=True)
 
 logging.debug("Hello from KI")
 

ki/actions/seed.py

@@ -26,7 +26,15 @@ def seed_contacttypes():
                 db.session.add(ContactType(id=int(contacttype["id"]), name=contacttype["name"]))
 
 
-def seed_user(nickname, visible=False, skills=[], languages=[], volunteerwork="", availability="", freetext=""):
+def seed_user(nickname,
+              visible=False,
+              skills=[],
+              languages=[],
+              volunteerwork="",
+              availability_status=False,
+              freetext="",
+              availability_text="",
+              availability_hours_per_week=42):
     app.logger.info(f"seeding {nickname} \\o/")
 
     user = User(auth_id=nickname)
@@ -35,7 +43,9 @@ def seed_user(nickname, visible=False, skills=[], languages=[], volunteerwork=""
     profile = Profile(nickname=nickname,
                       pronouns="",
                       volunteerwork=volunteerwork,
-                      availability=availability,
+                      availability_status=availability_status,
+                      availability_text=availability_text,
+                      availability_hours_per_week=availability_hours_per_week,
                       freetext=freetext,
                       visible=visible,
                       user=user)
@@ -91,7 +101,9 @@ def seed(dev: bool):
         peters_profile = Profile(nickname="peternichtlustig",
                                  pronouns="Herr Dr. Dr.",
                                  volunteerwork="Gartenverein",
-                                 availability="Immer",
+                                 availability_status=True,
+                                 availability_hours_per_week=42,
+                                 availability_text="Immer",
                                  freetext="Ich mag Kaffee",
                                  user=peter)
         db.session.add(peters_profile)
@@ -131,11 +143,15 @@ def seed(dev: bool):
         db.session.add(peter_fr)
 
         seed_user("klaus")
+        for i in range(1, 20):
+            seed_user(f"babsi{i}", visible=True)
 
         seed_user("dirtydieter",
                   visible=True,
                   volunteerwork="Müll sammeln",
-                  availability="Nur nachts",
+                  availability_status=True,
+                  availability_hours_per_week=24,
+                  availability_text="Nur Nachts!",
                   freetext="1001010010111!!!",
                   skills=[(Skill.skill_id_php, 5)])
 

ki/handlers/find_profiles.py

@@ -8,7 +8,10 @@ from ki.models import Profile, ProfileSkill, Skill, ProfileLanguage, Language
 
 
 def find_profiles():
-    page = int(request.args.get("page", 1))
+    try:
+        page = int(request.args.get("page", 1))
+    except ValueError:
+        page = 1
 
     if page < 1:
         return make_response({"messages": {"page": "Die angefragte Seite muss mindestens 1 sein"}}, 400)
@@ -19,6 +22,7 @@ def find_profiles():
         return make_response({"messages": {"page_size": "Die maximale Anzahl Einträge pro Seite beträgt 100"}}, 400)
 
     query = Profile.query.distinct(Profile.id) \
+        .order_by(Profile.nickname) \
         .filter(Profile.visible.is_(True)) \
         .join(Profile.skills, isouter=True).join(ProfileSkill.skill, isouter=True) \
         .join(Profile.languages, isouter=True).join(ProfileLanguage.language, isouter=True)
@@ -33,13 +37,15 @@ def find_profiles():
         nickname = request.args.get("nickname")
         query = query.filter(Profile.nickname.like(f"%{nickname}%"))
 
-    count = query.count()
-
-    offset = (page - 1) * page_size
-    db_profiles = query.limit(page_size).offset(offset).all()
+    paginated_result = query.paginate(page=page, per_page=page_size)
     api_profiles = []
 
-    for db_profile in db_profiles:
+    for db_profile in paginated_result.items:
         api_profiles.append(db_profile.to_dict())
 
-    return make_response({"total": count, "profiles": api_profiles})
+    return make_response({
+        "total": paginated_result.total,
+        "pages": paginated_result.pages,
+        "page": paginated_result.page,
+        "profiles": api_profiles
+    })

ki/handlers/update_profile.py

@@ -30,19 +30,19 @@ def update_languages(profile, languages_data):
     profile_language_ids = []
 
     for language_data in languages_data:
-        language_id = language_data["language"]["id"]
-        language = Language.query.get(language_id)
+        if "id" not in language_data["language"]:
+            continue
 
+        language = Language.query.get(language_data["language"]["id"])
         profile_language = ProfileLanguage.query.filter(ProfileLanguage.profile == profile,
-                                                        ProfileLanguage.language_id == language_id).first()
+                                                        ProfileLanguage.language == language).first()
 
         if profile_language is None:
             profile_language = ProfileLanguage(profile=profile, language=language)
             db.session.add(profile_language)
 
         profile_language.level = language_data["level"]
-
-        profile_language_ids.append(language_id)
+        profile_language_ids.append(language.id)
 
     ProfileLanguage.query.filter(ProfileLanguage.profile == profile,
                                  not_(ProfileLanguage.language_id.in_(profile_language_ids))).delete()
@@ -133,9 +133,21 @@ def update_profile(user_id: int):
         profile = Profile(user=user, nickname=user.auth_id)
         db.session.add(profile)
 
+    profile.nickname = request.json.get("nickname", "")
     profile.pronouns = request.json.get("pronouns", "")
     profile.volunteerwork = request.json.get("volunteerwork", "")
-    profile.availability = request.json.get("availability", "")
+    profile.availability_status = request.json.get("availability_status", False)
+    profile.availability_text = request.json.get("availability_text", "")
+
+    availability_hours_per_week_raw = request.json.get("availability_hours_per_week", 0)
+
+    try:
+        availability_hours_per_week = int(availability_hours_per_week_raw)
+    except:
+        availability_hours_per_week = None
+
+    profile.availability_hours_per_week = availability_hours_per_week
+
     profile.freetext = request.json.get("freetext", "")
     profile.visible = request.json.get("visible", False)
 

ki/models.py

@@ -32,7 +32,11 @@ class Profile(db.Model):
     pronouns = Column(String(25), default="")
     volunteerwork = Column(String(4000), default="")
     freetext = Column(String(4000), default="")
-    availability = Column(String(4000), default="")
+
+    availability_status = Column(Boolean, default=False)
+    availability_text = Column(String(4000), default="")
+    availability_hours_per_week = Column(Integer, default=0)
+
     visible = Column(Boolean, nullable=False, default=False)
     created = Column(DateTime, nullable=False, default=datetime.now)
     updated = Column(DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)
@@ -50,7 +54,9 @@ class Profile(db.Model):
             "nickname": self.nickname,
             "pronouns": self.pronouns,
             "volunteerwork": self.volunteerwork,
-            "availability": self.availability,
+            "availability_status": self.availability_status,
+            "availability_text": self.availability_text,
+            "availability_hours_per_week": self.availability_hours_per_week,
             "freetext": self.freetext,
             "visible": self.visible,
             "address": self.address.to_dict() if self.address else None,
@@ -143,7 +149,7 @@ class Skill(db.Model):
     __tablename__ = "skill"
 
     id = Column(Integer, primary_key=True)
-    name = Column(String(25), unique=True, nullable=False)
+    name = Column(String(50), unique=True, nullable=False)
 
     profiles = relationship("ProfileSkill", back_populates="skill")
     searchtopics = relationship("ProfileSearchtopic", back_populates="skill")

ki/test/test_find_profiles_endpoint.py

@@ -20,25 +20,33 @@ class TestFindProfilesEndpoint(ApiTest):
         response = self.client.get("/users/profiles?nickname=horsthorsthorst",
                                    headers={"Authorization": "Bearer " + token})
         self.assertEqual(response.status_code, 200)
-        self.assertEqual(response.json, {"total": 0, "profiles": []})
+        self.assertEqual(response.json, {"total": 0, "page": 1, "pages": 0, "profiles": []})
 
     def test_find_sql_specialchars(self):
         token = self.login("peter", "geheim")["token"]
 
         response = self.client.get("/users/profiles?nickname=%22%27%25", headers={"Authorization": "Bearer " + token})
         self.assertEqual(response.status_code, 200)
-        self.assertEqual(response.json, {"total": 0, "profiles": []})
+        self.assertEqual(response.json, {"total": 0, "page": 1, "pages": 0, "profiles": []})
 
-    def test_find_all(self):
+    def test_find_all_page1(self):
         token = self.login("peter", "geheim")["token"]
 
         response = self.client.get("/users/profiles", headers={"Authorization": "Bearer " + token})
         self.assertEqual(response.status_code, 200)
-        self.assertDictContainsSubset({"total": 4}, response.json)
-        self.assertDictContainsSubset({"nickname": "dirtydieter"}, response.json["profiles"][0])
+        self.assertDictContainsSubset({"total": 23, "page": 1, "pages": 2}, response.json)
+        self.assertDictContainsSubset({"nickname": "babsi1"}, response.json["profiles"][0])
+        self.assertDictContainsSubset({"nickname": "dirtydieter"}, response.json["profiles"][19])
+
+    def test_find_all_page2(self):
+        token = self.login("peter", "geheim")["token"]
+
+        response = self.client.get("/users/profiles?page=2", headers={"Authorization": "Bearer " + token})
+        self.assertEqual(response.status_code, 200)
+        self.assertDictContainsSubset({"total": 23, "page": 2, "pages": 2}, response.json)
+        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][0])
         self.assertDictContainsSubset({"nickname": "jutta"}, response.json["profiles"][1])
-        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][2])
-        self.assertDictContainsSubset({"nickname": "monique"}, response.json["profiles"][3])
+        self.assertDictContainsSubset({"nickname": "monique"}, response.json["profiles"][2])
 
     def test_find_dieter(self):
         token = self.login("peter", "geheim")["token"]
@@ -62,8 +70,8 @@ class TestFindProfilesEndpoint(ApiTest):
         response = self.client.get("/users/profiles?search=sql", headers={"Authorization": "Bearer " + token})
         self.assertEqual(response.status_code, 200)
         self.assertDictContainsSubset({"total": 2}, response.json)
-        self.assertDictContainsSubset({"nickname": "jutta"}, response.json["profiles"][0])
-        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][1])
+        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][0])
+        self.assertDictContainsSubset({"nickname": "jutta"}, response.json["profiles"][1])
 
     def test_find_postgres(self):
         token = self.login("peter", "geheim")["token"]
@@ -71,8 +79,8 @@ class TestFindProfilesEndpoint(ApiTest):
         response = self.client.get("/users/profiles?search=post", headers={"Authorization": "Bearer " + token})
         self.assertEqual(response.status_code, 200)
         self.assertDictContainsSubset({"total": 2}, response.json)
-        self.assertDictContainsSubset({"nickname": "jutta"}, response.json["profiles"][0])
-        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][1])
+        self.assertDictContainsSubset({"nickname": "giesela"}, response.json["profiles"][0])
+        self.assertDictContainsSubset({"nickname": "jutta"}, response.json["profiles"][1])
 
     def test_find_php_franzosen(self):
         token = self.login("peter", "geheim")["token"]

ki/test/test_profile_endpoint.py

@@ -31,9 +31,12 @@ class TestProfileEndpoint(ApiTest):
         token = self.login("peter", "geheim")["token"]
 
         data = {
+            "nickname": "Hebbert",
             "pronouns": "Monsieur",
             "volunteerwork": "ja",
-            "availability": "Nie",
+            "availability_status": False,
+            "availability_text": "Nie",
+            "availability_hours_per_week": "23",
             "freetext": "Hallo",
             "visible": True,
             "address": {
@@ -108,9 +111,12 @@ class TestProfileEndpoint(ApiTest):
         with app.app_context():
             user = User.query.filter(User.id == 1).first()
             profile = user.profile
+            self.assertEqual("Hebbert", profile.nickname)
             self.assertEqual("Monsieur", profile.pronouns)
             self.assertEqual("ja", profile.volunteerwork)
-            self.assertEqual("Nie", profile.availability)
+            self.assertEqual(False, profile.availability_status)
+            self.assertEqual("Nie", profile.availability_text)
+            self.assertEqual(23, profile.availability_hours_per_week)
             self.assertEqual("Hallo", profile.freetext)
             self.assertTrue(profile.visible)
 
@@ -198,7 +204,9 @@ class TestProfileEndpoint(ApiTest):
                     "user_id": 1,
                     "nickname": "peternichtlustig",
                     "pronouns": "Herr Dr. Dr.",
-                    "availability": "Immer",
+                    "availability_status": True,
+                    "availability_hours_per_week": 42,
+                    "availability_text": "Immer",
                     "freetext": "Ich mag Kaffee",
                     "volunteerwork": "Gartenverein",
                     "visible": False,

migrations/versions/459520b01f34_.py

@@ -0,0 +1,41 @@
+"""empty message
+
+Revision ID: 459520b01f34
+Revises: 9183e2335b05
+Create Date: 2021-10-03 14:45:30.389359
+
+"""
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.sql import expression
+
+# revision identifiers, used by Alembic.
+revision = '459520b01f34'
+down_revision = '9183e2335b05'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    # ### commands auto generated by Alembic - please adjust! ###
+    with op.batch_alter_table("profile") as batch_op:
+        batch_op.alter_column('availability',
+                              new_column_name='availability_text',
+                              existing_type=sa.String(4000),
+                              existing_server_default="")
+        batch_op.add_column(
+            sa.Column('availability_status', sa.Boolean(), server_default=expression.true(), nullable=False))
+        batch_op.add_column(sa.Column('availability_hours_per_week', sa.Integer(), nullable=True))
+    # ### end Alembic commands ###
+
+
+def downgrade():
+    # ### commands auto generated by Alembic - please adjust! ###
+    with op.batch_alter_table("profile") as batch_op:
+        batch_op.alter_column('availability_text',
+                              new_column_name='availability',
+                              existing_type=sa.String(4000),
+                              existing_serve_server_default="")
+        batch_op.drop_column('availability_hours_per_week')
+        batch_op.drop_column('availability_status')
+    # ### end Alembic commands ###

migrations/versions/b5023977cbda_extend_skill_length_to_50_chars.py

@@ -0,0 +1,24 @@
+"""extend skill length to 50 chars
+
+Revision ID: b5023977cbda
+Revises: 459520b01f34
+Create Date: 2021-11-22 20:07:19.188217
+
+"""
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision = 'b5023977cbda'
+down_revision = '459520b01f34'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    with op.batch_alter_table("skill") as batch_op:
+        batch_op.alter_column('name',
+                existing_type=sa.VARCHAR(length=25),
+                type_=sa.String(length=50),
+                existing_nullable=False)