-- store the next dir to lookup the next local path from
res[i+1] := r.target;
end if;
end loop;
-- at this moment, r is the result. Patch its 'name' with the full path before returning it.
r.name := paths;
return r;
end
$$;
ALTER FUNCTION public.swh_find_directory_entry_by_path(walked_dir_id public.sha1_git, dir_or_content_path bytea[]) OWNER TO tony;
--
-- Name: swh_mktemp(regclass); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp(tblname regclass) RETURNS void
LANGUAGE plpgsql
AS $_$
begin
execute format('
create temporary table tmp_%1$I
(like %1$I including defaults)
on commit drop;
alter table tmp_%1$I drop column if exists object_id;
', tblname);
return;
end
$_$;
ALTER FUNCTION public.swh_mktemp(tblname regclass) OWNER TO tony;
--
-- Name: swh_mktemp_bytea(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_bytea() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_bytea (
id bytea
) on commit drop;
$$;
ALTER FUNCTION public.swh_mktemp_bytea() OWNER TO tony;
--
-- Name: swh_mktemp_dir_entry(regclass); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_dir_entry(tblname regclass) RETURNS void
LANGUAGE plpgsql
AS $_$
begin
execute format('
create temporary table tmp_%1$I
(like %1$I including defaults, dir_id sha1_git)
on commit drop;
alter table tmp_%1$I drop column id;
', tblname);
return;
end
$_$;
ALTER FUNCTION public.swh_mktemp_dir_entry(tblname regclass) OWNER TO tony;
--
-- Name: swh_mktemp_entity_history(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_entity_history() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_entity_history (
like entity_history including defaults) on commit drop;
alter table tmp_entity_history drop column id;
$$;
ALTER FUNCTION public.swh_mktemp_entity_history() OWNER TO tony;
--
-- Name: swh_mktemp_entity_lister(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_entity_lister() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_entity_lister (
id bigint,
lister_metadata jsonb
) on commit drop;
$$;
ALTER FUNCTION public.swh_mktemp_entity_lister() OWNER TO tony;
--
-- Name: swh_mktemp_occurrence_history(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_occurrence_history() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_occurrence_history(
like occurrence_history including defaults,
visit bigint not null
) on commit drop;
alter table tmp_occurrence_history
drop column visits,
drop column object_id;
$$;
ALTER FUNCTION public.swh_mktemp_occurrence_history() OWNER TO tony;
--
-- Name: swh_mktemp_release(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_release() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_release (
like release including defaults,
author_fullname bytea,
author_name bytea,
author_email bytea
) on commit drop;
alter table tmp_release drop column author;
alter table tmp_release drop column object_id;
$$;
ALTER FUNCTION public.swh_mktemp_release() OWNER TO tony;
--
-- Name: swh_mktemp_revision(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_revision() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_revision (
like revision including defaults,
author_fullname bytea,
author_name bytea,
author_email bytea,
committer_fullname bytea,
committer_name bytea,
committer_email bytea
) on commit drop;
alter table tmp_revision drop column author;
alter table tmp_revision drop column committer;
alter table tmp_revision drop column object_id;
$$;
ALTER FUNCTION public.swh_mktemp_revision() OWNER TO tony;
--
-- Name: swh_mktemp_snapshot_branch(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_snapshot_branch() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_snapshot_branch (
name bytea not null,
target bytea,
target_type snapshot_target
) on commit drop;
$$;
ALTER FUNCTION public.swh_mktemp_snapshot_branch() OWNER TO tony;
--
-- Name: swh_mktemp_tool(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_mktemp_tool() RETURNS void
LANGUAGE sql
AS $$
create temporary table tmp_tool (
like tool including defaults
) on commit drop;
alter table tmp_tool drop column id;
$$;
ALTER FUNCTION public.swh_mktemp_tool() OWNER TO tony;
--
-- Name: swh_object_find_by_sha1_git(); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_object_find_by_sha1_git() RETURNS SETOF public.object_found
LANGUAGE plpgsql
AS $$
begin
return query
with known_objects as ((
select id as sha1_git, 'release'::object_type as type, id, object_id from release r
where exists (select 1 from tmp_bytea t where t.id = r.id)
) union all (
select id as sha1_git, 'revision'::object_type as type, id, object_id from revision r
where exists (select 1 from tmp_bytea t where t.id = r.id)
) union all (
select id as sha1_git, 'directory'::object_type as type, id, object_id from directory d
where exists (select 1 from tmp_bytea t where t.id = d.id)
) union all (
select sha1_git as sha1_git, 'content'::object_type as type, sha1 as id, object_id from content c
where exists (select 1 from tmp_bytea t where t.id = c.sha1_git)
))
select t.id::sha1_git as sha1_git, k.type, k.id, k.object_id from tmp_bytea t
left join known_objects k on t.id = k.sha1_git;
end
$$;
ALTER FUNCTION public.swh_object_find_by_sha1_git() OWNER TO tony;
--
-- Name: occurrence; Type: TABLE; Schema: public; Owner: tony
--
CREATE TABLE public.occurrence (
origin bigint NOT NULL,
branch bytea NOT NULL,
target public.sha1_git NOT NULL,
target_type public.object_type NOT NULL
);
ALTER TABLE public.occurrence OWNER TO tony;
--
-- Name: swh_occurrence_by_origin_visit(bigint, bigint); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_occurrence_by_origin_visit(origin_id bigint, visit_id bigint) RETURNS SETOF public.occurrence
LANGUAGE sql STABLE
AS $$
select origin, branch, target, target_type from occurrence_history
where origin = origin_id and visit_id = ANY(visits);
$$;
ALTER FUNCTION public.swh_occurrence_by_origin_visit(origin_id bigint, visit_id bigint) OWNER TO tony;
--
-- Name: occurrence_history; Type: TABLE; Schema: public; Owner: tony
--
CREATE TABLE public.occurrence_history (
origin bigint NOT NULL,
branch bytea NOT NULL,
target public.sha1_git NOT NULL,
target_type public.object_type NOT NULL,
visits bigint[] NOT NULL,
object_id bigint NOT NULL,
snapshot_branch_id bigint
);
ALTER TABLE public.occurrence_history OWNER TO tony;
--
-- Name: swh_occurrence_get_by(bigint, bytea, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_occurrence_get_by(origin_id bigint, branch_name bytea DEFAULT NULL::bytea, date timestamp with time zone DEFAULT NULL::timestamp with time zone) RETURNS SETOF public.occurrence_history
LANGUAGE plpgsql
AS $$
declare
filters text[] := array[] :: text[]; -- AND-clauses used to filter content
visit_id bigint;
q text;
begin
if origin_id is null then
raise exception 'Needs an origin_id to get an occurrence.';
array(select rh.parent_id::bytea from revision_history rh where rh.id = t.id order by rh.parent_rank)
as parents, r.object_id
from tmp_bytea t
left join revision r on t.id = r.id
left join person a on a.id = r.author
left join person c on c.id = r.committer;
return;
end
$$;
ALTER FUNCTION public.swh_revision_get() OWNER TO tony;
--
-- Name: swh_revision_get_by(bigint, bytea, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: tony
--
CREATE FUNCTION public.swh_revision_get_by(origin_id bigint, branch_name bytea DEFAULT NULL::bytea, date timestamp with time zone DEFAULT NULL::timestamp with time zone) RETURNS SETOF public.revision_entry