grahamc/hydra - Change FZR7ET5DMHNNGB24QVFVVTGT3F6OKGG4PX7HW2YLJPBAUDVNJJHQC

Jobsets: add a SERIAL, unique, non-null id column

A postgresql column which is non-null and unique is treated with the same optimisations as a primary key, so we have no need to try and recreate the id as the primary key.

No read paths are impacted by this change, and the database will automatically create an ID for each insert. Thus, no code needs to change.

Created by Graham Christensen on February 5, 2020

FZR7ET5DMHNNGB24QVFVVTGT3F6OKGG4PX7HW2YLJPBAUDVNJJHQC

Dependencies

In channels

upstream

main

Change contents

Insertion in src/lib/Hydra/Schema/Jobsets.pm at line 42 [4.1960]
[3.18015]
[3.18029]
```
=head2 id
```

Insertion in src/lib/Hydra/Schema/Jobsets.pm at line 45 [4.1960]

[3.18030]

  data_type: 'integer'
  is_auto_increment: 1
  is_nullable: 0
  sequence: 'jobsets_id_seq'

Insertion in src/lib/Hydra/Schema/Jobsets.pm at line 163 [4.1960]

[5.5577]

[4.2219]

  "id",
  {
    data_type         => "integer",
    is_auto_increment => 1,
    is_nullable       => 0,
    sequence          => "jobsets_id_seq",
  },

Insertion in src/lib/Hydra/Schema/Jobsets.pm at line 225 [4.1960]

[6.21678]

[7.1044]


=head1 UNIQUE CONSTRAINTS
=head2 C<jobsets_id_unique>
=over 4
=item * L</id>
=back
=cut
__PACKAGE__->add_unique_constraint("jobsets_id_unique", ["id"]);

Replacement in src/lib/Hydra/Schema/Jobsets.pm at line 381 [4.1960]

B:BD[6.22628] → [2.643:785]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-02-09 15:21:11
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:FVP1/AWjdKTlY6djrG592A

[6.22628]

[8.217]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-02-09 15:25:17
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:1CMOaLf9fYRdJBlYiivmxA
__PACKAGE__->add_column(
    "+id" => { retrieve_on_insert => 1 }
);

Insertion in src/sql/hydra.sql at line 55 [10.1]
[9.4982]
[9.4982]
```
    id            serial not null,
```

Replacement in src/sql/hydra.sql at line 80 [10.1]

B:BD[9.5514] → [9.5514:5604]

    foreign key   (project) references Projects(name) on delete cascade on update cascade

[9.5514]

[9.5604]

    foreign key   (project) references Projects(name) on delete cascade on update cascade,
    constraint    Jobsets_id_unique UNIQUE(id)

File addition: upgrade-59.sql (----------)

[11.3004]

-- will automatically add unique IDs to Jobsets.
ALTER TABLE Jobsets
  ADD COLUMN id SERIAL NOT NULL,
  ADD CONSTRAINT Jobsets_id_unique UNIQUE (id);