Page MenuHomeSoftware Heritage
Files F9337018

D1325.id4233.diff
No OneTemporary
Actions

D1325.id4233.diff
View Options

diff --git a/sql/upgrades/132.sql b/sql/upgrades/132.sql
new file mode 100644
--- /dev/null
+++ b/sql/upgrades/132.sql
@@ -0,0 +1,12 @@
+-- SWH DB schema upgrade
+-- from_version: 131
+-- to_version: 132
+-- description: Use sha1 instead of bigint as FK from origin_visit to snapshot (part 2: backfill)
+
+insert into dbversion(version, release, description)
+ values(132, now(), 'Work In Progress');
+
+update origin_visit
+ set snapshot=snapshot.id
+ from snapshot
+ where snapshot.object_id=origin_visit.snapshot_id;
diff --git a/swh/storage/db.py b/swh/storage/db.py
--- a/swh/storage/db.py
+++ b/swh/storage/db.py
@@ -175,7 +175,8 @@
def snapshot_get_by_origin_visit(self, origin_id, visit_id, cur=None):
cur = self._cursor(cur)
query = """\
- SELECT swh_snapshot_get_by_origin_visit(%s, %s)
+ SELECT snapshot from origin_visit where
+ origin_visit.origin=%s and origin_visit.visit=%s;
"""
cur.execute(query, (origin_id, visit_id))
@@ -313,13 +314,13 @@
if 'snapshot' in updates:
# New 'snapshot' column
update_cols.append('snapshot=%s')
- values.append(updates['snapshot'])
+ values.append(updates.pop('snapshot'))
# Old 'snapshot_id' column
update_cols.append('snapshot_id=snapshot.object_id')
from_ = 'FROM snapshot'
where.append('snapshot.id=%s')
- where_values.append(updates.pop('snapshot'))
+ where_values.append()
assert not updates, 'Unknown fields: %r' % updates
query = """UPDATE origin_visit
SET {update_cols}
@@ -355,13 +356,12 @@
args = (origin_id, limit)
query = """\
- SELECT %s,
- (select id from snapshot where object_id = snapshot_id) as snapshot
+ SELECT %s
FROM origin_visit
WHERE origin=%%s %s
order by visit asc
limit %%s""" % (
- ', '.join(self.origin_visit_get_cols[:-1]), extra_condition
+ ', '.join(self.origin_visit_get_cols), extra_condition
)
cur.execute(query, args)
@@ -382,12 +382,10 @@
cur = self._cursor(cur)
query = """\
- SELECT %s,
- (select id from snapshot where object_id = snapshot_id)
- as snapshot
+ SELECT %s
FROM origin_visit
WHERE origin = %%s AND visit = %%s
- """ % (', '.join(self.origin_visit_get_cols[:-1]))
+ """ % (', '.join(self.origin_visit_get_cols))
cur.execute(query, (origin_id, visit_id))
r = cur.fetchall()
@@ -425,15 +423,13 @@
(tuple(allowed_statuses),)).decode()
query = """\
- SELECT %s,
- (select id from snapshot where object_id = snapshot_id)
- as snapshot
+ SELECT %s
FROM origin_visit
WHERE
- origin = %%s AND snapshot_id is not null %s
+ origin = %%s AND snapshot is not null %s
ORDER BY date DESC, visit DESC
LIMIT 1
- """ % (', '.join(self.origin_visit_get_cols[:-1]), extra_clause)
+ """ % (', '.join(self.origin_visit_get_cols), extra_clause)
cur.execute(query, (origin_id,))
r = cur.fetchone()
diff --git a/swh/storage/sql/30-swh-schema.sql b/swh/storage/sql/30-swh-schema.sql
--- a/swh/storage/sql/30-swh-schema.sql
+++ b/swh/storage/sql/30-swh-schema.sql
@@ -12,7 +12,7 @@
-- latest schema version
insert into dbversion(version, release, description)
- values(131, now(), 'Work In Progress');
+ values(132, now(), 'Work In Progress');
-- a SHA1 checksum
create domain sha1 as bytea check (length(value) = 20);

File Metadata

Mime Type
text/plain
Expires
Jul 3 2025, 7:50 AM (10 w, 4 d ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3229942

Event Timeline

About · Developer information · Legal