Implement database model changes for services

[lkuchenb]

• Add model Service with fields
  • Service should have a site_id. Add to .ini config.
  • Service.id : PK
  • Service.name: str
• Add attribute MetaDatum.service_id [FK services.id, nullable]
• Add association table for n:m relationship users-services with relationship attributes
  • User.services
  • Service.users
• Add ServiceExecution model which logs which service has been executed by who for which dataset with fields
  • ServiceExecution.id : PK
  • ServiceExecution.service_id: FK Service.id
  • ServiceExecution.user_id: FK User.id
  • ServiceExecution.metadataset_id: FK MetadataSet.id
  • ServiceExecution.datetime: datetime

[MoritzHahn1337]

@lkuchenb

I have a couple of questions:

• Should the Service have a uuid as well?
• I am not familiar with n:m tables in alembic, is this what you were looking for?

class UserService(Base):
    __tablename__ = "users_services"
    id            = Column(Integer, primary_key=True)
    # Relationships
    user          = relationship('User')
    Service       = relationship('Service')

[MoritzHahn1337]

@lkuchenb

After some more digging, it now looks like this:

user_service_table = Table('service_user', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('service_id', Integer, ForeignKey('services.id'))
)

class Service(Base):
    __tablename__ = "services"
    id           = Column(Integer, primary_key=True)
    uuid         = Column(UUID(as_uuid=True), unique=True, default=uuid.uuid4, nullable=False)
    site_id      = Column(String(50), unique=True, nullable=False, index=True)
    name         = Column(Text, nullable=True)
    # Relationships
    users        = relationship("User",
                    secondary=user_service_table,
                    back_populates="services")

class ServiceExecution(Base):
    __tablename__    = "serviceecexution"
    id               = Column(Integer, primary_key=True) 
    service_id       = Column(Integer, ForeignKey('services.id'), nullable=False)    
    user_id          = Column(Integer, ForeignKey('users.id'), nullable=False)
    metadataset_id   = Column(Integer, ForeignKey('metadatasets.id'), nullable=False)
    datetime         = Column(DateTime, nullable=False)