046.sql
No OneTemporary
Actions

Size

7 KB

Subscribers

None

046.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 45
	-- to_version: 46
	-- description: Rename revision to target and add target_type in tables release and occurrence_history

	insert into dbversion(version, release, description)
	values(46, now(), 'Work In Progress');

	ALTER TABLE occurrence_history
	DROP CONSTRAINT if exists occurrence_history_pkey;

	ALTER TABLE occurrence
	DROP CONSTRAINT if exists occurrence_pkey;

	ALTER TABLE occurrence_history
	DROP CONSTRAINT if exists occurrence_history_origin_branch_revision_authority_validi_excl;

	DROP INDEX if exists occurrence_history_revision_idx;

	DROP INDEX if exists release_revision_idx;

	create type object_type as enum ('content', 'directory', 'revision', 'release');


	ALTER TABLE occurrence_history
	RENAME COLUMN revision TO target;

	ALTER TABLE occurrence_history
	ADD COLUMN target_type object_type NOT NULL DEFAULT 'revision';

	ALTER TABLE occurrence_history
	ALTER COLUMN target_type DROP DEFAULT;

	ALTER TABLE occurrence
	RENAME COLUMN revision TO target;

	ALTER TABLE occurrence
	ADD COLUMN target_type object_type NOT NULL DEFAULT 'revision';

	ALTER TABLE occurrence
	ALTER COLUMN target_type DROP DEFAULT;

	ALTER TABLE "release"
	RENAME COLUMN revision TO target;

	ALTER TABLE "release"
	ADD COLUMN target_type object_type NOT NULL DEFAULT 'revision';

	ALTER TABLE "release"
	ALTER COLUMN target_type DROP DEFAULT;

	drop type release_entry cascade;
	create type release_entry as
	(
	id sha1_git,
	target sha1_git,
	target_type object_type,
	date timestamptz,
	date_offset smallint,
	name text,
	comment bytea,
	synthetic boolean,
	author_name bytea,
	author_email bytea
	);

	drop type content_occurrence cascade;
	create type content_occurrence as (
	origin_type text,
	origin_url text,
	branch text,
	target sha1_git,
	target_type object_type,
	path unix_path
	);

	CREATE OR REPLACE FUNCTION swh_content_find_occurrence(content_id sha1) RETURNS content_occurrence
	LANGUAGE plpgsql
	AS $$
	declare
	dir content_dir;
	rev sha1_git;
	occ occurrence%ROWTYPE;
	coc content_occurrence;
	begin
	-- each step could fail if no results are found, and that's OK
	select * from swh_content_find_directory(content_id) -- look up directory
	into dir;
	if not found then return null; end if;

	select id from revision where directory = dir.directory -- look up revision
	limit 1
	into rev;
	if not found then return null; end if;

	select * from swh_revision_find_occurrence(rev) -- look up occurrence
	into occ;
	if not found then return null; end if;

	select origin.type, origin.url, occ.branch, occ.target, occ.target_type, dir.path
	from origin
	where origin.id = occ.origin
	into coc;

	return coc; -- might be NULL
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_occurrence_history_add() RETURNS void
	LANGUAGE plpgsql
	AS $$
	begin
	-- Update intervals we have the data to update
	with new_intervals as (
	select t.origin, t.branch, t.authority, t.validity,
	o.validity - t.validity as new_validity
	from tmp_occurrence_history t
	left join occurrence_history o
	using (origin, branch, authority)
	where o.origin is not null),
	-- do not update intervals if they would become empty (perfect overlap)
	to_update as (
	select * from new_intervals
	where not isempty(new_validity))
	update occurrence_history o set validity = t.new_validity
	from to_update t
	where o.origin = t.origin and o.branch = t.branch and o.authority = t.authority;

	-- Now only insert intervals that aren't already present
	insert into occurrence_history (origin, branch, target, target_type, authority, validity)
	select distinct origin, branch, target, target_type, authority, validity
	from tmp_occurrence_history t
	where not exists (
	select 1 from occurrence_history o
	where o.origin = t.origin and o.branch = t.branch and
	o.authority = t.authority and o.target = t.target and
	o.target_type = t.target_type and o.validity = t.validity);
	return;
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_release_add() RETURNS void
	LANGUAGE plpgsql
	AS $$
	begin
	perform swh_person_add_from_release();

	insert into release (id, target, target_type, date, date_offset, name, comment, author, synthetic)
	select t.id, t.target, t.target_type, t.date, t.date_offset, t.name, t.comment, a.id, t.synthetic
	from tmp_release t
	left join person a on a.name = t.author_name and a.email = t.author_email;
	return;
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_release_get() RETURNS SETOF release_entry
	LANGUAGE plpgsql
	AS $$
	begin
	return query
	select r.id, r.target, r.target_type, r.date, r.date_offset, r.name, r.comment,
	r.synthetic, p.name as author_name, p.email as author_email
	from tmp_release_get t
	inner join release r on t.id = r.id
	inner join person p on p.id = r.author;
	return;
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_release_get_by(origin_id bigint) RETURNS SETOF release_entry
	LANGUAGE sql STABLE
	AS $$
	select r.id, r.target, r.target_type, r.date, r.date_offset,
	r.name, r.comment, r.synthetic, a.name as author_name,
	a.email as author_email
	from release r
	inner join occurrence_history occ on occ.target = r.target
	left join person a on a.id = r.author
	where occ.origin = origin_id and occ.target_type = 'revision' and r.target_type = 'revision';
	$$;

	CREATE OR REPLACE FUNCTION swh_revision_find_occurrence(revision_id sha1_git) RETURNS occurrence
	LANGUAGE sql STABLE
	AS $$
	select origin, branch, target, target_type
	from swh_revision_list_children(ARRAY[revision_id] :: bytea[]) as rev_list
	left join occurrence_history occ_hist
	on rev_list.id = occ_hist.target
	where occ_hist.origin is not null and
	occ_hist.target_type = 'revision'
	order by upper(occ_hist.validity) -- TODO filter by authority?
	limit 1;
	$$;

	CREATE OR REPLACE FUNCTION swh_revision_get_by(origin_id bigint, branch_name text = NULL::text, validity timestamp with time zone = NULL::timestamp with time zone) RETURNS SETOF revision_entry
	LANGUAGE sql STABLE
	AS $$
	select r.id, r.date, r.date_offset,
	r.committer_date, r.committer_date_offset,
	r.type, r.directory, r.message,
	a.name, a.email, c.name, c.email, r.metadata, r.synthetic,
	array(select rh.parent_id::bytea
	from revision_history rh
	where rh.id = r.id
	order by rh.parent_rank
	) as parents
	from swh_occurrence_get_by(origin_id, branch_name, validity) as occ
	inner join revision r on occ.target = r.id
	left join person a on a.id = r.author
	left join person c on c.id = r.committer;
	$$;

	ALTER TABLE occurrence_history
	ADD CONSTRAINT occurrence_history_pkey PRIMARY KEY (object_id);

	ALTER TABLE occurrence
	ADD CONSTRAINT occurrence_pkey PRIMARY KEY (origin, branch);

	CREATE INDEX occurrence_history_origin_branch_idx ON occurrence_history USING btree (origin, branch);

	CREATE INDEX occurrence_history_target_target_type_idx ON occurrence_history USING btree (target, target_type);

	CREATE INDEX release_target_target_type_idx ON "release" USING btree (target, target_type);

File Metadata

Mime Type: text/plain
Expires: Wed, Jun 4, 7:19 PM (5 d, 7 h ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3283049

046.sqlNo OneTemporaryActions

046.sqlView Options

File Metadata

Event Timeline

046.sql
No OneTemporary
Actions

046.sql
View Options