#
# Aura Engine API (https://gitlab.servus.at/aura/engine-api)
#
# Copyright (C) 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 datetime
import json
from sqlalchemy import create_engine, Column, DateTime, String, Integer, Boolean
from sqlalchemy.event import listen
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
from marshmallow import Schema, fields, post_dump
db = SQLAlchemy()
ma = Marshmallow()
class PlayLog(db.Model):
"""
Table holding play-log entries.
"""
__tablename__ = 'playlog'
# Primary Key
track_start = Column(DateTime, primary_key=True)
# Columns
track_artist = Column(String(256))
track_album = Column(String(256))
track_title = Column(String(256))
track_duration = Column(Integer)
track_type = Column(Integer)
track_num = Column(Integer)
playlist_id = Column(Integer)
timeslot_id = Column(Integer)
show_id = Column(Integer)
show_name = Column(String(256))
log_source = Column(Integer) # The play-out source which this log is coming from (e.g. engine1, engine2)
is_synced = Column(Boolean) # Only relevant for main nodes, in a multi-node setup
def __init__(self, data):
"""
Initializes a trackservice entry
"""
self.track_start = data.track_start
self.track_artist = data.track_artist
self.track_album = data.track_album
self.track_title = data.track_title
self.track_duration = data.track_duration
self.track_type = data.track_type
self.track_num = data.track_num
self.playlist_id = data.playlist_id
self.timeslot_id = data.timeslot_id
self.show_id = data.show_id
self.show_name = data.show_name
self.log_source = data.log_source
self.is_synced = False
def save(self):
db.session.add(self)
db.session.commit()
@staticmethod
def get(start_time):
"""
Selects the playlog identified by start time.
"""
db.session.commit()
track = db.session.query(PlayLog).\
filter(PlayLog.track_start <= str(start_time)).\
order_by(PlayLog.track_start.desc()).first()
return track
@staticmethod
def select_current():
"""
Selects the currently playing track.
"""
db.session.commit()
now = datetime.datetime.now()
track = db.session.query(PlayLog).\
order_by(PlayLog.track_start.desc()).\
filter(PlayLog.track_start <= str(now)).first()
if track:
# Preferably only get playlogs which are known for still being on air
if track.track_start + datetime.timedelta(0, track.track_duration) > now:
return track
# Station fallback may not provide a duration. Take this one, it's better than nothing.
if track.track_duration == 0:
return track
return None
@staticmethod
def select_for_timeslot(timeslot_id):
"""
Selects all playlogs which appear to be in the current timeslot.
If playlogs without a valid timeslot are queried (-1), then only
the last 50 total playlogs are respected.
"""
db.session.commit()
now = datetime.datetime.now()
before12h = datetime.datetime.now() - datetime.timedelta(hours=12)
playlogs = None
# Invalid Timeslot ID
if timeslot_id == -1:
playlogs = []
result = db.session.query(PlayLog).\
order_by(PlayLog.track_start.desc()).\
filter(PlayLog.track_start >= str(before12h)).\
limit(50)
for playlog in result.all():
if playlog.timeslot_id != -1:
break
playlogs.append(playlog)
# Valid Timeslot ID
else:
result = db.session.query(PlayLog).\
order_by(PlayLog.track_start.desc()).\
filter(PlayLog.timeslot_id == timeslot_id).\
playlogs = result.all()
return playlogs
@staticmethod
def paginate(page, page_size, from_time=None, to_time=None, skip_synced=False):
"""
Returns a list of entries for a given page and an start time (optional).
"""
if not from_time:
from_time = datetime.datetime.utcnow().date()
if not to_time:
to_time = datetime.datetime.now()
def q(page=0, page_size=None):
query = db.session.query(PlayLog).order_by(PlayLog.track_start.desc())
if isinstance(from_time, datetime.datetime):
query = query.filter(PlayLog.track_start >= from_time.isoformat(' ', 'seconds'))
if isinstance(to_time, datetime.datetime):
query = query.filter(PlayLog.track_start <= to_time.isoformat(' ', 'seconds'))
if skip_synced == True:
query = query.filter(PlayLog.is_synced == False)
listen(query, 'before_compile', apply_limit(page, page_size), retval=True)
print("Paginate Query: " + str(query))
return query
def apply_limit(page, page_size):
def wrapped(query):
if page_size:
query = query.limit(page_size)
if page:
query = query.offset(page * page_size)
return query
return wrapped
return q(page, page_size)
@staticmethod
def select_last_hours(n):
"""
Selects the tracks playing in the past (`n`) hours.
"""
db.session.commit()
last_hours = datetime.datetime.today() - datetime.timedelta(hours=n)
tracks = db.session.query(PlayLog).filter(PlayLog.track_start >= str(last_hours)).\
order_by(PlayLog.track_start.desc()).all()
return tracks
@staticmethod
def select_by_day(day):
"""
Select the track-service items for a day.
"""
db.session.commit()
day_plus_one = day + datetime.timedelta(days=1)
tracks = db.session.query(PlayLog).\
filter(PlayLog.track_start >= str(day), PlayLog.track_start < str(day_plus_one)).\
order_by(PlayLog.track_start.desc()).all()
return tracks
@staticmethod
def select_by_range(from_day, to_day):
"""
Selects the track-service items for a day range.
"""
db.session.commit()
tracks = db.session.query(PlayLog).filter(PlayLog.track_start >= str(from_day),\
PlayLog.track_start < str(to_day)).order_by(PlayLog.track_start.desc()).all()
return tracks
def __str__(self):
return "Track [track_start: %s, track_title: %s]" % (str(self.track_start), str(self.track_title))
class PlayLogSchema(ma.SQLAlchemyAutoSchema):
"""
Schema for playlog entries.
"""
class Meta:
model = PlayLog
sqla_session = db.session
SKIP_VALUES = set([None])
@post_dump
def remove_skip_values(self, data, many=False):
return {
key: value for key, value in data.items()
if value not in self.SKIP_VALUES
}
class TrackSchema(ma.SQLAlchemySchema):
"""
Schema for trackservice entries.
"""
class Meta:
model = PlayLog
sqla_session = db.session
fields = (
"track_start",
"track_artist",
"track_album",
"track_title",
"track_duration",
"track_type",
"track_num",
"playlist_id",
"timeslot_id",
"show_id",
"show_name"
)
class ActivityLog(db.Model):
"""
Table holding a log of play-out source activity and their sync states.
Only used in "SYNC" deployment mode.
"""
__tablename__ = 'activity_log'
# Primary Key
log_time = Column(DateTime, primary_key=True)
# Columns
source_number = Column(Integer)
is_synced = Column(Boolean)
def __init__(self, source_number):
"""
Initializes an activity entry
"""
self.log_time = datetime.datetime.now()
self.source_number = source_number
self.is_synced = False
@staticmethod
def is_empty():
"""
Checks if the tables is empty.
"""
db.session.commit()
return not db.session.query(ActivityLog).one_or_none()
@staticmethod
def get_active_source():
"""
Retrieves the currently active source.
"""
db.session.commit()
source = db.session.query(ActivityLog).\
order_by(ActivityLog.log_time.desc()).first()
return source
def save(self):
db.session.add(self)
db.session.commit()
class HealthHistory(db.Model):
"""
Table holding an history of health information for sources.
"""
__tablename__ = 'health_history'
# Primary Key
log_time = Column(DateTime, primary_key=True)
# Columns
log_source = Column(Integer) # The source the history entry relates to
is_healthy = Column(Boolean) # Indicates if source is "healthy enough" to be used for play-out
is_synced = Column(Boolean) # Only relevant for main nodes, in a multi-node setup
health_info = Column(String(4096)) # Stringified JSON object or other, if needed
def __init__(self, source_number, log_time, is_healthy, health_info):
"""
Initializes an health entry.
"""
self.log_time = log_time
self.log_source = source_number
self.is_healthy = is_healthy
self.is_synced = False
self.health_info = health_info
@staticmethod
def get_latest_entry(source_number):
"""
Retrieves the most recent health history entry for the given source number.
"""
return db.session.query(HealthHistory).filter(HealthHistory.log_source == source_number).\
order_by(HealthHistory.log_time.desc()).first()
def save(self):
db.session.add(self)
db.session.commit()
class HealthHistorySchema(ma.SQLAlchemyAutoSchema):
"""
Schema for health history entries.
"""
class Meta:
model = HealthHistory
sqla_session = db.session
class ClockInfo(db.Model):
"""
Table holding information for the current and next show to be displayed by the studio clock.
Important: This table doesn't hold the history of information for efficiency. It only stores one
record for each possible source. For example in a redundant deployment it holds two records, for
engine1 and engine2.
The stringified objects allow easy, future extension of the properties, without the need to change
the database model.
"""
__tablename__ = 'clock_info'
# Primary Key
log_source = Column(Integer, primary_key=True) # The source this entry was updated from ("1" for engine1, "2" for engine2)
# Columns
log_time = Column(DateTime)
current_track = None # Populated live from within `get_info(..)`
current_playlist = Column(String(4096)) # Stringified "#/components/schemas/Playlist" OpenAPI JSON object
current_timeslot = Column(String(2048)) # Stringified "#/components/schemas/Schedule" OpenAPI JSON object
next_timeslot = Column(String(2048)) # Stringified "#/components/schemas/Schedule" OpenAPI JSON object
def __init__(self):
"""
Initializes an clock info entry.
"""
def set_info(self, source_number, current_playlist, current_timeslot, next_timeslot):
"""
Sets the values for a clock info entry.
"""
self.log_time = datetime.datetime.now()
self.log_source = source_number
if current_playlist:
self.current_playlist = json.dumps(current_playlist.to_dict(), default=str)
else:
self.current_playlist = None
if current_timeslot:
self.current_timeslot = json.dumps(current_timeslot.to_dict(), default=str)
else:
self.current_timeslot = None
if next_timeslot:
self.next_timeslot = json.dumps(next_timeslot.to_dict(), default=str)
else:
self.next_timeslot = None
@staticmethod
def get(source_number):
"""
Retrieves the clock info for the given source number.
"""
return db.session.query(ClockInfo).filter(ClockInfo.log_source == source_number).first()
@staticmethod
def get_info(source_number):
"""
Retrieves the clock info for the given source number.
"""
track_schema = TrackSchema()
info = dict()
data = db.session.query(ClockInfo).filter(ClockInfo.log_source == source_number).first()
current_track = PlayLog.select_current()
current_playlist_id = -1
updated_playlist = None
if current_track:
updated_playlist = PlayLog.select_for_timeslot(current_track.timeslot_id)
updated_playlist.sort(key=lambda track: track.track_start, reverse=False)
if data:
info["log_source"] = data.log_source
info["log_time"] = data.log_time
if current_track:
info["current_track"] = track_schema.dump(current_track)
if data.current_playlist:
info["planned_playlist"] = json.loads(data.current_playlist)
current_playlist_id = info["planned_playlist"]["playlist_id"]
for next_entry in info["planned_playlist"]["entries"]:
if next_entry.get("start_date") and next_entry.get("start_date") > datetime.datetime.now():
updated_playlist["entries"].append(next_entry)
if data.current_timeslot:
info["current_timeslot"] = json.loads(data.current_timeslot)
if data.next_timeslot:
info["next_timeslot"] = json.loads(data.next_timeslot)
playlog_schema = PlayLogSchema(many=True)
info["current_playlist"] = {
"playlist_id": current_playlist_id,
"entries": playlog_schema.dump(updated_playlist)
}
return info
def save(self):
db.session.add(self)
db.session.commit()
def update(self):
db.session.merge(self)
db.session.commit()
class ClockInfoSchema(ma.SQLAlchemySchema):
"""
Schema for trackservice entries.
"""
class Meta:
model = ClockInfo
sqla_session = db.session
fields = (
"log_source",
"log_time",
"current_track",
"planned_playlist",
"current_playlist",
"current_timeslot",
"next_timeslot"
)