147.sql
No OneTemporary
Actions

Size

2 KB

Subscribers

None

147.sql
View Options

	-- 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';

	alter type origin_visit_state add value 'created' before 'ongoing';
	comment on type origin_visit_state IS 'Possible origin visit state values';

	-- 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';
	comment on column origin_visit.snapshot is '(Deprecated) Optional, possibly partial, snapshot of the origin visit.';


	-- Crawling history of software origin visits by Software Heritage. Each
	-- visit see its history change through new origin visit status updates
	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.';


	-- origin_visit_status

	create unique index origin_visit_status_pkey on origin_visit_status(origin, visit, date);
	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, date)
	do nothing;

File Metadata

Mime Type: text/plain
Expires: Sat, Jun 21, 5:41 PM (1 w, 6 d ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3299623

147.sqlNo OneTemporaryActions

147.sqlView Options

File Metadata

Event Timeline

147.sql
No OneTemporary
Actions

147.sql
View Options