030.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

030.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: XXX TODO
	-- to_version: 30
	-- description: XXX TODO

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

	create type entity_id as (
	id bigint,
	uuid uuid,
	parent uuid,
	name text,
	type entity_type,
	description text,
	homepage text,
	active boolean,
	generated boolean,
	lister uuid,
	lister_metadata jsonb,
	doap jsonb,
	last_seen timestamptz,
	last_id bigint
	);

	CREATE OR REPLACE FUNCTION swh_entity_from_tmp_entity_lister() RETURNS SETOF entity_id
	LANGUAGE plpgsql
	AS $$
	begin
	return query
	select t.id, e.*
	from tmp_entity_lister t
	left join entity e
	on t.lister = e.lister AND e.lister_metadata @> t.lister_metadata;
	return;
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_entity_history_add() RETURNS void
	LANGUAGE plpgsql
	AS $$
	begin
	insert into entity_history (
	uuid, parent, name, type, description, homepage, active, generated,
	lister, lister_metadata, doap, validity
	) select * from tmp_entity_history;
	return;
	end
	$$;

	CREATE OR REPLACE FUNCTION swh_mktemp_entity_history() RETURNS void
	LANGUAGE sql
	AS $$
	create temporary table tmp_entity_history (
	like entity_history including defaults);
	alter table tmp_entity_history drop column id;
	$$;

	CREATE OR REPLACE FUNCTION swh_mktemp_entity_lister() RETURNS void
	LANGUAGE sql
	AS $$
	create temporary table tmp_entity_lister (
	id bigint,
	lister uuid,
	lister_metadata jsonb
	);
	$$;