Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/postgresql/db.py
| Show First 20 Lines • Show All 650 Lines • ▼ Show 20 Lines | def origin_visit_get(self, origin_id, visit_id, cur=None): | ||||
| """ | """ | ||||
| cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
| query = """\ | query = """\ | ||||
| SELECT %s | SELECT %s | ||||
| FROM origin_visit ov | FROM origin_visit ov | ||||
| INNER JOIN origin o ON o.id = ov.origin | INNER JOIN origin o ON o.id = ov.origin | ||||
| INNER JOIN origin_visit_status ovs | INNER JOIN origin_visit_status ovs USING (origin, visit) | ||||
| ON ov.origin = ovs.origin AND ov.visit = ovs.visit | |||||
| WHERE o.url = %%s AND ov.visit = %%s | WHERE o.url = %%s AND ov.visit = %%s | ||||
| ORDER BY ovs.date DESC | ORDER BY ovs.date DESC | ||||
| LIMIT 1 | LIMIT 1 | ||||
| """ % ( | """ % ( | ||||
| ", ".join(self.origin_visit_select_cols) | ", ".join(self.origin_visit_select_cols) | ||||
| ) | ) | ||||
| cur.execute(query, (origin_id, visit_id)) | cur.execute(query, (origin_id, visit_id)) | ||||
| ▲ Show 20 Lines • Show All 45 Lines • ▼ Show 20 Lines | ): | ||||
| The origin_visit information, or None if no visit matches. | The origin_visit information, or None if no visit matches. | ||||
| """ | """ | ||||
| cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
| query_parts = [ | query_parts = [ | ||||
| "SELECT %s" % ", ".join(self.origin_visit_select_cols), | "SELECT %s" % ", ".join(self.origin_visit_select_cols), | ||||
| "FROM origin_visit ov ", | "FROM origin_visit ov ", | ||||
| "INNER JOIN origin o ON o.id = ov.origin", | "INNER JOIN origin o ON o.id = ov.origin", | ||||
| "INNER JOIN origin_visit_status ovs ", | "INNER JOIN origin_visit_status ovs USING (origin, visit)", | ||||
| "ON o.id = ovs.origin AND ov.visit = ovs.visit ", | |||||
| ] | ] | ||||
| query_parts.append("WHERE o.url = %s") | query_parts.append("WHERE o.url = %s") | ||||
| query_params: List[Any] = [origin_id] | query_params: List[Any] = [origin_id] | ||||
| if type is not None: | if type is not None: | ||||
| query_parts.append("AND ov.type = %s") | query_parts.append("AND ov.type = %s") | ||||
| query_params.append(type) | query_params.append(type) | ||||
| Show All 21 Lines | def origin_visit_get_random(self, type, cur=None): | ||||
| months | months | ||||
| """ | """ | ||||
| cur = self._cursor(cur) | cur = self._cursor(cur) | ||||
| columns = ",".join(self.origin_visit_select_cols) | columns = ",".join(self.origin_visit_select_cols) | ||||
| query = f"""select {columns} | query = f"""select {columns} | ||||
| from origin_visit ov | from origin_visit ov | ||||
| inner join origin o on ov.origin=o.id | inner join origin o on ov.origin=o.id | ||||
| inner join origin_visit_status ovs | inner join origin_visit_status ovs using (origin, visit) | ||||
| on ov.origin = ovs.origin and ov.visit = ovs.visit | |||||
| where ovs.status='full' | where ovs.status='full' | ||||
| and ov.type=%s | and ov.type=%s | ||||
| and ov.date > now() - '3 months'::interval | and ov.date > now() - '3 months'::interval | ||||
| and random() < 0.1 | and random() < 0.1 | ||||
| limit 1 | limit 1 | ||||
| """ | """ | ||||
| cur.execute(query, (type,)) | cur.execute(query, (type,)) | ||||
| return cur.fetchone() | return cur.fetchone() | ||||
| ▲ Show 20 Lines • Show All 246 Lines • ▼ Show 20 Lines | ): | ||||
| FROM filtered_origins AS o | FROM filtered_origins AS o | ||||
| """ | """ | ||||
| if with_visit: | if with_visit: | ||||
| query += """ | query += """ | ||||
| WHERE 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 USING (origin, 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 | ||||
| ) | ) | ||||
| """ | """ | ||||
| if not count: | if not count: | ||||
| query += "OFFSET %s LIMIT %s" | query += "OFFSET %s LIMIT %s" | ||||
| query_params.extend([offset, limit]) | query_params.extend([offset, limit]) | ||||
| ▲ Show 20 Lines • Show All 329 Lines • Show Last 20 Lines | |||||