models.py 19.4 KB
Newer Older
1
#
David Trattnig's avatar
David Trattnig committed
2
# Aura Engine (https://gitlab.servus.at/aura/engine)
3
#
David Trattnig's avatar
David Trattnig committed
4
# Copyright (C) 2017-2020 - The Aura Engine Team.
5
#
David Trattnig's avatar
David Trattnig committed
6
7
8
9
# 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.
10
#
David Trattnig's avatar
David Trattnig committed
11
12
13
14
# 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.
15
#
David Trattnig's avatar
David Trattnig committed
16
17
18
# 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/>.

19

20
import sys
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
21
import time
22
23
import logging
import datetime
24

25
26
import sqlalchemy as sa

David Trattnig's avatar
David Trattnig committed
27
28
29
30
31
32
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
33

David Trattnig's avatar
David Trattnig committed
34
35
36
from src.base.config import AuraConfig
from src.base.utils import SimpleUtil
from src.core.resources import ResourceUtil
David Trattnig's avatar
David Trattnig committed
37

David Trattnig's avatar
David Trattnig committed
38
# Initialize DB Model and session
David Trattnig's avatar
David Trattnig committed
39
config = AuraConfig()
David Trattnig's avatar
David Trattnig committed
40
engine = sa.create_engine(config.get_database_uri())
41
42
43
44
Base = declarative_base()
Base.metadata.bind = engine

class DB():
David Trattnig's avatar
David Trattnig committed
45
46
47
    session_factory = sessionmaker(bind=engine)
    Session = scoped_session(session_factory)
    session = Session()
48
49
50
    Model = Base


51
52

class AuraDatabaseModel():
53
54
55
56
57
    """
    AuraDataBaseModel.

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

60

61
    def __init__(self):
62
63
64
        """
        Constructor.
        """
65
66
        self.logger = logging.getLogger("AuraEngine")

67

68
    def store(self, add=False, commit=False):
69
70
71
        """
        Store to the database
        """
72
        if add:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
73
            DB.session.add(self)
74
75
        else:
            DB.session.merge(self)
76
        if commit:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
77
            DB.session.commit()
78

79

80
    def delete(self, commit=False):
81
82
83
        """
        Delete from the database
        """
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
84
        DB.session.delete(self)
85
        if commit:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
86
            DB.session.commit()
87

88

89
90
91
92
93
94
95
96
    def refresh(self):
        """
        Refreshes the currect record
        """
        DB.session.expire(self)
        DB.session.refresh(self)


97
98
    def _asdict(self):
        return self.__dict__
99

100

101
102
103
    @staticmethod
    def init_database():
        """
104
        Initializes the database tables if they are not existing.
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

        Raises:
            sqlalchemy.exc.ProgrammingError:    In case the DB model is invalid
        """
        try:
            Playlist.is_empty()
        except sa.exc.ProgrammingError as e:
            errcode = e.orig.args[0]

            if errcode == 1146: # Error for no such table
                model = AuraDatabaseModel()
                model.recreate_db()
            else:
                raise


121
122
    @staticmethod
    def recreate_db(systemexit = False):
123
        """
124
        Deletes all tables and re-creates the database.
David Trattnig's avatar
David Trattnig committed
125
        """        
126
127
        Base.metadata.drop_all()
        Base.metadata.create_all()
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
128
        DB.session.commit()
129

130
131
        if systemexit:
            sys.exit(0)
132

133

134
135

#
David Trattnig's avatar
David Trattnig committed
136
#   TIMESLOT
137
138
#

139

140
class Timeslot(DB.Model, AuraDatabaseModel):
141
    """
David Trattnig's avatar
David Trattnig committed
142
    One specific timeslot for a show.
143
144
145
146
147
148
149
150

    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 errorneous (includes silence detection)
        show_fallback (Playlist):       Some playlist played as fallback, when no schedule fallback playlist is assigned or if some specific playlist is errorneous (includes silence detection)
        station_fallback (Playlist):    Defined in the original AURA API but not implemented, as station fallbacks are handled locally
151
    """
152
    __tablename__ = 'timeslot'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
153

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

David Trattnig's avatar
David Trattnig committed
157
158
159
160
161
    # 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")
162
163
164
165
166
167
168
169
    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")                            
David Trattnig's avatar
David Trattnig committed
170
171
172
    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)",
173
                            uselist=False, back_populates="timeslot")                            
David Trattnig's avatar
David Trattnig committed
174
175
176
177
178
179
180
181
182
183
    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)
184
185
    default_schedule_playlist_id = Column(Integer)
    default_show_playlist_id = Column(Integer)
David Trattnig's avatar
David Trattnig committed
186
187
188
189
190
    schedule_fallback_id = Column(Integer)
    show_fallback_id = Column(Integer)
    station_fallback_id = Column(Integer)

    # Data
191
192
193
    timeslot_start = Column(DateTime, unique=True, index=True)
    timeslot_end = Column(DateTime, unique=True, index=True)
    timeslot_id = Column(Integer, unique=True)
194

195
    show_id = Column(Integer)
196
197
    show_name = Column(String(256))
    show_hosts = Column(String(256))
198
    funding_category = Column(String(256))
199
200
201
202
203
204
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
205
    is_repetition = Column(Boolean())
David Trattnig's avatar
David Trattnig committed
206
    
207
208
    # Transients
    active_entry = None
209

210

211
    @staticmethod
212
213
214
215
216
217
218
    def for_datetime(date_time):
        """
        Select a timeslot at the given datetime.

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

221

222
    @staticmethod
223
    def get_timeslots(date_from=datetime.date.today()):
224
        """
225
        Select all timeslots starting from `date_from` or from today if no
226
227
228
        parameter is passed.

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

        Returns:
232
            ([Timeslot]):           List of timeslots
233
        """
234
235
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start >= date_from).\
236
            order_by(Timeslot.timeslot_start).all()        
237
        return timeslots
238

239

240
    def set_active_entry(self, entry):
David Trattnig's avatar
David Trattnig committed
241
        """
242
243
244
245
        Sets the currently playing entry.

        Args:
            entry (PlaylistEntry): The entry playing right now
David Trattnig's avatar
David Trattnig committed
246
        """
247
        self.active_entry = entry
David Trattnig's avatar
David Trattnig committed
248
249


250
    def get_recent_entry(self):
David Trattnig's avatar
David Trattnig committed
251
        """
252
253
        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.
David Trattnig's avatar
David Trattnig committed
254
        """
255
        return self.active_entry
David Trattnig's avatar
David Trattnig committed
256
257


David Trattnig's avatar
David Trattnig committed
258
259
260
    @hybrid_property
    def start_unix(self):
        """
261
        Start time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
262
        """
263
        return time.mktime(self.timeslot_start.timetuple())
David Trattnig's avatar
David Trattnig committed
264
265
266
267
268


    @hybrid_property
    def end_unix(self):
        """
269
        End time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
270
        """
271
        return time.mktime(self.timeslot_end.timetuple())
David Trattnig's avatar
David Trattnig committed
272
273


David Trattnig's avatar
David Trattnig committed
274
275
    def as_dict(self):
        """
276
        Returns the timeslot as a dictionary for serialization.
David Trattnig's avatar
David Trattnig committed
277
278
279
280
        """
        playlist = self.playlist

        return {
281
282
283
            "timeslot_id": self.timeslot_id,        
            "timeslot_start": self.timeslot_start.isoformat(),
            "timeslot_end": self.timeslot_end.isoformat(),
David Trattnig's avatar
David Trattnig committed
284
285
286
287
288
289
290
291
292

            "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,
293
294
            "schedule_default_id": self.schedule_default_id,
            "show_default_id": self.show_default_id,            
David Trattnig's avatar
David Trattnig committed
295
296
297
298
299
300
301
302
303
304
305
306
307
            "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
        }


308
309
310
311
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
312
313
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
314
        return "ID#%s [Show: %s, ShowID: %s | %s - %s ]" % (str(self.timeslot_id), self.show_name, str(self.show_id), time_start, time_end)
David Trattnig's avatar
David Trattnig committed
315
316


David Trattnig's avatar
David Trattnig committed
317
318
319
#
#   PLAYLIST
#
320

321
class Playlist(DB.Model, AuraDatabaseModel):
322
323
324
    """
    The playlist containing playlist entries.
    """
325
326
    __tablename__ = 'playlist'

David Trattnig's avatar
David Trattnig committed
327
    # Primary and Foreign Key
328
    artificial_id = Column(Integer, primary_key=True)
329
    timeslot_start = Column(DateTime, ForeignKey("timeslot.timeslot_start"))
David Trattnig's avatar
David Trattnig committed
330
331

    # Relationships
332
    timeslot = relationship("Timeslot", uselist=False, back_populates="playlist")
333
    entries = relationship("PlaylistEntry", back_populates="playlist")
David Trattnig's avatar
David Trattnig committed
334
335
336

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

340

341
342
    @staticmethod
    def select_all():
343
344
345
        """
        Fetches all entries
        """
346
        all_entries = DB.session.query(Playlist).filter(Playlist.fallback_type == 0).all()
347
348
349
350
351
352
353
354

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

        return all_entries

355

356
    @staticmethod
357
    def select_playlist_for_timeslot(start_date, playlist_id):
358
        """
359
        Retrieves the playlist for the given timeslot identified by `start_date` and `playlist_id`
360
361
362
363
364
365

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

        Returns:
366
            (Playlist):             The playlist, if existing for timeslot
367
368
369
370

        Raises:
            Exception:              In case there a inconsistent database state, such es multiple playlists for given date/time.
        """
David Trattnig's avatar
David Trattnig committed
371
        playlist = None
372
        playlists = DB.session.query(Playlist).filter(Playlist.timeslot_start == start_date).all()
373

David Trattnig's avatar
David Trattnig committed
374
375
376
        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p
377

David Trattnig's avatar
David Trattnig committed
378
        return playlist
379

380

381
382
    @staticmethod
    def select_playlist(playlist_id):
383
384
385
386
387
388
389
390
391
        """
        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
        """
392
        return DB.session.query(Playlist).filter(Playlist.playlist_id == playlist_id).order_by(Playlist.timeslot_start).all()
393
    
394

395
396
397
398
399
400
401
402
403
404
405
    @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


406
407
    @hybrid_property
    def start_unix(self):
408
409
410
        """
        Start time of the playlist in UNIX time.
        """
411
        return time.mktime(self.timeslot_start.timetuple())
412

413

414
415
416
417
418
    @hybrid_property
    def end_unix(self):
        """
        End time of the playlist in UNIX time.
        """
419
        return time.mktime(self.timeslot_start.timetuple()) + self.duration
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436


    @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


David Trattnig's avatar
David Trattnig committed
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
    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


454
455
456
457
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
458
459
460
461
        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))

462

David Trattnig's avatar
David Trattnig committed
463
464
465
#
#   PLAYLIST ENTRY
#
466

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

David Trattnig's avatar
David Trattnig committed
473
    # Primary and Foreign Keys
474
475
    artificial_id = Column(Integer, primary_key=True)
    artificial_playlist_id = Column(Integer, ForeignKey("playlist.artificial_id"))
476

David Trattnig's avatar
David Trattnig committed
477
478
479
480
481
482
    # Relationships
    playlist = relationship("Playlist", uselist=False, back_populates="entries")
    meta_data = relationship("PlaylistEntryMetaData", uselist=False, back_populates="entry")

    # Data
    entry_num = Column(Integer)
483
    duration = Column(BigInteger)
David Trattnig's avatar
David Trattnig committed
484
    volume = Column(Integer, ColumnDefault(100))
485
    source = Column(String(1024))
486
    entry_start = Column(DateTime)
David Trattnig's avatar
David Trattnig committed
487

488
    # Transients
489
    entry_start_actual = None # Assigned when the entry is actually played
490
    channel = None # Assigned when entry is actually played
491
    queue_state = None # Assigned when entry is about to be queued    
David Trattnig's avatar
David Trattnig committed
492
    status = None # Assigned when state changes
493

494

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
495
    @staticmethod
496
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
497
498
499
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
500
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()
501

502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
    @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

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

525
526
527
528
529
530
    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

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

533

David Trattnig's avatar
David Trattnig committed
534
    def get_content_type(self):
535
        return ResourceUtil.get_content_type(self.source)
536

537

538
539
540
541
542
543
544
545
546
547
548
549
550
551
    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


552
    def get_next_entries(self, timeslot_sensitive=True):
David Trattnig's avatar
David Trattnig committed
553
        """
554
        Retrieves all following entries as part of the current entry's playlist.
David Trattnig's avatar
David Trattnig committed
555

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

David Trattnig's avatar
David Trattnig committed
560
561
562
563
564
565
        Returns:
            (List):     List of PlaylistEntry
        """
        next_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start > self.entry_start:
566
567
                if timeslot_sensitive:
                    if entry.entry_start < self.playlist.timeslot.timeslot_end:
568
569
570
                        next_entries.append(entry)
                else:
                    next_entries.append(entry)
David Trattnig's avatar
David Trattnig committed
571
572
        return next_entries

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
573

574
575
576
577
578
579
    def as_dict(self):
        """
        Returns the entry as a dictionary for serialization.
        """
        if self.meta_data:
            return {
David Trattnig's avatar
David Trattnig committed
580
                "id": self.artificial_id,
581
582
583
584
585
586
587
                "duration": self.duration,
                "artist": self.meta_data.artist,
                "album": self.meta_data.album,
                "title": self.meta_data.title
            }
        return None

588
589
590
591
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
592
593
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
594
595
        track = self.source[-25:]
        return "PlaylistEntry #%s [%s - %s | %ssec | Source: ...%s]" % (str(self.artificial_id), time_start, time_end, self.duration, track)
596

David Trattnig's avatar
David Trattnig committed
597

598

David Trattnig's avatar
David Trattnig committed
599
600
601
602
#
#   PLAYLIST ENTRY METADATA
#

603

604
class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
605
    """
David Trattnig's avatar
David Trattnig committed
606
    Metadata for a playlist entry such as the artist, album and track name.
607
    """
608
    __tablename__ = "playlist_entry_metadata"
609

David Trattnig's avatar
David Trattnig committed
610
    # Primary and Foreign Keys
611
612
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))
613

David Trattnig's avatar
David Trattnig committed
614
615
616
617
    # Relationships
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")

    # Data
618
619
620
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))
621

622
623
    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
624
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()
625
626