Exemplo n.º 1
0
 def __init__(self, store):
     if store is None:
         raise ValueError("store must not be None.")
     self.store = store
     self.scheduleMaintainer = ScheduleMaintainer(self)
Exemplo n.º 2
0
 def __init__(self, store):
     if store is None:
         raise ValueError("store must not be None.")
     self.store = store
     self.scheduleMaintainer = ScheduleMaintainer(self)
Exemplo n.º 3
0
class PriyomInterface:
    Class2RootNode = {
        Transmission: "priyom-transmission-export",
        Schedule: "priyom-schedule-export",
        Station: "priyom-station-export",
        Broadcast: "priyom-broadcast-export",
        TransmissionClass: "priyom-generic-export",
        TransmissionTable: "priyom-generic-export"
    }
    
    def __init__(self, store):
        if store is None:
            raise ValueError("store must not be None.")
        self.store = store
        self.scheduleMaintainer = ScheduleMaintainer(self)
        
    def createDocument(self, rootNodeName):
        return ElementTree.ElementTree(ElementTree.Element(u"{{{0}}}{1}".format(XMLIntf.namespace, rootNodeName)))
        
    def _createDocumentOptional(self, givendoc, rootNodeName):
        return self.createDocument(rootNodeName) if givendoc is None else givendoc
        
    def _getClassDoc(self, classType, doc):
        return self._createDocumentOptional(doc, self.Class2RootNode[classType])
        
    def _exportToDomSimple(self, obj, rootName, flags = None, tree = None):
        tree = self._getClassDoc(type(obj), tree)
        obj.toDom(tree.getroot(), flags)
        return tree
        
    def exportTransmissionToDom(self, transmission, flags = None, doc = None):
        return self._exportToDomSimple(transmission, "priyom-transmission-export", flags, doc)
        
    def exportScheduleToDom(self, schedule, flags = None, doc = None):
        return self._exportToDomSimple(schedule, "priyom-schedule-export", flags, doc)
        
    def exportStationToDom(self, station, flags = None, doc = None):
        return self._exportToDomSimple(station, "priyom-station-export", flags, doc)
        
    def exportBroadcastToDom(self, broadcast, flags = None, doc = None):
        return self._exportToDomSimple(broadcast, "priyom-broadcast-export", flags, doc)
        
    def exportToETree(self, obj, flags = None, doc = None):
        return self._exportToDomSimple(obj, self.Class2RootNode[type(obj)], flags, doc)
        
    def exportListToETree(self, list, classType, flags = None, doc = None):
        tree = self._getClassDoc(classType, doc)
        for obj in list:
            obj.toDom(tree.getroot(), flags)
        return tree
        
    def getImportContext(self):
        return Imports.ImportContext(self.store)
        
    def garbageCollection(self):
        staleBroadcasts = list((str(t[0]) for t in self.store.execute("""SELECT broadcasts.ID FROM broadcasts LEFT OUTER JOIN stations ON (broadcasts.StationID = stations.ID) WHERE stations.ID IS NULL""")))
        if len(staleBroadcasts) > 0:
            self.store.execute("""DELETE FROM broadcasts WHERE ID IN ({0})""".format(",".join(staleBroadcasts)))
        
        staleTransmissions = list((str(t[0]) for t in self.store.execute("""SELECT transmissions.ID FROM transmissions LEFT OUTER JOIN broadcasts ON (transmissions.BroadcastID = broadcasts.ID) WHERE broadcasts.ID IS NULL""")))
        if len(staleTransmissions) > 0:
            self.store.execute("""DELETE FROM transmissions WHERE ID IN ({0})""".format(",".join(staleTransmissions)))
            
        staleBroadcastFrequencies = list((str(t[0]) for t in self.store.execute("""SELECT broadcastFrequencies.ID FROM broadcastFrequencies LEFT OUTER JOIN broadcasts ON (broadcastFrequencies.BroadcastID = broadcasts.ID) WHERE broadcasts.ID IS NULL""")))
        if len(staleBroadcastFrequencies) > 0:
            self.store.execute("""DELETE FROM broadcastFrequencies WHERE ID IN ({0})""".format(",".join(staleBroadcastFrequencies)))
        
        foreignSupplementTables = {}
        staleForeignSupplements = list()
        for id, localID, fieldName in self.store.execute("""SELECT t.ID, t.LocalID, t.FieldName FROM foreignSupplement as t"""):
            partitioned = fieldName.partition(".")
            if len(partitioned[2]) == 0:
                staleForeignSupplements.append(str(id))
                continue
            tableName = partitioned[0]
            if not tableName in foreignSupplementTables:
                foreignSupplementTables[tableName] = list()
            foreignSupplementTables[tableName].append((localID, id))
        if len(staleForeignSupplements) > 0:
            self.store.execute("""DELETE FROM foreignSupplement WHERE ID IN ({0})""".format(",".join(staleForeignSupplements)))
        
        staleForeignSupplementCount = len(staleForeignSupplements)
        for table, items in foreignSupplementTables.iteritems():
            staleForeignSupplements = list((str(t[0]) for t in self.store.execute("""SELECT foreignSupplement.ID FROM foreignSupplement LEFT OUTER JOIN `{0}` ON (foreignSupplement.LocalID = `{0}`.ID) WHERE (`{0}`.ID IS NULL) AND (foreignSupplement.ID IN ({1}))""".format(table, ",".join((str(item[1]) for item in items))))))
            if len(staleForeignSupplements) > 0:
                self.store.execute("""DELETE FROM foreignSupplement WHERE ID IN ({0})""".format(",".join(staleForeignSupplements)))
            staleForeignSupplementCount += len(staleForeignSupplements)
        
        emptySupplements = self.store.find(ForeignSupplement, 
            Or(
                ForeignSupplement.LangCode == u"",
                ForeignSupplement.ForeignText == u""
            ))
        emptySupplementCount = emptySupplements.count()
        emptySupplements.remove()

        staleTXItemCount = 0
        for table in self.store.find(TransmissionTable):
            staleTXItems = list((str(t[0]) for t in self.store.execute("""SELECT `{0}`.ID FROM `{0}` LEFT OUTER JOIN transmissions ON (`{0}`.TransmissionID = transmissions.ID) WHERE transmissions.ID IS NULL""".format(table.TableName))))
            staleTXItemCount += len(staleTXItems)
            if len(staleTXItems) > 0:
                self.store.execute("""DELETE FROM `{0}` WHERE ID IN ({1})""".format(table.TableName, ",".join(staleTXItems)))
        
        return (len(staleBroadcasts), len(staleTransmissions), staleForeignSupplementCount, staleTXItemCount, emptySupplementCount)
        
    def deleteTransmissionBlock(self, obj, force = False):
        store = self.store
        obj.deleteForeignSupplements()
        store.remove(obj)
        
    def deleteTransmission(self, obj, force = False):
        store = self.store
        blocks = obj.blocks
        for block in blocks:
            self.deleteTransmissionBlock(block, True)
        if obj.ForeignCallsign is not None:
            del obj.ForeignCallsign.Value
        if obj.Broadcast is not None:
            obj.Broadcast.transmissionRemoved()
        store.remove(obj)
        return True
        
    def deleteScheduleLeaf(self, obj, force = False):
        store = self.store
        freqs = store.find(ScheduleLeafFrequency, ScheduleLeafFrequency.ScheduleLeafID == obj.ID)
        if force:
            freqs.remove()
        else:
            if freqs.count() > 0:
                return False
        store.remove(obj)
        return True
        
    def deleteSchedule(self, obj, force = False):
        store = self.store
        stations = store.find(Station, Station.ScheduleID == obj.ID)
        # won't delete all stations which are using this schedule, not
        # even with force == True
        if stations.count() > 0:
            return False
        children = store.find(Schedule, Schedule.ParentID == obj.ID)
        leaves = store.find(ScheduleLeaf, ScheduleLeaf.ScheduleID == obj.ID)
        if force:
            for child in children:
                self.deleteSchedule(child, True)
            for leaf in leaves:
                self.deleteScheduleLeaf(leaf, True)
        else:
            if children.count() > 0:
                return False
            if leaves.count() > 0:
                return False
        if self.Parent is not None:
            self.Parent.touch()
        store.remove(Schedule)
        return True
    
    def deleteStation(self, obj, force = False):
        store = self.store
        broadcasts = store.find(Broadcast, Broadcast.StationID == obj.ID)
        if force:
            for broadcast in broadcasts:
                self.deleteBroadcast(broadcast, True)
        else:
            if broadcasts.count() > 0:
                return False
        store.remove(obj)
        return True
        
    def deleteBroadcast(self, obj, force = False):
        store = self.store
        transmissions = store.find(Transmission, Transmission.BroadcastID == obj.ID)
        if force:
            for transmission in transmissions:
                self.deleteTransmission(transmission, True)
        else:
            if transmissions.count() > 0:
                return False
        store.find(BroadcastFrequency, BroadcastFrequency.BroadcastID == obj.ID).remove()
        if obj.Station is not None:
            obj.Station.broadcastRemoved()
        store.remove(obj)
        return True
        
    def delete(self, obj, force = False):
        try:
            method = {
                Transmission: self.deleteTransmission,
                Schedule: self.deleteSchedule,
                Station: self.deleteStation,
                Broadcast: self.deleteBroadcast
            }[type(obj)]
        except KeyError:
            raise Exception("Can only delete elementary objects (got object of type %s)." % (str(type(obj))))
        return method(obj, force)
        
    def normalizeDate(self, dateTime):
        return datetime(year=dateTime.year, month=dateTime.month, day=dateTime.day)
        
    def importTransaction(self, tree):
        context = self.getImportContext()
        for node in tree.getroot():
            tag = node.tag
            tagPart = tag.partition("}")
            if len(tagPart[1]) == 0:
                context.log("Encountered non-namespaced tag: {0}".format(tag))
                continue
            if tagPart[0][1:] != XMLIntf.namespace:
                context.log("Encountered tag with wrong namespace: {0}. Only namespace supported is {1}".format(tag, XMLIntf.importNamespace))
                continue
            tag = tagPart[2]
            if node.tag == u"delete":
                try:
                    clsName = node.getAttribute("type")
                    id = node.getAttribute("id")
                except:
                    context.log("Something is wrong -- perhaps a missing attribute?")
                    continue
                try:
                    cls = {
                        "transmission": Transmission,
                        "broadcast": Broadcast,
                        "station": Station,
                        "schedule": Schedule
                    }[clsName]
                except KeyError:
                    context.log("Attempt to delete unknown type: %s" % node.getAttribute("type"))
                    continue
                try:
                    id = int(id)
                except ValueError:
                    context.log("Supplied invalid id to delete: %s" % node.getAttribute("id"))
                    continue
                obj = self.store.get(cls, id)
                if obj is None:
                    context.log("Cannot delete %s with id %d: Not found" % (str(cls), id))
                    continue
                if not self.delete(obj, node.hasAttribute("force") and (node.getAttribute("force") == "true")):
                    context.log(u"Could not delete %s with id %d (did you check there are no more objects associated with it?)" % (unicode(cls), id))
                else:
                    context.log(u"Deleted %s with id %d" % (unicode(cls), id))
            else:
                try:
                    cls = {
                        "transmission": Transmission,
                        "broadcast": Broadcast,
                        "station": Station,
                        "schedule": Schedule
                    }[tag]
                except KeyError:
                    context.log("Invalid transaction node: %s" % node.tagName)
                    continue
                context.importFromETree(node, cls)
        return context
        
    def getStation(self, stationDesignator, notModifiedCheck = None, head = False):
        try:
            stationId = int(stationDesignator)
        except ValueError:
            stationId = None
        if stationId is not None:
            station = self.store.get(Station, stationId)
        else:
            resultSet = self.store.find(Station, Station.EnigmaIdentifier == stationDesignator)
            station = resultSet.any()
            if station is None:
                resultSet = self.store.find(Station, Station.PriyomIdentifier == stationDesignator)
            station = resultSet.any()
        if notModifiedCheck is not None:
            notModifiedCheck(station.Modified)
        return (station.Modified, station)
        
    def getCloseBroadcasts(self, stationId, time, jitter, notModifiedCheck = None, head = False):
        wideBroadcasts = self.store.find(Broadcast, Broadcast.StationID == stationId)
        lastModified = max(
            wideBroadcasts.max(Broadcast.Modified), 
            self.store.get(Station, stationId).BroadcastRemoved
        )
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        
        broadcasts = wideBroadcasts.find(And(
            And(
                Broadcast.BroadcastStart <= time + jitter,
                Broadcast.BroadcastEnd > time - jitter
            ),
            Broadcast.Type == u"data"
        ))
        return (lastModified, broadcasts)
        
    def listObjects(self, cls, limiter = None, notModifiedCheck = None, head = False):
        objects = self.store.find(cls)
        lastModified = objects.max(cls.Modified)
        if cls == Transmission:
            lastModified = max(lastModified, self.store.find(Broadcast).max(Broadcast.TransmissionRemoved))
        elif cls == Broadcast:
            lastModified = max(lastModified, self.store.find(Station).max(Station.BroadcastRemoved))
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        if limiter is not None:
            objects = limiter(objects)
        return (lastModified, objects)
    
    def getTransmissionsByMonth(self, stationId, year, month, limiter = None, notModifiedCheck = None, head = False):
        if month is not None:
            startTimestamp = datetime(year, month, 1)
            if month != 12:
                endTimestamp = datetime(year, month+1, 1)
            else:
                endTimestamp = datetime(year+1, 1, 1)
        else:
            startTimestamp = datetime(year, 1, 1)
            endTimestamp = datetime(year+1, 1, 1)
        startTimestamp = TimeUtils.toTimestamp(startTimestamp)
        endTimestamp = TimeUtils.toTimestamp(endTimestamp)
        
        transmissions = self.store.find((Transmission, Broadcast), 
            Transmission.BroadcastID == Broadcast.ID,
            And(Broadcast.StationID == stationId, 
                And(Transmission.Timestamp >= startTimestamp,
                    Transmission.Timestamp < endTimestamp)))
        lastModified = max(
            transmissions.max(Transmission.Modified), 
            self.store.find(Broadcast, Broadcast.StationID == stationId).max(Broadcast.TransmissionRemoved),
            self.store.get(Station, stationId).BroadcastRemoved
        )
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        transmissions.order_by(Desc(Transmission.Timestamp))
        if limiter is not None:
            transmissions = limiter(transmissions)
        return (lastModified, (transmission for (transmission, broadcast) in transmissions))
        
    def getTransmissionStats(self, stationId, notModifiedCheck = None, head = False):
        transmissions = self.store.find(Transmission, 
            Transmission.BroadcastID == Broadcast.ID, 
            Broadcast.StationID == stationId)
        lastModified = max(
            transmissions.max(Transmission.Modified),
            self.store.find(Broadcast, Broadcast.StationID == stationId).max(Broadcast.TransmissionRemoved),
            self.store.get(Station, stationId).BroadcastRemoved
        )
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        
        months = self.store.execute("SELECT YEAR(FROM_UNIXTIME(Timestamp)) as year, MONTH(FROM_UNIXTIME(Timestamp)) as month, COUNT(DATE_FORMAT(FROM_UNIXTIME(Timestamp), '%%Y-%%m')) FROM transmissions LEFT JOIN broadcasts ON (transmissions.BroadcastID = broadcasts.ID) WHERE broadcasts.StationID = '%d' GROUP BY year, month ORDER BY year ASC, month ASC" % (stationId))
        
        return (lastModified, months)
        
    def getUpcomingBroadcasts(self, station, all, update, timeLimit, maxTimeRange, limiter = None, notModifiedCheck = None, head = False):
        now = TimeUtils.now()
        where = And(Or(Broadcast.BroadcastEnd > now, Broadcast.BroadcastEnd == None), (Broadcast.BroadcastStart < (now + timeLimit)))
        if not all:
            where = And(where, Broadcast.Type == u"data")
        if station is not None:
            where = And(where, Broadcast.StationID == stationId)
        
        broadcasts = self.store.find(Broadcast, where)
        lastModified = max(
            broadcasts.max(Broadcast.Modified),
            station.BroadcastRemoved if station is not None else None,
            station.Schedule.Modified if (station is not None and station.Schedule is not None) else None
        )
        if head:
            return (lastModified, None, None, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        
        if update:
            untilDate = datetime.fromtimestamp(now)
            untilDate += timedelta(seconds=timeLimit)
            untilDate = self.normalizeDate(untilDate)
            
            until = TimeUtils.toTimestamp(untilDate)
            
            if station is None:
                validUntil = self.scheduleMaintainer.updateSchedules(until, maxTimeRange)
            else:
                validUntil = self.scheduleMaintainer.updateSchedule(station, until, maxTimeRange)
            # trans.set_header_value("Expires", self.model.formatHTTPTimestamp(validUntil))
        return (lastModified, broadcasts if limiter is None else limiter(broadcasts), validUntil >= until, validUntil)
        
    def getStationFrequencies(self, station, notModifiedCheck = None, head = False):
        global UPCOMING, PAST, ONAIR
        broadcasts = self.store.find(Broadcast,
            Broadcast.StationID == station.ID)
        lastModified = max(
            broadcasts.max(Broadcast.Modified),
            station.BroadcastRemoved
        )
        if station.Schedule is not None:
            scheduleLeafs = self.store.find(ScheduleLeaf,
                ScheduleLeaf.StationID == station.ID)
            lastModified = lastModified if station.Schedule.Modified < lastModified else station.Schedule.Modified
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        
        if station.Schedule is None:
            now = TimeUtils.now()
            frequencies = self.store.find(
                (Max(Broadcast.BroadcastEnd), Min(Broadcast.BroadcastStart), Broadcast.BroadcastStart > now, BroadcastFrequency.Frequency, Modulation.Name), 
                BroadcastFrequency.ModulationID == Modulation.ID,
                BroadcastFrequency.BroadcastID == Broadcast.ID,
                Broadcast.StationID == station.ID)
            frequencies.group_by(Or(Func("ISNULL", Broadcast.BroadcastEnd), And(Broadcast.BroadcastEnd >= now, Broadcast.BroadcastStart <= now)), Broadcast.BroadcastStart > now, BroadcastFrequency.Frequency, Modulation.Name)
            
            return (lastModified, ((freq, modulation, UPCOMING if isUpcoming == 1 else (ONAIR if lastUse is None else PAST), nextUse if isUpcoming else lastUse) for (lastUse, nextUse, isUpcoming, freq, modulation) in frequencies))
            
    def getDuplicateTransmissions(self, txTable, mainStation, matchFields = None, includeOtherStationsWithin = 86400, notModifiedCheck = None, head = False):
        txItem1 = ClassAlias(txTable.PythonClass, name="txItem1")
        txItem2 = ClassAlias(txTable.PythonClass, name="txItem2")
        
        tx1 = ClassAlias(Transmission, name="tx1")
        tx2 = ClassAlias(Transmission, name="tx2")
        
        bc1 = ClassAlias(Broadcast, name="broadcast1")
        bc2 = ClassAlias(Broadcast, name="broadcast2")
        
        on = txItem1.TransmissionID > txItem2.TransmissionID
        if matchFields is None:
            matchFields = txTable.PythonClass.fields
        for field in txTable.PythonClass.fields:
            cond = getattr(txItem1, field.FieldName) == getattr(txItem2, field.FieldName)
            on = And(on, cond)
        
        
        dupes = self.store.using(
            txItem1, 
            LeftJoin(tx1, on=(txItem1.TransmissionID == tx1.ID)), 
            LeftJoin(bc1, on=(tx1.BroadcastID == bc1.ID)),
            LeftJoin(txItem2, on=on), 
            LeftJoin(tx2, on=(txItem2.TransmissionID == tx2.ID)),
            LeftJoin(bc2, on=(tx2.BroadcastID == bc2.ID))
        ).find((bc1, tx1, txItem1, bc2, tx2, txItem2), 
            And(
                bc1.StationID == mainStation.ID,  
                Or(
                    bc2.StationID == mainStation.ID,
                    Func("ABS", (tx2.Timestamp - tx1.Timestamp) <= includeOtherStationsWithin)
                )
            )
        )
        
        lastModified = max(
            dupes.max(tx1.Modified),
            dupes.max(tx2.Modified),
            dupes.max(bc1.TransmissionRemoved),
            dupes.max(bc2.TransmissionRemoved),
            self.store.find(Station).max(Station.BroadcastRemoved)
        )
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
            
            
        return (lastModified, dupes.order_by(Asc(tx1.Timestamp)))
    
    def getStatistics(self):
        """Returns a tuple with the following statistical values (in order):
        * Station count
        * Broadcast count
        * Transmission count
        * Transmissions / broadcast mean
        * Items
        * Items / transmission mean"""
        
        stationCount = self.store.find(Station).count()
        broadcastCount = self.store.find(Broadcast).count()
        transmissionCount = self.store.find(Transmission).count()
        
        tables = self.store.find(TransmissionTable)
        itemCount = 0
        #countLists = []
        for table in tables:
            itemCount += self.store.find(table.PythonClass).count()
            
            """countList = list(self.store.using(
                Transmission,
                LeftJoin(table.PythonClass, Transmission.ID == table.PythonClass.TransmissionID)
            ).find(Count() - Func("IF", table.PythonClass.TransmissionID == None, 1, 0)).group_by(Transmission.ID))
            countLists.append(countList)"""
        """print("\n".join((unicode(countList) for countList in countLists)))
        countList = [sum((countList[i] for countList in countLists)) for i in xrange(transmissionCount)]
        mean = float(itemCount) / float(transmissionCount)
        meansqr = mean * mean
        s = 0
        for item in countList:
            s += (item - mean) * (item - mean)
        s = math.sqrt(1./(len(countList)*(len(countList)-1.))*s)"""
        
        return (stationCount, broadcastCount, transmissionCount, float(transmissionCount)/float(broadcastCount), itemCount, float(itemCount)/float(transmissionCount))
Exemplo n.º 4
0
class PriyomInterface:
    Class2RootNode = {
        Transmission: "priyom-transmission-export",
        Schedule: "priyom-schedule-export",
        Station: "priyom-station-export",
        Broadcast: "priyom-broadcast-export",
        TransmissionClass: "priyom-generic-export",
        TransmissionTable: "priyom-generic-export"
    }

    def __init__(self, store):
        if store is None:
            raise ValueError("store must not be None.")
        self.store = store
        self.scheduleMaintainer = ScheduleMaintainer(self)

    def createDocument(self, rootNodeName):
        return ElementTree.ElementTree(
            ElementTree.Element(u"{{{0}}}{1}".format(XMLIntf.namespace,
                                                     rootNodeName)))

    def _createDocumentOptional(self, givendoc, rootNodeName):
        return self.createDocument(
            rootNodeName) if givendoc is None else givendoc

    def _getClassDoc(self, classType, doc):
        return self._createDocumentOptional(doc,
                                            self.Class2RootNode[classType])

    def _exportToDomSimple(self, obj, rootName, flags=None, tree=None):
        tree = self._getClassDoc(type(obj), tree)
        obj.toDom(tree.getroot(), flags)
        return tree

    def exportTransmissionToDom(self, transmission, flags=None, doc=None):
        return self._exportToDomSimple(transmission,
                                       "priyom-transmission-export", flags,
                                       doc)

    def exportScheduleToDom(self, schedule, flags=None, doc=None):
        return self._exportToDomSimple(schedule, "priyom-schedule-export",
                                       flags, doc)

    def exportStationToDom(self, station, flags=None, doc=None):
        return self._exportToDomSimple(station, "priyom-station-export", flags,
                                       doc)

    def exportBroadcastToDom(self, broadcast, flags=None, doc=None):
        return self._exportToDomSimple(broadcast, "priyom-broadcast-export",
                                       flags, doc)

    def exportToETree(self, obj, flags=None, doc=None):
        return self._exportToDomSimple(obj, self.Class2RootNode[type(obj)],
                                       flags, doc)

    def exportListToETree(self, list, classType, flags=None, doc=None):
        tree = self._getClassDoc(classType, doc)
        for obj in list:
            obj.toDom(tree.getroot(), flags)
        return tree

    def getImportContext(self):
        return Imports.ImportContext(self.store)

    def garbageCollection(self):
        staleBroadcasts = list((str(t[0]) for t in self.store.execute(
            """SELECT broadcasts.ID FROM broadcasts LEFT OUTER JOIN stations ON (broadcasts.StationID = stations.ID) WHERE stations.ID IS NULL"""
        )))
        if len(staleBroadcasts) > 0:
            self.store.execute(
                """DELETE FROM broadcasts WHERE ID IN ({0})""".format(
                    ",".join(staleBroadcasts)))

        staleTransmissions = list((str(t[0]) for t in self.store.execute(
            """SELECT transmissions.ID FROM transmissions LEFT OUTER JOIN broadcasts ON (transmissions.BroadcastID = broadcasts.ID) WHERE broadcasts.ID IS NULL"""
        )))
        if len(staleTransmissions) > 0:
            self.store.execute(
                """DELETE FROM transmissions WHERE ID IN ({0})""".format(
                    ",".join(staleTransmissions)))

        staleBroadcastFrequencies = list((str(
            t[0]
        ) for t in self.store.execute(
            """SELECT broadcastFrequencies.ID FROM broadcastFrequencies LEFT OUTER JOIN broadcasts ON (broadcastFrequencies.BroadcastID = broadcasts.ID) WHERE broadcasts.ID IS NULL"""
        )))
        if len(staleBroadcastFrequencies) > 0:
            self.store.execute(
                """DELETE FROM broadcastFrequencies WHERE ID IN ({0})""".
                format(",".join(staleBroadcastFrequencies)))

        foreignSupplementTables = {}
        staleForeignSupplements = list()
        for id, localID, fieldName in self.store.execute(
                """SELECT t.ID, t.LocalID, t.FieldName FROM foreignSupplement as t"""
        ):
            partitioned = fieldName.partition(".")
            if len(partitioned[2]) == 0:
                staleForeignSupplements.append(str(id))
                continue
            tableName = partitioned[0]
            if not tableName in foreignSupplementTables:
                foreignSupplementTables[tableName] = list()
            foreignSupplementTables[tableName].append((localID, id))
        if len(staleForeignSupplements) > 0:
            self.store.execute(
                """DELETE FROM foreignSupplement WHERE ID IN ({0})""".format(
                    ",".join(staleForeignSupplements)))

        staleForeignSupplementCount = len(staleForeignSupplements)
        for table, items in foreignSupplementTables.iteritems():
            staleForeignSupplements = list((str(
                t[0]
            ) for t in self.store.execute(
                """SELECT foreignSupplement.ID FROM foreignSupplement LEFT OUTER JOIN `{0}` ON (foreignSupplement.LocalID = `{0}`.ID) WHERE (`{0}`.ID IS NULL) AND (foreignSupplement.ID IN ({1}))"""
                .format(table, ",".join((str(item[1]) for item in items))))))
            if len(staleForeignSupplements) > 0:
                self.store.execute(
                    """DELETE FROM foreignSupplement WHERE ID IN ({0})""".
                    format(",".join(staleForeignSupplements)))
            staleForeignSupplementCount += len(staleForeignSupplements)

        emptySupplements = self.store.find(
            ForeignSupplement,
            Or(ForeignSupplement.LangCode == u"",
               ForeignSupplement.ForeignText == u""))
        emptySupplementCount = emptySupplements.count()
        emptySupplements.remove()

        staleTXItemCount = 0
        for table in self.store.find(TransmissionTable):
            staleTXItems = list((str(t[0]) for t in self.store.execute(
                """SELECT `{0}`.ID FROM `{0}` LEFT OUTER JOIN transmissions ON (`{0}`.TransmissionID = transmissions.ID) WHERE transmissions.ID IS NULL"""
                .format(table.TableName))))
            staleTXItemCount += len(staleTXItems)
            if len(staleTXItems) > 0:
                self.store.execute(
                    """DELETE FROM `{0}` WHERE ID IN ({1})""".format(
                        table.TableName, ",".join(staleTXItems)))

        return (len(staleBroadcasts), len(staleTransmissions),
                staleForeignSupplementCount, staleTXItemCount,
                emptySupplementCount)

    def deleteTransmissionBlock(self, obj, force=False):
        store = self.store
        obj.deleteForeignSupplements()
        store.remove(obj)

    def deleteTransmission(self, obj, force=False):
        store = self.store
        blocks = obj.blocks
        for block in blocks:
            self.deleteTransmissionBlock(block, True)
        if obj.ForeignCallsign is not None:
            del obj.ForeignCallsign.Value
        if obj.Broadcast is not None:
            obj.Broadcast.transmissionRemoved()
        store.remove(obj)
        return True

    def deleteScheduleLeaf(self, obj, force=False):
        store = self.store
        freqs = store.find(ScheduleLeafFrequency,
                           ScheduleLeafFrequency.ScheduleLeafID == obj.ID)
        if force:
            freqs.remove()
        else:
            if freqs.count() > 0:
                return False
        store.remove(obj)
        return True

    def deleteSchedule(self, obj, force=False):
        store = self.store
        stations = store.find(Station, Station.ScheduleID == obj.ID)
        # won't delete all stations which are using this schedule, not
        # even with force == True
        if stations.count() > 0:
            return False
        children = store.find(Schedule, Schedule.ParentID == obj.ID)
        leaves = store.find(ScheduleLeaf, ScheduleLeaf.ScheduleID == obj.ID)
        if force:
            for child in children:
                self.deleteSchedule(child, True)
            for leaf in leaves:
                self.deleteScheduleLeaf(leaf, True)
        else:
            if children.count() > 0:
                return False
            if leaves.count() > 0:
                return False
        if self.Parent is not None:
            self.Parent.touch()
        store.remove(Schedule)
        return True

    def deleteStation(self, obj, force=False):
        store = self.store
        broadcasts = store.find(Broadcast, Broadcast.StationID == obj.ID)
        if force:
            for broadcast in broadcasts:
                self.deleteBroadcast(broadcast, True)
        else:
            if broadcasts.count() > 0:
                return False
        store.remove(obj)
        return True

    def deleteBroadcast(self, obj, force=False):
        store = self.store
        transmissions = store.find(Transmission,
                                   Transmission.BroadcastID == obj.ID)
        if force:
            for transmission in transmissions:
                self.deleteTransmission(transmission, True)
        else:
            if transmissions.count() > 0:
                return False
        store.find(BroadcastFrequency,
                   BroadcastFrequency.BroadcastID == obj.ID).remove()
        if obj.Station is not None:
            obj.Station.broadcastRemoved()
        store.remove(obj)
        return True

    def delete(self, obj, force=False):
        try:
            method = {
                Transmission: self.deleteTransmission,
                Schedule: self.deleteSchedule,
                Station: self.deleteStation,
                Broadcast: self.deleteBroadcast
            }[type(obj)]
        except KeyError:
            raise Exception(
                "Can only delete elementary objects (got object of type %s)." %
                (str(type(obj))))
        return method(obj, force)

    def normalizeDate(self, dateTime):
        return datetime(year=dateTime.year,
                        month=dateTime.month,
                        day=dateTime.day)

    def importTransaction(self, tree):
        context = self.getImportContext()
        for node in tree.getroot():
            tag = node.tag
            tagPart = tag.partition("}")
            if len(tagPart[1]) == 0:
                context.log("Encountered non-namespaced tag: {0}".format(tag))
                continue
            if tagPart[0][1:] != XMLIntf.namespace:
                context.log(
                    "Encountered tag with wrong namespace: {0}. Only namespace supported is {1}"
                    .format(tag, XMLIntf.importNamespace))
                continue
            tag = tagPart[2]
            if node.tag == u"delete":
                try:
                    clsName = node.getAttribute("type")
                    id = node.getAttribute("id")
                except:
                    context.log(
                        "Something is wrong -- perhaps a missing attribute?")
                    continue
                try:
                    cls = {
                        "transmission": Transmission,
                        "broadcast": Broadcast,
                        "station": Station,
                        "schedule": Schedule
                    }[clsName]
                except KeyError:
                    context.log("Attempt to delete unknown type: %s" %
                                node.getAttribute("type"))
                    continue
                try:
                    id = int(id)
                except ValueError:
                    context.log("Supplied invalid id to delete: %s" %
                                node.getAttribute("id"))
                    continue
                obj = self.store.get(cls, id)
                if obj is None:
                    context.log("Cannot delete %s with id %d: Not found" %
                                (str(cls), id))
                    continue
                if not self.delete(
                        obj,
                        node.hasAttribute("force") and
                    (node.getAttribute("force") == "true")):
                    context.log(
                        u"Could not delete %s with id %d (did you check there are no more objects associated with it?)"
                        % (unicode(cls), id))
                else:
                    context.log(u"Deleted %s with id %d" % (unicode(cls), id))
            else:
                try:
                    cls = {
                        "transmission": Transmission,
                        "broadcast": Broadcast,
                        "station": Station,
                        "schedule": Schedule
                    }[tag]
                except KeyError:
                    context.log("Invalid transaction node: %s" % node.tagName)
                    continue
                context.importFromETree(node, cls)
        return context

    def getStation(self, stationDesignator, notModifiedCheck=None, head=False):
        try:
            stationId = int(stationDesignator)
        except ValueError:
            stationId = None
        if stationId is not None:
            station = self.store.get(Station, stationId)
        else:
            resultSet = self.store.find(
                Station, Station.EnigmaIdentifier == stationDesignator)
            station = resultSet.any()
            if station is None:
                resultSet = self.store.find(
                    Station, Station.PriyomIdentifier == stationDesignator)
            station = resultSet.any()
        if notModifiedCheck is not None:
            notModifiedCheck(station.Modified)
        return (station.Modified, station)

    def getCloseBroadcasts(self,
                           stationId,
                           time,
                           jitter,
                           notModifiedCheck=None,
                           head=False):
        wideBroadcasts = self.store.find(Broadcast,
                                         Broadcast.StationID == stationId)
        lastModified = max(wideBroadcasts.max(Broadcast.Modified),
                           self.store.get(Station, stationId).BroadcastRemoved)
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)

        broadcasts = wideBroadcasts.find(
            And(
                And(Broadcast.BroadcastStart <= time + jitter,
                    Broadcast.BroadcastEnd > time - jitter),
                Broadcast.Type == u"data"))
        return (lastModified, broadcasts)

    def listObjects(self,
                    cls,
                    limiter=None,
                    notModifiedCheck=None,
                    head=False):
        objects = self.store.find(cls)
        lastModified = objects.max(cls.Modified)
        if cls == Transmission:
            lastModified = max(
                lastModified,
                self.store.find(Broadcast).max(Broadcast.TransmissionRemoved))
        elif cls == Broadcast:
            lastModified = max(
                lastModified,
                self.store.find(Station).max(Station.BroadcastRemoved))
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        if limiter is not None:
            objects = limiter(objects)
        return (lastModified, objects)

    def getTransmissionsByMonth(self,
                                stationId,
                                year,
                                month,
                                limiter=None,
                                notModifiedCheck=None,
                                head=False):
        if month is not None:
            startTimestamp = datetime(year, month, 1)
            if month != 12:
                endTimestamp = datetime(year, month + 1, 1)
            else:
                endTimestamp = datetime(year + 1, 1, 1)
        else:
            startTimestamp = datetime(year, 1, 1)
            endTimestamp = datetime(year + 1, 1, 1)
        startTimestamp = TimeUtils.toTimestamp(startTimestamp)
        endTimestamp = TimeUtils.toTimestamp(endTimestamp)

        transmissions = self.store.find(
            (Transmission, Broadcast),
            Transmission.BroadcastID == Broadcast.ID,
            And(
                Broadcast.StationID == stationId,
                And(Transmission.Timestamp >= startTimestamp,
                    Transmission.Timestamp < endTimestamp)))
        lastModified = max(
            transmissions.max(Transmission.Modified),
            self.store.find(Broadcast, Broadcast.StationID == stationId).max(
                Broadcast.TransmissionRemoved),
            self.store.get(Station, stationId).BroadcastRemoved)
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)
        transmissions.order_by(Desc(Transmission.Timestamp))
        if limiter is not None:
            transmissions = limiter(transmissions)
        return (lastModified, (transmission
                               for (transmission, broadcast) in transmissions))

    def getTransmissionStats(self,
                             stationId,
                             notModifiedCheck=None,
                             head=False):
        transmissions = self.store.find(
            Transmission, Transmission.BroadcastID == Broadcast.ID,
            Broadcast.StationID == stationId)
        lastModified = max(
            transmissions.max(Transmission.Modified),
            self.store.find(Broadcast, Broadcast.StationID == stationId).max(
                Broadcast.TransmissionRemoved),
            self.store.get(Station, stationId).BroadcastRemoved)
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)

        months = self.store.execute(
            "SELECT YEAR(FROM_UNIXTIME(Timestamp)) as year, MONTH(FROM_UNIXTIME(Timestamp)) as month, COUNT(DATE_FORMAT(FROM_UNIXTIME(Timestamp), '%%Y-%%m')) FROM transmissions LEFT JOIN broadcasts ON (transmissions.BroadcastID = broadcasts.ID) WHERE broadcasts.StationID = '%d' GROUP BY year, month ORDER BY year ASC, month ASC"
            % (stationId))

        return (lastModified, months)

    def getUpcomingBroadcasts(self,
                              station,
                              all,
                              update,
                              timeLimit,
                              maxTimeRange,
                              limiter=None,
                              notModifiedCheck=None,
                              head=False):
        now = TimeUtils.now()
        where = And(
            Or(Broadcast.BroadcastEnd > now, Broadcast.BroadcastEnd == None),
            (Broadcast.BroadcastStart < (now + timeLimit)))
        if not all:
            where = And(where, Broadcast.Type == u"data")
        if station is not None:
            where = And(where, Broadcast.StationID == stationId)

        broadcasts = self.store.find(Broadcast, where)
        lastModified = max(
            broadcasts.max(Broadcast.Modified),
            station.BroadcastRemoved if station is not None else None,
            station.Schedule.Modified if
            (station is not None and station.Schedule is not None) else None)
        if head:
            return (lastModified, None, None, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)

        if update:
            untilDate = datetime.fromtimestamp(now)
            untilDate += timedelta(seconds=timeLimit)
            untilDate = self.normalizeDate(untilDate)

            until = TimeUtils.toTimestamp(untilDate)

            if station is None:
                validUntil = self.scheduleMaintainer.updateSchedules(
                    until, maxTimeRange)
            else:
                validUntil = self.scheduleMaintainer.updateSchedule(
                    station, until, maxTimeRange)
            # trans.set_header_value("Expires", self.model.formatHTTPTimestamp(validUntil))
        return (lastModified,
                broadcasts if limiter is None else limiter(broadcasts),
                validUntil >= until, validUntil)

    def getStationFrequencies(self,
                              station,
                              notModifiedCheck=None,
                              head=False):
        global UPCOMING, PAST, ONAIR
        broadcasts = self.store.find(Broadcast,
                                     Broadcast.StationID == station.ID)
        lastModified = max(broadcasts.max(Broadcast.Modified),
                           station.BroadcastRemoved)
        if station.Schedule is not None:
            scheduleLeafs = self.store.find(
                ScheduleLeaf, ScheduleLeaf.StationID == station.ID)
            lastModified = lastModified if station.Schedule.Modified < lastModified else station.Schedule.Modified
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)

        if station.Schedule is None:
            now = TimeUtils.now()
            frequencies = self.store.find(
                (Max(Broadcast.BroadcastEnd), Min(
                    Broadcast.BroadcastStart), Broadcast.BroadcastStart > now,
                 BroadcastFrequency.Frequency, Modulation.Name),
                BroadcastFrequency.ModulationID == Modulation.ID,
                BroadcastFrequency.BroadcastID == Broadcast.ID,
                Broadcast.StationID == station.ID)
            frequencies.group_by(
                Or(
                    Func("ISNULL", Broadcast.BroadcastEnd),
                    And(Broadcast.BroadcastEnd >= now,
                        Broadcast.BroadcastStart <= now)),
                Broadcast.BroadcastStart > now, BroadcastFrequency.Frequency,
                Modulation.Name)

            return (lastModified,
                    ((freq, modulation, UPCOMING if isUpcoming == 1 else
                      (ONAIR if lastUse is None else PAST),
                      nextUse if isUpcoming else lastUse)
                     for (lastUse, nextUse, isUpcoming, freq,
                          modulation) in frequencies))

    def getDuplicateTransmissions(self,
                                  txTable,
                                  mainStation,
                                  matchFields=None,
                                  includeOtherStationsWithin=86400,
                                  notModifiedCheck=None,
                                  head=False):
        txItem1 = ClassAlias(txTable.PythonClass, name="txItem1")
        txItem2 = ClassAlias(txTable.PythonClass, name="txItem2")

        tx1 = ClassAlias(Transmission, name="tx1")
        tx2 = ClassAlias(Transmission, name="tx2")

        bc1 = ClassAlias(Broadcast, name="broadcast1")
        bc2 = ClassAlias(Broadcast, name="broadcast2")

        on = txItem1.TransmissionID > txItem2.TransmissionID
        if matchFields is None:
            matchFields = txTable.PythonClass.fields
        for field in txTable.PythonClass.fields:
            cond = getattr(txItem1,
                           field.FieldName) == getattr(txItem2,
                                                       field.FieldName)
            on = And(on, cond)

        dupes = self.store.using(
            txItem1, LeftJoin(tx1, on=(txItem1.TransmissionID == tx1.ID)),
            LeftJoin(bc1, on=(tx1.BroadcastID == bc1.ID)),
            LeftJoin(txItem2, on=on),
            LeftJoin(tx2, on=(txItem2.TransmissionID == tx2.ID)),
            LeftJoin(bc2, on=(tx2.BroadcastID == bc2.ID))).find(
                (bc1, tx1, txItem1, bc2, tx2, txItem2),
                And(
                    bc1.StationID == mainStation.ID,
                    Or(
                        bc2.StationID == mainStation.ID,
                        Func("ABS", (tx2.Timestamp - tx1.Timestamp) <=
                             includeOtherStationsWithin))))

        lastModified = max(
            dupes.max(tx1.Modified), dupes.max(tx2.Modified),
            dupes.max(bc1.TransmissionRemoved),
            dupes.max(bc2.TransmissionRemoved),
            self.store.find(Station).max(Station.BroadcastRemoved))
        if head:
            return (lastModified, None)
        if notModifiedCheck is not None:
            notModifiedCheck(lastModified)

        return (lastModified, dupes.order_by(Asc(tx1.Timestamp)))

    def getStatistics(self):
        """Returns a tuple with the following statistical values (in order):
        * Station count
        * Broadcast count
        * Transmission count
        * Transmissions / broadcast mean
        * Items
        * Items / transmission mean"""

        stationCount = self.store.find(Station).count()
        broadcastCount = self.store.find(Broadcast).count()
        transmissionCount = self.store.find(Transmission).count()

        tables = self.store.find(TransmissionTable)
        itemCount = 0
        #countLists = []
        for table in tables:
            itemCount += self.store.find(table.PythonClass).count()
            """countList = list(self.store.using(
                Transmission,
                LeftJoin(table.PythonClass, Transmission.ID == table.PythonClass.TransmissionID)
            ).find(Count() - Func("IF", table.PythonClass.TransmissionID == None, 1, 0)).group_by(Transmission.ID))
            countLists.append(countList)"""
        """print("\n".join((unicode(countList) for countList in countLists)))
        countList = [sum((countList[i] for countList in countLists)) for i in xrange(transmissionCount)]
        mean = float(itemCount) / float(transmissionCount)
        meansqr = mean * mean
        s = 0
        for item in countList:
            s += (item - mean) * (item - mean)
        s = math.sqrt(1./(len(countList)*(len(countList)-1.))*s)"""

        return (stationCount, broadcastCount, transmissionCount,
                float(transmissionCount) / float(broadcastCount), itemCount,
                float(itemCount) / float(transmissionCount))