Mac::Apps::Seasonality::LoadICAOHistory - Load data into an SQLite2 database

Mac-Apps-Seasonality-LoadICAOHistory documentation Contained in the Mac-Apps-Seasonality-LoadICAOHistory distribution.

Index

Code Index:

NAME

Top

Mac::Apps::Seasonality::LoadICAOHistory -- Load data into an SQLite2 database with the Seasonality weather.db schema.

VERSION

Top

This document describes Mac::Apps::Seasonality::LoadICAOHistory version 0.0.6.

SYNOPSIS

Top

    use English qw{ -no_match_vars };
    use DBI;
    use Mac::Apps::Seasonality::LoadICAOHistory qw{
        :conversion
        &clean_icao_history_set
        &load_icao_history
    };
    use Mac::Apps::Seasonality::Exceptions;

    my $celsius = convert_from_fahrenheit_to_celsius( 32.0 );
    my $hectopascals = convert_from_inches_of_mercury_to_hectopascals( 31.32);
    my $knots = convert_from_miles_per_hour_to_knots( 5.5 );

    my $data =
        [
            [
                'blah_blah_blah',   # icao
                '200609201751',     # date
                330,                # wind_direction
                8,                  # wind_speed_knots
                0,                  # gust_speed_knots
                10.000000,          # visibility_miles
                16.000000,          # temperature_c
                1.000000,           # dewpoint_c
                1018,               # pressure_hpa
                25,                 # relative_humidity
            ],
            [
                'boing',            # icao
                '200610150918',     # date
                2,                  # wind_direction
                57,                 # wind_speed_knots
                59,                 # gust_speed_knots
                0.000000,           # visibility_miles
                31.5    ,           # temperature_c
                15.000000,          # dewpoint_c
                939,                # pressure_hpa
                19,                 # relative_humidity
            ],
            [
                'keep_music_evil',  # icao
                '200512161530',     # date
                -1,                 # wind_direction
                -1000,              # wind_speed_knots
                -1000,              # gust_speed_knots
                -1000.000000,       # visibility_miles
                -1000.000000,       # temperature_c
                -1000.000000,       # dewpoint_c
                -1000,              # pressure_hpa
                -1000,              # relative_humidity
            ],
        ];

    my $database_connection =
        DBI->connect(
            "dbi:SQLite2:$database_file_name",
            q{},
            q{},
            {
                AutoCommit => 0,
                RaiseError => 1,
            }
        );

    clean_icao_history_set($data);

    eval { load_icao_history($database_connection, $data) };

    my $exception
    if ($exception = Mac::Apps::Seasonality::InvalidDatumException->caught()) {
        ...
    } elsif ($EVAL_ERROR) {
        ...
    } # end if

DESCRIPTION

Top

Seasonality http://gauchosoft.com/Software/Seasonality/ is a weather tracking and display application for Mac OS X. This module provides a means of getting data into Seasonality that it cannot retrieve on its own.

INTERFACE

Top

Nothing is exported by default, but you can import everything using the :all tag.

Conversion

The following can be imported using the :conversion tag.

convert_from_fahrenheit_to_celsius($fahrenheit)
convert_from_inches_of_mercury_to_hectopascals($inches)
convert_from_miles_per_hour_to_knots($mph)

These do the obvious conversions, but return the original value if it is undef or the "$SEASONALITY_INVALID_DATA" in Mac::Apps::Seasonality::Constants value.

Input data cleanup

The following can be imported using the :cleaning tag.

clean_icao_history_set($icao_history_ref)

Takes a reference to an array of references to arrays containing values representing a single data point. The values in the second level arrays are expected to be in the icao_history table's schema column order. In other words, the parameter is a table that matches the icao_history database table's layout.

Any invalid data in the set is marked as missing via the "$SEASONALITY_INVALID_DATA" in Mac::Apps::Seasonality::Constants value.

Returns a list of messages about the data that was affected.

clean_icao_history_point($icao_history_point_ref, $line_number)

Takes a reference to an array of values representing a single data point and the original line number this data was found on in the input source. The values are expected to be in the icao_history table's schema column order.

Any invalid data in the point is marked as missing via the "$SEASONALITY_INVALID_DATA" in Mac::Apps::Seasonality::Constants value.

Returns a list of messages about the data that was affected.

Individual column cleanup

The following can be imported using the :cleaning tag.

clean_wind_direction( $icao_history_point_ref, $line_number)
clean_wind_speed_knots( $icao_history_point_ref, $line_number)
clean_gust_speed_knots( $icao_history_point_ref, $line_number)
clean_visibility_miles( $icao_history_point_ref, $line_number)
clean_temperature_c( $icao_history_point_ref, $line_number)
clean_dewpoint_c( $icao_history_point_ref, $line_number)
clean_pressure_hpa( $icao_history_point_ref, $line_number)
clean_relative_humidity( $icao_history_point_ref, $line_number)

Each of these takes a reference to an array of values representing a single data point and the original line number this data was found on in the input source. They pick out the datum that they are concerned about from the array and attempt to fix any problems they find.

A list of messages about the modifications made is returned.

Data loading.

load_icao_history($database_connection, $icao_history_ref)

Takes a reference to a DBI handle and to a reference to a set of ICAO metrics and loads the data into the database.

$database_connection must be an open handle to an SQLite2 database with Seasonality's schema. This handle must have the RaiseError option set on it; this module does no error checking of database actions on its own.

$icao_history_ref must be a reference to an array of ICAO data points, where each data point is represented as a reference to an array of values in the following order:

Observation location identifier
Date and time of observation
Wind direction in degrees
Wind speed in knots
Gust speed in knots
Visibility in miles
Temperature in degrees Celsius
Dew point in degrees Celsius
Atmospheric pressure in hectopascals
Relative humidity in percent

If the attempt to load data is successful, no useful data is returned. All failures result in exceptions.

Input data validation

The following can be imported using the :validation tag.

validate_icao_history_set($icao_history_ref)

Takes a reference to an array of references to arrays containing values representing a single data point. The values in the second level arrays are expected to be in the icao_history table's schema column order. In other words, the parameter is a table that matches the icao_history database table's layout.

If the data is valid, no useful value is returned.

If the data is invalid, an instance of Mac::Apps::Seasonality::InvalidDatumException describing the problem is thrown.

validate_icao_history_point($icao_history_point_ref, $line_number)

Takes a reference to an array of values representing a single data point and the original line number this data was found on in the input source. The values are expected to be in the icao_history table's schema column order.

If the data is valid, no useful value is returned.

If the data is invalid, an instance of Mac::Apps::Seasonality::InvalidDatumException describing the problem is thrown.

Individual column validation

The following can be imported using the :validation tag.

validate_icao( $icao_history_point_ref, $line_number)
validate_date( $icao_history_point_ref, $line_number)
validate_wind_direction( $icao_history_point_ref, $line_number)
validate_wind_speed_knots( $icao_history_point_ref, $line_number)
validate_gust_speed_knots( $icao_history_point_ref, $line_number)
validate_visibility_miles( $icao_history_point_ref, $line_number)
validate_temperature_c( $icao_history_point_ref, $line_number)
validate_dewpoint_c( $icao_history_point_ref, $line_number)
validate_pressure_hpa( $icao_history_point_ref, $line_number)
validate_relative_humidity( $icao_history_point_ref, $line_number)

Each of these takes a reference to an array of values representing a single data point and the original line number this data was found on in the input source. They pick out the datum that they are concerned about from the array and check whether it conforms to the restrictions of that particular column.

If the datum is valid, no useful value is returned.

If the datum is invalid, an instance of Mac::Apps::Seasonality::InvalidDatumException describing the problem is thrown.

DIAGNOSTICS

Top

TODO

CONFIGURATION AND ENVIRONMENT

Top

This module assumes that it is running against a database in the format used by Seasonality versions 1.3 and 1.4.

DEPENDENCIES

Top

DBD::SQLite2 Regexp::Common Mac::Apps::Seasonality::Constants

INCOMPATIBILITIES

Top

This module will not work on databases used by versions of Seasonality earlier than version 1.3.

BUGS AND LIMITATIONS

Top

· The interface to this module is not frozen yet.
· Dates are only checked that they consist of twelve digits and are not yet checked to see whether they are valid in the Gregorian calendar.
· The DIAGNOSTICS section above has not been filled in.

Please report any bugs or feature requests to bug-mac-apps-seasonality-loadicaohistory@rt.cpan.org, or through the web interface at http://rt.cpan.org.

AUTHOR

Top

Elliot Shank <perl@galumph.com>

LICENSE AND COPYRIGHT

Top

Copyright ©2006-2007, Elliot Shank <perl@galumph.com>. All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.

DISCLAIMER OF WARRANTY

Top

BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION.

IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE SOFTWARE AS PERMITTED BY THE ABOVE LICENSE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Mac-Apps-Seasonality-LoadICAOHistory documentation Contained in the Mac-Apps-Seasonality-LoadICAOHistory distribution. 
package Mac::Apps::Seasonality::LoadICAOHistory;

use utf8;
use 5.008006;
use strict;
use warnings;
use Carp;

use version; our $VERSION = qv('v0.0.6');

use Exporter qw( import );

our @EXPORT_OK =
    qw{
        &convert_from_fahrenheit_to_celsius
        &convert_from_inches_of_mercury_to_hectopascals
        &convert_from_miles_per_hour_to_knots

        &clean_icao_history_set
        &clean_icao_history_point
        &clean_wind_direction
        &clean_wind_speed_knots
        &clean_gust_speed_knots
        &clean_visibility_miles
        &clean_temperature_c
        &clean_dewpoint_c
        &clean_pressure_hpa
        &clean_relative_humidity

        &load_icao_history

        &validate_icao_history_set
        &validate_icao_history_point
        &validate_icao
        &validate_date
        &validate_wind_direction
        &validate_wind_speed_knots
        &validate_gust_speed_knots
        &validate_visibility_miles
        &validate_temperature_c
        &validate_dewpoint_c
        &validate_pressure_hpa
        &validate_relative_humidity
    };
our %EXPORT_TAGS    = (
    all => [@EXPORT_OK],
    conversion => [
        qw{
            &convert_from_fahrenheit_to_celsius
            &convert_from_inches_of_mercury_to_hectopascals
            &convert_from_miles_per_hour_to_knots
        }
    ],
    cleaning => [
        qw{
            &clean_icao_history_set
            &clean_icao_history_point
            &clean_wind_direction
            &clean_wind_speed_knots
            &clean_gust_speed_knots
            &clean_visibility_miles
            &clean_temperature_c
            &clean_dewpoint_c
            &clean_pressure_hpa
            &clean_relative_humidity
        }
    ],
    validation => [
        qw{
            &validate_icao_history_set
            &validate_icao_history_point
            &validate_icao
            &validate_date
            &validate_wind_direction
            &validate_wind_speed_knots
            &validate_gust_speed_knots
            &validate_visibility_miles
            &validate_temperature_c
            &validate_dewpoint_c
            &validate_pressure_hpa
            &validate_relative_humidity
        }
    ],
);

use Readonly;

use DBI qw{ :sql_types };
use Regexp::Common;

use Mac::Apps::Seasonality::Constants qw{ :database :data };
use Mac::Apps::Seasonality::LoadICAOHistoryExceptions;


Readonly my $FAHRENHEIT_CELSIUS_OFFSET => 32.0;
Readonly my $FAHRENHEIT_CELSIUS_FACTOR => 5.0 / 9.0;
sub convert_from_fahrenheit_to_celsius {
    my ($fahrenheit) = @_;

    return $fahrenheit if not defined $fahrenheit;
    return $fahrenheit if q{} eq $fahrenheit;
    return $fahrenheit if $SEASONALITY_INVALID_DATA == $fahrenheit;

    return
            ($fahrenheit - $FAHRENHEIT_CELSIUS_OFFSET)
        *   $FAHRENHEIT_CELSIUS_FACTOR;
} # end convert_from_fahrenheit_to_celsius()

Readonly my $HECTOPASCALS_PER_INCH_OF_MERCURY => 33.863886667;
sub convert_from_inches_of_mercury_to_hectopascals {
    my ($inches) = @_;

    return $inches if not defined $inches;
    return $inches if q{} eq $inches;
    return $inches if $SEASONALITY_INVALID_DATA == $inches;

    return $inches * $HECTOPASCALS_PER_INCH_OF_MERCURY;
} # end convert_from_inches_of_mercury_to_hectopascals()

Readonly my $KNOTS_PER_MILES_PER_HOUR => 0.868_976_242;
sub convert_from_miles_per_hour_to_knots {
    my ($mph) = @_;

    return $mph if not defined $mph;
    return $mph if q{} eq $mph;
    return $mph if $SEASONALITY_INVALID_DATA == $mph;

    return $mph * $KNOTS_PER_MILES_PER_HOUR;
} # end convert_from_miles_per_hour_to_knots()

sub clean_icao_history_set {
    my $icao_history_ref = shift;
    my $line_number = 1;
    my @clean_messages;

    foreach my $icao_history_point_ref (@{$icao_history_ref}) {
        push
            @clean_messages,
            clean_icao_history_point($icao_history_point_ref, $line_number);

        $line_number++;
    } # end foreach

    return @clean_messages;
} # end clean_icao_history_set()

sub clean_icao_history_point {
    my ($icao_history_point_ref, $line_number) = @_;
    my @clean_messages;

    _check_basic_history_point_validity(
        $icao_history_point_ref,
        $line_number,
        'clean_icao_history_point()'
    );

    push @clean_messages, clean_wind_direction(    $icao_history_point_ref, $line_number);
    push @clean_messages, clean_wind_speed_knots(  $icao_history_point_ref, $line_number);
    push @clean_messages, clean_gust_speed_knots(  $icao_history_point_ref, $line_number);
    push @clean_messages, clean_visibility_miles(  $icao_history_point_ref, $line_number);
    push @clean_messages, clean_temperature_c(     $icao_history_point_ref, $line_number);
    push @clean_messages, clean_dewpoint_c(        $icao_history_point_ref, $line_number);
    push @clean_messages, clean_pressure_hpa(      $icao_history_point_ref, $line_number);
    push @clean_messages, clean_relative_humidity( $icao_history_point_ref, $line_number);

    return @clean_messages;
} # end clean_icao_history_point()

sub clean_wind_direction {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_integer(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_WIND_DIRECTION,
            $SEASONALITY_WIND_DIRECTION_MINIMUM,
            $SEASONALITY_WIND_DIRECTION_MAXIMUM,
        );
} # end clean_wind_direction()

sub clean_wind_speed_knots {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_integer(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_WIND_SPEED_KNOTS,
            $SEASONALITY_WIND_SPEED_MINIMUM,
            $SEASONALITY_WIND_SPEED_MAXIMUM,
        );
} # end clean_wind_speed_knots()

sub clean_gust_speed_knots {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_integer(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_GUST_SPEED_KNOTS,
            $SEASONALITY_WIND_SPEED_MINIMUM,
            $SEASONALITY_WIND_SPEED_MAXIMUM,
        );
} # end clean_gust_speed_knots()

sub clean_visibility_miles {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_floating_point(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_VISIBILITY_MILES,
            $SEASONALITY_VISIBILITY_MINIMUM,
            $SEASONALITY_VISIBILITY_MAXIMUM,
        );
} # end clean_visibility_miles()

sub clean_temperature_c {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_floating_point(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_TEMPERATURE_C,
            $SEASONALITY_TEMPERATURE_MINIMUM,
            $SEASONALITY_TEMPERATURE_MAXIMUM,
        );
} # end clean_temperature_c()

sub clean_dewpoint_c {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_floating_point(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_DEWPOINT_C,
            $SEASONALITY_TEMPERATURE_MINIMUM,
            $SEASONALITY_TEMPERATURE_MAXIMUM,
        );
} # end clean_dewpoint_c()

sub clean_pressure_hpa {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_integer(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_PRESSURE_HPA,
            $SEASONALITY_PRESSURE_MINIMUM,
            $SEASONALITY_PRESSURE_MAXIMUM,
        );
} # end clean_pressure_hpa()

sub clean_relative_humidity {
    my ($icao_history_point_ref, $line_number) = @_;

    return
        _clean_integer(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_RELATIVE_HUMIDITY,
            $SEASONALITY_RELATIVE_HUMIDITY_MINIMUM,
            $SEASONALITY_RELATIVE_HUMIDITY_MAXIMUM,
        );
} # end clean_relative_humidity()

sub load_icao_history {
    my ($database_connection, $icao_history_ref) = @_;

    validate_icao_history_set($icao_history_ref);

    my $delete_statement = _prepare_icao_history_delete_statement($database_connection);
    my $insert_statement = _prepare_icao_history_insert_statement($database_connection);
    my $db_status_statement = _prepare_db_status_update_statement($database_connection);

    foreach my $icao_history_point_ref (@{$icao_history_ref}) {
        _load_icao_history_row(
            $delete_statement,
            $insert_statement,
            $db_status_statement,
            $icao_history_point_ref,
        );
    } # end foreach

    $database_connection->commit();

    return;
} # end load_icao_history()

sub validate_icao_history_set {
    my $icao_history_ref = shift;
    my $line_number = 1;

    foreach my $icao_history_point_ref (@{$icao_history_ref}) {
        validate_icao_history_point($icao_history_point_ref, $line_number);

        $line_number++;
    } # end foreach

    return;
} # end validate_icao_history_set()

sub validate_icao_history_point {
    my ($icao_history_point_ref, $line_number) = @_;

    _check_basic_history_point_validity(
        $icao_history_point_ref,
        $line_number,
        'clean_icao_history_point()'
    );

    validate_icao(              $icao_history_point_ref, $line_number);
    validate_date(              $icao_history_point_ref, $line_number);
    validate_wind_direction(    $icao_history_point_ref, $line_number);
    validate_wind_speed_knots(  $icao_history_point_ref, $line_number);
    validate_gust_speed_knots(  $icao_history_point_ref, $line_number);
    validate_visibility_miles(  $icao_history_point_ref, $line_number);
    validate_temperature_c(     $icao_history_point_ref, $line_number);
    validate_dewpoint_c(        $icao_history_point_ref, $line_number);
    validate_pressure_hpa(      $icao_history_point_ref, $line_number);
    validate_relative_humidity( $icao_history_point_ref, $line_number);

    return;
} # end validate_icao_history_point()

sub validate_icao {
    my ($icao_history_point_ref, $line_number) = @_;

    my ($icao, $column_number) =
        _get_value_and_column_number_and_validate_defined(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_ICAO
        );

    if (
            $icao
        !~  m{
                                \A
                                \w {$SEASONALITY_ICAO_MINIMUM_LENGTH,$SEASONALITY_ICAO_MAXIMUM_LENGTH}
                                \z
                        }xmso
    ) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "Value for $SEASONALITY_HISTORY_COLUMN_ICAO ('$icao') on line"
                    . " $line_number, column number $column_number, does not"
                    . " consist of $SEASONALITY_ICAO_MINIMUM_LENGTH to"
                    . " $SEASONALITY_ICAO_MAXIMUM_LENGTH alphanumeric"
                    . ' characters.',
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $SEASONALITY_HISTORY_COLUMN_ICAO,
            column_number => $column_number,
            invalid_value => $icao,
        );
    } # end if

    return;
} # end validate_icao()

sub validate_date {
    my ($icao_history_point_ref, $line_number) = @_;

    my ($date, $column_number) =
        _get_value_and_column_number_and_validate_defined(
            $icao_history_point_ref,
            $line_number,
            $SEASONALITY_HISTORY_COLUMN_DATE
        );

    if ($date !~ m{ \A \d{12} \z }xms) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "Value given for $SEASONALITY_HISTORY_COLUMN_DATE ('$date') on"
                    . " line $line_number, column number, $column_number, is"
                    . ' not 12 digits.',
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $SEASONALITY_HISTORY_COLUMN_DATE,
            column_number => $column_number,
            invalid_value => $date,
        );
    } # end if

    return;
} # end validate_date()

sub validate_wind_direction {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_integer(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_WIND_DIRECTION,
        $SEASONALITY_WIND_DIRECTION_MINIMUM,
        $SEASONALITY_WIND_DIRECTION_MAXIMUM,
    );

    return;
} # end validate_wind_direction()

sub validate_wind_speed_knots {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_integer(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_WIND_SPEED_KNOTS,
        $SEASONALITY_WIND_SPEED_MINIMUM,
        $SEASONALITY_WIND_SPEED_MAXIMUM,
    );

    return;
} # end validate_wind_speed_knots()

sub validate_gust_speed_knots {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_integer(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_GUST_SPEED_KNOTS,
        $SEASONALITY_WIND_SPEED_MINIMUM,
        $SEASONALITY_WIND_SPEED_MAXIMUM,
    );

    return;
} # end validate_gust_speed_knots()

sub validate_visibility_miles {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_floating_point(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_VISIBILITY_MILES,
        $SEASONALITY_VISIBILITY_MINIMUM,
        $SEASONALITY_VISIBILITY_MAXIMUM,
    );

    return;
} # end validate_visibility_miles()

sub validate_temperature_c {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_floating_point(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_TEMPERATURE_C,
        $SEASONALITY_TEMPERATURE_MINIMUM,
        $SEASONALITY_TEMPERATURE_MAXIMUM,
    );

    return;
} # end validate_temperature_c()

sub validate_dewpoint_c {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_floating_point(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_DEWPOINT_C,
        $SEASONALITY_TEMPERATURE_MINIMUM,
        $SEASONALITY_TEMPERATURE_MAXIMUM,
    );

    return;
} # end validate_dewpoint_c()

sub validate_pressure_hpa {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_integer(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_PRESSURE_HPA,
        $SEASONALITY_PRESSURE_MINIMUM,
        $SEASONALITY_PRESSURE_MAXIMUM,
    );

    return;
} # end validate_pressure_hpa()

sub validate_relative_humidity {
    my ($icao_history_point_ref, $line_number) = @_;

    _validate_integer(
        $icao_history_point_ref,
        $line_number,
        $SEASONALITY_HISTORY_COLUMN_RELATIVE_HUMIDITY,
        $SEASONALITY_RELATIVE_HUMIDITY_MINIMUM,
        $SEASONALITY_RELATIVE_HUMIDITY_MAXIMUM,
    );

    return;
} # end validate_relative_humidity()

sub _check_basic_history_point_validity {
    # Don't want to mess with caller(), so explicit subroutine name is used.
    my ($icao_history_point_ref, $line_number, $subroutine) = @_;

    if (! defined $icao_history_point_ref) {
        ## no critic (RequireInterpolationOfMetachars)
        Mac::Apps::Seasonality::InvalidDataSizeException->throw(
            message =>
                'INTERNAL ERROR: please report this to the maintainer--'
                    . ' undefined $icao_history_point_ref passed to'
                    . " $subroutine.",
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            expected_number_of_elements => $SEASONALITY_HISTORY_COLUMNS_COUNT,
            actual_number_of_elements => 0,
        );
        ## use critic
    } # end if

    my $column_count = scalar @{$icao_history_point_ref};
    my $incorrect_quantity_qualifier =
        $SEASONALITY_HISTORY_COLUMNS_COUNT > $column_count
            ? 'Insufficient'
            : $SEASONALITY_HISTORY_COLUMNS_COUNT < $column_count
                ? 'Excess'
                : undef;
    if ($incorrect_quantity_qualifier) {
        Mac::Apps::Seasonality::InvalidDataSizeException->throw(
            message =>
                "$incorrect_quantity_qualifier data found on line $line_number;"
                    . " expected $SEASONALITY_HISTORY_COLUMNS_COUNT columns,"
                    . " but found $column_count columns.",
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            expected_number_of_elements => $SEASONALITY_HISTORY_COLUMNS_COUNT,
            actual_number_of_elements => $column_count,
        );
    } # end if

    return;
} # end _check_basic_history_point_validity()

Readonly my $ROUND_UP_POINT => 0.5;
sub _clean_integer {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $minimum_value,
        $maximum_value
    )
        = @_;

    my ($metric_value, $column_number, $defined_message) =
        _get_value_and_column_number_and_clean_undefined(
            $icao_history_point_ref,
            $line_number,
            $column_name,
        );
    my @clean_messages = $defined_message ? ( $defined_message ) : ( );

    if ($metric_value !~ m/\A $RE{num}{int} \z/xms) {
        my $real_column_number = $column_number - 1;

        if ($metric_value =~ m/\A $RE{num}{real} \z/xms) {
            my $int_value = int $metric_value + $ROUND_UP_POINT;
            $icao_history_point_ref->[ $real_column_number ] = $int_value;

            push
                @clean_messages,
                "Value given for $column_name ('$metric_value') on line"
                    . " $line_number, column number $column_number, was"
                    . " rounded to $int_value.";
        } else {
            $icao_history_point_ref->[ $real_column_number ] = $SEASONALITY_INVALID_DATA;

            push
                @clean_messages,
                "Value given for $column_name ('$metric_value') on line"
                    . " $line_number, column number $column_number, does not"
                    . ' look like an integer. Will be treated as missing.';
        };
    } # end if

    push
        @clean_messages,
        _clean_number_range(
            $icao_history_point_ref,
            $line_number,
            $column_name,
            $column_number,
            $minimum_value,
            $maximum_value
        );

    return @clean_messages;
} # end _clean_integer()

sub _clean_floating_point {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $minimum_value,
        $maximum_value
    )
        = @_;

    my ($metric_value, $column_number, $defined_message) =
        _get_value_and_column_number_and_clean_undefined(
            $icao_history_point_ref,
            $line_number,
            $column_name,
        );
    my @clean_messages = $defined_message ? ( $defined_message ) : ( );

    if ($metric_value !~ m/\A $RE{num}{real} \z/xms) {
        $icao_history_point_ref->[ $column_number - 1 ] = $SEASONALITY_INVALID_DATA;

        push
            @clean_messages,
            "Value given for $column_name ('$metric_value') on line"
                . " $line_number, column number $column_number, does not"
                . ' look like a number. Will be treated as missing.';
    } # end if

    push
        @clean_messages,
        _clean_number_range(
            $icao_history_point_ref,
            $line_number,
            $column_name,
            $column_number,
            $minimum_value,
            $maximum_value
        );

    return @clean_messages;
} # end _clean_floating_point()

sub _get_value_and_column_number_and_clean_undefined {
    my ($icao_history_point_ref, $line_number, $column_name) = @_;

    my $real_column_number =
        $SEASONALITY_HISTORY_COLUMN_NUMBERS_BY_COLUMN_NAME_REF->{$column_name};

    my $metric_value = $icao_history_point_ref->[ $real_column_number ];

    my $column_number = $real_column_number + 1;

    my $message;
    if (not defined $metric_value or $metric_value eq q{}) {
        $metric_value = $SEASONALITY_INVALID_DATA;
        $icao_history_point_ref->[ $real_column_number ] = $metric_value;

        $message =
            "No value given for $column_name on line $line_number, column"
                . " number $column_number.";
    } # end if

    return ($metric_value, $column_number, $message);
} # end _get_value_and_column_number_and_clean_undefined()

sub _clean_number_range {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $column_number,
        $minimum_value,
        $maximum_value
    )
        = @_;

    my $real_column_number = $column_number - 1;
    my $metric_value = $icao_history_point_ref->[$real_column_number];

    if ($metric_value == $SEASONALITY_INVALID_DATA) {
        return;
    } # end if

    if ($metric_value < $minimum_value or $metric_value > $maximum_value) {
        $icao_history_point_ref->[$real_column_number] = $SEASONALITY_INVALID_DATA;

        return
            "Value given for $column_name ('$metric_value') on line"
                . " $line_number, column number $column_number, was not"
                . " within the range $minimum_value to $maximum_value."
                . ' Will be treated as a missing value.'
    } # end if

    return;
} # end _clean_number_range()

sub _validate_integer {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $minimum_value,
        $maximum_value
    )
        = @_;

    my ($metric_value, $column_number) =
        _get_value_and_column_number_and_validate_defined(
            $icao_history_point_ref,
            $line_number,
            $column_name,
        );

    if ($metric_value !~ m/\A $RE{num}{int} \z/xms) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "Value given for $column_name ('$metric_value') on line"
                    . " $line_number, column number $column_number, does not"
                    . ' look like an integer.',
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $column_name,
            column_number => $column_number,
            invalid_value => $metric_value,
        );
    } # end if

    _validate_number_range(
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $column_number,
        $metric_value,
        $minimum_value,
        $maximum_value
    );

    return;
} # end _validate_integer()

sub _validate_floating_point {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $minimum_value,
        $maximum_value
    )
        = @_;

    my ($metric_value, $column_number) =
        _get_value_and_column_number_and_validate_defined(
            $icao_history_point_ref,
            $line_number,
            $column_name,
        );

    if ($metric_value !~ m/\A $RE{num}{real} \z/xms) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "Value given for $column_name ('$metric_value') on line"
                    . " $line_number, column number $column_number, does not"
                    . ' look like a number.',
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $column_name,
            column_number => $column_number,
            invalid_value => $metric_value,
        );
    } # end if

    _validate_number_range(
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $column_number,
        $metric_value,
        $minimum_value,
        $maximum_value
    );

    return;
} # end _validate_floating_point()

sub _get_value_and_column_number_and_validate_defined {
    my ($icao_history_point_ref, $line_number, $column_name) = @_;

    my $column_number =
        $SEASONALITY_HISTORY_COLUMN_NUMBERS_BY_COLUMN_NAME_REF->{$column_name};

    my $metric_value = $icao_history_point_ref->[ $column_number ];

    $column_number++;

    if (! defined $metric_value) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "No value given for $column_name on line $line_number, column"
                    . " number $column_number.",
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $column_name,
            column_number => $column_number,
            invalid_value => $metric_value,
        );
    } # end if

    return ($metric_value, $column_number);
} # end _get_value_and_column_number_and_validate_defined()

sub _validate_number_range {
    my (
        $icao_history_point_ref,
        $line_number,
        $column_name,
        $column_number,
        $metric_value,
        $minimum_value,
        $maximum_value
    )
        = @_;

    if ($metric_value == $SEASONALITY_INVALID_DATA) {
        return;
    } # end if

    if ($metric_value < $minimum_value or $metric_value > $maximum_value) {
        Mac::Apps::Seasonality::InvalidDatumException->throw(
            message =>
                "Value given for $column_name ('$metric_value') on line"
                    . " $line_number, column number $column_number, is not"
                    . " within the range $minimum_value to $maximum_value.",
            input_line_number => $line_number,
            input_data_ref => $icao_history_point_ref,
            column_name => $column_name,
            column_number => $column_number,
            invalid_value => $metric_value,
        );
    } # end if

    return;
} # end _validate_number_range()

sub _prepare_icao_history_delete_statement {
    my $database_connection = shift;

    my $delete_statement = $database_connection->prepare(<<"END_DML");
        DELETE FROM
            $SEASONALITY_HISTORY_TABLE
        WHERE
                $SEASONALITY_HISTORY_COLUMN_ICAO = ?
            AND $SEASONALITY_HISTORY_COLUMN_DATE = ?
END_DML

    # The following values are bogus-- these statements are simply to tell
    # the driver what the parameter types are so that we can use execute()
    # without calling bind_param() each time. See "Binding Values Without
    # bind_param()" on pages 126-7 of Programming the Perl DBI.
    $delete_statement->bind_param(1, 'x', SQL_VARCHAR);
    $delete_statement->bind_param(2, '193705061825', SQL_DATETIME);

    return $delete_statement;
} # end _prepare_icao_history_delete_statement()

sub _prepare_icao_history_insert_statement {
    my $database_connection = shift;

    my $insert_statement = $database_connection->prepare(<<"END_DML");
        INSERT INTO
            $SEASONALITY_HISTORY_TABLE
        (
            $SEASONALITY_HISTORY_COLUMN_ICAO,
            $SEASONALITY_HISTORY_COLUMN_DATE,
            $SEASONALITY_HISTORY_COLUMN_WIND_DIRECTION,
            $SEASONALITY_HISTORY_COLUMN_WIND_SPEED_KNOTS,
            $SEASONALITY_HISTORY_COLUMN_GUST_SPEED_KNOTS,
            $SEASONALITY_HISTORY_COLUMN_VISIBILITY_MILES,
            $SEASONALITY_HISTORY_COLUMN_TEMPERATURE_C,
            $SEASONALITY_HISTORY_COLUMN_DEWPOINT_C,
            $SEASONALITY_HISTORY_COLUMN_PRESSURE_HPA,
            $SEASONALITY_HISTORY_COLUMN_RELATIVE_HUMIDITY
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
END_DML

    # The following values are bogus-- these statements are simply to tell
    # the driver what the parameter types are so that we can use execute()
    # without calling bind_param() each time. See "Binding Values Without
    # bind_param()" on pages 126-7 of "Programming the Perl DBI".
    ## no critic (ProhibitMagicNumbers)
    $insert_statement->bind_param( 1, 'x',              SQL_VARCHAR);
    $insert_statement->bind_param( 2, '193705061825',   SQL_DATETIME);
    $insert_statement->bind_param( 3, 1,                SQL_INTEGER);
    $insert_statement->bind_param( 4, 1,                SQL_INTEGER);
    $insert_statement->bind_param( 5, 1,                SQL_INTEGER);
    $insert_statement->bind_param( 6, 1.0,              SQL_FLOAT);
    $insert_statement->bind_param( 7, 1.0,              SQL_FLOAT);
    $insert_statement->bind_param( 8, 1.0,              SQL_FLOAT);
    $insert_statement->bind_param( 9, 1,                SQL_INTEGER);
    $insert_statement->bind_param(10, 1,                SQL_INTEGER);
    ## use critic

    return $insert_statement;
} # end _prepare_icao_history_insert_statement()

sub _prepare_db_status_update_statement {
    my $database_connection = shift;

    my $update_statement = $database_connection->prepare(<<"END_DML");
        UPDATE
            $SEASONALITY_DB_STATUS_TABLE
        SET
            $SEASONALITY_DB_STATUS_COLUMN_NEW_RECORDS_SINCE_VACUUM
                = 2 + $SEASONALITY_DB_STATUS_COLUMN_NEW_RECORDS_SINCE_VACUUM
END_DML

    return $update_statement;
} # end _prepare_db_status_update_statement()

sub _load_icao_history_row {
    my (
        $delete_statement,
        $insert_statement,
        $db_status_statement,
        $icao_history_point_ref
    ) =
        @_;

    $delete_statement->execute($icao_history_point_ref->[0], $icao_history_point_ref->[1]);
    $insert_statement->execute(@{$icao_history_point_ref});
    $db_status_statement->execute();

    return;
} # end _load_icao_history_row()


1; # Magic true value required at end of module
__END__


# setup vim: set filetype=perl tabstop=4 softtabstop=4 expandtab :
# setup vim: set shiftwidth=4 shiftround textwidth=78 nowrap autoindent :
# setup vim: set foldmethod=indent foldlevel=0 :