Changeset View
Changeset View
Standalone View
Standalone View
sql/upgrades/147.sql
- This file was added.
-- SWH DB schema upgrade | |||||
-- from_version: 146 | |||||
-- to_version: 147 | |||||
-- description: Add origin_visit_update table and migrate origin_visit | |||||
-- to origin_visit_update | |||||
-- latest schema version | |||||
insert into dbversion(version, release, description) | |||||
values(147, now(), 'Work In Progress'); | |||||
-- schema change | |||||
-- Crawling history of software origin visits by Software Heritage. Each | |||||
-- visit see its history change through new origin visit updates | |||||
create table origin_visit_update | |||||
( | |||||
origin bigint not null, | |||||
visit bigint not null, | |||||
date timestamptz not null, | |||||
status origin_visit_status not null, | |||||
metadata jsonb, | |||||
snapshot sha1_git | |||||
); | |||||
comment on column origin_visit_update.origin is 'origin concerned by the visit update'; | |||||
comment on column origin_visit_update.visit is 'visit concerned by the visit update'; | |||||
comment on column origin_visit_update.date is 'Visit update timestamp'; | |||||
comment on column origin_visit_update.status is 'Visit update status'; | |||||
comment on column origin_visit_update.metadata is 'Origin metadata at visit update time'; | |||||
comment on column origin_visit_update.snapshot is 'Origin snapshot at visit update time'; | |||||
-- origin_visit_update | |||||
create unique index concurrently origin_visit_update_pkey on origin_visit_update(origin, visit, date); | |||||
alter table origin_visit_update add primary key using index origin_visit_update_pkey; | |||||
alter table origin_visit_update | |||||
add constraint origin_visit_update_origin_visit_fkey | |||||
foreign key (origin, visit) | |||||
references origin_visit(origin, visit) not valid; | |||||
alter table origin_visit_update validate constraint origin_visit_update_origin_visit_fkey; | |||||
-- data change | |||||
-- best approximation of the visit update date is the origin_visit's date | |||||
insert into origin_visit_update (origin, visit, date, status, metadata, snapshot) | |||||
select origin, visit, date, status, metadata, snaspshot | |||||
from origin_visit | |||||
on conflict origin_visit_update(origin, visit, date) | |||||
do nothing; |