models.py 17.8 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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
    @staticmethod
    def init_database():
        """
        Initializes the database.

        Raises:
            sqlalchemy.exc.ProgrammingError:    In case the DB model is invalid
        """
        if AuraConfig.config().get("recreate_db") is not None:
            AuraDatabaseModel.recreate_db(systemexit=True)

        # Check if tables exists, if not create them
        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


125
126
    @staticmethod
    def recreate_db(systemexit = False):
127
128
        """
        Re-creates the database for developments purposes.
David Trattnig's avatar
David Trattnig committed
129
        """        
130
131
        Base.metadata.drop_all()
        Base.metadata.create_all()
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
132
        DB.session.commit()
133

134
135
        if systemexit:
            sys.exit(0)
136

137

138
139

#
David Trattnig's avatar
David Trattnig committed
140
#   TIMESLOT
141
142
#

143

144
class Timeslot(DB.Model, AuraDatabaseModel):
145
    """
David Trattnig's avatar
David Trattnig committed
146
    One specific timeslot for a show.
147
    """
148
    __tablename__ = 'timeslot'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
149

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

David Trattnig's avatar
David Trattnig committed
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
    # 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")
    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)
    schedule_fallback_id = Column(Integer)
    show_fallback_id = Column(Integer)
    station_fallback_id = Column(Integer)

    # Data
177
178
179
    timeslot_start = Column(DateTime, unique=True, index=True)
    timeslot_end = Column(DateTime, unique=True, index=True)
    timeslot_id = Column(Integer, unique=True)
180

181
    show_id = Column(Integer)
182
183
    show_name = Column(String(256))
    show_hosts = Column(String(256))
184
    funding_category = Column(String(256))
185
186
187
188
189
190
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
191
    is_repetition = Column(Boolean())
David Trattnig's avatar
David Trattnig committed
192
    
193
194
    # Transients
    active_entry = None
195

196

197
    @staticmethod
198
199
200
201
202
203
204
    def for_datetime(date_time):
        """
        Select a timeslot at the given datetime.

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

207

208
    @staticmethod
209
    def get_timeslots(date_from=datetime.date.today()):
210
        """
211
        Select all timeslots starting from `date_from` or from today if no
212
213
214
        parameter is passed.

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

        Returns:
218
            ([Timeslot]):           List of timeslots
219
        """
220
221
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start >= date_from).\
222
            order_by(Timeslot.timeslot_start).all()        
223
        return timeslots
224

225

226
    def set_active_entry(self, entry):
David Trattnig's avatar
David Trattnig committed
227
        """
228
229
230
231
        Sets the currently playing entry.

        Args:
            entry (PlaylistEntry): The entry playing right now
David Trattnig's avatar
David Trattnig committed
232
        """
233
        self.active_entry = entry
David Trattnig's avatar
David Trattnig committed
234
235


236
    def get_recent_entry(self):
David Trattnig's avatar
David Trattnig committed
237
        """
238
239
        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
240
        """
241
        return self.active_entry
David Trattnig's avatar
David Trattnig committed
242
243


David Trattnig's avatar
David Trattnig committed
244
245
246
    @hybrid_property
    def start_unix(self):
        """
247
        Start time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
248
        """
249
        return time.mktime(self.timeslot_start.timetuple())
David Trattnig's avatar
David Trattnig committed
250
251
252
253
254


    @hybrid_property
    def end_unix(self):
        """
255
        End time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
256
        """
257
        return time.mktime(self.timeslot_end.timetuple())
David Trattnig's avatar
David Trattnig committed
258
259


David Trattnig's avatar
David Trattnig committed
260
261
    def as_dict(self):
        """
262
        Returns the timeslot as a dictionary for serialization.
David Trattnig's avatar
David Trattnig committed
263
264
265
266
        """
        playlist = self.playlist

        return {
267
268
269
            "timeslot_id": self.timeslot_id,        
            "timeslot_start": self.timeslot_start.isoformat(),
            "timeslot_end": self.timeslot_end.isoformat(),
David Trattnig's avatar
David Trattnig committed
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291

            "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_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
        }


292
293
294
295
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
296
297
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
298
        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
299
300


David Trattnig's avatar
David Trattnig committed
301
302
303
#
#   PLAYLIST
#
304

305
class Playlist(DB.Model, AuraDatabaseModel):
306
307
308
    """
    The playlist containing playlist entries.
    """
309
310
    __tablename__ = 'playlist'

David Trattnig's avatar
David Trattnig committed
311
    # Primary and Foreign Key
312
    artificial_id = Column(Integer, primary_key=True)
313
    timeslot_start = Column(DateTime, ForeignKey("timeslot.timeslot_start"))
David Trattnig's avatar
David Trattnig committed
314
315

    # Relationships
316
    timeslot = relationship("Timeslot", uselist=False, back_populates="playlist")
317
    entries = relationship("PlaylistEntry", back_populates="playlist")
David Trattnig's avatar
David Trattnig committed
318
319
320

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

324

325
326
    @staticmethod
    def select_all():
327
328
329
        """
        Fetches all entries
        """
330
        all_entries = DB.session.query(Playlist).filter(Playlist.fallback_type == 0).all()
331
332
333
334
335
336
337
338

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

        return all_entries

339

340
    @staticmethod
341
    def select_playlist_for_timeslot(start_date, playlist_id):
342
        """
343
        Retrieves the playlist for the given timeslot identified by `start_date` and `playlist_id`
344
345
346
347
348
349

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

        Returns:
350
            (Playlist):             The playlist, if existing for timeslot
351
352
353
354

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

David Trattnig's avatar
David Trattnig committed
358
359
360
        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p
361

David Trattnig's avatar
David Trattnig committed
362
        return playlist
363

364

365
366
    @staticmethod
    def select_playlist(playlist_id):
367
368
369
370
371
372
373
374
375
        """
        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
        """
376
        return DB.session.query(Playlist).filter(Playlist.playlist_id == playlist_id).order_by(Playlist.timeslot_start).all()
377
    
378

379
380
381
382
383
384
385
386
387
388
389
    @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


390
391
    @hybrid_property
    def start_unix(self):
392
393
394
        """
        Start time of the playlist in UNIX time.
        """
395
        return time.mktime(self.timeslot_start.timetuple())
396

397

398
399
400
401
402
    @hybrid_property
    def end_unix(self):
        """
        End time of the playlist in UNIX time.
        """
403
        return time.mktime(self.timeslot_start.timetuple()) + self.duration
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420


    @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
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
    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


438
439
440
441
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
442
443
444
445
        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))

446

David Trattnig's avatar
David Trattnig committed
447
448
449
#
#   PLAYLIST ENTRY
#
450

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

David Trattnig's avatar
David Trattnig committed
457
    # Primary and Foreign Keys
458
459
    artificial_id = Column(Integer, primary_key=True)
    artificial_playlist_id = Column(Integer, ForeignKey("playlist.artificial_id"))
460

David Trattnig's avatar
David Trattnig committed
461
462
463
464
465
466
    # Relationships
    playlist = relationship("Playlist", uselist=False, back_populates="entries")
    meta_data = relationship("PlaylistEntryMetaData", uselist=False, back_populates="entry")

    # Data
    entry_num = Column(Integer)
467
468
    uri = Column(String(1024))
    duration = Column(BigInteger)
David Trattnig's avatar
David Trattnig committed
469
    volume = Column(Integer, ColumnDefault(100))
470
    source = Column(String(1024))
471
    entry_start = Column(DateTime)
David Trattnig's avatar
David Trattnig committed
472

473
    # Transients
474
    entry_start_actual = None # Assigned when the entry is actually played
475
    channel = None # Assigned when entry is actually played
476
    queue_state = None # Assigned when entry is about to be queued    
David Trattnig's avatar
David Trattnig committed
477
    status = None # Assigned when state changes
478

479

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
480
    @staticmethod
481
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
482
483
484
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
485
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()
486

487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
    @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

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

510
511
512
513
514
515
    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

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

518

David Trattnig's avatar
David Trattnig committed
519
520
    def get_content_type(self):
        return ResourceUtil.get_content_type(self.uri)
521

522

523
524
525
526
527
528
529
530
531
532
533
534
535
536
    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


537
    def get_next_entries(self, timeslot_sensitive=True):
David Trattnig's avatar
David Trattnig committed
538
        """
539
        Retrieves all following entries as part of the current entry's playlist.
David Trattnig's avatar
David Trattnig committed
540

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

David Trattnig's avatar
David Trattnig committed
545
546
547
548
549
550
        Returns:
            (List):     List of PlaylistEntry
        """
        next_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start > self.entry_start:
551
552
                if timeslot_sensitive:
                    if entry.entry_start < self.playlist.timeslot.timeslot_end:
553
554
555
                        next_entries.append(entry)
                else:
                    next_entries.append(entry)
David Trattnig's avatar
David Trattnig committed
556
557
        return next_entries

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
558

559
560
561
562
563
564
    def as_dict(self):
        """
        Returns the entry as a dictionary for serialization.
        """
        if self.meta_data:
            return {
David Trattnig's avatar
David Trattnig committed
565
                "id": self.artificial_id,
566
567
568
569
570
571
572
                "duration": self.duration,
                "artist": self.meta_data.artist,
                "album": self.meta_data.album,
                "title": self.meta_data.title
            }
        return None

573
574
575
576
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
577
578
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
579
580
        track = self.source[-25:]
        return "PlaylistEntry #%s [%s - %s | %ssec | Source: ...%s]" % (str(self.artificial_id), time_start, time_end, self.duration, track)
581

David Trattnig's avatar
David Trattnig committed
582

583

David Trattnig's avatar
David Trattnig committed
584
585
586
587
#
#   PLAYLIST ENTRY METADATA
#

588

589
class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
590
    """
David Trattnig's avatar
David Trattnig committed
591
    Metadata for a playlist entry such as the artist, album and track name.
592
    """
593
    __tablename__ = "playlist_entry_metadata"
594

David Trattnig's avatar
David Trattnig committed
595
    # Primary and Foreign Keys
596
597
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))
598

David Trattnig's avatar
David Trattnig committed
599
600
601
602
    # Relationships
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")

    # Data
603
604
605
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))
606

607
608
    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
609
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()
610
611