Differential D8760 Diff 31679 swh/storage/sql/upgrades/187.sql

Changeset View

Standalone View

View Options

swh/storage/sql/upgrades/187.sql

This file was added.

-- SWH DB schema upgrade

-- from_version: 186

-- to_version: 187

-- description: Add payloads to ExtIDs

insert into dbversion(version, release, description)

values(187, now(), 'Work In Progress');

-- Add payload_type and payload columns.

alter table extid

add column payload_type text;

comment on column extid.payload_type is 'The type of the payload object';

alter table extid

add column payload sha1_git;

comment on column extid.payload is 'Content object ID of data associated with the ExtID';

vlorentzUnsubmitted

Not Done

add column payload sha1;

- comment on column extid.payload is 'Object storage ID of data associated with the ExtID';

+ comment on column extid.payload is 'sha1_git of data associated with the ExtID';

-- Add payload constraint.

vlorentz:

vlorentzUnsubmitted

Not Done

and this

vlorentz: and this

-- Add payload constraint.

alter table extid

add constraint extid_payload_check

check ((payload_type is null) = (payload is null))

not valid;

-- Update the unique index.

create unique index concurrently extid_no_payload_idx

on extid(extid_type, extid, extid_version, target_type, target)

where payload_type is null and payload is null;

create unique index concurrently extid_payload_idx

on extid(extid_type, extid, extid_version, target_type, target, payload_type, payload)

where payload_type is not null and payload is not null;

drop index if exists extid_extid_type_extid_extid_version_target_type_target_idx;

-- Update the swh_extid_add procedure to include the new columns.

create or replace function swh_extid_add()

returns void

language plpgsql

as $$

begin

insert into extid (extid_type, extid, extid_version, target_type, target, payload_type, payload)

select distinct t.extid_type, t.extid, t.extid_version, t.target_type, t.target, t.payload_type, t.payload

from tmp_extid t

on conflict do nothing;

return;

end

$$;