Fix matrix model

shared/workspaces/workspaces/capability/schema.py

@@ -50,6 +50,7 @@ from workspaces.products.schema_interfaces import FutureProductIF
 from workspaces.system.schema import AbstractFile, JSONSerializable
 
 mapper_registry = registry()
+Base = mapper_registry.generate_base()
 
 logger = logging.getLogger(__name__)
 
@@ -967,6 +968,66 @@ class QaStaff(JSONSerializable):
         return QaStaff(**copy)
 
 
+matrix_capabilities = sa.Table(
+    "casa_matrix_capabilities",
+    Base.metadata,
+    sa.Column("matrix_id", sa.Integer, sa.ForeignKey("casa_matrix_casa_versions.matrix_id")),
+    sa.Column("capability_name", sa.String, sa.ForeignKey("capabilities.capability_name")),
+    sa.ForeignKeyConstraint(
+        ["matrix_id"], ["casa_matrix_casa_versions.matrix_id"], name="casa_matrix_capabilities_matrix_id_fkey"
+    ),
+    sa.ForeignKeyConstraint(
+        ["capability_name"], ["capabilities.capability_name"], name="casa_matrix_capabilities_capability_name_fkey"
+    ),
+)
+
+
+@mapper_registry.mapped
+class CasaMatrixCasaVersion(JSONSerializable):
+    """
+    Schema representation of a CASA version within the CASA Matrix
+    """
+
+    matrix_id: int
+    casa_version: str
+    is_cluster_compatible: bool
+
+    __tablename__ = "casa_matrix_casa_versions"
+
+    matrix_id = sa.Column(
+        "matrix_id",
+        sa.Integer,
+        primary_key=True,
+    )
+    casa_version = sa.Column(
+        "casa_version",
+        sa.String,
+        nullable=False,
+        unique=True,
+    )
+    is_cluster_compatible = sa.Column(
+        "is_cluster_compatible",
+        sa.Boolean,
+        default=True,
+    )
+    capabilities = relationship("Capability", back_populates="casa_versions", secondary=matrix_capabilities)
+
+    # Pyramid support method: must accept a "request" argument that is unused by us
+    def __json__(self, request=None) -> dict:
+        return {
+            "type": self.__class__.__name__,
+            "matrix_id": self.matrix_id,
+            "casa_version": self.casa_version,
+            "is_cluster_compatible": self.is_cluster_compatible,
+        }
+
+    def from_json(cls, json: dict) -> any:
+        return CasaMatrixCasaVersion(
+            casa_version=json["casa_version"],
+            is_cluster_compatible=json["is_cluster_compatible"],
+        )
+
+
 @mapper_registry.mapped
 class Capability(JSONSerializable):
     """
@@ -999,6 +1060,9 @@ class Capability(JSONSerializable):
     templates = relationship("CapabilityTemplate", backref="capabilities", lazy="dynamic")
     requests = relationship("CapabilityRequest", back_populates="capability")
     state_machine = relationship("StateMachine", back_populates="capability", uselist=False)
+    casa_versions = relationship(
+        "CasaMatrixCasaVersion", back_populates="capabilities", lazy=True, secondary=matrix_capabilities
+    )
 
     def create_request(self, parameters: ParameterIF, future_products: FutureProductIF):
         """
@@ -1702,61 +1766,3 @@ class CapabilityRequestStateSetter:
                     return False
             return True
         return False
-
-
-@mapper_registry.mapped
-class CasaMatrixCapability:
-    """
-    a bridge between the casa matrix versions and the available capabilities
-    """
-
-    __tablename__ = "casa_matrix_capabilities"
-    matrix_id = sa.Column(sa.ForeignKey("casa_matrix_casa_version.matrix_id"), primary_key=True)
-    capability_name = sa.Column(sa.ForeignKey("capabilities.capability_name"), primary_key=True)
-    capability = relationship("Capability")
-
-
-@mapper_registry.mapped
-class CasaMatrixCasaVersion(JSONSerializable):
-    """
-    Schema representation of a CASA version within the CASA Matrix
-    """
-
-    matrix_id: int
-    casa_version: str
-    is_cluster_compatible: bool
-
-    __tablename__ = "casa_matrix_casa_versions"
-
-    matrix_id = sa.Column(
-        "matrix_id",
-        sa.Integer,
-        primary_key=True,
-    )
-    casa_version = sa.Column(
-        "casa_version",
-        sa.String,
-        nullable=False,
-        unique=True,
-    )
-    is_cluster_compatible = sa.Column(
-        "is_cluster_compatible",
-        sa.Boolean,
-        default=True,
-    )
-    capabilities = relationship("CasaMatrixCapability")
-
-    # Pyramid support method: must accept a "request" argument that is unused by us
-    def __json__(self, request=None) -> dict:
-        return {
-            "type": self.__class__.__name__,
-            "matrix_id": self.matrix_id,
-            "casa_version": self.casa_version,
-            "is_cluster_compatible": self.is_cluster_compatible,
-        }
-
-    def from_json(cls, json: dict) -> any:
-        return CasaMatrixCasaVersion(
-            casa_version=json["casa_version"],
-            is_cluster_compatible=json["is_cluster_compatible"],
-        )