NAME

Footprintless::Plugin::Database::PreparedStatementTemplate

VERSION

version 1.06

SYNOPSIS

    use Footprintless::Plugin::Database::PreparedStatementTemplate;
    
    my $second_fruit;
    my $statement = Footprintless::Plugin::Database::PreparedStatementTemplate->new(
        "SELECT * FROM fruit_basket WHERE fruit IN ('_FRUIT_1_', '_FRUIT_2_', '_FRUIT_3_')",
        _FRUIT_1_ => 'first_fruit',
        _FRUIT_2_ => sub { $second_fruit },
        _FRUIT_3_ => { value => 'banana' }
    );
    
    my $context = { first_fruit => 'grape' };
    $second_fruit = 'apple';
    
    my $query1 = $statement->query($context);
    croak("query1 SQL bad")
      unless $query1->{sql} eq
      "SELECT * FROM fruit_basket WHERE fruit IN ('?', '?', '?')";
    croak("query1, param[0] bad") unless $query1->{parameters}->[0] eq 'grape';
    croak("query1, param[1] bad") unless $query1->{parameters}->[1] eq 'apple';
    croak("query1, param[2] bad") unless $query1->{parameters}->[2] eq 'banana';
    
    $context->{first_fruit} = 'pear';
    $second_fruit = 'strawberry';
    
    my $query2 = $statement->query($context);
    croak("query2 SQL bad")
      unless $query2->{sql} eq
      "SELECT * FROM fruit_basket WHERE fruit IN ('?', '?', '?')";
    croak("query2, param[0] bad") unless $query2->{parameters}->[0] eq 'pear';
    croak("query2, param[1] bad") unless $query2->{parameters}->[1] eq 'strawberry';
    croak("query2, param[2] bad") unless $query2->{parameters}->[2] eq 'banana';

DESCRIPTION

Footprintless::Plugin::Database::PreparedStatementTemplate

Prepared statements are a best practice, yet they are a pain in the neck, since every parameter is represented by a '?' and the parameters are provided in an array of values that correspond to the '?' in the prepared statement. This class allows the user to create these painful prepared statements using named parameters AND binding them to a context either by properties, hard-coded values, and/or closures.

CONSTRUCTORS

new($sql_template, %bindings)

Creates the prepared statement template with the given bindings. The key of each binding is the string to replace in the $sql_template and the value of each binding tells this prepared statement template how to bind values to each of the parameters. The binding value may be one of the following:

key

To bind a property from a context, the binding value should be a simple string with the property name, or a hash-ref like this: { key = 'property_name' }>

code

To bind a property to a value returned from a closure, the binding value should be a CODE (subroutine) ref, or a hash-ref like this: { code = sub {...} }>

reference

To bind a property to a reference (variable), the binding value should be a scalar-ref, or a hash-ref like this: { reference = \$variable }>

value

To bind a property to a constant value, the binding value should be a hash-ref like this: { value = 'constant_value' }>

METHODS

query($context)

Generate a query acceptable to the Footprintless::Plugin::Database::AbstractProvider using this prepared statement template and a context to bind to. The context is either a class instance or hash-ref that has the properties identified in the bindings passed to the constructor. Properties are primarily a property of the hash-ref, but if it is not defined, the prepared statement template will attempt to call a no-arg method that has the name of the property to extract it's value.

AUTHOR

Lucas Theisen <lucastheisen@pastdev.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2016 by Lucas Theisen.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

SEE ALSO

Please see those modules/websites for more information related to this module.

• Footprintless::Plugin::Database

• Footprintless::Plugin::Database::AbstractProvider