-- SWH DB schema upgrade
-- from_version: 159
-- to_version: 160
-- description: Make neg_utc_offset not null

-- latest schema version
insert into dbversion(version, release, description)
      values(160, now(), 'Work Still In Progress');


update release
    set date_neg_utc_offset=false
    where date is not null and date_neg_utc_offset is null;

alter table release
    add constraint release_date_neg_utc_offset_not_null
    check (date is null or date_neg_utc_offset is not null)
    not valid;

alter table release
    validate constraint release_date_neg_utc_offset_not_null;

-- for 20000 ranges on revision id
-- author date_neg_utc_offset to false when needed
update revision set date_neg_utc_offset=false where date is not null and date_neg_utc_offset is null and '\x000346dc5d638865a00000000000000000000000' <= id and id < '\x00068db8bac710cb400000000000000000000000';
-- committer_date_neg_utc_offset to false when needed
update revision set committer_date_neg_utc_offset=false where committer_date is not null and committer_date_neg_utc_offset is null and '\x000346dc5d638865a00000000000000000000000' <= id and id < '\x00068db8bac710cb400000000000000000000000';
-- extra_headers to empty array (not null)
update revision set extra_headers = ARRAY[]::bytea[][] where extra_headers is null and '\x000346dc5d638865a00000000000000000000000' <= id and id < '\x00068db8bac710cb400000000000000000000000';

update revision set date_neg_utc_offset=false where date is not null and date_neg_utc_offset is null and '\x00068db8bac710cb400000000000000000000000' <= id and id < '\x0009d495182a9930e00000000000000000000000';
update revision set committer_date_neg_utc_offset=false where committer_date is not null and committer_date_neg_utc_offset is null and '\x00068db8bac710cb400000000000000000000000' <= id and id < '\x0009d495182a9930e00000000000000000000000';
update revision set extra_headers = ARRAY[]::bytea[][] where extra_headers is null and '\x00068db8bac710cb400000000000000000000000' <= id and id < '\x0009d495182a9930e00000000000000000000000';

...

alter table revision
    add constraint revision_date_neg_utc_offset_not_null
    check (date is null or date_neg_utc_offset is not null)
    not valid;
alter table revision
    add constraint revision_committer_date_neg_utc_offset_not_null
    check (committer_date is null or committer_date_neg_utc_offset is not null)
    not valid;

alter table revision
    validate constraint revision_date_neg_utc_offset_not_null;
alter table revision
    validate constraint revision_committer_date_neg_utc_offset_not_null;
alter table revision
    alter column extra_headers set not null;