models.py 17.2 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
171
    # Transients
    active_entry = None
172

173

174
    @staticmethod
175
176
177
178
179
180
181
    def for_datetime(date_time):
        """
        Select a timeslot at the given datetime.

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

184

185
    @staticmethod
186
    def get_timeslots(date_from=datetime.date.today()):
187
        """
188
        Select all timeslots starting from `date_from` or from today if no
189
190
191
        parameter is passed.

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

        Returns:
195
            ([Timeslot]):           List of timeslots
196
        """
197
198
        timeslots = DB.session.query(Timeslot).\
            filter(Timeslot.timeslot_start >= date_from).\
199
            order_by(Timeslot.timeslot_start).all()        
200
        return timeslots
201

202

203
    def set_active_entry(self, entry):
David Trattnig's avatar
David Trattnig committed
204
        """
205
206
207
208
        Sets the currently playing entry.

        Args:
            entry (PlaylistEntry): The entry playing right now
David Trattnig's avatar
David Trattnig committed
209
        """
210
        self.active_entry = entry
David Trattnig's avatar
David Trattnig committed
211
212


213
    def get_recent_entry(self):
David Trattnig's avatar
David Trattnig committed
214
        """
215
216
        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
217
        """
218
        return self.active_entry
David Trattnig's avatar
David Trattnig committed
219
220


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


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


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

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

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


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


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

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

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

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

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

301

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

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

        return all_entries

316

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

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

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

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

David Trattnig's avatar
David Trattnig committed
335
336
337
        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p
338

David Trattnig's avatar
David Trattnig committed
339
        return playlist
340

341

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

356
357
358
359
360
361
362
363
364
365
366
    @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


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

374

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


    @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
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
    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


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

423

David Trattnig's avatar
David Trattnig committed
424
425
426
#
#   PLAYLIST ENTRY
#
427

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

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

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

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

450
    # Transients
451
    entry_start_actual = None # Assigned when the entry is actually played
452
    channel = None # Assigned when entry is actually played
453
    queue_state = None # Assigned when entry is about to be queued    
David Trattnig's avatar
David Trattnig committed
454
    status = None # Assigned when state changes
455

456

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
457
    @staticmethod
458
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
459
460
461
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
462
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()
463

464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
    @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

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

487
488
489
490
491
492
    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

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

495

David Trattnig's avatar
David Trattnig committed
496
497
    def get_content_type(self):
        return ResourceUtil.get_content_type(self.uri)
498

499

500
501
502
503
504
505
506
507
508
509
510
511
512
513
    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


514
    def get_next_entries(self, timeslot_sensitive=True):
David Trattnig's avatar
David Trattnig committed
515
        """
516
        Retrieves all following entries as part of the current entry's playlist.
David Trattnig's avatar
David Trattnig committed
517

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

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

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
535

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

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

David Trattnig's avatar
David Trattnig committed
559

560

David Trattnig's avatar
David Trattnig committed
561
562
563
564
#
#   PLAYLIST ENTRY METADATA
#

565

566
class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
567
    """
David Trattnig's avatar
David Trattnig committed
568
    Metadata for a playlist entry such as the artist, album and track name.
569
    """
570
    __tablename__ = "playlist_entry_metadata"
571

David Trattnig's avatar
David Trattnig committed
572
    # Primary and Foreign Keys
573
574
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))
575

David Trattnig's avatar
David Trattnig committed
576
577
578
579
    # Relationships
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")

    # Data
580
581
582
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))
583

584
585
    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
586
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()
587
588