BenchmarkAnything-Storage-Backend-SQL
view release on metacpan or search on metacpan
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
use Data::Dumper;
my $hr_default_config = {
select_cache => 0,
default_aggregation => 'min',
tables => {
unit_table => 'bench_units',
benchmark_table => 'benchs',
benchmark_value_table => 'bench_values',
subsume_type_table => 'bench_subsume_types',
benchmark_backup_value_table => 'bench_backup_values',
additional_type_table => 'bench_additional_types',
additional_value_table => 'bench_additional_values',
additional_relation_table => 'bench_additional_relations',
additional_type_relation_table => 'bench_additional_type_relations',
backup_additional_relation_table => 'bench_backup_additional_relations',
},
};
my $hr_column_ba_mapping = {
bench_value_id => 'VALUE_ID',
bench => 'NAME',
bench_value => 'VALUE',
bench_unit => 'UNIT',
created_at => 'CREATED',
};
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
$or_self->{config}{tables}{benchmark_value_table},
'bench_value_id',
);
# insert subsumed benchmark additional values
$or_self->{query}->copy_additional_values({
new_bench_value_id => $i_bench_value_id,
old_bench_value_id => $hr_atts->{rows}[0]{bench_value_id},
});
for my $hr_backup_row ( @{$hr_atts->{rows}} ) {
if ( $hr_backup_row->{bench_subsume_type_rank} == 1 ) {
if ( $hr_atts->{backup} ) {
# copy data rows to backup table
$or_self->{query}->copy_benchmark_backup_value({
new_bench_value_id => $i_bench_value_id,
old_bench_value_id => $hr_backup_row->{bench_value_id},
});
my $i_bench_backup_value_id = $or_self->{query}->last_insert_id(
$or_self->{config}{tables}{benchmark_backup_value_table},
'bench_backup_value_id',
);
$or_self->{query}->copy_benchmark_backup_additional_relations({
new_bench_value_id => $i_bench_backup_value_id,
old_bench_value_id => $hr_backup_row->{bench_value_id},
});
}
}
else {
# update bench_value_id in backup table
$or_self->{query}->update_benchmark_backup_value({
new_bench_value_id => $i_bench_value_id,
old_bench_value_id => $hr_backup_row->{bench_value_id},
});
}
# now lets remove the old rows
$or_self->{query}->delete_benchmark_additional_relations(
$hr_backup_row->{bench_value_id},
);
$or_self->{query}->delete_benchmark_value(
$hr_backup_row->{bench_value_id},
);
}
};
$or_self->{query}->finish_transaction( $@ );
return 1;
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
exclude_additionals => \@a_excluded_adds,
subsume_type_id => $hr_subsume_type->{bench_subsume_type_id},
});
require DateTime::Format::Strptime;
my $or_strp = DateTime::Format::Strptime->new( pattern => '%F %T', );
my @a_rows;
my $i_counter = 0;
my $i_sum_value = 0;
my $b_backup = ((not exists $hr_options->{backup}) || $hr_options->{backup}) ? 1 : 0;
my $s_last_key = q##;
while ( my $hr_values = $or_data_values->fetchrow_hashref() ) {
my $s_act_key = join '__',
$hr_values->{bench_id},
$or_strp->parse_datetime( $hr_values->{created_at} )->strftime( $hr_subsume_type->{datetime_strftime_pattern} ),
$hr_values->{additionals} || q##,
;
if ( $s_last_key ne $s_act_key ) {
if ( $i_counter ) {
$or_self->$fn_add_subsumed_point({
rows => \@a_rows,
VALUE => $i_sum_value / $i_counter,
backup => $b_backup,
type_id => $hr_subsume_type->{bench_subsume_type_id}
});
}
@a_rows = ();
$i_counter = 0;
$i_sum_value = 0;
$s_last_key = $s_act_key;
}
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
$i_sum_value += $hr_values->{bench_value};
push @a_rows, $hr_values;
}
if ( $i_counter ) {
$or_self->$fn_add_subsumed_point({
rows => \@a_rows,
VALUE => $i_sum_value / $i_counter,
backup => $b_backup,
type_id => $hr_subsume_type->{bench_subsume_type_id}
});
}
return 1;
}
sub _get_additional_key_id {
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
It is separated from those processing just for safety reasons until
the transactional code in there is waterproof.
The gc function can cleanup more stuff in the future.
=head3 subsume
This is a maintenance function for reducing the number of data points in the
database. Calling this function reduces the rows in the benchmark values table
by building an average value for all benchmark data points grouped by specfic
columns. By default all old grouped columns will be added to backup tables for
rebuilding the original state.
It is highly recommended to do this periodically for better search performance.
my $b_success = $or_bench->subsume({
subsume_type => 'month',
exclude_additionals => [qw/ benchmark_date /],
date_from => '2013-01-01 00:00:00',
date_to => '2014-01-01 00:00:00',
backup => 0,
});
=over 4
=item subsume_type
The subsume of benchmark data points is made by group with the following
elements:
- bench_id
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
Begin of subsume period.
=item date_to
End of subsume period.
=item exclude_additionals
Array Reference of additional values that should be excluded from grouping.
=item backup
By default all subsumed rows will be inserted to backup tables. If this
isn't desired a false value must be passed.
=back
=head3 init_search_engine( $force )
Initializes the configured search engine (Elasticsearch). If the index
already exists it does nothing, except when you set C<$force> to a
true value which deletes and re-creates the index. This is necessary
for example to apply new type mappings.
lib/BenchmarkAnything/Storage/Backend/SQL.pm view on Meta::CPAN
=item tables
Containing the names of the tables used bei B<BenchmarkAnything::Storage::Backend::SQL>
tables => {
unit_table => 'bench_units',
benchmark_table => 'benchs',
benchmark_value_table => 'bench_values',
subsume_type_table => 'bench_subsume_types',
benchmark_backup_value_table => 'bench_backup_values',
additional_type_table => 'bench_additional_types',
additional_value_table => 'bench_additional_values',
additional_relation_table => 'bench_additional_relations',
additional_type_relation_table => 'bench_additional_type_relations',
backup_additional_relation_table => 'bench_backup_additional_relations',
}
=item select_cache [optional]
In case of a true value the module cache some select results
=back
=head3 default_columns
lib/BenchmarkAnything/Storage/Backend/SQL/Query/common.pm view on Meta::CPAN
my ( $or_self, @a_vals ) = @_;
return $or_self->execute_query( "
INSERT INTO raw_bench_bundles
(raw_bench_bundle_serialized)
VALUES ( ? )
", @a_vals );
}
sub copy_benchmark_backup_value {
my ( $or_self, $hr_vals ) = @_;
for my $s_param (qw/ new_bench_value_id old_bench_value_id /) {
if (! $hr_vals->{$s_param} ) {
require Carp;
Carp::confess("missing parameter '$s_param'");
return;
}
}
return $or_self->execute_query( "
INSERT INTO $or_self->{config}{tables}{benchmark_backup_value_table}
( bench_value_id, bench_id, bench_subsume_type_id, bench_value, active, created_at )
SELECT
?, bench_id, bench_subsume_type_id, bench_value, active, created_at
FROM
$or_self->{config}{tables}{benchmark_value_table}
WHERE
bench_value_id = ?
", @{$hr_vals}{qw/ new_bench_value_id old_bench_value_id /} );
}
sub copy_benchmark_backup_additional_relations {
my ( $or_self, $hr_vals ) = @_;
for my $s_param (qw/ new_bench_value_id old_bench_value_id /) {
if (! $hr_vals->{$s_param} ) {
require Carp;
Carp::confess("missing parameter '$s_param'");
return;
}
}
return $or_self->execute_query( "
INSERT INTO $or_self->{config}{tables}{backup_additional_relation_table}
( bench_backup_value_id, bench_additional_value_id, active, created_at )
SELECT
?, bench_additional_value_id, active, created_at
FROM
$or_self->{config}{tables}{additional_relation_table}
WHERE
bench_value_id = ?
", @{$hr_vals}{qw/ new_bench_value_id old_bench_value_id /} );
}
sub update_benchmark_backup_value {
my ( $or_self, $hr_vals ) = @_;
return $or_self->execute_query( "
UPDATE $or_self->{config}{tables}{benchmark_backup_value_table}
SET bench_value_id = ?
WHERE bench_value_id = ?
", @{$hr_vals}{qw/
new_bench_value_id
old_bench_value_id
/} );
}
sub start_processing_raw_bench_bundle {
share/create-schema.SQLite view on Meta::CPAN
DROP TABLE IF EXISTS `bench_backup_additional_relations`;
DROP TABLE IF EXISTS `bench_backup_values`;
DROP TABLE IF EXISTS `bench_additional_relations`;
DROP TABLE IF EXISTS `bench_additional_type_relations`;
DROP TABLE IF EXISTS `bench_additional_values`;
DROP TABLE IF EXISTS `bench_additional_types`;
DROP TABLE IF EXISTS `bench_values`;
DROP TABLE IF EXISTS `benchs`;
DROP TABLE IF EXISTS `bench_units`;
DROP TABLE IF EXISTS `bench_subsume_types`;
DROP TABLE IF EXISTS `raw_bench_bundles`;
share/create-schema.SQLite view on Meta::CPAN
REFERENCES `bench_values` (`bench_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_additional_relations_02`
FOREIGN KEY (`bench_additional_value_id`)
REFERENCES `bench_additional_values` (`bench_additional_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ; -- COMMENT 'add additional values to benchmarks';
CREATE TABLE `bench_backup_values` (
`bench_backup_value_id` INTEGER PRIMARY KEY NOT NULL , -- COMMENT 'unique key (PK)',
`bench_value_id` int(10) NOT NULL , -- COMMENT 'FK to bench_values',
`bench_id` int(10) NOT NULL , -- COMMENT 'FK to benchs',
`bench_subsume_type_id` tinyint(3) NOT NULL , -- COMMENT 'FK to bench_subsume_types',
`bench_value` float DEFAULT NULL , -- COMMENT 'value for bench data point',
`active` tinyint(3) NOT NULL , -- COMMENT 'is entry still active (0=no,1=yes)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -- COMMENT 'creation date',
CONSTRAINT `fk_bench_backup_values_01`
FOREIGN KEY (`bench_id`)
REFERENCES `benchs` (`bench_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_values_02`
FOREIGN KEY (`bench_subsume_type_id`)
REFERENCES `bench_subsume_types` (`bench_subsume_type_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_values_03`
FOREIGN KEY (`bench_value_id`)
REFERENCES `bench_values` (`bench_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ; -- COMMENT='backup table for data points for benchmark';
CREATE TABLE `bench_backup_additional_relations` (
`bench_backup_value_id` int(10) NOT NULL , -- COMMENT 'FK to bench_backup_values',
`bench_additional_value_id` int(10) NOT NULL , -- COMMENT 'FK to bench_additional_values',
`active` tinyint(3) NOT NULL , -- COMMENT 'is entry still active (0=no,1=yes)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -- COMMENT 'creation date',
PRIMARY KEY (`bench_backup_value_id`,`bench_additional_value_id`),
CONSTRAINT `fk_bench_backup_additional_relations_01`
FOREIGN KEY (`bench_backup_value_id`)
REFERENCES `bench_backup_values` (`bench_backup_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_additional_relations_02`
FOREIGN KEY (`bench_additional_value_id`)
REFERENCES `bench_additional_values` (`bench_additional_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ; -- COMMENT='add additional values to benchmarks';
CREATE TABLE `raw_bench_bundles` (
`raw_bench_bundle_id` INTEGER PRIMARY KEY NOT NULL,
`raw_bench_bundle_serialized` BLOB NOT NULL,
`processing` tinyint(3) NOT NULL DEFAULT 0 , -- COMMENT 'is entry processed (0=no,1=yes)',
share/create-schema.mysql view on Meta::CPAN
use `testrundb`;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `bench_backup_additional_relations`;
DROP TABLE IF EXISTS `bench_backup_values`;
DROP TABLE IF EXISTS `bench_additional_relations`;
DROP TABLE IF EXISTS `bench_additional_type_relations`;
DROP TABLE IF EXISTS `bench_additional_values`;
DROP TABLE IF EXISTS `bench_additional_types`;
DROP TABLE IF EXISTS `bench_values`;
DROP TABLE IF EXISTS `benchs`;
DROP TABLE IF EXISTS `bench_units`;
DROP TABLE IF EXISTS `bench_subsume_types`;
DROP TABLE IF EXISTS `raw_bench_bundles`;
share/create-schema.mysql view on Meta::CPAN
REFERENCES `bench_values` (`bench_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_additional_relations_02`
FOREIGN KEY (`bench_additional_value_id`)
REFERENCES `bench_additional_values` (`bench_additional_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB COMMENT 'add additional values to benchmarks';
CREATE TABLE `bench_backup_values` (
`bench_backup_value_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique key (PK)',
`bench_value_id` int(10) unsigned NOT NULL COMMENT 'FK to bench_values',
`bench_id` int(10) unsigned NOT NULL COMMENT 'FK to benchs',
`bench_subsume_type_id` tinyint(3) unsigned NOT NULL COMMENT 'FK to bench_subsume_types',
`bench_value` float DEFAULT NULL COMMENT 'value for bench data point',
`active` tinyint(3) unsigned NOT NULL COMMENT 'is entry still active (0=no,1=yes)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation date',
PRIMARY KEY (`bench_backup_value_id`),
KEY `fk_bench_backup_values_01` (`bench_id`),
KEY `fk_bench_backup_values_02` (`bench_subsume_type_id`),
KEY `fk_bench_backup_values_03` (`bench_value_id`),
CONSTRAINT `fk_bench_backup_values_01`
FOREIGN KEY (`bench_id`)
REFERENCES `benchs` (`bench_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_values_02`
FOREIGN KEY (`bench_subsume_type_id`)
REFERENCES `bench_subsume_types` (`bench_subsume_type_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_values_03`
FOREIGN KEY (`bench_value_id`)
REFERENCES `bench_values` (`bench_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB COMMENT='backup table for data points for benchmark';
CREATE TABLE `bench_backup_additional_relations` (
`bench_backup_value_id` int(10) unsigned NOT NULL COMMENT 'FK to bench_backup_values',
`bench_additional_value_id` int(10) unsigned NOT NULL COMMENT 'FK to bench_additional_values',
`active` tinyint(3) unsigned NOT NULL COMMENT 'is entry still active (0=no,1=yes)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation date',
PRIMARY KEY (`bench_backup_value_id`,`bench_additional_value_id`),
KEY `fk_bench_backup_additional_relations_01` (`bench_backup_value_id`),
KEY `fk_bench_backup_additional_relations_02` (`bench_additional_value_id`),
CONSTRAINT `fk_bench_backup_additional_relations_01`
FOREIGN KEY (`bench_backup_value_id`)
REFERENCES `bench_backup_values` (`bench_backup_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_bench_backup_additional_relations_02`
FOREIGN KEY (`bench_additional_value_id`)
REFERENCES `bench_additional_values` (`bench_additional_value_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB COMMENT='add additional values to benchmarks';
CREATE TABLE `raw_bench_bundles` (
`raw_bench_bundle_id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
`raw_bench_bundle_serialized` LONGBLOB NOT NULL,
`processing` tinyint(3) NOT NULL DEFAULT 0 , -- COMMENT 'is entry processed (0=no,1=yes)',
( run in 1.468 second using v1.01-cache-2.11-cpan-49f99fa48dc )