Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/db.py
Show First 20 Lines • Show All 572 Lines • ▼ Show 20 Lines | ) -> Optional[Dict[str, Any]]: | ||||
query_parts.append("ORDER BY ovs.date DESC LIMIT 1") | query_parts.append("ORDER BY ovs.date DESC LIMIT 1") | ||||
query = "\n".join(query_parts) | query = "\n".join(query_parts) | ||||
cur.execute(query, tuple(query_params)) | cur.execute(query, tuple(query_params)) | ||||
row = cur.fetchone() | row = cur.fetchone() | ||||
return self._make_origin_visit_status(row) | return self._make_origin_visit_status(row) | ||||
def origin_visit_get_all(self, origin_id, last_visit=None, limit=None, cur=None): | def origin_visit_get_all( | ||||
self, origin_id, last_visit=None, order="asc", limit=None, cur=None | |||||
): | |||||
"""Retrieve all visits for origin with id origin_id. | """Retrieve all visits for origin with id origin_id. | ||||
Args: | Args: | ||||
origin_id: The occurrence's origin | origin_id: The occurrence's origin | ||||
Yields: | Yields: | ||||
The visits for that origin | The visits for that origin | ||||
""" | """ | ||||
cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
assert order.lower() in ["asc", "desc"] | |||||
query_parts = [ | |||||
"SELECT DISTINCT ON (ov.visit) %s " | |||||
% ", ".join(self.origin_visit_select_cols), | |||||
"FROM origin_visit ov", | |||||
"INNER JOIN origin o ON o.id = ov.origin", | |||||
"INNER JOIN origin_visit_status ovs", | |||||
"ON ov.origin = ovs.origin AND ov.visit = ovs.visit", | |||||
] | |||||
query_parts.append("WHERE o.url = %s") | |||||
query_params: List[Any] = [origin_id] | |||||
if last_visit: | if last_visit: | ||||
extra_condition = "and ov.visit > %s" | query_parts.append("and ov.visit > %s") | ||||
args = (origin_id, last_visit, limit) | query_params.append(last_visit) | ||||
else: | |||||
extra_condition = "" | |||||
args = (origin_id, limit) | |||||
query = """\ | query_parts.append("ORDER BY ov.visit " + order + ", ovs.date DESC") | ||||
SELECT DISTINCT ON (ov.visit) %s | |||||
FROM origin_visit ov | |||||
INNER JOIN origin o ON o.id = ov.origin | |||||
INNER JOIN origin_visit_status ovs | |||||
ON ov.origin = ovs.origin AND ov.visit = ovs.visit | |||||
WHERE o.url=%%s %s | |||||
ORDER BY ov.visit ASC, ovs.date DESC | |||||
LIMIT %%s""" % ( | |||||
", ".join(self.origin_visit_select_cols), | |||||
extra_condition, | |||||
) | |||||
cur.execute(query, args) | if limit is not None: | ||||
query_parts.append("LIMIT %s") | |||||
query_params.append(limit) | |||||
query = "\n".join(query_parts) | |||||
cur.execute(query, tuple(query_params)) | |||||
yield from cur | yield from cur | ||||
def origin_visit_get(self, origin_id, visit_id, cur=None): | def origin_visit_get(self, origin_id, visit_id, cur=None): | ||||
"""Retrieve information on visit visit_id of origin origin_id. | """Retrieve information on visit visit_id of origin origin_id. | ||||
Args: | Args: | ||||
origin_id: the origin concerned | origin_id: the origin concerned | ||||
visit_id: The visit step for that origin | visit_id: The visit step for that origin | ||||
▲ Show 20 Lines • Show All 634 Lines • Show Last 20 Lines |