Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/sql/40-swh-func.sql
| Show First 20 Lines • Show All 931 Lines • ▼ Show 20 Lines | create type origin_metadata_signature as ( | ||||
| tool_id bigint, | tool_id bigint, | ||||
| metadata jsonb, | metadata jsonb, | ||||
| provider_id integer, | provider_id integer, | ||||
| provider_name text, | provider_name text, | ||||
| provider_type text, | provider_type text, | ||||
| provider_url text | provider_url text | ||||
| ); | ); | ||||
| create or replace function swh_origin_metadata_get_by_origin( | create or replace function swh_origin_metadata_get_by_origin( | ||||
| origin integer) | origin text) | ||||
| returns setof origin_metadata_signature | returns setof origin_metadata_signature | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| select om.id as id, origin_id, discovery_date, tool_id, om.metadata, | select om.url as url, origin_id, discovery_date, tool_id, om.metadata, | ||||
| mp.id as provider_id, provider_name, provider_type, provider_url | mp.id as provider_id, provider_name, provider_type, provider_url | ||||
| from origin_metadata as om | from origin_metadata as om | ||||
| inner join metadata_provider mp on om.provider_id = mp.id | inner join metadata_provider mp on om.provider_id = mp.id | ||||
| where om.origin_id = origin | inner join origin o on o.id = om.origin_id | ||||
| where o.url = origin | |||||
| order by discovery_date desc; | order by discovery_date desc; | ||||
| $$; | $$; | ||||
| create or replace function swh_origin_metadata_get_by_provider_type( | create or replace function swh_origin_metadata_get_by_provider_type( | ||||
| origin integer, | origin text, | ||||
| type text) | type text) | ||||
| returns setof origin_metadata_signature | returns setof origin_metadata_signature | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| select om.id as id, origin_id, discovery_date, tool_id, om.metadata, | select om.id as id, origin_id, discovery_date, tool_id, om.metadata, | ||||
| mp.id as provider_id, provider_name, provider_type, provider_url | mp.id as provider_id, provider_name, provider_type, provider_url | ||||
| from origin_metadata as om | from origin_metadata as om | ||||
| inner join metadata_provider mp on om.provider_id = mp.id | inner join metadata_provider mp on om.provider_id = mp.id | ||||
| where om.origin_id = origin | inner join origin o on o.id = om.origin_id | ||||
| where o.url = origin | |||||
| and mp.provider_type = type | and mp.provider_type = type | ||||
| order by discovery_date desc; | order by discovery_date desc; | ||||
| $$; | $$; | ||||
| -- end origin_metadata functions | -- end origin_metadata functions | ||||
| -- add tmp_tool entries to tool, | -- add tmp_tool entries to tool, | ||||
| -- skipping duplicates if any. | -- skipping duplicates if any. | ||||
| -- | -- | ||||
| ▲ Show 20 Lines • Show All 148 Lines • Show Last 20 Lines | |||||