def get_tags_by_domain(domain: str, at_time: datetime.datetime = None, limit: int = config['default_limit'], offset: int = config['default_offset']): """ Returns all taxonomies and tags of a given {domain} **GET Parameters:** * domain ... the domain name to query (required) * at_time .. reference time to look at. If empty, open tags are returned. (YYYY-MM-DDTHH:mm:ss) * limit .... how many entries should we return? * offset.... starting at {offset} **Output (JSON):** * domain name... string * domain_id ... ID of the domain * tag_id... int * tag_name ... name of the tag * taxonomy_id ... int * taxonomy_name ... name of the linked taxonomy """ parameters = { "domain": domain, "limit": limit, "offset": offset, "at_time": at_time } base_table, base_table_params = get_sql_base_table(at_time, domain=domain) parameters.update(base_table_params) SQL = """ SELECT taxonomy_id, taxonomy.name AS taxonomy_name, tag_table.tag_id, tag_name, value_id, value, start_time, measured_at, end_time FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) LEFT JOIN taxonomy_tag_val ON (tag_table.value_id = taxonomy_tag_val.id) ORDER BY domain_id, tag_table.tag_id asc LIMIT %%(limit)s OFFSET %%(offset)s""" % (base_table) rows = execute_db(SQL, parameters, dict_=True) return rows
def get_stats_taxonomies( at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset'] ): """ Returns all taxonomies with the number of domains that are associated with at least one tag from this taxonomy. A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** | parameter | description | | --------------------------- | ------------------------------------------------------------------------------- | | at_time | point in time to query | | filter | filter domains by entries in the filter table before counting | | limit | limit answer to {limit} entries | | offset | start answer at the {offset}-th entry | **Output (JSON):** JSON list of objects, ordered by count, descending. Each object has the following keys: | key | description | | --------------------------- | ------------------------------------------------------------------------------- | | taxonomy_name | name of the taxonomy | | count | number of domains labeled by at least one tag from this taxonomy | """ base_table, base_table_params = get_sql_base_table(at_time, filter) # Build the SQL statement SQL = """ SELECT COUNT(DISTINCT domain_id), taxonomy.name AS taxonomy_name FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) GROUP by taxonomy.id, taxonomy.name ORDER BY count DESC LIMIT %%(limit)s OFFSET %%(offset)s """ % (base_table) params = { 'at_time': at_time, 'limit': limit, 'offset': offset } params.update(base_table_params) rows = execute_db(SQL, params, dict_=True) return rows
def get_domains_by_version( taxonomy: str, tag: str, version: Optional[str] = Query( None, regex="^(?:[0-9]+)(?:\.[0-9]+)*$" # noqa: W605 ), operator: VersionComparisonOperatorParameter = "=", at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset']): """ Return domains by the version of the tag {tag} in taxonomy {taxonomy}. Only tags with a value that looks like a version number (e.g. 1.2.3.4) will be returned. Versioning schemes with letters are not considered. A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** | parameter | description | | ----------------- | -------------------------------------------------------------------------------- | | taxonomy | taxonomy to query from | | tag | tag to query from | | version | version to compare to. Must be in format 1.2.3.4 ... or null | | operator | operator used to compare version strings. One of =, <, <=, >, >= | | at_time | point in time to query | | filter | filter domains by entries in the filter table before counting | | limit | limit answer to {limit} entries | | offset | start answer at the {offset}-th entry | **Output (JSON):** JSON list of objects, ordered by count, descending. Each object has the following keys: | key | description | | ----------------| ------------------------------------------------------------------------------- | | domain_id | ID of the found domain | | domain_name | name of the found domain | | version | version found in the value field | | start_time | start time of the tag | | measured_at | time of last measurement of the tag | | end_time | end time of the tag | """ if version is None and operator != VersionComparisonOperatorParameter.equal: raise HTTPException( status_code=400, detail="null tags can only be searched with operator '='") parameters = { "taxonomy": taxonomy, "tag_name": tag, "version": version, "limit": limit, "offset": offset, "at_time": at_time } base_table, base_table_params = get_sql_base_table(at_time, filter) parameters.update(base_table_params) if version is None: value_clause = "(value IS NULL)" else: if operator == VersionComparisonOperatorParameter.equal: op = "=" elif operator == VersionComparisonOperatorParameter.lessthan: op = "<" elif operator == VersionComparisonOperatorParameter.lessthanequal: op = "<=" elif operator == VersionComparisonOperatorParameter.greaterthan: op = ">" elif operator == VersionComparisonOperatorParameter.greaterthanequal: op = ">=" else: raise HTTPException(status_code=400, detail="invalid operator") if op == "=": value_clause = "(value = %(version)s)" else: value_clause = ( "(value ~ '^(?:[0-9]+)(?:\.[0-9]+)*$')" # noqa: W605 " AND (string_to_array(value, '.')::bigint[] %s " "string_to_array(%%(version)s, '.')::bigint[])" % op) SQL = """ SELECT domain_id, domain_name, start_time, measured_at, end_time, value AS version FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) LEFT JOIN taxonomy_tag_val ON (tag_table.value_id = taxonomy_tag_val.id) WHERE (taxonomy.name = %%(taxonomy)s) AND (tag_name = %%(tag_name)s) AND (%s) -- value_clause ORDER BY domain_id, tag_table.tag_id ASC LIMIT %%(limit)s OFFSET %%(offset)s """ % (base_table, value_clause) rows = execute_db(SQL, parameters, dict_=True) return rows
def get_domains_by_tag(tag: str, at_time: datetime.datetime = None, filter_by_value: Optional[bool] = False, value: Optional[str] = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset']): """ Returns all domains of a given {tag} A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** * tag ... the tag name to query (required) * at_time .. reference time to look at. If empty, open tags are returned. (YYYY-MM-DDTHH:mm:ss) * limit .... how many entries should we return? * offset.... starting at {offset} **Output (JSON):** * domain name... string * domain_id ... ID of the domain * tag_id... int * tag_name ... name of the tag :type tag: str """ base_table, base_table_params = get_sql_base_table(at_time, filter) parameters = { "tag_name": tag, "limit": limit, "offset": offset, "at_time": at_time, "value": value } parameters.update(base_table_params) whereclause_list = [ "(tag_name = %(tag_name)s)", ] if filter_by_value: if value is None: whereclause_list.append("(value IS NULL)") elif isinstance(value, str): whereclause_list.append("(value = %(value)s)") else: raise ValueError("expected value to be str, got '%s'" % str(type(value))) whereclause = ' AND '.join(whereclause_list) SQL = (""" SELECT domain_id, domain_name, tag_type, value, start_time, measured_at, end_time FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) LEFT JOIN taxonomy_tag_val ON (tag_table.value_id = taxonomy_tag_val.id) WHERE (%s) -- whereclause ORDER BY domain_id, tag_table.tag_id ASC LIMIT %%(limit)s OFFSET %%(offset)s""" % (base_table, whereclause)) rows = execute_db(SQL, parameters, dict_=True) return rows
def get_domains_by_category(taxonomy: str, category: Optional[str] = '', at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset']): """ Returns all domains of a given {taxonomy} A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** * taxonomy ... the taxonomy name to query (required) * at_time .. reference time to look at. If empty, open tags are returned. (YYYY-MM-DDTHH:mm:ss) * limit .... how many entries should we return? * offset.... starting at {offset} **Output (JSON):** * domain name... string * domain_id ... ID of the domain * taxonomy_id... int * taxonomy_name ... name of the taxonomy :type taxonomy: str """ parameters = { "taxonomy_name": taxonomy, "category": category, "limit": limit, "offset": offset, "at_time": at_time } base_table, base_table_params = get_sql_base_table(at_time, filter) parameters.update(base_table_params) SQL = """SELECT domain_id, domain_name, tag_id, tag_name, start_time, measured_at, end_time FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) WHERE (taxonomy.name = %%(taxonomy_name)s) AND ( ( -- tags with a category (STRPOS(tag_name, '::') != 0) AND ( (REGEXP_REPLACE(tag_name, '^(.+)::.+$', '\\1')) = %%(category)s) ) OR ( -- tags without a category (STRPOS(tag_name, '::') = 0) AND (%%(category)s = '') ) ) ORDER BY domain_id, tag_id asc LIMIT %%(limit)s OFFSET %%(offset)s""" % base_table rows = execute_db(SQL, parameters, dict_=True) start = time.time() ret = [] cur_domain = None for row in rows: if cur_domain is None or row["domain_id"] != cur_domain["domain_id"]: if cur_domain is not None: ret.append(cur_domain) cur_domain = { "domain_id": row["domain_id"], "domain_name": row["domain_name"], "tags": [] } cur_domain["tags"].append({ "tag_id": row["tag_id"], "tag_name": row["tag_name"], "start_time": row["start_time"], "measured_at": row["measured_at"], "end_time": row["end_time"] }) if cur_domain is not None: ret.append(cur_domain) logger.debug("reshuffled results in %f s", time.time() - start) logger.debug("preparing response...") start = time.time() response = ORJSONResponse(content=ret) logger.debug("response prepared in %f s", time.time() - start) return response
def get_stats_categories( taxonomy: str, at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset'] ): """Returns the categories within a taxonomy with the number of domains that are associated with at least one tag from the category. A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** | parameter | description | | --------------------------- | ------------------------------------------------------------------------------- | | taxonomy | name of taxonomy to be looked up | | at_time | point in time to query | | filter | filter domains by entries in the filter table before counting | | limit | limit answer to {limit} entries | | offset | start answer at the {offset}-th entry | **Output (JSON):** JSON list of objects, ordered by count, descending. Each object has the following keys: | key | description | | --------------------------- | ------------------------------------------------------------------------------- | | category | name of the category | | count | number of domains labeled by at least one tag from this category | """ base_table, base_table_params = get_sql_base_table(at_time, filter) SQL = """ WITH t AS ( SELECT domain_id, REGEXP_REPLACE(tag_name, '^(.+)::.+$', '\\1') AS category FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) WHERE (taxonomy.name = %%(taxonomy)s) AND (STRPOS(tag_name, '::') != 0) UNION ALL SELECT domain_id, '' AS category FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) WHERE (taxonomy.name = %%(taxonomy)s) AND (STRPOS(tag_name, '::') = 0) ) SELECT category, COUNT(*) AS count FROM ( SELECT DISTINCT ON (domain_id, category) category FROM t ) AS subquery GROUP BY category ORDER BY count DESC LIMIT %%(limit)s OFFSET %%(offset)s""" % (base_table, base_table) params = { 'taxonomy': taxonomy, 'at_time': at_time, 'limit': limit, 'offset': offset } params.update(base_table_params) rows = execute_db(SQL, params, dict_=True) return rows
def get_stats_values( taxonomy: str, tag: str, at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset'] ): """ Returns stats on the values associated with a tag in a taxonomy. A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** | parameter | description | | --------------------------- | ------------------------------------------------------------------------------- | | taxonomy | name of taxonomy the tag to be looked up belongs to | | tag | name of the tag to be looked up | | at_time | point in time to query | | filter | filter domains by entries in the filter table before counting | | limit | limit answer to {limit} entries | | offset | start answer at the {offset}-th entry | **Output (JSON):** JSON list of objects, ordered by count, descending. Each object has the following keys: | key | description | | --------------------------- | ------------------------------------------------------------------------------- | | value | value | | count | number of domains labeled by value and tag {tag} | """ base_table, base_table_params = get_sql_base_table(at_time, filter) SQL = """ SELECT value, COUNT(domain_id) AS count FROM ( SELECT DISTINCT domain_id, value FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) JOIN taxonomy_tag_val ON (tag_table.value_id = taxonomy_tag_val.id) WHERE (taxonomy.name = %%(taxonomy)s) AND (tag_name = %%(tag)s) ) AS t GROUP BY value ORDER BY count DESC """ % base_table params = { 'taxonomy': taxonomy, 'tag': tag, 'at_time': at_time, 'limit': limit, 'offset': offset } params.update(base_table_params) rows = execute_db(SQL, params, dict_=True) return rows
def get_stats_bycategory( taxonomy: str, category: Optional[str] = None, at_time: datetime.datetime = None, filter: str = Query(None, regex=RE_FILTER), limit: int = config['default_limit'], offset: int = config['default_offset'] ): """ Returns stats on all tags that are within a taxonomy, optionally filtered by categories. A filter is a string of the form Tag=Value. The available tags and values can be fetched using the /api/v1/filters/types and /api/v1/filters/values endpoints, respectively. **GET Parameters:** | parameter | description | | ------------------ | -------------------------------------------------------------------------------------------------------------------- | | taxonomy | taxonomy to calculate stats for | | category | filter for this category. If not given all tags in taxonomy are returned. Set empty string for tags in root category | | at_time | point in time to query | | filter | filter domains by entries in the filter table before counting | | limit | limit answer to {limit} entries | | offset | start answer at the {offset}-th entry | **Output (JSON):** JSON list of objects, ordered by count, descending. Each object has the following keys: | key | description | | ------------------ | ------------------------------------------------------------------------------- | | tag_name | name of the tag | | count | number of domains labeled by this tag in taxonomy {taxonomy} | """ base_table, base_table_params = get_sql_base_table(at_time, filter) if category is not None: category_clause = """ ( ( -- tags with a category (STRPOS(tag_name, '::') != 0) AND ( (REGEXP_REPLACE(tag_name, '^(.+)::.+$', '\\1')) = %(category)s ) ) OR ( -- tags without a category (STRPOS(tag_name, '::') = 0) AND (%(category)s = '') ) ) """ else: category_clause = "TRUE" SQL = """ SELECT tag_name, COUNT(DISTINCT domain_id) AS count FROM %s AS tag_table -- base_table JOIN tags USING (tag_id) JOIN taxonomy ON (tags.taxonomy_id = taxonomy.id) WHERE (taxonomy.name = %%(taxonomy)s) AND (%s) -- category clause GROUP BY tag_id, tag_name ORDER BY count DESC """ % (base_table, category_clause) params = { 'taxonomy': taxonomy, 'category': category, 'at_time': at_time, 'limit': limit, 'offset': offset } params.update(base_table_params) rows = execute_db(SQL, params, dict_=True) return rows