Python database.Indexer类代码示例

def init_indexer_table_entry(indexer_name):
    try:
        Indexer.get(fn.lower(Indexer.name) == indexer_name.lower())
    except Indexer.DoesNotExist as e:
        logger.info("Unable to find indexer with name %s in database. Will add it" % indexer_name)
        indexer = Indexer().create(name=indexer_name)
        IndexerStatus.create_or_get(indexer=indexer, first_failure=None, latest_failure=None, disabled_until=None)

 def setUp(self):    
     set_and_drop()
     config.load("testsettings.cfg")
     self.nzbsorgdb = Indexer(name="NZBs.org")
     self.nzbsorgdb.save()
     self.dognzbdb = Indexer(name="DOGNzb")
     self.dognzbdb.save()
     
     
     config.indexerSettings.newznab1.enabled = True
     config.indexerSettings.newznab1.host.set("http://127.0.0.1:5001/nzbsorg")
     config.indexerSettings.newznab1.apikey.set("apikeynzbsorg")
     self.n1 = NewzNab(config.indexerSettings.newznab1)
     self.n2 = NewzNab(config.indexerSettings.newznab2)

def get_indexer_nzb_link(indexer_name, indexerguid, title, searchid, mode, log_api_access):
    """
    Build a link that leads to the actual NZB of the indexer using the given informations. We log this as indexer API access and NZB download because this is only called
    when the NZB will be actually downloaded later (by us or a downloader) 
    :return: str
    """
    for p in indexers.enabled_indexers:
        if p.name.strip() == indexer_name.strip():
            link = p.get_nzb_link(indexerguid, title)

            # Log to database
            indexer = Indexer.get(fn.lower(Indexer.name) == indexer_name.lower())
            papiaccess = IndexerApiAccess(indexer=p.indexer, type="nzb", url=link, response_successful=None, indexer_search=searchid) if log_api_access else None
            try:
                papiaccess.username = request.authorization.username if request.authorization is not None else None
            except RuntimeError:
                pass
            papiaccess.save()
            pnzbdl = IndexerNzbDownload(indexer=indexer, indexer_search=searchid, api_access=papiaccess, mode=mode, title=title, guid=indexerguid)
            pnzbdl.save()

            return link, papiaccess, pnzbdl

    else:
        logger.error("Did not find indexer with name %s" % indexer_name)
        return None, None, None

def get_indexer_response_times():
    result = []
    for p in Indexer.select():
        print("Limiting stats to 100 for testing only!")
        result.append({"key": p.name,
                       "values": [{"responseTime": x.response_time, "date": x.time.timestamp} for x in IndexerApiAccess().select(IndexerApiAccess.response_time, IndexerApiAccess.time).where((IndexerApiAccess.response_successful) & (IndexerApiAccess.indexer == p)).join(Indexer).limit(1)]})
    return result

def get_avg_indexer_response_times(after, before):
    result = []
    response_times = []
    for p in Indexer.select().order_by(Indexer.name):
        try:
            indexer = getIndexerByName(p.name)
            if not indexer.settings.enabled:
                logger.debug("Skipping download stats for %s" % p.name)
                continue
        except IndexerNotFoundException:
            logger.error("Unable to find indexer %s in configuration" % p.name)
            continue
        where = (IndexerApiAccess.response_successful) & (IndexerApiAccess.indexer == p) & (IndexerApiAccess.time > after) & (IndexerApiAccess.time < before)
        avg_response_time = IndexerApiAccess().select(fn.AVG(IndexerApiAccess.response_time)).where(where).tuples()[0][0]
        if avg_response_time:
            response_times.append({"name": p.name, "avgResponseTime": int(avg_response_time)})
    where = (IndexerApiAccess.response_successful) & (IndexerApiAccess.response_time is not None) & (IndexerApiAccess.time > after) & (IndexerApiAccess.time < before)
    avg_response_time = IndexerApiAccess().select(fn.AVG(IndexerApiAccess.response_time)).where(where).tuples()[0][0]
    for i in response_times:
        delta = i["avgResponseTime"] - avg_response_time
        i["delta"] = delta
        result.append(i)
    result = sorted(result, key=lambda x: x["name"])
    result = sorted(result, key=lambda x: x["avgResponseTime"])

    return result

def get_avg_indexer_search_results_share():
    results = []
    for p in Indexer.select().order_by(Indexer.name):
        result = database.db.execute_sql(
            "select (100 * (select cast(sum(ps.results) as float) from indexersearch ps where ps.search_id in (select ps.search_id from indexersearch ps where ps.indexer_id == %d) and ps.indexer_id == %d)) / (select sum(ps.results) from indexersearch ps where ps.search_id in (select ps.search_id from indexersearch ps where ps.indexer_id == %d)) as sumAllResults" % (
                p.id, p.id, p.id)).fetchone()
        results.append({"name": p.name, "avgResultsShare": result[0] if result[0] is not None else "N/A"})
    return results

def getIndexerDownloadStats():
    results = []
    allDownloadsCount = IndexerNzbDownload.select().count()
    for p in Indexer.select().order_by(Indexer.name):
        dlCount = IndexerNzbDownload().select(Indexer.name, IndexerApiAccess.response_successful).join(IndexerSearch, JOIN.LEFT_OUTER).join(Search, JOIN.LEFT_OUTER).switch(IndexerNzbDownload).join(IndexerApiAccess, JOIN.LEFT_OUTER).join(Indexer, JOIN.LEFT_OUTER).where(Indexer.id == p).count()
        results.append({"name": p.name,
                        "total": dlCount,
                        "share": 100 / (allDownloadsCount / dlCount) if allDownloadsCount > 0 and dlCount > 0 else 0})
    return results

def get_avg_indexer_search_results_share(afterSql, beforeSql):
    results = []
    for p in Indexer.select().order_by(Indexer.name):
        try:
            indexer = getIndexerByName(p.name)
            if not indexer.settings.enabled:
                logger.debug("Skipping download stats for %s" % p.name)
                continue
            if indexer.settings.name == "Womble":
                logger.debug("Skipping download stats for Womble because we exclude update queries without specific query or ID")
                continue
        except IndexerNotFoundException:
            logger.error("Unable to find indexer %s in configuration" % p.name)
            continue
        innerSelect = """(SELECT ps.search_id
                                    FROM indexersearch ps, search s
                                    WHERE ps.indexer_id == %(id)d AND ps.search_id = s.id AND ps.successful AND (s.episode NOT NULL OR s.season NOT NULL OR s.identifier_key NOT NULL OR s.query NOT NULL)) AND ps.time > %(after)s and ps.time < %(before)s""" % {"id": p.id, "after": afterSql, "before": beforeSql}

        result = database.db.execute_sql(
            """
            SELECT (100 *
            (SELECT cast(sum(ps.resultsCount) AS FLOAT)
             FROM indexersearch ps
             WHERE ps.search_id IN %s AND ps.indexer_id == %d))
           /
           (SELECT sum(ps.resultsCount)
            FROM indexersearch ps
            WHERE ps.search_id IN %s) AS sumAllResults
             """
            % (innerSelect, p.id, innerSelect)).fetchone()
        avgResultsShare = int(result[0]) if result is not None and len(result) > 0 and result[0] is not None else "N/A"

        result = database.db.execute_sql(
            """
            SELECT avg(
                CASE WHEN uniqueResults > 0
                  THEN
                    100 / (processedResults * 1.0 / uniqueResults)
                ELSE 0
                END) as avgUniqueResults
            FROM indexersearch s
            WHERE processedResults IS NOT NULL AND uniqueResults IS NOT NULL
                  AND s.indexer_id == %(id)d AND s.time > %(after)s and s.time < %(before)s
            GROUP BY indexer_id;

            """
            % {"id": p.id, "after": afterSql, "before": beforeSql}).fetchone()
        if p.name in ["NZBIndex", "Binsearch", "NZBClub"]:
            avgUniqueResults = "-"
        elif result is not None and len(result) > 0 and result[0] is not None:
            avgUniqueResults = int(result[0])
        else:
            avgUniqueResults = "N/A"
        results.append({"name": p.name, "avgResultsShare": avgResultsShare, "avgUniqueResults": avgUniqueResults})
    results = sorted(results, key=lambda x: x["name"])
    results = sorted(results, key=lambda x: 0 if x["avgResultsShare"] == "N/A" else x["avgResultsShare"], reverse=True)
    return results

    def setUp(self):
        set_and_drop()

        self.indexercom = Indexer(name="indexer.com")
        self.indexercom.save()

        self.newznab1 = Bunch()
        self.newznab1.enabled = True
        self.newznab1.name = "indexer.com"
        self.newznab1.host = "https://indexer.com"
        self.newznab1.apikey = "apikeyindexer.com"
        self.newznab1.timeout = None
        self.newznab1.score = 0
        self.newznab1.search_ids = ["imdbid", "rid", "tvdbid"]
        self.n1 = NewzNab(self.newznab1)

def get_avg_indexer_response_times():
    result = []
    response_times = []
    for p in Indexer.select().order_by(Indexer.name):

        avg_response_time = IndexerApiAccess().select(fn.AVG(IndexerApiAccess.response_time)).where((IndexerApiAccess.response_successful) & (IndexerApiAccess.indexer == p)).tuples()[0][0]
        if avg_response_time:
            response_times.append({"name": p.name, "avgResponseTime": avg_response_time})
    avg_response_time = IndexerApiAccess().select(fn.AVG(IndexerApiAccess.response_time)).where((IndexerApiAccess.response_successful) & (IndexerApiAccess.response_time is not None)).tuples()[0][0]
    for i in response_times:
        delta = i["avgResponseTime"] - avg_response_time
        i["delta"] = delta
        result.append(i)

    return result

def getIndexerBasedDownloadStats(afterSql, beforeSql):
    enabledIndexerIds = []
    for p in Indexer.select().order_by(Indexer.name):
        try:
            indexer = getIndexerByName(p.name)
            if not indexer.settings.enabled:
                logger.debug("Skipping download stats for %s because it's disabled" % p.name)
                continue
            enabledIndexerIds.append(str(p.id))
        except IndexerNotFoundException:
            logger.error("Unable to find indexer %s in configuration" % p.name)
            continue
    enabledIndexerIds = ", ".join(enabledIndexerIds)
    query = """
    SELECT
      indexer.name,
      count(*) AS total,
      CASE WHEN count(*) > 0
        THEN
          100 / (1.0 * countall.countall / count(*))
      ELSE 0
      END
               AS share
    FROM
      indexernzbdownload dl,
      (SELECT count(*) AS countall
       FROM
         indexernzbdownload dl
         LEFT OUTER JOIN indexerapiaccess api
           ON dl.apiAccess_id = api.id
       WHERE api.indexer_id IN (%(enabledIndexerIds)s)
       AND api.time > %(afterSql)s AND api.time < %(beforeSql)s
       )
      countall
      LEFT OUTER JOIN indexerapiaccess api
        ON dl.apiAccess_id = api.id
      LEFT OUTER JOIN indexer indexer
        ON api.indexer_id = indexer.id
    WHERE api.indexer_id IN (%(enabledIndexerIds)s)
    AND api.time > %(afterSql)s AND api.time < %(beforeSql)s
    GROUP BY indexer.id
    """ % {"enabledIndexerIds": enabledIndexerIds, "afterSql": afterSql, "beforeSql": beforeSql}
    stats = database.db.execute_sql(query).fetchall()
    stats = [{"name": x[0], "total": x[1], "share": x[2]} for x in stats]

    stats = sorted(stats, key=lambda x: x["name"])
    stats = sorted(stats, key=lambda x: x["share"], reverse=True)
    return stats

def get_avg_indexer_search_results_share():
    results = []
    for p in Indexer.select().order_by(Indexer.name):
        try:
            indexer = getIndexerByName(p.name)
            if not indexer.settings.enabled:
                logger.debug("Skipping download stats for %s" % p.name)
                continue
        except IndexerNotFoundException:
            logger.error("Unable to find indexer %s in configuration" % p.name)
            continue
        result = database.db.execute_sql(
                "select (100 * (select cast(sum(ps.resultsCount) as float) from indexersearch ps where ps.search_id in (select ps.search_id from indexersearch ps where ps.indexer_id == %d) and ps.indexer_id == %d)) / (select sum(ps.resultsCount) from indexersearch ps where ps.search_id in (select ps.search_id from indexersearch ps where ps.indexer_id == %d)) as sumAllResults" % (
                    p.id, p.id, p.id)).fetchone()
        results.append({"name": p.name, "avgResultsShare": result[0] if result[0] is not None else "N/A"})
    return results

def search_and_handle_db(dbsearch, indexers_and_search_requests):
    results_by_indexer = start_search_futures(indexers_and_search_requests)
    dbsearch.username = request.authorization.username if request.authorization is not None else None
    with db.atomic():
        dbsearch.save()
    for indexer, result in results_by_indexer.items():
        if result.didsearch:
            with db.atomic():
                indexersearchentry = result.indexerSearchEntry
                indexersearchentry.search = dbsearch
                indexersearchentry.save()
                result.indexerApiAccessEntry.username = request.authorization.username if request.authorization is not None else None
                try:
                    result.indexerApiAccessEntry.indexer = Indexer.get(Indexer.name == indexer)
                    result.indexerApiAccessEntry.save()
                    result.indexerStatus.save()
                except Exception:
                    logger.error("Error saving IndexerApiAccessEntry. Debug info: %s" % json.dumps(model_to_dict(result.indexerApiAccessEntry)))

    logger.debug("Returning search results now")
    return {"results": results_by_indexer, "dbsearch": dbsearch}

def getIndexerDownloadStats():
    results = []
    allDownloadsCount = IndexerNzbDownload.select().count()
    for p in Indexer.select().order_by(Indexer.name):
        try:
            indexer = getIndexerByName(p.name)
            if not indexer.settings.enabled:
                logger.debug("Skipping download stats for %s" % p.name)
                continue
        except IndexerNotFoundException:
            logger.error("Unable to find indexer %s in configuration" % p.name)
            continue
        dlCount = IndexerNzbDownload().\
            select(Indexer.name, IndexerApiAccess.response_successful). \
            join(IndexerApiAccess, JOIN.LEFT_OUTER). \
            join(Indexer, JOIN.LEFT_OUTER).\
            where(Indexer.id == p).\
            count()
        results.append({"name": p.name,
                        "total": dlCount,
                        "share": 100 / (allDownloadsCount / dlCount) if allDownloadsCount > 0 and dlCount > 0 else 0})
    return results

def get_nzb_link(indexer_name, guid, title, searchid):
    """
    Build a link that leads to the actual NZB of the indexer using the given informations. We log this as indexer API access and NZB download because this is only called
    when the NZB will be actually downloaded later (by us or a downloader) 
    :return: str
    """
    for p in indexers.enabled_indexers:
        if p.name == indexer_name:
            link = p.get_nzb_link(guid, title)

            # Log to database
            indexer = Indexer.get(fn.lower(Indexer.name) == indexer_name.lower())
            papiaccess = IndexerApiAccess(indexer=p.indexer, type="nzb", url=link, response_successful=None, indexer_search=indexer)
            papiaccess.save()
            pnzbdl = IndexerNzbDownload(indexer=indexer, indexer_search=searchid, api_access=papiaccess, mode="redirect")
            pnzbdl.save()

            return link

    else:
        logger.error("Did not find indexer with name %s" % indexer_name)
        return None