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

33

David Trattnig's avatar
David Trattnig committed
34
from modules.scheduling.types           import PlaylistType
David Trattnig's avatar
David Trattnig committed
35
from modules.base.config                import AuraConfig
David Trattnig's avatar
David Trattnig committed
36
from modules.base.utils                 import SimpleUtil, EngineUtil
David Trattnig's avatar
David Trattnig committed
37

38

David Trattnig's avatar
David Trattnig committed
39

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

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

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


53
54

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

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

62

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

69

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

81

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

90

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


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

102

103
104
    @staticmethod
    def recreate_db(systemexit = False):
105
106
107
        """
        Re-creates the database for developments purposes.
        """
108
109
        manualschedule = Schedule()
        manualschedule.schedule_id = 0
110
        manualschedule.show_name = "Manual Show"
111
112
113
114
        Base.metadata.drop_all()
        Base.metadata.create_all()

        
115
116
117
118
        # self.logger.debug("inserting manual scheduling possibility and fallback trackservice schedule")
        # DB.session.add(manualschedule)
        # db.session.add(fallback_trackservice_schedule)
        # self.logger.debug("all created. commiting...")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
119
        DB.session.commit()
120

121
122
        if systemexit:
            sys.exit(0)
123

124

125
126

#
127
#   SCHEDULES & PLAYLISTS
128
129
#

130

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
131
class Schedule(DB.Model, AuraDatabaseModel):
132
    """
133
134
    One specific Schedule for a show on a timeslot.
    Holding references to playlists and fallback-playlists.
135
    """
136
    __tablename__ = 'schedule'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
137

138
139
140
141
142
143
    # Primary keys
    id = Column(Integer, primary_key=True, autoincrement=True)

    schedule_start = Column(DateTime, unique=True, index=True)
    schedule_end = Column(DateTime, unique=True, index=True)
    schedule_id = Column(Integer, unique=True)
144

145
    show_id = Column(Integer)
146
147
    show_name = Column(String(256))
    show_hosts = Column(String(256))
148
    funding_category = Column(String(256))
149
150
151
152
153
154
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
155
156
157

    is_repetition = Column(Boolean())

158
    playlist_id = Column(Integer) #, ForeignKey("playlist.playlist_id"))
David Trattnig's avatar
David Trattnig committed
159
    schedule_fallback_id = Column(Integer)
160
161
    show_fallback_id = Column(Integer)
    station_fallback_id = Column(Integer)
162
    fallback_state = PlaylistType.DEFAULT
David Trattnig's avatar
David Trattnig committed
163
164
    
    fadeouttimer = None # Used to fade-out the schedule, even when entries are longer
165

166
167
168
    playlist = relationship("Playlist",
                            primaryjoin="and_(Schedule.schedule_start==Playlist.schedule_start, Schedule.playlist_id==Playlist.playlist_id, Schedule.show_name==Playlist.show_name)",
                            back_populates="schedule")
David Trattnig's avatar
David Trattnig committed
169
170
    schedule_fallback = relationship("Playlist",
                            primaryjoin="and_(Schedule.schedule_start==Playlist.schedule_start, Schedule.schedule_fallback_id==Playlist.playlist_id, Schedule.show_name==Playlist.show_name)",
171
172
173
174
175
176
177
                            back_populates="schedule")
    show_fallback = relationship("Playlist",
                            primaryjoin="and_(Schedule.schedule_start==Playlist.schedule_start, Schedule.show_fallback_id==Playlist.playlist_id, Schedule.show_name==Playlist.show_name)",
                            back_populates="schedule")
    station_fallback = relationship("Playlist",
                            primaryjoin="and_(Schedule.schedule_start==Playlist.schedule_start, Schedule.station_fallback_id==Playlist.playlist_id, Schedule.show_name==Playlist.show_name)",
                            back_populates="schedule")
178

179

180
    @staticmethod
181
182
    def select_show_on_datetime(date_time):
        return DB.session.query(Schedule).filter(Schedule.schedule_start == date_time).first()
183

184

185
    @staticmethod
186
187
    def select_programme(date_from=datetime.date.today()):
        """
188
        Select all schedules starting from `date_from` or from today if no
189
190
191
192
193
194
195
196
197
198
199
        parameter is passed.

        Args:
            date_from (datetime):   Select schedules from this date and time on

        Returns:
            ([Schedule]):           List of schedules
        """
        schedules = DB.session.query(Schedule).\
            filter(Schedule.schedule_start >= date_from).\
            order_by(Schedule.schedule_start).all()
200

201
        return schedules
202

203

David Trattnig's avatar
David Trattnig committed
204
205
206
207
208
209
210
211
212
213
214
215
216
217
    @staticmethod
    def select_upcoming(n):
        """
        Selects the (`n`) upcoming schedules.
        """
        now = datetime.datetime.now()
        DB.session.commit() # Required since independend session is used.
        schedules = DB.session.query(Schedule).\
            filter(Schedule.schedule_start > str(now)).\
            order_by(Schedule.schedule_start.asc()).limit(n).all()
        
        return schedules


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


    @hybrid_property
    def end_unix(self):
        """
        End time of the schedule in UNIX time.
        """
        return time.mktime(self.schedule_end.timetuple())


David Trattnig's avatar
David Trattnig committed
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
    def as_dict(self):
        """
        Returns the schedule as a dictionary for serialization.
        """
        playlist = self.playlist

        return {
            "schedule_id": self.schedule_id,        
            "schedule_start": self.schedule_start.isoformat(),
            "schedule_end": self.schedule_end.isoformat(),

            "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,
259
                "type": self.get_type(),
David Trattnig's avatar
David Trattnig committed
260
261
262
263
264
265
266
                "host": self.show_hosts
            },

            "playlist": playlist
        }


267
268
269
270
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
271
272
273
274
275
        time_start = SimpleUtil.fmt_time(self.start_unix)
        time_end = SimpleUtil.fmt_time(self.end_unix)
        return "ID#%s [Show: %s, ShowID: %s | %s - %s ]" % (str(self.schedule_id), self.show_name, str(self.show_id), time_start, time_end)


276

277
class Playlist(DB.Model, AuraDatabaseModel):
278
279
280
    """
    The playlist containing playlist entries.
    """
281
282
283
284
285
286
287
288
289
290
291
292
    __tablename__ = 'playlist'

    # pk,fk
    artificial_id = Column(Integer, primary_key=True)
    schedule_start = Column(DateTime, ForeignKey("schedule.schedule_start"))
    # relationships
    schedule = relationship("Schedule", uselist=False, back_populates="playlist")
    entries = relationship("PlaylistEntry", back_populates="playlist")
    # data
    playlist_id = Column(Integer, autoincrement=False)  # , ForeignKey("schedule.playlist_id"))
    show_name = Column(String(256))
    fallback_type = Column(Integer)
293
    entry_count = Column(Integer)
294

295

296
297
    @staticmethod
    def select_all():
298
299
300
        """
        Fetches all entries
        """
301
        all_entries = DB.session.query(Playlist).filter(Playlist.fallback_type == 0).all()
302
303
304
305
306
307
308
309

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

        return all_entries

310

311
    @staticmethod
312
    def select_playlist_for_schedule(start_date, playlist_id):
313
314
315
316
317
318
319
320
321
322
323
324
325
        """
        Retrieves the playlist for the given schedule identified by `start_date` and `playlist_id`

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

        Returns:
            (Playlist):             The playlist, if existing for schedule

        Raises:
            Exception:              In case there a inconsistent database state, such es multiple playlists for given date/time.
        """
David Trattnig's avatar
David Trattnig committed
326
        playlist = None
327
        playlists = DB.session.query(Playlist).filter(Playlist.schedule_start == start_date).all()
David Trattnig's avatar
David Trattnig committed
328
329
330
331
332
333
        # FIXME There are unknown issues with the native SQL query by ID
        # playlists = DB.session.query(Playlist).filter(Playlist.schedule_start == datetime and Playlist.playlist_id == playlist_id).all()
        
        for p in playlists:
            if p.playlist_id == playlist_id:
                playlist = p
334

David Trattnig's avatar
David Trattnig committed
335
        return playlist
336

337

338
339
    @staticmethod
    def select_playlist(playlist_id):
340
341
342
343
344
345
346
347
348
        """
        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
        """
349
350
        return DB.session.query(Playlist).filter(Playlist.playlist_id == playlist_id).order_by(Playlist.schedule_start).all()
    
351

352
353
354
355
356
357
358
359
360
361
362
    @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


363
364
    @hybrid_property
    def start_unix(self):
365
366
367
        """
        Start time of the playlist in UNIX time.
        """
368
369
        return time.mktime(self.schedule_start.timetuple())

370

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


    @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
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
    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


411
412
413
414
    def __str__(self):
        """
        String representation of the object.
        """
David Trattnig's avatar
David Trattnig committed
415
416
417
418
        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))

419
420


421
class PlaylistEntry(DB.Model, AuraDatabaseModel):
422
423
424
    """
    Playlist entries are the individual items of a playlist such as audio files.
    """
425
    __tablename__ = 'playlist_entry'
426

427
428
    # primary keys
    artificial_id = Column(Integer, primary_key=True)
429

430
431
432
    # foreign keys
    artificial_playlist_id = Column(Integer, ForeignKey("playlist.artificial_id"))
    entry_num = Column(Integer) # , primary_key=True)
433

434
435
    uri = Column(String(1024))
    duration = Column(BigInteger)
436
    source = Column(String(1024))
437
    entry_start = Column(DateTime)
438
    entry_start_actual = None # Assigned when the entry is actually played
439
    channel = None # Assigned when entry is actually played
440
    queue_state = None # Assigned when entry is about to be queued    
David Trattnig's avatar
David Trattnig committed
441
    status = None # Assigned when state changes
David Trattnig's avatar
David Trattnig committed
442
443
444
445
    
    switchtimer = None
    loadtimer = None
    fadeouttimer = None
446

447
448
    # relationships
    playlist = relationship("Playlist", uselist=False, back_populates="entries")
449
    meta_data = relationship("PlaylistEntryMetaData", uselist=False, back_populates="entry")
450

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
451
    @staticmethod
452
    def select_playlistentry_for_playlist(artificial_playlist_id, entry_num):
453
454
455
        """
        Selects one entry identified by `playlist_id` and `entry_num`.
        """
456
        return DB.session.query(PlaylistEntry).filter(PlaylistEntry.artificial_playlist_id == artificial_playlist_id, PlaylistEntry.entry_num == entry_num).first()
457

458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
    @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

477
478
479
480
    @hybrid_property
    def entry_end(self):
        return self.entry_start + datetime.timedelta(seconds=self.duration)

481
482
483
484
485
486
    @hybrid_property
    def start_unix(self):
        return time.mktime(self.entry_start.timetuple())

    @hybrid_property
    def end_unix(self):
487
        return time.mktime(self.entry_end.timetuple())
488

489
    @hybrid_property
490
    def volume(self):
491
        return 100 # FIXME Make DB Column
492

493
    def get_type(self):
494
        return EngineUtil.get_channel_type(self.uri)
495

496

497
498
499
500
501
502
503
504
505
506
507
508
509
510
    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


511
    def get_next_entries(self, schedule_sensitive=True):
David Trattnig's avatar
David Trattnig committed
512
        """
513
        Retrieves all following entries as part of the current entry's playlist.
David Trattnig's avatar
David Trattnig committed
514

515
516
517
518
        Args:
            schedule_sensitive (Boolean):   If `True` entries which start after \
                the end of the schedule are excluded

David Trattnig's avatar
David Trattnig committed
519
520
521
522
523
524
        Returns:
            (List):     List of PlaylistEntry
        """
        next_entries = []
        for entry in self.playlist.entries:
            if entry.entry_start > self.entry_start:
525
526
527
528
529
                if schedule_sensitive:
                    if entry.entry_start < self.playlist.schedule.schedule_end:
                        next_entries.append(entry)
                else:
                    next_entries.append(entry)
David Trattnig's avatar
David Trattnig committed
530
531
        return next_entries

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
532

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

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

David Trattnig's avatar
David Trattnig committed
556

557

558

559
class PlaylistEntryMetaData(DB.Model, AuraDatabaseModel):
560
561
562
    """
    Metadata for a playlist entry such as the artist and track name.
    """
563
    __tablename__ = "playlist_entry_metadata"
564

565
566
    artificial_id = Column(Integer, primary_key=True)
    artificial_entry_id = Column(Integer, ForeignKey("playlist_entry.artificial_id"))
567

568
569
570
    artist = Column(String(256))
    title = Column(String(256))
    album = Column(String(256))
571

572
    entry = relationship("PlaylistEntry", uselist=False, back_populates="meta_data")
573

574
575
    @staticmethod
    def select_metadata_for_entry(artificial_playlistentry_id):
576
        return DB.session.query(PlaylistEntryMetaData).filter(PlaylistEntryMetaData.artificial_entry_id == artificial_playlistentry_id).first()
577
578