models.py

#
# Aura Engine (https://gitlab.servus.at/aura/engine)
#
# Copyright (C) 2017-2020 - The Aura Engine Team.
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.


import sys
import time
import logging
import datetime

import sqlalchemy as sa

from sqlalchemy import BigInteger, Boolean, Column, DateTime, Integer, String, ForeignKey, ColumnDefault
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property

from src.base.config import AuraConfig
from src.base.utils import SimpleUtil
from src.resources import ResourceUtil

# Initialize DB Model and session
config = AuraConfig()
engine = sa.create_engine(config.get_database_uri())
Base = declarative_base()
Base.metadata.bind = engine

class DB():
    session_factory = sessionmaker(bind=engine)
    Session = scoped_session(session_factory)
    session = Session()
    Model = Base



class AuraDatabaseModel():
    """
    AuraDataBaseModel.

    Holding all tables and relationships for the engine.
    """
    logger = None


    def __init__(self):
        """
        Constructor.
        """
        self.logger = logging.getLogger("AuraEngine")


    def store(self, add=False, commit=False):
        """
        Store to the database
        """
        if add:
            DB.session.add(self)
        else:
            DB.session.merge(self)
        if commit:
            DB.session.commit()


    def delete(self, commit=False):
        """
        Delete from the database
        """
        DB.session.delete(self)
        if commit:
            DB.session.commit()


    def refresh(self):
        """
        Refreshes the correct record
        """
        DB.session.expire(self)
        DB.session.refresh(self)


    def _asdict(self):
        return self.__dict__


    @staticmethod
    def init_database():
        """
        Initializes the database tables if they are not existing.

        Raises:
            sqlalchemy.exc.ProgrammingError:    In case the DB model is invalid
        """
        try:
            Playlist.is_empty()
        except sa.exc.ProgrammingError as e:
            is_available = True

            # PostgreSQL table not available
            if e.code == "f405":
                is_available = False
            # MariaDB table not available
            elif e.orig.args[0] == 1146:
                is_available = False

            if not is_available:
                model = AuraDatabaseModel()
                model.recreate_db()
            else:
                raise


    @staticmethod
    def recreate_db(systemexit = False):
        """
        Deletes all tables and re-creates the database.
        """
        Base.metadata.drop_all()
        Base.metadata.create_all()
        DB.session.commit()

        if systemexit:
            sys.exit(0)



#
#   TIMESLOT
#


class Timeslot(DB.Model, AuraDatabaseModel):
    """
    One specific timeslot for a show.

    Relationships:
        playlist (Playlist):            The specific playlist for this timeslot
        schedule_default (Playlist):    Some playlist played by default, when no specific playlist is assigned
        show_default (Playlist):        Some playlist played by default, when no default schedule playlist is assigned
        schedule_fallback (Playlist):   Some playlist played as fallback, when no specific playlist is assigned or if it is erroneous (includes silence detection)
        show_fallback (Playlist):       Some playlist played as fallback, when no schedule fallback playlist is assigned or if some specific playlist is erroneous (includes silence detection)
        station_fallback (Playlist):    Defined in the original AURA API but not implemented, as station fallbacks are handled locally
    """
    __tablename__ = 'timeslot'

    # Primary keys
    id = Column(Integer, primary_key=True, autoincrement=True)

    # Relationships
    playlist = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.playlist_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")
    default_schedule_playlist = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.default_schedule_playlist_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")
    default_show_playlist = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.default_show_playlist_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")
    schedule_fallback = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.schedule_fallback_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")
    show_fallback = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.show_fallback_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")
    station_fallback = relationship("Playlist",
                            primaryjoin="and_(Timeslot.timeslot_start==Playlist.timeslot_start, \
                                Timeslot.station_fallback_id==Playlist.playlist_id, Timeslot.show_name==Playlist.show_name)",
                            uselist=False, back_populates="timeslot")

    playlist_id = Column(Integer)
    default_schedule_playlist_id = Column(Integer)
    default_show_playlist_id = Column(Integer)
    schedule_fallback_id = Column(Integer)
    show_fallback_id = Column(Integer)
    station_fallback_id = Column(Integer)

    # Data
    timeslot_start = Column(DateTime, unique=True, index=True)
    timeslot_end = Column(DateTime, unique=True, index=True)
    timeslot_id = Column(Integer, unique=True)

    show_id = Column(Integer)
    show_name = Column(String(256))
    show_hosts = Column(String(256))
    funding_category = Column(String(256))
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
    is_repetition = Column(Boolean())

    # Transients
    active_entry = None


    @staticmethod
    def for_datetime(date_time):
        """
        Select a timeslot at the given datetime.

        Args:
            date_time (datetime): date and time when the timeslot starts
        """
        return DB.session.query(Timeslot).filter(Timeslot.timeslot_start == date_time).first()


    @staticmethod
    def get_timeslots(date_from=datetime.date.today()):
        """
        Select all timeslots starting from `date_from` or from today if no
        parameter is passed.

        Args:
            date_from (datetime):   Select timeslots from this date and time on

        Returns:
            ([Timeslot]):           List of timeslots
        """
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start >= date_from).\
            order_by(Timeslot.timeslot_start).all()
        return timeslots


    def set_active_entry(self, entry):
        """
        Sets the currently playing entry.

        Args:
            entry (PlaylistEntry): The entry playing right now
        """
        self.active_entry = entry


    def get_recent_entry(self):
        """
        Retrieves the most recent played or currently playing entry. This is used to fade-out
        the timeslot, when there is no other entry is following the current one.
        """
        return self.active_entry


    @hybrid_property
    def start_unix(self):
        """
        Start time of the timeslot in UNIX time.
        """
        return time.mktime(self.timeslot_start.timetuple())


    @hybrid_property
    def end_unix(self):
        """
        End time of the timeslot in UNIX time.
        """
        return time.mktime(self.timeslot_end.timetuple())


    def as_dict(self):
        """
        Returns the timeslot as a dictionary for serialization.
        """
        playlist = self.playlist

        return {
            "timeslot_id": self.timeslot_id,
            "timeslot_start": self.timeslot_start.isoformat(),
            "timeslot_end": self.timeslot_end.isoformat(),

            "topic": self.topic,
            "musicfocus": self.musicfocus,
            "funding_category": self.funding_category,
            "is_repetition": self.is_repetition,
            "category": self.category,
            "languages": self.languages,
            "comment": self.comment,
            "playlist_id": self.playlist_id,
            "schedule_default_id": self.schedule_default_id,
            "show_default_id": self.show_default_id,
            "schedule_fallback_id": self.schedule_fallback_id,
            "show_fallback_id": self.show_fallback_id,
            "station_fallback_id": self.station_fallback_id,

            "show": {
                "name": self.show_name,
                "host": self.show_hosts
            },

            "playlist": playlist
        }


    def __str__(self):
        """
        String representation of the object.
        """
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
        return "ID#%s [Show: %s, ShowID: %s | %s - %s ]" % (str(self.timeslot_id), self.show_name, str(self.show_id), time_start, time_end)


#
#   PLAYLIST
#

class Playlist(DB.Model, AuraDatabaseModel):
    """
    The playlist containing playlist entries.
    """
    __tablename__ = 'playlist'

    # Static Playlist Types
    TYPE_TIMESLOT = { "id": 0, "name": "timeslot" }
    TYPE_SCHEDULE = { "id": 1, "name": "schedule" }
    TYPE_SHOW = { "id": 2, "name": "show" }

    # Primary and Foreign Key
    artificial_id = Column(Integer, primary_key=True)
    timeslot_start = Column(DateTime, ForeignKey("timeslot.timeslot_start"))

    # Relationships
    timeslot = relationship("Timeslot", uselist=False, back_populates="playlist")
    entries = relationship("PlaylistEntry", back_populates="playlist")

    # Data
    playlist_id = Column(Integer, autoincrement=False)
    show_name = Column(String(256))
    entry_count = Column(Integer)


    # @staticmethod
    # def select_all():
    #     """
    #     Fetches all entries
    #     """
    #     all_entries = DB.session.query(Playlist).filter(Playlist.fallback_type == 0).all()

    #     cnt = 0
    #     for entry in all_entries:
    #         entry.programme_index = cnt
    #         cnt = cnt + 1

    #     return all_entries


    @staticmethod
    def select_playlist_for_timeslot(start_date, playlist_id):
        """
        Retrieves the playlist for the given timeslot identified by `start_date` and `playlist_id`

        Args:
            start_date (datetime):  Date and time when the playlist is scheduled
            playlist_id (Integer):  The ID of the playlist

        Returns:
            (Playlist):             The playlist, if existing for timeslot

        Raises:
            Exception:              In case there a inconsistent database state, such es multiple playlists for given date/time.
        """
        playlist = None
        playlists = DB.session.query(Playlist).filter(Playlist.timeslot_start == start_date).all()

        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p

        return playlist


    @staticmethod
    def select_playlist(playlist_id):
        """
        Retrieves all paylists for that given playlist ID.

        Args:
            playlist_id (Integer):  The ID of the playlist

        Returns:
            (Array<Playlist>):      An array holding the playlists
        """
        return DB.session.query(Playlist).filter(Playlist.playlist_id == playlist_id).order_by(Playlist.timeslot_start).all()


    @staticmethod
    def is_empty():
        """
        Checks if the given is empty
        """
        try:
            return not DB.session.query(Playlist).one_or_none()
        except sa.orm.exc.MultipleResultsFound:
            return False


    @hybrid_property
    def start_unix(self):
        """
        Start time of the playlist in UNIX time.
        """
        return time.mktime(self.timeslot_start.timetuple())


    @hybrid_property
    def end_unix(self):
        """
        End time of the playlist in UNIX time.
        """
        return time.mktime(self.timeslot_start.timetuple()) + self.duration


    @hybrid_property
    def duration(self):
        """
        Returns the total length of the playlist in seconds.

        Returns:
            (Integer):  Length in seconds
        """
        total = 0

        for entry in self.entries:
            total += entry.duration
        return total


    # def as_dict(self):
    #     """
    #     Returns the playlist as a dictionary for serialization.
    #     """
    #     entries = []
    #     for e in self.entries:
    #         entries.append(e.as_dict())

    #     playlist = {
    #         "playlist_id": self.playlist_id,
    #         "fallback_type": self.fallback_type,
    #         "entry_count": self.entry_count,
    #         "entries": entries
    #     }
    #     return playlist


    def __str__(self):
        """
        String representation of the object.
        """
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
        return "ID#%s [items: %s | %s - %s]" % (str(self.playlist_id), str(self.entry_count), str(time_start), str(time_end))


#
#   PLAYLIST ENTRY
#

class PlaylistEntry(DB.Model, AuraDatabaseModel):
    """
    Playlist entries are the individual items of a playlist such as audio files.
    """
    __tablename__ = 'playlist_entry'

    # Primary and Foreign Keys
    artificial_id = Column(Integer, primary_key=True)
    artificial_playlist_id = Column(Integer, ForeignKey("playlist.artificial_id"))

    # Relationships
    playlist = relationship("Playlist", uselist=False, back_populates="entries")
    meta_data = relationship("PlaylistEntryMetaData", uselist=False, back_populates="entry")

    # Data
    entry_num = Column(Integer)
    duration = Column(BigInteger)
    volume = Column(Integer, ColumnDefault(100))
    source = Column(String(1024))
    entry_start = Column(DateTime)

    # Transients
    entry_start_actual = None # Assigned when the entry is actually played
    channel = None # Assigned when entry is actually played
    queue_state = None # Assigned when entry is about to be queued
    status = None # Assigned when state changes


    @staticmethod
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()

    @staticmethod
    def delete_entry(artificial_playlist_id, entry_num):
        """
        Deletes the playlist entry and associated metadata.
        """
        entry = PlaylistEntry.select_playlistentry_for_playlist(artificial_playlist_id, entry_num)
        metadata = PlaylistEntryMetaData.select_metadata_for_entry(entry.artificial_id)
        metadata.delete()
        entry.delete()
        DB.session.commit()

    @staticmethod
    def count_entries(artificial_playlist_id):
        """
        Returns the count of all entries.
        """
        result = DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id).count()
        return result

    @hybrid_property
    def entry_end(self):
        return self.entry_start + datetime.timedelta(seconds=self.duration)

    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

    @hybrid_property
    def end_unix(self):
        return time.mktime(self.entry_end.timetuple())


    def get_content_type(self):
        return ResourceUtil.get_content_type(self.source)


    def get_prev_entries(self):
        """
        Retrieves all previous entries as part of the current entry's playlist.

        Returns:
            (List):     List of PlaylistEntry
        """
        prev_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start < self.entry_start:
                prev_entries.append(entry)
        return prev_entries


    def get_next_entries(self, timeslot_sensitive=True):
        """
        Retrieves all following entries as part of the current entry's playlist.

        Args:
            timeslot_sensitive (Boolean):   If `True` entries which start after \
                the end of the timeslot are excluded

        Returns:
            (List):     List of PlaylistEntry
        """
        next_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start > self.entry_start:
                if timeslot_sensitive:
                    if entry.entry_start < self.playlist.timeslot.timeslot_end:
                        next_entries.append(entry)
                else:
                    next_entries.append(entry)
        return next_entries


    def as_dict(self):
        """
        Returns the entry as a dictionary for serialization.
        """
        if self.meta_data:
            return {
                "id": self.artificial_id,
                "duration": self.duration,
                "artist": self.meta_data.artist,
                "album": self.meta_data.album,
                "title": self.meta_data.title
            }
        return None

    def __str__(self):
        """
        String representation of the object.
        """
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
        track = self.source[-25:]
        return "PlaylistEntry #%s [%s - %s | %ssec | Source: ...%s]" % (str(self.artificial_id), time_start, time_end, self.duration, track)



#
#   PLAYLIST ENTRY METADATA
#


class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
    """
    Metadata for a playlist entry such as the artist, album and track name.
    """
    __tablename__ = "playlist_entry_metadata"

    # Primary and Foreign Keys
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))

    # Relationships
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")

    # Data
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))

    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()