Page Menu
Home
Software Heritage
Search
Configure Global Search
Log In
Files
F8393972
046.sql
No One
Temporary
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Flag For Later
Size
7 KB
Subscribers
None
046.sql
View Options
-- SWH DB schema upgrade
-- from_version: 45
-- to_version: 46
-- description: Rename revision to target and add target_type in tables release and occurrence_history
insert
into
dbversion
(
version
,
release
,
description
)
values
(
46
,
now
(),
'Work In Progress'
);
ALTER
TABLE
occurrence_history
DROP
CONSTRAINT
if
exists
occurrence_history_pkey
;
ALTER
TABLE
occurrence
DROP
CONSTRAINT
if
exists
occurrence_pkey
;
ALTER
TABLE
occurrence_history
DROP
CONSTRAINT
if
exists
occurrence_history_origin_branch_revision_authority_validi_excl
;
DROP
INDEX
if
exists
occurrence_history_revision_idx
;
DROP
INDEX
if
exists
release_revision_idx
;
create
type
object_type
as
enum
(
'content'
,
'directory'
,
'revision'
,
'release'
);
ALTER
TABLE
occurrence_history
RENAME
COLUMN
revision
TO
target
;
ALTER
TABLE
occurrence_history
ADD
COLUMN
target_type
object_type
NOT
NULL
DEFAULT
'revision'
;
ALTER
TABLE
occurrence_history
ALTER
COLUMN
target_type
DROP
DEFAULT
;
ALTER
TABLE
occurrence
RENAME
COLUMN
revision
TO
target
;
ALTER
TABLE
occurrence
ADD
COLUMN
target_type
object_type
NOT
NULL
DEFAULT
'revision'
;
ALTER
TABLE
occurrence
ALTER
COLUMN
target_type
DROP
DEFAULT
;
ALTER
TABLE
"release"
RENAME
COLUMN
revision
TO
target
;
ALTER
TABLE
"release"
ADD
COLUMN
target_type
object_type
NOT
NULL
DEFAULT
'revision'
;
ALTER
TABLE
"release"
ALTER
COLUMN
target_type
DROP
DEFAULT
;
drop
type
release_entry
cascade
;
create
type
release_entry
as
(
id
sha1_git
,
target
sha1_git
,
target_type
object_type
,
date
timestamptz
,
date_offset
smallint
,
name
text
,
comment
bytea
,
synthetic
boolean
,
author_name
bytea
,
author_email
bytea
);
drop
type
content_occurrence
cascade
;
create
type
content_occurrence
as
(
origin_type
text
,
origin_url
text
,
branch
text
,
target
sha1_git
,
target_type
object_type
,
path
unix_path
);
CREATE
OR
REPLACE
FUNCTION
swh_content_find_occurrence
(
content_id
sha1
)
RETURNS
content_occurrence
LANGUAGE
plpgsql
AS
$$
declare
dir
content_dir
;
rev
sha1_git
;
occ
occurrence
%
ROWTYPE
;
coc
content_occurrence
;
begin
-- each step could fail if no results are found, and that's OK
select
*
from
swh_content_find_directory
(
content_id
)
-- look up directory
into
dir
;
if
not
found
then
return
null
;
end
if
;
select
id
from
revision
where
directory
=
dir
.
directory
-- look up revision
limit
1
into
rev
;
if
not
found
then
return
null
;
end
if
;
select
*
from
swh_revision_find_occurrence
(
rev
)
-- look up occurrence
into
occ
;
if
not
found
then
return
null
;
end
if
;
select
origin
.
type
,
origin
.
url
,
occ
.
branch
,
occ
.
target
,
occ
.
target_type
,
dir
.
path
from
origin
where
origin
.
id
=
occ
.
origin
into
coc
;
return
coc
;
-- might be NULL
end
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_occurrence_history_add
()
RETURNS
void
LANGUAGE
plpgsql
AS
$$
begin
-- Update intervals we have the data to update
with
new_intervals
as
(
select
t
.
origin
,
t
.
branch
,
t
.
authority
,
t
.
validity
,
o
.
validity
-
t
.
validity
as
new_validity
from
tmp_occurrence_history
t
left
join
occurrence_history
o
using
(
origin
,
branch
,
authority
)
where
o
.
origin
is
not
null
),
-- do not update intervals if they would become empty (perfect overlap)
to_update
as
(
select
*
from
new_intervals
where
not
isempty
(
new_validity
))
update
occurrence_history
o
set
validity
=
t
.
new_validity
from
to_update
t
where
o
.
origin
=
t
.
origin
and
o
.
branch
=
t
.
branch
and
o
.
authority
=
t
.
authority
;
-- Now only insert intervals that aren't already present
insert
into
occurrence_history
(
origin
,
branch
,
target
,
target_type
,
authority
,
validity
)
select
distinct
origin
,
branch
,
target
,
target_type
,
authority
,
validity
from
tmp_occurrence_history
t
where
not
exists
(
select
1
from
occurrence_history
o
where
o
.
origin
=
t
.
origin
and
o
.
branch
=
t
.
branch
and
o
.
authority
=
t
.
authority
and
o
.
target
=
t
.
target
and
o
.
target_type
=
t
.
target_type
and
o
.
validity
=
t
.
validity
);
return
;
end
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_release_add
()
RETURNS
void
LANGUAGE
plpgsql
AS
$$
begin
perform
swh_person_add_from_release
();
insert
into
release
(
id
,
target
,
target_type
,
date
,
date_offset
,
name
,
comment
,
author
,
synthetic
)
select
t
.
id
,
t
.
target
,
t
.
target_type
,
t
.
date
,
t
.
date_offset
,
t
.
name
,
t
.
comment
,
a
.
id
,
t
.
synthetic
from
tmp_release
t
left
join
person
a
on
a
.
name
=
t
.
author_name
and
a
.
email
=
t
.
author_email
;
return
;
end
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_release_get
()
RETURNS
SETOF
release_entry
LANGUAGE
plpgsql
AS
$$
begin
return
query
select
r
.
id
,
r
.
target
,
r
.
target_type
,
r
.
date
,
r
.
date_offset
,
r
.
name
,
r
.
comment
,
r
.
synthetic
,
p
.
name
as
author_name
,
p
.
email
as
author_email
from
tmp_release_get
t
inner
join
release
r
on
t
.
id
=
r
.
id
inner
join
person
p
on
p
.
id
=
r
.
author
;
return
;
end
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_release_get_by
(
origin_id
bigint
)
RETURNS
SETOF
release_entry
LANGUAGE
sql
STABLE
AS
$$
select
r
.
id
,
r
.
target
,
r
.
target_type
,
r
.
date
,
r
.
date_offset
,
r
.
name
,
r
.
comment
,
r
.
synthetic
,
a
.
name
as
author_name
,
a
.
email
as
author_email
from
release
r
inner
join
occurrence_history
occ
on
occ
.
target
=
r
.
target
left
join
person
a
on
a
.
id
=
r
.
author
where
occ
.
origin
=
origin_id
and
occ
.
target_type
=
'revision'
and
r
.
target_type
=
'revision'
;
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_revision_find_occurrence
(
revision_id
sha1_git
)
RETURNS
occurrence
LANGUAGE
sql
STABLE
AS
$$
select
origin
,
branch
,
target
,
target_type
from
swh_revision_list_children
(
ARRAY
[
revision_id
]
::
bytea
[])
as
rev_list
left
join
occurrence_history
occ_hist
on
rev_list
.
id
=
occ_hist
.
target
where
occ_hist
.
origin
is
not
null
and
occ_hist
.
target_type
=
'revision'
order
by
upper
(
occ_hist
.
validity
)
-- TODO filter by authority?
limit
1
;
$$
;
CREATE
OR
REPLACE
FUNCTION
swh_revision_get_by
(
origin_id
bigint
,
branch_name
text
=
NULL
::
text
,
validity
timestamp
with
time
zone
=
NULL
::
timestamp
with
time
zone
)
RETURNS
SETOF
revision_entry
LANGUAGE
sql
STABLE
AS
$$
select
r
.
id
,
r
.
date
,
r
.
date_offset
,
r
.
committer_date
,
r
.
committer_date_offset
,
r
.
type
,
r
.
directory
,
r
.
message
,
a
.
name
,
a
.
email
,
c
.
name
,
c
.
email
,
r
.
metadata
,
r
.
synthetic
,
array
(
select
rh
.
parent_id
::
bytea
from
revision_history
rh
where
rh
.
id
=
r
.
id
order
by
rh
.
parent_rank
)
as
parents
from
swh_occurrence_get_by
(
origin_id
,
branch_name
,
validity
)
as
occ
inner
join
revision
r
on
occ
.
target
=
r
.
id
left
join
person
a
on
a
.
id
=
r
.
author
left
join
person
c
on
c
.
id
=
r
.
committer
;
$$
;
ALTER
TABLE
occurrence_history
ADD
CONSTRAINT
occurrence_history_pkey
PRIMARY
KEY
(
object_id
);
ALTER
TABLE
occurrence
ADD
CONSTRAINT
occurrence_pkey
PRIMARY
KEY
(
origin
,
branch
);
CREATE
INDEX
occurrence_history_origin_branch_idx
ON
occurrence_history
USING
btree
(
origin
,
branch
);
CREATE
INDEX
occurrence_history_target_target_type_idx
ON
occurrence_history
USING
btree
(
target
,
target_type
);
CREATE
INDEX
release_target_target_type_idx
ON
"release"
USING
btree
(
target
,
target_type
);
File Metadata
Details
Attached
Mime Type
text/plain
Expires
Wed, Jun 4, 7:19 PM (6 d, 3 h ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3283049
Attached To
rDSTO Storage manager
Event Timeline
Log In to Comment