From 139bee80df64897eb16032ae1a633507615da71c Mon Sep 17 00:00:00 2001 From: Michael Tremer Date: Wed, 2 Jul 2025 14:03:30 +0000 Subject: [PATCH] builds: Migrate to SQLModel (mostly) Signed-off-by: Michael Tremer --- src/buildservice/builds.py | 250 +++++++++++++++++++------------------ 1 file changed, 130 insertions(+), 120 deletions(-) diff --git a/src/buildservice/builds.py b/src/buildservice/builds.py index f4f82c7e..ece6736f 100644 --- a/src/buildservice/builds.py +++ b/src/buildservice/builds.py @@ -7,10 +7,10 @@ import itertools import logging import os import re - import sqlalchemy -from sqlalchemy import Column, ForeignKey, Index -from sqlalchemy import Boolean, DateTime, Integer, Text, UUID +import sqlmodel + +from uuid import UUID from . import base from . import builds @@ -30,7 +30,7 @@ log = logging.getLogger("pbs.builds") class Builds(base.Object): async def get_by_uuid(self, uuid): stmt = ( - sqlalchemy + sqlmodel .select(Build) .where( Build.deleted_at == None, @@ -354,7 +354,7 @@ class Builds(base.Object): return group -class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): +class Build(sqlmodel.SQLModel, database.BackendMixin, database.SoftDeleteMixin): __tablename__ = "builds" def __str__(self): @@ -368,7 +368,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # ID - id = Column(Integer, primary_key=True) + id: int = sqlmodel.Field(primary_key=True, exclude=True) @property def url(self): @@ -435,16 +435,20 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # UUID - uuid = Column(UUID, unique=True, nullable=False, - server_default=sqlalchemy.func.gen_random_uuid()) + uuid: UUID = sqlmodel.Field( + unique = True, + sa_column_kwargs = { + "server_default" : sqlalchemy.text("gen_random_uuid()"), + }, + ) # Package ID - pkg_id = Column(Integer, ForeignKey("packages.id"), nullable=False) + pkg_id: int = sqlmodel.Field(foreign_key="packages.id") # Package - pkg = sqlalchemy.orm.relationship("Package", foreign_keys=[pkg_id], lazy="selectin") + pkg: "Package" = sqlmodel.Relationship() @property def name(self): @@ -452,8 +456,9 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Created At - created_at = Column(DateTime(timezone=False), nullable=False, - server_default=sqlalchemy.func.current_timestamp()) + created_at: datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) # Date @@ -463,15 +468,15 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Finished At - finished_at = Column(DateTime(timezone=False)) + finished_at: datetime.datetime # Owner ID - owner_id = Column(Integer, ForeignKey("users.id")) + owner_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # Owner - owner = sqlalchemy.orm.relationship("User", foreign_keys=[owner_id], lazy="joined") + owner: "User" = sqlmodel.Relationship() # Is this a scratch build? @@ -488,11 +493,11 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Repo ID - repo_id = Column(Integer, ForeignKey("repositories.id"), nullable=False) + repo_id: int = sqlmodel.Field(foreign_key="repositories.id", exclude=True) # Repo - repo = sqlalchemy.orm.relationship("Repo", lazy="selectin") + repo: "Repo" = sqlmodel.Relationship() # Distro @@ -502,27 +507,25 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Group ID - group_id = Column(Integer, ForeignKey("build_groups.id")) + group_id: int = sqlmodel.Field(foreign_key="build_groups.id") # Group - group = sqlalchemy.orm.relationship( - "BuildGroup", foreign_keys=[group_id], back_populates="builds", lazy="selectin", - ) + group: "BuildGroup" = sqlmodel.Relationship(back_populates="builds") # Severity - severity = Column(Text) + severity : str # Commit ID - commit_id = Column(Integer, ForeignKey("source_commits.id")) + #commit_id = Column(Integer, ForeignKey("source_commits.id")) # Commit - commit = sqlalchemy.orm.relationship( - "SourceCommit", foreign_keys=[commit_id], lazy="selectin", - ) + #commit = sqlalchemy.orm.relationship( + # "SourceCommit", foreign_keys=[commit_id], lazy="selectin", + #) def has_perm(self, user): """ @@ -545,7 +548,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Priority - priority = Column(Integer, nullable=False, default=0) + priority: int = sqlmodel.Field(default=0) # Arches @@ -567,7 +570,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Jobs - This fetches all jobs that have ever existed for this build - alljobs = sqlalchemy.orm.relationship("Job", back_populates="build", lazy="joined") + #alljobs = sqlalchemy.orm.relationship("Job", back_populates="build", lazy="joined") @property def jobs(self): @@ -636,19 +639,19 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Notify await comment.notify() - comments = sqlalchemy.orm.relationship( - "BuildComment", back_populates="build", lazy="selectin", - ) + #comments = sqlalchemy.orm.relationship( + # "BuildComment", back_populates="build", lazy="selectin", + #) # Deleted By ID - deleted_by_id = Column(Integer, ForeignKey("users.id")) + #deleted_by_id = Column(Integer, ForeignKey("users.id")) # Deleted By - deleted_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[deleted_by_id], lazy="selectin", - ) + #deleted_by = sqlalchemy.orm.relationship( + # "User", foreign_keys=[deleted_by_id], lazy="selectin", + #) # Add Points @@ -666,7 +669,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Points - points = Column(Integer, nullable=False, default=0) + points : int = sqlmodel.Field(default=0) ## Watchers @@ -817,7 +820,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Failed - failed = Column(Boolean, nullable=False, default=False) + failed : bool = sqlmodel.Field(default=False) # Failed? @@ -865,16 +868,16 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Repos - repos = sqlalchemy.orm.relationship( - "Repo", - secondary = "repository_builds", - primaryjoin = """and_( - RepoBuild.build_id == Build.id, - RepoBuild.removed_at == None - )""", - viewonly = True, - lazy = "selectin", - ) + #repos = sqlalchemy.orm.relationship( + # "Repo", + # secondary = "repository_builds", + # primaryjoin = """and_( + # RepoBuild.build_id == Build.id, + # RepoBuild.removed_at == None + # )""", + # viewonly = True, + # lazy = "selectin", + #) async def _update_repos(self, build=False): """ @@ -1102,7 +1105,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Deprecated At - deprecated_at = Column(DateTime(timezone=False)) + deprecated_at : datetime.datetime | None # Deprecated? @@ -1119,21 +1122,21 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Deprecated By ID - deprecated_by_id = Column(Integer, ForeignKey("users.id")) + #deprecated_by_id = Column(Integer, ForeignKey("users.id")) # Deprecated By - deprecated_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[deprecated_by_id], lazy="selectin", - ) + #deprecated_by = sqlalchemy.orm.relationship( + # "User", foreign_keys=[deprecated_by_id], lazy="selectin", + #) # Deprecating Build ID - deprecating_build_id = Column(Integer, ForeignKey("builds.id")) + #deprecating_build_id = Column(Integer, ForeignKey("builds.id")) - deprecating_build = sqlalchemy.orm.relationship( - "Build", foreign_keys=[deprecating_build_id], - ) + #deprecating_build = sqlalchemy.orm.relationship( + # "Build", foreign_keys=[deprecating_build_id], + #) @functools.cached_property async def deprecated_builds(self): @@ -1172,7 +1175,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Tests Builds - test = Column(Boolean, nullable=False, default=False) + test : bool = sqlmodel.Field(default=False) # Test? @@ -1181,7 +1184,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): # Disable Test Builds? - disable_test_builds = Column(Boolean, nullable=False, default=False) + disable_test_builds : bool = sqlmodel.Field(default=False) async def create_test_builds(self): """ @@ -1297,7 +1300,7 @@ class Build(database.Base, database.BackendMixin, database.SoftDeleteMixin): return clone -class BuildGroup(database.Base, database.SoftDeleteMixin): +class BuildGroup(sqlmodel.SQLModel, database.SoftDeleteMixin, table=True): __tablename__ = "build_groups" def __str__(self): @@ -1319,50 +1322,50 @@ class BuildGroup(database.Base, database.SoftDeleteMixin): # ID - id = Column(Integer, primary_key=True) + id: int = sqlmodel.Field(primary_key=True, exclude=True) # UUID - uuid = Column(UUID, nullable=False, server_default=sqlalchemy.func.gen_random_uuid()) + uuid: UUID = sqlmodel.Field( + unique = True, + sa_column_kwargs = { + "server_default" : sqlalchemy.text("gen_random_uuid()"), + }, + ) # Created At - created_at = Column(DateTime(timezone=False), nullable=False, - server_default=sqlalchemy.func.current_timestamp()) + created_at: datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) # Created By ID - created_by_id = Column(Integer, ForeignKey("users.id")) + created_by_id: int = sqlmodel.Field(foreign_key="users.id") # Created By - created_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[created_by_id], lazy="selectin", - ) + created_by: "User" = sqlmodel.Relationship() # Deleted By ID - deleted_by_id = Column(Integer, ForeignKey("users.id")) + #deleted_by_id: int = sqlmodel.Field(foreign_key="users.id") # Deleted By - deleted_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[deleted_by_id], lazy="selectin", - ) + #deleted_by: "User" = sqlmodel.Relationship() # Finished At - finished_at = Column(DateTime(timezone=False), nullable=False) + finished_at: datetime.datetime # Failed - failed = Column(Boolean, nullable=False, default=False) + failed: bool = sqlmodel.Field(default=False) # Builds - builds = sqlalchemy.orm.relationship( - "Build", foreign_keys=[Build.group_id], back_populates="group", lazy="joined", - ) + builds: "Build" = sqlmodel.Relationship(back_populates="group") @staticmethod def _sort_builds(build): @@ -1394,13 +1397,11 @@ class BuildGroup(database.Base, database.SoftDeleteMixin): # Tested Build ID - tested_build_id = Column(Integer, ForeignKey("builds.id")) + tested_build_id: int = sqlmodel.Field(foreign_key="builds.id") # Tested Build - tested_build = sqlalchemy.orm.relationship( - "Build", foreign_keys=[tested_build_id], lazy="joined", - ) + tested_build: "Build" = sqlmodel.Relationship() # Test? @@ -1498,7 +1499,7 @@ class BuildGroup(database.Base, database.SoftDeleteMixin): self.failed = True -class BuildBug(database.Base): +class BuildBug(sqlmodel.SQLModel, table=True): __tablename__ = "build_bugs" def __str__(self): @@ -1506,48 +1507,45 @@ class BuildBug(database.Base): # ID - id = Column(Integer, primary_key=True) + id: int = sqlmodel.Field(primary_key=True, exclude=True) # Build ID - build_id = Column(Integer, ForeignKey("builds.id"), index=True, nullable=False) + build_id: int = sqlmodel.Field(foreign_key="builds.id", exclude=True) # Build - build = sqlalchemy.orm.relationship("Build") + build: "Build" = sqlmodel.Relationship() # Bug ID - bug_id = Column(Integer, nullable=False) + bug_id: int # Added At - added_at = Column(DateTime(timezone=False), nullable=False, - default=sqlalchemy.func.current_timestamp()) + added_at: datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) # Added By ID - added_by_id = Column(Integer, ForeignKey("users.id"), nullable=False) + added_by_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # Added By - added_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[added_by_id], lazy="joined", innerjoin=True, - ) + added_by: "User" = sqlmodel.Relationship() # Removed At - removed_at = Column(DateTime(timezone=False)) + removed_at: datetime.datetime # Removed By ID - removed_by_id = Column(Integer, ForeignKey("users.id")) + #removed_by_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # Removed ID - removed_by = sqlalchemy.orm.relationship( - "User", foreign_keys=[removed_by_id], lazy="selectin", - ) + #removed_by: "User" = sqlmodel.Relationship() # Remove! @@ -1562,37 +1560,39 @@ class BuildBug(database.Base): self.removed_by = removed_by -class BuildComment(database.Base, database.BackendMixin, database.SoftDeleteMixin): +class BuildComment(sqlmodel.SQLModel, database.BackendMixin, database.SoftDeleteMixin, table=True): __tablename__ = "build_comments" # ID - id = Column(Integer, primary_key=True) + id: int = sqlmodel.Field(primary_key=True, exclude=True) # Build ID - build_id = Column(Integer, ForeignKey("builds.id"), index=True, nullable=False) + build_id: int = sqlmodel.Field(foreign_key="builds.id", exclude=True) # Build - build = sqlalchemy.orm.relationship("Build", back_populates="comments") + build: "Build" = sqlmodel.Relationship(back_populates="comments") # User ID - user_id = Column(Integer, ForeignKey("users.id"), index=True, nullable=False) + # XXX Create an index on this field + user_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # User - user = sqlalchemy.orm.relationship("User") + user: "User" = sqlmodel.Relationship() # Text - text = Column(Text, nullable=False, default="") + text: str = sqlmodel.Field(default="") # Created At - created_at = Column(DateTime(timezone=False), nullable=False, - server_default=sqlalchemy.func.current_timestamp()) + created_at : datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) # Notify! @@ -1604,36 +1604,41 @@ class BuildComment(database.Base, database.BackendMixin, database.SoftDeleteMixi exclude=[self.user], build=self.build, comment=self) -class BuildPoint(database.Base, database.BackendMixin): +class BuildPoint(sqlmodel.SQLModel, database.BackendMixin, table=True): __tablename__ = "build_points" + # ID + + id: int = sqlmodel.Field(primary_key=True, exclude=True) + # Build ID - build_id = Column(Integer, ForeignKey("builds.id"), primary_key=True, nullable=False) + build_id: int = sqlmodel.Field(foreign_key="builds.id", exclude=True) # Build - build = sqlalchemy.orm.relationship("Build", foreign_keys=[build_id], lazy="selectin") + build: "Build" = sqlmodel.Relationship(back_populates="comments") # Created At - created_at = Column(DateTime(timezone=False), primary_key=True, - nullable=False, server_default=sqlalchemy.func.current_timestamp()) + created_at: datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) # Points - points = Column(Integer, nullable=False) + points: int # User ID - user_id = Column(Integer, ForeignKey("users.id")) + user_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # User - user = sqlalchemy.orm.relationship("User", foreign_keys=[user_id], lazy="selectin") + user: "User" = sqlmodel.Relationship() -class BuildWatcher(database.Base, database.BackendMixin, database.SoftDeleteMixin): +class BuildWatcher(sqlmodel.SQLModel, database.BackendMixin, database.SoftDeleteMixin, table=True): __tablename__ = "build_watchers" def __lt__(self, other): @@ -1642,23 +1647,28 @@ class BuildWatcher(database.Base, database.BackendMixin, database.SoftDeleteMixi return NotImplemented + # ID + + id: int = sqlmodel.Field(primary_key=True, exclude=True) + # Build ID - build_id = Column(Integer, ForeignKey("builds.id"), primary_key=True, nullable=False) + build_id: int = sqlmodel.Field(foreign_key="builds.id", exclude=True) # Build - build = sqlalchemy.orm.relationship("Build", lazy="selectin") + build: "Build" = sqlmodel.Relationship(back_populates="comments") # User ID - user_id = Column(Integer, ForeignKey("users.id"), primary_key=True, nullable=False) + user_id: int = sqlmodel.Field(foreign_key="users.id", exclude=True) # User - user = sqlalchemy.orm.relationship("User", lazy="joined", innerjoin=True) + user: "User" = sqlmodel.Relationship() # Added At - added_at = Column(DateTime(timezone=False), nullable=False, - server_default=sqlalchemy.func.current_timestamp()) + added_at: datetime.datetime = sqlmodel.Field( + sa_column_kwargs = {"server_default" : sqlalchemy.text("CURRENT_TIMESTAMP")} + ) -- 2.47.2