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, | |||||
vlorentz: why about `alter type origin_visit_status rename to origin_visit_state`? | |||||
Done Inline ActionsPerfect, that works (tested on staging) and this simplify the migration below. ardumont: Perfect, that works (tested on staging) and this simplify the migration below. | |||||
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'; | |||||
Not Done Inline Actionsis this solved? did you check it works in staging/docker? vlorentz: is this solved?
did you check it works in staging/docker? | |||||
Done Inline ActionsI checked my test with a transaction in staging indeed. I'll check with your alter statement proposal which sounds nice. ardumont: I checked my test with a transaction in staging indeed.
But no it's not really satisfying yet. | |||||
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 | |||||
Not Done Inline Actionsnot a very helpful comment... vlorentz: not a very helpful comment... | |||||
-- best approximation of the visit update date is the origin_visit's date | |||||
Not Done Inline Actionsisn't the metadata about the visit, instead of the origin? vlorentz: isn't the metadata about the visit, instead of the origin? | |||||
Done Inline Actionsyes. ardumont: yes. | |||||
insert into origin_visit_update (origin, visit, date, status, metadata, snapshot) | |||||
Not Done Inline Actionspossibly partial vlorentz: `possibly partial` | |||||
select origin, visit, date, status, metadata, snaspshot | |||||
from origin_visit | |||||
on conflict origin_visit_update(origin, visit, date) | |||||
do nothing; |
why about alter type origin_visit_status rename to origin_visit_state?