Differential D2938 Diff 10933 sql/upgrades/147.sql

Changeset 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_status table
				-- 1. Rename enum origin_visit_status to origin_visit_state
				-- 2. Add new origin_visit_status table
				-- 3. Migrate origin_visit data to new origin_visit_status data

				-- latest schema version
				insert into dbversion(version, release, description)
				values(147, now(), 'Work In Progress');

				-- schema change

				-- Rename old enum
				alter type origin_visit_status rename to origin_visit_state;
				comment on type origin_visit_state IS 'Possible visit status';

				-- Update origin visit comment on deprecated columns
				comment on column origin_visit.status is '(Deprecated) Visit status';
				comment on column origin_visit.metadata is '(Deprecated) Optional origin visit metadata';
				vlorentzUnsubmitted Not Done Inline Actions why about `alter type origin_visit_status rename to origin_visit_state`? vlorentz: why about `alter type origin_visit_status rename to origin_visit_state`?
				ardumontAuthorUnsubmitted Done Inline Actions Perfect, that works (tested on staging) and this simplify the migration below. ardumont: Perfect, that works (tested on staging) and this simplify the migration below.
				comment on column origin_visit.snapshot is '(Deprecated) Optional snapshot of the origin visit. It can be partial.';


				-- Crawling history of software origin visits by Software Heritage. Each
				-- visit see its history change through new origin visit status updates
				vlorentzUnsubmitted Not Done Inline Actions is this solved? did you check it works in staging/docker? vlorentz: is this solved? did you check it works in staging/docker?
				ardumontAuthorUnsubmitted Done Inline Actions I checked my test with a transaction in staging indeed. But no it's not really satisfying yet. 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.
				create table origin_visit_status
				(
				origin bigint not null,
				visit bigint not null,
				date timestamptz not null,
				status origin_visit_state not null,
				metadata jsonb,
				snapshot sha1_git
				);

				comment on column origin_visit_status.origin is 'Origin concerned by the visit update';
				comment on column origin_visit_status.visit is 'Visit concerned by the visit update';
				comment on column origin_visit_status.date is 'Visit update timestamp';
				comment on column origin_visit_status.status is 'Visit status (ongoing, failed, full)';
				comment on column origin_visit_status.metadata is 'Optional origin visit metadata';
				comment on column origin_visit_status.snapshot is 'Optional, possibly partial, snapshot of the origin visit. It can be partial.';


				-- origin_visit_status
				vlorentzUnsubmitted Not Done Inline Actions not a very helpful comment... vlorentz: not a very helpful comment...

				vlorentzUnsubmitted Not Done Inline Actions isn't the metadata about the visit, instead of the origin? vlorentz: isn't the metadata about the visit, instead of the origin?
				ardumontAuthorUnsubmitted Done Inline Actions yes. ardumont: yes.
				create unique index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date);
				vlorentzUnsubmitted Not Done Inline Actions `possibly partial` vlorentz: `possibly partial`
				alter table origin_visit_status add primary key using index origin_visit_status_pkey;

				alter table origin_visit_status
				add constraint origin_visit_status_origin_visit_fkey
				foreign key (origin, visit)
				references origin_visit(origin, visit) not valid;
				alter table origin_visit_status validate constraint origin_visit_status_origin_visit_fkey;


				-- data change

				-- best approximation of the visit update date is the origin_visit's date
				insert into origin_visit_status (origin, visit, date, status, metadata, snapshot)
				select origin, visit, date, status, metadata, snapshot
				from origin_visit
				on conflict origin_visit_status(origin, visit, date)
				do nothing;