broadcasts.py 11.9 KB
Newer Older
1
__author__ = 'gg'
2
3
# -*- coding: utf-8 -*-

4
import sys
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
5
import time
6
7
import logging
import datetime
8

9
from sqlalchemy import orm, func, Boolean, Column, DateTime, Integer, String, ForeignKey, ForeignKeyConstraint
10
from sqlalchemy.orm import relationship
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
11
from sqlalchemy.sql.expression import false
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
12
from libraries.database.database import DB
13
from libraries.enum.scheduleentrytype import ScheduleEntryType
14

15

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
16
class AuraDatabaseModel:
17
18
19
20
21
    logger = None

    def __init__(self):
        self.logger = logging.getLogger("AuraEngine")

22
23
    def store(self, add=False, commit=False):
        if add:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
24
            DB.session.add(self)
25
        if commit:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
26
            DB.session.commit()
27
28

    def delete(self, commit=False):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
29
        current_db_sessions = DB.session.object_session(self)
30
31
        current_db_sessions.delete(self)
        return
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
32
        DB.session.delete(self)
33
        if commit:
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
34
            DB.session.commit()
35

36
37
    def _asdict(self):
        return self.__dict__
38

39
    def recreate_db(self, systemexit = False):
40
41
        manualschedule = Schedule()
        manualschedule.schedule_id = 0
42
        manualschedule.show_name = "Manual Show"
43

44
        self.logger.debug("Recreating Database...")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
45
        DB.drop_all()
46
        self.logger.debug("all dropped. creating...")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
47
        DB.create_all()
48
        self.logger.debug("inserting manual scheduling possibility and fallback trackservice schedule")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
49
        DB.session.add(manualschedule)
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
50
#        db.session.add(fallback_trackservice_schedule)
51
        self.logger.debug("all created. commiting...")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
52
        DB.session.commit()
53
        self.logger.debug("Database recreated!")
54

55
56
        if systemexit:
            sys.exit(0)
57

58

59
# ------------------------------------------------------------------------------------------ #
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
60
class Schedule(DB.Model, AuraDatabaseModel):
61
    """
62
    One specific Schedule for a show on a timeslot
63
    """
64
    __tablename__ = 'schedule'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
65
66

    # primary and foreign keys
67
    schedule_id = Column(Integer, primary_key=True, autoincrement=False)
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
68
    show_id = Column(Integer) # well, not needed..
69

70
71
    schedule_start = Column(DateTime) # can be null due to manual entries
    schedule_end = Column(DateTime) # can be null due to manual entries
72
73
74
75
76
77
78
79
80
    show_name = Column(String(256))
    show_hosts = Column(String(256))
    rtr_category = Column(String(256))
    comment = Column(String(512))
    languages = Column(String(256))
    type = Column(String(256))
    category = Column(String(256))
    topic = Column(String(256))
    musicfocus = Column(String(256))
81
82
83

    is_repetition = Column(Boolean())

84
85
86
87
88
    playlist_id = Column(Integer)
    timeslot_fallback_id = Column(Integer)
    show_fallback_id = Column(Integer)
    station_fallback_id = Column(Integer)

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
89
90
91
92
93
94
95
96
97
98
99
    @staticmethod
    def select_all():
        # fetching all entries
        all_entries = DB.session.query(Schedule).filter().order_by(Schedule.schedule_start).all()
        return all_entries

    @staticmethod
    def select_by_id(id):
        entry = DB.session.query(Schedule).filter(Schedule.schedule_id == id).first()
        return entry

100
101
102
103
104
105
    def get_length(self):
        sec1 = int(datetime.datetime.strptime(self.start[0:16].replace(" ", "T"), "%Y-%m-%dT%H:%M").strftime("%s"))
        sec2 = int(datetime.datetime.strptime(self.end[0:16].replace(" ", "T"), "%Y-%m-%dT%H:%M").strftime("%s"))
        len = sec2 - sec1
        return len

106

107
# ------------------------------------------------------------------------------------------ #
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
108
class ScheduleEntry(DB.Model, AuraDatabaseModel):
109
    """
110
    One schedule can have multiple entries
111
    """
112
113
    __tablename__ = 'schedule_entry'

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
114
    # primary and foreign keys
115
116
117
    playlist_id = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
    entry_num = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
    schedule_id = Column(Integer, ForeignKey("schedule.schedule_id"))
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
118

119
    entry_start = Column(DateTime)
120
    source = Column(String(256))
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
121
    volume = Column(Integer, default=100)
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
122
    is_fallback = Column(Boolean, default=False)
123
    cleansource = ""
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
124
    entry_start_unix = 0
125
    programme_index = -1
126
    type = None
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
127

128
    schedule = relationship("Schedule", foreign_keys=[schedule_id], lazy="joined")
129

130
    # normal constructor
131
132
    def __init__(self, **kwargs):
        super(ScheduleEntry, self).__init__(**kwargs)
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
133
        self.calc_unix_times()
134

135
    # constructor like - called from sqlalchemy
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
136
    @orm.reconstructor
137
138
139
140
    def reconstructor(self):
        self.calc_unix_times()
        self.set_entry_type()

141
142
143
144
145
146
147
148
    def define_clean_source(self):
        if self.source.startswith("http") or self.source.startswith("live") or self.source.startswith("linein"):
            self.cleansource = self.source
        if self.source.startswith("pool") or self.source.startswith("file"):
            self.cleansource = self.source[7:]
        if self.source.startswith("playlist"):
            self.cleansource = self.source[11:]

149
    def calc_unix_times(self):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
150
151
        if self.entry_start is not None:
            self.entry_start_unix = time.mktime(self.entry_start.timetuple())
152

153
154
    def set_entry_type(self):
        if self.source.startswith("http"):
155
156
157
158
159
            self.type = ScheduleEntryType.STREAM
        if self.source.startswith("pool") or self.source.startswith("playlist") or self.source.startswith("file"):
            self.type = ScheduleEntryType.FILESYSTEM
        if self.source.startswith("live") or self.source.startswith("linein"):
            self.type = ScheduleEntryType.LIVE
160

161
162
163
    # ------------------------------------------------------------------------------------------ #
    @staticmethod
    def select_all():
164
        # fetching all entries
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
165
        all_entries = DB.session.query(ScheduleEntry).filter(ScheduleEntry.is_fallback == false()).order_by(ScheduleEntry.entry_start).all()
166
167
168
169
170
171
172
173
174
175
176

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

        return all_entries

    @staticmethod
    def select_next_manual_entry_num():

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
177
        max_manual_entry_num = DB.session.query(func.max(ScheduleEntry.entry_num)).filter(ScheduleEntry.schedule_id == 0).first()
178
179
180
181
182
183
184
185
186

        if max_manual_entry_num[0] is None:
            return 0
        else:
            return int(max_manual_entry_num[0])+1

    # ------------------------------------------------------------------------------------------ #
    @staticmethod
    def upcoming(datefrom=datetime.datetime.now()):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
187
        upcomingtracks = DB.session.query(ScheduleEntry).filter(ScheduleEntry.start > datefrom).all()
188
189
190
191
192
        return upcomingtracks

    # ------------------------------------------------------------------------------------------ #
    @staticmethod
    def select_one(playlist_id, entry_num):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
193
        return DB.session.query(ScheduleEntry).filter(ScheduleEntry.playlist_id == playlist_id, ScheduleEntry.entry_num == entry_num).first()
194

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
195
196
197
198
199
    # ------------------------------------------------------------------------------------------ #
    @staticmethod
    def select_playlist(playlist_id):
        return DB.session.query(ScheduleEntry).filter(ScheduleEntry.playlist_id == playlist_id).all()

200
201
    # ------------------------------------------------------------------------------------------ #
    def __str__(self):
202
        return "ScheduleID: #" + str(self.schedule_id) + " Showname: " + self.schedule.show_name + " starts @ " + str(self.entry_start) + " and plays " + self.source
203

204

205
# ------------------------------------------------------------------------------------------ #
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
206
class TrackService(DB.Model, AuraDatabaseModel):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
207
    __tablename__ = 'trackservice'
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
208

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
209
210
211
    trackservice_id = Column(Integer, primary_key=True, autoincrement=True)
    playlist_id = Column(Integer, nullable=False)
    entry_num = Column(Integer, nullable=False)
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
212

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
213
    source = Column(String(255), nullable=False)
214
    start = Column(DateTime, nullable=False, default=func.now())
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
215

216
217
218
    __table_args__ = (
        ForeignKeyConstraint(['playlist_id', 'entry_num'], ['schedule_entry.playlist_id', 'schedule_entry.entry_num']),
    )
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
219

220
    #schedule = relationship("Schedule", foreign_keys=[schedule_id], lazy="joined")
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
221
222
    # trackservice_entry = relationship("ScheduleEntry", foreign_keys=[playlist_id, entry_num], lazy="joined")
    schedule_entry = relationship("ScheduleEntry", primaryjoin="and_(TrackService.playlist_id==ScheduleEntry.playlist_id, TrackService.entry_num==ScheduleEntry.entry_num)", lazy="joined")
223

224
    @staticmethod
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
225
    # ------------------------------------------------------------------------------------------ #
226
    def select_one(trackservice_id):
Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
227
        return DB.session.query(TrackService).filter(TrackService.trackservice_id == trackservice_id).first()
228

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
229
230
231
232
233
234
235
    @staticmethod
    # ------------------------------------------------------------------------------------------ #
    def select_by_day(day):
        day_plus_one = day + datetime.timedelta(days=1)
        tracks = DB.session.query(TrackService).filter(TrackService.start >= str(day), TrackService.start < str(day_plus_one)).all()
        return tracks

236
237
238
239
240
241
242
    @staticmethod
    # ------------------------------------------------------------------------------------------ #
    def select_by_range(from_day, to_day):
        tracks = DB.session.query(TrackService).filter(TrackService.start >= str(from_day),
                                                       TrackService.start < str(to_day)).all()
        return tracks

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
243
244
245
246
    # ------------------------------------------------------------------------------------------ #
    def __str__(self):
        return "TrackServiceID: #" + str(self.trackservice_id) + " playlist_id: " + str(self.playlist_id) + " started @ " + str(self.start) + " and played " + self.source

Gottfried Gaisbauer's avatar
Gottfried Gaisbauer committed
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
# ------------------------------------------------------------------------------------------ #
# class TrackServiceSchedule(db.Model, AuraDatabaseModel):
#     """
#     Trackservice is tracking every schedule.
#     """
#     __tablename__ = 'trackservice_schedule'
#
#     # primary and foreign keys
#     ts_schedule_id = Column(Integer, primary_key=True, autoincrement=True)
#     schedule_id = Column(Integer, ForeignKey("schedule.schedule_id"))
#
#     schedule = relationship("Schedule", foreign_keys=[schedule_id], lazy="joined")
#
#     # ------------------------------------------------------------------------------------------ #
#     @staticmethod
#     def select_one(schedule_id):
#         # damn BAND-AID
#         # db.session.commit()
#
#         return db.session.query(ScheduleEntry).filter(TrackServiceSchedule.schedule_id == schedule_id).first()
#
# # ------------------------------------------------------------------------------------------ #
# class TrackServiceScheduleEntry(db.Model, AuraDatabaseModel):
#     """
#     And a schedule can have multiple entries
#     """
#     __tablename__ = 'trackservice_entry'
#
#     # primary and foreign keys. the foreign keys here can be null, because of fallback stuff
#     ts_entry_id = Column(Integer, primary_key=True, autoincrement=True)
#     ts_schedule_id = Column(Integer, ForeignKey("trackservice_schedule.ts_schedule_id"), nullable=True)
#     playlist_id = Column(Integer, nullable=True)
#     entry_num = Column(Integer, nullable=True)
#
#     fallback = Column(Boolean, default=False)
#     fallback_start = Column(DateTime, nullable=True, default=None)
#     source = Column(String(256), nullable=True, default=None)
#
#     # foreign key definitions
#     __table_args__ = (
#         ForeignKeyConstraint(['playlist_id', 'entry_num'], ['schedule_entry.playlist_id', 'schedule_entry.entry_num']),
#     )
#
#     trackservice_schedule = relationship("TrackServiceSchedule", foreign_keys=[ts_schedule_id], lazy="joined")
#     #trackservice_entry = relationship("ScheduleEntry", foreign_keys=[playlist_id, entry_num], lazy="joined")
#     trackservice_entry = relationship("ScheduleEntry", primaryjoin="and_(TrackServiceScheduleEntry.playlist_id==ScheduleEntry.playlist_id, TrackServiceScheduleEntry.entry_num==ScheduleEntry.entry_num)" , lazy="joined")
#
#     @staticmethod
#     def select_all():
#         return db.session.query(TrackServiceScheduleEntry).filter().all()

298
#AuraDatabaseModel.recreate_db(True)