Song_ORM.py

songcommits_table = Table(
  'songcommits', Base.metadata,
  Column('commit_id', Integer, ForeignKey('commits.commit_id')),
  Column('song_id', Integer, ForeignKey('songs.song_id'))
)


class Commit(Base):

  __tablename__ = 'commits'

  commit_id = Column(Integer, primary_key=True)

  user = Column(
    String(24),
    CheckConstraint("TRIM(user) <> ''"), nullable=False
  )

  device = Column(
    String(48),
    CheckConstraint("TRIM(device) <> ''"), nullable=False
  )

  source = Column(
    String(24),
    CheckConstraint("TRIM(source) <> ''"), nullable=False
  )

  at = Column(
    DateTime,
    nullable=False, default=datetime.now, onupdate=datetime.now
  )

  note = Column(String(140))

  songs = relationship(
    "Song", secondary=songcommits_table, backref="commits"
  )

  stats = relationship('Stat', backref='commit')

  def __repr__(self):
    return "<Commit {0.commit_id}>".format(self)


class Song(Base):

  __tablename__ = 'songs'

  song_id = Column(Integer, primary_key=True)

  path = Column(
    String(250),
    CheckConstraint("TRIM(path) <> ''"),
    nullable=False, unique=True, index=True
  )

  stats = relationship(
    'Stat', backref='song'
  )

  def __repr__(self):
    return "<Song {0.song_id}>".format(self)


class Stat(Base):

  __tablename__ = 'stats'

  stat_id = Column(Integer, primary_key=True)

  commit_id = Column(Integer, ForeignKey('commits.commit_id'))  # backref: `commit`

  song_id = Column(Integer, ForeignKey('songs.song_id'))  # backref: `song`

  rating = Column(Integer, CheckConstraint('rating BETWEEN 0 and 5'))

  play_count = Column(Integer, CheckConstraint('play_count >= 0'))

  last_played = Column(DateTime)

  def __repr__(self):
    return "<Stat {0.stat_id}>".format(self)