Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/db.py
Show First 20 Lines • Show All 475 Lines • ▼ Show 20 Lines | ) -> None: | ||||
f"VALUES ((select id from origin_id), " | f"VALUES ((select id from origin_id), " | ||||
f"{', '.join(['%s']*len(cols))}, %s) " | f"{', '.join(['%s']*len(cols))}, %s) " | ||||
f"ON CONFLICT (origin, visit, date) do nothing", | f"ON CONFLICT (origin, visit, date) do nothing", | ||||
[visit_status.origin] | [visit_status.origin] | ||||
+ [getattr(visit_status, key) for key in cols] | + [getattr(visit_status, key) for key in cols] | ||||
+ [jsonize(visit_status.metadata)], | + [jsonize(visit_status.metadata)], | ||||
) | ) | ||||
origin_visit_cols = ["origin", "visit", "date", "type"] | |||||
def origin_visit_add_with_id(self, origin_visit: OriginVisit, cur=None) -> None: | def origin_visit_add_with_id(self, origin_visit: OriginVisit, cur=None) -> None: | ||||
"""Insert origin visit when id are already set | """Insert origin visit when id are already set | ||||
""" | """ | ||||
ov = origin_visit | ov = origin_visit | ||||
assert ov.visit is not None | assert ov.visit is not None | ||||
cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
origin_visit_cols = ["origin", "visit", "date", "type"] | |||||
query = """INSERT INTO origin_visit ({cols}) | query = """INSERT INTO origin_visit ({cols}) | ||||
VALUES ((select id from origin where url=%s), {values}) | VALUES ((select id from origin where url=%s), {values}) | ||||
ON CONFLICT (origin, visit) DO NOTHING""".format( | ON CONFLICT (origin, visit) DO NOTHING""".format( | ||||
cols=", ".join(origin_visit_cols), | cols=", ".join(self.origin_visit_cols), | ||||
values=", ".join("%s" for col in origin_visit_cols[1:]), | values=", ".join("%s" for col in self.origin_visit_cols[1:]), | ||||
) | ) | ||||
cur.execute(query, (ov.origin, ov.visit, ov.date, ov.type)) | cur.execute(query, (ov.origin, ov.visit, ov.date, ov.type)) | ||||
origin_visit_get_cols = [ | origin_visit_get_cols = [ | ||||
"origin", | "origin", | ||||
"visit", | "visit", | ||||
"date", | "date", | ||||
"type", | "type", | ||||
▲ Show 20 Lines • Show All 62 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( | def origin_visit_get_all( | ||||
ardumont: I kept this because I'm gonna do another paginated endpoint for `origin_visit_status_get`… | |||||
self, origin_id, last_visit=None, order="asc", limit=None, cur=None | 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: | ||||
Show All 29 Lines | ): | ||||
if limit is not None: | if limit is not None: | ||||
query_parts.append("LIMIT %s") | query_parts.append("LIMIT %s") | ||||
query_params.append(limit) | query_params.append(limit) | ||||
query = "\n".join(query_parts) | query = "\n".join(query_parts) | ||||
cur.execute(query, tuple(query_params)) | cur.execute(query, tuple(query_params)) | ||||
yield from cur | yield from cur | ||||
def origin_visit_get_range( | |||||
self, origin: str, visit_from: int, order: str, limit: int, cur=None, | |||||
): | |||||
assert order in ["asc", "desc"] | |||||
cur = self._cursor(cur) | |||||
Done Inline Actionsdefault arguments aren't needed in db.py. vlorentz: default arguments aren't needed in `db.py`. | |||||
Done Inline ActionsYes, thanks. I'll stop asking myself that question in the future. ardumont: Yes, thanks. I'll stop asking myself that question in the future. | |||||
origin_visit_cols = ["o.url as origin", "ov.visit", "ov.date", "ov.type"] | |||||
query_parts = [ | |||||
Done Inline ActionsRemove .lower() from this assertion; the code below assumes it's already lowered. vlorentz: Remove `.lower()` from this assertion; the code below assumes it's already lowered. | |||||
Done Inline Actionsok ardumont: ok | |||||
f"SELECT {', '.join(origin_visit_cols)} FROM origin_visit ov ", | |||||
"INNER JOIN origin o ON o.id = ov.origin ", | |||||
] | |||||
query_parts.append("WHERE o.url = %s") | |||||
query_params: List[Any] = [origin] | |||||
Done Inline Actionsconcat vlorentz: concat | |||||
Done Inline Actionsdamn black annoys me sometimes... ardumont: damn black annoys me sometimes... | |||||
if visit_from > 0: | |||||
op_comparison = ">" if order == "asc" else "<" | |||||
query_parts.append(f"and ov.visit {op_comparison} %s") | |||||
query_params.append(visit_from) | |||||
if order == "asc": | |||||
query_parts.append("ORDER BY ov.visit ASC") | |||||
elif order == "desc": | |||||
query_parts.append("ORDER BY ov.visit DESC") | |||||
query_parts.append("LIMIT %s") | |||||
query_params.append(limit) | |||||
query = "\n".join(query_parts) | |||||
cur.execute(query, tuple(query_params)) | |||||
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 | ||||
Returns: | Returns: | ||||
▲ Show 20 Lines • Show All 674 Lines • Show Last 20 Lines |
I kept this because I'm gonna do another paginated endpoint for origin_visit_status_get similar to origin_visit_get. In my mind, it makes sense that endpoint will use this (it will get renamed heh).