Changeset View
Changeset View
Standalone View
Standalone View
sql/upgrades/165.sql
- This file was added.
-- SWH DB schema upgrade | |||||
-- from_version: 164 | |||||
-- to_version: 165 | |||||
-- description: add branches_name_pattern parameter to swh_snapshot_get_by_id | |||||
insert into dbversion(version, release, description) | |||||
values(165, now(), 'Work In Progress'); | |||||
create or replace function swh_snapshot_get_by_id(id sha1_git, | |||||
branches_from bytea default '', branches_count bigint default null, | |||||
target_types snapshot_target[] default NULL, | |||||
branches_name_pattern text default NULL) | |||||
returns setof snapshot_result | |||||
language sql | |||||
stable | |||||
as $$ | |||||
with filtered_snapshot_branches as ( | |||||
select swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type | |||||
from snapshot_branches | |||||
inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id | |||||
where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id) | |||||
and (target_types is null or target_type = any(target_types)) | |||||
order by name | |||||
) | |||||
select snapshot_id, name, target, target_type | |||||
from filtered_snapshot_branches | |||||
where name >= branches_from | |||||
and (branches_name_pattern is null or convert_from(name, 'utf-8') ~* branches_name_pattern) | |||||
order by name limit branches_count; | |||||
$$; |