Materialize the number of finished builds

[?]

Aug 12, 2013, 6:11 PM

FANTYCR7X2TYLJKGO3E5CU4PVXPSOMQZELEFQCZ6E7GEJOPYXQUAC

Dependencies

[2] AVOPQAF7 * Make the "all" page faster by not doing four identical `select
[3] PT5XCF2N Add another index to speed up the jobstatus page
[4] 32MXC7NN hydra-init: show SQL commands being executed
[5] S5NPXZG3 Fix indentation
[6] 4HPT4SDD Revert "Remove now-unused SystemTypes table"
[7] 3XM2QEGW Fix query-all-tables test count, there are more tables now
[8] AK66K4KY Fix the test
[9] JD7AWXQG Remove now-unused SystemTypes table
[10] AMFMXR52 Provide a command ‘hydra-init’ to initialise/upgrade the database
[11] YDW2NUIW Fix "make check"
[12] E7FID72S Remove the BuildMachines and BuildMachinesSystemTypes tables
[13] FPK5LF53 * Put the project-related actions in a separate controller. Put the
[14] D6EL7KR6 Fix broken test
[15] P43FHUUV there are 43 tests
[16] DES4PSRL add basic query tests for JobStatus/LatestSucceeded/ActiveJobs
[17] QL55ECJ6 - adapted ui for hydra, more in line with nixos.org website
[18] 5L272QSC Speed up the /jobset overview pages a lot
[19] 2I2ZX6JB * Make the "latest succeeded" query (used by the "latest" channel)
[20] E2TOU3L6 * More indices.
[21] 6QRHXIM3 * Speed up the jobset index page. Especially the query to get the
[22] G2ZB6464 first test, not yet in buildprocess
[23] 5SMQ2PLK Fix tests
[24] P5VJCT6U fix wrong comment
[25] U4TD3AIQ Add support for viewing jobset evaluations
[*] J5UVLXOK * Start of a basic Catalyst web interface.
[*] AKAZKCR6 * At top-level and for each project, provide two channels: "latest"
[*] QNYIOH25 * Generate a robots.txt.
[*] D5QIOJGP * Move everything up one directory.
[*] N22GPKYT * Put info about logs / build products in the DB.
[*] ZWCTAZGL added newsitems, added some admin options to clear various caches.
[*] SMCOU72F hydra: add some admin for adding/enabling/etc build machines
[*] 4MBKR4XM Gratuitous whitespace.

Change contents

edit in src/lib/Hydra/Base/Controller/ListBuilds.pm at line 58

[7.29]→[7.534:535](∅→∅),[7.534]→[7.534:535](∅→∅),[7.535]→[2.0:75](∅→∅)

my $nrBuilds = $c->stash->{allBuilds}->search({finished => 1})->count;
replacement in src/lib/Hydra/Base/Controller/ListBuilds.pm at line 63

[7.780]→[7.0:36](∅→∅)

$c->stash->{total} = $nrBuilds;

[7.780]

[7.822]

$c->stash->{total} = $c->stash->{allBuilds}->search({finished => 1})->count
unless defined $c->stash->{total};
edit in src/lib/Hydra/Controller/Root.pm at line 158

[28.823]

[29.0]

$c->stash->{total} = $c->model('DB::NrBuilds')->find('finished')->count;
file addition: NrBuilds.pm (----------)

[30.477]

use utf8;
package Hydra::Schema::NrBuilds;

# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE

=head1 NAME

Hydra::Schema::NrBuilds

=cut

use strict;
use warnings;

use base 'DBIx::Class::Core';

=head1 COMPONENTS LOADED

=over 4

=item * L<Hydra::Component::ToJSON>

=back

=cut

__PACKAGE__->load_components("+Hydra::Component::ToJSON");

=head1 TABLE: C<NrBuilds>

=cut

__PACKAGE__->table("NrBuilds");

=head1 ACCESSORS

=head2 what

data_type: 'text'
is_nullable: 0

=head2 count

data_type: 'integer'
is_nullable: 0

=cut

__PACKAGE__->add_columns(
"what",
{ data_type => "text", is_nullable => 0 },
"count",
{ data_type => "integer", is_nullable => 0 },
);

=head1 PRIMARY KEY

=over 4

=item * L</what>

=back

=cut

__PACKAGE__->set_primary_key("what");

# Created by DBIx::Class::Schema::Loader v0.07033 @ 2013-08-12 17:59:18
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CK8eJGC803nGj0wnete9xg

# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;
replacement in src/script/hydra-init at line 54

[4.395]→[4.395:413](∅→∅)

sub run {

[4.395]

[4.413]

sub run_ {
replacement in src/script/hydra-init at line 59

[4.537]→[4.537:574](∅→∅)

run($_) foreach @statements;

[4.537]

[4.574]

run_($_) foreach @statements;
edit in src/sql/hydra.sql at line 514

[32.11975]

[33.12395]

);

-- Cache of the number of finished builds.
create table NrBuilds (
what text primary key not null,
count integer not null
edit in src/sql/hydra.sql at line 522

[33.12398]

[34.4]

insert into NrBuilds(what, count) values('finished', 0);

#ifdef POSTGRESQL

create function modifyNrBuildsFinished() returns trigger as $$
begin
if ((tg_op = 'INSERT' and new.finished = 1) or
(tg_op = 'UPDATE' and old.finished = 0 and new.finished = 1)) then
update NrBuilds set count = count + 1 where what = 'finished';
elsif ((tg_op = 'DELETE' and old.finished = 1) or
(tg_op = 'UPDATE' and old.finished = 1 and new.finished = 0)) then
update NrBuilds set count = count - 1 where what = 'finished';
end if;
return null;
end;
$$ language plpgsql;

create trigger NrBuildsFinished after insert or update or delete on Builds
for each row
execute procedure modifyNrBuildsFinished();

#endif
replacement in src/sql/hydra.sql at line 567

[7.3040]→[3.0:104](∅→∅)

create index IndexBuildsOnJobsetFinishedTimestamp on Builds(project, jobset, finished, timestamp DESC);

[7.3040]

[7.704]

create index IndexBuildsOnJobsetFinishedTimestamp on Builds(project, jobset, finished, timestamp DESC); -- obsolete?
file addition: upgrade-17.sql (----------)

[30.3004]

create table NrBuilds (
what text primary key not null,
count integer not null
);

create function modifyNrBuildsFinished() returns trigger as $$
begin
if ((tg_op = 'INSERT' and new.finished = 1) or
(tg_op = 'UPDATE' and old.finished = 0 and new.finished = 1)) then
update NrBuilds set count = count + 1 where what = 'finished';
elsif ((tg_op = 'DELETE' and old.finished = 1) or
(tg_op = 'UPDATE' and old.finished = 1 and new.finished = 0)) then
update NrBuilds set count = count - 1 where what = 'finished';
end if;
return null;
end;
$$ language plpgsql;

create trigger NrBuildsFinished after insert or update or delete on Builds
for each row
execute procedure modifyNrBuildsFinished();

insert into NrBuilds(what, count) select 'finished', count(*) from Builds where finished = 1;
replacement in tests/query-all-tables.pl at line 10

[7.497]→[6.1227:1257](∅→∅)

use Test::Simple tests => 43;

[7.497]

[7.527]

use Test::Simple tests => 44;
replacement in tests/query-all-tables.pl at line 14

[5.2275]→[5.2275:2313](∅→∅)

if ($source eq "SchemaVersion") {

[5.2275]

[5.2313]

if ($source eq "SchemaVersion" || $source eq "NrBuilds") {