models.py 17.9 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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
    @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


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

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

138

139
140

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

144

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

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

David Trattnig's avatar
David Trattnig committed
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
    # 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
178
179
180
    timeslot_start = Column(DateTime, unique=True, index=True)
    timeslot_end = Column(DateTime, unique=True, index=True)
    timeslot_id = Column(Integer, unique=True)
181

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

197

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

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

208

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

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

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

226

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

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


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


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


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


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

        return {
268
269
270
            "timeslot_id": self.timeslot_id,        
            "timeslot_start": self.timeslot_start.isoformat(),
            "timeslot_end": self.timeslot_end.isoformat(),
David Trattnig's avatar
David Trattnig committed
271
272
273
274
275
276
277
278
279
280
281
282
283
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,
            "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
        }


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


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

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

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

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

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

325

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

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

        return all_entries

340

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

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

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

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

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

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

365

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

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


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

398

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


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


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

447

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

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

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

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

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

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

480

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

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

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

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

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

519

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

523

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


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

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

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

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
559

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

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

David Trattnig's avatar
David Trattnig committed
583

584

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

589

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

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

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

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

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