Changeset View
Changeset View
Standalone View
Standalone View
swh/provenance/sql/40-funcs.sql
| Show All 22 Lines | |||||
| as $$ | as $$ | ||||
| select C.sha1 as content, | select C.sha1 as content, | ||||
| R.sha1 as revision, | R.sha1 as revision, | ||||
| R.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| L.path as path | L.path as path | ||||
| from content as C | from content as C | ||||
| inner join content_in_revision as CR on (CR.content = C.id) | inner join content_in_revision as CR on (CR.content = C.id) | ||||
| inner join location as L on (CR.location = L.id) | inner join location as L on (L.id = CR.location) | ||||
| inner join revision as R on (CR.revision = R.id) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where C.sha1=content_id | where C.sha1 = content_id | ||||
| order by date, revision, origin, path asc limit 1 | order by date, revision, origin, path asc limit 1 | ||||
| $$; | $$; | ||||
| create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | ||||
| returns table ( | returns table ( | ||||
| content sha1_git, | content sha1_git, | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| (select C.sha1 as content, | (select C.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| l.path as path | L.path as path | ||||
| from content as c | from content as C | ||||
| inner join content_in_revision as cr on (cr.content = c.id) | inner join content_in_revision as CR on (CR.content = C.id) | ||||
| inner join location as l on (cr.location = l.id) | inner join location as L on (L.id = CR.location) | ||||
| inner join revision as r on (cr.revision = r.id) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin AS O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where c.sha1=content_id) | where C.sha1 = content_id) | ||||
| union | union | ||||
| (select c.sha1 as content, | (select C.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| case dirloc.path | case DL.path | ||||
| when '' then cntloc.path | when '' then CL.path | ||||
| when '.' then cntloc.path | when '.' then CL.path | ||||
| else (dirloc.path || '/' || cntloc.path)::unix_path | else (DL.path || '/' || CL.path)::unix_path | ||||
| end as path | end as path | ||||
| from content as c | from content as C | ||||
| inner join content_in_directory as cd on (c.id = cd.content) | inner join content_in_directory as CD on (CD.content = C.id) | ||||
| inner join directory_in_revision as dr on (cd.directory = dr.directory) | inner join directory_in_revision as DR on (DR.directory = CD.directory) | ||||
| inner join revision as r on (dr.revision = r.id) | inner join revision as R on (R.id = DR.revision) | ||||
| inner join location as cntloc on (cd.location = cntloc.id) | inner join location as CL on (CL.id = CD.location) | ||||
| inner join location as dirloc on (dr.location = dirloc.id) | inner join location as DL on (DL.id = DR.location) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where C.sha1=content_id) | where C.sha1 = content_id) | ||||
| order by date, revision, origin, path limit early_cut | order by date, revision, origin, path limit early_cut | ||||
| $$; | $$; | ||||
| \else | \else | ||||
| -- | -- | ||||
| -- without path and normalized | -- without path and normalized | ||||
| -- | -- | ||||
| create or replace function swh_provenance_content_find_first(content_id sha1_git) | create or replace function swh_provenance_content_find_first(content_id sha1_git) | ||||
| Show All 9 Lines | |||||
| as $$ | as $$ | ||||
| select C.sha1 as content, | select C.sha1 as content, | ||||
| R.sha1 as revision, | R.sha1 as revision, | ||||
| R.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from content as C | from content as C | ||||
| inner join content_in_revision as CR on (CR.content = C.id) | inner join content_in_revision as CR on (CR.content = C.id) | ||||
| inner join revision as R on (CR.revision = R.id) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where C.sha1=content_id | where C.sha1 = content_id | ||||
| order by date, revision, origin asc limit 1 | order by date, revision, origin asc limit 1 | ||||
| $$; | $$; | ||||
| create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | ||||
| returns table ( | returns table ( | ||||
| content sha1_git, | content sha1_git, | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| (select C.sha1 as content, | (select C.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from content as c | from content as C | ||||
| inner join content_in_revision as cr on (cr.content = c.id) | inner join content_in_revision as CR on (CR.content = C.id) | ||||
| inner join revision as r on (cr.revision = r.id) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where c.sha1=content_id) | where C.sha1 = content_id) | ||||
| union | union | ||||
| (select c.sha1 as content, | (select C.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from content as c | from content as C | ||||
| inner join content_in_directory as cd on (c.id = cd.content) | inner join content_in_directory as CD on (CD.content = C.id) | ||||
| inner join directory_in_revision as dr on (cd.directory = dr.directory) | inner join directory_in_revision as DR on (DR.directory = CD.directory) | ||||
| inner join revision as r on (dr.revision = r.id) | inner join revision as R on (R.id = DR.revision) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| where C.sha1=content_id) | where C.sha1 = content_id) | ||||
| order by date, revision, origin, path limit early_cut | order by date, revision, origin, path limit early_cut | ||||
| $$; | $$; | ||||
| -- :dbflavor_with_path | -- :dbflavor_with_path | ||||
| \endif | \endif | ||||
| -- :dbflavor_norm | -- :dbflavor_norm | ||||
| \else | \else | ||||
| Show All 9 Lines | returns table ( | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| select C_L.sha1 as content, | select CL.sha1 as content, | ||||
| R.sha1 as revision, | R.sha1 as revision, | ||||
| R.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| L.path as path | L.path as path | ||||
| from ( | from ( | ||||
| select C.sha1 as sha1, | select C.sha1 as sha1, | ||||
| unnest(revision) as revision, | unnest(CR.revision) as revision, | ||||
| unnest(location) as location | unnest(CR.location) as location | ||||
| from content_in_revision as C_R | from content_in_revision as CR | ||||
| inner join content as C on (C.id=C_R.content) | inner join content as C on (C.id = CR.content) | ||||
| where C.sha1=content_id | where C.sha1 = content_id | ||||
| ) as C_L | ) as CL | ||||
| inner join revision as R on (R.id=C_L.revision) | inner join revision as R on (R.id = CL.revision) | ||||
| inner join location as L on (L.id=C_L.location) | inner join location as L on (L.id = CL.location) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| order by date, revision, origin, path asc limit 1 | order by date, revision, origin, path asc limit 1 | ||||
| $$; | $$; | ||||
| create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | ||||
| returns table ( | returns table ( | ||||
| content sha1_git, | content sha1_git, | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| (with cnt as ( | (with cntrev as ( | ||||
| select c.sha1 as sha1, | select C.sha1 as sha1, | ||||
| unnest(c_r.revision) as revision, | unnest(CR.revision) as revision, | ||||
| unnest(c_r.location) as location | unnest(CR.location) as location | ||||
| from content_in_revision as c_r | from content_in_revision as CR | ||||
| inner join content as c on (c.id = c_r.content) | inner join content as C on (C.id = CR.content) | ||||
| where c.sha1 = content_id | where C.sha1 = content_id | ||||
| ) | ) | ||||
| select cnt.sha1 as content, | select CR.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| l.path as path | L.path as path | ||||
| from cnt | from cntrev as CR | ||||
| inner join revision as r on (r.id = cnt.revision) | inner join revision as R on (R.id = CR.revision) | ||||
| inner join location as l on (l.id = cnt.location) | inner join location as L on (L.id = CR.location) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| ) | ) | ||||
| union | union | ||||
| (with cnt as ( | (with cntdir as ( | ||||
| select c.sha1 as content_sha1, | select C.sha1 as sha1, | ||||
| unnest(cd.directory) as directory, | unnest(CD.directory) as directory, | ||||
| unnest(cd.location) as location | unnest(CD.location) as location | ||||
| from content as c | from content as C | ||||
| inner join content_in_directory as cd on (cd.content = c.id) | inner join content_in_directory as CD on (CD.content = C.id) | ||||
| where c.sha1 = content_id | where C.sha1 = content_id | ||||
| ), | ), | ||||
| cntdir as ( | cntrev as ( | ||||
| select cnt.content_sha1 as content_sha1, | select CD.sha1 as sha1, | ||||
| cntloc.path as file_path, | L.path as path, | ||||
| unnest(dr.revision) as revision, | unnest(DR.revision) as revision, | ||||
| unnest(dr.location) as prefix_location | unnest(DR.location) as prefix | ||||
| from cnt | from cntdir as CD | ||||
| inner join directory_in_revision as dr on (dr.directory = cnt.directory) | inner join directory_in_revision as DR on (DR.directory = CD.directory) | ||||
| inner join location as cntloc on (cntloc.id = cnt.location) | inner join location as L on (L.id = CD.location) | ||||
| ) | ) | ||||
| select cntdir.content_sha1 as content, | select CR.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| case dirloc.path | case DL.path | ||||
| when '' then cntdir.file_path | when '' then CR.path | ||||
| when '.' then cntdir.file_path | when '.' then CR.path | ||||
| else (dirloc.path || '/' || cntdir.file_path)::unix_path | else (DL.path || '/' || CR.path)::unix_path | ||||
| end as path | end as path | ||||
| from cntdir | from cntrev as CR | ||||
| inner join location as dirloc on (cntdir.prefix_location = dirloc.id) | inner join revision as R on (R.id = CR.revision) | ||||
| inner join revision as r on (cntdir.revision = r.id) | inner join location as DL on (DL.id = CR.prefix) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| ) | ) | ||||
| order by date, revision, origin, path limit early_cut | order by date, revision, origin, path limit early_cut | ||||
| $$; | $$; | ||||
| \else | \else | ||||
| -- | -- | ||||
| -- without path and denormalized | -- without path and denormalized | ||||
| -- | -- | ||||
| create or replace function swh_provenance_content_find_first(content_id sha1_git) | create or replace function swh_provenance_content_find_first(content_id sha1_git) | ||||
| returns table ( | returns table ( | ||||
| content sha1_git, | content sha1_git, | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| select C_L.sha1 as content, | select CL.sha1 as content, | ||||
| R.sha1 as revision, | R.sha1 as revision, | ||||
| R.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from ( | from ( | ||||
| select C.sha1, unnest(revision) as revision | select C.sha1, unnest(revision) as revision | ||||
| from content_in_revision as C_R | from content_in_revision as CR | ||||
| inner join content as C on (C.id=C_R.content) | inner join content as C on (C.id = CR.content) | ||||
| where C.sha1=content_id | where C.sha1=content_id | ||||
| ) as C_L | ) as CL | ||||
| inner join revision as R on (R.id=C_L.revision) | inner join revision as R on (R.id = CL.revision) | ||||
| left join origin as O on (R.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| order by date, revision, origin, path asc limit 1 | order by date, revision, origin, path asc limit 1 | ||||
| $$; | $$; | ||||
| create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int) | ||||
| returns table ( | returns table ( | ||||
| content sha1_git, | content sha1_git, | ||||
| revision sha1_git, | revision sha1_git, | ||||
| date timestamptz, | date timestamptz, | ||||
| origin unix_path, | origin unix_path, | ||||
| path unix_path | path unix_path | ||||
| ) | ) | ||||
| language sql | language sql | ||||
| stable | stable | ||||
| as $$ | as $$ | ||||
| (with cnt as ( | (with cntrev as ( | ||||
| select c.sha1 as sha1, | select C.sha1 as sha1, | ||||
| unnest(c_r.revision) as revision | unnest(CR.revision) as revision | ||||
| from content_in_revision as c_r | from content_in_revision as CR | ||||
| inner join content as c on (c.id = c_r.content) | inner join content as C on (C.id = CR.content) | ||||
| where c.sha1 = content_id | where C.sha1 = content_id | ||||
| ) | ) | ||||
| select cnt.sha1 as content, | select CR.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from cnt | from cntrev as CR | ||||
| inner join revision as r on (r.id = cnt.revision) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin as O on (r.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| ) | ) | ||||
| union | union | ||||
| (with cnt as ( | (with cntdir as ( | ||||
| select c.sha1 as content_sha1, | select C.sha1 as sha1, | ||||
| unnest(cd.directory) as directory | unnest(CD.directory) as directory | ||||
| from content as c | from content as C | ||||
| inner join content_in_directory as cd on (cd.content = c.id) | inner join content_in_directory as CD on (CD.content = C.id) | ||||
| where c.sha1 = content_id | where C.sha1 = content_id | ||||
| ), | ), | ||||
| cntdir as ( | cntrev as ( | ||||
| select cnt.content_sha1 as content_sha1, | select CD.sha1 as sha1, | ||||
| unnest(dr.revision) as revision | unnest(DR.revision) as revision | ||||
| from cnt | from cntdir as CD | ||||
| inner join directory_in_revision as dr on (dr.directory = cnt.directory) | inner join directory_in_revision as DR on (DR.directory = CD.directory) | ||||
| ) | ) | ||||
| select cntdir.content_sha1 as content, | select CR.sha1 as content, | ||||
| r.sha1 as revision, | R.sha1 as revision, | ||||
| r.date as date, | R.date as date, | ||||
| O.url as origin, | O.url as origin, | ||||
| '\x'::unix_path as path | '\x'::unix_path as path | ||||
| from cntdir | from cntrev as CR | ||||
| inner join revision as r on (cntdir.revision = r.id) | inner join revision as R on (R.id = CR.revision) | ||||
| left join origin as O on (r.origin=O.id) | left join origin as O on (O.id = R.origin) | ||||
| ) | ) | ||||
| order by date, revision, origin, path limit early_cut | order by date, revision, origin, path limit early_cut | ||||
| $$; | $$; | ||||
| \endif | \endif | ||||
| -- :dbflavor_with_path | -- :dbflavor_with_path | ||||
| \endif | \endif | ||||
| -- :dbflavor_norm | -- :dbflavor_norm | ||||