models.py 17.6 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
from sqlalchemy                     import BigInteger, Boolean, Column, DateTime, Integer, String, ForeignKey, ColumnDefault
28
from sqlalchemy                     import orm
David Trattnig's avatar
David Trattnig committed
29
from sqlalchemy.ext.declarative     import declarative_base
30
31
32
33
34
35
from sqlalchemy.orm                 import relationship
from sqlalchemy.ext.hybrid          import hybrid_property

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
36

37

David Trattnig's avatar
David Trattnig committed
38

David Trattnig's avatar
David Trattnig committed
39
40
41
# Init Config
config = AuraConfig()

David Trattnig's avatar
David Trattnig committed
42
# Initialize DB Model and session
David Trattnig's avatar
David Trattnig committed
43
engine = sa.create_engine(config.get_database_uri())
44
45
46
47
48
49
50
51
Base = declarative_base()
Base.metadata.bind = engine

class DB():
    session = orm.scoped_session(orm.sessionmaker())(bind=engine)
    Model = Base


52
53

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

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

61

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

68

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

80

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

89

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


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

101

102
103
    @staticmethod
    def recreate_db(systemexit = False):
104
105
        """
        Re-creates the database for developments purposes.
David Trattnig's avatar
David Trattnig committed
106
        """        
107
108
        Base.metadata.drop_all()
        Base.metadata.create_all()
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
109
        DB.session.commit()
110

111
112
        if systemexit:
            sys.exit(0)
113

114

115
116

#
David Trattnig's avatar
David Trattnig committed
117
#   TIMESLOT
118
119
#

120

121
class Timeslot(DB.Model, AuraDatabaseModel):
122
    """
David Trattnig's avatar
David Trattnig committed
123
    One specific timeslot for a show.
124
    """
125
    __tablename__ = 'timeslot'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
126

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

David Trattnig's avatar
David Trattnig committed
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
    # 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
154
155
156
    timeslot_start = Column(DateTime, unique=True, index=True)
    timeslot_end = Column(DateTime, unique=True, index=True)
    timeslot_id = Column(Integer, unique=True)
157

158
    show_id = Column(Integer)
159
160
    show_name = Column(String(256))
    show_hosts = Column(String(256))
161
    funding_category = Column(String(256))
162
163
164
165
166
167
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
168
    is_repetition = Column(Boolean())
David Trattnig's avatar
David Trattnig committed
169
    
170
    fadeouttimer = None # Used to fade-out the timeslot, even when entries are longer
171

172

173
    @staticmethod
174
    def select_show_on_datetime(date_time):
175
        return DB.session.query(Timeslot).filter(Timeslot.timeslot_start == date_time).first()
176

177

178
    @staticmethod
179
180
    def select_programme(date_from=datetime.date.today()):
        """
181
        Select all timeslots starting from `date_from` or from today if no
182
183
184
        parameter is passed.

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

        Returns:
188
            ([Timeslot]):           List of timeslots
189
        """
190
191
192
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start >= date_from).\
            order_by(Timeslot.timeslot_start).all()
193

194
        return timeslots
195

196

David Trattnig's avatar
David Trattnig committed
197
198
199
    @staticmethod
    def select_upcoming(n):
        """
200
        Selects the (`n`) upcoming timeslots.
David Trattnig's avatar
David Trattnig committed
201
202
203
        """
        now = datetime.datetime.now()
        DB.session.commit() # Required since independend session is used.
204
205
206
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start > str(now)).\
            order_by(Timeslot.timeslot_start.asc()).limit(n).all()
David Trattnig's avatar
David Trattnig committed
207
        
208
        return timeslots
David Trattnig's avatar
David Trattnig committed
209
210


David Trattnig's avatar
David Trattnig committed
211
212
213
214
215
216
217
218
219
220
221
    def has_queued_entries(self):
        """
        Checks if entries of this timeslot have been queued at the engine.        
        """
        #TODO Make logic more transparent
        if hasattr(self, "queued_entries"):
            if len(self.queued_entries) > 0:    
                return True
        return False


David Trattnig's avatar
David Trattnig committed
222
223
224
    @hybrid_property
    def start_unix(self):
        """
225
        Start time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
226
        """
227
        return time.mktime(self.timeslot_start.timetuple())
David Trattnig's avatar
David Trattnig committed
228
229
230
231
232


    @hybrid_property
    def end_unix(self):
        """
233
        End time of the timeslot in UNIX time.
David Trattnig's avatar
David Trattnig committed
234
        """
235
        return time.mktime(self.timeslot_end.timetuple())
David Trattnig's avatar
David Trattnig committed
236
237


David Trattnig's avatar
David Trattnig committed
238
239
    def as_dict(self):
        """
240
        Returns the timeslot as a dictionary for serialization.
David Trattnig's avatar
David Trattnig committed
241
242
243
244
        """
        playlist = self.playlist

        return {
245
246
247
            "timeslot_id": self.timeslot_id,        
            "timeslot_start": self.timeslot_start.isoformat(),
            "timeslot_end": self.timeslot_end.isoformat(),
David Trattnig's avatar
David Trattnig committed
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269

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


270
271
272
273
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
274
275
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
276
        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
277
278


David Trattnig's avatar
David Trattnig committed
279
280
281
#
#   PLAYLIST
#
282

283
class Playlist(DB.Model, AuraDatabaseModel):
284
285
286
    """
    The playlist containing playlist entries.
    """
287
288
    __tablename__ = 'playlist'

David Trattnig's avatar
David Trattnig committed
289
    # Primary and Foreign Key
290
    artificial_id = Column(Integer, primary_key=True)
291
    timeslot_start = Column(DateTime, ForeignKey("timeslot.timeslot_start"))
David Trattnig's avatar
David Trattnig committed
292
293

    # Relationships
294
    timeslot = relationship("Timeslot", uselist=False, back_populates="playlist")
295
    entries = relationship("PlaylistEntry", back_populates="playlist")
David Trattnig's avatar
David Trattnig committed
296
297
298

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

302

303
304
    @staticmethod
    def select_all():
305
306
307
        """
        Fetches all entries
        """
308
        all_entries = DB.session.query(Playlist).filter(Playlist.fallback_type == 0).all()
309
310
311
312
313
314
315
316

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

        return all_entries

317

318
    @staticmethod
319
    def select_playlist_for_timeslot(start_date, playlist_id):
320
        """
321
        Retrieves the playlist for the given timeslot identified by `start_date` and `playlist_id`
322
323
324
325
326
327

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

        Returns:
328
            (Playlist):             The playlist, if existing for timeslot
329
330
331
332

        Raises:
            Exception:              In case there a inconsistent database state, such es multiple playlists for given date/time.
        """
David Trattnig's avatar
David Trattnig committed
333
        playlist = None
334
        playlists = DB.session.query(Playlist).filter(Playlist.timeslot_start == start_date).all()
David Trattnig's avatar
David Trattnig committed
335
        # FIXME There are unknown issues with the native SQL query by ID
336
        # playlists = DB.session.query(Playlist).filter(Playlist.timeslot_start == datetime and Playlist.playlist_id == playlist_id).all()
David Trattnig's avatar
David Trattnig committed
337
338
339
340
        
        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p
341

David Trattnig's avatar
David Trattnig committed
342
        return playlist
343

344

345
346
    @staticmethod
    def select_playlist(playlist_id):
347
348
349
350
351
352
353
354
355
        """
        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
        """
356
        return DB.session.query(Playlist).filter(Playlist.playlist_id == playlist_id).order_by(Playlist.timeslot_start).all()
357
    
358

359
360
361
362
363
364
365
366
367
368
369
    @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


370
371
    @hybrid_property
    def start_unix(self):
372
373
374
        """
        Start time of the playlist in UNIX time.
        """
375
        return time.mktime(self.timeslot_start.timetuple())
376

377

378
379
380
381
382
    @hybrid_property
    def end_unix(self):
        """
        End time of the playlist in UNIX time.
        """
383
        return time.mktime(self.timeslot_start.timetuple()) + self.duration
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400


    @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
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
    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


418
419
420
421
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
422
423
424
425
        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))

426

David Trattnig's avatar
David Trattnig committed
427
428
429
#
#   PLAYLIST ENTRY
#
430

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

David Trattnig's avatar
David Trattnig committed
437
    # Primary and Foreign Keys
438
439
    artificial_id = Column(Integer, primary_key=True)
    artificial_playlist_id = Column(Integer, ForeignKey("playlist.artificial_id"))
440

David Trattnig's avatar
David Trattnig committed
441
442
443
444
445
446
    # Relationships
    playlist = relationship("Playlist", uselist=False, back_populates="entries")
    meta_data = relationship("PlaylistEntryMetaData", uselist=False, back_populates="entry")

    # Data
    entry_num = Column(Integer)
447
448
    uri = Column(String(1024))
    duration = Column(BigInteger)
David Trattnig's avatar
David Trattnig committed
449
    volume = Column(Integer, ColumnDefault(100))
450
    source = Column(String(1024))
451
    entry_start = Column(DateTime)
David Trattnig's avatar
David Trattnig committed
452

453
    entry_start_actual = None # Assigned when the entry is actually played
454
    channel = None # Assigned when entry is actually played
455
    queue_state = None # Assigned when entry is about to be queued    
David Trattnig's avatar
David Trattnig committed
456
    status = None # Assigned when state changes
David Trattnig's avatar
David Trattnig committed
457
458
459
    switchtimer = None
    loadtimer = None
    fadeouttimer = None
460

461

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
462
    @staticmethod
463
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
464
465
466
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
467
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()
468

469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
    @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

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

492
493
494
495
496
497
    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

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

500

David Trattnig's avatar
David Trattnig committed
501
502
    def get_content_type(self):
        return ResourceUtil.get_content_type(self.uri)
503

504

505
506
507
508
509
510
511
512
513
514
515
516
517
518
    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


519
    def get_next_entries(self, timeslot_sensitive=True):
David Trattnig's avatar
David Trattnig committed
520
        """
521
        Retrieves all following entries as part of the current entry's playlist.
David Trattnig's avatar
David Trattnig committed
522

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

David Trattnig's avatar
David Trattnig committed
527
528
529
530
531
532
        Returns:
            (List):     List of PlaylistEntry
        """
        next_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start > self.entry_start:
533
534
                if timeslot_sensitive:
                    if entry.entry_start < self.playlist.timeslot.timeslot_end:
535
536
537
                        next_entries.append(entry)
                else:
                    next_entries.append(entry)
David Trattnig's avatar
David Trattnig committed
538
539
        return next_entries

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
540

541
542
543
544
545
546
    def as_dict(self):
        """
        Returns the entry as a dictionary for serialization.
        """
        if self.meta_data:
            return {
David Trattnig's avatar
David Trattnig committed
547
                "id": self.artificial_id,
548
549
550
551
552
553
554
                "duration": self.duration,
                "artist": self.meta_data.artist,
                "album": self.meta_data.album,
                "title": self.meta_data.title
            }
        return None

555
556
557
558
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
559
560
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
561
562
        track = self.source[-25:]
        return "PlaylistEntry #%s [%s - %s | %ssec | Source: ...%s]" % (str(self.artificial_id), time_start, time_end, self.duration, track)
563

David Trattnig's avatar
David Trattnig committed
564

565

David Trattnig's avatar
David Trattnig committed
566
567
568
569
#
#   PLAYLIST ENTRY METADATA
#

570

571
class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
572
    """
David Trattnig's avatar
David Trattnig committed
573
    Metadata for a playlist entry such as the artist, album and track name.
574
    """
575
    __tablename__ = "playlist_entry_metadata"
576

David Trattnig's avatar
David Trattnig committed
577
    # Primary and Foreign Keys
578
579
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))
580

David Trattnig's avatar
David Trattnig committed
581
582
583
584
    # Relationships
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")

    # Data
585
586
587
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))
588

589
590
    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
591
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()
592
593