Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/db.py
Show First 20 Lines • Show All 994 Lines • ▼ Show 20 Lines | class Db(BaseDb): | ||||
): | ): | ||||
""" | """ | ||||
Method factorizing query creation for searching and counting origins. | Method factorizing query creation for searching and counting origins. | ||||
""" | """ | ||||
cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
if count: | if count: | ||||
origin_cols = "COUNT(*)" | origin_cols = "COUNT(*)" | ||||
order_clause = "" | |||||
else: | else: | ||||
origin_cols = ",".join(self.origin_cols) | origin_cols = ",".join(self.origin_cols) | ||||
order_clause = "ORDER BY id" | |||||
if not regexp: | |||||
operator = "ILIKE" | |||||
query_params = [f"%{url_pattern}%"] | |||||
else: | |||||
operator = "~*" | |||||
query_params = [url_pattern] | |||||
query = f""" | |||||
WITH filtered_origins AS ( | |||||
SELECT * | |||||
FROM origin | |||||
WHERE url {operator} %s | |||||
{order_clause} | |||||
) | |||||
SELECT {origin_cols} | |||||
FROM filtered_origins AS o | |||||
""" | |||||
query = """SELECT %s | |||||
FROM origin o | |||||
WHERE """ | |||||
if with_visit: | if with_visit: | ||||
query += """ | query += """ | ||||
EXISTS ( | WHERE EXISTS ( | ||||
SELECT 1 | SELECT 1 | ||||
FROM origin_visit ov | FROM origin_visit ov | ||||
INNER JOIN origin_visit_status ovs | INNER JOIN origin_visit_status ovs | ||||
ON ov.origin = ovs.origin AND ov.visit = ovs.visit | ON ov.origin = ovs.origin AND ov.visit = ovs.visit | ||||
INNER JOIN snapshot ON ovs.snapshot=snapshot.id | INNER JOIN snapshot ON ovs.snapshot=snapshot.id | ||||
WHERE ov.origin=o.id | WHERE ov.origin=o.id | ||||
) | ) | ||||
AND """ | """ | ||||
query += "url %s %%s " | |||||
if not count: | |||||
query += "ORDER BY id OFFSET %%s LIMIT %%s" | |||||
if not regexp: | |||||
query = query % (origin_cols, "ILIKE") | |||||
query_params = (f"%{url_pattern}%", offset, limit) | |||||
else: | |||||
query = query % (origin_cols, "~*") | |||||
query_params = (url_pattern, offset, limit) | |||||
if count: | if not count: | ||||
query_params = (query_params[0],) | query += "OFFSET %s LIMIT %s" | ||||
query_params.extend([offset, limit]) | |||||
cur.execute(query, query_params) | cur.execute(query, query_params) | ||||
def origin_search( | def origin_search( | ||||
self, | self, | ||||
url_pattern: str, | url_pattern: str, | ||||
offset: int = 0, | offset: int = 0, | ||||
limit: int = 50, | limit: int = 50, | ||||
▲ Show 20 Lines • Show All 296 Lines • Show Last 20 Lines |